számított mező, számított tétel
2018-08-20    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 mező és a sta­tisz­ti­kai mező értékeinek feldolgozása. Ebben a cikkben az első csoportba tartozó objektumokat mutatom be az Excel 2016-os ver­zi­ó­já­ban.
    Példánk egy borászat 2017-es értékesítéseit tartalmazó, eladások nevű adatbázis-táblázat lesz, amelyet a lenti kép mutat. Mezői a kö­vet­ke­zők. Számla: a számla sorszáma. Dátum: a vásárlás napja. Név: a vásárló neve. Típus: a vásárló kereskedelmi besorolása. Tétel: az adott tétel sorszáma a számlán belül. Fajta: a vásárolt bor szőlő-fajtája. Évjárat: a termés éve. Üveg: egy üveg nettó ára. Karton: a vá­sá­rolt kar­to­nok száma. A legkisebb vásárolható mennyiség egy karton, amely hat üveget tartalmaz. Nettó ár: a tétel ára forgalmi adó nélkül.
pivot tábla, példa
Hozzunk létre pivot táblát az eladások adatbázis-táblázat elemzésére! Helyezzük a számla mezőt a segédablak SOROK területére, majd nyissuk meg a számított mezők kezelésére szolgáló parancstáblát a Kimutatáseszközök, Elemzés, Számítások, Mezők, elemek és hal­ma­zok, Számított mező… utasítással.
pivot tábla, számított mező létrehozása
Először a számlák ÁFA tartalmát fogjuk képezni. A számított mező neve forgalmi adó legyen! Képlete: = ’nettó ár’ * 27%. A szóközt tar­tal­ma­zó neveket aposztrófok között kell a képletbe beírnunk. A panel Mezők listájából dupla kattintással vagy a Mezők beszúrása pa­rancs­gomb­bal is bevihetjük a kiválasztott mezőnevet a képletbe. A képlet véglegesítése a Hozzáadás gombbal történik.
    A számított mezőt a program automatikusan a segédablak ÉRTÉKEK területére helyezi és így azonnal megjelenik a pivot táblában. Ad­junk nevet a statisztikai mezőnek: ÁFA, és a számformátum beállításával tüntessük el a pénznem jelölőt, javítva ezzel az adatok át­te­kint­he­tő­sé­gét.
    A forgalmi adó ismeretében már képezni tudjuk a számla végösszegét. Hozzunk létre újabb számított mezőt, bruttó ár néven! Kép­le­te: = 'nettó ár' + 'forgalmi adó'. Az újabb statisztikai mező neve legyen bruttó!
számított mező a pivot táblában
Módosítsuk a bruttó ár számított mező képletét: [1] azok a vevők, akik legalább tíz kartont vásároltak, kapjanak tizenöt százalék en­ged­ményt, [2] a számítást százas pontosággal végezzük! Jelenítsük meg ismét a Számított mező beszúrása parancstáblát, majd vá­lasszuk ki a Név vezérlő listájából a bruttó ár mezőt!
pivot tábla, számított mező képletének módosítása
A módosítsuk a képletet = KEREKÍTÉS( HA( karton > 9 ; ( 'nettó ár' + ’forgalmi adó’ ) * 85% ; 'nettó ár' + ’forgalmi adó’ ) ; -2 )! A javítást a Mó­do­sí­tás gombbal véglegesítjük. Gondolkozzunk el egy kicsit a képleten! Hogyan értelmezi a program a képletben a karton mezőt? A fel­té­tel teljesülését tételenként vagy számlánként vizsgálja? Ha nézegetjük az eladások táblázatot láthatjuk, hogy a karton-szám té­te­len­ként maximálisan négy. Ha tételenként történne a feldolgozás, akkor ezek szerint, senki sem kaphatna engedményt, de a pivot táb­lá­ban mé­gis találunk ilyen kereskedőket! Tehát a program a csoport (a számla) karton-számát ellenőrzi. Egy másik példával is szeretném meg­vi­lá­gí­ta­ni ezt a működését. Egészítsük ki a pivot táblát egy második sor mezővel: fajta!
pivot tábla, számított mező részösszege
A kép az első három számla elemzését mutatja. A számla-számok sorában megjelentek a részösszegek. Ha alaposan megfigyeljük a brut­tó statisztikai mező részösszegeit, akkor kiderül, hogy az első és a harmadik vásárlónál valami nincs rendben! Első számla: 35 100 + 35 800 + 13 900 + 31 700 = 116 500, és nem 99 000. Harmadik számla: 16 500 + 34 100 + 8 400 + 20 400 = 79 400, és nem 67 500. A je­len­ség magyarázata: a program a részösszegeket is a számított mező képletével képzi. Tehát az egyes fajtákért fizetendő összegek nem tartalmaznak kedvezményt, viszont számla-szinten már jár a tizenöt százalék!
    A végösszeg számítása is a számított mező képletével történik. Kapcsoljuk be az eladások adatbázis-táblázat összegsorát! Megjelenik a net­tó ár összege. Próbáljuk ki: = KEREKÍTÉS( SZUM( <nettó ár> ) * 127% * 85% ; -2 ), ami egyenlő a bruttó ár vég­össze­gé­vel.
