Последовательность дат и календарь в Excel

В предыдущей статье мы рассмотрели применение новой функции динамического массива ПОСЛЕД для генерации числовой последовательности. Поскольку даты в Excel хранятся в виде чисел, мы можем создать последовательность дат и предложить вам календарь. 

Вам необходимо лишь правильно настроить параметры, как показано в следующих примерах.

Простая последовательность дат в Excel

Чтобы создать последовательность дат в Excel, используйте аргументы функции ПОСЛЕД:

ПОСЛЕД(строки; [столбцы]; [начало]; [шаг])

• Строки: количество строк, заполняемых датами.
• Столбцы: количество столбцов для заполнения датами.
• Начало: начальная дата в формате, понятном Excel. Чтобы избежать ошибок, используйте функцию ДАТА, например, ДАТА(2024; 6; 1).
• Шаг: на сколько изменяется каждая последующая дата в последовательности.

Для создания списка из 10 дат, начиная с 1 июня 2024 года с шагом в 1 день, используйте формулу:

=ПОСЛЕД(10; 1; ДАТА(2024;6;1); 1)

Чтобы сделать формулу более гибкой, можно ввести количество дат (B1), начальную дату (B2) и шаг (B3) в заранее определенные ячейки и ссылаться на них в формуле. Поскольку последовательность дат генерируется вертикально, количество столбцов (1) указывается непосредственно в формуле:

=ПОСЛЕД(B1; 1; B2; B3)

Введите формулу в верхнюю ячейку (например, A6), нажмите Enter, и результаты автоматически появятся в нужных ячейках.

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

Последовательность рабочих дней

Чтобы создать последовательность рабочих дней, используйте функцию РАБДЕНЬ вместе с ПОСЛЕД:

=РАБДЕНЬ(нач_дата -1; ПОСЛЕД(количество_дней))

Поскольку РАБДЕНЬ добавляет к начальной дате указанное количество дней, мы вычитаем 1, чтобы начальная дата включалась в последовательность дат.

Например, для создания календаря рабочих дней, начиная с даты в B2, используйте формулу:

=РАБДЕНЬ(B2-1; ПОСЛЕД(B1))

Где B1 — длина последовательности.

Советы и примечания

• Если начальная дата приходится на выходной, список дат начнется со следующего рабочего дня.
• Функция РАБДЕНЬ считает субботу и воскресенье выходными. Чтобы учитывать и праздничные дни, используйте функцию ЧИСТРАБДНИ.МЕЖД.

Как создать последовательность месяцев

Чтобы сделать в Excel календарь дат с шагом в один месяц используйте формулу:

=ДАТА(год; ПОСЛЕД(12); день)

Укажите нужный год в первом аргументе функции ДАТА и день в третьем аргументе. В качестве второго аргумента функция ПОСЛЕД возвращает числа от 1 до 12. Эта комбинация создает последовательность  дат.

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

• ммм — короткая форма (например, янв, фев, мар).
• мmmm — полная форма (например, январь, февраль, март).

Последовательность дат с шагом в месяц

Чтобы создать последовательность дат в Excel с увеличением на один месяц и начальной датой, используйте ДАТАМЕС вместе с ПОСЛЕД:

=ДАТАМЕС(начальная_дата; ПОСЛЕД(12; 1; 0))

Функция ДАТАМЕС возвращает дату через указанное количество месяцев от начальной даты, а ПОСЛЕД создает массив чисел, чтобы перемещаться с шагом в один месяц.

Если начальная дата указана в B1, формула выглядит так:

=ДАТАМЕС(B1; ПОСЛЕД(12; 1; 0))

Не забудьте применить соответствующий формат даты после заполнения формулы.

Как создать последовательность лет

Для создания календаря лет в Excel используйте формулу:

=ДАТА(ПОСЛЕД(n; 1; ГОД(начальная_дата)); МЕСЯЦ(начальная_дата); ДЕНЬ(начальная_дата))

Где n — количество дат, которые нужно создать. Эта формула создает даты, начиная с года начальной даты и увеличивая на один год для каждого шага.

