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 idő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, amelynek 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 szerint á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 naptá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 struktú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 adattípusú 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, negyedé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. Vegyük számba a dátumegységek azonosítóit!
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 PowerPivot 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étrehozá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ódosulhat. 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 munká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 átadá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!
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étrehozá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 tartalmazó Date 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.
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, Formá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 hetek 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üggvé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örtökét tartalmazót. Tehát azokban az években, amelyekben a január elseje péntek, szombat vagy vasárnapra esik, a WEEKNUM függvény eredmé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űvelet 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űvelettel 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 határoznunk 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ő alapján művelettel az alábbi táblázat alapján!
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 megjelenő 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áltozá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 megadott sorszá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 Kezdő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íthetjü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.
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 szerepeltetni: 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 szalag 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 adatnézetbe 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épletet, szorozzuk meg eggyel a különbséget: = ( [Date] - DATE( YEAR( [Date] ) - 1 ; 12 ; 31 )) * 1. Mentsük a módosításokat a Konfiguráció menté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 importá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 parancsá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 deklaráció automatikus.
Elfáradtam Gyerekek! Hamarosan folytatom a naptár tábla kapcsolatai című mesével, amelyben izgalmas kalandokba keveredünk a naptár adatbázisba integrálása és frissítése közben. Jó éjszakát Gyerekek, álmodjatok szépeket!
margitfalvi.arpad@gmail.com
