Gyári naptár Excelben

Gyártási naptár, azaz dátumlista, ahol minden hivatalos munkanap és ünnepnap ennek megfelelően meg van jelölve – ez minden Microsoft Excel felhasználó számára feltétlenül szükséges. A gyakorlatban nem nélkülözheti:

• számviteli számításoknál (bér, szolgálati idő, szabadság stb.)
• a logisztikában – a szállítási idők pontos meghatározásához, a hétvégék és ünnepnapok figyelembevételével (emlékezzünk a klasszikus „gyere az ünnepek után?”)
  
• projektmenedzsmentben – a pontos határidőbecsléshez, ismételten a munkaszüneti napok figyelembevételével
• olyan funkciók használata, mint pl MUNKANAP (MUNKANAP) or TISZTA MUNKÁSOK (NETWORKDAYS), mert érvként megkövetelik az ünnepnapok listáját
• időintelligencia funkciók (például TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR stb.) használatakor a Power Pivotban és a Power BI-ban
  
• … stb. stb. – rengeteg példa.
  

Könnyebb a dolguk azoknak, akik olyan vállalati ERP rendszerekben dolgoznak, mint az 1C vagy az SAP, mivel ezekbe be van építve a termelési naptár. De mi a helyzet az Excel felhasználókkal?

Természetesen egy ilyen naptárt manuálisan is vezethet. De akkor évente legalább egyszer frissítenie kell (vagy még gyakrabban, mint a „jolly” 2020-ban), gondosan megadva a kormányunk által kitalált hétvégéket, átutalásokat és munkaszüneti napokat. Ezután ismételje meg ezt az eljárást minden következő évben. Unalom.

Mit szólna egy kicsit megőrülni és elkészíteni egy „örök” gyári naptárat Excelben? Olyan, amely frissíti magát, adatokat vesz az internetről, és mindig naprakész listát készít a munkaszüneti napokról, hogy később felhasználhassa bármilyen számításban? Csábító?

Ennek megvalósítása valójában egyáltalán nem nehéz.

Adatforrás

A fő kérdés az, hogy honnan szerezzük be az adatokat? A megfelelő forrás keresése során több lehetőséget is végignéztem:

• Az eredeti rendeletek PDF formátumban megjelennek a kormány honlapján (itt például az egyik), és azonnal eltűnnek – hasznos információkat nem lehet kihúzni belőlük.
• Első pillantásra csábító lehetőségnek tűnt a „Föderáció Nyílt Adatportálja”, ahol van egy megfelelő adatsor, de közelebbről megvizsgálva minden szomorúnak bizonyult. Borzasztóan kényelmetlen az oldal az Excelbe importáláshoz, a technikai támogatás nem válaszol (önelszigetelődik?), és ott maguk az adatok is régóta elavultak – a 2020-as gyártási naptár utoljára 2019 novemberében frissült (szégyen!) ill. , természetesen nem tartalmazza például a „koronavírusunkat” és a 2020-as „szavazási” hétvégét.

Kiábrándulva a hivatalos forrásokból, elkezdtem ásni a nem hivatalos forrásokat. Sok van belőlük az interneten, de a legtöbbjük ismét teljesen alkalmatlan az Excelbe importálásra, és gyönyörű képek formájában gyártási naptárat adnak ki. De nem nekünk kell a falra akasztani, ugye?

És a keresés során véletlenül egy csodálatos dolgot fedeztek fel - a http://xmlcalendar.ru/ webhelyet

Fölösleges „fricskák” nélkül, egy egyszerű, könnyű és gyors oldal, egyetlen feladatra kihegyezve – hogy mindenki megkapja a kívánt évre szóló gyártási naptárat XML formátumban. Kiváló!

Ha hirtelen nem vagy tisztában vele, akkor az XML egy olyan szövegformátum, amelynek tartalma speciális jelöléssel van ellátva . Könnyű, kényelmes és olvasható a legtöbb modern programmal, beleértve az Excelt is.

Minden esetre felvettem a kapcsolatot az oldal készítőivel, és megerősítették, hogy az oldal 7 éve létezik, folyamatosan frissülnek az adatok rajta (még a githubon is van fiókjuk erre), és nem fogják bezárni. És egyáltalán nem bánom, hogy te és én töltünk be róla adatokat bármelyik projektünkhöz és számításunkhoz Excelben. Ingyenes. Jó tudni, hogy vannak még ilyen emberek! Tisztelet!

Ezeket az adatokat a Power Query bővítmény segítségével kell betölteni az Excelbe (az Excel 2010-2013 verzióihoz ingyenesen letölthető a Microsoft webhelyéről, az Excel 2016 és újabb verzióiban pedig már alapértelmezés szerint be van építve ).

