Sådan sammenlignes to kolonner i Excel ved hjælp af VLOOKUP

Når du arbejder med store datasæt i Excel, skal du muligvis sammenligne to kolonner for at finde ligheder eller forskelle.

VLOOKUP er en kraftfuld funktion, der giver dig mulighed for at søge efter matchende data mellem to kolonner. Funktionen giver dig mulighed for at søge efter værdier i en kolonne, der vises i en anden.

Denne artikel viser dig, hvordan du sammenligner to kolonner ved hjælp af VLOOKUP, så du effektivt kan analysere dine data.

Indholdsfortegnelse

• Grundlæggende om Vlookup-funktionen
  • Vlookup funktion
• Forberedelse af dine data til VLOOKUP
  • Formatering af data
• Sådan bruges VLOOKUP til at sammenligne to kolonner
  • Håndtering af andre fejl
• Alternativer til VLOOKUP for kolonnesammenligning
  • 1. Brug af indeks- og matchfunktioner
  • 2. Anvendelse af XLOOKUP i Excel
• Fem tips til at forbedre VLOOKUP-ydeevnen
  • 1. Begræns dit opslagsområde
  • 2. Brug absolutte referencer
  • 3. Sorter dine data
  • 4. Brug et dobbelt VLOOKUP
  • 5. Brug betinget formatering
• Avanceret anvendelse til VLOOKUP
• Vores sidste ord

Grundlæggende om Vlookup-funktionen

Antag, at du har et regneark med to lister over emner i kolonne A og kolonne B. Du vil finde emnerne i liste 1, der også optræder i liste 2.

Du kan forestille dig, at det ville være enormt tidskrævende at arbejde manuelt gennem lister med tusindvis af varer. Heldigvis bringer VLOOKUP til undsætning!

Udtrykket VLOOKUP står for Vertical Lookup. Funktionen sammenligner to kolonner, finder match mellem dem og returnerer de tilknyttede værdier.

Vlookup funktion

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

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

Dette er en opdeling af elementerne:

• lookup_value : den værdi, du vil søge efter i den første kolonne i table_array.

• table_array : intervallet af celler, der indeholder de data, du vil søge i.

• col_index_num : kolonnenummeret i table_arrayet, du vil returnere værdien fra.

• range_lookup : Valgfrit. Det er enten SAND (omtrentlig match) eller FALSK (nøjagtig match). Standarden er TRUE.

Forberedelse af dine data til VLOOKUP

Før du bruger VLOOKUP til at sammenligne to , skal du forberede dine data.

Opret to separate kolonner i dit regneark, hvor du vil sammenligne værdierne. Denne artikel bruger kolonne A og kolonne B til vores eksempler.

Her er prøvedataene, der er brugt i denne artikel:

Dette er eksempeltabellen med overskrift, der skal tilføjes til afsnittet med titlen "Forberedelse af dine data til VLOOKUP":

Liste 1 Liste 2
Emne 1 Emne 3
Emne 2
Emne 4 Emne 3 Emne 1
Emne 4 Emne 5

Formatering af data

Det er vigtigt at sikre, at dataene i begge kolonner er formateret ens. VLOOKUP er store og små bogstaver, hvilket betyder, at store og små bogstaver betyder noget.

Matchningsfejl kan forekomme i det endelige resultat, hvis formateringen er inkonsekvent.

Det er også en god idé at fjerne eventuelle duplikerede værdier eller tomme celler for at minimere risikoen for fejl.

Sådan fjerner du dublerede værdier:

1. Vælg kolonnen.

2. Gå til fanen Data i det øverste bånd.

3. Klik på Fjern dubletter i afsnittet Dataværktøjer.

Knappen kan være lidt svær at se. Dette billede vil hjælpe dig:

Sådan fjerner du tomme celler:

1. Vælg kolonnen.

2. Gå til fanen Hjem i det øverste bånd.

3. Udvid menuen "Find og vælg".

4. Vælg "Gå til Special".

5. Vælg "Blanks" fra mulighederne.

6. Klik på "Slet" i afsnittet Celler.

Sådan bruges VLOOKUP til at sammenligne to kolonner

Når data er forberedt, kan du skrive VLOOKUP-formlen for at sammenligne to kolonner i Excel og identificere match. Følg disse trin:

1. Vælg en celle i en ny kolonne, hvor du vil vise sammenligningsresultaterne (f.eks. celle C2).

2. Indtast følgende formel: =VLOOKUP(A2, B:B, 1, FALSK))

3. Tryk på Enter for at anvende formlen.

