Tag Archives: vlookup

Nopeampi PHAKU

Tee Excel PHAKU (VLOOKUP) kaavastasi hieman nopeampi parilla pikku kikalla.

Ensiksi älä käytä & -merkkiä yhdistäessäsi hakuehtoa. Luo oma sarake yhdistelylle ja viittaa hakukaavassa siihen.

Siis älä tee näin =PHAKU(A2&B2;Tauli2!$A$1:$C$1000;2;EPÄTOSI)

Luo oma sarake yhdistelylle: =A2&B2 ja viittaa tähän sarakkeeseen PHAKU -kaavassa.

 

Toinen nopeuttava tekijä on; lajittele ensin taulukko josta haet tietoa (hakusarakkeen mukaan).

 

Kolmas mihin usein törmää on JOS -lausekkeen käyttö PHAKU -kaavassa kun halutaan virhearvo pois. Käytä JOSVIRHE -funktiota tällaisissa tilanteissa.

=JOSVIRHE(PHAKU(A2&B2;Tauli2!$A$1:$C$1000;2;EPÄTOSI);” “). Tämä esimerkkikaava jättää solun tyhjäksi jos hakuarvoa ei löydy.

 

Tietojen ryhmittelyä

Tässä kun Pivot- ja PowerPivot kurssia valmistelen tuli taas esiin kysymys tietojen ryhmittelystä ryhmiin, ryhmiin joita ei ole lähdetiedoissa.

Alla kaksi eri esimerkkiä miten sen voisi ratkaista.

Esimerkissäni käytän Accessin Northwind-esimerkki kannasta ottamaani dataa.

Esimerkkidata

Esimerkkidata

Pivot esimerkki:

Haluan laskea myynnin maantieteellisesti jaettuna. Lähdetiedoissa on vain maiden nimet joten joutuisin tämän tekemään manuaalisesti pivotissa joka on hankalaa.

Eräs kiertotapa on että poimin kaikki yksilölliset maat eri tauluun ja laitan siihen mihin maantieteelliseen alueeseen ao. maa kuuluu.

Tietysti voin tämän tehdä suoraan lähdetietoonkin mutta tuhansien rivien päivitys manuaalisesti on hankalaa.

Joten poimin lähteestä yksilölliset maat seuraavasti:

1. Valitsen B-sarakkeen
2. Tiedot, Lajittele ja suodata, Lisäasetukset – Vain ainutkertaiset tiedot (Data, Sort & Filter, Advanced – Unique records only)
3. Kopioin tuloksen ja liitän sen uuteen taulukkoon
(Olisin kyllä voinut lähteä liikkeelle uudesta taulukosta ja erikoissuodatuksella saada sinne kaikki yksilölliset maat mutta tällä kertaa teen sen näin)
4. Määrittelen mihin alueeseen kukin maa kuuluu (manuaalisesti) uuden taulukon B -sarake.

Ryhmät

Ryhmät

5. Tämän jälkeen siirryn lähdetietoon ja luon sinne uuden sarakkeen PHAKU (VLOOKUP) kaavalla joka poimii luomastani uudesta taulusta ryhmän.

H-sarakkeen kaava: =PHAKU(B2;Groups!A:B;2;EPÄTOSI), (=VLOOKUP(B2;Groups!A:B;2;FALSE))

Jos taulu on määritelty Taulukoksi niin kaava kopioituu automaattisesti kaikille riveille.

Valmis data

Valmis data

Nyt voin ryhmitellä pivotissani tiedot tämän ryhmän mukaan.

 PowerPivot esimerkki:

Kopioin yksilölliset maat omaksi tauluksi PowerPivottiin ja lisäsin varisainaiseen tauluun uuden sarakkeen johon laitoin kaavan:

=LOOKUPVALUE(Ryhmat[Ryhmä];Ryhmat[Country];[Country])
(Ryhmat on uusi taulu jonka loin)

PowerPivot esimerkki

PowerPivot esimerkki

