In diesem zweiteiligen Tutorial zeigen wir ihnen zahlreiche verschiedene Möglichkeiten, wie Sie in Excel schnell und einfach doppelte Einträge finden und markieren können, Listen auf Übereinstimmung prüfen oder die Eingabe doppelter Daten verhindern können.
Hintergrund & Aufgabenstellung
Welcher Excel-Anwender kennt die folgenden Aufgabenstellungen nicht?
Sie müssen eine umfangreiche Liste mit Rechnungs-, Kunden- oder Artikelnummern zusammenführen oder importieren und dann weiterverarbeiten, Sie bekommen eine Vielzahl von Adressdaten und sollen ein Mailing vorbereiten oder Sie werden gebeten sicherzustellen, dass zwei Listen identische Inhalte haben, leider sind diese aber unterschiedlich sortiert.
In allen Fällen geht es darum, mit möglichst wenig Aufwand zu ermitteln, ob Duplikate bzw. nur Unikate in den Listen enthalten sind. Bei umfangreichen Listen (d.h. bereits bei mehr als 20 Einträgen) ist eine manuelle Prüfung aus Zeit- und Fehlergründen nicht möglich bzw. sinnvoll.
Für die häufigsten Anwendungsfälle wollen wir ihnen im Folgenden verschiedene Lösungsansätze aufzeigen. Dabei wird versucht, die jeweilige Fragestellung möglichst ohne zusätzliche Hilfsspalten mit einer oder wenigen universellen Formeln zu lösen. Auf die Darstellung der ab Excel 2007 implementierten Funktion „Duplikate entfernen“ (Registerkarte „Daten“) soll hier verzichtet werden, da diese Methode mit bestimmten Einschränkungen verbunden ist.
Dieser erste Teil des Tutorials beschreibt Lösungen für die Aufgabenstellungen:
- Doppelte Einträge in einer Liste (z.B. farblich) hervorheben
- Ausschließlich Duplikate mehrerer Spalten bzw. Datensätze markieren
- Mit einer einzigen Formel jede beliebige Liste auf doppelte Einträge prüfen
Doppelte Einträge in einer Liste (farblich) hervorheben
Eine der einfachsten und schnell umzusetzenden Möglichkeiten Doppeleinträge zu erkennen und zu markieren führt über die bedingte Formatierung in Excel.
Entsprechend formatierte Zellen können sowohl für bestehende wie auch für noch zu erstellende Listen bzw. eine Kombination aus beiden eingesetzt werden. In einem derart formatierten Bereich werden mit dieser Methode alle Werte die doppelt vorkommen, gemäß ihren individuellen Vorgaben (z.B. farbig) markiert. Wird ein zusätzlicher Wert eingetragen der bereits vorhanden ist, werden dieser und ggf. der gleiche Wert, sofern vorhanden, farbig hinterlegt.
Die Umsetzung erfolgt über das Register „Start“ durch klicken auf „Bedingte Formatierung“, wo man im sich öffnenden Menü eine neue Regel anlegt. Ab Excel 2007 bietet sich hier die Möglichkeit den Eintrag „Nur eindeutige oder doppelte Werte formatieren“ zu nutzen. Über die Schaltfläche „Formatieren“ stellen Sie die gewünschten Formatattribute (z.B. Farbe, Schrift, Rahmen etc.) ein.
Im älteren Excel 2003 muss dies über eine Formel umgesetzt werden, sowie im Screenshot gezeigt.
Als Funktion wird dabei ZÄHLENWENN genutzt. Wollen Sie bspw. die gesamte Spalte A berücksichtigen, dann lautet die Formel: =ZÄHLENWENN(A:A;A1)>1
D.h. der erste Parameter gibt den Bereich an, in diesem Fall die gesamte Spalte A, und der zweite muss auf die obere linke Zelle des selektierten Bereichs verweisen. Diese Formel ist auch ab Excel 2007 erforderlich, sofern Sie alle Werte mit einer höheren Mindesthäufigkeit markieren wollen, zum Beispiel nur dann, wenn sie mindestens drei Mal vorkommen.
Die Formel lautet dann analog: =ZÄHLENWENN(A:A;A1)>3
Sofern sich die Duplikatsuche auf mehrere Spalten/Kriterien beziehen soll, wird es etwas komplizierter, folgt aber dem gleichen Prinzip.
Ab Excel 2007 gibt es nämlich neben ZÄHLENWENN auch noch die Funktion ZÄHLENWENNS. Das zusätzliche „S“ am Ende erlaubt die Berücksichtigung mehrerer Kriterien (maximal 127 verschiedene sind möglich).
Der allgemeine Synthax sieht folgendermaßen aus:
ZÄHLENWENNS(Kriterienbereich1;Kriterien1;[Kriterienbereich2; Kriterien2]…)
In unserer Beispieldatei (siehe Screenshot) wären die Spalten I und J zu prüfen, so dass bei der bedingten Formatierung folgende Formel einzusetzen ist:
=ZÄHLENWENNS(I:I;I8;J:J;J8)>1
Sollten Sie tatsächlich noch mit der überalterten Version Excel 2003 arbeiten, hier noch der Hinweis, dass dort die Umsetzung nur mittels einer Hilfsspalte und einer Matrixformel möglich ist.
Wichtig: Matrixformeln müssen immer mit „Shift+Strg+Return“ abgeschlossen werden => dann werden die geschweiften Klammern automatisch ergänzt. Wir empfehlen ihnen dazu unser Spezialtutorial „Matrixformeln und deren Verwendung im Financial Modelling“, dass Sie hier finden.
Für unser Beispiel würde die Formel in der Hilfsspalte folgendermaßen aussehen:
{=SUMME(WENN(($I$8:$I$35=I8)*($J$8:$J$35=J8);1))}
In der bedingten Formatierung wäre dann auf diese Hilfszelle/-spalte zu referenzieren. Falls der Wert größer als 1 ist, gibt es Doppelte bzw. Mehrfacheinträge.
Abschließend sei darauf hingewiesen, dass die in diesem Beispiel vorgestellten Methoden mit Text, Zahlen aber auch gemischten Einträgen funktionieren (auch Email-Adressen, Hyperlinks etc.). Leere Zellen werden bei Verwendung der ZÄHLENWENN- bzw. ZÄHLENWENNS-Funktion nicht markiert (Nullwerte, nicht >1). Nicht unterschieden wird zwischen Groß- und Kleinschreibung, d.h. TEXT, text und TeXT würden als doppelt markiert.
Ausschließlich Duplikate mehrerer Spalten bzw. Datensätze markieren
Diese Aufgabenstellung ähnelt im Grunde der vorherigen mit dem kleinen aber wichtigen Unterschied, dass ausschließlich die doppelten bzw. mehrfach vorhandenen Einträge gekennzeichnet werden sollen. Lautet die Aufgabenstellung bspw. eine Mailingliste um Doppeleinträge zu bereinigen und „Max Mustermann“ käme dreimal vor, dann würden mit der weiter oben beschriebenen Methode alle 3 Einträge farbig markiert. Mit der im Folgenden beschriebenen Lösung würden aber nur 2 der 3 Einträge gekennzeichnet. Bei Doppeleinträgen nur einer von beiden. Das macht deshalb Sinn, da Sie am Ende ja den „Max Mustermann“ einmal in der Liste behalten wollen.
Für ein anschauliches Beispiel legen wir die Latte gleich ein wenig höher und wollen 3 verschiedene Kriterien berücksichtigen. Wenn Sie die Vorgehensweise nachvollzogen haben, bekommen Sie den „Max Mustermann“, also mit nur einem Kriterium, spielend alleine hin.
Nehmen wir an, die Kriterien stehen in den Spalten B, C und D, dann ließe sich die Aufgabenstellung über eine zusätzliche Hilfsspalte lösen (siehe Screenshot). In der Zelle F9 wäre dann folgende – zunächst etwas abschreckende – Matrixformel einzufügen:
{=WENN(VERGLEICH(B9&C9&D9;B$1:B$100&C$1:C$100&D$1:D$100;0)=ZEILE();“-„;“Duplikat“)}
(Hinweis: Matrixformeln müssen immer mit „Shift+STRG+Return“ abgeschlossen werden => dann werden die geschweiften Klammern automatisch ergänzt)
Zentraler Bestandteil der Formel ist die VERGLEICH-Funktion in der Mitte (oben rot markiert). Mit der Funktion VERGLEICH wird in einem Bereich von Zellen nach einem angegebenen Element gesucht und anschließend die relative Position dieses Elements im Bereich zurückgegeben. Klingt theoretisch, ist aber relativ simpel. Markieren Sie bspw. in der Excel-Beispieldatei nur den (oben rot markierten) Vergleich-Teil der Formel und drücken anschließend die Taste „F9“. Excel gibt ihnen dann das Ergebnis, statt der Formel aus. Hier bspw. den Wert 9.
Es wird also nachgeschaut wo in den ersten 100 Zeilen (kann beliebig verändert werden => siehe Beispielformel) der Spalten B, C und D die Werte von B9, C9 bzw. D9 vorkommen und dann die Position, also hier die Zeilennummer, zurückgegeben. Wenn ein Eintrag (alle 3 Kriterien identisch) nur einmal vorkommt, erhalten wir jeweils die Zeilennummer in der dieser Eintrag steht, kommt er doppelt oder mehr als zweimal vor, bekommen wir jeweils die Zeilennummer des ersten Eintrages.
Das können wir nutzen und schachteln eine entsprechende WENN-Funktion um die VERGLEICH-Funktion, die uns bei Unikaten bzw. dem ersten Vorkommen von Mehrfacheinträgen einen Strich „-„ (beliebige andere Texte oder Zahlenwerte sind hier möglich) und bei allen Doppelten den Eintrag „Duplikat“ (ebenfalls beliebig anpassbar) ausgibt. Da bei Doppelten beim ersten Eintrag das Ergebnis von VERGLEICH der Zeilennummer entspricht (=Zeile()), werden wie gewünscht nur die Mehrfacheinträge, nicht der „Originaleintrag“ ausgewiesen (so wie im Screenshot-Beispiel der Eintrag von Helmuth Schachner => nur in Zeilen 19 und 20 erscheint Duplikat, nicht in Zeile 11).
Anschließend können Sie ihren Datenbestand nach der Hilfsspalte filtern oder sortieren („Sortieren und Filtern“ => „Benutzerdefiniertes Sortieren …“) und ggf. Mehrfacheinträge löschen.
Hinweis: Duplikate in der Originaltabellen (Spalten B bis D) könnten theoretisch wieder über eine bedingte Formatierung automatisch markiert werden (siehe Anleitung im ersten Beispiel oben). Wenn diese aber sowieso gelöscht werden sollen, kann man sich diesen Schritt allerdings sparen.
Mit einer einzigen Formel jede beliebige Liste auf doppelte Einträge prüfen
In diesem Anwendungsfall geht es darum für eine beliebige Liste sicherzustellen, dass keine doppelten Einträge vorkommen, d.h. nur Unikate vorhanden sind. Dies lässt sich schnell und einfach mit nur einer einzigen Formel umsetzen.
Geben wir unserer zu prüfenden Liste bspw. den Namen „Liste2“ (sie können natürlich jeden beliebigen anderen Namen vergeben), dann beantwortet die folgende Matrixformel unsere Fragestellung:
{=MAX(ZÄHLENWENN(Liste2;Liste2))=1}
(Hinweis: Matrixformeln müssen immer mit „Shift+STRG+Return“ abgeschlossen werden => dann werden die geschweiften Klammern automatisch ergänzt)
Das Ergebnis lautet entweder WAHR, sofern nur Unikate, also keine Doppel- bzw. Mehrfacheinträge vorkommen oder FALSCH, sofern Mehrfacheinträge enthalten sind. Um das Ergebnis anschaulicher zu machen, können Sie direkt neben der Formelzelle eine WENN-Funktion integrieren, die ihnen das Ergebnis textlich mitteilt. Wenn die oben genannte Formel bspw. in Zelle C9 steht kann diese lauten:
=WENN(C9*1=0;“ Es kommen Duplikate in der Liste vor“;“ Es sind nur Unikate in der Liste enthalten“)
Ergänzung: Sollen unterschiedliche Listenlängen ohne Anpassung verarbeitet werden können, muss der benannte Zellbereich „Liste2“ noch dynamisiert werden.
Dazu geht man in den Namensmanager (Strg+F3) und gibt für „Liste2“ bei „Bezieht sich auf“ folgende Formel ein:
=BEREICH.VERSCHIEBEN($A$8;1;0;ANZAHL2($A$9:$A$500);1)
Nun kann die Liste beliebig ergänzt bzw. erweitert werden, hier bspw. bis Zeile 500. Achten Sie allerdings darauf, dass keine leeren Zellen/Zeilen in der Liste enthalten sind. Man kann auch die komplette Spalte A berücksichtigen, dann lässt sich die Liste im Grunde beliebig verlängern. Allerdings raten wir davon ab, da die Berechnung in Excel bei runterkopierten Array-Formeln (= Matrixformeln) bei großen Datenbeständen (> 500 Einträge) teilweise sehr langsam werden kann.
Mehr zu Duplikaten (Teil 2)
Noch mehr Ideen und Beispiele zum Thema „Duplikate“ finden Sie im zweiten Teil dieses Tutorials. Dieser zeigt ihnen Lösungen für die Aufgabenstellungen:
- Universelle Formel zur Auflistung von Duplikaten aus einer beliebig langen Liste
- Zwei Listen mit unterschiedlicher Sortierung auf gleiche Inhalte prüfen
- Doppelte Werte (Duplikate) bereits bei der Eingabe verhindern
Wie üblich haben wir auch zu diesem Tutorial eine entsprechende Excel-Datei vorbereitet, die wir ihnen gerne kostenlos zur Verfügung stellen.
