Kuinka käyttää VLOOKUPia Excelissä

VLOOKUP on yksi Excelin hyödyllisimmistä toiminnoista, ja se on myös yksi vähiten ymmärretyistä. Tässä artikkelissa selvitämme VLOOKUPin tosielämän esimerkin avulla. Luomme käyttökelpoisen laskumallin fiktiiviselle yritykselle.

VLOOKUP on Excel- toiminto . Tässä artikkelissa oletetaan, että lukija ymmärtää jo hyvin Excel-funktiot ja voi käyttää perustoimintoja, kuten SUMMA, KESKIARVO ja TÄNÄÄN. Yleisimmässä käytössä VLOOKUP on tietokantatoiminto , mikä tarkoittaa, että se toimii tietokantataulukkojen kanssa - tai yksinkertaisemmin, luetteloiden asioista Excel-laskentataulukossa. Millaisia ​​asioita? No, mikä tahansa asia. Sinulla voi olla laskentataulukko, joka sisältää luettelon työntekijöistä, tuotteista tai asiakkaista, CD-kokoelmasi CD-levyt tai tähdet yötaivaalla. Sillä ei ole väliä.

Tässä on esimerkki luettelosta tai tietokannasta. Tässä tapauksessa se on luettelo tuotteista, joita kuvitteellinen yritys myy:

Yleensä tällaisilla luetteloilla on jonkinlainen yksilöllinen tunniste jokaiselle luettelon kohteelle. Tällöin yksilöivä tunniste on ”Tuotekoodi” -sarakkeessa. Huomaa: Jotta VLOOKUP-toiminto toimisi tietokannan / luettelon kanssa, luettelossa on oltava sarake, joka sisältää yksilöllisen tunnisteen (tai "avain" tai "ID"), ja kyseisen sarakkeen on oltava taulukon ensimmäinen sarake . Yllä oleva esimerkkitietokanta täyttää tämän kriteerin.

VLOOKUPin vaikein osa on ymmärtää tarkalleen, mihin se on tarkoitettu. Katsotaanpa, saammeko ensin selvän:

VLOOKUP hakee tietoja tietokannasta / luettelosta yksilöllisen tunnisteen toimitetun ilmentymän perusteella.

Yllä olevassa esimerkissä lisäisit VLOOKUP-funktion toiseen taulukkoon, jossa on tuotekoodi, ja se palauttaisi sinulle joko vastaavan tuotteen kuvauksen, hinnan tai saatavuuden (sen "varastossa" -määrän) alkuperäisessä kuvauksessa. lista. Mitä näistä tiedoista se välittää sinulle takaisin? No, sinun on päätettävä tämä, kun luot kaavaa.

Jos tarvitset vain yhden osan tietokannasta, olisi paljon vaivaa mennä rakentamaan kaava, jossa on VLOOKUP-funktio. Tyypillisesti käyttäisit tällaista toimintoa uudelleenkäytettävässä laskentataulukossa, kuten mallissa. Aina kun joku syöttää kelvollisen tuotekoodin, järjestelmä hakisi kaikki tarvittavat tiedot vastaavasta tuotteesta.

Luodaan tästä esimerkki: Laskumalli , jota voimme käyttää yhä uudelleen fiktiivisessä yrityksessämme.

Ensin aloitamme Excelin ja luomme itsellemme tyhjän laskun:

Näin se toimii: Laskumallia käyttävä henkilö täyttää sarakkeessa "A" sarjan tuotekoodeja, ja järjestelmä hakee kunkin tuotteen kuvauksen ja hinnan tuotetietokannastamme. Näitä tietoja käytetään laskemaan rivikohtainen summa kullekin tuotteelle (olettaen, että syötämme kelvollisen määrän).

Tämän esimerkin pitämiseksi yksinkertaisena löydämme tuotetietokannan erillisestä taulukosta samasta työkirjasta:

Todellisuudessa on todennäköisempää, että tuotetietokanta olisi erillisessä työkirjassa. VLOOKUP-toiminnolla ei ole juurikaan eroa, mikä ei todellakaan välitä siitä, onko tietokanta samalla taululla, eri arkilla vai täysin erilaisella työkirjalla.

Joten olemme luoneet tuotetietokantamme, joka näyttää tältä:

Testataksemme VLOOKUP-kaavaa, jota kirjoitamme, syötämme ensin kelvollisen tuotekoodin tyhjän laskun soluun A11:

Seuraavaksi siirrämme aktiivisen solun soluun, johon haluamme tallentaa tietokannasta VLOOKUPin hakemat tiedot. Mielenkiintoista on, että tämä on askel, jonka useimmat ihmiset eksyvät. Selittäkää tarkemmin: Olemme luomassa VLOOKUP-kaavan, joka noutaa kuvauksen, joka vastaa alkukoodia solussa A11. Mihin haluamme tämän kuvauksen laitettavan, kun saamme sen? Tietenkin solussa B11. Joten kirjoitamme VLOOKUP-kaavan: soluun B11. Valitse solu B11 nyt.

