Tovarniški koledar v Excelu

Proizvodni koledar, torej seznam datumov, kjer so ustrezno označeni vsi uradni delovni dnevi in ​​prazniki – nepogrešljiva stvar za vsakega uporabnika Microsoft Excela. V praksi brez tega ne morete:

• v računovodskih izračunih (plača, delovna doba, dopusti…)
• v logistiki – za pravilno določanje dobavnih rokov, upoštevajoč vikende in praznike (se spomnite klasičnega »pridi po praznikih?«)
  
• pri vodenju projektov – za pravilno oceno terminov, ponovno z upoštevanjem delovnih-prostih dni.
• kakršna koli uporaba funkcij, kot je DELOVNI DAN (DELAVNIK) or ČISTI DELAVCI (OMREŽNI DNEVI), ker kot argument zahtevajo seznam praznikov
• pri uporabi funkcij Time Intelligence (kot so TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR itd.) v Power Pivot in Power BI
  
• … itd. itd. – veliko primerov.
  

Tistim, ki delajo v korporativnih sistemih ERP, kot sta 1C ali SAP, je lažje, saj je vanje vgrajen proizvodni koledar. Kaj pa uporabniki Excela?

Takšen koledar seveda lahko vodite ročno. Potem pa ga boste morali posodobiti vsaj enkrat letno (ali še pogosteje, kot v "veselem" letu 2020), pri čemer skrbno vnesete vse vikende, prenose in dela proste dneve, ki jih je izumila naša vlada. In potem ta postopek ponovite vsako naslednje leto. Dolgčas.

Kaj če bi se malo zmešali in naredili "večni" tovarniški koledar v Excelu? Takega, ki se posodablja sam, jemlje podatke iz interneta in vedno generira ažuren seznam dela prostih dni za kasnejšo uporabo pri morebitnih izračunih? Mamljivo?

Narediti to pravzaprav sploh ni težko.

Vir podatkov

Glavno vprašanje je, kje dobiti podatke? V iskanju primernega vira sem šel skozi več možnosti:

• Originalni odloki so objavljeni na vladni spletni strani v formatu PDF (tukaj npr. eden od njih) in takoj izginejo – iz njih ni mogoče potegniti koristnih informacij.
• A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.

Razočaran nad uradnimi viri sem začel kopati po neuradnih. Na internetu jih je veliko, vendar je večina spet popolnoma neprimernih za uvoz v Excel in dajejo proizvodni koledar v obliki čudovitih slik. Ampak ni za nas, da ga obesimo na steno, kajne?

In v procesu iskanja je bila po naključju odkrita čudovita stvar - spletno mesto http://xmlcalendar.ru/

Brez nepotrebnih “fint”, enostavna, lahka in hitra stran, nabrušena za eno nalogo – vsakomur dati proizvodni koledar za želeno leto v formatu XML. odlično!

Če nenadoma niste seznanjeni, potem je XML besedilni format z vsebino, označeno s posebnim . Lahek, priročen in berljiv z večino sodobnih programov, vključno z Excelom.

Za vsak slučaj sem kontaktiral avtorje strani in potrdili so, da stran obstaja že 7 let, podatki na njej se stalno posodabljajo (za to imajo celo podružnico na githubu) in je ne bodo zaprli. In prav nič me ne moti, da ti in jaz iz njega nalagamo podatke za katere koli naše projekte in izračune v Excelu. Je brezplačen. Lepo je vedeti, da še obstajajo taki ljudje! Spoštovanje!

Te podatke je treba naložiti v Excel z dodatkom Power Query (za različice Excela 2010–2013 ga je mogoče brezplačno prenesti s spletnega mesta Microsoft, v različicah Excela 2016 in novejših pa je že privzeto vgrajen ).

Logika dejanj bo naslednja:

1. Zahtevamo prenos podatkov s spletnega mesta za katero koli leto
2. Spreminjanje naše zahteve v funkcijo
3. To funkcijo uporabimo na seznamu vseh razpoložljivih let, od leta 2013 do tekočega leta – in dobimo »večni« proizvodni koledar s samodejnim posodabljanjem. Voila!
   

Korak 1. Uvozite koledar za eno leto

