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 a database funksjon, noe som betyr at den fungerer med databasetabeller - eller mer enkelt, lister av ting i et Excel-regneark. Hva slags ting? Vi vil, 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 "tasten" 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 retrieves information from a database/list based on a supplied instance of the unique identifier.
I eksemplet ovenfor vil du sette inn VLOOKUP-funksjonen 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 originalen 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 med å 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: An Faktura mal at vi kan gjenbruke om og om igjen i vårt fiktive selskap.
Først begynner vi Excel, og vi lager oss en tom faktura:
For å holde dette eksemplet enkelt, vil vi finne produktdatabasen på et eget ark i samme arbeidsbok:
Så, vi har opprettet vår produktdatabase, som ser slik ut:
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 er funnet ved først å klikke på formler og deretter klikke Sett inn funksjon:
For å finne den vi leter etter, kan vi skrive inn et søkeord som "oppslag" (fordi funksjonen vi er interessert i, er en se opp funksjon). Systemet vil gi oss en liste over alle oppslagrelaterte funksjoner i Excel. VLOOKUP er den andre i listen. Velg det et klikk OK.
De Funksjonsargumenter boksen vises, og ber oss om alle argumenter (eller parametere) som 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 med fet skrift, som indikerer at de er påbudt, bindende 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:
Det første argumentet vi må fullføre er søkeverdi argument. Funksjonen trenger oss til å fortelle det hvor du skal finne den unike identifikatoren (the Varenummer i dette tilfellet) at det 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:
Deretter klikker du en gang på cellen som inneholder varenummeret (A11), og trykker på Tast inn:
Nå må vi skrive inn en verdi for tabell argument. Med andre ord, vi må fortelle VLOOKUP hvor du finner databasen / listen. Klikk på velgerikonet ved siden av det andre argumentet:
… og trykk Tast inn. 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 gå inn i 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 eksempelet ønsker vi å ha varen beskrivelse returnert til oss. Hvis du ser på databladets regneark, vil du legge merke til at kolonnen "Beskrivelse" er sekund kolonne i databasen. Dette betyr at vi må legge inn en verdi på "2" i Col_index_num eske:
Det er viktig å merke seg at vi ikke skriver inn en "2" her fordi "Beskrivelse" kolonnen er i B kolonne på det regnearket. Hvis databasen skjedde å starte i kolonne K av regnearket, ville vi fortsatt angi en "2" i dette feltet fordi "Beskrivelse" -kolonnen 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 a ekte eller falsk verdi, eller det skal stå tomt. 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 bli tenkt på som følger:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Som den første kolonnen i vår database er ikke sortert, vi skriver inn falsk inn 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 legg merke til at beskrivelsen som svarer til varenummer "R99245" er riktig angitt i celle B11:
Hvis vi skriver inn en forskjellig varekoden i celle A11, begynner vi å se kraften i VLOOKUP-funksjonen: Beskrivelsescellen endres for å matche den nye elementskoden:
Vi kan utføre et lignende sett med trinn for å få varen pris returnert til celle E11. Merk at den nye formelen må opprettes i celle E11. Resultatet ser slik ut:
Merk at den eneste forskjellen mellom de to formlene er det tredje argumentet (Col_index_num) har endret seg fra en "2" til en "3" (fordi vi ønsker data hentet fra den tredje kolonnen i databasen).
Hvis vi bestemte oss for å kjøpe 2 av disse elementene, ville vi skrive inn en "2" i celle D11. Vi ville da skrive inn en enkel formel i celle F11 for å få linjens totale:
=D11*E11
… som ser slik ut …
Fyller på 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:
-
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:
Image Vi kan rette opp dette ved dømmende bruk av Excel HVIS() 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 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 celle referanser. Alternativt - og enda bedre - kunne vi lage en serienavn for hele produktdatabasen (for eksempel "Produkter"), og bruk dette serienavnet i stedet for cellehenvisninger. Formelen ville 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.
- Vi vil trolig "låse" cellene som inneholder våre formler (eller rettere sagt låse opp de annen celler), og deretter beskytte regnearket for å sikre at våre nøye konstruerte formler ikke overskrides ved et uhell når noen kommer til å fylle ut fakturaen.
- Vi ville lagre filen som en mal, slik at den kan gjenbrukes av alle i vårt firma
Hvis vi følte egentlig smart, ville vi opprette en database med alle våre kunder i et annet regneark, og bruk deretter kunde-IDen som er angitt i celle F5, for å automatisk fylle inn kundens navn og adresse i cellene B6, B7 og B8.
