VLOOKUP on üks Exceli kõige kasulikumatest funktsioonidest ja see on ka üks kõige vähem mõistlikest. Käesolevas artiklis me demonstreerime VLOOKUPi reaalse näitena. Loome kasutatavaks Arve mall fiktiivse ettevõtte jaoks.
VLOOKUP on Exceli funktsioon. See artikkel eeldab, et lugejal on juba Exceli funktsioonide mõistmine ja nad saavad kasutada selliseid põhifunktsioone nagu SUM, AVERAGE ja TODAY. Kõige tavalisemas kasutuses on VLOOKUP a andmebaas funktsioon, mis tähendab, et see toimib andmebaaside tabelitega - või lihtsalt lihtsalt nimekirjad asjadest Exceli töölehel. Milliseid asju? Noh kõik mingi asi. Sul võib olla tööleht, mis sisaldab CD-kollektsioonis olevaid töötajaid, tooteid või kliente või CD-sid, või tähed öösel. See pole tõesti oluline.
Siin on näide loendist või andmebaasist. Sel juhul on see nimekiri toodetest, mida meie fiktiivne ettevõte müüb:
Tavaliselt sellistes nimekirjades on nimekirja iga üksuse jaoks mingi kordumatu tunnus. Sel juhul on kordumatu tunnus veerg "Item Code". Märkus. VLOOKUP-i funktsiooniga andmebaas / loendiga töötamiseks peab loendis olema veerg, mis sisaldab unikaalset tunnust (või "key" või "ID") ja see veerg peab olema tabeli esimene veerg. Eespool toodud näidisandmebaas vastab sellele kriteeriumile.
VLOOKUPi kõige raskem osa on täpselt teada, mis see on. Nii et vaatame, kas saame kõigepealt selgeks teha:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Eespool toodud näites sisestaksite VLOOKUP-i funktsiooni teisele arvutustabeleid koos objektikoodiga ja see tagastaks teile kas vastava üksuse kirjelduse, selle hinna või selle kättesaadavuse (selle "Laoseisu" kogus), nagu on kirjeldatud teie originaalil nimekiri. Milline neist informatsioonidest läheb sind tagasi? Noh, saate seda otsustada, kui loote valemit.
Kui kõik, mida vajate, on üks andmebaasist pärinev informatsioon, oleks palju probleeme ehitada valem koos VLOOKUP-i funktsiooniga. Tavaliselt kasutate seda tüüpi funktsioone korduvkasutatavas arvutustabelis, näiteks mallis. Iga kord, kui keegi siseneb kehtiva üksuse koodi, laadib süsteem kogu vajaliku teabe vastava elemendi kohta.
Loome selle näiteks: An Arve mall et saaksime korduvalt meie fiktiivses ettevõttes uuesti kasutada.
Esmalt käivitame Exceli ja loome endale tühja arve:
Sellise näite lihtsaks pidamiseks leiame toote andmebaasi samas töökirjas eraldi lehele:
Nii oleme loonud oma tooteandmebaasi, mis näeb välja selline:
Peame leidma kõigi olemasolevate funktsioonide nimekirja, mida Excel võib pakkuda, nii et saaksime valida VLOOKUPi ja saada valemit täiendava abi saamiseks. Seda leitakse esmalt klikkides nupul Valemid vahelehel ja seejärel klõpsates Funktsiooni sisestamine:
Selle otsingu leidmiseks, mida me otsime, võime sisestada otsingutermina nagu "otsing" (kuna huvitav funktsioon on Vaata üles funktsioon). Süsteem tagastaks meile kõikide Exceli otsingupõhiste funktsioonide nimekirja. VLOOKUP on teine nimekirjas. Valige see klõps Okei.
The Funktsioon Argumendid ilmub kast, mis palub meil kõigile argumendid (või parameetrid), mis on VLOOKUP-i funktsiooni täitmiseks vajalik. Saate mõelda selle kasti funktsioonina, milles küsitakse järgmisi küsimusi:
- Millist unikaalset identifikaatorit näete andmebaasis?
- Kus on andmebaas?
- Millist informatsiooni andmebaasist, mis on seotud unikaalse identifikaatoriga, kas soovite seda teie jaoks alla laadida?
Esitatakse kolm esimest argumenti paksus kirjas, mis näitab, et nad on kohustuslik argumendid (VLOOKUP funktsioon on ilma nendeta puudulik ja ei tagasta kehtivat väärtust). Neljas argument ei ole julge, see tähendab, et see on vabatahtlik:
Esimene argument, mida me peame täitma, on Lookup_value argument. Funktsioon vajab, et me ütleksime, kust leida unikaalne tunnus ( eseme kood antud juhul), et see peaks olema kirjelduse tagasitulek. Me peame valima varem sisestatud objekti koodi (A11-s).
Klõpsake esimese argumendi paremal asuval valija ikoonil:
Seejärel klõpsake üksust koodi (A11) sisaldavas lahtris ja vajutage Sisenema:
Nüüd peame sisestama väärtuse Table_array argument. Teisisõnu, me peame VLOOKUPile ütlema, kuhu andmebaas / loend leida. Klõpsake teise argumendi kõrval oleval valija ikoonil:
… ja vajutage Sisenema. Andmebaasi esindavate rakkude vahemik (sellisel juhul sisestatakse automaatselt teine argu "" tooteandmebaas "A2: D7").
Nüüd peame sisestama kolmanda argumendi Col_index_num. Me kasutame seda argumenti, et täpsustada VLOOKUPile, milline informatsioon andmebaasist seostatakse meie elemendikoodiga A11-s, mida me soovime meile tagasi saata. Selles konkreetses näites soovime, et toode oleks kirjeldus tagasi meile. Kui vaatate andmebaasi töölehte, märkate, et veeru "Kirjeldus" on teine veeru andmebaasi. See tähendab, et peame sisestama väärtuseks "2" Col_index_num kast:
Tähtis on märkida, et me ei sisesta siia siin "2", sest veerg "Kirjeldus" asub B selle töölehe veerg. Kui andmebaas juhtus alustama veerus K töölehest, sisestaksime sellel väljal ikkagi "2", sest veeru "Kirjeldus" on lahtrite komplekti teine veerg, mille valisime "Tabeli_raami" määramisel.
Lõpuks peame otsustama, kas sisestada väärtus lõplikku VLOOKUP-i argumenti Range_lookup. See argument nõuab kas a tõsi või vale väärtust või jätta see tühjaks. Kui kasutate VLOOKUPi andmebaasidega (nagu on tõsi 90% ajast), siis võib selle argumendi otsustada, kuidas seda teha:
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
Meie andmebaasi esimene veerg on mitte sorteeritud me sisestame vale arvesse seda argumenti:
See ongi! Oleme sisestanud kogu VLOOKUP-i jaoks vajalikku teavet vajaliku väärtuse tagastamiseks. Klõpsake nupul Okei nuppu ja märkate, et kirje koodile "R99245" vastav kirjeldus on korras sisestatud lahtrisse B11:
Kui me sisestame a erinevad objekti koodi lahtrisse A11, hakkame nägema VLOOKUP-i funktsiooni võimsust: kirjelduse elemendi muudab uue elemendi koodi.
Objekti saamiseks võime teha samalaadseid samme hind tagasi rakku E11. Pange tähele, et uus valem tuleb luua lahtris E11. Tulemus näeb välja selline:
Pange tähele, et ainus erinevus kahe valemi vahel on kolmas argument (Col_index_num) on muutunud vahemikus "2" kuni "3" (sest me tahame, et andmebaasi kolmandast veerust saadud andmed oleksid kättesaadavad).
Kui me otsustasime neid 2 esemeid osta, sisestaksime lahtrisse D11 "2". Seejärel sisestage liinide kogumiseks lahtrisse F11 lihtne valem:
=D11*E11
… mis näeb välja selline …
Arve malli täitmine
Oleme VLOOKUP-i kohta seni palju õppinud. Tegelikult oleme õppinud kõik, mida me selles artiklis õpime. Oluline on märkida, et VLOOKUP-i saab lisaks andmebaasidele kasutada ka muudel asjaoludel. See on vähem levinud ja see võib olla kajastatud tulevaste Geokogude artiklites.
Meie arve mall ei ole veel lõpetatud. Selle teostamiseks teeme järgmist:
-
Me eemaldaksime näidisobjekti koodi lahtrist A11 ja lahtrist D11 lahtrist D11. See põhjustab meie hiljuti loodud VLOOKUPi valemite kuvamiseks veateateid:
Image Me saame seda parandada Excel'i mõistliku kasutamise kaudu IF () ja ISBLANK () funktsioonid. Muutame oma valemit sellest … = VLOOKUP (A11, tooteandmebaas! A2: D7,2, FALSE) … sellele … = IF (ISBLANK (A11), "", VLOOKUP (A11, "Product Database"! A2: D7,2, FALSE))
- Me kopeerime valemid lahtritesse B11, E11 ja F11 ülejäänud arve elementide ridade juurde. Pidage meeles, et kui me seda teeme, ei tule tuletatud valemid andmebaaside tabelile õigesti. Võiksime seda lahendada, muutes andmebaasi viiteid rakule absoluutne raku viited. Alternatiivselt - ja veelgi parem - võiksime luua vahemiku nimi kogu tooteandmebaasi (näiteks "Tooted") jaoks ja kasutage selle vahemiku nime, mitte raku viiteid. Valem muudaks sellest … = IF (ISBLANK (A11), "", VLOOKUP (A11, "Product Database"! A2: D7,2, FALSE)) … sellele … = IF (ISBLANK (A11), "", VLOOKUP (A11, Tooted, 2, FALSE)) … ja siis kopeerige valemid ülejäänud arve kirje ridadele.
- Me tõenäoliselt lukustaksime rakud, mis sisaldavad meie valemeid (või pigem avage a muu rakud) ja seejärel kaitsta töölehte, et tagada, et meie hoolikalt konstrueeritud valemeid ei juhuslikult üle kirjutatud, kui keegi saab arve täitma.
- Me salvestaksime faili kui malli, nii et seda saaksid kõik meie firmasid uuesti kasutada
Kui me tunneksime tõesti tark, loome kõikide meie klientide andmebaasi mõnes teises töölehel ja seejärel kasutage lahtris F5 sisestatud kliendiandmeid, et automaatselt täita kliendi nimi ja aadress lahtrites B6, B7 ja B8.
