1Aug
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 ganske 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 VLOOKUP-funksjonen å arbeide med en database / liste, må listen
ha en kolonne som inneholder den unike identifikatoren( eller "nøkkel" eller "ID") og at 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å et levert eksempel på den unike identifikatoren.
I eksemplet 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, pris eller tilgjengelighet( den "i lager" -mengden) som beskrevet i dinopprinnelig 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 problemer å 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 går inn i 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årt fiktive 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 elementkoder i kolonne "A", og systemet henter hvert elementbeskrivelse og pris fra produktdatabasen vår. 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å, vi har opprettet vår produktdatabase som ser slik ut:
For å teste VLOOKUP-formelen skal vi først skrive inn en gyldig varenummer i celle A11 i vår tomme faktura:
Neste, viflytt den aktive cellen til cellen der vi vil ha informasjon hentet fra databasen ved 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å litt hjelp til å fullføre formelen. Dette er funnet ved å først klikke på Formulas -fanen, og deretter klikke Sett inn funksjon :
En boks vises som tillater 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 .
-funksjonargumentet -boksen vises, og ber om at alle -argumentene ( eller -parametere ) trengs for å fullføre VLOOKUP-funksjonen. Du kan tenke på denne boksen som funksjonen spør oss følgende spørsmål:
- Hvilken unik identifikator ser du opp i databasen?
- Hvor er databasen?
- Hvilket stykke informasjon fra databasen, knyttet til den unike identifikatoren, ønsker du å ha hentet for deg?
De tre første argumentene vises i fet skrift , noe som 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.
Det første argumentet vi må fullføre er Lookup_value -argumentet. 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 varekoden 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" settes 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, så vi klikker først på det regnearket kategorien:
Deretter velger vi hele databasen, ikke inkludert topplinjen:
. .. og trykk Enter .Utvalget av celler som representerer databasen( i dette tilfellet "Produktdatabase"! A2: D7 ") blir automatisk skrevet inn 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 beskrivelse returneres til oss. Hvis du ser på databladets regneark, vil du legge merke til at kolonnen "Beskrivelse" er andre kolonne i databasen. Dette betyr at vi må legge inn en verdi på "2" i -boksen Col_index_num :
Det er viktig å merke seg at vi ikke skriver inn en "2" her fordi kolonnen "Beskrivelse" er i B kolonnen pådet regnearket. Hvis databasen skjedde for å 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 -sann eller -falsk -verdi, eller den skal stå tom. Når du bruker VLOOKUP med databaser( som er sant 90% av tiden), kan måten å bestemme hva du skal sette inn i dette argumentet tenkes på følgende måte:
Hvis den første kolonnen i databasen( kolonnen som inneholder de unike identifikatorene)er sortert alfabetisk / numerisk i stigende rekkefølge, så er det mulig å legge inn en verdi av true 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, så må angi en verdi av falsk i dette argumentet
Som den første kolonnen i databasen er ikke sortert,vi angir falsk i dette argumentet:
Det er det! Vi har oppgitt all informasjonen som kreves for VLOOKUP, for å returnere verdien vi trenger. Klikk på OK -knappen og merk at beskrivelsen som samsvarer med varenummeret "R99245" er riktig angitt i celle B11:
Formelen som ble opprettet for oss, ser slik ut:
Hvis vi skriver inn en annen -elementskode i cellenA11, vil vi begynne å se kraften til VLOOKUP-funksjonen: Beskrivelsescellen endres for å matche den nye elementskoden:
Vi kan utføre et lignende sett med trinn for å få elementets -pris tilbake til cellen E11.Merk at den nye formelen må opprettes i celle E11.Resultatet vil se slik ut:
. .. og formelen ser slik ut:
Vær oppmerksom på at den eneste forskjellen mellom de to formlene er det tredje argumentet( Col_index_num ), er endret fra en "2" til en "3"( fordivi 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 ville da skrive inn en enkel formel i celle F11 for å få linjene totalt:
= D11 * E11
. .. som ser slik ut. ..
Fjerning av fakturaen
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:
- 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ømmende 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))
- Vi ville kopiere formlene i cellene B11, E11 og F11 ned til resten av fakturaens rader. Merk at hvis vi gjør dette, vil de resulterende formlene ikke lenger korrekt referere til databasetabellen. Vi kunne fikse dette ved å endre cellehenvisninger for databasen til absolutte cellehenvisninger. Alternativt - og enda bedre - kunne 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 , så kopier formlene ned til resten av fakturaelementrader.
- Vi vil sannsynligvis "låse" cellene som inneholder våre formler( eller heller låser andre celler) og beskytter deretter regnearket for å sikre at våre nøye konstruerte formler ikke overskrides ved et uhell når noen kommer til å fyllepå fakturaen.
- Vi ville lagre filen som en -mal , slik at den kunne gjenbrukes av alle i vårt firma
Hvis vi følte virkelig smart, ville vi lage en database av alle våre kunder i et annet regneark, og bruk deretterkunde-ID angitt i celle F5 for automatisk å fylle inn kundens navn og adresse i celler B6, B7 og B8.
Hvis du vil øve med VLOOKUP, eller bare se vår resulterende faktura mal, kan den lastes ned herfra.