agwebrunner.com

VLOOKUP on üks Exceli kõige kasulikumaid funktsioone ja ühtlasi vähem mõistetav. Selles artiklis demüstifitseerime VLOOKUPi reaalse näite abil. Loome kasutatava Arve mall fiktiivse ettevõtte jaoks.

VLOOKUP on Excel funktsioon. Selles artiklis eeldatakse, et lugejal on Exceli funktsioonidest juba möödapääsmatu arusaam ja ta saab kasutada selliseid põhifunktsioone nagu SUM, KESKMINE ja TÄNA. Kõige tavalisemas kasutuses on VLOOKUP a andmebaasi funktsioon, mis tähendab, et see töötab andmebaasitabelitega - või lihtsamalt, nimekirjad asjadest Exceli töölehel. Milliseid asju? Noh, ükskõik milline mingi asi. Teil võib olla tööleht, mis sisaldab töötajate, toodete või klientide loendit, või CD-sid teie CD-kollektsioonis või tähti öötaevas. See pole tegelikult oluline.

Siin on loendi või andmebaasi näide. Sel juhul on see loetelu toodetest, mida meie fiktiivne ettevõte müüb:

Tavaliselt on sellistel loenditel loendi iga üksuse jaoks mingi unikaalne identifikaator. Sel juhul on unikaalne identifikaator veerus „Üksuse kood“. Märkus. Et funktsioon VLOOKUP töötaks andmebaasi / loendiga, peab sellel loendil olema veerg, mis sisaldab unikaalset identifikaatorit (või „võti” või „ID”) ja see veerg peab olema tabeli esimene veerg. Meie ülaltoodud näidisandmebaas vastab sellele kriteeriumile.

VLOOKUPi kasutamise kõige raskem osa on täpselt aru saada, milleks see on mõeldud. Nii et vaatame, kas saame selle kõigepealt selgeks:

VLOOKUP otsib teavet andmebaasist / loendist ainulaadse identifikaatori tarnitud eksemplari põhjal.

Ülaltoodud näites sisestate funktsiooni VLOOKUP teise arvutustabelisse, millel on üksuse kood, ja see tagastab teile kas vastava üksuse kirjelduse, selle hinna või saadavuse (selle kogus "Laos"), nagu on kirjeldatud originaalis nimekirja. Millise neist andmetest see teile tagasi annab? Valemi loomisel peate selle otsustama.

Kui teil on vaja ainult ühte andmebaasist pärinevat teavet, oleks palju vaeva minna valemi koostamiseks, milles on funktsioon VLOOKUP. Tavaliselt kasutaksite sellist funktsiooni korduvkasutatavas arvutustabelis, näiteks mallis. Iga kord, kui keegi sisestab kehtiva kaubakoodi, hangib süsteem kogu vajaliku teabe vastava kauba kohta.

Koostame selle näite: An Arve mall et saame oma fiktiivses ettevõttes ikka ja jälle taaskasutada.

Kõigepealt käivitame Exceli ja loome endale tühja arve:

See hakkab toimima nii: inimene, kes kasutab arve malli, täidab veerus „A” rea koodikoode ja süsteem hangib iga toote kirjelduse ja hinna meie toodete andmebaasist. Seda teavet kasutatakse iga üksuse rea kogusumma arvutamiseks (eeldades, et sisestame kehtiva koguse).

Selle näite lihtsuse huvides leiame toote andmebaasi sama töövihiku eraldi lehelt:

Tegelikult on tõenäolisem, et toodete andmebaas asub eraldi töövihikus. Funktsioonil VLOOKUP on vähe vahet, mida tegelikult ei huvita, kas andmebaas asub samal lehel, teisel lehel või hoopis teisel töövihikul.

Nii oleme loonud oma toodete andmebaasi, mis näeb välja selline:

Kirjutatava VLOOKUP-valemi testimiseks sisestame tühja arve lahtrisse A11 kõigepealt kehtiva üksuse koodi:

Järgmisena liigutame aktiivse lahtri lahtrisse, kuhu soovime, et VLOOKUP andmebaasist saadud teave salvestataks. Huvitaval kombel eksib see samm enamikul inimestel. Edasiseks selgitamiseks: Oleme koostamas VLOOKUP-i valemit, mis toob välja kirjelduse, mis vastab lahtris A11 olevale üksuse koodile. Kuhu me tahame selle kirjelduse panna, kui me selle saame? Lahtris B11 muidugi. Nii kirjutame sinna valemi VLOOKUP: lahtrisse B11. Valige nüüd lahter B11.

