mező szűrése feltétellel: tétel-címkék szelektálása

2018-07-18    Excel 2016
A pivot tábla mezőjének tételei, forrás-oszlopának egyedi adatai. A felhasználó meghatározhatja azoknak a tételeknek a körét, amelyeket szerepeltetni akar az elemzésben. A tételek szelektálása, vagy másként fogalmazva, a mező szűrése, történhet manuálisan, a tétel-listából választva, vagy automatikusan a felhasználó által megadott feltételek alapján. A feltétellel való szűrés csak a pivot tábla csoportosító mezőinél alkalmazható, magyarul csak a SOROK vagy az OSZLOPOK területen álló mezőknél.
Először a fogalmak tisztázásával kell kezdenünk. A tételek feltétellel történő szelektálása történhet a tétel-címkék vagy a tétel-végösszegek alapján. Másként fogalmazva, a feltétel vonatkozhat a mező címkéire vagy a végösszegeire. A feltételt a felhasználó deklarálja, míg teljesülését a program ellenőrzi: csak azt a tételt veszi számításba, amelynek címkéje illetve végösszege megfelel a feltételnek. Ebben a cikkben a tétel-címkék feltétellel való szűrését tekintjük át az Excel 2016-os verziójában.
A feltétel relációból és viszonyítási alapból áll. Szöveg adattípusú címkék esetén a reláció lehet egyenlő, nem egyenlő, kezdete, nem kezdete, vége, nem vége, tartalmazza és nem tartalmazza. A viszonyítási alap szövegre vonatkozó feltétel esetén egy karakterlánc, amely joker karaktereket (?, *) is tartalmazhat. Szám adattípusú címkék esetén a reláció lehet kisebb, kisebb egyenlő, nagyobb, nagyobb egyenlő, tartományban, tartományon kívül és a szövegekhez használt relációk. A viszonyítási alap számokra vonatkozó feltétel esetében egy szám.
pivot tábla, relációk a szűrő-feltételben
A műveleteket a lekötések nevű adatbázis-táblázat alapján létrehozott pivot táblában hajtjuk végre. A táblázat oszlopai a következők. Név: a számla-tulajdonos neve (szöveg). Számla: a számla nyolc számjegyű azonosítója (szöveg). Hónapok: a lekötés időtartama hónapokban (szám). Lekötés: a zárolás időpontja (dátum). Pénz: a lekötött összeg (szám, pénzként formázva).
pivot tábla, szűrés feltétellel, példa
A feltétel deklarálása a program eszközeivel történik. Először a szöveg adattípusú tétel-címkék szelektálását tekintsük át! Helyezzük a név mezőt a pivot tábla SOROK területére, majd a mezőnév menüjében nyissuk meg a Feliratszűrők parancslistát!
pivot tábla, feliratszűrők
Ami nem más, mint a relációk felsorolása. Minket most, csak a szöveghez alkalmazható relációk érdekelnek. A relációra kattintva a kép alján álló panel jelenik meg. Ha a mezőnév menüjében mellényúltunk, akkor a panel bal oldalán álló listában módosíthatjuk a választásunkat. Természetesen, ebben a listában is csak a szövegekhez alkalmazható relációt választhatunk! A viszonyítási karakterláncot a jobb oldali mezőbe kell begépelnünk, amiben joker karakterek is szerepelhetnek.
Vegyük sorra, az egyes relációk jelentését a viszonyítási alap ismeretében. Akkor fog szerepelni a tétel a pivot táblában, ha: [egyenlő] címkéje azonos a viszonyítási alappal, [nem egyenlő] címkéje eltér a viszonyítási alaptól, [kezdete] címkéjének első karakterei azonosak a viszonyítási alappal, [kezdete nem] címkéjének első karakterei eltérnek a viszonyítási alaptól, [vége] címkéjének utolsó karakterei azonosak a viszonyítási alappal, [vége nem] címkéjének utolsó karakterei különböznek a viszonyítási alaptól, [tartalmazza] címkéjében szerepel a viszonyítási alap, [nem tartalmazza], címkéjében nem szerepel a viszonyítási alap.
A mező eredeti állapotát a mezőnév menüjének Szűrő törlése (hely: <mezőnév>) vagy a relációk listájának Szűrő törlése parancsával állíthatjuk helyre.
Módosítsuk pivot táblánk szerkezetét: a név helyett most a számla mező szerepeljen a SOROK területen! A mező szűrő-feltételének megadásakor ismét csak a szövegekhez használható relációk között választhatunk, mert a számlaszámokat ugyan számok alkotják, de az adatbázis-táblázatban szövegként vannak tárolva! A program nem figyelmeztet a helytelenül alkalmazott relációra, hanem „végrehajtja” a szűrést, amelynek eredményeként a mező összes tétele eltűnik a pivot táblából.
Most vizsgáljuk meg a számokat tartalmazó mező feltételen alapuló szűrését! Cseréljük le a számla mezőt a hónapok mezőre. A mező hat tétele a következő: 6, 12, 18, 24, 30, 36. Vizsgáljunk meg néhány „szöveges” relációt.
pivot tábla, szöveges relációk számokra alkalmazva
A „szöveges” relációk nem okoztak meglepetést, de a program eszközeinek többszöri használata során feltűnik néhány furcsaság: a Feliratszűrők menüpont és a Feliratszűrő (<mezőnév>) parancstábla listája eltérő elnevezéseket tartalmaz és a relációk sorrendje sem azonos a két listában.
pivot tábla, feliratszűrők, eltérő elnevezések
A tartományban és a tartományon kívül relációk négy elnevezése közül három félrevezető: két érték között, a következők között van és nincs a következők között. Ezért tisztázzuk a két fogalmat! A tartományban reláció két viszonyítási alap megadását „kéri”, ebben a sorrendben: az intervallum kezdő- és végértékét. Minden olyan tétel részt vesz majd a statisztikai elemzésben, amelynek címke-száma a tartomány eleme, a határértékeket is beleértve. A tartományon kívül reláció alkalmazásakor a deklarált intervallum elemeit kívánjuk kizárni a számításból. Ebben az esetben is a tartomány kezdő- és végértékét kell megadnunk, ebben a sorrendben. Minden olyan tétel részt vesz majd a statisztikai elemzésben, amelynek címke-száma nem az intervallum eleme, a határértékeket is beleértve. Ha a kezdő- és a végérték megadásának sorrendjét felcseréljük, akkor egyetlen tételt sem kapunk (tartományban) vagy egyetlen tétel sem kerül szelektálásra (tartományon kívül).
pivot tábla, tartományban és tartományon kívül relációk
A további „számos” relációk értelmezése nem okozhat gondot, térjünk át inkább a dátum címkéjű tételek feltételen alapuló szűrésére. Cseréljük a SOROK területen a hónapok mezőt a lekötés mezőre. Hogyan befolyásolja az automatikus csoportosítás a csoportosított mező szűrését?
pivot tábla, automatikus csoportosítás és szűrés viszonya
Az első szembeötlő változás: a program a címke-szűrő menüpontot a Dátumszűrők névre cserélte és mind a csoportosító, mind a csoportosított mező menüjében is elhelyezte. A csoportosító mezőhöz beállított feltétel nem csak a saját, de a többi csoportosító mező és a csoportosított mező tételeit is szelektálja, ezért témánk szempontjából nincs rájuk szükségünk. Csoportbontással távolítsuk el őket a pivot táblából!
A Dátumszűrők lista elemei relációk és feltételek. Először tekintsük át a rendelkezésünkre álló relációkat, amelyet az Egyéni szűrő… utasítással megjeleníthető parancstáblán találunk meg. Dátum adattípusú tételek esetén a viszonyítási alap természetesen dátum.
pivot tábla, dátumszűrők
A parancstábla két mezője a reláció és a viszonyítási alap bevitelére szolgál. Az időszakra vonatkozó két reláció (a következők között van, nincs a következők között) természetesen két dátum megadását igényli, első nap utolsó nap sorrendben.
A viszonyítási alap begépelésénél ügyeljünk az előírt formátumra: egy-, két- vagy négy számjegyes évszám, elválasztó karakter, hónapszám vezető nulla nélkül, elválasztó karakter, napszám vezető nulla nélkül. Az elválasztó karakter lehet pont (.), kötőjel (-), perjel (/). Az egy- vagy két-számjegyes évszám értelmezése, a szokásos magyar vezérlőpult beállítások mellett a következő. [1] Harminctól kilencvenkilencig (30-99) a határértékeket is beleértve: 1900-as évek. Például: 38/8/8 értelmezése ezerkilencszázharmincnyolc augusztus nyolc. [2] Nullától huszonkilencig (0-29), a határértékeket is beleértve: 2000-es évek. Például: 8/8/8 értelmezése kétezernyolc augusztus nyolc.
A viszonyítási alapot a panel naptárával, kattintással is bevihetjük. A naptárban a hónapokat a címsorban álló két háromszöggel, az éveket a Ctrl+PageUp és Ctrl+PageDown billentyűparanccsal válthatjuk. Ezután már tanulmányozhatjuk a relációkat a viszonyítási alap ismeretében.
pivot tábla, dátum-relációk
A deklarált feltétel kiértékelésekor a program nem veszi figyelembe a dátumot és időpontot is tartalmazó tétel-címkék időrészét, ha a Dátumszűrő panel Egész napok jelölőnégyzetét kipipáltuk. A következő kép egy ilyen mező szűrését mutatja.
pivot tábla, dátum-relációk
A relációk teljes kínálatát tehát a Dátumszűrők, Egyéni szűrő… utasítással megjeleníthető parancstáblán találjuk, de néhány reláció egyszerűbben is elérhető a Dátumszűrők menüpontban (természetesen, egy kivételével, más néven): egyenlő (Egyenlő…), korábbi, mint (Előtte…), későbbi, mint (Utána…), a következők között van (Két érték között…).
A Dátumszűrők lista további elemei nem szerkeszthető feltételek, amelyek egy dátumegység- vagy egy időszak dátumait határozzák meg. Viszonyítási alapjuk az aktuális dátum.
pivot tábla, beépített feltételek
A Dátumszűrők, Minden dátum adott időszakon belül listában további, nem szerkeszthető, feltételeket találunk: a négy negyedév- és a tizenkét hónap dátumait meghatározó kritériumokat. Ezek a feltételek nem tartalmaznak az évszámra vonatkozó korlátozást, tehát minden év adott negyedévének, vagy adott hónapjainak dátumait választják ki.
Az alkalmazott beépített feltételt közvetlenül, a szűrt állapot törlése nélkül, válthatjuk egy másik beépített feltétellel. Ha a szelektált mezőt eltávolítjuk a pivot táblából, akkor a program menti szűrt állapotot, amit a segédablakban tölcsérrel jelez. A mező ismételt felhasználásakor a szűrt állapotnak megfelelő tételek vesznek csak részt az elemzésben.
Kedves kis Pajtások! Biztos elfáradtatok már, ezért a végösszegek szerinti szűrést már csak egy másik alkalommal mesélem el nektek. A fogmosást ne felejtsétek el lefekvés előtt kis Pajtások!
margitfalvi.arpad@gmail.com