Fabrikkkalender i Excel

Produksjonskalender, altså en liste over datoer, der alle offisielle virkedager og helligdager er merket deretter – en helt nødvendig ting for enhver bruker av Microsoft Excel. I praksis kan du ikke klare deg uten det:

• i regnskapsberegninger (lønn, tjenestetid, ferier ...)
• i logistikk – for riktig bestemmelse av leveringstider, tatt i betraktning helger og helligdager (husk den klassiske "kom igjen etter ferien?")
  
• i prosjektledelse – for riktig estimering av vilkår, med hensyn til, igjen, arbeidsfrie dager
• enhver bruk av funksjoner som ARBEIDSDAG (ARBEIDSDAG) or RENE ARBEIDERE (NETTVERKDAGER), fordi de krever en liste over helligdager som argument
• når du bruker Time Intelligence-funksjoner (som TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, osv.) i Power Pivot og Power BI
  
• … osv. osv. – mange eksempler.
  

Det er lettere for de som jobber i bedriftens ERP-systemer som 1C eller SAP, da produksjonskalenderen er innebygd i dem. Men hva med Excel-brukere?

Du kan selvfølgelig holde en slik kalender manuelt. Men da må du oppdatere den minst en gang i året (eller enda oftere, som i "jolly" 2020), nøye legge inn alle helger, overføringer og ikke-arbeidsdager oppfunnet av vår regjering. Og deretter gjenta denne prosedyren hvert neste år. Kjedsomhet.

Hva med å bli litt gal og lage en "evig" fabrikkkalender i Excel? En som oppdaterer seg selv, tar data fra Internett og alltid genererer en oppdatert liste over arbeidsfrie dager for senere bruk i eventuelle beregninger? Fristende?

Å gjøre dette er faktisk ikke vanskelig i det hele tatt.

Datakilde

Hovedspørsmålet er hvor man får tak i dataene? På leting etter en passende kilde gikk jeg gjennom flere alternativer:

• De opprinnelige dekretene publiseres på regjeringens nettsider i PDF-format (her, for eksempel en av dem) og forsvinner umiddelbart – nyttig informasjon kan ikke trekkes ut av dem.
• Et fristende alternativ, ved første øyekast, så ut til å være "Open Data Portal of the Federation", der det er et tilsvarende datasett, men ved nærmere undersøkelse viste alt seg å være trist. Siden er fryktelig upraktisk å importere til Excel, teknisk støtte reagerer ikke (selvisolert?), og selve dataene er utdaterte der i lang tid – produksjonskalenderen for 2020 ble sist oppdatert i november 2019 (skam!) og , selvfølgelig, inneholder ikke vårt "koronavirus" og "avstemningshelgen" i 2020, for eksempel.

Desillusjonert over offisielle kilder begynte jeg å grave uoffisielle. Det er mange av dem på Internett, men de fleste av dem, igjen, er helt uegnet for import til Excel og gir ut en produksjonskalender i form av vakre bilder. Men det er vel ikke for oss å henge det på veggen?

Og i prosessen med å søke ble en fantastisk ting ved et uhell oppdaget - nettstedet http://xmlcalendar.ru/

Uten unødvendige “frills”, en enkel, lett og rask side, skjerpet for én oppgave – for å gi alle en produksjonskalender for ønsket år i XML-format. Utmerket!

Hvis du plutselig ikke vet det, er XML et tekstformat med innhold merket med spesiell . Lett, praktisk og lesbar av de fleste moderne programmer, inkludert Excel.

I tilfelle kontaktet jeg forfatterne av nettstedet, og de bekreftet at nettstedet har eksistert i 7 år, dataene på det oppdateres kontinuerlig (de har til og med en filial på github for dette), og de kommer ikke til å lukke det. Og jeg har ikke noe imot at du og jeg laster inn data fra den for noen av våre prosjekter og beregninger i Excel. Er gratis. Det er fint å vite at det fortsatt finnes slike mennesker! Respekt!

Det gjenstår å laste disse dataene inn i Excel ved å bruke Power Query-tillegget (for versjoner av Excel 2010-2013 kan det lastes ned gratis fra Microsofts nettsted, og i versjoner av Excel 2016 og nyere er det allerede innebygd som standard ).

