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

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

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

Построение таблицы и формулы

Для корректной работы нам понадобится таблица, представим, что это таблица с нашими точками продаж.

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

СЕГОДНЯ();
СЛУЧМЕЖДУ();
МЕСЯЦ();
ЕСЛИ().

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

ЗАДАЧИ:

Заливать зелёным цветом ячейки в столбце "Дата оплаты", если дата в них равна сегодняшней дате.
Заливать оранжевым цветом ячейки в столбце "Дата поставки", если поставка выпадает на текущий месяц.
Найти в таблице всех сотрудников у которых оклад ниже среднего по организации.

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

Задача №1

Решение первой задачи самое простое. Нам потребуется:

Выделить даты в столбце С;
Нажать кнопку "Условное форматирование";
Выбрать пункт "Управление правилами".

После него появится окно управления правилами условного форматирования.

Нажимаем "Создать правило", далее "Использовать формулу для определения форматируемых ячеек".

Теперь определим формулу для применения условного форматирования - =ЕСЛИ(C2=СЕГОДНЯ();1;0), то есть если в ячейке С2 содержится дата равная сегодняшней дате, возвращаем 1 (истина), если нет - возвращаем 0 (ложь), также не забываем в нижнем правом углу нажать "Формат", перейти на вкладку "Заливка" и указать цвет заливки. Как помним - лучше брать не яркие цвета.

Подтверждаем нажатием "ОК" два раза. Таблица принимает следующий вид. Задача решена.

Задача №2

В случае со второй задачей будем использовать функцию МЕСЯЦ(), она возвращает номер месяца из ячейки, помним, что Excel специфически воспринимает даты.
Так же - выделяем диапазон с D2:D22 и повторяем действия по созданию условного форматирования. Только теперь в формуле применим следующую конструкцию - =ЕСЛИ(МЕСЯЦ(D2)=10;1;0), то есть если в ячейке D2 месяц будет совпадать с октябрём возвращаем 1 (истина), если нет - 0 (ложь), не забываем указать жёлтый цвет заливки ячеек.

Таблица принимает следующий вид.

Задача №3

Для решения третьей задачи сначала узнаем средний оклад. Это можно сделать без формулы, просто выделив все ячейки с окладом. Видим, 4 029,76 ₽. Округлим до 4030. Кстати, если щёлкнуть на это число в Excel - оно скопируется в буфер обмена вместе с валютой.

Выделять будем столбец с фамилиями, проходим по предыдущим пунктам. Формула будет следующей- =ЕСЛИ(G2<4030;1;0), то есть если в ячейках столбца с окладом будет число меньше 4030 ставим 1 (истина), если нет - 0 (ложь). Не забываем про "Формат" и заливку.