1111 Budapest, Bertalan Lajos utca 7-9., BME L épület
Az előző bejegyzéseink során különböző függvények segítségével valósítottunk meg kereséseket táblázatainkban. Bemutattuk az FKERES függvényt, majd az INDEX és HOL.VAN ismertetését követően kombinálva használtuk azokat. A keresés témakörében írt utolsó, 10. bejegyzésünkben az XKERES függvény kerül ismertetésre, mely az Office 365-ben tavaly bevezetett új eszköztár egy igencsak hasznos eleme.
XKERES függvény
A címben említett függvény méltó utódja lesz a mindenki által jól ismert FKERES-nek, hiszen pont olyan tulajdonságokkal rendelkezik, melyek mindig is hiányoztak a régi függvényből. Nézzük is végig a működését, de először a felépítése:
=XKERES(keresési_érték;keresési_tömb;visszaadandó_tömb;[ha_nincs_találat];[egyeztetési_mód];[keresési_mód])
A következő pontokban bemutatjuk a függvény képességeit, sokrétűségét példákkal szemléltetjük.
Alap paraméterek
Mint látjuk, van pár paramétere a függvénynek, de az utolsó három csak opcionális.
- A keresési érték az az érték, amit szeretnénk megtalálni a vizsgált tartomány valamely oszlopában.
- A keresési tömb adja meg azt az oszlopot, amelyben keressük az előző értéket. Fontos, hogy itt – ellentétben az FKERES-sel – ez az oszlop nem feltétlen kell az első legyen.
- A visszaadandó tömb tartalmazza azt az értéket, amelyet szeretnénk eredményül kapni. Tehát ha a keresési értéket a keresési tömb 3. helyén találta meg a függvény, akkor a visszaadandó tömb 3. értékét fogjuk eredményül kapni. Fontos, hogy a visszaadandó tömbnek nem kell feltétlen kapcsolatban állnia a keresési tömbbel, akár egy teljesen más munkalap tartománya is lehet.
1. példa
Egy már korábban is használt táblázatban keressük meg a T0006-os termékhez tartozó értékesített mennyiséget. Ahogy az a lenti ábrán is látható, a keresett értékünk a „T0006”-os, ezt az elemet kell megtalálni az „A” oszlopban, eredményül pedig a találat sorának megfelelő sort szeretnénk megkapni a „B” oszlopból. Ez egy tipikus, FKERES-sel is megoldható probléma.
=XKERES(F2;A2:A18;B2:B18)
2. példa
Annyit csavarjunk a feladaton, hogy átrendezzük a táblázatot. A cél ugyan az, csak most a termékeket az utolsó oszlop tartalmazza, míg az eredményül kívánt értékeket a középső. Ilyen esetben eddig az INDEX + HOL.VAN kombinációt kellett volna használnunk, de most ez is könnyen kivitelezhető ezzel a függvénnyel:
=XKERES(F2;C2:C18;B2:B18)
3. példa
Újdonság, hogy most már nem csak egyetlen értéket tud a függvény visszaadni eredményül, hanem egy teljes tömböt is (pl. a táblázat adott sorát). Ehhez nem kell mást tennünk, mint egynél több oszlopot kijelölni a harmadik, visszaadandó tömb paraméterénél.
Ezt követően simán leüthetjük az ENTER-t, így a függvény automatikusan annyi értéket szúr be a képlettől jobbra, amennyit szükséges (ld. derengő kék jelölés). Az automatikus tömb beszúrás sem létezik régóta az Excelben, eddig a CTRL+SHIFT+ENTER kombinációt kellett használni, miután előre kijelöltük a várható tömb méretének megfelelő tartományt.
=XKERES(F2;A2:A18;B2:C18)
Speciális paraméterek
Nem tettünk még említést az XKERES függvény másik három paraméteréről. Ismétlésképpen a szintaxis:
=XKERES(keresési_érték;keresési_tömb;visszaadandó_tömb;[ha_nincs_találat];[egyeztetési_mód];[keresési_mód])
- A függvény a negyedik paraméterben deklarált értéket, szöveget adja vissza eredményül, amennyiben a keresés nem futott találatra. Az FKERES ilyen esetben hibát dobott, azt külön – pl. a HAHIBA függvény segítségével – kellett lekezelni, de most már ezt beépítve megapjuk.
- Az ötödik egyeztetési mód paraméter ismerős lehet az FKERES esetén is:
- 0 érték esetén csakis a pontos találat esetén ad vissza eredményt a függvény, egyébként hibát – vagy a negyedik paraméterben megadott értéket – dobja vissza.
- -1 érték esetén a pontos egyezést vagy a keresett értékhez legközelebbi kisebb érték sorának megfelelő értéket,
- 1 esetén pedig a pontos egyezést vagy a keresett értékhez legközelebb eső nagyobb érték sora alapján vett értéket adja eredményül.
- Az utolsó paraméter segítségével megcserélhetjük a keresési sorrendet:
- 1 esetén fentről lefelé keressük az értéket és első találat esetén leáll a függvény,
- -1 esetén pedig alulról felfelé tesszük ugyan ezt.
4. példa
Azt a terméket keressük, amelyből pontosan 20.000 darabot, kicsit kevesebb és kicsit több, mint 20.000 darabot adtak el. Ezekre mind megoldást nyújt ez a függvény annak 5. paramétere.
- -1 esetén a legközelebbi kisebb érték a 18886, amely a 14. termék sorában található,
- +1 esetén a legközelebbi nagyobb érték az 5. termék sorában található 20173-as érték,
- míg 0 esetén nincs pontos találat, így az eredmény a 4. paraméter helyére beírt „nincs találat” szöveg.
5. példa
Utolsó példánkban nézzük meg a hatodik paraméter működését: keressük meg azt a terméket, amelyből 1800 Ft-ért értékesítjük darabját. Az utolsó oszlopban fentről lefelé keresve ezt az értéket a 7-es terméket, míg lentről felfelé keresve a 15-ös terméket kapjuk eredményül.
