Excel XLOOKUP funkció: Minden, amit tudnia kell (10 példa)

Videó megtekintése - Excel XLOOKUP funkció (10 példa XLOOKUP -ra)

Excel XLOOKUP funkció végre megérkezett.

Ha a VLOOKUP vagy az INDEX/MATCH funkciót használta, biztos vagyok benne, hogy tetszeni fog az XLOOKUP funkció rugalmassága.

Ebben az oktatóanyagban mindent leírok, amit az XLOOKUP funkcióról tudni kell, és néhány példát, amelyek segítenek a legjobb használatban.

Kezdjük hát el!

Mi az XLOOKUP?

Az XLOOKUP az Office 365 új funkciója, és a VLOOKUP/HLOOKUP funkció új és továbbfejlesztett változata.

Mindent megtesz, amit a VLOOKUP szokott, és még sok minden mást.

Az XLOOKUP egy olyan funkció, amely lehetővé teszi, hogy gyorsan keressen egy értéket egy adatkészletben (függőleges vagy vízszintes), és adja vissza a megfelelő értéket egy másik sorban/oszlopban.

Például, ha megvan a vizsga diákjainak pontszáma, akkor az XLOOKUP használatával gyorsan ellenőrizheti, hogy egy tanuló mennyit ért el a diák nevével.

Ennek a funkciónak az ereje még világosabbá válik, amint mélyen belemerülök néhányba XLOOKUP példák később ebben az oktatóanyagban.

Mielőtt azonban belekezdenék a példákba, van egy nagy kérdés - hogyan juthatok hozzá az XLOOKUP -hoz?

Hogyan lehet hozzáférni az XLOOKUP szolgáltatáshoz?

Az XLOOKUP jelenleg csak az Office 365 felhasználói számára érhető el.

Tehát, ha az Excel korábbi verzióit használja (2010/2013/2016/2019), akkor nem fogja tudni használni ezt a funkciót.

Nem vagyok biztos abban sem, hogy ezt valaha is kiadják-e korábbi verziókhoz, vagy sem (talán a Microsoft létrehozhat egy kiegészítőt a Power Queryhez hasonló módon). De egyelőre csak akkor használhatja, ha Office 365 -en van.

Kattintson ide az Office 365 -re történő frissítéshez

Ha már használja az Office 365 -öt (otthoni, személyes vagy egyetemi kiadás), és nem fér hozzá, akkor lépjen a Fájl fülre, majd kattintson a Fiók elemre.

Lenne egy Office Insider program, és rákattinthat, és csatlakozhat az Office Insider Programhoz. Így hozzáférhet az XLOOKUP funkcióhoz.

Azt várom, hogy az XLOOKUP hamarosan elérhető lesz az összes Office 365 verzióban.

Megjegyzés: Az XLOOKUP elérhető az Office 365 for Mac és az Excel for Web (Excel online) számára is

XLOOKUP funkció szintaxisa

Az alábbiakban az XLOOKUP függvény szintaxisa látható:

= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Ha a VLOOKUP -ot használta, észre fogja venni, hogy a szintaxis meglehetősen hasonló, természetesen néhány fantasztikus kiegészítő funkcióval.

Ne aggódjon, ha a szintaxis és az érvelés túl soknak tűnik. Ezeket néhány egyszerű XLOOKUP példával fedem le az oktatóanyag későbbi részében, amelyek kristálytisztává teszik.

Az XLOOKUP függvény 6 érvet tartalmazhat (3 kötelező és 3 opcionális):

1. lookup_value - az Ön által keresett érték
2. lookup_array - az a tömb, amelyben a keresési értéket keresi
3. return_array - az a tömb, amelyből le szeretné kérni és visszaadni az értéket (megfelel annak a pozíciónak, ahol a keresési érték megtalálható)
4. [if_not_found] - a visszaadandó érték, ha a keresési érték nem található. Ha nem adja meg ezt az érvet, akkor egy #N/A hiba kerül visszaadásra
5. [match_mode] - Itt adhatja meg a kívánt egyezés típusát:
   • 0 - Pontos egyezés, ahol a lookup_value értéknek pontosan meg kell egyeznie a lookup_array értékével. Ez az alapértelmezett opció.
   • -1 - A pontos egyezést keresi, de ha megtalálta, akkor a következő kisebb tételt/értéket adja vissza
   • 1 - A pontos egyezést keresi, de ha megtalálta, akkor a következő nagyobb tételt/értéket adja vissza
   • 2 - Részleges egyeztetés helyettesítő karakterek használatával (* vagy ~)
