Fabrikskalender i Excel

Produktionskalender, altså en liste over datoer, hvor alle officielle arbejdsdage og helligdage er markeret i overensstemmelse hermed – en absolut nødvendig ting for enhver bruger af Microsoft Excel. I praksis kan du ikke undvære det:

• i regnskabsberegninger (løn, anciennitet, ferier ...)
• i logistik – for den korrekte bestemmelse af leveringstider, under hensyntagen til weekender og helligdage (husk den klassiske "kom så efter ferien?")
  
• i projektledelse – for den korrekte estimering af vilkår, under hensyntagen igen til arbejds-fridage
• enhver brug af funktioner som f.eks ARBEJDSDAG (ARBEJDSDAG) or RENE ARBEJDERE (NETVÆRKSDAGE), fordi de kræver en liste over helligdage som argument
• når du bruger Time Intelligence-funktioner (som TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR osv.) i Power Pivot og Power BI
  
• … osv. osv. – masser af eksempler.
  

Det er nemmere for dem, der arbejder i virksomhedens ERP-systemer som 1C eller SAP, da produktionskalenderen er indbygget i dem. Men hvad med Excel-brugere?

Du kan selvfølgelig holde sådan en kalender manuelt. Men så bliver du nødt til at opdatere det mindst en gang om året (eller endnu oftere, som i "jolly" 2020), og omhyggeligt indtaste alle weekender, overførsler og ikke-arbejdsdage opfundet af vores regering. Og gentag derefter denne procedure hvert næste år. Kedsomhed.

Hvad med at gå lidt amok og lave en "evig" fabrikskalender i Excel? En der opdaterer sig selv, tager data fra internettet og altid genererer en opdateret liste over ikke-arbejdsdage til efterfølgende brug i eventuelle beregninger? Fristende?

At gøre dette er faktisk slet ikke svært.

Datakilde

Hovedspørgsmålet er, hvor man kan hente dataene? På jagt efter en passende kilde gik jeg igennem flere muligheder:

• De oprindelige dekreter offentliggøres på regeringens hjemmeside i PDF-format (her f.eks. et af dem) og forsvinder med det samme – nyttig information kan ikke trækkes ud af dem.
• En fristende mulighed så ved første øjekast ud til at være "Open Data Portal of the Federation", hvor der er et tilsvarende datasæt, men ved nærmere undersøgelse viste alt sig at være trist. Siden er frygtelig ubelejlig at importere til Excel, teknisk support reagerer ikke (selvisoleret?), og selve dataene er forældede der i lang tid – produktionskalenderen for 2020 blev sidst opdateret i november 2019 (skam!) og indeholder naturligvis ikke vores "coronavirus" og "afstemningsweekenden" i 2020, for eksempel.

Desillusioneret over officielle kilder begyndte jeg at grave uofficielle. Der er mange af dem på internettet, men de fleste af dem er igen fuldstændig uegnede til at importere til Excel og udlevere en produktionskalender i form af flotte billeder. Men det er ikke for os at hænge det på væggen, vel?

Og i færd med at søge blev en vidunderlig ting ved et uheld opdaget - webstedet http://xmlcalendar.ru/

Uden unødvendige “dikkedarer”, en enkel, let og hurtig side, skærpet til én opgave – at give alle en produktionskalender for det ønskede år i XML-format. Fremragende!

Hvis du pludselig ikke er ved det, så er XML et tekstformat med indhold markeret med speciel . Let, praktisk og læsbar af de fleste moderne programmer, inklusive Excel.

For en sikkerheds skyld kontaktede jeg forfatterne af webstedet, og de bekræftede, at webstedet har eksisteret i 7 år, dataene på det opdateres konstant (de har endda en filial på github til dette), og de vil ikke lukke det. Og jeg gider overhovedet ikke, at du og jeg indlæser data fra den til nogen af ​​vores projekter og beregninger i Excel. Er ledig. Det er rart at vide, at der stadig er mennesker som dette! Respekt!

Det er tilbage at indlæse disse data i Excel ved hjælp af Power Query-tilføjelsesprogrammet (for versioner af Excel 2010-2013 kan det downloades gratis fra Microsofts websted, og i versioner af Excel 2016 og nyere er det allerede indbygget som standard ).

