Obsah
Výrobný kalendár, teda zoznam dátumov, kde sú podľa toho označené všetky oficiálne pracovné dni a sviatky – absolútne nevyhnutná vec pre každého používateľa Microsoft Excel. V praxi sa bez neho nezaobídete:
- pri účtovných výpočtoch (plat, odpracované roky, dovolenka...)
- v logistike – pre správne určenie dodacích lehôt s prihliadnutím na víkendy a sviatky (pamätáte na klasické „prísť po sviatkoch?“)
- v projektovom riadení – pre správny odhad termínov s prihliadnutím opäť na pracovné-nepracovné dni
- akékoľvek použitie funkcií ako napr PRACOVNÝ DEŇ (PRACOVNÝ DEŇ) or ČISTÍ ROBOTNÍCI (NETWORKDAYS), pretože ako argument vyžadujú zoznam sviatkov
- pri používaní funkcií Time Intelligence (napríklad TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR atď.) v doplnkoch Power Pivot a Power BI
- … atď. atď. – veľa príkladov.
Pre tých, ktorí pracujú v podnikových ERP systémoch ako 1C alebo SAP, je to jednoduchšie, keďže je v nich zabudovaný produkčný kalendár. Ale čo používatelia Excelu?
Takýto kalendár si môžete, samozrejme, viesť aj manuálne. Potom ho však budete musieť aktualizovať aspoň raz za rok (alebo ešte častejšie, ako v „veselom“ roku 2020), pričom opatrne zadáte všetky víkendy, presuny a dni pracovného pokoja, ktoré vymyslela naša vláda. A potom tento postup opakujte každý ďalší rok. Nuda.
Čo tak sa trochu vyblázniť a spraviť si „večný“ fabrický kalendár v Exceli? Takú, ktorá sa sama aktualizuje, berie dáta z internetu a generuje vždy aktuálny zoznam nepracovných dní pre následné použitie pri akýchkoľvek výpočtoch? Lákavé?
V skutočnosti to nie je vôbec ťažké.
Zdroj údajov
Hlavnou otázkou je, kde získať údaje? Pri hľadaní vhodného zdroja som prešiel niekoľkými možnosťami:
- Pôvodné vyhlášky sú zverejnené na webovej stránke vlády vo formáte PDF (tu je napríklad jedna z nich) a okamžite miznú – nedajú sa z nich vytiahnuť užitočné informácie.
- Na prvý pohľad sa zdala lákavá možnosť „Otvorený dátový portál federácie“, kde existuje zodpovedajúci súbor údajov, ale po bližšom preskúmaní sa všetko ukázalo ako smutné. Stránka je strašne nepohodlná na import do Excelu, technická podpora nereaguje (samoizolovaná?) a samotné dáta sú tam dlhodobo neaktuálne – produkčný kalendár na rok 2020 bol naposledy aktualizovaný v novembri 2019 (hanba!) a , samozrejme, neobsahuje napríklad náš „koronavírus“ a „hlasovací“ víkend 2020.
Rozčarovaný z oficiálnych zdrojov som začal kopať tie neoficiálne. Na internete je ich veľa, no väčšina z nich je opäť úplne nevhodná na import do Excelu a rozdáva produkčný kalendár v podobe krásnych obrázkov. Ale to nie je pre nás, aby sme to vešali na stenu, však?
A v procese hľadania bola náhodne objavená úžasná vec - stránka http://xmlcalendar.ru/
Bez zbytočných „okolkov“, jednoduchá, ľahká a rýchla stránka, nabrúsená na jednu úlohu – dať každému produkčný kalendár na želaný rok vo formáte XML. Výborne!
Ak zrazu nič nepoznáte, potom je XML textový formát s obsahom označeným špeciál
Pre každý prípad som oslovil autorov stránky a tí mi potvrdili, že stránka existuje 7 rokov, údaje na nej sú neustále aktualizované (majú na to dokonca pobočku na githube) a nechystajú sa ju zavrieť. A vôbec mi nevadí, že si z neho vy aj ja načítavame dáta pre akýkoľvek náš projekt a výpočty v Exceli. Je zadarmo. Je pekné vedieť, že ešte stále existujú takíto ľudia! Rešpekt!
Zostáva načítať tieto údaje do Excelu pomocou doplnku Power Query (pre verzie Excel 2010-2013 si ho možno stiahnuť zadarmo z webu Microsoftu a vo verziách Excelu 2016 a novších je už štandardne zabudovaný ).
Logika akcií bude nasledovná:
- Požiadame o stiahnutie údajov zo stránky na jeden rok
- Premena našej požiadavky na funkciu
- Túto funkciu aplikujeme na zoznam všetkých dostupných rokov od roku 2013 až po aktuálny rok – a získame „večný“ kalendár výroby s automatickou aktualizáciou. Voila!
Krok 1. Importujte kalendár na jeden rok
Najprv načítajte produkčný kalendár na ľubovoľný rok, napríklad na rok 2020. Ak to chcete urobiť, prejdite v Exceli na kartu dátum (Alebo mocenský dotazak ste ho nainštalovali ako samostatný doplnok) a vyberte Z internetu (Z webu). V okne, ktoré sa otvorí, prilepte odkaz na príslušný rok skopírovaný z lokality:
Po kliknutí na OK zobrazí sa okno náhľadu, v ktorom musíte kliknúť na tlačidlo Previesť údaje (Transformácia údajov) or Ak chcete zmeniť údaje (Upraviť údaje) a dostaneme sa do okna editora dotazov Power Query, kde budeme pokračovať v práci s údajmi:
Ihneď môžete bezpečne odstrániť v pravom paneli Parametre požiadavky (Nastavenia dopytu) krok upravený typ (Zmenený typ) Nepotrebujeme ho.
Tabuľka v stĺpci sviatky obsahuje kódy a popisy dní pracovného pokoja – jej obsah uvidíte tak, že ju dvakrát „prepadnete“ kliknutím na zelené slovo Tabuľka:
Ak sa chcete vrátiť späť, budete musieť v pravom paneli vymazať všetky kroky, ktoré sa zobrazili späť zdroj (Zdroj).
Druhá tabuľka, ku ktorej sa dá dostať podobným spôsobom, obsahuje presne to, čo potrebujeme – dátumy všetkých dní pracovného pokoja:
Zostáva spracovať tento tanier, a to:
1. Podľa druhého stĺpca filtrujte iba dátumy sviatkov (tj jedničky). Atribút:t
2. Odstrániť všetky stĺpce okrem prvého – kliknutím pravým tlačidlom myši na hlavičku prvého stĺpca a výberom príkazu Odstráňte ostatné stĺpce (Odstrániť ďalšie stĺpce):
3. Rozdeľte prvý stĺpec bodkami oddelene pre mesiac a deň pomocou príkazu Rozdeliť stĺpec – podľa oddeľovača pútko Premena (Transformácia – Rozdeliť stĺpec – Podľa oddeľovača):
4. A nakoniec vytvorte vypočítaný stĺpec s normálnymi dátumami. Ak to chcete urobiť, na karte Pridanie stĺpca kliknite na tlačidlo Vlastný stĺpec (Pridať stĺpec – vlastný stĺpec) a do zobrazeného okna zadajte nasledujúci vzorec:
=#datované(2020, [#»Atribút:d.1″], [#»Atribút:d.2″])
Tu má operátor #date tri argumenty: rok, mesiac a deň. Po kliknutí na OK získame požadovaný stĺpec s bežnými víkendovými dátumami a zvyšné stĺpce odstránime ako v kroku 2
Krok 2. Premena požiadavky na funkciu
Našou ďalšou úlohou je previesť dotaz vytvorený pre rok 2020 na univerzálnu funkciu pre ľubovoľný rok (argumentom bude číslo roku). Ak to chcete urobiť, postupujte takto:
1. Rozbalenie (ak ešte nie je rozbalený) panel dotazy (Dopyty) vľavo v okne Power Query:
2. Po konverzii požiadavky na funkciu sa, žiaľ, stratí možnosť vidieť kroky tvoriace požiadavku a jednoducho ich upravovať. Preto má zmysel urobiť si kópiu našej požiadavky a už sa s ňou vyšantiť a originál nechať v zálohe. Ak to chcete urobiť, kliknite pravým tlačidlom myši v ľavom paneli na našu žiadosť o kalendár a vyberte príkaz Duplikovať.
Opätovným kliknutím pravým tlačidlom myši na výslednú kópiu kalendára(2) vyberiete príkaz premenovať (Premenovať) a zadajte nový názov – nech je to napr. fxYear:
3. Zdrojový kód dotazu otvoríme v internom jazyku Power Query (výstižne sa nazýva „M“) pomocou príkazu Pokročilý editor pútko preskúmanie(Zobraziť — Pokročilý editor) a urobiť tam malé zmeny, aby sme našu požiadavku premenili na funkciu na ktorýkoľvek rok.
To bolo:
Po:
Ak vás zaujímajú podrobnosti, tak tu:
- (rok ako číslo)=> – deklarujeme, že naša funkcia bude mať jeden číselný argument – premennú rok
- Prilepenie premennej rok na webový odkaz v kroku zdroj. Keďže Power Query neumožňuje spájať čísla a text, konvertujeme číslo roku na text za behu pomocou funkcie Number.ToText
- Premennú rok na rok 2020 dosadíme v predposlednom kroku #”Pridaný vlastný objekt«, kde sme z fragmentov tvorili dátum.
Po kliknutí na úprava naša požiadavka sa stáva funkciou:
Krok 3. Importujte kalendáre pre všetky roky
Zostáva posledný hlavný dotaz, ktorý nahrá dáta za všetky dostupné roky a všetky prijaté dátumy sviatkov pridá do jednej tabuľky. Pre to:
1. Klikneme do ľavého panela dotazu do sivého prázdneho priestoru pravým tlačidlom myši a postupne vyberáme Nová požiadavka – Iné zdroje – Prázdna požiadavka (Nový dopyt – Z iných zdrojov – Prázdny dopyt):
2. Potrebujeme vygenerovať zoznam všetkých rokov, pre ktoré budeme požadovať kalendáre, teda 2013, 2014 … 2020. Za týmto účelom do riadku vzorcov prázdneho dotazu, ktorý sa zobrazí, zadajte príkaz:
štruktúra:
={NumberA..NumberB}
… v Power Query vygeneruje zoznam celých čísel od A do B. Napríklad výraz
={1..5}
… by vytvoril zoznam 1,2,3,4,5.
No, aby sme neboli pevne viazaní na rok 2020, používame funkciu DateTime.LocalNow() – analóg funkcie Excel DNES (DNES) v Power Query – a následne z neho pomocou funkcie extrahujte aktuálny rok Dátum.Rok.
3. Výsledná množina rokov, hoci vyzerá celkom adekvátne, nie je tabuľkou pre Power Query, ale špeciálnym objektom – zoznam (zoznam). Ale previesť ho na tabuľku nie je problém: stačí kliknúť na tlačidlo Na stôl (K stolu) v ľavom hornom rohu:
4. Cieľová čiara! Použitie funkcie, ktorú sme vytvorili predtým fxYear do výsledného zoznamu rokov. Ak to chcete urobiť, na karte Pridanie stĺpca stlač tlačidlo Zavolajte vlastnú funkciu (Pridať stĺpec – vyvolať vlastnú funkciu) a nastavte jeho jediný argument – stĺpec Column1 v priebehu rokov:
Po kliknutí na OK naša funkcia fxYear import bude fungovať postupne pre každý rok a dostaneme stĺpec, kde každá bunka bude obsahovať tabuľku s dátumami dní pracovného pokoja (obsah tabuľky je dobre viditeľný, ak kliknete na pozadie bunky vedľa slovo Tabuľka):
Zostáva rozbaliť obsah vnorených tabuliek kliknutím na ikonu s dvojitými šípkami v hlavičke stĺpca Termíny (zaškrtnúť Ako predponu použite pôvodný názov stĺpca dá sa odstrániť):
...a po kliknutí na OK dostali sme to, čo sme chceli – zoznam všetkých sviatkov od roku 2013 po aktuálny rok:
Prvý, už nepotrebný stĺpec, je možné vymazať a pre druhý nastaviť typ údajov dáta (Dátum) v rozbaľovacom zozname v záhlaví stĺpca:
Samotný dotaz možno premenovať na niečo zmysluplnejšie ako Žiadosť1 a potom pomocou príkazu nahrajte výsledky do hárka vo forme dynamickej „inteligentnej“ tabuľky zavrieť a stiahnuť pútko Domov (Domov – zavrieť a načítať):
Vytvorený kalendár môžete v budúcnosti aktualizovať kliknutím pravým tlačidlom myši na tabuľku alebo dotazom v pravej časti príkazu Aktualizovať a uložiť. Alebo použite tlačidlo Obnoviť všetko pútko dátum (Dátum – Obnoviť všetko) alebo klávesová skratka ctrl+ostatné+F5.
To je všetko.
Teraz už nikdy nebudete musieť strácať čas a premýšľať nad hľadaním a aktualizovaním zoznamu sviatkov – teraz máte „večný“ kalendár výroby. V každom prípade, pokiaľ autori stránky http://xmlcalendar.ru/ podporia svojich potomkov, čo, dúfam, bude ešte veľmi, veľmi dlho (ešte raz im ďakujem!).
- Importujte bitcoinovú sadzbu do excelu z internetu cez Power Query
- Vyhľadanie nasledujúceho pracovného dňa pomocou funkcie WORKDAY
- Nájdenie priesečníka dátumových intervalov