Handlingslogikken vil være som følger:

1. Vi ber om å laste ned data fra nettstedet for et hvilket som helst år
2. Gjør forespørselen vår til en funksjon
3. Vi bruker denne funksjonen på listen over alle tilgjengelige år, fra 2013 og frem til inneværende år – og vi får en «evig» produksjonskalender med automatisk oppdatering. Voila!
   

Trinn 1. Importer en kalender for ett år

Last først produksjonskalenderen for et hvilket som helst år, for eksempel for 2020. For å gjøre dette, i Excel, gå til fanen Data (eller Strømforespørselhvis du installerte det som et eget tillegg) og velg Fra Internett (Fra nettet). I vinduet som åpnes, lim inn lenken til det tilsvarende året, kopiert fra nettstedet:

Etter å ha klikket på OK et forhåndsvisningsvindu vises, der du må klikke på knappen Konverter data (Transformer data) or For å endre dataene (Rediger data) og vi kommer til Power Query-spørringsredigeringsvinduet, hvor vi fortsetter å jobbe med dataene:

Umiddelbart kan du trygt slette i høyre panel Be om parametere (Søkeinnstillinger) trinn modifisert type (Endret type) Vi trenger ham ikke.

Tabellen i helligdagskolonnen inneholder koder og beskrivelser av ikke-arbeidsdager – du kan se innholdet ved å "falle gjennom" det to ganger ved å klikke på det grønne ordet Bord:

For å gå tilbake, må du slette i høyre panel alle trinnene som har dukket opp tilbake til kilde (Kilde).

Den andre tabellen, som kan nås på lignende måte, inneholder nøyaktig det vi trenger – datoene for alle ikke-arbeidsdager:

Det gjenstår å behandle denne platen, nemlig:

1. Filtrer kun feriedatoer (dvs. en) etter den andre kolonnen Attributt:t

2. Slett alle kolonner unntatt den første - ved å høyreklikke på overskriften til den første kolonnen og velge kommandoen Slett andre kolonner (Fjern andre kolonner):

3. Del første kolonne for prikk separat for måned og dag med kommando Del kolonne – etter skilletegn tab Transformation (Transformer – Del kolonne – Med skilletegn):

4. Og til slutt lage en beregnet kolonne med normale datoer. For å gjøre dette, på fanen Legger til en kolonne klikk på knappen Egendefinert kolonne (Legg til kolonne – egendefinert kolonne) og skriv inn følgende formel i vinduet som vises:

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

Her har #date-operatoren tre argumenter: henholdsvis år, måned og dag. Etter å ha klikket på OK vi får den nødvendige kolonnen med normale helgedatoer, og sletter de resterende kolonnene som i trinn 2

Trinn 2. Gjør forespørselen om til en funksjon

Vår neste oppgave er å konvertere spørringen opprettet for 2020 til en universell funksjon for et hvilket som helst år (årstallet vil være argumentet). For å gjøre dette, gjør vi følgende:

1. Utvider (hvis ikke allerede utvidet) panelet forespørsler (Forespørsler) til venstre i Power Query-vinduet:

2. Etter å ha konvertert forespørselen til en funksjon, forsvinner dessverre muligheten til å se trinnene som utgjør forespørselen og enkelt redigere dem. Derfor er det fornuftig å lage en kopi av forespørselen vår og boltre seg allerede med den, og la originalen være i reserve. For å gjøre dette, høyreklikk i venstre rute på vår kalenderforespørsel og velg Dupliser-kommandoen.

Høyreklikk igjen på den resulterende kopien av kalender(2) vil velge kommandoen Gi nytt navn (Gi nytt navn) og skriv inn et nytt navn – la det være f.eks. fx år:

3. Vi åpner spørringskildekoden på det interne Power Query-språket (det kalles kortfattet "M") ved å bruke kommandoen Avansert redaktør tab Anmeldelse(Vis — Avansert redaktør) og gjør små endringer der for å gjøre forespørselen vår til en funksjon for ethvert år.

Det var:

Etter:

Hvis du er interessert i detaljene, så her:

