Производственный календарь со всеми официальными нерабочими днями — это необходимый инструмент практически для любого пользователя.
Производственный календарь нужен:
➔ бухгалтерии при расчете стажа зарплаты и отпусков;
➔ в логистике для корректной оценки сроков доставки товара с учетом не рабочих дней;
➔для управление проектами, чтобы правильно определить сроки выполнения проектов с учетом рабочих и нерабочих дней;
➔ для других целей.
Можно делать производственный календарь каждый раз вручную, а можно сделать вечный производственный календарь в excel, который будет брать данные из интернета и формировать всегда актуальный список нерабочих (праздничных и выходных) дней для последующего использования в любых расчетах.
Как будем создавать производственный календарь:
Сначала создадим запрос для любого года, затем преобразуем запрос в функцию и применим эту функцию для всех имеющихся лет, начиная с 2013 года до текущего года.
Для этого:
- Зайти на сайт https://xmlcalendar.ru/ и скопировать любую ссылку на производственный календарь любого года. Например, эту: https://xmlcalendar.ru/data/ru/2024/calendar.xml
- Открыть Excel и выбрать команды: Данные ? Получить данные ? Из других источников ? Из интернета ? вставить скопированную ссылку ? ОК ? Преобразовать данные (Изменить).
- Откроется окно редактора Power Query. Главная информация располагается в двух таблицах:
holidays — представляет собой справочник по праздникам, в котором первый столбец — код праздника, второй столбец — название праздника.
days — представляет собой справочник всех нерабочих дней, в котором: 
? первый столбец — даты всех нерабочих дней в формате месяц.день
? второй столбец — тип дня: 1 — выходной, 2- сокращенный предпраздничный день, 3 — перенесенный рабочий день
? третий столбец — код праздника их таблицы holidays
? четвертый столбец — с какой даты перенесен рабочий/выходной день.
Осталось преобразовать данные. Например, если нам нужны только не рабочие дни, то:
- Отфильтруем таблицу по второму столбцу со значением 1 (выходные дни).
- Удалим все столбцы, кроме первого. Получим столбец с выходными днями.
- Месяц и день лучше разделить на две отдельные колонки: для этого на главной вкладке выберем команду разделить столбец по разделителю и выбираем разделитель «точка»
- Соединить дату в полноценную: Вкладка Добавление столбца ? Настраиваемый столбец ? вводим формулу:
#date(2024,[#»Attribute:d.1″],[#»Attribute:d.2″])
где данные располагаются в порядке год-месяц-дата. - Осталось убрать лишние столбцы: правая кнопка мышки ? удалить другие столбцы
- Настроим формат даты.
Формирование универсальной функции для производственного календаря
Чтобы извлекать нерабочие дни для любого года, нужно преобразовать этот запрос в универсальную функцию,
Чтобы было легче исправить в случае ошибок, сделайте дубликат функции. 
Дадим название функции, например, fYear.
Откроем расширенный редактор: Главная ? Расширенный редактор.
Здесь будет исходный код нашего запроса на языке М.
let
Источник = Xml.Tables(Web.Contents("https://xmlcalendar.ru/data/ru/2024/calendar.xml")),
days = Источник{0}[days],
day = days{0}[day],
#"Строки с примененным фильтром" = Table.SelectRows(day, each ([#"Attribute:t"] = "1")),
#"Другие удаленные столбцы" = Table.SelectColumns(#"Строки с примененным фильтром",{"Attribute:d"}),
#"Разделить столбец по разделителю" = Table.SplitColumn(#"Другие удаленные столбцы", "Attribute:d", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute:d.1", "Attribute:d.2"}),
#"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Attribute:d.1", Int64.Type}, {"Attribute:d.2", Int64.Type}}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "Пользовательская", each #date(2024,[#"Attribute:d.1"],[#"Attribute:d.2"])),
#"Измененный тип1" = Table.TransformColumnTypes(#"Добавлен пользовательский объект",{{"Пользовательская", type date}}),
#"Другие удаленные столбцы1" = Table.SelectColumns(#"Измененный тип1",{"Пользовательская"})
in
#"Другие удаленные столбцы1"
В него нужно внести следующие правки, а именно: изменить 2024 год на изменяющийся аргумент функции.
Во-первых, в самое начало кода вставляем новую пустую строчку и в скобках прописываем исходные аргументы, по которым функция будет выдавать результат. У нас аргумент один — это год. Назовем этот аргумент year и запишем: (year as number) =>
Во-вторых, заменим 2024 год на переменную year (в двух местах):
? Когда мы склеивали полноценную дату: вместо 2024 года как числа вписываем переменную year.
? Когда мы прописываем полный путь: нужно сделать склейку текста с помощью кавычек и амперсанда. Но так как мы не можем в Power Query склеивать числа и текст, то число переведем в текст с помощью функции Number.ToText.
Получаем:
(year as number) =>
let
Источник = Xml.Tables(Web.Contents("https://xmlcalendar.ru/data/ru/" & Number.ToText(year) & "/calendar.xml")),
days = Источник{0}[days],
day = days{0}[day],
#"Строки с примененным фильтром" = Table.SelectRows(day, each ([#"Attribute:t"] = "1")),
#"Другие удаленные столбцы" = Table.SelectColumns(#"Строки с примененным фильтром",{"Attribute:d"}),
#"Разделить столбец по разделителю" = Table.SplitColumn(#"Другие удаленные столбцы", "Attribute:d", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute:d.1", "Attribute:d.2"}),
#"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Attribute:d.1", Int64.Type}, {"Attribute:d.2", Int64.Type}}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "Пользовательская", each #date(year,[#"Attribute:d.1"],[#"Attribute:d.2"])),
#"Измененный тип1" = Table.TransformColumnTypes(#"Добавлен пользовательский объект",{{"Пользовательская", type date}}),
#"Другие удаленные столбцы1" = Table.SelectColumns(#"Измененный тип1",{"Пользовательская"})
in
#"Другие удаленные столбцы1"
Мы получили функцию.
Использование функции
Создадим новый запрос: правая кнопка мыши ? новый запрос ? другие источники ? пустой запрос.
В строке формул, чтобы создать числовую последовательность лет, пропишем следующую формулу:
= {2013..Date.Year(DateTime.LocalNow())}
где:
в фигурных скобках задаем начало и конец числовой последовательности. То есть если мы напишем пример 2013..2024 и нажмем на enter, то получим арифметическую прогрессию от 2013 до 2024 года. Чтобы сделать автоматическое обновление года, запишем в функцию DateTime.LocalNow(), которую обернем в функцию Year() для получения года. Далее год будет обновляться автоматически.
Для работы функции список нужно превратить в таблицу: Вкладка Преобразование ? В таблицу.
Вызовем созданную функцию: Вкладка Добавление столбца ? кнопка Вызвать настраиваемую функцию ? выбираем из выпадающего списка функцию ? Ок.
Получаем вложенные в каждую ячейку таблички. Если щелкнуть мышкой на пустое место в поле с таблицами, то мы увидим содержимое этих таблиц, в каждой из которых будут перечислены все нерабочие дни каждого года. 
Осталось создать один большой список. Для этого нажмем на двойные стрелки и развернем столбец. Мы получили один большой список нерабочих дней, который можно обновлять каждый год.
Если Вам не все понятно для создания функции производственного календаря или Вы хотите больше знать о Power Query, посмотрите «Самоучитель Excel: Power Query«, который поможет освоить базовые и продвинутые навыки работы в Power Quer.