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 blot lister af ting i et Excel-regneark. Hvad slags ting? Nå, nogen 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 ligegyldigt.

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 hver vare 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 med den unikke identifikator (eller "nøgle" eller "ID") og skal denne kolonne 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 varekode, og det vil returnere til dig enten den tilsvarende varens beskrivelse, pris eller tilgængelighed (dens "i lager" mængde) som beskrevet i din oprindelige liste. Hvilke af disse oplysninger vil det give dig tilbage? Nå skal du bestemme dette, når du opretter formlen.

Hvis alt du har brug for er et stykke information fra databasen, ville det være meget umuligt at gå til at konstruere en formel med en VLOOKUP funktion i det. 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 række varekoder i kolonne "A" og systemet henter hver enkelt artikels beskrivelse 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).

Med henblik på at holde dette eksempel enkelt, finder vi produktdatabasen på et særskilt ark i den samme projektmappe:

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

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

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

Dernæst flytter vi 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 lokalisere 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 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 interesseret i, er en opslag> -funktion). Systemet ville returnere os en liste over alle opslagsrelaterede funktioner i Excel. VLOOKUP er den anden i listen. Vælg det et klik OK .

feltet Funktionsargumenter vises, og vi beder os om alle de nødvendige argumenter (eller parametre ) for at fuldføre VLOOKUP-funktionen. Du kan tænke på denne boks som funktionen og spørger os om 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 forbundet med De unikke identifikator, ønsker du at have hentet for dig?

De første tre argumenter er vist med fed skrift , der angiver, 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 orden, øverst til nederst.

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

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

Klik derefter en gang på cellen indeholdende varenummeret (A11), og tryk på Indtast :

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 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 - uden overskriftslinjen . I vores eksempel er databasen placeret på et særskilt regneark, så vi klikker først på fanebladet:

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

... og tryk på Enter . 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å varens beskrivelse returneret til os. Hvis du ser på databladets regneark, vil du bemærke, at kolonnen "Beskrivelse" er kolonnen anden i databasen. Dette betyder, at vi skal indtaste en værdi af "2" i feltet Col_index_num :

Det er vigtigt at bemærke, at vi ikke indtaster en "2" her, fordi kolonnen "Beskrivelse" er i kolonnen B på det pågældende regneark. Hvis databasen er begyndt i kolonne K i regnearket, vil vi stadig indtaste en "2" i dette felt, fordi kolonnen "Beskrivelse" er den anden kolonne i det sæt celler, vi valgte, når du angav " Table_array ".

Endelig skal vi beslutte, om du 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 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å følgende måde:

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

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

Da den første kolonne i vores database er ikke sorteret, indtaster vi false 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å knappen OK 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 går ind en anden varekode i celle A11, begynder vi at se effekten af ​​VLOOKUP-funktionen: Beskrivelsescellen ændres for at matche den nye elementkode:

Vi kan udføre et lignende sæt trin for at få varens pris returneres 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 ) er ændret fra en "2 "Til en" 3 "(fordi vi ønsker data hentet fra den tredje kolonne i databasen).

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

= D11 * E11

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

Fuldfør faktura skabelonen

Vi har lært meget om VLOOKUP indtil nu. 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 Excel's 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 cellehenvisningerne til databasen til absolut 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 derefter kopier formlerne ned til resten af ​​fakturaelementraderne. ville sandsynligvis "låse" de celler, der indeholder vores formler (eller rettere
3. låse op de andre celler) og derefter beskytte regnearket for at sikre, at vores omhyggeligt konstruerede formler ikke er ved et uheld overskrevet når nogen kommer til at udfylde fakturaen. Vi ville gemme filen som en
4. skabelon , så den kunne genbruges af alle i vores firma Hvis vi følte os

virkelig klogt, vi ville oprette en database over alle vores kunder i et andet regneark og derefter bruge det kunde-id, der blev indtastet i celle F5, for automatisk at udfylde kundens navn og adresse i cellerne B6, B7 og B8. Hvis du vil gerne øve med VLOOKUP, eller bare se vores resulterende Faktura T emplate, den kan downloades herfra.

Macs firewall er slukket som standard: Har du brug for at aktivere det?

Mac OS X leveres med en indbygget firewall, men den er ikke aktiveret som standard. Windows-firewall er blevet aktiveret som standard, siden orm som Blaster inficerede alle de sårbare Windows XP-systemer, så hvad giver? Mac'er til at inkludere en firewall, som du kan aktivere fra Sikkerhed og privatliv i Systemindstillinger.

(how-to)

Sådan ændrer du farve i en samtale i Android's Messenger-app

Folk elsker tilpasning, og hvis der er én ting, er Android virkelig god til, så er det. Og Googles Messenger er ingen undtagelse. Hver samtale har en bestemt farve, men du kan ændre farven på en samtale gennem menuen. RELATED: Sådan sikkerhedskopieres dine tekstbeskeder til din Gmail-konto Det første du skal gøre er at hop ind i den samtale, du vil ændre farven på.

(how-to)