Az XLOOKUP függvény használata a Microsoft Excelben

Az Excel új XLOOKUP-ja felváltja a VLOOKUP-ot, és hatékonyan helyettesíti az Excel egyik legnépszerűbb funkcióját. Ez az új funkció megoldja a VLOOKUP néhány korlátját, és extra funkciókkal rendelkezik. Itt van, amit tudnia kell.

Mi az XLOOKUP?

Az új XLOOKUP funkció megoldásokat kínál a VLOOKUP legnagyobb korlátozásaira. Ráadásul a HLOOKUP-ot is felváltja. Például az XLOOKUP balra nézhet, alapértelmezés szerint pontos egyezést ad meg, és lehetővé teszi, hogy oszlopszám helyett cellatartományt adjon meg. A VLOOKUP nem ilyen könnyen használható, vagy annyira sokoldalú. Megmutatjuk, hogyan működik mindez.

Az XLOOKUP jelenleg csak az Insider program felhasználói számára elérhető. Bárki csatlakozhat az Insiders programhoz, hogy elérje a legújabb Excel-szolgáltatásokat, amint elérhetővé válnak. A Microsoft hamarosan megkezdi az összes Office 365 felhasználó számára.

Az XLOOKUP funkció használata

Merüljünk egyenesen az XLOOKUP példáján. Vegyük az alábbi példaadatokat. Vissza akarjuk adni az osztályt az F oszlopból az A oszlop minden egyes azonosítójához.

Ez egy klasszikus pontos egyezés keresési példa. Az XLOOKUP funkció csak három információt igényel.

Az alábbi képen az XLOOKUP látható hat argumentummal, de a pontos egyezéshez csak az első háromra van szükség. Tehát összpontosítsunk rájuk:

  • Keresési_érték:  Amit keres.
  • Lookup_array:  Hol keressük.
  • Return_array:  a visszatérendő értéket tartalmazó tartomány.

A következő képlet működik ebben a példában: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Vizsgáljuk meg most az XLOOKUP előnyeit a VLOOKUP-tal szemben.

Nincs több oszlopindex szám

A VLOOKUP hírhedt harmadik érve az volt, hogy megadta a táblázat tömbből visszatérő információk oszlopának számát. Ez már nem kérdés, mert az XLOOKUP lehetővé teszi, hogy kiválassza a visszatérni kívánt tartományt (ebben a példában az F oszlop).

És ne felejtsd el, hogy az XLOOKUP a VLOOKUP-tal ellentétben megtekintheti a kiválasztott cellából maradt adatokat. Bővebben erről alább.

Új oszlopok beillesztésekor már nem kérdése a törött képlet. Ha ez megtörtént a táblázatban, akkor a visszatérési tartomány automatikusan beáll.

Pontos egyezés az alapértelmezett

A VLOOKUP megtanulása során mindig zavaró volt, miért kellett pontos egyezést megadni.

Szerencsére az XLOOKUP alapértelmezés szerint pontos egyezést jelent - ez a gyakoribb ok a keresési képlet használatára). Ez csökkenti annak az ötödik érvnek a megválaszolásának szükségességét, és kevesebb hibát biztosít a felhasználók számára, akik újak a képletben.

Röviden: az XLOOKUP kevesebb kérdést tesz fel, mint a VLOOKUP, felhasználóbarátabb és tartósabb is.

Az XLOOKUP balra nézhet

A keresési tartomány kiválasztása sokoldalúbbá teszi az XLOOKUP-ot, mint a VLOOKUP. Az XLOOKUP használatakor a táblázat oszlopainak sorrendje nem számít.

A VLOOKUP-ot úgy korlátozták, hogy egy táblázat bal szélső oszlopában keresgélt, majd egy meghatározott számú oszlopból jobbra tért vissza.

Az alábbi példában meg kell keresnünk egy azonosítót (E oszlop), és vissza kell adnunk a személy nevét (D oszlop).

A következő képlettel lehet ezt elérni: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Mi a teendő, ha nem található

A keresési funkciók felhasználói jól ismerik a # N / A hibaüzenetet, amely üdvözli őket, amikor a VLOOKUP vagy a MATCH funkciójuk nem találja meg azt, amire szüksége van. Ennek pedig gyakran logikus oka van.

Ezért a felhasználók gyorsan megvizsgálják, hogyan lehet elrejteni ezt a hibát, mert az nem helyes vagy hasznos. És természetesen van rá mód.

Az XLOOKUP saját beépített "ha nem található" argumentummal rendelkezik az ilyen hibák kezeléséhez. Lássuk működés közben az előző példával, de hibásan beírt azonosítóval.

A következő képlet a „Hibás azonosító” szöveget jeleníti meg a hibaüzenet helyett: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Az XLOOKUP használata tartománykereséshez

