Условное форматирование в Excel

В этом руководстве мы рассмотрим основы применения условного форматирования в Excel.

С его помощью мы можем выделять значения таблицы цветов по заданным критериям, искать дубликаты и даже графически «выделять» важную информацию.

Основы условного форматирования в Excel

Используя условное форматирование, мы можем:

• раскрасьте значения цветом
• изменить характер
• устанавливает размер границы

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

Где находится условное форматирование в Эксель?

Кнопка «Условное форматирование» находится на панели инструментов на вкладке «Главная”:

Как сделать условное форматирование в Excel?

При применении условного форматирования системе необходимо установить две настройки:

• Какие ячейки вы хотите отформатировать;
• При каких условиях будет назначен формат.

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

• В таблице данных выберите диапазон, для которого мы хотим применить цветовое выделение:

• Переходим на вкладку «Главная» панели инструментов и щелкаем по элементу «Условное форматирование». В раскрывающемся списке вы увидите несколько типов форматов на выбор:
  • Правила присвоения
  • Правила выбора первого и последнего значения
  • Гистограммы
  • Цветовые шкалы
  • Набор иконок
• В нашем примере мы хотим выделить данные с отрицательным значением. Для этого выберите тип «Правила выбора ячеек» => «Минус”:

Кроме того, доступны следующие условия:

1. Значения больше или равны любому значению;
2. Выделите текст, содержащий определенные буквы или слова;
3. Выделите дубликаты;
4. Выделите конкретные даты.

• Во всплывающем окне в поле «Формат ячеек, которые имеют МИНУС» мы укажем значение «0», так как мы должны выделить отрицательные значения цветом. В раскрывающемся списке справа выберите формат, соответствующий условиям:

• Чтобы назначить формат, вы можете использовать предварительно настроенные цветовые палитры, а также создать свою собственную палитру. Для этого кликните по пункту:

• Во всплывающем окне формата укажите:
  • цвет заливки
  • цвет шрифта
  • шрифт
  • границы ячеек

• После завершения настроек нажмите кнопку «ОК”.

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

Как создать правило

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

• Выберем диапазон данных. Щелкните элемент «Условное форматирование» на панели инструментов. В раскрывающемся списке выберите пункт «Новое правило”:

• Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подходит тип «Форматировать только те ячейки, которые содержат». Затем мы устанавливаем условие для выбора данных, значения которых больше «57», но меньше «59”:

• Нажмите кнопку «Форматировать» и установите формат, как в примере выше. Нажмите кнопку «ОК”:

Условное форматирование по значению другой ячейки

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

Чтобы создать условие для значения другой ячейки, сделайте следующее:

• Выберите первую ячейку для назначения правила. Щелкните элемент «Условное форматирование» на панели инструментов. Выбираем условие «Меньше”.
• Во всплывающем окне укажите ссылку на ячейку, с которой будет сравниваться эта ячейка. Выбор формата. Щелкните кнопку «ОК”.

• Снова выберите ячейку, которой мы присвоили формат, с помощью левой кнопки мыши. Щелкните элемент «Условное форматирование». В раскрывающемся меню выберите «Управление правилами» => нажмите кнопку «Изменить правило”:

• В левом поле всплывающего окна «удалите» ссылку знаком «$». Нажмите кнопку «ОК», затем нажмите кнопку «Применить”.

• Теперь нам нужно назначить настраиваемый формат остальным ячейкам таблицы. Для этого выделите ячейку с заданным форматом, затем в левом верхнем углу панели инструментов нажмите на «ролик» и назначьте формат остальным ячейкам:

На скриншоте ниже данные выделены цветом, где курс стал ниже, чем в предыдущем периоде:

Как применить несколько правил условного форматирования к одной ячейке

к ячейке можно применить несколько правил.

Например, в таблице с прогнозом погоды мы хотим раскрасить показатели температуры в разные цвета. Условия подсветки: при температуре выше 10 градусов — зеленым, выше 20 градусов — желтым, выше 30 градусов — красным.

Чтобы применить к ячейке несколько условий, выполните следующие действия:

• Выберите диапазон с данными, к которым вы хотите применить условное форматирование => щелкните элемент «Условное форматирование» на панели инструментов => выберите условие выбора «Другое…» и укажите первое условие (если больше 10, отсюда зеленая заливка). Повторяем одинаковые действия для каждого из условий (более 20 и более 30). Хотя мы применили три правила, данные в таблице окрашены в зеленый цвет:

• Щелкните любую ячейку с заданным форматированием. Затем снова нажмите «Условное форматирование» и перейдите в раздел «Управление правилами». Во всплывающем окне распределите правила от наибольшего к наименьшему и перед первыми двумя установите флажок «Остановить, если истинно». Этот элемент позволяет не применять к ячейке остальные правила, если первое совпадает. Затем нажмите кнопку «Применить» и «ОК”:

Когда они применяются, наша температурная карта «подсвечивается» правильными цветами в зависимости от наших условий.

Как редактировать правило условного форматирования

Чтобы изменить назначенное правило, сделайте следующее:

• Выделите левой кнопкой мыши ячейку, правило которой вы хотите изменить.
• Перейдите в пункт меню панели инструментов «Условное форматирование». Затем в пункте «Управление правилами». Щелкните левой кнопкой мыши правило, которое хотите изменить. Нажмите кнопку «Изменить правило”:

 

• После внесения изменений нажмите кнопку «ОК”.

Как копировать правило условного форматирования

Чтобы скопировать формат в другие ячейки, сделайте следующее:

• Выберите диапазон данных с примененным условным форматированием. Щелкните на панели инструментов пункт «Формат для образца”.
• Левой кнопкой мыши выбираем диапазон, к которому мы хотим применить скопированные правила форматирования:

 

Как удалить условное форматирование

Чтобы удалить формат, сделайте следующее:

• Выбрать ячейки;
• Щелкните пункт меню «Условное форматирование» на панели инструментов. Щелкните по пункту «Удалить правила». В раскрывающемся меню выберите способ удаления: