содржина
Календар за производство, односно листа на датуми, каде соодветно се означени сите официјални работни денови и празници – апсолутно неопходна работа за секој корисник на Microsoft Excel. Во пракса, не можете без него:
- во сметководствените пресметки (плата, стаж, годишен одмор...)
- во логистика - за правилно одредување на времето на испорака, земајќи ги предвид викендите и празниците (запомнете го класичниот „ајде после празниците?“)
- во управувањето со проекти – за правилна проценка на термините, земајќи ги предвид, повторно, работните-неработни денови
- било каква употреба на функции како РАБОТЕН ДЕН (РАБОТЕН ДЕН) or ЧИСТИ РАБОТНИЦИ (NETWORKDAYS), бидејќи бараат список на празници како аргумент
- кога користите функции на 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 година па до тековната година – и добиваме „вечен“ производствен календар со автоматско ажурирање. Voila!
Чекор 1. Увезете календар за една година
Прво, вчитајте го календарот за производство за која било година, на пример, за 2020 година. За да го направите ова, во Excel, одете на картичката податоци (Или Барање за напојувањеако сте го инсталирале како посебен додаток) и одберете Од Интернет (Од веб). Во прозорецот што се отвора, залепете ја врската до соодветната година, копирана од страницата:
По кликнување на OK се појавува прозорец за преглед, во кој треба да кликнете на копчето Конвертирај податоци (Трансформирајте податоци) or За промена на податоците (Уреди податоци) и ќе стигнеме до прозорецот на уредувачот на барањето Power Query, каде што ќе продолжиме да работиме со податоците:
Веднаш можете безбедно да избришете во десниот панел Параметри за барање (Поставки за барање) чекор модифициран тип (Променет тип) Тој не ни треба.
Табелата во колоната празници содржи шифри и описи на неработни денови - можете да ја видите нејзината содржина со двапати „пропаѓање“ со кликнување на зелениот збор Табела:
За да се вратите назад, ќе треба да ги избришете во десниот панел сите чекори на кои се појавија назад извор (Извор).
Втората табела, до која може да се пристапи на сличен начин, го содржи токму она што ни треба – датумите на сите неработни денови:
Останува да се обработи оваа плоча, имено:
1. Филтрирајте само датуми за празници (т.е. оние) според втората колона Атрибут: т
2. Избришете ги сите колони освен првата - со десен клик на насловот на првата колона и избирање на командата Избришете ги другите колони (Отстранете ги другите колони):
3. Поделете ја првата колона по точка одделно за месец и ден со команда Сплит колона - со разграничувач табот Трансформација (Трансформација - Сплит колона - по разграничувач):
4. И конечно креирајте пресметана колона со нормални датуми. За да го направите ова, на јазичето Додавање колона кликнете на копчето Прилагодена колона (Додај колона - приспособена колона) и внесете ја следната формула во прозорецот што се појавува:
=#датиран(2020 година, [#»Атрибут:д.1″], [#»Атрибут:д.2″])
Овде, операторот #date има три аргументи: година, месец и ден, соодветно. Откако ќе кликнете на OK ја добиваме потребната колона со нормални датуми за викенд и ги бришеме преостанатите колони како во чекор 2
Чекор 2. Претворање на барањето во функција
Нашата следна задача е да го претвориме барањето создадено за 2020 година во универзална функција за која било година (бројот на годината ќе биде негов аргумент). За да го направите ова, го правиме следново:
1. Проширување (ако веќе не е проширено) на панелот Прашања (Прашања) лево во прозорецот Power Query:
2. По конвертирање на барањето во функција, можноста да се видат чекорите што го сочинуваат барањето и лесно да се уредуваат, за жал, исчезнува. Затоа, има смисла да се направи копија од нашето барање и да се забавуваме веќе со него, а оригиналот да го оставиме во резерва. За да го направите ова, кликнете со десното копче во левиот панел на нашето барање за календар и изберете ја командата Duplicate.
Со повторно десен клик на добиената копија од календарот(2) ќе се избере командата Преименувате (Преименувај) и внесете ново име - нека биде, на пример, fxГодина:
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 година, ја користиме функцијата DateTime.LocalNow() – аналог на функцијата Excel ДЕНЕС (ДЕНЕС) во Power Query – и извлечете од него, пак, тековната година по функцијата Датум.Година.
3. Добиениот сет на години, иако изгледа сосема адекватен, не е табела за Power Query, туку посебен објект - листа (Список). Но, конвертирањето во табела не е проблем: само кликнете на копчето На маса (До маса) во горниот лев агол:
4. Завршна линија! Примена на функцијата што ја создадовме претходно fxГодина на добиената листа на години. За да го направите ова, на јазичето Додавање колона притисни го копчето Повикајте приспособена функција (Додај колона - повикај ја прилагодената функција) и го постави нејзиниот единствен аргумент – колоната Column1 преку годините:
По кликнување на OK нашата функција fxГодина увозот ќе работи наизменично за секоја година и ќе добиеме колона каде што секоја ќелија ќе содржи табела со датумите на неработните денови (содржината на табелата е јасно видлива ако кликнете во позадината на ќелијата до зборот Табела):
Останува да се прошири содржината на вгнездените табели со кликнување на иконата со двојни стрелки во заглавието на колоната Датуми (штиклирајте Користете го оригиналното име на колоната како префикс може да се отстрани):
… и откако ќе кликнете на OK го добиваме она што го сакавме – список на сите празници од 2013 до тековната година:
Првата, веќе непотребна колона, може да се избрише, а за втората да го поставите типот на податоци датум (Датум) во паѓачката листа во насловот на колоната:
Самото барање може да се преименува на нешто позначајно од Барање 1 а потоа прикачете ги резултатите на листот во форма на динамична „паметна“ табела користејќи ја командата затворете и преземете табот Почетна (Дома - Затвори и вчитај):
Можете да го ажурирате креираниот календар во иднина со десен клик на табелата или барање во десниот панел преку командата Ажурирајте и зачувајте. Или користете го копчето Освежете ги сите табот податоци (Датум - Освежи ги сите) или кратенка на тастатура Ctrl+Alt+F5.
Тоа е се.
Сега никогаш повеќе не треба да губите време и енергија за размислување барајќи и ажурирајќи ја листата на празници - сега имате „вечен“ календар за производство. Во секој случај, сè додека авторите на страницата http://xmlcalendar.ru/ го поддржуваат своето потомство, што, се надевам, ќе биде многу, многу долго (повторно благодарение на нив!).
- Увезете стапка на биткоин за ексел од интернет преку Power Query
- Наоѓање на следниот работен ден со помош на функцијата WORKDAY
- Наоѓање на пресекот на временски интервали