2 способа изменить цвет заливки ячеек в Excel в зависимости от их значений

В этой статье вы найдете два быстрых способа изменить цвет ячейки на основе ее значения в Excel 2013, 2010 и 2007. Кроме того, вы узнаете, как использовать формулы в Excel для изменения цвета пустых ячеек или ячеек с помощью ошибки в формулах.

Всем известно, что для изменения цвета заливки ячейки или всего диапазона в Excel просто нажмите кнопку «Цвет заливки». Но что, если вам нужно изменить цвет заливки всех ячеек, содержащих определенное значение? Кроме того, что, если вы хотите, чтобы цвет заливки каждой ячейки изменялся автоматически при изменении содержимого этой ячейки? Далее в статье вы найдете ответы на эти вопросы и пару полезных советов, которые помогут вам выбрать правильный метод решения каждой конкретной проблемы.

• Изменение цвета заливки ячеек в зависимости от их значений (динамически) — цвет заливки будет изменяться автоматически при изменении значения ячейки.
• Изменить цвет ячейки на основе ее текущего значения (статически) — после установки цвет заливки не меняется, независимо от того, как изменяется значение ячейки.
• Изменить цвет некоторых ячеек (пустые, с ошибками, с формулами)

Как в Excel динамически изменять цвет ячейки, основываясь на её значении

Цвет заливки будет меняться в зависимости от значения ячейки.

Проблема: у вас есть таблица или диапазон данных, и вы хотите изменить цвет заливки ячеек в зависимости от их значений. Кроме того, этот цвет должен динамически изменяться, отражая изменения данных в ячейках.

Решение: используйте условное форматирование в Excel, чтобы выделить значения больше X, меньше Y или между X и Y.

Предположим, у вас есть список цен на бензин в разных штатах, и вы хотите выделить красным цветом цены выше 3,7 доллара США, а цены ниже или равные 3,45 доллара США — зеленым цветом.

Примечание. Снимки экрана в этом примере были сделаны в Excel 2010, однако в Excel 2007 и 2013 кнопки, диалоговые окна и настройки будут такими же или немного другими.

Итак, вот что вам нужно сделать шаг за шагом:

1. Выберите таблицу или диапазон, для которого нужно изменить цвет заливки ячеек. В этом примере мы выделяем $ B $ 2: $ H $ 10 (заголовки столбцов и первый столбец статуса не выделяются).
2. Перейдите на вкладку «Главная» в разделе «Стили» и нажмите «Условное форматирование»> «Новое правило).
3. В верхней части диалогового окна «Новое правило форматирования» в поле «Выбор типа правила» выберите «Форматировать только те ячейки, которые содержат).
4. Внизу диалогового окна в поле Форматировать только ячейки с помощью задайте условия для правила. Мы выбираем форматирование только ячеек с условием: Значение ячейки — больше — 3,7, как показано на скриншоте ниже.Затем нажмите кнопку «Формат», чтобы выбрать, какой цвет заливки следует применить при соблюдении указанного условия.
5. В появившемся диалоговом окне «Формат ячеек» откройте вкладку «Заливка», выберите цвет (мы выбрали красноватый) и нажмите «ОК.
6. Это вернет вас в окно «Новое правило форматирования», где образец вашего форматирования будет показан в поле «Предварительный просмотр». Если вас все устраивает, нажмите ОК.

Результат ваших настроек форматирования будет выглядеть так:

Поскольку нам нужно установить другое условие, которое позволяет нам изменить цвет заливки на зеленый для ячеек со значениями, меньшими или равными 3,45, затем снова нажмите кнопку «Новое правило» и повторите шаги с 3 по 6, установив желаемое правило. Ниже приведен пример второго созданного нами правила условного форматирования:

Когда все будет готово, нажмите ОК. Теперь у вас есть хорошо отформатированная таблица, которая дает вам быстрый взгляд на высокие и низкие цены на газ в разных штатах. Хорошо для них в Техасе!

Совет: Аналогичным образом вы можете изменить цвет шрифта в зависимости от значения ячейки. Для этого просто откройте вкладку «Шрифт» в диалоговом окне «Формат ячеек», как мы это делали на шаге 5, и выберите желаемый цвет шрифта.

Как настроить постоянный цвет ячейки, основываясь на её текущем значении

После установки цвет заливки не изменится независимо от того, как содержимое ячейки изменится в будущем.

Проблема: вы хотите настроить цвет ячейки на основе ее текущего значения и хотите, чтобы цвет заливки оставался неизменным даже при изменении значения ячейки.

Решение: найдите все ячейки с определенным значением (или значениями) с помощью инструмента «Найти все», затем измените формат найденных ячеек с помощью диалогового окна «Формат ячеек».

Это одна из тех редких задач, для которой нет разъяснений в файлах справки Excel, форумах или блогах и для которой нет прямого решения. И это понятно, ведь эта задача нетипичная. Тем не менее, если вам нужно полностью изменить цвет заливки ячеек, то есть раз и навсегда (или пока вы не измените его вручную), выполните следующие действия.

Найти и выделить все ячейки, удовлетворяющие заданному условию

Здесь возможны несколько сценариев, в зависимости от типа значения, которое вы ищете.

