Office-Blog

Duplikate entfernen oder Unikate finden in Excel 2010: Wie funktioniert’s, wo sind die Fallstricke?

04.04.2012   Dominik Petri

Beim Zusammenführen von Daten aus unterschiedlichen Quellen kommt es vor, dass Datensätze doppelt eingefügt werden. Bis Excel 2003 ließen sich solche Dubletten per Spezialfilter aussortieren.

Seit Excel 2007 gibt es den Befehl Duplikate entfernen. Obwohl beide Varianten scheinbar das Gleiche tun, führen sie mitunter zu unterschiedlichen Ergebnissen! Grund genug, die Funktionsweisen genau zu analysieren.

Duplikate entfernen zweimal im Menüband

Neu ab Excel 2007: Die Schaltfläche Duplikate entfernen in der Registerkarte Daten und beim Arbeiten mit »intelligenten« Tabellen in der Registerkarte Tabellentools .

Variante 1: Über die Schaltfläche „Duplikate entfernen“

Es reicht aus, eine Zelle im zu durchsuchenden Zellbereich zu markieren, bevor ich den Befehl Duplikate entfernen anklicke.

Im sich dann öffnenden Dialogfeld gebe ich an, ob die erste Zeile meiner Liste die Spaltenüberschriften darstellt und welche Spalten Excel zur Suche von Duplikaten verwenden soll.

Die Schritte zum Entfernen von Duplikaten

Wie bestimmt Excel, ob es sich bei einem Datensatz um ein Duplikat handelt?

Excel durchsucht die Liste von oben nach unten. Ein doppelter Datensatz liegt dann vor, wenn es bereits eine Zeile in der Liste gibt, bei der in allen im o.g. Dialogfeld ausgewählten Spalten eine exakte Übereinstimmung vorliegt. Hat Excel eine solche Zeile gefunden, wird sie vollständig gelöscht.

Duplikate entfernen mit Erfolgsmeldung

Achtung Datenverlust!

Doppelte Datensätze werden tatsächlich entfernt! Wer also seine Originaldaten nicht ungewollt zerstören möchte, sollte vorher eine Kopie davon anfertigen und mit dieser Kopie arbeiten. Unmittelbar danach lässt sich das Entfernen der Duplikate natürlich noch mit Strg+Z rückgängig machen.

Unterschiedliche Formate in einer Spalte haben Folgen

Beim Zusammenkopieren von Datensätzen aus mehreren Quellen kommt es schon mal vor, dass Datensätze unterschiedlich formatiert sind. Um dies zu simulieren, mache ich das Entfernen der Duplikate rückgängig und ändere die Formatierung des Geburtsdatums in Zelle C13 von TT.MM.JJJJ auf TT.MM.JJ. Führe ich nun die Suche nach Duplikaten erneut aus, wird dieser Datensatz nicht als Duplikat erkannt!

Duplikate entfernen bei unterschiedlichen Formaten

Diese Tatsache ist kein Fehler von Excel, sondern in der Hilfe entsprechend dokumentiert: „Doppelte Werte werden durch den Wert bestimmt, der in der Zelle angezeigt wird, und nicht unbedingt durch den in der Zelle gespeicherten Wert.“

Schlussfolgerung: Datensätze müssen vor dem Entfernen von Duplikaten einheitlich formatiert werden.

Variante 2: Spezialfilter

Im Unterschied zur oben gezeigten Variante ist es bei der Verwendung des Spezialfilters zwingend erforderlich, dass die Spalten Überschriften tragen. Außerdem löscht der Spezialfilter die doppelten Datensätze nicht, sondern blendet sie aus oder erstellt eine Liste der Unikate an einer anderen Stelle.

Liste an gleicher Stelle filtern

Es ist wiederum ausreichend, in der Liste eine einzige Zelle zu markieren. Nach einem Klick auf die Schaltfläche Erweitert in der Registerkarte Daten öffnet sich das schon aus früheren Excel-Versionen bekannte Dialogfeld des Spezialfilters.

Ich überprüfe, ob Excel den Listenbereich korrekt erkannt, setze das Häkchen vor „Keine Duplikate“ und klicke ich auf OK.

Duplikate per Spezialfilter auffinden

Excel zeigt mir nun in der Statusleiste wie viele eindeutige Datensätze gefunden wurden. An der Blaufärbung der Zeilennummern (und dem Fehlen einiger Zeilen) erkennen Sie, dass die Duplikate nicht gelöscht, sondern lediglich ausgeblendet wurden.

Ergebnis des Spezialfilters

Unikate an eine andere Stelle kopieren

Anstatt die doppelt vorkommenden Datensätze auszublenden, kann der Spezialfilter die Liste mit Unikaten auch an einer anderen Stelle ausgeben.

