XLOOKUP függvény

Az XKERES függvénnyel soronként kereshet dolgokat egy táblázatban vagy tartományban. Megkeresheti például egy autóipari alkatrész árát a cikkszám alapján, vagy megkeresheti az alkalmazott nevét az alkalmazott azonosítója alapján. Az XKERES függvény segítségével megkeresheti az egyik oszlopban a keresési kifejezést, és visszaadhatja egy másik oszlop ugyanazon sorának eredményét, függetlenül attól, hogy a visszatérési oszlop melyik oldalon van.

Megjegyzés: Az XKERES függvény nem érhető el az Excel 2016 és az Excel 2019-ben, azonban előfordulhat, hogy egy munkafüzetet Excel 2016 vagy Excel 2019-ben használ, amelyben az XKERES függvényt valaki más hozta létre az Excel újabb verzióját használva.

Ez a böngésző nem támogatja a videók lejátszását. Telepítse a Microsoft Silverlightot, az Adobe Flash Playert vagy az Internet Explorer 9-es verzióját.

Szintaxis

Az XKERES függvény egy tartományban vagy tömbben keres, majd visszaadja az első találatnak megfelelő elemet. Ha nincs egyezés, akkor az XKERES függvény a legközelebbi (közelítő) egyezést adja vissza.

= XLOOKUP (keresett_érték; keresési_tömb; visszaadott_tömb; [ha_nincs_találat]; [illesztési_mód]; [keresési_mód])

argumentumok	Leírás
keresési_érték: Szükséges*	A keresendő érték Ha nincs megadva, az XKERES függvény üres cellákat ad vissza lookup_array.*
tábla: Kötelező	A keresendő tömb vagy tartomány
return_array Kötelező	A visszaadni kívánt tömb vagy tartomány
[if_not_found] Nem kötelező	Ha nem található érvényes egyezés, adja vissza a megadott [if_not_found] szöveget. Ha nem található érvényes egyezés, és [if_not_found] hiányzik, a rendszer #N/A értéket ad vissza.
[match_mode] Nem kötelező	Adja meg az egyezés típusát: 0 - Pontos egyezés. Ha nem található, adja vissza #N/A értéket. Ez az alapértelmezett beállítás. -1 - Pontos egyezés. Ha nem található, a következő kisebb elemet adja vissza. 1 - Pontos egyezés. Ha nem található, a következő nagyobb elemet adja vissza. 2 – Helyettesítő karakter, ahol a *, a ? és a ~ karakternek speciális jelentése van.
[search_mode] Nem kötelező	Adja meg a használni kívánt keresési módot: 1 – Végezzen keresést az első elemtől kezdve. Ez az alapértelmezett beállítás. -1 – Végezzen fordított keresést az utolsó elemtől kezdve. 2 – Végezzen bináris keresést, amely lookup_array növekvő sorrendbe rendezésre támaszkodik. Ha nincs rendezve, a rendszer érvénytelen eredményeket ad vissza. -2 – Olyan bináris keresést hajt végre, amely a lookup_array-re hagyatkozva azt csökkenő sorrendbe rendezi. Ha nincs rendezve, a rendszer érvénytelen eredményeket ad vissza.

argumentumok

Leírás

keresési_érték:

Szükséges*

A keresendő

érték *Ha nincs megadva, az XKERES függvény üres cellákat ad vissza lookup_array.

tábla:

Kötelező

A keresendő tömb vagy tartomány

return_array

Kötelező

A visszaadni kívánt tömb vagy tartomány

[if_not_found]

Nem kötelező

Ha nem található érvényes egyezés, adja vissza a megadott [if_not_found] szöveget.

Ha nem található érvényes egyezés, és [if_not_found] hiányzik, a rendszer #N/A értéket ad vissza.

[match_mode]

Nem kötelező

Adja meg az egyezés típusát:

0 - Pontos egyezés. Ha nem található, adja vissza #N/A értéket. Ez az alapértelmezett beállítás.

-1 - Pontos egyezés. Ha nem található, a következő kisebb elemet adja vissza.

1 - Pontos egyezés. Ha nem található, a következő nagyobb elemet adja vissza.

2 – Helyettesítő karakter, ahol a *, a ? és a ~ karakternek speciális jelentése van.

[search_mode]

Nem kötelező

Adja meg a használni kívánt keresési módot:

1 – Végezzen keresést az első elemtől kezdve. Ez az alapértelmezett beállítás.

-1 – Végezzen fordított keresést az utolsó elemtől kezdve.

2 – Végezzen bináris keresést, amely lookup_array növekvő sorrendbe rendezésre támaszkodik. Ha nincs rendezve, a rendszer érvénytelen eredményeket ad vissza.

-2 – Olyan bináris keresést hajt végre, amely a lookup_array-re hagyatkozva azt csökkenő sorrendbe rendezi. Ha nincs rendezve, a rendszer érvénytelen eredményeket ad vissza.

Példák

