VLOOKUP Between Two Workbooks: Enkel trinn-for-trinn-veiledning

Hvis du jobber med flere arbeidsbøker i Excel, kan det hende du må bruke VLOOKUP-funksjonen for å hente data fra en arbeidsbok til en annen.

VLOOKUP-funksjonen søker etter en bestemt verdi i en tabell i en separat arbeidsbok og returnerer en tilsvarende verdi fra samme rad. Syntaksen må inneholde en fullstendig referanse til den andre arbeidsboken og regnearkets navn.

Å få riktige referanser kan være utfordrende når du bruker funksjonen for første gang. Denne artikkelen bruker eksempelregneark for å vise deg nøyaktig hvordan du legger inn formlene.

Du vil også lære noen forbedringer av VLOOKUP og flere alternativer til funksjonen.

La oss komme i gang!

Innholdsfortegnelse

• Hva er VLOOKUP?
• Hvordan bruke VLOOKUP Between Two Workbooks
  • Eksempeldata
  • Trinn 1: Hold begge arbeidsbøkene åpne
  • Trinn 2: Begynn å gå inn i VLOOKUP-formelen
  • Trinn 3: Hvordan referere til den andre arbeidsboken
• 3 Vanlige VLOOKUP-feil og hvordan du fikser dem
  • 1. #N/A feil
  • 2. #Ref! Kolonnenummerfeil
  • 3. Anførselstegn og hakeparentesfeil
• 4 tips for å administrere ulike arbeidsbøker
• Hvordan oppdatere data automatisk
• Avansert bruk av VLOOKUP-funksjonen
• 2 Hovedalternativer til VLOOKUP
  • 1. INDEKSTREFF
  • 2. XLOOKUP
• Hvordan bruke VLOOKUP på tvers av flere ark
  • Trinn 1: Identifiser komponentene
  • Trinn 2: Sett opp dataene dine
  • Trinn 3: Bruk VLOOKUP-funksjonen
  • (Valgfritt) Trinn 4: Feilsøk #N/A-feil
• Siste tanker

Hva er VLOOKUP?

Anta at du har en arbeidsbok med produktsalg og en egen arbeidsbok med prisene på hvert produkt. Du vil ha tilgang til prisene fra den andre arbeidsboken og bruke dem i beregninger i den første arbeidsboken.

VLOOKUP er én løsning for denne oppgaven. Begrepet står for "Vertical Lookup . "

Det er en innebygd funksjon som lar deg søke etter en bestemt verdi i en kolonne og returnere en tilsvarende verdi i en annen kolonne basert på plasseringen.

Den kan brukes med:

• et enkelt regneark

• flere ark i samme arbeidsbok

• to Excel-arbeidsbøker

Hvis du ikke har den ekstra kompleksiteten til flere arbeidsbøker, sjekk ut denne opplæringen på .

Hvordan bruke VLOOKUP Between Two Workbooks

VLOOKUP-funksjonen er spesielt nyttig når du trenger å søke etter data på tvers av flere regneark eller arbeidsbøker.

De tre hovedtrinnene for å gjøre det er:

1. Åpne begge arbeidsbøkene.

2. Skriv inn VLOOKUP-formelen i den første Excel-arbeidsboken.

3. Gi en fullstendig referanse til den andre arbeidsboken.

Eksempeldata

Denne enkle opplæringen arbeider gjennom eksempler basert på eksempeldata. For å følge med, lag en ny arbeidsbok kalt "Salg" og skriv inn disse dataene i kolonne A og B:

• Epler, 40

• Appelsiner, 30

• Pærer, 70

• Bananer, 20

Legg til en tredje kolonne for å inneholde prisinformasjonen:

Lag en ekstra Excel-arbeidsbok kalt "Produkt" og gi nytt navn til Ark1 til "Priser". Skriv inn disse dataene i kolonne A og kolonne B:

• Epler, 1,50