Dazu lösche ich zunächst den Filter, klicke wiederum auf Erweitert und wähle die Option An eine andere Stelle kopieren. Dadurch wird es möglich, unter Kopieren nach einen Zielbereich (es reicht eine einzelne leere Zelle) auszuwählen, in den Excel die Liste ausgeben soll. Natürlich setze ich auch wieder das Häkchen vor Keine Duplikate.

Per Spezialfilter Unikate kopieren

Wenn ich den Ausgabebereich mit Spaltenüberschriften versehe, kann ich analog zur Variante mit der Schaltfläche Duplikate entfernen ebenfalls nur bestimmte Spalten zur Suche nach Duplikaten verwenden.

Unikate in einem anderen Arbeitsblatt ausgeben

Falls ich im Ausgabebereich des Spezialfilters ein anderes Arbeitsblatt in der gleichen oder einer anderen Arbeitsmappe angebe, quittiert Excel dies mit folgender Fehlermeldung:

Fehlermeldung beim Spezialfilter

Mit einem Trick geht es trotzdem! Ich starte den Spezialfilter in dem Arbeitsblatt (hier: „Ausgabe“), in dem das Ergebnis ausgegeben werden soll und wähle wieder die Option „An eine andere Stelle kopieren“. Anschließend wähle ich manuell den Listenbereich, den Ausgabebereich und setze wieder das Häkchen vor Keine Duplikate – und schon ist die Liste ohne doppelte Datensätze im Blatt „Ausgabe“!

Ergebnis des Spezialfilters in anderes Arbeitsblatt kopieren

Unterschiedliche Formate haben keinen Einfluss auf das Ergebnis

Im Unterschied zur Variante „Duplikate entfernen“ hat der Spezialfilter kein Problem damit, wenn die Daten in den einzelnen Spalten unterschiedlich formatiert sind. Trotz einer Änderung des Zahlenformats des Geburtsdatums in Zelle C13 (siehe oben) wird dieser Datensatz vom Spezialfilter (korrekt) als Duplikat erkannt!

Fazit

Ich verwende zum Löschen von Duplikaten weiterhin den Spezialfilter, denn

  • die Datenbasis wird nicht verändert (es werden keine Daten gelöscht, sondern nur ausgeblendet),
  • ich kann das Ergebnis an einer anderen Stelle ausgeben, ohne vorher die Daten selbst dorthin kopieren zu müssen,
  • das Format der Zellen spielt keine Rolle; zum Erkennen von Duplikaten kommt es auf den Inhalt der Zelle an und nicht auf ihre Darstellung.

Über den Autor

Dominik Petri

- Dipl.-Betriebswirt mit 20 Jahren Berufserfahrung in Großbanken
- Chartered Financial Analyst® und Spezialist für Business Intelligence (BI)
- Zertifizierter Office-Trainer mit den Schwerpunkten Excel und VBA
- Spezialist der ersten Stunde für Power BI mit Excel
- Gründer der offiziellen Microsoft Power BI User Group Frankfurt
- Unterstützt Firmen beim Einführen und Verwenden der BI-Tools von Microsoft
- Projekterfahrener Excel-Berater, VBA-Programmierer für Analysetools
- Autor für Microsoft Press, dpunkt.verlag und »Modernes Reporting mit Excel«

10 Comments so far

ChristianPosted on  2:54 pm - Mai 20, 2015

Ich versuche gefilterte Daten die Duplikate zu entfernen (nur die gefilterten), aber Excel entfernt immer Duplikate des gesamten Datenhaushalts.

Weiß jemand wie Excel nur die gefilterten Duplikate entfernt ?

Danke vorab
Christian

EricPosted on  9:37 am - Okt 23, 2014

Hallo,
vielen Dank! Durch das Kopieren an andere Stelle konnte ich endlich die Duplikate anzeigen.

Grüße
Eric

Ralf FrankePosted on  1:32 pm - Jan 7, 2014

Mich interessiert, ob es auch möglich ist zwei Listenbereiche durchsuchen zu lassen; z.B. um die Gemeinsamen aus zwei Tabellen (zwei Blätter) in eine neue Tabelle zu überführen?
Vielen Dank.

MarenPosted on  12:16 pm - Dez 20, 2013

Danke für die gute Erklärung – hat mir weitergeholfen!
:)

SoldanskiPosted on  3:54 pm - Nov 25, 2013

Das waren gute Ratschläge, doch sehe ich immer noch nicht, welche Daten gelöscht wurden. und genau die möchte ich zuvor kontrollieren!

DirkPosted on  4:24 pm - Aug 22, 2012

genial! Das löst sogar Probleme die ich noch gar nicht hatte :-)

MarcPosted on  9:16 pm - Apr 10, 2012

sehr professionell aufgearbeitet. Gute Analyse der beiden Techniken

FrankPosted on  9:21 pm - Apr 5, 2012

Super Beitrag und sehr hilfreich!
Ich kannte beide Varianten gar nicht.

Bitte mehr davon!