Twee Excel-bladen vergelijken voor duplicaten: 5 snelle manieren

Bij het werken met grote datasets in Excel, is het gebruikelijk om meerdere werkbladen met vergelijkbare gegevens te hebben. Het vergelijken van deze bladen voor dubbele records kan een behoorlijke uitdaging zijn, vooral wanneer de gegevens verspreid zijn over verschillende kolommen en rijen.

De meest gebruikelijke methoden om duplicaten in twee Excel-bladen te vinden, zijn:

1. VERT.ZOEKEN, AANTAL.ALS of EXACT-functies

2. Conditionele opmaak

3. Power-query

4. Externe tools en invoegtoepassingen

5. Visuele controle op duplicaten

Dit artikel loopt stap voor stap door deze vijf methoden om duplicaten op meerdere werkbladen te lokaliseren en af ​​te handelen. Tegen het einde zult u zien hoe gemakkelijk het is om dubbele waarden te beheren.

Laten we beginnen!

Inhoudsopgave

• Voorbeeld werkbladen en gegevens
• 1. Gebruik VERT.ZOEKEN, AANTAL.ALS of EXACT-functies om duplicaten te vinden
  • A. Hoe gebruikt u de VLOOKUP-functie om duplicaten in twee bladen te vinden?
    • Wat als u met verschillende werkmappen werkt?
  • B. Hoe gebruikt u de AANTAL.ALS-functie om duplicaten in werkbladen te vinden?
  • C. Hoe gebruikt u de EXACT-functie om duplicaten in werkbladen te vinden?
• 2. Voorwaardelijke opmaak gebruiken voor dubbele rijen
  • Hoe de Beheerder voor voorwaardelijke opmaakregels te gebruiken
• 3. Hoe Power Query te gebruiken om duplicaten in werkbladen te vinden
• 4. Hulpmiddelen en invoegtoepassingen om duplicaten op werkbladen te identificeren
• 5. Visueel controleren op duplicaten in twee bladen
• 3 tips voor het voorbereiden van uw Excel-werkbladen
• Hoe om te gaan met fouten en inconsistenties
• Laatste gedachten

Voorbeeld werkbladen en gegevens

Om de voorbeelden in dit artikel te volgen, maakt u een nieuwe Excel-werkmap met deze gegevens in kolom A van het eerste blad:

• Appel

• Oranje

• Peer

• Aardbei

Zet deze gegevens in kolom A van het tweede blad:

• Peer

• Aardbei

• Appel

• Ananas

Je werkbladen zien er zo uit:

Zodra u uw werkbladen hebt ingesteld, kunnen we nu de verschillende manieren bespreken om duplicaten in twee bladen te vinden, te beginnen met de functies VERT.ZOEKEN, AANTAL.ALS en EXACT.

Laten we gaan!

1. Gebruik VERT.ZOEKEN, AANTAL.ALS of EXACT-functies om duplicaten te vinden

Excel heeft drie ingebouwde functies die het vinden van duplicaten een fluitje van een cent kunnen maken: VERT.ZOEKEN , AANTAL.ALS en EXACT .

Deze functies zijn ontworpen om u te helpen bij het vinden, tellen en vergelijken van gegevens in uw spreadsheets, waardoor ze ideale hulpmiddelen zijn voor het vinden van dubbele vermeldingen.

Elke functie heeft een uniek doel en als u begrijpt hoe u ze moet gebruiken, kunt u uw gegevensanalyseproces aanzienlijk stroomlijnen.

In de volgende secties zullen we onderzoeken hoe u de functies VERT.ZOEKEN, AANTAL.ALS en EXACT kunt gebruiken om duplicaten in uw Excel-werkbladen effectief te identificeren.

A. Hoe gebruikt u de VLOOKUP-functie om duplicaten in twee bladen te vinden?

VERT.ZOEKEN staat voor Vertical Lookup. U gebruikt deze functie om dubbele waarden tussen twee kolommen te vinden. Dit is de syntaxis:

