OPSLAG mellem to projektmapper: Nem trin-for-trin guide

Hvis du arbejder med flere projektmapper i Excel, skal du muligvis bruge VLOOKUP-funktionen til at hente data fra en projektmappe til en anden.

Funktionen VOPSLAG søger efter en bestemt værdi i en tabel i en separat projektmappe og returnerer en tilsvarende værdi fra den samme række. Syntaksen skal indeholde en fuldt kvalificeret reference til den anden projektmappe og regnearknavnet.

Det kan være en udfordring at få de rigtige referencer, når du bruger funktionen for første gang. Denne artikel bruger eksempelregneark til at vise dig præcis, hvordan du indtaster formlerne.

Du vil også lære nogle forbedringer til VLOOKUP og flere alternativer til funktionen.

Lad os komme igang!

Indholdsfortegnelse

• Hvad er VLOOKUP?
• Sådan bruges VLOOKUP Mellem to projektmapper
  • Eksempeldata
  • Trin 1: Hold begge projektmapper åbne
  • Trin 2: Begynd at indtaste VLOOKUP-formlen
  • Trin 3: Sådan henvises til den anden projektmappe
• 3 Almindelige VLOOKUP-fejl og hvordan man løser dem
  • 1. #N/A fejl
  • 2. #Ref! Kolonnenummerfejl
  • 3. Anførselstegn og firkantede parenteser Fejl
• 4 tips til håndtering af forskellige projektmapper
• Sådan opdateres data automatisk
• Avanceret anvendelse af VLOOKUP-funktionen
• 2 vigtigste alternativer til VLOOKUP
  • 1. INDEX MATCH
  • 2. XLOOKUP
• Sådan bruges VLOOKUP på tværs af flere ark
  • Trin 1: Identificer komponenterne
  • Trin 2: Konfigurer dine data
  • Trin 3: Brug VLOOKUP-funktionen
  • (Valgfrit) Trin 4: Fejlfinding af #N/A fejl
• Afsluttende tanker

Hvad er VLOOKUP?

Antag, at du har en projektmappe over produktsalg og en separat projektmappe med priserne på hvert produkt. Du vil have adgang til priserne fra den anden projektmappe og bruge dem i beregninger i den første projektmappe.

VLOOKUP er en løsning til denne opgave. Udtrykket står for "Lodret opslag . “

Det er en indbygget funktion, der giver dig mulighed for at søge efter en bestemt værdi i en kolonne og returnere en tilsvarende værdi i en anden kolonne baseret på placeringen.

Den kan bruges med:

• et enkelt regneark

• flere ark i samme projektmappe

• to Excel-projektmapper

Hvis du ikke har den ekstra kompleksitet af flere projektmapper, så tjek denne øvelse på .

Sådan bruges VLOOKUP Mellem to projektmapper

VLOOKUP-funktionen er især nyttig, når du skal søge efter data på tværs af flere regneark eller projektmapper.

De tre hovedtrin til at gøre det er:

1. Åbn begge projektmapper.

2. Indtast VLOOKUP-formlen i den første Excel-projektmappe.

3. Angiv en fuldt kvalificeret reference til den anden projektmappe.

Eksempeldata

Denne enkle vejledning arbejder gennem eksempler baseret på eksempeldata. For at følge med skal du oprette en ny projektmappe kaldet "Salg" og indtaste disse data i kolonne A og B:

• Æbler, 40

• Appelsiner, 30

• Pærer, 70

• Bananer, 20

Tilføj en tredje kolonne for at indeholde prisoplysningerne:

Opret en anden Excel-projektmappe kaldet "Produkt", og omdøb Ark1 til "Priser". Indtast disse data i kolonne A og kolonne B:

• Æbler, 1,50

• Appelsiner, 2.30

• Pærer, 3,60

• Bananer, 2.10

Dataene ser således ud:

For at beregne omsætningen for salget i det første regneark skal vi slå prisen op i det andet regneark. VLOOKUP til undsætning!

Trin 1: Hold begge projektmapper åbne

Begge projektmapper skal være åbne, når du bruger VLOOKUP-formlen mellem to projektmapper. Hvis den anden projektmappe lukkes, vil funktionen returnere en fejl.

Du kan enten åbne dem separat eller bruge kommandoen "Åbn" i Excel til at åbne dem begge på én gang. Der er to måder at åbne begge projektmapper på:

• Gå til fanen Filer og vælg "Åbn".

• Brug tastaturgenvejen "Ctrl + O" .

Når du har begge projektmapper åbne, kan du skifte mellem dem ved at klikke på den relevante fane nederst i Excel-vinduet.