6. [keresési mód] - Itt adhatja meg, hogy az XLOOKUP függvény hogyan keresse a lookup_array -t
   • 1 - Ez az alapértelmezett opció, amikor a függvény elkezdi keresni a lookup_value értéket felülről (első elem) és alulról (utolsó elem) a lookup_array -ben
   • -1 - Alulról felfelé halad a keresés. Hasznos, ha az utolsó egyező értéket szeretné megtalálni a lookup_array -ben
   • 2 - Bináris keresést végez, ahol az adatokat növekvő sorrendben kell rendezni. Ha nincs rendezve, ez hibát vagy rossz eredményeket eredményezhet
   • -2 - Bináris keresést végez, ahol az adatokat csökkenő sorrendben kell rendezni. Ha nincs rendezve, ez hibát vagy rossz eredményeket eredményezhet

Példák az XLOOKUP funkciókra

Most térjünk át az érdekes részre - néhány praktikus XLOOKUP példa.

Ezek a példák segítenek abban, hogy jobban megértse, hogyan működik az XLOOKUP, hogyan különbözik a VLOOKUP -tól és az INDEX/MATCH -tól, valamint ennek a funkciónak néhány fejlesztését és korlátozását.

Kattintson ide a mintafájl letöltéséhez, és kövesse

1. példa: Keressen le egy keresési értéket

Tegyük fel, hogy rendelkezik a következő adatkészlettel, és le szeretné kérni Greg matematikai pontszámát (a keresési érték).

Az alábbi képlet ezt teszi:

= XLOOKUP (F2, A2: A15, B2: B15)

A fenti képletben éppen a kötelező argumentumokat használtam, ahol a nevet keresi (felülről lefelé), megtalálja a pontos egyezést, és visszaadja a megfelelő értéket a B2: B15 -ből.

Az XLOOKUP és a VLOOKUP függvény egyik nyilvánvaló különbsége a keresési tömb kezelésének módja. A VLOOKUP-ban a teljes tömb megvan, ahol a keresési érték a bal szélső oszlopban található, majd adja meg az oszlop számát, ahonnan le szeretné kérni az eredményt. Az XLOOKUP viszont lehetővé teszi a lookup_array és a return_array külön kiválasztását

Az azonnali előny, hogy a lookup_array és a return_array külön argumentumként szerepel, azt jelenti, hogy most már megteheti nézz balra. A VLOOKUP -nak volt ez a korlátozása, ahol csak felnézhet, és jobbra eső értéket találhat. De az XLOOKUP esetében ez a korlátozás megszűnt.

Íme egy példa. Ugyanaz az adatkészletem van, ahol a név a jobb oldalon, a return_range pedig a bal oldalon található.

Az alábbiakban bemutatjuk a képletet, amellyel lekérhetem Greg pontszámát a matematikában (ami azt jelenti, hogy a lookup_value bal oldalára kell nézni)

= FELVÁRÁS (F2, D2: D15, A2: A15)

Az XLOOKUP egy másik fontos problémát old meg - Ha új oszlopot szúr be, vagy oszlopokat mozgat, a kapott adatok továbbra is helyesek. A VLOOKUP valószínűleg tönkremegy, vagy helytelen eredményt ad olyan esetekben, amikor az oszlopindex-érték legtöbbször keményen van kódolva.

2. példa: Keressen és kérjen le egy teljes rekordot

Vegyük példaként ugyanazokat az adatokat.

Ebben az esetben nem csak Greg pontszámát akarom lekérni a matematikából. Minden tantárgyból meg akarom kapni a pontszámokat.

Ebben az esetben az alábbi képletet használhatom:

= XLOOKUP (F2, A2: A15, B2: D15)

A fenti képlet egy return_array tartományt használ, amely több, mint egy oszlop (B2: D15). Tehát amikor a keresési érték megtalálható az A2: A15 -ben, a képlet a return_array teljes sorát adja vissza.

Ezenkívül nem törölheti csak azokat a cellákat, amelyek a tömb részét képezik, és amelyek automatikusan feltöltésre kerültek. Ebben a példában nem törölheti a H2 -t vagy az I2 -t. Ha megpróbálná, semmi sem történne. Ha kijelöli ezeket a cellákat, a képlet a képsávon szürkén jelenik meg (jelezve, hogy nem módosítható)

Törölheti a képletet a G2 cellából (ahová eredetileg beírtuk), ez törli a teljes eredményt.

Ez hasznos fejlesztés, mint korábban a VLOOKUP esetében, minden oszlop számát külön kell megadni.

