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

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

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

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

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

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

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

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

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

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

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

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

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

Выполняя другие задачи по форматированию, вы также можете использовать правила форматирования:

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

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

• В качестве формата ячеек вы можете использовать как заранее подготовленные цветовые палитры, так и задать их на свой вкус, кликнув по пункту “Пользовательский формат”:

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

• Когда мы ввели все настройки отображения форматирования ячеек жмем кнопку “Ок”.

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

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

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

• Выделем диапазон ячеек, для которых мы хотим применить правило условного форматирования. Нажмем на пункт “Условное форматирование” в панели инструментов и в выпадающем списке выберем пункт “Новое правило”:

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

• Кликнем на кнопку “Формат” и зададим формат ячеек, как мы это делали в примере выше. Нажмите кнопку “ОК”. Условное форматирование по нашему собственному правилу готово:

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

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

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

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

Так как система по-умолчанию фиксирует указанную ссылку в знак “$”, это означает, что при протягивании этого формата на другие ячейки, они будут сравниваться с указанной в первый раз ячейкой.

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

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

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

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

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

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

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

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

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

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

Применив эти правила, наша таблица с данными температуры “подсвечена” правильными цветами, в соответствии с нашими условиями.

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

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

• Выделить левой клавишей мыши ячейку, правило условного форматирования которой вы хотите отредактировать.
• Перейдите в пункт меню панели инструментов “Условное форматирование” => “Управление правилами” => щелкните левой клавишей мыши правилу, которое вы хотите отредактировать и затем нажмите на кнопку “Изменить правило”:

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

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

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

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

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

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

• Выделим диапазон ячеек, с которого мы хотим удалить условное форматирование;
• Нажмем на пункт меню “Условное форматирование” на панели инструментов => кликнем по пункту “Удалить правила” и в раскрывающемся меню выберем метод удаления, наиболее подходящий для нашей ситуации:

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