VLOOKUP er en av Excels mest nyttige funksjoner, og det er også en av de minst forstått. I denne artikkelen demystiserer vi VLOOKUP ved hjelp av et virkelighetseksempel. Vi lager en brukbar faktura mal for et fiktivt selskap.

VLOOKUP er en Excel -funksjon . Denne artikkelen vil anta at leseren allerede har en bestått forståelse av Excel-funksjoner, og kan bruke grunnleggende funksjoner som SUM, AVERAGE og TODAY. I sin vanligste bruk er VLOOKUP en database -funksjon, noe som betyr at den fungerer med databasetabeller - eller mer enkelt lister av ting i et Excel-regneark. Hva slags ting? Vel, noen slags ting. Du kan ha et regneark som inneholder en liste over ansatte, eller produkter, eller kunder, eller CDer i CD-samlingen din eller stjerner i nattehimmelen. Det spiller ingen rolle.

Her er et eksempel på en liste eller database. I dette tilfellet er det en liste over produkter som vårt fiktive selskap selger:

Vanligvis lister som dette har en slags unik identifikator for hvert element i listen. I dette tilfellet er den unike identifikatoren i kolonnen "Elementkode". Merk: For at VLOOKUP-funksjonen skal fungere med en database / liste, må listen må ha en kolonne som inneholder den unike identifikatoren (eller "nøkkelen" eller "ID") og den kolonnen må være den første kolonnen i tabellen . Vår prøvedatabase over tilfredsstiller dette kriteriet.

Den vanskeligste delen av å bruke VLOOKUP er å forstå nøyaktig hva det er for. Så la oss se om vi kan få det klart først:

VLOOKUP henter informasjon fra en database / liste basert på en tilordnet forekomst av den unike identifikatoren.

I eksempelet ovenfor vil du sette VLOOKUP-funksjonen inn i et annet regneark med en varenummer, og det vil returnere til deg enten den tilsvarende varens beskrivelse, prisen eller tilgjengeligheten (dens antall i lager) som beskrevet i din opprinnelige liste. Hvilke av disse opplysninger vil det passere deg tilbake? Vel, du kommer til å bestemme dette når du oppretter formelen.

Hvis alt du trenger er en del informasjon fra databasen, ville det være mye trøbbel å gå til å konstruere en formel med en VLOOKUP-funksjon i den. Vanligvis vil du bruke denne typen funksjonalitet i et gjenbrukbart regneark, for eksempel en mal. Hver gang noen skriver inn en gyldig varenummer, vil systemet hente all nødvendig informasjon om det tilsvarende elementet.

La oss lage et eksempel på dette: En Faktura Mal som vi kan gjenbruke om og om igjen i vår fiktivt selskap.

Først begynner vi Excel, og vi lager oss en tom faktura:

Slik fungerer det: Personen som bruker faktura-malen, vil fylle ut en serie med varekoder i kolonne "A", og systemet vil hente hvert enkelt produkts beskrivelse og pris fra produktdatabasen. Denne informasjonen vil bli brukt til å beregne linjotall for hvert element (forutsatt at vi oppgir en gyldig mengde).

For å holde dette eksemplet enkelt, vil vi finne produktdatabasen på et eget ark i samme arbeidsbok:

I virkeligheten er det mer sannsynlig at produktdatabasen vil bli plassert i en egen arbeidsbok. Det gjør liten forskjell på VLOOKUP-funksjonen, noe som egentlig ikke bryr seg om databasen er plassert på samme ark, et annet ark eller en helt annen arbeidsbok.

Så har vi opprettet vår produktdatabase, som ser ut slik:

For å teste VLOOKUP-formelen som vi skal skrive, skriver vi først en gyldig elementkode i celle A11 av vår tomme faktura:

Deretter flytter vi den aktive cellen til cellen der Vi vil ha informasjon hentet fra databasen av VLOOKUP som skal lagres. Interessant nok er dette det skrittet at de fleste blir feil. Forklare videre: Vi skal lage en VLOOKUP-formel som vil hente beskrivelsen som tilsvarer varenummeret i celle A11. Hvor vil vi ha denne beskrivelsen satt når vi får det? I celle B11, selvfølgelig. Så det er der vi skriver VLOOKUP-formelen: i celle B11. Velg celle B11 nå.

Vi må finne listen over alle tilgjengelige funksjoner som Excel har å tilby, slik at vi kan velge VLOOKUP og få hjelp til å fullføre formelen. Dette finner du ved å først klikke på tabellen Formler , og deretter klikke Sett inn funksjon :

