Az Excel XLOOKUP függvény használata -

Összegzés

Az Excel XLOOKUP funkció modern és rugalmas helyettesítő a régebbi funkciókhoz, például a VLOOKUP, a HLOOKUP és a LOOKUP. Az XLOOKUP támogatja a hozzávetőleges és pontos egyezést, a helyettesítő karaktereket (*?) A részleges egyezésekhez, valamint a függőleges vagy vízszintes tartományban történő keresést.

Célja

Keresési értékek tartományban vagy tömbben

Visszatérési érték

Egyező érték (ek) a visszatérő tömbből

Szintaxis

= XLOOKUP (lookup, look__ray, return_array, (not_found), (match_mode), (search_mode))

Érvek

  • keresés - A keresési érték.
  • lookup_array - A tömb vagy a keresendő tartomány.
  • return_array - A visszatérő tömb vagy tartomány.
  • not_found - (opcionális) Visszaadandó érték, ha nem található egyezés.
  • match_mode - (opcionális) 0 = pontos egyezés (alapértelmezett), -1 = pontos egyezés vagy a következő legkisebb, 1 = pontos egyezés vagy a következő nagyobb, 2 = helyettesítő karakter.
  • keresési mód - (opcionális) 1 = első keresés (alapértelmezett), -1 = utolsó keresés, 2 = bináris keresés növekvő, -2 = bináris keresés csökkenő.

Változat

Excel 365

Használati megjegyzések

Az XLOOKUP a VLOOKUP funkció modern helyettesítője. Ez egy rugalmas és sokoldalú funkció, amely sokféle helyzetben használható.

Az XLOOKUP függőleges vagy vízszintes tartományokban képes megtalálni az értékeket, hozzávetőleges és pontos egyezéseket képes végrehajtani, és részleges egyezések esetén támogatja a helyettesítő karaktereket (*?). Ezenkívül az XLOOKUP az első vagy az utolsó értéktől kezdve kereshet adatokat (lásd alább az egyezés típusát és a keresési mód részleteit). A régebbi funkciókhoz, például a VLOOKUP, a HLOOKUP és a LOOKUP, az XLOOKUP számos kulcselőnyt kínál.

Nem található üzenet

Ha az XLOOKUP nem talál egyezést, a # N / A hibát adja vissza, mint az Excel többi egyezési funkciója. A többi egyezési függvénnyel ellentétben az XLOOKUP támogatja a not_found nevű opcionális argumentumot, amely felhasználható a # N / A hiba felülbírálására, ha az egyébként megjelenik. A not_found tipikus értékei lehetnek: "Not found", "No match", "No result" stb. Ha a not_found értékét adja meg, csatolja a szöveget dupla idézőjelbe ("").

Megjegyzés: Vigyázzon, ha egy üres karakterláncot ("") ad meg a not_found kifejezéshez. Ha nem található egyezés, az XLOOKUP nem jelenít meg semmit a # N / A helyett. Ha a # N / A hibát szeretné látni, ha nem található egyezés, hagyja ki teljesen az argumentumot.

Egyezés típusa

Alapértelmezés szerint az XLOOKUP pontos egyezést fog végrehajtani. Az egyezés viselkedését egy választható match_type nevű argumentum vezérli, amelynek a következő lehetőségei vannak:

Egyezés típusa Viselkedés
0 (alapértelmezett) Pontos mérkőzés. # N / A-t ad vissza, ha nincs meccs.
-1 Pontos egyezés vagy a következő kisebb elem.
1 Pontos egyezés vagy következő nagyobb tétel.
2 Helyettesítőmeccs (*,?, ~)

Keresési mód

Alapértelmezés szerint az XLOOKUP az első adatértéktől kezdi meg az egyezést. A keresési magatartást egy választható argumentum, az úgynevezett search_mode szabályozza , amely a következő lehetőségeket kínálja:

Keresési mód Viselkedés
1 (alapértelmezett) Keresés az első értéktől
-1 Keresés az utolsó értékből (fordított)
2 A bináris keresési értékek növekvő sorrendben rendezve
-2 A bináris keresési értékek csökkenő sorrendben rendezve

A bináris keresések nagyon gyorsak, de az adatokat szükség szerint kell rendezni. Ha az adatok nincsenek rendezve megfelelően, akkor a bináris keresés érvénytelen eredményeket adhat, amelyek teljesen normálisnak tűnnek.

1. példa - pontos pontos egyezés

Alapértelmezés szerint az XLOOKUP pontos egyezést fog végrehajtani. Az alábbi példában az XLOOKUP az értékesítés lekérésére szolgál a Movie pontos egyezése alapján. A H5 képlete a következő:

=XLOOKUP(H4,B5:B9,E5:E9)

Részletesebb magyarázat itt.

2. példa - alapvető hozzávetőleges egyezés

