Obsah
Výrobní kalendář, tedy seznam termínů, kde jsou podle toho označeny všechny oficiální pracovní dny a svátky – naprosto nezbytná věc pro každého uživatele Microsoft Excel. V praxi se bez něj neobejdete:
- v účetních výpočtech (plat, odsloužená doba, dovolená…)
- v logistice – pro správné stanovení dodacích lhůt s přihlédnutím k víkendům a svátkům (pamatujete na klasické „přijeďte po prázdninách?“)
- v projektovém řízení – pro správný odhad termínů s přihlédnutím opět k pracovním-nepracovním dnům
- jakékoli použití funkcí jako PRACOVNÍ DEN (PRACOVNÍ DEN) or ČISTÍ PRACOVNÍCI (NETWORKDAYS), protože jako argument vyžadují seznam svátků
- při používání funkcí Time Intelligence (jako TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR atd.) v Power Pivot a Power BI
- … atd. atd. – mnoho příkladů.
Pro ty, kteří pracují ve firemních ERP systémech jako je 1C nebo SAP, je to jednodušší, protože je v nich zabudován produkční kalendář. Ale co uživatelé Excelu?
Takový kalendář si můžete samozřejmě vést ručně. Ale pak jej budete muset aktualizovat alespoň jednou ročně (nebo ještě častěji, jako v „veselém“ roce 2020), pečlivě zadávat všechny víkendy, převody a dny pracovního klidu vymyšlené naší vládou. A pak tento postup opakujte každý další rok. Nuda.
Co takhle se trochu zbláznit a udělat si „věčný“ tovární kalendář v Excelu? Takový, který se sám aktualizuje, bere data z internetu a generuje vždy aktuální seznam nepracovních dnů pro následné použití v jakýchkoli výpočtech? Lákavý?
Udělat to ve skutečnosti není vůbec obtížné.
Zdroj dat
Hlavní otázkou je, kde získat data? Při hledání vhodného zdroje jsem prošel několika možnostmi:
- Původní vyhlášky jsou zveřejněny na webu vlády ve formátu PDF (zde například jedna z nich) a okamžitě zmizí – užitečné informace z nich nelze vytáhnout.
- Lákavou možností se na první pohled zdál „Otevřený datový portál federace“, kde existuje odpovídající soubor dat, ale po bližším prozkoumání se vše ukázalo jako smutné. Stránka je strašně nepohodlná pro import do Excelu, technická podpora nereaguje (sebeizolovaná?) a samotná data jsou tam dlouhodobě zastaralá – produkční kalendář na rok 2020 byl naposledy aktualizován v listopadu 2019 (ostuda!) a , samozřejmě neobsahuje například náš „koronavirus“ a „hlasovací“ víkend 2020.
Zklamán oficiálními zdroji jsem začal kopat ty neoficiální. Na internetu je jich mnoho, ale většina z nich je opět zcela nevhodná pro import do Excelu a rozdávají produkční kalendář v podobě krásných obrázků. Ale není na nás, abychom si to pověsili na zeď, že?
A v procesu hledání byla náhodně objevena úžasná věc - stránka http://xmlcalendar.ru/
Jednoduchý, lehký a rychlý web bez zbytečných „zbytečných věcí“, vybroušený pro jediný úkol – dát každému produkční kalendář na požadovaný rok ve formátu XML. Vynikající!
Pokud náhle nevíte, pak je XML textový formát s obsahem označeným speciálním
Pro jistotu jsem kontaktoval autory stránek a ti potvrdili, že stránka existuje 7 let, data na ní jsou neustále aktualizována (mají na to dokonce pobočku na githubu) a nechystají ji zavřít. A vůbec mi nevadí, že z něj vy i já načítáme data pro jakýkoli náš projekt a výpočty v Excelu. Je zdarma. Je hezké vědět, že ještě existují takoví lidé! Úcta!
Zbývá načíst tato data do Excelu pomocí doplňku Power Query (pro verze Excelu 2010-2013 jej lze zdarma stáhnout z webu Microsoftu a ve verzích Excelu 2016 a novějších je již ve výchozím nastavení integrován ).
Logika akcí bude následující:
- Požádáme o stažení dat z webu po dobu jednoho roku
- Přeměna našeho požadavku na funkci
- Tuto funkci aplikujeme na seznam všech dostupných roků počínaje rokem 2013 až do aktuálního roku – a získáme „věčný“ produkční kalendář s automatickou aktualizací. Voila!
Krok 1. Import kalendáře na jeden rok
Nejprve načtěte produkční kalendář pro libovolný jeden rok, například pro rok 2020. Chcete-li to provést, přejděte v Excelu na kartu Data (nebo Dotaz na napájenípokud jste jej nainstalovali jako samostatný doplněk) a vyberte Z internetu (Z webu). V okně, které se otevře, vložte odkaz na odpovídající rok zkopírovaný z webu:
Po kliknutí na tlačítko OK zobrazí se okno náhledu, ve kterém musíte kliknout na tlačítko Převést data (Transformace dat) or Chcete-li změnit údaje (Upravit údaje) a dostaneme se do okna editoru dotazů Power Query, kde budeme s daty dále pracovat:
Ihned můžete bezpečně smazat v pravém panelu Parametry požadavku (Nastavení dotazu) krok upravený typ (Změněný typ) Nepotřebujeme ho.
Tabulka ve sloupci svátky obsahuje kódy a popisy dnů pracovního klidu – její obsah uvidíte tak, že ji dvakrát „propadnete“ kliknutím na zelené slovo Tabulka:
Chcete-li se vrátit zpět, budete muset v pravém panelu odstranit všechny kroky, které se objevily zpět Zdroj (Zdroj).
Druhá tabulka, ke které se lze dostat podobným způsobem, obsahuje přesně to, co potřebujeme – data všech dnů pracovního klidu:
Zbývá zpracovat tuto desku, a to:
1. Filtrujte pouze prázdninová data (tj. jedničky) podle druhého sloupce Atribut:t
2. Smazat všechny sloupce kromě prvního – kliknutím pravým tlačítkem myši na záhlaví prvního sloupce a výběrem příkazu Odstraňte další sloupce (Odstranit další sloupce):
3. Rozdělte první sloupec tečkou zvlášť pro měsíc a den pomocí příkazu Rozdělit sloupec – podle oddělovače Karta Proměna (Transformace — Rozdělit sloupec — Podle oddělovače):
4. A nakonec vytvořte vypočítaný sloupec s normálními daty. Chcete-li to provést, na kartě Přidání sloupce klikněte na tlačítko Vlastní sloupec (Přidat sloupec – vlastní sloupec) a do zobrazeného okna zadejte následující vzorec:
=#datováno(2020, [#»Atribut:d.1″], [#»Atribute:d.2″])
Zde má operátor #date tři argumenty: rok, měsíc a den. Po kliknutí na OK získáme požadovaný sloupec s běžnými víkendovými daty a zbývající sloupce odstraníme jako v kroku 2
Krok 2. Přeměna požadavku na funkci
Naším dalším úkolem je převést dotaz vytvořený pro rok 2020 na univerzální funkci pro libovolný rok (argumentem bude číslo roku). Za tímto účelem provedeme následující:
1. Rozbalení (pokud již není rozbaleno) panelu Dotazy (Dotazy) vlevo v okně Power Query:
2. Po převedení požadavku na funkci bohužel zmizí možnost vidět kroky tvořící požadavek a jednoduše je upravit. Proto má smysl udělat si kopii naší žádosti a už s ní skotačit a originál nechat v záloze. Chcete-li to provést, klikněte pravým tlačítkem v levém podokně na naši žádost o kalendář a vyberte příkaz Duplikovat.
Opětovným kliknutím pravým tlačítkem myši na výslednou kopii kalendáře(2) vyberete příkaz Přejmenovat (Přejmenovat) a zadejte nový název – nechejte to být např. fxYear:
3. Zdrojový kód dotazu otevřeme v interním jazyce Power Query (výstižně se mu říká „M“) pomocí příkazu Pokročilý editor Karta přezkoumání(Zobrazit — Pokročilý editor) a provést tam malé změny, aby se náš požadavek stal funkcí pro kterýkoli rok.
To bylo:
Po:
Pokud vás zajímají podrobnosti, tak zde:
- (rok jako číslo)=> – prohlásíme, že naše funkce bude mít jeden číselný argument – proměnnou rok
- Vložení proměnné rok na webový odkaz v kroku Zdroj. Vzhledem k tomu, že Power Query neumožňuje slepovat čísla a text, převádíme pomocí funkce číslo roku na text za běhu Number.ToText
- V předposledním kroku dosadíme proměnnou rok pro rok 2020 #”Přidán vlastní objekt«, kde jsme z fragmentů vytvořili datum.
Po kliknutí na tlačítko úprava náš požadavek se stává funkcí:
Krok 3. Import kalendářů pro všechny roky
Poslední, co zbývá, je provést poslední hlavní dotaz, který nahraje data za všechny dostupné roky a sečte všechny přijaté prázdninové termíny do jedné tabulky. Pro tohle:
1. Klikneme do levého panelu dotazu do šedého prázdného místa pravým tlačítkem myši a postupně vybíráme Nový požadavek – Jiné zdroje – Prázdný požadavek (Nový dotaz — Z jiných zdrojů — Prázdný dotaz):
2. Potřebujeme vygenerovat seznam všech let, pro které budeme kalendáře žádat, tj. 2013, 2014 … 2020. Chcete-li to provést, do řádku vzorců prázdného dotazu, který se objeví, zadejte příkaz:
Struktura:
={NumberA..NumberB}
… v Power Query vygeneruje seznam celých čísel od A do B. Například výraz
={1..5}
… by vytvořil seznam 1,2,3,4,5.
Abychom nebyli pevně svázáni s rokem 2020, používáme funkci DateTime.LocalNow() – analog funkce Excel DNES (DNES) v Power Query – a extrahujte z něj postupně aktuální rok pomocí funkce Datum.Rok.
3. Výsledná sada let, i když to vypadá docela adekvátně, není tabulka pro Power Query, ale speciální objekt – lest (Seznam). Ale převést ji na tabulku není problém: stačí kliknout na tlačítko Ke stolu (ke stolu) v levém horním rohu:
4. Cílová čára! Použití funkce, kterou jsme vytvořili dříve fxYear do výsledného seznamu let. Chcete-li to provést, na kartě Přidání sloupce zmáčknout tlačítko Volání vlastní funkce (Přidat sloupec – vyvolat vlastní funkci) a nastavte jeho jediný argument – sloupec Column1 v průběhu let:
Po kliknutí na tlačítko OK naše funkce fxYear import bude fungovat střídavě pro každý rok a dostaneme sloupec, kde každá buňka bude obsahovat tabulku s daty dnů pracovního klidu (obsah tabulky je dobře viditelný, pokud kliknete na pozadí buňky vedle slovo Tabulka):
Zbývá rozbalit obsah vnořených tabulek kliknutím na ikonu s dvojitými šipkami v záhlaví sloupce Termíny (klíště Jako předponu použijte původní název sloupce lze to odstranit):
… a po kliknutí na OK dostáváme, co jsme chtěli – seznam všech svátků od roku 2013 do aktuálního roku:
První, již nepotřebný sloupec, lze smazat a u druhého nastavit datový typ datum (Datum) v rozevíracím seznamu v záhlaví sloupce:
Samotný dotaz lze přejmenovat na něco smysluplnějšího než Žádost1 a poté pomocí příkazu nahrajte výsledky na list ve formě dynamické „inteligentní“ tabulky zavřít a stáhnout Karta Domů (Domů – Zavřít a načíst):
Vytvořený kalendář můžete v budoucnu aktualizovat kliknutím pravým tlačítkem myši na tabulku nebo dotazem v pravém podokně pomocí příkazu Aktualizovat a uložit. Nebo použijte tlačítko Obnovit vše Karta Data (Datum – Obnovit vše) nebo klávesovou zkratkou Ctrl+Další+F5.
To je vše.
Už nikdy nebudete muset ztrácet čas a přemýšlet hledáním a aktualizací seznamu svátků – nyní máte „věčný“ kalendář výroby. V každém případě, pokud autoři stránek http://xmlcalendar.ru/ podpoří své potomky, což, jak doufám, bude ještě hodně, hodně dlouho (ještě jednou jim děkujeme!).
- Import bitcoinů do Excelu z internetu přes Power Query
- Vyhledání dalšího pracovního dne pomocí funkce WORKDAY
- Hledání průsečíku datových intervalů