Kako uporabljati VLOOKUP v Excelu

VLOOKUP je ena od najbolj uporabnih funkcij programa Excel in je tudi ena izmed najmanj razuma. V tem članku demodificiramo VLOOKUP z resničnim primerom. Ustvarili bomo uporabno računsko kodo za fiktivno podjetje.

VLOOKUP je funkcija Excel .Ta članek bo domneval, da ima bralec že minljivo razumevanje funkcij programa Excel in lahko uporablja osnovne funkcije, kot so SUM, AVERAGE in DODAJ.V svoji najpogostejši uporabi je VLOOKUP funkcija , kar pomeni, da deluje z tabelami baz podatkov - ali preprosto, navaja stvari v Excelovem delovnem listu. Kakšne stvari? No, vse vrste .Morda imate delovni list, ki vsebuje seznam zaposlenih ali izdelkov ali strank ali CD-jev v zbirki CD-jev ali zvezd na nočnem nebu. Ni pomembno.

Primer seznama ali baze podatkov. V tem primeru je seznam izdelkov, ki jih naša izmišljena družba prodaja:

Običajno imajo taki seznami nekakšen enotni identifikator za vsak element na seznamu. V tem primeru je edinstveni identifikator v stolpcu »Oznaka postavke«.Opomba: Za funkcijo VLOOKUP za delo z bazo podatkov / seznama mora ta seznam

imeti stolpec, ki vsebuje enolični identifikator( ali "ključ" ali "ID") in , ta stolpec pa mora biti prvi stolpec v tabeli.Naša vzorec baze podatkov zgoraj izpolnjuje to merilo.

Najtežji del uporabe VLOOKUP-a je razumevanje natančno, za kaj gre. Torej, poglejmo, ali lahko najprej jasno povemo:

VLOOKUP prikliče podatke iz baze podatkov / seznama na podlagi priloženega primerka edinstvenega identifikatorja.

V zgornjem primeru bi vstavili funkcijo VLOOKUP v drugo preglednico s kodo postavke in vrnila vam bo bodisi opis ustreznega predmeta, njegovo ceno ali njegovo razpoložljivost( njena količina "na zalogi"), kot je opisano v vašemprvotni seznam. Kateri od teh informacij vas bo vrnil nazaj? No, odločite se za to, ko ustvarjate formulo.

Če je vse, kar potrebujete, en kos podatkov iz baze podatkov, bi bilo veliko težav pri oblikovanju formule s funkcijo VLOOKUP v njem. Običajno bi tovrstno funkcijo uporabljali v razpredelnici, ki jo je mogoče ponovno uporabiti, na primer predlogo. Vsakič ko nekdo vstopi v veljavno kodo postavke, bi sistem pridobil vse potrebne podatke o ustreznem elementu.

Ustvarimo si primer tega: -račun, ki ga lahko ponovno uporabimo v naši izmišljeni družbi.

Najprej začnemo Excel in ustvarimo si prazen račun:

Tako bo deloval: oseba, ki uporablja predlogo računa, v stolpcu "A" izpolni vrsto elementov postavk, sistem pa bo naložil vsak elementopis in ceno iz naše baze podatkov o izdelkih. Te informacije bodo uporabljene za izračun skupne linije za vsak element( ob predpostavki, da vnesemo veljavno količino).

Za ohranitev tega primera bomo preprosto našli bazo podatkov o izdelku na posebnem listu v isti delovni zvezki:

V resnici je bolj verjetno, da bi bila zbirka podatkov izdelka v ločenem delovnem zvezku. Za funkcijo VLOOKUP je malo razlike, ki ne skrbi, če se baza podatkov nahaja na istem listu, drugem listu ali popolnoma drugačni delovni zvezki.

Tako smo ustvarili bazo podatkov o izdelkih, ki je videti takole:

Da bi preizkusili formulo VLOOKUP, ki jo nameravamo napisati, najprej vnesemo veljavno kodo postavke v celico A11 na našem praznem računu:

premaknite aktivno celico v celico, v kateri želimo, da se podatki, ki jih pridobi iz podatkovne zbirke, shranijo v VLOOKUP.Zanimivo je, da je to korak, v katerem se večina ljudi narobe. Za nadaljnje pojasnilo: izdelali bomo formulo VLOOKUP, ki bo dobila opis, ki ustreza kode postavke v celici A11.Kje želimo ta opis, ko ga dobimo? V celici B11, seveda. Torej, tukaj napišemo formulo VLOOKUP: v celici B11.Sedaj izberite celico B11.

