VLOOKUP-funktion i Excel – Begyndervejledning: Syntaks og eksempler

Indhold

VLOOKUP funktion i Excel – generel beskrivelse og syntaks
- Syntaks for VLOOKUP-funktionen
VLOOKUP eksempler
Nøjagtig eller omtrentlig overensstemmelse i VLOOKUP-funktionen
- Eksempel 1: Find et nøjagtigt match med VLOOKUP
- Eksempel 2: Brug af VLOOKUP til at finde et omtrentligt match
VLOOKUP i Excel – du skal huske dette!

I dag starter vi en række artikler, der beskriver en af de mest nyttige funktioner i Excel − VPR (VOPSLAG). Denne funktion er på samme tid en af de mest komplekse og mindst forståede.

I denne tutorial vedr VPR Jeg vil forsøge at lægge det grundlæggende ud så enkelt som muligt for at gøre læringsprocessen så overskuelig som muligt for uerfarne brugere. Derudover vil vi studere flere eksempler med Excel-formler, der vil demonstrere de mest almindelige use cases for funktionen VPR.

VLOOKUP funktion i Excel – generel beskrivelse og syntaks

Så hvad er det VPR? Nå, først og fremmest er det en Excel-funktion. Hvad laver hun? Den slår den værdi op, du angiver, og returnerer den tilsvarende værdi fra den anden kolonne. Teknisk set, VPR slår værdien op i den første kolonne i det givne område og returnerer resultatet fra en anden kolonne i samme række.

I den mest almindelige applikation er funktionen VPR søger i databasen efter en given unik identifikator og udtrækker nogle oplysninger relateret til den fra databasen.

Første bogstav i funktionsnavn VPR (VLOOKUP) betyder Вlodret (Vlodret). Ved det kan du skelne VPR fra GPR (HLOOKUP), som søger efter en værdi i den øverste række af et område − Гvandret (Hvandret).

Funktion VPR tilgængelig i Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP og Excel 2000.

Syntaks for VLOOKUP-funktionen

Funktion VPR (VLOOKUP) har følgende syntaks:

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

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

Som du kan se, en funktion VPR i Microsoft Excel har 4 muligheder (eller argumenter). De tre første er obligatoriske, den sidste er valgfri.

opslagsværdi (lookup_value) – Værdien, der skal kigges efter. Dette kan være en værdi (tal, dato, tekst) eller en cellereference (som indeholder opslagsværdien) eller en værdi, der returneres af en anden Excel-funktion. For eksempel vil denne formel lede efter værdien 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Hvis opslagsværdien er mindre end den mindste værdi i den første kolonne i det område, der slås op, vil funktionen VPR vil rapportere en fejl #AT (#N/A).

tabelmatrix (tabel) – to eller flere kolonner med data. Husk funktionen VPR søger altid efter værdien i den første kolonne i det område, der er angivet i argumentet tabelmatrix (bord). Det synlige område kan indeholde forskellige data, såsom tekst, datoer, tal, booleaner. Funktionen er ufølsom mellem store og små bogstaver, hvilket betyder, at store og små bogstaver betragtes som ens. Så vores formel vil lede efter værdien 40 i celler fra A2 til A15, fordi A er den første kolonne i området A2:B15 givet i argumentet tabelmatrix (bord):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (kolonne_nummer) er nummeret på kolonnen i det givne område, hvorfra værdien i den fundne række vil blive returneret. Kolonnen længst til venstre i det givne område er 1, den anden kolonne er 2, den tredje kolonne er 3 og så videre. Nu kan du læse hele formlen:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Formel på udkig efter værdi 40 i rækken A2: A15 og returnerer den tilsvarende værdi fra kolonne B (fordi B er den anden kolonne i området A2:B15).

Hvis værdien af argumentet col_index_num (kolonne_nummer) mindre end 1derefter VPR vil rapportere en fejl #VÆRDI! (#VÆRDI!). Og hvis det er mere end antallet af kolonner i området tabelmatrix (tabel), vil funktionen returnere en fejl #REF! (#LINK!).

rækkevidde (range_lookup) – bestemmer, hvad der skal kigges efter:
- eksakt match, argument skal være ens FALSK (FALSK);
- omtrentlig match, argument er lig SAND KODE (TRUE) eller slet ikke specificeret.
Denne parameter er valgfri, men meget vigtig. Senere i denne tutorial på VPR Jeg vil vise dig nogle eksempler, der forklarer, hvordan man skriver formler til at finde nøjagtige og omtrentlige match.

VLOOKUP eksempler