A műveletek logikája a következő lesz:

1. Egy évre vonatkozóan kérünk adatokat letölteni az oldalról
2. Kérésünket funkcióvá alakítva
3. Ezt a funkciót az összes elérhető év listájára alkalmazzuk, 2013-tól az aktuális évig – és kapunk egy „örök” gyártási naptárt automatikus frissítéssel. Voálá!
   

1. lépés: Importáljon egy naptárt egy évre

Először töltse be a termelési naptárt bármelyik évre, például 2020-ra. Ehhez lépjen az Excel lapjára. dátum (Vagy Teljesítmény lekérdezésha külön kiegészítőként telepítette), és válassza ki Az internetről (Webről). A megnyíló ablakban illessze be a megfelelő évre mutató hivatkozást, amelyet a webhelyről másoltak:

Miután rákattintott OK megjelenik egy előnézeti ablak, amelyben a gombra kell kattintania Adatok konvertálása (Adatok átalakítása) or Az adatok megváltoztatásához (Adatok szerkesztése) és eljutunk a Power Query lekérdezésszerkesztő ablakához, ahol folytatjuk a munkát az adatokkal:

Azonnal biztonságosan törölheti a jobb oldali panelen Paraméterek kérése (Lekérdezési beállítások) lépés módosított típus (Módosított típus) Nincs szükségünk rá.

Az ünnepek oszlopban található táblázat a munkaszüneti napok kódjait és leírásait tartalmazza – a zöld szóra kattintva kétszer „átesve” láthatja a tartalmát. Táblázat:

A visszalépéshez törölnie kell a jobb oldali panelen az összes olyan lépést, amelyhez visszatért forrás (Forrás).

A második, hasonló módon elérhető táblázat pontosan azt tartalmazza, amire szükségünk van – minden munkaszüneti nap dátumát:

A lemez feldolgozása hátra van, nevezetesen:

1. Csak az ünnepnapok dátumait (azaz a dátumokat) szűrje a második oszlop alapján Tulajdonság:t

2. Törölje az összes oszlopot az első kivételével – kattintson jobb gombbal az első oszlop fejlécére, és válassza ki a parancsot Törölje a többi oszlopot (További oszlopok eltávolítása):

3. Az első oszlop felosztása pontonként külön-külön hónapra és napra a paranccsal Oszlop felosztása – Határolóval lap Átalakítás (Átalakítás – Oszlop felosztása – Határolóval):

4. És végül hozzon létre egy számított oszlopot normál dátumokkal. Ehhez a lapon Oszlop hozzáadása kattintson a gombra Egyéni oszlop (Oszlop hozzáadása – Egyéni oszlop) és a megjelenő ablakba írja be a következő képletet:

=#keltezett(2020, [#»Attribútum:d.1″], [#»Attribútum:d.2″])

Itt a #date operátornak három argumentuma van: év, hónap és nap. Kattintás után OK megkapjuk a szükséges oszlopot normál hétvégi dátumokkal, a többi oszlopot pedig a 2. lépésben leírtak szerint töröljük

2. lépés: A kérés funkcióvá alakítása

Következő feladatunk, hogy a 2020-ra készített lekérdezést bármely évre univerzális függvénnyel alakítsuk át (a argumentuma az évszám lesz). Ehhez a következőket tesszük:

1. A panel kibontása (ha még nincs kibontva). Érdeklődés (Lekérdezések) a Power Query ablak bal oldalán:

2. A kérés függvényvé alakítása után sajnos megszűnik a kérést alkotó lépések megtekintésének és egyszerű szerkesztésének lehetősége. Ezért célszerű másolatot készíteni a kérésünkről, és már vele tréfálkozni, az eredetit pedig tartalékban hagyni. Ehhez kattintson a jobb gombbal a bal oldali ablaktáblában a naptárkérelmünkre, és válassza a Duplicate parancsot.

Ha ismét jobb gombbal kattint a naptár(2) eredményül kapott példányára, akkor a parancs kiválasztásra kerül átnevezése (Átnevezés) és írjon be egy új nevet – legyen ez például fxYear:

3. Megnyitjuk a lekérdezés forráskódját a belső Power Query nyelven (ezt röviden „M”-nek hívják) a paranccsal Speciális szerkesztő lap Felülvizsgálat(Nézet – Speciális szerkesztő) és végezzen apró változtatásokat ott, hogy kérésünk bármelyik évre funkcióvá váljon.

Ez volt:

Után:

Ha érdekelnek a részletek, akkor itt:

• (év számként)=>  – deklaráljuk, hogy a függvényünknek egy numerikus argumentuma lesz – egy változó év
• A változó beillesztése év lépésben a webes linkre forrás. Mivel a Power Query nem teszi lehetővé számok és szövegek ragasztását, az évszámot menet közben szöveggé alakítjuk a függvény segítségével. Number.ToText
• Az utolsó előtti lépésben a 2020-as évváltozót helyettesítjük #”Egyéni objektum hozzáadva«, ahol a töredékekből alakítottuk ki a dátumot.
  

Miután rákattintott befejez kérésünk funkcióvá válik:

3. lépés: Naptárak importálása minden évre

Utolsó hátra van az utolsó fő lekérdezés elkészítése, amely az összes rendelkezésre álló év adatait feltölti, és az összes beérkezett ünnepi dátumot egy táblázatba adja. Ezért:

1. Jobb egérgombbal kattintunk a bal oldali lekérdező panelen egy szürke üres helyre, és sorban kiválasztjuk Új kérelem – Egyéb források – Üres kérelem (Új lekérdezés – Más forrásból – Üres lekérdezés):

2. Létre kell hoznunk egy listát az összes évről, amelyekre naptárakat kérünk, azaz 2013, 2014 … 2020. Ehhez a megjelenő üres lekérdezés képletsorába írja be a következő parancsot:

Szerkezet:

={SzámA..B szám}

… a Power Query egész számok listáját generálja A-tól B-ig. Például a kifejezés

={1..5}

… egy 1,2,3,4,5-ös listát készítene.

Nos, hogy ne kötődjünk mereven 2020-hoz, használjuk a funkciót DateTime.LocalNow() – az Excel függvény analógja MA (MA) a Power Queryben – és kivonja belőle a függvény által az aktuális évet Dátum.Év.

3. Az így kapott évkészlet, bár elég megfelelőnek tűnik, nem a Power Query táblázata, hanem egy speciális objektum – lista (Lista). De táblázattá alakítani nem probléma: csak kattintson a gombra Az asztalhoz (A táblázathoz) a bal felső sarokban:

4. Célvonal! A korábban létrehozott függvény alkalmazása fxYear az eredményül kapott évek listájához. Ehhez a lapon Oszlop hozzáadása nyomja meg a gombot Egyéni funkció hívása (Oszlop hozzáadása – Egyéni függvény meghívása) és állítsa be egyetlen argumentumát – az oszlopot Column1 az évek alatt:

Miután rákattintott OK funkciónk fxYear az importálás minden évben felváltva fog működni és kapunk egy oszlopot, ahol minden cellában lesz egy táblázat a munkaszüneti napok dátumaival (a táblázat tartalma jól látható, ha a cella hátterébe kattint a a szó Táblázat):

Marad a beágyazott táblázatok tartalmának bővítése az oszlopfejlécben található dupla nyíl ikonra kattintva Időpontok (ketyegés Használja az eredeti oszlop nevét előtagként eltávolítható):

… és rákattintás után OK azt kapjuk, amit akartunk – az összes ünnep listája 2013-tól az aktuális évig:

Az első, amúgy is felesleges oszlop törölhető, a másodiknál ​​pedig beállíthatjuk az adattípust adat (Dátum) az oszlopfejléc legördülő listában:

Maga a lekérdezés átnevezhető valami értelmesebbre, mint Kérelem 1 majd a paranccsal töltse fel az eredményeket a lapra dinamikus „okos” táblázat formájában zárja be és töltse le lap Kezdőlap (Főoldal – Bezárás és betöltés):

A létrehozott naptárt a jövőben frissítheti, ha a jobb oldali ablaktáblában jobb gombbal kattint a táblára vagy a lekérdezésre a parancs segítségével Frissítés és mentés. Vagy használja a gombot Az összes frissítése lap dátum (Dátum – Összes frissítése) vagy billentyűparancsot Ctrl+más+F5.

Ez minden.

Most már soha többé nem kell időt és gondolkodást vesztegetnie az ünnepek listájának keresésére és frissítésére – most van egy „örök” gyártási naptár. Mindenesetre mindaddig, amíg a http://xmlcalendar.ru/ oldal szerzői támogatják utódaikat, ami, remélem, nagyon-nagyon sokáig az lesz (ezért is köszönjük nekik!).

• Importáljon bitcoin árfolyamot az excelhez az internetről a Power Query segítségével
• A következő munkanap megkeresése a WORKDAY funkció segítségével
  
• Dátumintervallumok metszéspontjának megkeresése