Excel: Dubletten verhindern 13

3 Methoden, um doppelte Werte bereits bei der Dateneingabe zu vermeiden
 

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

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

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

Format auswählen

Und schon werden bei der Dateneingabe etwaige doppelte Einträge farblich hervorgehoben:

Dubletten werden markiert

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

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 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

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

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...

Fast fertig…

Und der Vollständigkeit halber erstellen wir die gleiche Regel noch für die Spalte B:

Identische Regel für Spalte B

Identische Regel für Spalte B

Und damit ist unsere Prüfung fertig:

Fertige Dublettenprüfung

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

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

Benutzerdefinierte Fehlermeldung

Und schon werde ich beim Versuch einer Dubletteneingabe mit der entsprechenden Meldung “belohnt”:

Die Fehlerprüfung im Einsatz

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?

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

13 Gedanken zu “Excel: Dubletten verhindern

  • Gerhard Pundt

    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

    • Martin Weiß Beitragsautor

      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

  • Andreas Katits

    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

    • Martin Weiß Beitragsautor

      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

      • Uwe Schanz

        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

      • Uwe Schanz

        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

        • Martin Weiß Beitragsautor

          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

          • Uwe Schanz

            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

          • Martin Weiß Beitragsautor

            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

          • Uwe Schanz

            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

          • Martin Weiß Beitragsautor

            Hallo Uwe,

            ja, einen Versuch wäre es Wert. ISTLEER wäre dann der richtige Ansatz.

            Schöne Grüße,
            Martin

          • Uwe Schanz

            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

          • Martin Weiß Beitragsautor

            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