1Aug
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äytännöllisen laskutoimitusmallin fiktiiviselle yritykselle.
VLOOKUP on Excel -toiminto .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 tietokantataulukoiden kanssa - tai yksinkertaisemmin, näyttää : n asioita Excel-laskentataulukossa. Millaisia asioita? No, tahansa juttu. 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 fiktionaalinen yritys myy:
Tällä tavoin listoilla on jonkinlainen yksilöllinen tunniste jokaiselle listalle. Tällöin yksilöllinen tunniste on "Item Code" -sarakkeessa. Huomaa: Jos VLOOKUP-funktio toimii tietokannan / luettelon kanssa,
-luettelossa : n on oltava sarake, joka sisältää yksilöllisen tunnisteen( tai "avaimen" tai "ID") ja : n, sarakkeen on oltava 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 hakee tiedot tietokannasta / luettelosta, joka perustuu yksilöllisen tunnisteen toimitukseen.
Edellä 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 olevan määrän), kutenalkuperäinen luettelo. 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ä: laskumalli , jota voimme käyttää uudestaan uudestaan uudestaan fiktiivisessä yrityksessä.
Aluksi käynnistämme Excelin, ja luomme itsellemme tyhjän laskun:
Näin se toimii: Laskusatemallin käyttäjä täyttää sarjan alkiotunnuksia sarakkeessa "A" ja järjestelmä hakee jokaisen kohteenkuvaus ja hinta tuotetietokantaamme. Näitä tietoja käytetään laskettaessa rivikohta kunkin kohteen osalta( olettaen, että annamme voimassa olevan määrän).
Tämän esimerkin pitämiseksi yksinkertaisena, löydämme tuotetietokannan erillisessä arkistossa samassa työkirjassa:
Todennäköisesti on todennäköisempää, että tuotetietokanta sijaitsee erillisessä työkirjassa. VLOOKUP-toiminnolla on vain vähän eroa, joka ei todellakaan välitä, jos tietokanta sijaitsee samassa arkistossa, eri arkissa tai täysin erilaisessa työkirjassa.
Joten olemme luoneet tuotetietokannamme, joka näyttää tältä:
VLOOKUP-kaavan testaamiseksi aiomme kirjoittaa, antamalla ensin tyhjän laskun soluun A11 kelvollinen alkion koodi:
Seuraavaksi mesiirrä aktiivinen solu soluun, jossa haluamme VLOOKUP-tietokannasta haettua tietoa tallennettavaksi. Mielenkiintoista on se, että useimmat ihmiset saavat väärin. Jotta selitettäisiin edelleen: Aiomme luoda VLOOKUP-kaavan, joka hakee kuvauksen, joka vastaa alkion koodia solussa A11.Mistä haluamme tämän kuvauksen, kun saamme sen? Solussa B11 tietenkin. Niinpä kirjoitamme VLOOKUP-kaavan: solussa B11.Valitse solu B11 nyt.
Meidän on löydettävä luettelo kaikista Excelin käytettävissä olevista toiminnoista, jotta voimme valita VLOOKUPin ja saada apua kaavion täyttämisessä.Tämä löytyy napsauttamalla ensin -kaavojen -välilehteä ja napsauttamalla -lisätoimintoa :
Näyttöön tulee ruutu, jonka avulla voimme valita jonkin Excelissä käytettävissä olevista toiminnoista.
Löydämme etsimämme, voisimme kirjoittaa hakutermi kuten "lookup"( koska funktio, johon olemme kiinnostuneita, on -haku -toiminto).Järjestelmä palauttaa meille luettelon kaikista Excelin hakuun liittyvistä toiminnoista. VLOOKUP on toinen luettelossa. Valitse se napsautuksella OK .
Näyttöön tulee -funktiota koskeva argumentti -ruutu, joka pyytää meiltä kaikki -argumentit ( tai -parametrit ), jotka tarvitaan VLOOKUP-toiminnon täydentämiseksi. Voit ajatella tätä laatikkoa funktioksi, joka kysyy meiltä seuraavat kysymykset:
- Mikä ainutlaatuinen tunniste etsii tietokannasta?
- Missä tietokanta on?
- Mikä tietokannasta, joka liittyy yksilöivään tunnisteeseen, haluatko hakea sinulle?
Kolme ensimmäistä argumenttia näytetään : llä lihavoituna : llä, mikä osoittaa, että ne ovat : n pakollisia -argumentteja( VLOOKUP-funktio on puutteellinen ilman niitä eikä palauta kelvollista arvoa).Neljäs argumentti ei ole lihavoitu, joten se on valinnainen:
Viimeistämme argumentit järjestyksessä ylhäältä alas.
Ensimmäinen argumentti, joka meidän on täytettävä, on Lookup_value -argumentti. Toiminto vaatii meitä kertomaan, mistä löytää yksilöllinen tunniste( -koodikoodi tässä tapauksessa), että sen pitäisi palauttaa kuvaus. Meidän on valittava aikaisemmin kirjoitettu koodi( A11: ssä).
Napsauta ensimmäisen argumentin oikealla puolella olevaa valitsinkuvaketta:
Napsauta sitten solua, joka sisältää kohdekoodin( A11) ja paina Syötä :
"A11" -arvo lisätään ensimmäiseen argumenttiin.
Nyt on syötettävä arvo Table_array -argumentille. Toisin sanoen, meidän on kerrottava VLOOKUP: lle, mistä löydät tietokannan / luettelon. Napsauta toisen argumentin vieressä olevaa valitsinkuvaketta:
Etsi tietokanta / luettelo ja etsi koko lista - , ei kuitenkaan otsikkoriviltä .Esimerkkinä tietokanta sijaitsee erillisessä laskentataulukossa, joten klikkaamme ensin kyseistä laskentataulua:
Seuraavaksi valitaan koko tietokanta, ei kuitenkaan otsikkoriviltä:
. .. ja paina Syötä .Tietokannan edustavien solujen alue( tässä tapauksessa "Tuotetietokanta A2: D7") syötetään automaattisesti toiselle argumentille.
Nyt meidän on syötettävä kolmas argumentti Col_index_num .Käytämme tätä argumenttia määriteltäessä VLOOKUP: lle, mikä tieto tietokannasta, liittää A11-tuotteidemme koodimme, haluamme palauttaa meille. Tässä nimenomaisessa esimerkissä haluamme, että kohteen kuvaus palaa meille. Jos tarkastelet tietokentän laskentataulukkoa, huomaat, että Kuvaus-sarake on : n toinen -sarake tietokannassa. Tämä tarkoittaa, että Col_index_num -laatikkoon on syötettävä arvo "2":
On tärkeää huomata, että emme anna tässä "2", koska "Kuvaus" -sarakkeessa on B -sarakeettä laskentataulukko. Jos tietokanta alkoi aloittaa taulukon K -sarakkeessa, annamme vielä "2" tälle kentälle, koska "Kuvaus" sarake on solujen joukon toinen sarake, jonka valitsimme määritettäessä taulukkoa.
Lopuksi meidän on päätettävä, syötetäänkö arvo lopulliseen VLOOKUP-argumenttiin Range_lookup .Tämä argumentti edellyttää joko -todellista - tai -vääriä -arvoa tai se pitäisi jättää tyhjäksi. Kun käytät VLOOKUP-tietokantaa tietokantojen kanssa( kuten 90% ajasta), voidaan päättää, mitä tämän argumentin esittämistä voidaan ajatella:
Jos tietokannan ensimmäinen sarake( sarake, joka sisältää yksilölliset tunnisteet)on lajiteltu aakkosjärjestykseen / numeerisesti nousevassa järjestyksessä, on mahdollista syöttää arvo todellinen tähän argumenttiin tai jättää se tyhjäksi.
Jos tietokannan ensimmäinen sarake on eikä lajiteltu tai se lajitellaan laskevassa järjestyksessä, : n täytyy : n syöttää : n väärä -arvo tähän argumenttiin
Koska tietokannan ensimmäinen sarake on ei lajiteltu,annamme vääriä tähän argumenttiin:
That's it! Olemme syöttäneet kaikki tiedot, jotka vaaditaan VLOOKUP: lle palauttaaksemme tarvitsemamme arvon. Napsauta OK -painiketta ja huomaa, että kohteen koodi "R99245" vastaava kuvaus on syötetty oikein soluun B11:
Meille luodut kaava näyttää:
Jos annamme : n eri -kohteen koodin soluunA11, voimme alkaa nähdä VLOOKUP-toiminnon voiman: Kuvaus solu muuttuu vastaamaan uutta kohdekoodia:
Voimme suorittaa samanlaisen vaiheen saadaksemme kohteen hinta palautettu 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 kahden kaavan välillä on kolmas argumentti( Col_index_num ) on muuttunut "2": sta "3": ksi( koskahaluamme 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ä. ..
Laskumallin täyttäminen
Olemme oppineet paljon VLOOKUP: sta toistaiseksi. 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. Täydellistääksemme teemme seuraavasti:
- Poistamme näytteen kohteen koodin solusta A11 ja "2" solusta D11.Tämä aiheuttaa äskettäin luotujen VLOOKUP-kaavojen näyttämisen virheilmoituksille:
Voimme korjata tämän Excelin IF() - ja ISBLANK() -toimintojen järkevällä käytöllä.Muutos kaavasta tästä. .. = VLOOKUP( A11, 'Tuotetietokanta'! A2: D7,2, FALSE) . .. tähän. .. = IF( ISBLANK( A11), "VLOOKUP( A11,'! A2: D7,2, FALSE))
- Kopioimme kaavat soluihin B11, E11 ja F11 alaspäin laskuun sisältyvien rivien riville. Huomaa, että jos teemme tämän, tuloksena olevat kaavat eivät enää viittaa oikein tietokantataulukkoon. Voimme korjata tämän muuttamalla tietokannan soluversioita : n absoluuttisiin -soluviitteisiin. Vaihtoehtoisesti - ja vielä paremmin - voisimme luoda -alueen nimen koko tuotetietokantaan( kuten "Tuotteet") ja käyttää tätä alueen nimeä soluviitteiden sijaan. Kaava muuttuisi tästä. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, "Tuotetietokanta"! A2: D7,2, FALSE)) . .. tähän. .. = IF( ISBLANK( A11), "VLOOKUP( A11, Products, 2, FALSE)) . .. ja ja sitten kopioivat kaavat muille laskuerien riveille.
- Me luultavasti luettaisiin soluihin, jotka sisältävät kaavoja( tai : n avata : n : n muut -solut) ja sitten suojata laskentataulukkoa varmistaaksemme, että huolellisesti rakennettuja kaavoja ei vahingossa korvata, kun joku täyttäälaskussa.
- Me tallennamme tiedoston -mallina , jotta kaikki yritykset voivat käyttää sitä uudelleen
: ssä. Jos olisimme tunne todella : n älykkäämpää, luomme tietokannan kaikille asiakkaillemme toisessa laskentataulukossa ja käytämme sittenasiakastunnus, joka syötetään soluun F5, täyttää asiakkaan nimen ja osoitteen automaattisesti soluissa B6, B7 ja B8.
Jos haluat harjoitella VLOOKUP: lla tai yksinkertaisesti nähdä tuloksena olevan laskumallin, se voidaan ladata täältä.
