M365Con 2027 – gratis online conferentie · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

Duplicaten zoeken in Excel: markeren, tellen en weergeven

By · · Updated

Duplicaten zoeken in Excel met voorwaardelijke opmaak, COUNTIF-markeringen, UNIQUE, Power Query en controles tussen werkbladen, plus hoe je bijna-overeenkomsten herkent voordat je iets verwijdert.

Automatically translated with AI from the original post. Read the English original

Wil je duplicaten in Excel zoeken zonder iets te verwijderen, selecteer dan je gegevens en gebruik Start > Voorwaardelijke opmaak > Regels voor het markeren van cellen > Dubbele waarden. Excel kleurt elke waarde die meer dan eens voorkomt. Wil je getallen in plaats van kleuren, voeg dan een hulpkolom toe met een COUNTIF-formule. Dit artikel is bedoeld voor Excel-gebruikers die rommelige lijsten erven en het probleem eerst zichtbaar willen maken voordat ze het oplossen. Je krijgt de methode met markeren, formules voor tellen en markeren, een manier om alleen de herhaalde waarden weer te geven, een Power Query-optie, controles tussen werkbladen en een aanpak voor bijna-overeenkomsten.

Belangrijkste punten

  • De snelste manier om duplicaten in Excel te vinden is Start, Voorwaardelijke opmaak, Regels voor het markeren van cellen, Dubbele waarden. Het kleurt herhaalde waarden en verwijdert niets.
  • Een COUNTIF-hulpkolom laat zien hoe vaak elke waarde voorkomt, en met COUNTIFS kun je meerdere kolommen tegelijk controleren.
  • COUNTIF negeert hoofdletters en behandelt ? en * als jokertekens, dus het aantal kan afwijken van wat je verwacht.
  • Om twee werkbladen te vergelijken, tel je elke waarde met COUNTIF ten opzichte van de andere lijst, en open je beide werkmappen als de tweede lijst in een ander bestand staat.
  • Bijna-overeenkomsten, zoals Jon en John, worden niet als duplicaten gezien. Verwijder eerst spaties en let op hoofdletters, en controleer de rest daarna met het oog of met de invoegtoepassing Fuzzy Lookup.

Eerst zoeken, dan verwijderen. Weet je al welke rijen je wilt verwijderen, dan behandelt onze verdiepingsgids over het verwijderen van duplicaten in Excel de stap van het verwijderen. Hier is het doel om eerst goed te kijken in Excel, zodat jij bepaalt wat als duplicaat geldt en wat je bewaart.

Duplicaten markeren met voorwaardelijke opmaak

Dit is de snelste controle, en Microsoft raadt die aan als controlestap voordat je iets verwijdert. Het volgt het Microsoft-artikel over filteren op unieke waarden of het verwijderen van dubbele waarden.

  1. Selecteer de cellen, kolom of het bereik dat je wilt controleren. Meestal is één kolom genoeg.
  2. Open op het tabblad Start de optie Voorwaardelijke opmaak.
  3. Kies Regels voor het markeren van cellen en daarna Dubbele waarden.
  4. Kies een opmaak, bijvoorbeeld een lichtrode vulling, en selecteer OK.

Elke cel waarvan de waarde meer dan eens voorkomt, is nu gekleurd. Scrol door het werkblad of filter de kolom op kleur om alleen de gemarkeerde cellen te zien. Wil je dit ongedaan maken, ga dan naar Voorwaardelijke opmaak en verwijder de regels uit het bereik.

Er is een tweede manier die meer controle geeft. Open Voorwaardelijke opmaak > Regels beheren, maak een Nieuwe regel en kies Alleen unieke of dubbele waarden opmaken. In de lijst Alles opmaken kies je uniek of dubbel, en je kunt de getalnotatie, het lettertype, de rand of de vulling instellen. Dit is handig als je de waarden die maar één keer voorkomen wilt kleuren en de herhaalde waarden met rust wilt laten.

Er zijn twee beperkingen om te kennen. Microsoft geeft aan dat Excel geen duplicaten kan markeren in het gebied Waarden van een draaitabel. En de regel kijkt naar één kolom tegelijk als je één kolom selecteert. Een rij die alleen dubbel is wanneer je meerdere kolommen samen bekijkt, wordt dus niet gemarkeerd. Daarvoor heb je een formule nodig.

