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

Производственный календарь, т.е. список дат, где соответствующим образом помечены все официальные рабочие и выходные дни - совершенно необходимая штука для любого пользователя Microsoft Excel. На практике без него не обойтись:

  • в бухгалтерских расчетах (зарплата, стаж, отпуска...)
  • в логистике - для корректного определения сроков доставки с учётом выходных и праздников (помните классическое "давай уже после праздников?")
  • в управлении проектами - для правильной оценки сроков с учётом, опять же, рабочих-нерабочих дней
  • при любом использовании функций типа РАБДЕНЬ (WORKDAY) или ЧИСТРАБДНИ (NETWORKDAYS), т.к. они требуют в качестве аргумента список праздников
  • при использовании функций Time Intelligence (типа TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR и т.д.) в Power Pivot и Power BI
  • ... и т.д. и т.п. - примеров масса.

Тем, кто работает в корпоративных ERP-системах типа 1С или SAP - проще, в них производственный календарь встроен. Но что делать пользователям Excel?

Можно, конечно, вести такой календарь вручную. Но тогда придется как минимум раз в год (а то и чаще, как в "весёлом" 2020 году) его актуализировать, аккуратно внося туда все выходные, переносы и нерабочие дни, придуманные нашим правительством. А потом повторять эту процедуру каждый следующий год. Скукота.

А как насчёт чуть напрячься и сделать "вечный" производственный календарь в Excel? Такой, чтобы сам обновлялся, брал данные из интернета и формировал всегда актуальный список нерабочих дней для последующего использования в любых расчетах? Заманчиво?

Сделать такое, на самом деле, совсем не сложно.

Источник данных

Главный вопрос - а где брать данные? В поисках подходящего источника я перебрал несколько вариантов:

  • Оригинальные указы публикуются на сайте правительства в формате PDF (вот, один из них, например) и отпадают сразу - полезную информацию из них не вытащить.
  • Заманчивым вариантом, на первый взгляд, казался "Портал открытых данных РФ", где есть соответствующий набор данных, но, при ближайшем изучении всё оказалось печально. Для импорта в Excel сайт жутко неудобен, техподдержка не отвечает (самоизолировались?), да и сами данные там давно устарели - производственный календарь на 2020 год обновлялся последний раз в ноябре 2019 (позорище!) и, само-собой, не содержит наших "коронавирусных" и "голосовательных" выходных 2020 года, например.

Разочаровавшись в официальных источниках, я стал рыть неофициальные. Их в интернете множество, но большинство из них, опять же, совершенно неприспособлены для импорта в Excel и выдают производственный календарь в виде красивых картинок. Но нам-то с вами не на стенку его вешать, правильно?

И вот в процессе поисков случайно обнаружилось прекрасное - сайт http://xmlcalendar.ru/

Сайт xmlcalendar.ru

Без лишних "рюшечек", простой, легкий и быстрый сайт, заточенный под одну задачу - отдавать всем желающим производственный календарь за нужный год в XML-формате. Отлично!

Если, вдруг, вы не в курсе, то XML - это текстовый формат с разметкой содержимого специальными <тегами>. Легкий, удобный и читаемый большинством современных программ, включая Excel.

Я, на всякий случай, связался с авторами сайта и они подтвердили, что сайт существует уже 7 лет, данные на нем постоянно актуализируются (у них для этого даже ветка на github есть) и закрывать его они не собираются. И совершенно не против, чтобы мы с вами с него грузили данные для любых наших проектов и расчётов в Excel. Бесплатно. Как же приятно, что есть еще такие люди! Респектище!

Осталось загрузить эти данные в Excel с помощью надстройки Power Query (для версий Excel 2010-2013 её можно бесплатно скачать с сайта Microsoft, а в версиях Excel 2016 и новее - она уже встроена по умолчанию).

