Sadržaj
Proizvodni kalendar, tj. popis datuma, gdje su svi službeni radni dani i praznici odgovarajuće označeni – prijeko potrebna stvar svakom korisniku Microsoft Excela. U praksi ne možete bez njega:
- u računovodstvenim obračunima (plaće, radni staž, godišnji odmori…)
- u logistici – za pravilno određivanje vremena isporuke, uzimajući u obzir vikende i praznike (sjećate se klasičnog “ajde poslije praznika?”)
- u upravljanju projektima – za ispravnu procjenu termina, opet uzimajući u obzir radni-neradni dani
- svako korištenje funkcija poput RADNI DAN (RADNI DAN) or ČISTI RADNICI (DANI MREŽE), jer kao argument traže popis praznika
- kada koristite funkcije Time Intelligence (kao što su TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR itd.) u Power Pivot i Power BI
- … itd. itd. – puno primjera.
Lakše je onima koji rade u korporativnim ERP sustavima kao što su 1C ili SAP, jer je proizvodni kalendar ugrađen u njih. Ali što je s korisnicima programa Excel?
Takav kalendar, naravno, možete voditi ručno. Ali tada ćete ga morati ažurirati barem jednom godišnje (ili čak i češće, kao u "veseloj" 2020.), pažljivo unoseći sve vikende, transfere i neradne dane koje je izmislila naša vlada. I onda ponovite ovaj postupak svake sljedeće godine. Dosada.
Kako bi bilo da malo poludite i napravite “vječni” tvornički kalendar u Excelu? Onaj koji se sam ažurira, uzima podatke s interneta i uvijek generira ažuran popis neradnih dana za kasniju upotrebu u bilo kakvim izračunima? primamljivo?
Učiniti to, zapravo, uopće nije teško.
Izvor podataka
Glavno pitanje je gdje dobiti podatke? U potrazi za prikladnim izvorom, prošao sam kroz nekoliko opcija:
- Izvorne uredbe objavljuju se na web stranicama Vlade u PDF formatu (ovdje, na primjer, jedna od njih) i odmah nestaju – iz njih se ne mogu izvući korisne informacije.
- Primamljiva opcija, na prvi pogled, činila se „Portal otvorenih podataka Federacije“, gdje postoji odgovarajući skup podataka, ali, nakon detaljnijeg ispitivanja, sve se pokazalo tužnim. Stranica je užasno nezgodna za import u Excel, tehnička podrška ne odgovara (samoizolacija?), a i sami podaci su tamo odavno zastarjeli – produkcijski kalendar za 2020. je zadnji put ažuriran u studenom 2019. (sramota!) i , naravno, ne sadrži naš “koronavirus” i 'glasački' vikend 2020., na primjer.
Razočaran u službene izvore, počeo sam kopati po neslužbenim. Mnogo ih je na internetu, ali većina ih je, opet, potpuno neprikladna za uvoz u Excel i daju proizvodni kalendar u obliku prekrasnih slika. Ali nije na nama da to objesimo na zid, zar ne?
I u procesu traženja, slučajno je otkrivena divna stvar - stranica http://xmlcalendar.ru/
Bez suvišnih “nabora”, jednostavna, lagana i brza stranica, naoštrena za jedan zadatak – dati svakom proizvodni kalendar za željenu godinu u XML formatu. izvrsno!
Ako odjednom ne znate, onda je XML tekstualni format sa sadržajem označenim posebnim
Za svaki slučaj kontaktirao sam autore stranice i potvrdili su mi da stranica postoji već 7 godina, da se podaci na njoj stalno ažuriraju (čak imaju i podružnicu na githubu za to) i da je ne namjeravaju zatvoriti. I uopće mi ne smeta što ti i ja iz njega učitavamo podatke za bilo koji naš projekt i izračun u Excelu. je besplatno. Lijepo je znati da još ima ovakvih ljudi! Poštovanje!
Preostaje te podatke učitati u Excel pomoću dodatka Power Query (za verzije Excela 2010-2013 može se besplatno preuzeti s Microsoftove web stranice, a u verzijama Excela 2016 i novijim već je standardno ugrađen ).
Logika radnji bit će sljedeća:
- Podnosimo zahtjev za preuzimanje podataka sa stranice za bilo koju godinu
- Pretvaranje našeg zahtjeva u funkciju
- Ovu funkciju primjenjujemo na popis svih dostupnih godina, počevši od 2013. pa sve do tekuće godine – i dobivamo „vječni” proizvodni kalendar s automatskim ažuriranjem. Voila!
Korak 1. Uvezite kalendar za jednu godinu
Prvo učitajte proizvodni kalendar za bilo koju godinu, na primjer, za 2020. Da biste to učinili, u Excelu idite na karticu Datum (Ili Upit snageako ste ga instalirali kao zaseban dodatak) i odaberite S interneta (S weba). U prozoru koji se otvori zalijepite vezu na odgovarajuću godinu, kopiranu sa stranice:
Nakon što kliknete OK pojavljuje se prozor za pregled u kojem trebate kliknuti gumb Pretvori podatke (Pretvori podatke) or Za promjenu podataka (uredi podatke) i doći ćemo do prozora uređivača upita Power Query, gdje ćemo nastaviti raditi s podacima:
Odmah možete sigurno izbrisati na desnoj ploči Parametri zahtjeva (Postavke upita) korak modificirani tip (Promijenjena vrsta) Ne trebamo ga.
Tablica u stupcu praznici sadrži šifre i opise neradnih dana – njen sadržaj možete vidjeti tako da je dva puta “proletite” klikom na zelenu riječ Stol:
Da biste se vratili, morat ćete na desnoj ploči izbrisati sve korake koji su se pojavili natrag izvor (Izvor).
Druga tablica, kojoj se pristupa na sličan način, sadrži upravo ono što nam treba – datume svih neradnih dana:
Ostaje obraditi ovu ploču, naime:
1. Filtrirajte samo datume praznika (tj. one) prema drugom stupcu Atribut: t
2. Izbrišite sve stupce osim prvog – desnim klikom na naslov prvog stupca i odabirom naredbe Izbrišite ostale stupce (Ukloni druge stupce):
3. Podijeli prvi stupac po točki odvojeno za mjesec i dan s naredbom Podijeli stupac – prema razdjelniku kartica transformacija (Transformacija — Podijeli stupac — Prema razdjelniku):
4. I na kraju stvorite izračunati stupac s normalnim datumima. Da biste to učinili, na kartici Dodavanje stupca kliknite na gumb Prilagođeni stupac (Dodaj stupac — prilagođeni stupac) i unesite sljedeću formulu u prozor koji se pojavi:
=#datirano(2020, [#»Atribut:d.1″], [#»Atribut:d.2″])
Ovdje operator #date ima tri argumenta: godinu, mjesec i dan. Nakon klika na OK dobivamo traženi stupac s uobičajenim datumima vikenda i brišemo preostale stupce kao u koraku 2
Korak 2. Pretvaranje zahtjeva u funkciju
Naš sljedeći zadatak je pretvoriti upit kreiran za 2020. u univerzalnu funkciju za bilo koju godinu (broj godine bit će njegov argument). Da bismo to učinili, činimo sljedeće:
1. Proširivanje (ako već nije prošireno) ploče Upiti (Upiti) s lijeve strane u prozoru Power Query:
2. Nakon pretvaranja zahtjeva u funkciju, mogućnost da vidite korake koji čine zahtjev i lako ih uredite, nažalost, nestaje. Stoga ima smisla napraviti kopiju našeg zahtjeva i već se pozabaviti njime, a original ostaviti u rezervi. Da biste to učinili, kliknite desnom tipkom miša u lijevom oknu na zahtjev našeg kalendara i odaberite naredbu Dupliciraj.
Ponovni desni klik na rezultirajuću kopiju kalendara(2) odabrat će naredbu preimenovati (Preimenovati) i unesite novi naziv – neka bude npr. fxYear:
3. Otvaramo izvorni kod upita u internom Power Query jeziku (sažeto se naziva “M”) pomoću naredbe Napredni uređivač kartica pregled(Prikaz — napredni uređivač) i tamo napraviti male promjene kako bismo naš zahtjev pretvorili u funkciju za bilo koju godinu.
Bilo je:
Nakon:
Ako vas zanimaju detalji, onda ovdje:
- (godina kao broj)=> – izjavljujemo da će naša funkcija imati jedan numerički argument – varijablu godina
- Lijepljenje varijable godina na web vezu u koraku izvor. Budući da Power Query ne dopušta lijepljenje brojeva i teksta, pretvaramo broj godine u tekst u hodu pomoću funkcije Number.ToText
- Zamjenjujemo varijablu godine za 2020. u pretposljednjem koraku #”Dodan prilagođeni objekt«, gdje smo od fragmenata formirali datum.
Nakon što kliknete završiti naš zahtjev postaje funkcija:
Korak 3. Uvezite kalendare za sve godine
Zadnje što preostaje je napraviti zadnji glavni upit, koji će učitati podatke za sve dostupne godine i zbrojiti sve primljene datume praznika u jednu tablicu. Za ovo:
1. Kliknemo na lijevu ploču upita u sivi prazan prostor desnom tipkom miša i odabiremo redom Novi zahtjev – Drugi izvori – Prazan zahtjev (Novi upit — Iz drugih izvora — Prazan upit):
2. Trebamo generirati popis svih godina za koje ćemo tražiti kalendare, tj. 2013., 2014. … 2020. Da biste to učinili, u traku formule praznog upita koji se pojavi unesite naredbu:
Struktura:
={BrojA..BrojB}
… u Power Queryju generira popis cijelih brojeva od A do B. Na primjer, izraz
={1..5}
… dao bi popis od 1,2,3,4,5.
Pa, kako ne bismo bili kruto vezani za 2020., koristimo funkciju DateTime.LocalNow() – analog Excel funkcije DANAS (DANAS) u Power Queryju – i ekstrahirajte iz njega, pak, tekuću godinu pomoću funkcije Datum.Godina.
3. Rezultirajući skup godina, iako izgleda sasvim primjereno, nije tablica za Power Query, već poseban objekt – popis (Popis). Ali pretvaranje u tablicu nije problem: samo kliknite gumb Za stol (Za stol) u gornjem lijevom kutu:
4. Cilj! Primjena funkcije koju smo ranije izradili fxYear na rezultirajući popis godina. Da biste to učinili, na kartici Dodavanje stupca pritisni gumb Poziv prilagođene funkcije (Dodaj stupac — pozovi prilagođenu funkciju) i postavite njegov jedini argument – stupac Column1 tijekom godina:
Nakon što kliknete OK naša funkcija fxYear uvoz će raditi redom za svaku godinu i dobit ćemo stupac gdje će svaka ćelija sadržavati tablicu s datumima neradnih dana (sadržaj tablice je jasno vidljiv ako kliknete u pozadini ćelije pored riječ Stol):
Ostaje proširiti sadržaj ugniježđenih tablica klikom na ikonu s dvostrukim strelicama u zaglavlju stupca Termini (kvačicom Koristite izvorni naziv stupca kao prefiks može se ukloniti):
… i nakon klika na OK dobili smo što smo htjeli – popis svih praznika od 2013. do tekuće godine:
Prvi, već nepotrebni stupac, možete obrisati, a za drugi postaviti tip podataka podaci (Datum) na padajućem popisu u naslovu stupca:
Sam upit može se preimenovati u nešto smislenije od Zahtjev1 a zatim prenesite rezultate na list u obliku dinamičke “pametne” tablice pomoću naredbe zatvorite i preuzmite kartica Naslovna (Početna — Zatvori i učitaj):
Stvoreni kalendar možete ažurirati u budućnosti desnim klikom na tablicu ili upitom u desnom oknu putem naredbe Ažurirajte i spremite. Ili upotrijebite gumb Osvježi sve kartica Datum (Datum — Osvježi sve) ili tipkovni prečac Ctrl+drugo+F5.
To je sve.
Sada više nikada ne morate gubiti vrijeme i energiju traženja i ažuriranja popisa praznika - sada imate "vječni" proizvodni kalendar. U svakom slučaju, sve dok autori stranice http://xmlcalendar.ru/ podržavaju svoje potomke, što će, nadam se, biti još jako, jako dugo (opet im hvala!).
- Uvezite tečaj bitcoina za Excel s interneta putem Power Queryja
- Pronalaženje sljedećeg radnog dana pomoću funkcije WORKDAY
- Pronalaženje sjecišta datumskih intervala