Kaava etsii maan luomastani taulusta ja palauttaa ryhmän johon ao. maa kuluu.
Funktion LOOKUPVALUE syntaksi on:
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
result_columnName = sarake josta arvo palautetaan
search_columnName = sarake josta hakuarvo etsitään
search_value = arvo jota etsitään

PowerPivotista saat yksilölliset rivit esimerkiksi viemällä ne pivot-taulukoksi ja sitten kopioi tuloksen takasin uuteen taulukkoon PowerPivottiin.

 

Kahden listan vertailu

Verrataan kahta listaa keskenään ja poimitaan toisesta listasta tietoja ensimmäiseen.
Tähän on kätevä käyttää funktiota PHAKU (VLOOKUP).
Esimerkissä poimitaan henkilön palkka palkat.xlsx –työkirjasta.
Kaava solussa C2 on =PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI).
=VLOOKUP(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;FALSE)

PHAKU (VLOOKUP)

PHAKU (VLOOKUP)

Hakuarvo on tässä tapauksessa se arvo jonka perusteella toisesta taulukosta haetaan tietoja.
Taulukko_matriisi on se taulukko alue jonka vasemmanpuoleisesta sarakkeesta arvoa haetaan.
Sar_indeksi_nro on sen sarakkeen numero valitulla taulukkoalueella josta arvo poimitaan.
Alue_halu on epätosi koska halutaan löytää täsmällinen vastine.

Taulu josta haetaan tietoja kannattaa lajitella hakuarvon perusteella.

Mikäli tietoja ei toisesta taulusta löydy palauttaa funktio virhearvon #PUUTTUU! (#N/A).
Tämän saat pois seuraavalla tavalla kaavalla (Excel versiot 2007 ja uudemmat):
=JOSVIRHE(PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI);”Ei löydy”)
JOSVIRHE = IFERROR
EPÄTOSI = FALSE
Kaavan tekstin “Ei löydy” sijasta voit käyttää mitä tahansa haluamaasi tekstiä tai sitten “” jotta solu jäisi tyhjäksi.
=JOSVIRHE(PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI);””)

Jos käytössäsi on vanhempi Excel voit poistaa virhesanoman esimerkiksi seuraavanlaisella kaavalla:
=JOS(ONVIRHE(PHAKU(A3;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI));””;PHAKU(A3;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI)).
JOS = IF
ONVIRHE = ISERROR
Huomaa että tässä kaavassa haku suoritetaan kaksi kertaa joka tekee kaavasta hitaan.

Tehosta Excel kaavoja

Järkeä PHAKU (VLOOKUP) –funktion käyttöön.

Kun halutaan välttää PHAKU-funktion palauttama virhe, niissä tapauksissa kun hakuarvoa ei löydetä, käytetään usein rakenteeltaan seuraavan laista kaavaa:

=JOS(ONVIRHE(PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI));””;PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI))

Eli JOS (IF) ja ONVIRHE (ISERROR) funktiolla testataan palauttaako PHAKU virheen. Oheisessa esimerkkikaavassa PHAKU suoritetaan kaksi kertaa, mikäli arvo löydetään. Ensin tarkistetaan palauttaako virheen ja mikäli ei palauta virhettä suoritetaan haku uudestaan arvon palauttamiseksi.

Kaava voidaan optimoida käyttämällä JOSVIRHE (IFERROR) funktiota seuraavasti:
=JOSVIRHE(PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI);””)
Nyt suoritetaan PHAKU vain kerran jolloin laskenta nopeutuu.

Kannattaa myös lajitella se sarake josta PHAKU-funktiolla haetaan.

Usein etsitään tietoja myös samasta taulusta samalla hakuarvolla useista sarakkeista PHAKU –funktiolla.
Edullisempi tapa saattaa usein olla PHAKU –funktion korvaaminen VASTINEN (MATCH) ja INDEKSI (INDEX) –funktiolla.
VASTINE palauttaa rivinumeron jolla hakuarvo sijaitsee ja INDEKSI –funktiolla poimitaan tieto halutusta sarakkeesta. Täten tehdään haku vain kerran.

Esimerkki:

Hakufunktiot