Довольно часто в кабинет поддержки курса  "Excel бизнес-анализ и прогнозирование" приходят запросы на помощь в создании графиков работы, автоматических заполнений табелей учёта рабочего времени и т.п….

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

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

Первую дату месяца можем «собрать» с помощью функции ДАТА (DATE), она нам позволяет нам создать дату из номера года, месяца и дня.

Можем написать формулу примерно так:

=ДАТА(B2;B1;1)    =DATE(B2;B1;1)

На месте аргумента «день» мы поставили 1, чтобы получить первое число месяца.

Теперь найдем последнюю дату месяца. Для этого мы можем использовать функцию КОНМЕСЯЦА (EOMONTH)

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

Что касается аргумента «Число_месяцев» - тут нам нужно поставить 0, потому что нам не нужно сдвигаться на следующие/предыдущие месяцы, а остаться в текущем.

=КОНМЕСЯЦА(E1;0)     =EOMONTH(E1;0)

Если в результате Вы увидели не дату а пятизначное число – просто поменяйте формат ячейки на «Дата»…

Теперь можем приступить к формированию диапазона дат.

Первая дата – делаем ссылку на ячейку с датой начала месяца.

=E1

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

Формула может быть следующей:

=ЕСЛИ(G1+1<=$E$2;G1+1;"")  =IF(G1+1<=$E$2;G1+1;"")

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

Протянув формулу вниз (на 30 ячеек), мы получили правильный результат…

и теперь при смене месяца и года (ячейки B1, B2) будут формироваться даты соответствующего периода.

Правда, если поставить 2 месяц, то в последних ячейках будут ошибки (в феврале меньше дней и формула ссылается на ячейку с пустым значением), поэтому рекомендую дополнить формулу функцией ЕСЛИОШИБКА (IFERROR) – в первом её аргументе размещаем выражение и если оно будет выдавать ячейку, функция подставит значение второго аргумента.

=ЕСЛИОШИБКА(ЕСЛИ(G1+1<=$E$2;G1+1;"");"")          =IFERROR(IF(G1+1<=$E$2;G1+1;"");"")

Следующим шагом сделаем организацию автоматического выделения цветом выходных дней. Сделаем мы это с помощью инструмента Условное форматирование (Conditional formatting), который находится на вкладке Главная (Home).

В нём есть готовые наборы правил, но они нам не подходят – будем писать своё правило.

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

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

=ДЕНЬНЕД(G1;2)>5       =WEEKDAY((G1;2)>5

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

Обратите внимание, что в формуле мы ячейки не фиксировали, потому что в Условном форматировании она будет автоматически скопирована на остальные (выделенные) ячейки, а если зафиксировать ячейку G1, то правило будет проверять значения только в ней и применять на остальные вне зависимости от их значений.

Также в функции ДЕНЬНЕД (WEEKDAY) мы указали второй (необязательный) аргумент – 2, чтобы дни недели нумеровались в привычном для нас порядке – с понедельника по воскресенье, потому что по умолчанию идёт нумерация с воскресенья по субботу.

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

Но это ещё не всё, ведь не только суббота и воскресенье могут быть выходными, более того некоторые субботы могут быть рабочими – это нам тоже нужно учесть. Это и будет темой нашего следующего "кейса".

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

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

 

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"