01 van 03
Vind benaderde ooreenkomste met data met Excel se VLOOKUP
Hoe die VLOOKUP funksie werk
Excel se VLOOKUP funksie , wat staan vir vertikale opzoeking , kan gebruik word om spesifieke inligting in 'n tabel van data of databasis op te soek.
VLOOKUP gee normaalweg 'n enkele dataveld as sy uitvoer. Hoe dit dit doen is:
- Jy gee 'n naam of kykup_value wat VLOOKUP vertel in watter ry of rekord van die datatabel om die gewenste data te soek
- U verskaf die kolomnommer - bekend as die kol_index_num - van die data wat u soek
- Die funksie soek na die lookup_value in die eerste kolom van die datatabel
- VLOOKUP soek dan die inligting wat jy soek van 'n ander veld van dieselfde rekord, met behulp van die verskafde kolomnommer
Sorteer die data eerste
Alhoewel dit nie altyd nodig is nie, is dit gewoonlik die beste om eers die omvang van data wat VLOOKUP soek in stygende volgorde te sorteer deur die eerste kolom van die reeks vir die sorteer sleutel te gebruik.
As die data nie gesorteer word nie, kan VLOOKUP 'n foutiewe resultaat teruggee.
Die VLOOKUP-funksie se sintaxis en argumente
'N Funksie se sintaksis verwys na die uitleg van die funksie en sluit die funksie se naam, hakies en argumente in .
Die sintaksis vir die VLOOKUP funksie is:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
soek _value - (vereis) die waarde om te soek na - soos die hoeveelheid wat in die prent hierbo verkoop is
table_array - (vereis) Dit is die tabel van data wat VLOOKUP soek om die inligting te vind wat jy na die bladsy soek.
- Die tabel_raam moet minstens twee kolomme data bevat
- Die eerste kolom bevat normaalweg die lookup_value
col_index_num - (vereis) die kolomnommer van die waarde wat u wil kry.
- Die nommering begin met die search_key kolom as kolom 1
- As col_index_num is ingestel op 'n getal groter as die aantal kolomme wat in die table_array- argument gekies is, is 'n #REF! Fout word deur die funksie teruggestuur
range_lookup - (opsioneel) dui aan of die reeks in stygende volgorde gesorteer word.
- Die data in die eerste kolom word as die sorteer sleutel gebruik
- 'N Boole-waarde - WAAR of ONWAAR is die enigste aanvaarbare waardes
- As dit weggelaat word, word die waarde as WAAR by verstek gestel
- As dit op WAAR of weggelaat is en die eerste kolom van die reeks nie in stygende volgorde gesorteer word nie, kan 'n foutiewe resultaat voorkom
- As dit ingestel is op WAAR of weggelaat en 'n presiese wedstryd vir die opzoeking _value nie gevind word nie, word die naaste wedstryd wat kleiner is in grootte of waarde as die soektog gebruik
- Indien ingestel op ONWAAR, aanvaar VLOOKUP slegs 'n presiese wedstryd vir die soektog _value . As daar verskeie ooreenstemmende waardes is, word die eerste ooreenstemmende waarde teruggestuur
- As dit ingestel is op ONWAAR en geen ooreenstemmende waarde vir die soektog is gevind nie, word 'n # N / A-fout deur die funksie teruggestuur
Voorbeeld: Vind die afslagkoers vir hoeveelheid gekoop
Die voorbeeld in die prent hierbo gebruik die VLOOKUP funksie om die verdiskonteringskoers te vind wat wissel afhangende van die hoeveelheid items wat aangekoop word.
Die voorbeeld toon dat die afslag vir die aankoop van 19 items 2% is. Dit is omdat die hoeveelheid kolom waardes bevat. As gevolg daarvan kan VLOOKUP nie 'n presiese wedstryd vind nie. In plaas daarvan moet 'n benaderde wedstryd gevind word om die korrekte diskontokoers te kan terugbesorg.
Om benaderde ooreenkomste te vind:
- sorteer die data in die table_array in stygende volgorde;
- stel die range_lookup- argument in WAAR
In die voorbeeld word die volgende formule met die VLOOKUP funksie gebruik om die afslag vir hoeveelhede gekoopte goedere te vind.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, WAAR)
Alhoewel hierdie formule net in 'n werkbladsel getik kan word, is 'n ander opsie, soos gebruik met die stappe wat hieronder gelys word, om die dialoog van die funksie te gebruik om sy argumente in te voer.
- Deur die dialoogkassie te gebruik, maak dit makliker om 'n funksie se argumente korrek in te voer.
Open die VLOOKUP dialoogkassie
Die stappe wat gebruik word om die VLOOKUP funksie in die bogenoemde beeld in sel B2 in te voer, is:
- Klik op sel B2 om dit die aktiewe sel te maak - die plek waar die resultate van die VLOOKUP-funksie vertoon word
- Klik op die Formules- oortjie.
- Kies Soek en verwysing uit die lint om die aftreklys vir funksies oop te maak
- Klik op VLOOKUP in die lys om die dialoog van die funksie te vertoon
02 van 03
Voer Excel se VLOOKUP-funksie se argumente in
Met verwysing na selverwysings
Die argumente vir die VLOOKUP-funksie word in afsonderlike reëls van die dialoogkassie ingevoer, soos in die prent hierbo aangedui.
Die selverwysings wat as argumente gebruik kan word, kan in die korrekte lyn getik word, of, soos in die onderstaande stappe gedoen word, wat die hoogte van die verlangde reeks selle met die muiswyser aandui, kan gebruik word om dit in die dialoogkassie in te voer. .
Die voordele van die gebruik van die wysheid sluit in:
- Dit is vinniger as tik;
- Minder foute word gemaak om die korrekte selverwysings in te voer.
Gebruik Relatiewe en Absolute Selverwysings met Argumente
Dit is nie ongewoon om verskeie kopieë van VLOOKUP te gebruik om verskillende inligting van dieselfde data tabel terug te gee nie. Om dit makliker te maak, kan VLOOKUP dikwels van een sel na 'n ander gekopieer word. Wanneer funksies na ander selle gekopieer word, moet daar sorg gedra word dat die gevolglike selverwysings korrek is met die funksie se nuwe ligging.
In die bostaande beeld omsingel dollartekens ( $ ) die selverwysings vir die table_array- argument wat aandui dat hulle absolute selverwysings is , wat beteken dat hulle nie sal verander as die funksie na 'n ander sel gekopieer word nie. Dit is wenslik aangesien veelvuldige afskrifte van VLOOKUP alle dieselfde tabel van data as die bron van inligting sal verwys.
Die selverwysing wat gebruik word vir lookup_value, aan die ander kant , word nie omring deur dollartekens nie, wat dit 'n relatiewe selverwysing maak. Relatiewe selverwysings verander wanneer hulle gekopieer word om hul nuwe ligging te reflekteer relatief tot die posisie van die data waarna hulle verwys.
Voer die funksie Argumente in
- Klik op die Lookup _value lyn in die VLOOKUP dialoogkassie
- Klik op sel C2 in die werkblad om hierdie selverwysing as die search_key- argument in te voer
- Klik op die Table_array lyn van die dialoogkassie
- Merk selle C5 tot D8 in die werkblad om hierdie reeks as die Table_array- argument te betree - die tabelopskrifte is nie ingesluit nie
- Druk die F4 sleutel op die sleutelbord om die reeks te verander na absolute selverwysings
- Klik op die Col_index_numreël van die dialoogkassie
- Tik 'n 2 op hierdie reël as die Col_index_num- argument, aangesien die verdiskonteringskoerse in kolom 2 van die Table_array- argument geleë is.
- Klik op die Range_lookup lyn van die dialoogkassie
- Tik die woord Waar as die Range_lookup- argument
- Druk die Enter- sleutel op die sleutelbord om die dialoog te sluit en terug te keer na die werkblad
- Die antwoord 2% (die afslagkoers vir die gekoopte hoeveelheid) moet in sel D2 van die werkblad verskyn
- As jy op sel D2 klik, verskyn die volledige funksie = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, WAAR) in die formulebalk bokant die werkblad.
Waarom VLOOKUP het 2% as 'n resultaat behaal
- In die voorbeeld bevat die hoeveelheid kolom nie 'n presiese pas vir die search_key- waarde van 19 nie.
- Aangesien die is_sorted- argument op WAAR gestel word, sal VLOOKUP 'n benaderde pas by die search_key- waarde vind.
- Die naaste waarde in grootte wat nog kleiner is as die search_key- waarde van 19 is 11.
- VLOOKUP kyk dus na die afslagpryse in die ry wat 11 bevat, en gee gevolglik 'n verdiskonteringskoers van 2%.
03 van 03
Excel VLOOKUP werk nie: # NVT en #REF foute
VLOOKUP Fout Boodskappe
Die volgende foutboodskappe word geassosieer met VLOOKUP.
A # Nvt ("waarde nie beskikbaar nie") Fout word vertoon as:
- Die soektog _value word nie in die eerste kolom van die reeksargument gevind nie
- Die Table_array- argument is onakkuraat. Byvoorbeeld, die argument mag leë kolomme aan die linkerkant van die reeks insluit
- Die Range_lookup- argument is ingestel op ONWAAR en 'n presiese wedstryd vir die search_key- argument kan nie in die eerste kolom van die reeks gevind word nie.
- Die Range_lookup argument is ingestel op WAAR en al die waardes in die eerste kolom van die reeks is groter as die soektog
'N #REF! ("verwysing buite bereik") Fout word vertoon as:
- Die Col_index_num argument is groter as die aantal kolomme in die Table_array