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. Íme, amit tudnod kell.
Tartalomjegyzék
Mi az XLOOKUP?
Az új XLOOKUP funkció megoldásokat kínál a VLOOKUP legnagyobb korlátaira. Ráadásul a HLOOKUP helyébe is lép. Például az XLOOKUP balra nézhet, alapértelmezés szerint pontos egyezést ad, és lehetővé teszi, hogy oszlopszám helyett cellatartományt adjon meg. A VLOOKUP nem olyan könnyen használható vagy nem olyan sokoldalú. Megmutatjuk, hogyan működik mindez.
Jelenleg az XLOOKUP csak az Insiders program felhasználói számára érhető el. Bárki tud csatlakozzon az Insiders programhoz hogy elérje a legújabb Excel-szolgáltatásokat, amint azok elérhetővé válnak. A Microsoft hamarosan megkezdi az összes Office 365 felhasználó számára történő bevezetését.
Az XLOOKUP funkció használata
Vessünk egy pillantást az XLOOKUP működési példájára. Vegyük az alábbi példaadatokat. Az F oszlopból szeretnénk visszaadni a részleget minden egyes azonosítóhoz az A oszlopban.
Ez egy klasszikus pontos egyezési példa. Az XLOOKUP funkcióhoz mindössze három információra van szükség.
Az alábbi képen az XLOOKUP látható hat argumentummal, de csak az első három szükséges a pontos egyezéshez. Tehát koncentráljunk rájuk:
Lookup_value: Amit keres.
Lookup_array: Hol kell keresni.
Return_array: a visszaadandó é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 néhány előnyét a VLOOKUP-pal szemben.
Nincs több oszlop indexszáma
A VLOOKUP hírhedt harmadik érve az volt, hogy megadja a táblatömbből visszaadandó információ oszlopszámát. Ez már nem probléma, mert az XLOOKUP lehetővé teszi, hogy kiválassza a tartományt, ahonnan visszatérjen (ebben a példában az F oszlop).
És ne feledje, az XLOOKUP meg tudja tekinteni a kiválasztott cellától balra lévő adatokat, ellentétben a VLOOKUP-pal. Erről bővebben alább.
Többé nem fordul elő hibás képlet új oszlopok beszúrásakor. Ha ez történik a táblázatban, a visszatérési tartomány automatikusan módosulna.
A pontos egyezés az alapértelmezett
Mindig zavaró volt a VLOOKUP tanulása során, hogy miért kell pontos egyezést megadni.
Szerencsére az XLOOKUP alapértelmezés szerint pontos egyezést ad – ez sokkal gyakoribb oka a keresési képlet használatának). Ez csökkenti az ötödik érv megválaszolásának szükségességét, és kevesebb hibát vét a képletet újonnan ismerő felhasználók számára.
Tehát 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ásának lehetősége az XLOOKUP-ot sokoldalúbbá teszi, mint a VLOOKUP. Az XLOOKUP esetén a táblázat oszlopainak sorrendje nem számít.
A VLOOKUP korlátozása úgy történt, hogy a táblázat bal szélső oszlopában keresett, majd adott számú oszlopról tért vissza jobbra.
Az alábbi példában meg kell keresnünk egy azonosítót (E oszlop), és vissza kell adni a személy nevét (D oszlop).
Ezt a következő képlet érheti el: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Mi a teendő, ha nem található
A keresési függvények felhasználói nagyon jól ismerik az #N/A hibaüzenetet, amely akkor üdvözli őket, ha a VLOOKUP vagy a MATCH funkciójuk nem találja meg, amire szüksége van. És ennek gyakran logikus oka van.
Ezért a felhasználók gyorsan utánanéznek, hogyan lehet elrejteni ezt a hibát, mert nem helyes vagy hasznos. És persze erre is van mód.
Az XLOOKUP saját beépített „ha nem található” argumentummal rendelkezik az ilyen hibák kezelésére. Lássuk működés közben az előző példával, de elgépelt azonosítóval.
A következő képlet a „Helytelen azonosító” szöveget jeleníti meg a hibaüzenet helyett: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,”Helytelen azonosító)
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 felhasználása az, ha egy értéket tartományokban keresünk. Vegyük a következő példát. A kedvezményt az elköltött összeg függvényében szeretnénk visszaadni.
Ezúttal nem keresünk konkrét értéket. Tudnunk kell, hogy a B oszlopban lévő értékek hol esnek az E oszlop tartományába. Ez határozza meg a megszerzett engedményt.
Az XLOOKUP-nak van egy opcionális ötödik argumentuma (ne feledje, hogy alapértelmezés szerint a pontos egyezést használja), melynek neve egyezési mód.
Látható, hogy az XLOOKUP hozzávetőleges egyezésekkel nagyobb képességekkel rendelkezik, mint a VLOOKUP.
Lehetőség van megtalálni a legközelebbi egyezést, amely kisebb, mint (-1), vagy a legközelebbi nagyobb, mint (1) a keresett értékhez. 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ében.
A példában szereplő képlet a keresett értéknél a legközelebbi kisebbet adja vissza, ha nem található pontos egyezés: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Azonban hiba van a C7 cellában, ahol a #N/A hibaüzenet jelenik meg (az „ha nem található” argumentum nem került felhasználásra). Ennek 0% kedvezményt kellett volna visszaadnia, mert a 64 elköltése nem éri el a kedvezmény feltételeit.
Az XLOOKUP függvény másik előnye, hogy nem igényli, hogy a keresési tartomány növekvő sorrendben legyen, mint ahogy a VLOOKUP teszi.
Í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 sarkokra kattintva és húzva.
A képlet azonnal kijavítja a hibát. Nem probléma, ha a „0” a tartomány alján van.
Személy szerint továbbra is a keresési oszlop szerint rendezném a táblázatot. Ha alul „0” lenne, az megőrjítene. De az a tény, hogy a képlet nem tört el, zseniális.
XLOOKUP A HLOOKUP funkciót is helyettesíti
Amint már említettük, az XLOOKUP függvény is itt helyettesíti a HLOOKUP-ot. Egy funkció kettőt helyettesít. Kiváló!
A HLOOKUP funkció a vízszintes keresés, amely a sorok mentén történő keresésre szolgál.
Nem annyira ismert, mint a testvére VLOOKUP, de hasznos az alábbi példákhoz, ahol a fejlécek az A oszlopban, az adatok pedig a 4. és 5. sorban találhatók.
Az XLOOKUP mindkét irányba nézhet – az oszlopok lefelé és a 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 az A2 cellában lévő névhez tartozó eladási érték visszaadására szolgál. Végignéz a 4. sorban, hogy megtalálja a nevet, és visszaadja az 5. sor értéket: =XLOOKUP(A2,B4:E4,B5:E5)
Az XLOOKUP alulról felfelé nézhet
Általában le kell vadásznia egy listát, hogy megtalálja egy érték első (gyakran csak) előfordulását. Az XLOOKUP-nak van egy hatodik argumentuma, a keresési mód. Ez lehetővé teszi számunkra, hogy a keresést úgy váltsuk át, hogy alulról kezdjük, és inkább egy listában keressük meg az érték utolsó előfordulását.
Az alábbi példában az A oszlopban lévő egyes termékek készletszintjét szeretnénk megtalálni.
A keresési táblázat dátum szerinti sorrendben van, és termékenként több készletellenőrzés van. Szeretnénk visszaadni a legutóbbi ellenőrzés (a termékazonosító utolsó előfordulása) készletszintjét.
Az XLOOKUP függvény hatodik argumentuma négy lehetőséget kínál. Érdekelnek bennünket a „Keresés az utolsótól az elsőig” opció használata iránt.
Az elkészült 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 argumentumot figyelmen kívül hagyták. Ez nem kötelező, és mi szerettük volna az alapértelmezett pontos egyezést.
Felhajt
Az XLOOKUP függvény a izgatottan várt utódja mind a VLOOKUP, mind a HLOOKUP funkciókhoz.
Ebben a cikkben számos példát használtunk az XLOOKUP előnyeinek bemutatására. Ezek közül az egyik az, hogy az XLOOKUP használható lapokon, munkafüzeteken és táblázatokon is. A példák egyszerűek voltak a cikkben, hogy jobban megértsük.
Következtében dinamikus tömbök bevezetése az Excelbe hamarosan egy értéktartományt is visszaadhat. Ezt mindenképpen érdemes tovább vizsgálni.
A VLOOKUP napjai meg vannak számlálva. Az XLOOKUP itt van, és hamarosan ez lesz a de facto keresési képlet.