Excel 2020: Az XLOOKUP tizenkét előnye - Excel tippek

Az új XLOOKUP funkció 2019 novemberétől indul az Office 365-ben. Joe McDaid, az Excel csapata az XLOOKUP-ot azért tervezte, hogy egyesítse a VLOOKUP-ot és az INDEX / MATCH-ot használókat. Ez a szakasz az XLOOKUP 12 előnyét tárgyalja:

  1. A pontos egyezés az alapértelmezett.
  2. A VLOOKUP egész szám alapú harmadik argumentuma ma már megfelelő hivatkozás.
  3. Az IFNA a hiányzó értékek kezelésére van beépítve.
  4. Az XLOOKUP-nak nincs problémája balra haladni.
  5. Keresse meg a következő-kisebb vagy a következő-nagyobb mérkőzést az asztal rendezése nélkül.
  6. Az XLOOKUP elvégzi a HLOOKUP funkciót.
  7. Alulról keresve keresse meg az utolsó mérkőzést.
  8. A helyettesítő karakterek alapértelmezés szerint "ki vannak kapcsolva", de visszakapcsolhatja őket.
  9. Tegye vissza mind a 12 hónapot egyetlen képlettel.
  10. Visszaadhat egy cellahivatkozást, ha az XLOOKUP egy kettőspont mellett van, például XLOOKUP (); XLOOKUP ()
  11. Kétirányú mérkőzést végezhet, mint az INDEX (, MATCH, MATCH).
  12. Összefoglalhatja az összes keresést egyetlen képletben, mint amilyen a LOOKUP lehet.

Itt található a szintaxis: = XLOOKUP (Keresési_érték, Keresési_Array, Eredmények_Array, (ha_nem_találkozott), (egyezési_mód), (keresési_mód)).

XLOOKUP 1. előny: Alapértelmezés szerint pontos egyezés

A VLOOKUP képletem 99% -a a FALSE vagy 0 értékre végződik, hogy pontos egyezést jelezzen. Ha mindig a VLOOKUP pontos egyezési verzióját használja, akkor elkezdheti a match_mode kikapcsolását az XLOOKUP funkcióból.

A következő ábrán a W25-6-ot keresi fel az A4-es cellából. Meg akarja keresni az elemet az L8: L35-ben. Ha megtalálta, a megfelelő árat az N. oszlopból szeretné megkapni. Nem szükséges megadni a False értéket a match_mode módnak, mert az XLOOKUP alapértelmezés szerint pontos egyezést ad meg.

XLOOKUP az érték A4-ben. Lásd: L8: L35. Adja vissza az N8: N35 megfelelő árát.

XLOOKUP 2. előny: Az Results_Array hivatkozás egész szám helyett

Gondoljon a VLOOKUP képletre, amelyet az XLOOKUP előtt használna. A harmadik érv 3 lett volna, jelezve, hogy vissza szeretné adni a 3. oszlopot. Mindig fennállt a veszélye, hogy egy tanácstalan munkatárs beillesztett (vagy törölt) egy oszlopot a táblázatba. A táblázat egy további oszlopával a VLOOKUP, amely árat adott vissza, megkezdi a leírás visszaadását. Mivel az XLOOKUP egy cellahivatkozásra mutatott, a képlet átírja magát, hogy folyamatosan az O oszlopban szereplő árra mutasson.

A régi VLOOKUP nem fog sikerülni, ha valaki új oszlopot illeszt be a keresőtáblába. Az XLOOKUP folyamatosan működik.

XLOOKUP 3. előny: Az IFNA opcionális argumentumként van beépítve

A rettegett # N / A hiba akkor jelenik meg, ha a keresési érték nem található a táblázatban. Régebben a # N / A helyettesítésére valami mással IFERROR-t vagy IFNA-t kellett használnia a VLOOKUP köré.

Ha egy elem nem található, akkor a # N / A értéket adja vissza a VLOOKUP vagy az XLOOKUP fájlból …

