Unohda PHAKU, kohta tulee XHAKU-funktio

Excelin yksi suurimmista uudistuksista pitkään aikaan on käynnissä. Microsoft päivittää Excelin taustalla toimivaa laskentakonetta, joka mahdollistaa dynaamiset matriisit ja sen mukana useita uusia funktioita. Näistä mielenkiintoisin on XHAKU, joka korvaa PHAKU-funktion.

Elokuussa 2019 julkisuuteen tullut XHAKU-funktio on jo nyt saatavilla Office Insider -version käyttäjille ja tulee pian kaikkien Excel-käyttäjien saataville. Tällä hetkellä (13.11.2019) sitä ei ole vielä julkaistu kaikille. XHAKU tulee kuitenkin olemaan hakufunktioiden uusi standardi, joten se kannattaa ottaa haltuun heti.

Käydään nyt läpi XHAKU-funktion toiminnallisuus, uudet ominaisuudet ja parannukset verrattuna aikaisempaan kuningasfunktioon, eli PHAKU-funktioon (englanniksi VLOOKUP).

XHAKU-funktio (englanniksi XLOOKUP)

XHAKU-funktiolla yhdistetään dataa. Sillä voi hakea yhdistävän tekijän avulla dataa yhdestä taulukosta toiseen.

Yksinkertaisessa esimerkissä voimme hakea tilaukset-taulukkoon tuotteelle hinnan hinnasto-taulukosta yhteisen tekijän, tässä tapauksessa tuotetunnuksen, avulla.

Kokeile itse XHAKU-funktiota interaktiivisella opetusvideolla

Toivottavasti XHAKU-funktion tekeminen jäi mieleen interaktiivisella opetusvideollamme. Jos tämä tuntui luontevalta tavalta oppia, löytyy Futuedu:sta niitä paljon lisää.

Katsotaan seuraavaksi tarkemmin XHAKU-funktion syntaksia ja sen ominaisuuksia.

Syntaksi

XHAKU-funktion syntaksi on hieman monimutkaisen näköinen:

=XHAKU(hakuarvo; haku_matriisi; palautusmatriisi; [jos_ei_löydy]; [vastaavuustila]; [hakutila])

XHAKU-funktioon voi siis syöttää yhteensä 6 argumenttia, näistä pakollisia on kuitenkin vain 3. Hakasuluissa olevat argumentit eivät ole pakollisia.

Käydään kuitenkin funktion kaikki argumentit läpi, sillä ne mahdollistavat useita eri toiminallisuuksia, jotka käymme läpi alempana.

hakuarvo - Mitä arvoa haetaan, eli yhteinen tekijä.

haku_matriisi - Mistä arvo haetaan. Tämä voi olla sarake tai rivi.

palautusmatriisi - Mistä arvo palautetaan. Tämä voi olla sarake, rivi tai alue. Sen tulee kuitenkin olla yhtä korkea tai leveä kuin haku_matriisi.

[jos_ei_löydy] - Mitä funktio palauttaa, jos arvoa ei löydy. Jos tämän vapaaehtoisen argumentin jättää tyhjäksi, näyttää Excel #PUUTTUU-virheen.

[vastaavuustila] - Miten vastaavuudet haetaan. Tähän on neljä vaihtoehtoa:

• 1 Tee haku aloittamalla ensimmäisestä kohteesta (oletus, jos tyhjä)
• -1 Tee haku aloittamalla viimeisestä kohteesta)
• 2 Tee binäärinen haku (vaatii haku_matriisin nousevan lajittelun)
• -2 Tee binäärinen haku (vaatii haku_matriisin laskevan lajittelun)

[hakutila] - Miten Excel suorittaa haun, toisin sanoen etsitäänkö vain tarkkaa arvoa vai tarkkaa arvoa ja joko suurempaa tai pienempää kohdetta.

• 0 tarkka vastine (oletus, jos tyhjä)
• -1 Tarkka vastine tai seuraavaksi pienempi kohde (phaun oletus)
• 1 Tarkka vsatine tai seuraavaksi suurempi kohde
• 2 Yleismerkkihakuvastine (jos haluaa käyttää 'wildcard'-hakua, eli etsiä sisältöä sisältä. Esimerkiksi "*"&hakuarvo&"*" löytää 'hakuarvo':n myös lauseesta 'tässä on hakuarvo esimerkki')

Tärkeimmät ominaisuudet

XHAKU-funktiossa on useita uusia ominaisuuksia, jotka eivät ole aiemmissa hakufunktioissa olleet mahdollisia tai vaativat erilaisia kikkailuja, kuten INDEKSI-VASTINE -funktioiden yhdistämistä.

Haku_matriisi ja palautusmatriisi määritellään erikseen

XHAKU-funktion yksi parhaista ominaisuuksista on, että haku_matriisi, eli alue mistä tietoa haetaan ja palautusmatriisi, eli mistä tieto palautetaan, määritellään erikseen.

Yksi isoimpia PHAKU-funktion heikkouksia oli sen vaatimus, että hakuarvo on sarakkeen ensimmäisessä sarakkeessa. Tämä esti esimerkiksi tiedon hakemisen yhteisen tekijän vasemmalta puolelta.

