VLOOKUP-funksie in Excel – Beginnersgids: Sintaksis en voorbeelde

Vandag begin ons 'n reeks artikels wat een van die nuttigste kenmerke van Excel beskryf − VPR (VLOOKUP). Hierdie funksie is terselfdertyd een van die mees komplekse en die minste verstaanbaar.

In hierdie handleiding oor VPR Ek sal probeer om die basiese beginsels so eenvoudig moontlik uit te lê om die leerproses so duidelik moontlik vir onervare gebruikers te maak. Daarbenewens sal ons verskeie voorbeelde met Excel-formules bestudeer wat die mees algemene gebruiksgevalle vir die funksie sal demonstreer VPR.

  • Algemene beskrywing en sintaksis
  • VLOOKUP Voorbeelde
    • Hoe om VLOOKUP te gebruik om in 'n ander Excel-blad te soek
    • Soek in 'n ander werkboek met VLOOKUP
    • Hoe om 'n genoemde reeks of tabel in formules met VLOOKUP te gebruik
    • Gebruik jokertekens in VLOOKUP-formules
  • Presiese of benaderde passing in die VLOOKUP-funksie
  • VLOOKUP in Excel – jy moet dit onthou!

VLOOKUP-funksie in Excel – algemene beskrywing en sintaksis

So wat is dit VPR? Wel, eerstens, dit is 'n Excel-funksie. Wat doen sy? Dit soek die waarde wat jy spesifiseer op en gee die ooreenstemmende waarde van die ander kolom terug. Tegnies gesproke, VPR soek die waarde in die eerste kolom van die gegewe reeks op en gee die resultaat van 'n ander kolom in dieselfde ry terug.

In die mees algemene toepassing, die funksie VPR soek die databasis vir 'n gegewe unieke identifiseerder en onttrek sommige inligting wat daarmee verband hou uit die databasis.

Eerste letter in funksie naam VPR (VLOOKUP) beteken Вvertikaal (Vvertikaal). Daardeur kan jy onderskei VPR van GPR (HLOOKUP), wat soek na 'n waarde in die boonste ry van 'n reeks − Гhorisontaal (Hhorisontaal).

funksie VPR beskikbaar in Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP en Excel 2000.

Sintaksis van die VLOOKUP-funksie

funksie VPR (VLOOKUP) het die volgende sintaksis:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Soos jy kan sien, 'n funksie VPR in Microsoft Excel het 4 opsies (of argumente). Die eerste drie is verpligtend, die laaste is opsioneel.

  • lookup_value (lookup_value) – Die waarde waarna gesoek moet word. Dit kan 'n waarde (getal, datum, teks) of 'n selverwysing (wat die opsoekwaarde bevat) wees, of 'n waarde wat deur 'n ander Excel-funksie teruggestuur word. Byvoorbeeld, hierdie formule sal die waarde soek 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

As die opsoekwaarde minder is as die kleinste waarde in die eerste kolom van die reeks wat opgesoek word, sal die funksie VPR sal 'n fout rapporteer #BY (#N/A).

  • table_array (tabel) – twee of meer kolomme data. Onthou, die funksie VPR soek altyd na die waarde in die eerste kolom van die reeks wat in die argument gegee word table_array (tabel). Die sigbare reeks kan verskeie data bevat, soos teks, datums, getalle, booleans. Die funksie is hoofletter-onsensitief, wat beteken dat hoofletters en kleinletters dieselfde beskou word. Ons formule sal dus die waarde soek 40 in selle van A2 om A15, want A is die eerste kolom van die reeks A2:B15 wat in die argument gegee word table_array (tabel):

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • col_index_num (kolom_nommer) is die nommer van die kolom in die gegewe reeks waaruit die waarde in die gevind ry teruggestuur sal word. Die mees linkse kolom in die gegewe reeks is 1, is die tweede kolom 2, is die derde kolom 3 en so aan. Nou kan jy die hele formule lees:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    Formule wat waarde soek 40 in die reeks A2: A15 en gee die ooreenstemmende waarde van kolom B terug (omdat B die tweede kolom in die reeks A2:B15 is).

