Как подсветить строки в зависимости от значения в них Excel

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

Допустим, у нас есть такая табличка. Я подсветил те строки, в которых продавец — Боб.

Сейчас я продемонстрирую вам, как можно сделать также и не только.

Итак, давайте начнём!

Подсвечиваем строки с нужным нам текстом

Допустим, у нас есть такая табличка:

Нам нужно подсветить все строки, в которых продавцом является Боб.

Как это сделать?

Пошаговая инструкция:

• Выделите вашу табличку;

• Щелкните на «Главная»;

• Щелкните на «Условное форматирование»;

• Щелкните на «Создать правило…»;

• В открывшемся окне, выберите указанную на картинке опцию;

• В поле «Форматировать значения, для которых следующая формула является истинной» введите =$C2=»Bob»;

• А теперь настроим формат, щелкните на «Формат…»;

• И, в открывшемся окне, формат, которым нужно выделить ячейки;

• Подтвердите.

Итак, у нас получилось:

Как это работает?

Условное форматирование — функция, которая проверяет ячейки на наличие заданных нами параметров. В этом примере, мы задали следующее — выделять все строки, в ячейках которых содержится слово Боб. Этим и занимается функция.

Итак, строки выделены.

Подсвечиваем строки с нужным нам числами

Итак, как подсветить строку с определенным текстовым значением мы разобрались.

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

Допустим, у нас есть та же табличка. Только теперь нам нужно выделить те строки, в которых количество товара больше или равно 15-ти.

Как это сделать?

Пошаговая инструкция:

• Выделите вашу табличку;

• Щелкните на «Главная» -> «Условное форматирование» -> «Создать правило…»;

• В открывшемся окне, из списка выберите последнюю опцию;

• В поле для описания функции введите =$D2>=15;

• Щелкните на «Формат…» и укажите цвет подсветки;

• Подтвердите.

Взглянем на результат:

Как видно, все получилось.

Точно также, кстати, и с датами.

Например, вам нужно выделить строки где дата — позже первого января 2021 года. Формула будет такой:

=$A2>ДАТА(2021,01,01)

Подсвечиваем строки с одновременно несколькими нужными нам значениями

Те варианты которые мы рассмотрели ранее — лишь основы использования функции «Условное форматирование».

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

Итак, давайте разбираться. Допустим, нам нужно выделить все строки в которых продавец Боб и количество товара больше 10-ти.

Как это сделать?

Пошаговая инструкция:

• Выделите вашу табличку;

• Щелкните на «Главная» -> «Условное форматирование» -> «Создать правило…»;

• В открывшемся окне, из списка выберите последнюю опцию;

• В поле для описания функции введите =И($C2=»Боб»;$D2>10)

• Укажите формат;

• Подтвердите.

В этом варианте выделяются те строки, которые обладают всеми указанными критериями.

Но если вам необходимо выделить те строки, которые обладают любым из указанных критериев(даже одним) используйте такую формулу:

=ИЛИ($C2="Bob";$D2>15)

Подсвечиваем строки разным форматом в зависимости от значения

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

Допустим, нам нужно выделить строки в которых кол-во более 20-ти — зелененьким, а если кол-во менее 20-ти, но более 15-ти — оранжевым.

Как это сделать?

Пошаговая инструкция:

• Выделите вашу табличку;

• Щелкните на «Главная» -> «Условное форматирование» -> «Создать правило…»;

• В открывшемся окне, из списка выберите последнюю опцию;

• В поле для описания функции введите =$D2>15

• Установите оранжевый цвет в опции «Формат»;

• Подтвердите.
• В опции «Управление правилами» функции «Условное форматирование» щелкните «Создать правило»;

• И делаем еще одну проверку, только уже с формулой =$D2>20;

• В опции формат укажите зеленый цвет;

• Подтвердите.

Итак, вот результат:

Понимание порядка правил:

Условное форматирование проверяет правила по порядку. Из-за этого очень важно, в каком порядке они проверяются. Вы можете узнать это в диспетчере правил (открывается в «Условное форматирование» -> «Управление правилами…»).

У нас все правильно, но если бы правила проверялись наоборот, возникли бы ошибки. Из-за чего?

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

Порядок проверки можно менять с помощью стрелки, идущей вниз. Как на картинке:

Подсвечиваем строки с пустыми ячейками

Такие ситуации тоже часто имеют место быть. Как выделить строки с пустыми ячейками?

Пошаговая инструкция:

• Выделите вашу табличку;

• Щелкните на «Главная» -> «Условное форматирование» -> «Создать правило…»;

• В открывшемся окне, из списка выберите последнюю опцию;

• В поле для описания функции введите =СЧЁТЕСЛИ($A2:$F2;»»)>0

• Установите оранжевый цвет в опции «Формат»;

• Подтвердите.

Вот результат:

Подсвечивание разных строк в зависимости от вашего выбора в списке

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

Вот пример такого подсвечивания:

Как это сделать?

Пошаговая инструкция:

• Для начала создайте этот список;

• Выделите вашу табличку;

• Щелкните на «Главная» -> «Условное форматирование» -> «Создать правило…»;

• В открывшемся окне, из списка выберите последнюю опцию;

• В поле для описания функции введите =$E2=$A$2

• Установите нужный формат;

• Подтвердите.

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