Sådan sammenlignes to kolonner i Excel (for kampe og forskelle)

Se video - Sammenlign to kolonner i Excel for kampe og forskelle

Den ene forespørgsel, som jeg får meget, er - 'hvordan sammenligner jeg to kolonner i Excel?'.

Dette kan gøres på mange forskellige måder, og metoden til brug vil afhænge af datastrukturen og hvad brugeren ønsker af den.

For eksempel vil du måske 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 en kolonne og ikke i den anden) osv.

Da jeg bliver spurgt om dette så meget, besluttede jeg at skrive denne massive tutorial med det formål at dække de fleste (hvis ikke alle) mulige scenarier.

Hvis du finder dette nyttigt, skal du give det videre til andre Excel -brugere.

Bemærk, at teknikkerne til sammenligning af kolonner vist i denne vejledning ikke er de eneste.

Baseret på dit datasæt skal du muligvis ændre eller justere metoden. De grundlæggende principper ville imidlertid forblive de samme.

Hvis du tror, ​​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ække Match

Denne er den enkleste form for sammenligning. I dette tilfælde skal du foretage en række for række -sammenligning og identificere, hvilke rækker der har de samme data, og hvilke der ikke har.

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, har jeg brug for resultatet som "TRUE", og hvis det ikke matcher, så har jeg brug for resultatet som "FALSE".

Nedenstående formel ville gøre dette:

= A2 = B2

Eksempel: Sammenlign celler i samme række (ved hjælp af IF -formel)

Hvis du vil have 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.

= HVIS (A2 = B2, "Match", "Mismatch")

Bemærk: Hvis du vil gøre sammenligningen mellem store og små bogstaver, skal du bruge følgende IF -formel:

= HVIS (PRÆCIS (A2, B2), "Match", "Mismatch")

Med ovenstående formel ville 'IBM' og 'ibm' blive betragtet som to forskellige navne, og ovenstående formel ville returnere 'Uoverensstemmelse'.

Eksempel: Fremhæv 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 til at gøre dette:

1. Vælg hele datasættet.
2. Klik på fanen 'Hjem'.
3. Klik på indstillingen 'Betinget formatering' i gruppen Stilarter.
4. Klik på 'Ny regel' i rullemenuen.
5. I dialogboksen 'Ny formateringsregel' skal du klikke på 'Brug en formel til at bestemme, hvilke celler der skal formateres'.
6. Indtast formlen i formelfeltet: = $ A1 = $ B1
7. Klik på knappen Format, og angiv det format, du vil anvende på de matchende celler.
8. Klik på OK.

Dette vil fremhæve alle cellerne, hvor navnene er de samme i hver række.

Sammenlign to kolonner og fremhæv matches

Hvis du vil sammenligne to kolonner og fremhæve matchende data, kan du bruge den dobbelte funktionalitet 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 række for række -sammenligning.

Eksempel: Sammenlign to kolonner og fremhæv matchende data

Ofte får du datasæt, hvor der er kampe, men disse er muligvis ikke i samme række.

Noget som vist herunder:

Bemærk, at listen i kolonne A er større end den i B. Der er også nogle navne på begge lister, men ikke i samme række (f.eks. IBM, Adobe, Walmart).

Hvis du vil fremhæve alle de matchende firmanavne, kan du gøre det ved hjælp af betinget formatering.

Her er trinene til at gøre dette:

1. Vælg hele datasættet.
2. Klik på fanen Hjem.
3. Klik på indstillingen 'Betinget formatering' i gruppen Stilarter.
4. Hold markøren på indstillingen Fremhæv celleregler.
5. Klik på Duplicate Values.
6. Sørg for, at 'Duplikat' er valgt i dialogboksen Duplikatværdier.
7. Angiv formateringen.
8. Klik på OK.

Ovenstående trin ville give dig resultatet som vist nedenfor.

Bemærk: Reglen om betinget formatering af dubletter er ikke store og små bogstaver. Så 'Apple' og 'æble' betragtes som det samme og vil blive fremhævet som dubletter.

