Deur Excel se VLOOKUP-funksie te kombineer met die KOLOM funksie , kan ons 'n opsommings formule skep wat u toelaat om verskeie waardes van 'n enkele ry van 'n databasis of tabel van data terug te gee.
In die voorbeeld wat in die prent hierbo gewys word, maak die opzoekformule dit maklik om alle waardes terug te keer - soos prys, deelnommer en verskaffer - wat verband hou met verskillende hardeware.
01 van 10
Wys meerwaardes met Excel VLOOKUP
Na aanleiding van die stappe wat hieronder gelys word, word die opsomming formule wat in die prent hierbo gesien word, meer waardes van 'n enkele data rekord.
Die opsoekformule vereis dat die KOLOM-funksie binne-in VLOOKUP genesteer word.
Om 'n funksie te nader, behels die tweede funksie as een van die argumente vir die eerste funksie.
In hierdie handleiding sal die KOLOM-funksie as die kolomindeks- argument vir VLOOKUP ingeskryf word.
Die laaste stap in die tutoriaal behels die kopiëring van die opzoekingsformule na bykomende kolomme om bykomende waardes vir die gekose deel op te haal.
Handleiding Inhoud
- Voer die tutoriaal data in
- Die skep van 'n benoemde reeks vir die datatabel
- Begin die VLOOKUP-funksie
- Die Argument vir Opzoeking Waarde invoer deur Absolute Selverwysings te gebruik
- Invoer van die Tabel Array Argument
- Voer die geneste KOLOM-funksie in
- Voltooi die VLOOKUP-funksie
- Die opzoekingsformule kopieer met die vulhantering
- Data herwin met die Lookup Formule
02 van 10
Voer die tutoriaal data in
Die eerste stap in die tutoriaal is om die data in 'n Excel- werkblad in te voer .
Om die stappe in die tutoriaal te volg, voer die gegewe data in die bostaande prent in die volgende selle in .
- Voer die boonste reeks data in selle D1 tot G1 in
- Voer die tweede reeks in selle D4 tot G10 in
Die soekkriteria en die soekformule wat tydens hierdie tutoriaal geskep is, word in ry 2 van die werkblad ingevoer.
Die handleiding bevat nie die formatering wat in die prent gesien word nie, maar dit sal nie beïnvloed hoe die oproepformule werk nie.
Inligting oor formatering opsies soortgelyk aan dié wat hierbo gesien is, is beskikbaar in hierdie Basiese Excel Formatering Tutoriaal .
Tutoriaalstappe
- Gee die data soos gesien in die prent hierbo in selle D1 tot G10
03 van 10
Die skep van 'n benoemde reeks vir die datatabel
'N Benoemde reeks is 'n maklike manier om na 'n reeks data in 'n formule te verwys. Eerder as om die selverwysings vir die data in te tik, kan jy net die naam van die reeks invoer.
'N Tweede voordeel vir die gebruik van 'n benoemde reeks is dat die selverwysings vir hierdie reeks nooit verander nie, selfs wanneer die formule na ander selle in die werkblad gekopieer word.
Reeksname is dus 'n alternatief vir die gebruik van absolute selverwysings om foute te voorkom wanneer formules gekopieer word.
Nota: Die reeks naam sluit nie die opskrifte of veldname vir die data in (ry 4) nie, maar slegs die data self.
Tutoriaalstappe
- Merk selle D5 tot G10 in die werkblad om hulle te selekteer
- Klik op die naam boks bo kolom A
- Tik 'Tabel' (geen aanhalingstekens) in die naamvak
- Druk die Enter- sleutel op die sleutelbord
- Selle D5 tot G10 het nou die reeksnaam van "Tabel". Ons sal die naam vir die VLOOKUP- tabelversameling- argument later in die tutoriaal gebruik
04 van 10
Open die VLOOKUP dialoogkassie
Alhoewel dit moontlik is om net ons soekformule direk in 'n sel in 'n werkblad te tik, vind dit baie moeilik om die sintaksis reguit te hou - veral vir 'n komplekse formule soos die een wat ons in hierdie handleiding gebruik.
'N Alternatief, in hierdie geval, is om die VLOOKUP dialoogkassie te gebruik . Byna al die funksies van Excel het 'n dialoogkassie waarmee u elke argument van die funksie op 'n aparte lyn kan invoer.
Tutoriaalstappe
- Klik op sel E2 van die werkblad - die plek waar die resultate van die tweedimensionele opzoekformule vertoon sal word
- Klik op die Formules- oortjie van die lint
- Klik op die opsie Soek en verwysing in die lint om die aftreklys vir funksies oop te maak
- Klik op VLOOKUP in die lys om die dialoog van die funksie oop te maak
05 van 10
Die Argument vir Opzoeking Waarde invoer deur Absolute Selverwysings te gebruik
Normaalweg pas die soekwaarde ooreen met 'n veld van data in die eerste kolom van die datatabel.
In ons voorbeeld verwys die opzoekwaarde na die naam van die hardeware gedeelte waaroor ons inligting wil vind.
Die toelaatbare tipes data vir die opzoekwaarde is:
- teks data
- 'n logiese waarde (slegs WAAR of ONWAAR)
- n nommer
- 'n selverwysing na 'n waarde in die werkblad
In hierdie voorbeeld sal ons die selverwysing inskryf na waar die deelnaam geleë sal wees - sel D2.
Absolute Selverwysings
In 'n latere stap in die tutoriaal, sal ons die opskrifformule in sel E2 na selle F2 en G2 kopieer.
Normaalweg, wanneer formules in Excel gekopieer word, verander selverwysings om hul nuwe ligging te weerspieël.
As dit gebeur, sal D2 - die selverwysing vir die opkykwaarde - verander namate die formule gekopieer word, wat foute in selle F2 en G2 skep.
Om die foute te voorkom, sal ons die selverwysing D2 omskep in 'n absolute selverwysing .
Absolute selverwysings verander nie wanneer formules gekopieer word nie.
Absolute selverwysings word geskep deur die F4- sleutel op die sleutelbord te druk. As jy dit doen, voeg dollarstekens by die selverwysing toe, soos $ D $ 2
Tutoriaalstappe
- Klik op die lookup_value- lyn in die dialoogkassie
- Klik op sel D2 om hierdie selverwysing by die lookup_value- lyn te voeg. Dit is die sel waar ons die deel naam sal tik waaroor ons inligting soek
- Sonder om die invoegpunt te beweeg, druk die F4 sleutel op die sleutelbord om D2 om te skakel na die absolute selverwysing $ D $ 2
- Laat die VLOOKUP funksie dialoog oop vir die volgende stap in die tutoriaal
06 van 10
Invoer van die Tabel Array Argument
Die tabel skikking is die tabel van data wat die soekformule soek om die inligting te vind wat ons wil hê.
Die tabel skikking moet ten minste twee kolomme data bevat .
- die eerste kolom bevat die opvattingswaarde-argument (vorige stap in die tutoriaal)
- Die tweede en enige bykomende kolomme sal deur die opzoekformule gesoek word om die inligting wat ons spesifiseer, te vind.
Die tabel array argument moet ingevoer word as óf 'n reeks wat die selverwysings vir die datatabel of as 'n reeksnaam bevat .
Vir hierdie voorbeeld gebruik ons reeksnaam wat in stap 3 van die tutoriaal geskep is.
Tutoriaalstappe
- Klik op die table_array lyn in die dialoogkassie
- Tik 'Tabel' (geen aanhalingstekens) om die reeksnaam vir hierdie argument in te voer
- Laat die VLOOKUP funksie dialoog oop vir die volgende stap in die tutoriaal
07 van 10
Nader die KOLOM-funksie
Gewoonlik lewer VLOOKUP slegs data van een kolom van 'n datatabel en word hierdie kolom bepaal deur die kolomindeksnommer- argument.
In hierdie voorbeeld het ons egter drie kolomme waarin ons data wil terugstuur. Ons het dus 'n manier nodig om die kolomindeksnommer maklik te verander sonder om ons soekformule te verander.
Hier kom die KOLOM funksie in. Deur dit in te voer as die kolom indeks nommer argument, sal dit verander, aangesien die soekformule van sel D2 na selle E2 en F2 later in die tutoriaal gekopieer word.
Nestende funksies
Die KOLOM funksie dien dus as VLOOKUP se kolom indeks nommer argument .
Dit word bereik deur die KOLOM-funksie binne-in VLOOKUP in die Col_index_num- lyn van die dialoog te nes.
Die COLUMN-funksie handmatig invoer
By die nesfunksies laat Excel ons nie toe om die dialoog van die tweede funksie oop te maak om sy argumente te betree nie.
Die COLUMN-funksie moet dus handmatig in die Col_index_numreël ingevoer word.
Die KOLOM funksie het slegs een argument - die verwysingsargument wat 'n selverwysing is.
Die keuse van die KOLOM-funksie se verwysingsargument
Die KOLOM funksie se taak is om die nommer van die kolom wat as die verwysingsargument gegee word, terug te gee.
Met ander woorde, dit omsit die kolom letter in 'n getal met kolom A die eerste kolom, kolom B die tweede en so aan.
Aangesien die eerste veld van data wat ons wil teruggee, die prys van die item - wat in kolom twee van die datatabel voorkom - ons die selverwysing vir enige sel in kolom B as die verwysingsargument kan kies om die nommer 2 te kry vir die Col_index_num argument.
Tutoriaalstappe
- In die VLOOKUP-funksie dialoogkassie, klik op die Col_index_numreël
- Tik die funksie naam kolom gevolg deur 'n oop ronde bracket " ( "
- Klik op sel B1 in die werkblad om daardie selverwysing as die verwysingsargument in te voer
- Tik 'n sluitingsrondjie " ) " om die COLUMN-funksie te voltooi
- Laat die VLOOKUP funksie dialoog oop vir die volgende stap in die tutoriaal
08 van 10
Voer die VLOOKUP-reeks Soek Argument in
VLOOKUP se Range_lookup-argument is 'n logiese waarde (slegs WAAR of ONWAAR) wat aandui of jy VLOOKUP wil hê om 'n presiese of 'n benaderde pas by die Lookup_value te vind.
- As WAAR of indien hierdie argument weggelaat word, lewer VLOOKUP óf 'n presiese ooreenstem met die Lookup_value, óf, as 'n presiese wedstryd nie gevind word nie, sal VLOOKUP die volgende grootste waarde teruggee. Vir die formule om dit te doen, moet die data in die eerste kolom van Table_array in stygende volgorde gesorteer word .
- As ONWAAR, sal VLOOKUP slegs 'n presiese pas by die Lookup_value gebruik. As daar twee of meer waardes in die eerste kolom van Table_array is wat die soekwaarde ooreenstem, word die eerste waarde wat gevind word, gebruik. As 'n presiese wedstryd nie gevind word nie, word 'n # N / A-fout teruggestuur.
In hierdie handleiding, aangesien ons spesifieke inligting oor 'n spesifieke hardewareitem soek, sal ons Range_lookup gelyk wees aan Valse .
Tutoriaalstappe
- Klik op die Range_lookup reël in die dialoogkassie
- Tik die woord Vals in hierdie reël om aan te dui dat ons VLOOKUP wil hê om 'n presiese wedstryd vir die data wat ons soek, terug te stuur
- Klik OK om die opsoekformule te voltooi en sluit dialoogkassie in
- Aangesien ons nog nie die soekkriteria in sel D2 ingevoer het nie, sal 'n # N / A fout in sel E2 teenwoordig wees
- Hierdie fout sal reggestel word wanneer ons die soekkriteria in die laaste stap van die tutoriaal sal byvoeg
09 van 10
Die opzoekingsformule kopieer met die vulhantering
Die opzoekformulier is bedoel om op een keer data uit verskeie kolomme van die data tafel te haal.
Om dit te doen, moet die opskrifformule in al die velde waaruit ons inligting wil hê, woon.
In hierdie tutoriaal wil ons data in kolomme 2, 3 en 4 van die datatabel kry - dit is die prys, die deelnommer en die verskaffer se naam wanneer ons 'n deelnaam as die Lookup_value invoer.
Aangesien die data in 'n gereelde patroon in die werkblad uitgelê word, kan ons die opzoekingsformule in sel E2 na selle F2 en G2 kopieer.
Namate die formule gekopieer word, sal Excel die relatiewe selverwysing in die KOLOMfunksie (B1) opdateer om die formule se nuwe ligging te weerspieël.
Ook verander Excel nie absolute selverwysing $ D $ 2 nie en die naamreeks Tabel as die formule gekopieer word.
Daar is meer as een manier om data in Excel te kopieer, maar waarskynlik die maklikste manier is om die Fill Handle te gebruik .
Tutoriaalstappe
- Klik op sel E2 - waar die soekformule geleë is - om dit die aktiewe sel te maak
- Plaas die muiswyser oor die swart blokkie in die onderste regterkantste hoek. Die wyser sal verander na 'n plusteken " + " - dit is die vulhandvatsel
- Klik op die linker muisknop en sleep die vulhandvatsel oor na sel G2
- Laat die muisknop los en sel F3 moet die tweedimensionele opzoekformule bevat
- As dit korrek gedoen word, behoort selle F2 en G2 nou ook die # N / A-fout wat in sel E2 teenwoordig is, te bevat
10 van 10
Die opzoekingskriteria invoer
Sodra die opsoekformule na die vereiste selle gekopieer is, kan dit gebruik word om inligting uit die datatabel te kry.
Om dit te doen, tik die naam van die item wat jy wil ophaal in die Lookup_value-sel (D2) en druk die Enter-sleutel op die sleutelbord.
Sodra dit gedoen is, moet elke sel wat die opzoek formule bevat 'n ander stuk data bevat oor die hardeware-item wat jy soek.
Tutoriaalstappe
- Klik op sel D2 in die werkblad
- Tik Widget in sel D2 en druk die Enter- sleutel op die sleutelbord
- Die volgende inligting moet in selle E2 tot G2 vertoon word:
- E2 - $ 14.76 - die prys van 'n widget
- F2 - PN-98769 - die deelnommer vir 'n widget
- G2 - Widgets Inc. - die naam van die verskaffer vir widgets
- Toets die VLOOKUP-skikking formule verder deur die naam van ander dele in sel D2 te tik en die resultate in selle E2 tot G2 in ag te neem.
As 'n foutboodskap soos #REF! verskyn in selle E2, F2 of G2, kan hierdie lys met VLOOKUP foutboodskappe u help om te bepaal waar die probleem lê.