В этой статье вы найдете два быстрых способа изменить цвет ячейки на основе ее значения в Excel 2013, 2010 и 2007. Кроме того, вы узнаете, как использовать формулы в Excel для изменения цвета пустых ячеек или ячеек с помощью ошибки в формулах.
Всем известно, что для изменения цвета заливки ячейки или всего диапазона в Excel просто нажмите кнопку «Цвет заливки». Но что, если вам нужно изменить цвет заливки всех ячеек, содержащих определенное значение? Кроме того, что, если вы хотите, чтобы цвет заливки каждой ячейки изменялся автоматически при изменении содержимого этой ячейки? Далее в статье вы найдете ответы на эти вопросы и пару полезных советов, которые помогут вам выбрать правильный метод решения каждой конкретной проблемы.
Цвет заливки будет меняться в зависимости от значения ячейки.
Проблема: у вас есть таблица или диапазон данных, и вы хотите изменить цвет заливки ячеек в зависимости от их значений. Кроме того, этот цвет должен динамически изменяться, отражая изменения данных в ячейках.
Решение: используйте условное форматирование в Excel, чтобы выделить значения больше X, меньше Y или между X и Y.
Предположим, у вас есть список цен на бензин в разных штатах, и вы хотите выделить красным цветом цены выше 3,7 доллара США, а цены ниже или равные 3,45 доллара США — зеленым цветом.
Примечание. Снимки экрана в этом примере были сделаны в Excel 2010, однако в Excel 2007 и 2013 кнопки, диалоговые окна и настройки будут такими же или немного другими.
Итак, вот что вам нужно сделать шаг за шагом:
Затем нажмите кнопку «Формат», чтобы выбрать, какой цвет заливки следует применить при соблюдении указанного условия.
Результат ваших настроек форматирования будет выглядеть так:
Поскольку нам нужно установить другое условие, которое позволяет нам изменить цвет заливки на зеленый для ячеек со значениями, меньшими или равными 3,45, затем снова нажмите кнопку «Новое правило» и повторите шаги с 3 по 6, установив желаемое правило. Ниже приведен пример второго созданного нами правила условного форматирования:
Когда все будет готово, нажмите ОК. Теперь у вас есть хорошо отформатированная таблица, которая дает вам быстрый взгляд на высокие и низкие цены на газ в разных штатах. Хорошо для них в Техасе!
Совет: Аналогичным образом вы можете изменить цвет шрифта в зависимости от значения ячейки. Для этого просто откройте вкладку «Шрифт» в диалоговом окне «Формат ячеек», как мы это делали на шаге 5, и выберите желаемый цвет шрифта.
После установки цвет заливки не изменится независимо от того, как содержимое ячейки изменится в будущем.
Проблема: вы хотите настроить цвет ячейки на основе ее текущего значения и хотите, чтобы цвет заливки оставался неизменным даже при изменении значения ячейки.
Решение: найдите все ячейки с определенным значением (или значениями) с помощью инструмента «Найти все», затем измените формат найденных ячеек с помощью диалогового окна «Формат ячеек».
Это одна из тех редких задач, для которой нет разъяснений в файлах справки Excel, форумах или блогах и для которой нет прямого решения. И это понятно, ведь эта задача нетипичная. Тем не менее, если вам нужно полностью изменить цвет заливки ячеек, то есть раз и навсегда (или пока вы не измените его вручную), выполните следующие действия.
Здесь возможны несколько сценариев, в зависимости от типа значения, которое вы ищете.
Если вы хотите раскрасить ячейки определенным значением, например 50, 100 или 3,4, на вкладке «Главная» в разделе «Правка» щелкните «Найти» («Выбрать»> «Найти».
Введите желаемое значение и нажмите «Найти все».
Совет: в правой части диалогового окна «Найти и заменить» есть кнопка «Параметры», которая дает вам доступ к ряду расширенных параметров поиска, таких как «Учитывать регистр» и «Сопоставить все содержимое ячейки»… Можно использовать символы подстановки, например звездочку. (*) соответствует любой строке символов или вопросительному знаку (?) соответствует любому одиночному символу.
Как и в предыдущем примере, если мы хотим найти все цены на бензин от 3,7 до 3,799, мы установим следующие критерии поиска:
Теперь щелкните один из найденных элементов в нижней части диалогового окна «Найти и заменить» и нажмите Ctrl + A, чтобы выбрать все найденные элементы. Затем нажмите кнопку Закрыть).
Вот как вы можете выбрать все ячейки с определенными значениями, используя параметр «Найти все» в Excel.
Однако на самом деле нам нужно найти все цены на бензин выше 3,7 доллара. К сожалению, инструмент «Найти и заменить» нам в этом не поможет.
Теперь вы выбрали все ячейки с заданным значением (или значениями), мы просто сделали это с помощью инструмента «Найти и заменить». Все, что вам нужно сделать, это установить цвет заливки для выбранных ячеек.
Откройте диалоговое окно «Формат ячеек» одним из трех способов:
Затем настройте параметры форматирования по своему усмотрению. На этот раз мы установим оранжевый цвет заливки, просто чтобы изменить
Если вы хотите изменить только цвет заливки, не касаясь остальных параметров форматирования, вы можете просто нажать кнопку «Цвет заливки» и выбрать нужный цвет.
Вот результат наших изменений форматирования в Excel:
В отличие от предыдущего метода (с условным форматированием), установленный таким образом цвет заливки никогда не изменится сам по себе без вашего ведома, независимо от того, как меняются значения.
Как и в предыдущем примере, вы можете изменить цвет заливки специальных ячеек двумя способами: динамически и статически.
Цвет ячейки автоматически изменится в зависимости от значения ячейки.
Скорее всего, вы воспользуетесь этим методом для решения проблемы в 99% случаев, т.е заливка ячеек будет меняться в соответствии с заданными условиями.
Например, давайте снова возьмем таблицу цен на бензин, но на этот раз мы добавим еще пару состояний и очистим некоторые ячейки. Теперь посмотрим, как можно обнаружить эти пустые ячейки и изменить их цвет заливки.
= ISBLONE()
= ISBLONE()
= ISERROR()
= ISERROR()
После установки заливка останется неизменной независимо от значения ячейки.
Если вы хотите установить согласованный цвет заливки для пустых ячеек или ячеек с формулами, содержащими ошибки, используйте этот метод:
Если вы хотите выделить ячейки, содержащие формулы с ошибками, выберите «Формулы»> «Ошибки». Как вы можете видеть на изображении выше, вам доступно множество других настроек.Не забывайте, что сделанные таким образом настройки форматирования останутся даже тогда, когда пустые ячейки будут заполнены значениями или исправлены ошибки в формулах. Трудно представить, чтобы кто-то пошел по этому пути, кроме эксперимента.
