Производственный календарь в 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/
Без лишних "рюшечек", простой, легкий и быстрый сайт, заточенный под одну задачу - отдавать всем желающим производственный календарь за нужный год в XML-формате. Отлично!
Если, вдруг, вы не в курсе, то XML - это текстовый формат с разметкой содержимого специальными <тегами>. Легкий, удобный и читаемый большинством современных программ, включая Excel.
Я, на всякий случай, связался с авторами сайта и они подтвердили, что сайт существует уже 7 лет, данные на нем постоянно актуализируются (у них для этого даже ветка на github есть) и закрывать его они не собираются. И совершенно не против, чтобы мы с вами с него грузили данные для любых наших проектов и расчётов в Excel. Бесплатно. Как же приятно, что есть еще такие люди! Респектище!
Осталось загрузить эти данные в Excel с помощью надстройки Power Query (для версий Excel 2010-2013 её можно бесплатно скачать с сайта Microsoft, а в версиях Excel 2016 и новее - она уже встроена по умолчанию).
Логика действий будет такая:
- Делаем запрос для загрузки данных с сайта за один любой год
- Превращаем наш запрос в функцию
- Применяем эту функцию к списку всех имеющихся лет, начиная с 2013 и до текущего года - и получаем "вечный" производственный календарь с автоматическим обновлением. Вуаля!
Шаг 1. Импортируем календарь за один год
Сначала загрузим производственный календарь за один любой год, например, за 2020. Для этого в Excel идём на вкладку Данные (или Power Query, если вы установили её как отдельную надстройку) и выбираем Из интернета (From Web). В открывшееся окно вставляем ссылку на соответствующий год, скопированную с сайта:
После нажатия на ОК появляется окно предварительного просмотра, в котором нужно нажать кнопку Преобразовать данные (Transform data) или Изменить данные (Edit data) и мы попадем в окно редактора запросов 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 по очереди отработает импорт для каждого года и мы получим столбец, где в каждой ячейке будет содержаться таблица с датами нерабочих дней (содержимое таблицы хорошо видно, если щёлкнуть мышью в фон ячейки рядом со словом Table):
Останется развернуть содержимое вложенных таблиц, щёлкнув по значку с двойными стрелками в шапке столбца Даты (галочку Использовать исходное имя столбца как префикс при этом можно снять):
... и после нажатия на ОК мы получим то, что хотели - список всех праздников с 2013 и до текущего года:
Первый, ненужный уже столбец, можно удалить, а для второго задать тип данных Дата (Date) в выпадающем списке в шапке столбца:
Сам запрос можно переименовать как-нибудь более осмысленно, чем Запрос1 и выгрузить затем результаты на лист в виде динамической "умной" таблицы с помощью команды Закрыть и загрузить на вкладке Главная (Home - Close & Load):
Обновлять созданный календарь в будущем можно щелчком правой кнопки мыши по таблице или запросу в правой панели через команду Обновить. Или же использовать кнопку Обновить все на вкладке Данные (Date - Refresh All) или сочетание клавиш Ctrl+Alt+F5.
Вот и всё.
Теперь вам больше никогда не нужно тратить время и мыслетопливо на поиски и актуализацию списка праздничных дней - теперь у вас есть "вечный" производственный календарь. Во всяком случае, до тех пор, пока авторы сайта http://xmlcalendar.ru/ поддерживают своё детище, что, я надеюсь, будет ещё очень и очень долго (спасибо им еще раз!).
Ссылки по теме
- Импорт курса биткойна в Excel из интернета через Power Query
- Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)
- Поиск пересечения интервалов дат
Но можно обойти пока через Добавление столбца - Добавить пользовательский (настраиваемый) столбец и ввести формулу вручную:
=fxYear([имя столбца с номером года])
Программа запросила куда разместить данные и вывела часть таблицы на лист excel. Часть, это значит,
Excel у меня 2007.
Для того, чтобы реализовать описанное в статье вам нужен хотя бы Excel 2010-2013 с установленной на него надстройкой Power Query.
Интересно, а как можно сделать при помощи PowerQuery, чтобы напротив дат еще и проставлялись названия праздников?
Сравниваю с производственным календарём
Так что все ОК
На сайте http/xmlcalendar.ru/ появился календарь на 2021 год.
Почему в вашем файле не происходит обновление, нет данных на 2021 год?
Это связано с функцией "сегодня"? Данные появятся когда наступит 2021 год?
Так что просто спасибо за мануал, Николай!
Что нужно изменить?
Сведения:
Attribute:f
Ccылка на файл Excel:
Сергей доступ к вашим файлам закрыт. Проделал сам этот мануал все работает (исключение перед тем как делать =#date(2021, [#"Attribute:d.1"], [#"Attribute:d.2"]) нужно изменить тип данных в столбцах!). Посмотрел на данные которые из 2021г. и на реальный календарь, к сожалению данные почти бесполезны.
(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
дважды "провалившись" в неё щелчком мыши по зелёному слову Table:
ничего нет...
и на 2022 год календаря пока нет, но на других ресурсах он по умолчанию есть. и для планирования можно подставить даты руками. как это сделать? спасибо
1 2 3 4 5 6 7 8 9 10 11
иванов х х
иванов х х
иванов х х
иванов х х примерно так. Извините - пенсионер,но активный. 70лет. Постоянно в поиске.
Каким образом можно реализовать этот календарь в гугл-таблицах? Как добавить этот список туда?
Почему у меня дата в формате мм.дд.гггг, а не дд.мм.гггг?
Функция РабДень вычисляет рабочие дни между двумя датами. Есть ли способ учесть ( с помощью иной функции или комбинации функций) перенос выходных дней? В частности, в этом году 5 марта - суббота - рабочий день. Спасибо.
PS Спасибо, уже решил сам, пока писал. )
Кнопками сделать не получается, но если прописать источник вручную:
= Xml.Tables(Web.Contents("http://xmlcalendar.ru/data/ru/2020/calendar.xml"))upd
В последнем офисе все хорошо работает.
Видать, в 2016 логика обработчика/запросчика другая.