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