VLOOKUP-funktion i Excel – Nybörjarguide: Syntax och exempel

Innehåll

VLOOKUP-funktion i Excel – allmän beskrivning och syntax
- Syntax för VLOOKUP-funktionen
VLOOKUP Exempel
Exakt eller ungefärlig matchning i VLOOKUP-funktionen
- Exempel 1: Hitta en exakt matchning med VLOOKUP
- Exempel 2: Använd VLOOKUP för att hitta en ungefärlig matchning
VLOOKUP i Excel – du måste komma ihåg detta!

Idag startar vi en serie artiklar som beskriver en av de mest användbara funktionerna i Excel − VPR (VLOOKUP). Denna funktion är samtidigt en av de mest komplexa och minst förstådda.

I denna handledning på VPR Jag kommer att försöka lägga upp grunderna så enkelt som möjligt för att göra inlärningsprocessen så tydlig som möjligt för oerfarna användare. Dessutom kommer vi att studera flera exempel med Excel-formler som kommer att visa de vanligaste användningsfallen för funktionen VPR.

VLOOKUP-funktion i Excel – allmän beskrivning och syntax

Så vad är det VPR? Tja, först och främst är det en Excel-funktion. Vad hon gör? Den slår upp värdet du anger och returnerar motsvarande värde från den andra kolumnen. Tekniskt talat, VPR slår upp värdet i den första kolumnen i det givna intervallet och returnerar resultatet från en annan kolumn i samma rad.

I den vanligaste applikationen är funktionen VPR söker i databasen efter en given unik identifierare och extraherar viss information relaterad till den från databasen.

Första bokstaven i funktionsnamn VPR (VLOOKUP) betyder Вvertikal (Vvertikal). Genom det kan du skilja VPR från GPR (HLOOKUP), som söker efter ett värde i den översta raden i ett intervall − Гhorisontell (Hvågrätt).

Funktion VPR tillgänglig i Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP och Excel 2000.

Syntax för VLOOKUP-funktionen

Funktion VPR (VLOOKUP) har följande syntax:

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

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

Som du kan se, en funktion VPR i Microsoft Excel har 4 alternativ (eller argument). De tre första är obligatoriska, den sista är valfri.

letauppvärde (lookup_value) – Värdet att leta efter. Detta kan vara ett värde (nummer, datum, text) eller en cellreferens (som innehåller uppslagsvärdet), eller ett värde som returneras av någon annan Excel-funktion. Till exempel kommer den här formeln att leta efter värdet 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Om uppslagsvärdet är mindre än det minsta värdet i den första kolumnen i intervallet som slås upp, visas funktionen VPR kommer att rapportera ett fel #AT (#N/A).

Tabellmatris (tabell) – två eller flera kolumner med data. Kom ihåg funktionen VPR letar alltid efter värdet i den första kolumnen i intervallet som anges i argumentet Tabellmatris (tabell). Det visningsbara intervallet kan innehålla olika data, såsom text, datum, siffror, booleaner. Funktionen är skiftlägesokänslig, vilket innebär att versaler och gemener anses vara samma. Så vår formel kommer att leta efter värdet 40 i celler från A2 till A15, eftersom A är den första kolumnen i området A2:B15 som anges i argumentet Tabellmatris (tabell):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

kolumnindex (kolumnnummer) är numret på kolumnen i det givna intervallet från vilket värdet i den hittade raden kommer att returneras. Kolumnen längst till vänster i det givna intervallet är 1, är den andra kolumnen 2, är den tredje kolumnen 3 och så vidare. Nu kan du läsa hela formeln:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Formel söker värde 40 innom räckhåll A2: A15 och returnerar motsvarande värde från kolumn B (eftersom B är den andra kolumnen i intervallet A2:B15).

Om värdet av argumentet kolumnindex (kolumnnummer) mindre än 1sedan VPR kommer att rapportera ett fel #VÄRDE! (#VÄRDE!). Och om det är fler än antalet kolumner i intervallet Tabellmatris (tabell) returnerar funktionen ett fel #REF! (#LÄNK!).

range_lookup (range_lookup) – bestämmer vad du ska leta efter:
- exakt matchning måste argumentet vara lika FALSK (FALSK);
- ungefärlig matchning, argument är lika SANT KOD (TRUE) eller inte specificerat alls.
Denna parameter är valfri, men mycket viktig. Senare i denna handledning VPR Jag ska visa dig några exempel som förklarar hur man skriver formler för att hitta exakta och ungefärliga matchningar.

