mező szűrése feltétellel: végösszegek szelektálása

2018-07-19    Excel 2016
A mező feltétellel való szűrése, történhet a tétel-címkék, illetve a tételek végösszegeinek szelektálásával. Ebben a cikkben a 2016-os Excel verzió végösszeg szűrőit mutatom be.
A példáink a kötések nevű adatbázis-táblázat elemzésén alapulnak. A táblázat egy biztosító társaság 2017-ben kötött szerződéseinek sorszámát (azonosító), dátumát (kötés), a klienst felhajtó munkatárs nevét (üzletkötő), a biztosítás fajtájának belső azonosítóját (típus) és értékét (összeg) tartalmazza.
pivot tábla, mező szűrése végösszeg alapján
A tételek címkék szerinti szelektálásakor a rendelkezésünkre álló eszközök a címke adattípusától függenek. Ezért beszélhetünk szöveg-, szám- és dátumszűrőkről. A végösszegek szerinti szelektálás, ebből a szempontból, egyszerűbb, mert csak számokra kell a feltételeket meghatároznunk. Készítsünk pivot táblát a következő felépítéssel: SOROK: üzletkötő. ÉRTÉKEK: összeg (összesen néven, HUF pénznem formátummal), azonosító (darabszám néven). Nézzük meg, milyen relációkat találunk az Értékszűrők listában az üzletkötő mező tételeinek végösszeg szerinti szelektálásához!
pivot tábla, értékszűrők
Nagy meglepetést a relációk listája nem okozhatott. Az Értékszűrők lista tetszőleges relációjára kattintva az Értékszűrő (<mezőnév>) panel jelenik meg. Válasszuk ki a szűrni kívánt mezőt, módosítsuk a relációt, ha esetleg mellényúltunk, és adjuk meg a viszonyítási alapot! Például kik azok az üzletkötők, akik legalább kilencvenhat kötést hoztak tető alá?
pivot tábla, értékszűrők
A tételeket végösszegük rangsora szerint is szűrhetjük. A rangsort a végösszegek nagysága határozza meg: a rangsor elején a nagy-, a végén a kis számok állnak. Egy adott végösszeg rangsorban elfoglalt helyét egy sorszámmal adjuk meg. Ez a sorszám a végösszeg rangsor-pozíciója. Az azonos végösszegek rangsor pozícióját a pivot táblában elfoglalt helyük alapján rangsoroljuk. Az a végösszeg, amely a pivot táblában, fölülről lefelé haladva, előrébb áll, az a rangsorban is megelőzi, a pivot táblában őt követő azonos végösszegeket. A tételek rangsora megegyezik a végösszegük rangsorával.
A rangsor szerinti szűrés három módszere közül választhatunk. [1] A kimutatásban meghatározott számú tételt szerepeltetünk a rangsor elejéről vagy a végéről. Ha a kért tételszámmal azonos rangsor-pozíciójú végösszeg többször is szerepel a végösszegek között, akkor azok tételei is részt vesznek az elemzésben. [2] Csak azokat a tételeket szerepeltetjük a pivot táblában, a rangsor elejéről vagy a rangsor végéről, amelyek végösszegeinek összege a mező teljes összegének, meghatározott százalékát adják. Ez az összeg az utolsó előtti- és az utolsó tétel göngyölített összegeivel meghatározott tartományban áll, vagy az utolsó tétel göngyölített összegével azonos. [3] Mint az előző lehetőség, de a kívánt göngyölített összeget nem százalékban, hanem egy számmal határozzuk meg.
Első olvasatra kissé bonyolultnak tűnik a magyarázat, ezért vegyünk egy-egy példát a három lehetőségre! A rangsor szerinti szűrés parancstábláját az Értékszűrők, Toplista… utasítással jeleníthetjük meg. Először a három legtöbb kötést nyélbe ütő üzletkötőt szerepeltessük a pivot táblában!
pivot tábla, szűrés rangsor szerint
A kért három helyett, öt üzletkötőt kaptunk, ha tizet kérünk tizenegyet jelenít meg a program. Miért? Mert a rangsor harmadik és a tizedik helyén álló tétel végösszege további tételekben is szerepel.
Lássuk a második lehetőséget! Kíváncsiak vagyunk azokra a legnagyobb árbevételű (összesen) üzletkötőkre, akik az éves árbevétel (összeg végösszege) tíz százalékát produkálták! A rangsor szerinti szelektálást közvetlenül válthatjuk, a szűrő törlése nélkül, egy másik rangsor szerint szelektálásra.
pivot tábla, szűrés rangsor szerint
Az összesen statisztikai mező teljes összege 898 490 000 HUF. Ennek tíz százaléka 89 849 000 HUF. Miért kaptunk akkor 100 410 000 HUF összegű tételt? A kép jobb oldalán álló táblázatban meg tudjuk figyelni a göngyölített összegeket: Szigeti Marcell végösszegével majdnem elérjük a tíz százalékot, de kell még egy „kicsi”, és így Tomcsik Gellértet is szerepeltetni kell a pivot táblában.
A harmadik lehetőség értelmezése már nem okozhat gondot! Kik azok, a legtöbb klienst felhajtó üzletkötők, akiknek összesen ezer szerződést köszönhetünk?
pivot tábla, szűrés rangsor szerint
A példa nem igényel kommentárt. A szokásos beállítások mellett a címkék szűrése kizárja a végösszegek szűrését és viszont. Ha együtt szeretnénk a két lehetőséggel élni, akkor a Kimutatáseszközök, Elemzés, Kimutatás, Beállítások, Összegek és szűrők, Mezőnként több szűrő engedélyezése vezérlővel feloldhatjuk a korlátozás. Az együtt használt címke- és végösszeg szűrők logikai ÉS viszonyban állnak egymással.
Csak azok az üzletkötők szerepeljenek az elemzésben, akiknek a neve „s” betűvel kezdődik és legalább hetvenegy kötést hoztak a konyhára!
pivot tábla, felirat és értékszűrők együttes használata
Összefoglalva a pivot tábla csoportosító mezőinek szűrési lehetőségeit: a tétel-lista és a külső szűrő az elemzendő tételek közvetlen megadását teszik lehetővé, a kritériumon alapuló szűrés pedig a közvetett eszköz. A feltétel vonatkozhat a tétel-címkékre illetve a tételek végösszegeire. Ennyi.
margitfalvi.arpad@gmail.com