3. példa: Kétirányú keresés az XLOOKUP (vízszintes és függőleges keresés) használatával

Az alábbiakban egy adathalmaz található, ahol szeretném tudni Greg pontszámát a matematikában (a tárgy a G2 cellában).

Ezt meg lehet tenni egy kétirányú kereséssel, ahol a nevet keresem az A oszlopban, és a tárgy nevét az 1. sorban. Ennek a kétirányú keresésnek az az előnye, hogy az eredmény független a tantárgy nevének hallgatói nevétől. Ha a tárgy nevét kémiára változtatom, ez a kétirányú XLOOKUP képlet továbbra is működik, és megadja a helyes eredményt.

Az alábbi képlet elvégzi a kétirányú keresést és megadja a helyes eredményt:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))

Ez a képlet beágyazott XLOOKUP -ot használ, ahol először azt használom, hogy lekérjem a tanuló összes jegyét az F2 cellában.

Tehát az XLOOKUP (F2, A2: A15, B2: D15) eredménye {21,94,81}, ami ebben az esetben Greg által pontozott tömb.

Ezt ismét a külső XLOOKUP képletben használják visszatérési tömbként. A külső XLOOKUP képletben keresem a tárgy nevét (ami a G1 cellában van), és a keresési tömb B1: D1.

Ha a tárgy neve Math, ez a külső XLOOKUP képlet lekéri az első értéket a visszatérési tömbből - ami ebben a példában {21,94,81}.

Ez ugyanazt teszi, mint amit eddig az INDEX és a MATCH kombinációval értek el

Kattintson ide a mintafájl letöltéséhez, és kövesse

4. példa: Ha a keresési érték nem található (hiba kezelés)

A hibakezelést most hozzáadtuk az XLOOKUP képlethez.

Az XLOOKUP függvény negyedik argumentuma az [if_not_found], ahol megadhatja, hogy mit szeretne, ha a keresés nem található.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, ahol meg szeretné kapni a matematikai pontszámot arra az esetre, ha egyezik, és ha a név nem található, akkor vissza kell térnie - „Nem jelent meg”

Az alábbi képlet ezt teszi:

= XLOOKUP (F2, A2: A15, B2: B15, "Nem jelent meg")

Ebben az esetben keményen kódoltam, hogy mit szeretnék kapni, ha nincs egyezés. Használhat cellahivatkozást cellára vagy képletre is.

5. példa: Beágyazott XLOOKUP (keresés több tartományban)

Az [if_not_found] argumentum zsenialitása az, hogy lehetővé teszi a használatát beágyazott XLOOKUP képlet.

Tegyük fel például, hogy két külön listája van, amint az alább látható. Míg ez a két táblázat ugyanazon a lapon van, ezeket külön lapokban vagy akár munkafüzetekben is megtalálhatja.

Az alábbiakban a beágyazott XLOOKUP képlet található, amely ellenőrzi a nevet mindkét táblában, és visszaadja a megfelelő értéket a megadott oszlopból.

= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))

A fenti képletben az [if_not_found] argumentumot használtam egy másik XLOOKUP képlet használatához. Ez lehetővé teszi a második XLOOKUP hozzáadását ugyanabban a képletben, és két tábla beolvasását egyetlen képlettel.

Nem tudom, hány egymásba ágyazott XLOOKUP -ot használhat egy képletben. 10 -ig próbáltam, és működött, aztán feladtam 🙂

6. példa: Keresse meg az utolsó egyező értéket

Erre nagy szükség volt, és az XLOOKUP lehetővé tette ezt. Most már nem kell összevissza módokat keresnie, hogy a tartomány utolsó egyező értékét megkapja.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és szeretné ellenőrizni, hogy mikor vették fel utoljára az egyes részlegeket, és mi volt a kölcsönzés dátuma.

Az alábbi képlet megkeresi az egyes osztályok utolsó értékét, és megadja az utolsó bérlet nevét:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)

És az alábbi képlet megadja az egyes részlegek utolsó bérletének bérleti dátumát:

= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)

Mivel az XLOOKUP beépített funkcióval rendelkezik, amely megadja a keresés irányát (elsőtől az utolsóig vagy az utolsótól az elsőig), ez egy egyszerű képlettel történik. Függőleges adatok esetén a VLOOKUP és az INDEX/MATCH mindig felülről lefelé néz, de az XLOOKUP funkcióval és lentről felfelé is megadhatja az irányt.

Példa 7: Hozzávetőleges egyezés az XLOOKUP -tal (Adókulcs keresése)

