Hvordan sammenligne to kolonner i Excel og fjerne duplikater (utheve, fargelegge, flytte)

Denne artikkelen vil ta deg omtrent 10 minutter å lese. I løpet av de neste 5 minuttene kan du enkelt sammenligne to kolonner i Excel og finne ut om det er duplikater i dem, slette dem eller markere dem i farger. Så, tiden er inne!

Excel er en veldig kraftig og veldig kul applikasjon for å lage og behandle store mengder data. Hvis du har flere arbeidsbøker med data (eller bare en stor tabell), vil du sannsynligvis sammenligne 2 kolonner, finne dupliserte verdier og deretter gjøre noe med dem, for eksempel slette, markere eller fjerne innholdet. Kolonner kan være i samme tabell, være tilstøtende eller ikke tilstøtende, kan være plassert på 2 forskjellige ark eller til og med i forskjellige bøker.

Tenk deg at vi har 2 kolonner med folks navn – 5 navn per kolonne A og 3 navn i en kolonne B. Du må sammenligne navnene i disse to kolonnene og finne duplikater. Som du forstår er dette fiktive data, tatt utelukkende for eksempel. I ekte tabeller har vi å gjøre med tusenvis eller titusenvis av poster.

Alternativ A: begge kolonnene er på samme ark. For eksempel en kolonne A og kolonne B.

Alternativ B: Kolonnene er på forskjellige ark. For eksempel en kolonne A på arket Sheet2 og kolonne A på arket Sheet3.

Excel 2013, 2010 og 2007 har et innebygd verktøy Fjern duplikater (Fjern duplikater), men den er maktesløs i denne situasjonen da den ikke kan sammenligne data i 2 kolonner. Dessuten kan den bare fjerne duplikater. Det er ingen andre alternativer som å fremheve eller endre farger. Og pek!

Deretter vil jeg vise deg de mulige måtene å sammenligne to kolonner i Excel, som lar deg finne og fjerne dupliserte poster.

• Sammenlign 2 kolonner og se etter duplikater ved hjelp av formler
  
  • Alternativ A: begge kolonnene er på samme ark
  • Alternativ B: Kolonnene er på forskjellige ark eller i forskjellige arbeidsbøker
• Behandling av funnet duplikater
  
  • Vis bare dupliserte rader i kolonne A
  • Endre farge eller fremhev duplikater du har funnet
  • Fjern duplikater fra første kolonne

Sammenlign to kolonner i Excel og finn dupliserte oppføringer ved hjelp av formler

Alternativ A: begge kolonnene er på samme ark

