Die Datenüberprüfung in Excel – Dropdown-Felder, Regeln, abhängige Verzeichnisse und sich selbst aktualisierende Matrizen

FAQ — Welche Fragen werden in diesem Artikel beantwortet?

Q: Wie erstelle ich eine Dropdown-Liste, die sich automatisch aktualisiert, wenn ich neue Elemente hinzufüge?

A: Speichern Sie Ihre Listenelemente dazu in einer Spalte einer Excel-Tabelle. Wenn Sie diese Spalte als Validierungsquelle angeben, wird die Dropdown-Liste automatisch erweitert, sobald der Tabelle eine neue Zeile hinzugefügt wird. Es müssen weder benannte Bereiche aktualisiert noch Validierungen neu definiert werden.

Q: Wie erstelle ich ein abhängiges Dropdown-Menü, also eine zweite Liste, deren Inhalt sich je nach Auswahl in der ersten Liste ändert?

A: Der moderne Ansatz nutzt die Funktion FILTER und den Spill-Range-Operator #. Eine FILTER-Funktion extrahiert die relevanten Elemente für die ausgewählte Kategorie und überträgt sie in eine Hilfssäule. Das zweite Dropdown-Menü verweist dann mit # auf diese Spill-Range. Wenn sich die erste Auswahl ändert, wird das Filterergebnis aktualisiert und das zweite Dropdown-Menü zeigt sofort die neuen Optionen an.

Q: Was ist der Unterschied zwischen den Fehlermeldungen „Stop“, „Warnung“ und „Information“?

A: „Stop“ verhindert die Eingabe vollständig. Der Benutzer kann erst fortfahren, wenn er einen gültigen Wert eingegeben hat. „Warnung“ hingegen lässt die Eingabe zu, fordert den Benutzer aber zur Bestätigung auf. „Information“ zeigt eine Meldung an und akzeptiert die Eingabe ohne Rückfrage. Verwenden Sie für Daten, die korrekt sein müssen, immer die Option „Stop“.

Q: Kann ich eine Formel als Validierungsregel verwenden, um beispielsweise doppelte Einträge zu verhindern?

A: Ja. Unter der Option „Benutzerdefiniert“ in der Datenüberprüfung funktioniert jede Formel, die WAHR oder FALSCH zurückgibt, als Regel. Die Formel =COUNTIF($A$2:$A$100,A2)=1 lehnt einen Wert ab, wenn er bereits irgendwo in Spalte A vorkommt. =ISTZAHL(A2) lehnt Texteingaben in einer Zahlenspalte ab. Die Formel wird zum Zeitpunkt der Eingabe ausgewertet.

Q: Verhindert die Datenüberprüfung, dass fehlerhafte Daten eingefügt werden?

A: Standardmäßig nicht. Beim Einfügen wird die Überprüfung umgangen. Um sich davor zu schützen, kombinieren Sie die Überprüfung mit dem Schutz der Arbeitsblätter: Sperren Sie die überprüften Zellen, sodass nur die Eingabe über die Tastatur möglich ist. Oder verwenden Sie einen auf ZÄHLENWENN basierenden Ansatz, bei dem vorhandene fehlerhafte Daten durch bedingte Formatierung gekennzeichnet werden. So bleiben sie auch nach dem Einfügen sichtbar.

Die Datenvalidierung ist eine der am wenigsten genutzten Funktionen in Excel. Die meisten Benutzer kennen die Dropdown-Liste. Doch nur wenige kennen den vollen Funktionsumfang: benutzerdefinierte Formelregeln, die logisch ungültige Eingaben ablehnen, abhängige Dropdown-Listen, die ihre Optionen basierend auf einer anderen Zelle filtern, sowie den modernen dynamischen Ansatz, durch den sich jede Liste selbst aktualisiert.

Dieser Artikel führt Sie von den Grundlagen bis hin zu fortgeschrittenen Funktionen und verwendet dabei durchgehend ein einheitliches Beispiel aus dem Bereich der Personaldaten.

Was die Datenüberprüfung leistet – und was nicht

Die Datenüberprüfung steuert, welche Eingaben in eine Zelle zulässig sind. Sie wird zum Zeitpunkt der Eingabe ausgeführt und kann ungültige Eingaben entweder vollständig blockieren, den Benutzer warnen oder lediglich eine Meldung anzeigen. Bereits vorhandene Daten werden nicht korrigiert: Enthält eine Zelle vor der Anwendung der Überprüfung einen ungültigen Wert, bleibt dieser bestehen, bis er geändert wird.

