Гайд по использованию условного форматирования в Excel

Что такое «Условное форматирование» и для чего оно нужно?

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

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

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

Студенты сдают тест по теме «Рыночная экономика», оценка за тест ставится в формате зачет/незачет. При этом «зачет» ставится, если набрано не менее 80 баллов. Необходимо выделить оранжевым цветом строки со студентами, которые провалили тестирование.

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

Правила выделения ячеек ​

При нажатии на иконку «Условное форматирование» мы видим выпадающий список, первым в нём находится раздел «Правила выделения ячеек». С помощью этих правил можно выделить числовые значения (больше, меньше, между, равно), текстовые (текст содержит) или даты. Также правило даёт возможности найти повторяющиеся значения (все значения, которые встречаются в указанном диапазоне больше одного раза, но это правило не будет выделять разные значения разными цветами).

В данном примере у нас есть числовое значение – количество баллов. Давайте выделим цветом те ячейки, где количество баллов не дотягивает до зачета.

Для этого выделяем диапазон значений, для которого будем применять правило, и выбираем «Правила выделения ячеек» – «Меньше».

После этого видим открывшееся окошко для ввода данных. Вводим количество баллов, необходимое для зачета – 80.  

Теперь осталось выбрать формат. 

Можно выбрать из предложенного списка, а можно нажать на «Пользовательский формат» и задать его самостоятельно. Для этого в открывшемся окне нужно поменять параметры, в нашем случае – выбрать оранжевую заливку. При необходимости здесь же можно изменить формат текста, шрифт (цвет, начертание и т.д.), границы (цвет, тип линии).  

Нажимаем «Ок» и видим результат: ячейки, значение которых было меньше 80, выделены оранжевым цветом.  

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

В открывшемся окошке вводим текст, который нам необходимо выделить – слово «незачет» и задаем нужный формат точно так же, как делали ранее.  

В итоге мы имеем подсвеченные ячейки с нужной отметкой.

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

Для того, чтобы выделить строку целиком, зайдём в раздел «Управление правилами».

В открывшемся окне выберемся из выпадающего списка «Этот лист» (чтобы увидеть, какие правила у нас применены на листе, а не только к ячейке, на которой в данный момент стоит выделение), и нажмём кнопку «Создать правило».

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

Форматировать все ячейки на основании их значений ​

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

Двухцветная шкала – от минимального значения в выделенном диапазоне к максимальному. Цветовую схему шкалы при необходимости можно изменить.

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

  Гистограмма тоже вполне наглядна. Берет максимальное значение диапазона за 100% и пропорционально заполняет ячейку цветом (цвет также можно изменить).

Наборы значков – тоже интересное решение. Рядом с текстом в ячейке появляется иконка (или вместо текста если поставить галочку в поле «Показать только значок»). Стили значков можно поменять, а также задать для них параметры (какой значок за какой интервал значений отвечает).  

Главное не забывайте указывайте диапазон, для которого данное правило будет применяться (это касается любого правила).  

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

Примечание: о том, как правильно и продуктивно работать с правилами фильтрации, читайте в нашей статье «Правила фильтрации в MS Excel».

Форматировать только ячейки, которые содержат

Здесь мы не будем подробно останавливаться, так как это те же самые правила для числовых, которые мы рассматривали вначале: больше, меньше, между, равно и т.д.  

Форматировать только первые или последние значения ​

Это правило не так часто применяется, но если Вам нужно выделить, например, 5 ячеек с наивысшим результатом (значения, которые относятся к первым 5), или, наоборот, 10 ячеек с наименьшим результатом (значения, которые относятся к последним 10), то используйте его.  

Форматировать только значения, которые находятся выше или ниже среднего ​

Аналогично, выбираем нужный параметр: выше, ниже, равно или ниже и т.п. Среднее значение для диапазона правило определит само, нам нужно только задать необходимый формат (и не забыть про диапазон, к которому будет применяться условие).  

Форматировать только уникальные или повторяющиеся значения ​

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

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

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

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

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

Так как нам необходимо форматировать всю таблицу, т.е. использовать в формуле весь столбец D, перед строкой символ $ убираем (перед столбцом убирать не нужно). В итоге остается $D5.  

И теперь мы видим результат: оранжевым цветом выделены строки со студентами, у которых оценка за тест – незачет. Задача выполнена!  

Как изменить или удалить правило? ​

На одном листе может применяться более одного правила на один и тот же, либо на разные диапазоны.

По кнопке «Изменить правило» откроется меню, в котором можно отредактировать формулу, изменить параметры форматирования и т.д.

Кнопка «Удалить правило» удалит то, на которым в данный момент стоит выделение.

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

Галочка «Остановить, если истина» означает, что при выполнении условия этого правила, другие правила к этим ячейкам применяться не будут.  

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