Katso video - Excel XLOOKUP -toiminto (10 XLOOKUP -esimerkkiä)
Excel XLOOKUP -toiminto on vihdoin saapunut.
Jos olet käyttänyt VLOOKUP- tai INDEX/MATCH -toimintoa, pidät varmasti XLOOKUP -toiminnon tarjoamasta joustavuudesta.
Tässä opetusohjelmassa käsitän kaiken, mitä XLOOKUP -toiminnosta on tiedettävä, ja joitakin esimerkkejä, jotka auttavat sinua tietämään, miten sitä käytetään parhaiten.
Aloitetaan siis!
Mikä on XLOOKUP?
XLOOKUP on uusi Office 365: n toiminto ja uusi ja parannettu versio VLOOKUP/HLOOKUP -toiminnosta.
Se tekee kaiken, mitä VLOOKUP teki, ja paljon muuta.
XLOOKUP on toiminto, jonka avulla voit nopeasti etsiä arvon tietojoukosta (pystysuora tai vaakasuuntainen) ja palauttaa vastaavan arvon toisella rivillä/sarakkeessa.
Jos sinulla on esimerkiksi kokeen oppilaiden pisteet, voit tarkistaa XLOOKUP -toiminnon avulla nopeasti, kuinka paljon oppilas on saanut pistettä opiskelijan nimen perusteella.
Tämän toiminnon voima tulee entistä selvemmäksi, kun sukellan syvälle joihinkin XLOOKUP -esimerkkejä myöhemmin tässä opetusohjelmassa.
Mutta ennen kuin käsittelen esimerkkejä, on suuri kysymys - miten pääsen XLOOKUPiin?
Kuinka päästä XLOOKUPiin?
Tällä hetkellä XLOOKUP on käytettävissä vain Office 365: n käyttäjille.
Joten jos käytät Excelin aiempia versioita (2010/2013/2016/2019), et voi käyttää tätä toimintoa.
En ole myöskään varma, julkaistaanko tämä koskaan aiemmille versioille vai ei (ehkä Microsoft voi luoda lisäosan Power Queryn tapaan). Mutta toistaiseksi voit käyttää sitä vain, jos käytät Office 365: tä.
Napsauta tätä päivittääksesi Office 365: een
Jos sinulla on jo Office 365 (Home-, Personal- tai University -versio) etkä voi käyttää sitä, voit siirtyä Tiedosto -välilehteen ja napsauttaa sitten Tili.
Siellä olisi Office Insider -ohjelma ja voit napsauttaa ja liittyä Office Insider -ohjelmaan. Tämä antaa sinulle pääsyn XLOOKUP -toimintoon.
Odotan, että XLOOKUP on pian saatavilla kaikissa Office 365 -versioissa.
Huomautus: XLOOKUP on saatavana myös Office 365 for Macille ja Excel for the Webille (Excel online)XLOOKUP -toiminnon syntaksi
Alla on XLOOKUP -toiminnon syntaksi:
= HAKU (haku_arvo, hakutoiminto, paluutaulukko, [jos_ ei löydy], [ottelutila], [hakutila])
Jos olet käyttänyt VLOOKUPia, huomaat, että syntaksi on melko samanlainen, ja siinä on tietysti mahtavia lisäominaisuuksia.
Älä huolestu, jos syntaksi ja argumentti näyttävät hieman liikaa. Peitän nämä muutamalla helpolla XLOOKUP -esimerkillä myöhemmin tässä opetusohjelmassa, mikä tekee siitä kristallinkirkkaan.XLOOKUP -toiminto voi kertoa 6 argumenttia (3 pakollista ja 3 valinnaista):
- haku_arvo - arvo, jota etsit
- lookup_array - taulukko, josta etsit hakuarvoa
- return_array - taulukko, josta haluat noutaa ja palauttaa arvon (vastaa paikkaa, josta hakuarvo löytyy)
- [if_not_found] - palautettava arvo, jos hakuarvoa ei löydy. Jos et määritä tätä argumenttia, #N/A -virhe palautetaan
- [match_mode] - Tässä voit määrittää haluamasi ottelun tyypin:
- 0 - Tarkka haku, jossa haun_arvon tulee täsmälleen vastata hakusarjan arvoa. Tämä on oletusasetus.
- -1 - etsii tarkkaa vastaavuutta, mutta jos se löytyy, palauttaa seuraavan pienemmän kohteen/arvon
- 1 - etsii tarkkaa vastaavuutta, mutta jos se löytyy, palauttaa seuraavan suuremman kohteen/arvon
- 2 - Osittaisen vastaavuuden tekeminen käyttämällä yleismerkkejä (* tai ~)
- [haku_tila] - Tässä voit määrittää, miten XLOOKUP -funktion tulee hakea lookup_array
- 1 - Tämä on oletusasetus, jossa toiminto alkaa etsiä haun_arvoa ylhäältä (ensimmäinen kohde) alas (viimeinen kohde)
- -1 - Onko haku alhaalta ylös. Hyödyllinen, kun haluat löytää haun_taulukosta viimeisen vastaavan arvon
- 2 - Suorittaa binäärihaun, jossa tiedot on lajiteltava nousevaan järjestykseen. Jos sitä ei lajitella, se voi antaa virheen tai vääriä tuloksia
- -2 - Suorittaa binäärihaun, jossa tiedot on lajiteltava laskevaan järjestykseen. Jos sitä ei lajitella, se voi antaa virheen tai vääriä tuloksia
Esimerkkejä XLOOKUP -toiminnoista
Siirrymme nyt mielenkiintoiseen osaan - joitain käytännön XLOOKUP -esimerkkejä.
Nämä esimerkit auttavat sinua ymmärtämään paremmin, miten XLOOKUP toimii, miten se eroaa VLOOKUPista ja INDEX/MATCH: sta, sekä joitakin tämän toiminnon parannuksia ja rajoituksia.
Lataa esimerkkitiedosto napsauttamalla tätä ja seuraa sitä
Esimerkki 1: Hae hakuarvo
Oletetaan, että sinulla on seuraava tietojoukko ja haluat hakea Gregin matemaattiset pisteet (hakuarvo).
Alla on kaava, joka tekee tämän:
= HAKU (F2, A2: A15, B2: B15)
Yllä olevassa kaavassa olen juuri käyttänyt pakollisia argumentteja, joissa se etsii nimeä (ylhäältä alas), löytää tarkan vastaavuuden ja palauttaa vastaavan arvon kohdasta B2: B15.
Yksi ilmeinen ero XLOOKUP- ja VLOOKUP -toiminnoilla on tapa, jolla ne käsittelevät hakutaulukkoa. VLOOKUPissa sinulla on koko taulukko, jossa hakuarvo on vasemmassa sarakkeessa, ja määrität sitten sarakkeen numeron, josta haluat hakea tuloksen. XLOOKUP mahdollistaa toisaalta hakutoiminnon ja paluutaulukon valitsemisen erikseenYksi välitön hyöty siitä, että lookup_array ja return_array ovat erillisiä argumentteja, tarkoittaa, että nyt voit katso vasemmalle. VLOOKUPilla oli tämä rajoitus, jossa voit vain katsoa ylös ja löytää arvon, joka on oikealla. Mutta XLOOKUPin avulla tämä rajoitus on kadonnut.
Tässä on esimerkki. Minulla on sama tietojoukko, jossa nimi on oikealla ja return_range vasemmalla.
Alla on kaava, jolla voin saada pisteet Gregille matematiikassa (mikä tarkoittaa etsimistä haun arvon vasemmalle puolelle)
= HAKU (F2, D2: D15, A2: A15)
XLOOKUP ratkaisee toisen suuren ongelman - Jos lisäät uuden sarakkeen tai siirrät sarakkeita ympäri, tuloksena olevat tiedot ovat edelleen oikein. VLOOKUP todennäköisesti rikkoisi tai antaisi väärän tuloksen, jos useimmiten sarakehakemiston arvo on kovakoodattu.
Esimerkki 2: Etsi ja hae koko tietue
Otetaan samat tiedot esimerkkinä.
Tässä tapauksessa en halua vain hakea Gregin pisteitä matematiikassa. Haluan saada pisteet kaikista aiheista.
Tässä tapauksessa voin käyttää seuraavaa kaavaa:
= HAKU (F2, A2: A15, B2: D15)
Yllä oleva kaava käyttää return_array -aluetta, joka on enemmän kuin sarake (B2: D15). Joten kun hakuarvo löytyy kohdasta A2: A15, kaava palauttaa return_array -rivin koko rivin.
Et voi myöskään poistaa vain soluja, jotka ovat osa matriisia ja jotka täytettiin automaattisesti. Tässä esimerkissä et voi poistaa H2 tai I2. Jos yrität, mitään ei tapahdu. Jos valitset nämä solut, kaavapalkin kaava näkyy harmaana (mikä tarkoittaa, että sitä ei voi muuttaa)
Voit poistaa kaavan solusta G2 (johon alun perin annoimme sen), se poistaa koko tuloksen.
Tämä on hyödyllinen parannus, kuten aikaisemmin VLOOKUP: n kanssa, sarakkeen numero on määritettävä erikseen jokaiselle kaavalle.
Esimerkki 3: Kaksisuuntainen haku käyttämällä XLOOKUP -toimintoa (vaaka- ja pystykatselu)
Alla on aineisto, jossa haluan tietää Gregin pisteet matematiikassa (aihe solussa G2).
Tämä voidaan tehdä käyttämällä kaksisuuntaista hakua, jossa etsin nimeä sarakkeesta A ja aiheen nimeä riviltä 1. Tämän kaksisuuntaisen haun etu on, että tulos on riippumaton aiheen nimen opiskelijan nimestä. Jos vaihdan aiheen nimen kemiaksi, tämä kaksisuuntainen XLOOKUP-kaava toimii edelleen ja antaa minulle oikean tuloksen.
Alla on kaava, joka suorittaa kaksisuuntaisen haun ja antaa oikean tuloksen:
= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))
Tämä kaava käyttää sisäkkäistä XLOOKUPia, jossa ensin haen sen avulla kaikki oppilaan merkit solusta F2.
XLOOKUPin (F2, A2: A15, B2: D15) tulos on siis {21,94,81}, joka on joukko Gregin pisteitä tässä tapauksessa.
Tätä käytetään sitten uudelleen ulkoisessa XLOOKUP -kaavassa palautusmatriisina. Ulkoisessa XLOOKUP -kaavassa etsin aiheen nimeä (joka on solussa G1) ja hakutaulukko on B1: D1.
Jos aiheen nimi on matematiikka, tämä ulkoinen XLOOKUP -kaava hakee ensimmäisen arvon palautusmatriisista - joka on tässä esimerkissä {21,94,81}.
Tämä tekee saman, joka tähän asti saavutettiin käyttämällä INDEX- ja MATCH -yhdistelmää
Lataa esimerkkitiedosto napsauttamalla tätä ja seuraa sitä
Esimerkki 4: Kun hakuarvoa ei löydy (virheenkäsittely)
Virheiden käsittely on nyt lisätty XLOOKUP -kaavaan.
Neljäs argumentti XLOOKUP -funktiossa on [if_not_found], jossa voit määrittää, mitä haluat, jos hakua ei löydy.
Oletetaan, että sinulla on alla näkyvä tietojoukko, josta haluat saada matematiikkapisteen siltä varalta, että jos osumaa, ja jos nimeä ei löydy, haluat palauttaa - "Ei näkynyt"
Alla oleva kaava tekee tämän:
= HAKU (F2, A2: A15, B2: B15, "Ei näkynyt")
Tässä tapauksessa olen koodannut kovasti, mitä haluan saada, jos ottelua ei löydy. Voit myös käyttää soluviittausta soluun tai kaavaan.
Esimerkki 5: Sisäinen XLOOKUP (haku useilla alueilla)
[If_not_found] -argumentin nero on, että sen avulla voit käyttää sisäkkäinen XLOOKUP -kaava.
Oletetaan esimerkiksi, että sinulla on kaksi erillistä luetteloa, kuten alla on esitetty. Vaikka minulla on nämä kaksi taulukkoa samalla arkilla, voit pitää ne erillisinä arkkeina tai jopa työkirjoina.
Alla on sisäkkäinen XLOOKUP -kaava, joka tarkistaa nimen molemmista taulukoista ja palauttaa vastaavan arvon määritetystä sarakkeesta.
= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))
Yllä olevassa kaavassa olen käyttänyt [if_not_found] -argumenttia toisen XLOOKUP -kaavan käyttämiseen. Tämän avulla voit lisätä toisen XLOOKUPin samaan kaavaan ja skannata kaksi taulukkoa yhdellä kaavalla.
En ole varma, kuinka monta sisäkkäistä XLOOKUPia voit käyttää kaavassa. Kokeilin kymmeneen asti ja se toimi, sitten luovutin 🙂
Esimerkki 6: Etsi viimeinen vastaava arvo
Tätä tarvittiin kipeästi, ja XLOOKUP teki tämän mahdolliseksi. Nyt sinun ei tarvitse löytää mutkikkaita tapoja saada alueen viimeinen vastaava arvo.
Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat tarkistaa milloin viimeksi palkattiin kussakin osastossa ja mikä oli vuokrauspäivä.
Alla oleva kaava etsii kunkin osaston viimeisen arvon ja antaa viimeisen vuokrauksen nimen:
= HAKU (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)
Ja alla oleva kaava antaa viimeisen vuokrauksen päivämäärän kullekin osastolle:
= HAKU (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)
Koska XLOOKUPissa on sisäänrakennettu ominaisuus hakusuunnan määrittämiseksi (ensimmäisestä viimeiseen tai viimeisestä ensimmäiseen), tämä tehdään yksinkertaisella kaavalla. Pystysuuntaisten tietojen avulla VLOOKUP ja INDEX/MATCH näyttävät aina ylhäältä alas, mutta XLOOKUPilla ja voivat myös määrittää suunnan alhaalta ylöspäin.
Esimerkki 7: Arvioitu ottelu XLOOKUPin avulla (Etsi veroprosentti)
Toinen merkittävä parannus XLOOKUPissa on, että nyt on olemassa neljä ottelutilaa (VLOOKUPilla on 2 ja MATCH: lla 3).
Voit määrittää minkä tahansa neljästä argumentista päättääksesi, miten hakuarvo tulee sovittaa yhteen:
- 0 - Tarkka haku, jossa haun_arvon tulee täsmälleen vastata hakusarjan arvoa. Tämä on oletusasetus.
- -1 - etsii tarkkaa vastaavuutta, mutta jos se löytyy, palauttaa seuraavan pienemmän kohteen/arvon
- 1 - etsii tarkkaa vastaavuutta, mutta jos se löytyy, palauttaa seuraavan suuremman kohteen/arvon
- 2 - Osittaisen vastaavuuden tekeminen käyttämällä yleismerkkejä (* tai ~)
Alla on tietojoukko, josta haluan löytää jokaisen henkilön palkkion - ja palkkio on laskettava oikealla olevan taulukon avulla.
Alla on kaava, jolla tämä tehdään:
= HAKU (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2
Tämä käyttää yksinkertaisesti myyntiarvoa hakutuloksena ja katsoo oikealla olevan hakutaulukon läpi. Tässä kaavassa olen käyttänyt -1: tä viidentenä argumenttina ([match_mode]), mikä tarkoittaa, että se etsii tarkan vastaavuuden, ja jos se ei löydä sitä, se palauttaa vain pienemmän arvon kuin hakuarvo .
Ja kuten sanoin, sinun ei tarvitse huolehtia siitä, onko tietosi lajiteltu.
Lataa esimerkkitiedosto napsauttamalla tätä ja seuraa sitä
Esimerkki 8: Vaakahaku
XLOOKUP voi tehdä sekä pysty- että vaakasuuntaisen haun.
Alla on tietojoukko, jossa minulla on opiskelijoiden nimet ja niiden tulokset riveillä, ja haluan hakea solun B7 nimen pisteet.
Alla oleva kaava tekee tämän:
= HAKU (B7, B1: O1, B2: O2)
Tämä on vain yksinkertainen haku (samanlainen kuin esimerkissä 1), mutta vaakasuora.
Kaikki esimerkit, joita käsittelen pystysuorasta hausta, voidaan tehdä myös vaakasuoralla haulla käyttämällä XLOOKUPia (jäähyväiset VLOOKUPille ja HLOOKUPille).
Esimerkki 9: Ehdollinen haku (XLOOKUPin käyttäminen muiden kaavojen kanssa)
Tämä on hieman kehittynyt esimerkki, ja se näyttää myös XLOOKUPin tehon, kun sinun on tehtävä monimutkaisia hakuja.
Alla on tietojoukko, jossa minulla on oppilaiden nimet ja heidän pisteet, ja haluan tietää oppilaan nimen, joka on saavuttanut maksimipisteen kussakin oppiaineessa, ja niiden opiskelijoiden määrän, jotka ovat saaneet yli 80 pistettä jokaisessa oppiaineessa.
Alla on kaava, joka antaa korkeimman arvosanan saaneen oppilaan nimen kustakin aiheesta:
= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)
Koska XLOOKUPia voidaan käyttää koko taulukon palauttamiseen, olen käyttänyt sitä ensin kaikkien vaaditun kohteen merkintöjen saamiseen.
Esimerkiksi matematiikassa, kun käytän XLOOKUPia (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), se antaa minulle kaikki matematiikan tulokset. Voin sitten käyttää MAX -toimintoa löytääksesi maksimipistemäärän tällä alueella.
Tästä enimmäispisteestä tulee sitten hakuarvoni, ja hakualue olisi XLOOKUPin palauttama taulukko (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)
Käytän tätä toisessa XLOOKUP -kaavassa hakemaan maksimipisteitä saaneen opiskelijan nimen.
Voit laskea yli 80 pistettä saaneiden opiskelijoiden määrän käyttämällä seuraavaa kaavaa:
= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")
Tämä käyttää yksinkertaisesti XLOOKUP -kaavaa saadakseen alueen kaikki arvot tietylle aiheelle. Se käärii sen COUNTIF -funktioon saadakseen yli 80 pistemäärän.
Esimerkki 10: Yleismerkin käyttäminen XLOOKUPissa
Aivan kuten voit käyttää jokerimerkkejä VLOOKUP- ja MATCH -sovelluksissa, voit tehdä tämän myös XLOOKUP -toiminnolla.
Mutta eroa on.
XLOOKUPissa sinun on määritettävä, että käytät jokerimerkkejä (viidennessä argumentissa). Jos et määritä tätä, XLOOKUP antaa sinulle virheen.
Alla on tietojoukko, jossa minulla on yritysten nimet ja niiden markkina -arvo.
Haluan etsiä yrityksen nimen sarakkeesta D ja hakea markkina -arvon vasemmalla olevasta taulukosta. Ja koska sarakkeen D nimet eivät ole täsmällisiä, joudun käyttämään jokerimerkkejä.
Alla on kaava, jolla tämä tehdään:
= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)
Yllä olevassa kaavassa olen käyttänyt tähti (*) -merkkiä ennen D2: n jälkeistä (sen on oltava lainausmerkeissä ja liitettävä D2: een käyttämällä ampersandia).
Tämä kehottaa kaavaa katsomaan kaikki solut läpi, ja jos se sisältää sanan solussa D2 (joka on Apple), pidä sitä täsmällisenä. Riippumatta siitä, kuinka monta ja mitä merkkejä on ennen solun D2 tekstiä ja sen jälkeen.
Ja varmistaaksesi, että XLOOKUP hyväksyy jokerimerkit, viides argumentti on asetettu arvoon 2 (jokerimerkki).
Esimerkki 11: Etsi viimeinen arvo sarakkeesta
Koska XLOOKUP mahdollistaa haun alhaalta ylös, voit helposti löytää luettelon viimeisen arvon ja noutaa vastaavan arvon sarakkeesta.
Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat tietää, mikä on viimeinen yritys ja mikä on tämän viimeisen yrityksen markkina -arvo.
Alla oleva kaava antaa viimeisen yrityksen nimen:
= HAKU ("*", A2: A11, A2: A11,, 2, -1)
Ja alla oleva kaava antaa luettelon viimeisen yrityksen markkina -arvon:
= HAKU ("*", A2: A11, B2: B11,, 2, -1)
Nämä kaavat käyttävät jälleen jokerimerkkejä. Näissä olen käyttänyt tähtiä (*) hakuarvona, mikä tarkoittaa, että tämä pitää ensimmäistä solua, jonka se kohtaa, täsmällisenä vastaavuutena (koska tähti voi olla mikä tahansa merkki ja mikä tahansa määrä merkkejä).
Ja koska suunta on alhaalta ylöspäin (pystysuunnassa järjestetylle datalle), se palauttaa luettelon viimeisen arvon.
Ja toinen kaava sen jälkeen käyttää erillistä return_rangea saadakseen listan sukunimen markkina -arvon.
Lataa esimerkkitiedosto napsauttamalla tätä ja seuraa sitä
Entä jos sinulla ei ole XLOOKUPia?
Koska XLOOKUP on todennäköisesti vain Office 365 -käyttäjien käytettävissä, yksi tapa saada se on päivittää Office 365: een.
Jos sinulla on jo Office 365 Home-, Personal- tai University -versio, sinulla on jo XLOOKUP -käyttöoikeus. Sinun tarvitsee vain liittyä Office Insider -ohjelmaan.
Voit tehdä tämän siirtymällä Tiedosto -välilehteen, napsauttamalla Tili ja sitten Office -sisäpiirivaihtoehtoa. Olisi mahdollisuus liittyä sisäpiiriohjelmaan.
Jos sinulla on muita Office 365 -tilauksia (kuten Enterprise), olen varma, että XLOOKUP ja muut mahtavat ominaisuudet (kuten dynaamiset taulukot, kaavat, kuten SORT ja FILTER) tulevat pian saataville.
Jos käytät Exceliä 2010/2013/2016/2019, sinulla ei ole XLOOKUP -toimintoa ja sinun on edelleen käytettävä VLOOKUP-, HLOOKUP- ja INDEX/MATCH -yhdistelmää, jotta saat parhaan hyödyn hakukaavoista.
XLOOKUP -yhteensopivuus taaksepäin
Tässä on oltava varovainen - XLOOKUP on EI taaksepäin yhteensopiva.
Tämä tarkoittaa, että jos luot tiedoston ja käytät XLOOKUP -kaavaa ja avaat sen sitten versiossa, jossa ei ole XLOOKUPia, se näyttää virheitä.
Koska XLOOKUP on valtava askel eteenpäin oikeaan suuntaan, uskon, että tästä tulee oletushakukaava, mutta kestää varmasti muutaman vuoden ennen kuin se otetaan laajasti käyttöön. Loppujen lopuksi näen edelleen joitakin käyttäjiä Excel 2003: ssa.
Nämä ovat siis 11 XLOOKUP -esimerkkiä, joiden avulla voit tehdä kaikki haku- ja viiteasiat nopeammin ja helpottaa niiden käyttöä.
Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä!
