PowerPivot naptár-tábla
2018-07-29    Excel 2016
A naptár-tábla szerepe kettős, lehetővé teszi a kimutatásban a dátum adattípusú mezők tételeinek csoportosítását és biztosítja az i­dő­szak-kezelő függvények működéséhez szükséges összefüggő dátum-tartományt. Ezért szükségünk van rá minden olyan adatbázisban, a­mely­nek adatait dátum szerint kívánjuk elemezni. Vagyis majdnem minden adatbázisban.
    A bővítmény 2016-os verziója már rendelkezik beépített naptár-táblával, amelyet tetszőleges adatbázisba beilleszthetünk, sőt igény sze­rint át is alakíthatunk. A módosított táblát, sablonként elmenthetjük. Ezután a PowerPivot már a saját elképzelésünk szerint kialakított nap­tár-táblát fogja az adatbázisokba beszúrni.
    Szerepe szerint, tehát a naptár tábla csak egy segéd-objektum, de a többi táblához hasonlóan, kapcsolatokkal épül be az adatbázis struk­tú­rá­já­ba. Ebben az ismertetőben a bővítmény 2016-os változatának naptár-tábla szolgáltatásait mutatom be.
    Van egy javaslatom! Mondjunk naptár-tábla helyett egyszerűen csak naptárt! Vagy naptárat!? Tehát a naptár első funkciója a dátum a­dat­tí­pu­sú mezők csoportosíthatóságának biztosítása. A dátumok csoportosítása dátumegységenként történhet: évek, félévek, ne­gyed­é­vek, hónapok, hetek, az év napjai, a hónap napjai és a hét napjai. A dátumegység azonosítása történhet sorszám vagy név alapján. Ve­gyük szám­ba a dátumegységek azonosítóit!
PowerPivot naptár-tábla, dátumegységek azonosítói
A naptár tartalmazza majd az aktuális adatbázis legkorábbi évének január elsejével kezdődő és legkésőbbi évének szilveszterével zá­ró­dó időszak összes napjának dátumát és dátumegységeinek azonosítóit. Ez tizenöt (1 + 14 ) mezőt jelent.
    A naptárt a felhasználó hozza létre a bővítmény beépített táblájának átalakításával. A mentett tábla mintaként szolgál majd a Po­wer­Pi­vot számára. A bővítmény a naptárt felhasználói utasításra hozza létre, mindig az aktuális adatbázis dátumainak megfelelően.
    A naptár elkészítését az adatbázis dátum adattípusú mezőinek vizsgálata előzi meg. Ha nincs dátum-mező, akkor nincs naptár sem, a lét­re­ho­zást indító menü-utasítás elérhetetlen. Új táblák létrehozásával vagy a meglévők frissítésével az adatbázis dátum-tartománya mó­do­sul­hat. Ezt a változást a naptár dátum-tartománya nem követi automatikusan. A frissítés a felhasználó feladata.
    Miután tisztáztuk a fogalmakat következzen a gyakorlat! A naptár létrehozásának feltétele legalább egy dátum adattípusú mező. A mun­kák adatbázis-táblázatban kettő is akad: azonosító - a munka sorszáma (szöveg), szerződés - a szerződéskötés napja (dátum), kategória - a munka besorolása (szöveg), munkadíj - a munka elvégzésének ellenértéke (pénznem), teljesítés - az elvégzett munka át­a­dá­sá­nak napja (dátum). Hozzunk létre csatolt táblát a PowerPivot, Táblázatok, Hozzáadás az adatmodellhez utasítással! Adjuk meg a tábla dátum- és a pénznem mezőinek formátumát!
