feltételes formázás a pivot táblában
2019-04-24    Excel 2019
A PowerPivot bővítmény KPI (Key Performance Indicator) szolgáltatása egy ikonkészletes feltételes formázás, amely egyéni össze­sí­té­sek cél­ér­té­kes vagy középértékes vizuális elemzését teszi lehetővé.
    Az ikonkészletes feltételes formázással általában a vizsgált adathalmaz elemeinek nagyságát kívánjuk szemléltetni. A kategóriákhoz ren­delt ábrácskák ismeretében, már egyetlen futó pillantással megállapíthatjuk egy elem hozzávetőleges nagyságát.
    A célértékes vizuális elemzés az ideálisnak vélt érték, azaz a célérték, megközelítésének módja szerint lehet alulról közelítő vagy fe­lül­ről közelítő. Meghatározott halmazok elemeitől azt várjuk, hogy minél nagyobbak legyenek és így közelítsék meg az általunk ide­á­lis­nak vélt ér­té­ket, azaz a célértéket. Nevezzük ezeket „minél nagyobb, annál jobb” típusú halmazoknak. Más halmazok elemeitől, éppen ellen­ke­ző­leg, azt kívánjuk, hogy legyenek minél kisebbek és ezáltal kerüljenek közelebb az optimálisnak vélt értékhez. Ezeket pedig hív­juk „mi­nél kisebb, annál jobb” típusú halmazoknak. Az első típushoz tartozó halmazokat alulról közelítő, a második típushoz tar­to­zó­kat fe­lül­ről kö­ze­lí­tő fel­té­te­les formázással elemezzük. Alulról közelítő módszert alkalmazunk például a bevételeink elemzésekor, de a ki­a­dá­sa­in­kat már a fe­lül­ről kö­ze­lí­tő módszerrel vizsgáljuk.
PowerPivot, feltételes formázás, a célérték megközelítése
A középértékes feltételes formázást azoknál a halmazoknál alkalmazzuk, amelynek elemeitől azt várjuk el, hogy a számegyenesen áb­rá­zolt ideális értékhez, azaz a középértékhez, a lehető legközelebb álljanak. Másként fogalmazva a vizsgált érték és a középérték ab­szo­lút kü­lönb­sé­ge a lehető legkisebb legyen. Példaként gondoljunk autóbusz járatok követési idejére vagy egy valutára, amelyet az or­szág nem­ze­ti bankja tranzakciókkal próbál egy árfolyam-sávban tartani.
    A pivot táblában alkalmazható grafikus elemzés az egyéni összesítés értéktartományának szakaszokra bontásán és a kiválasztott ikon­kész­let elemeinek a szakaszokhoz rendelésén alapszik. A szolgáltatás legfontosabb tulajdonságait a felhasználó határozza meg: [1] ikon­kész­le­tet választ, [2] megadja az elemzés típusát, célértékes vagy középértékes vizsgálatot szeretne, [3] megadja a sza­kasz­ha­tá­ro­kat, [4] a célértéket illetve a középértéket, valamint [5] meghatározza az ábrácskák sorrendjét.
    A bővítmény ablakában, adatnézetben, az egyéni összesítést a számítási területen kijelölve, a Kezdőlap, Számítások, KPI lét­re­ho­zá­sa, vagy az egyéni összesítés helyi menüjében a KPI létrehozása… paranccsal állíthatunk be feltételes formázást. Az egyéni össze­sí­tés helyi menüje tartalmazza a feltételes formázás módosításának és törlésének utasításait is. A program-ablakban egy listában ta­lál­ha­tók a fel­té­te­les formázás parancsai: PowerPivot, Számítások, KPI-k.
    Először ismerkedjünk meg az alulról közelítő vizuális elemzés tulajdonságaival egy kéttáblás adatbázis segítségével: diákok - vizs­gák. A feladat szempontjából lényeges körülmények: [1] egy diák több tantárgyat hallgat, [2] egy tantárgyból több vizsgát kell ab­szol­vál­nia, [3] meg­fe­lelt a tantárgyi követelménynek az a hallgató, akinek a vizsgákon elért pontszámainak átlaga elérte az abból a tantárgyból el­nyer­he­tő maxi­má­lis pontszámok átlagának hatvan százalékát, [4] kitűnően megfelelt az a hallgató, aki kilencven százalékot ért el. Ér­té­kel­jük a hall­ga­tók féléves munkáját, tantárgyanként, a megszerzett pontszámaik alapján.
