Contents [show]
Produksiekalender, dit wil sê 'n lys van datums, waar alle amptelike werksdae en vakansiedae dienooreenkomstig gemerk is – 'n absoluut noodsaaklike ding vir enige gebruiker van Microsoft Excel. In die praktyk kan jy nie daarsonder klaarkom nie:
- in rekeningkundige berekeninge (salaris, dienstyd, vakansies …)
- in logistiek – vir die korrekte bepaling van afleweringstye, met inagneming van naweke en vakansiedae (onthou die klassieke "kom aan na die vakansie?")
- in projekbestuur – vir die korrekte beraming van terme, met inagneming, weereens, werk-nie-werksdae
- enige gebruik van funksies soos WERSDAG (WERKDAG) or SUIWER WERKERS (NETWERKDAE), want hulle vereis 'n lys van vakansiedae as 'n argument
- wanneer Time Intelligence-funksies (soos TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, ens.) in Power Pivot en Power BI gebruik word
- … ens. ens. – baie voorbeelde.
Dit is makliker vir diegene wat in korporatiewe ERP-stelsels soos 1C of SAP werk, aangesien die produksiekalender daarin ingebou is. Maar wat van Excel-gebruikers?
U kan natuurlik so 'n kalender met die hand hou. Maar dan sal jy dit ten minste een keer per jaar moet bywerk (of selfs meer dikwels, soos in die "jolly" 2020), en noukeurig alle naweke, oorplasings en nie-werkdae wat deur ons regering uitgevind is, moet inskryf. En herhaal dan hierdie prosedure elke volgende jaar. Verveling.
Hoe gaan dit met 'n bietjie mal en maak 'n "ewigdurende" fabriekskalender in Excel? Een wat homself opdateer, data van die internet af neem en altyd 'n bygewerkte lys van nie-werksdae genereer vir latere gebruik in enige berekeninge? Aanloklik?
Om dit te doen, is eintlik glad nie moeilik nie.
Data bron
Die hoofvraag is waar om die data te kry? Op soek na 'n geskikte bron, het ek deur verskeie opsies gegaan:
- Die oorspronklike dekrete word op die regering se webwerf in PDF-formaat gepubliseer (hier, byvoorbeeld een daarvan) en verdwyn dadelik – nuttige inligting kan nie daaruit gehaal word nie.
- 'N Aanloklike opsie, met die eerste oogopslag, blyk die "Open Data Portal of the Federation" te wees, waar daar 'n ooreenstemmende datastel is, maar by nadere ondersoek het alles hartseer geword. Die webwerf is verskriklik ongerieflik vir invoer in Excel, tegniese ondersteuning reageer nie (self-geïsoleer?), en die data self is lankal verouderd daar – die produksiekalender vir 2020 is laas in November 2019 opgedateer (skande!) en bevat natuurlik nie ons "coronavirus" en die "stem"-naweek van 2020, byvoorbeeld.
Ontnugter met amptelike bronne het ek begin om nie-amptelike bronne te grawe. Daar is baie van hulle op die internet, maar die meeste van hulle is weer heeltemal ongeskik vir invoer in Excel en gee 'n produksiekalender in die vorm van pragtige prente uit. Maar dit is nie vir ons om dit aan die muur te hang nie, reg?
En in die proses van soek is 'n wonderlike ding per ongeluk ontdek - die webwerf http://xmlcalendar.ru/
Sonder onnodige “frills”, ’n eenvoudige, ligte en vinnige werf, opgeskerp vir een taak – om vir almal ’n produksiekalender vir die verlangde jaar in XML-formaat te gee. Uitstekend!
As jy skielik nie weet nie, dan is XML 'n teksformaat met inhoud gemerk met spesiale
Net vir ingeval, ek het die skrywers van die webwerf gekontak en hulle het bevestig dat die webwerf al 7 jaar bestaan, die data daarop word voortdurend opgedateer (hulle het selfs 'n tak op github hiervoor) en hulle gaan dit nie toemaak nie. En ek gee glad nie om dat ek en jy data daaruit laai vir enige van ons projekte en berekeninge in Excel nie. Is gratis. Dis lekker om te weet daar is nog sulke mense! Respek!
Dit bly om hierdie data in Excel te laai deur die Power Query-byvoeging te gebruik (vir weergawes van Excel 2010-2013 kan dit gratis van die Microsoft-webwerf afgelaai word, en in weergawes van Excel 2016 en nuwer is dit reeds by verstek ingebou ).
Die logika van aksies sal soos volg wees:
- Ons rig 'n versoek om data van die webwerf af te laai vir enige een jaar
- Om ons versoek in 'n funksie te verander
- Ons pas hierdie funksie toe op die lys van alle beskikbare jare, vanaf 2013 en tot die huidige jaar – en ons kry 'n "ewigdurende" produksiekalender met outomatiese opdatering. Voila!
Stap 1. Voer 'n kalender vir een jaar in
Laai eers die produksiekalender vir enige een jaar, byvoorbeeld vir 2020. Om dit te doen, in Excel, gaan na die oortjie data (Of Kragnavraagas jy dit as 'n aparte byvoeging geïnstalleer het) en kies Van die internet af (Van die web). In die venster wat oopmaak, plak die skakel na die ooreenstemmende jaar, gekopieer vanaf die webwerf:
Na kliek op OK 'n voorskouvenster verskyn, waarin jy op die knoppie moet klik Skakel data om (Transformeer data) or Om die data te verander (Wysig data) en ons sal by die Power Query-navraagredigeerdervenster uitkom, waar ons sal voortgaan om met die data te werk:
Onmiddellik kan jy veilig in die regte paneel uitvee Versoek parameters (Navraaginstellings) stap gewysigde tipe (Veranderde tipe) Ons het hom nie nodig nie.
Die tabel in die vakansiekolom bevat kodes en beskrywings van nie-werkdae – jy kan die inhoud daarvan sien deur twee keer daardeur te “val” deur op die groen woord te klik Tabel:
Om terug te gaan, sal jy in die regter paneel al die stappe moet uitvee waarna terug verskyn het Bron (Bron).
Die tweede tabel, wat op soortgelyke wyse verkry kan word, bevat presies wat ons nodig het – die datums van alle nie-werksdae:
Dit bly om hierdie bord te verwerk, naamlik:
1. Filtreer slegs vakansiedatums (dws ene) volgens die tweede kolom Eienskap: t
2. Vee alle kolomme behalwe die eerste uit – deur regs te klik op die opskrif van die eerste kolom en die opdrag te kies Vee ander kolomme uit (Verwyder ander kolomme):
3. Verdeel eerste kolom vir punt afsonderlik vir maand en dag met opdrag Verdeel kolom – volgens skeiding tab Transformasie (Transformeer — Verdeel kolom — Deur skeidingsteken):
4. En uiteindelik skep 'n berekende kolom met normale datums. Om dit te doen, op die blad Voeg 'n kolom by klik op die knoppie Gepasmaakte kolom (Voeg kolom by – pasgemaakte kolom) en voer die volgende formule in in die venster wat verskyn:
=#gedateer(2020, [#»kenmerk:d.1″], [#»kenmerk:d.2″])
Hier het die #datum-operateur drie argumente: jaar, maand en dag, onderskeidelik. Nadat u op OK ons kry die vereiste kolom met normale naweekdatums, en vee die oorblywende kolomme uit soos in stap 2
Stap 2. Verander die versoek in 'n funksie
Ons volgende taak is om die navraag wat vir 2020 geskep is, om te skakel na 'n universele funksie vir enige jaar (die jaarnommer sal sy argument wees). Om dit te doen, doen ons die volgende:
1. Brei die paneel uit (indien nie reeds uitgebrei nie). Navrae (Navrae) aan die linkerkant in die Power Query-venster:
2. Nadat die versoek na 'n funksie omgeskakel is, verdwyn die vermoë om die stappe waaruit die versoek bestaan te sien en dit maklik te redigeer, ongelukkig. Daarom maak dit sin om 'n afskrif van ons versoek te maak en reeds daarmee te baljaar, en die oorspronklike in reserwe te laat. Om dit te doen, regskliek in die linkervenster op ons kalenderversoek en kies die Dupliseer-opdrag.
Deur weer regs te klik op die resulterende kopie van kalender(2), sal die opdrag kies Herbenoem (Hernoem) en voer 'n nuwe naam in – laat dit bv. fxJaar:
3. Ons maak die navraagbronkode oop in die interne Power Query-taal (dit word bondig "M" genoem) deur die opdrag te gebruik Gevorderde redakteur tab Resensie(Bekyk - Gevorderde Redigeerder) en maak klein veranderinge daar om ons versoek in 'n funksie vir enige jaar te verander.
Dit was:
Na:
As jy belangstel in die besonderhede, dan hier:
- (jaar as getal)=> – ons verklaar dat ons funksie een numeriese argument sal hê – 'n veranderlike jaar
- Plak die veranderlike jaar na webskakel in stap Bron. Aangesien Power Query jou nie toelaat om nommers en teks vas te plak nie, skakel ons die jaarnommer om na teks met behulp van die funksie Number.ToText
- Ons vervang die jaarveranderlike vir 2020 in die voorlaaste stap #”Gepasmaakte voorwerp bygevoeg«, waar ons die datum uit die fragmente gevorm het.
Na kliek op Voltooi ons versoek word 'n funksie:
Stap 3. Voer kalenders vir alle jare in
Die laaste ding wat oorbly is om die laaste hoofnavraag te maak, wat data vir alle beskikbare jare sal oplaai en al die ontvangde vakansiedatums in een tabel sal byvoeg. Vir dit:
1. Ons klik in die linkernavraagpaneel in 'n grys leë spasie met die regtermuisknoppie en kies opeenvolgend Nuwe versoek – Ander bronne – Leë versoek (Nuwe navraag - Uit ander bronne - Leë navraag):
2. Ons moet 'n lys genereer van alle jare waarvoor ons kalenders sal aanvra, dws 2013, 2014 … 2020. Om dit te doen, voer die opdrag in die formulebalk van die leë navraag wat verskyn in:
struktuur:
={NumberA..NumberB}
… in Power Query genereer 'n lys heelgetalle van A tot B. Byvoorbeeld, die uitdrukking
={1..5}
… sal 'n lys van 1,2,3,4,5 produseer.
Wel, om nie streng aan 2020 gebind te wees nie, gebruik ons die funksie DateTime.LocalNow() – analoog van die Excel-funksie VANDAG (VANDAG) in Power Query – en onttrek daaruit, op sy beurt, die huidige jaar deur die funksie Datum.Jaar.
3. Die gevolglike stel jare, hoewel dit redelik voldoende lyk, is nie 'n tabel vir Power Query nie, maar 'n spesiale voorwerp - lys (Lys). Maar om dit na 'n tabel om te skakel is nie 'n probleem nie: klik net op die knoppie Na tafel (Tafel toe) in die boonste linkerhoek:
4. Eindstreep! Die toepassing van die funksie wat ons vroeër geskep het fxJaar na die gevolglike lys van jare. Om dit te doen, op die blad Voeg 'n kolom by druk die knoppie Bel persoonlike funksie (Voeg kolom by – Roep pasgemaakte funksie aan) en stel sy enigste argument – die kolom Column1 oor die jare:
Na kliek op OK ons funksie fxJaar die invoer sal om die beurt vir elke jaar werk en ons sal 'n kolom kry waar elke sel 'n tabel sal bevat met die datums van nie-werksdae (die inhoud van die tabel is duidelik sigbaar as jy in die agtergrond van die sel langsaan klik die woord Tabel):
Dit bly om die inhoud van geneste tabelle uit te brei deur op die ikoon met dubbele pyle in die kolomkop te klik Datums (merk Gebruik oorspronklike kolomnaam as voorvoegsel dit kan verwyder word):
… en nadat u daarop geklik het OK ons kry wat ons wou hê – 'n lys van alle vakansiedae vanaf 2013 tot die huidige jaar:
Die eerste, reeds onnodige kolom, kan uitgevee word, en vir die tweede stel die datatipe in datum (Datum) in die aftreklys in die kolomopskrif:
Die navraag self kan hernoem word na iets meer betekenisvol as Versoek 1 en laai dan die resultate op na die blad in die vorm van 'n dinamiese "slim" tabel deur die opdrag te gebruik maak toe en laai af tab What is This (Tuis — Maak toe en laai):
Jy kan die geskepde kalender in die toekoms bywerk deur regs te klik op die tabel of navraag in die regter paneel deur die opdrag Dateer op en stoor. Of gebruik die knoppie Verfris alles tab data (Datum – Herlaai alles) of sleutelbordkortpad Ctrl+alt+F5.
Dis alles.
Nou hoef jy nooit weer tyd en nadenke te mors om die lys van vakansiedae te soek en op te dateer nie – nou het jy 'n "ewige" produksiekalender. In elk geval, solank die skrywers van die webwerf http://xmlcalendar.ru/ hul nageslag ondersteun, wat ek hoop vir 'n baie, baie lang tyd sal wees (weereens te danke aan hulle!).
- Voer bitcoin-koers in om uit te blink vanaf die internet via Power Query
- Vind die volgende werksdag deur die WERKDAG-funksie te gebruik
- Vind die kruising van datumintervalle