1. SoftwareMicrosoft OfficeExcelHow az XLOOKUP funkció használata az Excel 2016 programban

Greg Harvey készítette

Az Excel 2016 Office 365 előfizetők számára Windows és Mac rendszereken most támogatja az új XLOOKUP funkciót, amelyet a nagyon népszerű (mégis gyakran rosszindulatú) vertikális keresési funkció, a VLOOKUP jelentősen egyszerűbb és sokoldalúbb helyettesítésére szántak (nem tudom, mi az XOOKUP programban található X). jelentése; eXtensive, talán?).

Azoknak, akik még nem ismerik a VLOOKUP-t (amelyet közvetlenül a SUM és az ÁTLAG után a harmadik leggyakrabban használt funkciónak tekintünk), ez a függvény függőlegesen sor szerint keresi a kijelölt keresési táblázat bal oldali oszlopában fentről lefelé, amíg egy értéket egy keresési oszlop, amelyet eltolás szám jelöl, amely megegyezik vagy meghaladja a keresett számot. Bár a VLOOKUP funkció rendkívül hasznos bizonyos tételek megtalálása a munkalap adattábláinak hosszú listájában vagy oszlopában, számos korlátozással rendelkezik, amelyeket az új keresési funkció, mint az XLOOKUP, nem oszt meg:

  • Alapértelmezés szerint a keresési értékhez pontosan illeszkedik a keresési tartományban Kereshet egy táblában függőlegesen (sor szerint) és vízszintesen (oszlop szerint), ezáltal felváltva a HLOOKUP funkció használatának szükségességét, amikor vízszintesen oszlop alapján keres Balra vagy jobbra is kereshet, hogy a keresési táblázatban a keresési tartománynak nem kell oszlopban elhelyezkednie a visszatérési tartománytól balra lévő oszlopban, hogy a funkció működjön A pontos egyezési alapértelmezés használatakor akkor is működik, ha a keresési tartomány értékei nem vannak rendezve bizonyos sorrendbe Kereshet az alsó sorból felfelé a keresési tömb tartományban, opcionális keresési mód argumentum segítségével

Az XLOOKUP függvénynek öt lehetséges argumentuma van, amelyek közül az első három kötelező, az utóbbi kettő pedig opcionális, a következő szintaxissal:

XLOOKUP (keresési_érték, tábla, return_array, [match_mode], [search_mode])

A szükséges lookup_value argumentum azt az értéket vagy elemet jelöli, amelyre keres. A szükséges look_up tömb argumentum a keresési érték keresésére szolgáló cellák tartományát, a return_array argumentum pedig a kívánt értéket tartalmazó cellák tartományát adja meg, amikor az Excel pontos egyezést talál.

* Ne feledje, amikor a lookup_array és a return_array argumentumokat jelöli ki az XLOOKUP funkcióban, mindkét tartománynak azonos hosszúságúnak kell lennie, különben az Excel visszaadja a #VALUE! hiba a képlethez. Ez annál is inkább az oka annak, hogy a kijelölt adattábla tartományneveit vagy oszlopneveit használja ezeknek az argumentumoknak a meghatározásakor, ahelyett, hogy rámutatnának, vagy beírnák a celláikat.

Az opcionális match_mode argumentum a következő négy érték bármelyikét tartalmazhatja:

  • 0 a pontos egyezéshez (az alapértelmezett, ugyanúgy, mint amikor a match_mode argumentumot nem jelölik ki) -1 a pontos egyezéshez vagy a következő alacsonyabb értékhez 1 a pontos egyezéshez vagy a következő nagyobb értékhez A 2. rész a részleges egyezéshez helyettesítő karaktereket használva, a cellába történő hivatkozáshoz csatlakoztatva a lookup_value argumentumban

Az opcionális search_mode argumentum a következő négy érték bármelyikét tartalmazhatja:

  • 1 az elsőtől az utolsóig keresni, azaz fentről lefelé (az alapértelmezett, ugyanaz, mint amikor a search_mode argumentumot nem jelölik ki) -1, hogy keressen utoljára, azaz alulról felfelé A 2. ábrán bináris keresés növekvő sorrendben található -2 bináris keresésre csökkenő sorrendben

Az új XLOOKUP funkció hatalmának és sokoldalúságának megértésének legjobb módja az, ha működés közben látja egy Excel munkalapon. A következő ábrán egy munkalap található egy egyszerű 2019-es értékesítési adattáblázattal, országonként elrendezve. Az XLOOKUP használatával visszatérítheti az E4 cellában ebből a táblázatból származó teljes értékesítést, a munkalap D4 cellájába megadott ország alapján, tegye a következőket:

  1. Helyezze a cella kurzort a munkalap E4 cellájába Kattintson a Képletek lapon a Kutatás és referencia opcióra, majd a legördülő menü alján található XLOOKUP elemre, hogy megnyissa a Funkció érvei párbeszédpanelt. Kattintson a munkalap D4 cellájára, hogy megadja a cellára vonatkozó hivatkozást a Lookup_value argumentum szövegmezőbe. A Tab gombbal válassza ki a Lookup_array argumentum szövegmezőt, majd kattintson az A4-es cellára, és tartsa lenyomva a Shift billentyűt, amikor a Ctrl lefelé mutató nyíllal megnyomja az A4: A8-at a keresendő tartományt (mivel az A3: B8 tartományt Excel adattáblázatként definiálták, Az 1. táblázat [Ország] jelenik meg a szövegmezőben az A4: A8 tartomány helyett). A Tab gombbal válassza ki a Return_array argumentum szövegmezőt, majd kattintson a B4 cellára, és tartsa lenyomva a Shift billentyűt, amikor a Ctrl lefelé mutató nyíllal megnyomja a B4: B8 értéket, amely a keresési eredmények alapján visszaadandó értékeket tartalmazza (ez a 1. táblázat [Teljes értékesítés] a szövegmezőben).