• Appelsiner, 2.30

• Pærer, 3,60

• Bananer, 2.10

Dataene ser slik ut:

For å beregne inntektene for salget i det første regnearket, må vi slå opp prisen i det andre regnearket. VLOOKUP til unnsetning!

Trinn 1: Hold begge arbeidsbøkene åpne

Begge arbeidsbøkene må være åpne når du bruker VLOOKUP-formelen mellom to arbeidsbøker. Hvis den andre arbeidsboken lukkes, vil funksjonen returnere en feil.

Du kan enten åpne dem separat eller bruke "Åpne"-kommandoen i Excel for å åpne dem begge samtidig. Det er to måter å åpne begge arbeidsbokene på:

• Gå til Fil-fanen og velg "Åpne".

• Bruk hurtigtasten "Ctrl + O" .

Når du har begge arbeidsbøkene åpne, kan du bytte mellom dem ved å klikke på den aktuelle fanen nederst i Excel-vinduet.

Trinn 2: Begynn å gå inn i VLOOKUP-formelen

Her er den grunnleggende syntaksen til VLOOKUP-funksjonen:

=VLOOKUP(oppslagsverdi, tabellmatrise, kolonneindeksnummer, [områdeoppslag])

• lookup_value : Verdien du vil slå opp i den andre arbeidsboken.

• table_array : Celleområdet i den andre arbeidsboken som inneholder dataene du vil hente.

• col_index_num : Kolonnenummeret i området som inneholder dataene du ønsker å hente.

• range_lookup : Denne siste parameteren er valgfri. Hvis du setter den til TRUE eller utelater den, vil VLOOKUP se etter en omtrentlig match. Hvis du setter den til FALSE, vil VLOOKUP kun se etter en eksakt match.

La oss si at du vil slå opp prisen på epler i den andre arbeidsboken i prøvedataene våre. Slik vil formelen brytes ned:

• Oppslagsverdien er i celle A2 i den første arbeidsboken.

• Tabellmatrisen er i celleområdet A1:B5 i den andre arbeidsboken.

• Col_index_num er den andre kolonnen som inneholder prisen.

Den mest kompliserte delen av VLOOKUP-formelen er å referere til den andre arbeidsboken riktig.

Trinn 3: Hvordan referere til den andre arbeidsboken

For å fullføre VLOOKUP-formelen for to arbeidsbøker, må du bruke en fullt kvalifisert referanse som inkluderer disse detaljene fra den andre arbeidsboken:

• arbeidsboknavn

• arknavn

• celleområde

Dette sikrer at Excel vet nøyaktig hvor du skal finne dataene du leter etter. Den fullt kvalifiserte referansen brukes for table_array-argumentet til VLOOKUP-funksjonen.

Dette er verdiene for eksempeldataene:

• arbeidsboknavn: Product.xlsx

• arknavn: Priser

• celleområde: $A1:$B5

For å slå opp prisen for det første elementet, skriv inn følgende formel i celle C2 i den første arbeidsboken:

=SØKUP(A2, [Produkt.xlsx]Priser!$A$1:$B$5, 2, FALSE)

Dette bildet viser funksjonen i aksjon i formellinjen for den første cellen:

Merk at arbeidsboknavnet er omsluttet av hakeparenteser, og arknavnet er etterfulgt av et utropstegn.

Hvis Excel-filnavnet inneholder et mellomrom eller spesialtegn, må du omslutte hele arbeidsboken og arket i enkle anførselstegn.

Formelen bruker absolutte verdier for celleområdet ($-prefikset). Dette lar deg kopiere ned formelen til resten av radene ved å klikke på fyllhåndtaket.

Her er VLOOKUP-resultatene:

Og det er det! Du vet nå hvordan du bruker VLOOKUP effektivt. For å hjelpe deg i tilfelle du støter på feil, har vi samlet tre av de vanligste VLOOKUP-feilene i neste avsnitt.

