Telefonszámunk: 1-472-0679

Lista szűrése másik lista szerint

2013-09-19 - horvimi - Megtekintések száma: 4,943 - Kategória: Adatkezelés - Szűrés, Képletek, Tömbképletek
Hivatkozott Excel verzio:

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.

alap-problema

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.

iranyitott-szuro-menu

iranyitott-szuro

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]

Ha változik a bal vagy a jobb oldali lista, vagy új elemekkel bővül, akkor természetesen újra kell futtatni a szűrést.

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.

szurolista

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 fenti képlet tömbképlet, ezért a bevitel után CTRL+Shift+ENTER-el kell lezárni.

Eredményül a fenti képen a szerkesztőlécen látható módon, kapcsos zárójelek kerülnek a képlet elejére és végére, ami tömb határoló az excelben.

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

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