Talán sokan tudjátok, hogy ha egy PIVOT tábla (Kimutatás) forrásaként szolgáló tartományban változás történik, akkor az nem jelenik meg automatikusan a kimutatásban. A változások követéséhez frissíteni kell a PIVOT-ot. Ennek az a magyarázata, hogy a kimutatás nem az eredeti adatforrás alapján készül, hanem annak másolata alapján. Ez […]
Tovább... →Adatkezelés - Szűrés
Elég sok anyag született már a szűrésekről itt a Bázison. Az autoszűrő és az Irányított (haladó) szűrő közös tulajdonsága, hogy nem követi a változásokat, hanem mint egy egyszerű SELECT lekérdezés, az aktuális állapotról ad egy eredményt. Ebből következően, ha változik az adatforrás, akkor újra kell futtatni a szűrést. […]
Tovább... →A haladó szűrő eszközt érdemtelenül kevesen ismerik. Többet érdemelne. Emiatt egy sorozatot indítok a lehetőségeiről. Volt már szó korábban róla, de úgy döntöttem, még egyszer nekifutok. A haladó szűrőt speciális vagy irányított szűrőnek is szokták nevezni. Az autoszűrővel egy lépésben nem megoldható, összetettebb szűrésekhez, illetve a gyakran ismétlődő, akár egyszerűbb szűrésekhez használjuk.
Lényege, hogy a szűrendő táblázaton kívül egy külön tartományban fogalmazzuk meg a szűrési feltételeket, majd a haladó szűrő párbeszédében megadjuk a feltételtartomány helyét, pár dolgot még beállítunk, és kész!
A haladó szűrő előnyei
- Bonyolultabb szűrésekre is alkalmas egy lépésben
- Az eredmény akár azonnal új helyre másolható, akár másik lapra
- A szűrési feltételeket tároljuk, nem kell folyton megcsinálni, kitalálni
- Az eredményből kiszűrhetjük a szükségtelen oszlopokat (Projekció)
- Szűrés közben az oszlopok sorrendjét is módosíthatjuk
A cikksorozat tagjai
Mi az, hogy számított feltétel?
Amikor a feltételben nem állandó (konstans) értékhez viszonyítunk, hanem egy számítás eredményéhez. Alapvetően kétféle esetről beszélhetünk.
Egy oszlop minden elemének hasonlítása ugyanahhoz a számításhoz
- Hasonlítás egy konkrét számítás eredményéhez
- Hasonlítás egy másik cellában lévő értékhez
Ilyenkor a feltételként egy képletet adunk meg. De a képlet megadási módja speciális.
="relációsjel" & képlet vagy cellacím
Konkrét példa: Az oszlop átlagánál nagyobb
=">"&AVERAGE(oszlop) =">"&ÁTLAG(oszlop)
Konkrét példa: A H1 cellánál nagyobb
=">"&H1
Hogy működik?
A relációs jel után fűzi a számítás eredményét, és a kettőt együtt tekinti feltételnek.
Soronkénti feltételvizsgálat
Ebben az esetben egy minden sorban kiértékelendő képletet adunk meg. Például arra vagyunk kíváncsiak, hogy az egyik oszlopban lévő érték, hogy viszonyul egy ugyanabban a sorban, de másik oszlopban lévőhöz.
Két nagyon fontos dolgot kell ilyenkor tenni:
- A feltételtartomány fejlécének üresnek kell maradni, vagy ha adunk nevet, az nem egyezhet meg a szűrendő táblázat egyik mezőnevével sem.
- A szűréshez használt képletet a táblázat első adatsorára kell megadni
Konkrét példa
Tegyük fel, hogy a fenti táblázatban keressük azokat a sorokat, ahol a számlaérték nem egyezik meg a fizetett összeggel, azaz valami hiba van. Ezt másképpen úgy fogalmazhatjuk meg, hogy keressük azokat a sorokat, ahol a C oszlopban lévő érték nem egyezik meg a D oszlopban lévővel.
A feltételtartományt a fentiek szerint kell megadni, tehát mindkét megoldás megfelelő.
Hogy működik?
A haladó szűrő futtatásakor a megadott vizsgálatot minden soron elvégzi, és azokat a sorokat, ahol a képlet FALSE (HAMIS) eredményt ad, elrejti (kiszűri)
Letölthető munkafüzet
És persze a videó
Számított feltétel a kritérium tartományban
A haladó szűrő eszközt érdemtelenül kevesen ismerik. Többet érdemelne. Emiatt egy sorozatot indítok a lehetőségeiről. Volt már szó korábban róla, de úgy döntöttem, még egyszer nekifutok. A haladó szűrőt speciális vagy irányított szűrőnek is szokták nevezni. Az autoszűrővel egy lépésben nem megoldható, összetettebb szűrésekhez, illetve a gyakran ismétlődő, akár egyszerűbb szűrésekhez használjuk.
Lényege, hogy a szűrendő táblázaton kívül egy külön tartományban fogalmazzuk meg a szűrési feltételeket, majd a haladó szűrő párbeszédében megadjuk a feltételtartomány helyét, pár dolgot még beállítunk, és kész!
A haladó szűrő előnyei
- Bonyolultabb szűrésekre is alkalmas egy lépésben
- Az eredmény akár azonnal új helyre másolható, akár másik lapra
- A szűrési feltételeket tároljuk, nem kell folyton megcsinálni, kitalálni
- Az eredményből kiszűrhetjük a szükségtelen oszlopokat (Projekció)
- Szűrés közben az oszlopok sorrendjét is módosíthatjuk
A cikksorozat tagjai
Névtartományok használata
A szűrésnél a Listatartományt (List range), a szűrőtartományt (Criteria range), sőt, akár a hova másolja (Copy to) tartományt is megadhatjuk névtartománnyal. Ezzel egyszerűbbé és rugalmasabbá tehetjük a szűrő használatát.
A névtartományok létrehozásával és kezelésével kapcsolatban már volt egy korábbi anyag, amit itt olvashatsz el:
https://excel-bazis.hu/tutorial/dinamikus-nevtartomany-hasznalata
Táblázatok használata
Abban az esetben, amikor az adatforrást (listatartomány) táblázattá alakítottuk, tisztában kell lennünk azzal, hogy a táblázat egyes részeire hogyan hivatkozhatunk. Erről bővebben pedig itt lehet olvasni:
https://excel-bazis.hu/tutorial/strukturalt-hivatkozas-adattablak-hasznalata-eseten
Ebből számunkra az lesz a legfontosabb, hogy a haladó szűrésnél a listatartománynak tartalmaznia kell a fejléc adatokat is. Ha viszont csak a nevét használjuk, akkor az nem tartalmazza a fejlécet. A teljes táblára fejléccel együtt, szabályos, un. struktúrált hivatkozással így kell hivatkozni:
Magyar Excel: Tabla_neve[#Mind]
Angol Excel: Tabla_neve[#All]
Letölthető munkafüzet
Mindez működés közben
Névtartományok haladó szűrésnél
A haladó szűrő eszközt érdemtelenül kevesen ismerik. Többet érdemelne. Emiatt egy sorozatot indítok a lehetőségeiről. Volt már szó korábban róla, de úgy döntöttem, még egyszer nekifutok. A haladó szűrőt speciális vagy irányított szűrőnek is szokták nevezni. Az autoszűrővel egy lépésben nem megoldható, összetettebb szűrésekhez, illetve a gyakran ismétlődő, akár egyszerűbb szűrésekhez használjuk.
Lényege, hogy a szűrendő táblázaton kívül egy külön tartományban fogalmazzuk meg a szűrési feltételeket, majd a haladó szűrő párbeszédében megadjuk a feltételtartomány helyét, pár dolgot még beállítunk, és kész!
A haladó szűrő előnyei
- Bonyolultabb szűrésekre is alkalmas egy lépésben
- Az eredmény akár azonnal új helyre másolható, akár másik lapra
- A szűrési feltételeket tároljuk, nem kell folyton megcsinálni, kitalálni
- Az eredményből kiszűrhetjük a szükségtelen oszlopokat (Projekció)
- Szűrés közben az oszlopok sorrendjét is módosíthatjuk
A cikksorozat tagjai
Ismétlődések eltávolítása (Remove Duplicates)
Gyakori feladat, hogy egy listából, amiben ismétlődő elemek is vannak, képezni kell egy olyan listát, amiben minden elem csak egyszer szerepel. Ezt a listát hívjuk egyedi (Unique) listának.
Erre a feladatra több megoldás is létezik.
- Haladó szűrés speciális esete
- PVOT tábla speciális esete
- Excel 2007-től külön gomb van rá
Melyiket használjam?
Ha Excel 2007-nél korábbi verzió esetén az első két megoldás jöhet számításba. Ha Excel 2007 után vagyunk, akkor viszont attól függ, hogy a táblázat egy vagy több oszlopból áll, illetve attól, hogy az eredeti listát is meg szeretnénk-e őrizni. Az is szempont lehet, hogy a folyamatot esetleg akarjuk-e automatizálni, azaz makrózni.
Megoldás a haladó szűrővel
- Kijelöljük az eredeti listát
- Adatok->Speciális (Advanced) szűrő
- Nem adunk feltételt (ha minden elem kell)
- Beállítjuk, hogy helyben vagy más helyre szűrjön
- Csak az egyedi rekordok (Unique records only)
Minden egyéb pontosan ugyanúgy működik, mint ahogy a többi leírásban olvashattad, azaz szűrhető helyben is, az eredmény tehető azonnal másik munkalapra, használhatók nevek a forrásokban, és akár még feltétel is adható.
Letölthető munkafüzet
Mindez működés közben
A videóban természetesen szó lesz a másik két megoldási lehetőségről is
Egyedi lista készítése
A haladó szűrő eszközt érdemtelenül kevesen ismerik. Többet érdemelne. Emiatt egy sorozatot indítok a lehetőségeiről. Volt már szó korábban róla, de úgy döntöttem, még egyszer nekifutok. A haladó szűrőt speciális vagy irányított szűrőnek is szokták nevezni. Az autoszűrővel egy lépésben nem megoldható, összetettebb szűrésekhez, illetve a gyakran ismétlődő, akár egyszerűbb szűrésekhez használjuk.
Lényege, hogy a szűrendő táblázaton kívül egy külön tartományban fogalmazzuk meg a szűrési feltételeket, majd a haladó szűrő párbeszédében megadjuk a feltételtartomány helyét, pár dolgot még beállítunk, és kész!
A haladó szűrő előnyei
- Bonyolultabb szűrésekre is alkalmas egy lépésben
- Az eredmény akár azonnal új helyre másolható, akár másik lapra
- A szűrési feltételeket tároljuk, nem kell folyton megcsinálni, kitalálni
- Az eredményből kiszűrhetjük a szükségtelen oszlopokat (Projekció)
- Szűrés közben az oszlopok sorrendjét is módosíthatjuk
A cikksorozat tagjai
Az eredmény másik munkalapra egyből?
Talán van, aki tapasztalta, hogy a haladó szűrő nem engedi a szűrés eredményét egyből másik munkalapra tenni. Emiatt azt előbb az aktuális munkalapra, majd onnan másolva és beillesztve lehet áttenni más lapra.
Íme a hibaüzenet
Hosszú évekig bosszantott, hogy miért van ez így, míg egyszer csak rátaláltam a megoldásra. Ami mindig is ott volt, csak nem gondolkodtam, hanem csak idegeskedtem. Szóval a megoldás benne van az üzenetben. Ha csak az aktív munkalapra kerülhetnek az eredmények, akkor mi lenne, ha az egészet egy üres munkalapról indítom, és az adatforrást választom ki másik lapról (mert azt lehet)?
A fenti beállításban a Listatartomány másik lapról lett kijelölve, a Szűrőtartomány és a Hová másolja pedig az aktuálisról.
És működik!
Letölthető munkafüzet
Lássuk a videót!
Haladó szűrés eredménye másik munkalapra
Az haladó szűrő eszközt érdemtelenül kevesen ismerik. Többet érdemelne. Emiatt egy sorozatot indítok a lehetőségeiről. Volt már szó korábban róla, de úgy döntöttem, még egyszer nekifutok. A haladó szűrőt speciális vagy irányított szűrőnek is szokták nevezni. Az autoszűrővel egy lépésben nem megoldható, összetettebb szűrésekhez, illetve a gyakran ismétlődő, akár egyszerűbb szűrésekhez használjuk.
Lényege, hogy a szűrendő táblázaton kívül egy külön tartományban fogalmazzuk meg a szűrési feltételeket, majd a haladó szűrő párbeszédében megadjuk a feltételtartomány helyét, pár dolgot még beállítunk, és kész!
A haladó szűrő előnyei
- Bonyolultabb szűrésekre is alkalmas egy lépésben
- Az eredmény akár azonnal új helyre másolható, akár másik lapra
- A szűrési feltételeket tároljuk, nem kell folyton megcsinálni, kitalálni
- Az eredményből kiszűrhetjük a szükségtelen oszlopokat (Projekció)
- Szűrés közben az oszlopok sorrendjét is módosíthatjuk
A cikksorozat tagjai
Csak bizonyos oszlopok lekérése az eredménybe (projekció)
Ez a művelet elvileg adatbázis-kezeléshez kapcsolódik, és szűréssel nem szoktak ilyesmit csinálni, de a haladó szűrő tudja ezt is. Tehát adott az alap táblázatunk:
Ha semmi mást nem szeretnénk, csak kivenni néhány tetszőleges oszlopot, akkor ezt kell tenni:
- Egy üres területre (praktikusan az alaptábla mellé (de alá is lehet), másoljuk ki a kívánt oszlopok neveit(mezőnevek) abban a sorrendben, ahogy szeretnénk őket
- Haladó szűrő indul
- Más helyre másol, Feltétel nincs, és célként jelöljük ki az előkészített mezőneveket!
- Eredményként csak a kiválasztott három oszlopot kapjuk.
Szűrés és projekció egyben
Itt csak az lesz a különbség, hogy megadjuk a kritérium tartományt is, azaz nem marad üresen.
Letölthető munkafüzet
És persze a kapcsolódó videó
Projekció haladó szűrővel
Adatlekérdezések és feldolgozás során leggyakrabban valamilyen feltételnek megfelelő adatrekordokkal végzünk műveletet, tehát az eredeti adattáblát feltétel szerint szűkítjük. A feltételt meg kell valahogy adni vagy fogalmazni.
Ennek a témának egy összefoglaló anyagát már bemutattam, emlékeztetőként, vagy gondolatébresztőként érdemes megnézni.
https://excel-bazis.hu/tutorial/halado-szuresi-technikak-szurooszlop-vagy-iranyitott-szuro
Most a feltételtartomány fogalmát és gyakorlati példáit nézzük meg. Ez az alapelv aztán visszaköszön az MS QUERY vagy az ACCESS lekérdező varázslójában.
Tehát van egy adattáblánk
Ehhez a táblához szeretnénk szűrőfeltételeket létrehozni. A legegyszerűbb szűrés az Autoszűrő lenne, talán mindenki ismeri,most nem térek ki rá. Ott oszloponként állíthatunk szűrési feltételt. Minden további feltétel tovább szűkíti a találati listát, mert az egymás mellett lévő feltételek ÉS kapcsolatba kerülnek, emiatt az eredmény rekordok azok lesznek, amelyek minden beállított feltételnek megfelelek.
Vannak azonban olyan esetek, amelyekhez nem tudunk beállítani az Autoszűrővel egy lépésben feltételt.
Autoszűrő fricska
Szűrjük a Budapesti férfiakat és a Miskolci nőket egy lépésben!
A Település oszlopban még csak beállítom, hogy a Budapestieket meg a Miskolciakat is mutassa, de a Nem oszlopban akkor mit állítsak, hogy a fenti feltétel teljesüljön?
Na, ilyen esetekben szokták két lépésben megoldani a feladatot. Szűrik először az egyik adagot, kimásolják egy új lapra, majd a másikra is szűrnek, és az előző eredmény után másolják.
Nem valami jó, viszont rossz. Legalább is nem optimális.
A megoldás: Kritérium tartomány(Criteria Range) és irányított szűrő (Advanced Filter)
A kritérium tartomány az alap adattáblától külön lévő kis táblázat, amiben speciális módon meg tudjuk fogalmazni a feltételeket, és ezt a tartományt adjuk paraméterként az Irányított szűrőnek és az összes adatbázis függvénynek.
Az alap feltétel tartományok a táblázat mezőneveiből és a feltételekből állnak. A működése egyszerű. A feltételeket soronként értékeli ki az Excel. Ami egymás mellett van, az ÉS kapcsolatot jelöl, ami egymás alatt van, az pedig VAGY kapcsolatot.
Alap szűrőtartományok. Ezeket Autoszűrővel is lehet
Összetettebb feltételek, amiket autoszűrővel nem lehet
Helyettesítő (Joker) karakterek használata
Ha ki szeretnénk szűrni azokat az elemeket, amik tartalmaznak egy adott karakter sorozatot, akkor használhatjuk a jól ismert joker karaktereket. (* és ?)
Ha esetleg a szűrendő szövegrészben van csillag vagy kérdőjel, akkor azokat úgy szűrhetjük, hogy elé teszünk egy hullámjelet (tilde)
Használat az irányított (speciális) szűrőhöz
- A listatartomány a teljes táblázat fejléccel együtt
- A szűrőtartomány pedig a külön részben megfogalmazott feltételtartomány lesz
- Az eredményt lehet az aktuális munkalap más helyére is másolni azonnal.
Használat az adatbázis függvényekhez
Például egy cellába szeretném megtudni a Budapesti férfiak és a Miskolci nők összlétszámát, akkor erre az AB.DARAB2
( DCOUNTA ) függvényt használhatjuk. A NÉV oszlopban számoljuk meg a külön kritériumtartományban megfogalmazottaknak megfelelő sorok számát.
Adatbázis: A teljes táblázat fejléccel
Mező: Az az oszlop, ahol a megszámlálást kell végezni. Megadható sorszámmal, hogy hányadik oszlop vagy a nevével, vagy a fejléc szöveget tartalmazó cella címével.
Kritérium: A feltételtartomány címe
Letölthető gyakorló munkafüzet
iranyitott-szuro-adatbazis-fuggveny-felteteltartomany
Mindez működés közben
Feltétel tartományok az Excelben
A feladat az, hogy egy szöveges oszlopot szűrjünk az alapján, hogy csak azok a sorok maradjanak (vagy ne maradjanak), amelyek tartalmazzák egy másik tartományba előre felrögzített szavak bármelyikét.
Tehát a bal oldali listában, ami valószínűleg egy hosszabb lista, jelölni, vagy szűrni szeretnénk azokat, amikben előfordul a jobb oldali lista bármely eleme.
Alakítsuk táblázattá!
Mielőtt bármelyik megoldásnak nekikezdenénk, mindkét tartományt alakítsuk táblázattá. Ennek több előnye is van.
- Az új tételeket automatikusan beleveszi a hivatkozásba
- Egyszerűbb megadni a lista és kritérium-tartományokat
- Egyszerűbben hivatkozhatjuk őket akkor is, ha különböző lapokon vannak
Megoldás irányított szűrővel
Felállítunk egy kritériumtartományt, ahol a szűrendő szavak egymás alatt vannak, joker karakterekkel (*) kiegészítve az elején és a végén. Mivel egymás alatt vannak, VAGY kapcsolatban leszek egymással, tehát bármelyik előfordul a bal oldali oszlopban, azt szűrni fogja.
Ha csak a táblázatok nevét használjuk, akkor a táblázatok fejléce nem fog szerepelni a hivatkozásban. Emiatt mögé kell írni az [#All] kiegészítést. Tehát:
List Range: adatok[#all]
Criteria range: szurolista[#all]
Megoldás képlettel
Ebben az estben a táblázattá alakított szűrőlista elemeihez nem kell joker karaktereket adni, csak egymás alá felsorolni a szűrendő szavakat. Az adatlistát pedig kiegészítjük egy új oszloppal, ahová képlettel megállapítjuk, hogy a kizárandó elemek előfordulnak-e az adott sorban lévő szövegben.
A C4-be írt képlet megvizsgálja, hogy a szűrőlista bármely eleme benne van-e az aktuális sor „Megnevezés” oszlopában.
=SUM(IFERROR(SEARCH(szurolista;[@Megnevezés]);0)) =SZUM(HAHIBA(SZÖVEG.KERES(szurolista;[@Megnevezés]);0))
A képletet érvényesítsük a teljes oszlopra.
Ahol nullát kapunk, az azt jelenti, hogy egyik jobb oldalon felsorolt szó sem fordul elő a bal oldali lista aktuális sorában. Ennek megfelelően lehet a szűrés oszlopban szűrni a nullákat vagy a nem nullákat.
Letölthető minta munkafüzet: lista-szurese-lista-szerint
A videóban látható mindez működés közben, részletesebb magyarázatokkal, főleg ami a képlet működését illeti.
Lista szűrése másik lista szerint
A táblázatok ugyebár sorokból és oszlopokból állnak. Gyakori művelet, amikor a táblázat sorait egy vagy akár több oszlop alapján sorba rendezzük. Például név szerint vagy érték szerint növekvő vagy csökkenő sorba.
Az erre szolgáló gombokat is mindenki ismeri.
Igen ám, de mi van akkor, ha a táblázat oszlopait szeretnénk rendezni valamelyik sorban lévő értékek alapján? A következő példában a nevekhez tartozó oszlopokat szeretnénk névsor szerint rendezni úgy, hogy a napok a helyükön maradjanak.
Rendezés előtt
rendezés után
Megoldás
A rendezés előtt be kell állítani, hogy nem oszlop szerint (fentről lefelé) hanem sorok szerint (balról jobbra) szeretnénk rendezni. Ezt a rendezés párbeszédablakban lévő „Options…” (Beállítások) gomb mögött tudjuk megtenni.
- Data -> Sort -> Options -> Left to Right
- Adatok ->Rendezés ->Beállítások ->Balról jobbra
Ezután jelöljük ki a táblázatot az első oszlop nélkül, hiszen a hét napjait ott szeretnénk hagyni.
Most jön a rendezés végrehajtása. (Sort / Rendezés gomb)
A rendezés beállítása sor alapján
Mindez működés közben
Oszlopok rendezése sorok szerint
