Inhoud
Productiekalender, dat wil zeggen een lijst met data, waarop alle officiële werkdagen en feestdagen dienovereenkomstig zijn gemarkeerd - een absoluut noodzakelijk iets voor elke gebruiker van Microsoft Excel. In de praktijk kun je niet zonder:
- in boekhoudkundige berekeningen (salaris, diensttijd, vakanties …)
- in de logistiek – voor de juiste bepaling van levertijden, rekening houdend met weekends en feestdagen (weet je nog de klassieker “kom maar op na de feestdagen?”)
- in projectmanagement – voor de juiste inschatting van termijnen, wederom rekening houdend met werkdagen-niet-werkdagen
- elk gebruik van functies zoals WERKDAG (WERKDAG) or PURE WERKERS (NETWERKDAGEN), omdat ze een lijst met vakanties als argument nodig hebben
- bij gebruik van Time Intelligence-functies (zoals TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, enz.) in Power Pivot en Power BI
- … enz. enz. – veel voorbeelden.
Het is gemakkelijker voor degenen die in zakelijke ERP-systemen zoals 1C of SAP werken, omdat de productiekalender erin is ingebouwd. Maar hoe zit het met Excel-gebruikers?
U kunt zo'n kalender natuurlijk handmatig bijhouden. Maar dan moet je het minstens één keer per jaar bijwerken (of zelfs vaker, zoals in de "vrolijke" 2020), zorgvuldig alle weekenden, transfers en niet-werkdagen invoeren die door onze regering zijn uitgevonden. En herhaal deze procedure vervolgens elk volgend jaar. Verveling.
Wat dacht je van een beetje gek worden en een "eeuwige" fabriekskalender maken in Excel? Eentje die zichzelf bijwerkt, gegevens van internet haalt en altijd een actuele lijst van niet-werkdagen genereert voor later gebruik in eventuele berekeningen? Aanlokkelijk?
Om dit te doen, is in feite helemaal niet moeilijk.
Databron
De belangrijkste vraag is waar de gegevens vandaan komen? Op zoek naar een geschikte bron heb ik verschillende opties doorlopen:
- De originele decreten worden in pdf-formaat op de website van de overheid gepubliceerd (hier bijvoorbeeld een van hen) en verdwijnen onmiddellijk – bruikbare informatie kan er niet uit worden gehaald.
- Een verleidelijke optie leek op het eerste gezicht het 'Open Data Portal van de Federatie', waar een overeenkomstige dataset aanwezig is, maar bij nader onderzoek bleek alles triest. De site is vreselijk onhandig om in Excel te importeren, de technische ondersteuning reageert niet (zelfgeïsoleerd?) en de gegevens zelf zijn daar al lange tijd verouderd – de productiekalender voor 2020 is voor het laatst bijgewerkt in november 2019 (schande!) en bevat natuurlijk niet ons ‘coronavirus’ en het ‘stemweekend’ van bijvoorbeeld 2020.
Gedesillusioneerd door officiële bronnen, begon ik onofficiële bronnen te graven. Er zijn er veel op internet, maar de meeste zijn wederom totaal ongeschikt om in Excel te importeren en een productiekalender uit te geven in de vorm van prachtige foto's. Maar het is niet aan ons om het aan de muur te hangen, toch?
En tijdens het zoeken werd er per ongeluk iets geweldigs ontdekt - de site http://xmlcalendar.ru/
Zonder onnodige "franje", een eenvoudige, lichte en snelle site, geslepen voor één taak - om iedereen een productiekalender voor het gewenste jaar in XML-formaat te geven. Uitstekend!
Als u plotseling niet op de hoogte bent, is XML een tekstindeling met inhoud gemarkeerd met speciaal
Voor het geval dat ik contact heb opgenomen met de auteurs van de site en zij hebben bevestigd dat de site al 7 jaar bestaat, de gegevens erop worden constant bijgewerkt (ze hebben hier zelfs een filiaal op github voor) en ze gaan het niet sluiten. En ik vind het helemaal niet erg dat jij en ik er gegevens uit laden voor al onze projecten en berekeningen in Excel. Is gratis. Fijn om te weten dat er nog zulke mensen zijn! Respect!
Het blijft om deze gegevens in Excel te laden met behulp van de Power Query-invoegtoepassing (voor versies van Excel 2010-2013 kan deze gratis worden gedownload van de Microsoft-website en in versies van Excel 2016 en nieuwer is deze standaard al ingebouwd ).
De logica van acties is als volgt:
- We doen een verzoek om een jaar lang gegevens van de site te downloaden
- Ons verzoek omzetten in een functie
- We passen deze functie toe op de lijst van alle beschikbare jaren, vanaf 2013 tot en met het lopende jaar – en we krijgen een “eeuwigdurende” productiekalender met automatische updates. Voila!
Stap 1. Importeer een kalender voor één jaar
Laad eerst de productiekalender voor een willekeurig jaar, bijvoorbeeld voor 2020. Ga hiervoor in Excel naar het tabblad Data (of Power Queryals je het als een aparte add-on hebt geïnstalleerd) en selecteer Van het internet (Van internet). Plak in het geopende venster de link naar het bijbehorende jaar, gekopieerd van de site:
Na het klikken op OK er verschijnt een voorbeeldvenster waarin u op de knop moet klikken Gegevens converteren (Gegevens transformeren) or Om de gegevens te wijzigen: (Gegevens bewerken) en we gaan naar het Power Query-query-editorvenster, waar we met de gegevens zullen blijven werken:
Onmiddellijk kunt u veilig verwijderen in het rechterpaneel Verzoek om parameters (Query-instellingen) stap gewijzigd type: (Gewijzigd type) We hebben hem niet nodig.
De tabel in de kolom feestdagen bevat codes en beschrijvingen van niet-werkdagen - u kunt de inhoud ervan zien door er twee keer doorheen te "bladeren" door op het groene woord te klikken tafel:
Om terug te gaan, moet u in het rechterdeelvenster alle stappen verwijderen die zijn verschenen naar bron (Bron).
De tweede tabel, die op een vergelijkbare manier toegankelijk is, bevat precies wat we nodig hebben - de data van alle niet-werkdagen:
Het blijft om deze plaat te verwerken, namelijk:
1. Filter alleen vakantiedata (dwz enen) op de tweede kolom Attribuut:t
2. Verwijder alle kolommen behalve de eerste – door met de rechtermuisknop op de kop van de eerste kolom te klikken en de opdracht te selecteren Andere kolommen verwijderen (Verwijder andere kolommen):
3. Splits de eerste kolom per punt afzonderlijk voor maand en dag met commando Kolom splitsen - op scheidingsteken tab Transformatie (Transformeren — Kolom splitsen — Per scheidingsteken):
4. En maak tot slot een berekende kolom met normale datums. Om dit te doen, op het tabblad Een kolom toevoegen klik op de knop Aangepaste kolom (Kolom toevoegen — Aangepaste kolom) en voer de volgende formule in het venster dat verschijnt in:
=#gedateerd(2020, [#»Attribuut:d.1″], [#»Attribuut:d.2″])
Hier heeft de operator #date drie argumenten: respectievelijk jaar, maand en dag. Na het klikken op OK we krijgen de vereiste kolom met normale weekenddata en verwijderen de resterende kolommen zoals in stap 2
Stap 2. Het verzoek omzetten in een functie
Onze volgende taak is om de query die voor 2020 is gemaakt om te zetten in een universele functie voor elk jaar (het jaarnummer zal het argument zijn). Hiervoor doen we het volgende:
1. Het paneel uitvouwen (indien nog niet uitgevouwen) vragen (Vragen) aan de linkerkant in het Power Query-venster:
2. Na het converteren van het verzoek naar een functie, verdwijnt de mogelijkheid om de stappen waaruit het verzoek bestaat te zien en deze gemakkelijk te bewerken helaas. Daarom is het logisch om een kopie van ons verzoek te maken en er al mee te stoeien, en het origineel in reserve te laten. Om dit te doen, klikt u met de rechtermuisknop in het linkerdeelvenster op ons kalenderverzoek en selecteert u de opdracht Dupliceren.
Door nogmaals met de rechtermuisknop op de resulterende kopie van kalender (2) te klikken, wordt de opdracht geselecteerd andere naam geven (Hernoemen) en voer een nieuwe naam in – laat het bijvoorbeeld zijn fxJaar:
3. We openen de broncode van de query in de interne Power Query-taal (deze wordt kortweg "M" genoemd) met behulp van de opdracht Geavanceerde editor tab Beoordeling(Bekijken - Geavanceerde editor) en maak daar kleine wijzigingen om van ons verzoek een functie voor elk jaar te maken.
Het was:
Na:
Als u geïnteresseerd bent in de details, dan hier:
- (jaar als getal)=> – we verklaren dat onze functie één numeriek argument zal hebben – een variabele jaar
- De variabele plakken jaar naar weblink in stap bron. Aangezien u met Power Query geen getallen en tekst kunt plakken, converteren we het jaartal direct naar tekst met behulp van de functie Nummer.ToText
- We vervangen de jaarvariabele voor 2020 in de voorlaatste stap #"Aangepast object toegevoegd«, waar we de datum uit de fragmenten hebben gevormd.
Na het klikken op Finish ons verzoek wordt een functie:
Stap 3. Kalenders importeren voor alle jaren
Het laatste dat overblijft is om de laatste hoofdquery te maken, die gegevens voor alle beschikbare jaren zal uploaden en alle ontvangen vakantiedata in één tabel zal toevoegen. Voor deze:
1. We klikken in het linkerquerypaneel in een grijze lege ruimte met de rechtermuisknop en selecteren opeenvolgend Nieuw verzoek – Andere bronnen – Leeg verzoek (Nieuwe zoekopdracht — Uit andere bronnen — Lege zoekopdracht):
2. We moeten een lijst genereren van alle jaren waarvoor we kalenders zullen opvragen, bijv. 2013, 2014 … 2020. Om dit te doen, voert u in de formulebalk van de lege query die verschijnt de opdracht in:
Structuur:
={NummerA..NummerB}
… in Power Query genereert een lijst met gehele getallen van A naar B. Bijvoorbeeld, de uitdrukking
={1..5}
… zou een lijst van 1,2,3,4,5 opleveren.
Welnu, om niet rigide gebonden te zijn aan 2020, gebruiken we de functie DateTime.LocalNow() – analoog van de Excel-functie VANDAAG (VANDAAG) in Power Query - en eruit halen, op zijn beurt, het huidige jaar door de functie Datum.Jaar.
3. De resulterende reeks jaren, hoewel het er behoorlijk adequaat uitziet, is geen tabel voor Power Query, maar een speciaal object - lijst (Lijst). Maar het omzetten naar een tabel is geen probleem: klik gewoon op de knop Aan tafel (Aan tafel) in de linker bovenhoek:
4. Eindstreep! De functie toepassen die we eerder hebben gemaakt fxJaar naar de resulterende lijst van jaren. Om dit te doen, op het tabblad Een kolom toevoegen druk op de knop Aangepaste functie oproepen (Kolom toevoegen — Aangepaste functie oproepen) en stel het enige argument in - de kolom Column1 door de jaren heen:
Na het klikken op OK onze functie fxJaar de import werkt beurtelings voor elk jaar en we krijgen een kolom waarin elke cel een tabel bevat met de datums van niet-werkdagen (de inhoud van de tabel is duidelijk zichtbaar als u op de achtergrond van de cel naast het woord tafel):
Het blijft over om de inhoud van geneste tabellen uit te breiden door op het pictogram met dubbele pijlen in de kolomkop te klikken Data (Kruis aan Gebruik de originele kolomnaam als voorvoegsel het kan worden verwijderd):
… en na het klikken op OK we krijgen wat we wilden - een lijst van alle vakanties van 2013 tot het huidige jaar:
De eerste, al onnodige kolom, kan worden verwijderd en voor de tweede stelt u het gegevenstype in gegevens (Datum) in de vervolgkeuzelijst in de kolomkop:
De query zelf kan worden hernoemd tot iets dat betekenisvoller is dan Verzoek1 en upload vervolgens de resultaten naar het blad in de vorm van een dynamische "slimme" tabel met behulp van de opdracht sluiten en downloaden tab Home (Home — Sluiten & Laden):
U kunt de gemaakte kalender in de toekomst bijwerken door met de rechtermuisknop op de tabel te klikken of door in het rechterdeelvenster een zoekopdracht te geven via de opdracht Bijwerken en opslaan. Of gebruik de knop Ververs alles tab Data (Datum — Alles vernieuwen) of sneltoets Ctrl+anders+F5.
Dat is alles.
Nu hoeft u nooit meer tijd en energie te verspillen aan het zoeken naar en bijwerken van de lijst met vakanties - nu heeft u een "eeuwige" productiekalender. In ieder geval, zolang de auteurs van de site http://xmlcalendar.ru/ hun nakomelingen ondersteunen, wat, naar ik hoop, voor een zeer, zeer lange tijd zal zijn (nogmaals dankzij hen!).
- Importeer bitcoin-snelheid om uit te blinken van internet via Power Query
- De volgende werkdag zoeken met de WORKDAY-functie
- Het snijpunt van datumintervallen vinden