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 Ex­cel 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 szer­ző­dé­se­i­nek sor­szá­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 fel­té­te­le­ket meg­ha­tá­roz­nunk. 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énz­nem 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 üz­let­kö­tő mező té­te­le­i­nek 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>) pa­nel je­le­nik 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 vi­szo­nyí­tá­si a­la­pot! Pél­dá­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 rang­sor-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 pi­vot 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­össze­ge­ket. 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 rang­sor 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, ak­kor az­ok 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 utol­só 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 pa­rancs­táb­lá­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 sze­re­pel­tes­sü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 ti­ze­dik 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ált­hat­juk, a szűrő tör­lé­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 majd­nem 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 szer­ző­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, Me­zőn­ké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 vi­szony­ban á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 kony­há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 meg­a­dá­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­össze­ge­i­re. Ennyi.
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com