Hur man jämför två kolumner i Excel och tar bort dubbletter (markera, färglägga, flytta)

Den här artikeln kommer att ta dig cirka 10 minuter att läsa. Under de kommande 5 minuterna kan du enkelt jämföra två kolumner i Excel och ta reda på om det finns dubbletter i dem, ta bort dem eller markera dem i färg. Så, det är dags!

Excel är en väldigt kraftfull och riktigt cool applikation för att skapa och bearbeta stora mängder data. Om du har flera arbetsböcker med data (eller bara en stor tabell), så vill du förmodligen jämföra 2 kolumner, hitta dubbletter av värden och sedan göra något med dem, till exempel ta bort, markera eller rensa innehållet. Kolumner kan vara i samma tabell, vara intilliggande eller inte intill varandra, kan vara placerade på två olika ark eller till och med i olika böcker.

Föreställ dig att vi har 2 kolumner med personers namn – 5 namn per kolumn A och 3 namn i en kolumn B. Du måste jämföra namnen i dessa två kolumner och hitta dubbletter. Som du förstår är detta fiktiva uppgifter, enbart tagna till exempel. I riktiga tabeller har vi att göra med tusentals eller till och med tiotusentals poster.

Alternativ A: båda kolumnerna finns på samma ark. Till exempel en kolumn A och kolumn B.

Alternativ B: Kolumnerna finns på olika ark. Till exempel en kolumn A på arket Sheet2 och kolumn A på arket Sheet3.

Excel 2013, 2010 och 2007 har ett inbyggt verktyg Ta bort dubbletter (Ta bort dubbletter) men den är maktlös i den här situationen eftersom den inte kan jämföra data i 2 kolumner. Dessutom kan den bara ta bort dubbletter. Det finns inga andra alternativ som att markera eller ändra färger. Och peka!

Därefter kommer jag att visa dig de möjliga sätten att jämföra två kolumner i Excel, vilket gör att du kan hitta och ta bort dubbletter av poster.

• Jämför två kolumner och leta efter dubbletter med hjälp av formler
  
  • Alternativ A: båda kolumnerna finns på samma ark
  • Alternativ B: Kolumnerna finns på olika ark eller i olika arbetsböcker
• Bearbetning av hittade dubbletter
  
  • Visa endast dubbletter av rader i kolumn A
  • Ändra färg eller markera hittade dubbletter
  • Ta bort dubbletter från första kolumnen

Jämför två kolumner i Excel och hitta dubbletter med hjälp av formler

Alternativ A: båda kolumnerna finns på samma ark

1. I den första tomma cellen (i vårt exempel är detta cell C1) skriver vi följande formel:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   I vår formel A1 detta är den första cellen i den första kolumnen vi ska jämföra. $B$1 и $B$10000 dessa är adresserna till de första och sista cellerna i den andra kolumnen, med vilka vi kommer att utföra jämförelsen. Notera de absoluta referenserna – kolumnbokstäver och radnummer föregås av ett dollartecken ($). Jag använder absoluta referenser så att celladresser förblir desamma när jag kopierar formler.

   Om du vill hitta dubbletter i en kolumn B, ändra referenserna så att formeln ser ut så här:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Istället "Endast"Och"duplicera» Du kan skriva dina egna etiketter, till exempel «Hittades inte"Och"Hittades", eller lämna bara"duplicera' och ange ett blanksteg istället för det andra värdet. I det senare fallet kommer cellerna för vilka inga dubbletter hittas att förbli tomma, och jag tror att denna representation av data är mest bekväm för vidare analys.

2. Låt oss nu kopiera vår formel till alla celler i kolumnen C, ända ner till den nedre raden, som innehåller data i kolumnen A. För att göra detta, flytta muspekaren till det nedre högra hörnet av cellen C1, kommer pekaren att ha formen av ett svart hårkors, som visas på bilden nedan:Klicka och håll ned vänster musknapp och dra ramens kant nedåt, markera alla celler där du vill infoga formeln. När alla nödvändiga celler är markerade släpper du musknappen:

   

Dricks: I stora tabeller går det snabbare att kopiera formeln om du använder kortkommandon. Markera en cell C1 och tryck Ctrl + C (för att kopiera formeln till urklipp), klicka sedan på Ctrl + Skift + Slut (för att markera alla icke-tomma celler i kolumn C) och tryck slutligen Ctrl + V (för att infoga formeln i alla markerade celler).

3. Bra, nu är alla dubbletter av värden markerade som "duplicera':

Alternativ B: två kolumner finns på olika ark (i olika arbetsböcker)

