A VLOOKUP függvény mindig is az Excel egyik leghatékonyabb funkciója volt. Lehetővé teszi, hogy értékeket keressen a táblázat első oszlopában, és értékeket adjon vissza a jobb oldali mezőkből. De az Excelnek van egy XLOOKUP nevű függvénye is, amely lehetővé teszi, hogy értéket keressen bármely oszlopban vagy sorban, és bármely másik oszlopból visszaadjon adatokat.
Hogyan működik az XLOOKUP
Az XLOOKUP funkció sokkal könnyebben használható, mint a VLOOKUP funkció, mivel ahelyett, hogy értéket adna meg az eredmények oszlopában, megadhatja a teljes tartományt.
A funkció lehetővé teszi, hogy oszlopban és sorban is keressen, az értéket a metsző cellában keresse meg.
Az XLOOKUP függvény paraméterei a következők:
=XLOOKUP (keresési_érték, keresési_tömb, visszatérési_tömb, [egyezési_mód], [keresési_mód])
- lookup_value: A keresni kívánt érték
- lookup_array: A keresni kívánt tömb (oszlop)
- return_array: Az eredmény (oszlop), amelyből értéket szeretne lekérni
- match_mode (opcionális): Válasszon ki egy pontos egyezést (0), pontos egyezést vagy a következő legkisebb értéket (-1), vagy helyettesítő karakteres egyezést (2).
- search_mode (opcionális): Válassza ki, hogy a keresést az oszlop első elemével (1), az oszlop utolsó elemével (-1) kezdje-e, bináris keresés növekvő sorrendben (2) vagy csökkenő bináris keresés (-2).
A következő néhány leggyakoribb keresés az XLOOKUP funkcióval.
Hogyan keressünk egyetlen eredményt az XLOOKUP használatával
Az XLOOKUP használatának legegyszerűbb módja, ha egyetlen eredményt keresünk egy oszlopból származó adatpont használatával.
-
Ez a példatáblázat az értékesítési képviselők által benyújtott rendelések listája, beleértve a tételt, az egységek számát, a költséget és a teljes eladást.
Image -
Ha meg szeretné találni az első eladást egy adott értékesítési képviselő által benyújtott listában, létrehozhat egy XLOOKUP függvényt, amely a Rep oszlopban keres egy nevet. A függvény az Összes oszlop eredményét adja vissza. Az XLOOKUP függvény ehhez:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: A Rep Name keresési cellára mutat
- C2:C44: Ez a Rep oszlop, amely a keresési tömb
- G2:G33: Ez a Total oszlop, amely a visszatérési tömb
- 0: Pontos egyezés kiválasztása
- 1: Az eredmények első találatának kiválasztása
-
Ha megnyomja az Enter gombot, és beírja az értékesítési képviselő nevét, az Összes eredmény cella az adott értékesítési képviselőhöz tartozó táblázat első eredményét mutatja.
Image -
Ha a legutóbbi akciót szeretné megkeresni (mivel a táblázat dátum szerint fordított sorrendben van rendezve), módosítsa az utolsó XLOOKUP argumentumot - 1-ra, ami elindul. a keresési tömb utolsó cellájában keressen, és helyette ezt az eredményt adja meg.
Image -
Ez a példa egy hasonló keresést mutat be, amelyet egy VLOOKUP függvénnyel hajthat végre, ha a Rep oszlopot használja a keresési táblázat első oszlopaként. Az XLOOKUP azonban lehetővé teszi, hogy bármelyik oszlopra keressen bármelyik irányba. Például, ha meg szeretné keresni azt az értékesítési képviselőt, aki az év első Binder-rendelését adta el, használja a következő XLOOKUP függvényt:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: A tételkereső cellára mutat
- D2:D44: Ez az Elem oszlop, ami a keresési tömb
- C2:C44: Ez a Rep oszlop, amely a keresési tömbtől balra lévő visszatérési tömb
- 0: Pontos egyezés kiválasztása
- 1: Az eredmények első találatának kiválasztása
-
Ezúttal az eredmény annak az értékesítési képviselőnek a neve lesz, aki eladta az év első iratgyűjtő-rendelését.
Image
Függőleges és vízszintes egyezés végrehajtása az XLOOKUP segítségével
Az XLOOKUP egy másik olyan képessége, amelyre a VLOOKUP nem képes, az a képesség, hogy függőleges és vízszintes keresést is végrehajtson, vagyis kereshet egy elemet egy oszlopban lefelé és egy soron keresztül is.
Ez a kettős keresési funkció hatékonyan helyettesíti az Excel más funkcióit, például az INDEX, MATCH vagy HLOOKUP.
-
A következő példatáblázatban az egyes értékesítési képviselők eladásai negyedévenként vannak felosztva. Ha egy adott értékesítési képviselő harmadik negyedéves eladásait szeretné látni, az XLOOKUP funkció nélkül, ez a fajta keresés nehézkes lenne.
Image -
Az XLOOKUP funkcióval ez a fajta keresés egyszerű. A következő XLOOKUP függvény segítségével megkeresheti egy adott értékesítési képviselő harmadik negyedéves eladásait:
=XKERESÉS(J2, B2:B42, XKERESÉS(K2, C1:H1, C2:H42))
- J2: A Rep keresési cellára mutat
- B2:B42: Ez az Elem oszlop, ami az oszlopkereső tömb
- K2: A negyed keresési cellára mutat
- C1:H1: Ez a sorkereső tömb
- C2:H42: Ez a dollárösszeg keresési tömbje minden negyedévben
Ez a beágyazott XLOOKUP függvény először az értékesítési képviselőt azonosítja, a következő XLOOKUP függvény pedig a kívánt negyedévet. A will visszatérési értéke az a cella, ahol ez a kettő metszi.
-
Ennek a képletnek az eredménye a Thompson nevű képviselő negyedéves bevétele.
Image
Az XLOOKUP funkció használata
Az XLOOKUP funkció csak az Office Insider-előfizetők számára érhető el, de hamarosan az összes Microsoft 365-előfizető számára elérhető lesz.
Ha saját maga szeretné kipróbálni a funkciót, válhat Office Insiderré. Válassza a File > Account lehetőséget, majd válassza ki az Office Insider legördülő menüt az előfizetéshez.
Miután csatlakozik az Office Insider programhoz, az Excel telepített verziója megkapja az összes legújabb frissítést, és elkezdheti használni az XLOOKUP funkciót.
