Wer mit großen Datenmengen und langen Listen in Excel arbeiten muss, kennt vermutlich das Thema der Dubletten:
Einträge wurden doppelt in der Tabelle erfasst, obwohl eigentlich jeder Wert nur einmal vorkommen dürfte.
Wie man bereits vorhandene Dubletten erkennt, habe ich unter anderem in diesem Artikel beschrieben.
Aber wie lässt sich schon bei der Datenerfassung in Excel verhindern, dass neue Dubletten angelegt werden?
So geht’s:
Methode 1: Bedingte Formatierung
In meinem Beispiel möchte ich sofort darauf aufmerksam gemacht werden, sobald ich eine bereits vorhandene Artikelnummer ein weiteres Mal eingegeben habe:
Beispiel: Erfassung von Artikelnummern
Dazu markiere ich den kompletten Eingabebereich, in meinem Beispiel als die Zellen A7:A20 und lege im Register „Start“ eine neue Regel für eine Bedingte Formatierung an. Hier gibt es bereits eine für unseren Zweck vordefinierte Regel, nämlich „Doppelte Werte…“:
Neue Formatierungsregel anlegen
Jetzt muss ich nur noch eine der angebotenen Formatierungen auswählen (oder über den Punkt „benutzerdefiniertem Format…“ ein eigenes Format anlegen):
Format auswählen
Und schon werden bei der Dateneingabe etwaige doppelte Einträge farblich hervorgehoben:
Dubletten werden markiert
Methode 2: Bedingte Formatierung (erweitert)
Das war ein einfaches Szenario, da wir nur innerhalb der Artikelspalte auf Dubletten geprüft haben. Was machen wir aber, wenn zwei Spalten für die Prüfung relevant sind?
In meinem erweiterten Beispiel würde die oben gezeigte Dublettenprüfung nicht mehr funktionieren, da neben dem Namen auch der Vorname einbezogen werden muss:
Zwei Spalten berücksichtigen
Es gibt in drei Zeilen einen Herrn Fischer, aber nur die Zeilen 8 und 14 enthalten Dubletten. Für diese erweiterte Prüfung müssen wir uns also eine eigene Regel schnitzen.
Dabei hilft uns die mächtige SUMMENPRODUKT-Funktion:
SUMMENPRODUKT hilft weiter
=SUMMENPRODUKT((A8=$A$7:$A$20)*(B8=$B$7:$B$20))
Mit der oben gezeigten Funktion passiert folgendes:
- Im ersten Teil prüfen wir, wie oft der Wert in Zelle A8 im Bereich A7:A20 enthalten ist. Des Ergebnis ist WAHR, wenn der Wert in einer der Zellen gefunden wurde und FALSCH, wenn er nicht gefunden wurde.
- Im zweiten Teil prüfen wir, wie oft der Wert in Zelle B8 im Bereich B7:B20 enthalten ist. Des Ergebnis ist wieder WAHR, wenn der Wert in einer der Zellen gefunden wurde und FALSCH, wenn er nicht gefunden wurde.
- Dann werden die beiden Wahrheitswerwerte multipliziert.
Am Beispiel der Zeile 8 würde das Ganze so aussehen:
SUMMENPRODUKT zerlegt
Da SUMMENPRODUKT die einzelnen ermittelten Produkte noch summiert, erhalten wir als Ergebnis die Anzahl der vorhandenen Datensätze. Wie man im Screenshot oben sieht, haben wir insgesamt auch 6 leere Zeilen, für die ebenfalls das entsprechende Ergebnis berechnet wird.
Nun müssen wir das Ganze nur noch in eine Formatierungsregel packen. Dazu markieren wir erst die Spalte A und legen eine neue Regel an:
Neue Formatierungsregel für Spalte A
Die eingetragene SUMMENPRODUKT-Funktion prüfe ich in meinem Beispiel auf den Wert 2, da ich sofort bei einer doppelten Eingabe darauf hingewiesen werden möchte:
Fast fertig…
Und der Vollständigkeit halber erstellen wir die gleiche Regel noch für die Spalte B:
Identische Regel für Spalte B
Und damit ist unsere Prüfung fertig:
Fertige Dublettenprüfung
Methode 3: Datenüberprüfung
Einen „härteren“ Weg können wir über die Datenüberprüfung einschlagen. Damit ist es möglich, eine Falscheingabe nicht nur hervorzuheben, sondern direkt unmöglich zu machen.
Hierzu gebe ich in der Gültigkeitsprüfung eine benutzerdefinierte Formel ein:
Datenüberprüfung per Formel
Mit Hilfe der ZÄHLENWENN-Funktion prüfe ich, wie oft der Wert aus der aktuellen Zelle in meiner Liste vorkommt. Ist das Ergebnis kleiner oder gleich 1, handelt es sich folglich um keine Dublette und der Wert wird akzeptiert.
Aus Gründen der Bedienerfreundlichkeit möchte ich noch eine angepasste Fehlermeldung ausgeben:
Benutzerdefinierte Fehlermeldung
Und schon werde ich beim Versuch einer Dubletteneingabe mit der entsprechenden Meldung „belohnt“:
Die Fehlerprüfung im Einsatz
Hausaufgabe für dich
Wie setzt man die Datenüberprüfung um, wenn wie oben zwei Felder überprüft werden sollen. Also Name und Vorname?
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
der Artikel hat mir wieder gut gefallen, schön, dass es meistens mehrere Möglichkeiten gibt, Antworten zu bekommen. Mit der Datenüberprüfung ist es, so finde ich, doch recht knifflig.
Zu Deiner Hausaufgabe ist mir dies eingefallen:
In E9 schreibe ich „Fischer“ oder baue eine Dropdownliste mit den Nachnamen.
In F9 schreibe ich „Fritz“ oder baue eine Dropdownliste mit den Vornamen.
A7:B20 markieren, Datenüberprüfung benutzerdefiniert und als Formel
=UND($A7=$E$9;$B7=$F$9)<=1
eingeben, dann OK. In eine nächste Zeile dürfte nun Fischer und Fritz nicht mehr eingegeben werden können.
Viele Grüße
Gerhard
Hallo Gerhard,
Dein Vorschlag funktioniert zwar, bezieht sich bei der Prüfung aber immer auf die Werte, die gerade in E9 und F9 stehen.
Ich hatte mir folgendes überlegt:
Einfach die oben unter Methode 2 beschriebene Formel als benutzerdefinierte Datenüberprüfung verwenden:
=SUMMENPRODUKT((A7=$A$7:$A$20)*(B7=$B$7:$B$20))<=1 Und dann eine entsprechende Fehlermeldung ausgeben lassen. Somit ist die Eingabe einer Dublette ebenfalls verhindert. Schöne Grüße, Martin
Hallo Martin,
finde den Beitrag toll, allerdings funktioniert die Dublettenprüfung nicht, wenn die Kombination mehr als 2x vorkommt. Ich habe mir folgende Ergänzung zu deiner Formel überlegt:
=SUMMENPRODUKT((A8=$A$7:$A$20)*(B8=$B$7:$B$20)*($A$7:$A$20″“))>1
liebe Grüße
Andreas
Hallo Andreas,
Du hast Recht, meine Formel funktioniert tatsächlich nur bei Dubletten, aber nicht Tripletten etc!
In deiner Formel hat sich nur ein kleiner Tippfehler eingeschlichen, den ich hier für die anderen Leser noch kurz korrigieren möchte:
=SUMMENPRODUKT((A7=$A$7:$A$20)*(B7=$B$7:$B$20)*($A$7:$A$20<>„“))>1
Damit sollte es klappen.
Vielen Dank für den guten Hinweis,
Martin
Hallo Martin,
habe versucht die Formel umzusetzen. Ich möchte in einer Spalte Zahlen mehrfach vergeben können (1,1,1,1,2,2,2 usw.) aber in der nächsten Spalte soll die zweite Zahl nur einmal pro Zahl in der vorherigen Spalte vorhanden sein (1-1,1-2,1-3… 2-1,2-2,…usw.)
Wenn ich die Summenprodukt Formel verwende, wie oben beschrieben erhalte ich eine Fehlermeldung in der ersten Zelle der Spalte, bereits bei der EIngabe der Zahl 1 wenn in der Spalte davor schon eine 1 steht. Danach kann ich aber in den folgenden Zeilen die 1 so oft eingeben, wie ich möchte -unabhängig davon welche Zahl in der Spalte davor steht – ohne Fehlermeldung.
Was mache ich falsch?
Hoffe ich habe mich verständlich ausgedrückt.
Viele Grüße
Uwe
Hallo Martin,
sieht so aus, als hätte ich die Lösung gefunden: =SUMMENPRODUKT((C1=$C$2:$C$40)*(D1=$D$2:$D$40)*($C$2:$C$40″“))<=1
Damit funktioniert es soweit, was die Überprüfung der Gültikeit betrifft.
Jetzt habe ich aber noch das Problem, dass ich die Dateneingabe in Spalte D nur freigeben möchte, wenn in der jeweiligen Zelle in Spalte C ein Eintrag vorhanden ist. Eine Idee, wie ich dies zusätzlich zur Gültigkeitsprüfung in der Spalte vor geben kann oder mit der Gültigkeitsprüfung kombinieren könnte?
Vielen Dank und viele Grüße
Uwe
Hallo Uwe,
du kannst einfach eine Datenprüfung für die Spalte D einrichten. Unter „Zulassen“ wählst Du „Benutzerdefiniert“, als Formel gibst Du ein „=NICHT(ISTLEER(C1))“. Wichtig dabei ist, das der Haken „Leerzellen ignorieren“ nicht gesetzt ist. Jetzt lässt sich in D1 nur ein Wert eingeben, wenn C1 nicht leer ist.
Grüße,
Martin
Hallo Martin,
vielen Dank für den Hinweis. Dieser löst aber mein Problem nicht. Für die Spalte habe ich ja bereits die Formel bezüglich der Dubletten. Ich müsste daher die beiden Formeln miteinander kombinieren und daran scheitert es bei mir. Ich bekommen die Kombination nicht hin.
Sorry, falls ich mich missverständlich ausgedrückt habe.
Viele Grüße
Uwe
Hallo Uwe,
sorry, das war dann ein Missverständnis. Leider lassen sich zwei Gültigkeitsregeln nicht miteinander kombinieren. Es bliebe noch die Möglichkeit, eine der beiden Regeln als bedingte Formatierung umzusetzen und den Anwender auf eine ungültige Eingabe zumindest mit einer farblichen Markierung hinzuweisen.
Schöne Grüße,
Martin
Hallo Martin,
schade. Hab aber noch eine Idee: könnte ich Spalte C-Zellen sperren, falls in den Spalte D-Zellen schon was eingetragen ist? Quasi die umgekehrte Variante. Dann vermutlich nur mit ISTLEER? In Spalte C habe ich ja bisher noch keine Datenprüfung.
Viele Grüße
Uwe
Hallo Uwe,
ja, einen Versuch wäre es Wert. ISTLEER wäre dann der richtige Ansatz.
Schöne Grüße,
Martin
Hallo Martin,
mit ISTLEER bin ich weitergekommen. Zusätzlich habe ich mit VBA eine weitere Kontrolllösung in meiner Tabelle verarbeitet. Damit steht zunächst mal meine „Basis“-Tabelle für meine Wunschtabelle/-formular.
Mit der doppelten dynamischen Dropdown-Liste bin ich aber noch nicht am Ende und werde Dein Dropdown-Leitfaden für mein weiteres voregehen zu Rate ziehen.
Meine Frage ist nun, nach dem ich den Eintrag mit Dropdown und Bereich verschieben gesehen habe, geht so etwas auch mit mehr als einer zweifache dynamische Dropdown-Liste und funktioniert dies auch innerhalb der Liste, nach Auswahl bestimmten Inhaltes bzw. neuer Auswahl der generierten Dropdown-Einträge oder geht dies nur jeweils am aktuellen Ende der Liste? Hilft mir dafür Dein Leitfaden ebenfalls weiter?
Ich versuche es mal darzustellen: nach Auswahl A stehen 1, 2 oder in der nächsten Liste (Spalte) zur Verfügung. Wird 2 gewählt, würde sich daraus ergeben, das 2 vier feste Einträge zugeordnet sind/werden, die dann in der nächsten Spalte (3) und den nächsten Zeilen (beides soll eingeschoben werden) abgebildet werden. Bei der Auswahl 1 oder 3 wäre keine weitere Spalte und auch damit auch keine weitere Zeile erforderlich. (falls sowas überhaupt geht…)
Vielen Dank und viele Grüße
Uwe
Hallo Uwe,
ohne Deine Lösung jetzt im Detail zu kennen: In dem Dropdown-Leitfaden wird unter anderem erklärt wie man mehrstufige Dropdown-Listen erzeugt, die auch mehr als zwei Stufen. Die Verschachtelung kann (zumindest theoretisch) beliebig tief sein.
Mir ist aber nicht klar, was Du jetzt genau mit dem automatischen Einschieben von neuen Zeilen und Spalten meinst. Automatisch wird hier nichts eingefügt. Es werden lediglich die Inhalten der Dropdown-Listen in den unterschiedlichen Spalten dynamisch generiert, abhängig von den zuvor eingegebenen Werten.
Grüße,
Martin
Grüße,
Martin
Hi,
Kann ich bei einer Artikelnummer die höchste (letzte z.B. 99) Nummer +1 in der Fehlermeldung anzeigen? Z.B.: „Doppelter Wert, Bitte Nummer 100 Verwenden“?
Danke im Voraus
Hallo Dimitri,
bei den Fehlermeldungen zur Gültigkeitsprüfung können leider keinen Formeln verwendet werden, sondern nur statischer Text.
Schöne Grüße,
Martin
Danke für die Anleitung, wie man Dubletten bei Excel verhindert. Auf der Arbeit haben wir einen Experten, der mit schon erklärt hat, wie man Dubletten sucht in Excel. Ich hätte in der Schule besser aufpassen sollen, als wir Excel durch genommen haben.
Hallo Franzi,
gern geschehen. Aufpassen in der Schule hilft durchaus, dann hat man etwas, das man wieder vergessen kann 🙂
Schöne Grüße,
Martin
Hallo Martin,
bei mir soll eine Spalte überwacht werden, aber die Einträge werden über eine Verketten Funktion reingeschrieben und nicht manuell.
Gibt es für so was auch eine Möglichkeit der Datenüberprüfung?
Hallo Marko,
die Datenüberprüfung greift nur, wenn die Daten manuell eingegeben werden. In deinem Fall bleibt also nur die bedingte Formatierung.
Schöne Grüße,
Martin
Hallo Martin.
Vielen Dank für den tollen, praktischen Beitrag.
Die ZÄHLENWENN Formel war mir eine große Hilfe. Zusätzlich hat Excel den Bereich dann automatisch erweitern können, d.h. ich musste meine bestehenden Inhalte nicht überschreiben, sonder konnte nur die Überprüfung auf die gewünschten Felder erweitern…
Hallo Martin,
ich möchte B1 sperren wenn der gleiche Text wie in A1 eingegeben wird und umgekehrt. Das funktioniert auch mit der Formel =ZÄHLENWENN(A1:B1;A1)=1 aber nur wenn der Text über die Tastatur eingegeben wird. A1 und B1 möchte ich aber per Dropdownliste befüllen und dann funktioniert das nicht mehr. A1 und B1 sind wiederum mit einem SVERWEIS versehen =SVERWEIS(A8;A14:B16;2) und =SVERWEIS(A8;A14:B16;2 (falls das damit zusammenhängt?). Gibt es dafür eine Lösung?
Vielen Dank schon mal
Gruß Pascal
Hallo Pascal,
Dropdownlisten und Formeln schließen sich gegenseitig aus. Denn sobald man aus der Dropdownliste einen Wert ausgewählt hat, geht die in der Zelle vorhandene Formel ja verloren.
Schöne Grüße,
Martin
Hallo, vielen Dank für das nützliche Tuturial. Eine Frage komtm bei mir noch auf. Wenn ich Daten in eine Zelle einkopiere von einer Website zB eine Artikelnummer von einer Website in mein vorhandenes gefülltes Excel Sheet einKOPIERE, erscheint keine Fehlermeldung, dass diese Artikelnummer bereits eingegeben ist. Nur, wenn ich die Artikelnummer manuell eingebe.
Hallo Eric,
ja, das ist leider ein Ärgernis und eine Lücke in der Datenüberprüfung in Excel. Mit dem Einkopieren von Werten lässt sich die komplette Datenüberprüfung aushebeln. Dagegen ist leider auch kein Kraut gewachsen.
Schöne Grüße,
Martin
Hallo Martin,
deine Formel für die Datenüberprüfung funktioniert leider nur für zuvor bekannte Breiche. Bei einer formatierten Tabelle ist der Bereich jedoch nicht bekannt. Hier kommt die Datenübeprüfung durcheinander.
In einer Zelle eingegeben akzeptiert Excel die Formel und sie funktioniert auch:
=ZÄHLENWENN(tbl_Networks_Tenant[VLAN];tbl_Networks_Tenant[VLAN])<=1
In der Datenüberprüfung wird diese Formel jedoch nicht akzeptiert.
### GELÖST ###
Ich habe nach ein paar Recherchen wohl die Lösung gefunden. Du kannst Dein Beispiel ja um folgende Formel erweitern:
=ZÄHLENWENN(INDIREKT(„Area_Networks_Tenant_VLAN“);A2)<=1
Ich habe der Spalte (in meinem Fall die Spalte A) in der formatierten Tabelle den Namen Area_Networks_Tenant_VLAN gegeben. Wenn nun die Formel wie oben in der Zeile 2 (erste Zeile der formatierten Tabelle) eingegeben wird, wird diese Datenüberprüfung bei Anfügen einer neuen Zeile mit übernommen und die neue Eingabe mit den schon getätigten verglichen. Wird ein schon vorhandener Wert eingegebe, kommt es zur Fehlermeldung.
Hallo Ralph,
danke für deine Lösung. Ja, leider können formatierte Tabellen in einer Datenüberprüfung nicht direkt angesprochen werden. Ein Trick, wie es doch geht, wäre folgender:
Man muss nur zusätzlich im Namensmanager einen Namen für die gewünschte Spalte der formatierten Tabelle vergeben. Also in deinem Fall einen Namen für =tbl_Networks_Tenant[VLAN], also z.B. ddVLAN
Diesen Namen kannst du dann in der Datenüberprüfung verwenden und er wird sich automatisch an eine erweiterte Tabellenspalte anpassen.
Schöne Grüße,
Martin
Hallo Martin,
vielen lieben Dank für diese Anleitung, welche sehr verständlich und einfach zu handhaben ist. Meine Frage hierzu ist, ob es in der Datenüberprüfung in Zusammenhang mit dem obigen Bsp. mit der Namensliste möglich ist, die direkte Fehlermeldung angezeigt zu bekommen, also im Moment der Eingabe des doppelten Namens?
Hallo Glebs,
das wäre möglich, wenn du mit einer Hilfsspalte arbeitest, in der zum Beispiel mit ZÄHLENWENN die Anzahl des jeweiligen Namens bestimmt wird. Und in der Datenprüfung prüft man, ob dieser Wert > 1 ist. Wenn mehrere Spalten zu berücksichtigen sind (z.B. Name + Vorname), könntest du diese Spalten zuerst in einer eigenen Hilfsspalte zusammenfassen und dann die ZÄHLENWENN-Funktion auf diese Spalte anwenden. Die Datenprüfung muss dann auch für beiden Spalten eingerichtet werden:
Schöne Grüße,
Martin
Hallo Martin,
Ich wollte dich auf etwas hinweisen. Bei mir hat das mit der Datenüberprüfung durch das Summenprodukt nicht hingehauen.
Die Lösung war es, die Spalten der Zellen (nach denen in der Spalte gesucht wird) zu fixieren.
=SUMMENPRODUKT(($A8=$A$7:$A$20)*($B8=$B$7:$B$20))
Mit freundlichen Grüßen
Martin
Hallo Martin,
ja, die Fixierung bringt zusätzliche Sicherheit, wenn man die Formel in andere Spalten kopiert. Danke für den Hinweis.
Schöne Grüße,
Martin
Nachtrag:
Wenn man die Spalte mit der dynamischen Sortieren Funktion einer intelligenten Tabelle sortiert, so kann man mit der Zählenwenn Funktion eine funktionierende Datenüberprüfung auf Dubletten einbauen. Das funktioniert bestimmt auch im Rahmen des Summenproduktes, habe ich aber noch nicht probiert.
Nachtrag Nachtrag:
Es funktioniert auch mit der Summenprodukt Funktion. Jede Spalte benötigt eine eigene benutzerdefinierte Datenüberprüfung mit der Summenprodukt Formel.
Hallo Martin,
das ist natürlich auch eine sehr gute Idee, um Dubletten gleich bei der Eingabe zu verhindern. Auch hier vielen Dank für den Tipp!
Schöne Grüße,
Martin
Nachtrag Nachtrag Nachtrag
Dann aber ein dynamisches Array mit der Bereich.verschieben Funktion erzeugen. Nicht sortieren.