Adott érték n-dik előfordulásának keresése egy oszlopban

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

Találkoztam néhányszor azzal az igénnyel, hogy egy tartományban egy adott értéknek ne csak az első, hanem a második, harmadik, n-dik előfordulást is meg lehessen keresni.

Az Excelnek erre nincs külön fügvénye, és a VLOOKUP (FKERES), illetve a MATCH (HOL.VAN) függvények csak az első előfordulást tudják megkeresni.

Vannak az Interneten segédoszlopot használó, megkerülő megoldások, de egyszer csak ráakadtam egy régebbi bejegyzésre, aminek a tanulmányozása után arra jutottam, hogy ez lesz a helyes megoldás.

Az eredeti cikk egy ősrégi Microsoft tudásbázis bejegyzés, Excel 4.0 és Excel 97 verziókra vonatkoztatva.

Nézzük a megoldást

Az alábbi ábrán látjuk a mintaként használt táblázatot. Tehát a feladat az lenne, hogy az „alma” szó második vagy harmadik előfordulását is meg tudjuk találni, és esetleg kivenni mellőle a hozzá tartozó értéket ugyanabból a sorból.

Az ötlet azon alapszik, hogy ha tudnánk képezni egy listát az „alma” szót tartalmazó sorokról (1;4;6), akkor ezek közül a SMALL (KICSI) függvénnyel kiválaszthatnánk az n-dik legkisebbet, ami megadná az n-dik előfordulás munkalap-sorszámát. Jelen példában, ha az {1;4;6} tömb 2-dik legkisebb elemét keresem, akkor egy cellába beírhatom a következő képletet:

=SMALL({1;4;6};2)

Eredményként a 4-et fogom kapni.

Hogy lehet az „almás” sorok tömbjét előállítani?

Természetesen tömbképlettel. Végignézzük az „A1:A6” tartományt, és ha bármelyik eleme egyenlő az  „alma” szóval, akkor kivesszük az aktuális sor számát, különben egy üres sztringet.

Jelöljünk ki 6 egymás alatti cellát (mert 6 elemből áll a példa táblázat), írjuk be a következő képletet, és a végén nyomjuk meg a Ctrl+Shift+ENTER kombinációt!

=IF((A1:A6)<>"alma";"";ROW(A1:A6))

A cellákban a képlet kapcsos zárójelek közé került: {=IF((A1:A6)<>”alma”;””;ROW(A1:A6))} , és a kijelölt cellákban ez lett az eredmény:

Tehát megkaptuk a 1;4;6 listát, igaz, hogy közben vannak üres cellák is, de az nem baj. Már csak az van hátra, hogy ebből a listából kiválasszuk a SMALL (KICSI) függvénnyel mondjuk a második legkisebbet, azaz az „alma” szó második előfordulásának  munkalap-sorszámát (4)

=SMALL(IF((A1:A6)<>"alma";"";ROW(A1:A6));2)

A végén látható, hogy a második legkisebbet keressük. Ne felejtsük el ezt is a Ctrl+Shift+Enter-el lezárni.

Amennyiben a táblázat a munkalap első sorában kezdődött, akkor a megtalált munkalap sorszám egyenlő lesz az adott tartományban elfoglalt sorszámmal.

És a mellette levő érték?

Ha valójában az n-dik előfordulás melletti értéket keressük, akkor a sorszám ismeretében az INDEX függvénnyel célt érünk, azaz az egészet beágyazhatjuk egy INDEX függvénybe, ami a „B” oszlopból kiveszi a megkapott sorszámú elemet. Ezt a képletet is természetesen a tömbképleteket megillető Ctrl+Shift+Enter-el kell lezárni.

=INDEX(B1:B6;SMALL(IF((A1:A7)<>"alma";"";ROW(A1:A7));2))

És mi van akkor, ha a táblázat nem az első sorban kezdődik?

Akkor egy kicsit matekozni kell a Sorokkal, de azért megoldható. Ha kíváncsi vagy rá, nézd meg a Videó anyagot is!

Ertek-n-dik-elofordulasa