VLOOKUP vai INDEX ja MATCH?

VLOOKUP vai INDEX ja MATCH?

VLOOKUP (PHAKU) on yksi Excelin käytetyimmistä hakufunktioista, vaikka siihen liittyy lukuisia rajoituksia ja puutteita. Funktion suosio perustuu siihen, että sen avulla oppii helposti hakufunktioiden idean. Monille käyttäjille se on kuitenkin jäänyt ainoaksi hakuvälineeksi, vaikkei se olekaan työkirjojen ylläpidon ja hakunopeuden kannalta se fiksuin vaihtoehto.

Jos käytät runsaasti VLOOKUP:ia, lue miksi se on syytä korvata INDEX (INDEKSI) ja MATCH (VASTINE) -funktioiden yhdistelmällä, jotta pystyt jatkossa muuttamaan lähdedataa helpommin joutumatta muokkaamaan kaikkia kaavojasi.

VLOOKUP:in kielioppi

Kerrataanpa pikaisesti VLOOKUP:in kielioppi ennen kuin perehdytään sen pulmiin ja niiden ratkomiseen. VLOOKUP-funktion syntaksi on seuraava:

=VLOOKUP( Etsittävä tieto;  Hakutaulu;  Sarakenumero;  Hakutyyppi  )

Esimerkiksi kaava =VLOOKUP( ”010”;  Kunnat; 6;  FALSE ) etsii Kunnat-nimisen taulukon (table) ensimmäisestä sarakkeesta merkkijonoa ”010” ja poimii tuloksen kuudennesta (6) sarakkeesta (kuvassa Väkiluku).  Viimeinen hakutyyppi-argumentti FALSE (EPÄTOSI) huolehtii siitä, että funktio hakee täsmälleen tietoa ”010” ja antaa tuloksena virheilmoituksen, jos tietoa ei löydy. Mikäli funktion viimeisenä argumenttina olisi TRUE (TOSI) ja etsittävää merkkijonoa ei löytyisi, funktio poimisi arvon ”pienemmän” tiedon kohdalta (kuvassa 009) edellyttäen että aineisto olisi lajiteltu suuruusjärjestykseen ensimmäisen sarakkeen perusteella.

kunnat

VLOOKUP:in ongelmat

  1. Yksi merkittävimmistä VLOOKUP:in ongelmista on kolmantena argumenttina funktiolle välitettävä sarakenumero, mikä voi aiheuttaa aineistojen muutosten yhteydessä valtavasti ylimääräistä työtä. Kuvittele tilanne, jossa sinulla on työkirjassasi kymmenittäin erilaisia VLOOKUP:peja ja lähdedataan tulee uusia sarakkeita. Joudut muokkaamaan jokaisen VLOOKUP-kaavan eli käytännössä käymään läpi ja korjaamaan kaavoissa olevat sarakenumerot.
  2. Toinenkin ongelma liittyy sarakenumeroon, sillä runsaasti numeerista dataa sisältävissä aineistoissa et edes välttämättä huomaa, jos jokin kaava poimii arvon väärästä sarakkeesta. Kun aineisto muuttuu ja VLOOKUP jää korjaamatta, se voi hakea luvun edelleen sarakkeesta 7, vaikka tieto on siirtynyt sarakkeeseen 8.
  3. VLOOKUP edellyttää aina, että etsittävä tieto on hakutaulun ensimmäisessä sarakkeessa eli poimittavan tiedon vasemmalla puolella. Tämän rajoituksen johdosta joudut ehkä muokkaamaan aineistojasi VLOOKUP-hakuihin soveltuviksi.
  4. Kun määrität neljänneksi argumentiksi arvon TRUE, voit poimia pienemmän arvon kohdalta tiedon, mutta se ei osaa poimia etsittävää tietoa suurempaa tietoa.
  5. VLOOKUP on hidas, jos dataa on runsaasti ja hakuja tehdään paljon.
  6. Kun haluat poimia vaakasuuntaiselta alueelta, joudut käyttämään eri funktiota eli HLOOKUP (VHAKU) -funktiota.
  7. Jos haluat noutaa VLOOKUP:illa kahden tai useamman kriteerin perusteella,  sinun pitää lisätä lähtödataan ylimääräinen sarake, johon yhdistät sarakkeet esimerkiksi & tai CONCATENATE-funktion avulla.

INDEX ja MATCH yhdessä

Tutkitaanpa mitä INDEX ja MATCH tekevät sekä erikseen että yhdessä.

INDEX (INDEKSI)

INDEX (INDEKSI) -funktio poimii halutusta sarakkeesta halutulta riviltä arvon:

=INDEX( Sarake josta poimitaanRivinumero )

