Sådan bruges VLOOKUP i Excel

VLOOKUP er en af ​​Excels mest nyttige funktioner, og det er også en af ​​de mindst forståede. I denne artikel afmystificerer vi VLOOKUP ved hjælp af et virkeligt eksempel. Vi opretter en anvendelig fakturaskabelon til et fiktivt firma.

VLOOKUP er en Excel- funktion . Denne artikel antager, at læseren allerede har en gennemgående forståelse af Excel-funktioner og kan bruge grundlæggende funktioner som SUM, GEMIDDELIG og I DAG. I sin mest almindelige anvendelse er VLOOKUP en databasefunktion , hvilket betyder, at den fungerer med databasetabeller - eller mere enkelt, lister over ting i et Excel-regneark. Hvilke slags ting? Nå, enhver form for ting. Du har muligvis et regneark, der indeholder en liste over medarbejdere eller produkter eller kunder eller cd'er i din cd-samling eller stjerner på nattehimlen. Det betyder ikke rigtig noget.

Her er et eksempel på en liste eller en database. I dette tilfælde er det en liste over produkter, som vores fiktive firma sælger:

Normalt har lister som denne en eller anden unik identifikator for hvert element på listen. I dette tilfælde er den unikke identifikator i kolonnen "Varekode". Bemærk: For at VLOOKUP-funktionen skal fungere med en database / liste, skal listen have en kolonne, der indeholder den unikke identifikator (eller "nøgle" eller "ID"), og den kolonne skal være den første kolonne i tabellen . Vores eksempeldatabase ovenfor opfylder dette kriterium.

Den sværeste del af at bruge VLOOKUP er at forstå præcis, hvad det er til. Så lad os se, om vi først kan få det klart:

VLOOKUP henter oplysninger fra en database / liste baseret på en leveret forekomst af den unikke identifikator.

I eksemplet ovenfor indsætter du VLOOKUP-funktionen i et andet regneark med en varekode, og den returnerer enten den tilsvarende vares beskrivelse, pris eller tilgængelighed (dens "På lager" -mængde) som beskrevet i din original liste. Hvilke af disse oplysninger videregiver det dig? Nå, du skal beslutte dette, når du opretter formlen.

Hvis alt hvad du behøver er et stykke information fra databasen, ville det være meget besvær at gå til for at konstruere en formel med en VLOOKUP-funktion i den. Typisk bruger du denne form for funktionalitet i et genanvendeligt regneark, såsom en skabelon. Hver gang nogen indtaster en gyldig varekode, henter systemet alle de nødvendige oplysninger om den tilsvarende vare.

Lad os oprette et eksempel på dette: En fakturaskabelon, som vi kan genbruge igen og igen i vores fiktive firma.

Først starter vi Excel, og vi opretter selv en tom faktura:

Sådan fungerer det: Den person, der bruger fakturaskabelonen, udfylder en række varekoder i kolonne “A”, og systemet henter hver vares beskrivelse og pris fra vores produktdatabase. Disse oplysninger vil blive brugt til at beregne linjesummen for hver vare (forudsat at vi indtaster en gyldig mængde).

For at holde dette eksempel simpelt finder vi produktdatabasen på et separat ark i den samme projektmappe:

I virkeligheden er det mere sandsynligt, at produktdatabasen vil være placeret i en separat projektmappe. Det gør lille forskel for VLOOKUP-funktionen, som ikke er ligeglad med, om databasen er placeret på det samme ark, et andet ark eller en helt anden projektmappe.

Så vi har oprettet vores produktdatabase, der ser sådan ud:

For at teste den VLOOKUP-formel, vi er ved at skrive, indtaster vi først en gyldig varekode i celle A11 i vores tomme faktura:

Dernæst flytter vi den aktive celle til den celle, hvor vi ønsker, at oplysninger hentet fra databasen af ​​VLOOKUP skal gemmes. Interessant er dette det skridt, som de fleste mennesker tager fejl. For at forklare yderligere: Vi er ved at oprette en VLOOKUP-formel, der henter beskrivelsen, der svarer til varekoden i celle A11. Hvor vil vi have denne beskrivelse, når vi får den? I celle B11 naturligvis. Så det er her, vi skriver VLOOKUP-formlen: i celle B11. Vælg celle B11 nu.