Duplicaten tellen en markeren met COUNTIF

Een hulpkolom geeft je een getal waarop je kunt sorteren en filteren. Het is ook het juiste hulpmiddel wanneer twee rijen alleen dubbel zijn als twee of drie kolommen overeenkomen.

Elke herhaling markeren

Stel dat je waarden in A2:A100 staan. Typ in B2:

=COUNTIF($A$2:$A$100,A2)

Kopieer de formule naar beneden. Een resultaat van 1 betekent dat de waarde uniek is. Een 2 betekent dat deze twee keer voorkomt, enzovoort. Elk exemplaar van een herhaalde waarde krijgt hetzelfde getal, inclusief het eerste. Wil je het getal omzetten naar WAAR of ONWAAR, voeg dan een vergelijking toe: =COUNTIF($A$2:$A$100,A2)>1.

Alleen de latere exemplaren markeren

Wanneer je van plan bent rijen te verwijderen, wil je meestal de eerste behouden. Gebruik dan een groeiend bereik:

=COUNTIF(A$2:A2,A2)>1

Het bereik A$2:A2 groeit wanneer je de formule naar beneden kopieert. De eerste keer dat een waarde voorkomt, krijg je ONWAAR. Elke latere keer krijgt een waarde WAAR. Dit komt overeen met wat de opdracht Duplicaten verwijderen van Excel doet, omdat die de eerste voorkomst behoudt.

Meerdere kolommen controleren met COUNTIFS

Gaat het om een record dat alleen een duplicaat is wanneer bijvoorbeeld voornaam en achternaam allebei overeenkomen, gebruik dan COUNTIFS met per kolom één paar van bereik en criterium:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

Een resultaat hoger dan 1 betekent dat de combinatie vaker voorkomt. Voeg meer paren toe voor meer kolommen. Probeer niet dezelfde controle te bouwen door de bereiken binnen COUNTIF samen te voegen, zoals in COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2). COUNTIF verwacht als eerste argument een echt bereik, dus die formule doet niet wat hij lijkt te doen. COUNTIFS is hier het juiste antwoord.

Wat COUNTIF doet en wat mensen verrast

Microsofts pagina over COUNTIF noemt een aantal gedragingen die belangrijk zijn bij het zoeken naar duplicaten:

  • Tekstvergelijking negeert hoofdletters. "appels" en "APPELS" tellen als dezelfde waarde. Excel behandelt ze hier als duplicaten.
  • De tekens ? en * werken als jokertekens. Als je gegevens ze bevatten, bijvoorbeeld in productcodes, kan COUNTIF meer matchen dan je verwacht. Zet een tilde voor het teken om het letterlijk te zoeken.
  • COUNTIF geeft onjuiste resultaten voor tekenreeksen langer dan 255 tekens.
  • Voorloop- of volgspaties, een mix van rechte en krullende aanhalingstekens en niet-afdrukbare tekens kunnen de telling allemaal verstoren. Microsoft adviseert de functies TRIM en CLEAN om de gegevens eerst op te schonen.

Dat laatste punt is de oorzaak van de meeste klachten dat Excel duplicaten 'mist'. Twee cellen die identiek lijken, kunnen verschillen door een spatie aan het eind. Maak eerst schoon, en tel dan pas.

Filteren en controleren

Zodra de hulpkolom er staat, filter je erop op WAAR, of op waarden hoger dan 1, en lees je de rijen na. Je kunt op de hulpkolom sorteren om alle herhaalde rijen bij elkaar te zetten. Bekijk een steekproef voordat je iets verwijdert. Vaak blijkt een 'duplicaat' twee echte records te zijn die toevallig dezelfde naam delen.

Alleen de dubbele waarden weergeven

Soms wil je een korte lijst van wat zich herhaalt, en geen markering naast elke rij.

In Excel voor Microsoft 365, 2024 en 2021 kan de functie UNIQUE dit werk doen. Volgens Microsofts pagina over UNIQUE geeft =UNIQUE(array,[by_col],[exactly_once]) de unieke waarden uit een bereik terug, en levert het instellen van exactly_once op WAAR alleen de waarden die precies één keer voorkomen. Dus =UNIQUE(A2:A100) geeft elke waarde één keer, en =UNIQUE(A2:A100,,TRUE) geeft de waarden die helemaal niet dubbel voorkomen. Voor alleen de herhaalde waarden combineer je UNIQUE met FILTER en COUNTIF:

=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))

Dit houdt de rijen over waarvan de telling hoger is dan 1 en toont elk daarvan één keer. Het vereist een Excel-versie met dynamische matrices. Test het op een paar rijen voordat je het op een grote lijst vertrouwt.

In oudere versies gebruik je een hulpkolom. Typ =IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") en kopieer de formule naar beneden. De kolom toont nu elke herhaalde waarde en laat de rest leeg. Filter de lege cellen eruit. Je ziet elke herhaalde waarde zo vaak als hij voorkomt, wat prima is voor een controle.

Met Geavanceerd filter kun je alleen de unieke records naar een nieuwe locatie kopiëren. Ga naar Gegevens > Geavanceerd, kies Kopiëren naar andere locatie, vul een cel in het vak Kopiëren naar in en vink Alleen unieke records aan. Als je deze lijst vergelijkt met je origineel, zie je wat er dubbel stond.

Gebruik Power Query om alleen de duplicaten te behouden

Als je data groot is of elke week binnenkomt, laat dan Power Query de duplicaten opsporen. De documentatie van Microsoft over Power Query beschrijft een opdracht Duplicaten behouden, het tegenovergestelde van Duplicaten verwijderen. Die filtert de tabel naar de rijen waarvan de waarden in de geselecteerde kolommen meer dan één keer voorkomen. En anders dan Duplicaten verwijderen geeft die alle overeenkomende rijen terug, niet één per waarde.

Het artikel van Microsoft over dubbele rijen behouden of verwijderen beschrijft de stappen voor het verwijderen. Het begin is voor beide hetzelfde:

  1. Selecteer een cel in je gegevens en kies Query > Bewerken, of laad ze met Gegevens > Uit tabel/bereik.
  2. Klik op een kolomkop om die te selecteren. Houd Shift of Ctrl ingedrukt om meer kolommen te selecteren.
  3. Pas de opdracht toe. De stappen van Microsoft voor verwijderen gebruiken Start > Rijen verwijderen > Duplicaten verwijderen. Zoek op het tabblad Start naast de andere rijopdrachten naar de bijbehorende opdracht Duplicaten behouden en controleer de preview.

Microsoft wijst er ook op dat Power Query hoofdlettergevoelig is, dus "Apple" en "apple" zijn daar verschillende waarden, anders dan bij COUNTIF. Dat is een echt verschil tussen de twee methoden. Als je doel een herhaalbare wekelijkse controle is, kies dan één gedrag en houd je daaraan, bijvoorbeeld door de kolom eerst naar hoofdletters om te zetten.

Duplicaten zoeken tussen twee werkbladen of lijsten

Een veelvoorkomende taak is controleren of een lijst op het ene werkblad ook op een ander voorkomt: een nieuwe klantenlijst naast de oude, of de bestellingen van deze maand naast die van vorige maand.

  1. Zet de twee lijsten op aparte werkbladen, bijvoorbeeld Blad1 en Blad2, met de waarden in kolom A.
  2. Voer op Blad1 in B2 in: =COUNTIF(Blad2!A:A;A2)>0.
  3. Kopieer de formule naar beneden. WAAR betekent dat de waarde ook op Blad2 voorkomt.
  4. Filter op WAAR om de overlap te zien, of op ONWAAR om te zien wat nieuw is.

Voor twee kolommen die samen moeten overeenkomen, gebruik je COUNTIFS met een paar voor elke kolom en de bereiken van het andere werkblad.

Als de tweede lijst in een andere werkmap staat, waarschuwt Microsoft dat een formule die naar een gesloten werkmap verwijst de fout #WAARDE! kan geven. Houd beide werkmappen open terwijl je werkt, of haal de tweede lijst met Power Query in de eerste werkmap. Onthoud na de controle dat het resultaat afhangt van de gegevens op dat moment. Verandert een van beide lijsten, dan veranderen ook de markeringen.

Bijna-overeenkomsten: Jon Smith en John Smith

Alles hierboven vindt alleen exacte overeenkomsten, afgezien van hoofdletters. Echte lijsten bevatten ook typefouten en afwijkende schrijfwijzen. Excel heeft geen ingebouwde functie die aangeeft hoe sterk twee tekstwaarden op elkaar lijken, dus pak je dit in twee stappen aan.