PowerPivot, feltételes formázás, alulról közelítő vizuális elemzés
Az egy oldali tábla (vizsgák) tartalmazza az összes tantárgy összes vizsgáját és az egyes vizsgákon elérhető maximális pontszámot. A két táblát a vizsga azonosítóját tartalmazó mező kapcsolja össze. Az adott vizsgán elért pontszámot a több oldali táblában (diákok) ta­lál­juk. Az értékelést pivot tábla segítségével fogjuk elvégezni. Egyéni összesítéssel megállapítjuk az egyes hallgatók, az adott tantárgy vizs­gá­in meg­szer­zett pontszámainak átlagát, majd egy három-elemű ikonkészletes-formázással megjelenítjük, hány százaléka ez az ér­ték, a maxi­mális pontszámok átlagának, amelyet szintén egyéni összesítéssel számolunk ki.
    A kimutatás sormezői a ’diákok’[név] és a ’vizsgák’[tantárgy] lesznek. A statisztikai mező, a „teljesítmény” nevű egyéni összesítés, amely a hallgatók átlagos pontszámát számolja ki. Képlete: =ROUND( AVERAGE( ’diákok’[pontszám] ) ; 0 ). Ezután hozzuk létre a fel­té­te­les for­má­zás célértékét szolgáltató egyéni összesítést! Neve legyen „maximális”. Képlete =CALCULATE( ROUND( AVERAGE( ’vizs­gák’[ma­xi­má­lis pontszám] ) ; 0 ) ; ’diákok’ ). A második összesítés létrehozása után adjuk meg a feltételes formázás tulajdonságait.
    A Fő teljesítmény mutató feliratú parancstáblán először a KPI alapmezője (érték) feliratú listából ki kell választanunk a formázni kí­vánt egyéni összesítést. Ezt követően a célértéket kell deklarálnunk: [1] ha ez konstans, akkor beírjuk a Rögzített érték mezőbe, [2] ha egy má­sik egyéni összesítés, akkor a Mérték legördülő listából kell kiválasztanunk a nevét. A mi esetünkben ez a „maximális” nevű egyéni össze­sí­tés! A szakaszhatárok megadása előtt ikonkészletet kell választanunk a parancstábla aljáról. A vizuális elemzéshez a bővítmény négy darab há­rom elemű és egy darab öt elemű ikonkészletet kínál. Válasszunk egy három elemű készletet!
    Az egyéni összesítés értéktartományának szakaszhatárait az Állapotküszöbök definiálása felirat alatt álló színes sávon kell meg­ad­nunk. A modul három elemű ikonkészlethez két darab, öt elemű ikonkészlethez négy darab szakaszhatárolót jelenít meg. A kis, töl­csér­re em­lé­kez­te­tő ábrácskát lenyomott egérbillentyűvel jobbra-balra huzigálhatjuk. Állítsuk be az első csúszkát hatvan, a másodikat ki­lenc­ven szá­za­lék­ra!
PowerPivot, feltételes formázás, szakasz-határok beállítása
Ha az elemzés célértéke egyéni összesítés, akkor a határértékeket az egyéni összesítés eredményének százalékában adhatjuk meg. Kons­tans célérték esetén a határértékek is konstansok lesznek. A sávon megjelenített Cél feliratú szaggatott vonal a célértéket jelöli. A mi példánkban ez száz százalékot jelent.
PowerPivot, feltételes formázás, automatikus szakasz-határok
A feltételes formázás tulajdonságainak megadását követően az egyéni összesítés vezérlője átalakul a mezőlistán. Az fx jelzés helyett egy közlekedési lámpát, az egy jelölőnégyzet helyett hármat kapunk, amellyel külön-külön szabályozhatjuk a statisztikai értékeknek (Ér­ték <e­gyéni összesítés>), a feltételes formázás cél- vagy középértékének (Cél) illetve a feltételes formázásnak (Állapot) a meg­je­le­ní­té­sét. Adat­né­zet­ben a feltételes formázást a bővítmény pici, háromszínű téglalappal jelöli.
PowerPivot, feltételes formázás, megjelenítése a mezőlistán és adatnézetben
A feltételes formázás létrehozás után az ábrácskák helyett számok (-1, 0, 1) jelennek meg a kimutatásban. Ez program-hiba. Ha az Ál­la­pot jelölőnégyzet pipáját töröljük, majd újra megadjuk, akkor láthatóvá válnak az ikonok.
PowerPivot, feltételes formázás, ábrácskák megjelenítése
Konstans célérték megadása szükségessé teheti a színes sávval szimbolizált értéktartomány tágítását illetve szűkítését. Erre a célra szol­gál a sáv két szélén álló kétirányú nyíl. Ha lenyomott egér-billentyűvel a kis ábrácskát „kihúzzuk” a panelről, akkor tágítjuk, ha „be­húz­zuk” a panel közepe felé, akkor szűkítjük a szimbolizált értéktartományt.
PowerPivot, feltételes formázás, értéktartomány módosítása
A felülről közelítő célértékes vizuális elemzés létrehozásához át kell húznunk a kis tölcséreket a célérték jobb oldalára és ha szükséges a négy színes sáv bal alsó elemével meg kell fordítanunk az ikonok sorrendjét.
    A középértékes feltételes formázás egy alulról közelítő és egy felülről közelítő célértékes elemzés egyesítése. Beállítása az in­ter­val­lum-sáv alatti négy színes téglalap jobb felső elemével történik. A jobb alsó vezérlővel az ikonok sorrendjét fordíthatjuk meg. A meg­je­le­ní­tés tu­laj­don­sá­ga­i­nak megadási módja azonos a célértékes elemzésnél megismerttel.
PowerPivot, középértékes feltételes formázás beállításai
A Fő teljesítmény mutató KPI panel egy összetett parancstábla, két lappal. A lapok közötti váltás vezérlője a felfelé mutató kettős nyíl a pa­nel bal alsó sarkában. A parancstábla második lapján megjegyzéseket fűzhetünk a feltételes formázás objektumaihoz.
    Nagyon csendes vagy ma Pistike! Le vagyok nyűgözve Tanító bácsi! Értem! De mi az a „nyűg”? Mozgást gátló kötés, Tanító bácsi! Ré­gi emberek, ha azt akarták, hogy lovaik ne szaladgáljanak el, akkor mellső lábaikat lazán összekötötték… Aha, akkor ez valami bék­lyó fé­leség!
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com