VLOOKUP Exempel

Jag hoppas funktionen VPR bli lite tydligare för dig. Låt oss nu titta på några användningsfall VPR i formler med riktiga data.

Hur man använder VLOOKUP för att söka i ett annat Excel-ark

I praktiken formler med en funktion VPR används sällan för att söka efter data på samma kalkylblad. Oftare än inte kommer du att slå upp och hämta motsvarande värden från ett annat ark.

För att kunna använda VPR, sök i ett annat Microsoft Excel-ark, Du måste i argumentet Tabellmatris (tabell) ange arknamnet med ett utropstecken följt av ett cellintervall. Till exempel visar följande formel att intervallet A2: B15 finns på ett blad som heter Sheet2.

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

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

Bladnamnet behöver naturligtvis inte anges manuellt. Börja bara skriva formeln, och när det kommer till argumentet Tabellmatris (tabell), växla till önskat ark och välj önskat cellområde med musen.

Formeln som visas i skärmdumpen nedan letar efter texten "Produkt 1" i kolumn A (det är den första kolumnen i intervallet A1:B2) på ett kalkylblad Priser.

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

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

Kom ihåg att när du söker efter ett textvärde måste du omge det inom citattecken (""), vilket vanligtvis görs i Excel-formler.

För argument Tabellmatris (tabell) är det önskvärt att alltid använda absoluta referenser (med $-tecknet). I det här fallet kommer sökintervallet att förbli oförändrat när formeln kopieras till andra celler.

Sök i en annan arbetsbok med VLOOKUP

Att fungera VPR arbetade mellan två Excel-arbetsböcker måste du ange arbetsbokens namn inom hakparenteser före arknamnet.

Till exempel nedan är en formel som letar efter värdet 40 på arket Sheet2 i boken Numbers.xlsx:

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

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

Här är det enklaste sättet att skapa en formel i Excel med VPRsom länkar till en annan arbetsbok:

Öppna båda böckerna. Detta krävs inte, men det är lättare att skapa en formel på detta sätt. Du vill inte ange namnet på arbetsboken manuellt, eller hur? Dessutom kommer det att skydda dig från oavsiktliga stavfel.
Börja skriva en funktion VPRoch när det kommer till argumentet Tabellmatris (tabell), växla till en annan arbetsbok och välj önskat sökintervall i den.

Skärmdumpen nedan visar formeln med sökningen inställd på ett intervall i arbetsboken Prislista.xlsx på arket Priser.

Funktion VPR fungerar även när du stänger den sökta arbetsboken och hela sökvägen till arbetsboksfilen visas i formelfältet, som visas nedan:

Om namnet på arbetsboken eller arket innehåller mellanslag, måste det omges av apostrof:

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

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

Hur man använder ett namngivet område eller tabell i formler med VLOOKUP

Om du planerar att använda samma sökintervall i flera funktioner VPR, kan du skapa ett namngivet område och ange dess namn i formeln som ett argument Tabellmatris (tabell).

För att skapa ett namngivet område, markera helt enkelt cellerna och ange ett lämpligt namn i fältet Förnamn, till vänster om formelfältet.

Nu kan du skriva ner följande formel för att hitta priset på en produkt Produkt 1:

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

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

De flesta intervallnamn fungerar för hela Excel-arbetsboken, så det finns inget behov av att ange arknamnet för argumentet Tabellmatris (tabell), även om formeln och sökintervallet finns på olika kalkylblad. Om de finns i olika arbetsböcker måste du före namnet på intervallet ange namnet på arbetsboken, till exempel så här:

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

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

Så formeln ser mycket tydligare ut, håller du med? Att använda namngivna intervall är också ett bra alternativ till absoluta referenser eftersom det namngivna intervallet inte ändras när du kopierar formeln till andra celler. Det betyder att du kan vara säker på att sökintervallet i formeln alltid kommer att förbli korrekt.

Om du konverterar ett cellintervall till ett fullfjädrat Excel-kalkylblad med kommandot Bord (Tabell) fliken Införande (Infoga), när du sedan väljer ett område med musen kommer Microsoft Excel automatiskt att lägga till kolumnnamnen (eller tabellnamnet om du markerar hela tabellen) till formeln.

Den färdiga formeln kommer att se ut ungefär så här:

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

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

Eller kanske till och med så här:

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

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

När du använder namngivna intervall kommer länkarna att peka på samma celler oavsett var du kopierar funktionen VPR i arbetsboken.

