Deduplizieren in Excel: Duplikate finden und entfernen

Das Deduplizieren von Daten ist ein häufiges Problem in Excel. Excel bietet eine praktische Funktion für einfaches Deduplizieren, aber diese Methode ist endgültig und schwer zu überprüfen.

Unsere Excel-Experten werden oft hinzugezogen, um bei der Deduplizierung komplexer Dateien zu helfen, beispielsweise wenn mehrere Quellen kombiniert werden müssen.

Anmerkung: PerfectXL Academy wurde mit KI übersetzt. Haben Sie Verbesserungsvorschläge? Dann senden Sie uns eine E-Mail an info@Perfectxl.com.

Einfaches Deduplizieren

Excel bietet hervorragende Werkzeuge für einfache Deduplikationen. Zum Beispiel, wenn Sie eine Liste von E-Mail-Adressen haben, können Sie Excel problemlos auffordern, Duplikate zu finden und zu entfernen.

Duplikate finden

Wählen Sie den Bereich aus, in dem Excel nach Duplikaten suchen soll:

Klicken Sie im Hauptmenü auf Start > Bedingte Formatierung > Zellregeln hervorheben > Duplikate:

Klicken Sie auf „OK“ im Pop-up-Fenster:

Als Ergebnis zeigt Excel alle Werte an, die mehr als einmal vorkommen.

Remove duplicate values

Um die Duplikate zu entfernen, wähle erneut die betreffende Spalte aus und klicke auf Daten > Duplikate entfernen:

Sobald du im Dialogfeld auf „OK“ klickst, entfernt Excel alle Duplikate (sodass immer nur ein eindeutiger Wert übrig bleibt). Im Pop-up-Fenster siehst du genau, wie viele Einträge entfernt wurden und wie viele übrig bleiben.

Hinweis: Du musst nicht die Schritte zum Finden und Hervorheben der Duplikate durchgehen. Du kannst auch direkt mit der Duplikatbeseitigung fortfahren.

Komplexe Duplikatbeseitigung

Schau dir die untenstehende Adressdatei an. Es gibt vier Spalten:

• Vorname
• Nachname
• Geburtsdatum
• E-Mail-Adresse

Wie duplizierst du eine solche Datei? Namen und Geburtsdaten können mehrmals erscheinen, müssen aber nicht unbedingt auf einen Doppel-Eintrag hinweisen. Du könntest die Duplikate anhand einer eindeutigen E-Mail-Adresse beseitigen, aber was passiert, wenn mehrere Personen im Haushalt dieselbe E-Mail-Adresse verwenden?

Wenn wir die gleichen Schritte wie im ersten Beispiel durchgehen, finden wir die folgenden Duplikate:

Das Problem der standardmäßigen Duplikatentfernung in Excel

Wenn wir Excel nun bitten, die Duplikate auf die gleiche Weise wie im ersten Beispiel zu entfernen, verschwinden nur die Duplikate, die in allen vier Spalten erscheinen:

Wie Sie sehen können, erscheint Sam de Wit immer noch zweimal in unserer Datenbank.

Wir können eine zweite Runde der Duplikatentfernung basierend auf der E-Mail-Adresse durchführen, aber in diesem Fall bleibt Tess Bakker zweimal in unserer Tabelle, weil sie mit zwei verschiedenen E-Mail-Adressen registriert ist:

Wie gehen Sie mit solchen Situationen um, wenn es sich um viel größere Dateien mit mehr Daten handelt oder wenn mehrere Quellen kombiniert werden müssen?

Manuelle Duplikatentfernung

Die manuelle Duplikatentfernung erfordert etwas mehr Zeit für die Einrichtung, hat jedoch mehrere Vorteile. Sie ermöglicht es Ihnen, die ursprüngliche Liste auf einem ersten Arbeitsblatt zu behalten (und neue Daten hinzuzufügen), während Sie die deduplizierte Liste auf einem zweiten Arbeitsblatt erstellen. Auf diese Weise können Sie das Ergebnis immer mit den Quelldaten vergleichen.

Siehe Beispiel unten.