Najprej naložite proizvodni koledar za katero koli leto, na primer za 2020. Če želite to narediti, v Excelu pojdite na zavihek datum (ali poizvedba o močiče ste ga namestili kot ločen dodatek) in izberite Iz interneta (iz spleta). V okno, ki se odpre, prilepite povezavo do ustreznega leta, kopirano s spletnega mesta:

Po kliku OK prikaže se okno za predogled, v katerem morate klikniti gumb Pretvori podatke (Pretvori podatke) or Za spremembo podatkov (Uredi podatke) in prišli bomo do okna urejevalnika poizvedb Power Query, kjer bomo nadaljevali delo s podatki:

Takoj lahko varno izbrišete na desni plošči Parametri zahteve (Nastavitve poizvedbe) korak spremenjeni tip (Spremenjena vrsta) Ne potrebujemo ga.

Tabela v stolpcu prazniki vsebuje šifre in opise prostih dni – njeno vsebino si lahko ogledate tako, da jo dvakrat “preletite” s klikom na zeleno besedo Tabela:

Če se želite vrniti, boste morali na desni plošči izbrisati vse korake, ki so se pojavili nazaj vir (Vir).

Druga tabela, do katere lahko dostopamo na podoben način, vsebuje točno to, kar potrebujemo – datume vseh dela prostih dni:

Ostaja še obdelava te plošče, in sicer:

1. Filtrirajte samo praznične datume (tj. tiste) po drugem stolpcu Atribut: t

2. Izbrišite vse stolpce razen prvega – z desnim klikom na naslov prvega stolpca in izbiro ukaza Izbrišite druge stolpce (Odstrani druge stolpce):

3. Z ukazom razdeli prvi stolpec s piko ločeno za mesec in dan Razdeli stolpec – z ločilom tab Preoblikovanje (Pretvorba — Razdeli stolpec — Po ločilu):

4. In končno ustvarite izračunan stolpec z običajnimi datumi. Če želite to narediti, na zavihku Dodajanje stolpca kliknite na gumb Stolpec po meri (Dodaj stolpec — stolpec po meri) in v okno, ki se prikaže, vnesite naslednjo formulo:

=#z datumom(2020, [#»Atribut:d.1″], [#»Atribut:d.2″])

Tukaj ima operator #date tri argumente: leto, mesec in dan. Po kliku na OK dobimo zahtevani stolpec z običajnimi datumi ob koncu tedna in izbrišemo preostale stolpce kot v 2. koraku

2. korak. Spreminjanje zahteve v funkcijo

Naša naslednja naloga je pretvoriti poizvedbo, ustvarjeno za leto 2020, v univerzalno funkcijo za katero koli leto (številka leta bo njen argument). Za to naredimo naslednje:

1. Razširitev (če še ni razširjena) plošče Poizvedbe (Poizvedbe) na levi v oknu Power Query:

2. Po pretvorbi zahteve v funkcijo možnost ogleda korakov, ki sestavljajo zahtevo, in njihovega enostavnega urejanja žal izgine. Zato je smiselno narediti kopijo naše zahteve in se že z njo poigrati, original pa pustiti v rezervi. To naredimo tako, da v levem podoknu z desno miškino tipko kliknemo našo koledarsko zahtevo in izberemo ukaz Podvoji.

S ponovnim desnim klikom na nastalo kopijo koledarja(2) boste izbrali ukaz preimenovanje (Preimenuj) in vnesite novo ime – naj bo npr. fxYear:

3. Izvorno kodo poizvedbe odpremo v internem jeziku Power Query (jedrnato se imenuje »M«) z ukazom Napredni urejevalnik tab pregled(Pogled — napredni urejevalnik) in tam naredite majhne spremembe, da našo zahtevo spremenite v funkcijo za katero koli leto.

Bilo je:

Po:

Če vas zanimajo podrobnosti, potem tukaj:

• (leto kot številka)=>  – deklariramo, da bo imela naša funkcija en numerični argument – ​​spremenljivko leto
• Lepljenje spremenljivke leto na spletno povezavo v koraku vir. Ker Power Query ne omogoča lepljenja številk in besedila, številko leta pretvorimo v besedilo sproti s funkcijo Number.ToText
• V predzadnjem koraku zamenjamo spremenljivko leto za 2020 #”Dodan predmet po meri«, kjer smo iz drobcev oblikovali datum.
  

