VLOOKUP-funksjon i Excel – Nybegynnerveiledning: Syntaks og eksempler

I dag starter vi en serie artikler som beskriver en av de mest nyttige funksjonene i Excel − VPR (VLOOKUP). Denne funksjonen er samtidig en av de mest komplekse og minst forståtte.

I denne opplæringen om VPR Jeg vil prøve å legge det grunnleggende så enkelt som mulig for å gjøre læringsprosessen så oversiktlig som mulig for uerfarne brukere. I tillegg skal vi studere flere eksempler med Excel-formler som vil demonstrere de vanligste brukstilfellene for funksjonen VPR.

  • Generell beskrivelse og syntaks
  • VLOOKUP eksempler
    • Hvordan bruke VLOOKUP for å søke i et annet Excel-ark
    • Søk i en annen arbeidsbok med VLOOKUP
    • Hvordan bruke et navngitt område eller tabell i formler med VLOOKUP
    • Bruke jokertegn i VLOOKUP-formler
  • Nøyaktig eller omtrentlig samsvar i VLOOKUP-funksjonen
  • VLOOKUP i Excel – du må huske dette!

VLOOKUP-funksjon i Excel – generell beskrivelse og syntaks

Så hva er det VPR? Vel, først og fremst er det en Excel-funksjon. Hva gjør hun? Den slår opp verdien du spesifiserer og returnerer den tilsvarende verdien fra den andre kolonnen. Teknisk sett, VPR slår opp verdien i den første kolonnen i det gitte området og returnerer resultatet fra en annen kolonne i samme rad.

I den vanligste applikasjonen, funksjonen VPR søker i databasen etter en gitt unik identifikator og trekker ut noe informasjon relatert til den fra databasen.

Første bokstav i funksjonsnavn VPR (VLOOKUP) betyr Вvertikal (Vvertikal). Ved det kan du skille VPR fra GPR (HLOOKUP), som søker etter en verdi i den øverste raden i et område − Гhorisontal (Hhorisontalt).

Funksjon VPR tilgjengelig i Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP og Excel 2000.

Syntaks for VLOOKUP-funksjonen

Funksjon VPR (VLOOKUP) har følgende syntaks:

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

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

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

  • søkeverdi (lookup_value) – Verdien du skal se etter. Dette kan være en verdi (tall, dato, tekst) eller en cellereferanse (som inneholder oppslagsverdien), eller en verdi som returneres av en annen Excel-funksjon. For eksempel vil denne formelen se etter verdien 40:

    =VLOOKUP(40,A2:B15,2)

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

Hvis oppslagsverdien er mindre enn den minste verdien i den første kolonnen i området som slås opp, vil funksjonen VPR vil rapportere en feil #AT (#N/A).

  • tabell (tabell) – to eller flere kolonner med data. Husk funksjonen VPR ser alltid etter verdien i den første kolonnen i området gitt i argumentet tabell (bord). Det synlige området kan inneholde ulike data, for eksempel tekst, datoer, tall, booleaner. Funksjonen skiller ikke mellom store og små bokstaver, noe som betyr at store og små bokstaver anses som de samme. Så formelen vår vil se etter verdien 40 i celler fra A2 til A15, fordi A er den første kolonnen i området A2:B15 gitt i argumentet tabell (bord):

    =VLOOKUP(40,A2:B15,2)

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

  • col_index_num (column_number) er nummeret på kolonnen i det gitte området som verdien i den funnet raden vil bli returnert fra. Kolonnen lengst til venstre i det gitte området er 1, er den andre kolonnen 2, er den tredje kolonnen 3 og så videre. Nå kan du lese hele formelen:

    =VLOOKUP(40,A2:B15,2)

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

    Formel på jakt etter verdi 40 i området A2: A15 og returnerer den tilsvarende verdien fra kolonne B (fordi B er den andre kolonnen i området A2:B15).

Hvis verdien av argumentet col_index_num (kolonne_nummer) mindre enn 1deretter VPR vil rapportere en feil #VERDI! (#VERDI!). Og hvis det er mer enn antall kolonner i området tabell (tabell), vil funksjonen returnere en feil #REF! (#LINK!).

  • range_lookup (range_lookup) – bestemmer hva du skal se etter:
    • eksakt samsvar, argumentet må være likt FALSK (FALSK);
    • omtrentlig samsvar, argument er lik SANN KODE (TRUE) eller ikke spesifisert i det hele tatt.

    Denne parameteren er valgfri, men veldig viktig. Senere i denne opplæringen VPR Jeg skal vise deg noen eksempler som forklarer hvordan du skriver formler for å finne eksakte og omtrentlige treff.

