Sådan bruges VLOOKUP i Excel

VLOOKUP er en af ​​Excels mest nyttige funktioner, og det er også en af ​​de mindst forstående. I denne artikel demystificerer vi VLOOKUP ved hjælp af et virkelige eksempel. Vi opretter en brugbar faktura skabelon for et fiktivt firma.

VLOOKUP er en Excel funktion .Denne artikel vil antage, at læseren allerede har en forbigående forståelse af Excel-funktioner, og kan bruge grundlæggende funktioner som SUM, GENNEMSÆT, og I DAG.I sin mest almindelige brug er VLOOKUP en -database -funktion, hvilket betyder, at den fungerer med databasetabeller - eller mere, lister af ting i et Excel-regneark. Hvad slags ting? Nå, enhver slags ting. Du kan have et regneark, der indeholder en liste over medarbejdere eller produkter eller kunder eller cd'er i din cd-samling eller stjerner i nattehimlen. Det er ikke rigtig noget.

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

Normalt lister som denne har en slags unik identifikator for hvert emne 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 indeholdende den unikke identifikator( eller "nøgle" eller "ID") og , at kolonnen skal være den første kolonne i tabellen.Vores prøve database 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 kan få det klart først:

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

I eksemplet ovenfor vil du indsætte VLOOKUP-funktionen i et andet regneark med en varenummer, og det vil returnere til dig enten den tilsvarende varens beskrivelse, pris eller tilgængelighed( dens "i lager" mængde) som beskrevet i dinoprindelige liste. Hvilke af disse oplysninger vil det give dig tilbage? Nå skal du bestemme dette, når du laver formlen.

Hvis alt du behøver er ét stykke information fra databasen, ville det være meget umuligt at gå til at konstruere en formel med en VLOOKUP-funktion i den. Typisk vil du bruge denne form for funktionalitet i et genbrugbart regneark, som f.eks. En skabelon. Hver gang en person indtaster en gyldig varekode, vil systemet hente alle de nødvendige oplysninger om den tilsvarende vare.

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

Først begynder vi Excel, og vi skaber os en tom faktura:

Sådan fungerer det: Den person, der bruger faktura-skabelonen, udfylder en serie af varekoder i kolonne "A", og systemet henter hvert elementbeskrivelse og pris fra vores produktdatabase. Disse oplysninger vil blive brugt til at beregne linjenummen for hver vare( forudsat at vi indtaster en gyldig mængde).

For at holde dette eksempel enkelt, vil vi finde produktdatabasen på et separat ark i den samme projektmappe:

I virkeligheden er det mere sandsynligt, at produktdatabasen vil blive placeret i en separat projektmappe. Det gør lidt forskel på VLOOKUP-funktionen, hvilket ikke er ligeglad, hvis databasen er placeret på det samme ark, et andet ark eller en helt anden projektmappe.

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

For at teste VLOOKUP-formlen, som vi skal skrive, indfører vi først en gyldig varenummer i celle A11 i vores tomme faktura:

Dernæstflyt den aktive celle til cellen, hvor vi ønsker information hentet fra databasen ved VLOOKUP, der skal gemmes. Interessant nok er dette det skridt, at de fleste mennesker bliver forkerte. At forklare yderligere: Vi er i færd med at oprette en VLOOKUP-formel, der henter beskrivelsen, der svarer til varekoden i celle A11.Hvor vil vi have denne beskrivelse sat, når vi får det? I celle B11, selvfølgelig. Så det er her, hvor vi skriver VLOOKUP-formlen: i celle B11.Vælg celle B11 nu.

Vi skal finde listen over alle tilgængelige funktioner, som Excel har at byde på, så vi kan vælge VLOOKUP og få hjælp ved at udfylde formlen. Dette findes ved først at klikke på Formulas -fanen, og derefter klikke på Indsæt Funktion :

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

For at finde den, vi leder efter, kunne vi skrive et søgeord som "opslag"( fordi den funktion, vi er interesserede i, er en lookup funktion).Systemet ville returnere os en liste over alle opsætningsrelaterede funktioner i Excel. VLOOKUP er den anden i listen. Vælg det et klik OK .

-funktionargumentet -boksen vises, og vi beder os om alle -argumenterne ( eller -parametre ), der er nødvendige for at fuldføre VLOOKUP-funktionen. Du kan tænke på denne boks som funktionen spørger os følgende spørgsmål:

1. Hvilken unik identifikator kigger du op i databasen?
2. Hvor er databasen?
3. Hvilket stykke information fra databasen, der er knyttet til den unikke identifikator, ønsker du at have hentet for dig?

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

