Каждый, кто работает с датами в Excel, сталкивался с необходимостью учитывать выходные и праздничные дни. Производственный календарь — это незаменимый инструмент для многих специалистов. Он нужен:
- Бухгалтерии — для расчета зарплаты, отпусков и стажа.
- Логистам — чтобы корректно оценивать сроки доставки с учетом нерабочих дней.
- Менеджерам проектов — для точного планирования сроков выполнения задач.
- Всем, кто работает с датами — для автоматизации любых расчетов, где важны рабочие и выходные дни.
Можно каждый год вручную отмечать праздники в календаре, а можно один раз создать производственный календарь excel, который будет автоматически загружать актуальные данные из интернета и всегда держать под рукой полный список нерабочих дней. В этой статье мы разберем, как это сделать с помощью Power Query.
Как будет работать наш производственный календарь
Мы создадим систему, которая самостоятельно подгружает данные о праздниках и переносах выходных дней. Сначала сделаем запрос для одного года, затем превратим его в универсальную функцию, и применим эту функцию сразу для всех лет — с 2013 года до текущего. В результате вы получите единый список нерабочих дней, который можно использовать в любых расчетах, и который легко обновлять каждый год.
Пошаговое создание производственного календаря в Excel
Шаг 1. Получаем ссылку на данные о праздниках
Нам понадобится надежный источник информации о выходных и праздничных днях. Зайдите на сайт https://xmlcalendar.ru/. Здесь в открытом доступе есть файлы с производственными календарями. Скопируйте ссылку на любой год, например, для 2024 года она выглядит так:https://xmlcalendar.ru/data/ru/2024/calendar.xml
Шаг 2. Загружаем данные в Power Query
Откройте Excel и выполните последовательность команд:
- Данные → Получить данные → Из других источников → Из интернета
- Вставьте скопированную ссылку → нажмите ОК → выберите Преобразовать данные.
Откроется редактор Power Query. Здесь вся информация о нерабочих днях представлена в виде двух основных таблиц:
- holidays — справочник праздников: первый столбец — код праздника, второй — название.
- days — справочник всех нерабочих дней:
- первый столбец — даты в формате
месяц.день; - второй столбец — тип дня: 1 — выходной, 2 — сокращенный предпраздничный, 3 — перенесенный рабочий день;
- третий столбец — код праздника (ссылается на таблицу holidays);
- четвертый столбец — информация о переносе.
- первый столбец — даты в формате
Шаг 3. Преобразуем данные под наши задачи
Наша цель — получить простой список всех выходных дней (нерабочих) в виде дат. Для этого:
- Фильтруем второй столбец таблицы days, оставляя только строки со значением 1 (выходные дни).
- Удаляем все столбцы, кроме первого (с датами в формате месяц.день).
- Разделяем столбец с датой по разделителю «точка»: вкладка Главная → Разделить столбец → По разделителю, выбираем точку. Получаем два столбца: месяц и день.
- Создаем полноценную дату: вкладка Добавление столбца → Настраиваемый столбец, вводим формулу:
#date(2024, [месяц], [день])
Здесь важно подставить правильные названия ваших столбцов. - Удаляем лишние столбцы (оставляем только созданный столбец с датами).
- Настраиваем формат данных на тип Дата.
Формирование универсальной функции для производственного календаря
Мы получили запрос для конкретного года (2024). Чтобы наш производственный календарь excel мог работать с любым годом, преобразуем этот запрос в функцию.
Шаг 4. Создаем дубликат запроса
В панели запросов Power Query нажмите правой кнопкой мыши на созданный запрос и выберите Дублировать. Это поможет сохранить исходный вариант на случай ошибок.
Дайте дубликату понятное имя, например, fYear.
Шаг 5. Редактируем код функции (язык M)
Откройте Расширенный редактор (вкладка Главная). Вы увидите исходный код вашего запроса на языке M. Нам нужно сделать так, чтобы год можно было передавать как параметр.
Исходный код выглядит примерно так:
let
Источник = Xml.Tables(Web.Contents("https://xmlcalendar.ru/data/ru/2024/calendar.xml")),
days = Источник{0}[days],
day = days{0}[day],
// ... далее преобразования
in
#"Другие удаленные столбцы1"
Внесите следующие изменения:
- В самом начале, перед словом let, добавьте объявление функции:
(year as number) => - В строке с Web.Contents замените жестко заданный год (2024) на переменную year. Поскольку нужно склеить текст и число, используйте функцию Number.ToText:
Web.Contents("https://xmlcalendar.ru/data/ru/" & Number.ToText(year) & "/calendar.xml") - В шаге создания даты #date замените 2024 на year.
В итоге код функции будет выглядеть так:
(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"
Нажмите Готово. Теперь у нас есть готовая функция, которая возвращает список выходных дней для любого переданного года.
Использование функции для создания вечного календаря
Шаг 6. Создаем список лет
Создадим новый запрос (правой кнопкой мыши в панели запросов → Новый запрос → Другие источники → Пустой запрос).
В строке формул введите следующую конструкцию:
= {2013..Date.Year(DateTime.LocalNow())}
Эта запись создает список чисел от 2013 года до текущего года. Функция DateTime.LocalNow() возвращает текущую дату, а Date.Year извлекает из нее год. Благодаря этому список лет будет автоматически обновляться каждый год.
Шаг 7. Превращаем список в таблицу
Нажмите Enter, чтобы сформировать список. Затем на вкладке Преобразование выберите В таблицу.
У вас появится таблица с одним столбцом, содержащим все годы.
Шаг 8. Вызываем созданную функцию для каждого года
Добавим новый столбец, который будет содержать список выходных дней для каждого года:
- Вкладка Добавление столбца → Вызвать настраиваемую функцию.
- В открывшемся окне выберите из списка нашу функцию fYear.
- В качестве аргумента укажите столбец с годами.
Теперь в каждой строке таблицы находится вложенная табличка с датами выходных дней для конкретного года.
Шаг 9. Разворачиваем данные в единый список
Нажмите на значок с двойными стрелками в заголовке нового столбца и выберите Развернуть. Все вложенные таблицы объединятся в один общий столбец.
Готово! Вы получили единый производственный календарь excel, содержащий все выходные и праздничные дни с 2013 года по текущий год. При добавлении нового года достаточно будет просто обновить запрос (кнопка Обновить все), и календарь загрузит актуальные данные.
Как использовать полученный календарь
Теперь этот список дат можно использовать в любых расчетах:
- В формулах РАБДЕНЬ или ЧИСТРАБДНИ — просто укажите полученный диапазон как аргумент «праздники».
- В сводных таблицах для фильтрации рабочих и нерабочих периодов.
- В условном форматировании для визуального выделения выходных дней.
Такой подход экономит часы ручной работы и исключает риск пропустить важный праздник или перенос выходного дня.
Дополнительные возможности
Если вы хотите глубже разобраться в работе Power Query и научиться создавать более сложные запросы, обратите внимание на Самоучитель Excel: Power Query. Он поможет освоить базовые и продвинутые навыки работы с данными.
Заключение
Создание производственного календаря excel с автоматическим обновлением — это один из ярких примеров того, как Power Query может упростить рутинные задачи. Вы потратите время один раз, чтобы настроить запросы и функцию, а затем будете пользоваться готовым инструментом годами. Такой календарь всегда будет содержать актуальную информацию о переносах и праздничных днях, что особенно важно для бухгалтерии, логистики и проектного управления. Попробуйте повторить шаги из этой статьи, и вы увидите, как автоматизация данных меняет подход к работе в Excel.