LuckyTemplates Dátumok között Munkaidő Megoldás

A mai blogban bemutatok egy megközelítést az Access Analytic Start Stop Challenge programjához , ahol a LuckyTemplates segítségével kiszámítják a dátumok közötti teljes munkaórát. Az oktatóanyag teljes videóját a blog alján tekintheti meg .

A feladat az, hogy minden hónapban és napon kiszámolja az alkalmazottak teljes óraszámát. Bemutattam a kezdő és befejező dátumokat, amelyek több hónapon keresztül is áthaladhatnak, és figyelembe veszik az állapotot az esetleges elírások kezelésére. 

Tartalomjegyzék

• Dataset For LuckyTemplates Dátumok Munkaidő között
• A sorok szűrése
• Egyéni oszlop hozzáadása
• LuckyTemplates Between-Dates Munkaidő: A változók
• LuckyTemplates Dátumok között Munkaidő: Kivetítés
• LuckyTemplates Dátumok között Munkaidő: Összesített értékek
• Következtetés

Dataset For LuckyTemplates Dátumok Munkaidő között

Menjünk a Power Query szerkesztőjébe. 

Íme az adataink ehhez a kihíváshoz. Van egy táblázatunk a személyzet neve, kezdési dátuma, kezdési időpontja, leállítási dátuma, leállítási ideje és állapota oszlopaival.

A sorok szűrése

Kezdjük azzal, hogy állapotuk alapján válasszuk ki a sorokat, és ebben a logikában a Lekérdezésszerkesztő segíthet. Kattintson az Állapot melletti legördülő nyílra. Válassza kiés válassza a Nem kezdődik. 

A megjelenő Sorok szűrése ablakba írja be az „e” betűt. Ezután kattintson az OK gombra .

Még nem fog történni semmi, és ez így van jól, mert kis „e”-t írunk, és az „E” nagybetűvel kezdődő „Kizárást” az adatainkba.

Ennek javítására a Text.StartsWith opcionális harmadik paraméterét használjuk . A képletsorban lépjen a Text.StartsWith függvény végére . Adjon hozzá egy vesszőt, és írja be a Comparer.OrdinalIgnoreCase-t. Kattintson a pipára, és el kell távolítania a Kizárás sorokat.

Természetesen előfordulhat valaki, aki hibásan beírta a „Kizárás” szót az „E” kezdőbetű nélkül. Ebben az esetben további logikát alkothatunk a helyzethez. 