Dieser Unterschied ist wichtig. Die Validierung ist ein Präventions-, kein Bereinigungswerkzeug. Verwenden Sie für bereits vorhandene fehlerhafte Daten die Bereinigungstechniken aus dem Artikel: So bereinigen Sie "schmutzige" Daten in Excel. Wenden Sie anschließend die Validierung an, um zu verhindern, dass dieselben Probleme erneut auftreten.

So wenden Sie die Validierung an: Wählen Sie die Zielzellen aus und gehen Sie zu „Daten“ → „Datenvalidierung“. Wählen Sie dort Ihren Regeltyp aus und konfigurieren Sie ihn.

Die sechs Regelarten

Ganzzahl: Es werden nur ganze Zahlen innerhalb eines definierten Bereichs akzeptiert. Dies ist nützlich für Mengen, Stückzahlen und Jahre.

Dezimal: Akzeptiert Zahlen einschließlich Dezimalstellen innerhalb eines Bereichs. Nützlich für Stunden, Preise und Prozentsätze.

Liste: Akzeptiert nur Werte aus einer definierten Liste. Dies ist der am häufigsten verwendete Typ und die Grundlage für Dropdown-Menüs.

Datum: Akzeptiert nur Datumsangaben innerhalb eines Bereichs. Er verhindert Texteingaben wie „5. März“ in Datumsspalten.

Drei Ansätze für Dropdown-Listen

Methode 1: statische, durch Kommas getrennte Liste

Geben Sie die zulässigen Werte direkt in das Feld „Quelle“ ein, getrennt durch Kommas:

Offen,In Bearbeitung,Zurückgestellt,Abgeschlossen

Das ist einfach und schnell bei kurzen, unveränderlichen Listen. Das Problem dabei ist jedoch, dass Sie, wenn Sie einen Wert hinzufügen oder ändern müssen, die Validierungsregel selbst suchen und bearbeiten müssen. Bei einer gemeinsam genutzten Arbeitsmappe wissen zudem nur wenige Benutzer, wo sie suchen müssen. Verwenden Sie diese Funktion daher nur für Listen, die sich niemals ändern werden.

Methode 2: Tabellenspalte als Quelle (selbstaktualisierend)

Speichern Sie Ihre Listenelemente in einer Excel-Tabelle. Wenn die Tabelle den Namen StatusList trägt und die Werte in einer Spalte namens Status stehen, verweisen Sie im Feld „Quelle” wie folgt darauf:

=StatusList[Status]

Fügen Sie nun einen neuen Status zur Tabelle hinzu. Die Dropdown-Liste in jeder validierten Zelle wird automatisch aktualisiert. Es sind keine Änderungen der Formeln, keine Erweiterung des Bereichs und kein Wartungsaufwand erforderlich. Dies ist die richtige Standardeinstellung für jede Dropdown-Liste, die möglicherweise erweitert wird.

Befindet sich die Tabelle auf einem anderen Blatt, funktioniert der Verweis weiterhin: =Einstellungen[Status] – vorausgesetzt, das Blatt „Einstellungen“ und die Tabelle sind vorhanden.

Methode 3: Überlaufbereich aus einer dynamischen Array-Formel (modern)

Für maximale Flexibilität generiert eine dynamische Array-Formel die Liste und das Dropdown-Menü verweist mithilfe von # auf deren Überlaufbereich. Fügen Sie diese Formel in eine Hilfssäule ein. Das Blatt „Einstellungen“ ist hierfür der richtige Ort.

=SORTIEREN(EINDEUTIG(HoursLog[Department]))

Dadurch wird eine sortierte und deduplizierte Liste aller tatsächlich in den Daten vorhandenen Abteilungen erstellt. Wenn neue Abteilungen zu HoursLog hinzugefügt werden, erscheinen sie automatisch in der Dropdown-Liste. Geben Sie anschließend den Überlaufbereich im Feld „Validierungsquelle“ an.

=Einstellungen!$A$2#

Das Zeichen # weist Excel an, alle Zellen zu verwenden, in die die Formel in A2 überläuft, unabhängig davon, wie viele das sind.