Логика действий будет такая:

  1. Делаем запрос для загрузки данных с сайта за один любой год
  2. Превращаем наш запрос в функцию
  3. Применяем эту функцию к списку всех имеющихся лет, начиная с 2013 и до текущего года - и получаем "вечный" производственный календарь с автоматическим обновлением. Вуаля!

Шаг 1. Импортируем календарь за один год

Сначала загрузим производственный календарь за один любой год, например, за 2020. Для этого в Excel идём на вкладку Данные (или Power Query, если вы установили её как отдельную надстройку) и выбираем Из интернета (From Web). В открывшееся окно вставляем ссылку на соответствующий год, скопированную с сайта:

Грузим данные с сайта через Power Query

После нажатия на ОК появляется окно предварительного просмотра, в котором нужно нажать кнопку Преобразовать данные (Transform data) или Изменить данные (Edit data) и мы попадем в окно редактора запросов Power Query, где и продолжим работу с данными:

Окно Power Query

Сразу же можно смело удалить в правой панели Параметры запроса (Query settings) шаг Измененный тип (Changed Type) - он нам не нужен.

Таблица в столбце holidays содержит коды и описания нерабочих дней - увидеть её содержимое можно, дважды "провалившись" в неё щелчком мыши по зелёному слову Table:

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

Для возврата назад придется удалить в правой панели все появившиеся шаги обратно до Источника (Source).

Вторая таблица, куда можно зайти аналогичным образом, содержит как раз то, что нам нужно - даты всех нерабочих дней:

Таблица выходных

Останется эту табличку обработать, а именно:

1. Отфильтровать только даты праздников (т.е. единички) по второму столбцу Attribute:t

Удаляем лишние столбцы

2. Удалить все столбцы, кроме первого - щелчком правой кнопкой мыши по заголовку первого столбца и выбором команды Удалить другие столбцы (Remove Other Columns):

Удаляем лишние столбцы

3. Разделить первый столбец по точке отдельно на месяц и день с помощью команды Разделить столбец - По разделителю на вкладке Преобразование (Transform - Split column - By delimiter):

Делим по точке на месяц и день

4. И, наконец, создать вычисляемый столбец с нормальными датами. Для этого на вкладке Добавление столбца жмём на кнопку Настраиваемый столбец (Add Column - Custom Column) и вводим в появившееся окно следующую формулу:

Создаем столбец с датами

=#date(2020, [#"Attribute:d.1"], [#"Attribute:d.2"])

Здесь у оператора #date три аргумента: год, месяц и день, соответственно. После нажатия на ОК получаем требуемый столбец с нормальными датами выходных, а остальные столбцы удаляем как в п.2

Готовый список нерабочих дней

Шаг 2. Превращаем запрос в функцию

Следующая наша задача - преобразовать созданный на 2020 год запрос в универсальную функцию для любого года (номер года будет её аргументом). Для этого делаем следующее:

1. Разворачиваем (если ещё не развернута) панель Запросы (Queries) слева в окне Power Query:

Панель запросов

2. После преобразования запроса в функцию возможность видеть шаги, из которых состоит запрос и легко их редактировать, к сожалению, пропадает. Поэтому имеет смысл сделать копию нашего запроса и резвиться уже с ней, а оригинал оставить про запас. Для этого щелкаем правой кнопкой мыши в левой панели по нашему запросу calendar и выбираем команду Дублировать (Duplicate).

Щелкнув еще раз правой по получившейся копии calendar(2) выберем команду Переименовать (Rename) и введём новое имя - пусть будет, например, fxYear:

Копируем исходный запрос

3. Открываем исходный код запроса на внутреннем языке Power Query (он лаконично назвается "М") с помощью команды Расширенный редактор на вкладке Просмотр (View - Advanced Editor) и вносим туда небольшие правки для превращения нашего запроса в функцию на любой год.

Было:

Было

Стало:

Стало

