INDEKSI ja VASTINE vaiko sittenkin XHAKU?

Julkaistu Julkaistu: Blogi,Excel,Microsoft Office

Hakufunktiot PHAKU ja VHAKU ovat käteviä, mutta niillä on omat rajoituksensa, joiden kiertämiseen on tietyissä tapauksissa käytetty funktioiden INDEKSI ja VASTINE yhdistelmää (INDEX, MATCH). Funktiopari koetaan kuitenkin hankalaksi ja kieltämättä sen toimintaperiaatetta pitää vähän palautella mieleen mikäli edellisestä käyttökerrasta on ehtinyt kulua aikaa.

Tässä artikkelissa kirjoitan kaavat samaan tarkoitukseen sekä INDEKSI- ja VASTINE-parin että XHAKU-funktion avulla. Esimerkkiaineistona on Wikipediasta peräisin oleva taulukko, jossa on maailman valtioiden väkilukutietoja. Käytin tietojen hakemiseen Power Queryä (Tiedot/Nouda tiedot/Muista lähteistä/Internetistä) ja käsittelin ne Excelin Muotoile taulukoksi -toiminnolla. Kaavojen kirjoittaminen toimii artikkelissa kuvatulla tavalla, jos Excelin asetusten kohdassa Kaavat on valittuna Käytä taulukoiden nimiä kaavoissa (Use table names in formulas). Lisäksi olen antanut taulukoksi muotoilulle alueelle nimen Väkiluku_1 – tähän törmäät esimerkin kaavoissa.

Kuva esimerkkinä olevasta taulukosta.

Haluan hakea taulukosta maan väkiluvun nimen perusteella, mutta PHAKU-funktio ei sovi tarkoitukseen, koska sitä käytettäessä hakuarvon pitää olla taulukkomatriisin eli haun kohteena olevan alueen ensimmäisessä sarakkeessa. Sija-sarakkeen voisi tietysti poistaa tai muuttaa taulukon rakennetta funktiolle sopivaksi, mutta isossa aineistossa tällainen ei välttämättä ole mielekästä tai edes mahdollista. Hakukaavan voi kirjoittaa INDEKSI- ja VASTINE-funktioiden yhdistelmänä kuten seuraavassa tehdään. Selitän funktioiden toiminnan siltä osin kuin olen käyttänyt niitä esimerkissäni, mutta niillä voidaan toki tehdä paljon muutakin.

INDEKSI-funktiolla voit selvittää mitä tietoa on argumentiksi annetulla rivillä tai sarakkeessa tai niiden leikkauskohdassa. Kaava

=INDEKSI(Väkiluku_1[Väkiluku];11)

palauttaisi taulukkoon arvon 126200000, joka on Väkiluku-sarakkeessa rivillä 11 eli Japanin väkiluvun (ks. tarvittaessa kaavojen viittausmerkintöjen tarkempi selitys alempaa).

VASTINE-funktio taasen etsii hakuarvoa vastaavaa tietoa määritellyltä alueelta ja palauttaa sen rivin numeron, jolta tieto löytyy. Esimerkiksi

=VASTINE(”Japani”;Väkiluku_1[Valtio];0)

antaisi vastaukseksi 11 eli rivin, jolta Japani löytyy tällä tietoalueella – huomaa, että otsikot eivät sisälly funktion käyttämiin rivinumeroihin. Kaavan lopussa oleva argumentti 0 tarkoittaa sitä, että hakuarvon on vastattava sarakkeessa olevaa tietoa.

Funktioiden kanssa puljaamisen mielekkyys selvinnee kun ne yhdistetään kirjoittamalla soluun H2 kaava

=INDEKSI(Väkiluku_1[Väkiluku];VASTINE(H1;Väkiluku_1[Valtio];0))

Tämän jälkeen taulukko toimii siten, että kirjoitettaessa värilliseen soluun (H1) valtion nimi, kaava hakee alapuolelle väkiluvun. Kaavassa VASTINE-funktio siis sijoittuu INDEKSI-funktion rivinumeron paikalle ja selvittää miltä taulukon riviltä valtion nimi löytyy. Kun rivinumero on tiedossa kaava hakee sen kohdalta väkiluvun INDEKSI-funktion ilmoittamasta sarakkeesta. Samalla periaatteella voit mm. kirjoittaa kaavan näyttääksesi valtion %-osuuden koko maailman väkiluvusta.

Kaava valmiina

XHAKU-funktion avulla sama onnistuisi kaavalla, johon saadaan mukaan selväkielinen virheilmoituskin:

Videolla kirjoitettu kaava:

=XHAKU(H1;Väkiluku_1[Valtio];Väkiluku_1[Väkiluku];”Tarkista kirjoitusasu!”;0)

Hakuarvona on solun H1 sisältö, jossa olevaa merkkijonoa eli valtion nimeä etsitään Valtio-sarakkeesta. Tämän jälkeen haetaan Väkiluku-sarakkeesta luku vastaavalta kohdalta. Kaavaan on lisätty myös virheilmoitus kaiken varalta, koska lopussa oleva 0 edellyttää, että maan nimi kirjoitetaan oikein.

Ovatko kaavojen viittausmerkinnät outoja?

Kaavojen viittaustapa perustuu Muotoile taulukoksi -toimintoon, joka muuttaa tietoa sisältävän alueen tietokantamaiseksi kokonaisuudeksi eli taulukoksi. Excel antaa taulukoksi muotoilulle alueelle aina oletusnimen Taulukko+numero, mutta parempi on nimetä alue itse jollakin sen sisältöä kuvaavalla tavalla kuten olen tässä tehnyt: Väkiluku_1. Nimen pääsee kirjoittamaan Taulukon muotoilu -välilehden vasemmassa reunassa olevaan ruutuun. Tämän jälkeen taulukon sarakkeisiin voi viitata taulukon nimellä, johon yhdistetään hakasulkuihin kirjoitettu sarakenimi. Ensi hämmennyksen jälkeen tämä kirjoitustapa on helppo ja isoissa aineistoissa solumerkintöjä selkeämpi. En tiedä sinusta, mutta minusta taulukon ja sarakkeiden nimien kanssa kirjoitettu kaava on helpompi lukea kuin esimerkiksi tämä: =INDEKSI(’INDEKSI ja VASTINE (2)’!$C$2:$C$237;VASTINE($H$1;’INDEKSI ja VASTINE (2)’!$B$2:$B$237;0)).

Sarakenimien käytön kaavoissa saa kytkettyä pois päältä, mutta käytäntöön kannattaa totutella etenkin, jos aiot jossakin vaiheessa kirjoittaa DAX-kaavoja ja mittareita Power BI -visualisointeja tehdessäsi!

Ellet ole vielä tutustunut XHAKU-funktion peruskäyttöön, käy lukemassa edellinen postaus: Tutustu Excelin XHAKU-funktioon.

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

Vastaa

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