Vi er nødt til at finde listen over alle tilgængelige funktioner, som Excel har at tilbyde, så vi kan vælge VLOOKUP og få hjælp til at udfylde formlen. Dette findes ved først at klikke på fanen Formler og derefter klikke på Indsæt funktion :

Der vises et felt, der giver os mulighed for at vælge en af ​​de tilgængelige funktioner i Excel.

For at finde den, vi leder efter, kunne vi skrive et søgeudtryk som "opslag" (fordi den funktion, vi er interesseret i, er en opslagsfunktion ). Systemet giver os en liste over alle opslagsrelaterede funktioner i Excel.  VLOOKUP er den anden på listen. Vælg det, og klik OK .

De Funktionsargumenter vises, hvilket fik os til alle de argumenter (eller parametre ) er nødvendige for at fuldføre LOPSLAG funktion. Du kan tænke på dette felt som funktionen, der stiller os følgende spørgsmål:

  1. Hvilket unikt id kigger du op i databasen?
  2. Hvor er databasen?
  3. Hvilket stykke information fra databasen, der er knyttet til den unikke identifikator, vil du have hentet til dig?

De første tre argumenter vises med fed skrift , hvilket indikerer, at de er obligatoriske argumenter (VLOOKUP-funktionen er ufuldstændig uden dem og returnerer ikke en gyldig værdi). Det fjerde argument er ikke fed, hvilket betyder, at det er valgfrit:

Vi udfylder argumenterne i rækkefølge, top til bund.

Det første argument, vi skal færdiggøre, er argumentet Lookup_value . Funktionen har brug for os til at fortælle den, hvor den unikke identifikator ( varekoden i dette tilfælde) skal findes, som den skal returnere beskrivelsen af. Vi skal vælge den varekode, vi indtastede tidligere (i A11).

Klik på vælgerikonet til højre for det første argument:

Klik derefter en gang på cellen, der indeholder varekoden (A11), og tryk på Enter :

Værdien af ​​“A11” indsættes i det første argument.

Nu skal vi indtaste en værdi for argumentet Table_array . Med andre ord er vi nødt til at fortælle VLOOKUP, hvor databasen / listen skal findes. Klik på vælgerikonet ved siden af ​​det andet argument:

Find nu databasen / listen, og vælg hele listen - undtagen overskriftslinjen. I vores eksempel er databasen placeret på et separat regneark, så vi klikker først på fanen regneark:

Dernæst vælger vi hele databasen, inklusive hovedlinjen:

... og tryk på Enter . Celleområdet, der repræsenterer databasen (i dette tilfælde "'Produktdatabase"! A2: D7 ") indtastes automatisk for os i det andet argument.

Nu skal vi indtaste det tredje argument, Col_index_num . Vi bruger dette argument til at specificere til VLOOKUP, hvilket stykke information fra databasen, der er knyttet til vores varekode i A11, som vi ønsker at have returneret til os. I dette særlige eksempel ønsker vi, at varens beskrivelse returneres til os. Hvis du ser på databasearket, vil du bemærke, at kolonnen "Beskrivelse" er den anden kolonne i databasen. Dette betyder, at vi skal indtaste en værdi på "2" i feltet Col_index_num :

Det er vigtigt at bemærke, at vi ikke indtaster en "2" her, fordi "Beskrivelse" kolonnen er i B- kolonnen på dette regneark. Hvis databasen tilfældigvis startede i kolonne K i regnearket, ville vi stadig indtaste et "2" i dette felt, fordi kolonnen "Beskrivelse" er den anden kolonne i det sæt af celler, vi valgte, når vi specificerede "Table_array".

Endelig skal vi beslutte, om vi skal indtaste en værdi i det endelige VLOOKUP-argument, Range_lookup . Dette argument kræver enten en sand eller falsk værdi, eller den skal være tom. Når du bruger VLOOKUP med databaser (som det er sandt 90% af tiden), kan man tænke på, hvordan man beslutter, hvad man skal lægge i dette argument, som følger:

Hvis den første kolonne i databasen (kolonnen, der indeholder de unikke identifikatorer) er sorteret alfabetisk / numerisk i stigende rækkefølge, er det muligt at indtaste en værdi af sand i dette argument eller lade det være tomt.

Hvis den første kolonne i databasen ikke sorteres, eller det er sorteret i faldende rækkefølge, så du skal indtaste en værdi på falsk i dette argument

Da den første kolonne i vores database ikke er sorteret, indtaster vi falsk i dette argument:

Det er det! Vi har indtastet alle de oplysninger, der kræves for VLOOKUP for at returnere den værdi, vi har brug for. Klik på OK- knappen og bemærk, at beskrivelsen, der svarer til varekoden “R99245”, er korrekt indtastet i celle B11:

Formlen, der blev oprettet for os, ser sådan ud:

Hvis vi indtaster en anden varekode i celle A11, begynder vi at se kraften i VLOOKUP-funktionen: Beskrivelsescellen ændres for at matche den nye varekode:

Vi kan udføre et lignende sæt trin for at få varens pris returneret i celle E11. Bemærk, at den nye formel skal oprettes i celle E11. Resultatet vil se sådan ud:

... og formlen vil se sådan ud:

Bemærk, at den eneste forskel mellem de to formler er det tredje argument ( Col_index_num ) er ændret fra et "2" til et "3" (fordi vi ønsker, at data skal hentes fra 3. kolonne i databasen).

Hvis vi besluttede at købe 2 af disse varer, ville vi indtaste en "2" i celle D11. Vi ville derefter indtaste en simpel formel i celle F11 for at få linjens samlede:

= D11 * E1

... der ser sådan ud ...

Udfyldelse af fakturaskabelonen

Vi har hidtil lært meget om VLOOKUP. Faktisk har vi lært alt, hvad vi vil lære i denne artikel. Det er vigtigt at bemærke, at VLOOKUP kan bruges under andre omstændigheder udover databaser. Dette er mindre almindeligt og kan blive dækket af fremtidige How-To Geek-artikler.

Vores fakturaskabelon er endnu ikke komplet. For at fuldføre det ville vi gøre følgende:

  1. Vi fjerner kodeeksemplet fra celle A11 og “2” fra celle D11. Dette får vores nyoprettede VLOOKUP-formler til at vise fejlmeddelelser:



    Vi kan afhjælpe dette ved omhyggelig brug af Excels funktioner IF () og ISBLANK () . Vi ændrer vores formel fra denne…      = VLOOKUP (A11, 'Produktdatabase'! A2: D7,2, FALSE) ... til denne… = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'Product Database'! A2) : D7,2, FALSE))


  2. Vi kopierede formlerne i cellerne B11, E11 og F11 ned til resten af ​​varelinjerne på fakturaen. Bemærk, at hvis vi gør dette, henviser de resulterende formler ikke længere korrekt til databasetabellen. Vi kunne løse dette ved at ændre cellereferencer til databasen til absolutte cellereferencer. Alternativt - og endnu bedre - kunne vi oprette et intervalnavn for hele produktdatabasen (f.eks. "Produkter") og bruge dette intervalnavn i stedet for cellereferencer. Formlen vil ændre sig fra denne…      = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'Produktdatabase'! A2: D7,2, FALSE)) ... til denne…       = IF (ISBLANK (A11), ”” , VLOOKUP (A11, Products, 2, FALSE)) ... og derefter kopier formlerne ned til resten af ​​fakturapostrækken.
  3. Vi vil sandsynligvis "låse" cellerne, der indeholder vores formler (eller rettere låse de andre celler op) og derefter beskytte regnearket for at sikre, at vores omhyggeligt konstruerede formler ikke ved et uheld overskrives, når nogen kommer til at udfylde fakturaen.
  4. Vi gemmer filen som en skabelon , så den kan genbruges af alle i vores virksomhed

Hvis vi følte os virkelig kloge, ville vi oprette en database med alle vores kunder i et andet regneark og derefter bruge kunde-id'et, der blev indtastet i celle F5, til automatisk at udfylde kundens navn og adresse i cellerne B6, B7 og B8.

Hvis du gerne vil øve med VLOOKUP eller blot se vores resulterende fakturaskabelon, kan den downloades herfra.