pivot tábla, számított mező végösszege
A Számított mező beszúrása parancstábla Törlés gombjával nem csak a Név vezérlő listájában kiválasztott számított mezőt, de a mező alap­ján létrehozott statisztikai mezőt is töröljük.
    A számított mező bemutatását befejezve, lovagoljunk egy kicsit még a fogalmakon! A származtatott- és a számított mező a szá­mí­tás­tech­ni­ká­ban gyakran szinonimák, de nem a pivot táblában! Itt önálló objektumok: [1] a származtatott mező a tételek csoportosításakor lét­re­jö­vő mező, [2] a számított mező egy felhasználói képleten alapuló objektum, amely a mezők egyéni feldolgozását teszi lehetővé.
    A számított mező mellett, számított tételt is létrehozhatunk. Szerepe a számított mezőhöz hasonlóan, a tételek felhasználói képleten ala­pu­ló feldolgozása. Alakítsuk át a pivot táblánkat! SOROK: fajta. OSZLOPOK: típus. ÉRTÉKEK: karton (karton össz. néven).
    A számított tétel létrehozása a tételt tartalmazó mező meghatározásával kezdődik: kattintsunk a pivot táblában a fajta mező nevére vagy egyik tételére, majd adjuk ki Kimutatáseszközök, Elemzés, Számítások, Mezők, elemek és halmazok, Számított tétel… utasítást.
pivot tábla, számított tétel létrehozása
A parancstábla a számított mezők kezelésére szolgáló panel kiegészítése a Tételek listájával. Utóbbi vezérlő a Mezők listájában ki­vá­lasz­tott mező tételeit tartalmazza. A számított tétel létrehozása, módosítása és törlése azonos a számított mezőével.
    Hozzunk létre három számított tételt, ebben a sorrendben: [1] fajta mezőhöz, száraz néven, a száraz borok értékeinek összegzésére, = SZUM( chardonnay ; királylányka ; olaszrizling ; szürkebarát ), [2] fajta mezőhöz, arány néven, amely az édes bor (hárslevelű) el­a­dá­sa­i­nak arányát, mutatja, az összes eladáshoz viszonyítva (hárslevelű / ( hárslevelű + száraz )), százalék számformátum egy tizedes jeggyel, cel­la­for­má­zás­ként megadva, [3] típus mezőhöz, kereskedők néven, a kis- és nagykereskedők eladásainak összegzésére ( = kis­ke­res­ke­dő + nagykereskedő )! A feleslegessé vált tételeket rejtsük el szűréssel! Fajta mező: chardonnay, királylányka, olaszrizling, szür­ke­ba­rát. Típus mező: kiskereskedő, nagykereskedő.
számított tétel a pivot táblában
A pivot táblát nézegetve szembeötlő a kereskedők hárslevelű vásárlásainak magas aránya: 36,1%. De nem is jó ez a szám! A ke­res­ke­dők összes vásárlásai hozzávetőlegesen harmincnyolcezer karton, ennek tíz százaléka háromezer nyolcszáz. Tehát a hárslevelű vá­sár­lá­sok kar­ton-száma még a teljes kereskedői vásárlás húsz százalékát sem teszi ki. Mi lehet a hiba? Először ellenőrizni kellene a kép­le­te­ket! Ezt könnyen megtehetjük, egyszerűen rákattintunk a pivot táblában az arány egy értékére, mondjuk a kereskedőkére. És ezt lát­juk a szer­kesz­tő­lé­cen: = kiskereskedő + nagykereskedő.
    Talán már rá is jöttek a magyarázatra. Az arány sorral és a kereskedők oszloppal meghatározott cellában két képlet „áll” és a prog­ram lét­re­ho­zá­suk sorrendjében értékeli ki őket. Magyarul, <kiskereskedői arány> + <nagykereskedői arány> = 36,1 %. A hibát a Ki­mu­ta­tás­esz­kö­zök, Elemzés, Számítások, Mezők, elemek és hal­ma­zok, Megoldási sorrend… utasításra megjelenő parancstáblán or­vo­sol­hat­juk.
pivot tábla, számított tételek kiértékelési sorrendje
A panel parancsgombjai felett álló magyarázó szöveg majdnem tökéletes, de kimaradt belőle az „akkor” és a „képlet”. Ha a ki­mu­ta­tás­cel­la értékét több számított tétel befolyásolja, akkor az érték tényleges meghatározója a megoldási sorrendben utolsóként szereplő képlet lesz.
    A számított tétel képletében tétel-indexet is használhatunk. Ez a lehetőség állandó pozíciójú, de változó nevű tételek egyszerű fel­dol­go­zá­sát teszi lehetővé: a pivot tábla frissítését követően a képleteket nem kell újraírni. A sorszámmal azonosított tétel írásmódja más ha az őt tar­tal­ma­zó számított tétel első vagy az utolsó a tételek sorában. Első helyen a hivatkozott tétel azonosítója: mezőnév[+index]. Utolsó he­lyen: me­ző­név[index]. A tételek sorszámozása egytől kezdődik és felülről lefelé vagy balról jobbra halad. Az index negatív szám nem lehet.
pivot tábla, indexek használata számított tétel képletében
Aki listát szeretne a számított mezőkről és tételekről, az kattintson Kimutatáseszközök, Elemzés, Számítások, Mezők, elemek és hal­ma­zok, Képletek felsorolása menüpontra.
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com