Egy másik figyelemre méltó fejlesztés az XLOOKUP -nál az, hogy most négy egyezési mód létezik (a VLOOKUP 2, a MATCH 3).

A négy argumentum bármelyikével megadhatja a keresési érték illesztésének módját:

• 0 - Pontos egyezés, ahol a lookup_value értéknek pontosan meg kell egyeznie a lookup_array értékével. Ez az alapértelmezett opció.
• -1 - A pontos egyezést keresi, de ha megtalálta, akkor a következő kisebb tételt/értéket adja vissza
• 1 - A pontos egyezést keresi, de ha megtalálta, akkor a következő nagyobb tételt/értéket adja vissza
• 2 - Részleges egyeztetés helyettesítő karakterek használatával (* vagy ~)

De a legjobb az, hogy nem kell aggódnia, hogy az adatok növekvő vagy csökkenő sorrendben vannak -e rendezve. Még akkor is, ha az adatok nincsenek rendezve, az XLOOKUP gondoskodik róla.

Az alábbiakban van egy adatkészletem, ahol meg akarom találni az egyes személyek jutalékát - és a jutalékot a jobb oldali táblázat segítségével kell kiszámítani.

Az alábbiakban ezt a képletet találja meg:

= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2

Ez egyszerűen az értékesítési értéket használja keresésként, és átnézi a jobb oldali keresési táblázatot. Ebben a képletben -1 -et használtam ötödik argumentumként ([match_mode]), ami azt jelenti, hogy pontos egyezést fog keresni, és ha nem talál egyet, akkor csak a keresési értéknél kisebb értéket adja vissza .

És ahogy mondtam, nem kell aggódnia, hogy az adatok rendezése nem.

Kattintson ide a mintafájl letöltéséhez, és kövesse

8. példa: Vízszintes keresés

Az XLOOKUP függőleges és vízszintes keresést is végezhet.

Az alábbiakban van egy adatkészletem, amelyben a diákok nevei és azok pontszáma szerepel a sorokban, és szeretném lekérni a név pontszámát a B7 cellában.

Az alábbi képlet ezt teszi:

= ELVÁRÁS (B7, B1: O1, B2: O2)

Ez nem más, mint egy egyszerű keresés (hasonló az 1. példában látottakhoz), de vízszintes.

Az összes példa, amelyet a függőleges keresésre vonatkozóan kitérek, vízszintes kereséssel is elvégezhető az XLOOKUP használatával (búcsú a VLOOKUP -tól és a HLOOKUP -tól).

9. példa: Feltételes keresés (XLOOKUP használata más képletekkel)

Ez egy kissé fejlett példa, és bemutatja az XLOOKUP erejét is, amikor összetett kereséseket kell végeznie.

Az alábbiakban egy adathalmaz található, ahol megvannak a diákok nevei és azok pontszámai, és szeretném tudni annak a diáknak a nevét, aki minden tantárgyból a maximumot érte el, és azoknak a tanulóknak a számát, akik 80 -nál többet értek el minden tárgyban.

Az alábbi képlet megadja az egyes tantárgyakban a legmagasabb pontszámot kapott diák nevét:

= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A 2 USD: 15 USD)

Mivel az XLOOKUP segítségével vissza lehet küldeni egy egész tömböt, ezért arra használtam, hogy először megszerezzem a kívánt tárgy összes jelét.

Például a Math esetében az XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15) használata esetén az összes matematikai eredményt megadja. Ezután a MAX funkcióval megkereshetem a maximális pontszámot ebben a tartományban.

Ez a maximális pontszám lesz a keresési értékem, és a keresési tartomány az XLOOKUP által visszaadott tömb (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Ezt egy másik XLOOKUP képletben használom annak a diáknak a nevének lekéréséhez, aki a maximális pontszámot érte el.

A 80 -nál több pontot elérő tanulók számolásához használja az alábbi képletet:

= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")

Ez egyszerűen az XLOOKUP képletet használja az adott tárgy összes értékének tartományának lekéréséhez. Ezután COUNTIF függvénybe csomagolja, hogy megkapja a 80 -nál nagyobb pontszámot.

10. példa: helyettesítő karakter használata az XLOOKUP -ban

Ahogy helyettesítő karaktereket is használhat a VLOOKUP és a MATCH alkalmazásban, ezt az XLOOKUP segítségével is megteheti.

De van különbség.

Az XLOOKUP -ban meg kell adnia, hogy helyettesítő karaktereket használ (az ötödik argumentumban). Ha ezt nem adja meg, az XLOOKUP hibát jelez.

Az alábbiakban egy adathalmaz található, ahol megvannak a vállalatok nevei és piaci kapitalizációjuk.

Keresni szeretnék egy cégnevet a D oszlopban, és lekérni a piaci kapitalizációt a bal oldali táblázatból. És mivel a D oszlopban szereplő nevek nem egyeznek pontosan, helyettesítő karaktereket kell használnom.

Az alábbiakban ezt a képletet találja meg:

= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)

