Производственный календарь в 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" поэтому если выводить с названиями оснований, то нужно на коде основания применить операцию заполнить вниз. Тогда все встанет на свои места.
25.11.2020 23:16:34
Николай, добрый вечер!
На сайте http/xmlcalendar.ru/   появился календарь на 2021 год.
Почему в вашем файле не происходит обновление, нет данных на 2021 год?
Это связано с функцией "сегодня"? Данные появятся когда наступит 2021 год?  
Не обновилась страница перед тем как я написала. Сергей Лео у же задал аналогичный вопрос =)
Так что просто спасибо за мануал, Николай!8-)
15.12.2020 00:38:57
Николай, так и не нашёл ответа на вопрос: как привязать расшифровку праздников в производственный календарь?
27.12.2020 22:40:38
Добрый день. А существует международная база данных по праздникам? Интересует Германия и Англия. Спс.
03.01.2021 20:22:56
на последнем шаге, после вызова настраиваемой функции выпадает такое сообщение: Formula.Firewall: Запрос "праздники" (шаг "Вызвана настраиваемая функция") запрашивает доступ к источникам данных, имеющим уровни конфиденциальности, которые не могут использоваться вместе. Измените эту комбинацию данных.
Что нужно изменить?
26.02.2021 00:20:39
В PowerQuery нужно в Файл-Параметры и настройки-Параметры запроса-Конфиденциальность поставить галку "Всегда игнорировать параметры уровней конфиденциальности".
26.02.2021 14:44:04
21.02.2021 21:48:47
На последнем запросе применения функции все года кроме 2021 Error. Ошибка: В запросе "" произошла ошибка. Expression.Error: Столбец "Attribute:f" таблицы не найден.
Сведения:
   Attribute:f

https://drive.google.com/file/d/1ZqVPFCmJMooZfiVx0Gmb6ncQ267wuIgE/view?usp=sharing
https://drive.google.com/file/d/1sCsVlusNKpsF5W98eyxNvgpNAMfNcuVn/view?usp=sharing
Ccылка на файл Excel:
https://drive.google.com/file/d/1Qot2oZmYfZR0BPZAbvHHrnlPRX9zVSVo/view?usp=sharing
25.02.2021 21:05:07
Все дело в том, что структура xml файла в 2021г. изменилась (добавился 1 столбец). Думаю проще импортировать html файл и с ним работать.
Сергей доступ к вашим файлам закрыт. Проделал сам этот мануал все работает (исключение перед тем как делать =#date(2021, [#"Attribute:d.1"], [#"Attribute:d.2"]) нужно изменить тип данных в столбцах!). Посмотрел на данные которые из 2021г. и на реальный календарь, к сожалению данные почти бесполезны.
26.02.2021 06:07:01
Спасибо. Обновил ссылки.  
26.02.2021 13:16:49
В создании функции косяк. В Просмотр-расширенный редактор вставь код (вместо своего)и все работает:
(year as number)=>
let
   Источник = Xml.Tables(Web.Contents("http://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.SelectColumns(#"Добавлен пользовательский объект",{"дата"}),
   Пользовательский1 = #"Другие удаленные столбцы1"
in
   Пользовательский1
26.02.2021 14:49:00
 Спасибо, буду пробовать.
12.03.2021 10:12:01
отсутствуют праздники.
дважды "провалившись" в неё щелчком мыши по зелёному слову Table:
ничего нет...
12.03.2021 14:48:44
Иногда делают переносы рабочих дней на субботу. Например на 20-е февраля 2021. И ладно, если этот факт перед праздником, что при пометке его предпраздничным днем дает возможность исключить эту субботу из выходных дней. А если перенос будет на обычную субботу на неделю вперед? Как вылавливать такие рабочие дни? Такая форма календаря плохо работает. Уже можно сказать обжегся на субботе 20 февраля - неправильно рассчитал количество рабочих дней из-за нее.
16.08.2021 12:03:45
Юрий, удалось решить эту проблему?
18.05.2021 13:11:57
как добавить корпоративный отпуск в этот календарь? у нас в компании майские идут сплошными.
и на 2022 год календаря пока нет, но на других ресурсах он по умолчанию есть. и для планирования можно подставить даты руками. как это сделать? спасибо
26.08.2021 22:09:22
Добрый вечер. Только что нашел данный сайт, прошел регистрацию. Где-то на сайте есть построение графика работы 4-х человек (охраны) для работы сутки-трое .  

             1 2 3 4 5 6 7 8 9 10 11
иванов  х          х
иванов    х          х
иванов       х         х
иванов         х         х                      примерно так.  Извините - пенсионер,но активный. 70лет. Постоянно в поиске.
28.09.2021 20:33:09
Добрый день!
Каким образом можно реализовать этот календарь в гугл-таблицах? Как добавить этот список туда?
Наверх