Haku taulukosta kahden tai useamman arvon perusteella

Tässä ohjeessa käydään läpi tilanne, jossa sarakkeen C arvo haetaan sarakkeissa A ja B olevien arvojen perusteella.

Eli toisin sanoen, hakulauseelle annetaan kaksi kriteeriä, joiden tulee täyttyä. Tavallinen VLOOKUP (PHAKU), XLOOKUP (XHAKU) tai INDEX(MATCH)) (INDEKSI(VASTINE) ei silloin riitä, sillä niille voi syöttää vain yhden hakuarvon.

Esimerkki tilanteesta voisi olla sellainen, jossa sarakkeessa A on Maa, B on Tuote ja C on Hinta, ja soluun G3 täytyy etsiä virvelin hinta Suomessa.

Esittelen tässä kaksi ratkaisua, joista voit valita omaan tilanteeseesi sopivimman.

Ratkaisu 1: Tee uusi sarake, joka yhdistää sarakkeet A ja B

Esittelen tämän ratkaisun ensin sen vuoksi, että se on yksinkertaisempi.

Tämä ratkaisu mahdollistaa perinteisten VLOOKUPin ja INDEX(MATCH)in käyttämisen, mutta ei ole kuitenkaan suositeltavaa suurempien datamäärien kanssa, sillä silloin taulukkoon tulee turhaan uusi sarake.

Pienien taulukoiden kanssa kikkaillessa taas… jos siltä tuntuu, niin miksi ei.

Luodaan ensin uusi sarake valitsemalla sarake C ja painamalla Ctrl ja +. Näin luot uuden sarakkeen.

Anna sitten sarakkeelle nimi, esimerkiksi Maa&Tuote. Kirjoita kaavaksi A2&” – ”&B2, jolloin uuteen sarakkeeseen tulee arvot muotoon ”Suomi – Satula” ja niin edelleen.

Nyt voit käyttää tätä uutta arvoa VLOOKUPissa normaaliin tapaan, eli

=VLOOKUP(”Suomi – Virveli”;C:D;2;FALSE)

Toinen tapa: Taulukkofunktio

Seuraavaksi esittelen tavan, jota rautaisimmat ammattilaiset käyttää.

Lupaan, että kun lähetät kollegalle työkirjan, joka sisältää tämän kaavan, pisteesi Excel-osaajana nousevat huomattavasti.

Tämä tapa on myös sen vuoksi parempi, että se on vain yksi kaava, eikä alkuperäiseen aineistoon tarvitse tehdä muokkauksia. Suosittelenkin siis ehdottomasti tätä isoihin taulukoihin.

Esittelen ensin kaavan ja sanon vaikean sanan : array function

{=INDEX(D:D;MATCH(1;("Suomi"=A:A)*("Virveli"=B:B);0))}

Kutsutaan sitä tässä taulukkofunktioksi. Jutun lopussa on pieni kommentti tähän ilmaukseen liittyen.

Miten se toimii?

Tämän kaavan ymmärtäminen vaatii hieman matemaattista päättelykykyä. Keskeistä on seuraavat asiat:

• Index-funktio, jonka avulla haetaan solun arvo sijainnin perusteella
• Fakta, että Tosi/Epätosi voidaan esittää luvuilla 1 ja 0
• Ctrl + Shift + Enter, jota tarvitaan taulukkofunktion kirjoittamiseen

Kerrataan ensin MATCH-funktio. Match-funktio siis toimii kuten VLOOKUP, mutta varsinaisen arvon sijaan palauttaa solun järjestysnumeron listassa. Minkä luvun alla oleva kaava palauttaa?

Vastaus on 4.

Tehdään seuraavaksi tämä yksinkertainen asia vähän eri tavalla.

1800-luvulla englantilainen matemaatikko ja filosofi nimeltään George Boole keksi, että logiikassa tosi voidaan ilmaista luvulla 1 ja epätosi luvulla 0. Samalla hän tuli keksineeksi yhden tietotekniikan peruspilareista, vaikka ei elinaikanaan näistä hedelmistä päässytkään nauttimaan.

Tein alla olevaan taulukoon hetkeksi sarakkeen, jossa näkyy kaavan =”Virveli”=B2 tulokset. Kuten näemme, arvo on FALSE kaikissa muissa, paitsi niissä, joissa on sana Virveli. Näissä se on TRUE, jota siis voimme ilmaista myös luvulla 1.

Tehdään seuraavksi ensimmäinen taulukkofunktio. Katsotaan alla olevaa kaavaa.

Siinä hakuehtona käytetään lukua 1, ja hakutaulukkona on kaava (”Virveli”=B:B) *1.

Kuten äsken näimme, tuo funktio antaa jokaiselle riville arvon sen mukaan, onko siellä virveli vai ei. Jotta tuon saa muunnettua numeroksi, kerromme sen luvulla 1.

Myös tämä kaava antaa vastaukseksi luvun 4.

Seuraavaksi voimme laajentaa funktiota siten, että lisäämme kertolaskuun sarakkeen Maa. Haemme tällä kertaa virvelin hinnan Ruotsissa, sillä se tulee taulukossa Suomen jälkeen. Tähän mennessä saaneet aina hinnan Suomessa, joka tulee ensimmäisenä.

Nyt saamme vastaukseksi luvun 7, joka on vastauksen rivinumero taulukossa. Seuraavaksi meidän täytyy vain syöttää tämä luku INDEX-funktiolle, eli:

Index-funktion ensimmäinen kohta D:D on siis se sarake, josta haluamme poimia hinnan, ja MATCH-kohta on sama kuin edellisessä esimerkissä, joka antaa luvun 7.

Näin saamme siis poimittua Hinta-sarakkeesta seitsemännen arvon.

Tässä esimerkissä käytimme hakuarvoina kahta saraketta, mutta voit lisätä samaan malliin kertolaskuun useampiakin sarakkeita. Muista vain aina painaa Ctrl+Shift+ Enter silloin, kun normaalisti painaisit kaavan kirjoituksen jälkeen vain Enter.

Kommentti käännökseen liittyen

Tässä kohtaa tietotekniikka on kehittynyt niin nopeasti, että suomen kieli ei ole pysynyt perässä. Kääntäisin itse tuon sanan taulukkofunktioksi, sen vuoksi että normaaleista funktioista poiketen sille voi syöttää yhden arvon sijaan kokonaisen taulukon.

Microsoftin omilla sivuilla koneen antama käännös on joko:

• matriisikaava
• dynaaminen matsiisi kaava, tai
• Täsmähakukonekaava (?)

Joskus tulee myös törmättyä sanaan CSE-funktio, joka tulee sanoista Ctrl+Shift+Enter.

Mikä sinun mielestäsi on hyvä sana taulukkofunktiolle?

Jäikö jotain kysyttävää? Jätä kommentti!