A fenti képletben korábban csillag (*) helyettesítő karaktert használtam, mint a D2 után (dupla idézőjelek között kell lennie, és a D2 -vel az ampersand használatával kell összekapcsolni).

Ez azt mondja a képletnek, hogy nézze át az összes cellát, és ha a szót tartalmazza a D2 cellában (ami Apple), akkor tekintse pontos egyezésnek. Nem számít, hány és milyen karakter van a D2 cellában lévő szöveg előtt és után.

Annak érdekében, hogy az XLOOKUP elfogadja a helyettesítő karaktereket, az ötödik argumentum értéke 2 (helyettesítő karakter egyezés).

11. példa: Keresse meg az utolsó értéket az oszlopban

Mivel az XLOOKUP lehetővé teszi a keresést alulról felfelé, könnyen megtalálhatja a lista utolsó értékét, valamint lekérheti a megfelelő értéket egy oszlopból.

Tegyük fel, hogy rendelkezik egy adathalmazzal, amint az alább látható, és szeretné tudni, hogy mi az utolsó vállalat, és mi ennek az utolsó vállalatnak a piaci kapitalizációja.

Az alábbi képlet megadja az utolsó cég nevét:

= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)

És az alábbi képlet megadja a lista utolsó vállalatának piaci sapkáját:

= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)

Ezek a képletek ismét helyettesítő karaktereket használnak. Ezekben csillagot (*) használtam keresési értékként, ami azt jelenti, hogy ez az első cellát, amellyel találkozik, pontos egyezésnek tekinti (mivel csillag lehet bármilyen karakter és tetszőleges számú karakter).

És mivel az irány alulról felfelé halad (a függőlegesen elrendezett adatokhoz), a lista utolsó értékét adja vissza.

A második képlet pedig külön return_range -t használ a vezetéknév piaci sapkájának meghatározásához a listában.

Kattintson ide a mintafájl letöltéséhez, és kövesse

Mi van, ha nincs XLOOKUP szolgáltatása?

Mivel az XLOOKUP valószínűleg csak az Office 365 felhasználók számára lesz elérhető, ennek egyik módja az Office 365 -re frissítés.

Ha már rendelkezik Office 365 Home, Personal vagy University kiadással, akkor már hozzáférhet az XLOOKUP szolgáltatáshoz. Csak annyit kell tennie, hogy csatlakozik az Office Insider programhoz.

Ehhez lépjen a Fájl fülre, kattintson a Fiók, majd az Office bennfentes lehetőségre. Lehetőség lenne csatlakozni a bennfentes programhoz.

Ha más Office 365 -előfizetéssel rendelkezik (például Enterprise), biztos vagyok benne, hogy az XLOOKUP és más fantasztikus funkciók (például dinamikus tömbök, képletek, mint a SORT és a FILTER) hamarosan elérhetők lesznek.

Abban az esetben, ha az Excel 2010/2013/2016/2019 alkalmazást használja, akkor nem lesz XLOOKUP, és továbbra is a VLOOKUP, HLOOKUP és INDEX/MATCH kombinációt kell használnia a keresési képletek legjobb kihasználásához.

XLOOKUP Visszafelé kompatibilitás

Ez az egyik dolog, amire vigyáznia kell - az XLOOKUP az NEM visszafelé kompatibilis.

Ez azt jelenti, hogy ha létrehoz egy fájlt, és használja az XLOOKUP képletet, majd olyan verzióban nyitja meg, amely nem rendelkezik XLOOKUP -tal, akkor hibákat jelenít meg.

Mivel az XLOOKUP hatalmas előrelépés a helyes irányba, úgy gondolom, hogy ez lesz az alapértelmezett keresési képlet, de biztosan eltart néhány évig, mielőtt széles körben elfogadják. Végül is még mindig látok néhány embert, akik Excel 2003 -at használnak.

Tehát ez a 11 XLOOKUP példa, amelyek segítségével gyorsabban elvégezheti az összes keresési és hivatkozási feladatot, és megkönnyítheti a használatát is.

Remélem, hasznosnak találta ezt az oktatóanyagot!