A hozzávetőleges egyezés engedélyezéséhez adjon meg egy értéket a "match_mode" argumentumnak. Az alábbi példában az XLOOKUP-ot használjuk a mennyiség alapján történő kedvezmény kiszámításához, amely hozzávetőleges egyezést igényel. Az F5 képlete -1-et ad a match_mode számára, hogy lehetővé tegye a hozzávetőleges egyezést a "pontos egyezés vagy a következő legkisebb" viselkedéssel:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Részletesebb magyarázat itt.

3. példa - több érték

Az XLOOKUP egynél több értéket adhat vissza ugyanarra a mérkőzésre. Az alábbi példa bemutatja, hogy az XLOOKUP hogyan konfigurálható úgy, hogy három egyező értéket adjon vissza egyetlen képlettel. A C5 képlete a következő:

=XLOOKUP(B5,B8:B15,C8:E15)

Figyelje meg, hogy a visszatérő tömb (C8: E15) 3 oszlopot tartalmaz: Első, Utolsó, Osztály. Mindhárom érték visszaadódik, és a C5: E5 tartományba kerül.

4. példa - kétirányú keresés

Az XLOOKUP felhasználható kétirányú keresésre, az egyik XLOOKUP beágyazásával a másikba. Az alábbi példában a "belső" XLOOKUP lekér egy teljes sort (az Glass összes értékét), amelyet visszatérő tömbként a "külső" XLOOKUP kap. A külső XLOOKUP megtalálja a megfelelő csoportot (B), és a megfelelő értéket (17.25) adja vissza végeredményként.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

További részletek itt.

5. példa - nem található üzenet

A többi keresési funkcióhoz hasonlóan, ha az XLOOKUP nem talál értéket, akkor a # N / A hibát adja vissza. Ha egyéni üzenetet szeretne megjeleníteni a # N / A helyett, adja meg az opcionális "nem található" argumentum értékét, dupla idézőjelek ("") között. Például a "Nem található" megjelenítéséhez, ha nem található megfelelő film, az alábbi munkalap alapján használja:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Testre szabhatja ezt az üzenetet tetszése szerint: "Nincs egyezés", "A film nem található" stb.

6. példa - összetett kritériumok

A tömbök natív kezelésének képességével az XLOOKUP összetett feltételekkel használható. Az alábbi példában az XLOOKUP illeszkedik az első rekordhoz, ahol: a számla "x" -nel kezdődik, a régió pedig "kelet", a hónap pedig nem április:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Részletek: (1) egyszerű példa, (2) összetettebb példa.

Az XLOOKUP előnyei

Az XLOOKUP számos fontos előnyt kínál, különösen a VLOOKUP-hoz képest:

  • Az XLOOKUP a keresési értékektől jobbra vagy balra kereshet adatokat
  • Az XLOOKUP több eredményt is eredményezhet (fenti 3. példa)
  • Az XLOOKUP alapértelmezés szerint pontos egyezést ad meg (a VLOOKUP alapértelmezés szerint hozzávetőleges)
  • Az XLOOKUP függőleges és vízszintes adatokkal is képes dolgozni
  • Az XLOOKUP fordított keresést hajthat végre (utolsótól az elsőig)
  • Az XLOOKUP egész sorokat vagy oszlopokat adhat vissza, nem csak egy értéket
  • Az XLOOKUP összetett kritériumok alkalmazásában natív tömbökkel dolgozhat

Megjegyzések

  1. Az XLOOKUP függőleges és vízszintes tömbökkel egyaránt képes működni.
  2. Az XLOOKUP # N / A értéket ad vissza, ha a keresési érték nem található.
  3. A lookup_array kell egy dimenzió összeegyeztethető return_array érv, különben XLOOKUP visszatér # ÉRTÉK!
  4. Ha az XLOOKUP alkalmazást használja a munkafüzetek között, akkor mindkét munkafüzetnek nyitva kell lennie, különben az XLOOKUP visszaadja a #REF!
  5. Az INDEX függvényhez hasonlóan az XLOOKUP ennek eredményeként referenciát ad vissza.

Kapcsolódó videók

Alapvető példa az XLOOKUP-ra Ebben a videóban egy alapvető példával állítjuk be az XLOOKUP funkciót. A városnév alapján egyeztetjük az országot és a népességet. Alapvető XLOOKUP hozzávetőleges egyezés Ebben a videóban egy XLOOKUP funkciót állítunk be hozzávetőleges egyezés végrehajtására a mennyiségalapú kedvezmény kiszámításához. XLOOKUP logikai logikával Ebben a videóban megnézzük, hogyan lehet használni az XLOOKUP függvényt logikai logikával több feltétel alkalmazásához. XLOOKUP több keresési értékkel Ebben a videóban az XLOOKUP-ot úgy állítjuk be, hogy több értéket adjon vissza egy dinamikus tömbben, egy keresési értéktartomány megadásával egyetlen keresési érték helyett.

érdekes cikkek...