Peame leidma kõigi saadaolevate funktsioonide loendi, mida Excel pakub, et saaksime valida VLOOKUP ja saada valemi täitmisel abi. Selle leiate, klõpsates kõigepealt nuppu Valemid ja klõpsake siis nuppu Sisesta funktsioon:

Ilmub kast, mis võimaldab meil valida mis tahes Excelis saadaolevat funktsiooni.

Otsitava otsingu leidmiseks võiksime sisestada otsingutermini nagu „otsing” (kuna meid huvitav funktsioon on Vaata üles funktsioon). Süsteem tagastaks meile kõigi Excelis otsimisega seotud funktsioonide loendi. VLOOKUP on loendis teine. Valige see klõpsuga Okei.

The Funktsiooni argumendid ilmub kast, mis palub meil kõik argumendid (või parameetrid), mis on vajalik funktsiooni VLOOKUP lõpuleviimiseks. Võite mõelda sellest kastist kui funktsioonist, mis esitab meile järgmised küsimused:

1. Millist unikaalset identifikaatorit otsite andmebaasist?
2. Kus on andmebaas?
3. Millise unikaalse identifikaatoriga seotud andmebaasi osa soovite enda jaoks hankida?

Esitatakse kolm esimest argumenti paksus kirjas, mis näitab, et nad on kohustuslik argumendid (funktsioon VLOOKUP on ilma nendeta puudulik ja ei tagasta kehtivat väärtust). Neljas argument ei ole julge, see tähendab, et see on valikuline:

Lõpetame argumendid järjest ülevalt alla.

Esimene argument, mille peame täiendama, on Otsinguväärtus argument. Funktsioon peab meid ütlema, kust leida kordumatu tunnus ( eseme kood antud juhul), et see peaks tagastama. Peame valima üksuse koodi, mille sisestasime varem (A11-s).

Klõpsake valikuikoonil esimesest argumendist paremal:

Seejärel klõpsake üksuse üksust, mis sisaldab üksuse koodi (A11), ja vajutage Sisenema:

Esimesse argumendi lisatakse "A11" väärtus.

Nüüd peame sisestama väärtusele Tabeli_värv argument. Teisisõnu, peame VLOOKUPile ütlema, kust andmebaasi / loendi leida. Klõpsake valikuikoonil teise argumendi kõrval:

Nüüd leidke andmebaas / loend ja valige kogu loend - välja arvatud päiserida. Meie näites asub andmebaas eraldi töölehel, seega klõpsame kõigepealt sellel töölehe vahekaardil:

Järgmisena valime kogu andmebaasi, välja arvatud päiserida:

... ja vajutage Sisenema. Andmebaasi esindav lahtrivahemik (antud juhul „’ Product Database ’! A2: D7“) sisestatakse meie jaoks automaatselt teise argumendini.

Nüüd peame sisestama kolmanda argumendi, Col_index_num. Selle argumendi abil täpsustame VLOOKUP-ile, milline andmebaasist pärinev teave on seotud meie üksuse koodiga A11 ja me tahame, et oleksime meile tagasi pöördunud. Selles konkreetses näites soovime, et üksus oleks olemas kirjeldus naasis meie juurde. Kui vaatate andmebaasi töölehte, märkate, et veerg „Kirjeldus” on teine veerg andmebaasis. See tähendab, et peame sisestama väärtusesse „2” Col_index_num kast:

Oluline on märkida, et me ei sisesta siia „2”, kuna veerg „Kirjeldus” asub veerus B selle töölehe veerg. Kui andmebaas juhtus veerus alustama K töölehel sisestaksime sellesse välja ikka „2”, kuna veerg „Kirjeldus” on teine ​​veerg lahtrite komplektis, mille valisime „Table_array” määramisel.

Lõpuks peame otsustama, kas sisestada väärtus VLOOKUP lõplikku argumenti, Vahemiku_otsing. See argument nõuab kas a tõsi või vale väärtus või see tuleks jätta tühjaks. VLOOKUP-i kasutamisel andmebaasidega (nagu 90% juhtudest on tõsi) võib mõelda selle argumendi lisamise viisi üle järgmiselt:

Kui andmebaasi esimene veerg (unikaalseid identifikaatoreid sisaldav veerg) on ​​järjestatud tähestiku / numbri järgi kasvavas järjekorras, on võimalik sisestada väärtus tõsi selle argumendi sisse või jätke see tühjaks.