Trin 2: Begynd at indtaste VLOOKUP-formlen

Her er den grundlæggende syntaks for VLOOKUP-funktionen:

=VOPSLAG(opslagsværdi, tabelmatrix, kolonneindekstal, [områdeopslag])

• lookup_value : Den værdi, du vil slå op i den anden projektmappe.

• table_array : Området af celler i den anden projektmappe, der indeholder de data, du vil hente.

• col_index_num : Kolonnenummeret i området, der indeholder de data, du ønsker at hente.

• range_lookup : Denne sidste parameter er valgfri. Hvis du indstiller den til TRUE eller udelader den, vil VLOOKUP lede efter et omtrentligt match. Hvis du indstiller det til FALSK, vil VLOOKUP kun lede efter et nøjagtigt match.

Lad os sige, at du vil slå prisen på æbler op i den anden projektmappe af vores eksempeldata. Sådan vil formlen opdeles:

• Opslagsværdien er i celle A2 i den første projektmappe.

• Tabellarrayet er i celleområdet A1:B5 i den anden projektmappe.

• Col_index_num er den anden kolonne, der indeholder prisen.

Den mest komplicerede del af VLOOKUP-formlen er at referere den anden projektmappe korrekt.

Trin 3: Sådan henvises til den anden projektmappe

For at fuldføre VLOOKUP-formlen for to projektmapper skal du bruge en fuldt kvalificeret reference, der inkluderer disse detaljer fra den anden projektmappe:

• projektmappenavn

• arknavn

• celleområde

Dette sikrer, at Excel ved præcis, hvor man kan finde de data, du leder efter. Den fuldt kvalificerede reference bruges til argumentet table_array for funktionen VLOOKUP.

Disse er værdierne for eksempeldataene:

• projektmappenavn: Product.xlsx

• arknavn: Priser

• celleområde: $A1:$B5

For at slå prisen op for den første vare skal du indtaste følgende formel i celle C2 i den første projektmappe:

=OPSLAG(A2, [Produkt.xlsx]Priser!$A$1:$B$5, 2, FALSK)

Dette billede viser funktionen i aktion i formellinjen for den første celle:

Bemærk, at projektmappens navn er omgivet af firkantede parenteser, og arknavnet er efterfulgt af et udråbstegn.

Hvis Excel-filnavnet indeholder et mellemrum eller specialtegn, skal du omslutte hele projektmappen og arket i enkelte anførselstegn.

Formlen bruger absolutte værdier for celleområdet (præfikset $). Dette giver dig mulighed for at kopiere formlen ned til resten af ​​rækkerne ved at klikke på udfyldningshåndtaget.

Her er VLOOKUP-resultaterne:

Og det er det! Du ved nu, hvordan du bruger VLOOKUP effektivt. For at hjælpe dig, hvis du støder på fejl, har vi samlet tre af de mest almindelige VLOOKUP-fejl i næste afsnit.

3 Almindelige VLOOKUP-fejl og hvordan man løser dem

Nu hvor du har lært at bruge VLOOKUP fra en anden projektmappe, kan du støde på nogle almindelige fejl.

Her er de tre hyppigste fejl, og hvordan du løser dem.

1. #N/A fejl

#N/A-fejlen opstår, når opslagsværdien ikke findes i tabelarrayet. For at rette denne fejl kan du prøve følgende:

• Kontroller stavningen af ​​opslagsværdien.

• Sørg for, at opslagsværdien er i den første kolonne i tabelarrayet.

• Tjek, at opslagsværdien ikke er formateret som tekst i de to ark.

• Sørg for, at range_lookup-argumentet er sat til FALSE for at finde et eksakt match.

Ovenstående eksempel har ikke fundet en matchende værdi for Cherries i den anden projektmappe.

2. #Ref! Kolonnenummerfejl

Den # Ref! Fejl opstår, når argumentet col_index_num er større end antallet af kolonner i tabelarrayet.

For at rette denne fejl skal du kontrollere, at argumentet col_index_num er mindre end eller lig med antallet af kolonner i kildetabellen.

På billedet nedenfor er col_index_num sat til 3, men der er kun to kolonner i referenceområdet.

3. Anførselstegn og firkantede parenteser Fejl

Anførselstegn og firkantede parenteser-fejlen opstår, når du udfører VLOOKUP med en værdi, der indeholder anførselstegn eller firkantede parenteser.

For at rette denne fejl kan du prøve følgende:

• Brug dobbelte anførselstegn omkring opslagsværdien.

• Brug CHAR(34) -funktionen til at indsætte dobbelte anførselstegn.

