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

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

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

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

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

• закрашивать значения цветом
• менять шрифт
• задавать формат границ

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

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

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

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

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

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

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

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

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

Также, доступны следующие условия:

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

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

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

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

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

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

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

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

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

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

• Кликнем на кнопку «Формат» и зададим формат, как мы это делали в примере выше. Нажмите кнопку «ОК»:

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

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

Для создания условия по значению другой ячейки выполним следующие шаги:

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

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

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

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

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

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

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

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

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

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

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

Применив их, наша таблица с данными температуры «подсвечена» корректными цветами, в соответствии с нашими условиями.

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

Для редактирования присвоенного правила выполните следующие шаги:

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

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

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

Для копирования формата на другие ячейки выполним следующие действия:

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

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

Для удаления формата проделайте следующие действия:

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