4. I tilfælde af en matchende værdi vil værdien blive vist i samme række i resultatkolonnen (f.eks. kolonne C).

5. Træk formlen ned fra C2 for at kopiere den over så mange celler, som du har brug for.

Hvis du bruger vores eksempeldata, skal du placere dine sammenligningsresultater i den tredje kolonne. Formlen vil finde tre matches ud af de fire elementer på liste 1.

Bemærk, at vare 2 vises som #N/A. Excel fortæller os, at matchningen ikke er relevant, dvs. den kunne ikke findes.

Det er korrekt og er nyttig information om manglende værdier. Nogle Excel-brugere kan dog tro, at der er et dataproblem eller funktionsfejl.

Det er god praksis at vise en anden indikation af, at der ikke blev fundet nogen match. Det kunne simpelthen være et tomt.

For at gøre det skal du kombinere VLOOKUP-funktionen med IFNA-funktionen på denne måde:

=IFNA(VOPSLAG(A1; B:B; 1; FALSK); “”)

IFNA-funktionen registrerer #N/A-fejlen og erstatter outputtet med et tomt mellemrum (""). Du kan også bruge ISNA-funktionen eller have yderligere logik med IF-funktionen.

Håndtering af andre fejl

VLOOKUP kan nogle gange generere andre fejl i dine sammenligningsresultater. #REF ! Fejl i Excel er et reelt problem med dine data.

Det sker normalt, når dit angivne interval er forkert. Vores eksempel har refereret til hele kolonnen, men du kan også bruge lodrette celleområder.

Sørg for, at det søgeområde, du henviser til, dækker alle de værdier, du vil sammenligne.

Alternativer til VLOOKUP for kolonnesammenligning

Der er to primære alternative opslagsfunktioner, når man sammenligner to kolonner i Excel for at finde matches.

1. Brug af indeks- og matchfunktioner

I stedet for at bruge VLOOKUP, kan du sammenligne to kolonner i Excel ved at kombinere funktionerne INDEX og MATCH.

Denne metode giver en mere fleksibel måde at slå data op og er især nyttig, når du arbejder med ikke-tilstødende kolonner, eller når kolonneindekset kan ændre sig.

Dette er syntaksen, der skal indsættes i resultatkolonnen:

=INDEKS(afkastområde, MATCH(opslagsværdi, opslagsområde, 0))

• Return_range : intervallet af celler, der indeholder de data, du vil returnere.

• Opslagsværdi : den værdi, du vil søge efter inden for opslagsområdet.

• Lookup_range : det område af celler, inden for hvilket du vil finde opslagsværdien.

Ved at bruge de samme data i de foregående eksempler erstatter vi VLOOKUP-formlen som følger:

=(INDEKS(B:B; SAMMENLIGN(A2; B:B; 0)))

MATCH-funktionen returnerer den relative position af opslagsværdien inden for opslagsområdet, og INDEX-funktionen returnerer den tilsvarende værdi fra returområdet.

Resultaterne på de samme data vil være de samme som VLOOKUP, vi brugte tidligere. Dette billede viser funktionerne i brug:

Du kan også erstatte #N/A-fejlen med en brugerdefineret meddelelse. Her er et eksempel på brug af IFERROR-funktionen.

=IFEJL(INDEKS(B:B, MATCH(A2, B:B, 0)), "Ikke fundet")

2. Anvendelse af XLOOKUP i Excel

For brugere med Excel 365 eller Excel 2019 er XLOOKUP en alternativ logisk test til VLOOKUP for at finde fælles værdier i to kolonner.

XLOOKUP giver flere fordele. Du kan bruge den til at søge i data både vandret og lodret, arbejde med ikke-tilstødende kolonner og angive brugerdefinerede værdier for fejl.

Syntaksen for XLOOKUP er:

=XLOOKUP(opslagsværdi, opslagsområde, returområde, [hvis_ikke_fundes], [matchtilstand], [søgetilstand])

• lookup_value : den værdi, du vil søge efter inden for opslagsområdet.

• lookup_range : det område af celler, inden for hvilket du vil finde opslagsværdien.

• return_range : det område af celler, der indeholder de data, du vil returnere.

• Tilføj tilpassede fejlværdier, matchtilstand og søgetilstandsparametre efter behov.

XLOOKUP finder opslagsværdien inden for opslagsområdet og returnerer den tilsvarende værdi fra returområdet.

Formlen er især nyttig, når du har to lister indlejret i datasæt med flere kolonner. I vores tidligere eksempler har listerne været i første kolonne og anden kolonne, men dit ark kan have flere data end det.

Her er et eksempel med to lister i første og tredje kolonne i et regneark:

XLOOKUP-formlen ser sådan ud:

=XLOOKUP(A2;C:C;C:C,"")

Dette billede viser resultatet med sammenligningen i kolonne D. Den første værdi er til stede, men den anden mangler.

Bemærk, at jeg ikke har nogen ekstra fejlformler, men den manglende værdi vises som tom. Det er fordi jeg bruger den fjerde parameter, som er en brugerdefineret fejlværdi. I dette tilfælde er det en tom streng.

Som en bonus vil jeg vise dig, hvordan du bruger XLOOKUP til at sammenligne på tværs af rækker. Hvis du har to lister, der er i række 1 og række 2, ser den enkleste brug af formlen sådan ud:

=XLOOKUP(B1; 2:2; 2:2; "")

Dette billede viser resultaterne på de to rækker.

Med sin forbedrede funktionalitet og fleksibilitet er XLOOKUP et glimrende alternativ til VLOOKUP til at sammenligne lister i Excel for at afgøre, om der er matches.

Fem tips til at forbedre VLOOKUP-ydeevnen

Optimering af din VLOOKUP-funktion kan hjælpe dig med at undgå lange ventetider og forbedre den overordnede reaktionsevne af dit Excel-regneark.

Her er seks tips og tricks, du kan anvende for at forbedre ydeevnen af ​​dit VLOOKUP i Excel.

1. Begræns dit opslagsområde

Jeg brugte hele kolonner i eksemplerne for at gøre dem enkle. Hvis du arbejder med en stor mængde data, bør du undgå at gøre det. Brug af hele kolonner kan bremse Excels beregningsproces.

Prøv i stedet at bruge det nøjagtige område, der kræves for dine data (f.eks. A1:A100). Dette reducerer antallet af celler, som din VLOOKUP-funktion skal evaluere.

2. Brug absolutte referencer

Når du angiver et dataområde (f.eks. cellerne fra B2 til B5), skal du bruge absolutte referencer. Dette sikrer, at formlen er konsistent, og at data eksisterer, når du kopierer den på tværs af flere celler.

Her er et eksempel:

=OPSLAG(A2, $B$2:$B$5, 1, FALSK)

3. Sorter dine data

Hvis du ved, at dataene i opslagskolonnen er sorteret i stigende rækkefølge, kan du bruge VLOOKUP med et TRUE eller 1 til 'range_lookup'-argumentet.

Dette vil få Excel til at udføre et omtrentligt match, som er hurtigere end et eksakt match på tværs af alle celler. Vær dog forsigtig, når du bruger denne mulighed, da en forkert sortering kan føre til forkerte resultater.

4. Brug et dobbelt VLOOKUP

Du kan bruge to VLOOKUP-funktioner til at fremskynde søgeprocessen i Excel.

Den første VLOOKUP vil afgøre, om opslagsværdien eksisterer ved at sætte 'col_index_num' til 1 og 'range_lookup' til TRUE.

Hvis den returnerer TRUE, henter en anden VLOOKUP den ønskede værdi med 'range_lookup' sat til TRUE.

5. Brug betinget formatering

Du kan bruge betinget formatering i dit Excel-regneark til at fremhæve matchende eller manglende værdier i den angivne kolonne. Du kan også anvende farver på unikke værdier. Dette gør dine data nemmere at læse.

Du finder menuen for betinget formatering i stilgruppen på fanen Hjem.

Avanceret anvendelse til VLOOKUP

Parrede prøver T-test bruges til at sammenligne gennemsnittet af to relaterede prøver. Denne video viser deres brug i Excel.

Hvis du har yderligere data eller variabler, som du skal referere under din analyse, kan du bruge VLOOKUP til at hente de nødvendige værdier fra en anden tabel eller regneark.

For eksempel kan du bruge VLOOKUP til at hente demografiske oplysninger eller behandlingsbetingelser for hver parrede observation i dit datasæt.

Vores sidste ord

Efterhånden har du en solid forståelse af at bruge VLOOKUP. Denne kraftfulde funktion giver dig mulighed for hurtigt at identificere forskelle og matchende værdier mellem to lister, hvilket gør dataanalyse mere effektiv og nøjagtig.

Du så også eksempler på andre opslags- og referencefunktioner, såsom XLOOKUP, INDEX og MATCH. Tilføjelse af disse elementer til dine færdigheder vil yderligere styrke dine dataanalysemuligheder.

Fortsæt med at øve og forfine dine VLOOKUP-færdigheder, og du vil snart blive en ekspert i at sammenligne kolonner i Excel, hvilket sparer tid og øger din produktivitet.