Если интересны подробности, то здесь:

  • (year as number)=>  - объявляем, что в нашей функции будет один числовой аргумент - переменная year
  • Подклеиваем переменную year в веб-ссылку на шаге Источник. Поскольку Power Query не позволяет склеивать числа и текст, то на лету преобразовываем номер года в текст с помощью функции Number.ToText
  • Подставляем переменную year вместо 2020 года на предпоследнем шаге #"Добавлен пользовательский объект", где мы формировали дату из фрагментов.

После нажатия на Готово наш запрос превращается в функцию:

Запрос превратился в функцию

Шаг 3. Импортируем календари за все года

Осталась самая малость - сделать последний главный запрос, который будет закачивать данные за все имеющиеся года и складывать все полученные даты праздников в одну таблицу. Для этого:

1. Щёлкаем в левой панели запросов в серое пустое место правой кнопкой мыши и выбираем последовательно Новый запрос - Другие источники - Пустой запрос (New Query - From other sources - Blank query):

Создаем пустой запрос

2. Нам нужно сформировать список всех лет, для которых мы будем запрашивать календари, т.е. 2013, 2014 ... 2020. Для этого в строке формул появившегося пустого запроса вводим команду:

Формируем список лет

Конструкция:

={ЧислоА..ЧислоБ}

... в Power Query формирует список целых чисел от А до Б. Например, выражение

={1..5}

...выдало бы список 1,2,3,4,5.

Ну, а чтобы не привязываться жёстко к 2020 году, мы используем функцию DateTime.LocalNow() - аналог экселевской функции СЕГОДНЯ (TODAY) в Power Query - и извлекаем из неё, в свою очередь, текущий год функцией Date.Year.

3. Полученный набор лет хоть и выглядит вполне адекватно, но для Power Query представляет собой не таблицу, а особый объект - список (List). Но преобразовать его в таблицу - не проблема: достаточно нажать кнопку В таблицу (To Table) в левом верхнем углу:

Преобразуем список в таблицу

4. Финишная прямая! Применяем созданную нами ранее функцию fxYear к полученному списку лет. Для этого на вкладке Добавление столбца жмём кнопку Вызвать настраиваемую функцию (Add Column - Invoke Custom Function) и задаём её единственный аргумент - колонку Column1 с годами:

Вызываем функцию fxYear

После нажатия на ОК наша функция fxYear по очереди отработает импорт для каждого года и мы получим столбец, где в каждой ячейке будет содержаться таблица с датами нерабочих дней (содержимое таблицы хорошо видно, если щёлкнуть мышью в фон ячейки рядом со словом Table):

Вложенные таблицы с данными по каждому году

Останется развернуть содержимое вложенных таблиц, щёлкнув по значку с двойными стрелками в шапке столбца Даты (галочку Использовать исходное имя столбца как префикс при этом можно снять):

Разворачиваем вложенные таблицы

... и после нажатия на ОК мы получим то, что хотели - список всех праздников с 2013 и до текущего года:

Все загруженные даты праздников за все годы

Первый, ненужный уже столбец, можно удалить, а для второго задать тип данных Дата (Date) в выпадающем списке в шапке столбца:

Задаем тип данных даты

Сам запрос можно переименовать как-нибудь более осмысленно, чем Запрос1 и выгрузить затем результаты на лист в виде динамической "умной" таблицы с помощью команды Закрыть и загрузить на вкладке Главная (Home - Close & Load):

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

Обновлять созданный календарь в будущем можно щелчком правой кнопки мыши по таблице или запросу в правой панели через команду Обновить. Или же использовать кнопку Обновить все на вкладке Данные (Date - Refresh All) или сочетание клавиш Ctrl+Alt+F5.

Вот и всё.

Теперь вам больше никогда не нужно тратить время и мыслетопливо на поиски и актуализацию списка праздничных дней - теперь у вас есть "вечный" производственный календарь. Во всяком случае, до тех пор, пока авторы сайта http://xmlcalendar.ru/ поддерживают своё детище, что, я надеюсь, будет ещё очень и очень долго (спасибо им еще раз!).

Ссылки по теме



