Kuinka käyttää XLOOKUP-toimintoa Excelissä?

VLOOKUP-toiminto on aina ollut yksi Excelin tehokkaimmista ominaisuuksista. Voit etsiä arvoja taulukon ensimmäisestä sarakkeesta ja palauttaa arvot oikeanpuoleisista 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.

Miten XSEARCH toimii

XLOOKUP-funktio on paljon helpompi käyttää kuin VLOOKUP-funktio, koska tulossarakkeen arvon määrittämisen sijaan voit määrittää koko alueen. Toiminnon avulla voit myös etsiä sekä sarakkeesta että rivistä ja paikantaa arvon leikkaavasta solusta. XLOOKUP-funktion parametrit ovat seuraavat: =XLOOKUP(hakuarvo, haun_taulukko, paluutaulukko, [match_mode]† [search_mode]†

• hakuarvo: Arvo, jota haluat etsiä
• lookup_array: Taulukko (sarake), josta haluat etsiä
• return_array: Tulos (sarake), josta haluat saada arvon
• match_mode (valinnainen): Valitse tarkka vastaavuus (0), tarkka vastaavuus tai seuraavaksi pienin arvo (-) tai jokerimerkki (2).
• hakutila (valinnainen): Valitse, etsitäänkö sarakkeen ensimmäisestä kohteesta (1), sarakkeen viimeisestä kohteesta (-1), binäärihakusta nousevassa (2) vai laskevassa (-2).

Seuraavassa on joitain yleisimpiä hakuja, joita voit tehdä XFIND-toiminnolla.

Etsitään yhtä tulosta XLOOKUPilla

Helpoin tapa käyttää XFIND:tä on etsiä yhtä tulosta käyttämällä yhtä saraketietopistettä.

1. Tämä esimerkkilaskentataulukko on luettelo myyntiedustajien lähettämistä tilauksista, mukaan lukien nimike, yksikkömäärä, kustannukset ja kokonaismyynti.

2. Löytääksesi ensimmäisen myynnin tietyn myyjän lähettämästä luettelosta, voit luoda XFIND-toiminnon, joka etsii nimeä Rep-sarakkeesta. Funktio palauttaa tuloksen Yhteensä-sarakkeesta. XHAKU-funktio tälle on: =XHAKU(I2,C2:C44,G2:G44,0,1)

   • I2: Osoittaa Nimi edustaja etsi solua
   • C2:C44: Tämä on Rep-sarake, hakutaulukko
   • G2:G33: Tämä on Total-sarake, palautustaulukko
   • 0: Valitsee tarkan vastaavuuden
   • 1: Valitsee tulosten ensimmäisen ottelun

3. Kun painat Tulla sisään ja kirjoita myyjän nimi, Kokonaistulos -solussa näkyy kyseisen myyjän ensimmäinen tulos taulukossa.

   

4. Jos haluat etsiä viimeisintä myyntiä (koska taulukko on järjestetty uudelleen päivämäärän mukaan), muuta viimeinen argumentti XHAKU arvoon -1joka aloittaa haun hakutaulukon viimeisestä solusta ja antaa sen sijaan sen tuloksen.

   

5. Tämä esimerkki näyttää samanlaisen haun, jonka voit suorittaa VLOOKUP-funktiolla käyttämällä Rep-saraketta hakutaulukon ensimmäisenä sarakkeena. XLOOKUP antaa kuitenkin mahdollisuuden etsiä mitä tahansa saraketta kumpaankin suuntaan. Esimerkiksi löytääksesi myyjän, joka myi vuoden ensimmäisen Binder-tilauksen, käytä seuraavaa XHAKU-funktiota: =XHAKU(I2,D2:D44,C2:C44,0,1)

   • D2: viittaa artikkelien hakusoluun
   • D2:D44: Tämä on Kohde-sarake, hakutaulukko
   • C2:C44: Tämä on Rep-sarake, palautustaulukko hakutaulukon vasemmalla puolella
   • 0: Valitsee tarkan vastaavuuden
   • 1: Valitsee tulosten ensimmäisen ottelun

6. Tällä kertaa tuloksena on vuoden ensimmäisen kansiotilauksen myyneen myyjän nimi.

   

Suorita pysty- ja vaakasuora sovitus XFIND:llä

Toinen XLOOKUPin ominaisuus, johon VLOOKUP ei pysty, on kyky etsiä sekä pysty- että vaakasuunnassa, mikä tarkoittaa, että voit etsiä kohteita sekä sarakkeesta että rivistä. Tämä kaksoishakutoiminto korvaa tehokkaasti muita Excelin toimintoja, kuten INDEX, MATCH tai HLOOKUP.

1. Seuraava esimerkkilaskentataulukko erittelee kunkin myyjän myynnit vuosineljänneksittäin. Jos haluat nähdä kolmannen vuosineljänneksen myynnin tietylle myyjälle ilman XSEARCH-ominaisuutta, tällainen haku olisi vaikeaa.

   

2. XLOOKUP-toiminnolla tällainen haku on helppoa. Voit etsiä kolmannen vuosineljänneksen myyntiä tietylle myyjälle käyttämällä seuraavaa XHAKU-toimintoa: =XHAKU(J2,B2:B42,XHAKU(K2,C1:H1,C2:H42))

   • J2: viittaa Rep-hakusoluun
   • B2:B42: Tämä on Nimike-sarake, sarakkeen hakumatriisi
   • K2: viittaa vuosineljänneksen hakusoluun
   • C1:H1: Tämä on rivien hakutaulukko
   • C2:H42: Tämä on kunkin vuosineljänneksen dollarimäärän hakumerkkijono

   Tämä sisäkkäinen XFIND-funktio identifioi ensin myyjän ja seuraava XFIND-funktio identifioi halutun vuosineljänneksen. Palautettu arvo on solu, jossa nämä kaksi leikkaavat toisensa.

3. Tämän kaavan tulos on Thompson-nimisen myyntiedustajan neljännesvuosittainen voitto.

   

Käyttämällä XSEARCH-toimintoa

XLOOKUP-ominaisuus on vain Office Insider -tilaajien käytettävissä, mutta se otetaan pian käyttöön kaikille Microsoft 365 -tilaajille. Jos haluat testata ominaisuutta itse, voit ryhtyä Office Insideriksi. Valitse Tiedosto † tilija valitse sitten Office Insider avattavasta luettelosta tilataksesi. Kun liityt Office Insider -ohjelmaan, asennettu Excel-versio vastaanottaa kaikki uusimmat päivitykset ja voit alkaa käyttää XFIND-ominaisuutta.