Handlingslogikken vil være som følger:

1. Vi anmoder om at downloade data fra siden for et hvilket som helst år
2. Forvandler vores anmodning til en funktion
3. Vi anvender denne funktion på listen over alle tilgængelige år fra 2013 og frem til indeværende år – og vi får en "evig" produktionskalender med automatisk opdatering. Voila!
   

Trin 1. Importer en kalender for et år

Først skal du indlæse produktionskalenderen for ethvert år, for eksempel for 2020. For at gøre dette skal du i Excel gå til fanen data (eller Strømforespørgselhvis du har installeret det som en separat tilføjelse), og vælg Fra internettet (Fra nettet). Indsæt linket til det tilsvarende år i det vindue, der åbnes, kopieret fra webstedet:

Efter at have klikket på OK et forhåndsvisningsvindue vises, hvor du skal klikke på knappen Konverter data (Transformer data) or For at ændre data (Rediger data) og vi kommer til vinduet Power Query-forespørgselseditor, hvor vi vil fortsætte med at arbejde med dataene:

Straks kan du trygt slette i højre panel Forespørgselsparametre (Forespørgselsindstillinger) trin ændret type (Ændret type) Vi har ikke brug for ham.

Tabellen i helligdagskolonnen indeholder koder og beskrivelser af ikke-arbejdsdage – du kan se indholdet ved at "falde igennem" det to gange ved at klikke på det grønne ord Bordlampe:

For at gå tilbage skal du i højre panel slette alle de trin, der er vist tilbage til Kilde (Kilde).

Den anden tabel, som kan tilgås på lignende måde, indeholder præcis, hvad vi har brug for – datoerne for alle ikke-arbejdsdage:

Det er tilbage at behandle denne plade, nemlig:

1. Filtrer kun feriedatoer (dvs. en) efter den anden kolonne Egenskab:t

2. Slet alle kolonner undtagen den første - ved at højreklikke på overskriften til den første kolonne og vælge kommandoen Slet andre kolonner (Fjern andre kolonner):

3. Opdel første kolonne for prik separat for måned og dag med kommando Opdel kolonne - Efter afgrænsning fanen Transformation (Transformer - Opdel kolonne - Efter afgrænsning):

4. Og lav endelig en beregnet kolonne med normale datoer. For at gøre dette, på fanen Tilføjelse af en kolonne klik på knappen Brugerdefineret kolonne (Tilføj kolonne – tilpasset kolonne) og indtast følgende formel i det vindue, der vises:

=#dateret(2020, [#»Attribut:d.1″], [#»Attribute:d.2″])

Her har #dato-operatoren tre argumenter: henholdsvis år, måned og dag. Efter at have klikket på OK vi får den påkrævede kolonne med normale weekenddatoer, og sletter de resterende kolonner som i trin 2

Trin 2. Forvandling af anmodningen til en funktion

Vores næste opgave er at konvertere den forespørgsel, der er oprettet for 2020, til en universel funktion for ethvert år (årstallet vil være dets argument). For at gøre dette gør vi følgende:

1. Udvidelse (hvis ikke allerede udvidet) panelet Forespørgsler (Forespørgsler) til venstre i Power Query-vinduet:

2. Efter at have konverteret anmodningen til en funktion, forsvinder muligheden for at se de trin, der udgør anmodningen, og nemt redigere dem, desværre. Derfor giver det mening at lave en kopi af vores anmodning og boltre sig allerede med den og lade originalen være i reserve. For at gøre dette skal du højreklikke i venstre rude på vores kalenderanmodning og vælge kommandoen Dupliker.

Højreklik igen på den resulterende kopi af kalender(2) vil vælge kommandoen Omdøb (Omdøb) og indtast et nyt navn – lad det f.eks. fx år:

3. Vi åbner forespørgselskildekoden i det interne Power Query-sprog (det kaldes kort og godt "M") ved hjælp af kommandoen Avanceret editor fanen anmeldelse(Vis — Avanceret editor) og lav små ændringer der for at gøre vores anmodning til en funktion for ethvert år.

Det var:

Efter:

Hvis du er interesseret i detaljerne, så her:

• (år som tal)=>  – vi erklærer, at vores funktion vil have ét numerisk argument – ​​en variabel år
• Indsætter variablen år til weblink i trin Kilde. Da Power Query ikke tillader dig at lime tal og tekst, konverterer vi årstal til tekst i farten ved hjælp af funktionen Number.ToText
• Vi erstatter årsvariablen for 2020 i næstsidste trin #”Tilføjet brugerdefineret objekt«, hvor vi dannede datoen ud fra fragmenterne.
  

Efter at have klikket på Finish vores anmodning bliver en funktion:

Trin 3. Importer kalendere for alle år

Den sidste ting tilbage er at lave den sidste hovedforespørgsel, som vil uploade data for alle tilgængelige år og tilføje alle de modtagne feriedatoer i én tabel. For det:

1. Vi klikker i venstre forespørgselspanel i et gråt tomt rum med højre museknap og vælger sekventielt Ny anmodning – Andre kilder – Tom anmodning (Ny forespørgsel - Fra andre kilder - Tom forespørgsel):

2. Vi skal generere en liste over alle år, som vi vil anmode om kalendere for, dvs. 2013, 2014 … 2020. For at gøre dette skal du indtaste kommandoen i formellinjen i den tomme forespørgsel, der vises:

Struktur:

={NumberA..NumberB}

… i Power Query genererer en liste over heltal fra A til B. For eksempel udtrykket

={1..5}

… ville producere en liste med 1,2,3,4,5.

Nå, for ikke at være bundet fast til 2020, bruger vi funktionen DateTime.LocalNow() – analog til Excel-funktionen I DAG (I DAG) i Power Query – og udtræk derfra til gengæld det aktuelle år efter funktionen Dato.År.

3. Det resulterende sæt af år, selvom det ser ganske passende ud, er ikke en tabel for Power Query, men et særligt objekt – liste (Liste). Men at konvertere den til en tabel er ikke et problem: bare klik på knappen Til bord (Til bord) i øverste venstre hjørne:

4. Målstregen! Anvendelse af den funktion, vi oprettede tidligere fx år til den resulterende årstal. For at gøre dette, på fanen Tilføjelse af en kolonne tryk på knappen Kald brugerdefineret funktion (Tilføj kolonne – Kald brugerdefineret funktion) og sæt dets eneste argument - kolonnen Column1 i årenes løb:

Efter at have klikket på OK vores funktion fx år importen vil fungere på skift for hvert år, og vi får en kolonne, hvor hver celle vil indeholde en tabel med datoer for ikke-arbejdsdage (indholdet af tabellen er tydeligt synligt, hvis du klikker i baggrunden af ​​cellen ved siden af ordet Bordlampe):

Det er tilbage at udvide indholdet af indlejrede tabeller ved at klikke på ikonet med dobbeltpile i kolonneoverskriften Datoer (sæt kryds Brug det originale kolonnenavn som præfiks det kan fjernes):

… og efter at have klikket videre OK vi får, hvad vi ønskede - en liste over alle helligdage fra 2013 til indeværende år:

Den første, allerede unødvendige kolonne, kan slettes, og for den anden skal du indstille datatypen dato (Dato) i rullelisten i kolonneoverskriften:

Selve forespørgslen kan omdøbes til noget mere meningsfuldt end Anmodning 1 og upload derefter resultaterne til arket i form af en dynamisk "smart" tabel ved hjælp af kommandoen luk og download fanen Home (Hjem — Luk og indlæs):

Du kan opdatere den oprettede kalender i fremtiden ved at højreklikke på tabellen eller forespørge i højre rude gennem kommandoen Opdater & Gem. Eller brug knappen Opdater alle fanen data (Dato – Opdater alle) eller tastaturgenvej Ctrl+andre+F5.

Det er alt.

Nu behøver du aldrig mere spilde tid og tanke-brændstof på at søge efter og opdatere listen over helligdage - nu har du en "evig" produktionskalender. Under alle omstændigheder, så længe forfatterne af webstedet http://xmlcalendar.ru/ støtter deres afkom, hvilket jeg håber, vil være i meget, meget lang tid (tak til dem igen!).

• Importer bitcoin rate for at udmærke sig fra internettet via Power Query
• Find den næste hverdag ved hjælp af WORKDAY-funktionen
  
• Find skæringspunktet mellem datointervaller