Rico javaslatának köszönhetően a YouTube-csatornámon az Excel csapata beépített egy opcionális negyedik argumentumot az if_not_found kifejezésre. Ha ezeket a # N / A hibákat nullával kívánja lecserélni, egyszerűen adja hozzá a 0-t negyedik argumentumként. Vagy használhat valamilyen szöveget, például "Az érték nem található".

Az XLOOKUP opcionális negyedik argumentuma "ha nem található". Helyezzen oda 0-t vagy "Nem található" -t.

XLOOKUP 4. előny: Nem probléma a kulcsmező bal oldalára nézni

A VLOOKUP nem nézhet a kulcsmező bal oldalán a VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False) igénybevétele nélkül. Az XLOOKUP használatával nincs probléma, ha az Eredmények_array a Keresés_array bal oldalán található.

Az XLOOKUP használatával nincs probléma a kategória visszaküldésével az F oszlopból, miközben a G oszlopban keresi a cikkszámokat. Ez mindig a VLOOKUP gyengesége volt: nem tudott balra nézni.

XLOOKUP 5. előny: Következő-kisebb vagy következő-nagyobb mérkőzés válogatás nélkül

A VLOOKUP-nak lehetősége volt megkeresni a pontos egyezést vagy csak kisebb értéket. Vagy hagyhatja a negyedik argumentumot a VLOOKUP-ból, vagy megváltoztathatja a False-t True-ra. Ahhoz, hogy ez működjön, a keresőtáblát növekvő sorrendben kellett rendezni.

Példa a VLOOKUP hozzávetőleges egyezés verziójára. Minden 10 ezer és 20 ezer közötti eladás 12 dolláros bónuszt kap.

De a VLOOKUP nem tudta visszaadni a pontos egyezést vagy a következő nagyobb elemet. Ehhez át kellett váltania a MATCH -1-es használatára match_mode-ként, és vigyáznia kellett arra, hogy a keresési táblázat csökkenő sorrendben legyen.

Az XLOOKUP opcionális ötödik argumentuma match_mode csak a pontos egyezést keresi, egyenlő vagy csak kisebb, egyenlő vagy éppen nagyobb. Vegye figyelembe, hogy az XLOOKUP értékei értelmesebbek, mint a MATCH-ban:

  • -1 az értéket egyenlőnek vagy éppen kisebbnek találja
  • 0 találjon pontos egyezést
  • Az 1 az értéket egyenlőnek vagy éppen nagyobbnak találja.

De a legcsodálatosabb rész: a keresési táblázatot nem kell rendezni, és minden match_mode működni fog.

Az alábbiakban a -1 match_mode -1 alapján keresse meg a következő kisebb elemet.

Az XLOOKUP ötödik argumentuma a Match_Mode. A 0 a pontos egyezés. A negatív az Exact Match vagy a Next Smaller elemhez használható. Az 1. pozitív a pontos egyezésre vagy a következő nagyobb tételre vonatkozik. 2 helyettesítő helyettesítő. Annak tükrözése érdekében, hogy mit tenne a VLOOKUP with True a negyedik argumentumban, tegyen egy negatív értéket az match_mode argumentumként az XLOOKUP-ba.

Itt az 1-es match_mode 1 megtalálja, hogy milyen járműre van szükség, a pártban résztvevők számától függően. Ne feledje, hogy a keresési táblázatot nem utasok szerint rendezik, és a jármű neve a kulcs bal oldalán található.

Az XLOOKUP tehet valamit, amit a VLOOKUP nem tudott: megtalálni a pontos egyezést, vagy csak nagyobbat. Ebben az esetben egy utazási társaság rendelkezik a foglalások listájával. Az utasok száma alapján a keresési táblázat megmutatja, hogy milyen járműre van szüksége az emberek számára.

