Kuinka käyttää Excel XLOOKUP -toimintoa -

Yhteenveto

Excel XLOOKUP -toiminto on moderni ja joustava korvike vanhemmille toiminnoille, kuten VLOOKUP, HLOOKUP ja LOOKUP. XLOOKUP tukee likimääräistä ja tarkkaa hakua, jokerimerkkejä (*?) Osittaisille osumille ja hakuja pysty- tai vaaka-alueille.

Tarkoitus

Haun arvot alueella tai taulukossa

Palautusarvo

Palautuvan taulukon arvo (t)

Syntaksi

= XLOOKUP (haku, haku_palautus, paluukuva, (ei löydy), (vastaavuustila), (hakutapa))

Väitteet

  • haku - hakuarvo.
  • lookup_array - Haettava taulukko tai alue.
  • return_array - Palautettava taulukko tai alue.
  • not_found - (valinnainen) Palautettava arvo, jos vastaavuutta ei löydy.
  • match_mode - (valinnainen) 0 = tarkka haku (oletus), -1 = tarkka haku tai seuraavaksi pienin, 1 = tarkka ottelu tai seuraava suurempi, 2 = jokerimerkki.
  • hakutapa - (valinnainen) 1 = haku ensimmäisestä (oletus), -1 = haku viimeisestä, 2 = binaarihaku nouseva, -2 = binaarihaku laskeva.

Versio

Excel 365

Käyttöohjeet

XLOOKUP on moderni korvaus VLOOKUP-toiminnolle. Se on joustava ja monipuolinen toiminto, jota voidaan käyttää monissa tilanteissa.

XLOOKUP löytää arvot pysty- tai vaaka-alueilta, voi suorittaa likimääräisen ja tarkan vastaavuuden ja tukee jokerimerkkejä (*?) Osittaisissa otteluissa. Lisäksi XLOOKUP voi hakea tietoja ensimmäisestä tai viimeisestä arvosta alkaen (katso alla hakutyypin ja hakutilan yksityiskohdat). Verrattuna vanhempiin toimintoihin, kuten VLOOKUP, HLOOKUP ja LOOKUP, XLOOKUP tarjoaa useita keskeisiä etuja.

Viestiä ei löydy

Kun XLOOKUP ei löydä vastaavuutta, se palauttaa # N / A -virheen, kuten muutkin Excelin vastaavuustoiminnot. Toisin kuin muut ottelutoiminnot, XLOOKUP tukee valinnaista argumenttia nimeltä not_found, jota voidaan käyttää # N / A-virheen ohittamiseen, kun se muuten ilmestyy. Not_found-tyypilliset arvot voivat olla "Ei löydy", "Ei vastaavuuksia", "Ei tulosta" jne. Kun annat arvon not_found -arvolle, liitä teksti kaksoislainausmerkkeihin ("").

Huomaa: Ole varovainen, jos syötät tyhjän merkkijonon ("") not_found-tiedostolle. Jos vastaavuutta ei löydy, XLOOKUP ei näytä mitään # N / A: n sijaan. Jos haluat nähdä # N / A -virheen, kun osumaa ei löydy, jätä argumentti kokonaan väliin.

Ottelutyyppi

Oletuksena XLOOKUP suorittaa tarkan ottelun. Ottelun käyttäytymistä ohjaa valinnainen argumentti nimeltä match_type, jolla on seuraavat vaihtoehdot:

Ottelutyyppi Käyttäytyminen
0 (oletus) Tarkka ottelu. Palauttaa # N / A, jos ei ole ottelua.
-1 Tarkka ottelu tai seuraava pienempi kohde.
1 Tarkka ottelu tai seuraava suurempi tuote.
2 Jokerimerkki (*,?, ~)

Hakutila

Oletuksena XLOOKUP alkaa täsmää ensimmäisestä data-arvosta. Hakukäyttäytymistä ohjaa valinnainen argumentti nimeltä hakutapa , joka tarjoaa seuraavat vaihtoehdot:

Hakutila Käyttäytyminen
1 (oletus) Hae ensimmäisestä arvosta
-1 Haku viimeisestä arvosta (käänteinen)
2 Binaarihakuarvot lajiteltu nousevassa järjestyksessä
-2 Binaarihakuarvot lajiteltu laskevassa järjestyksessä

Binaarihaut ovat erittäin nopeita, mutta tiedot on lajiteltava tarpeen mukaan. Jos tietoja ei ole lajiteltu oikein, binaarihaku voi antaa virheellisiä tuloksia, jotka näyttävät täysin normaaleilta.

Esimerkki 1 - tarkka tarkka haku

Oletuksena XLOOKUP suorittaa tarkan ottelun. Alla olevassa esimerkissä XLOOKUPia käytetään myynnin hakemiseen elokuvan tarkan vastaavuuden perusteella. H5: n kaava on:

=XLOOKUP(H4,B5:B9,E5:E9)

Tarkempi selitys täältä.

Esimerkki # 2 - peruskeskiarvo ottelu