3 Vanlige VLOOKUP-feil og hvordan du fikser dem

Nå som du har lært hvordan du bruker VLOOKUP fra en annen arbeidsbok, kan du støte på noen vanlige feil.

Her er de tre vanligste feilene og hvordan du fikser dem.

1. #N/A feil

#N/A-feilen oppstår når oppslagsverdien ikke finnes i tabellmatrisen. For å fikse denne feilen kan du prøve følgende:

• Sjekk stavemåten til oppslagsverdien.

• Sørg for at oppslagsverdien er i den første kolonnen i tabellmatrisen.

• Sjekk at oppslagsverdien ikke er formatert som tekst i de to arkene.

• Sørg for at range_lookup-argumentet er satt til FALSE for å finne et eksakt samsvar.

Eksempelet ovenfor har ikke klart å finne en samsvarende verdi for Kirsebær i den andre arbeidsboken.

2. #Ref! Kolonnenummerfeil

# Ref! Feil oppstår når argumentet col_index_num er større enn antall kolonner i tabellmatrisen.

For å fikse denne feilen, kontroller at argumentet col_index_num er mindre enn eller lik antall kolonner i kildetabellen.

På bildet nedenfor er col_index_num satt til 3, men det er bare to kolonner i referanseområdet.

3. Anførselstegn og hakeparentesfeil

Anførselstegn og hakeparenteser-feilen oppstår når du utfører VLOOKUP med en verdi som inneholder anførselstegn eller hakeparenteser.

For å fikse denne feilen kan du prøve følgende:

• Bruk doble anførselstegn rundt oppslagsverdien.

• Bruk funksjonen CHAR(34) for å sette inn doble anførselstegn.

• Bruk IFERROR-funksjonen til å returnere en tom celle hvis oppslagsverdien inneholder anførselstegn eller hakeparenteser.

Når du først er kjent med disse feilene, er det enkelt å fikse dem! For ytterligere å hjelpe deg med å bli en VLOOKUP-proff, har vi gitt fire tips for å administrere forskjellige arbeidsbøker i neste seksjon.

4 tips for å administrere ulike arbeidsbøker

Når du arbeider med flere arbeidsbøker, er det viktig å holde styr på hvilken arbeidsbok som inneholder hvilke data.

Her er våre fire beste tips for å holde på fornuften:

1. Bruk navnekonvensjoner for å hjelpe deg med å huske hvilke Excel-regneark som inneholder hvilke data.

2. Bruk absolutte referanser i formlene dine for å sikre at oppslagsområdet refererer til de riktige cellene selv om du flytter eller kopierer formelen til en annen plassering eller et annet Excel-ark.

3. Bruk IFERROR-funksjonen til å håndtere eventuelle #N/A-feil som kan oppstå hvis oppslagsverdiene ikke finnes i kildedataene.

4. Vurder slik at de ikke kan overskrives.

Hvordan oppdatere data automatisk

Når du arbeider med flere Excel-filer, kan det være tidkrevende å .

Hvis du vil at VLOOKUP-formelen skal oppdateres automatisk når du legger til eller fjerner data fra de andre arbeidsbøkene, kan du bruke INDIREKTE-funksjonen.

Dette er hvordan:

• I VLOOKUP-formelen erstatter du tabellmatriseargumentet med INDIREKTE-funksjonen.

• I INDIREKTE-funksjonen bruker du en referanse til cellen som inneholder tabellmatrisen i den andre arbeidsboken.

For å oppnå dette med våre eksempelarbeidsbøker, følg disse trinnene.

1. Bruk én celle i kolonne D for å skrive inn referansen til «Produkt»-arbeidsbokens «Priser»-ark og celleområdet A1:B5 slik: [Product.xlsx]Priser!A:B

2. I celle C2 i "Salg"-arbeidsboken bruker du denne formelen: =VLOOKUP(A2, INDIREKTE($D$1), 2, FALSE)

