Se video - Sammenlign to kolonner i Excel for treff og forskjeller
Den eneste spørringen jeg får mye er - 'hvordan sammenligne to kolonner i Excel?'.
Dette kan gjøres på mange forskjellige måter, og metoden som skal brukes vil avhenge av datastrukturen og hva brukeren ønsker av den.
For eksempel kan det være lurt å sammenligne to kolonner og finne eller markere alle matchende datapunkter (som er i begge kolonnene), eller bare forskjellene (hvor et datapunkt er i en kolonne og ikke i den andre), etc.
Siden jeg blir spurt om dette så mye, bestemte jeg meg for å skrive denne massive opplæringen med den hensikt å dekke de fleste (om ikke alle) mulige scenarier.
Hvis du synes dette er nyttig, kan du gi det videre til andre Excel -brukere.
Vær oppmerksom på at teknikkene for å sammenligne kolonner vist i denne opplæringen ikke er de eneste.
Basert på datasettet ditt, må du kanskje endre eller justere metoden. Imidlertid vil de grunnleggende prinsippene forbli de samme.
Hvis du tror det er noe som kan legges til i denne opplæringen, gi meg beskjed i kommentarfeltet
Sammenlign to kolonner for eksakt radmatch
Denne er den enkleste formen for sammenligning. I dette tilfellet må du sammenligne rad for rad og identifisere hvilke rader som har de samme dataene og hvilke som ikke har det.
Eksempel: Sammenlign celler i samme rad
Nedenfor er et datasett der jeg må kontrollere om navnet i kolonne A er det samme i kolonne B eller ikke.
Hvis det er en match, trenger jeg resultatet som "TRUE", og hvis det ikke stemmer, trenger jeg resultatet som "FALSE".
Formelen nedenfor vil gjøre dette:
= A2 = B2
Eksempel: Sammenlign celler i samme rad (ved hjelp av IF -formel)
Hvis du vil få et mer beskrivende resultat, kan du bruke en enkel IF -formel for å returnere "Match" når navnene er de samme og "Mismatch" når navnene er forskjellige.
= HVIS (A2 = B2, "Match", "Mismatch")
Merk: Hvis du vil gjøre sammenligningen mellom store og små bokstaver, bruk følgende IF -formel:
= HVIS (EKSAKT (A2, B2), "Match", "Mismatch")
Med formelen ovenfor vil 'IBM' og 'ibm' bli betraktet som to forskjellige navn, og formelen ovenfor vil returnere 'Mismatch'.
Eksempel: Marker rader med matchende data
Hvis du vil markere radene som har matchende data (i stedet for å få resultatet i en egen kolonne), kan du gjøre det ved å bruke betinget formatering.
Her er trinnene for å gjøre dette:
- Velg hele datasettet.
- Klikk på "Hjem" -fanen.
- Klikk på alternativet "Betinget formatering" i gruppen Stiler.
- Klikk på "Ny regel" fra rullegardinmenyen.
- I dialogboksen "Ny formateringsregel" klikker du på "Bruk en formel for å bestemme hvilke celler som skal formateres".
- Skriv inn formelen i formelfeltet: = $ A1 = $ B1
- Klikk på Format -knappen og angi formatet du vil bruke på de matchende cellene.
- Klikk OK.
Dette vil markere alle cellene der navnene er de samme i hver rad.
Sammenlign to kolonner og uthevingskamper
Hvis du vil sammenligne to kolonner og markere matchende data, kan du bruke duplikatfunksjonaliteten i betinget formatering.
Vær oppmerksom på at dette er annerledes enn det vi har sett når vi sammenligner hver rad. I dette tilfellet vil vi ikke gjøre en rad for rad sammenligning.
Eksempel: Sammenlign to kolonner og fremhev matchende data
Ofte får du datasett der det er treff, men disse er kanskje ikke i samme rad.
Noe som vist nedenfor:
Vær oppmerksom på at listen i kolonne A er større enn den i B. Også noen navn er der i begge listene, men ikke i samme rad (for eksempel IBM, Adobe, Walmart).
Hvis du vil markere alle matchende firmanavn, kan du gjøre det ved å bruke betinget formatering.
Her er trinnene for å gjøre dette:
- Velg hele datasettet.
- Klikk på kategorien Hjem.
- Klikk på alternativet "Betinget formatering" i gruppen Stiler.
- Hold markøren på alternativet Marker celleregler.
- Klikk på Duplicate Values.
- I dialogboksen Dupliser verdier, må du kontrollere at 'Dupliser' er valgt.
- Spesifiser formateringen.
- Klikk OK.
Trinnene ovenfor vil gi deg resultatet som vist nedenfor.
Merk: Duplikatregel for betinget formatering er ikke mellomstore og små bokstaver. Så "Apple" og "eple" regnes som det samme og vil bli fremhevet som dubletter.
Eksempel: Sammenlign to kolonner og fremhev feilmatchede data
Hvis du vil markere navnene som er tilstede i den ene listen og ikke den andre, kan du også bruke den betingede formateringen for dette.
- Velg hele datasettet.
- Klikk på kategorien Hjem.
- Klikk på alternativet "Betinget formatering" i gruppen Stiler.
- Hold markøren på alternativet Marker celleregler.
- Klikk på Duplicate Values.
- Kontroller at "Unikt" er valgt i dialogboksen Dupliser verdier.
- Spesifiser formateringen.
- Klikk OK.
Dette vil gi deg resultatet som vist nedenfor. Det markerer alle cellene som har et navn som ikke er tilstede på den andre listen.
Sammenlign to kolonner og finn manglende datapunkter
Hvis du vil identifisere om et datapunkt fra den ene listen er tilstede i den andre listen, må du bruke oppslagsformlene.
Anta at du har et datasett som vist nedenfor, og du vil identifisere selskaper som er tilstede i kolonne A, men ikke i kolonne B,
For å gjøre dette kan jeg bruke følgende VLOOKUP -formel.
= FEIL (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))
Denne formelen bruker VLOOKUP -funksjonen til å kontrollere om et firmanavn i A er tilstede i kolonne B eller ikke. Hvis det er tilstede, returnerer det navnet fra kolonne B, ellers returnerer det en #N/A -feil.
Disse navnene som returnerer #N/A -feilen er de som mangler i kolonne B.
ISERROR -funksjonen vil returnere TRUE hvis det er VLOOKUP -resultatet er en feil og FALSE hvis det ikke er en feil.
Hvis du vil få en liste over alle navnene der det ikke er samsvar, kan du filtrere resultatkolonnen for å få alle cellene med TRUE.
Du kan også bruke MATCH -funksjonen til å gjøre det samme;
= IKKE (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Merk: Personlig foretrekker jeg å bruke Match -funksjonen (eller kombinasjonen INDEX/MATCH) i stedet for VLOOKUP. Jeg synes det er mer fleksibelt og kraftfullt. Du kan lese forskjellen mellom Vlookup og Index/Match her.
Sammenlign to kolonner og trekk de matchende dataene
Hvis du har to datasett og du vil sammenligne elementer i den ene listen med den andre og hente det matchende datapunktet, må du bruke oppslagsformlene.
Eksempel: Trekk de matchende dataene (nøyaktig)
For eksempel, i listen nedenfor, vil jeg hente markedsverdivurderingsverdien for kolonne 2. For å gjøre dette må jeg slå opp den verdien i kolonne 1 og deretter hente den tilsvarende markedsverdien.
Nedenfor er formelen som gjør dette:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
eller
= INDEKS ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Eksempel: Trekk de matchende dataene (delvis)
Hvis du får et datasett der det er en liten forskjell i navnene i de to kolonnene, vil ikke bruk av de ovenfor viste oppslagsformlene fungere.
Disse oppslagsformlene trenger en eksakt samsvar for å gi det riktige resultatet. Det er et omtrentlig matchalternativ i VLOOKUP eller MATCH -funksjonen, men det kan ikke brukes her.
Anta at du har datasettet som vist nedenfor. Vær oppmerksom på at det er navn som ikke er fullstendige i kolonne 2 (for eksempel JPMorgan i stedet for JPMorgan Chase og Exxon i stedet for ExxonMobil).
I et slikt tilfelle kan du bruke et delvis oppslag ved å bruke jokertegn.
Følgende formel vil gi det riktige resultatet i dette tilfellet:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
eller
= INDEKS ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
I eksemplet ovenfor er stjernen (*) et jokertegn som kan representere et hvilket som helst antall tegn. Når oppslagsverdien er flankert med den på begge sider, vil enhver verdi i kolonne 1 som inneholder oppslagsverdien i kolonne 2 bli betraktet som en samsvar.
For eksempel vil * Exxon * være en match for ExxonMobil (da * kan representere et hvilket som helst antall tegn).
Du kan også like følgende Excel -tips og opplæringsprogrammer:
- Hvordan sammenligne to Excel -ark (for forskjeller)
- Hvordan markere tomme celler i Excel.
- Uthev hver annen rad i Excel.
- Excel Advanced Filter: En komplett guide med eksempler.
- Uthev rader basert på en celleverdi i Excel.
