Sisu
Tootmiskalender ehk kuupäevade loetelu, kuhu on vastavalt märgitud kõik ametlikud tööpäevad ja pühad – igale Microsoft Exceli kasutajale igati vajalik asi. Praktikas ei saa te ilma selleta hakkama:
- raamatupidamisarvestuses (palk, tööstaaž, puhkused jne)
- logistikas - tarneaegade õigeks määramiseks, arvestades nädalavahetusi ja pühasid (pidage meeles klassikalist "tule pärast pühi?")
- projektijuhtimises – tähtaegade õigeks hindamiseks, arvestades jällegi töö- ja puhkepäevi
- mis tahes funktsioonide kasutamine nagu TÖÖPÄEV (TÖÖPÄEV) or PUHTAD TÖÖLISED (NETWORKDYS), sest nad nõuavad argumendina pühade nimekirja
- kui kasutate Power Pivotis ja Power BI-s Time Intelligence'i funktsioone (nt TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR jne)
- … jne jne – palju näiteid.
Lihtsam on neil, kes töötavad ettevõtte ERP-süsteemides nagu 1C või SAP, kuna tootmiskalender on neisse sisse ehitatud. Aga kuidas on lood Exceli kasutajatega?
Loomulikult saate sellist kalendrit käsitsi pidada. Kuid siis peate seda värskendama vähemalt kord aastas (või veelgi sagedamini, nagu “rõõmsal” 2020), sisestades hoolikalt kõik meie valitsuse välja mõeldud nädalavahetused, ülekanded ja vabad päevad. Ja siis korrake seda protseduuri igal järgmisel aastal. Igavus.
Kuidas oleks, kui läheks natuke hulluks ja teeks Excelis igavese tehasekalendri? Selline, mis uuendab ennast, võtab andmeid Internetist ja genereerib alati ajakohase nimekirja vabadest päevadest, et seda hiljem mis tahes arvutustes kasutada? Ahvatlev?
Selle tegemine pole tegelikult sugugi keeruline.
Andmeallikas
Peamine küsimus on, kust saada andmeid? Sobiva allika otsimisel läbisin mitu võimalust:
- Algsed määrused avaldatakse valitsuse kodulehel PDF-vormingus (siin näiteks üks neist) ja kaovad kohe – kasulikku infot sealt välja tõmmata ei saa.
- Esmapilgul tundus ahvatlev variant “Föderatsiooni avaandmete portaal”, kus on olemas vastav andmekogum, kuid lähemal uurimisel osutus kõik kurvaks. Sait on Excelisse importimiseks kohutavalt ebamugav, tehniline tugi ei reageeri (iseisoleeritud?) ja andmed ise on seal ammu vananenud – 2020. aasta tootmiskalender on viimati uuendatud 2019. aasta novembris (häbi!) ja muidugi ei sisalda näiteks meie koroonaviirust ja 2020. aasta hääletusnädalavahetust.
Ametlikes allikates pettunud, hakkasin kaevama mitteametlikke. Internetis on neid palju, kuid enamik neist on jällegi täiesti sobimatud Excelisse importimiseks ja annavad ilusate piltide kujul tootmiskalendrit. Aga meie asi pole seda seinale riputada, eks?
Ja otsimise käigus avastati kogemata imeline asi – sait http://xmlcalendar.ru/
Ilma tarbetute “sabasteta” lihtne, kerge ja kiire sait, mis on teravdatud üheks ülesandeks – anda kõigile XML-vormingus soovitud aasta tootmiskalender. Suurepärane!
Kui te äkki ei tea, siis on XML tekstivorming, mille sisu on märgistatud spetsiaalsega
Igaks juhuks võtsin ühendust saidi autoritega ja nad kinnitasid, et sait on eksisteerinud 7 aastat, sellel olevad andmed täienevad pidevalt (nendel on selleks isegi githubis filiaal) ja nad ei kavatse seda sulgeda. Ja mul pole üldse midagi selle vastu, et teie ja mina laadime sealt andmeid mis tahes meie projekti ja Exceli arvutuste jaoks. On vaba. Tore on tõdeda, et selliseid inimesi veel leidub! Respekt!
Jääb üle laadida need andmed Excelisse Power Query lisandmooduli abil (Exceli versioonide 2010–2013 jaoks saab selle tasuta alla laadida Microsofti veebisaidilt ning Excel 2016 ja uuemates versioonides on see vaikimisi juba sisse ehitatud ).
Toimingute loogika on järgmine:
- Taotleme saidilt andmete allalaadimist ühe aasta jooksul
- Meie taotluse muutmine funktsiooniks
- Rakendame selle funktsiooni kõigi saadaolevate aastate loendisse, alates 2013. aastast kuni jooksva aastani – ja saame automaatse uuendusega "igavese" tootmiskalendri. Voila!
1. samm. Importige kalender üheks aastaks
Esmalt laadige tootmiskalender mis tahes aasta kohta, näiteks 2020. Selleks minge Excelis vahekaardile kuupäev (Või Toite päringkui installisite selle eraldi lisandmoodulina) ja valige Internetist (veebist). Avanevas aknas kleepige saidilt kopeeritud vastava aasta link:
Pärast klõpsamist nupul OK ilmub eelvaate aken, kus peate klõpsama nuppu Teisenda andmed (teisenda andmed) or Andmete muutmiseks (Muuda andmeid) ja jõuame Power Query päringuredaktori aknasse, kus jätkame andmetega töötamist:
Saate kohe paremal paneelil turvaliselt kustutada Päringu parameetrid (päringu seaded) samm muudetud tüüp (Muudetud tüüp) Me ei vaja teda.
Tabel pühade veerus sisaldab koode ja puhkepäevade kirjeldusi – selle sisu näed kaks korda “läbi kukkudes” rohelisele sõnale klõpsates Tabel:
Tagasi naasmiseks peate parempoolselt paneelilt kustutama kõik tagasi ilmunud sammud allikas (Allikas).
Teises tabelis, kuhu pääseb ligi sarnaselt, on täpselt see, mida vajame – kõikide vabade päevade kuupäevad:
Jääb seda plaati töödelda, nimelt:
1. Teise veeru järgi filtreerige ainult pühade kuupäevad (st kuupäevad). Atribuut: t
2. Kustutage kõik veerud peale esimese – paremklõpsates esimese veeru pealkirjal ja valides käsu Kustutage muud veerud (Eemalda muud veerud):
3. Jagage esimene veerg punktide kaupa eraldi kuu ja päeva jaoks käsuga Tükeldatud veerg – eraldaja järgi tab Transformation (Teisendus – veeru poolitamine – eraldaja järgi):
4. Ja lõpuks looge tavaliste kuupäevadega arvutatud veerg. Selleks vahekaardil Veeru lisamine klõpsa nupul Kohandatud veerg (Lisa veerg – kohandatud veerg) ja sisestage ilmuvas aknas järgmine valem:
=#kuupäevaga(2020, [#»Atribuut:d.1″], [#»Atribuut:d.2″])
Siin on operaatoril #date kolm argumenti: vastavalt aasta, kuu ja päev. Pärast klõpsamist OK saame vajaliku veeru tavaliste nädalavahetuse kuupäevadega ja kustutame ülejäänud veerud nagu toimingus 2
2. samm. Taotluse muutmine funktsiooniks
Meie järgmiseks ülesandeks on teisendada 2020. aasta jaoks loodud päring mis tahes aasta universaalseks funktsiooniks (selle argumendiks saab aastanumber). Selleks teeme järgmist.
1. Paneeli laiendamine (kui pole veel laiendatud). Päringud (päringud) Power Query aknas vasakul:
2. Pärast päringu funktsiooniks teisendamist kaob kahjuks võimalus näha päringu moodustavaid samme ja neid lihtsalt redigeerida. Seetõttu on mõttekas meie palvest koopia teha ja juba sellega möllata ning originaal varuks jätta. Selleks paremklõpsake vasakpoolsel paanil meie kalendripäringul ja valige käsk Duplicate.
Paremklõpsake uuesti saadud kalendri (2) koopial, valides käsu Nimeta (Nimeta ümber) ja sisestage uus nimi – olgu see näiteks fxYear:
3. Avame päringu lähtekoodi sisemises Power Query keeles (seda nimetatakse lühidalt "M"), kasutades käsku Täiustatud redaktor tab Ülevaade(Vaade – täiustatud redaktor) ja tehke seal väikesed muudatused, et muuta meie taotlus mis tahes aasta funktsiooniks.
See oli:
Pärast:
Kui olete huvitatud üksikasjadest, siis siin:
- (aasta numbrina)=> – deklareerime, et meie funktsioonil on üks arvargument – muutuja aasta
- Muutuja kleepimine aasta etapis veebilingile allikas. Kuna Power Query ei võimalda numbreid ja teksti liimida, siis teisendame aastanumbri käigult tekstiks, kasutades funktsiooni Number.To Text
- Asendame aastamuutuja 2020. aastaga eelviimases etapis #"Lisatud kohandatud objekt«, kus fragmentidest moodustasime kuupäeva.
Pärast klõpsamist nupul lõpp meie taotlusest saab funktsioon:
Samm 3. Importige kõigi aastate kalendrid
Viimasena jääb üle teha viimane põhipäring, mis laeb üles kõigi saadaolevate aastate andmed ja liidab ühte tabelisse kõik laekunud pühade kuupäevad. Selle jaoks:
1. Klõpsame hiire parema nupuga vasakpoolses päringupaneelis hallis tühjas kohas ja valime järjest Uus päring – muud allikad – tühi päring (Uus päring – muudest allikatest – tühi päring):
2. Peame koostama loendi kõigist aastatest, mille kohta kalendreid küsime, st 2013, 2014 … 2020. Selleks sisestage ilmuva tühja päringu valemiribale käsk:
Struktuur:
={ArvA..ArvB}
… genereerib Power Query täisarvude loendi vahemikus A kuni B. Näiteks avaldis
={1..5}
… koostaks nimekirja 1,2,3,4,5.
Noh, selleks, et mitte olla jäigalt seotud 2020. aastaga, kasutame funktsiooni DateTime.LocalNow() – Exceli funktsiooni analoog TÄNA (TÄNA) Power Querys – ja eraldage sellest omakorda funktsiooni järgi jooksev aasta Kuupäev.Aasta.
3. Saadud aastate kogum, kuigi see näeb välja üsna adekvaatne, pole Power Query tabel, vaid spetsiaalne objekt - nimekiri (loend). Kuid selle tabeliks teisendamine pole probleem: klõpsake lihtsalt nuppu Laua juurde (Tabeli juurde) vasakus ülanurgas:
4. Finišijoonel! Varem loodud funktsiooni rakendamine fxYear saadud aastate loendisse. Selleks vahekaardil Veeru lisamine vajuta nuppu Helista kohandatud funktsioonile (Lisa veerg – kutsuge kohandatud funktsioon) ja määrake selle ainus argument – veerg Column1 aastate jooksul:
Pärast klõpsamist nupul OK meie funktsioon fxYear import toimib kordamööda igal aastal ja saame veeru, kus igas lahtris on tabel puhkepäevade kuupäevadega (tabeli sisu on selgelt näha, kui klõpsate lahtri taustal sõna Tabel):
Jääb üle pesastatud tabelite sisu laiendada, klõpsates veeru päises topeltnooltega ikooni Kuupäevad (linnuke Kasutage eesliitena algset veeru nime seda saab eemaldada):
… ja pärast klõpsamist OK saame, mida tahtsime – kõigi pühade loetelu alates 2013. aastast kuni käesoleva aastani:
Esimese, juba ebavajaliku veeru saab kustutada ja teise jaoks määrata andmetüübi andmed (Kuupäev) veeru pealkirja ripploendis:
Päringu enda saab ümber nimetada millekski tähendusrikkamaks kui Taotlus1 ja seejärel laadige tulemused lehele üles dünaamilise "nutika" tabeli kujul, kasutades käsku sulgege ja laadige alla tab Avaleht (Kodu – sulge ja laadi):
Loodud kalendrit saab edaspidi uuendada paremklõpsates paremas paanis tabelis või päringul läbi käsu Värskenda ja salvesta. Või kasutage nuppu Värskenda kõike tab kuupäev (Kuupäev — Värskenda kõike) või klaviatuuri otsetee Ctrl+muu+F5.
See on kõik.
Nüüd ei pea te enam kunagi aega ja mõtteainet raiskama pühadenimekirja otsimisele ja uuendamisele – nüüd on teil "igavene" tootmiskalender. Igal juhul seni, kuni saidi http://xmlcalendar.ru/ autorid toetavad oma järglasi, mis, ma loodan, on väga-väga pikka aega (tänu neile veelkord!).
- Importige bitcoini määr, et Power Query kaudu Internetist silma paista
- Järgmise tööpäeva leidmine funktsiooni WORKDAY abil
- Kuupäevaintervallide ristumiskoha leidmine