Kui andmebaasi esimene veerg on mitte või see on järjestatud kahanevas järjekorras, siis sina peab sisestage väärtus vale sellesse argumenti

Nagu meie andmebaasi esimene veerg on mitte sorditud, astume sisse vale selle argumendi juurde:

See on kõik! Oleme sisestanud kogu vajaliku teabe, et VLOOKUP tagaks meile vajaliku väärtuse. Klõpsake nuppu Okei nupp ja pange tähele, et üksuse koodile “R99245” vastav kirjeldus on lahtrisse B11 õigesti sisestatud:

Meie jaoks loodud valem näeb välja selline:

Kui sisestame a erinevad üksuse koodi lahtrisse A11, hakkame nägema funktsiooni VLOOKUP jõudu: kirjeldusrakk muutub uue üksuse koodiga vastavaks:

Saame üksuse hankimiseks teha sarnaseid toiminguid hind naaseb lahtrisse E11. Pange tähele, et uus valem tuleb luua lahtrisse E11. Tulemus näeb välja selline:

... ja valem näeb välja selline:

Pange tähele, et ainus erinevus kahe valemi vahel on kolmas argument (Col_index_num) on muutunud väärtusest “2” väärtuseks “3” (kuna soovime, et andmed saadakse andmebaasi 3. veerust).

Kui otsustaksime osta kaks neist üksustest, sisestaksime lahtrisse D11 „2”. Seejärel sisestame rea lahtrisse F11 lihtsa valemi:

= D11 * E1

... mis näeb välja selline ...

Arve malli täitmine

Oleme VLOOKUPi kohta siiani palju õppinud. Tegelikult oleme selles artiklis õppinud kõike, mida õppima hakkame. Oluline on märkida, et VLOOKUPi saab lisaks andmebaasidele kasutada ka muudel tingimustel. See on vähem levinud ja seda võib käsitleda tulevastes How-To Geeki artiklites.

Meie arve mall pole veel valmis. Selle lõpuleviimiseks teeme järgmist:

1. Eemaldaksime lahtrist A11 näidise üksuse koodi ja lahtrist D11 „2”. See kuvab meie äsja loodud VLOOKUP-valemites veateateid:

   
   

   
   Selle saame parandada Exceli mõistliku kasutamisega KUI () ja ISBLANK () funktsioone. Muudame oma valemit sellest ... = VLOOKUP (A11, tooteandmebaas! A2: D7,2, FALSE)... sellele ...= KUI (ISBLANK (A11), ””, VLOOKUP (A11, ’Toote andmebaas’! A2: D7,2, FALSE))

   
   

2. Kopeerime lahtrites B11, E11 ja F11 olevad valemid arve ülejäänud üksuste ridadeni. Pange tähele, et kui me seda teeme, ei viita saadud valemid enam õigesti andmebaasi tabelile. Selle saaksime parandada, muutes andmebaasi lahtriviited absoluutne lahtriviited. Alternatiivina - ja veelgi parem - võiksime luua a vahemiku nimi kogu tootebaasi jaoks (nt „Tooted”) ja kasutage lahtriviidete asemel seda vahemiku nime. Valem muutuks sellest ... = KUI (ISBLANK (A11), ””, VLOOKUP (A11, ’Toote andmebaas’! A2: D7,2, FALSE))... sellele ... = KUI (ISBLANK (A11), ””, VLOOKUP (A11, tooted, 2, VALE))… Ja siis kopeerige valemid ülejäänud arveüksuste ridadesse.
3. Tõenäoliselt "lukustaksime" lahtrid, mis sisaldavad meie valemeid (või pigem avage muud lahtrid) ja seejärel kaitsta töölehte, et meie hoolikalt koostatud valemeid ei kirjutataks kogemata üle, kui keegi arve täitma tuleb.
4. Me salvestaksime faili kui mall, et seda saaksid kõik meie ettevõttes kasutajad uuesti kasutada

Kui me tunneksime tõesti nutikas, loome kõigi oma klientide andmebaasi teises töölehel ja kasutaksime lahtrisse F5 sisestatud kliendi ID-d, et täita lahtrites B6, B7 ja B8 automaatselt kliendi nimi ja aadress.

Kui soovite harjutada VLOOKUPiga või lihtsalt vaadata meie tulemuseks olevat arvemalli, saate selle siit alla laadida.