a statisztikai értékek feldolgozása
2018-09-30    Excel 2016
A pivot táblában négy számított objektumot hozhatunk létre. Ebből kettőnek a képletét közvetlenül a felhasználó szerkeszti meg, a másik ket­tő­ét a program állítja össze. Az első csoportba tartozik a számított mező és a számított tétel, a másodikba a statisztikai me­ző és a sta­tisz­ti­ka­i me­ző értékeinek feldolgozása. Ebben a cikkben a második csoportba tartozó objektumokat mutatom be. A szöveg az Excel 2016-os verziója a­lap­ján készült.
    Példánk egy borászat 2017. első negyedéves értékesítéseit tartalmazó, eladások nevű adatbázis-táblázat lesz, amelyet a lenti kép mu­tat. Mezői a következők. Számla: a számla sorszáma. Dátum: a vásárlás napja. Név: a vásárló neve. Tétel: a tétel sorszáma a számlán be­lül. Faj­ta: a vásárolt bor szőlő-fajtája. Évjárat: a termés éve. Egységár: egy üveg bor ára. Üveg: vásárolt palackok száma. Ár: a tétel ára.
a pivot tábla statisztikai értékeinek feldolgozása, példa
A statisztikai mező egy képleten alapuló számított objektum a pivot táblában. A program a felhasználó által kiválasztott művelet függ­vé­nyé­vel vizsgálja a pivot tábla ÉRTÉKEK területén álló mező csoportjait, amelyeket a sor- és oszlop mezők tételei határoznak meg. Kép­le­te: FÜGGVÉNY(adat-csoport). Tehát, ahány csoport annyi képlet, és annyi eredmény, vagy másként fogalmazva, annyi statisztikai érték.
    Képezzük az eladások összegét pivot táblával, szőlőfajta és, azon belül, évjárat szerint, havi bontásban. A Hónapok mezőt a prog­ram­mal ho­zas­suk létre, a dátum mező OSZLOPOK területre helyezésével.
pivot tábla, statisztikai értékek feldolgozása, összegek
A pivot tábla statisztikai értékeivel további számításokat végezhetünk. A feldolgozáshoz használt képletet, ebben az esetben is, a prog­ram állítja össze, nekünk csak a műveletet paramétereit kell beállítanunk. A számítás eredménye a statisztikai érték cellájában, azt fe­lül­ír­va, je­le­nik meg. Talán ezért nevezi a program a statisztikai értékek feldolgozását „megjelenítésnek”?!
    A művelet részleteit beállíthatjuk a pivot tábla segédablakában és magában a pivot táblában is. A segédablak ÉRTÉKEK területen ál­ló me­ző menüjéből az Értékmező-beállítások… utasítással hívható az a parancstábla, amelynek Az értékek megjelenítése lapján találjuk a szá­mí­tá­sok vezérlőit. Felül az eljárások-, alatta a mező- és a tétel-lista. Utóbbi kettő csak akkor aktív, ha a kiválasztott számítás pa­ra­mé­ter meg­a­dá­sát igényli. Az aktív mező-lista a forrás táblázat minden oszlopát tartalmazza, de természetesen a számítás paramétere csak a pivot táblában szereplő mező lehet. Ha véletlenül mellényúlunk, akkor a program a statisztikai értékeket a #HIÁNYZIK hibaértékre cseréli.
    A pivot táblában tetszőleges statisztikai érték helyi menüjéből, Az értékek megjelenítése utasítással jeleníthetjük meg a számítások lis­tá­ját. Ha paraméteres eljárást választottunk, akkor azt, egy automatikusan megjelenített kis parancstáblán kell megadnunk. Ezen a pa­ne­lon már csak a pivot táblában szereplő mezőket sorolja fel a program.
    Még mielőtt elmerülnénk a részletekben, nevezzük meg a statisztikai mező kiszámított értékeit.
pivot tábla, a statisztikai értékek elnevezése
[1] A „statisztikai érték” a belső csoportosító mezők tételei által meghatározott adat-csoport statisztikai vizsgálatának eredménye. [2] A „csoport érték” a külső csoportosító mezők tételei által meghatározott adat-csoport statisztikai értéke. [3] A sor mező tételei által meg­ha­tá­ro­zott adat-csoport statisztikai értéke a „sor érték”, az oszlop mező tételei által meghatározott adat-csoport statisztikai értéke a „oszlop ér­ték”. [4] A statisztikai mező összes adatának statisztikai értéke a „tábla érték”.
    Kezdjük az ismerkedést a „göngyölített összeg” számítással! A számítás paramétere az évjárat mező legyen!