As die waarde van die argument col_index_num (kolom_nommer) minder as 1dan VPR sal 'n fout rapporteer Fout # WAARDE! (#WAARDE!). En as dit meer is as die aantal kolomme in die reeks table_array (tabel), sal die funksie 'n fout terugstuur #REF! (#LINK!).

  • reeks_opsoek (range_lookup) – bepaal waarna om te kyk:
    • presiese passing, argument moet gelyk wees ONWAAR (ONWAAR);
    • benaderde passing, argument is gelyk WARE KODE (WAAR) of glad nie gespesifiseer nie.

    Hierdie parameter is opsioneel, maar baie belangrik. Later in hierdie handleiding oor VPR Ek sal jou 'n paar voorbeelde wys wat verduidelik hoe om formules te skryf om presiese en benaderde passings te vind.

VLOOKUP Voorbeelde

Ek hoop die funksie VPR word vir jou 'n bietjie duideliker. Kom ons kyk nou na 'n paar gebruiksgevalle VPR in formules met werklike data.

Hoe om VLOOKUP te gebruik om in 'n ander Excel-blad te soek

In die praktyk, formules met 'n funksie VPR word selde gebruik om data op dieselfde werkblad te soek. Meer dikwels as nie, sal jy die ooreenstemmende waardes van 'n ander blad opsoek en haal.

Ten einde te gebruik VPR, soek in 'n ander Microsoft Excel-blad, Jy moet in die argument table_array (tabel) spesifiseer die bladnaam met 'n uitroepteken gevolg deur 'n reeks selle. Byvoorbeeld, die volgende formule toon dat die reeks A2: B15 is op 'n blad met die naam Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Natuurlik hoef die bladnaam nie handmatig ingevoer te word nie. Begin net die formule tik, en wanneer dit by die argument kom table_array (tabel), skakel oor na die verlangde blad en kies die verlangde reeks selle met die muis.

Die formule wat in die skermkiekie hieronder gewys word, soek die teks “Produk 1” in kolom A (dit is die 1ste kolom van die reeks A2:B9) op 'n werkblad Pryse.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Onthou asseblief dat wanneer jy na 'n tekswaarde soek, jy dit tussen aanhalingstekens ("") moet plaas, soos gewoonlik in Excel-formules gedoen word.

Vir argument table_array (tabel) is dit wenslik om altyd absolute verwysings (met die $-teken) te gebruik. In hierdie geval sal die soekreeks onveranderd bly wanneer die formule na ander selle gekopieer word.

Soek in 'n ander werkboek met VLOOKUP

Om te funksioneer VPR tussen twee Excel-werkboeke gewerk het, moet jy die werkboeknaam tussen vierkantige hakies voor die bladnaam spesifiseer.

Byvoorbeeld, hieronder is 'n formule wat die waarde soek 40 op die blad Sheet2 in die boek Getalle.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Hier is die maklikste manier om 'n formule in Excel te skep VPRwat na 'n ander werkboek skakel:

  1. Maak albei boeke oop. Dit is nie nodig nie, maar dit is makliker om 'n formule op hierdie manier te skep. Jy wil nie die werkboeknaam handmatig invoer nie, doen jy? Daarbenewens sal dit jou beskerm teen toevallige tikfoute.
  2. Begin 'n funksie tik VPRen wanneer dit by die argument kom table_array (tabel), skakel oor na 'n ander werkboek en kies die vereiste soekreeks daarin.

Die skermkiekie hieronder wys die formule met die soektog ingestel op 'n reeks in die werkboek Pryslys.xlsx op die blad Pryse.

funksie VPR sal werk selfs wanneer jy die gesoekte werkboek toemaak en die volledige pad na die werkboeklêer verskyn in die formulebalk, soos hieronder getoon:

As die naam van die werkboek of blad spasies bevat, moet dit in apostrofe ingesluit word:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Hoe om 'n genoemde reeks of tabel in formules met VLOOKUP te gebruik

