В предыдущем "кейсе" мы затронули вопрос формирования перечня дат необходимого месяца, а также научились с помощью Условного форматирования выделять ячейки, даты которых выпадают на выходные дни (субботу и воскресенье). Сегодня мы продолжим данную тему, сделав возможность учитывать праздничные дни, а также рабочие, которые переносятся на выходные. Если кто-то пропустил - ознакомиться с первой частью этого кейса можно тут.

Создадим в нашей книге новый лист, под названием ПРАЗДНИКИ, на нем организуем  «умную таблицу», я назову её ВЫХОДНЫЕ (сменить имя таблицы можно на вкладке «Конструктор», когда выделена любая ячейка таблицы).

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

Присвоим имена нашим столбцам (без заголовков), чтобы их можно было использовать в правилах Условного форматирования.

Для этого нужно выделить ячейки, к примеру A1:A2, на вкладке Формулы выбрать команду Задать Имя, и в выпадающем окошке ввести имя для диапазона (я назвал его «Праздник», второму диапазону B1:B2 я присвоил имя «Перенос»).

Вызывать именованные диапазоны в формулы можно с помощью клавиши F3 (или написать имя вручную).

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

Теперь необходимо дополнить формулу для условного форматирования, чтобы эти даты учитывались при определении выходных дней.

1. Выделяем диапазон ячеек, для которых нужно применять форматирование.
2. На вкладке Главная, выбираем Условное форматирование и в выпадающем списке пункт Управление правилами
3. В появившемся окне выбираем правило и нажимаем на кнопку Изменить правило.4, Меняем нашу формулу.

=ИЛИ(И(ДЕНЬНЕД(G1;2)>5;СЧЁТЕСЛИ(Перенос;G1)=0);СЧЁТЕСЛИ(Праздник;G1)>0)

=OR(AND(WEEKDAY(G1;2)>5;SUMIF(Перенос;G1)=0);SUMIF(Праздник;G1)>0)

Т.е. у нас должно выполняться несколько условий:

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

-чтобы дата была в списке праздничных дней, независимо от того на какой день недели она попадает.

Так как эти условия не могут выполняться одновременно – нужно использовать функцию ИЛИ (OR).

Нажав на «ОК» (2 раза) мы увидим, что всё работает так, как мы задумывали и теперь эти даты тоже участвуют в определении выходных и рабочих дней.

Для тех кто хочет комплексно изучить Excel, понять его суть, логику и принципы работы курс "Excel бизнес-анализ и прогнозирование" 
Кроме 27 часов материала, 12 домашних заданий и курсового проекта в чате онлайн поддержки вы сможете задавать свои вопросы и получать подобные подсказки в решение Ваших трудностей по работе с Excel.
Детально ознакомиться с программой курса, наполнением пакетов и ценами можно тут.

Автор статьи тренер DATAbi Михаил Беленчук

Узнайте больше про полную программу "EXCEL: бизнес-анализ и прогнозирование"