Esimerkiksi =INDEX( Kunnat[Väkiluku]; 3) poimii Kunnat-taulukon Väkiluku-sarakkeesta kolmannelta riviltä arvon.

MATCH (VASTINE)

Lopullisessa kaavassa ei suinkaan käytetä vakioarvoa 3, vaan se korvataan MATCH (VASTINE) -funktiolla:

=MATCH( Etsittävä tieto;  Sarake; 0 ), missä 0 (nolla) tarkoittaa että etsitään täsmälleen etsittävää tietoa eikä pienempää tai suurempaa arvoa. Jos poimittaisiin pienempi, argumentiksi annettaisiin 1 ja suuremman poimimisessa käytettäisiin argumenttia -1. MATCH-funktio palauttaa tuloksenaan löytämänsä tiedon sijainnin, kuten numeron 3.

Esimerkiksi =MATCH( ”010”; Kunnat[Kuntanro]; 0 ) kertoo monennella rivillä tunnus ”010” sijaitsee Kuntanro-sarakkeessa. Jos tunnusta ei löydy, kaava antaa tulokseksi virheilmoituksen.

MOLEMMAT

Lopputuloksena seuraava kaava suorittaa molemmat ja palauttaa kuntanumeron ”010” väkiluvun:

=INDEX( Kunnat[Väkiluku]; MATCH( ”010”;  Kunnat[Kuntanro];  0 ) )

Seuraavan kuvan esimerkkikaavoissa poimitaan Kuntanro, Väkiluku ja Maakunta soluun B2 valitun kunnan nimen perusteella. Huomaa, että myös kuntanumero voidaan poimia, vaikka etsittävä tieto eli kunnan nimi on sen oikealla puolella!

kunnat-funktioineen

INDEX ja MATCH -yhdistelmän edut

  1. Funktiossa ei kuvata poimittavan sarakkeen sijaintia numerona, vaan viitataan sarakealueeseen (esim. Kunnat[Väkiluku]), joten kaava ei edellytä muokkausta, vaikka aineistoon tulisi uusia sarakkeita. Kaava toimii kunhan sarakkeen otsikko pysyy samana. Mikäli otsikko muuttuu, se on helppo korvata kaikista kaavoista Replace (Korvaa) -komennolla.
  2. Kaava ei voi poimia väärästä sarakkeesta, sillä haku perustuu sarakkeen otsikkoon. Jos sarake häviää datasta, kaava antaa virheilmoituksen.
  3. Sarakkeiden järjestyksellä ei ole merkitystä! Etsittävän tiedon ei tarvitse olla poimittavan tiedon vasemmalla puolella. Etsittävä tieto (esim. Kunnat[Kuntanro]) voi olla vaikka aineiston viimeinen sarake ja haku toimii. Voit myös huoletta muuttaa
  4. INDEX ja MATCH -yhdistelmä osaa hakea myös suuremman arvon kohdalta, kun MATCH-funktiolle annetaan kolmanneksi argumentiksi -1. VLOOKUP kykenee hakemaan vain täsmälleen samaa (FALSE) tai pienempää (TRUE).
  5. INDEX ja MATCH on VLOOKUP-funktiota nopeampi hakumenetelmä, sillä funktioissa prosessoidaan vain kahta saraketta, kun VLOOKUP-funktiolle välitetään argumenttina isompi alue. Nykyisissä Excel-versioissa nopeusero ei tosin enää ole välttämättä niin suuri kuin esim. 1990 tai 2000 -luvun Exceleissä. INDEX ja MATCH -funktioiden tehokkuudesta löytyy runsaasti artikkeleita, kuten Why INDEX MATCH is better than VLOOKUP tai Excel’s Fastest Lookup Methods: The Tested Results
  6. Jos haluat poimia vaakasuuntaiselta alueelta, voit käyttää samaa INDEX ja MATCH -funktoiden yhdistelmää eli funktioille annettavat alueet voivat olla myös rivejä.
  7. Jos haluat poimia kahden tai useamman eri kriteerin perusteella, lähdedataan ei tarvitse lisätä ylimääräistä saraketta vaan voit syöttää INDEX ja MATCH -funktioiden yhdistelmän matriisikaavana. Huomaa, että matriisikaavat pitää hyväksyä Ctrl + Shift + Enter -näppäinyhdistelmällä.

matriisikaava

2 kommenttia artikkeliin ”VLOOKUP vai INDEX ja MATCH?

  1. Paluuviite: VLOOKUP ja MATCH ne yhteen soppii | HExcelligent.fi

  2. Paluuviite: Pitkäveto 1 x 2 ennuste Excelissä | HExcelligent.fi

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s