Stap één: verwijder de schijnverschillen. Gebruik SPATIES.WISSEN om overbodige spaties te verwijderen en WISSEN.NIET.AFDRUKBAAR om niet-afdrukbare tekens te verwijderen, en baseer de controle op de opgeschoonde waarden. In een hulpkolom toont =SPATIES.WISSEN(A2) de opgeschoonde tekst. Tel de duplicaten op die kolom, niet op het origineel. Omdat COUNTIF geen onderscheid maakt tussen hoofdletters, hoef je daar de hoofdletters niet aan te passen. In Power Query, waar hoofdletters wel tellen, zet je de kolom om naar hoofd- of kleine letters.

Stap twee: kijk naar wat er overblijft. Sorteer de opgeschoonde lijst alfabetisch en lees de naastliggende regels door. Bijna-overeenkomsten zoals "Jon Smith" en "John Smith" komen zo naast elkaar te staan, zodat een mens ze kan zien. Voor grotere lijsten heeft Microsoft Research de Fuzzy Lookup-invoegtoepassing voor Excel gepubliceerd. Die kan fuzzy-dubbele rijen binnen één tabel zoeken of vergelijkbare rijen tussen twee tabellen matchen, en geeft elke match een gelijkenisscore. Controleer de vereisten voordat je er je plannen op baseert: de downloadpagina vermeldt Windows, met Excel 2007, 2010, 2013 en 2016, en een release uit 2020. Hij installeert mogelijk niet op een actuele Microsoft 365-omgeving en is niet beschikbaar op Mac.

Gebruik welke methode je ook kiest, controleer fuzzy-resultaten altijd handmatig. Twee personen met vergelijkbare namen zijn niet per se dezelfde persoon.

Veelgemaakte fouten

  • Eén kolom controleren terwijl het duplicaat door twee kolommen wordt bepaald. Een herhaalde achternaam alleen is geen dubbel record.
  • COUNTIF-resultaten vertrouwen op data met losse spaties of vreemde tekens. Maak de data eerst schoon met TRIM en CLEAN.
  • Vergeten dat COUNTIF hoofdlettergevoeligheid negeert terwijl Power Query die wel respecteert, waardoor je met de twee verschillende resultaten krijgt.
  • COUNTIF($A$2:$A$100,A2)>1 gebruiken en elke WAAR-rij verwijderen. Dan wordt ook het eerste exemplaar gemarkeerd, zodat je alle exemplaren verwijdert. Gebruik het uitbreidende bereik om alleen de latere exemplaren te markeren.
  • Hele kolommen doorzoeken op tekenreeksen langer dan 255 tekens, waarbij COUNTIF foutieve antwoorden geeft.
  • Direct na het markeren verwijderen. Kopieer het blad eerst, want Microsoft zegt dat verwijderde duplicaten definitief worden gewist.

Gerelateerde onderwerpen om verder te verkennen

  • Verwijder de duplicaten die je hebt gevonden. Met de opdracht Duplicaten verwijderen op het tabblad Gegevens behoudt Excel de eerste match en verwijdert de rest. Sorteer eerst om te bepalen welke rij blijft staan.
  • Behoud het beste record, niet het eerste. Sorteer op datum of waarde zodat de rij die je wilt bovenaan staat, en markeer daarna de latere exemplaren met het uitbreidende COUNTIF.
  • Vergelijk lijsten over bestanden heen. Power Query kan meerdere werkmappen in één tabel samenvoegen, zodat je ze samen kunt controleren.
  • Voorkom nieuwe duplicaten. Gegevensvalidatie met een aangepaste formule kan een waarde blokkeren die al bestaat. Probeer dit eerst op een testblad.
  • Houd een controlespoor bij. Laat de hulpkolom staan in een kopie van het blad, zodat je later kunt aantonen wat er is gemarkeerd en waarom.

Volgende stap

Kies één kolom in je huidige bestand, markeer de duplicaten met voorwaardelijke opmaak en tel hoeveel cellen oplichten. Voeg daarna een COUNTIF-hulpkolom toe en vergelijk de aantallen. Als ze verschillen, heb je verborgen spaties of hoofdlettverschillen gevonden, en weet je hoe je duplicaten in Excel vindt voordat je iets verwijdert.

Bronnen

Entities in this article

Microsoft Excel Power Query

Related articles

RSS