Hur man jämför två kolumner i Excel (för matchningar och skillnader)
Video – Jämför två kolumner i Excel för matchningar och skillnader
En fråga som jag får ofta är: ”Hur jämför man två kolumner i Excel?”.
Detta kan göras på många olika sätt, och vilken metod som ska användas beror på datastrukturen och vad användaren vill ha ut av den.
Du kanske till exempel vill jämföra två kolumner och hitta eller markera alla matchande datapunkter (som finns i båda kolumnerna), eller bara skillnaderna (där en datapunkt finns i den ena kolumnen och inte i den andra), osv.
Då jag får så många frågor om detta bestämde jag mig för att skriva denna omfattande handledning med avsikt att täcka de flesta (om inte alla) möjliga scenarier.
Om du tycker att detta är användbart, skicka det vidare till andra Excel-användare.
Denna handledning täcker:
Notera att de tekniker för att jämföra kolumner som visas i den här handledningen inte är de enda.
Baserat på din datamängd kan du behöva ändra eller justera metoden. De grundläggande principerna förblir dock desamma.
Om du tycker att det finns något som kan läggas till den här handledningen, låt mig veta det i kommentarsfältet
Jämför två kolumner för exakt radmatchning
Detta är den enklaste formen av jämförelse. I det här fallet måste du göra en jämförelse rad för rad och identifiera vilka rader som har samma data och vilka som inte har det.
Exempel: Jämför celler i samma rad
Nedan följer en datamängd där jag måste kontrollera om namnet i kolumn A är detsamma i kolumn B eller inte.
Om det finns en matchning behöver jag resultatet som ”TRUE”, och om det inte finns en matchning behöver jag resultatet som ”FALSE”.
Den nedanstående formeln skulle göra detta:
=A2=B2
Exempel: Om du vill få ett mer beskrivande resultat kan du använda en enkel IF-formel som returnerar ”Match” när namnen är desamma och ”Mismatch” när namnen är olika.
=IF(A2=B2,"Match","Mismatch")
Notera: Om du vill göra jämförelsen skiftlägeskänslig kan du använda följande IF-formel:
=IF(EXACT(A2,B2),"Match","Mismatch")
Med ovanstående formel skulle ”IBM” och ”ibm” betraktas som två olika namn och formeln skulle ge ”Mismatch”.
Exempel:
Om du vill markera de rader som har matchande data (i stället för att få resultatet i en separat kolumn) kan du göra det med hjälp av villkorlig formatering.
Så här gör du:
- Välj hela datasetet.
- Klicka på fliken ”Hem”.
- I gruppen Stilar klickar du på alternativet ”Villkorlig formatering”.
- I rullgardinsmenyn klickar du på ”Ny regel”.
- I dialogrutan ”Ny formateringsregel” klickar du på ”Använd en formel för att bestämma vilka celler som ska formateras”.
- I formelfältet anger du formeln: =$A1=$B1
- Klicka på knappen Formatera och ange vilket format du vill tillämpa på de matchande cellerna.
- Klicka på OK.
Detta kommer att markera alla celler där namnen är desamma i varje rad.
Jämför två kolumner och markera matchningar
Om du vill jämföra två kolumner och markera matchande data kan du använda duplikatfunktionaliteten i villkorlig formatering.
Bemärk att detta skiljer sig från vad vi har sett när vi jämförde varje rad. I det här fallet kommer vi inte att göra en jämförelse rad för rad.
Exempel: Jämför två kolumner och markera matchande data
Ofta får du dataset där det finns matchningar, men dessa kanske inte finns i samma rad.
Som visas nedan:
Notera att listan i kolumn A är större än den i B. Vissa namn finns också i båda listorna, men inte i samma rad (t.ex. IBM, Adobe, Walmart).
Om du vill markera alla matchande företagsnamn kan du göra det med hjälp av villkorlig formatering.
Sedan följer stegen för att göra detta:
- Välj hela datamängden.
- Klicka på fliken Hem.
- I gruppen Stilar klickar du på alternativet ”Villkorlig formatering”.
- Hoppa markören på alternativet Markera cellregler.
- Klicka på Duplicera värden.
- I dialogrutan Duplicera värden ser du till att ”Duplicera” är markerat.
- Specificera formateringen.
- Klicka på OK.
Ovanstående steg ger det resultat som visas nedan.
Notera: Regeln för duplicering av villkorlig formatering är inte skiftlägeskänslig. Så ”Apple” och ”apple” anses vara samma sak och skulle markeras som dubbletter.
Exempel:
Om du vill markera de namn som finns i den ena listan och inte i den andra kan du använda villkorlig formatering även för detta.
- Välj hela datamängden.
- Klicka på fliken Hem.
- I gruppen Styles klickar du på alternativet ”Conditional Formatting”.
- Hoppa markören på alternativet Markera cellregler.
- Klicka på Duplicera värden.
- I dialogrutan Duplicera värden kontrollerar du att ”Unik” är markerat.
- Specificera formateringen.
- Klicka på OK.
Detta ger dig resultatet som visas nedan. Det markerar alla celler som har ett namn som inte finns i den andra listan.
Jämför två kolumner och hitta saknade datapunkter
Om du vill identifiera om en datapunkt från en lista finns i den andra listan måste du använda uppslagsformlerna.
Antag att du har ett dataset som visas nedan och du vill identifiera företag som finns i kolumn A men inte i kolumn B,
För att göra detta kan jag använda följande VLOOKUP-formel.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Denna formel använder VLOOKUP-funktionen för att kontrollera om ett företagsnamn i A finns i kolumn B eller inte. Om det finns kommer den att returnera det namnet från kolumn B, annars kommer den att returnera ett #N/A-fel.
Dessa namn som returnerar #N/A-felet är de som saknas i kolumn B.
ISERROR-funktionen skulle returnera TRUE om det finns VLOOKUP-resultatet är ett fel och FALSE om det inte är ett fel.
Om du vill få en lista med alla namn där det inte finns någon matchning kan du filtrera resultatkolumnen för att få fram alla celler med TRUE.
Du kan också använda MATCH-funktionen för att göra detsamma;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Anmärkning: Personligen föredrar jag att använda Match-funktionen (eller kombinationen av INDEX/MATCH) istället för VLOOKUP. Jag tycker att den är mer flexibel och kraftfull. Du kan läsa skillnaden mellan Vlookup och Index/Match här.
Jämför två kolumner och hämta matchande data
Om du har två datamängder och vill jämföra objekt i den ena listan med den andra och hämta den matchande datapunkten måste du använda lookup-formlerna.
Exempel: Exempel: Hämta matchande data (exakt)
I nedanstående lista vill jag till exempel hämta marknadsvärderingsvärdet för kolumn 2. För att göra detta måste jag söka upp det värdet i kolumn 1 och sedan hämta motsvarande marknadsvärderingsvärde.
Nedan följer formeln som gör detta:
=VLOOKUP(D2,$A:$B,2,0)
eller
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Exempel: Om du får ett dataset där det finns en liten skillnad mellan namnen i de två kolumnerna, kommer det inte att fungera att använda ovanstående formler.
De här formlerna behöver en exakt matchning för att ge rätt resultat. Det finns ett alternativ för ungefärlig matchning i VLOOKUP- eller MATCH-funktionen, men det kan inte användas här.
Antag att du har datamängden som visas nedan. Observera att det finns namn som inte är fullständiga i kolumn 2 (t.ex. JPMorgan istället för JPMorgan Chase och Exxon istället för ExxonMobil).
I ett sådant fall kan du använda en partiell sökning genom att använda jokertecken.
Följande formel ger rätt resultat i detta fall:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
eller
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
I exemplet ovan är asterisken (*) ett jokertecken som kan representera valfritt antal tecken. När uppslagsvärdet flankeras av det på båda sidor, anses varje värde i kolumn 1 som innehåller uppslagsvärdet i kolumn 2 vara en matchning.
Till exempel skulle *Exxon* vara en matchning för ExxonMobil (eftersom * kan representera ett obegränsat antal tecken).
Du kanske också gillar följande Excel Tips & Tutorials:
- Hur man jämför två Excel-ark (för skillnader)
- Hur man markerar tomma celler i Excel.
- Highlight EVERY Other ROW in Excel.
- Excel Advanced Filter: En komplett guide med exempel.
- Highlight Rows Based on a Cell Value in Excel.