PHAKU-funktiossa myös määriteltiin palautettava arvo sarakkeen numerolla. Jos sarakkeiden sijaintia muutti tai lisäsi tai poisti sarakkeita, ei PHAKU enää toiminut oikein.

Koska XHAKU-funktiossa haku- ja palautusalueet määritellään soluviittauksin, pysyvät ne oikeina vaikka niiden paikkaa muuttaa tai sarakkeita lisää tai poistaa.

Haku_matriisin ja palautusmatriisin määrittäminen mahdollistaa myös vaakahaun, eli XHAKU korvaa PHAKU-funktion lisäksi myös VHAKU-funktion!

Toimintalogiikka on täysin sama. Valitaan yhteinen tekijä ja määritellään hakualue ja palautusalue.

Oletuksena tarkka vastine

XHAKU-funktion oletuksena oleva tarkka vastine tekee funktion peruskäytöstä nopeampaa ja selkeämpää. Jos haluat etsiä tarkan arvon, riittää, että syötät XHAKU-funktioon vain kolme argumenttia: hakuarvon, haku_matriisin ja palautusmatriisin.

Voit itse määrittää, jos arvoa ei löydy

Tähän asti esimerkit ovat olleet niin sanotusti perushakuja, eli olemme etsineet tarkkaa arvoa. Uusi funktio sisältää kuitenkin paljon muitakin ominaisuuksia, joita hallitaan funktion kolmella viimeisellä argumentilla.

Näistä ensimmäinen on [jos_ei_löydy], joka antaa sinun määritellä mitä hakufunktio palauttaa, jos haettavaa arvoa ei löydy. Jos tämän vapaaehtoisen argumentin jättää tyhjäksi, palauttaa Excel #PUUTTUU-virheen (minun Excel Office Insider -versioni näyttää, että tämä olisi viimeinen, eli 6. argumentti, vaikka se on oikeasti 4. argumentti).

Voit hakea tarkkojen arvojen lisäksi suurempia tai pienempiä arvoja

Voimme hakea XHAKU-funktiossa tarkan arvon sijaan myös tarkkaa ja pienempää tai suurempaa aroa. PHAKU-funktiossa [alue_haku]-ominaisuudella pystyimme hakemaan vain tarkkaa ja pienempää arvoa.

[Hakutila]-argumentissa 0 on tarkka arvo, joka on oletuksena, jos argumentin jättää tyhjäksi. 1 palauttaa tarkan tai suuremman arvon ja -1 tarkan tai pienemmän arvon.

Päivitetyn laskentamoottorin ansiosta haettavien tietojen ei myöskään tarvitse olla lajiteltuna pienimmästä suurimpaan. Tämä oli vaatimus PHAKU-funktion kanssa.

Alla olevassa esimerkissä näkyy kaksi XHAKU-funktion uutta ominaisuutta, jotka PHAKU-funktiossa eivät toimineet: haku tarkalle tai suuremmalle arvolle ja ei-lajiteltu tieto.

XHAKU voi palauttaa yksittäisen arvon lisäksi matriisin

Parannettu laskentamoottori mahdollistaa myös dynaamisten matriisien palauttamisen. Tämä tarkoittaa, että XHAKU-funktio voi palauttaa yksittäisen arvon sijaan usean sarakkeen ja/tai rivin! Tämä onnistuu yksinkertaisesti valitsemalla palautusmatriisiin halutun kokoinen alue.

Huomaa, että tässäkin palautusmatriisin tulee olla olla yhtä korkea tai leveä kuin haku_matriisi. Alla olevassa esimerkissä se on yhtä korkea (4 riviä) kuin haku_matriisi, mutta sisältää useita sarakkeita.

Koska XHAKU-funktio pystyy siis palauttamaan useita arvoja, voimme käyttää näitä arvoja toisen XHAKU-funktion sisällä. Näin pystymme hakemaan kaksiulotteisesti tietoa sekä sarakkeen, että rivin mukaan.

Alla olevassa esimerkissä haemme ensin oikean rivin, jonka haemme toisen XHAKU-funktion rajaamasta vuosineljänneksestä.

Voimme hakea haku_matriisi:sta ensimmäisen tai viimeisen arvon

PHAKU-funktio palautti aina oletuksena ensimmäisen löytämänsä tarkan arvon. XHAKU-funktio mahdollistaa myös viimeisimmän tarkan arvon hakemisen.

Voimme [hakutila]-argumentilla määritellä haetaanko ensimmäinen tulos ylhäältä vai alhaalta (1 hakee ylhäältä, -1 alhaalta). Voimme siten hakea esimerkiksi viimeisimmän tiedon listasta, jonne olemme syöttäneet ajoneuvojen kilometrit kuukausittain.

XHAKU-funktio tuo tullessaan paljon uusia ominaisuuksia. Se korvaa PHAKU-funktion täysin ja mahdollistaa täysin uusia tapoja hakea ja yhdistää dataa. Hyvä Microsoft!