As jy van plan is om dieselfde soekreeks in verskeie funksies te gebruik VPR, kan jy 'n benoemde reeks skep en sy naam in die formule as 'n argument invoer table_array (tabel).

Om 'n benoemde reeks te skep, kies eenvoudig die selle en voer 'n toepaslike naam in die veld in Naam, aan die linkerkant van die formulebalk.

Nou kan jy die volgende formule neerskryf om die prys van 'n produk te vind Produk 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Die meeste reeksname werk vir die hele Excel-werkboek, so dit is nie nodig om die bladnaam vir die argument te spesifiseer nie table_array (tabel), selfs al is die formule en die soekreeks op verskillende werkblaaie. As hulle in verskillende werkboeke is, dan moet jy voor die naam van die reeks die naam van die werkboek spesifiseer, byvoorbeeld, soos volg:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

So die formule lyk baie duideliker, stem saam? Die gebruik van benoemde reekse is ook 'n goeie alternatief vir absolute verwysings omdat die benoemde reeks nie verander wanneer jy die formule na ander selle kopieer nie. Dit beteken dat jy seker kan wees dat die soekreeks in die formule altyd korrek sal bly.

As jy 'n reeks selle omskep in 'n volwaardige Excel-sigblad met behulp van die opdrag Tabel (Tabel) oortjie invoeging (Voeg in), wanneer jy dan 'n reeks met die muis kies, sal Microsoft Excel outomaties die kolomname (of die tabelnaam as jy die hele tabel kies) by die formule voeg.

Die voltooide formule sal iets soos volg lyk:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Of dalk selfs so:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Wanneer benoemde reekse gebruik word, sal die skakels na dieselfde selle wys, ongeag waar jy die funksie kopieer VPR binne die werkboek.

Gebruik jokertekens in VLOOKUP-formules

Soos met baie ander funksies, VPR Jy kan die volgende jokertekens gebruik:

  • Vraagteken (?) – vervang enige enkele karakter.
  • Asterisk (*) – vervang enige volgorde van karakters.

Gebruik jokertekens in funksies VPR kan in baie gevalle nuttig wees, byvoorbeeld:

  • Wanneer jy nie presies die teks onthou wat jy moet vind nie.
  • Wanneer jy 'n woord wil vind wat deel is van die inhoud van 'n sel. Weet dat VPR soek volgens die inhoud van die sel as geheel, asof die opsie geaktiveer is Pas die hele sel inhoud (Hele sel) in die standaard Excel-soektog.
  • Wanneer 'n sel ekstra spasies aan die begin of einde van die inhoud bevat. In so 'n situasie, kan jy jou brein vir 'n lang tyd ruk, probeer om uit te vind hoekom die formule nie werk nie.

Voorbeeld 1: Soek teks wat met sekere karakters begin of eindig

Kom ons sê jy wil vir 'n spesifieke kliënt soek in die databasis wat hieronder gewys word. Jy onthou nie sy van nie, maar jy weet dat dit met “ack” begin. Hier is 'n formule wat die werk goed sal doen:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Noudat jy seker is dat jy die korrekte naam gekry het, kan jy dieselfde formule gebruik om die bedrag wat deur hierdie kliënt betaal is, te vind. Om dit te doen, verander net die derde argument van die funksie VPR na die verlangde kolomnommer. In ons geval is dit kolom C (3de in die reeks):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Hier is nog 'n paar voorbeelde met jokertekens:

~ Soek 'n naam wat eindig op "man":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Soek 'n naam wat met "advertensie" begin en met "seun" eindig:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Ons vind die voornaam in die lys wat uit 5 karakters bestaan:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Om te funksioneer VPR met jokertekens het korrek gewerk, as die vierde argument wat jy altyd moet gebruik ONWAAR (ONWAAR). As die soekreeks meer as een waarde bevat wat ooreenstem met die soekterme met jokertekens, dan sal die eerste waarde wat gevind is, teruggestuur word.

