Содержание:
В этой статье вы найдете два быстрых способа изменить цвет ячейки в зависимости от ее значения в Excel 2013, 2010 и 2007. Кроме того, вы узнаете, как использовать формулы в Excel для изменения цвета пустых ячеек. или ячейки с ошибками в формулах.
Всем известно, что чтобы изменить цвет заливки отдельной ячейки или целого диапазона в Excel, достаточно нажать кнопку Цвет заливки (Цвет заливки). Но что, если вам нужно изменить цвет заливки всех ячеек, содержащих определенное значение? Более того, что, если вы хотите, чтобы цвет заливки каждой ячейки менялся автоматически при изменении содержимого этой ячейки? Далее в статье вы найдете ответы на эти вопросы и получите пару полезных советов, которые помогут выбрать правильный метод решения каждой конкретной проблемы.
Как динамически изменить цвет ячейки в Excel в зависимости от ее значения
Цвет заливки будет меняться в зависимости от значения ячейки.
Проблема: У вас есть таблица или диапазон данных, и вы хотите изменить цвет заливки ячеек в зависимости от их значений. Причем необходимо, чтобы этот цвет менялся динамически, отражая изменения данных в ячейках.
Решение: Используйте условное форматирование в Excel, чтобы выделить значения больше X, меньше Y или между X и Y.
Допустим, у вас есть список цен на бензин в разных штатах, и вы хотите, чтобы цены были выше, чем $ 3.7, были выделены красным цветом и меньше или равны $ 3.45 - зеленый.
Примечание: Скриншоты для этого примера были сделаны в Excel 2010, однако в Excel 2007 и 2013 кнопки, диалоговые окна и настройки будут точно такими же или немного отличаться.
Итак, вот что вам нужно сделать шаг за шагом:
- Выберите таблицу или диапазон, в котором вы хотите изменить цвет заливки ячеек. В этом примере мы выделяем $B$2:$H$10 (заголовки столбцов и первый столбец, содержащий названия штатов, не выделены).
- Нажмите Главная (Главная), в разделе Стили (Стили) нажмите Условное форматирование (Условное форматирование) > Новые правила (Создать правило).
- В верхней части диалогового окна Новое правило форматирования (Создать правило форматирования) в поле Выберите тип правила (Выберите тип правила) выберите Форматировать только ячейки, содержащие (Форматируйте только те ячейки, которые содержат).
- В нижней части диалогового окна в поле Форматировать только ячейки с (Форматируйте только те ячейки, которые соответствуют следующему условию) Установите условия для правила. Мы выбираем форматировать только ячейки с условием: Значение ячейки (значение ячейки) – больше (более) - 3.7как показано на рисунке ниже.Затем нажмите кнопку Размер (Формат), чтобы выбрать, какой цвет заливки следует применить, если указанное условие выполнено.
- В появившемся диалоговом окне Формат ячеек Вкладка (Формат ячеек) Заполнять (Заливка) и выберите цвет (мы выбрали красный) и нажмите OK.
- После этого вы вернетесь в окно Новое правило форматирования (Создание правила форматирования) где в поле предварительный просмотр (Образец) покажет образец вашего форматирования. Если вы удовлетворены, нажмите OK.
Затем нажмите кнопку Размер (Формат), чтобы выбрать, какой цвет заливки следует применить, если указанное условие выполнено.
Результат ваших настроек форматирования будет выглядеть примерно так:
Так как нам необходимо задать еще одно условие, позволяющее изменить цвет заливки на зеленый для ячеек со значениями меньше или равными 3.45, затем нажмите кнопку еще раз Новые правила (Создать правило) и повторите шаги с 3 по 6, установив нужное правило. Ниже приведен пример второго созданного нами правила условного форматирования:
Когда все будет готово – нажмите OK. Теперь у вас есть красиво отформатированная таблица, которая позволяет вам сразу увидеть максимальные и минимальные цены на газ в разных штатах. Молодцы им там, в Техасе! 🙂
Наконечник: Таким же образом вы можете изменить цвет шрифта в зависимости от значения ячейки. Для этого достаточно открыть вкладку шрифт (Шрифт) в диалоговом окне Формат ячеек (Формат ячейки), как мы это делали на шаге 5, и выберите нужный цвет шрифта.
Как установить постоянный цвет ячейки на основе ее текущего значения
После установки цвет заливки не изменится, независимо от того, как изменится содержимое ячейки в будущем.
Проблема: Вы хотите настроить цвет ячейки на основе ее текущего значения и хотите, чтобы цвет заливки оставался неизменным даже при изменении значения ячейки.
Решение: Найдите все ячейки с определенным значением (или значениями) с помощью инструмента Найти все (Найти все), а затем измените формат найденных ячеек с помощью диалогового окна Формат ячеек (формат ячейки).
Это одна из тех редких задач, для которой нет объяснения в файлах справки Excel, на форумах или в блогах и для которой нет прямого решения. И это понятно, поскольку данная задача нетипичная. И все же, если вам нужно изменить цвет заливки ячейки навсегда, то есть раз и навсегда (или пока вы не измените его вручную), выполните следующие действия.
Найдите и выберите все ячейки, соответствующие заданному условию
Здесь возможны несколько сценариев, в зависимости от того, какой тип значения вы ищете.
Например, если вы хотите окрасить ячейки с определенным значением, 50, 100 or 3.4 – затем на вкладке Главная (Главная) в разделе Редактирование (Редактирование) нажмите Найти Выбрать (Найти и выделить) > Найдите (Находить).
Введите желаемое значение и нажмите Найти все (Найти все).
Наконечник: В правой части диалогового окна Найти и заменить (Найти и заменить) есть кнопка Опции (Опции), нажав которую вы получите доступ к ряду расширенных настроек поиска, таких как С учетом регистра (с учетом регистра) и Соответствие всему содержимому ячейки (Вся ячейка). Вы можете использовать подстановочные знаки, такие как звездочка (*), чтобы соответствовать любой строке символов, или знак вопроса (?), чтобы соответствовать любому отдельному символу.
Что касается предыдущего примера, если нам нужно найти все цены на бензин из 3.7 в 3.799, то мы установим следующие критерии поиска:
Теперь нажмите на любой из найденных элементов внизу диалогового окна. Найти и заменить (Найти и заменить) и нажмите Ctrl +чтобы выделить все найденные записи. После этого нажмите кнопку Близко (Закрывать).
Вот как вы можете выбрать все ячейки с заданным значением (значениями), используя опцию Найти все (Найти все) в Excel.
Однако в действительности нам необходимо найти все цены на бензин, превышающие $ 3.7. К сожалению, инструмент Найти и заменить (Найти и заменить) не может нам в этом помочь.
Измените цвета заливки выбранных ячеек с помощью диалогового окна «Формат ячеек»
Теперь у вас выбраны все ячейки с заданным значением (или значениями), мы только что сделали это с помощью инструмента Найти и заменить (Найти и заменить). Все, что вам нужно сделать, это установить цвет заливки для выбранных ячеек.
Открыть диалоговое окно Формат ячеек (Формат ячейки) любым из 3 способов:
- прессование Ctrl + 1.
- нажав на любую выделенную ячейку правой кнопкой мыши и выбрав пункт из контекстного меню Формат ячеек (формат ячейки).
- таб Главная (Главная) > Клетки. Клетки. (ячейки) > Размер (Формат) > Формат ячеек (формат ячейки).
Далее настройте параметры форматирования по своему усмотрению. На этот раз мы установим оранжевый цвет заливки, просто для разнообразия 🙂
Если вы хотите изменить только цвет заливки, не затрагивая остальные параметры форматирования, вы можете просто нажать кнопку Цвет заливки (Цвет заливки) и выберите понравившийся цвет.
Вот результат наших изменений форматирования в Excel:
В отличие от предыдущего способа (с условным форматированием), заданный таким образом цвет заливки никогда не изменится сам без вашего ведома, как бы ни менялись значения.
Изменение цвета заливки для специальных ячеек (пустых, с ошибкой в формуле)
Как и в предыдущем примере, изменить цвет заливки конкретных ячеек можно двумя способами: динамически и статически.
Используйте формулу, чтобы изменить цвет заливки специальных ячеек в Excel
Цвет ячейки будет меняться автоматически в зависимости от значения ячейки.
Этот способ решения задачи вы, скорее всего, воспользуетесь в 99% случаев, то есть заполнение ячеек будет меняться в соответствии с указанным вами условием.
Например, возьмем снова таблицу цен на бензин, но на этот раз добавим еще пару штатов, а некоторые ячейки сделаем пустыми. Теперь посмотрим, как можно найти эти пустые ячейки и изменить цвет их заливки.
- На вкладке Дополнительно Главная (Главная) в разделе Стили (Стили) нажмите Условное форматирование (Условное форматирование) > Новые правила (Создать правило). Точно так же, как на втором этапе примера «Как динамически менять цвет ячейки в зависимости от ее значения».
- В диалоговом окне Новое правило форматирования (Создать правило форматирования) выберите вариант Используйте формулу, чтобы определить, какие ячейки форматировать (Используйте формулу, чтобы определить, какие ячейки нужно форматировать). Дальше в поле Формат значений, где эта формула истинна (Значения формата, для которых справедлива следующая формула) введите одну из формул:
- изменить заливку пустых ячеек
=ISBLANK()=ЕПУСТО() - изменить заливку ячеек, содержащих формулы, возвращающие ошибку
=ISERROR()=ЕОШИБКА()
Поскольку мы хотим изменить цвет пустых ячеек, нам понадобится первая функция. Введите его, затем поместите курсор между скобками и нажмите значок выбора диапазона в правой части строки (или введите нужный диапазон вручную):
=ISBLANK(B2:H12)=ЕПУСТО(B2:H12) - изменить заливку пустых ячеек
- Нажмите кнопку Размер (Формат), выберите на вкладке нужный цвет заливки Заполнять (Заливка), а затем нажмите кнопку OK. Подробные инструкции приведены в шаге 5 примера «Как динамически менять цвет ячейки в зависимости от ее значения». Пример настроенного вами условного форматирования будет выглядеть примерно так:
- Если вас устраивает цвет, нажмите OK. Вы увидите, как созданное правило будет немедленно применено к таблице.
Статически изменить цвет заливки специальных ячеек
После настройки заливка останется неизменной независимо от значения ячейки.
Если вы хотите установить постоянный цвет заливки для пустых ячеек или ячеек с формулами, содержащими ошибки, используйте этот метод:
- Выберите таблицу или диапазон и нажмите F5чтобы открыть диалог Перейти к (Прыжок), затем нажмите кнопку Особый (Выделять).
- В диалоговом окне Перейти к специальному (Выберите группу ячеек) отметьте опцию заготовки (Пустые ячейки), чтобы выбрать все пустые ячейки.Если вы хотите выделить ячейки, содержащие формулы с ошибками, установите флажок Формулы (формулы) > ошибки (Ошибки). Как вы можете видеть на рисунке выше, вам доступно множество других настроек.
- Наконец, измените заливку выбранных ячеек или установите любые другие параметры форматирования с помощью диалогового окна. Формат ячеек (Формат ячеек), как описано в разделе «Изменение заливки выбранных ячеек».
Если вы хотите выделить ячейки, содержащие формулы с ошибками, установите флажок Формулы (формулы) > ошибки (Ошибки). Как вы можете видеть на рисунке выше, вам доступно множество других настроек.Не забывайте, что сделанные таким образом настройки форматирования сохранятся даже при заполнении пустых ячеек значениями или исправлении ошибок в формулах. Трудно представить, что кому-то может понадобиться пойти этим путем, кроме как в целях эксперимента 🙂