Telefonszámunk: 1-472-0679

Dinamikus szűrés képlettel

2014-11-22 - horvimi - Megtekintések száma: 9,027 - 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

7 Comments
  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

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