=VERT.ZOEKEN(opzoekwaarde, tabelmatrix, col_index_getal, [bereik_opzoeken])

• lookup_value : De waarde waarnaar u wilt zoeken in de eerste kolom van de table_array.

• table_array : Het cellenbereik met de gegevens waarin u wilt zoeken.

• col_index_num : Het kolomnummer in de table_array waaruit u de waarde wilt retourneren.

• range_lookup : Optioneel. Het is WAAR (overeenkomst bij benadering) of ONWAAR (exacte overeenkomst). Standaard is WAAR.

Als u de functie op twee werkbladen in een Excel-bestand wilt gebruiken, moet u weten hoe u naar een afzonderlijk blad in een formule kunt verwijzen. Typ hiervoor de bladnaam gevolgd door het uitroepteken (!) en uw cel of celbereik.

Dit verwijst bijvoorbeeld naar de cellen A2 tot en met A5 in blad 2 van dezelfde werkmap: Blad 2!$A$2:$A$5.

Volg deze stappen om de functie VERT.ZOEKEN in uw voorbeeldspreadsheet te gebruiken:

1. Selecteer cel B2 om het eerste vergelijkingsresultaat weer te geven.

2. Typ deze formule: =VERT.ZOEKEN(A2,Blad2!$A$2:$A$5, 1, FALSE).

3. Druk op Enter om het vergelijkingsresultaat weer te geven.

4. Vul de formule in om de waarden voor de rest van de rijen in het eerste blad te vergelijken.

De resultaten zien er uit zoals op onderstaande afbeelding:

U kunt de weergave opschonen door een gebruiksvriendelijke melding te tonen in plaats van een foutmelding wanneer er geen duplicaat wordt gevonden.

Deze formule geeft bijvoorbeeld "Ja" of "Nee" weer voor respectievelijk gevonden en niet gevonden waarden:

=IF(ISNA(VERT.ZOEKEN(A2, Blad 2!$A$2:$A$5, 1, FALSE)), "Nee", "Ja")

Er zijn meer voorbeelden van deze functie in ons artikel over het gebruik van VERT.ZOEKEN om twee kolommen te vergelijken .

Wat als u met verschillende werkmappen werkt?

Als uw werkbladen in afzonderlijke werkmappen staan, is het functiegebruik hetzelfde met twee Excel-bestanden. Het verwijzen naar het tweede werkblad is echter iets ingewikkelder.

Je moet:

• Plaats de naam van de Excel-werkmap tussen haakjes

• Volg met de naam van het werkblad

• Plaats de werkmap en het werkblad tussen aanhalingstekens.

Als de cellen zich bijvoorbeeld in een blad met de naam Blad2 bevinden in een werkmap met de naam "WB 2.xlsx", ziet de indeling er als volgt uit:

'[WB 2.xlsx]Blad2'!$A$2:$A$5

Sluit de tweede werkmap voordat u de formule invoert. Anders krijg je een foutmelding.

B. Hoe gebruikt u de AANTAL.ALS-functie om duplicaten in werkbladen te vinden?

De AANTAL.ALS-functie in Excel wordt gebruikt om het aantal cellen binnen een opgegeven bereik te tellen dat aan een bepaald criterium voldoet.

Als u meerdere bladen wilt vergelijken, kunt u het aantal cellen in het tweede werkblad tellen dat overeenkomt met een cel in het eerste werkblad.

Dit is de syntaxis van de functie:

=AANTAL.ALS(bereik, criteria)

• Bereik : het cellenbereik dat u wilt tellen op basis van de opgegeven criteria.

• Criteria : de voorwaarde waaraan moet worden voldaan om een ​​cel te kunnen tellen.

Volg deze stappen om de functie met de voorbeeldgegevens te gebruiken:

1. Selecteer cel B2 om het eerste vergelijkingsresultaat weer te geven.

2. Typ deze formule: =AANTAL.ALS(Blad2!$A$2:$A$5, A2)

3. Druk op Enter om het vergelijkingsresultaat weer te geven.