A táblázat a következőket mondja:

  • A busz 64 embert tud befogadni
  • Az autó 4 fő befogadására alkalmas
  • A Motorcyle 1 fő befogadására alkalmas
  • A Tour Van 12 fő befogadására alkalmas
  • Van 6 ember befogadására alkalmas.

Bónuszként az adatokat jármű szerint rendezik (a régi megoldásban, a MATCH használatával, a táblázatot Kapacitás szerinti csökkenő sorrendben kellene rendezni. Továbbá: A Jármű a Kapacitás bal oldalán található.

XLOOKUP 6. előny: Oldalt az XLOOKUP helyettesíti a HLOOKUP-ot

A lookup_array és results_array vízszintes lehet az XLOOKUP használatával, így egyszerű a HLOOKUP cseréje.

Itt a keresőtábla vízszintes. Korábban ehhez HLOOKUP kellett volna, de az XLOOKUP képes egy olyan táblával foglalkozni, amely oldalra megy.

XLOOKUP 7. előny: Alulról keresse a legújabb mérkőzést

Van egy régi videónk a YouTube-on, amely egy brit lótelep kérdésére válaszol. Volt járműparkjuk. Valahányszor bejött egy jármű üzemanyag vagy szerviz céljából, táblázatba vitte a járművet, a dátumot és a futásteljesítményt. Meg akarták találni az egyes járművek legújabb ismert futásteljesítményét. Míg az Excel-2017 korszak MAXIFS megoldhatja ezt ma, a sok évvel ezelőtti megoldás egy arculatos képlet volt, amely a LOOKUP-ot használta, és magában foglalta a nullával való felosztást.

Ma az XLOOKUP opcionális hatodik argumentuma lehetővé teszi, hogy a keresést az adatkészlet aljától kell kezdeni.

Keresse meg a listában az utolsó mérkőzést.

jegyzet

Bár ez nagyszerű javulás, csak az első vagy utolsó meccs megtalálását teszi lehetővé. Néhányan azt remélték, hogy ez lehetővé teszi a második vagy a harmadik találatot, de ez nem a keresés_mód argumentum célja.

Vigyázat

A fenti ábra azt mutatja, hogy vannak keresési módok a régi bináris keresést használva. Joe McDaid nem javasolja ezek használatát. Először is, a továbbfejlesztett keresési algoritmus 2018-tól elég gyors ahhoz, hogy ne legyen jelentős sebességelőny. Másodszor azt kockáztatja, hogy egy tanácstalan munkatárs válogatja a keresési táblázatot és rossz válaszokat vezet be.

XLOOKUP 8. előny: A helyettesítő karakterek alapértelmezés szerint "ki vannak kapcsolva"

A legtöbb ember nem vette észre, hogy a VLOOKUP a csillagot, a kérdőjelet és a tildet helyettesítő karakterként kezeli, amint azt a "# 51 Helyettesítő karakter használata a VLOOKUP-ban" című rész (143. oldal) leírja. Az XLOOKUP használatakor a helyettesítő karakterek alapértelmezés szerint ki vannak kapcsolva. Ha azt szeretné, hogy az XLOOKUP ezeket a karaktereket helyettesítő karakterként kezelje, használja a 2-t Match_Mode-ként.

Nagyon kevesen vették észre, hogy a VLOOKUP a keresési értékben szereplő csillagokat helyettesítő karakterként kezeli. Alapértelmezés szerint az XLOOKUP nem használ helyettesítő karaktereket, de kényszerítheti a VLOOKUP viselkedésére, ha 2-es egyezési módot használ: Helyettesítő karakteres egyezés.

XLOOKUP 9. előny: Mind a 12 hónapot adja vissza egyetlen formulában!

Ez valóban a Dynamic Arrays előnye, de ez a kedvenc okom az XLOOKUP szeretetére. Ha a keresés során vissza kell térnie mind a 12 hónapra, akkor a B6-ba bevitt egyetlen képlet téglalap alakú return_array-vel több eredményt ad. Ezek az eredmények a szomszédos sejtekbe kerülnek.

Az alábbi ábrán a B7-be beírt egyetlen képlet adja vissza a B7-ben bemutatott mind a 12 választ: M7.

Egyetlen XLOOKUP a januári oszlopban január és december közötti számokkal tér vissza. Ez úgy történik, hogy megad egy 12 oszlopos eredmény_array-t.

XLOOKUP 10. előny: Visszaadhat egy cellahivatkozást, ha a kettőspont mellett van

Ez összetett, de gyönyörű. Korábban hét olyan függvény volt, amely a cellaérték visszatéréséről a cella referencia visszaadására változott, ha a funkció kettőspontot ért. Például lásd: A2: INDEX () használata nem felejtő OFFSET-ként. Az XLOOKUP a nyolcadik funkció ennek a viselkedésnek a felajánlásához, csatlakozva a KIVÁLASZTÁS, IF, IFS, INDEX, INDIRECT, OFFSET és SWITCH kapcsolatokhoz.

Tekintsük a következő ábrát. Valaki válassza az E4-ben a Cherry-t és az E5-ben a Fig-t. Olyan képletet szeretne, amely a B6-tól B9-ig mindent összegez.

Az ábra két XLOOKUP képletet mutat két cellában. Az első 15-öt ad vissza a B6 cellából. A második 30-at újból a B9-ből hajt. De akkor egy harmadik cellában van egy képlet, amely kettősponttal egyesíti a két XLOOKUP képletet, majd ezt egy SUM függvénybe burkolja. Az eredmény a B6: B9 SUMMA, mert az XLOOKUP visszaadhat egy cellahivatkozást, ha a függvény megjelenik egy operátor mellett, például kettőspont. Annak bizonyítására, hogy ez működik, a következő néhány ábra ezt a képletet mutatja be a Formula értékelése párbeszédpanelen.

A fenti ábrán láthatja, hogy az E4 XLOOKUP-ja a B6 cellából adja vissza a 15-öt. Az E5 XLOOKUP-ja a 30-at adja vissza a B9-ből. Ha azonban a D9 és D10 cellákból kiveszi a két XLOOKUP függvényt, és kettősponttal összerakja őket, akkor az XLOOKUP viselkedése megváltozik. 15 helyett az első XLOOKUP adja vissza a B6 cellacímet!

Ennek bizonyítására a D7-et választottam, és a Képletek, a Képlet értékelése elemet használtam. Miután kétszer megnyomta az Evaluate gombot, a következő kiszámítandó rész az XLOOKUP ("Cherry", A4: A29, B4: B29), az itt látható módon.

Ez megmutatja a Képlet értékelése párbeszédpanelt közvetlenül az első XLOOKUP értékelése előtt. Ez az XLOOKUP közvetlenül egy kettőspont előtt jelenik meg.

Nyomja meg ismét az Értékelés gombot és csodálatosan, az XLOOKUP képlet $ B $ 6-ot ad vissza a B6-ban tárolt 15 helyett. Ez azért történik, mert egy kettőspont közvetlenül követi ezt az XLOOKUP képletet.

Kattintson az Értékelés gombra, és az első XLOOKUP 15 helyett $ B $ 6-ot ad vissza.

Nyomja meg még kétszer az Értékelés gombot, és az időközi képlet = SUM (B6: B9) lesz.

A második XLOOKUP kiértékelése után az időközi képlet = SUM (B6: B9).

Ez csodálatos viselkedés, amelyről a legtöbb ember nem tud. Charles Williams Charles Williams elmondja, hogy az XLOOKUP mellett a három operátor bármelyikével kiváltható:

  • Kettőspont
  • Tér (kereszteződés operátor)
  • Vessző (uniós operátor)

XLOOKUP 11. előny: Kétirányú mérkőzés, például INDEX (, MATCH, MATCH)

Az összes VLOOKUP barátomnál az INDEX / MATCH emberek arra vártak, hogy vajon az XLOOKUP képes-e kezelni egy kétirányú mérkőzést. A nagy hír: meg tudja csinálni. Rossz hír: a módszertan kicsit más, mint az INDEX / MATCH rajongók elvárják. Lehet, hogy kicsit a fejük fölött van. De biztos vagyok benne, hogy meg tudják találni ezt a módszert.

Kétirányú mérkőzéshez meg kell találni, hogy melyik sor tartalmazza a J3-ban látható A621 számlaszámot. Tehát, az XLOOKUP elég könnyen indul: = XLOOKUP (J3, A5: A15. De akkor meg kell adnod a result_array elemet. Használhatja ugyanazt a trükköt, mint az XLOOKUP 9. előnyben: Mind a 12 hónapot adja vissza a fenti egyetlen képletben, de függőleges vektor visszaadására szolgál. Egy belső XLOOKUP a J4 hónapot keresi a B4: G4 hónap fejlécében. A return_array értéke B5: G15. Ennek eredményeként a belső XLOOKUP olyan tömböt ad vissza, mint az I10-ben. : I20 lentebb. Mivel az A621 a keresési_array ötödik cellájában található, a 104 pedig az result_array ötödik cellájában található, a képletről kapja meg a helyes választ. Az alábbiakban a J6 a régi utat mutatja. J7 az új utat adja vissza.

XLookup J3 az A5: A15 számlák listáján. Az Eredménytömbhöz használja az XLOOKUP (J4, B4: G4, B5: G15) elemet. Ebben a képletben a B4: G4 a hónapok listája. B5: A G15 az összes fiók összes téglalap alakú tömbje, minden hónapra. Egy másik cellában csak a belső XLOOKUP mutatja, hogyan adja vissza a májusi értékek teljes oszlopát.

XLOOKUP 12. előny: Összes képlet összesítése egyetlen képlettel

Az ősi LOOKUP funkció két furcsa trükköt kínált. Először is, ha megpróbálja kitalálni a felhalmozódó bónuszköltség teljes összegét, megkérheti a LOOKUP-ot, hogy egyetlen képlettel keresse meg az összes értéket. Az alábbi képen a LOOKUP (C4: C14 11 keresést végez). A LOOKUP funkció azonban nem kínált pontos egyezést, és a keresési táblázatot rendezni kellett.

Keressen 13 értéket, és összegezze azokat. Ez korábban a LOOKUP-tal működött, de az XLOOKUP-tal is. Adja meg az összes C4: C14 keresési értéket első argumentumként. Csomagolja be az XLOOKUP elemet egy SUM funkcióba.

Az XLOOKUP segítségével megadhat egy tartományt, mivel a keresési_érték és az XLOOKUP az összes választ megadja. Előnye, hogy az XLOOKUP pontos egyezéseket képes végrehajtani.

Az a trükk, hogy a LOOKUP használatával összesítik a keresési eredményeket, csak a Lookup hozzávetőleges egyezési verziójával működött. Itt az XLOOKUP pontosan egyezik az L4: L14 összes nevével, és az összes eredményt megszerzi.

Bónusztipp: Mi van a Twisted LOOKUP-mal?

Mike Girvin, az Excel MVP-je gyakran megmutatja a LOOKUP függvény trükkjét, ahol a Lookup_Vector függőleges és az Result_Vector vízszintes. Az XLOOKUP nem fogja natívan támogatni ezt a trükköt. De ha egy kicsit megcsal, és az eredmény_array-t a TRANSPOSE függvénybe csomagolja, akkor kezelheti a csavart keresést.

Itt a keresési tömb függőleges, az eredménytömb pedig vízszintes. A régi LOOKUP függvény képes kezelni ezt, de az XLOOKUP használatához mindkét tömböt be kell csomagolnia a TRANSPOSE fájlba.

érdekes cikkek...