VLOOKUP eksempler

Jeg håper funksjonen VPR bli litt klarere for deg. La oss nå se på noen brukstilfeller VPR i formler med reelle data.

Hvordan bruke VLOOKUP for å søke i et annet Excel-ark

I praksis formler med en funksjon VPR brukes sjelden til å søke etter data på samme regneark. Oftere enn ikke vil du slå opp og hente tilsvarende verdier fra et annet ark.

For å kunne bruke VPR, søk i et annet Microsoft Excel-ark, Du må i argumentet tabell (tabell) spesifiser arknavnet med et utropstegn etterfulgt av et celleområde. For eksempel viser følgende formel at området A2: B15 er på et ark som heter Sheet2.

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

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

Arknavnet må selvfølgelig ikke angis manuelt. Bare begynn å skrive formelen, og når det kommer til argumentet tabell (tabell), bytt til ønsket ark og velg ønsket celleområde med musen.

Formelen vist i skjermbildet nedenfor ser etter teksten "Produkt 1" i kolonne A (det er den første kolonnen i området A1:B2) på et regneark Prisene.

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

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

Husk at når du søker etter en tekstverdi, må du sette den i anførselstegn (""), slik det vanligvis gjøres i Excel-formler.

For argumentasjon tabell (tabell) det er ønskelig å alltid bruke absolutte referanser (med $-tegnet). I dette tilfellet vil søkeområdet forbli uendret når formelen kopieres til andre celler.

Søk i en annen arbeidsbok med VLOOKUP

Å fungere VPR jobbet mellom to Excel-arbeidsbøker, må du spesifisere arbeidsboknavnet i hakeparentes før arknavnet.

Nedenfor er for eksempel en formel som ser etter verdien 40 på arket Sheet2 i boken Numbers.xlsx:

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

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

Her er den enkleste måten å lage en formel i Excel med VPRsom lenker til en annen arbeidsbok:

  1. Åpne begge bøkene. Dette er ikke nødvendig, men det er lettere å lage en formel på denne måten. Du vil vel ikke skrive inn arbeidsboknavnet manuelt? I tillegg vil det beskytte deg mot tilfeldige skrivefeil.
  2. Begynn å skrive en funksjon VPRog når det kommer til argumentasjonen tabell (tabell), bytt til en annen arbeidsbok og velg ønsket søkeområde i den.

Skjermbildet nedenfor viser formelen med søket satt til et område i arbeidsboken Prisliste.xlsx på arket Prisene.

Funksjon VPR vil fungere selv når du lukker den søkte arbeidsboken og hele banen til arbeidsbokfilen vises i formellinjen, som vist nedenfor:

Hvis navnet på arbeidsboken eller arket inneholder mellomrom, må det være omsluttet av apostrof:

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

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

Hvordan bruke et navngitt område eller tabell i formler med VLOOKUP

Hvis du planlegger å bruke samme søkeområde i flere funksjoner VPR, kan du opprette et navngitt område og skrive inn navnet i formelen som et argument tabell (bord).

For å lage et navngitt område, velg bare cellene og skriv inn et passende navn i feltet Fornavn, til venstre for formellinjen.

Nå kan du skrive ned følgende formel for å finne prisen på et produkt Produkt 1:

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

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

De fleste områdenavn fungerer for hele Excel-arbeidsboken, så det er ikke nødvendig å spesifisere arknavnet for argumentet tabell (tabell), selv om formelen og søkeområdet er på forskjellige regneark. Hvis de er i forskjellige arbeidsbøker, må du før navnet på området spesifisere navnet på arbeidsboken, for eksempel slik:

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

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

Så formelen ser mye klarere ut, enig? Bruk av navngitte områder er også et godt alternativ til absolutte referanser fordi det navngitte området ikke endres når du kopierer formelen til andre celler. Dette betyr at du kan være sikker på at søkeområdet i formelen alltid vil forbli riktig.

Hvis du konverterer en rekke celler til et fullverdig Excel-regneark ved hjelp av kommandoen Bord (Tabell)-fanen Innsetting (Sett inn), så når du velger et område med musen, vil Microsoft Excel automatisk legge til kolonnenavnene (eller tabellnavnet hvis du velger hele tabellen) til formelen.

Den ferdige formelen vil se omtrent slik ut:

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

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

Eller kanskje til og med slik:

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

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

Når du bruker navngitte områder, vil koblingene peke til de samme cellene uansett hvor du kopierer funksjonen VPR i arbeidsboka.

Bruke jokertegn i VLOOKUP-formler