PowerPivot naptár-tábla, megjelenítésének feltételei
A PowerPivot naptár-moduljának parancsait a Tervezés, Naptárak, Dátumtáblázat listában találjuk. Az Új utasítással kérhetjük a naptár lét­re­ho­zá­sát. Az automatikus tábla, teljesen váratlanul, a Naptár nevet kapta. Töröljük a tábla mezőit, egyedül csak a dátumokat tar­tal­ma­zó Da­te mezőt tartsuk meg, majd készítsük el az alábbi táblázat alapján az azonosítókat tartalmazó számított mezőket.
PowerPivot naptár-tábla, számított mezők képletei
Az év napjai index mezőnek a bővítmény dátum adattípust állít be. Először módosítsuk a típust tizedes tört számra a Kezdőlap, For­má­tum, Adattípus listájában, majd a végrehajtás után, ugyan ebben a listában válasszuk az egész számot.
    A képletekben előforduló DAX függvényeket ebben a cikkben nem ismertettem, de a hét index mező képlete magyarázatra szorul. A he­tek számát a WEEKNUM( [Date] ) képlettel számolhatjuk ki, amelyet nevezzünk így <hét száma>. De sajnos a DAX WEEKNUM függ­vé­nye nem az EU-s szabvány szerint működik, mert az év első hetének a január elsejét tartalmazó hetet tekinti és nem az év első csü­tör­tö­két tar­tal­ma­zót. Tehát azokban az években, amelyekben a január elseje péntek, szombat vagy vasárnapra esik, a WEEKNUM függ­vény ered­mé­nyé­ből le kell vonnunk egyet: <hét száma> - 1.
    Először a vizsgált dátum évének első napját kell képeznünk. <újév>: DATE( YEAR( [Date] ) ; 1 ; 1 ). Ezután meg kell állapítanunk, az <új­év> sorszámát a héten, a hétfőt egyesnek tekintve. <újév index>: WEEKDAY( <újév> ; 2 ).
    A következő lépésben logikai művelettel meghatározzuk, hogy az <újév index> a hétfő - csütörtök időszakra esik-e: <újév index> < 5. A mű­ve­let eredménye határozza meg a <működés>-t. Képlete <működés>: IF( <újév index> < 5 ; <hét száma> ; <hét száma> - 1 ). A DAX IF függvénye egy kis eltéréstől eltekintve, azonos a program HA függvényével.
    Ha az év első napja péntek, szombat vagy vasárnapra esik, akkor a <működés> nullát eredményez. Ezt az állapotot egy logikai mű­ve­let­tel ellenőrizhetjük: <működés> = 0. Ha az eredmény IGAZ, akkor az év első napjai az előző év utolsó hetébe esnek, tehát meg kell ha­tá­roz­nunk az előző év utolsó hetének számát. A számítás lépéseit már ismerjük.
    <előző újév>: DATE( YEAR( [Date] ) - 1 ; 1 ; 1 ).
    <előző újév index>: WEEKDAY( <előző újév> ; 2 ).
    <szilveszter>: DATE( YEAR( [Date] ) - 1 ; 12 ; 31 ).
    <szilveszter hét száma>: WEEKNUM( <szilveszter> ; 2 ).
    A végső képlet a fentieknek megfelelően: IF( <működés> = 0 ; IF( <előző újév index> < 5 ; <szilveszter hét száma> ; <szilveszter hét szá­ma> - 1) ; <működés> ).
    Nem valami elegáns megoldás! Mondhatnám mechanikus. De működik. Ha van kedvük próbálják ki! Hozzanak létre csatolt táblát a pró­ba adatbázis-táblázatból, amelyben egy-egy év első napjának dátumai állnak. Legyen a táblában két számított mező: hét napja index és hét index! Képletük egyezzen meg a naptár azonos nevű mezőinek képletével!
    Az elkészült naptárt optimalizáljuk a használatra! Határozzuk meg a név-mezők tételeinek egyedi rendezését a rendezés más mező a­lap­ján művelettel az alábbi táblázat alapján!
