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

Из-за “плавающих” выходных и праздничных дней в России мы не можем каждый год использовать один и тот же набор данных.

Лично у меня ежегодно возникает вопрос – где скачать производственный календарь. Желательно в удобном формате Excel и с наименьшими трудозатратами. И вот совсем недавно я нашёл для себя решение, которое, уверен, пригодится многим.

Для автоматизации процесса нам понадобится

• Microsoft Excel (2010, 2013 или 2016)
• Страничка на портале Открытых Данных России откуда можно скачать производственный календарь в csv формате: http://data.gov.ru/opendata/7708660670-proizvcalendar.

* для Excel 2010 и 2013 вам понадобится бесплатная надстройка Power Query, скачать можно отсюда.

Как страничка выглядит на момент написания данной статьи – возможность скачать актуальную версию проиводственного календаря

На странице так же доступны предыдущие версии и версия производственного календаря в xlsx:

В Excel 2016 для решения подобных задач у нас есть группа кнопок “Скачать и преобразовать” (Get & Transform) на закладке меню “Данные” (Data).

Для Excel 2010 и Excel 2013 такой же функционал доступен в виде бесплатной надстройки Power Query, можно скачать отсюда. Функционал надстройки развивается каждый месяц, я рекомендую регулярно обновляться, чтобы всегда иметь под рукой новейшие функции и возможности трансформации данных.

Когда все нужные программы установлены и источник информации исследован – вернемся к задаче.

Для скачивания чего-либо из Интернета нам нужна ссылка на файл или страницу.

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

Паспорт содержит важную информацию о наборе, из которой, в частности, мы можем взять “Гиперссылку (URL) на набор данных”

Скопируем URL и сделаем запрос “Из интернета”.

Вставляем ссылку на csv файл

Далее – выберем кодировку 65001 UTF-8, игнорируем авто-определение типов данных и жмём “Изменить”

Теперь необходимо преобразовать полученные данные.

• Используем первую строку в качестве заголовков столбцов

• Выберем нужные нам столбцы. Воспользуемся функцией “Выбор столбцов” (Select Columns)

• Для удобства фильтрации изменим формат столбца “Год/Месяц” на “Целое число”

• Воспользуемся фильтром, оставим только интересующие нас годы

Зададим фильтр с нужными нам условиями

• Переименуем столбцы в порядковые номера месяцев и “Год/Месяц” в “Год”

• Далее воспользуемся функцией “Отменить свёртывание других столбцов” (Unpivot Other Columns) доступной на закладке меню “Преобразование” (Transform), выделив предварительно столбец “Год”

  
  

• Теперь выделим столбец “Значение” и разделим его на столбцы по разделителю “запятая”.

  
  

Это действие превращает таблицу в нечто ужасное с кучей столбцов. К тому же возникает непрошенный шаг – “Измененный тип1”, который нужно удалить.

Чтобы привести таблицу в нормальный вид, еще раз воспользуемся функцией “Отменить свертывание других столбцов” (Unpivot Other Columns)

• Выделяем столбцы “Год” и “Атрибут” (далее его переименуем в месяц)

  
  

В результате получается таблица

• Удаляем столбец “Атрибут.1” образованный из заголовков столбцов

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

  Но если интерес представляют только нерабочие дни, то дни со звёздочкой нужно исключить из списка. Это можно сделать простым фильтром “не содержит” символ “*”

  
  

• Осталась пара штрихов – переименовать столбцы и установить корректный тип данных

• В заключение можно добавить столбец с датой

Используем формулу

• Получив столбец с датой, можно убрать предыдущие столбцы

• Важно установить корректный тип данных!

  
  

• Осталось сохранить запрос и можно выводить его на лист

  
  

Подобным запросом мы получаем не полный список календарных дат, так как публикуемый на портале Открытых Данных набор содержит только праздничные и предпраздничные дни.

Полученный список может быть использован в функциях РАБДЕНЬ (WORKDAY), РАБДЕНЬ.МЕЖД (WORKDAY.INTL), а также для разметки полного календаря рабочими / не рабочими днями (что актуально для моделей в Power Pivot и Power BI).

Как обращаться к наиболее актуальному набору данных

Благодаря тому, что есть HTML представление паспорта данных на странице http://data.gov.ru/node/19107/code-passport

к нему тоже можно сделать запрос из Power Query (“Скачать и Преобразовать” / Get & Transform)

Интерес представляет блок со словами “Гиперссылка (URL) на набор”. Именно эта строка содержит ссылку на наиболее актуальный набор.

Разобраться, как это работает, вы можете, изучив xlsx-файл доступный по ссылке.

В нём вы найдёте несколько запросов

Для упрощения жизни разработчиков моделей в Power Pivot и Power BI, запрос на получение списка выходных дней доступен в виде функции в моей коллекции функций на Github – List.Dates.HolidaysRU

Если вы знаете, где можно найти такую же официальную информацию для СНГ стран, в частности, Казахстана, Украины и Республики Беларусь, оставьте комментарий со ссылкой. А ещё лучше – pull request в проект с соответствующими функциями.