• Brug funktionen HVISER til at returnere en tom celle, hvis opslagsværdien indeholder anførselstegn eller firkantede parenteser.

Når du først er bekendt med disse fejl, er det nemt at rette dem! For yderligere at hjælpe dig med at blive VLOOKUP-professionel har vi givet fire tips til håndtering af forskellige projektmapper i næste afsnit.

4 tips til håndtering af forskellige projektmapper

Når du arbejder med flere projektmapper, er det vigtigt at holde styr på, hvilken projektmappe der indeholder hvilke data.

Her er vores fire bedste tips til at bevare din fornuft:

1. Brug navngivningskonventioner til at hjælpe dig med at huske, hvilke Excel-regneark der indeholder hvilke data.

2. Brug absolutte referencer i dine formler for at sikre, at opslagsområdet refererer til de korrekte celler, selvom du flytter eller kopierer formlen til en anden placering eller et andet Excel-ark.

3. Brug IFERROR-funktionen til at håndtere eventuelle #N/A-fejl, der kan opstå, hvis opslagsværdierne ikke findes i kildedataene.

4. Overvej, så de ikke kan overskrives.

Sådan opdateres data automatisk

Når du arbejder med flere Excel-filer, kan det være tidskrævende at .

Hvis du vil have din VLOOKUP-formel til at opdatere automatisk, når du tilføjer eller fjerner data fra de andre projektmapper, kan du bruge INDIREKTE-funktionen.

Sådan gør du:

• I VLOOKUP-formlen skal du erstatte tabelmatrixargumentet med funktionen INDIREKTE.

• I funktionen INDIREKTE skal du bruge en reference til den celle, der indeholder tabelarrayet i den anden projektmappe.

For at opnå dette med vores eksempelprojektmapper skal du følge disse trin.

1. Brug én celle i kolonne D til at indtaste referencen til "Produkt"-projektmappens "Priser"-ark og celleområdet A1:B5 sådan her: [Product.xlsx]Priser!A:B

2. I celle C2 i "Salg"-projektmappen skal du bruge denne formel: =VLOOKUP(A2, INDIREKTE($D$1), 2, FALSK)

3. Kopier formlen ned (de absolutte referencer sikrer, at cellen med regnearket bevares).

Disse handlinger sikrer, at ændringer i det originale ark automatisk opdateres i de tilsluttede celler.

Dette billede viser INDIREKTE-funktionen kombineret med VLOOKUP:

Bemærk, at dette eksempel bruger området "A:B" til at referere til de to kolonner. Det betyder, at når du tilføjer flere produkter og priser, vil formlen fortsætte med at fungere i den aktuelle projektmappe.

Nu hvor du har fået en smagsprøve på VLOOKUPs kraft, lad os tage et kig på nogle avancerede anvendelser af funktionen i afsnittet!

Avanceret anvendelse af VLOOKUP-funktionen

Eksemplerne i denne artikel returnerer et enkelt match for hver opslagsværdi. Hvis du vil arbejde med flere matches fra en anden projektmappe, kan du bruge en matrixformel i stedet.

Du kan også kombinere VLOOKUP med andre funktioner. For eksempel kan du indlejre den i en IF-funktion for yderligere kompleks analyse.

Kompleks analyse inkluderer teknikker som chi-kvadrat-test og parrede prøver T-test. Her er et eksempel på brug af chi-kvadrat-testen i Excel:

Avancerede brugere drager fordel af VLOOKUP til at sammenligne datasæt og identificere uoverensstemmelser. Du finder andre måder i denne artikel om.

Men hvad nu hvis du ikke vil bruge VLOOKUP af en eller anden grund? Du er heldig! Der er nogle alternativer, som vi vil dække i næste afsnit.

2 vigtigste alternativer til VLOOKUP

Når det kommer til at slå værdier op i forskellige ark, er VLOOKUP ofte den foretrukne funktion. Der er dog nogle alternativer, som kan være nyttige i visse situationer.

De to mest almindelige er:

• INDEX MATCH

• XLOOKUP

1. INDEX MATCH

INDEX og MATCH er en kraftfuld kombination af to funktioner, der kan bruges til at slå værdier op i en tabel. I modsætning til VLOOKUP, som kræver, at opslagsværdien er i kolonnen længst til venstre i tabellen, kan INDEX MATCH slå værdier op i enhver kolonne.

Sådan fungerer det:

1. Funktionen INDEKS returnerer en værdi fra et specificeret område baseret på et række- og kolonnenummer.

2. MATCH-funktionen returnerer positionen af ​​en opslagsværdi i et specificeret område.

For at bruge INDEX MATCH skal du angive det område, du vil søge i, rækken, hvor du vil finde opslagsværdien, og kolonnen, hvor du vil returnere resultatet.

Her er en eksempelformel:

=INDEX(Tabelområde,MATCH(Opslagsværdi,Opslagsområde,0),Kolonneindeks)

For at bruge dette med eksempelregnearkene er dette formlen:

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

Dette opdeles som:

• MATCH-funktionen søger efter værdien i celle A2 (produktnavn) inden for området A1:A5 i arket "Priser" i "Produkt"-projektmappen.

• Funktionen INDEX returnerer en værdi fra området B1:B5 i arket "Priser" i "Produkt"-projektmappen, baseret på rækkenummeret fra MATCH-funktionen.

Dette billede viser formlen i aktion:

2. XLOOKUP

XLOOKUP er en nyere funktion, der blev introduceret i Excel 365 . Det ligner VLOOKUP, men med nogle ekstra funktioner.

En af de største fordele ved XLOOKUP er, at den kan slå værdier op til venstre for opslagskolonnen.

Sådan fungerer det:

1. XLOOKUP-funktionen returnerer en værdi fra et specificeret interval baseret på en opslagsværdi.

2. Du kan angive, om du ønsker et nøjagtigt match eller et omtrentligt match.

3. Du kan også angive, hvad der skal returneres, hvis der ikke findes noget match.

For at bruge XLOOKUP skal du angive opslagsværdien, det område, du vil søge i, og den kolonne, hvor du vil returnere resultatet.

Her er en eksempelformel:

=XLOOKUP(Opslagsværdi,Opslagsområde,Returområde,"Ikke fundet",0)

For at bruge dette med eksempelregnearkene er dette formlen:

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

Dette opdeles som:

• A2: Den værdi, du vil slå op (produktnavn).

• [Product.xlsx]Priser!$A$1:$A$5: Det område, hvor du vil søge efter opslagsværdien (produktnavne i arket "Priser" i projektmappen "Produkt").

• [Product.xlsx]Priser!$B$1:$B$5: Det interval, der indeholder returværdierne (priser i arket "Priser" i projektmappen "Produkt").

Dette er formlen i aktion:

Sådan bruges VLOOKUP på tværs af flere ark

Hvis du har brug for at udføre en VOPSLAG mellem to projektmapper, skal du muligvis også bruge VOPSLAG på tværs af flere ark.

Heldigvis er processen for at gøre det meget lig at udføre en VLOOKUP mellem to projektmapper.

Der er fire trin.

Trin 1: Identificer komponenterne

I stedet for at inkludere tabelarrayet, som du ville gøre for et ark, skal du også inkludere arknavnet.

Hvis du f.eks. vil søge efter en værdi i området A2:B6 på Jan-arket i Sales_reports.xlsx-projektmappen, vil din formel se sådan ud:

=OPSLAG(A2,[Salg_reports.xlsx]Jan!$A$2:$B$6,2,FALSK)

Trin 2: Konfigurer dine data

Sørg for, at dine data er organiseret på en måde, der gør det nemt at referere på tværs af flere ark.

Du vil måske overveje at oprette forskellige faner til specifikke referencedata.

Trin 3: Brug VLOOKUP-funktionen

Når dine data er sat op, kan du bruge VLOOKUP-funktionen til at søge efter og hente data fra andre ark.

Bare sørg for at inkludere arknavnet i din formel, som vist ovenfor.

(Valgfrit) Trin 4: Fejlfinding af #N/A fejl

Hvis du støder på #N/A-fejl, når du bruger VLOOKUP på tværs af flere ark, skal du dobbelttjekke din formel for at sikre dig, at du har refereret korrekt til alle de nødvendige ark og områder.

Det kan også være en god idé at tjekke, at du udfører et nøjagtigt match i stedet for et områdeopslag.

Afsluttende tanker

Du har lært, hvordan du bruger VLOOKUP til at søge og hente data på tværs af to regneark. Denne alsidige funktion gør det nemt at slå værdier op i én projektmappe baseret på tilsvarende værdier i en anden.

VLOOKUP-funktionens kraft strækker sig langt ud over enkelte regneark og rækker på tværs af forskellige projektmapper for at linke og analysere data.

At mestre denne funktion er et væsentligt skridt i retning af at blive dygtig til Excel, da det åbner op for nye muligheder for at administrere og fortolke dine data.

Ved at inkorporere funktioner som INDIRECT, INDEX-MATCH eller XLOOKUP kan du yderligere forbedre fleksibiliteten og effektiviteten af ​​dine dataopslag.

Fortsæt med at øve og udforske de store muligheder i Excel; dette er kun toppen af ​​isbjerget. God datajagt!