3. Kopier formelen ned (de absolutte referansene sikrer at cellen med regnearket blir bevart).

Disse handlingene sikrer at endringer i det originale arket oppdateres automatisk i de tilkoblede cellene.

Dette bildet viser INDIREKTE-funksjonen kombinert med VLOOKUP:

Merk at dette eksemplet bruker området "A:B" for å referere til de to kolonnene. Dette betyr at når du legger til flere produkter og priser, vil formelen fortsette å fungere i gjeldende arbeidsbok.

Nå som du har fått en smak av VLOOKUPs kraft, la oss ta en titt på noen avanserte bruksområder for funksjonen i seksjonen!

Avansert bruk av VLOOKUP-funksjonen

Eksemplene i denne artikkelen returnerer ett enkelt samsvar for hver oppslagsverdi. Hvis du vil jobbe med flere treff fra en annen arbeidsbok, kan du bruke en matriseformel i stedet.

Du kan også kombinere VLOOKUP med andre funksjoner. Du kan for eksempel bygge den inn i en IF-funksjon for ytterligere kompleks analyse.

Kompleks analyse inkluderer teknikker som kjikvadrat-tester og sammenkoblede prøver T-tester. Her er et eksempel på bruk av kjikvadrattesten i Excel:

Avanserte brukere drar nytte av VLOOKUP for å sammenligne datasett og identifisere uoverensstemmelser. Du finner andre måter i denne artikkelen på .

Men hva om du ikke vil bruke VLOOKUP uansett grunn? Du er heldig! Det er noen alternativer, som er det vi skal dekke i neste avsnitt.

2 Hovedalternativer til VLOOKUP

Når det kommer til å slå opp verdier i forskjellige ark, er VLOOKUP ofte gå-til-funksjonen. Det er imidlertid noen alternativer som kan være nyttige i visse situasjoner.

De to vanligste er:

• INDEKSTREFF

• XLOOKUP

1. INDEKSTREFF

INDEX og MATCH er en kraftig kombinasjon av to funksjoner som kan brukes til å slå opp verdier i en tabell. I motsetning til VLOOKUP, som krever at oppslagsverdien er i kolonnen lengst til venstre i tabellen, kan INDEX MATCH slå opp verdier i en hvilken som helst kolonne.

Slik fungerer det:

1. INDEKS-funksjonen returnerer en verdi fra et spesifisert område basert på et rad- og kolonnenummer.

2. MATCH-funksjonen returnerer posisjonen til en oppslagsverdi i et spesifisert område.

For å bruke INDEX MATCH må du spesifisere området du vil søke i, raden der du vil finne oppslagsverdien og kolonnen der du vil returnere resultatet.

Her er en eksempelformel:

=INDEKS(Tabellområde,MATCH(Oppslagsverdi,Oppslagsområde,0),Kolonneindeks)

For å bruke dette med eksempelregnearkene, er dette formelen:

=INDEKS([Product.xlsx]Priser!$B$1:$B$5, MATCH(A2, [Product.xlsx]Priser!$A$1:$A$5, 0))

Dette brytes ned som:

• MATCH-funksjonen søker etter verdien i celle A2 (produktnavn) innenfor området A1:A5 i arket "Priser" i arbeidsboken "Produkt".

• INDEKS-funksjonen returnerer en verdi fra området B1:B5 i "Priser"-arket i "Produkt"-arbeidsboken, basert på radnummeret gitt av MATCH-funksjonen.

Dette bildet viser formelen i aksjon:

2. XLOOKUP

XLOOKUP er en nyere funksjon som ble introdusert i Excel 365 . Det ligner på VLOOKUP, men med noen tilleggsfunksjoner.

En av de største fordelene med XLOOKUP er at den kan slå opp verdier til venstre for oppslagskolonnen.

Slik fungerer det:

1. XLOOKUP-funksjonen returnerer en verdi fra et spesifisert område basert på en oppslagsverdi.

2. Du kan spesifisere om du vil ha et eksakt samsvar eller et omtrentlig samsvar.

3. Du kan også spesifisere hva som skal returneres hvis ingen treff blir funnet.

For å bruke XLOOKUP, må du spesifisere oppslagsverdien, området du vil søke i, og kolonnen hvor du vil returnere resultatet.

Her er en eksempelformel:

=XLOOKUP(Oppslagsverdi,Oppslagsområde,Returområde,”Ikke funnet”,0)

For å bruke dette med eksempelregnearkene, er dette formelen:

=XLOOKUP(A2, [Product.xlsx]Priser!$A$1:$A$5, [Product.xlsx]Priser!$B$1:$B$5)

Dette brytes ned som:

• A2: Verdien du vil slå opp (produktnavn).

• [Product.xlsx]Priser!$A$1:$A$5: Området der du vil søke etter oppslagsverdien (produktnavn i "Priser"-arket i "Produkt"-arbeidsboken).

• [Product.xlsx]Priser!$B$1:$B$5: Området som inneholder returverdiene (priser i «Priser»-arket i «Produkt»-arbeidsboken).

Dette er formelen i aksjon:

Hvordan bruke VLOOKUP på tvers av flere ark

Hvis du trenger å utføre en VLOOKUP mellom to arbeidsbøker, kan det hende du også må bruke VLOOKUP på tvers av flere ark.

Heldigvis er prosessen for å gjøre det veldig lik å utføre en VLOOKUP mellom to arbeidsbøker.

Det er fire trinn.

Trinn 1: Identifiser komponentene

I stedet for å inkludere tabellen som du ville gjort for ett ark, må du inkludere arknavnet også.

Hvis du for eksempel vil søke etter en verdi i området A2:B6 på Jan-arket i Sales_reports.xlsx-arbeidsboken, vil formelen din se slik ut:

=SØKUP(A2,[Salgsrapporter.xlsx]Jan!$A$2:$B$6,2,FALSE)

Trinn 2: Sett opp dataene dine

Sørg for at dataene dine er organisert på en måte som gjør det enkelt å referere på tvers av flere ark.

Det kan være lurt å vurdere å opprette forskjellige faner for spesifikke referansedata.

Trinn 3: Bruk VLOOKUP-funksjonen

Når dataene dine er satt opp, kan du bruke VLOOKUP-funksjonen til å søke etter og hente data fra andre ark.

Bare sørg for å inkludere arknavnet i formelen din, som vist ovenfor.

(Valgfritt) Trinn 4: Feilsøk #N/A-feil

Hvis du støter på #N/A-feil når du bruker VLOOKUP på tvers av flere ark, dobbeltsjekker du formelen din for å være sikker på at du har referert til alle nødvendige ark og områder.

Det kan også være lurt å sjekke at du utfører et eksakt samsvar, i stedet for et områdeoppslag.

Siste tanker

Du har lært hvordan du bruker VLOOKUP til å søke og hente data på tvers av to regneark. Denne allsidige funksjonen gjør det enkelt å slå opp verdier i én arbeidsbok basert på tilsvarende verdier i en annen.

VLOOKUP-funksjonens kraft strekker seg langt utover enkelt regneark, og når på tvers av forskjellige arbeidsbøker for å koble og analysere data.

Å mestre denne funksjonen er et viktig skritt mot å bli dyktig i Excel, da det åpner for nye muligheter for å administrere og tolke dataene dine.

Ved å inkorporere funksjoner som INDIRECT, INDEX-MATCH eller XLOOKUP, kan du ytterligere forbedre fleksibiliteten og effektiviteten til dine dataoppslag.

Fortsett å øve og utforske de enorme egenskapene til Excel; dette er bare toppen av isfjellet. God datajakt!