Moramo poiskati seznam vseh razpoložljivih funkcij, ki jih ponuja Excel, da lahko izberemo VLOOKUP in dobimo nekaj pomoči pri izpolnjevanju formule. To najdete tako, da najprej kliknete zavihek Formulas in nato kliknete Vstavi funkcijo :

Pojavi se okence, ki nam omogočajo, da izberete katero koli funkcijo, ki je na voljo v programu Excel.

Če želite poiskati tistega, ki ga iščemo, bi lahko vnesli iskalni izraz, kot je "iskanje"( ker je funkcija, ki nas zanima, funkcija za iskanje ).Sistem nam bo vrnil seznam vseh funkcij, ki so povezane z iskanjem v Excelu. VLOOKUP je drugi na seznamu. Izberite en klik OK .

Prikaže se armaturni elementi , pri čemer se zahtevajo vsi argumenti ( ali parametri ), potrebni za dokončanje funkcije VLOOKUP.To polje lahko zamislite kot funkcijo, ki nas sprašuje o naslednjih vprašanjih:

1. Kateri edinstveni identifikator iščete v zbirki podatkov?
2. Kje je zbirka podatkov?
3. Kateri podatki iz baze podatkov, povezani z edinstvenim identifikatorjem, želite dobiti za vas?

Prve tri argumente so prikazane kot s krepkim , kar pomeni, da so to obvezni argumenti ( funkcija VLOOKUP je nepopolna brez njih in ne bo vrnila veljavne vrednosti).Četrti argument ni krepko, kar pomeni, da je neobvezno:

Dokončali bomo argumente od zgoraj navzdol.

Prvi argument, ki ga potrebujemo, je Lookup_value argument. Funkcija nam mora povedati, kje najti edinstven identifikator( koda artikla v tem primeru), da mora vrniti opis. Izbrati moramo kodo izdelka, ki smo jo vnesli prej( v A11).

Kliknite na ikono izbirnika na desni strani prvega argumenta:

Potem kliknite na celico, ki vsebuje kodo postavke( A11), in pritisnite Vnesite :

V prvem argumentu je vstavljena vrednost "A11".

Zdaj moramo vnesti vrednost za argument Table_array .Z drugimi besedami, VLOOKUPu moramo povedati, kje najti bazo podatkov / seznam. Kliknite na ikono izbirnika poleg drugega argumenta:

Zdaj poiščite bazo podatkov / seznam in izberite celoten seznam - , ki ne vključuje vrstice za glavo .V našem primeru se baza podatkov nahaja na ločenem delovnem listu, zato najprej kliknemo na zavihku delovnega lista:

Nato izberemo celotno bazo podatkov, ki ne vključuje glave:

. .. in pritisnite Vnesite .Vrstica celic, ki predstavlja bazo podatkov( v tem primeru "Produktna baza podatkov!! A2: D7"), se nam samodejno vnese v drugi argument.

Zdaj moramo vnesti tretji argument, Col_index_num .Ta argument uporabljamo, da VLOOKUPu določimo, kateri podatki iz baze podatkov, povezani z našo kodo postavke v A11, želimo, da se nam vrnete. V tem posebnem primeru želimo, da se nam vrne opis elementa .Če pogledate delovni list baze podatkov, boste opazili, da je stolpec »Opis« stolpec drugi v bazi podatkov. To pomeni, da moramo v polje Col_index_num vnesti vrednost:

Pomembno je omeniti, da tukaj ne vstopamo v "2", ker je stolpec "Opis" v stolpcu B nata delovni list.Če se je baza podatkov začela v stolpcu K v delovnem listu, v tem polju še vedno vnesemo »2«, ker je stolpec »Opis« drugi stolpec v nizu celic, ki smo jih izbrali pri določanju »Table_array«.

Na koncu se moramo odločiti, ali želite vnesti vrednost v končni argument VLOOKUP, Range_lookup .Ta argument zahteva resnično ali vrednost false ali pa mora ostati prazen. Pri uporabi VLOOKUP-a z bazami podatkov( kot je to res 90-odstotno), se lahko odločite, kaj naj v tem argumentu uporabimo:

Če prvi stolpec podatkovne baze( stolpec, ki vsebuje edinstvene identifikatorje)je razvrščeno abecedno / številčno po naraščajočem vrstnem redu, potem je v ta argument mogoče vnesti vrednost true ali pustiti prazno.

Če je prvi stolpec podatkovne zbirke ne razvrščen ali pa je razvrščen v padajočem vrstnem redu, potem mora vnesti vrednost false v ta argument

Ker je prvi stolpec naše podatkovne zbirke ni razvrščen,v ta argument vnesemo false :

To je to! Vnesli smo vse podatke, potrebne za VLOOKUP, da vrnejo vrednost, ki jo potrebujemo. Kliknite gumb OK in opazite, da je bil v celico B11 pravilno vnesen opis, ki ustreza koda postavke "R99245":

Formula, ki je bila ustvarjena za nas, je videti takole:

Če v vnesemo različno kodo elementa A11, začeli bomo videti moč funkcije VLOOKUP: Opisna celica se spremeni, da se ujema z novo kodo postavke:

Izvajamo lahko podoben nabor korakov, da dobimo ceno izdelka , vrnjeno v celico E11.Upoštevajte, da je treba novo formulo ustvariti v celici E11.Rezultat bo videti takole:

. .. in formula bo izgledala takole:

Upoštevajte, da je edina razlika med obema formulama tretji argument( Col_index_num ) spremenil iz "2" v "3"( kerželimo podatke pridobiti iz 3. stolpca v bazi podatkov).

Če smo se odločili, da bomo kupili 2 od teh predmetov, bi vnesli "2" v celico D11.V celico F11 nato vnesemo preprosto formulo, da dobimo celotno vrstico:

= D11 * E11

. .., ki izgleda takole. ..

Dokončanje obrazca za račun

Veliko smo se že naučili o VLOOKUP-u. Dejansko smo se naučili vse, kar se bomo naučili v tem članku. Pomembno je omeniti, da se VLOOKUP poleg podatkovnih baz lahko uporablja tudi v drugih okoliščinah. To je manj pogosto in se lahko zajema v prihodnjih člankih How-To Geek.

Naša predloga za račun še ni končana. Da bi to dokončali, bomo naredili naslednje:

1. Odstranili bomo vzorčno kodo iz celice A11 in "2" iz celice D11.To bo naše novo oblikovane formule VLOOKUP prikazalo sporočila o napakah:
   
   To lahko odpravimo z razumno uporabo funkcij IF() in ISBLANK() .Mi spremenimo našo formulo iz tega. .. = VLOOKUP( A11, "Baza izdelkov"! A2: D7,2, FALSE) . .. v ta. .. = IF( ISBLANK( A11), "", VLOOKUP( A11,'! A2: D7,2, FALSE))
   
2. Formule smo kopirali v celice B11, E11 in F11 do preostalega dela vrstice postavk računa. Upoštevajte, da se, če to naredimo, dobljene formule ne bodo več pravilno sklicevale na tabelo zbirke podatkov. To bi lahko popravili s spreminjanjem referenc celic za bazo podatkov na referenčne celice absolutne vrednosti .Druga možnost - in še bolje - lahko ustvarimo nazivno območje za celotno podatkovno bazo izdelkov( na primer »izdelki«) in uporabite to razponsko ime namesto referenčnih celic. Formula bi se spremenila iz tega. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, "Baza izdelkov"! A2: D7,2, FALSE)) . .. v ta. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Products, 2, FALSE)) . .. in nato kopirajte formule do ostalih vrst postavk računa.
3. Verjetno bi "zaklenili" celice, ki vsebujejo naše formule( ali bolje odkleniti celice drugih celic), nato pa zaščititi delovni list, da bi zagotovili, da naše skrbno oblikovane formule niso naključno prepisane, ko nekdo pride do izpolnjevanjana računu.
4. Datoteko bomo shranili kot predlogo , tako da jo bodo lahko znova uporabili vsi v našem podjetju

. Če smo bili občutljivi res pametno , bi ustvarili bazo podatkov vseh naših strank v drugem delovnem listu in nato uporabiliID stranke, vnesen v celico F5, da samodejno izpolni ime in naslov stranke v celicah B6, B7 in B8.

Če želite vaditi z VLOOKUP ali pa preprosto prebrati našo izvirno predlogo za izdajo računov, jo lahko prenesete tukaj.