Tutustu Excelin XHAKU-funktioon

Julkaistu Julkaistu: Blogi,Excel,Microsoft Office

XHAKU (XLOOKUP) on uusi hakufunktio, jota pääset kokeilemaan mikäli käytössäsi on Excel 365. Funktion löydät työasemalle asennetusta Windows ja macOSversiosta sekä selainversiosta.

Hakufunktioita tarvitaan silloin kun solussa olevan arvon perusteella pitää hakea tietoa jostakin taulukkovälilehden toisesta kohdasta tai toiselta taulukkovälilehdeltä, jonka ei ole pakko sijaita edes samassa työkirjassa.

Kuvan tilanteessa halutaan tuoda aputaulukosta B-sarakkeeseen tuotteen selväkielinen nimi kun tuotekoodi tiedetään. Tähän on aikaisemmin käytetty PHAKU-funktio (VLOOKUP) ja katsotaankin vertailun vuoksi sen toiminta.

Esimerkki, kuva alkutilanteesta

Kaavaa kirjoittaessasi tarvitset funktion ensimmäiseksi argumentiksi hakuarvon, joka on A-sarakkeessa oleva solu. Sen jälkeen valitset taulukko_matriisi-argumentiksi tulevan alueen joka tässä on G2:I6. Lopuksi kerrot monesko taulukkomatriisin sarake sisältää haluamasi tiedon eli selväkielisen nimen – argumentti on nimeltään sar_indeksi_nro ja tässä se on 2. Funktiolla on valinnaisena argumentteina myös alue_haku. Oletuksena tämä argumentti on TOSI mikä tarkoittaa sitä, että hakuarvolle etsitään lähintä vastaavuutta. Jos haluat kaavan etsivän tarkkaa arvoa, valitse EPÄTOSI. (Korjattu 6.7. 20 typo: oletusarvoksi väitin EPÄTOSI. Kiitos tarkkaavaiselle lukijalle.)

Kaavassa olevat $-merkit tarkoittavat suoraa viittausta joko sarakkeeseen ($A) tai sekä riviin että sarakkeeseen ($G$2). Suoran viittauksen luot F4-näppäimen avulla. Viittaustavan ansiosta voit kopioida kaavan lähes sellaisenaan C-sarakkeeseen.

Kaava B-sarakkeessa: =PHAKU($A2;$G$2:$I$6;2;EPÄTOSI)
Kaava C-sarakkeessa:=PHAKU($A2;$G$2:$I$6;3;EPÄTOSI)

Ei ollut vaikeaa, mutta XHAKU tekee taulukon laatimisesta vieläkin helpompaa:

 

Videolla luotu kaava: =XHAKU($A2;$G$2:$G$6;$H$2:$I$6;”Tuotetta ei löydy”)

XHAKU-funktio tarvitsee toimiakseen hakuarvon, joka esimerkissä on tuotteen koodi sarakkeessa A.
Hakumatriisi on alue, jolta tietoa etsitään eli tässä tuotekoodeja sisältävä G-sarake. Palautusmatriisiksi valitaan alue H2:I6. Huomaa, että XHAKU pystyy palauttamaan tietoa useaan soluun, joten yhden kaavan kirjoittaminen riittääkin tuomaan näkyviin sekä tuotteen nimen että hinnan.

Tykkään XHAKU-funktion tarjoamasta mahdollisuudesta reagoida virhetilanteisiin selkokielisillä ilmoituksilla (argumentti [jos_ei_löydy]. Jos perinteinen #PUUTTUU on kuitenkin mielestäsi parempi virheilmoitus kuin esimerkiksi “Tuotetta ei löydy”, jätä kaikin mokomin tämä kohta tyhjäksi.

XHAKU-funktion valinnaisilla argumenteilla voit säädellä mm. sitä millä tavoin tieto vastaa hakumatriisissa olevaa tietoa. Vastaavuustila kertoo tarkkuuden, jolla tietoa etsitään. Oletus on tarkka vastaavuus, joten sitä ei tarvitse kirjoittaa kaavaan. Hakutila-argumentilla vaikutat etsintäjärjestykseen. Esimerkissä olen tälläkin kohdalla käyttänyt oletusarvoa.

XHAKU toimii myös sivusuunnassa. Aikaisemmin kuvan mukaisessa tilanteessa ostosten loppusummasta riippuva alennusprosentti piti hakea erillisestä aputaulukosta VHAKU-funktiolla, mutta se on nyt tarpeeton. Vihreällä reunustetussa solussa on kaava

=XHAKU(E6;L2:O2;L3:O3;;-1)

XHAKU korvaa VHAKU-funktion

Kaava etsii hakuarvona olevaa ostosten loppusummaa vastaavaa lukua aputaulukosta sarakkeesta L riviltä 2 alkaen ja palauttaa seuraavalta riviltä alennusprosentin. Sen määrittelemiseen tarvitsin vastaavuustila-argumenttia, jonka arvolla -1 Excel etsii hakumatriisista hakuarvon tarkkaa vastinetta tai ensimmäistä sitä pienempää arvoa ja palauttaa palautusmatriisista arvon vastaavalta kohdalta.

ageismi ajanhallinta esiintyminen esitysgrafiikka esitysgrafiikkakoulutus Evernote Excel Excel-funktio Excel-koulutus Instagram iOS itsensä johtaminen kaavio kirjat kuva Microsoft Office Microsoft Office -koulutus monipaikkatyö office 365 OneNote OneNote-koulutus oppiminen peliajattelu Pivot-taulukko PowerPoint PowerPoint-koulutus PowerPoint-vinkki presentaatio Prezi Prezi-koulutus some sosiaalinen media sparkline sparkline-kaavio Sway sähköposti tietotyö Tuolin ja näppäimistön välistä tuottavuus työssäoppiminen uusi työ visuaalinen ajattelu visualisointi webinaari yrittäjyys

2 kommenttia “Tutustu Excelin XHAKU-funktioon

  1. Moikka, totta! näinhän sen tietenkin pitää olla. Kiitos kun kerroit, korjataan 🙂 Ihminen on hämmästyttävän sokea omalle tekstilleen.

Vastaa käyttäjälle Kn Peruuta vastaus

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *