1Aug
VLOOKUP on üks Exceli kõige kasulikumatest funktsioonidest ja see on ka üks kõige vähem arusaadav. Käesolevas artiklis me demonstreerime VLOOKUPi reaalse näitena. Loome fiktiivsele ettevõttele kasutatava arve malli .
VLOOKUP on Excel funktsioon .See artikkel eeldab, et lugejal on juba mõista Exceli funktsioone ja nad saavad kasutada selliseid põhifunktsioone nagu SUM, AVERAGE ja TODAY.Kõige tavalisemas kasutuses on VLOOKUP andmebaasiga funktsioon, mis tähendab, et see töötab andmebaaside tabelitega või lihtsalt, lisab asjad Exceli töölehel. Milliseid asju? Noh, kasutab asju. 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. Sellisel juhul on see nimekiri toodetest, mida meie fiktiivne ettevõte müüb:
Tavaliselt sellistel loenditel on nimekirja iga üksuse jaoks mingi kordumatu tunnus. Sel juhul on kordumatu tunnus veerg "Item Code".Märkus: VLOOKUP-i funktsiooni kasutamiseks andmebaasiga / loendiga peab nimekirjas
olema veerg, mis sisaldab unikaalset tunnust( või "key" või "ID"), ja , et veerg peab olema tabeli esimene veerg.Eespool toodud näidisandmebaas vastab sellele kriteeriumile.VLOOKUPi kõige raskem osa on arusaamine just sellest, mis see on. Nii et vaatame, kas saame selle kõigepealt selgeks teha:
VLOOKUP otsib andmebaasist / loendist teavet, mis põhineb unikaalse identifikaatori esitatud eksemplaril.
Eespool toodud näites sisestaksite VLOOKUP-i funktsioon teise arvutustabelisse koos objektikoodiga ja see tagaks sulle kas vastava üksuse kirjelduse, selle hinna või selle kättesaadavuse( selle "Laoseisu" kogus), nagu on kirjeldatud teiealgne 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, siis on palju probleeme, et 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: arve mall , mida me võime korduvalt oma fiktiivses ettevõttes taaskasutada.
Kõigepealt käivitame Exceli ja loome ise tühja arve:
Nii töötab see: arve malli kasutav isik täidab veeru "A" -kategooria koodide seeria ja süsteem laadib iga üksusekirjeldus ja hind meie tooteandmebaasist. Seda teavet kasutatakse iga üksuse ridade arvutamiseks( eeldades, et sisestame kehtiva koguse).
Selle näite lihtsaks pidamiseks leiame toote andmebaasi samasse töövihiku eraldi lehele:
Tegelikkuses on tooteandmebaas tõenäoliselt paigutatud eraldi töövihikusse. VLOOKUP-i funktsioon on väga väike, mis ei huvita, kas andmebaas asub samal lehel, teisel lehel või täiesti erineval töövihikul.
Niisiis oleme loonud oma tooteandmebaasi, mis näeb välja selline:
Selleks, et testida VLOOKUPi valemit, mida me hakkame kirjutama, sisestage esmalt meie tühi arve lahtrisse A11 kehtiv koodi kood:
Järgmine meliiguta aktiivset lahtrit raku, milles me tahame säilitada andmebaasi VLOOKUPilt saadud teavet. Huvitav on see, et enamik inimesi valib. Järgnevas seletuses: me kavatseme luua VLOOKUPi valemi, mis võtab välja kirjelduse, mis vastab elemendikoodile lahtris A11.Kust me tahame seda kirjeldust teha, kui me seda saame? Loomulikult lahtris B11.Nii et see on see, kus me kirjutame VLOOKUPi valemit: lahtris B11.Valige lahtris B11 kohe.
Peame leidma kõigi olemasolevate funktsioonide loetelu, mida Excel võib pakkuda, nii et saaksime valida VLOOKUPi ja saada valemit täiendava abi saamiseks. Seda leitakse esmalt klikkides vahekaardil Formula ja seejärel klõpsates Lisa funktsioon :
Ilmub kasti, mis võimaldab meil valida mõne Excelis saadaval oleva funktsiooni.
Selle otsingu otsimiseks, mida me otsime, võime sisestada otsingutermina nagu "lookup"( kuna meie huvi pakkuv funktsioon on otsingu funktsioon).Süsteem tagastaks meile kõikide Exceli otsingupõhiste funktsioonide nimekirja. VLOOKUP on nimekirja teine. Vali see klikk OK .
Näidake funktsiooni argumendid , mis palub meil kõik VLOOKUP-i funktsiooni täitmiseks vajalikud argumendid ( või parameetrid ).Saate seda kasti mõelda kui funktsiooni, milles küsitakse järgmisi küsimusi:
- Millist unikaalset identifikaatorit otsite andmebaasis?
- Kus on andmebaas?
- Millist informatsiooni andmebaasist, mis on seotud unikaalse identifikaatoriga, kas soovite seda teie jaoks alla laadida?
Esimesed kolm argumenti näitavad paksus kirjas , mis näitab, et need on kohustuslikud argumendid( VLOOKUP funktsioon on ilma nendeta puudulik ja ei anna kehtivat väärtust).Neljas argument ei ole julge, see tähendab, et see on vabatahtlik:
Täidame argumendid järjest, ülalt alla.
Esimene argument, mille me peame täitma, on Lookup_value argument. Funktsioon vajab, et me selgitaksime, kust leida kordumatu tunnus( -koodi kood ) sel juhul, kui ta peaks kirjelduse uuesti tagasi saatma. Me peame valima varem sisestatud objekti koodi( A11-s).
Klõpsake esimese argumendi paremal asuvat valijaikooni:
. Seejärel klõpsake ükskord objekti koodi( A11) sisaldavas lahtris ja vajutage . Sisestage :
. A11 väärtus lisatakse esimesele argumendile.
Nüüd peame sisestama Table_array argumendi väärtuse. Teisisõnu, me peame VLOOKUPile ütlema, kuhu andmebaas / loend leida. Klõpsake teise argumendi kõrval oleval valijaikoonil:
Nüüd leidke andmebaas / loend ja valige kogu loend - , mis ei sisalda päise rida .Meie näites asub andmebaas eraldi töölehel, nii et me esmalt klõpsame selle töölehe vahekaarti:
Järgnevalt valime kogu andmebaasi, välja arvatud päise rida:
. .. ja vajutage Sisestage .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 meil oleks tagastatud objekti kirjeldus .Kui vaatate andmebaasi töölehte, märkate, et veerg "Kirjeldus" on andmebaasi teine veerg. See tähendab, et peame sisestama väärtuse "2" Col_index_num kasti:
. Oluline on märkida, et me ei sisesta siia siin "2", kuna veerg "Kirjeldus" on B veerussee tööleht. Kui andmebaas juhtus alustama töölehe veerus K , jätkaksime selles valdkonnas väljale "2", sest veerg "Kirjeldus" on lahtrite komplekt, mille valisime "tabeliarray" määramisel.
Lõpuks peame otsustama, kas sisestada väärtus lõpliku VLOOKUP-i argumendini, Range_lookup .Selle argumendi puhul tuleb kas tõsi või vale väärtus või jätta see tühjaks. Kui kasutate VLOOKUPi andmebaasidega( nagu on tõsi 90% ajast), võib selle argumendiga otsustada, kuidas seda argumenti panna:
Kui andmebaasi esimene veerg( veerg, mis sisaldab unikaalseid tunnuseid)on sorteeritud tähestiku / numbrite järgi kasvavas järjekorras, siis saab selle argumendini sisestada tõese väärtuse või jätta selle tühjaks.
Kui andmebaasi esimene veerg on mitte sorteeritud või on see sorteeritud kahanevas järjekorras, siis peab sisestama sellele argumendile vale väärtuse
Kuna meie andmebaasi esimene veerg on mitte sorteeritud,me sisestame sellesse argumendusse vale :
See ongi! Oleme sisestanud kogu VLOOKUP-i jaoks vajalikku teavet vajaliku väärtuse tagastamiseks. Klõpsake nupul OK ja märkige, et kirje koodile "R99245" vastav kirjeldus on õigesti sisestatud lahtrisse B11:
. Meie jaoks loodud valem näeb välja selline:
Kui me sisestame lahtrisse erinevat -koodi koodiA11, hakkame nägema VLOOKUP-i funktsiooni võimsust: Kirjeldav rakk muudab uue objekti koodi vastavat:
Võime sooritada samalaadseid samme, et saada objekti hind tagasi elemendi E11 juurde. Pange tähele, et uus valem tuleb luua lahtris E11.Tulemus näeb välja selline:
. .. ja valem näeb välja selline:
Pange tähele, et kahe valemi ainus erinevus on kolmas argument( Col_index_num ) on muutunud "2" -st "3" -ni( sestme tahame, et andmebaasi 3. veerus saadud andmed).
Kui me otsustaksime osta nendest objektidest 2, sisestaksime lahtrisse D11 "2".Seejärel sisesime lihtklassi F11 arvutamiseks lihtsa valemi, et saada joon kokku:
= D11 * E11
. .. mis näeb välja selline. ..
Arve malli lõpuleviimine
Oleme seni palju õppinud VLOOKUPi kohta. 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 lahtrile "2".See põhjustab meie äsja loodud VLOOKUPi valemite kuvamiseks tõrketeateid:
Me võime seda parandada Excel'i IF() ja ISBLANK() funktsioonide mõistliku kasutamise abil. Muutame oma valemit sellest. .. = VLOOKUP( A11, tooteandmebaas! A2: D7,2, FALSE) . .. sellele. .. = IF( ISBLANK( A11), VLOOKUP( A11, 'Product Database'! A2: D7,2, FALSE))
- Oleme kopeerinud valemid lahtrites B11, E11 ja F11 allapoole arve kirje ridadesse. Pidage meeles, et kui me seda teeme, ei tule tuletatud valemid andmebaaside tabelile õigesti. Me võime seda lahendada, muutes andmebaasi raku viited andmebaasi absoluutsele raku viitele. Alternatiivselt - ja veelgi parem - võiksime luua vahemiku nime kogu tooteandmebaasi( näiteks "Tooted") jaoks ja kasutada selle vahemiku nime, mitte raku viiteid. Valem muutub sellest. .. = IF( ISBLANK( A11), VLOOKUP( A11, Toote andmebaas! A2: D7,2, FALSE)) . .. sellele. .. = IF( ISBLANK( A11), VLOOKUP( A11, Tooted, 2, FALSE)) . .. ja , siis kopeerib need valemid ülejäänud arve kirje ridadele.
- Oleksime tõenäoliselt lukustanud meie valemeid sisaldavad rakud( või pigem , avage muud -rakud) ja seejärel kaitske töölehte, et tagada, et meie hoolikalt konstrueeritud valemeid ei juhuslikult üle kirjutatud, kui keegi täidabarvele.
- Me salvestaksime faili mallina , nii et seda saaksid kasutada kõik meie firmas
. Kui me tunneme -d tõepoolest targana, loome kõigi meie klientide andmebaasi mõnes teises töölehel ja seejärel kasutagekliendi ID sisestatakse lahtrisse F5, et automaatselt täita kliendi nimi ja aadress lahtrites B6, B7 ja B8.
Kui soovite VLOOKUP-i treenida või lihtsalt saadaolevat arvete malli, saate seda siit alla laadida.