Как быстро создать календарь в Excel используя одну формулу

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

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



Как получить дату по номеру дня недели в Excel

Ниже на рисунке представлена динамическая таблица с автозаполнением ячеек датами по условию. Ее формулы вычисляют даты номера дня недели в каждом месяце. При использовании этой таблицы следует ввести необходимый: год, месяц и номер появления дня недели в месяце (например, третий понедельник месяца, или вторая пятница и т.п.). В данном примере ячейка B2 показывает, что необходимо найти даты для вторых всех дней от понедельника и до воскресенья. Например, второй понедельник третьего месяца или вторая пятница января.

Формула для определения даты очереди дня недели в месяце:

Читайте также: Как узнать последний день недели месяца в Excel.

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

Чтобы воспользоваться этой таблице заполните значениями 2 столбца в диапазоне A3:B14 – года и номера месяцев, которые Вас интересуют. Далее смените число в ячейке B2 на желаемый порядковый номер появления дня недели в месяце. Например, если интересует дата второго вторника для каждого месяца введите значение 2 и посмотрите на значения столбца D3:D14 – даты вторых вторников. Если нужны даты для третьих четвергов тогда введите число 3 в ячейку B2 и смотрите на значения в столбце F3:F14 – там будут все третьи четверги по каждому месяцу.

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

=DATE($A3,$B3,1)+C3-WEEKDAY(DATE($A3,$B3,1),1)+($B$1-(C3>=WEEKDAY(DATE($A3,$B3,1),1)))*7

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

Как использовать формулу для выборки дат порядкового номера дней недели

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

Нам всего лишь потребуется создать табличку из номеров дней недели (1-7) в заголовках столбцов и порядковых номеров недель месяца (1-6) в заголовках строк. А дальше диапазон ячеек 7x6 следует заполнить всего лишь одной формулой и календарь готов:

=IF(WEEKDAY(DATE($A$2,$C$1,1),1)>DAY(DATE($A$2,$C$1,D$2)),DAY(DATE($A$2,$C$1,1)+D$2-WEEKDAY(DATE($A$2,$C$1,1),1)+(($C4-1)-(D$2>=WEEKDAY(DATE($A$2,$C$1,1),1)))*7),DAY(DATE($A$2,$C$1,1)+D$2-WEEKDAY(DATE($A$2,$C$1,1),1)+($C4-(D$2>=WEEKDAY(DATE($A$2,$C$1,1),1)))*7))

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

Также нам потребуется использовать 2 условных форматирования для двух диапазонов: верхний и нижний. Верхний диапазон охватывает первую неделю месяца и применяется простая формула. Числа меньше, чем 7 дней в этом диапазоне должны быть подсвечены другим цветом:

=D4>7

Таким образом мы указываем что это дни из предыдущего месяца, как обычно показано в календарях.

Второй нижний диапазон для условного форматирования охватывает 2 последних из возможных недель в месяце и к нему применяется более сложная формула:

=D8<DAY(EOMONTH(DATE($A$2,$C$1,1),0))-14

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

Шаблон динамического календаря в Excel из одной формулы

В результате мы создали динамический календарь в Excel. При изменении года в ячейке A2 или месяца С1, календарь будет автоматически обновляться и заполнять календарную сетку соответственными числами дней:

Скачать шаблон динамического календаря используя одну формулу Excel

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