Eksempel: Sammenlign to kolonner og fremhæv uoverensstemmende data

Hvis du vil fremhæve de navne, der findes på den ene liste og ikke den anden, kan du også bruge den betingede formatering til dette.

1. Vælg hele datasættet.
2. Klik på fanen Hjem.
3. Klik på indstillingen 'Betinget formatering' i gruppen Stilarter.
4. Hold markøren på indstillingen Fremhæv celleregler.
5. Klik på Duplicate Values.
6. Sørg for, at 'Unikt' er valgt i dialogboksen Duplicate Values.
7. Angiv formateringen.
8. Klik på OK.

Dette vil give dig resultatet som vist nedenfor. Det fremhæver alle de celler, der har et navn, der ikke er til stede på den anden liste.

Sammenlign to kolonner og find manglende datapunkter

Hvis du vil identificere, om et datapunkt fra den ene liste er til stede på den anden liste, skal du bruge opslagsformlerne.

Antag, at 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.

= FEJL (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))

Denne formel bruger VLOOKUP -funktionen til at kontrollere, om et firmanavn i A findes i kolonne B eller ej. Hvis det er til stede, returnerer det navnet fra kolonne B, ellers returnerer det en #N/A fejl.

Disse navne, der returnerer #N/A -fejlen, er dem, der mangler i kolonne B.

FEJL -funktionen returnerer SAND, hvis der er VLOOKUP -resultatet er en fejl og FALSK, hvis det ikke er en fejl.

Hvis du vil have en liste over alle de navne, hvor der ikke er nogen match, kan du filtrere resultatkolonnen for at få alle celler med SAND.

Du kan også bruge MATCH -funktionen til at gøre det samme;

= IKKE (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))

Bemærk: Personligt foretrækker jeg at bruge Match -funktionen (eller kombinationen af ​​INDEX/MATCH) i stedet for VLOOKUP. Jeg finder det mere fleksibelt og kraftfuldt. Du kan læse forskellen mellem Vlookup og Index/Match her.

Sammenlign to kolonner, og træk de matchende data

Hvis du har to datasæt, og du vil sammenligne elementer i den ene liste med den anden og hente det matchende datapunkt, skal du bruge opslagsformlerne.

Eksempel: Træk de matchende data (nøjagtig)

For eksempel i nedenstående liste vil jeg hente markedsværdiansættelsesværdien for kolonne 2. For at gøre dette skal jeg slå denne værdi op i kolonne 1 og derefter hente den tilsvarende markedsværdiansættelsesværdi.

Nedenfor er formlen, der gør dette:

= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)

eller

= INDEX ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)

Eksempel: Træk de matchende data (delvist)

Hvis du får et datasæt, hvor der er en mindre forskel i navnene i de to kolonner, fungerer det ikke at bruge de ovenfor viste opslagsformler.

Disse opslagsformler har brug for et nøjagtigt match for at give det rigtige resultat. Der er en omtrentlig matchmulighed i VLOOKUP eller MATCH -funktionen, men det kan ikke bruges her.

Antag, at du har datasættet som vist nedenfor. Bemærk, at der er navne, der ikke er komplette 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 delvis opslag ved at bruge jokertegn.

Følgende formel vil give det rigtige resultat i dette tilfælde:

= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)

eller

= INDEX ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)

I eksemplet ovenfor er stjernen (*) et jokertegn, der kan repræsentere et vilkårligt antal tegn. Når opslagsværdien flankeres med den på begge sider, betragtes enhver værdi i kolonne 1, der indeholder opslagsværdien i kolonne 2, som en matchning.

For eksempel ville * Exxon * være et match for ExxonMobil (da * kan repræsentere et vilkårligt antal tegn).

Du kan også lide følgende Excel -tips og vejledninger:

• Sådan sammenlignes to Excel -ark (for forskelle)
• Sådan fremhæves tomme celler i Excel.
• Fremhæv ALLE andre rækker i Excel.
• Excel Advanced Filter: En komplet guide med eksempler.
• Fremhæv rækker baseret på en celleværdi i Excel.