VLOOKUP-toiminto on aina ollut yksi Excelin tehokkaimmista toiminnoista. Sen avulla voit etsiä arvoja taulukon ensimmäisestä sarakkeesta ja palauttaa arvot oikealla olevista kentistä. Mutta Excelissä on myös XLOOKUP-niminen toiminto, jonka avulla voit etsiä arvoa mistä tahansa sarakkeesta tai rivistä ja palauttaa tietoja mistä tahansa muusta sarakkeesta.
Kuinka XLOOKUP toimii
XLOOKUP-toimintoa on paljon helpompi käyttää kuin VLOOKUP-funktiota, koska tulossarakkeen arvon määrittämisen sijaan voit määrittää koko alueen.
Funktion avulla voit myös etsiä sekä sarakkeesta että rivistä ja paikantaa arvon leikkaavasta solusta.
XLOOKUP-funktion parametrit ovat seuraavat:
=XHAKU (hakuarvo, hakutaulukko, paluutaulukko, [match_mode], [search_mode])
- lookup_value: Arvo, jota haluat hakea
- lookup_array: Taulukko (sarake), josta haluat etsiä
- return_array: Tulos (sarake), josta haluat noutaa arvon osoitteesta
- match_mode (valinnainen): Valitse tarkka vastaavuus (0), tarkka vastaavuus tai seuraavaksi pienin arvo (-1) tai jokerimerkkiosuma (2).
- search_mode (valinnainen): Valitse, haetaanko alkaen sarakkeen ensimmäisestä kohdasta (1), sarakkeen viimeisestä kohdasta (-1), binäärihaku nousevassa järjestyksessä (2) tai binäärihaku laskevassa (-2).
Seuraavassa on muutamia yleisimpiä hakuja, joita voit tehdä XLOOKUP-toiminnolla.
Yksittäisen tuloksen hakeminen XLOOKUP-sovelluksella
Helppoin tapa käyttää XLOOKUPia on etsiä yksittäinen tulos käyttämällä datapistettä yhdestä sarakkeesta.
-
Tämä esimerkkitaulukko on luettelo myyntiedustajien lähettämistä tilauksista, mukaan lukien nimike, yksikkömäärä, hinta ja kokonaismyynti.
Image -
Jos haluat löytää ensimmäisen myynnin tietyn myyntiedustajan lähettämästä luettelosta, voit luoda XLOOKUP-funktion, joka etsii edustaja-sarakkeesta nimen. Funktio palauttaa tuloksen Yhteensä-sarakkeesta. XLOOKUP-funktio tälle on:
=XHAKU(I2, C2:C44, G2:G44, 0, 1)
- I2: Osoittaa Edustajan nimi hakusoluun
- C2:C44: Tämä on Rep-sarake, joka on hakutaulukko
- G2:G33: Tämä on Yhteensä-sarake, joka on palautustaulukko
- 0: Valitsee tarkan vastaavuuden
- 1: Valitsee tulosten ensimmäisen ottelun
-
Kun painat Enter ja kirjoitat myyntiedustajan nimen, Kokonaistulos -solu näyttää sinulle kyseisen myyntiedustajan taulukon ensimmäisen tuloksen.
Image -
Jos haluat etsiä viimeisintä alennusmyyntiä (koska taulukko on järjestetty päivämäärän mukaan käänteisessä järjestyksessä), muuta viimeinen XLOOKUP-argumentti arvoon - 1, joka alkaa haun hakutaulukon viimeisestä solusta ja anna sen sijaan tuo tulos.
Image -
Tämä esimerkki näyttää samanlaisen haun, jonka voit tehdä VLOOKUP-funktiolla käyttämällä Rep-saraketta hakutaulukon ensimmäisenä sarakkeena. XLOOKUPilla voit kuitenkin etsiä mitä tahansa saraketta kumpaankin suuntaan. Jos esimerkiksi haluat löytää myyntiedustajan, joka myi vuoden ensimmäisen Binder-tilauksen, käytä seuraavaa XLOOKUP-toimintoa:
=XHAKU(I2, D2:D44, C2:C44, 0, 1)
- D2: Osoittaa kohteen hakusoluun
- D2:D44: Tämä on Nimike-sarake, joka on hakutaulukko
- C2:C44: Tämä on Rep-sarake, joka on hakutaulukon vasemmalla puolella oleva paluutaulukko
- 0: Valitsee tarkan vastaavuuden
- 1: Valitsee tulosten ensimmäisen ottelun
-
Tällä kertaa tuloksena on sen myyntiedustajan nimi, joka myi vuoden ensimmäisen kansiotilauksen.
Image
Suorita pysty- ja vaakasuuntainen vastaavuus XLOOKUP-sovelluksella
Toinen XLOOKUPin ominaisuus, johon VLOOKUP ei pysty, on kyky suorittaa sekä pysty- että vaakahaku, mikä tarkoittaa, että voit etsiä kohteen alas sarakkeesta ja myös riviltä.
Tämä kaksoishakuominaisuus korvaa tehokkaasti muut Excelin toiminnot, kuten INDEX, MATCH tai HLOOKUP.
-
Seuraavassa esimerkkilaskentataulukossa kunkin myyntiedustajan myynti on jaettu vuosineljänneksittäin. Jos haluat nähdä kolmannen vuosineljänneksen myynnin tietylle myyntiedustajalle ilman XLOOKUP-toimintoa, tällainen haku olisi vaikeaa.
Image -
XLOOKUP-toiminnon avulla tällainen haku on helppoa. Käyttämällä seuraavaa XLOOKUP-toimintoa voit etsiä kolmannen vuosineljänneksen myyntiä tietylle myyntiedustajalle:
=XHAKU(J2, B2:B42, XHAKU(K2, C1:H1, C2:H42))
- J2: Osoittaa Rep-hakusoluun
- B2:B42: Tämä on Tuote-sarake, joka on sarakkeen hakutaulukko
- K2: Osoittaa Quarter-hakusoluun
- C1:H1: Tämä on rivin hakutaulukko
- C2:H42: Tämä on kunkin vuosineljänneksen dollarimäärän hakutaulukko
Tämä sisäkkäinen XHAKU-funktio identifioi ensin myyntiedustajan ja seuraava XHAKU-funktio identifioi halutun vuosineljänneksen. Paluuarvo tulee on solu, jossa nämä kaksi leikkaavat.
-
Tämän kaavan tulos on Thompson-nimisen edustajan vuosineljänneksen tulos.
Image
XLOOKUP-toiminnon käyttäminen
XLOOKUP-toiminto on vain Office Insider -tilaajien käytettävissä, mutta se otetaan pian käyttöön kaikille Microsoft 365 -tilaajille.
Jos haluat testata toimintoa itse, voit ryhtyä Office Insideriksi. Valitse Tiedosto > Account ja tilaa sitten avattava Office Insider -valikko.
Kun liityt Office Insider -ohjelmaan, asennettu Excel-versio vastaanottaa kaikki uusimmat päivitykset ja voit alkaa käyttää XLOOKUP-toimintoa.