Voorbeeld 2: Kombineer jokertekens en selverwysings in VLOOKUP-formules

Kom ons kyk nou na 'n effens meer komplekse voorbeeld van hoe om met behulp van die funksie te soek VPR volgens waarde in 'n sel. Stel jou voor dat kolom A 'n lys lisensiesleutels is, en kolom B 'n lys name is wat 'n lisensie besit. Daarbenewens het jy 'n deel (verskeie karakters) van 'n soort lisensiesleutel in sel C1, en jy wil die naam van die eienaar vind.

Dit kan gedoen word deur die volgende formule te gebruik:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Hierdie formule soek die waarde van sel C1 in die gegewe reeks op en gee die ooreenstemmende waarde van kolom B terug. Let daarop dat ons in die eerste argument 'n ampersand (&) karakter voor en na die selverwysing gebruik om die teksstring te koppel.

Soos u in die figuur hieronder kan sien, is die funksie VPR gee "Jeremy Hill" terug omdat sy lisensiesleutel die volgorde van karakters uit sel C1 bevat.

Let daarop dat die argument table_array (tabel) in die skermkiekie hierbo bevat die naam van die tabel (Tabel 7) in plaas daarvan om 'n reeks selle te spesifiseer. Dit is wat ons in die vorige voorbeeld gedoen het.

Presiese of benaderde passing in die VLOOKUP-funksie

En laastens, kom ons kyk van naderby na die laaste argument wat vir die funksie gespesifiseer is VPR - reeks_opsoek (interval_aansig). Soos aan die begin van die les genoem, is hierdie argument baie belangrik. Jy kan heeltemal verskillende resultate kry in dieselfde formule met die waarde daarvan WARE KODE (WAAR) of ONWAAR (ONWAAR).

Laat ons eers uitvind wat Microsoft Excel bedoel met presiese en benaderde passings.

  • As die argument reeks_opsoek (reeks_soek) is gelyk aan ONWAAR (ONWAAR), die formule soek 'n presiese passing, dws presies dieselfde waarde as wat in die argument gegee word lookup_value (opsoek_waarde). As in die eerste kolom van die reeks tbekwaam_skikking (tabel) twee of meer waardes teëkom wat ooreenstem met die argument lookup_value (soekwaarde), dan sal die eerste een gekies word. Indien geen passings gevind word nie, sal die funksie 'n fout rapporteer #BY (#N/A). Byvoorbeeld, die volgende formule sal 'n fout rapporteer #BY (#N/A) as daar geen waarde in die reeks A2:A15 is nie 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • As die argument reeks_opsoek (reeks_soek) is gelyk aan WARE KODE (WAAR), die formule soek 'n benaderde passing. Meer presies, eers die funksie VPR soek vir 'n presiese passing, en as niemand gevind word nie, kies 'n benaderde een. 'n Geskatte passing is die grootste waarde wat nie die waarde wat in die argument gespesifiseer is, oorskry nie. lookup_value (opsoek_waarde).

As die argument reeks_opsoek (reeks_soek) is gelyk aan WARE KODE (WAAR) of nie gespesifiseer nie, dan moet die waardes in die eerste kolom van die reeks in stygende volgorde gesorteer word, dit wil sê van die kleinste na die grootste. Andersins, die funksie VPR kan 'n foutiewe resultaat gee.

Om die belangrikheid van keuse beter te verstaan WARE KODE (WAARHEID) of ONWAAR (ONWAAR), kom ons kyk na nog 'n paar formules met die funksie VPR en kyk na die resultate.

Voorbeeld 1: Vind 'n presiese pasmaat met VLOOKUP

Soos jy onthou, om te soek na 'n presiese passing, die vierde argument van die funksie VPR behoort saak te maak ONWAAR (ONWAAR).