Som med mange andre funksjoner, VPR Du kan bruke følgende jokertegn:

  • Spørsmålstegn (?) – erstatter et enkelt tegn.
  • Stjerne (*) – erstatter enhver sekvens av tegn.

Bruke jokertegn i funksjoner VPR kan være nyttig i mange tilfeller, for eksempel:

  • Når du ikke husker nøyaktig teksten du trenger å finne.
  • Når du vil finne et ord som er en del av innholdet i en celle. Vet at VPR søker etter innholdet i cellen som helhet, som om alternativet er aktivert Match hele celleinnholdet (Hele cellen) i standard Excel-søk.
  • Når en celle inneholder ekstra mellomrom på begynnelsen eller slutten av innholdet. I en slik situasjon kan du racke hjernen din i lang tid og prøve å finne ut hvorfor formelen ikke fungerer.

Eksempel 1: Leter etter tekst som starter eller slutter med bestemte tegn

La oss si at du vil søke etter en bestemt kunde i databasen vist nedenfor. Du husker ikke etternavnet hans, men du vet at det begynner med "ack". Her er en formel som vil gjøre jobben helt fint:

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

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

Nå som du er sikker på at du har funnet det riktige navnet, kan du bruke samme formel for å finne beløpet betalt av denne kunden. For å gjøre dette, endre bare det tredje argumentet til funksjonen VPR til ønsket kolonnenummer. I vårt tilfelle er dette kolonne C (3. i serien):

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

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

Her er noen flere eksempler med jokertegn:

~ Finn et navn som slutter på «mann»:

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

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

~ Finn et navn som begynner med «annonse» og slutter med «sønn»:

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

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

~ Vi finner fornavnet i listen, som består av 5 tegn:

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

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

Å fungere VPR med jokertegn fungerte riktig, som det fjerde argumentet du alltid bør bruke FALSK (FALSK). Hvis søkeområdet inneholder mer enn én verdi som samsvarer med søkeordene med jokertegn, vil den første verdien som ble funnet, returneres.

Eksempel 2: Kombiner jokertegn og cellereferanser i VLOOKUP-formler

La oss nå se på et litt mer komplekst eksempel på hvordan du søker ved hjelp av funksjonen VPR etter verdi i en celle. Tenk deg at kolonne A er en liste over lisensnøkler, og kolonne B er en liste over navn som eier en lisens. I tillegg har du en del (flere tegn) av en slags lisensnøkkel i celle C1, og du vil finne navnet på eieren.

Dette kan gjøres ved å bruke følgende formel:

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

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

Denne formelen slår opp verdien fra celle C1 i det gitte området og returnerer den tilsvarende verdien fra kolonne B. Merk at i det første argumentet bruker vi et og-tegn (&) før og etter cellereferansen for å koble tekststrengen.

Som du kan se i figuren nedenfor, funksjonen VPR returnerer "Jeremy Hill" fordi lisensnøkkelen hans inneholder sekvensen av tegn fra celle C1.

Merk at argumentet tabell (tabell) i skjermbildet ovenfor inneholder navnet på tabellen (Tabell7) i stedet for å spesifisere et celleområde. Dette er hva vi gjorde i forrige eksempel.

Nøyaktig eller omtrentlig samsvar i VLOOKUP-funksjonen

Og til slutt, la oss se nærmere på det siste argumentet som er spesifisert for funksjonen VPR - range_lookup (intervall_visning). Som nevnt i begynnelsen av leksjonen er dette argumentet svært viktig. Du kan få helt forskjellige resultater i samme formel med verdien SANN KODE (SANN) eller FALSK (FALSK).

La oss først finne ut hva Microsoft Excel betyr med nøyaktige og omtrentlige treff.

  • Hvis argumentet range_lookup (range_lookup) er lik FALSK (FALSK), formelen ser etter en eksakt match, dvs. nøyaktig samme verdi som gitt i argumentet søkeverdi (oppslagsverdi). Hvis i den første kolonnen i området tstand_array (tabell) møter to eller flere verdier som samsvarer med argumentet søkeverdi (søkeverdi), så vil den første bli valgt. Hvis ingen treff blir funnet, vil funksjonen rapportere en feil #AT (#N/A). For eksempel vil følgende formel rapportere en feil #AT (#N/A) hvis det ikke er noen verdi i området A2:A15 4:

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

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

  • Hvis argumentet range_lookup (range_lookup) er lik SANN KODE (TRUE), formelen ser etter en omtrentlig match. Mer presist, først funksjonen VPR ser etter en eksakt match, og hvis ingen blir funnet, velger du en omtrentlig. Et omtrentlig samsvar er den største verdien som ikke overskrider verdien angitt i argumentet. søkeverdi (oppslagsverdi).

