Titta på video - Jämför två kolumner i Excel för matchningar och skillnader
Den enda frågan som jag får mycket är - "hur jämför jag två kolumner i Excel?".
Detta kan göras på många olika sätt, och metoden att använda beror på datastrukturen och vad användaren vill ha av den.
Till exempel kanske du 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 en kolumn och inte i den andra), etc.
Eftersom jag blir frågad om detta så mycket bestämde jag mig för att skriva denna massiva 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.
Observera att teknikerna för att jämföra kolumner som visas i den här självstudien inte är de enda.
Baserat på din dataset kan du behöva ändra eller justera metoden. De grundläggande principerna skulle dock förbli desamma.
Om du tror att det finns något som kan läggas till i denna handledning, låt mig veta i kommentarfältet
Jämför två kolumner för exakt radmatchning
Den här är den enklaste formen av jämförelse. I det här fallet måste du göra en rad för rad jämförelse och identifiera vilka rader som har samma data och vilka som inte har.
Exempel: Jämför celler i samma rad
Nedan finns en datauppsättning 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 matchar, så behöver jag resultatet som "FALSE".
Nedanstående formel skulle göra detta:
= A2 = B2
Exempel: Jämför celler i samma rad (med IF -formel)
Om du vill få ett mer beskrivande resultat kan du använda en enkel IF -formel för att returnera "Match" när namnen är desamma och "Mismatch" när namnen är olika.
= IF (A2 = B2, "Matchning", "Fel matchning")
Obs! Om du vill göra jämförelsen skiftlägeskänslig använder du följande IF -formel:
= OM (EXAKT (A2, B2), "Match", "Fel matchning")
Med formeln ovan skulle 'IBM' och 'ibm' betraktas som två olika namn och ovanstående formel skulle returnera 'Mismatch'.
Exempel: Markera rader med matchande data
Om du vill markera raderna som har matchande data (istället för att få resultatet i en separat kolumn) kan du göra det genom att använda Villkorlig formatering.
Här är stegen för att göra detta:
- Välj hela datauppsättningen.
- Klicka på fliken "Hem".
- Klicka på alternativet "Villkorlig formatering" i gruppen Stilar.
- Klicka på "Ny regel" i rullgardinsmenyn.
- Klicka på "Använd en formel för att avgöra vilka celler som ska formateras" i dialogrutan "Ny formateringsregel".
- I formelfältet anger du formeln: = $ A1 = $ B1
- Klicka på knappen Format och ange formatet du vill använda på matchande celler.
- Klicka på OK.
Detta markerar 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 dubblettfunktionen i villkorlig formatering.
Observera att detta är annorlunda än vad vi har sett när vi jämför varje rad. I det här fallet kommer vi inte att göra en rad för rad jämförelse.
Exempel: Jämför två kolumner och markera matchande data
Ofta får du datamängder där det finns matchningar, men dessa kanske inte finns på samma rad.
Något som visas nedan:
Observera att listan i kolumn A är större än den i B. Även vissa namn finns i båda listorna, men inte på samma rad (t.ex. IBM, Adobe, Walmart).
Om du vill markera alla matchande företagsnamn kan du göra det med villkorlig formatering.
Här är stegen för att göra detta:
- Välj hela datamängden.
- Klicka på fliken Hem.
- Klicka på alternativet "Villkorlig formatering" i gruppen Stilar.
- Håll markören på alternativet Markera cellregler.
- Klicka på Dubbla värden.
- Kontrollera att duplicera är markerat i dialogrutan Dubbla värden.
- Ange formateringen.
- Klicka på OK.
Stegen ovan skulle ge dig resultatet enligt nedan.
Obs! Regel för dubbletter av villkorlig formatering är inte skiftlägeskänslig. Så "Apple" och "äpple" anses vara desamma och skulle framhävas som dubbletter.
Exempel: Jämför två kolumner och markera felaktiga data
Om du vill markera namnen som finns i en lista och inte den andra kan du också använda den villkorade formateringen för detta.
- Välj hela datamängden.
- Klicka på fliken Hem.
- Klicka på alternativet "Villkorlig formatering" i gruppen Stilar.
- Håll markören på alternativet Markera cellregler.
- Klicka på Dubbla värden.
- Kontrollera att "Unikt" är valt i dialogrutan Dubbla värden.
- Ange formateringen.
- Klicka på OK.
Detta ger dig resultatet enligt nedan. Det markerar alla celler som har ett namn som inte finns på 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.
Anta att du har en 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.
= FEL (VLOOKUP (A2, $ B $ 2: $ B $ 10,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 det att returnera det namnet från kolumn B, annars kommer det att returnera ett #N/A -fel.
Dessa namn som returnerar #N/A -felet är de som saknas i kolumn B.
ISERROR -funktionen returnerar 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å alla celler med TRUE.
Du kan också använda MATCH -funktionen för att göra detsamma;
= NOT (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Obs: Personligen föredrar jag att använda Match -funktionen (eller kombinationen av INDEX/MATCH) istället för VLOOKUP. Jag tycker att det är mer flexibelt och kraftfullt. Du kan läsa skillnaden mellan Vlookup och Index/Match här.
Jämför två kolumner och dra matchande data
Om du har två datamängder och du vill jämföra objekt i en lista med den andra och hämta matchande datapunkt måste du använda uppslagsformlerna.
Exempel: Dra matchande data (exakt)
Till exempel i listan nedan vill jag hämta marknadsvärderingsvärdet för kolumn 2. För att göra detta måste jag leta upp det värdet i kolumn 1 och sedan hämta motsvarande marknadsvärderingsvärde.
Nedan är formeln som gör detta:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
eller
= INDEX ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Exempel: Dra matchande data (delvis)
Om du får en datauppsättning där det finns en mindre skillnad i namnen i de två kolumnerna, kommer inte ovanstående sökformler att fungera.
Dessa uppslagsformler behöver en exakt matchning för att ge rätt resultat. Det finns ett ungefärligt matchningsalternativ i VLOOKUP- eller MATCH -funktionen, men det kan inte användas här.
Anta att du har datauppsättningen enligt nedan. Observera att det finns namn som inte är fullständiga i kolumn 2 (som 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 med hjälp av jokertecken.
Följande formel ger det rätta resultatet i detta fall:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
eller
= INDEX ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
I exemplet ovan är asterisken (*) ett jokertecken som kan representera valfritt antal tecken. När uppslagsvärdet flankeras med det på båda sidor, skulle alla värden i kolumn 1 som innehåller uppslagsvärdet i kolumn 2 betraktas som en matchning.
Till exempel skulle * Exxon * vara en matchning för ExxonMobil (eftersom * kan representera valfritt antal tecken).
Du kanske också gillar följande Excel -tips och självstudier:
- Hur man jämför två Excel -ark (för skillnader)
- Så här markerar du tomma celler i Excel.
- Markera VARJE ÖVR RAD i Excel.
- Excel Advanced Filter: En komplett guide med exempel.
- Markera rader baserat på ett cellvärde i Excel.