Kom ons gaan terug na die tabel vanaf die heel eerste voorbeeld en vind uit watter dier teen 'n spoed kan beweeg 50 myl per uur. Ek glo dat hierdie formule jou geen probleme sal veroorsaak nie:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Let daarop dat ons soekreeks (kolom A) twee waardes bevat 50 – in selle A5 и A6. Formule gee waarde van sel terug B5. Hoekom? Want wanneer jy 'n presiese pasmaat soek, is die funksie VPR gebruik die eerste waarde gevind wat ooreenstem met die een waarna gesoek word.

Voorbeeld 2: Gebruik VLOOKUP om 'n benaderde pasmaat te vind

Wanneer jy die funksie gebruik VPR om te soek na 'n benaderde passing, dws wanneer die argument reeks_opsoek (reeks_soek) is gelyk aan WARE KODE (WAAR) of weggelaat, die eerste ding wat jy moet doen is om die reeks volgens die eerste kolom in stygende volgorde te sorteer.

Dit is baie belangrik omdat die funksie VPR gee die volgende grootste waarde na die gegewe een terug, en dan stop die soektog. As jy die korrekte sortering nalaat, sal jy met baie vreemde resultate of 'n foutboodskap opeindig. #BY (#N/A).

Nou kan jy een van die volgende formules gebruik:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Soos jy kan sien, wil ek uitvind aan watter van die diere die naaste spoed het 69 myl per uur. En hier is die resultaat wat die funksie aan my terugbesorg het VPR:

Soos u kan sien, het die formule 'n resultaat opgelewer Antelope (Bos), wie se spoed 61 myl per uur, hoewel die lys ook insluit Jagluiperd (Cheetah) wat op spoed hardloop 70 myl per uur, en 70 is nader aan 69 as 61, is dit nie? Hoekom gebeur dit? Omdat die funksie VPR wanneer na 'n benaderde passing gesoek word, gee die grootste waarde terug wat nie groter is as die een waarna gesoek word nie.

Ek hoop hierdie voorbeelde werp lig op die werk met die funksie VPR in Excel, en jy kyk nie meer na haar as 'n buitestander nie. Nou maak dit nie seer om die sleutelpunte van die materiaal wat ons bestudeer het kortliks te herhaal om dit beter in die geheue vas te stel nie.

VLOOKUP in Excel – jy moet dit onthou!

  1. funksie VPR Excel kan nie links kyk nie. Dit soek altyd na die waarde in die mees linkerkolom van die reeks wat deur die argument gegee word table_array (tabel).
  2. In funksie VPR alle waardes is hoofletter-onsensitief, dws klein en groot letters is ekwivalent.
  3. As die waarde waarna jy soek minder is as die minimum waarde in die eerste kolom van die reeks wat opgesoek word, sal die funksie VPR sal 'n fout rapporteer #BY (#N/A).
  4. As 3de argument col_index_num (kolom_nommer) minder as 1funksie VPR sal 'n fout rapporteer Fout # WAARDE! (#WAARDE!). As dit groter is as die aantal kolomme in die reeks table_array (tabel), sal die funksie 'n fout rapporteer #REF! (#LINK!).
  5. Gebruik absolute selverwysings in argument table_array (tabel) sodat die korrekte soekreeks behoue ​​bly wanneer die formule gekopieer word. Probeer om genoemde reekse of tabelle in Excel as 'n alternatief te gebruik.
  6. Wanneer jy 'n benaderde pasmaatsoektog doen, onthou dat die eerste kolom in die reeks waarna jy soek, in stygende volgorde gesorteer moet word.
  7. Ten slotte, onthou die belangrikheid van die vierde argument. Gebruik waardes WARE KODE (WAARHEID) of ONWAAR (ONWAAR) doelbewus en jy sal ontslae raak van baie hoofpyne.

In die volgende artikels van ons funksie-tutoriaal VPR in Excel, sal ons meer gevorderde voorbeelde leer, soos om verskeie berekeninge uit te voer met behulp van VPR, onttrek waardes uit verskeie kolomme, en meer. Dankie dat jy hierdie tutoriaal gelees het en ek hoop om jou volgende week weer te sien!

Lewer Kommentaar