• (år som tall)=>  – vi erklærer at funksjonen vår vil ha ett numerisk argument – ​​en variabel år
• Lim inn variabelen år til nettlenke i trinn kilde. Siden Power Query ikke tillater deg å lime tall og tekst, konverterer vi årstallet til tekst i farten ved hjelp av funksjonen Number.ToText
• Vi erstatter årsvariabelen for 2020 i nest siste trinn #”Lagt til tilpasset objekt«, hvor vi dannet datoen fra fragmentene.
  

Etter å ha klikket på Finish vår forespørsel blir en funksjon:

Trinn 3. Importer kalendere for alle år

Det siste som gjenstår er å lage den siste hovedspørringen, som vil laste opp data for alle tilgjengelige år og legge til alle mottatte feriedatoer i én tabell. For dette:

1. Vi klikker i venstre spørrepanel i et grått tomt rom med høyre museknapp og velger sekvensielt Ny forespørsel – Andre kilder – Tom forespørsel (Ny spørring – fra andre kilder – tom spørring):

2. Vi må generere en liste over alle år som vi vil be om kalendere for, dvs. 2013, 2014 … 2020. For å gjøre dette, skriv inn kommandoen i formellinjen til den tomme spørringen som vises:

Struktur:

={NumberA..NumberB}

… i Power Query genererer en liste over heltall fra A til B. For eksempel uttrykket

={1..5}

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

Vel, for ikke å være stivt bundet til 2020, bruker vi funksjonen DateTime.LocalNow() – analog av Excel-funksjonen I DAG (I DAG) i Power Query – og trekke ut fra det, i sin tur, gjeldende år etter funksjonen Dato.År.

3. Det resulterende settet med år, selv om det ser ganske tilstrekkelig ut, er ikke en tabell for Power Query, men et spesielt objekt – liste (Liste). Men å konvertere den til en tabell er ikke et problem: bare klikk på knappen Til bord (Til bordet) i øvre venstre hjørne:

4. Mållinjen! Bruker funksjonen vi opprettet tidligere fx år til den resulterende listen over år. For å gjøre dette, på fanen Legger til en kolonne trykk på knappen Ring egendefinert funksjon (Legg til kolonne – Aktiver egendefinert funksjon) og angi dets eneste argument – ​​kolonnen Column1 i løpet av årene:

Etter å ha klikket på OK funksjonen vår fx år importen vil fungere etter tur for hvert år og vi får en kolonne der hver celle vil inneholde en tabell med datoer for ikke-arbeidsdager (innholdet i tabellen er godt synlig hvis du klikker i bakgrunnen av cellen ved siden av ordet Bord):

Det gjenstår å utvide innholdet i nestede tabeller ved å klikke på ikonet med doble piler i kolonneoverskriften datoer (sett kryss Bruk originalt kolonnenavn som prefiks det kan fjernes):

... og etter å ha klikket på OK vi får det vi ønsket – en liste over alle helligdager fra 2013 til inneværende år:

Den første, allerede unødvendige kolonnen, kan slettes, og for den andre, angi datatypen data (Dato) i rullegardinlisten i kolonneoverskriften:

Selve spørringen kan gis nytt navn til noe mer meningsfylt enn Forespørsel 1 og last deretter opp resultatene til arket i form av en dynamisk "smart" tabell ved å bruke kommandoen lukk og last ned tab Hjemprodukt (Hjem — Lukk og last):

Du kan oppdatere den opprettede kalenderen i fremtiden ved å høyreklikke på tabellen eller spørre i høyre rute gjennom kommandoen Oppdater og lagre. Eller bruk knappen Oppdater alle tab Data (Dato – Oppdater alle) eller hurtigtast Ctrl+andre+F5.

Det er alt.

Nå trenger du aldri mer å kaste bort tid og tanker på å søke etter og oppdatere listen over ferier – nå har du en "evig" produksjonskalender. I alle fall, så lenge forfatterne av nettstedet http://xmlcalendar.ru/ støtter deres avkom, som jeg håper vil være i veldig, veldig lang tid (takk til dem igjen!).

• Importer bitcoin rate for å utmerke seg fra internett via Power Query
• Finne neste virkedag ved å bruke WORKDAY-funksjonen
  
• Finne skjæringspunktet mellom datointervaller