Efnisyfirlit
Framleiðsludagatal, þ.e. listi yfir dagsetningar, þar sem allir opinberir virkir dagar og frídagar eru merktir í samræmi við það – algjörlega nauðsynlegt fyrir alla notendur Microsoft Excel. Í reynd geturðu ekki verið án þess:
- í bókhaldsútreikningum (laun, starfsaldur, orlof …)
- í flutningum – fyrir rétta ákvörðun á afhendingartíma, að teknu tilliti til helgar og frídaga (mundu eftir klassíska „komdu eftir frí?”)
- í verkefnastjórnun – fyrir rétt mat á kjörum, að teknu tilliti til, aftur, vinnu-frídaga
- hvers kyns notkun á aðgerðum eins og VINNUDAGUR (VIRKISDAGUR) or HREINIR VERKMENN (NETDAGAR), vegna þess að þeir krefjast lista yfir frídaga sem rök
- þegar tímagreindaraðgerðir eru notaðar (eins og TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, osfrv.) í Power Pivot og Power BI
- … o.s.frv. osfrv. – fullt af dæmum.
Það er auðveldara fyrir þá sem vinna í ERP kerfum fyrirtækja eins og 1C eða SAP þar sem framleiðsludagatalið er innbyggt í þau. En hvað með Excel notendur?
Þú getur auðvitað haldið slíku dagatali handvirkt. En þá verður þú að uppfæra það að minnsta kosti einu sinni á ári (eða jafnvel oftar, eins og í „gleði“ 2020), slá vandlega inn allar helgar, millifærslur og óvinnudaga sem stjórnvöld okkar fundu upp. Og endurtaktu síðan þessa aðferð á næsta ári. Leiðindi.
Hvernig væri að verða svolítið brjálaður og búa til „eilíft“ verksmiðjudagatal í Excel? Einn sem uppfærir sjálfan sig, tekur gögn af netinu og býr alltaf til uppfærðan lista yfir óvirka daga til að nota síðar í hvaða útreikningum sem er? Freistandi?
Að gera þetta, í raun, er alls ekki erfitt.
Gagnaheimild
Aðalspurningin er hvar á að fá gögnin? Í leit að viðeigandi heimild fór ég í gegnum nokkra möguleika:
- Upprunalegu tilskipanirnar eru birtar á vefsíðu ríkisstjórnarinnar á PDF-formi (hér, til dæmis ein þeirra) og hverfa strax – ekki er hægt að draga úr þeim gagnlegar upplýsingar.
- Freistandi valkostur, við fyrstu sýn, virtist vera „Opna gagnagátt sambandsins“, þar sem samsvarandi gagnasett er til, en við nánari athugun reyndist allt sorglegt. Síðan er hræðilega óþægileg til að flytja inn í Excel, tækniaðstoð bregst ekki við (sjálfeinangruð?) og gögnin sjálf eru úrelt þar í langan tíma – framleiðsludagatalið fyrir 2020 var síðast uppfært í nóvember 2019 (skömm!) og , auðvitað inniheldur ekki „kórónavírusinn“ okkar og „kosningahelgina“ 2020, til dæmis.
Ég var vonsvikinn með opinberar heimildir og fór að grafa óopinberar heimildir. Þeir eru margir á netinu, en flestir þeirra, aftur, eru algjörlega óhentugir til að flytja inn í Excel og gefa út framleiðsludagatal í formi fallegra mynda. En það er ekki okkar að hengja það upp á vegg, ekki satt?
Og í leitinni uppgötvaðist dásamlegur hlutur fyrir tilviljun - síðan http://xmlcalendar.ru/
Án óþarfa „frills“, einföld, létt og hröð síða, skerpt fyrir eitt verkefni – til að gefa öllum framleiðsludagatal fyrir viðkomandi ár á XML-sniði. Æðislegt!
Ef þú ert allt í einu ekki meðvitaður um það, þá er XML textasnið með efni merkt með sérstöku
Bara svona, ég hafði samband við höfunda síðunnar og þeir staðfestu að síðan hafi verið til í 7 ár, gögnin á henni eru stöðugt uppfærð (þeir eru meira að segja með útibú á github fyrir þetta) og þeir ætla ekki að loka henni. Og mér er alveg sama um að þú og ég hleðum gögnum úr því fyrir einhver verkefni okkar og útreikninga í Excel. Er ókeypis. Það er gaman að vita að enn er til svona fólk! Virðing!
Það er eftir að hlaða þessum gögnum inn í Excel með Power Query viðbótinni (fyrir útgáfur af Excel 2010-2013 er hægt að hlaða þeim niður ókeypis af vefsíðu Microsoft og í útgáfum af Excel 2016 og nýrri er það sjálfgefið innbyggt þegar ).
Rökfræði aðgerða verður sem hér segir:
- Við gerum beiðni um að hlaða niður gögnum af síðunni fyrir eitt ár
- Að breyta beiðni okkar í aðgerð
- Við notum þessa aðgerð á lista yfir öll tiltæk ár, frá og með 2013 og til yfirstandandi árs – og við fáum „ælíft“ framleiðsludagatal með sjálfvirkri uppfærslu. Voila!
Skref 1. Flytja inn dagatal fyrir eitt ár
Fyrst skaltu hlaða framleiðsludagatalinu fyrir eitt ár, til dæmis fyrir 2020. Til að gera þetta, í Excel, farðu á flipann Gögn (Eða Orkufyrirspurnef þú settir það upp sem sérstaka viðbót) og veldu Af netinu (Af vefnum). Í glugganum sem opnast skaltu líma hlekkinn á samsvarandi ár, afritað af síðunni:
Eftir að smella á OK Forskoðunargluggi birtist þar sem þú þarft að smella á hnappinn Umbreyta gögnum (Umbreyta gögnum) or Til að breyta gögnum (Breyta gögnum) og við munum komast í Power Query fyrirspurnarritaragluggann, þar sem við munum halda áfram að vinna með gögnin:
Strax geturðu örugglega eytt í hægri spjaldið Beiðni um færibreytur (Fyrirspurnarstillingar) skref breytt gerð (Breytt gerð) Við þurfum hann ekki.
Taflan í hátíðardálknum inniheldur kóða og lýsingar á óvinnudögum - þú getur séð innihald hennar með því að „falla í gegnum“ hana tvisvar með því að smella á græna orðið Tafla:
Til að fara til baka þarftu að eyða öllum skrefum sem hafa birst aftur á hægri spjaldið Heimild (Heimild).
Önnur taflan, sem hægt er að nálgast á svipaðan hátt, inniheldur nákvæmlega það sem við þurfum - dagsetningar allra óvirkra daga:
Það er eftir að vinna þessa plötu, þ.e.:
1. Sía aðeins frídagsetningar (þ.e. þær) eftir öðrum dálki Eiginleiki: t
2. Eyða öllum dálkum nema þeim fyrsta - með því að hægrismella á fyrirsögn fyrsta dálksins og velja skipunina Eyða öðrum dálkum (Fjarlægja aðra dálka):
3. Skiptu fyrsta dálki fyrir punkt sérstaklega fyrir mánuð og dag með skipun Skipta dálki - eftir afmörkun flipi Umbreyting (Umbreyta - Skipta dálki - Eftir afmörkun):
4. Og að lokum búðu til útreiknaðan dálk með venjulegum dagsetningum. Til að gera þetta, á flipanum Að bæta við dálki smelltu á hnappinn Sérsniðinn dálkur (Bæta við dálki – sérsniðnum dálki) og sláðu inn eftirfarandi formúlu í glugganum sem birtist:
=#dagsett(2020, [#»eigin:d.1″], [#»eigin:d.2″])
Hér hefur #date operator þrjú rök: ár, mánuður og dagur, í sömu röð. Eftir að hafa smellt á OK við fáum nauðsynlegan dálk með venjulegum helgardagsetningum og eyðum þeim dálkum sem eftir eru eins og í skrefi 2
Skref 2. Að breyta beiðninni í aðgerð
Næsta verkefni okkar er að umbreyta fyrirspurninni sem búin var til fyrir 2020 í alhliða fall fyrir hvaða ár sem er (ártalan verður rök hennar). Til að gera þetta gerum við eftirfarandi:
1. Stækka (ef ekki þegar stækkað) spjaldið Fyrirspurnir (Fyrirspurnir) til vinstri í Power Query glugganum:
2. Eftir að beiðninni hefur verið breytt í aðgerð hverfur möguleikinn til að sjá skrefin sem samanstendur af beiðninni og breyta þeim auðveldlega, því miður. Þess vegna er skynsamlegt að gera afrit af beiðni okkar og ærslast þegar með henni og skilja frumritið eftir. Til að gera þetta skaltu hægrismella á vinstri rúðuna á dagatalsbeiðninni okkar og velja afrit skipunina.
Með því að hægrismella aftur á afritið af dagatalinu (2) sem myndast verður skipunin valin Endurnefna (Endurnefna) og sláðu inn nýtt nafn – láttu það vera td. fxÁr:
3. Við opnum frumkóðann fyrir fyrirspurnina á innra Power Query tungumálinu (það er í stuttu máli kallað „M“) með skipuninni Háþróaður ritstjóri flipi Review(Skoða — Ítarleg ritstjóri) og gera litlar breytingar þar til að breyta beiðni okkar í aðgerð fyrir hvaða ár sem er.
Það var:
Eftir:
Ef þú hefur áhuga á smáatriðum, þá hér:
- (ár sem tala)=> – við lýsum því yfir að fallið okkar muni hafa eina tölugildi – breytu ári
- Límir breytuna ári á veftengil í skrefi Heimild. Þar sem Power Query leyfir þér ekki að líma tölur og texta, umbreytum við ártalinu í texta á flugi með aðgerðinni Number.ToText
- Við setjum út árbreytuna fyrir árið 2020 í næstsíðasta skrefið #“Bætti við sérsniðnum hlut«, þar sem við mynduðum dagsetninguna úr brotunum.
Eftir að smella á Ljúka beiðni okkar verður aðgerð:
Skref 3. Flytja inn dagatöl fyrir öll ár
Það síðasta sem er eftir er að gera síðustu aðalfyrirspurnina, sem mun hlaða upp gögnum fyrir öll tiltæk ár og bæta öllum mótteknum orlofsdögum í eina töflu. Fyrir þetta:
1. Við smellum á vinstri fyrirspurnarspjaldið í gráu tómu svæði með hægri músarhnappi og veljum í röð Ný beiðni – Aðrar heimildir – Tóm beiðni (Ný fyrirspurn - Frá öðrum aðilum - Auð fyrirspurn):
2. Við þurfum að búa til lista yfir öll ár sem við munum biðja um dagatöl fyrir, þ.e. 2013, 2014 … 2020. Til að gera þetta skaltu slá inn skipunina í formúlustikunni í tómu fyrirspurninni sem birtist:
Uppbygging:
={NumberA..NumberB}
… í Power Query býr til lista yfir heiltölur frá A til B. Til dæmis, segðin
={1..5}
… myndi gefa lista yfir 1,2,3,4,5.
Jæja, til að vera ekki bundin stíft við 2020, notum við aðgerðina DateTime.LocalNow() – hliðstæða Excel fallsins Í dag (Í DAG) í Power Query - og draga úr henni aftur á móti núverandi ár með aðgerðinni Dagsetning.Ár.
3. Árið sem myndast, þó að það líti alveg fullnægjandi út, er ekki tafla fyrir Power Query, heldur sérstakur hlutur - lista (listi). En að breyta því í töflu er ekki vandamál: smelltu bara á hnappinn Til borðs (Til borðs) í efra vinstra horninu:
4. Marklína! Að beita aðgerðinni sem við bjuggum til áðan fxÁr til áralistans sem út kom. Til að gera þetta, á flipanum Að bæta við dálki Ýttu á takkann Hringdu í sérsniðna aðgerð (Bæta við dálki - kalla fram sérsniðna aðgerð) og stilltu einu rökin hennar - dálkinn Column1 í gegnum árin:
Eftir að smella á OK hlutverk okkar fxÁr innflutningurinn mun virka til skiptis fyrir hvert ár og við fáum dálk þar sem hver reit mun innihalda töflu með dagsetningum óvirkra daga (innihald töflunnar sést vel ef smellt er í bakgrunni reitsins við hliðina á orðið Tafla):
Það er eftir að stækka innihald hreiðra taflna með því að smella á táknið með tvöföldum örvum í dálkhausnum Dagsetningar (merkið Notaðu upprunalega dálknafnið sem forskeyti það er hægt að fjarlægja það):
… og eftir að hafa smellt á OK við fáum það sem við vildum – listi yfir alla frídaga frá 2013 til yfirstandandi árs:
Fyrsta, þegar óþarfa dálkinn, er hægt að eyða, og fyrir þann seinni, stilltu gagnategundina dagsetning (Dagsetning) í fellilistanum í dálkfyrirsögninni:
Fyrirspurnina sjálfa er hægt að endurnefna eitthvað þýðingarmeira en Beiðni 1 og hlaðið síðan niðurstöðunum inn á blaðið í formi kraftmikillar „snjallrar“ töflu með skipuninni loka og hlaða niður flipi Heim (Heima — Loka og hlaða):
Þú getur uppfært búið dagatal í framtíðinni með því að hægrismella á töfluna eða spyrjast fyrir í hægri glugganum í gegnum skipunina Uppfærðu og vistaðu. Eða notaðu hnappinn Endurnýjaðu allt flipi Gögn (Dagsetning - endurnýja allt) eða flýtilykla Ctrl+Alt+F5.
Það er allt og sumt.
Nú þarftu aldrei aftur að eyða tíma og hugsunarhugsun í að leita að og uppfæra lista yfir hátíðir – nú hefurðu „ælíft“ framleiðsludagatal. Í öllum tilvikum, svo lengi sem höfundar síðunnar http://xmlcalendar.ru/ styðja afkvæmi sín, sem ég vona að verði í mjög, mjög langan tíma (þökk sé þeim aftur!).
- Flyttu inn bitcoin hlutfall til að skara fram úr af internetinu í gegnum Power Query
- Að finna næsta virka dag með því að nota WORKDAY aðgerðina
- Að finna gatnamót dagsetningarbila