Hoe twee kolommen in Excel te vergelijken en duplicaten te verwijderen (markeren, inkleuren, verplaatsen)

Dit artikel kost u ongeveer 10 minuten om te lezen. In de volgende 5 minuten kunt u eenvoudig twee kolommen in Excel vergelijken en zien of er duplicaten in staan, deze verwijderen of in kleur markeren. Zo, het is zover!

Excel is een zeer krachtige en echt coole applicatie voor het maken en verwerken van grote hoeveelheden gegevens. Als u meerdere werkmappen met gegevens hebt (of slechts één grote tabel), wilt u waarschijnlijk 2 kolommen vergelijken, dubbele waarden zoeken en er vervolgens iets mee doen, bijvoorbeeld de inhoud verwijderen, markeren of wissen. Kolommen kunnen in dezelfde tabel staan, aangrenzend of niet aangrenzend zijn, kunnen zich op 2 verschillende bladen of zelfs in verschillende boeken bevinden.

Stel je voor dat we 2 kolommen hebben met de namen van mensen - 5 namen per kolom A en 3 namen in een kolom B. U moet de namen in deze twee kolommen vergelijken en duplicaten vinden. Zoals u begrijpt, zijn dit fictieve gegevens, alleen genomen als voorbeeld. In echte tabellen hebben we te maken met duizenden of zelfs tienduizenden records.

Optie A: beide kolommen staan ​​op hetzelfde blad. Bijvoorbeeld een kolom A en kolom B.

Optie B: De kolommen staan ​​op verschillende vellen. Bijvoorbeeld een kolom A op het blad Sheet2 en kolom A op het blad Sheet3.

Excel 2013, 2010 en 2007 hebben een ingebouwde tool Verwijder duplicaten (Duplicaten verwijderen) maar het is machteloos in deze situatie omdat het geen gegevens in 2 kolommen kan vergelijken. Bovendien kan het alleen duplicaten verwijderen. Er zijn geen andere opties zoals markeren of kleuren veranderen. En punt!

Vervolgens laat ik u de mogelijke manieren zien om twee kolommen in Excel te vergelijken, zodat u dubbele records kunt vinden en verwijderen.

• Vergelijk 2 kolommen en zoek naar duplicaten met behulp van formules
  
  • Optie A: beide kolommen staan ​​op hetzelfde blad
  • Optie B: De kolommen staan ​​op verschillende bladen of in verschillende werkmappen
• Verwerking van gevonden duplicaten
  
  • Toon alleen dubbele rijen in kolom A
  • Verander de kleur of markeer gevonden duplicaten
  • Verwijder duplicaten uit de eerste kolom

Vergelijk 2 kolommen in Excel en vind dubbele vermeldingen met behulp van formules

Optie A: beide kolommen staan ​​op hetzelfde blad

1. In de eerste lege cel (in ons voorbeeld is dit cel C1), schrijven we de volgende formule:

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

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

   

   In onze formule A1 dit is de eerste cel van de eerste kolom die we gaan vergelijken. $B$1 и $B$10000 dit zijn de adressen van de eerste en laatste cellen van de tweede kolom, waarmee we de vergelijking zullen uitvoeren. Let op de absolute verwijzingen - kolomletters en rijnummers worden voorafgegaan door een dollarteken ($). Ik gebruik absolute verwijzingen zodat celadressen hetzelfde blijven bij het kopiëren van formules.

   Als u duplicaten in een kolom wilt vinden B, verander de verwijzingen zodat de formule er als volgt uitziet:

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

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

   In plaats van "Alleen"En"duplicaat» U kunt uw eigen labels schrijven, bijvoorbeeld «Niet gevonden"En"Gevonden“, of laat alleen”duplicaat' en voer een spatie in in plaats van de tweede waarde. In het laatste geval blijven de cellen waarvoor geen duplicaten worden gevonden leeg, en ik denk dat deze weergave van de gegevens het meest geschikt is voor verdere analyse.

2. Laten we nu onze formule kopiëren naar alle cellen in de kolom C, helemaal naar de onderste rij, die de gegevens in de kolom bevat A. Verplaats hiervoor de muisaanwijzer naar de rechterbenedenhoek van de cel C1, zal de aanwijzer de vorm hebben van een zwart dradenkruis, zoals weergegeven in de onderstaande afbeelding:Klik en houd de linkermuisknop ingedrukt en sleep de rand van het frame naar beneden, waarbij alle cellen worden gemarkeerd waar u de formule wilt invoegen. Wanneer alle vereiste cellen zijn geselecteerd, laat u de muisknop los:

   

Tip: In grote tabellen gaat het kopiëren van de formule sneller als u sneltoetsen gebruikt. Markeer een cel C1 en druk op CTRL + C (om de formule naar het klembord te kopiëren), klik dan op Ctrl + Shift + Einde (om alle niet-lege cellen in kolom C te selecteren) en druk tenslotte op Ctrl + V (om de formule in alle geselecteerde cellen in te voegen).

3. Geweldig, nu zijn alle dubbele waarden gemarkeerd als "duplicaat":

Optie B: twee kolommen staan ​​op verschillende bladen (in verschillende werkmappen)

