1111 Budapest, Bertalan Lajos utca 7-9., BME L épület

Excel a gyakorlatban IX. – Keresés tartományban

Excel keresés tartományban

Az előző bejegyzéseink során áttekintettük az INDEX, illetve a HOL.VAN és FKERES függvények használatát. Az FKERES képes volt arra, hogy egy első oszlopban keresett érték sorának egy másik elemét visszaadja. De mi a helyzet, ha a keresett értékünk nem az első oszlopban foglal helyet, hanem mondjuk a harmadikban, és nem a tőle jobbra lévő értéket szeretnénk adott sorból eredményül kapni, hanem egy tőle balra lévő értéket? Ilyenkor az FKERES függvény nem használható a táblázat átrendezése nélkül. Ezért mai írásunk célja, hogy megvalósuljon egy keresés tartományban az INDEX és HOL.VAN függvények kombinációjával.

Keresés tartományban – 1. példa

Excel kiinduló táblázat

A kiinduló táblánk egy törzstábla, mely tartalmazza az egyes munkavállalók nevét, lakcímét, személyigazolvány számát és telefonszámát. Első feladatunk a következő: keressük meg függvénnyel és írjuk ki a nevét annak a munkavállalónak, akinek személyigazolvány száma 712613PA. Ez majdnem olyan, mint egy FKERES függvény annyi különbséggel, hogy itt a visszavárt érték a keresési oszloptól balra található. Mivel minden függvényt már tanultunk korábban, így a megoldás az alábbi:

=INDEX(B3:G7;HOL.VAN(C9;D3:D7;0);1)

  • A fenti függvény közepén található HOL.VAN függvény megkeresi a D3:D7 tartományban (SZIG szám oszlop), hogy hányadik helyen szerepel pontosan a C9-es cella (beírt, keresett SZIG szám) értéke. Ennek eredménye egy szám, jelen példánkban ez 3.
  • AZ INDEX függvény egy megadott sor- és oszlopszám alapján kijelölt értéket ad vissza egy tartományból.
    • A keresési tartományunk a B3:G7 (maga a táblázat),
    • a HOL.VAN függvény eredménye adja meg, hogy melyik sort kell néznünk, ez most 3,
    • az utolsó 1-es paraméter pedig az oszlop indexet adja.
    • Így a függvény eredménye a táblázat 3. sorában és 1. oszlopában lévő érték, „Eleonóra Seres”.

Excel INDEX és HOL.VAN

Keresés tartományban – 2. példa

A második feladat a következő: válasszunk ki egy nevet és egy tábla fejlécet, a kettő ismerete pedig adja eredményül a táblázat megfelelő cellájának értékét. (Például név: Vida Lilien, fejléc: Irányítószám, ebből következik az érték: 1097.)

Kiválasztás listából

Hogy megkönnyítsük a feladatunkat, ne mi írjuk be a munkavállaló és a fejléc nevét, hanem válasszuk ki azokat egy listából. Így biztos nem írunk el semmit.

  1. Az előző bejegyzésben beszéltünk a tartományok elnevezéséről, most is használjuk ezt a módszert:
    1. jelöljük ki a neveket (fejléc nélkül, B3:B7)
    2. írjuk be bal felülre, hogy „Nevek”, majd nyomjunk egy entert.
    3. Ismételjük meg ezt a fejlécekre: jelöljük ki a B2:G2 cellákat és adjunk ennek a tartománynak egy „Fejlécek” nevet.
  2. Most álljunk rá arra a cellára, ahol a nevet szeretnénk megadni (ez nálam a C9-es cella).
    1. Az „Adatok” fülön és az „Adateszközök” szekción belül lesz egy „Érvényesítés” gomb, kattintsunk rá (a lenti képen sárga karika).
    2. A felugró ablakban a „Megengedve:” címszó alatti listából válasszuk ki a „Lista” opciót,
    3. a „Forrás:” alá pedig írjuk be, hogy „=Nevek”. (Természetesen itt használhatunk cellahivatkozásokat is, pl. „=B3:B7”)
Excel érvényesítés
Excel érvényesítés listából kiválasztásra

Ezzel nem csináltunk mást, minthogy azt mondtuk az Excelnek: a C9-es cellában csakis a Nevek lista értékeit engedjük meg. Most ismételjük meg a fenti két lépést a Fejlécekre is, annyi különbséggel, hogy a „Forrás:” alá a „=Fejlécek” szót (tartomány név) kell beírni.

Függvény összeállítása

Ha ezzel megvolnánk, akkor már csak a függvényt kell megírnunk. Hasonló lesz az előzőhöz, csak itt az INDEX függvényt sorát és oszlopát is egy-egy HOL.VAN függvény eredménye fogja megadni.

=INDEX(B3:G7;HOL.VAN(C9;Nevek;0);HOL.VAN(C10;Fejlécek;0))

  • A B3:G7-es tartományból szeretnénk visszakapni egy értéket.
  • Az első HOL.VAN megkeresi a „Nevek” oszlopban (B3:B7) a C9-es cellában listából kiválasztott nevet, és visszaadja számként annak helyét. (Pl. Lilien Vida esetén az eredmény 4.)
  • A második HOL.VAN megkeresi a „Fejlécek” sorban (B2:G2) a C10-es cellában listából kiválasztott fejléc címét, és visszaadja annak helyét a sorban. (Pl. Életkor esetén az eredmény 2.)
  • A sor (4) és az oszlop (2) index ismeretében az INDEX függvény megadja a megfelelő cella értékét (ebben a példában a 4. sor és 2. oszlop a C6-os cella, tehát Lilien Vida életkora 27 év).

Excel INDEX és HOL.VAN

Következő bejegyzésünkben egy újonnan bevezetett függvényről lesz szó, amely részben vagy egészben de ki tudja váltani a fenti függvény kombinációt.

Összefoglalás
Excel keresés tartományban
Cikk címe
Excel keresés tartományban
Leírás
Excel sorozatunk mostani részében INDEX és HOL.VAN függvények egyesített erejével valósítunk meg keresést egy tartományban.
Szerző
Kiadó
Innolog Solutions
Kiadó logó
Tetszett az oldal? Oszd meg másokkal is!
English