Jeg håber funktionen VPR blive lidt tydeligere for dig. Lad os nu se på nogle use cases VPR i formler med rigtige data.

Sådan bruger du VLOOKUP til at søge i et andet Excel-ark

I praksis formler med en funktion VPR bruges sjældent til at søge efter data på det samme regneark. Oftere end ikke vil du slå op og hente tilsvarende værdier fra et andet ark.

For at kunne bruge VPR, søg i et andet Microsoft Excel-ark, Du skal i argumentet tabelmatrix (tabel) angiv arknavnet med et udråbstegn efterfulgt af et celleområde. For eksempel viser følgende formel, at området A2: B15 er på et ark navngivet Sheet2.

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

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

Arknavnet skal selvfølgelig ikke indtastes manuelt. Bare begynd at skrive formlen, og når det kommer til argumentet tabelmatrix (tabel), skift til det ønskede ark og vælg det ønskede celleområde med musen.

Formlen vist på skærmbilledet nedenfor ser efter teksten "Produkt 1" i kolonne A (det er den første kolonne i området A1:B2) på et regneark Priser.

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

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

Husk, at når du søger efter en tekstværdi, skal du sætte den i anførselstegn (""), som det normalt gøres i Excel-formler.

Til argumentation tabelmatrix (tabel) det er ønskeligt altid at bruge absolutte referencer (med $-tegnet). I dette tilfælde vil søgeområdet forblive uændret, når formlen kopieres til andre celler.

Søg i en anden projektmappe med VLOOKUP

At fungere VPR arbejdede mellem to Excel-projektmapper, skal du angive projektmappens navn i firkantede parenteser før arknavnet.

Nedenfor er for eksempel en formel, der leder efter værdien 40 på arket Sheet2 I bogen Numbers.xlsx:

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

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

Her er den nemmeste måde at oprette en formel i Excel med VPRsom linker til en anden projektmappe:

Åbn begge bøger. Dette er ikke påkrævet, men det er nemmere at oprette en formel på denne måde. Du ønsker ikke at indtaste projektmappens navn manuelt, vel? Derudover vil det beskytte dig mod utilsigtede tastefejl.
Begynd at skrive en funktion VPRog når det kommer til argumentet tabelmatrix (tabel), skift til en anden projektmappe og vælg det ønskede søgeområde i den.

Skærmbilledet nedenfor viser formlen med søgningen indstillet til et område i projektmappen Prisliste.xlsx på arket Priser.

Funktion VPR vil fungere, selv når du lukker den søgte projektmappe, og den fulde sti til projektmappefilen vises i formellinjen, som vist nedenfor:

Hvis navnet på projektmappen eller arket indeholder mellemrum, skal det være omgivet af apostrof:

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

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

Sådan bruges et navngivet område eller tabel i formler med VLOOKUP

Hvis du planlægger at bruge det samme søgeområde i flere funktioner VPR, kan du oprette et navngivet område og indtaste dets navn i formlen som et argument tabelmatrix (bord).

For at oprette et navngivet område skal du blot vælge cellerne og indtaste et passende navn i feltet Fornavn, til venstre for formellinjen.

Nu kan du nedskrive følgende formel for at finde prisen på et produkt Produkt 1:

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

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

De fleste områdenavne fungerer for hele Excel-projektmappen, så der er ingen grund til at angive arknavnet for argumentet tabelmatrix (tabel), selvom formlen og søgeområdet er på forskellige regneark. Hvis de er i forskellige projektmapper, skal du før navnet på området angive navnet på projektmappen, for eksempel sådan:

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

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

Så formlen ser meget klarere ud, enig? Brug af navngivne områder er også et godt alternativ til absolutte referencer, fordi det navngivne område ikke ændres, når du kopierer formlen til andre celler. Det betyder, at du kan være sikker på, at søgeområdet i formlen altid forbliver korrekt.

Hvis du konverterer en række celler til et fuldgyldigt Excel-regneark ved hjælp af kommandoen Bordlampe (Tabel) fanen Indsættelse (Indsæt), så når du vælger et område med musen, tilføjer Microsoft Excel automatisk kolonnenavnene (eller tabelnavnet, hvis du vælger hele tabellen) til formlen.

Den færdige formel vil se sådan ud:

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

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

Eller måske endda sådan her:

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

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

Når du bruger navngivne områder, vil linkene pege på de samme celler, uanset hvor du kopierer funktionen VPR i arbejdsbogen.

Brug af jokertegn i VLOOKUP-formler

Som med mange andre funktioner, VPR Du kan bruge følgende jokertegn:

Spørgsmålstegn (?) – erstatter ethvert enkelt tegn.
Asterisk (*) – erstatter enhver sekvens af tegn.

Brug af jokertegn i funktioner VPR kan være nyttig i mange tilfælde, for eksempel:

Når du ikke kan huske præcis den tekst, du skal finde.
Når du vil finde et ord, der er en del af indholdet i en celle. Ved det VPR søger efter indholdet af cellen som helhed, som om indstillingen er aktiveret Match hele celleindholdet (Hele cellen) i standard Excel-søgning.
Når en celle indeholder ekstra mellemrum i begyndelsen eller slutningen af indholdet. I en sådan situation kan du rack din hjerne i lang tid og forsøge at finde ud af, hvorfor formlen ikke virker.

Eksempel 1: Søger efter tekst, der starter eller slutter med bestemte tegn

Lad os sige, at du vil søge efter en bestemt kunde i databasen vist nedenfor. Du kan ikke huske hans efternavn, men du ved, at det starter med "ack". Her er en formel, der vil gøre arbejdet fint:

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

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

Nu hvor du er sikker på, at du har fundet det rigtige navn, kan du bruge den samme formel til at finde det beløb, som denne kunde har betalt. For at gøre dette skal du blot ændre det tredje argument for funktionen VPR til det ønskede kolonnenummer. I vores tilfælde er dette kolonne C (3. i rækken):

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

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

Her er nogle flere eksempler med jokertegn:

~ Find et navn, der ender på "mand":

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

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

~ Find et navn, der starter med "annonce" og slutter med "søn":

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

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

~ Vi finder fornavnet på listen, bestående af 5 tegn:

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

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

At fungere VPR med jokertegn fungerede korrekt, som det fjerde argument du altid skal bruge FALSK (FALSK). Hvis søgeområdet indeholder mere end én værdi, der matcher søgetermerne med jokertegn, returneres den første fundne værdi.

Eksempel 2: Kombiner jokertegn og cellereferencer i VLOOKUP-formler

Lad os nu se på et lidt mere komplekst eksempel på, hvordan man søger ved hjælp af funktionen VPR efter værdi i en celle. Forestil dig, at kolonne A er en liste over licensnøgler, og kolonne B er en liste over navne, der ejer en licens. Derudover har du en del (flere tegn) af en form for licensnøgle i celle C1, og du vil gerne finde navnet på ejeren.

Dette kan gøres ved hjælp af følgende formel:

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

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

Denne formel slår værdien op fra celle C1 i det givne område og returnerer den tilsvarende værdi fra kolonne B. Bemærk, at i det første argument bruger vi et og-tegn (&) før og efter cellereferencen til at linke tekststrengen.

Som du kan se på figuren nedenfor, er funktionen VPR returnerer "Jeremy Hill", fordi hans licensnøgle indeholder sekvensen af tegn fra celle C1.

Bemærk, at argumentet tabelmatrix (tabel) i skærmbilledet ovenfor indeholder navnet på tabellen (tabel 7) i stedet for at angive et celleområde. Dette er, hvad vi gjorde i det forrige eksempel.

Nøjagtig eller omtrentlig overensstemmelse i VLOOKUP-funktionen

Og endelig, lad os se nærmere på det sidste argument, der er angivet for funktionen VPR - rækkevidde (interval_view). Som nævnt i begyndelsen af lektionen er dette argument meget vigtigt. Du kan få helt forskellige resultater i den samme formel med dens værdi SAND KODE (SAND) eller FALSK (FALSK).

Lad os først finde ud af, hvad Microsoft Excel betyder med nøjagtige og omtrentlige match.

Hvis argumentet rækkevidde (range_lookup) er lig med FALSK (FALSK), formlen leder efter et nøjagtigt match, dvs. nøjagtig den samme værdi som angivet i argumentet opslagsværdi (opslagsværdi). Hvis i den første kolonne i området tstand_array (tabel) støder på to eller flere værdier, der matcher argumentet opslagsværdi (search_value), så vil den første blive valgt. Hvis der ikke findes nogen match, vil funktionen rapportere en fejl #AT (#N/A). For eksempel vil følgende formel rapportere en fejl #AT (#N/A), hvis der ikke er nogen værdi i området A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Hvis argumentet rækkevidde (range_lookup) er lig med SAND KODE (TRUE), formlen leder efter et omtrentligt match. Mere præcist, først funktionen VPR søger efter et nøjagtigt match, og hvis ingen findes, vælger du et omtrentligt. Et omtrentligt match er den største værdi, der ikke overstiger den værdi, der er angivet i argumentet. opslagsværdi (opslagsværdi).

