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

2022-01-07    Excel 2019
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 az utóbbi lehetőséget ismerhetjük meg. A példáink a „szerződések” nevű adatbázis-táblázat pivot táblás elemzései. A táblázat egy biztosító társaság 2021-ben kötött szerződéseit tartalmazza. Oszlopai a következők. Azonosító: a szerződés sorszáma (adattípusa szöveg). Dátum: a szerződés aláírásának napja (dátum). Üzletkötő: az ügyfelet felhajtó munkatárs neve (szöveg). Típus: a biztosítás kódja (szöveg). Ár: a szerződés értéke (pénznem).
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élü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: ár (bevétel néven, HUF pénznem formátummal), azonosító (szerződés néven, db egyéni formátummal). 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 szerződé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űrő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ó megjelenített tétel göngyölített összegeivel meghatározott tartományban áll, vagy az utolsó megjelenített 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 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 szerződé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, mert a rangsor harmadik 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ű üzletkötőkre, akiknek bevételeik összege az éves árbevétel (árbevétel végösszege) tíz százalékát adjá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
A program először kiszámolja a bevétel statisztikai mező teljes összegének tíz százalékát (89 849 000 HUF), majd a háttérben nagyság szerint (bevétel), csökkenő sorrendbe állítja a tételeket, és az így kialakított számsorral futó összeget képez. A göngyölített összegek tömbjéből ezután kiválasztja azt a legkisebb számot, amely egyenlő vagy nagyobb a műveletsor elején megállapított határértéknél. A kiválasztott számhoz tartozó tétel lesz az utolsó megjelenítendő tétel.
A harmadik lehetőség értelmezése már nem okozhat gondot! Kik azok, a legtöbb szerződést hozó ü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