1111 Budapest, Bertalan Lajos utca 7-9., BME L épület
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
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”.
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.
- Az előző bejegyzésben beszéltünk a tartományok elnevezéséről, most is használjuk ezt a módszert:
- jelöljük ki a neveket (fejléc nélkül, B3:B7)
- írjuk be bal felülre, hogy „Nevek”, majd nyomjunk egy entert.
- 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.
- Most álljunk rá arra a cellára, ahol a nevet szeretnénk megadni (ez nálam a C9-es cella).
- 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).
- A felugró ablakban a „Megengedve:” címszó alatti listából válasszuk ki a „Lista” opciót,
- a „Forrás:” alá pedig írjuk be, hogy „=Nevek”. (Természetesen itt használhatunk cellahivatkozásokat is, pl. „=B3:B7”)
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).
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.