Hvis argumentet rækkevidde (range_lookup) er lig med SAND KODE (TRUE) eller ikke angivet, så skal værdierne i den første kolonne i området sorteres i stigende rækkefølge, det vil sige fra mindste til største. Ellers funktionen VPR kan returnere et forkert resultat.

For bedre at forstå vigtigheden af valg SAND KODE (SANDHED) eller FALSK (FALSK), lad os se på nogle flere formler med funktionen VPR og se på resultaterne.

Eksempel 1: Find et nøjagtigt match med VLOOKUP

Som du husker, for at søge efter et nøjagtigt match, det fjerde argument for funktionen VPR burde have betydning FALSK (FALSK).

Lad os gå tilbage til tabellen fra det allerførste eksempel og finde ud af, hvilket dyr der kan bevæge sig med en hastighed 50 mil i timen. Jeg tror, at denne formel ikke vil give dig nogen vanskeligheder:

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

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

Bemærk, at vores søgeområde (kolonne A) indeholder to værdier 50 – i celler A5 и A6. Formel returnerer værdi fra celle B5. Hvorfor? For når man leder efter et nøjagtigt match, er funktionen VPR bruger den første fundet værdi, der matcher den, der søges efter.

Eksempel 2: Brug af VLOOKUP til at finde et omtrentligt match

Når du bruger funktionen VPR at søge efter et omtrentligt match, altså når argumentet rækkevidde (range_lookup) er lig med SAND KODE (TRUE) eller udeladt, den første ting du skal gøre er at sortere området efter den første kolonne i stigende rækkefølge.

Dette er meget vigtigt, fordi funktionen VPR returnerer den næststørste værdi efter den givne, og derefter stopper søgningen. Forsømmer du den korrekte sortering, vil du ende med meget mærkelige resultater eller en fejlmeddelelse. #AT (#N/A).

Nu kan du bruge en af følgende formler:

=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)

Som du kan se, vil jeg gerne finde ud af, hvilket af dyrene, der har tættest fart på 69 mil i timen. Og her er resultatet, funktionen gav mig tilbage VPR:

Som du kan se, gav formlen et resultat Antilope (Antilope), hvis hurtighed 61 miles i timen, selvom listen også omfatter Cheetah (Gepard) der løber med fart 70 miles i timen, og 70 er tættere på 69 end 61, er det ikke? Hvorfor sker dette? Fordi funktionen VPR når du søger efter et omtrentligt match, returnerer den største værdi, der ikke er større end den, der søges efter.

Jeg håber, at disse eksempler kaster lys over arbejdet med funktionen VPR i Excel, og man ser hende ikke længere som en outsider. Nu skader det ikke kort at gentage nøglepunkterne i det materiale, vi har studeret, for bedre at fiksere det i hukommelsen.

VLOOKUP i Excel – du skal huske dette!

Funktion VPR Excel kan ikke se til venstre. Den leder altid efter værdien i kolonnen længst til venstre i området givet af argumentet tabelmatrix (bord).
I funktion VPR alle værdier er store og små bogstaver, dvs. små og store bogstaver er ækvivalente.
Hvis den værdi, du leder efter, er mindre end minimumsværdien i den første kolonne i det område, der slås op, vil funktionen VPR vil rapportere en fejl #AT (#N/A).
Hvis 3. argument col_index_num (kolonne_nummer) mindre end 1funktion VPR vil rapportere en fejl #VÆRDI! (#VÆRDI!). Hvis det er større end antallet af kolonner i området tabelmatrix (tabel), vil funktionen rapportere en fejl #REF! (#LINK!).
Brug absolutte cellereferencer i argument tabelmatrix (tabel), så det korrekte søgeområde bevares ved kopiering af formlen. Prøv at bruge navngivne områder eller tabeller i Excel som et alternativ.
Når du laver en omtrentlig matchsøgning, skal du huske, at den første kolonne i det område, du leder efter, skal sorteres i stigende rækkefølge.
Husk endelig vigtigheden af det fjerde argument. Brug værdier SAND KODE (SANDHED) eller FALSK (FALSK) bevidst, og du vil slippe af med mange hovedpine.

I de følgende artikler i vores funktionsvejledning VPR i Excel vil vi lære mere avancerede eksempler, såsom at udføre forskellige beregninger vha VPR, udtrækning af værdier fra flere kolonner og mere. Tak fordi du læste denne tutorial, og jeg håber at se dig igen i næste uge!