Производственный календарь: автоматически обновляемый со всеми праздниками

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

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

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

Как будем создавать производственный календарь:
Сначала создадим запрос для любого года, затем преобразуем запрос в функцию и применим эту функцию для всех имеющихся лет, начиная с 2013 года до текущего года.

Для этого:

  1. Зайти на сайт https://xmlcalendar.ru/ и скопировать любую ссылку на производственный календарь любого года. Например, эту: https://xmlcalendar.ru/data/ru/2024/calendar.xml
  2. Открыть Excel и выбрать команды: Данные ? Получить данные ? Из других источников ? Из интернета ? вставить скопированную ссылку ? ОК ? Преобразовать данные (Изменить).
  3. Откроется окно редактора Power Query. Главная информация располагается в двух таблицах:

holidays — представляет собой справочник по праздникам, в котором первый столбец — код праздника, второй столбец — название праздника.

days — представляет собой справочник всех нерабочих дней, в котором:
? первый столбец — даты всех нерабочих дней в формате месяц.день
? второй столбец — тип дня: 1 — выходной, 2- сокращенный предпраздничный день, 3 — перенесенный рабочий день
? третий столбец — код праздника их таблицы holidays 
? четвертый столбец — с какой даты перенесен рабочий/выходной день.

Осталось преобразовать данные. Например, если нам нужны только не рабочие дни, то:

  1. Отфильтруем таблицу по второму столбцу со значением 1 (выходные дни).
  2. Удалим все столбцы, кроме первого. Получим столбец с выходными днями.
  3. Месяц и день лучше разделить на две отдельные колонки: для этого на главной вкладке выберем команду разделить столбец по разделителю и выбираем разделитель «точка»
  4. Соединить дату в полноценную: Вкладка Добавление столбца ? Настраиваемый столбец ? вводим формулу:
    #date(2024,[#»Attribute:d.1″],[#»Attribute:d.2″])
    где данные располагаются в порядке год-месяц-дата. 
  5. Осталось убрать лишние столбцы: правая кнопка мышки ? удалить другие столбцы
  6. Настроим формат даты.

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

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

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

Дадим название функции, например, 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.

Оцените статью
( Пока нет оценок )
ПОЛЕЗНЫЕ ПРОГРАММЫ ДЛЯ УЧЕБЫ И РАБОТЫ
Добавить комментарий

Этот сайт защищен reCAPTCHA и применяются Политика конфиденциальности и Условия обслуживания применять.

Срок проверки reCAPTCHA истек. Перезагрузите страницу.