Sådan sammenligner du to kolonner i Excel og fjerner dubletter (fremhæv, farvelægger, flyt)

Denne artikel vil tage dig omkring 10 minutter at læse. I de næste 5 minutter kan du nemt sammenligne to kolonner i Excel og finde ud af, om der er dubletter i dem, slette dem eller fremhæve dem i farver. Så er tiden kommet!

Excel er en meget kraftfuld og virkelig cool applikation til at skabe og behandle store mængder data. Hvis du har flere projektmapper med data (eller bare en kæmpe tabel), så vil du sandsynligvis sammenligne 2 kolonner, finde dublerede værdier og derefter gøre noget med dem, for eksempel slette, fremhæve eller rydde indholdet. Kolonner kan være i samme tabel, være tilstødende eller ikke tilstødende, kan være placeret på 2 forskellige ark eller endda i forskellige bøger.

Forestil dig, at vi har 2 kolonner med folks navne – 5 navne pr. kolonne A og 3 navne i en kolonne B. Du skal sammenligne navnene i disse to kolonner og finde dubletter. Som du forstår, er der tale om fiktive data, udelukkende taget for eksempel. I rigtige tabeller har vi at gøre med tusinder eller endda titusindvis af poster.

Mulighed A: begge kolonner er på samme ark. For eksempel en kolonne A og kolonne B.

Mulighed B: Søjlerne er på forskellige ark. For eksempel en kolonne A på arket Sheet2 og kolonne A på arket Sheet3.

Excel 2013, 2010 og 2007 har et indbygget værktøj Fjern duplikater (Fjern dubletter), men den er magtesløs i denne situation, da den ikke kan sammenligne data i 2 kolonner. Desuden kan den kun fjerne dubletter. Der er ingen andre muligheder såsom fremhævning eller ændring af farver. Og point!

Dernæst vil jeg vise dig de mulige måder at sammenligne to kolonner i Excel, som giver dig mulighed for at finde og fjerne duplikerede poster.

• Sammenlign 2 kolonner og se efter dubletter ved hjælp af formler
  
  • Mulighed A: begge kolonner er på samme ark
  • Mulighed B: Kolonnerne er på forskellige ark eller i forskellige projektmapper
• Behandling af fundne dubletter
  
  • Vis kun duplikerede rækker i kolonne A
  • Skift farve eller fremhæv fundne dubletter
  • Fjern dubletter fra første kolonne

Sammenlign 2 kolonner i Excel og find duplikerede poster ved hjælp af formler

Mulighed A: begge kolonner er på samme ark

1. I den første tomme celle (i vores eksempel er dette celle C1) skriver vi følgende formel:

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

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

   

   I vores formel A1 dette er den første celle i den første kolonne, vi skal sammenligne. $B$1 и $B$10000 disse er adresserne på den første og sidste celle i den anden kolonne, som vi vil udføre sammenligningen med. Bemærk de absolutte referencer – kolonnebogstaver og rækkenumre er indledt med et dollartegn ($). Jeg bruger absolutte referencer, så celleadresser forbliver de samme ved kopiering af formler.

   Hvis du vil finde dubletter i en kolonne B, ændre referencerne, så formlen ser sådan ud:

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

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

   I stedet for "Kun"Og"Dupliker» Du kan skrive dine egne etiketter, for eksempel «Ikke fundet"Og"Fundet", eller forlad kun"Dupliker' og indtast et mellemrum i stedet for den anden værdi. I sidstnævnte tilfælde vil de celler, for hvilke der ikke findes dubletter, forblive tomme, og jeg tror, ​​at denne repræsentation af dataene er mest praktisk til yderligere analyse.

2. Lad os nu kopiere vores formel til alle cellerne i kolonnen C, helt ned til den nederste række, som indeholder dataene i kolonnen A. For at gøre dette skal du flytte musemarkøren til nederste højre hjørne af cellen C1, vil markøren have form af et sort trådkors, som vist på billedet nedenfor:Klik og hold venstre museknap nede, og træk rammens kant ned, og fremhæve alle de celler, hvor du vil indsætte formlen. Når alle de nødvendige celler er valgt, skal du slippe museknappen:

   

Tip: I store tabeller vil det være hurtigere at kopiere formlen, hvis du bruger tastaturgenveje. Fremhæv en celle C1 og tryk Ctrl + C (for at kopiere formlen til udklipsholderen), klik derefter Ctrl + Skift + Afslut (for at markere alle ikke-tomme celler i kolonne C) og tryk til sidst Ctrl + V (for at indsætte formlen i alle markerede celler).

3. Fantastisk, nu er alle duplikerede værdier markeret som "Dupliker":

Mulighed B: to kolonner er på forskellige ark (i forskellige projektmapper)

