Sådan sammenligner du to kolonner i Excel (for match og forskelle)
Se video – Sammenlign to kolonner i Excel for match og forskelle
Den forespørgsel, som jeg får ofte, er: “Hvordan sammenligner man to kolonner i Excel?”.
Dette kan gøres på mange forskellige måder, og den metode, der skal bruges, afhænger af datastrukturen, og hvad brugeren ønsker af den.
For eksempel kan du ønske at sammenligne to kolonner og finde eller fremhæve alle de matchende datapunkter (der er i begge kolonner) eller kun forskellene (hvor et datapunkt er i den ene kolonne og ikke i den anden) osv.
Da jeg bliver spurgt så meget om dette, besluttede jeg at skrive denne massive tutorial med en hensigt om at dække de fleste (hvis ikke alle) mulige scenarier.
Hvis du finder dette nyttigt, må du gerne give det videre til andre Excel-brugere.
Denne vejledning dækker:
Bemærk, at de teknikker til sammenligning af kolonner, der vises i denne vejledning, ikke er de eneste.
Baseret på dit datasæt skal du muligvis ændre eller justere metoden. De grundlæggende principper vil dog forblive de samme.
Hvis du mener, at der er noget, der kan tilføjes til denne vejledning, så lad mig vide det i kommentarfeltet
Sammenlign to kolonner for nøjagtig rækkeoverensstemmelse
Dette er den enkleste form for sammenligning. I dette tilfælde skal du foretage en sammenligning række for række og identificere, hvilke rækker der har de samme data, og hvilke rækker der ikke har det.
Eksempel: Sammenlign celler i samme række
Nedenfor er et datasæt, hvor jeg skal kontrollere, om navnet i kolonne A er det samme i kolonne B eller ej.
Hvis der er et match, skal jeg have resultatet som “TRUE”, og hvis ikke matcher, skal jeg have resultatet som “FALSE”.
Den nedenstående formel ville gøre dette:
=A2=B2
Eksempel: Hvis du ønsker at få et mere beskrivende resultat, kan du bruge en simpel IF-formel til at returnere “Match”, når navnene er de samme, og “Mismatch”, når navnene er forskellige.
=IF(A2=B2,"Match","Mismatch")
Bemærk: Hvis du ønsker at gøre sammenligningen case sensitive, skal du bruge følgende IF-formel:
=IF(EXACT(A2,B2),"Match","Mismatch")
Med ovenstående formel vil “IBM” og “ibm” blive betragtet som to forskellige navne, og ovenstående formel vil returnere “Mismatch”.
Eksempel: Hvis du vil fremhæve de rækker med matchende data
Hvis du vil fremhæve de rækker, der har matchende data (i stedet for at få resultatet i en separat kolonne), kan du gøre det ved at bruge betinget formatering.
Her er trinene for at gøre dette:
- Vælg hele datasættet.
- Klik på fanen ‘Start’.
- Klik på indstillingen ‘Betinget formatering’ i gruppen Stilarter.
- Klik på ‘Ny regel’ i rullemenuen.
- Klik på ‘Brug en formel til at bestemme, hvilke celler der skal formateres’ i dialogboksen ‘Ny formateringsregel’.
- Indtast formlen i formelfeltet: =$A1=$B1
- Klik på knappen Format, og angiv det format, du vil anvende på de matchende celler.
- Klik på OK.
Dette vil fremhæve alle de celler, hvor navnene er de samme i hver række.
Sammenlign to kolonner og fremhæv overensstemmelser
Hvis du vil sammenligne to kolonner og fremhæve matchende data, kan du bruge duplikeringsfunktionaliteten i betinget formatering.
Bemærk, at dette er anderledes end det, vi har set, når vi sammenligner hver række. I dette tilfælde foretager vi ikke en sammenligning række for række.
Eksempel: Sammenlign to kolonner og fremhæv matchende data
Ofte får du datasæt, hvor der er overenskomster, men disse er måske ikke i samme række.
Som vist nedenfor:
Bemærk, at listen i kolonne A er større end den i B. Der er også nogle navne i begge lister, men ikke i samme række (f.eks. IBM, Adobe, Walmart).
Hvis du vil fremhæve alle de matchende virksomhedsnavne, kan du gøre det ved hjælp af betinget formatering.
Her er trinene til at gøre dette:
- Vælg hele datasættet.
- Klik på fanen Start.
- Klik på indstillingen ‘Betinget formatering’ i gruppen Stilarter.
- Hold markøren på indstillingen Markér celleregler.
- Klik på Duplikér værdier.
- Sørg for, at ‘Duplikér’ er markeret i dialogboksen Duplikér værdier.
- Specificer formateringen.
- Klik på OK.
Overstående trin vil give dig resultatet som vist nedenfor.
Bemærk: Reglen om betinget formatering af duplikerede værdier er ikke stempelfølsom. Så “Apple” og “apple” betragtes som det samme og vil blive fremhævet som dubletter.
Eksempel: Hvis du vil fremhæve de navne, der findes i den ene liste og ikke i den anden, kan du også bruge betinget formatering til dette.
- Vælg hele datasættet.
- Klik på fanen Start.
- Klik på fanen Start.
- I gruppen Stilarter skal du klikke på indstillingen ‘Betinget formatering’.
- Hold markøren på indstillingen Markér celleregler.
- Klik på Duplikér værdier.
- Sørg for, at ‘Unik’ er valgt i dialogboksen Duplikér værdier.
- Specificer formateringen.
- Klik på OK.
Dette vil give dig resultatet som vist nedenfor. Det fremhæver alle de celler, der har et navn, som ikke findes på den anden liste.
Sammenlign to kolonner og find manglende datapunkter
Hvis du vil identificere, om et datapunkt fra den ene liste findes i den anden liste, skal du bruge opslagsformlerne.
Sæt, du har et datasæt som vist nedenfor, og du vil identificere virksomheder, der er til stede i kolonne A, men ikke i kolonne B,
For at gøre dette kan jeg bruge følgende VLOOKUP-formel.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Denne formel bruger VLOOKUP-funktionen til at kontrollere, om et virksomhedsnavn i A er til stede i kolonne B eller ej. Hvis det er til stede, returnerer den det pågældende navn fra kolonne B, ellers returnerer den en #N/A-fejl.
Disse navne, der returnerer #N/A-fejlen, er dem, der mangler i kolonne B.
ISERROR-funktionen ville returnere TRUE, hvis der er VLOOKUP-resultatet er en fejl, og FALSE, hvis der ikke er en fejl.
Hvis du vil have en liste over alle de navne, hvor der ikke er noget match, kan du filtrere resultatkolonnen for at få alle celler med TRUE.
Du kan også bruge MATCH-funktionen til at gøre det samme;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Note: Personligt foretrækker jeg at bruge Match-funktionen (eller kombinationen af INDEX/MATCH) i stedet for VLOOKUP. Jeg finder den mere fleksibel og kraftfuld. Du kan læse forskellen mellem Vlookup og Index/Match her.
Sammenlign to kolonner og hent de matchende data
Hvis du har to datasæt, og du ønsker at sammenligne elementer i den ene liste med den anden og hente det matchende datapunkt, skal du bruge lookup-formlerne.
Eksempel: F.eks. vil jeg i nedenstående liste hente markedsvurderingsværdien for kolonne 2. For at gøre dette skal jeg slå denne værdi op i kolonne 1 og derefter hente den tilsvarende markedsvurderingsværdi.
Nedenfor er den formel, der gør dette:
=VLOOKUP(D2,$A:$B,2,0)
eller
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Eksempel: Hvis du får et datasæt, hvor der er en mindre forskel på navnene i de to kolonner, vil det ikke fungere at bruge de ovenfor viste opslagsformler.
Disse opslagsformler har brug for et nøjagtigt match for at give det rigtige resultat. Der findes en mulighed for tilnærmelsesvis match i VLOOKUP- eller MATCH-funktionen, men den kan ikke bruges her.
Sæt, at du har datasættet som vist nedenfor. Bemærk, at der er navne, som ikke er fuldstændige i kolonne 2 (f.eks. JPMorgan i stedet for JPMorgan Chase og Exxon i stedet for ExxonMobil).
I et sådant tilfælde kan du bruge et partielt opslag ved at bruge jokertegn.
Følgende formel vil give er det rigtige resultat i dette tilfælde:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
eller
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
I ovenstående eksempel er stjernen (*) et wildcard-tegn, som kan repræsentere et vilkårligt antal tegn. Når opslagsværdien er flankeret med den på begge sider, vil enhver værdi i kolonne 1, som indeholder opslagsværdien i kolonne 2, blive betragtet som et match.
For eksempel vil *Exxon* være et match for ExxonMobil (da * kan repræsentere et vilkårligt antal tegn).
Du kan også lide følgende Excel-tips & Vejledninger:
- Sådan sammenligner du to Excel-ark (for forskelle)
- Sådan fremhæver du tomme celler i Excel.
- Highlight EVERY Other ROW in Excel.
- Excel Advanced Filter: En komplet guide med eksempler.
- Highlight Rows Based on a Cell Value in Excel.