Die oben stehende Tabelle zeigt, ob eine gegebene E-Mail-Adresse mehr als einmal erscheint, und weist jeder einzigartigen Erscheinung eine eindeutige Sequenznummer zu. Natürlich sind verschiedene Funktionen erforderlich, um diese Einrichtung zu realisieren.

Functions

In Spalte E (Doppelt) verwenden wir die Funktion COUNTIF, um zu bestimmen, ob die E-Mail-Adresse in Spalte D bereits im Bereich oben aufgetreten ist. Zum Beispiel lautet die Formel in Zelle E16: =COUNTIF(D$2
;D16). Das Ergebnis ist 0.

In Spalte F (Erstmaliges Auftreten) verwenden wir die IF-Funktion, um zu überprüfen, ob es sich um das erste Auftreten dieser E-Mail-Adresse handelt oder nicht. In Zelle F16 lautet die Formel: =IF(E16=0;1;0). Das Ergebnis ist 1.

Schließlich vergeben wir jeder einzigartigen E-Mail-Adresse eine Seriennummer in Spalte G (Rang). Dies tun wir mit einer Kombination aus der IF-Funktion und der SUM-Funktion. In Zelle G16 lautet die Formel: =IF(F16=1;SUM(F$2);0). Das Ergebnis ist 15.

Ergebnis

In einem zweiten Arbeitsblatt veröffentlichen wir dann die Liste der eindeutigen E-Mail-Adressen mithilfe der Kombination von INDEX und MATCH.

INDEX und MATCH

Wie sieht das in diesem Arbeitsblatt aus? In Spalte B (Match) verwenden wir die MATCH-Funktion, um die Sequenznummer in Spalte A im Arbeitsblatt mit den Originaldaten zu finden. Zum Beispiel enthält Zelle B16: =MATCH(A16;‘Arbeitsblatt1’!G
;0). Das Ergebnis dieses Abgleichs ist 16, weil die Sequenznummer 15 im ersten Arbeitsblatt der Zeile 16 entspricht.

Anschließend füllen wir die Spalten C bis F mit den Daten, die im ersten Arbeitsblatt in Zeile 16 für Vorname (Spalte A), Nachname (Spalte B), Geburtsdatum (Spalte C) und E-Mail-Adresse (Spalte D) zu finden sind, mithilfe der INDEX-Funktion. In den Zellen C16, D16, E16 und F16 steht:

C16: =INDEX(‘Arbeitsblatt1’!A

;$B16) D16: =INDEX(‘Arbeitsblatt1’!B
;$B16) E16: =INDEX(‘Arbeitsblatt1’!C
;$B16) F16: =INDEX(‘Arbeitsblatt1’!D
;$B16)

Vorteil

Der Vorteil dieser Methode ist, dass Sie das Arbeitsblatt mit den Originaldaten beibehalten und jederzeit neue Daten hinzufügen können. Um die Liste der eindeutigen Adressen im zweiten Arbeitsblatt zu vervollständigen, müssen Sie lediglich den Formelnbereich erweitern.

Deduplication ist eine maßgeschneiderte Aufgabe

Die oben genannten Beispiele sind recht einfach, aber in der Realität sind Deduplizierungsaufgaben oft komplex. Zum Beispiel, wenn Sie mehrere Datenbanken oder Dateien zusammenführen möchten oder wenn Sie die Deduplizierung auf mehr als einer Spalte basieren möchten. In diesem Fall können Sie mit einem „Schlüssel“ arbeiten, der Elemente mehrerer Spalten zusammenführt.

In der untenstehenden Tabelle haben wir in Spalte E (Schlüssel) eine Kombination aus dem ersten Buchstaben des Vornamens, dem Nachnamen und der E-Mail-Adresse erstellt. Danach suchen wir in Spalte F nach Schlüsseln, die mehr als einmal erscheinen.

Die Bestimmung des Schlüssels und der Bedingungen für die Duplikatbereinigung kann ein komplizierter Prozess sein, und es können viele verschiedene Szenarien auftreten. Aus diesem Grund ist komplexe Duplikatbereinigung oft eine maßgeschneiderte Aufgabe. Bei PerfectXL helfen wir Ihnen gerne bei Duplikatproblemen. Kontaktieren Sie uns für eine Beratung oder um die Duplikatbereinigung an uns auszulagern.