Inhalter
Produktiounskalenner, also eng Lëscht vun den Datumen, wou all offiziell Aarbechtsdeeg a Feierdeeg deementspriechend markéiert sinn - eng absolut néideg Saach fir all Benotzer vu Microsoft Excel. An der Praxis kënnt Dir net ouni et maachen:
- a Comptabilitéitsberechnungen (Pai, Dauer vum Service, Vakanzen ...)
- an der Logistik - fir d'korrekt Bestëmmung vun de Liwwerzäiten, berücksichtegt de Weekend a Feierdeeg (erënnert un de Klassiker "komm no der Vakanz?")
- am Projet Gestioun - fir déi richteg Estimatioun vun Begrëffer, Rechnung huelen, erëm, schaffen-Net-Aarbechtsdeeg
- all Notzung vu Funktiounen wéi SCHAFFTSDAG (ARBEITSDAG) or PURE Aarbechter (NETWORKDAYS), well se als Argument eng Lëscht vu Feierdeeg brauchen
- wann Dir Time Intelligence Funktiounen benotzt (wéi TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) am Power Pivot a Power BI
- … etc. etc. – vill Beispiller.
Et ass méi einfach fir déi, déi a Firmen-ERP Systemer wéi 1C oder SAP schaffen, well de Produktiounskalenner an hinnen agebaut ass. Awer wat iwwer Excel Benotzer?
Dir kënnt natierlech esou e Kalenner manuell halen. Awer dann musst Dir et op d'mannst eemol am Joer aktualiséieren (oder souguer méi dacks, wéi am "jolly" 2020), virsiichteg all Weekender, Transferen an Net-Aarbechtsdeeg erfonnt vun eiser Regierung aginn. An dann widderhuelen dës Prozedur all nächst Joer. Langweil.
Wéi wier et mat e bësse verréckt ze maachen an en "éiwege" Fabréckskalenner an Excel ze maachen? Een deen sech selwer aktualiséieren, Daten vum Internet hëlt an ëmmer eng aktuell Lëscht vun Net-Aarbechtsdeeg generéiert fir duerno an all Berechnungen ze benotzen? Verlockend?
Fir dëst ze maachen, ass guer net schwéier.
Datenquell
D'Haaptfro ass wou d'Donnéeën ze kréien? Op der Sich no enger gëeegenter Quell hunn ech e puer Optiounen duerchgaang:
- Déi ursprénglech Dekreter ginn op der Websäit vun der Regierung am PDF-Format publizéiert (hei, zum Beispill ee vun hinnen) a verschwannen direkt - nëtzlech Informatioune kënnen net erausgezunn ginn.
- Eng verlockend Optioun, op den éischte Bléck, schéngt den "Open Data Portal vun der Federatioun" ze sinn, wou et e entspriechend Datesaz ass, awer bei méi no unzefroen ass alles traureg. De Site ass schrecklech onbequem fir an Excel z'importéieren, technesch Ënnerstëtzung reagéiert net (selwer isoléiert?), an d'Donnéeën selwer sinn do laang Zäit verouderd - de Produktiounskalenner fir 2020 gouf fir d'lescht am November 2019 aktualiséiert (Schued!) an , enthält natierlech net eisen "Coronavirus" an de "Wahlweekend" vun 2020, zum Beispill.
Enttäuscht mat offiziellen Quellen, hunn ech ugefaang inoffiziell ze graven. Et gi vill vun hinnen um Internet, awer déi meescht vun hinnen, erëm, si komplett net gëeegent fir an Excel z'importéieren a ginn e Produktiounskalenner a Form vu schéine Biller eraus. Awer et ass net fir eis et un der Mauer ze hänken, oder?
An am Prozess vun der Sich gouf eng wonnerbar Saach zoufälleg entdeckt - de Site http://xmlcalendar.ru/
Ouni onnéideg "Frills", eng einfach, liicht a séier Site, geschäerft fir eng Aufgab - fir jiddereen e Produktiounskalenner fir dat gewënschte Joer am XML-Format ze ginn. Exzellent!
Wann Dir op eemol net am Wëssen sidd, dann ass XML en Textformat mat Inhalt markéiert mat speziellen
Just am Fall hunn ech d'Auteure vum Site kontaktéiert a si hunn bestätegt datt de Site fir 7 Joer existéiert, d'Donnéeën doriwwer ginn dauernd aktualiséiert (si hunn souguer eng Filial op Github fir dëst) a si wäerten et net zoumaachen. An et ass mir guer näischt drun, datt Dir an ech Daten dovunner lueden fir ee vun eise Projeten a Berechnungen an Excel. Ass gratis. Et ass schéin ze wëssen, datt et nach esou Leit gëtt! Respekt!
Et bleift fir dës Donnéeën an Excel mat der Power Query Add-in ze lueden (fir Versioune vun Excel 2010-2013 kann se gratis vun der Microsoft Websäit erofgeluede ginn, an a Versioune vun Excel 2016 a méi nei ass et scho Standard agebaut. ).
D'Logik vun den Aktiounen wäert wéi follegt sinn:
- Mir maachen eng Demande fir Daten vum Site fir all Joer erofzelueden
- Verwandelt eis Ufro an eng Funktioun
- Mir applizéieren dës Funktioun op d'Lëscht vun all verfügbare Joeren, ab 2013 a bis zum aktuelle Joer - a mir kréien en "éiwege" Produktiounskalenner mat automateschen Aktualiséierung. Voila!
Schrëtt 1. Import engem Kalenner fir ee Joer
Als éischt lued de Produktiounskalenner fir all Joer, zum Beispill fir 2020. Fir dëst ze maachen, gitt an Excel op den Tab Daten (oder Muecht Ufrowann Dir et als separat Add-on installéiert hutt) a wielt Vum Internet (vum Web). Paste an der Fënster déi opmaacht de Link op dat entspriechend Joer, kopéiert vum Site:
Nodeems Dir op OK eng Virschaufenster erschéngt, an där Dir op de Knäppchen klickt Konvertéieren Daten (Daten transforméieren) or Fir d'Donnéeën z'änneren (Daten änneren) a mir kommen an d'Power Query Query Editor Fënster, wou mir weider mat den Donnéeën schaffen:
Direkt kënnt Dir sécher am richtege Panel läschen Ufro Parameteren (Query Astellunge) Schrëtt geännert Typ (Ännert Typ) Mir brauchen hien net.
D'Tabell an der Vakanzkolonne enthält Coden a Beschreiwunge vun net-Aarbechtsdeeg - Dir kënnt hiren Inhalt gesinn andeems Dir zweemol "fälscht" andeems Dir op dat gréngt Wuert klickt Dësch:
Fir zréck ze goen, musst Dir an der rietser Panel all d'Schrëtt läschen, déi zréck op erschéngt sinn Source (Source).
Déi zweet Tabell, déi op eng ähnlech Manéier zougänglech ass, enthält genau dat wat mir brauchen - d'Datume vun all Net-Aarbechtsdeeg:
Et bleift fir dës Plack ze veraarbecht, nämlech:
1. Filter nëmmen Feierdeegsdatum (dh déi) no der zweeter Kolonn Attributioun: t
2. Läschen all Kolonnen ausser déi éischt - andeems Dir op d'Iwwerschrëft vun der éischter Kolonn klickt an de Kommando auswielen Aner Kolonnen läschen (Aner Kolonnen ewechhuelen):
3. Split éischt Kolonn duerch Punkt separat fir Mount an Dag mat Kommando Split Kolonn - Duerch Delimiter Reiter Transformatioun (Transforméieren - Spalt Kolonn - Duerch Ofgrenzer):
4. A schliisslech eng berechent Kolonn mat normalen Datumen erstellen. Fir dëst ze maachen, op der Tab Dobäizemaachen eng Kolonn klickt op de Knäppchen Benotzerdefinéiert Kolonn (Kolonn derbäisetzen - Benotzerdefinéiert Kolonn) a gitt déi folgend Formel an der Fënster déi erschéngt:
=#datéiert(2020, [#»Attributer:d.1″], [#»Attributer:d.2″])
Hei huet den #date Bedreiwer dräi Argumenter: Joer, Mount an Dag, respektiv. Nodeems Dir op klickt OK mir kréien déi néideg Kolonn mat normale Weekend Datumen, a läschen déi verbleiwen Kolonnen wéi am Schrëtt 2
Schrëtt 2. D'Ufro an eng Funktioun ëmsetzen
Eis nächst Aufgab ass d'Ufro erstallt fir 2020 an eng universell Funktioun fir all Joer ze konvertéieren (d'Joernummer wäert säin Argument sinn). Fir dëst ze maachen, maache mir déi folgend:
1. Erweidert (wann net scho erweidert) de Panel Inquiries (Ufroen) op der lénkser Säit an der Power Query Fënster:
2. Nodeems Dir d'Ufro an eng Funktioun ëmgewandelt hutt, verschwënnt d'Fäegkeet d'Schrëtt ze gesinn, déi d'Ufro ausmaachen an se einfach z'änneren, leider. Dofir ass et Sënn fir eng Kopie vun eiser Ufro ze maachen a scho mat ze froen, an d'Original an der Reserve loossen. Fir dëst ze maachen, klickt riets an der lénkser Fënster op eiser Kalenner Ufro a wielt de Kommando Duplizéiert.
Riets-klickt nach eng Kéier op déi resultéierend Kopie vum Kalenner (2) wielt de Kommando réckelt (Ëmbenennung) a gitt en neien Numm - loosst et sinn, zum Beispill, fxJoer:
3. Mir öffnen den Ufroquellcode an der interner Power Query Sprooch (et ass kuerz "M" genannt) mam Kommando Fortgeschratt Redakter Reiter iwwerpréiwen(View - Advanced Editor) a kleng Ännerungen do maachen fir eis Ufro zu enger Funktioun fir all Joer ze maachen.
Et wor:
Nodeem:
Wann Dir un den Detailer interesséiert sidd, dann hei:
- (Joer als Zuel) => - mir erklären datt eis Funktioun een numerescht Argument wäert hunn - eng Variabel Joer
- Paste der Variabel Joer op de Weblink a Schrëtt Source. Zënter Power Query erlaabt Iech net Zuelen an Text ze pechen, konvertéiere mir d'Joernummer an Text op der Flucht mat der Funktioun Number.ToText
- Mir ersetzen d'Joervariabel fir 2020 am virläitste Schrëtt #" Benotzerdefinéiert Objet dobäigesat«, wou mir den Datum aus de Fragmenter geformt hunn.
Nodeems Dir op Freck eis Ufro gëtt eng Funktioun:
Schrëtt 3. Import Kalenner fir all Joer
Déi lescht Saach lénks ass déi lescht Haaptufro ze maachen, déi Daten fir all verfügbare Joer eropluede an all déi kritt Vakanzendatum an eng Tabell bäidréit. Dofir:
1. Mir klickt op der lénkser Ufro Panel an engem groe eidele Raum mat der rietser Maus Knäppchen a wielt sequentiell Nei Ufro - Aner Quellen - Eidel Ufro (Nei Ufro - Vun anere Quellen - Blank Ufro):
2. Mir mussen eng Lëscht vun all Joer generéieren fir déi mir Kalenneren ufroen, dh 2013, 2014 ... 2020. Fir dëst ze maachen, gitt an der Formelbar vun der eidel Ufro déi erschéngt de Kommando:
Struktur:
={NummerA..NummerB}
... a Power Query generéiert eng Lëscht vun ganz Zuelen vun A bis B. Zum Beispill den Ausdrock
={1..5}
... géif eng Lëscht vun 1,2,3,4,5 produzéieren.
Gutt, fir net steif un 2020 gebonnen ze sinn, benotze mir d'Funktioun DateTime.LocalNow() - Analog vun der Excel Funktioun haut (HAUT) an Power Query - an Extrait aus et, am Tour, dat aktuellt Joer vun der Funktioun Datum.Joer.
3. Déi resultéierend Set vu Joeren, obwuel et zimlech adäquat ausgesäit, ass keen Dësch fir Power Query, awer e speziellen Objet - Lëscht (Lëscht). Awer et an en Dësch ëmsetzen ass kee Problem: klickt einfach op de Knäppchen Ze Dësch (An den Dësch) an der ieweschter lénkser Ecke:
4. Finish Linn! Uwendung vun der Funktioun déi mir virdru erstallt hunn fxJoer op déi resultéierend Lëscht vu Joeren. Fir dëst ze maachen, op der Tab Dobäizemaachen eng Kolonn dréckt op de Knäppchen Call Benotzerdefinéiert Funktioun (Kolonn derbäisetzen - Benotzerdefinéiert Funktioun opruffen) a setzt säin eenzegt Argument - d'Kolonn Column1 iwwert d'Joren:
Nodeems Dir op OK eis Funktioun fxJoer den Import fonctionnéiert fir all Joer ofwiesselnd a mir kréien eng Kolonn wou all Zell eng Tabell enthält mat den Datume vun net-Aarbechtsdeeg (den Inhalt vun der Tabell ass kloer ze gesinn wann Dir am Hannergrond vun der Zell nieft klickt d'Wuert Dësch):
Et bleift fir den Inhalt vun nestet Dëscher auszebauen andeems Dir op d'Ikon mat duebele Pfeile an der Kolonn Header klickt Datumer (tick Benotzt den ursprénglechen Kolonnnumm als Präfix et kann ewechgeholl ginn):
... an nodeems Dir op klickt OK mir kréien wat mir wollten - eng Lëscht vun alle Feierdeeg vun 2013 bis zum aktuelle Joer:
Déi éischt, scho onnéideg Kolonn, kann geläscht ginn, a fir déi zweet, setzen d'Datentyp Datum (Datum) an der Dropdown-Lëscht an der Kolonntitel:
D'Ufro selwer kann eppes méi sënnvoll ëmbenannt ginn wéi Ufro 1 an dann lued d'Resultater op d'Blat a Form vun enger dynamescher "Smart" Tabelle mam Kommando erop zoumaachen an erofzelueden Reiter Home (Home - Zoumaachen & Lueden):
Dir kënnt de erstallte Kalenner an Zukunft aktualiséieren andeems Dir op den Dësch klickt oder an der rietser Fënster duerch de Kommando ufroen Update & späicheren. Oder benotzt de Knäppchen Alles erfrëschen Reiter Daten (Datum - Alles erfrëschen) oder Tastatur Ofkiirzung Ctrl+Alt+F5.
Dat ass alles.
Elo musst Dir ni méi Zäit a Gedanken-Brennstoff verschwenden fir d'Lëscht vun de Vakanzen ze sichen an ze aktualiséieren - elo hutt Dir en "éiwege" Produktiounskalenner. Op alle Fall, soulaang d'Auteuren vum Site http://xmlcalendar.ru/ hir Nofolger ënnerstëtzen, déi, ech hoffen, fir eng ganz, ganz laang Zäit wäert sinn (Merci nach eng Kéier!).
- Import Bitcoin Taux fir aus dem Internet iwwer Power Query ze exceléieren
- Den nächsten Aarbechtsdag fannen mat der WORKDAY Funktioun
- Fannen d'Kräizung vun Datum Intervalle