Hvis argumentet range_lookup (range_lookup) er lik SANN KODE (TRUE) eller ikke spesifisert, bør verdiene i den første kolonnen i området sorteres i stigende rekkefølge, det vil si fra minste til største. Ellers funksjonen VPR kan gi et feilaktig resultat.

For bedre å forstå viktigheten av valg SANN KODE (SANNHET) eller FALSK (USANN), la oss se på noen flere formler med funksjonen VPR og se på resultatene.

Eksempel 1: Finne en eksakt match med VLOOKUP

Som du husker, for å søke etter et eksakt samsvar, det fjerde argumentet til funksjonen VPR bør ha betydning FALSK (FALSK).

La oss gå tilbake til tabellen fra det aller første eksemplet og finne ut hvilket dyr som kan bevege seg med en hastighet 50 miles per time. Jeg tror at denne formelen ikke vil forårsake noen problemer:

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

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

Merk at søkeområdet vårt (kolonne A) inneholder to verdier 50 – i celler A5 и A6. Formel returnerer verdi fra celle B5. Hvorfor? Fordi når du leter etter en eksakt match, funksjonen VPR bruker den første verdien som samsvarer med den det søkes etter.

Eksempel 2: Bruke VLOOKUP for å finne en omtrentlig match

Når du bruker funksjonen VPR å søke etter et omtrentlig samsvar, dvs. når argumentet range_lookup (range_lookup) er lik SANN KODE (TRUE) eller utelatt, det første du må gjøre er å sortere området etter den første kolonnen i stigende rekkefølge.

Dette er veldig viktig fordi funksjonen VPR returnerer den nest største verdien etter den gitte, og deretter stopper søket. Forsømmer du riktig sortering vil du ende opp med veldig merkelige resultater eller en feilmelding. #AT (#N/A).

Nå kan du bruke en av 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 dere ser ønsker jeg å finne ut hvilket av dyrene som har nærmest hastighet 69 miles per time. Og her er resultatet funksjonen returnerte til meg VPR:

Som du kan se, returnerte formelen et resultat Antilope (Antelope), hvis hastighet 61 miles per time, selv om listen også inkluderer Cheetah (Geparden) som løper i fart 70 miles per time, og 70 er nærmere 69 enn 61, er det ikke? Hvorfor skjer dette? Fordi funksjonen VPR når du søker etter et omtrentlig samsvar, returnerer den største verdien som ikke er større enn den det søkes etter.

Jeg håper disse eksemplene kaster litt lys over arbeidet med funksjonen VPR i Excel, og du ser ikke lenger på henne som en outsider. Nå skader det ikke å kort gjenta nøkkelpunktene i materialet vi har studert for bedre å fikse det i minnet.

VLOOKUP i Excel – du må huske dette!

  1. Funksjon VPR Excel kan ikke se til venstre. Den ser alltid etter verdien i kolonnen lengst til venstre i området gitt av argumentet tabell (bord).
  2. I funksjon VPR alle verdier skiller mellom store og små bokstaver, dvs. små og store bokstaver er likeverdige.
  3. Hvis verdien du leter etter er mindre enn minimumsverdien i den første kolonnen i området som slås opp, vil funksjonen VPR vil rapportere en feil #AT (#N/A).
  4. Hvis 3. argument col_index_num (kolonne_nummer) mindre enn 1funksjon VPR vil rapportere en feil #VERDI! (#VERDI!). Hvis det er større enn antall kolonner i området tabell (tabell), vil funksjonen rapportere en feil #REF! (#LINK!).
  5. Bruk absolutte cellereferanser i argument tabell (tabell) slik at riktig søkeområde bevares ved kopiering av formelen. Prøv å bruke navngitte områder eller tabeller i Excel som et alternativ.
  6. Når du gjør et omtrentlig samsvarssøk, husk at den første kolonnen i området du leter etter må sorteres i stigende rekkefølge.
  7. Husk til slutt viktigheten av det fjerde argumentet. Bruk verdier SANN KODE (SANNHET) eller FALSK (FALSK) bevisst og du vil bli kvitt mange hodepine.

I de følgende artiklene i funksjonsopplæringen vår VPR i Excel vil vi lære mer avanserte eksempler, som å utføre ulike beregninger vha VPR, trekke ut verdier fra flere kolonner og mer. Takk for at du leste denne opplæringen, og jeg håper å se deg igjen neste uke!

Legg igjen en kommentar