1. I den første celle i den første tomme kolonne på regnearket Sheet2 (i vores tilfælde er det kolonne B) indtast følgende formel:

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

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

   Her Sheet3 er navnet på det ark, hvorpå 2. kolonne er placeret, og $ A $ 1: $ A $ 10000 er celleadresser fra 1. til sidst i denne 2. kolonne.

2. Kopier formlen til alle celler i en kolonne B (samme som mulighed A).
3. Vi får dette resultat:

Behandling af fundne dubletter

Fantastisk, vi har fundet poster i den første kolonne, der også er til stede i den anden kolonne. Nu skal vi gøre noget med dem. At gennemgå alle de duplikerede poster i en tabel manuelt er ret ineffektivt og tager for meget tid. Der er bedre måder.

Vis kun duplikerede rækker i kolonne A

Hvis dine kolonner ikke har overskrifter, skal du tilføje dem. For at gøre dette skal du placere markøren på det tal, der repræsenterer den første linje, og det bliver til en sort pil, som vist i figuren nedenfor:

Højreklik og vælg fra kontekstmenuen Indsættelse (Indsæt):

Giv navne til kolonnerne, for eksempel "Navn"Og"Duplikere?» Åbn derefter fanen data (Data), og tryk på filtre (Filter):

Klik derefter på den lille grå pil ved siden af ​​"Duplikere?« for at åbne filtermenuen; fjern markeringen af ​​alle elementer på denne liste undtagen Dupliker, og tryk på OK.

Det er alt, nu ser du kun disse elementer i kolonnen А, som er duplikeret i kolonnen В. Der er kun to sådanne celler i vores træningstabel, men som du forstår, vil der i praksis være mange flere af dem.

For at vise alle rækker i en kolonne igen А, klik på filtersymbolet i kolonnen В, som nu ligner en tragt med en lille pil, og vælg Vælg alle (Vælg alle). Eller du kan gøre det samme gennem båndet ved at klikke data (Data) > Vælg og filtrer (Sorter og filtrer) > Klar (Slet) som vist på skærmbilledet nedenfor:

Skift farve eller fremhæv fundne dubletter

Hvis noterne "Dupliker" er ikke nok til dine formål, og du vil markere duplikerede celler med en anden skriftfarve, fyldfarve eller en anden metode...

I dette tilfælde skal du filtrere dubletterne som vist ovenfor, vælge alle filtrerede celler og klikke Ctrl + 1for at åbne dialogen formater celler (celleformat). Lad os som et eksempel ændre fyldfarven på celler i rækker med dubletter til lys gul. Selvfølgelig kan du ændre fyldfarven med værktøjet Fyld (Fyldfarve) fanen Home (Hjem) men dialogboks fordel formater celler (Cell Format) ved at du kan konfigurere alle formateringsmulighederne på samme tid.

Nu vil du bestemt ikke gå glip af nogen celler med dubletter:

Fjerner duplikerede værdier fra den første kolonne

Filtrer tabellen, så kun celler med dublerede værdier vises, og vælg disse celler.

Hvis de 2 kolonner, du sammenligner, er på forskellige ark, det vil sige i forskellige tabeller, højreklik på det valgte område og vælg Slet række (Fjern linje):

Presse OKnår Excel beder dig om at bekræfte, at du virkelig vil slette hele arkrækken og derefter rydde filteret. Som du kan se, er der kun rækker med unikke værdier tilbage:

Hvis 2 kolonner er på samme ark, tæt på hinanden (tilstødende) eller ikke tæt på hinanden (ikke tilstødende), så vil processen med at fjerne dubletter være lidt mere kompliceret. Vi kan ikke fjerne hele rækken med duplikerede værdier, da dette også vil fjerne cellerne fra den anden kolonne. Så for kun at efterlade unikke poster i en kolonne А, gør dette:

1. Filtrer tabellen for kun at vise duplikerede værdier, og vælg disse celler. Højreklik på dem og vælg fra kontekstmenuen Ryd indhold (klart indhold).
2. Rengør filteret.
3. Vælg alle celler i en kolonne А, startende fra cellen A1 helt ned til bunden med dataene.
4. Klik på knappen data (Data), og tryk på Sorter A til Z (Sortér fra A til Z). I den dialogboks, der åbnes, skal du vælge Fortsæt med det aktuelle valg (Sortér inden for det angivne udvalg) og klik på knappen Sort (Sortering):
5. Slet kolonnen med formlen, du har ikke længere brug for den, fra nu af har du kun unikke værdier.
6. Det er det, nu kolonnen А indeholder kun unikke data, der ikke er i kolonnen В:

Som du kan se, er det ikke så svært at fjerne dubletter fra to kolonner i Excel ved hjælp af formler.