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

Excel a gyakorlatban X. – XKERES függvény

Innolog Excel XKERES

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)

Excel XKERES példa

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)

Excel XKERES példa

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)

Excel XKERES példa

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.

Excel XKERES példa

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.

Excel XKERES példa

Összefoglalás
XKERES függvény
Cikk címe
XKERES függvény
Leírás
Bejegyzésünkben egy 2019 végén megjelent eszköz, a korábbi FKERES-t leváltó XKERES függvény kerül bemutatásra, példákkal szemléltetve
Szerző
Kiadó
Innolog Solutions
Kiadó logó
Tetszett az oldal? Oszd meg másokkal is!
English