Meidän on löydettävä luettelo kaikista käytettävissä olevista toiminnoista, joita Excel tarjoaa, jotta voimme valita VLOOKUP ja saada apua kaavan täyttämisessä. Tämä löytyy napsauttamalla ensin Kaavat- välilehteä ja valitsemalla sitten Lisää toiminto :

Näkyviin tulee ruutu, jonka avulla voimme valita minkä tahansa Excelissä käytettävissä olevista toiminnoista.

Löydämme etsimämme kirjoittamalla hakutermin, kuten "haku" (koska kiinnostava toiminto on hakutoiminto ). Järjestelmä palauttaisi meille luettelon kaikista hakuun liittyvistä toiminnoista Excelissä.  VLOOKUP on luettelon toinen. Valitse se napsauttamalla OK .

Näkyviin tulee Function Arguments -ruutu, joka kehottaa meitä antamaan kaikki argumentit (tai parametrit ), joita tarvitaan VLOOKUP-toiminnon suorittamiseen. Voit ajatella tätä ruutua funktiona, joka esittää meille seuraavat kysymykset:

  1. Mitä yksilöivää tunnusta etsit tietokannasta?
  2. Missä tietokanta on?
  3. Mitä tietokannan tietoja, jotka liittyvät yksilölliseen tunnisteeseen, haluat hakea puolestasi?

Kolme ensimmäistä argumenttia on lihavoitu , mikä osoittaa, että ne ovat pakollisia argumentteja (VLOOKUP-funktio on puutteellinen ilman niitä eikä palauta kelvollista arvoa). Neljäs argumentti ei ole lihavoitu, mikä tarkoittaa, että se on valinnainen:

Täydennämme argumentit järjestyksessä ylhäältä alas.

Ensimmäinen argumentti, joka meidän on täytettävä, on Lookup_value- argumentti. Toiminto tarvitsee meitä kertoa sille mistä löytää yksilöllinen tunniste (jäljempänä kohta koodi tässä tapauksessa), että se olisi palaamassa kuvausta. Meidän on valittava nimikoodi, jonka annoimme aiemmin (kohdassa A11).

Napsauta valintakuvaketta ensimmäisen argumentin oikealla puolella:

Napsauta sitten kerran solua, joka sisältää tuotekoodin (A11), ja paina Enter :

A11-arvon arvo lisätään ensimmäiseen argumenttiin.

Nyt meidän on syötettävä arvo Table_array- argumentille. Toisin sanoen meidän on kerrottava VLOOKUPille, mistä tietokanta / luettelo löytyy. Napsauta toisen argumentin vieressä olevaa valintakuvaketta:

Etsi nyt tietokanta / luettelo ja valitse koko luettelo - ilman otsikkoriviä. Esimerkissämme tietokanta sijaitsee erillisellä laskentataulukolla, joten napsautamme ensin kyseistä taulukko-välilehteä:

Seuraavaksi valitsemme koko tietokannan ilman otsikkoriviä:

… Ja paina Enter . Tietokantaa edustava solualue (tässä tapauksessa ”Tuotetietokanta! A2: D7”) syötetään meille automaattisesti toiseen argumenttiin.

Nyt meidän on syötettävä kolmas argumentti, Col_index_num . Käytämme tätä argumenttia määrittelemään VLOOKUP: lle, mitkä tietokannan tiedot liitetään A11: n tuotekoodiin, jotka haluamme palata meille. Tässä nimenomaisessa esimerkissä haluamme, että tuotteen kuvaus palautetaan meille. Jos tarkastelet tietokannan laskentataulukkoa, huomaat, että Kuvaus-sarake on tietokannan toinen sarake. Tämä tarkoittaa sitä, että meidän on syötettävä arvo "2" Col_index_num- ruutuun:

On tärkeää huomata, että emme kirjoita "2" tähän, koska "Kuvaus" -sarake on kyseisen taulukon B- sarakkeessa. Jos tietokanta sattuisi alkamaan laskentataulukon sarakkeesta K , kirjoitamme silti "2" tähän kenttään, koska "Kuvaus" -sarake on toinen sarake solusarjassa, jonka valitsimme määrittäessämme "Taulukkoarra".

Lopuksi meidän on päätettävä, syötetäänkö arvo viimeiseen VLOOKUP- argumenttiin Range_lookup . Tämä argumentti vaatii joko oikean tai väärän arvon tai se on jätettävä tyhjäksi. Kun käytetään VLOOKUPia tietokantojen kanssa (kuten on totta 90% ajasta), tapa päättää, mitä tähän argumenttiin laitetaan, voidaan ajatella seuraavasti:

Jos tietokannan ensimmäinen sarake (sarake, joka sisältää yksilölliset tunnisteet) on lajiteltu aakkosjärjestyksessä / numeerisesti nousevassa järjestyksessä, on mahdollista kirjoittaa tosi arvo tähän argumenttiin tai jättää tyhjäksi.

Jos tietokannan ensimmäistä saraketta ei ole lajiteltu tai se on järjestetty laskevassa järjestyksessä, sinun on syötettävä väärä arvo tähän argumenttiin

Koska tietokannan ensimmäistä saraketta ei ole lajiteltu, kirjoitamme väärän tähän argumenttiin:

Se siitä! Olemme syöttäneet kaikki tarvittavat tiedot VLOOKUP-toiminnolle tarvitsemamme arvon palauttamiseksi. Napsauta OK- painiketta ja huomaa, että tuotekoodia ”R99245” vastaava kuvaus on syötetty oikein soluun B11:

Meille luotu kaava näyttää tältä:

Jos astumme toinen erä koodin soluun A11, alamme nähdä voiman PHAKU toiminto: Kuvaus solu muuttuu vastaamaan uuden kohteen koodi:

Voimme suorittaa samanlaiset vaiheet saadaksesi tuotteen hinnan takaisin soluun E11. Huomaa, että uusi kaava on luotava solussa E11. Tulos näyttää tältä:

… Ja kaava näyttää tältä:

Huomaa, että ainoa ero näiden kahden kaavan välillä on, että kolmas argumentti ( Col_index_num ) on muuttunut arvosta "2" arvoon "3" (koska haluamme, että tiedot haetaan tietokannan kolmannesta sarakkeesta).

Jos päätämme ostaa 2 näistä tuotteista, kirjoitamme "2" soluun D11. Syötämme sitten yksinkertaisen kaavan soluun F11 rivin kokonaismäärän saamiseksi:

= D11 * E1

… Joka näyttää tältä ...

Laskutusmallin täyttäminen

Olemme tähän mennessä oppineet paljon VLOOKUPista. Itse asiassa olemme oppineet kaiken, mitä aiomme oppia tässä artikkelissa. On tärkeää huomata, että VLOOKUPia voidaan käyttää muissa olosuhteissa kuin tietokannoissa. Tämä on harvinaisempaa, ja sitä voidaan käsitellä tulevissa How-To Geek -artikkeleissa.

Laskumallimme ei ole vielä valmis. Sen suorittamiseksi teemme seuraavat:

  1. Poistettaisiin näytekoodikoodi solusta A11 ja "2" solusta D11. Tämä saa uudet VLOOKUP-kaavat näyttämään virheilmoituksia:



    Voimme korjata tämän käyttämällä järkevästi Excelin IF () - ja ISBLANK () -funktioita. Muutamme kaavan tästä ...      = VLOOKUP (A11, 'Tuotetietokanta'! A2: D7,2, EPÄTOSI) ... tähän ... = JOS (ISBLANK (A11), "", VLOOKUP (A11, 'Tuotetietokanta'! A2 : D7,2, EPÄTOSI))


  2. Kopioisimme solujen B11, E11 ja F11 kaavat laskun nimikkerivien loppuosaan. Huomaa, että jos teemme tämän, saadut kaavat eivät enää viittaa oikein tietokantataulukkoon. Voisimme korjata tämän muuttamalla tietokannan soluviitteet absoluuttisiksi soluviittauksiksi. Vaihtoehtoisesti - ja vielä parempaa - voisimme luoda alueen nimen koko tuotetietokannalle (kuten "Tuotteet") ja käyttää tätä alueen nimeä soluviittausten sijaan. Kaava muuttuisi tästä ...      = JOS (ISBLANK (A11), ””, VLOOKUP (A11, 'Tuotetietokanta'! A2: D7,2, FALSE)) ... tähän       = = IF (ISBLANK (A11), "" , VLOOKUP (A11, Tuotteet, 2, EPÄTOSI)) … ja sitten kopioi kaavat alaspäin laskuerän muille riveille.
  3. Meillä olisi todennäköisesti ”lock” sisältävien solujen meidän kaavat (tai pikemminkin avata muita soluja), ja sitten suojata laskentataulukon, jotta varmistetaan, että huolellisesti rakennettu kaavat eivät vahingossa päälle, kun joku tulee täyttää laskun.
  4. Tallennamme tiedoston mallina , jotta kaikki yrityksemme jäsenet voisivat käyttää sitä uudelleen

Jos olisimme todella älykkäitä, luomme tietokannan kaikista asiakkaistamme toiselle laskentataulukolle ja käytämme sitten soluun F5 syötettyä asiakastunnusta täyttämään automaattisesti asiakkaan nimi ja osoite soluissa B6, B7 ja B8.

Jos haluat harjoitella VLOOKUPin kanssa tai vain nähdä tuloksena olevan laskumallimme, se voidaan ladata täältä.