Använda jokertecken i VLOOKUP-formler

Som med många andra funktioner, VPR Du kan använda följande jokertecken:

Frågetecken (?) – ersätter varje enskilt tecken.
Asterisk (*) – ersätter alla teckensekvenser.

Använda jokertecken i funktioner VPR kan vara användbart i många fall, till exempel:

När du inte kommer ihåg exakt texten du behöver hitta.
När du vill hitta något ord som är en del av innehållet i en cell. Vet att VPR söker efter innehållet i cellen som helhet, som om alternativet är aktiverat Matcha hela cellinnehållet (Hela cellen) i standardsökningen i Excel.
När en cell innehåller extra mellanslag i början eller slutet av innehållet. I en sådan situation kan du racka på dina hjärnor under lång tid och försöka ta reda på varför formeln inte fungerar.

Exempel 1: Letar efter text som börjar eller slutar med vissa tecken

Låt oss säga att du vill söka efter en specifik kund i databasen som visas nedan. Du kommer inte ihåg hans efternamn, men du vet att det börjar med "ack". Här är en formel som kommer att göra jobbet bra:

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

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

Nu när du är säker på att du har hittat rätt namn kan du använda samma formel för att hitta det belopp som betalats av den här kunden. För att göra detta, ändra bara det tredje argumentet för funktionen VPR till önskat kolumnnummer. I vårt fall är detta kolumn C (3:e i intervallet):

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

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

Här är några fler exempel med jokertecken:

~ Hitta ett namn som slutar på "man":

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

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

~ Hitta ett namn som börjar med "annons" och slutar med "son":

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

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

~ Vi hittar förnamnet i listan, som består av 5 tecken:

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

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

Att fungera VPR med jokertecken fungerade korrekt, som det fjärde argumentet du alltid bör använda FALSK (FALSK). Om sökintervallet innehåller mer än ett värde som matchar söktermerna med jokertecken, kommer det första värdet som hittas att returneras.

Exempel 2: Kombinera jokertecken och cellreferenser i VLOOKUP-formler

Låt oss nu titta på ett lite mer komplext exempel på hur man söker med funktionen VPR efter värde i en cell. Föreställ dig att kolumn A är en lista över licensnycklar och kolumn B är en lista över namn som äger en licens. Dessutom har du en del (flera tecken) av någon sorts licensnyckel i cell C1, och du vill hitta namnet på ägaren.

Detta kan göras med följande formel:

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

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

Den här formeln slår upp värdet från cell C1 i det givna intervallet och returnerar motsvarande värde från kolumn B. Observera att i det första argumentet använder vi ett et-tecken (&) före och efter cellreferensen för att länka textsträngen.

Som du kan se i figuren nedan är funktionen VPR returnerar "Jeremy Hill" eftersom hans licensnyckel innehåller sekvensen av tecken från cell C1.

Observera att argumentet Tabellmatris (tabell) i skärmdumpen ovan innehåller namnet på tabellen (Tabell7) istället för att ange ett cellintervall. Detta är vad vi gjorde i föregående exempel.

Exakt eller ungefärlig matchning i VLOOKUP-funktionen

Och slutligen, låt oss titta närmare på det sista argumentet som anges för funktionen VPR - range_lookup (intervall_vy). Som nämndes i början av lektionen är detta argument mycket viktigt. Du kan få helt olika resultat i samma formel med dess värde SANT KOD (SANT) eller FALSK (FALSK).

Låt oss först ta reda på vad Microsoft Excel menar med exakta och ungefärliga matchningar.

Om argumentet range_lookup (range_lookup) är lika med FALSK (FALSK), formeln letar efter en exakt matchning, dvs exakt samma värde som anges i argumentet letauppvärde (uppslagsvärde). Om i den första kolumnen i intervallet table_array (tabell) stöter på två eller flera värden som matchar argumentet letauppvärde (search_value), då kommer den första att väljas. Om inga matchningar hittas kommer funktionen att rapportera ett fel #AT (#N/A). Till exempel kommer följande formel att rapportera ett fel #AT (#N/A) om det inte finns något värde i intervallet A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Om argumentet range_lookup (range_lookup) är lika med SANT KOD (TRUE), formeln letar efter en ungefärlig matchning. Mer exakt, först funktionen VPR letar efter en exakt matchning, och om ingen hittas väljer du en ungefärlig. En ungefärlig matchning är det största värdet som inte överstiger värdet som anges i argumentet. letauppvärde (uppslagsvärde).