1. I den første tomme cellen (i vårt 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 formelen vår A1 dette er den første cellen i den første kolonnen vi skal sammenligne. $B$1 и $B$10000 dette er adressene til den første og siste cellen i den andre kolonnen, som vi skal utføre sammenligningen med. Legg merke til de absolutte referansene – kolonnebokstaver og radtall innledes med et dollartegn ($). Jeg bruker absolutte referanser slik at celleadresser forblir de samme når jeg kopierer formler.

   Hvis du vil finne duplikater i en kolonne B, endre referansene slik at formelen ser slik ut:

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

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

   I stedet "Bare"Og"Dupliser» Du kan skrive dine egne etiketter, for eksempel «Ikke funnet"Og"Funnet", eller la bare"Dupliser' og skriv inn et mellomrom i stedet for den andre verdien. I sistnevnte tilfelle vil cellene som det ikke finnes duplikater for forbli tomme, og jeg tror denne representasjonen av dataene er mest praktisk for videre analyse.

2. La oss nå kopiere formelen vår til alle cellene i kolonnen C, helt ned til nederste rad, som inneholder dataene i kolonnen A. For å gjøre dette, flytt musepekeren til nedre høyre hjørne av cellen C1, vil pekeren ha form av et svart trådkors, som vist på bildet nedenfor:Klikk og hold nede venstre museknapp og dra rammen nedover, uthev alle cellene der du vil sette inn formelen. Når alle de nødvendige cellene er valgt, slipper du museknappen:

   

Tips: I store tabeller vil det gå raskere å kopiere formelen hvis du bruker hurtigtaster. Uthev en celle C1 og trykk Ctrl + C (for å kopiere formelen til utklippstavlen), klikk deretter Ctrl + Shift + End (for å velge alle ikke-tomme celler i kolonne C) og trykk til slutt Ctrl + V (for å sette inn formelen i alle valgte celler).

3. Flott, nå er alle dupliserte verdier merket som "Dupliser':

Alternativ B: to kolonner er på forskjellige ark (i forskjellige arbeidsbøker)

1. I den første cellen i den første tomme kolonnen på regnearket Sheet2 (i vårt tilfelle er det kolonne B) skriv inn 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å arket som 2. kolonne er plassert på, og $ A $ 1: $ A $ 10000 er celleadresser fra 1. til sist i denne 2. kolonnen.

2. Kopier formelen til alle cellene i en kolonne B (samme som alternativ A).
3. Vi får dette resultatet:

Behandling av funnet duplikater

Flott, vi har funnet oppføringer i den første kolonnen som også er til stede i den andre kolonnen. Nå må vi gjøre noe med dem. Å gå gjennom alle dupliserte postene i en tabell manuelt er ganske ineffektivt og tar for mye tid. Det finnes bedre måter.

Vis bare dupliserte rader i kolonne A

Hvis kolonnene dine ikke har overskrifter, må du legge dem til. For å gjøre dette, plasser markøren på tallet som representerer den første linjen, og det vil bli til en svart pil, som vist i figuren nedenfor:

Høyreklikk og velg fra hurtigmenyen Innsetting (Sett inn):

Gi navn til kolonnene, for eksempel "Navn"Og"Duplisere?» Åpne deretter fanen Data (Data) og trykk filtre (Filter):

Klikk deretter på den lille grå pilen ved siden av "Duplisere?« for å åpne filtermenyen; fjern merket for alle elementer i denne listen unntatt Dupliser, og trykk OK.

Det er alt, nå ser du bare de elementene i kolonnen А, som er duplisert i kolonnen В. Det er bare to slike celler i treningstabellen vår, men som du forstår vil det i praksis være mange flere av dem.

For å vise alle rader i en kolonne igjen Аklikker du på filtersymbolet i kolonnen В, som nå ser ut som en trakt med en liten pil, og velg Velg alle (Velg alle). Eller du kan gjøre det samme gjennom båndet ved å klikke Data (Data) > Velg og filtrer (Sorter og filtrer) > Fjern (Tøm) som vist på skjermbildet nedenfor:

Endre farge eller fremhev duplikater du har funnet

Hvis notatene "Dupliser" er ikke nok for dine formål, og du vil merke dupliserte celler med en annen skriftfarge, fyllfarge eller en annen metode...

I dette tilfellet filtrerer du duplikatene som vist ovenfor, velg alle filtrerte celler og klikk Ctrl + 1for å åpne dialogen Formater celler (celleformat). Som et eksempel, la oss endre fyllfargen på celler i rader med duplikater til knallgul. Selvfølgelig kan du endre fyllfargen med verktøyet Fyll (Fyllfarge)-fanen Hjemprodukt (Hjem) men dialogboks fordel Formater celler (Cell Format) ved at du kan konfigurere alle formateringsalternativene samtidig.

Nå vil du definitivt ikke gå glipp av noen celler med duplikater:

Fjerner dupliserte verdier fra den første kolonnen

Filtrer tabellen slik at bare celler med dupliserte verdier vises, og velg disse cellene.

Hvis de 2 kolonnene du sammenligner er på forskjellige ark, det vil si i forskjellige tabeller, høyreklikk det valgte området og velg Slett rad (Fjern linje):

Press OKnår Excel ber deg om å bekrefte at du virkelig vil slette hele arkraden og deretter tømme filteret. Som du kan se, gjenstår bare rader med unike verdier:

Hvis 2 kolonner er på samme ark, nær hverandre (tilstøtende) eller ikke nær hverandre (ikke tilstøtende), da vil prosessen med å fjerne duplikater være litt mer komplisert. Vi kan ikke fjerne hele raden med dupliserte verdier, da dette vil fjerne cellene fra den andre kolonnen også. Så for å la bare være unike oppføringer i en kolonne А, gjør dette:

1. Filtrer tabellen for å vise bare dupliserte verdier og velg disse cellene. Høyreklikk på dem og velg fra hurtigmenyen Tøm innholdet (klart innhold).
2. Rengjør filteret.
3. Velg alle cellene i en kolonne А, med start fra cellen A1 helt ned til bunnen som inneholder dataene.
4. Klikk på Data (Data) og trykk Sorter A til Å (Sorter fra A til Å). Velg i dialogboksen som åpnes Fortsett med gjeldende valg (Sorter innenfor det angitte utvalget) og klikk på knappen Svart (Sortering):
5. Slett kolonnen med formelen, du trenger den ikke lenger, fra nå av har du bare unike verdier.
6. Det er det, nå spalten А inneholder kun unike data som ikke er i kolonnen В:

Som du kan se, er det ikke så vanskelig å fjerne duplikater fra to kolonner i Excel ved hjelp av formler.