Vi vil færdiggøre argumenterne i rækkefølge, top til bund.

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

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

Klik derefter en gang på cellen indeholdende elementkode( A11), og tryk Enter :

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

Nu skal vi indtaste en værdi for Table_array argumentet. Med andre ord skal vi fortælle VLOOKUP hvor man finder databasen / listen. Klik på vælgerikonet ved siden af ​​det andet argument:

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

Næste vælger vi hele databasen, ikke inklusive overskriftslinjen:

. .. og tryk Indtast .Den række celler, 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 angive til VLOOKUP, hvilket stykke information fra databasen, der er knyttet til vores varenummer i A11, vi ønsker at have vendt tilbage til os. I dette særlige eksempel ønsker vi at få artiklens beskrivelse returneret til os. Hvis du ser på databladets regneark, vil du bemærke, at kolonnen "Beskrivelse" er anden kolonne i databasen. Dette betyder, at vi skal indtaste en værdi på "2" i -boksen Col_index_num :

Det er vigtigt at bemærke, at vi ikke indtaster en "2" her, fordi kolonnen "Beskrivelse" findes i B kolonnen pådet regneark. Hvis databasen tilfældigvis begyndte i kolonne K i regnearket, ville vi stadig indtaste en "2" i dette felt, fordi kolonnen "Beskrivelse" er den anden kolonne i det sæt celler, vi valgte, da vi angav "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 -ægte eller -falsk -værdi, eller den skal stå tom. Når man bruger VLOOKUP med databaser( som det er sandt 90% af tiden), kan måden at bestemme, hvad man skal sætte ind i dette argument tænkes på 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, så er det muligt at indtaste en værdi af true i dette argument, eller lad det være tomt.

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

Som den første kolonne i vores database er ikke sorteret ,vi indtaster 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 varenummeret "R99245" er korrekt indtastet i celle B11:

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

Hvis vi indtaster en anden -varekode i cellenA11 vil vi begynde at se kraften i VLOOKUP-funktionen: Beskrivelsescellen ændres for at matche den nye elementkode:

Vi kan udføre et lignende sæt trin for at få varens -pris returneret til 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 ) ændret fra en "2" til en "3"( fordivi ønsker data hentet 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 * E11

. .. som ser sådan ud. ..

Afslutte faktura skabelonen

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

Vores faktura skabelon er endnu ikke færdig. For at fuldføre det, ville vi gøre følgende:

1. Vi ville fjerne prøveobjektkoden fra celle A11 og "2" fra celle D11.Dette vil medføre, at vores nyoprettede VLOOKUP-formler viser fejlmeddelelser:
   
   Vi kan afhjælpe dette ved dømmende brug af Excels IF() og ISBLANK() funktioner. Vi ændrer vores formel fra dette. .. = VLOOKUP( A11, 'Produktdatabase'! A2: D7,2, FALSE) . .. til dette. .. = IF( ISBLANK( A11), "" VLOOKUP( A11, 'Produktdatabase'! A2: D7,2, FALSE))
   
2. Vi ville kopiere formlerne i cellerne B11, E11 og F11 ned til resten af ​​fakturaens rader. Bemærk, at hvis vi gør dette, henviser de resulterende formler ikke længere korrekt til databasetabellen. Vi kunne rette dette ved at ændre cellehenvisninger for databasen til absolutte cellehenvisninger. Alternativt - og endnu bedre - kunne vi oprette et rækkevidde navn for hele produktdatabasen( f.eks. "Produkter") og bruge dette intervalnavn i stedet for cellehenvisningerne. Formlen vil ændre sig fra dette. .. = IF( ISBLANK( A11), "VLOOKUP( A11," Produktdatabase '! A2: D7,2, FALSE)) . .. til dette. .. = IF( ISBLANK( A11)"", VLOOKUP( A11, Products, 2, FALSE)) . .. og , så kopier formlerne ned til resten af ​​fakturaelementraderne.
3. Vi ville sandsynligvis "låse" de celler, der indeholder vores formler( eller snarere låse andre celler) og derefter beskytte regnearket for at sikre, at vores omhyggeligt konstruerede formler ikke ved et uheld overskrives, når nogen kommer til at udfyldei fakturaen.
4. Vi ville gemme filen som en skabelon , så den kunne genanvendes af alle i vores firma

Hvis vi følte virkelig klog, ville vi oprette en database over alle vores kunder i et andet regneark og derefter brugekunde-id indført i celle F5 for automatisk at udfylde kundens navn og adresse i cellerne B6, B7 og B8.

Hvis du gerne vil øve med VLOOKUP, eller bare se vores resulterende faktura skabelon, kan den downloades herfra.