Формула Excel: динамическая календарная сетка -

Содержание

Резюме

Вы можете настроить динамическую календарную сетку на листе Excel с помощью ряда формул, как описано в этой статье. В показанном примере формула B6:

=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

где «начало» - именованный диапазон K5 и содержит дату 1 сентября 2018 г.

Объяснение

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

При такой схеме сетки основная проблема заключается в вычислении даты в первой ячейке календаря (B6). Это делается с помощью этой формулы:

=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)

Эта формула вычисляет воскресенье, предшествующее первому дню месяца, с помощью функции ВЫБОР для «отката» на нужное количество дней до предыдущего воскресенья. CHOOSE отлично работает в этой ситуации, потому что допускает произвольные значения для каждого дня недели. Мы используем эту функцию для отката нулевых дней, когда первый день месяца - воскресенье. Подробнее об этой проблеме можно прочитать здесь.

Когда первый день установлен в B6, другие формулы в сетке просто увеличивают предыдущую дату на единицу, начиная с формулы в C6:

=IF(B6"",B6,$H5)+1

Эта формула проверяет значение в ячейке слева. Если значение не найдено, он извлекает значение из столбца H в строке выше. Примечание. $ H5 - это смешанная ссылка для блокировки столбца, поскольку формула копируется по всей сетке. Эта же формула используется во всех ячейках, кроме B6.

Правила условного форматирования

В календаре используются формулы условного форматирования для изменения форматирования, чтобы затемнить предыдущие и будущие месяцы и выделить текущий день. Оба правила применяются ко всей сетке. Формула для предыдущего и следующего месяцев:

=MONTH(B6)MONTH(start)

На текущий день формула:

=B6=TODAY()

Дополнительные сведения см .: Условное форматирование с формулами (10 примеров)

Заголовок календаря

Название календаря - месяц и год - вычисляются по следующей формуле в ячейке B4:

=start

Отформатирован в произвольном числовом формате «мммм гггг». Чтобы центрировать заголовок над календарем, для диапазона B4: H4 горизонтальное выравнивание установлено на «центр по выделению». Это лучший вариант, чем объединение ячеек, поскольку он не меняет структуру сетки на листе.

Вечный календарь с текущей датой

Чтобы создать календарь, который автоматически обновляется в зависимости от текущей даты, вы можете использовать такую формулу в K5:

=EOMONTH(TODAY(),-1)+1

Эта формула получает текущую дату с помощью функции СЕГОДНЯ, а затем получает первый день текущего месяца с помощью функции EOMONTH. Замените СЕГОДНЯ () любой датой, чтобы построить календарь в другом месяце. Подробнее о том, как работает EOMONTH, можно здесь.