Az 1. példa az XKERES függvényt használja egy tartomány országnevének kereséséhez, majd visszaadja a telefon országkódját. Tartalmazza a lookup_value (F2 cella), lookup_array (B2:B11 tartomány) és return_array (D2:D11 tartomány) argumentumokat. Nem tartalmazza a match_mode argumentumot, mivel az XKERES függvény alapértelmezés szerint pontos egyezést hoz létre.

Példa az XKERES függvényre, amellyel az Alkalmazott neve és a Részleg értéket adja vissza az alkalmazott azonosítója alapján. A képlet az =XKERES(B2;B5:B14;C5:C14).

Megjegyzés: Az XKERES függvény egy keresési és egy visszatérési tömböt használ, míg az FKERES függvény egyetlen táblázattömböt és egy oszlopindexszámot használ. Ebben az esetben az egyenértékű FKERES képlet a következő: =FKERES(F2;B2:D11;3;HAMIS)

———————————————————————————

A 2. példa egy alkalmazotti azonosítószám alapján keresi meg az alkalmazottak adatait. Az FKERES függvénytől eltérően az XKERES függvény több elemet tartalmazó tömböt is visszaadhat, így egyetlen képlet az alkalmazott nevét és a részleget is visszaadhatja a C5:D14 cellából.

Példa az XLOOKUP függvényre, amellyel az Alkalmazott neve és a Részleg értéket adja vissza az Alkalmazotti azonosító alapján. A képlet a következő: =XKERES(B2;B5:B14;C5:D14;0;1)

———————————————————————————

A 3. példa egy if_not_found argumentumot ad hozzá az előző példához.

Példa az XKERES függvényre, amellyel az Alkalmazott neve és a Részleg értéket adja vissza az Alkalmazott azonosítója alapján a if_not_found argumentummal. A képlet a következő: =XKERES(B2;B5:B14;C5:D14;0;1;"Az alkalmazott nem található")

———————————————————————————

A 4. példa az E2 cellába beírt személyi jövedelem C oszlopában keresi meg a megfelelő adókulcsot a B oszlopban. A if_not_found argumentumot 0 (nulla) értékre állítja, ha nem található semmi. A match_mode argumentum értéke 1, ami azt jelenti, hogy a függvény pontos egyezést keres, és ha nem talál egyet, a következő nagyobb elemet adja vissza. Végül a search_mode argumentum értéke 1, ami azt jelenti, hogy a függvény az első elemtől az utolsóig fog keresni.

Az FKERES függvény képe, amellyel a maximális jövedelem alapján adókulcsot ad vissza. Ez egy hozzávetőleges egyezés. A képlet a következő: =XKERES(E2;C2:C7;B2:B7;1;1)

Megjegyzés: Az XARRAY lookup_array oszlopa az return_array oszloptól jobbra található, míg az FKERES függvény csak balról jobbra tud keresni.

———————————————————————————

5. példa beágyazott XKERES függvényt használ függőleges és vízszintes egyezés végrehajtásához. Először a B oszlopban keresi a Bruttó nyereséget , majd a táblázat felső sorában (C5:F5 tartomány) az 1. negyedévet , végül pedig a kettő metszéspontjában lévő értéket adja vissza. Ez hasonló az INDEX és a HOL.VAN függvény együttes használatához.

Tipp: Az FKERES függvényt az XKERES függvénnyel is lecserélheti.

Az XLOOKUP függvény képe, amellyel vízszintes adatokat ad vissza egy táblából két XLOOKUP beágyazásával. A képlet a következő: =XKERES(D2;$B 6:$B 17;XKERES($C 3;$C 5:$G 5;$C 6:$G 17))

Megjegyzés: A D3:F3 cella képlete a következő: =XKERES(D2;$B 6:$B 17;XKERES($C 3;$C 5:$G 5;$C 6:$G 17)))..

———————————————————————————

6. példa a SZUM függvénnyel és két beágyazott XKERES függvénnyel összegzi a két tartomány összes értékét. Ebben az esetben a szőlő, a banán és a körte értékeit szeretnénk összegzni, amelyek a kettő között vannak.

Az XKERES függvény használata a SZUM függvénnyel két kijelölés közötti értéktartomány összegzéséhez

Az E3 cellában lévő képlet a következő: =SZUM(XKERES(B3;B6:B10;E6:E10):XKERES(C3;B6:B10;E6:E10))

Hogyan működik? Az XKERES függvény egy tartományt ad vissza, így számításkor a képlet a következőképpen végződik: =SZUM($E$7:$E$9). Ennek működését önállóan is megtekintheti, ha kijelöl egy cellát az ehhez hasonló XKERES képlettel, majd a Képletek > Képletvizsgálat > Képletkiértékelő lehetőséget választja, majd a Kiértékelés lehetőséget választva lépeget a számításon.

Megjegyzés: Köszönjük a Microsoft Excel MVP-nek, Bill Jelennek, hogy javaslatot tesz erre a példára.

———————————————————————————