Für diesen Ansatz ist Excel 365 oder Excel 2021 erforderlich.

Abhängige Dropdown-Listen – der moderne Ansatz

Eine abhängige Dropdown-Liste passt ihre verfügbaren Optionen an die Auswahl in einer anderen Zelle an. Beim klassischen Ansatz wird die Funktion INDIRECT mit benannten Bereichen genutzt – ein benannter Bereich pro Kategorie. Das funktioniert zwar, versagt jedoch, sobald sich ein Kategoriename ändert oder eine neue Kategorie hinzugefügt wird.

Der moderne Ansatz nutzt die Funktion FILTER:

Einrichtung: Eine Tabelle namens RoleList verfügt über zwei Spalten: Department und Role. Sie enthält jede gültige Kombination aus Abteilung und Rolle.

Schritt 1: Fügen Sie die FILTER-Formel in den Einstellungen ein.

=FILTER(RoleList[Role]; RoleList[Department]=C2; "")

Dabei ist C2 die Zelle, die die Auswahl der Abteilung (Department) enthält. Die Formel befindet sich beispielsweise in „Einstellungen!E2“. Sie gibt eine Liste der Rollen (RoleList[Role]) aus, die zur ausgewählten Abteilung gehören.

Schritt 2: Wenden Sie die Validierung auf die Rollenspalte an.

Im Quellfeld der Validierung für die Spalte „Rolle“:

=Einstellung!$E$2#

Wählt der Benutzer in Zelle C2 eine Abteilung aus, wird das FILTER-Ergebnis aktualisiert, der Anzeigebereich ändert sich und im Rollen-Dropdown-Menü werden sofort nur die relevanten Rollen angezeigt.

Wichtig: Die FILTER-Formel muss sich auf einem Blatt befinden, das neu berechnet wird, wenn sich der Wert in Zelle C2 ändert. Befindet sich die Hilfsformel auf einem anderen Blatt als das Dropdown-Menü, muss die automatische Berechnung aktiviert sein (Formeln → Berechnungsoptionen → Automatisch).

Validierung benutzerdefinierter Formeln

Beim Regeltyp „Benutzerdefiniert“ können beliebige Excel-Formeln verwendet werden. Die Formel wird für die zu validierende Zelle ausgewertet: Wenn sie den Wert „WAHR“ zurückgibt, wird der Eintrag akzeptiert, andernfalls abgelehnt.

Doppelte Einträge vermeiden

=ZÄHLENWENN($A$2:$A$100; A2) = 1

Er verwirft jeden Wert, der bereits in A2:A100 vorhanden ist. Die Bedingung „=1” bedeutet, dass der Wert genau einmal vorkommen darf – und zwar bei dem Eintrag, der gerade vorgenommen wird. Eine Zahl größer als 1 bedeutet, dass bereits ein Duplikat vorhanden ist.

Eine Zahl angeben

=ISTZAHL(A2)

Er verwirft Texteingaben in einer Spalte, die ausschließlich Zahlen enthalten sollte. Dies ist einfacher und eindeutiger als die Dezimalregel für Fälle, in denen der Wertebereich unbegrenzt ist.

Validate a date within the current year

=UND(JAHR(A2)=JAHR(HEUTE()), A2<>"")

Akzeptiert nur Datumsangaben aus dem laufenden Jahr und lehnt leere Eingaben ab.

Einträge verhindern, die nicht in einer Referenzliste enthalten sind (Groß-/Kleinschreibung wird nicht berücksichtigt)

=ZÄHLENWENN(Einstellung[ValidCodes]; A2) > 0

Lehnt jeden Eintrag ab, der nicht in der Spalte „ValidCodes“ der Einstellungstabelle aufgeführt ist. Im Gegensatz zu einer Listenvalidierung wird bei dieser Vorgehensweise die Groß-/Kleinschreibung nicht berücksichtigt, und die Referenzliste kann sich auf einem separaten Blatt befinden.

Eingabemeldungen und Fehlermeldungen

Die Datenvalidierung unterstützt zwei optionale Kommunikationswerkzeuge.

Der Eingabehinweis wird als Tooltip angezeigt, wenn der Benutzer die Zelle auswählt. Nutzen Sie diesen Hinweis, um zu erläutern, welche Werte zulässig sind, welches Format erwartet wird und warum es Einschränkungen gibt. Durch einen einzeiligen Eingabehinweis werden Validierungsfehler erheblich reduziert, da die Benutzer die Regeln bereits vor der Eingabe kennen.