pivot tábla, göngyölített összeg
Sok a szám! Nehéz kisilabizálni, hogy milyen művelet történt. Vezessünk be egy egyszerű jelölés-rendszert a pivot táblánk statiszti­kai ér­té­ke­inek azonosítására! [statisztikai érték] <fajta-név első betűje><évjárat utolsó számjegye><hónap első betűje>. Például: m7f je­len­té­se mer­lot, 2017-es, február. [csoport érték] <fajta-név első betűje><hónap első betűje>. Az utolsó oszlopban: <fajta-név első betűje>. [sor ér­ték] <fajta-név első betűje><évjárat utolsó számjegye>. [oszlop érték] <fajta-név első betűje><hónap első betűje>. [tábla érték] nagy Ö betű. Valahogy így!
pivot tábla, göngyölített összeg egyedi jelölésekkel
Az azonosítókat használva, most már meg tudjuk jeleníteni a „göngyölített összeg” számítás három változatának képletét. A mező-pa­ra­mé­ter legyen újra a évjárat.
pivot tábla, göngyölített összeg egyedi jelölésekkel
Ahogy látjuk a belső sor mezőt választva a program a statisztikai értékeket és a sor értékeket göngyölíti, függőlegesen, a külső sor mező té­te­le­i­ben. Most válasszuk a számítás paraméterének a fajta mezőt!
pivot tábla, göngyölített összeg egyedi jelölésekkel
Külső sor mező választása esetén az azonos címkéjű statisztikai értékek, a sor értékek és a csoport értékek kerülnek függőlegesen gön­gyö­lít­ve. Vizsgáljuk meg a harmadik lehetőséget. A számítás paramétere a Hónapok mező legyen!
pivot tábla, göngyölített összeg egyedi jelölésekkel
Oszlop mező választása esetén a statisztikai értékek, az oszlop értékek és a csoport értékek kerülnek vízszintesen göngyölítve. Ál­ta­lá­no­sít­va a tapasztaltakat, ha sor mezőt választunk a művelethez, akkor a göngyölítés felülről lefelé halad, ha oszlop mezőt, akkor balról jobb­ra. A prog­ram belső csoportosító (sor- vagy oszlop-) mező megadása esetén, a statisztikai értékek göngyölítését a külső cso­por­to­sí­tó me­ző té­te­le­i­ben új­ra­kez­di, külső csoportosító mező választásakor, az azonos címkéjű tételek statisztikai értékét göngyölíti.
    A következő számítás az „eltérés” legyen, amely meghatározott statisztikai értékek különbségét adja. Paraméterként meg kell adnunk a számítás dimenzióját meghatározó mezőt és a számítás viszonyítási alapját adó tételt. A tétel-deklaráció lehet abszolút, a mező egy tet­sző­le­ges tételét megnevezve, vagy relatív, a képletet tartalmazó tétel egyik szomszédjának a pozícióját megadva: előző, következő. Szá­mít­suk ki a havi bevételek változását évjárat és fajta szerint, az előző hónaphoz képest!
    A statisztikai értékek feldolgozásának eredményét a számítások lista Normál tételével törölhetjük. Az utasítás hatására a pivot táb­lá­ban megjelennek a statisztikai értékek. Ha csak a feldolgozás képletét módosítjuk, akkor a program automatikusan törli az előző szá­mí­tás ered­mé­nyét. Tehát nekünk, a módosítás előtt, nem kell visszaállítanunk az eredeti állapotot.
pivot tábla, eltérések
Ismételjük meg számítást, de most a viszonyítási alap a januári bevételünk legyen! Nem kockáztatunk túl sokat, ha kijelentjük, a vál­to­zás feb­ru­á­ri értékei nem fognak változni.
pivot tábla, eltérések
Az „eltérés” nevű számítással a statisztikai értékek és a viszonyítási alap különbségeit képeztük. A változást százalékosan is meg­ad­hat­juk a „százalékos eltérés” számítással. A viszonyítási alap megadási módja azonos az előbb ismertetettel. A paraméterek legyenek a Hó­na­pok mező és az előző hónap statisztikai értéke.
pivot tábla, százalékos eltérés
A százalékos megjelenítés mechanizmusát nem ismerem, ezért a képletekből hiányoznak a konvertáló műveletek, de a számítás lé­nye­gét ez nem befolyásolja. A szokásos két tizedest, a jó olvashatóság érdekében, számformátummal, egyre csökkentettem.
    A további százalékos számítások tárgyalását folytassuk a „göngyölített összeg százalékban” nevű eljárással. A mező-paraméter le­gyen most is a Hónapok mező!
pivot tábla, göngyölített összeg százaléka
A göngyölített összegek osztója az azonos sorban álló összes statisztikai érték összege, amely a sor értékkel azonos. A képletek át­te­kint­he­tő­sé­gé­nek érdekében az osztóban nem a sor összes statisztikai értékének összegét képeztem, hanem csak a sor értéket sze­re­pel­tet­tem.
    A „százalék” nevű számítás a mező-paraméterében megadott mező tételeinek statisztikai értékeit a mező egy másik, relatív vagy ab­szo­lút módon meghatározott, tételének statisztikai értékéhez hasonlítja és az eredményt százalékos formában jeleníti meg. Válasszuk most is a Hónapok mezőt és annak január tételét!
