Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013
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.
A szűrés eredménye tudja követni a változásokat?
Ez a kis animGIF azt mutatja, hogy ha az oldal tetején változtatjuk a szűrési feltételt, akkor a szűrés eredménye automatikusan változik.
Semmi makró, csak sima képlet.
Igaz, nem akármilyen képlet, de ha nagyjából megértjük, akkor tudjuk a saját problémánkra is alkalmazni. Az eredeti megoldást itt találtam, miután megkérdezték tőlem, hogy egyáltalán lehetséges-e ilyesmi.
Nézegettem, értelmeztem, és végül alkalmazni tudtam az aktuális problémára. A letölthető mintafájlban van az egyes részek leírása, most csak arra koncentrálok, hogy hogyan lehet alkalmazni.
Egy adott összegnél többet keresők dinamikus szűrése
- A bal oldali, szűrendő tartományt alakítsd táblázattá. Ha nem tudod, hogy mi ez, akkor olvasd el itt. A táblázat neve a példában „lista”.
- Az eredménytábla fejlécébe (Az ábrán jobb oldalon), vedd fel azokat az oszlopokat, amiket a szűrés eredményében látni szeretnél. A forrás és a cél nem kell, hogy egy helyen legyen!
- Definiáld a feltételt tartalmazó cellát. A példában ez az „E2”-es cella. Ide kell írni, hogy mennyi a határ fizetés.
- Oszloponként alkalmazd a képletet.
NÉV oszlop képlete
=HAHIBA(INDEX(lista[NÉV];KICSI(HA(lista[FIZETÉS]>$E$2;SOR(lista[NÉV])-SOR(lista[#Fejlécek]));SOROK(A$5:A5)));"")
MUNKAH oszlop képlete
=HAHIBA(INDEX(lista[MUNKAH];KICSI(HA(lista[FIZETÉS]>$E$2;SOR(lista[MUNKAH])-SOR(lista[#Fejlécek]));SOROK(B$5:B5)));"")
FIZETÉS oszlop képlete
=HAHIBA(INDEX(lista[FIZETÉS];KICSI(HA(lista[FIZETÉS]>$E$2;SOR(lista[FIZETÉS])-SOR(lista[#Fejlécek]));SOROK(C$5:C5)));"")
Minden oszlop képletében a HA függvényben figyeljük a feltételt a FIZETÉS oszlopban, a képlet többi része igazodik ahhoz, hogy melyik oszlopban vagyunk.
A képlet lehúzása
Miután megvan a három képlet a három oszlop első sorára, kijelölhetjük, és elkezdhetjük lehúzni. Addig húzzuk le, amíg a képlet ad eredményt. Ha már üres sorok jönnek ki, akkor az azt jelenti, hogy nincs több, az aktuális szűrésnek megfelelő eredmény sor.
Mi van, ha változtatjuk a feltételt?
Ennek eredményeképpen vagy szűkül a találati lista, vagy bővül. Ha bővül, akkor tovább kell húzni a képletet az előzőek szerint.
Ha nem akarunk ezzel foglalkozni, akkor előre lehúzhatjuk a képletet annyi soron, amennyi találatot már biztosan nem várunk, vagy akár annyi soron, amennyi sora a forrás táblának van.
Mi lesz az új sorokkal?
Ha új sorok kerülnek a forrás táblába, akkor ezekkel nyilván nőni fog annak sorainak a száma. Az új sorok automatikusan megjelennek a szűrés eredményében, ha megfelelnek a feltételnek.
DEMO munkafüzet magyarázattal
És mindez működés közben a Videón
Dinamikus szűrés képlettel
Tisztelt Excel Szakértő!
Nagy örömömre szolgál, hogy ilyen hasznos leírásokat és tanácsokat oszt meg az Excel kezelésével kapcsolatban itt a weboldalon, többek között mint a fenti „Dinamikus szűrés képlettel” leírása. A fenti ismereteket szerettem volna hasznosítani saját munkafüzetemben is, de sajnos elakadtam. Amennyiben módjában áll, kérem szíves segítségét a probléma megoldásában.
Tulajdonképpen ilyen dinamikus képlettel történő szűrést szeretnék létrehozni a saját Excel munkafüzetemben, de nem számértékre szeretnék szűrni, mint ahogy a fentiekben szerepel, hanem egy szótöredékre (szövegre), melyben Joker / helyettesítő karakterek (*;?) is szerepelnének.
Az Önök példáját alapul véve, nem a fizetésre (számérték) szeretném a dinamikus szűrést megadni, hanem a nevekre. Például az 50 000 Ft helyére beírva a macskakörmök közötti szótöredéket (Év)és Joker (*) karakter: „Év*”, akkor listázza ki az összes olyan nevet, amelyben szerepel az, pl. Illés Éva; Menich Éva. Én egyébként egy cikkszámokat és termékneveket tartalmazó Excel tábla adatait szeretném így szűrni / kilistázni. A cikkszámok ugyan egyedi azonosítók, így aki azt tudja, az lekérdezheti a cikkszámhoz tartozó terméknevet is, de aki nem tudja a cikkszámot, csak a termék nevét az ezzel a dinamikus szűréssel tudná szűkíteni a többszáz soros adatbázist. A Joker karakterek használhatósága nagyon fontos lenne, mert rengeteg olyan megnevezésünk van, melyben szerepel például az „aeroszol”, de sok helyen csak „ae.”-ként van rövidítve!
Tisztelettel érdeklődni szeretnék, hogy megoldható-e a fenti probléma ilyen dinamikus képlettel való szűréssel (szótöredék + Joker karakter használatával), és ha igen, akkor milyen módon? Az Autoszűrők bekapcsolását nem szeretném használni, az az eredeti adatbázist szeretném a felhasználók elöl elrejtve tartani.
Előre is köszönöm szíves segítségét!
Üdvözlettel:
Attila
Igen, van megoldás. Joker karakterekre nem, de szótöredékre megoldható, kipróbáltam.
Tehát csak annyi, hogy nem kell beírni a * karaktert, csak a keresett szótöredéket.
A fórumban indíts egy új Topic-ot, tedd fel a kérdést, és feltöltöm neked a módosított verziót.
Imre
Kedves Imre!
Köszönöm szépen a gyors választ és segítséget!
Az új topik-ot elindítottam „Dinamikus szűrés szövegrészletre képlettel” címmel.
Link:
http://excel-bazis.hu/forumok/topic/dinamikus-szures-szovegreszletre-keplettel
Előre is köszönöm és várom szíves visszajelzését!
Üdvözlettel:
Attila
Sziasztok!
Nagyon kezdő vagyok elnézést ha hülyeséget kérdezek, de hogy lehet bővíteni a lekérdezés feltételetét úgy, hogy pl. egyszerre akarom hogy leszűrje azt akinek a fizetése mondjuk 50ezernél kevesebb és közben a matávnál dolgozik?
Segítségeteket előre is köszönöm!
Üdvözlettel,
Péter
Szia!
A HA függvény feltétel részét kell módosítani.
beletenni egy ÉS függvényt is.
Imre
Kedves Imre!
Köszönöm a segítséget! Próbálom de sehogy nem sikerül módosítani, hogy egyáltalán működjön. Így leírom konkrétabban hátha ..:)
A fentebb másik topicban leírt módszerrel megcsináltam a táblázatot szövegre ami szuperül működik viszont egy másik feltételem is lenne ami megint szöveges. (Igazából nem tudom miért nem abba a topicba írtam, bocsánat)
Szóval esetemben egyébként ez egy hibalista lenne ahol a leírásban leírom a szakmát és a hibát (pár szó pl: kőműves habarcs leverése lakásban). Azután szakmánként szűrném valamint az alapján, hogy a mellette lévő cellában jelezve (pl. x), hogy a javítás elkészült-e.
Logikailag kéne gondolom megvizsgálni, hogy a javítás oszlopban szerepel e ott az „X” vagy akármi (akár egy 0 vagy 1).
Köszönöm a fáradozást!
Üdv,
Péter
Két feltételes verzióban így, dinamikus képlettel most hirtelen nem tudom.
lehet, hogy meg lehet csinálni, sőt, biztos, de annyira összetett lenne a képlet..
A feladatból úgy látiom, hoyg ezt sima autofilterrel vagy speciális filterrel is meg lehet csinálni.
Meg persze ott az MS QUERY, Power QUERY..
Imre
Nagyon örülök, hogy erre rátaláltam, nagyon hasznos volt.
A leírás segítségével szépen ki tudtam szűrni egy adathalmazból a különböző szövegrészletet tartalmazó elemeket.
Arra nincs valami elegáns megoldás, hogy miként tudom kiszűrni a „maradékot”? Pl. Kiszűrtem ami tartalmazza az „AN”, a „CSN”, a „SZiG”, „SZH” szövegrészletet, hogy tudnám kiszűrni a maradékot, ami egyik előbbit sem tartalmazza?