Fehlermeldung wird angezeigt, wenn eine ungültige Eingabe versucht wird. Drei Darstellungsvarianten:

– Stopp – blockiert die Eingabe. Der Benutzer muss den Vorgang abbrechen oder den Wert ändern. Verwenden Sie diese Option für alle Felder, bei denen ein ungültiger Wert eine Formel beeinträchtigen oder einen Bericht beschädigen würde. – Warnung: Es wird eine Meldung angezeigt, jedoch wird auch die Option „Weiter“ angeboten. Verwenden Sie diese Option, wenn ein ungewöhnlicher Wert möglich ist, der jedoch gekennzeichnet werden sollte.

• Information: Zeigt die Meldung an und akzeptiert die Eingabe trotzdem. Diese Option ist selten sinnvoll und bietet keinen tatsächlichen Schutz.

Verfassen Sie stets aussagekräftige Fehlermeldungen. Die Standardmeldung „Der von Ihnen eingegebene Wert ist ungültig” ist für den Benutzer nicht hilfreich. Geben Sie stattdessen an, was erwartet wird: „Bitte geben Sie eine Abteilung aus der genehmigten Liste ein. Neue Abteilungen müssen vom Administrator hinzugefügt werden.“

Schutz der Validierung vor dem Einfügen

Das Einfügen umgeht die Validierung. Ein Benutzer, der einen Wert aus einer anderen Zelle kopiert und in eine validierte Zelle einfügt, hat Erfolg – selbst wenn der Wert ungültig ist. Es gibt drei Abhilfemaßnahmen:

Blattschutz mit gesperrten Zellen: Schützen Sie das Blatt und erlauben Sie nur die Bearbeitung entsperrter Zellen. - Legen Sie validierte Zellen als gesperrt fest. Benutzer können dort nur Text eingeben, aber nichts einfügen. Dies ist der robusteste Ansatz, erfordert jedoch eine bewusste Gestaltung des Blatts.

Einfügen Spezial → Nur Werte: Weisen Sie die Benutzer an, bei der Arbeit mit validierten Arbeitsblättern die Funktion „Einfügen → Nur Werte“ (Strg+Umschalt+V) zu verwenden. Dadurch werden nur Werte eingefügt, was die Validierung auslöst.

Bedingte Formatierung als Detektor: Wenden Sie eine Regel für bedingte Formatierung an, die alle Zellen hervorhebt, deren Wert nicht in der Liste der zulässigen Werte enthalten ist. Dies verhindert zwar keine fehlerhaften Daten, macht diese jedoch nach dem Einfügen sofort sichtbar.

Eine vollständige Validierungskonfiguration – der praktische Arbeitsablauf

1. Erstellen Sie Ihre Referenzlisten zunächst als Tabellenspalten im Arbeitsblatt „Einstellungen“. Benennen Sie jede Tabelle aussagekräftig.
2. Wenn eine Liste aus Daten abgeleitet wird (z. B. eindeutige Abteilungen, aktive Projekte), verwenden Sie im Arbeitsblatt „Einstellungen“ eine dynamische Array-Formel, um die Liste automatisch zu generieren.
3. Wenden Sie die Listenvalidierung auf Dropdown-Zellen an und verweisen Sie dabei auf Tabellenspalten oder Überlaufbereiche.
4. Platzieren Sie für abhängige Dropdown-Listen die FILTER-Formel im Blatt „Einstellungen“ und verweisen Sie auf deren Überlaufbereich.
5. Richten Sie Stopp-Fehlerwarnungen mit aussagekräftigen Meldungen für alle kritischen Felder ein.
6. Richten Sie Eingabemeldungen für Felder ein, die ein bestimmtes Format erfordern.
7. Schützen Sie das Blatt, falls das Umgehen der Einfügefunktion ein Problem darstellt.

Zusammenfassung

Durch die Validierung wird von vornherein verhindert, dass fehlerhafte Daten in Ihre Arbeitsmappe gelangen. In Kombination mit Tabellen als Datenquelle und dynamischen Array-Formeln für abhängige Listen entsteht so ein System, das sich bei wachsendem Datenvolumen selbst verwaltet – ganz ohne manuelle Listenpflege.