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 kettőé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 statisztikai mező értékeinek feldolgozása. Ebben a cikkben az első csoportba tartozó objektumokat mutatom be az Excel 2016-os verzió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ö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í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 kartonok 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 halmazok, 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 tartalmazó 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 parancsgombbal 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. Adjunk 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 áttekinthető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éplete: = '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 engedmé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ódosí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 felté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ételenké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áblá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 megvilágítani 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 bruttó 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 jelensé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 nettó ár összege. Próbáljuk ki: = KEREKÍTÉS( SZUM( <nettó ár> ) * 127% * 85% ; -2 ), ami egyenlő a bruttó ár végösszegé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ő alapjá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ástechniká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étrejö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 alapuló 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 kiválasztott 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ű) eladásainak 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, cellaformázásként megadva, [3] típus mezőhöz, kereskedők néven, a kis- és nagykereskedők eladásainak összegzésére ( = kiskereskedő + nagykereskedő )! A feleslegessé vált tételeket rejtsük el szűréssel! Fajta mező: chardonnay, királylányka, olaszrizling, szürkebará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 kereskedő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árlások karton-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épleteket! 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átjuk a szerkesztő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 program létrehozásuk sorrendjében értékeli ki őket. Magyarul, <kiskereskedői arány> + <nagykereskedői arány> = 36,1 %. A hibát a Kimutatáseszközök, Elemzés, Számítások, Mezők, elemek és halmazok, Megoldási sorrend… utasításra megjelenő parancstáblán orvosolhatjuk.
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 kimutatáscella é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ű feldolgozá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 tartalmazó 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ó helyen: mező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 halmazok, Képletek felsorolása menüpontra.
margitfalvi.arpad@gmail.com