pivot tábla, százalék
A „sorösszeg százaléka” és az „oszlopösszeg százaléka” számítások testvérek: a statisztikai értékek nagyságát adják eredményül a sor ér­ték illetve az oszlop érték százalékában. Mi az utóbbit próbáljuk ki, mert ez az összetettebb dimenziója a pivot táblánknak!
pivot tábla, oszlopösszeg százalék
A „végösszeg százaléka” számítás a statisztikai értékek nagyságát adja eredményül a tábla érték százalékában.
pivot tábla, végösszeg százalék
A külső csoportosító mező tételeinek százalékos összetételét a „szülőösszeg százaléka” nevű számítással képezhetjük. Paraméterként a kül­ső csoportosító mezőt kell megadnunk.
pivot tábla, szülőösszeg százaléka
Ennek a számításnak a kiegészítése a „szülősorösszeg százaléka” és a „szülőoszlop összeg százaléka” nevű eljárások, amellyel nem­csak a külső csoportosító mező-, hanem a sor- illetve az oszlop értékek százalékos összetételét is kiszámíthatjuk. Ha az oszlop értékek össze­té­te­lét vizsgáljuk, akkor a „szülősorösszeg százaléka” számítást, ha a sor értékekét, akkor a „szülőoszlopösszeg százaléka” el­já­rást kell al­kal­maz­nunk. Képezzük a fajta mező statisztikai értékeinek-, és az oszlop értékek százalékos összetételét!
pivot tábla, szülősorösszeg százaléka
A számítások listájában találunk két rangsoroló számítást is: „A legkisebbtől a legnagyobbig rangsorolva…”, „A legnagyobbtól a leg­ki­sebb­ig rangsorolva…”. A számítások a statisztikai értékek rangsor-pozícióját adják a paraméterként megadott mező tételeinek rangsorában.
    A számítás paramétere a mező, amelynek tételeit rangsorolni szeretnénk. [1] Ha az évjárat mezőt adjuk meg, akkor az egyes sző­lő­faj­ták té­te­le­i­nek rangsorát kapjuk, havi bontásban. [2] Ha a Hónapok mező a számítás paramétere, akkor a hónapok rangsorát kapjuk, sző­lő­faj­ták és azon belül, évjáratok szerint. [3] A szőlőfajta választása esetén a fajták rangsorát vizsgálhatjuk és ezen felül még a fajták év­já­ra­ta­i is sorszámozásra kerülnek. Próbáljuk ki a legnagyobbtól a legkisebbig rangsorolva számítást, a fajta paraméterrel!
pivot tábla, a statisztikai értékek rangsorolása
A február hónapot vizsgálva, a fajták rangsora így alakul: merlot, ezerjó, furmint. A februári árbevétel 2015-ös évjáratában az ezerjó a ki­rály, őt követi merlot és a sereghajtó a furmint.
    Már csak egyetlen számítás maradt, az „Index”. A program mutató(szám) értelemben használja ezt a többjelentésű fogalmat. Kép­le­te: sta­tisz­ti­ka­i érték * tábla érték / sor érték * oszlop érték.
pivot tábla, index
A számítás képletéből kiindulva, megállapíthatjuk, hogy az index egytől való eltérése mutatja a vizsgált statisztikai érték és a vele egy sor­ban és egy oszlopban álló értékek nagyságától való eltérését. Például az ezerjó 2015-ös évjáratának februári eladásaiból származó ár­be­vé­tel, mind saját kategóriájában (ezerjó, 2015-ös évjárat havi eladásai), mind a többi kategória februári érbevételei közül is ma­ga­san ki­e­mel­ke­dik. Ezt mutatja az 1-től felfelé jelentősen eltérő 1,33 indexe (számformátummal a tizedesjegyek megjelenítését kettőre kor­lá­toz­tam). Az ezerjó, 2017-es évjáratának februári árbevétele, a maga 0,77-es indexével, meglehetősen alacsony a saját ka­te­gó­ri­á­já­nak havi, és a többi kategória februári árbevételeihez képest.
    A matematikában és számítástechnikában az index sorszámot jelent. Bár ez az index egy viszonyszám, de azért ebben is van egy „ki­csi” sorszám! Figyeljük csak meg! Rejtsük el a pivot táblában a részösszegeket. Készítsünk egy 9 * 3-as (sorok * oszlopok) táb­lá­za­tot és cel­lá­i­ban számoljuk ki az indexek rangsorát a RANG.EGY függvénnyel. A képletben ne a KIMUTATÁSADATOT.VESZ függvényt, ha­nem cella- illetve tartomány hivatkozást használjunk!
pivot tábla, index
Ne felejtsük el, ez nem a statisztikai értékek rangsora, hanem az indexeké. Talán azt mondhatjuk, hogy ez a statisztikai értékek „har­mo­ni­kus” rangsora. Persze ezt csak én mondom!
    Összefoglalva a tapasztalatainkat: az érték mező eredményének feldolgozhatóságával a program újabb lehetőségeket biztosít ada­ta­ink elemzéséhez. Szabadság, egyenlőség, testvériség!
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com