1. In de eerste cel van de eerste lege kolom op het werkblad Sheet2 (in ons geval is dit kolom B) voer de volgende formule in:

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

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

   Here Sheet3 is de naam van het blad waarop de 2e kolom staat, en $ A $ 1: $ A $ 10000 zijn celadressen van de 1e tot de laatste in deze 2e kolom.

2. Kopieer de formule naar alle cellen in een kolom B (zelfde als optie A).
3. We krijgen dit resultaat:

Verwerking van gevonden duplicaten

Mooi, we hebben in de eerste kolom vermeldingen gevonden die ook in de tweede kolom voorkomen. Nu moeten we iets met ze doen. Het handmatig doornemen van alle dubbele records in een tabel is behoorlijk inefficiënt en kost te veel tijd. Er zijn betere manieren.

Toon alleen dubbele rijen in kolom A

Als uw kolommen geen kopteksten hebben, moet u deze toevoegen. Plaats hiervoor de cursor op het nummer dat de eerste regel vertegenwoordigt, en het zal veranderen in een zwarte pijl, zoals weergegeven in de onderstaande afbeelding:

Klik met de rechtermuisknop en selecteer in het contextmenu Invoeging (Invoegen):

Geef namen aan de kolommen, bijvoorbeeld "Naam"En"Duplicaat?» Open vervolgens het tabblad Data (Gegevens) en druk op FILTER (Filter):

Klik daarna op het grijze pijltje naast “Duplicaat?« om het filtermenu te openen; deselecteer alle items in deze lijst behalve duplicaaten druk op OK.

Dat is alles, nu zie je alleen die elementen van de kolom А, die worden gedupliceerd in de kolom В. Er zijn slechts twee van dergelijke cellen in onze trainingstabel, maar zoals u begrijpt, zullen er in de praktijk veel meer zijn.

Alle rijen van een kolom opnieuw weergeven А, klik op het filtersymbool in de kolom В, die er nu uitziet als een trechter met een kleine pijl, en selecteer Selecteer alle (Selecteer alles). Of u kunt hetzelfde doen via het lint door te klikken op Data (Gegevens) > Selecteer & Filter (Sorteren & Filteren) > Clear (Wissen) zoals weergegeven in de onderstaande schermafbeelding:

Verander de kleur of markeer gevonden duplicaten

Als de opmerkingen “duplicaat" is niet genoeg voor uw doeleinden en u wilt dubbele cellen markeren met een andere letterkleur, vulkleur of een andere methode ...

Filter in dit geval de duplicaten zoals hierboven weergegeven, selecteer alle gefilterde cellen en klik op Ctrl + 1om het dialoogvenster te openen Cellen opmaken (celformaat). Laten we als voorbeeld de opvulkleur van cellen in rijen met duplicaten wijzigen in felgeel. Natuurlijk kunt u de vulkleur wijzigen met de tool Vulling (Opvulkleur) tabblad Home (Home) maar voordeel van dialoogvenster Cellen opmaken (Cell Format) omdat u alle opmaakopties tegelijkertijd kunt configureren.

Nu zul je zeker geen cellen met duplicaten missen:

Dubbele waarden verwijderen uit de eerste kolom

Filter de tabel zodat alleen cellen met dubbele waarden worden weergegeven en selecteer die cellen.

Als de 2 kolommen die u vergelijkt op verschillende bladen staan, dat wil zeggen, in verschillende tabellen, klik met de rechtermuisknop op het geselecteerde bereik en selecteer Rij verwijderen (Verwijder regel):

Media OKwanneer Excel u vraagt ​​om te bevestigen dat u echt de hele werkbladrij wilt verwijderen en vervolgens het filter wilt wissen. Zoals u kunt zien, blijven alleen rijen met unieke waarden over:

Als 2 kolommen op hetzelfde blad staan, dicht bij elkaar (aangrenzend) of niet dicht bij elkaar (niet aangrenzend), dan zal het proces van het verwijderen van duplicaten iets gecompliceerder zijn. We kunnen niet de hele rij met dubbele waarden verwijderen, omdat hierdoor ook de cellen uit de tweede kolom worden verwijderd. Dus om alleen unieke items in een kolom achter te laten А, doe dit:

1. Filter de tabel om alleen dubbele waarden weer te geven en selecteer die cellen. Klik er met de rechtermuisknop op en selecteer in het contextmenu Duidelijke inhoud (Duidelijke inhoud).
2. Reinig het filter.
3. Selecteer alle cellen in een kolom А, beginnend bij de cel A1 helemaal naar beneden met de gegevens.
4. Klik op de Data (Gegevens) en druk op Sorteer A tot Z (Sorteren van A tot Z). In het dialoogvenster dat wordt geopend, selecteert u Ga verder met de huidige selectie (Sorteer binnen de opgegeven selectie) en klik op de knop Zwart (Sorteren):
5. Verwijder de kolom met de formule, deze heb je niet meer nodig, vanaf nu heb je alleen nog maar unieke waarden.
6. Dat is het, nu de column А bevat alleen unieke gegevens die niet in de kolom staan В:

Zoals u kunt zien, is het verwijderen van duplicaten uit twee kolommen in Excel met formules niet zo moeilijk.