VLOOKUP on eräs Excelin hyödyllisimmistä toiminnoista, ja se on myös yksi vähiten ymmärretty. Tässä artikkelissa me demystify VLOOKUP todellisen elämän esimerkin avulla. Luomme käyttökelpoisen Laskun malli fiktiiviselle yritykselle.
VLOOKUP on Excel toimia. Tässä artikkelissa oletetaan, että lukijalla on jo ohitettu Excel-toimintojen ymmärtäminen ja hän voi käyttää perustoimintoja, kuten SUM, AVERAGE ja TODAY. Yleisimmässä käytössä VLOOKUP on tietokanta toiminto eli se toimii tietokantapöytien kanssa - tai yksinkertaisemmin, luettelot asioita Excel-laskentataulukossa. Millaisia asioita? Hyvin, minkä tahansa eräänlainen asia. Sinulla voi olla laskentataulukko, jossa on luettelo työntekijöistä tai tuotteista tai asiakkaista tai CD-levyistä CD-kokoelmissasi tai tähdet yötaivasta. Ei ole väliä.
Tässä on esimerkki luettelosta tai tietokannasta. Tässä tapauksessa se on luettelo tuotteista, joita fiktiivinen yritys myy:
Tällä tavoin listoilla on eräänlainen yksilöllinen tunniste jokaiselle listalle. Tällöin yksilöllinen tunniste on "Item Code" -sarakkeessa. Huomaa: Jos VLOOKUP-funktio toimii tietokannan / luettelon kanssa, luettelossa on oltava sarake, joka sisältää yksilöllisen tunnisteen (tai "avaimen" tai "ID") ja tämä sarake on taulukon ensimmäinen sarake. Edellä oleva esimerkkitietokanta täyttää tämän kriteerin.
VLOOKUPin vaikein osa on ymmärtää tarkalleen, mitä se on. Katsotaanpa sitten, voimmeko saada sen selville ensin:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Yllä olevassa esimerkissä sijoittaisit VLOOKUP-toiminnon toiseen laskentataulukkoon, jossa on alkion koodi, ja palauttaa sinulle joko vastaavan kohteen kuvauksen, sen hinnan tai sen saatavuuden (sen "varastossa") alkuperäisessä muodossaan kuvatulla tavalla lista. Kumpi näistä tiedoista paljastaa sinut takaisin? No, pääset päättää, kun luot kaavan.
Jos tarvitset vain yhden tietokannasta, olisi vaikeaa mennä rakentamaan kaava, jossa on VLOOKUP-funktio. Tyypillisesti käytät tällaisia toimintoja uudelleenkäytettävissä laskentataulukossa, kuten mallissa. Joka kerta kun joku syöttää voimassa olevan kohteen koodin, järjestelmä hakee kaikki tarvittavat tiedot vastaavasta tuotteesta.
Luomme esimerkin tästä: An Laskun malli että voimme käyttää uudestaan yhä uudelleen fiktiivisessä yrityksessä.
Ensin aloitamme Excelin, ja luomme itselle tyhjä lasku:
Jotta tämä esimerkki olisi yksinkertainen, löydämme tuotetietokannan erillisessä arkistossa samaan työkirjaan:
Joten olemme luoneet tuotetietokantaamme, joka näyttää tältä:
Meidän on löydettävä luettelo kaikista Excelin tarjoamista käytettävissä olevista toiminnoista, jotta voimme valita VLOOKUPin ja saada apua kaavion täyttämisessä. Tämä löytyy napsauttamalla ensin kaavat välilehti ja napsauta sitten Lisää toiminto:
Löydämme etsimämme, voisimme kirjoittaa hakutermin, kuten "lookup" (koska toiminto, josta me olemme kiinnostuneita, on Katso ylös toimia). Järjestelmä palauttaa meille luettelon kaikista Excelin hakuun liittyvistä toiminnoista. VLOOKUP on toinen luettelossa. Valitse klikkaus kunnossa.
Function Arguments ilmestyy näyttöruutu, joka pyytää meitä kaikkia argumentit (tai parametrit), jotka tarvitaan VLOOKUP-toiminnon täydentämiseksi. Voit ajatella tätä ruutua toiminnaksi, jossa kysytte meiltä seuraavat kysymykset:
- Mikä ainutlaatuinen tunniste etsii tietokannasta?
- Missä tietokanta on?
- Mitkä tietokannasta, joka liittyy yksilölliseen tunnisteeseen, haluatko hakea puolestasi?
Kolme ensimmäistä argumenttia esitetään lihavoituna, mikä osoittaa, että ne ovat pakollinen argumentit (VLOOKUP-funktio ei ole täydellinen ilman niitä eikä palauta kelvollista arvoa). Neljäs argumentti ei ole lihavoitu, joten se on valinnainen:
Ensimmäinen argumentti, jonka meidän on täytettävä, on hakuarvo Perustelu. Toiminto vaatii meitä kertomaan, mistä löytää yksilöllinen tunniste ( tuotekoodi tässä tapauksessa), että sen pitäisi palauttaa kuvaus. Meidän on valittava aikaisemmin kirjoitettu koodi (A11: ssä).
Napsauta valintanäppäintä ensimmäisen argumentin oikealla puolella:
Napsauta sitten kerran solussa, joka sisältää kohdekoodin (A11), ja paina Tulla sisään:
Nyt meidän on syötettävä arvo Pöytäryhmä Perustelu. Toisin sanoen, meidän on kerrottava VLOOKUP: lle, mistä löydät tietokannan / luettelon. Napsauta toisen argumentin vieressä olevaa valintanäkymää:
Etsi tietokanta / luettelo ja valitse sitten koko luettelo - ei kuitenkaan otsikkoriviltä. Esimerkissämme tietokanta sijaitsee erillisessä laskentataulukossa, joten klikkaamme ensin kyseistä laskentataulua:
… ja paina Tulla sisään. Tietokannan edustavien solujen alue (tässä tapauksessa "Tuotetietokanta A2: D7") syötetään automaattisesti toiselle argumentille.
Nyt meidän on annettava kolmas argumentti, Col_index_num. Käytämme tätä argumenttia määriteltäessä VLOOKUP: lle, mikä tieto tietokannasta, yhdistää A11-nimikkeemme koodi, haluamme palauttaa meille. Tässä nimenomaisessa esimerkissä haluamme saada kohteen kuvaus palautti meille. Jos tarkastelet tietokannan laskentataulukkoa, huomaat, että Kuvaus-sarake on toinen sarakkeessa tietokannassa. Tämä tarkoittaa, että meidän on syötettävä arvo "2" Col_index_num laatikko:
On tärkeää huomata, että emme kirjaudu "2" tähän, koska "Kuvaus" -sarakkeessa on B sarakkeessa. Jos tietokanta alkoi aloittaa sarakkeessa K, kirjoittaisimme vielä kentän "2", koska "Kuvaus"-sarake on solujen joukon toinen sarake, jonka valitsimme määritettäessä "taulukkoarra".
Lopuksi meidän on päätettävä, syötetäänkö arvo lopulliseen VLOOKUP-argumenttiin, hakualue. Tämä väite edellyttää joko a totta tai väärä arvoa, tai se pitäisi jättää tyhjäksi. Kun käytät VLOOKUP-tietokantoja tietokantoihin (kuten 90% ajasta), voidaan päättää, mitä tämän argumentin esittämistä voidaan ajatella seuraavasti:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Tietokannan ensimmäinen sarake on ei lajitellaan, kirjoitamme väärä tähän väitteeseen:
Se siitä! Olemme syöttäneet kaikki tiedot, jotka vaaditaan VLOOKUP: lle palauttaaksemme tarvitsemamme arvon. Klikkaa kunnossa -painiketta ja huomaa, että nimikkeen "R99245" mukainen kuvaus on syötetty oikein soluun B11:
Jos annamme a eri elementtikoodi soluun A11, näemme VLOOKUP-toiminnon voiman: Kuvaus solu muuttaa uuden kohteen koodin mukaiseksi:
Voimme suorittaa samanlaisia vaiheita kohteen saamiseksi hinta palasi soluun E11. Huomaa, että uusi kaava on luotava solussa E11. Tulos näyttää tältä:
Huomaa, että näiden kahden kaavan välinen ero on vain kolmas argumentti (Col_index_num) on muuttunut "2": sta "3": ksi (koska haluamme tietojen keräämisen tietokannan kolmannesta sarakkeesta).
Jos päätimme ostaa 2 näistä tuotteista, annamme "2" soluun D11. Sitten annamme yksinkertaisen kaavan soluun F11 saadaksesi rivin kokonaismäärän:
=D11*E11
… joka näyttää tältä …
Laskutemallin täyttäminen
Toistaiseksi olemme oppineet paljon VLOOKUP: sta. Itse asiassa olemme oppineet kaikki, mitä aiomme oppia tässä artikkelissa. On tärkeää huomata, että VLOOKUPia voidaan käyttää muissa olosuhteissa tietokantojen lisäksi. Tämä on harvinaisempaa, ja se voidaan kattaa tulevissa How-To Geek -artikkeleissa.
Laskumallimme ei ole vielä valmis. Jotta voisimme tehdä sen, teemme seuraavasti:
-
Poistamme näytteen kohteen koodin solusta A11 ja "2" solusta D11. Tämä aiheuttaa äskettäin luotujen VLOOKUP-kaavojen näyttämiseksi virheilmoituksia:
Image Voimme korjata tämän Excelin hyödyllisellä tavalla JOS() ja ISBLANK () toiminnot. Me muuttaa kaavaa tästä … = VLOOKUP (A11, 'Tuotetietokanta'! A2: D7,2, FALSE) …tähän… = IF (ISBLANK (A11), "" VLOOKUP (A11, "Tuotetietokanta", A2: D7,2, FALSE))
- Kopioimme kaavat soluihin B11, E11 ja F11 alaspäin jäljellä olevaan laskuun sisältyvien rivien riveihin. Huomaa, että jos teemme tämän, tuloksena olevat kaavat eivät enää viittaa oikein tietokantataulukkoon. Voimme korjata tämän muuttamalla tietokannan soluviitteitä absoluuttinen soluviitteitä. Vaihtoehtoisesti - ja vielä paremmin - voisimme luoda a alueen nimi koko tuotetietokannasta (kuten "Tuotteet") ja käytä tämän alueen nimeä soluviitteiden sijasta. Kaava muuttuisi tästä … = IF (ISBLANK (A11), "" VLOOKUP (A11, "Tuotetietokanta", A2: D7,2, FALSE)) …tähän… = IF (ISBLANK (A11),””, PHAKU (A11, Tuotteet, 2, EPÄTOSI)) …ja sitten kopioi kaavat muille laskuerän riveille.
- Luultavasti luulisimme solujen, jotka sisältävät kaavoja (tai pikemminkin avata muut solut) ja suojaa sitten laskentataulukkoa sen varmistamiseksi, että huolellisesti rakennettuja kaavoja ei vahingossa korvata, kun joku tulee täyttämään laskun.
- Tallenna tiedosto nimellä sapluuna, jotta kaikki yritykset voisivat käyttää sitä uudelleen
Jos tunsimme Todella fiksu, luomme tietokannan kaikille asiakkaillemme toisessa laskentataulukossa ja käytämme sitten soluun F5 syötettyä asiakastunnusta automaattisesti täyttämään asiakkaan nimi ja osoite soluissa B6, B7 ja B8.