PowerPivot naptár-tábla, rendezések
Kattintsunk rá a rendezendő mezőre, majd adjuk ki a Kezdőlap, Rendezés és szűrés, Rendezés más oszlop alapján utasítást! A meg­je­le­nő parancstáblán az E szerint feliratú terület listájából kell kiválasztanunk a rendező mezőt. Ez a beállítás a naptárban semmilyen vál­to­zást nem hoz, csak pivot táblában tapasztalhatjuk a hatását: a bővítmény a rendezendő mező tételeit nem ABC szerint-, hanem a meg­a­dott sor­szá­mok alapján jeleníti meg. Például 1 = január, 2 = február, 3 = március…
    Ha véletlenül mellényúltunk, akkor a rendezendő mezőre kattintva, a Kezdőlap, Rendezés és szűrés, Rendezés más oszlop alapján, Más oszlop szerinti rendezés kikapcsolása utasítással törölhetjük a beállítást.
    A naptárnak van még egy másik szolgáltatása is, amit csak a pivot táblában hasznosíthatunk. Váltsunk át kapcsolatnézetbe a Kez­dő­lap, Nézet, Diagramnézet paranccsal! A naptár mezőlistáján egy hierarchikus mezőcsoport-kezdeményt találunk. Ezt a bővítmény hozta létre az Év, Hónap és Date mezőkkel, de mivel az első kettőt töröltük, ezért már csak a Date árválkodik a DateHierarchy felirat alatt. Bő­vít­het­jük ezt a csoportot is, de újakat is készíthetünk, mert a bővítmény a naptárral együtt, a hierarchikus mezőcsoportokat is elmenti.
hierarchikus mezőcsoport a PowerPivot naptár-táblában
Folytatva a naptár tökéletesítését, rejtsük el azokat a sorszám mezőket, amelyeket valószínűleg nem fogunk a pivot táblában sze­re­pel­tet­ni: félév index, negyedév index, hónap index, hét napjai index. A megjelenítést a mezőnév menüjének Elrejtés az ügyféleszközök elől és a sza­lag Kezdőlap, Nézet, Rejtett elemek megjelenítése utasításával szabályozhatjuk.
    Végre eljött a nagy pillanat: mentsük a naptárt a Tervezés, Naptárak, Dátumtáblázat, Konfiguráció mentése utasítással. Térjünk vissza adat­né­zet­be a Kezdőlap, Nézet, Adatnézet paranccsal és töröljük táblát, majd az Új paranccsal kérjük... Juj de izgulok Gyerekek!
    Minden tökéletes! Kivéve az év napjai index mezőt. A bővítmény visszaállította a dátum adattípust. Próbálkozzunk! Módosítsuk a kép­le­tet, szorozzuk meg eggyel a különbséget: = ( [Date] - DATE( YEAR( [Date] ) - 1 ; 12 ; 31 )) * 1. Mentsük a módosításokat a Kon­fi­gu­rá­ció men­té­se utasítással, majd jöjjön ismét a törlés és a létrehozás. Szuper!
    Természetesen a felhasználó önállóan is létrehozhat naptárt, de akkor minden adatbázisban neki kell gondoskodnia a tábla im­por­tá­lá­sos vagy vágólapos létrehozásáról. A létrehozás után a Naptárak, Megjelölés dátumtáblázatként, Megjelölés dátumtáblázatként pa­ran­csá­val deklarálnia kell a naptár-táblát és a dátumokat tartalmazó mezőjét. Ha a bővítmény naptár-modulját használjuk, akkor a dek­la­rá­ci­ó au­to­ma­ti­kus.
    Elfáradtam Gyerekek! Hamarosan folytatom a naptár tábla kapcsolatai című mesével, amelyben izgalmas kalandokba keveredünk a nap­tár adatbázisba integrálása és frissítése közben. Jó éjszakát Gyerekek, álmodjatok szépeket!
ismertető letöltése pdf-ben munkafüzet letöltése
margitfalvi.arpad@gmail.com