Arvioitu ottelu otetaan käyttöön antamalla arvo "match_mode" -argumentille. Alla olevassa esimerkissä XLOOKUPia käytetään alennuksen laskemiseen määrän perusteella, mikä edellyttää likimääräistä vastaavuutta. Kohdassa F5 oleva kaava antaa -1 ottelumoodille, jotta likimääräinen ottelu "tarkan haun tai seuraavaksi pienimmän" käyttäytymisen kanssa voidaan ottaa käyttöön:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Tarkempi selitys täältä.

Esimerkki # 3 - useita arvoja

XLOOKUP voi palauttaa useita arvoja samanaikaisesti samalle ottelulle. Alla oleva esimerkki osoittaa, kuinka XLOOKUP voidaan määrittää palauttamaan kolme täsmäytysarvoa yhdellä kaavalla. C5: n kaava on:

=XLOOKUP(B5,B8:B15,C8:E15)

Huomaa, että paluumatriisi (C8: E15) sisältää 3 saraketta: Ensimmäinen, Viimeinen, Osasto. Kaikki kolme arvoa palautetaan ja levitetään alueelle C5: E5.

Esimerkki # 4 - kaksisuuntainen haku

XLOOKUPia voidaan käyttää kaksisuuntaisen haun tekemiseen sijoittamalla yksi XLOOKUP toisen sisälle. Alla olevassa esimerkissä "sisempi" XLOOKUP hakee kokonaisen rivin (kaikki lasin arvot), joka luovutetaan "ulommalle" XLOOKUP: lle paluuryhmänä. Ulompi XLOOKUP löytää sopivan ryhmän (B) ja palauttaa vastaavan arvon (17.25) lopputuloksena.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Lisätietoja täältä.

Esimerkki # 5 - viestiä ei löydy

Kuten muut hakutoiminnot, jos XLOOKUP ei löydä arvoa, se palauttaa # N / A -virheen. Jos haluat näyttää mukautetun viestin # N / A: n sijaan, anna arvo valinnaiselle "ei löydy" -argumentille, joka on suljettu lainausmerkeissä (""). Jos esimerkiksi haluat näyttää "Ei löydy", kun vastaavaa elokuvaa ei löydy, käytä alla olevan laskentataulukon perusteella:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Voit muokata tätä viestiä haluamallasi tavalla: "Ei vastaavuuksia", "Elokuvaa ei löydy" jne.

Esimerkki # 6 - monimutkaiset kriteerit

Kykyä käsitellä matriiseja natiivisti, XLOOKUPia voidaan käyttää monimutkaisilla ehdoilla. Alla olevassa esimerkissä XLOOKUP vastaa ensimmäistä tietuetta, jossa: tili alkaa "x": llä ja alue on "itä" eikä kuukausi ole huhtikuu:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Yksityiskohdat: (1) yksinkertainen esimerkki, (2) monimutkaisempi esimerkki.

XLOOKUP-edut

XLOOKUP tarjoaa useita tärkeitä etuja, erityisesti verrattuna VLOOKUP: iin:

  • XLOOKUP voi hakea tietoja hakuarvojen oikealta tai vasemmalta puolelta
  • XLOOKUP voi palauttaa useita tuloksia (esimerkki 3 yllä)
  • XLOOKUP on oletusarvoisesti tarkka ottelu (VLOOKUP oletuksena likimääräinen)
  • XLOOKUP voi toimia pysty- ja vaakasuuntaisten tietojen kanssa
  • XLOOKUP voi suorittaa käänteisen haun (viimeinen ensimmäiseksi)
  • XLOOKUP voi palauttaa kokonaiset rivit tai sarakkeet, ei vain yhtä arvoa
  • XLOOKUP voi työskennellä matriisien kanssa natiivisti soveltaa monimutkaisia ​​ehtoja

Huomautuksia

  1. XLOOKUP voi toimia sekä pysty- että vaakasuuntaisten ryhmien kanssa.
  2. XLOOKUP palauttaa # N / A, jos hakuarvoa ei löydy.
  3. Etsintäjono oltava ulottuvuus yhteensopiva return_array väitteen, muuten XLOOKUP palaa #ARVO!
  4. Jos XLOOKUPia käytetään työkirjojen välillä, molempien työkirjojen on oltava auki, muuten XLOOKUP palauttaa #REF !.
  5. Kuten INDEX-funktio, XLOOKUP palauttaa tuloksena viitteen.

Liittyvät videot

XLOOKUP-perusesimerkki Tässä videossa määritämme XLOOKUP-toiminnon perusesimerkillä. Haemme kaupungin nimen, haemme maan ja väestön. Perusarvoinen XLOOKUP-likimääräinen ottelu Tässä videossa asetamme XLOOKUP-toiminnon suorittamaan likimääräisen ottelun määrältään alennuksen laskemiseksi. XLOOKUP loogisella logiikalla Tässä videossa tarkastellaan kuinka XLOOKUP-toimintoa voidaan käyttää loogisen logiikan kanssa useiden ehtojen soveltamiseen. XLOOKUP, jossa on useita hakuarvoja Tässä videossa asetamme XLOOKUPin palauttamaan useita arvoja dynaamisessa taulukossa tarjoamalla useita hakuarvoja yhden hakuarvon sijaan.

Mielenkiintoisia artikkeleita...