4. Vul de formule in om de waarden voor de rest van de rijen in het eerste blad te vergelijken.

De functie vindt één overeenkomst voor sommige cellen en geen enkele voor andere. De vergelijkingscel geeft het aantal weer. Hier is het resultaat van de voorbeeldgegevens:

De functie AANTAL.ALS kan worden gebruikt voor andere nuttige taken, zoals het tellen van niet-lege cellen in Excel .

C. Hoe gebruikt u de EXACT-functie om duplicaten in werkbladen te vinden?

De functie EXACT in Excel kan ook worden gebruikt om te zoeken naar duplicaten in dezelfde cellen in twee verschillende Excel-werkbladen. De syntaxis is:

=EXACT(tekst1, tekst2)

• text1 is de eerste tekenreeks die u wilt vergelijken.

• tekst2 is de tweede tekenreeks die u wilt vergelijken.

Volg deze stappen:

1. Selecteer cel B2.

2. Typ de formule =EXACT(A2, Sheet2!A2)

3. Druk op Enter om het vergelijkingsresultaat weer te geven. De formule retourneert WAAR als beide waarden identiek zijn of anders ONWAAR.

4. Vul de formule in om de waarden voor de rest van de rijen in het eerste blad te vergelijken.

Merk op dat deze methode niet zoekt naar duplicaten in een celbereik. In plaats daarvan zoekt u alleen naar overeenkomsten op basis van dezelfde cel in een ander blad.

Dit kan handig zijn bij geordende data waarbij je maar een paar uitzonderingen verwacht.

De functies VERT.ZOEKEN, AANTAL.ALS en EXACT zijn handig voor het vinden van duplicaten, maar Excel is een veelzijdig programma en er zijn andere opties. In het volgende gedeelte bekijken we hoe u voorwaardelijke opmaak kunt gebruiken om duplicaten in twee bladen te identificeren.

2. Voorwaardelijke opmaak gebruiken voor dubbele rijen

In dit gedeelte leert u hoe u voorwaardelijke opmaak kunt gebruiken om dubbele rijen in twee Excel-werkbladen te vinden en te markeren.

Volg deze stappen om een ​​regel voor voorwaardelijke opmaak te maken:

1. Selecteer het cellenbereik dat de gegevens bevat (in dit geval A2:A5).

2. Klik op het tabblad "Start" in het Excel-lint.

3. Klik op "Voorwaardelijke opmaak" in de groep "Stijlen".

4. Kies "Nieuwe regel" in het vervolgkeuzemenu.

De volgende taak is om een ​​formule te geven die uw regel kan gebruiken. Volg deze stappen:

1. Kies "Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt" in het dialoogvenster

2. Voer de volgende formule in: =AANTAL.ALS(Blad2!$A$2:$A$5, A2) > 0

Ten slotte past u de opmaak toe die u verkiest voor dubbele cellen.

1. Klik op de knop Formaat om het dialoogvenster Cellen opmaken te openen.

2. Kies een formaat, vul bijvoorbeeld duplicaten met een gele achtergrondkleur.

3. Klik OK.

Uw dubbele gegevens zijn nu geel gemarkeerd.

Hoe de Beheerder voor voorwaardelijke opmaakregels te gebruiken

Nadat u de regel voor voorwaardelijke opmaak hebt gemaakt, kunt u deze beheren met Beheer van regels voor voorwaardelijke opmaak.

Om toegang te krijgen tot de beheerder:

1. Ga naar het tabblad Start.

2. Klik op Voorwaardelijke opmaak.

3. Kies "Regels beheren".

U ziet een lijst met alle regels voor voorwaardelijke opmaak die op het geselecteerde blad zijn toegepast. U kunt de volgorde van regels bewerken, verwijderen of wijzigen door de regel te selecteren en op de juiste knoppen te klikken.

Volg deze stappen om dezelfde regel toe te passen op het andere blad:

1. Selecteer het bereik dat u wilt vergelijken in het tweede blad.

2. Ga naar Regelbeheer voor voorwaardelijke opmaak.