Bár nem olyan gyakori, mint a pontos egyezés, a keresési képlet nagyon hatékony használata az értékek keresése tartományokban. Vegyük a következő példát. A kedvezményt az elköltött összeg függvényében szeretnénk visszatéríteni.

Ezúttal nem konkrét értéket keresünk. Tudnunk kell, hogy a B oszlop értékei hol esnek az E. oszlop tartományaiba. Ez határozza meg a megszerzett kedvezményt.

Az XLOOKUP rendelkezik egy opcionális ötödik argumentummal (ne feledje, alapértelmezés szerint a pontos egyezést adja meg), amelynek neve egyezési mód.

Láthatja, hogy az XLOOKUP nagyobb képességekkel rendelkezik hozzávetőleges egyezéssel, mint a VLOOKUP.

Lehetőség van megtalálni a legközelebbi egyezést (-1) -nél kisebb vagy (1) -nél kisebbet. Lehetőség van helyettesítő karakterek (2) használatára is, például a? vagy a *. Ez a beállítás alapértelmezés szerint nincs bekapcsolva, mint a VLOOKUP esetén.

Az ebben a példában szereplő képlet kevesebbet ad vissza, mint a keresett érték, ha nem található pontos egyezés: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Van azonban egy hiba a C7 cellában, ahol a # N / A hibát adja vissza (a "ha nem található" argumentumot nem használták). Ennek 0% -os kedvezményt kellett volna eredményeznie, mert a 64-es kiadás nem éri el a kedvezmény kritériumait.

Az XLOOKUP funkció további előnye, hogy nem igényli, hogy a keresési tartomány növekvő sorrendben legyen, mint a VLOOKUP.

Írjon be egy új sort a keresőtábla aljára, majd nyissa meg a képletet. Bővítse a használt tartományt a sarkok kattintásával és húzásával.

A képlet azonnal kijavítja a hibát. Nem probléma, ha a „0” van a tartomány alsó részén.

Személy szerint még mindig a keresési oszlop szerint rendezném a táblázatot. Ha az alján „0” van, akkor megőrülök. De az a tény, hogy a képlet nem tört el, zseniális.

Az XLOOKUP helyettesíti a HLOOKUP funkciót is

Mint említettük, az XLOOKUP funkció is itt van a HLOOKUP helyettesítésére. Egy funkció kettő helyettesítésére. Kiváló!

A HLOOKUP függvény a vízszintes keresés, a sorok mentén történő keresésre szolgál.

Nem annyira ismert, mint testvére VLOOKUP, de hasznos például az alábbiakban, ahol a fejlécek az A oszlopban vannak, és az adatok a 4. és 5. sor mentén találhatók.

Az XLOOKUP mindkét irányba nézhet - lefelé oszlopokban és sorok mentén is. Nincs többé szükségünk két különböző funkcióra.

Ebben a példában a képlet arra szolgál, hogy az A2 cellában a névhez kapcsolódó értékesítési értéket adja vissza. A 4. sor mentén keresi a nevet, és visszaadja az 5. sor értékét:=XLOOKUP(A2,B4:E4,B5:E5)

Az XLOOKUP alulról felfelé nézhet

Általában egy listára kell vadásznia, hogy megtalálja az érték első (gyakran csak) előfordulását. Az XLOOKUP rendelkezik egy hatodik argumentummal, amelynek neve keresési mód. Ez lehetővé teszi számunkra, hogy a keresést az alulról kezdve átkapcsoljuk, és listát keressünk, hogy megkeresjük az érték utolsó előfordulását.

Az alábbi példában meg szeretnénk találni az A oszlop minden egyes termékének készletszintjét.

A keresési táblázat dátum szerinti sorrendben van, és termékenként több készletellenőrzést végeznek. Vissza akarjuk adni a készletszintet a legutóbbi ellenőrzéskor (a termékazonosító legutóbbi előfordulása).

Az XLOOKUP függvény hatodik argumentuma négy lehetőséget kínál. Érdeklődni kívánunk a „Keresés az utolsótól az elsőig” opció használatáról.

A kitöltött képlet itt látható: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

Ebben a képletben a negyedik és ötödik érvet figyelmen kívül hagyták. Ez opcionális, és a pontos egyezés alapértelmezését akartuk.

Felhajt

Az XLOOKUP funkció mind a VLOOKUP, mind a HLOOKUP funkciók által várva várt utód.

Ebben a cikkben számos példát alkalmaztak az XLOOKUP előnyeinek bemutatására. Az egyik az, hogy az XLOOKUP lapokon, munkafüzeteken és asztalokon keresztül is használható. A példák egyszerűek voltak a cikkben, hogy segítsenek megértésünkben.

Az Excel hamarosan bevezetett dinamikus tömbjei miatt számos értéket is visszaadhat. Ezt mindenképpen érdemes tovább vizsgálni.

A VLOOKUP napjai meg vannak számlálva. Az XLOOKUP itt van, és hamarosan a de facto keresési képlet lesz.