Tämän Excel -toiminnon sovellus ja määritelmä
VLOOKUP on Excel -toiminto, jonka avulla käyttäjä voi etsiä ja arvioida taulukon sisältöä. Tämä toiminto on saatavana Excel 2007: n Windows- ja Mac -versioina.
Mikä on VLOOKUP?
VLOOKUPin mahdollisia käyttötarkoituksia selitetään tässä esimerkin avulla: Tässä olet suuri kirjallisuuden fani ja olet siksi luonut oman Excel -laskentataulukon, johon voit lajitella keräämäsi kirjat huolellisesti. Jokaiseen työhön lisätään tiedot seuraavista kategorioista:
-
kirjoittaja
-
otsikko
-
Sivunumero
-
Julkaisuvuosi
Nyt haluat antaa ystävällesi kirjavinkin, jonka voit ottaa mukaasi seuraavaan tapaamiseen. Valitettavasti voit ajatella vain kirjoittajaa, et kirjan nimeä. Tässä kohtaa VLOOKUP tulee esille, koska se voi käyttää tätä syöttöarvoa heittääkseen etsimäsi tiedot yhdellä iskulla.
Miten VLOOKUPia käytetään?
Ennen kuin edes ajatellaan kaavojen muotoilua, on määritettävä, missä syöttökenttä ja eri tulostuskentät sijaitsevat myöhemmin. Tätä varten on järkevää luoda erillinen taulukko, joka on aluksi tyhjä ja antaa siten tilaa mainituille tiedoille. Jos suunnittelet tämän uuden taulukon olemassa olevan taulukon esimerkin perusteella, sinulla on aikaa säästävä etu myöhemmin.
Tämän perusteella VLOOKUP -kaava voidaan luoda joko manuaalisesti tai automaattisesti Excelillä. Aloittelijoille kannattaa käyttää jälkimmäistä lähestymistapaa, jotta opitaan asteittain kaavan rakenne ja vaikutus. Tätä varten "Lisää toiminto" -painike valitaan "Kaavat" -välilehdeltä. VLOOKUP on piilotettu avautuvaan ikkunaan. Vahvistuksen jälkeen avautuu jälleen ikkuna, jossa kaavan neljä parametria voidaan täyttää. Nämä ovat:
-
Hakuperuste
-
matriisi
-
Sarakehakemisto
-
Area_reference
Kaavan raaka luonnos näyttää siis tältä:
= VLOOKUP (hakukriteeri, matriisi, sarakehakemisto, alue_linkki)
ja yhdessä mahdollisessa sovelluksessa kuten tämä:
= HAKU (H3; A3: E40; 5)
Hakuperuste
Jotta funktio tietää, mitä arvoa tulisi käyttää lähtökohtana, rivi, joka valittiin syöttökenttään kaksi askelta aiemmin, merkitään "Hakuehto" -kenttään. Esimerkissämme kirjoittajan nimi "Phillip Pulmann" on kirjoitettu sinne. Tämä tekee kaavasta joustavan, eikä sitä tarvitse säätää uudelleen heti, kun syötetty arvo muuttuu.
matriisi
Syöttökenttä "Matriisi" kuvaa taulukkoa, josta tulostettavat tiedot löytyvät. Tämä erityinen matriisi sisältää siis myös kirjan nimen, sivunumeron ja julkaisuvuoden sarakkeet.
Matriisi valitaan kokonaan kerran ilman otsikoita vasemmasta yläkulmasta oikeaan alakulmaan. Näin Excel tietää, mikä sisältö on otettava huomioon arvioitaessa.
Sarakehakemisto
"Sarakeindeksin" syöttökenttä kehottaa käyttäjää määrittämään matriisin sarakkeen, jossa vain haettu arvo on lueteltu. Sarakkeiden määräys on numeroitu kronologisesti. Tämä tarkoittaa, että taulukon ensimmäinen sarake saa arvon 1, toinen arvon 2 jne. Esimerkissämme tämä vastaa sarakeindeksiä 1 tekijälle, sarakeindeksi 2 otsikolle, sarakeindeksi 3 sivunumerolle ja sarakeindeksi 4 julkaisuvuodelta.
Jotta taulukko olisi mahdollisimman joustava, sarakkeen otsikko voidaan linkittää numeron sijaan. Tästä on se etu, että kaava voidaan myös siirtää muille riveille ilman ongelmia, koska sarakkeen otsikkoa voidaan mukauttaa joustavasti joka kerta.
Huomio: VLOOKUP lukee matriisin vasemmalta oikealle, minkä vuoksi sarakehakemisto on sijoitettava sarakkeen oikealle puolelle hakukriteeriä varten, jotta toiminto ottaa sen huomioon!
Area_reference
Parametri "Range_Lookup" täydentää VLOOKUP -kaavan määrittämällä tarkkuuden, jolla taulukko arvioidaan. Se eroaa kuitenkin kaavan aiemmin mainituista komponenteista, koska se on valinnainen. Jos arvo "0" annetaan virheelliselle, Excel etsii vain hakukriteeriksi määritettyä arvoa. Kun arvo 1 on "tosi", ilmeisten arvojen haku kuitenkin jatkuu, jos tarkkaa arvoa ei löydy.
Tämän parametrin määrittäminen on valinnaista, koska arvo 1 on oletusarvoisesti asetettu. Tämä asetus on hyödyllinen myöhemmin edistyneessä VLOOKUPissa, jossa on useita hakukriteerejä.
Yhdistyminen
Heti kun kaikki tarvittavat parametrit on määritetty, VLOOKUP voidaan käyttää. Kun olet syöttänyt hakukriteerin ja vahvistanut toiminnon, etsimäsi arvo näkyy tulostuskenttään määritetyllä rivillä.
Esimerkissämme näytetään nyt kirjan nimi "Kultainen kompassi", joka vastaa kirjoittamaasi kirjailijaa. Sivunumeron ja julkaisuvuoden selvittämiseksi ei tarvitse tehdä muuta kuin vetää olemassa oleva VLOOKUP -kaava seuraaviin soluihin. Tämä on niin helppoa, koska VLOOKUPin sarakehakemisto on linkitetty ensimmäisen taulukon sarakkeen otsikkoon ja toinen taulukko on rakennettu samassa järjestyksessä.
Jos taulukot eroavat toisistaan tai jos kaikesta huolimatta tapahtuu virhe, VLOOKUP -kaavaa voidaan muuttaa myös manuaalisesti. Tätä varten sarakeindeksin toiseksi viimeinen numero on sovitettava tulostettavan uuden arvon sarakkeeseen.
VLOOKUP, jossa on useita hakukriteerejä
Usein käy niin, että yksi hakukriteeri ei riitä suuren Excel -taulukon tarkkaan arviointiin. Sitten on järkevää suorittaa VLOOKUP useilla hakukriteereillä. Tätä varten olemassa olevaa kaavaa on täydennettävä ylimääräisellä IF -funktiolla. Tällä tavalla jopa kahdeksan erilaista hakukriteeriä voidaan ottaa huomioon hakemuksen aikana.
VLOOKUP useissa Excel -laskentataulukoissa
Jos hakukriteeri ei löydy vain yhdestä taulukosta, vaan mahdollisesti myös toisesta taulukosta, VLOOKUP -kaavaa voidaan säätää vastaavasti. Tätä varten sekä if- että ISERROR -funktiot on sijoitettava olemassa olevan kaavan eteen. Tätä varten tarvitaan viisi parametria:
-
Hakuperuste
-
Matriisi 1 ja Matriisi 2
-
Sarakeindeksi1 ja sarakeindeksi2
Tulos näyttää tältä:
= JOS (ISERROR (VLOOKUP (hakuehto, matriisi1, sarake-indeksi1, 0));
VLOOKUP (hakukriteeri; matriisi2; sarakehakemisto2,0); VLOOKUP (hakukriteeri; matriisi1; sarakehakemisto1;))
ja yhdessä mahdollisessa sovelluksessa, kuten tämä:
= JOS (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Hakukriteeriä käytetään etsittävän arvon lisäämiseen kahteen taulukkoon. Matriisi1 ja matriisi2 määrittävät kahden taulukon vastaavat solualueet. Sarakeindeksillä1 ja sarakehakemistolla2 määritetään tarkemmin, mitkä vastaavien taulukoiden sarakkeet on haettava.
Jos etsimäsi arvo esiintyy molemmissa taulukoissa, Excel antaa tuloksen ensimmäisestä taulukosta. Jos arvoa ei kuitenkaan löydy kummastakaan taulukosta, näyttöön tulee virhesanoma. Kaavan etuna on, että näiden kahden luettelon ei tarvitse olla samaa rakennetta tai samaa kokoa.
Määritä arvot luokille käyttämällä VLOOKUP -toimintoa
VLOOKUP -lisätoiminnon avulla luetellut arvot voidaan jakaa automaattisesti valitsemillesi kirjaimille ja predikaateille. Edellisessä esimerkissämme kirjatyypille tulisi lisätä ylimääräinen taulukon sarake. Kirjojen, joiden pituus on enintään 50 sivua, pitäisi kuulua novellityyppiin, kun taas 51-150 sivun kirjoja on novellille ja 151 sivua romaanille. Tämän mahdollistamiseksi VLOOKUPissa ei tarvita ylimääräistä kaavaa, vain kiharat hakasulkeet “{}”. Valmis kaava näyttää tältä:
= VLOOKUP (B1; {1. "Novelli"; 51. "Novella"; 151. "Novel"}; 2)
Kierteisten hakasulkeiden sisältö osoittaa matriisin, joka määrittelee vastaavan kirjatyypin alueen. Sivupituuden määrittäminen sopivalle suvulle on siksi sijoitettu kiharakiinnikkeisiin. Kaava käyttää arvopareja, joista jokainen on erotettu pisteellä. Matriisi {1. "Novelli"; 51. "Novella"; 151. "Novelli"} luetaan seuraavasti:
"Yhdestä näytetään novelli, 51: stä novelli, 151: stä romaani."
Tämä matriisi voidaan helposti mukauttaa erilaisiin tehtäviin. Tämä koskee toisaalta matriisien kokoa ja lukumäärää sekä niiden nimeämistä. On siis mahdollista tulostaa merkkijonoja tai numeroita yksittäisten kirjainten sijasta. Sinun tarvitsee vain säätää kaavan kirjaimia.
VLOOKUP useista laskentataulukoista
VLOOKUPin toisen toiminnon avulla sen käyttäjät voivat linkittää eri laskentataulukoissa olevaa sisältöä. Esimerkistämme tämä vaihtoehto voi olla hyödyllinen, kun tiedot lajitellaan ensin eri laskentataulukoille ja päivitetään sitten yhteenvetotaulukkoon.
Kuvittele, että kirjojesi lisäksi luetteloit kerättyjä elokuvia Excel -laskentataulukkoon. Yhdistä sitten molemmat kokoelmat yhteen suureen taulukkoon.
Tämän menettelyn etuna ei ole vain lisääntynyt järjestys, vaan myös mahdollisten virheiden välttäminen. Jos haluat luoda uuden merkinnän tai päivittää olemassa olevan, sinun ei tarvitse etsiä suuresta taulukosta, vaan voit käyttää pienempiä. Arvot siirretään sitten automaattisesti Excel -yhteenvetotaulukkoon. Tästä syystä suuren taulukon uudelleenkirjoittaminen on tarpeetonta, mikä parhaimmillaan välttää valitettavan liikkeen ja myöhemmän ketjutuksen virheilmoituksista.
Miltä kaava näyttää?
Tämä toiminto on jälleen mahdollista lisäämällä toinen kaava. Vaikka haku useilla kriteereillä vaati ylimääräisen IF -kaavan, useiden laskentataulukoiden kanssa työskentely edellyttää Epäsuoraa kaavaa. Tämä mahdollistaa toisen laskentataulukon alueen määrittämisen VLOOKUP -matriisille.
= VLOOKUP (hakukriteeri; Epäsuora (matriisi); sarakeindeksi; alue_linkki)
Huomio: Tämä kaava toimii vain, jos eri taulukoiden yksittäisillä taulukoilla on samat nimet kuin yleisen taulukon sarakeotsikoilla. Koko taulukot voidaan nimetä "Nimi -kentässä" vasemmassa yläkulmassa soluristikon yläpuolella. Jo nimettyjä taulukoita voi tarkastella näppäinyhdistelmällä Ctrl + F3.
Uusien virheilmoitusten käsittely
Linkitettyjen Excel -taulukoiden kanssa työskentely voi johtaa ei -toivottuihin ongelmiin. Tämä koskee erityisesti vääriä arvoja. Jos tulostetaan väärä arvo 0, Excelin asetuksissa on pieni ongelma, joka voidaan korjata nopeasti.
Yleinen virheilmoitus #NV on sitä vastoin VLOOKUPin tarkoituksellinen toiminto, joka osoittaa käyttäjälle, että vaadittu arvo ei ole käytettävissä. Tämä muistiinpano voidaan suunnitella eri tavalla kaavan avulla.
VLOOKUP - yleiskatsaus
VLOOKUP on hyödyllinen Excel -toiminto, jota voidaan käyttää taulukoiden etsimiseen ja arviointiin. Sen edut näkyvät sen käyttäjäystävällisessä ja joustavassa sovelluksessa. Tällä tavalla jokainen, joka työskentelee säännöllisesti Excel -taulukoiden kanssa, voi hyötyä toiminnosta. Olipa kyseessä yksityinen keräilijä, joka luo omat pienet taulukkonsa, tai suuri yritys, joka käsittelee merkittävästi olennaisempia tietojoukkoja.
Jos toisaalta sinulla on edelleen vastaamattomia pyyntöjä, joita VLOOKUP ei voi täyttää, voit odottaa Excel-lisävaihtoehtoa: Microsoft on tarjonnut Excel 365 -käyttäjille uuden XLOOKUPin vuoden 2022-2023 alusta. Tämä perustuu VLOOKUPin osaamiseen ja täydentää sitä lisätoiminnoilla, joskus jopa yksinkertaisemmilla. Siksi uusi rutiini tietojen arvioinnissa avautuu myös tässä vaiheessa.