Например, если начальная дата в B1, следующая формула создаст последовательность из 10 дат с шагом в один год:

=ДАТА(ПОСЛЕД(10; 1; ГОД(B1)); МЕСЯЦ(B1); ДЕНЬ(B1))

После применения формата даты результаты будут отображаться корректно.

Как создать временную последовательность

Время в Excel хранится как дробная часть дня, поэтому функция ПОСЛЕД может работать и со временем. Формула выглядит так:

=ПОСЛЕД(строки; столбцы; начало; шаг_времени)

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

С разницей в 30 минут:

=ПОСЛЕД(10; 1; B1; 1/48)

С разницей в 1 час:

=ПОСЛЕД(10; 1; B1; 1/24)

С разницей в 2 часа:

=ПОСЛЕД(10; 1; B1; 1/12)

Чтобы не рассчитывать шаг вручную, используйте функцию ВРЕМЯ:

=ПОСЛЕД(строки; столбцы; начало; ВРЕМЯ(часы; минуты; секунды))

Календарь месяца в Excel при помощи одной формулы

Создайте ежемесячный календарь с функциями ПОСЛЕД, ДАТАЗНАЧ и ДЕНЬНЕД.

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

=ПОСЛЕД(6;7;ДАТАЗНАЧ("1/"&B2&"/"&B1)-ДЕНЬНЕД(ДАТАЗНАЧ("1/"&B2&"/"&B1);2)+1;1)

Форматируйте результаты как даты для получения правильного календаря.

Последовательность дней недели

Создайте в Excel последовательность дат, соответствующую каждому понедельнику. В ячейку B2 запишите первый понедельник, с которого вы хотите начать отсчет.

Формула последовательности дат из 10 понедельников будет выглядеть так:

=B2+ПОСЛЕД(10;;0;7)

Для последовательности дат без воскресенья также начинаем в B2 с первого понедельника:

=B2+ПОСЛЕД(10;1;1;1)+ОКРВВЕРХ(ПОСЛЕД(10;1;1;1)/6;1)-2

Для последовательности дней недели понедельник, среда, пятница начать нужно с воскресенья:

=B2+ПОСЛЕД(B1;1;1;2)-ОКРВВЕРХ(ПОСЛЕД(B1;1;0;-3)/3;3)/3

Для вторника, четверга, субботы начальной датой также должно быть воскресенье. И вот формула календаря из 3 дней недели:

=B2+ПОСЛЕД(10;1;2;2)-ОКРВВЕРХ(ПОСЛЕД(10;1;0;-3)/3;3)/3

Календарь определённых дней недели

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

=НАИМЕНЬШИЙ(ФИЛЬТР(ПОСЛЕД(52; 1; $B$1; 7); (МЕСЯЦ(ПОСЛЕД(52; 1; $B$1; 7)) = СТРОКА(A1))); 3)

Cкопируйте эту формулу вниз по столбцу.

В ячейке B2 укажите первый четверг года, в котором вы хотите создать последовательность дат.

Число 3 в формуле указывает, какой по счету четверг (или другой день недели) в месяце вы хотите получить.

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

Последовательность временных интервалов

Если вам нужно создать список временных интервалов (например, еженедельных), далее приведен пример решения этой задачи. 

Мы создаем две последовательности дат: одна для понедельников и другая для воскресений. 

Для простоты начальная дата установлена как первый понедельник нужного нам временного промежутка.

=ТЕКСТ(ПОСЛЕД(10; 1; $B$1; 7); "Д МММ ГГГГ") & " - " & ТЕКСТ((ПОСЛЕД(10; 1; $B$1; 7) + 6); "Д МММ ГГГГ")

Поскольку дата в Excel хранится как число, мы записываем её в виде текста в нужном формате с помощью функции ТЕКСТ. Далее объединяем две последовательности дат в текстовые строки.

Надеюсь, эти примеры помогут вам быстро и эффективно создавать последовательность дат и календарь в Excel.