Содержание:
Производственный календарь, то есть список дат, где соответствующим образом отмечены все официальные рабочие и праздничные дни – совершенно необходимая вещь для любого пользователя Microsoft Excel. На практике без этого не обойтись:
- в бухгалтерских расчетах (зарплата, стаж, отпуска…)
- в логистике – для правильного определения сроков доставки с учетом выходных и праздников (помните классическое «давай после праздников?»)
- в управлении проектами – для правильного расчета сроков с учетом опять же рабочих-нерабочих дней
- любое использование таких функций, как WORKDAY (РАБОЧИЙ ДЕНЬ) or ЧИСТЫЕ РАБОТНИКИ (ЧИСТЕВЫЕ ДНИ), потому что в качестве аргумента они требуют список праздников
- при использовании функций 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 — это текстовый формат, содержимое которого размечено специальными
На всякий случай я связался с авторами сайта и они подтвердили, что сайт существует уже 7 лет, данные на нем постоянно обновляются (у них даже есть ветка на github для этого) и закрывать его они не собираются. И я совершенно не против того, чтобы мы с вами загружали из него данные для любых наших проектов и расчетов в Excel. Бесплатно. Приятно осознавать, что еще есть такие люди! Уважать!
Осталось загрузить эти данные в Excel с помощью надстройки Power Query (для версий Excel 2010-2013 ее можно бесплатно скачать с сайта Microsoft, а в версиях Excel 2016 и новее она уже встроена по умолчанию ).
Логика действий будет следующая:
- Делаем запрос на скачивание данных с сайта за любой год
- Превращаем наш запрос в функцию
- Применяем эту функцию к списку всех доступных лет, начиная с 2013 и по текущий год — и получаем «вечный» производственный календарь с автоматическим обновлением. Вуаля!
Шаг 1. Импортируйте календарь на один год.
Сначала загрузите производственный календарь на любой год, например на 2020. Для этого в Excel перейдите на вкладку Данные (или Power Queryесли вы установили его как отдельное дополнение) и выберите Из Интернета (Из Интернета). В открывшееся окно вставьте ссылку на соответствующий год, скопированную с сайта:
После нажатия на OK появится окно предварительного просмотра, в котором нужно нажать кнопку Преобразование данных (Преобразование данных) or Чтобы изменить данные (Редактировать данные) и мы попадем в окно редактора запросов Power Query, где продолжим работу с данными:
Сразу можно смело удалять в правой панели Параметры запроса (Настройки запроса) шаг модифицированный тип (Измененный тип) Он нам не нужен.
Таблица в графе праздников содержит коды и описания нерабочих дней – ее содержимое можно увидеть, «пролистав» ее дважды, нажав на зеленое слово Настольные:
Чтобы вернуться назад, вам придется удалить в правой панели все появившиеся шаги назад к Источник (Источник).
Вторая таблица, доступ к которой можно получить аналогичным образом, содержит именно то, что нам нужно — даты всех нерабочих дней:
Осталось обработать эту пластину, а именно:
1. Фильтровать только праздничные даты (т. е. даты) по второму столбцу Атрибут: т
2. Удалить все столбцы, кроме первого – кликнув правой кнопкой мыши по заголовку первого столбца и выбрав команду Удалить другие столбцы (Удалить другие столбцы):
3. Разделить первый столбец точкой отдельно для месяца и дня с помощью команды Разделить столбец – по разделителю таб трансформация (Преобразование — Разделить столбец — По разделителю):
4. И, наконец, создайте вычисляемый столбец с обычными датами. Для этого на вкладке Добавление столбца нажмите на кнопку Пользовательский столбец (Добавить столбец — Пользовательский столбец) и в появившемся окне введите следующую формулу:
=#датированный(2020, [#»Атрибут:d.1″], [#»Атрибут:d.2″])
Здесь оператор #date имеет три аргумента: год, месяц и день соответственно. После нажатия на OK получаем нужный столбец с обычными выходными днями, а остальные столбцы удаляем, как в шаге 2
Шаг 2. Превращение запроса в функцию
Наша следующая задача — преобразовать запрос, созданный для 2020 года, в универсальную функцию для любого года (ее аргументом будет номер года). Для этого делаем следующее:
1. Развертывание (если оно еще не развернуто) панели Запросы (Вопросы) слева в окне Power Query:
2. После преобразования запроса в функцию возможность видеть шаги, из которых состоит запрос, и легко их редактировать, к сожалению, пропадает. Поэтому имеет смысл сделать копию нашего запроса и порезвиться уже с ней, а оригинал оставить про запас. Для этого щелкните правой кнопкой мыши в левой панели по нашему запросу календаря и выберите команду Дублировать.
Повторный щелчок правой кнопкой мыши по полученной копии календаря (2) выберет команду Переименовывать (Переименовать) и введите новое имя – пусть это будет, например, fxYear:
3. Открываем исходный код запроса на внутреннем языке Power Query (он лаконично называется «М») с помощью команды Расширенный редактор таб Обзор(Вид — Расширенный редактор) и внести туда небольшие изменения, чтобы превратить наш запрос в функцию на любой год.
Это было:
После:
Если интересны подробности, то здесь:
- (год как число)=> — мы объявляем, что наша функция будет иметь один числовой аргумент — переменную год
- Вставка переменной год на веб-ссылку в шаге Источник. Поскольку Power Query не позволяет склеивать числа и текст, преобразуем номер года в текст на лету с помощью функции Число.ToText
- На предпоследнем шаге подставляем переменную года на 2020 год. #”Добавлен пользовательский объект«, где мы формировали дату из фрагментов.
После нажатия на Завершить наш запрос становится функцией:
Шаг 3. Импортируйте календари за все годы.
Осталось сделать последний основной запрос, который выгрузит данные за все доступные годы и сложит все полученные даты праздников в одну таблицу. Для этого:
1. Щелкаем в левой панели запросов в сером пустом месте правой кнопкой мыши и выбираем последовательно Новый запрос – Другие источники – Пустой запрос (Новый запрос — Из других источников — Пустой запрос):
2. Нам необходимо сформировать список всех лет, для которых мы будем запрашивать календари, т.е. 2013, 2014… 2020. Для этого в строке формул появившегося пустого запроса введите команду:
Структура:
= {НомерА..НомерБ}
… в Power Query генерирует список целых чисел от A до B. Например, выражение
={1..5}
… выдаст список из 1,2,3,4,5.
Ну и чтобы не быть жестко привязанным к 2020 году, воспользуемся функцией ДатаВремя.LocalNow() — аналог функции Excel СЕГОДНЯ (CЕГОДНЯ) в Power Query — и извлекаем из него, в свою очередь, текущий год функцией Дата.Год.
3. Полученный набор лет хоть и выглядит вполне адекватно, но представляет собой не таблицу для Power Query, а специальный объект — список (Список). Но преобразовать его в таблицу не проблема: достаточно нажать кнопку К столу (К столу) в левом верхнем углу:
4. Финишная черта! Применение функции, которую мы создали ранее fxYear в полученный список лет. Для этого на вкладке Добавление столбца нажми на кнопку Вызов пользовательской функции (Добавить столбец — вызвать пользовательскую функцию) и установите его единственный аргумент – столбец Column1 с годами:
После нажатия на OK наша функция fxYear импорт будет работать поочередно для каждого года и мы получим столбец, где каждая ячейка будет содержать таблицу с датами нерабочих дней (содержимое таблицы хорошо видно, если нажать на фон ячейки рядом с слово Настольные):
Осталось развернуть содержимое вложенных таблиц, щелкнув по значку с двойными стрелками в заголовке столбца. Финики (галочка Использовать исходное имя столбца в качестве префикса его можно удалить):
… и после нажатия OK получаем то, что хотели — список всех праздников с 2013 по текущий год:
Первый, уже ненужный столбец, можно удалить, а для второго задать тип данных даты (Дата) в раскрывающемся списке в заголовке столбца:
Сам запрос можно переименовать как-нибудь более значимо, чем Запрос1 а затем загрузить результаты на лист в виде динамической «умной» таблицы с помощью команды закрыть и скачать таб Главная (Главная страница — Закрыть и загрузить):
Обновить созданный календарь в дальнейшем можно, щелкнув правой кнопкой мыши по таблице или запросив в правой панели команду Обновить и сохранить. Или воспользуйтесь кнопкой Обновить все таб Данные (Дата — Обновить все) или сочетание клавиш Ctrl+другой+F5.
Это все.
Теперь вам больше никогда не придется тратить время и мысли на поиск и обновление списка праздников – теперь у вас есть «вечный» производственный календарь. В любом случае, пока авторы сайта http://xmlcalendar.ru/ поддерживают свое детище, что, надеюсь, будет еще очень и очень долго (еще раз им спасибо!).
- Импортируйте курс биткойнов, чтобы преуспеть из Интернета с помощью Power Query.
- Поиск следующего рабочего дня с помощью функции РАБДЕНЬ
- Нахождение пересечения интервалов дат