3. Selecteer de regel, klik op Regel dupliceren en druk vervolgens op Regel bewerken.

4. Vervang "Blad2" door de naam van het eerste blad om te vergelijken.

Nu je de voorwaardelijke opmaakregel op beide bladen hebt toegepast, worden duplicaten gemarkeerd volgens de opmaak die je hebt gekozen.

Zorg ervoor dat u de bereik- en celverwijzingen in de formules zo nodig aanpast om alle gegevens te dekken die u wilt vergelijken.

Voorwaardelijke opmaak lijkt misschien een beetje primitief. Als u een fijnere controle wilt, dan is Power Query wellicht het antwoord! In het volgende gedeelte bespreken we hoe u Power Query kunt gebruiken om duplicaten te vinden.

3. Hoe Power Query te gebruiken om duplicaten in werkbladen te vinden

Power Query is een tool voor gegevenstransformatie en gegevensvoorbereiding in Microsoft Excel. Het identificeren van dezelfde waarden is slechts een van de vele analysetaken die u met de tool kunt uitvoeren.

Om dit te doen, moet u eerst de gegevens in de twee werkbladen in afzonderlijke tabellen importeren. Volg deze stappen binnen elk blad:

1. Klik met de rechtermuisknop op het celbereik.

2. Kies "Gegevens ophalen uit tabel/bereik".

3. Wijzig de tabelnaam in iets passends.

Na het importeren van beide bladen, is de eerste taak het samenvoegen van de gegevens:

1. Ga naar het tabblad Gegevens.

2. Klik op "Gegevens ophalen".

3. Selecteer "Query's combineren".

4. Kies "Samenvoegen" en selecteer de twee tabellen.

5. Klik op de twee sleutelkolommen.

6. Kies "Inner" als "Join Kind" en klik op OK.

De Power Query-editor wordt geopend met de gecombineerde gegevens van beide tabellen in uw Excel-blad. U ziet twee kolommen, één van elke tabel. Aangezien u alleen geïnteresseerd bent in de dubbele waarden, kunt u de tweede kolom verwijderen.

U kunt klikken op "Sluiten en laden" in de Power Query-editor om de duplicaten naar een nieuw werkblad te laden.

Volg de voorbeelden in ons artikel over het gebruik van Power Query in Excel om meer aspecten van deze krachtige functie te ontdekken .

Excel heeft ook tools van derden en invoegtoepassingen die de mogelijkheid toevoegen om naadloos duplicaten te vinden, dus laten we een paar van die tools in de volgende sectie bekijken.

4. Hulpmiddelen en invoegtoepassingen om duplicaten op werkbladen te identificeren

Externe tools en invoegtoepassingen kunnen geavanceerde functionaliteit bieden die mogelijk niet beschikbaar is in native Excel-functies. Deze tools kunnen het proces van het vergelijken van bladen op duplicaten verder stroomlijnen.

Spreadsheets vergelijken is een Microsoft-tool waarmee u twee werkmappen naast elkaar kunt vergelijken, verschillen kunt benadrukken en gemakkelijk duplicaten kunt identificeren. Je kunt het downloaden van de Microsoft-website.

Er zijn verschillende invoegtoepassingen die u kunt installeren om het proces van het vinden van duplicaten te automatiseren. Een voorbeeld is " Duplicate Remover ". Een invoegtoepassing installeren:

1. Ga naar het tabblad Invoegen.

2. Klik op "Invoegtoepassing ophalen".

3. Zoek naar "Dupliceren".

4. Klik op "Toevoegen" op de tool van uw keuze.

5. Visueel controleren op duplicaten in twee bladen

Als al het andere faalt, gebruik dan je ogen! Met het dialoogvenster Vensters schikken in Excel kunt u meerdere werkbladen of werkmappen naast elkaar bekijken.

Hoewel duplicaten niet direct worden gevonden, kan het u helpen bij het visueel vergelijken van gegevens tussen werkbladen of werkmappen om duplicaten te herkennen. Volg deze stappen:

1. Klik op het tabblad "Weergave" in het Excel-lint.

2. Klik op "Alles schikken" in de groep "Venster".

3. Kies een opstellingsoptie, bijv. "Verticaal" of "Horizontaal".

Hierdoor worden beide bladen naast elkaar of boven elkaar weergegeven. Nu kunt u de gegevens in elk blad handmatig vergelijken om duplicaten te identificeren.

U moet door de gegevens bladeren en elke waarde visueel inspecteren om overeenkomsten te vinden.

Merk op dat deze methode niet efficiënt is voor grote datasets, omdat handmatige vergelijking vereist is. Het gebruik van de andere methoden in dit artikel is effectiever voor het vinden van duplicaten in grotere datasets.

En dat is de laatste van onze gebruikelijke methoden voor het vinden van dubbele waarden in Excel-sheets! In het volgende gedeelte geven we je enkele tips voor het voorbereiden van je werkbladen.

3 tips voor het voorbereiden van uw Excel-werkbladen

Voordat u meerdere bladen gaat vergelijken, moet u ervoor zorgen dat de kolommen en rijen van uw datasets correct zijn uitgelijnd.

Zorg ervoor dat beide Excel-bladen dezelfde structuur en dezelfde kopnamen hebben. Indien nodig kunt u de kolommen in beide bladen herschikken zodat ze bij elkaar passen.

Hier zijn drie suggesties om nauwkeurige vergelijkingen te garanderen:

1. Rangschik uw gegevens in dezelfde volgorde op beide bladen. Dit maakt het gemakkelijker voor Excel-functies om effectief te werken.

2. Normaliseer uw gegevens door consistente opmaak, hoofdlettergebruik en gegevenstypen te gebruiken. Dit voorkomt niet-overeenkomende vermeldingen als gevolg van kleine verschillen.

3. Verwijder onnodige lege rijen of kolommen, aangezien deze het vergelijkingsproces kunnen verstoren.

U kunt beoordelen hoeveel duplicatie u in een dataset heeft door de unieke waarden te tellen. Deze video doorloopt verschillende methoden:

Hoe om te gaan met fouten en inconsistenties

Inconsistenties in uw gegevens kunnen het vergelijkingsproces beïnvloeden. Hier zijn vier tips voor het oplossen van inconsistenties:

1. Controleer op verschillen in gegevenstypen, zoals het combineren van tekst en numerieke waarden in dezelfde kolom.

2. Zorg ervoor dat er een consistente opmaak wordt gebruikt voor datums, getallen en andere gegevenstypen.

3. Controleer uw gegevens op ontbrekende of onjuiste invoer en werk ze indien nodig bij.

4. Standaardiseer afkortingen of inconsistente naamgevingsconventies binnen uw datasets.

Laatste gedachten

Het vinden van duplicaten in twee Excel-werkbladen is een essentiële taak voor gegevensbeheer en -analyse, waarbij gegevensintegriteit en nauwkeurigheid worden gegarandeerd. Excel biedt meerdere technieken om duplicaten te identificeren, elk met zijn eigen voordelen en beperkingen.

De keuze van de methode hangt af van de behoeften van de gebruiker, de omvang en complexiteit van de dataset en het gewenste resultaat. Voor kleinere datasets en eenvoudige vergelijkingen kan het gebruik van VERT.ZOEKEN , AANTAL.ALS of voorwaardelijke opmaak voldoende zijn.

Voor grotere datasets of complexere datatransformaties is Power Query een krachtige en flexibele tool die een breed scala aan datavoorbereidingstaken aankan, waaronder het vinden van duplicaten.

Om het af te ronden, is het vergelijken van Excel-bladen voor duplicaten een superhandige vaardigheid om in je gereedschapskist te hebben. Met de trucs in dit artikel kun je die vervelende duplicaten herkennen en je gegevens brandschoon houden.

Vertrouw ons, als je hier beter in wordt, zul je door je datataken heen vliegen en indruk maken op iedereen om je heen!