Ein alltägliches Problem: Duplikate in Excel-Listen. Wie du sie finden und ausmerzen, sogar mehrere Tabellen miteinander vergleichen kannst. So geht’s:
Durchschnittliche Lesezeit: ca. 4–6 Minuten
Wenn du mit einem derartigen Problem konfrontiert bist, kannst du entweder sofort ans Werk gehen oder die verschiedenen Möglichkeiten zunächst an einer Dummy-Liste überprüfen. Wir verwenden hier eine Liste, die wir mit dem Fake Name Generator erstellt haben. Deine freundliche künstliche Intelligenz, die nur einen Klick im Browser entfernt ist, steht dir ebenfalls gerne zur Seite und hilft dir beim Erstellen von Dummy-Listen zum Üben.
Ursprüngliche Daten behalten oder nicht?
Stelle dir zuerst diese Frage: Möchtest du deine Ausgangsdaten behalten? Wenn ja, kopiere deine Tabelle zunächst mit «Strg + C» und füge sie an geeigneter Stelle mit «Strg + V» in deine Tabellenkalkulation ein. Oder erstelle ein Duplikat deiner Excel-Mappe.
Duplikate entfernen mit «Duplikate entfernen»
Manchmal kann das Leben so einfach sein. Markiere deine Tabelle, die du überprüfen möchtest, und gehe zu «Daten > Duplikate entfernen».
Wenn deine Reihen Überschriften haben und du diese mit ausgewählt hast, klicke auf die Checkbox «Daten haben Überschriften» und wähle im Anschluss die Kriterien aus.
Wenn du mit einer eindeutigen Nummer arbeitest (hier ID), genügt es, wenn du diese zum Löschen der Duplikate verwenden. Wenn du doppelte Werte suchts, die sich über eine Kombination mehrerer Zelleninhalte definieren, wähle im Dialog «Duplikate entfernen» diese Zellen entsprechend aus.
Duplikate entfernen mit «Eindeutig»
Auch hier macht uns Excel das Leben leicht. Klicke in deinem Spreadsheet in eine Zelle, die du als Startpunkt für deine neue Tabelle ohne Duplikate nehmen möchtest, und gib =EINDEUTIG([Namen deiner Tabelle]) ein. Wenn du deine Daten noch nicht als Tabelle definiert hast, wähle vorher den Bereich aus und klicke auf «Start > Als Tabelle formatieren». Excel vergibt automatisch Namen für deine Tabellen, beginnend mit «Tabelle1», du kannst aber ohne Weiteres eigene Namen vergeben.
Der Vorteil dieser Methode ist, dass du die Tabelle mit der Maus auswählen kannst, statt den Namen eingeben zu müssen. Gehe dazu mit der Maus in die linke obere Ecke der Tabelle, bis sich der Mauszeiger in einen Pfeil nach rechts unten verwandelt (s. Abbildung 4) und bestätige mit einem Klick. Nun musst du nur noch die Schlussklammer in deiner Formel ergänzen, und das war es schon.
Auch weitere Zeilen, die du deiner Ursprungstabelle hinzufügst, werden auf Duplikate überprüft, sodass du den Vorgang nicht wiederholen musst.
Duplikate entfernen mit Power Query (Case sensitive)
Eine weitere Möglichkeit, Duplikate zu finden, ist es, eine «Abfrage und Verbindung» mit Power Query einzurichten. Das ist ein Vorgehen, das wir bereits in einem weiteren Artikel beschrieben haben. Dort kannst du auch nachlesen, wie du eine Verbindung mit externen Daten herstellen kannst.
[Weiterlesen: So importierest du den Inhalt von PDF-Dokumenten in Excel]
Diese Methode unterscheidet sich von der Lösung mit Formel darin, dass Änderungen, die du in der Ausgangstabelle vornimmst, erst durch eine Aktualisierung in der Zieltabelle (Rechtsklick > Aktualisieren) wirksam werden.
Zusätzlich berücksichtigt Power Query Gross-/Kleinschreibung. Alle vorhergehenden Methoden taten das nicht. Wenn du also Einträge hast, die sich in den Werten zwar gleichen, aber Unregelmässigkeiten in der Gross-/Kleinschreibung aufweisen, werden diese mit den zuvor beschriebenen Methoden entfernt. Mit Power Query funktioniert das nur durch einen zusätzlichen Schritt.
Nehmen wir zunächst einmal an, dass deine Tabelle keine Inkonsistenzen in der Schreibweise aufweist. Klicke in eine Zelle deiner Ausgangstabelle und gehe zu «Daten > Aus Tabelle/Bereich». Es öffnet sich der Power-Query-Editor. Nun musst lediglich per Mausklick und gedrückter Shifttaste die Spalten auswählen, die du zur Überprüfung verwenden möchtest. Klicke anschliessend mit der rechten Maustaste auf die Kopfzeile und wähle «Duplikate entfernen».
Nun klicke auf «Schliessen und laden», um die bestehende Tabelle mit deiner Abfrage zu ersetzen oder «Schliessen und laden in …», um die Power Query-Abfrage an einer anderen Stelle zu speichern.
Wenn du nun feststellen musst, dass in der neu erzeugten Tabelle noch Duplikate existieren, die sich nur durch Gross-/Kleinschreibung unterscheiden, kannst du diese natürlich in der Ausgangstabelle ausmerzen. Einfacher geht es hingegen so: Rufe deine Abfrage erneut auf (Abfragen und Verbindungen) und klicke auf den Schritt vor «Entfernte Duplikate» (s. Abbildung 7) und füge folgenden Zwischenschritt ein.
Nun wählst du mit gedrückter Shifttaste die Reihen aus, die die Unregelmässigkeiten aufweisen, und klicke auf «Transformieren > Format > Ersten Buchstaben im Wort grossschreiben».
Nun musst du nur noch deine Änderung bestätigen (Start > Schliessen & laden), und auch diese Duplikate gehören der Vergangenheit an.
Bonus: Tabelleninhalte vergleichen
Eine häufige Frage ist: Welche Daten aus dieser Liste sind auch in jener Liste enthalten? Um diese Aufgabenstellung zu bearbeiten, kopiere die Daten, die du miteinander vergleichen möchtest, auf ein gemeinsames Datenblatt. Das Vergleichen von umfangreicheren Daten in verschiedenen Arbeitsmappen ist natürlich auch ohne diesen Zwischenschritt möglich. Die angepasste Excel-Formel dafür erhältst du von mir ebenso.
Jetzt fügst du der Tabelle, die du überprüfen möchtest, eine weitere Spalte hinzu. In die oberste Zelle nach der Kopfzeile gibst du folgende Formel ein:
=WENN(ZÄHLENWENN(Tabelle2[Spalte1];A2);“Duplikat“;“OK“)
Für den Vergleich über mehrere Arbeitsmappen hinweg, verwendest du diesen Befehl:
=WENN(ZÄHLENWENN([Arbeitsmappe.xlsx]Tabelle2!$A:$A;A2);“Duplikat“;“OK“)
Du beginnst mit einer WENN-Bedingung, gefolgt von einer ZÄHLENWENN-Bedingung. Diese prüft deine ausgewählte Zelle (A2), ob darin Daten aus einer der Zellen in Spalte 1 von Tabelle 2 enthalten sind. ZÄHLENWENN gibt hier der WENN-Bedingung entweder den Wert «wahr», wenn der Wert enthalten ist, oder «falsch», wenn der Wert nicht enthalten ist, zurück. Die WENN-Bedingung gibt letztlich nur die Meldung «Duplikat» für wahr und «OK» für falsch in die jeweilige Zelle aus. Wenn du deine Tabellen und Spalten anders benannt hast, oder mit dem Vergleich in anderen Zellen beginnen möchtest, gleiche die Formel entsprechend an.
Kommst du mit einer vereinfachten Darstellung und «1» für wahr (also Duplikat) und «0» für falsch zurecht, kannst du die Formel auch auf =ZÄHLENWENN(Tabelle2[Spalte1];A2) verkürzen.
[Weiterlesen: Wie du mit Filterfunktionen und der WENN-Bedingung das Grussformelproblem in Excel löst]
Wenn du bis hierher alles richtig gemacht hast, dann sollte dein Ergebnis wie oben aussehen. Die rote Kennzeichnung der Duplikate wurde mit «Bedingte Formatierung» erreicht.
Die Duplikate kannst du anschliessend mit der Filterfunktion aussortieren und entfernen.
Duplikate erkennen und ausmerzen
Wie du siehst, für dieses alltägliche Excel-Problem gibt es mehrere Lösungen. Mit etwas Geduld und Ausdauer jedoch, können wir uns Formeln und Automatisierungen zurechtlegen, die diese Aufgabe ein für alle Mal lösen.
powered by Borlabs Cookie