Съдържание
Производствен календар, т.е. списък с дати, където са отбелязани съответно всички официални работни дни и празници – нещо абсолютно необходимо за всеки потребител на Microsoft Excel. На практика не можете без него:
- в счетоводните изчисления (заплата, трудов стаж, отпуски…)
- в логистиката – за правилното определяне на времето за доставка, като се вземат предвид почивните дни и празниците (помните класическото „хайде след празниците?“)
- в управлението на проекти – за правилна оценка на сроковете, като се вземат предвид отново работни и неработни дни
- всяко използване на функции като ДЕЛНИК (ДЕЛНИК) or ЧИСТИ РАБОТНИЦИ (МРЕЖИ ДНИ), защото изискват списък с празници като аргумент
- при използване на функции за Time Intelligence (като TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR и др.) в Power Pivot и Power BI
- … и т.н. и т.н. – много примери.
По-лесно е за тези, които работят в корпоративни ERP системи като 1C или 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 отидете на раздела Дата (или Запитване за захранванеако сте го инсталирали като отделна добавка) и изберете От Интернет (От мрежата). В прозореца, който се отваря, поставете връзката към съответната година, копирана от сайта:
След като кликнете върху OK появява се прозорец за предварителен преглед, в който трябва да щракнете върху бутона Конвертиране на данни (Трансформиране на данни) or За промяна на данните (Редактиране на данни) и ще стигнем до прозореца на редактора на заявки на Power Query, където ще продължим да работим с данните:
Веднага можете безопасно да изтриете в десния панел Параметри на заявката (Настройки на заявката) стъпка модифициран тип (Променен тип) Не ни трябва той.
Таблицата в колона празници съдържа кодове и описания на неработни дни – можете да видите съдържанието й, като я „пропуснете“ два пъти, като щракнете върху зелената дума Маса:
За да се върнете назад, ще трябва да изтриете в десния панел всички стъпки, които са се появили назад източник (Източник).
Втората таблица, достъпна по подобен начин, съдържа точно това, от което се нуждаем – датите на всички неработни дни:
Остава да обработим тази плоча, а именно:
1. Филтрирайте само празнични дати (т.е. такива) по втората колона Атрибут: t
2. Изтрийте всички колони с изключение на първата – като щракнете с десния бутон върху заглавието на първата колона и изберете командата Изтрийте други колони (Премахване на други колони):
3. Разделете първата колона по точка отделно за месец и ден с команда Разделяне на колона – чрез разделител етикет Трансформация (Трансформиране — Разделяне на колона — Чрез разделител):
4. И накрая създайте изчислена колона с нормални дати. За да направите това, в раздела Добавяне на колона щракнете върху бутона Персонализирана колона (Добавяне на колона — Персонализирана колона) и въведете следната формула в прозореца, който се показва:
=#с дата(2020 г., [#»Атрибут:d.1″], [#»Атрибут:d.2″])
Тук операторът #date има три аргумента: съответно година, месец и ден. След като щракнете върху OK получаваме необходимата колона с нормални дати през уикенда и изтриваме останалите колони, както в стъпка 2
Стъпка 2. Превръщане на заявката във функция
Следващата ни задача е да преобразуваме заявката, създадена за 2020 г., в универсална функция за всяка година (номерът на годината ще бъде нейният аргумент). За целта правим следното:
1. Разширяване (ако вече не е разширен) на панела Запитвания (Запитвания) отляво в прозореца на Power Query:
2. След преобразуване на заявката във функция, възможността да видите стъпките, които съставят заявката, и лесно да ги редактирате, за съжаление, изчезва. Затова има смисъл да направим копие на нашата заявка и да се забавляваме вече с нея, а оригинала да оставим в резерв. За да направите това, щракнете с десния бутон в левия панел върху нашата заявка за календар и изберете командата Дублиране.
Щракването с десния бутон отново върху полученото копие на calendar(2) ще избере командата Преименувам (Преименуване) и въведете ново име – нека бъде напр. fxYear:
3. Отваряме изходния код на заявката на вътрешния език на Power Query (той се нарича накратко „M“), като използваме командата Разширен редактор етикет Оценки на посетители(Преглед — Разширен редактор) и да направим малки промени там, за да превърнем нашата заявка във функция за всяка година.
Беше:
След:
Ако се интересувате от подробности, тогава тук:
- (година като число)=> – декларираме, че нашата функция ще има един числов аргумент – променлива година
- Поставяне на променливата година към уеб връзка в стъпка източник. Тъй като 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 и след това качете резултатите в листа под формата на динамична „интелигентна“ таблица с помощта на командата затворете и изтеглете етикет Начало (Начало — затвори и зареди):
Можете да актуализирате създадения календар в бъдеще, като щракнете с десния бутон върху таблицата или направите заявка в десния панел чрез командата Актуализиране и запазване. Или използвайте бутона Опресни всички етикет Дата (Дата — Обновяване на всички) или клавишна комбинация Ctrl+Друг+F5.
Това е всичко.
Сега вече никога няма да се налага да губите време и гориво за мисли в търсене и актуализиране на списъка с празници – сега имате „вечен“ производствен календар. Във всеки случай, стига авторите на сайта http://xmlcalendar.ru/ да подкрепят своето потомство, което, надявам се, ще бъде за много, много дълго време (благодаря им отново!).
- Импортирайте курса на биткойни, за да се отличавате от интернет чрез Power Query
- Намиране на следващия работен ден с помощта на функцията WORKDAY
- Намиране на пресечната точка на интервали от дати