зміст
Виробничий календар, тобто список дат, де відповідно відзначені всі офіційні робочі та святкові дні – річ вкрай необхідна для будь-якого користувача Microsoft Excel. На практиці без цього не обійтися:
- в бухгалтерських розрахунках (зарплата, стаж, відпустки…)
- в логістиці – для правильного визначення термінів доставки з урахуванням вихідних і свят (пам’ятаєте класичне «давай після свят?»)
- в проектному менеджменті – для правильної оцінки термінів, враховуючи, знову ж таки, робочі-неробочі дні
- будь-яке використання таких функцій, як Робочий день (РОБОЧИЙ ДЕНЬ) 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 перейдіть на вкладку дані (Або PowerQueryякщо ви встановили його як окремий додаток) і виберіть З Інтернету (З Інтернету). У вікні, що відкриється, вставте посилання на відповідний рік, скопійоване з сайту:
Натиснувши на OK з'являється вікно попереднього перегляду, в якому потрібно натиснути кнопку Перетворення даних (Перетворення даних) or Щоб змінити дані (Редагувати дані) і ми потрапимо у вікно редактора запитів Power Query, де продовжимо роботу з даними:
Відразу ви можете безпечно видалити на правій панелі Параметри запиту (Налаштування запиту) крок модифікований тип (Змінений тип) Він нам не потрібен.
Таблиця в графі «Святкові» містить коди та опис неробочих днів – ви можете переглянути її вміст, двічі «перегорнувши» її, натиснувши на зелене слово таблиця:
Щоб повернутися назад, вам доведеться видалити на правій панелі всі кроки, які з’явилися назад Source (Джерело).
Друга таблиця, доступ до якої доступна подібним чином, містить саме те, що нам потрібно – дати всіх неробочих днів:
Залишилося обробити цю плиту, а саме:
1. Фільтруйте лише дати свят (тобто одиниці) за другим стовпцем Атрибут: t
2. Видалити всі стовпці, крім першого, клацнувши правою кнопкою миші на заголовку першого стовпця та вибравши команду Видалити інші стовпці (Видалити інші стовпці):
3. Розділіть перший стовпець крапкою окремо для місяця та дня за допомогою команди Розділити стовпець – роздільником таб Перетворення (Перетворення — Розділити стовпець — За роздільником):
4. І, нарешті, створіть обчислюваний стовпець зі звичайними датами. Для цього на табл Додавання колонки натисніть на кнопку Спеціальний стовпець (Додати стовпець — спеціальний стовпець) і введіть наступну формулу у вікні, що з’явиться:
=#від(2020, [#»Атрибут:d.1″], [#»Атрибут:d.2″])
Тут оператор #date має три аргументи: рік, місяць і день відповідно. Після натискання на OK ми отримуємо потрібний стовпець із звичайними датами вихідних і видаляємо решту стовпців, як у кроці 2
Крок 2. Перетворення запиту на функцію
Наше наступне завдання — перетворити створений для 2020 року запит в універсальну функцію для будь-якого року (номер року буде її аргументом). Для цього робимо наступне:
1. Розгортання (якщо ще не розгорнуто) панелі Запити (Запити) ліворуч у вікні Power Query:
2. Після перетворення запиту на функцію можливість бачити кроки, з яких складається запит, і легко їх редагувати, на жаль, зникає. Тому є сенс зробити копію нашого запиту і вже з нею погратися, а оригінал залишити про запас. Для цього клацніть правою кнопкою миші на лівій панелі запиту нашого календаря та виберіть команду Дублювати.
Ще раз клацнувши правою кнопкою миші на отриманій копії calendar(2), ви виберете команду Rename (Перейменувати) і введіть нове ім’я – нехай це буде, наприклад, fxYear:
3. Вихідний код запиту відкриваємо на внутрішній мові Power Query (вона коротко називається «М») за допомогою команди Розширений редактор таб Розгляд(Перегляд — розширений редактор) і внесіть там невеликі зміни, щоб перетворити наш запит на функцію для будь-якого року.
Це було:
Після:
Якщо вас цікавлять подробиці, то сюди:
- (рік як число)=> – ми оголошуємо, що наша функція матиме один числовий аргумент – змінну рік
- Вставлення змінної рік на веб-посилання в кроці Source. Оскільки Power Query не дозволяє склеювати числа та текст, ми перетворюємо номер року на текст на льоту за допомогою функції Number.ToText
- Підставляємо змінну року на 2020 на передостанньому кроці #”Додано спеціальний об’єкт«, де ми сформували дату з фрагментів.
Натиснувши на обробка наш запит стає функцією:
Крок 3. Імпортуйте календарі на всі роки
Залишилося зробити останній головний запит, який вивантажить дані за всі доступні роки та зведе всі отримані святкові дати в одну таблицю. Для цього:
1. Клацаємо в лівій панелі запиту на сірому порожньому місці правою кнопкою миші і вибираємо послідовно Новий запит – Інші джерела – Порожній запит (Новий запит — З інших джерел — Пустий запит):
2. Нам потрібно сформувати список усіх років, для яких ми будемо запитувати календарі, тобто 2013, 2014 … 2020. Для цього в рядку формул порожнього запиту, який з’явився, введіть команду:
Структура:
={ЧислоA..ЧислоB}
… у Power Query створює список цілих чисел від A до B. Наприклад, вираз
={1..5}
… створить список із 1,2,3,4,5.
Ну а щоб не прив’язуватися жорстко до 2020 року, використовуємо функцію DateTime.LocalNow() – аналог функції Excel СЬОГОДНІ (СЬОГОДНІ) в Power Query – і витягти з нього, в свою чергу, поточний рік функцією Дата.Рік.
3. Отриманий набір років, хоч і виглядає цілком адекватно, є не таблицею для Power Query, а спеціальним об’єктом – список (Список). Але перетворити його на таблицю не проблема: просто натисніть кнопку До столу (До столу) у верхньому лівому куті:
4. Фінішна лінія! Застосування функції, яку ми створили раніше fxYear до отриманого списку років. Для цього на табл Додавання колонки натисніть кнопку Виклик спеціальної функції (Додати стовпець — викликати спеціальну функцію) і встановити його єдиний аргумент – стовпець Column1 за роки:
Натиснувши на OK наша функція fxYear імпорт буде працювати по черзі для кожного року і ми отримаємо стовпець, де кожна комірка буде містити таблицю з датами неробочих днів (вміст таблиці добре видно, якщо клацнути на фоні комірки поруч із слово таблиця):
Залишається розгорнути вміст вкладених таблиць, натиснувши на іконку з подвійними стрілками в заголовку стовпця Дати (поставте галочку Використовуйте оригінальну назву стовпця як префікс його можна видалити):
… і після натискання OK отримуємо те, що хотіли – список усіх свят з 2013 по поточний рік:
Перший, вже непотрібний стовпець, можна видалити, а для другого задати тип даних дата (Дата) у спадному списку в заголовку стовпця:
Сам запит можна перейменувати на щось більш значуще, ніж Запит1 а потім завантажити результати на аркуш у вигляді динамічної «розумної» таблиці за допомогою команди закрити і завантажити таб Головна (Додому — Закрити та завантажити):
Ви можете оновити створений календар у майбутньому, клацнувши правою кнопкою миші на таблиці або запит на правій панелі за допомогою команди Update & Save. Або скористайтеся кнопкою Оновити все таб дані (Дата — оновити все) або комбінацію клавіш Ctrl+інший+F5.
Це все.
Тепер вам більше ніколи не доведеться витрачати час і зусилля на пошук і оновлення списку свят – тепер у вас є «вічний» виробничий календар. У всякому разі, поки автори сайту http://xmlcalendar.ru/ підтримують своє дітище, яке, сподіваюся, буде ще дуже-дуже довго (ще раз їм спасибі!).
- Імпортуйте курс біткойнів, щоб досягти успіху з Інтернету за допомогою Power Query
- Пошук наступного робочого дня за допомогою функції WORKDAY
- Знаходження перетину інтервалів дат