sadržaj
Proizvodni kalendar, odnosno lista datuma, gde su u skladu sa tim označeni svi službeni radni dani i praznici – apsolutno neophodna stvar za svakog korisnika Microsoft Excel-a. U praksi, ne možete bez toga:
- u računovodstvenim obračunima (plata, radni staž, godišnji odmori…)
- u logistici – za pravilno određivanje rokova isporuke, uzimajući u obzir vikende i praznike (sjetite se klasičnog „ajde poslije praznika?“)
- u upravljanju projektima – za tačnu procjenu termina, uzimajući u obzir, opet, radne neradne dane
- svaka upotreba funkcija kao što su WORKDAY (RADNI DAN) or ČISTI RADNICI (MREŽNI DANI), jer traže spisak praznika kao argument
- 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 sistemima kao što su 1C ili SAP, jer je u njih ugrađen proizvodni kalendar. Ali šta je sa korisnicima Excela?
Takav kalendar možete, naravno, držati ručno. Ali tada ćete morati da ga ažurirate barem jednom godišnje (ili još češće, kao u “veseli” 2020.), pažljivo unoseći sve vikende, transfere i neradne dane koje je izmislila naša vlada. I onda ponovite ovu proceduru svake naredne godine. Dosada.
Šta kažete na to da malo poludite i napravite "vječni" fabrički kalendar u Excelu? Onaj koji se sam ažurira, uzima podatke sa interneta i uvijek generiše ažurnu listu neradnih dana za naknadnu upotrebu u bilo kakvim proračunima? Primamljivo?
To, zapravo, nije teško.
Izvor podataka
Glavno pitanje je gdje dobiti podatke? U potrazi za odgovarajućim izvorom, prošao sam kroz nekoliko opcija:
- Originalne uredbe se objavljuju na web stranici Vlade u PDF formatu (ovdje, na primjer, jedna od njih) i odmah nestaju – iz njih se ne mogu izvući korisne informacije.
- 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 zvaničnim izvorima, počeo sam da kopam po nezvaničnim. Ima ih mnogo 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 ga okačimo na zid, zar ne?
I u procesu pretraživanja slučajno je otkrivena divna stvar - stranica http://xmlcalendar.ru/
Bez suvišnih „problematika“, jednostavna, lagana i brza stranica, izoštrena za jedan zadatak – da svima pruži kalendar proizvodnje za željenu godinu u XML formatu. Odlično!
Ako, iznenada, niste upoznati, onda je XML tekstualni format sa sadržajem označenim posebnim
Za svaki slučaj, kontaktirao sam autore stranice i oni su mi potvrdili da stranica postoji već 7 godina, podaci o njoj se stalno ažuriraju (za to imaju čak i granu na githubu) i neće je zatvoriti. I nimalo mi ne smeta što vi i ja učitavamo podatke iz njega za bilo koji naš projekat i proračune u Excel-u. Besplatno je. Lijepo je znati da ovakvih ljudi još ima! Respect!
Ostaje učitati ove podatke u Excel pomoću dodatka Power Query (za verzije Excela 2010-2013 može se besplatno preuzeti sa Microsoft web stranice, a u verzijama Excel 2016 i novijim je već ugrađen po defaultu ).
Logika akcija će biti sljedeća:
- Mi postavljamo zahtjev za preuzimanje podataka sa stranice za bilo koju godinu
- Pretvaranje našeg zahtjeva u funkciju
- Ovu funkciju primjenjujemo na listu svih dostupnih godina, počevši od 2013. pa do tekuće godine – i dobijamo „vječni“ proizvodni kalendar sa automatskim ažuriranjem. Voila!
Korak 1. Uvezite kalendar za jednu godinu
Prvo učitajte kalendar proizvodnje za bilo koju godinu, na primjer, za 2020. Da biste to učinili, u Excelu idite na karticu podaci (ili power queryako ste ga instalirali kao poseban dodatak) i odaberite Sa interneta (Sa weba). U prozor koji se otvori zalijepite vezu do odgovarajuće godine, kopiranu sa stranice:
Posle klika na OK pojavljuje se prozor za pregled u kojem morate kliknuti na dugme Pretvori podatke (Transformirajte podatke) or Za promjenu podataka (Uredi podatke) i doći ćemo do prozora Power Query uređivača upita, gdje ćemo nastaviti rad sa podacima:
Odmah možete bezbedno izbrisati u desnom panelu Parametri zahtjeva (Postavke upita) korak modificirani tip (promijenjena vrsta) On nam ne treba.
Tabela u koloni praznici sadrži šifre i opise neradnih dana - njen sadržaj možete vidjeti tako što ćete je dvaput "propasti" klikom na zelenu riječ sto:
Da biste se vratili, morat ćete na desnom panelu izbrisati sve korake na koje ste se vratili izvor (Izvor).
Druga tabela, kojoj se može pristupiti na sličan način, sadrži upravo ono što nam treba – datume svih neradnih dana:
Ostaje obraditi ovu ploču, i to:
1. Filtrirajte samo datume praznika (tj. one) po drugoj koloni Atribut:t
2. Izbrišite sve kolone osim prve – desnim klikom na naslov prve kolone i odabirom naredbe Izbrišite druge kolone (Ukloni druge kolone):
3. Podijelite prvu kolonu po tački odvojeno za mjesec i dan pomoću komande Razdvojite kolonu – po graničniku tabulator transformacija (Transformacija — Podijeli kolonu — Po graničniku):
4. I konačno kreirajte izračunatu kolonu sa normalnim datumima. Da biste to učinili, na kartici Dodavanje kolone kliknite na dugme Prilagođena kolona (Dodaj kolonu — prilagođenu kolonu) i u prozor koji se pojavi unesite sljedeću formulu:
=#dated(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])
Ovdje operator #date ima tri argumenta: godinu, mjesec i dan. Nakon klika na OK dobijamo traženu kolonu sa normalnim vikend datumima, a preostale kolone brišemo kao u koraku 2
Korak 2. Pretvaranje zahtjeva u funkciju
Naš sljedeći zadatak je da pretvorimo upit kreiran za 2020. u univerzalnu funkciju za bilo koju godinu (broj godine će biti njen argument). Da bismo to učinili, radimo sljedeće:
1. Proširivanje (ako već nije prošireno) panel Upiti (Upite) lijevo u prozoru Power Query:
2. Nakon pretvaranja zahtjeva u funkciju, mogućnost da se vide koraci koji čine zahtjev i da se jednostavno uređuju, nažalost, nestaje. Stoga je logično napraviti kopiju našeg zahtjeva i već se zabaviti s njim, a original ostaviti u rezervi. Da biste to uradili, kliknite desnim tasterom miša u levom oknu na našem zahtevu za kalendar i izaberite komandu Duplicate.
Desnim klikom ponovo na rezultujuću kopiju kalendara(2) će se izabrati naredba Preimenuj (Preimenuj) i unesite novo ime – neka bude npr. fxYear:
3. Otvaramo izvorni kod upita u internom Power Query jeziku (sažeto se zove “M”) koristeći naredbu Napredni uređivač tabulator pregled(Prikaz — Napredni uređivač) i napravimo male promjene kako bismo naš zahtjev pretvorili u funkciju za bilo koju godinu.
Bilo je:
Posle:
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 do web linka u koraku izvor. Budući da vam Power Query ne dozvoljava lijepljenje brojeva i teksta, konvertujemo broj godine u tekst u hodu pomoću funkcije Number.ToText
- Varijablu godine za 2020. zamjenjujemo u pretposljednjem koraku #”Dodan prilagođeni objekat«, gdje smo od fragmenata formirali datum.
Posle klika na završiti naš zahtjev postaje funkcija:
Korak 3. Uvezite kalendare za sve godine
Zadnje što preostaje je napraviti posljednji glavni upit, koji će učitati podatke za sve dostupne godine i dodati sve primljene datume praznika u jednu tabelu. Za ovo:
1. Desnom tipkom miša kliknemo na lijevi panel upita u sivi prazan prostor i odabiremo uzastopno Novi zahtjev – Drugi izvori – Prazan zahtjev (Novi upit — Iz drugih izvora — Prazan upit):
2. Moramo da generišemo listu svih godina za koje ćemo tražiti kalendare, odnosno 2013, 2014…2020. Da biste to uradili, u traku formule praznog upita koji se pojavi unesite naredbu:
Struktura:
={BrojA..BrojB}
… u Power Queryju generiše listu cijelih brojeva od A do B. Na primjer, izraz
={1..5}
… bi proizveo listu od 1,2,3,4,5.
Pa, da ne bismo bili strogo vezani za 2020., koristimo funkciju DateTime.LocalNow() – analog Excel funkcije DANAS (DANAS) u Power Queryju – i iz njega izvući, zauzvrat, tekuću godinu po funkciji Datum.Godina.
3. Dobijeni skup godina, iako izgleda sasvim adekvatno, nije tabela za Power Query, već poseban objekat – lista (Lista). Ali konvertovanje u tabelu nije problem: samo kliknite na dugme Za sto (Do stola) u gornjem lijevom uglu:
4. Finish line! Primjena funkcije koju smo kreirali ranije fxYear na rezultirajuću listu godina. Da biste to učinili, na kartici Dodavanje kolone pritisnite dugme Pozovite prilagođenu funkciju (Dodaj kolonu — Pozovite prilagođenu funkciju) i postavite svoj jedini argument – kolonu Column1 tokom godina:
Posle klika na OK naša funkcija fxYear uvoz će raditi redom za svaku godinu i dobićemo kolonu u kojoj će svaka ćelija sadržavati tabelu sa datumima neradnih dana (sadržaj tabele je jasno vidljiv ako kliknete u pozadini ćelije pored Riječ sto):
Ostaje proširiti sadržaj ugniježđenih tabela klikom na ikonu sa dvostrukim strelicama u zaglavlju kolone Termini (kvačica Koristite originalno ime kolone kao prefiks može se ukloniti):
… i nakon klika na OK dobijamo ono što smo želeli – spisak svih praznika od 2013. do tekuće godine:
Prvu, već nepotrebnu kolonu, možete izbrisati, a za drugu podesiti tip podataka datum (Datum) na padajućoj listi u naslovu kolone:
Sam upit se može preimenovati u nešto značajnije od Zahtjev1 a zatim učitajte rezultate na list u obliku dinamičke “pametne” tablice koristeći naredbu zatvorite i preuzmite tabulator Početna (Početna — Zatvori i učitaj):
Kreirani kalendar možete ažurirati u budućnosti desnim klikom na tabelu ili upitom u desnom oknu putem naredbe Ažurirajte i sačuvajte. Ili koristite dugme Osvježi sve tabulator podaci (Datum — Osvježi sve) ili prečica na tastaturi Ctrl+alt+F5.
To je sve.
Sada više nikada ne morate gubiti vrijeme i razmišljanje tražeći i ažurirajući listu praznika – sada imate „vječni“ kalendar proizvodnje. U svakom slučaju, sve dok autori sajta http://xmlcalendar.ru/ podržavaju svoje potomstvo, što će, nadam se, još jako, jako dugo (hvala im još jednom!).
- Uvezite bitcoin stopu za excel s interneta putem Power Queryja
- Pronalaženje sljedećeg radnog dana pomoću funkcije WORKDAY
- Pronalaženje preseka datumskih intervala