Telefonszámunk: 1-472-0679

Dinamikus szűrés képlettel

2014-11-22 - horvimi - Kategória: Adatkezelés - Szűrés, Képletek, Tömbképletek
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.

szures-keplettel

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

  1. 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”.
  2. 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!
  3. 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.
  4. Oszloponként alkalmazd a képletet.

Elokeszuletek

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)));"")
FIGYELEM!

A képlet TÖMBKÉPLET, ezért beíráskor és módosításkor is CTRL-SHIFT+ENTER-el kell lezárni!

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.

FONTOS!

A képletek végén, a SOROK függvényben az 5-ös mondja meg azt, hogy a forrás táblában melyik sorban van az első adatrekord. (A fejléc utáni sor) A fenti pédában tehát ez az 5. sor. Ezt módosítani kell, ha e Te esetedben máshol kezdődne, pl. a másodikban.

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

Dinamikus-szures-keplettel

És mindez működés közben a Videón

  • Dinamikus szűrés képlettel

8 hozzászólás
  1. 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

  2. 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

  3. 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

  4. Szia!

    A HA függvény feltétel részét kell módosítani.
    beletenni egy ÉS függvényt is.

    Imre

  5. 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

  6. 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

  7. 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?

Vélemény, hozzászólás?