21.07.2020 16:22:28
День добрый! Данный календарь не учитывает региональные праздники (выходные дни). Например, в Башкирии и Татарстане.
23.07.2020 14:13:39
Само-собой. Это официальные выходные России. Для Республики Татарстан и т.п. нужно искать свой источник данных - возможно, сайт правительства Татарстана или что-то подобное. Все упирается в источник данных :)
23.07.2020 14:37:23
Здравствуйте Николай! В конце видео когда создали пустой запрос и преобразовали его в таблицу у меня нет вкладки Вызвать настраиваемую функцию, а  есть только там три вкладки Добавить пользовательский столбец, Добавить столбец индекса, Создать дубликат столбца, как решить эту проблему или у меня старая версия. Спасибо.
23.07.2020 14:40:04
Олег, вам точно надо обновиться :)
Но можно обойти пока через Добавление столбца - Добавить пользовательский (настраиваемый) столбец и ввести формулу вручную:

=fxYear([имя столбца с номером года])
23.07.2020 14:47:37
Николай спасибо огромное за ваш труд!
22.10.2020 10:45:16
У меня такая же проблема была. Я закрыл редактор запросов PowerQuery и создал пустой запрос в исходной таблице Excell.
24.07.2020 01:49:59
Николай, спасибо за статью, очень нужный материал. Но после копирования ссылки и копирования файла в excel никакой кнопки "Преобразовать данные (Transform data) или Изменить данные (Edit data)  не появилось и соответственно я не  попал в окно редактора запросов Power Query.
Программа запросила куда разместить данные и вывела часть таблицы на лист excel. Часть, это значит, .
Excel у меня 2007.  
24.07.2020 17:18:04
В Excel 2007 нет Power Query :D
Для того, чтобы реализовать описанное в статье вам нужен хотя бы Excel 2010-2013 с установленной на него надстройкой Power Query.
30.07.2020 12:24:42
Спасибо большое, Николай, за чудный и подробный эксель-лайф-хак! ;]
Интересно, а как можно сделать при помощи PowerQuery, чтобы напротив дат еще и проставлялись названия праздников?
07.10.2020 13:42:27
Да, но придется продублировать запрос, выдернуть другую таблицу и сделать потом слияние запросов с датами, чтобы подтянуть праздники.
30.07.2020 14:14:15
Спасибо большое. Раньше, действительно, приходилось постоянно дописывать даты праздников в ручную. По аналогии с этим приемом, хотелось бы узнать - возможно ли как то в эксель "подтягивать" данные адресов из ФИАС (https://fias.nalog.ru/)?. Т.е. в первой ячейке позволяет выбрать регион РФ. При выборе региона - для соседней ячейки подгружает список районов. При выборе района для следующей ячейки подгружает выбор сельского поселения. И т.д.
23.08.2020 16:51:57
Добрый день. Просматривая календарь http://xmlcalendar.ru/data/ru/2020/calendar.xml обнаружил, что нет в марте 2020 года даты 7 марта 2020. так как это суббота и вроде бы выходной ))) И нет даты сокращённого дня, то есть 6 марта 2020

Сравниваю с производственным календарём http://www.consultant.ru/law/ref/calendar/proizvodstvennye/2020/
23.08.2020 21:54:46
Слава, в этом календаре перечислены только даты праздников (а не суббот-воскресений или коротких дней).
Так что все ОК :)
01.10.2020 10:32:13
Николай, день добрый. Правильна я понимаю, что как только на сайте появится 2021 г,  после обновления таблицы в PQ,  подтянутся актуальные данные за  2021?
07.10.2020 13:41:10
Да, конечно. Задумка как раз в том, чтобы один раз сделать, а потом только обновлять :D
22.10.2020 10:55:47
Николай, небольшое уточнение: Перенесенные на рабочий день выходные из-за попадания праздника на выходной имеют код "null" поэтому если выводить с названиями оснований, то нужно на коде основания применить операцию заполнить вниз. Тогда все встанет на свои места.
Наверх