Po kliku Konec naša zahteva postane funkcija:

3. korak. Uvozite koledarje za vsa leta

Zadnja stvar, ki nam ostane, je še zadnja glavna poizvedba, ki bo naložila podatke za vsa razpoložljiva leta in združila vse prejete praznike v eno tabelo. Za to:

1. Z desnim gumbom miške kliknemo na levo poizvedovalno ploščo v sivo prazen prostor in zaporedno izberemo Nova zahteva – Drugi viri – Prazna zahteva (Nova poizvedba — Iz drugih virov — Prazna poizvedba):

2. Ustvariti moramo seznam vseh let, za katera bomo zahtevali koledarje, torej 2013, 2014 … 2020. To naredimo tako, da v vrstici s formulo prazne poizvedbe, ki se pojavi, vnesemo ukaz:

Struktura:

={ŠtevilkaA..ŠtevilkaB}

… v Power Query ustvari seznam celih števil od A do B. Na primer izraz

={1..5}

… bi ustvaril seznam 1,2,3,4,5.

No, da ne bi bili togo vezani na leto 2020, uporabljamo funkcijo DateTime.LocalNow() – analog funkcije Excel DANES (DANES) v Power Query – in iz nje ekstrahirajte tekoče leto s funkcijo Datum.Leto.

3. Nastali niz let, čeprav je videti povsem ustrezen, ni tabela za Power Query, ampak poseben objekt – Seznam (Seznam). Toda pretvorba v tabelo ni težava: samo kliknite gumb Na mizo (K mizi) v zgornjem levem kotu:

4. Ciljna črta! Uporaba funkcije, ki smo jo ustvarili prej fxYear do nastalega seznama let. Če želite to narediti, na zavihku Dodajanje stolpca pritisni gumb Pokličite funkcijo po meri (Dodaj stolpec — Prikliči funkcijo po meri) in nastavi svoj edini argument – ​​stolpec Column1 skozi leta:

Po kliku OK naša funkcija fxYear uvoz bo deloval po vrsti za vsako leto in dobili bomo stolpec, kjer bo vsaka celica vsebovala tabelo z datumi prostih dni (vsebina tabele je jasno vidna, če kliknete v ozadju celice poleg beseda Tabela):

Ostaja še razširiti vsebino ugnezdenih tabel s klikom na ikono z dvojnimi puščicami v glavi stolpca Termini (odkljukajte Kot predpono uporabite izvirno ime stolpca se lahko odstrani):

… in po kliku na OK dobimo kar smo želeli – seznam vseh praznikov od leta 2013 do tekočega leta:

Prvi, že nepotreben stolpec, lahko izbrišete, za drugega pa nastavite vrsto podatkov Datum (Datum) na spustnem seznamu v naslovu stolpca:

Samo poizvedbo lahko preimenujete v nekaj bolj smiselnega kot Zahteva1 in nato z ukazom naloži rezultate na list v obliki dinamične »pametne« tabele zaprite in prenesite tab Domov (Domov — Zapri in naloži):

Ustvarjen koledar lahko v prihodnje posodobite z desnim klikom na tabelo ali poizvedbo v desnem podoknu prek ukaza Posodobi in shrani. Ali pa uporabite gumb Osveži vse tab datum (Datum — Osveži vse) ali bližnjico na tipkovnici Ctrl+druga+F5.

To je vse.

Zdaj vam nikoli več ni treba izgubljati časa in energije za razmišljanje z iskanjem in posodabljanjem seznama praznikov – zdaj imate »večni« proizvodni koledar. V vsakem primeru, dokler avtorji spletnega mesta http://xmlcalendar.ru/ podpirajo svoje potomce, kar upam, da bo še zelo, zelo dolgo (hvala jim še enkrat!).

• Uvozite tečaj bitcoinov za excel iz interneta prek Power Query
• Iskanje naslednjega delovnega dne s funkcijo WORKDAY
  
• Iskanje presečišča datumskih intervalov