1. I den första cellen i den första tomma kolumnen på kalkylbladet Sheet2 (i vårt fall är det kolumn B) ange följande formel:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Här Sheet3 är namnet på arket där den andra kolumnen finns, och $ A $ 1: $ A $ 10000 är celladresser från 1:a till sist i denna 2:a kolumn.

2. Kopiera formeln till alla celler i en kolumn B (samma som alternativ A).
3. Vi får detta resultat:

Bearbetning av hittade dubbletter

Bra, vi har hittat poster i den första kolumnen som också finns i den andra kolumnen. Nu måste vi göra något med dem. Att gå igenom alla dubbletter i en tabell manuellt är ganska ineffektivt och tar för mycket tid. Det finns bättre sätt.

Visa endast dubbletter av rader i kolumn A

Om dina kolumner inte har rubriker måste du lägga till dem. För att göra detta, placera markören på siffran som representerar den första raden, och den kommer att förvandlas till en svart pil, som visas i bilden nedan:

Högerklicka och välj från snabbmenyn Införande (Föra in):

Ge namn till kolumnerna, till exempel "Namn "Och"Duplicera?» Öppna sedan fliken Data (Data) och tryck Filter (Filtrera):

Klicka sedan på den lilla grå pilen bredvid "Duplicera?« för att öppna filtermenyn; avmarkera alla objekt i den här listan utom dupliceraoch tryck på OK.

Det är allt, nu ser du bara de elementen i kolumnen А, som dupliceras i kolumnen В. Det finns bara två sådana celler i vår träningstabell, men som du förstår kommer det i praktiken att finnas många fler av dem.

För att visa alla rader i en kolumn igen Аklickar du på filtersymbolen i kolumnen В, som nu ser ut som en tratt med en liten pil, och välj Markera alla (Välj alla). Eller så kan du göra detsamma genom menyfliksområdet genom att klicka Data (Data) > Välj och filtrera (Sortera och filtrera) > Rensa (Rensa) som visas i skärmdumpen nedan:

Ändra färg eller markera hittade dubbletter

Om anteckningarna "duplicera" räcker inte för dina syften och du vill markera dubbletter av celler med en annan typsnittsfärg, fyllningsfärg eller någon annan metod...

I det här fallet filtrerar du dubbletterna enligt ovan, markerar alla filtrerade celler och klickar Ctrl + 1för att öppna dialogrutan Formatera celler (cellformat). Som ett exempel, låt oss ändra fyllningsfärgen för celler i rader med dubbletter till ljusgul. Naturligtvis kan du ändra fyllningsfärgen med verktyget Fyll fliken (Fyllfärg). Hem (Hem) men dialogruta fördel Formatera celler (Cell Format) genom att du kan konfigurera alla formateringsalternativ samtidigt.

Nu kommer du definitivt inte att missa några celler med dubbletter:

Ta bort dubbletter av värden från den första kolumnen

Filtrera tabellen så att endast celler med dubbletter av värden visas och välj dessa celler.

Om de två kolumnerna du jämför är på olika ark, det vill säga i olika tabeller, högerklicka på det valda området och välj Radera rad (Ta bort rad):

Presse OKnär Excel ber dig bekräfta att du verkligen vill ta bort hela arkraden och sedan rensa filtret. Som du kan se finns bara rader med unika värden kvar:

Om 2 kolumner finns på samma ark, nära varandra (intill) eller inte nära varandra (inte intill), då blir processen att ta bort dubbletter lite mer komplicerad. Vi kan inte ta bort hela raden med dubbletter av värden, eftersom detta tar bort cellerna från den andra kolumnen också. Så att bara lämna unika poster i en kolumn А, gör det här:

1. Filtrera tabellen för att bara visa dubbletter av värden och välj dessa celler. Högerklicka på dem och välj från snabbmenyn Rensa innehållet (tydligt innehåll).
2. Rengör filtret.
3. Markera alla celler i en kolumn А, med början från cellen A1 ända ner till botten som innehåller data.
4. Klicka på Data (Data) och tryck Sortera A till Z (Sortera från A till Ö). Välj i dialogrutan som öppnas Fortsätt med det aktuella valet (Sortera inom det angivna urvalet) och klicka på knappen Svart (Sortering):
5. Ta bort kolumnen med formeln, du behöver den inte längre, från och med nu har du bara unika värden.
6. Det var allt, nu kolumnen А innehåller endast unika data som inte finns i kolumnen В:

Som du kan se är det inte så svårt att ta bort dubbletter från två kolumner i Excel med formler.