En boks vises som lar oss velge noen av funksjonene som er tilgjengelige i Excel.

For å finne den vi leter etter, kan vi skrive inn et søkeord som "oppslag" (fordi funksjonen vi er interessert i, er en oppslag -funksjon). Systemet vil gi oss en liste over alle oppslagrelaterte funksjoner i Excel. VLOOKUP er den andre i listen. Velg det et klikk OK .

Boksen Funksjonsargumenter vises, og ber oss om alle nødvendige argumenter (eller parametere ) som trengs for å fullføre VLOOKUP-funksjonen. Du kan tenke på denne boksen som funksjonen spør oss om følgende spørsmål:

1. Hvilken unik identifikator ser du opp i databasen?
2. Hvor er databasen?
3. Hvilket stykke informasjon fra databasen som er knyttet til

De første tre argumentene vises med fet skrift , og indikerer at de er obligatoriske argumenter (VLOOKUP-funksjonen er ufullstendig uten dem og vil ikke returnere en gyldig verdi). Det fjerde argumentet er ikke fet, noe som betyr at det er valgfritt:

Vi vil fullføre argumentene i rekkefølge, topp til bunn.

Argumentet vi må fullføre er Lookup_value . Funksjonen trenger oss til å fortelle det hvor du finner den unike identifikatoren ( elementskoden i dette tilfellet) at den skal returnere beskrivelsen av. Vi må velge elementkoden vi skrev inn tidligere (i A11).

Klikk på velgerikonet til høyre for det første argumentet:

Klikk deretter en gang på cellen som inneholder elementkode (A11), og trykk Enter :

Verdien av "A11" er satt inn i det første argumentet.

Nå må vi skrive inn en verdi for argumentet Table_array . Med andre ord, vi må fortelle VLOOKUP hvor du finner databasen / listen. Klikk på velgerikonet ved siden av det andre argumentet:

Finn nå databasen / listen og velg hele listen - , ikke inkludert topplinjen . I vårt eksempel er databasen plassert på et eget regneark, slik at vi først klikker på det regnearket kategorien:

Neste velger vi hele databasen, ikke inkludert topplinjen:

... og trykker Enter . Antall celler som representerer databasen (i dette tilfellet "Produktdatabase"! A2: D7 ") blir automatisk innført for oss i det andre argumentet.

Nå må vi skrive inn det tredje argumentet, Col_index_num . Vi bruker dette argumentet til å spesifisere til VLOOKUP hvilket stykke informasjon fra databasen, assosiert med vår varenummer i A11, vi ønsker å ha returnert til oss. I dette spesielle eksemplet ønsker vi at varen er beskrivelse returnert til oss. Hvis du ser på databladets regneark, vil du legge merke til at kolonnen "Beskrivelse" er kolonnen andre i databasen. Dette betyr at vi må legge inn en verdi av «2» i feltet Col_index_num :

Det er viktig å merke seg at vi ikke skriver inn en "2" her fordi kolonnen "Beskrivelse" er i kolonnen B på det regnearket. Hvis databasen skjedde med å starte i kolonne K i regnearket, ville vi fortsatt angi en "2" i dette feltet fordi kolonnen "Beskrivelse" er den andre kolonnen i sett av celler vi valgte når du angav " Table_array ".

Til slutt må vi bestemme om du skal legge inn en verdi i det endelige VLOOKUP-argumentet, Range_lookup . Dette argumentet krever enten en ekte eller feil verdi, eller den skal stå tom. Når du bruker VLOOKUP med databaser (som det er sant 90% av tiden), kan måten å bestemme hva som skal settes i dette argumentet tenkes på følgende måte:

Hvis den første kolonnen i databasen (kolonnen som inneholder den unike identifikatorer) er sortert alfabetisk / numerisk i stigende rekkefølge, så er det mulig å angi en verdi på sant i dette argumentet, eller la det være tomt.

Hvis den første kolonnen i databasen er ikke sortert, eller den er sortert i synkende rekkefølge, må du må angi en verdi på false i dette argumentet

Som den første kolonnen i databasen er ikke sortert, skriver vi inn false i dette argumentet:

Det er den! Vi har oppgitt all informasjonen som kreves for VLOOKUP, for å returnere verdien vi trenger. Klikk på knappen OK og merk at beskrivelsen som svarer til varenummeret "R99245" er riktig angitt i celle B11:

Formelen som ble opprettet for oss ser slik ut:

Hvis vi går inn en forskjellig elementskode i celle A11, begynner vi å se effekten av VLOOKUP-funksjonen: Beskrivelsescellen endres for å matche den nye elementskoden:

Vi kan utføre et lignende sett med trinn for å få varens pris returnerte til celle E11. Merk at den nye formelen må opprettes i celle E11. Resultatet ser slik ut:

... og formelen ser slik ut:

Merk at den eneste forskjellen mellom de to formlene er det tredje argumentet ( Col_index_num ), er endret fra en "2 "Til en" 3 "(fordi vi vil ha data hentet fra den tredje kolonnen i databasen).

Hvis vi bestemte oss for å kjøpe 2 av disse elementene, ville vi legge inn en" 2 "i celle D11. Vi vil da legge inn en enkel formel i celle F11 for å få linjene totalt:

= D11 * E11

... som ser slik ut ...

Fyll ut faktura-malen

Vi har lært mye om VLOOKUP så langt. Faktisk har vi lært alt vi skal lære i denne artikkelen. Det er viktig å merke seg at VLOOKUP kan brukes under andre omstendigheter foruten databaser. Dette er mindre vanlig, og kan dekkes i fremtidige How-To Geek-artikler.

Vår faktura mal er ikke fullført ennå. For å fullføre det, ville vi gjøre følgende:

1. Vi ville fjerne prøveobjektkoden fra celle A11 og "2" fra celle D11. Dette vil føre til at våre nyopprettede VLOOKUP-formler viser feilmeldinger:
   

   

   
   Vi kan rette opp dette ved dømmekraftig bruk av Excels IF () og ISBLANK () -funksjoner. Vi endrer vår 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 formlene i cellene B11, E11 og F11 ned til resten av fakturaens posterrader. Merk at hvis vi gjør dette, vil de resulterende formlene ikke lenger korrekt referere til databasetabellen. Vi kan fikse dette ved å endre cellehenvisninger for databasen til absolutte cellehenvisninger. Alternativt - og enda bedre - kan vi opprette et serienavn for hele produktdatabasen (for eksempel "Produkter"), og bruk dette serienavnet i stedet for cellehenvisninger. Formelen vil endres fra dette ... = IF (ISBLANK (A11), " VLOOKUP (A11, 'Produktdatabase'! A2: D7,2, FALSE)) ... til dette ... = IF (ISBLANK (A11), ", VLOOKUP (A11, Products, 2, FALSE)) ... og deretter kopier formlene ned til resten av fakturaelementrader. vil sannsynligvis "låse" cellene som inneholder våre formler (eller heller
3. låse opp de andre -cellene), og deretter beskytte regnearket for å sikre at våre nøye konstruerte formler ikke er ved et uhell overskrevet når noen kommer til å fylle ut fakturaen. Vi ville lagre filen som en
4. mal , slik at den kunne gjenbrukes av alle i vårt firma. Hvis vi følte oss

virkelig smart, vi ville lage en database med alle våre kunder i et annet regneark, og bruk deretter kunde-IDen som ble angitt i celle F5, for å automatisk fylle inn kundens navn og adresse i cellene B6, B7 og B8. Hvis du ønsker å øve med VLOOKUP, eller bare se vår resulterende Faktura T emplate, den kan lastes ned herfra.

OTT forklarer - hvorfor har Windows flere virus enn Linux og OS X?

Jeg skriver dette innlegget fordi jeg nylig overheard en samtale mellom to venner der man anbefalte at vennen bytter fra Windows til Mac eller Linux fordi de sistnevnte operativsystemene er virusbeskyttede og aldri får skadelig programvare eller rootkits. Uhhh, feil. Helt feil. Dessverre er det mange som tror på dette, og tror å kjøpe en Mac eller installere Ubuntu, vil beskytte dem på en eller annen måte.Det e

(How-to)

Slik bruker du Bluetooth-hodetelefoner og høyttalere med Apple Watch (for å lytte til musikk)

Apple Watch er fullpakket med funksjoner som ikke er umiddelbart synlige ved første øyekast. Musikkappen på klokken umiddelbart (og intuitivt) styrer musikkappen på iPhone, men den kan også stå som sin egen Bluetooth-aktiverte iPod-lignende enhet. Les videre som vi viser deg hvordan du kobler den til Bluetooth-hodetelefoner og høyttalere.

(how-to)