Kattintson az OK gombra, hogy beírja az XLOOKUP képletet az E4 cellába.

XLOOKUP képlet létrehozása

Az Excel beírja az XLOOKUP képletet a munkalap E4 cellájába, és ennek eredményeként 4900-at ad vissza, mivel Costa Rica jelenleg bekerül a D4 keresési cellába, és amint azt a 2019. évi értékesítési táblázatból láthatjuk, ez valóban az ezen az országban elért összes eladás.

Mivel az XLOOKUP jobbról balra működik, ugyanúgy, mint balról jobbra, akkor ezt a funkciót ugyanúgy használhatja, hogy egy adott eladási adatok alapján az országot visszatérjen ebből az értékesítési táblázatból. A következő ábra bemutatja, hogyan kell ezt csinálni. Ezúttal létrehozza az XLOOKUP képletet a D4 cellában, és az E4 cellába bevitt értéket jelzi (ebben az esetben 11 000) a lookup_value argumentumként.

Ezenkívül a match_mode argumentumként -1 ad meg, hogy felülbírálja a függvény pontos egyezésének alapértelmezését, így az Excel pontos országot ad vissza az E4 keresési cellába megadott eladási értékhez megadott országhoz, vagy az országhoz, ahol a következő alacsonyabb teljes értékesítés (Mexikó Ebben az esetben 10 000 USD, mivel ebben a táblázatban nincs olyan ország, amelynek teljes eladása 11 000 USD). Annak meghatározása nélkül, hogy a képlethez match_mode argumentumot adna, az Excel eredményként #NA értéket ad vissza, mert ebben az értékesítési táblázatban nincs pontos egyezés a 11 000 dollárral.

XLOOKUP képlet a D4-ben

Mivel az XLOOKUP funkció ugyanúgy kényelmes, ha vízszintesen, oszlop szerint keres, mint függőlegesen sorban keres, akkor felhasználhatja egy kétirányú keresést végző képlet létrehozására (helyettesítve az olyan képlet létrehozásának szükségességét, amely az INDEX és a MATCH funkciókat kombinálja, mint pl. a múltban). A következő ábra, amely tartalmazza a 2019-es gyártási ütemtervet az alkatrészszámok számára, az AB-100-tól az AB-103-ig április-december hónapokra, bemutatja, hogyan történik ez.

beágyazott XLOOKUP funkciók

A B12 cellában a következő képletet hoztam létre:

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

Ez a képlet egy XLOOKUP függvény meghatározásával kezdődik, amely függőlegesen sor szerint keresi a pontos egyezést a part_lookup cella (ebben az esetben a B10 cella) cellatartományában megadott alkatrész bejegyzéshez a termelési táblázat $ A $ 3: $ A $ 6 cellatartományában. . Vegye figyelembe azonban, hogy az eredeti LOOKUP függvény return_array argumentuma önmagában egy második XLOOKUP függvény.

Ez a második, egymásba ágyazott XLOOKUP függvény vízszintesen oszlop alapján keresi a $ B $ 2: $ J $ 2 cellatartományt a pontos_egység a date_lookup nevű cellában (ebben az esetben a B11 cella) tett pontos egyezéssel kapcsolatban. A return_array argumentum erre a második, beágyazott XLOOKUP függvényre $ B $ 3: $ J $ 6, a táblázatban szereplő összes termelési érték cellatartománya.

Ez a képlet úgy működik, hogy az Excel először egy vízszintes keresés elvégzésével kiszámítja a második, egymásba ágyazott XLOOKUP függvény eredményét, amely ebben az esetben a tömböt adja meg a Jun-19 oszlop D3: D6 cellájának tartományában (az alábbi értékekkel: 438, 153, 306 és 779). Ez az eredmény viszont a return_array argumentum lesz az eredeti XLOOKUP függvény számára, amely függőleges soronkénti keresést hajt végre a B11 cellában (part_lookup elnevezésű cikkszám) történő pontos egyezés érdekében. Mivel ebben a példában ez a part_lookup cella tartalmazza az AB-102-et, a képlet csak a Jun-19 306 termelési értéket adja vissza a második, következő XLOOKUP függvény eredményeként.

Tessék, itt van! Első pillantás az XLOOKUP-ra, egy erőteljes, sokoldalú és meglehetősen egyszerűen használható új keresési funkcióra, amely nemcsak a VLOOKUP és a HLOOKUP funkciók által végrehajtott egyértékű kereséseket, hanem a kétirányú értékkereséseket is elvégzi, amelyeket a Az INDEX és a MATCH funkciók is.

* Sajnos az XLOOKUP funkció nem kompatibilis a Microsoft Excel korábbi verzióival, amelyek csak a VLOOKUP és a HLOOKUP funkciókat támogatják, vagy nem kompatibilis azokkal az aktuális verziókkal, amelyek még nem tartalmazzák keresési funkciójuk egyikét, például az Excel 2019 és az Excel Online. Ez azt jelenti, hogy ha megosztja az XLOOKUP képleteket tartalmazó munkafüzetet olyan munkatársakkal vagy ügyfelekkel, akik az Excel olyan verzióját használják, amely nem tartalmazza ezt az új keresési funkciót, akkor ezek a képletek #NAME-et adnak vissza? hibaértékek, amikor megnyitják a munkalapot.