A funkciónk kiterjesztésével és a Text.Contains használatával tesztelhetjük, hogy a szóban nem szerepel-e „X” . Ehhez adjon hozzá vagy Text.Contains ([Status], "x", Comparer.OrdinalIgnoreCase .

Figyeljük meg, hogy az első tagmondathoz képest az „e”-t „x”-re cseréltük. Továbbra is figyelmen kívül hagyjuk a kis- és nagybetűket, és zárójeleket teszünk mindkét tagmondat köré. Kattintson a pipára, és egy olyan táblázatot kell kapnunk, amely nem tartalmazza a Kizárás vagy Xzárás állapotot tartalmazó sorokat.

Egyéni oszlop hozzáadása

Most térjünk rá ennek a kihívásnak a fő részére. A felhasználói felület segítségével hozzáadunk egy egyéni oszlopot, majd átváltunk a szerkesztőre. 

Kezdje a mini asztal ikonnal a táblázat bal felső sarkában. Kattintson rá, és a legördülő menüben válassza a Hozzáadás lehetőséget . 

Jelenleg csak azon adatok beszerzése érdekel bennünket, amelyekre a későbbiekben szükségünk lesz. És mivel több mezővel fogunk dolgozni, hozzunk létre egy rekordot a rekord inicializálóival, amelyeket szögletes zárójelben jelölünk. 

Hozzon létre változókat, és rendelje hozzá az elérhető oszlopokat. Kezdje az SD beírásával, amelyet a kezdési dátummal fogunk egyenlővé tenni. Ezt úgy tehetjük meg, hogy a jobb oldali oszlopban a Kezdés dátuma gombra kattintunk.

Ugyanezt tesszük az ST, ED és ET változók esetében is, amelyek a kezdési időponthoz, a leállítási dátumhoz és a leállítási időponthoz vannak rendelve . Ezután zárja le a felvételt egy zárójellel. Kattintson az OK gombra.

A rekordokat tartalmazó egyéni oszlop hozzáadódik a táblázathoz. Kattintson a Felvétel melletti fehér területre , és megnyílik egy előnézeti ablak. Ez az összes értéket behozza ebből a sorból.

LuckyTemplates Between-Dates Munkaidő: A változók

Ezután a Speciális szerkesztő ablakon keresztül formázhatjuk a változóinkat . Így kell kinéznie az Advanced Editor ablakának. 

Ezután tegyük a változóinkat külön új sorokba.

Most kibővíthetjük a szükséges logikát. Tehát rekord helyett használhatunk itt egy beágyazott let kifejezést is. Vegye figyelembe, hogy ezek bármelyike ​​működni fog, mert több változónk vagy mezőnevünk is van hozzájuk értékekkel.

A kezdési dátumunk jelenleg szöveges értékként van írva, és ezt át kell alakítanunk megfelelő dátumformátumra. Ehhez tekerje köré  a Date.FromText függvényt.

A szintén karakterláncként formázott kezdési időpontot számmá kell alakítanunk. Ezután oszthatjuk százzal, és kerekíthetjük a tizedesjegyek eltávolításához. 

A Number.From függvény segítségével osszuk el a Kezdő dátum értékét 100- zal . Ezután csomagolja be őket a következő függvénybe, a Number.Round függvénybe , és a végéhez adjon hozzá egy , 0-t a tizedesjegyek nélküli kerekítéshez. 

A Befejezés dátuma és a Befejezés időpontja esetén ugyanazokat a függvényeket másoljuk át, mint a Kezdő dátum és a Kezdési idő , és lecseréljük őket a megfelelő változókra.

Ha a változók megfelelően vannak formázva, hozzon létre egy másik mezőt a rekordunkban további logika érdekében. Hozzon létre egy új sort az Enter megnyomásával . 

Fel kell készítenünk egy listát a dátumokról az első dátumtól egészen a befejezésig. Ezeket a dátumokat LD-nek nevezzük , és a List.Dates függvényt használjuk . 

Ennek a függvénynek az első paramétere a kezdési dátum , amely az SD lesz . Ezután számként vagy a kezdő dátum és a záró dátum közötti különbséget szeretné megadni. Ezt a Number.From függvény segítségével kaphatjuk meg, amelyet az ( ED – SD) + 1 követ . Vegye figyelembe, hogy a „+1” nem jelenik meg a következő képernyőképekben, de ott kell lennie egy +1-nek .

A harmadik paramétert a step as időtartamnak nevezzük , és egy napos növekményt szeretnénk. Ezt a Duration.From (1) használatával kaphatjuk meg . 

Létrehoztunk egy listát, amely tartalmazza a dátumokat az első kezdéstől a befejezés dátumáig. A következő lépésben szeretnénk létrehozni egy listát azokról az alkalmakról, amelyek ezzel együtt járnak. 

Nevezzük ezt LT-nek a listás időkért. Az LT egy napos időszaka egy napból állhat, ami kissé eltérő logikát igényel, mint ha több napon át ível. Tehát ennek a feltételét kell megteremtenünk. 

Azt akarjuk, hogy a feltétel a következő legyen: „Ha a kezdési dátumunk megegyezik a befejezési dátumunkkal, akkor a befejezési időpont mínusz a kezdési idő.” Az eredményeknek listás formátumban kell lenniük, így a lista inicializálóját használjuk, amelyet { } képvisel . 

Most, ha van egy dátumtartományunk, amely több napot ölel fel, akkor az első dátum 24 mínusz a kezdési idő lesz. Ezt is hozzáadjuk a feltételünkhöz, mint az else állítást. Ismételten formázza ezt listaként a göndör zárójelek használatával.

Jelenleg még nem számoltuk el a többi teljes napot, ezért az és jelet (&) fogjuk használni több lista hozzáfűzéséhez. Minden teljes naphoz hozzon létre egy listát a 24 értékkel, amely az egy nap 24 óráját jelenti. Ehhez  a List.Repeat parancsot fogjuk használni .

A List.Repeat funkcióval hozzon létre egy listát, amely 24-et tartalmaz, és ismételje meg ezt többször úgy, hogy megszámolja a napok számát az LD -ben . Ennek eléréséhez használja a List.Count (LD) parancsot   , majd vonja ki a 2-t, mert külön listánk van a kezdési dátumunkhoz, és egy másik listát készítünk a befejezési időponthoz. 

Lényegében ez egy olyan listát hoz létre, amely minden napra csak 24 órát tartalmaz. 

A befejezés idejére ismét hozzáfűzhetjük listaként és jellel, majd a lista inicializálóival hívjuk meg az ET-t. 

Eddig két nagy listánk van – a dátumok listája és az időpontok listája –, amelyek hossza megegyezik egymással. Ebből a két listából egyetlen táblázatot készíthetünk. 

Egy új sorban hozzon létre egy másik változót a táblához, amelyet t- nek hívunk, és használja a Table.FromColumns függvényt . Ehhez a funkcióhoz listákra van szükség listákként , és mi az LD-t használjuk . 

LD -nk egyedi dátumokat tartalmaz a kezdő dátumtól a befejezés dátumáig. Ezt a dátumlistát a hónap végi dátum értékévé alakíthatjuk az LD átadásával és a Date.EndOfMonth függvény meghívásával. Aztán a következő sorban vegyük a mi LT-nket is. 

Nyomja meg az Enter billentyűt egy másik sor létrehozásához, és állítsa be táblázatunkat típustáblázatként . A táblázatunkban két oszlop lesz, az első oszlop egy dátum oszlop. Tehát hozzon létre egy Dátum oszlopot, és hívja meg a Date.Type függvényt.

A második oszlop az Óra oszlopunk lesz , és egész szám lesz. Így létrehozunk egy Hrs oszlopot, és meghívjuk az Int8.Type-ot.

Van egy záró zárójel a rekordunk bezárásához, egy záró zárójel pedig a Table.AddColumn függvény bezárásához. Kattintson a Kész gombra , és az Egyéni oszlop alatt megjelenik a rekordok listája. 

Ismét kattintson az egyes rekordok melletti fehér mezőre , és a táblázatnak meg kell nyílnia a képernyő alsó részén. 

Figyeljük meg, hogy a t táblázatunk az utolsó sorban van . Próbáljunk belefúrni az egyik rekordba. Kattintson a jobb gombbal az első eredményre, és kattintson a Hozzáadás új lekérdezésként gombra . 

Ugyanazt az összegzést kapjuk, mint korábban, és ha a táblázatra kattintunk , megkapjuk a Dátum és az óra értékeit.

De minket elsősorban a végső kimeneti táblázat érdekel. Ahhoz, hogy erre összpontosítson, távolítsuk el a súgólekérdezést. Ehhez kattintson a jobb gombbal az Egyéni lehetőségre , és válassza a Törlés lehetőséget . Erősítse meg az előugró ablakban a Törlés gombra kattintva .

A törlés után visszatérhetünk az eredeti lekérdezésünkhöz, és ismét kattinthatunk a Rögzítés melletti szóközre. Ugyanazokat az eredményeket fogja mutatni, mint korábban. De ezúttal bontsuk ki a képletsort, és hívjuk a [t]-t a záró zárójelek mellé.

Az Egyéni oszlopban láthatjuk, hogy minden rekord táblázatra módosult . 

LuckyTemplates Dátumok között Munkaidő: Kivetítés

Az általunk vizsgált táblázatban csak két érdekes oszlop van: a személyzet neve és az egyéni oszlopunk. Használhatjuk a vetítést, hogy megtartsuk ezt a két oszlopot egyedül ebből a táblázatból. 

Lépjen a képletsorba, és a záró zárójel mellett szögletes zárójelbe helyezve jelölje ki azokat a mezőket, amelyeket meg akarunk tartani. Ezután nyomja meg a pipát.

Ezt az eredményt kellene elérnünk.

Az Egyéni  melletti nyíl ikonokra kattintva bővíthetjük ki . Törölje az Eredeti oszlopnév használata előtagként jelölőnégyzetet , majd kattintson az OK gombra.

A táblázatunknak így kell kinéznie.

LuckyTemplates Dátumok között Munkaidő: Összesített értékek

Már csak az értékek összesítése van hátra. Ehhez válassza ki a Személyzet neve oszlopot, és lépjen az Átalakításra. 

A felugró ablakban az Értékek oszlopban válassza az Óra lehetőséget , mert ezt szeretnénk összesíteni. Ezután válassza az Összeg lehetőséget a alatt . Ezután kattintson az OK gombra.

A végső kimenetünk így fog kinézni. 

Következtetés

Ebben a blogban az Access Analytic Start Stop Challenge egyik megközelítését láthatta. Megtanulta, hogyan számíthatja ki az egyes alkalmazottak által egy hónapban ledolgozott órák számát, és hogyan kezelheti az adatok esetleges elírásait. 

Ezzel a technikával és a LuckyTemplates segítségével a dátumok közötti munkaidő könnyen kiszámítható saját szervezetében, vagy gyakorlatként a LuckyTemplates ismeretei és készségei elmélyítésére.