Если вы хотите раскрасить ячейки определенным значением, например 50, 100 или 3,4, на вкладке «Главная» в разделе «Правка» щелкните «Найти» («Выбрать»> «Найти».

Введите желаемое значение и нажмите «Найти все».

Совет: в правой части диалогового окна «Найти и заменить» есть кнопка «Параметры», которая дает вам доступ к ряду расширенных параметров поиска, таких как «Учитывать регистр» и «Сопоставить все содержимое ячейки»… Можно использовать символы подстановки, например звездочку. (*) соответствует любой строке символов или вопросительному знаку (?) соответствует любому одиночному символу.

Как и в предыдущем примере, если мы хотим найти все цены на бензин от 3,7 до 3,799, мы установим следующие критерии поиска:

Теперь щелкните один из найденных элементов в нижней части диалогового окна «Найти и заменить» и нажмите Ctrl + A, чтобы выбрать все найденные элементы. Затем нажмите кнопку Закрыть).

Вот как вы можете выбрать все ячейки с определенными значениями, используя параметр «Найти все» в Excel.

Однако на самом деле нам нужно найти все цены на бензин выше 3,7 доллара. К сожалению, инструмент «Найти и заменить» нам в этом не поможет.

Измененяем цвета заливки выделенных ячеек при помощи диалогового окна «Формат ячеек»

Теперь вы выбрали все ячейки с заданным значением (или значениями), мы просто сделали это с помощью инструмента «Найти и заменить». Все, что вам нужно сделать, это установить цвет заливки для выбранных ячеек.

Откройте диалоговое окно «Формат ячеек» одним из трех способов:

• нажав Ctrl + 1.
• щелкнув по любой выделенной ячейке правой кнопкой мыши и выбрав «Формат ячеек» в контекстном меню).
• на вкладке Главная> Ячейки> Формат> Формат ячеек).

Затем настройте параметры форматирования по своему усмотрению. На этот раз мы установим оранжевый цвет заливки, просто чтобы изменить

Если вы хотите изменить только цвет заливки, не касаясь остальных параметров форматирования, вы можете просто нажать кнопку «Цвет заливки» и выбрать нужный цвет.

Вот результат наших изменений форматирования в Excel:

В отличие от предыдущего метода (с условным форматированием), установленный таким образом цвет заливки никогда не изменится сам по себе без вашего ведома, независимо от того, как меняются значения.

Изменяем цвет заливки для особых ячеек (пустые, с ошибкой в формуле)

Как и в предыдущем примере, вы можете изменить цвет заливки специальных ячеек двумя способами: динамически и статически.

Используем формулу для изменения цвета заливки особых ячеек в Excel

Цвет ячейки автоматически изменится в зависимости от значения ячейки.

Скорее всего, вы воспользуетесь этим методом для решения проблемы в 99% случаев, т.е заливка ячеек будет меняться в соответствии с заданными условиями.

Например, давайте снова возьмем таблицу цен на бензин, но на этот раз мы добавим еще пару состояний и очистим некоторые ячейки. Теперь посмотрим, как можно обнаружить эти пустые ячейки и изменить их цвет заливки.

1. На вкладке «Главная» в разделе «Стили» щелкните «Условное форматирование»> «Новое правило». Как и во втором шаге примера Как динамически изменять цвет ячейки в зависимости от ее значения.
2. В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения форматируемых ячеек». Затем в поле Форматировать значения, в которых эта формула истинна, введите одну из формул:< >изменить заливку пустых ячеек

   = ISBLONE()
   = ISBLONE()

   для изменения заливки ячеек, содержащих формулы, возвращающие ошибку

   = ISERROR()
   = ISERROR()

    

3. Нажмите кнопку «Формат», выберите нужный цвет заливки на вкладке «Заливка», затем нажмите «ОК». Подробные инструкции приведены в шаге 5 примера «Как динамически изменять цвет ячейки в зависимости от ее значения:
4. Если вас устраивает цвет, нажмите ОК. Вы увидите, как созданное правило будет сразу применено к таблице.

Изменяем цвет заливки особых ячеек статически

После установки заливка останется неизменной независимо от значения ячейки.

Если вы хотите установить согласованный цвет заливки для пустых ячеек или ячеек с формулами, содержащими ошибки, используйте этот метод:

1. Выберите таблицу или диапазон и нажмите F5, чтобы открыть диалоговое окно «Перейти», затем нажмите кнопку «Специальные).
2. В диалоговом окне «Перейти к специальному» выберите «Пустой», чтобы выбрать все пустые ячейки.Если вы хотите выделить ячейки, содержащие формулы с ошибками, выберите «Формулы»> «Ошибки». Как вы можете видеть на изображении выше, вам доступно множество других настроек.
3. Наконец, измените заливку выбранных ячеек или настройте любые другие параметры форматирования с помощью диалогового окна «Форматирование ячеек», как описано в разделе «Изменение заливки выбранных ячеек.

Не забывайте, что сделанные таким образом настройки форматирования останутся даже тогда, когда пустые ячейки будут заполнены значениями или исправлены ошибки в формулах. Трудно представить, чтобы кто-то пошел по этому пути, кроме эксперимента.