Скачать производственный календарь в Excel

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

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

Лично у меня ежегодно возникает вопрос – где скачать производственный календарь. Желательно в удобном формате 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 в проект с соответствующими функциями.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s