Deur Excel se VLOOKUP-funksie met die COLUMN-funksie te kombineer, kan jy 'n opsoekformule skep wat veelvuldige waardes van 'n enkele ry van 'n databasis of tabel van data terugstuur. Leer hoe om 'n opsoekformule te skep wat veelvuldige waardes vanaf 'n enkele datarekord terugstuur.
Instruksies in hierdie artikel is van toepassing op Excel 2019, 2016, 2013, 2010; en Excel vir Microsoft 365.
Onderste reël
Die opsoekformule vereis dat die COLUMN-funksie binne-in VLOOKUP geneste moet wees. Om 'n funksie te neste behels die invoer van die tweede funksie as een van die argumente vir die eerste funksie.
Voer die tutoriaaldata in
In hierdie tutoriaal word die KOLOM-funksie ingevoer as die kolomindeksnommerargument vir VLOOKUP. Die laaste stap in die tutoriaal behels die kopiëring van die opsoekformule na bykomende kolomme om bykomende waardes vir die gekose deel te herwin.
Die eerste stap in hierdie tutoriaal is om die data in 'n Excel-werkblad in te voer. Om die stappe in hierdie tutoriaal te volg, voer die data in die prent hieronder 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 opsoekformule wat in hierdie tutoriaal geskep is, word in ry 2 van die werkblad ingevoer.
Hierdie tutoriaal sluit nie die basiese Excel-formatering in wat in die prent gewys word nie, maar dit beïnvloed nie hoe die opsoekformule werk nie.
Skep '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 data in te tik, tik die naam van die reeks in.
'n Tweede voordeel van 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 'n alternatief vir die gebruik van absolute selverwysings om foute te voorkom wanneer formules gekopieer word.
Die reeksnaam sluit nie die opskrifte of veldname vir die data in nie (soos getoon in ry 4), net die data.
-
Highlight selle D5 na G10 in die werkblad.
Image -
Plaas die wyser in die naamkassie bo kolom A, tik Table, en druk dan Enter. Selle D5 tot G10 het die reeksnaam van Tabel.
Image - Die reeksnaam vir die VLOOKUP tabel skikking argument word later in hierdie tutoriaal gebruik.
Maak die VLOOKUP-dialoogkassie oop
Alhoewel dit moontlik is om die opsoekformule direk in 'n sel in 'n werkblad in te tik, vind baie mense dit moeilik om die sintaksis reguit te hou - veral vir 'n komplekse formule soos die een wat in hierdie tutoriaal gebruik word.
As 'n alternatief, gebruik die VLOOKUP Function Arguments dialoogkassie. Byna al Excel se funksies het 'n dialoogkassie waar elkeen van die funksie se argumente op 'n aparte reël ingevoer word.
-
Kies sel E2 van die werkblad. Dit is die ligging waar die resultate van die tweedimensionele opsoekformule sal vertoon.
Image -
Op die lint, gaan na die Formules-oortjie en kies Opsoek en verwysing.
Image -
Kies VLOOKUP om die Function Arguments dialoogkassie oop te maak.
Image - Die dialoogkassie Funksie-argumente is waar die parameters van die VLOOKUP-funksie ingevoer word.
Voer die opsoekwaarde-argument in
Gewoonlik pas die opsoekwaarde by 'n dataveld in die eerste kolom van die datatabel. In hierdie voorbeeld verwys die opsoekwaarde na die naam van die deel wat jy inligting wil vind. Die toelaatbare tipes data vir die opsoekwaarde is teksdata, logiese waardes, getalle en selverwysings.
Absolute Selverwysings
Wanneer formules in Excel gekopieer word, verander selverwysings om die nuwe ligging te weerspieël. As dit gebeur, verander D2, die selverwysing vir die opsoekwaarde, en skep foute in selle F2 en G2.
Absolute selverwysings verander nie wanneer formules gekopieer word nie.
Om die foute te voorkom, skakel die selverwysing D2 om in 'n absolute selverwysing. Om 'n absolute selverwysing te skep, druk die F4-sleutel. Dit voeg dollartekens rondom die selverwysing by soos $D$2.
-
In die Function Arguments dialoogkassie, plaas die wyser in die lookup_value tekskassie. Kies dan in die werkblad sel D2 om hierdie selverwysing by die lookup_value te voeg. Sel D2 is waar die onderdeelnaam ingevoer sal word.
Image -
Sonder om die invoegpunt te skuif, druk die F4 sleutel om D2 om te skakel na die absolute selverwysing $D$2.
Image - Laat die VLOOKUP-funksie-dialoogkassie oop vir die volgende stap in die tutoriaal.
Voer die Table Array-argument in
'n Tabelskikking is die tabel van data wat die opsoekformule deursoek om die inligting te vind wat jy wil hê. Die tabelskikking moet ten minste twee kolomme data bevat.
Die eerste kolom bevat die opsoekwaarde-argument (wat in die vorige afdeling opgestel is), terwyl die tweede kolom deur die opsoekformule gesoek word om die inligting te vind wat jy spesifiseer.
Die tabelskikkingsargument moet ingevoer word as óf 'n reeks wat die selverwysings vir die datatabel bevat óf as 'n reeksnaam.
Om die tabel van data by die VLOOKUP-funksie te voeg, plaas die wyser in die table_array tekskassie in die dialoogkassie en tik Tableom die reeksnaam vir hierdie argument in te voer.
Nest die KOLOM-funksie
Normaalweg gee VLOOKUP slegs data van een kolom van 'n datatabel terug. Hierdie kolom word gestel deur die kolomindeksnommerargument. In hierdie voorbeeld is daar egter drie kolomme, en die kolomindeksnommer moet verander word sonder om die opsoekformule te wysig. Om dit te bereik, nes die COLUMN-funksie binne die VLOOKUP-funksie as die Col_index_num-argument.
Wanneer funksies nes, maak Excel nie die tweede funksie se dialoogkassie oop om sy argumente in te voer nie. Die KOLOM-funksie moet met die hand ingevoer word. Die KOLOM-funksie het net een argument, die Verwysingsargument, wat 'n selverwysing is.
Die COLUMN-funksie gee die nommer van die kolom wat as die verwysingsargument verskaf is, terug. Dit verander die kolomletter in 'n nommer.
Om die prys van 'n item te vind, gebruik die data in kolom 2 van die datatabel. Hierdie voorbeeld gebruik kolom B as die verwysing om 2 in die Col_index_num argument in te voeg.
-
In die Function Arguments dialoogkassie, plaas die wyser in die Col_index_num tekskassie en tik COLUMN(. (Maak seker dat jy die oop ronde hakie insluit.)
Image -
In die werkblad, kies sel B1 om daardie selverwysing as die verwysingsargument in te voer.
Image - Tik 'n afsluitende ronde hakie om die KOLOM-funksie te voltooi.
Voer die VLOOKUP-reeksopsoekargument in
VLOOKUP se Range_lookup-argument is 'n logiese waarde (WAAR of ONWAAR) wat aandui of VLOOKUP 'n presiese of 'n benaderde passing met die Lookup_value moet vind.
- TRUE of weggelaat: VLOOKUP gee 'n noue passing terug met die Lookup_value. As 'n presiese passing nie gevind word nie, gee VLOOKUP die volgende grootste waarde. Die data in die eerste kolom van Table_array moet in stygende volgorde gesorteer word.
- FALSE: VLOOKUP gebruik 'n presiese passing met die Lookup_value. As daar twee of meer waardes in die eerste kolom van Table_array is wat ooreenstem met die opsoekwaarde, word die eerste waarde wat gevind is, gebruik. As 'n presiese passing nie gevind word nie, word 'n N/A-fout teruggestuur.
In hierdie tutoriaal sal spesifieke inligting oor 'n spesifieke hardeware-item opgesoek word, so die Range_lookup is op FALSE gestel.
In die Function Arguments dialoogkassie, plaas die wyser in die Range_lookup tekskassie en tik False om vir VLOOKUP te sê om 'n presiese passing vir die data terug te gee.
Kies OK om die opsoekformule te voltooi en dialoogkassie toe te maak. Sel E2 sal 'n N/A-fout bevat omdat die opsoekkriteria nie in sel D2 ingevoer is nie. Hierdie fout is tydelik. Dit sal reggestel word wanneer die opsoekkriteria bygevoeg word in die laaste stap van hierdie tutoriaal.
Kopieer die opsoekformule en voer kriteria in
Die opsoekformule haal data uit verskeie kolomme van die datatabel op een slag. Om dit te doen, moet die opsoekformule in al die velde wees waaruit jy inligting wil hê.
Om data uit kolomme 2, 3 en 4 van die datatabel te haal (die prys, die onderdeelnommer en die verskaffer se naam), voer 'n gedeeltelike naam as die Opsoekwaarde in.
Aangesien die data in 'n gereelde patroon in die werkblad uiteengesit is, kopieer die opsoekformule in sel E2 na selle F2 en G2 Soos die formule gekopieer word, werk Excel die relatiewe selverwysing in die KOLOM-funksie (sel B1) op om die formule se nuwe ligging te weerspieël. Excel verander nie absolute selverwysing (soos $D$2) en die genoemde reeks (tabel) aangesien die formule gekopieer word.
Daar is meer as een manier om data in Excel te kopieer, maar die maklikste manier is om die Vulhandvatsel te gebruik.
-
Kies sel E2, waar die opsoekformule geleë is, om dit die aktiewe sel te maak.
Image -
Sleep die vulhandvatsel oor na sel G2. Selle F2 en G2 vertoon die N/A-fout wat in sel E2 voorkom.
Image -
Om die opsoekformules te gebruik om inligting uit die datatabel te haal, kies in die werkblad sel D2, tik Widget, en druk Enter.
Image Die volgende inligting word in selle E2 tot G2 vertoon.
- E2: $14,76 - die prys van 'n legstuk
- F2: PN-98769 - die onderdeelnommer vir 'n legstuk
- G2: Widgets Inc. - die naam van die verskaffer vir legstukke
-
Om die VLOOKUP-skikkingformule te toets, tik die naam van ander dele in sel D2 in en neem die resultate in selle E2 tot G2 waar.
Image - Elke sel wat die opsoekformule bevat, bevat 'n ander stukkie data oor die hardeware-item waarna jy gesoek het.
Die VLOOKUP-funksie met geneste funksies soos COLUMN bied 'n kragtige metode om data binne 'n tabel op te soek, deur ander data as 'n opsoekverwysing te gebruik.