Om argumentet range_lookup (range_lookup) är lika med SANT KOD (TRUE) eller inte specificerat, bör värdena i den första kolumnen i intervallet sorteras i stigande ordning, det vill säga från minsta till största. Annars funktionen VPR kan ge ett felaktigt resultat.

För att bättre förstå vikten av val SANT KOD (SANNING) eller FALSK (FALSKT), låt oss titta på några fler formler med funktionen VPR och titta på resultatet.

Exempel 1: Hitta en exakt matchning med VLOOKUP

Som du kommer ihåg, för att söka efter en exakt matchning, det fjärde argumentet för funktionen VPR borde ha betydelse FALSK (FALSK).

Låt oss gå tillbaka till tabellen från det allra första exemplet och ta reda på vilket djur som kan röra sig med en hastighet 50 Mil per timme. Jag tror att denna formel inte kommer att orsaka dig några svårigheter:

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

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

Observera att vårt sökintervall (kolumn A) innehåller två värden 50 – i celler A5 и A6. Formel returnerar värde från cell B5. Varför? För när man letar efter en exakt matchning, funktionen VPR använder det första hittade värdet som matchar det som söks efter.

Exempel 2: Använd VLOOKUP för att hitta en ungefärlig matchning

När du använder funktionen VPR att söka efter en ungefärlig matchning, dvs när argumentet range_lookup (range_lookup) är lika med SANT KOD (TRUE) eller utelämnat, det första du behöver göra är att sortera intervallet efter den första kolumnen i stigande ordning.

Detta är mycket viktigt eftersom funktionen VPR returnerar det näst största värdet efter det givna, och sedan stoppas sökningen. Om du slarvar med korrekt sortering kommer du att få mycket konstiga resultat eller ett felmeddelande. #AT (#N/A).

Nu kan du använda en av följande 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 ni ser vill jag ta reda på vilket av djuren som har närmast hastighet 69 Mil per timme. Och här är resultatet som funktionen returnerade till mig VPR:

Som du kan se gav formeln ett resultat Antilop (Antilop), vars hastighet 61 miles per timme, även om listan också innehåller Cheetah (Geparden) som springer i fart 70 miles per timme, och 70 är närmare 69 än 61, eller hur? Varför händer det här? Eftersom funktionen VPR när du söker efter en ungefärlig matchning, returnerar det största värdet som inte är större än det som söks efter.

Jag hoppas att dessa exempel kastar lite ljus över arbetet med funktionen VPR i Excel, och du ser inte längre på henne som en outsider. Nu skadar det inte att kort upprepa nyckelpunkterna i materialet vi har studerat för att bättre fixa det i minnet.

VLOOKUP i Excel – du måste komma ihåg detta!

Funktion VPR Excel kan inte se till vänster. Den letar alltid efter värdet i kolumnen längst till vänster i intervallet som ges av argumentet Tabellmatris (tabell).
I funktion VPR alla värden är skiftlägesokänsliga, dvs små och stora bokstäver är likvärdiga.
Om värdet du letar efter är mindre än minimivärdet i den första kolumnen i intervallet som slås upp, visas funktionen VPR kommer att rapportera ett fel #AT (#N/A).
Om 3:e argumentet kolumnindex (kolumnnummer) mindre än 1fungera VPR kommer att rapportera ett fel #VÄRDE! (#VÄRDE!). Om det är större än antalet kolumner i intervallet Tabellmatris (tabell), kommer funktionen att rapportera ett fel #REF! (#LÄNK!).
Använd absoluta cellreferenser i argument Tabellmatris (tabell) så att rätt sökintervall bevaras vid kopiering av formeln. Prova att använda namngivna intervall eller tabeller i Excel som ett alternativ.
När du gör en ungefärlig matchningssökning, kom ihåg att den första kolumnen i intervallet du letar efter måste sorteras i stigande ordning.
Slutligen, kom ihåg vikten av det fjärde argumentet. Använd värden SANT KOD (SANNING) eller FALSK (FALSKT) medvetet och du kommer att bli av med många huvudvärk.

I följande artiklar i vår funktionshandledning VPR i Excel kommer vi att lära oss mer avancerade exempel, som att utföra olika beräkningar med hjälp av VPR, extrahera värden från flera kolumner och mer. Tack för att du läser den här handledningen och jag hoppas att vi ses igen nästa vecka!