Wie wendet man mehrere Datenüberprüfungsregeln in einer einzigen Zelle eines Excel-Arbeitsblatts an?
In einem Excel-Arbeitsblatt ist es üblich, eine einzelne Datenüberprüfungsregel auf eine Zelle anzuwenden. Dies trägt zur Konsistenz und Genauigkeit der Daten bei. Es gibt jedoch Szenarien, in denen mehrere Überprüfungskriterien innerhalb derselben Zelle durchgesetzt werden müssen – beispielsweise wenn entweder eine gültige Zahl oder ein Wert aus einer bestimmten Liste zulässig sein soll oder wenn spezifische Textanforderungen mit zulässigen Datumsbereich kombiniert werden müssen. Solche komplexeren Datenüberprüfungsanforderungen in Excel zu handhaben, ermöglicht Ihnen eine bessere Steuerung Ihrer Dateneingabeprozesse, verhindert Fehler und verbessert die Datenqualität.
Im Folgenden führen wir Sie Schritt für Schritt durch verschiedene praxisnahe Beispiele, wie Sie mehrere Datenüberprüfungsregeln in einer einzigen Excel-Zelle umsetzen können. Jedes Beispiel behandelt ein anderes Szenario, sodass Sie die Methode auswählen können, die am besten zu Ihren spezifischen Anforderungen passt. Zudem stellen wir alternative Ansätze wie den Einsatz von VBA vor – ideal, wenn Sie mehr Flexibilität oder fortgeschrittene Logik benötigen.
Mehrfache Datenüberprüfung in einer Zelle anwenden (Beispiel 1)
Mehrfache Datenüberprüfung in einer Zelle anwenden (Beispiel 2)
Mehrfache Datenüberprüfung in einer Zelle anwenden (Beispiel 3)
Mehrfache Datenüberprüfung mithilfe von VBA anwenden (Fortgeschritten)
Mehrfache Datenüberprüfung in einer Zelle anwenden (Beispiel 1)
Angenommen, Sie möchten eine Zelle so einrichten, dass sie nur Werte akzeptiert, die eine der folgenden beiden Bedingungen erfüllen:
– Ist der eingegebene Wert eine Zahl, muss er kleiner als 100 sein.
– Handelt es sich um eine Texteingabe, muss der Text in einer bestimmten Liste enthalten sein (z. B. im Bereich D2 bis D7).
Diese Situation tritt häufig auf, wenn innerhalb desselben Feldes entweder quantitative Codes oder vordefinierte kategoriale Antworten erfasst werden müssen. Durch die Kombination von Überprüfungsregeln vermeiden Sie separate Felder für Zahlen und Text – für mehr Klarheit und Effizienz.
1. Wählen Sie die Zelle oder den Bereich aus, für den Sie mehrere Datenüberprüfungskriterien festlegen möchten. Klicken Sie anschließend auf der Registerkarte Daten auf Datenüberprüfung > Datenüberprüfung im Menüband, wie unten dargestellt:
2. Wechseln Sie im Dialogfeld Datenüberprüfung zur Registerkarte Einstellungen und nehmen Sie folgende Einstellungen vor:
- (1.) Wählen Sie im Drop-down-Menü Zulassen die Option Benutzerdefiniert.
- (2.) Geben Sie im Feld Formeldie folgende Formel ein:=ODER(A2<$C$2;ZÄHLENWENN($D$2:$D$7;A2)=1)
Hinweis: In dieser Formel bezieht sich A2 auf die zu überprüfende Zelle, C2 enthält den maximal zulässigen Wert und D2:D7 listet die zulässigen Texteinträge auf. Passen Sie diese Bezüge entsprechend Ihrem Arbeitsblatt an.
3. Klicken Sie auf OK, um die Einstellungen zu übernehmen. Die ausgewählten Zellen akzeptieren nun nur noch Einträge, die entweder Zahlen kleiner als 100 oder Textzeichenfolgen aus dem Bereich D2:D7 sind. Sobald ein Benutzer einen Wert eingibt, der keine der beiden Bedingungen erfüllt, zeigt Excel sofort eine Warnmeldung an – so erfahren Anwender unverzüglich von ungültigen Eingaben.
Diese Methode eignet sich ideal für einfache Szenarien mit klaren Regelgrenzen. Sobald jedoch differenziertere oder interaktive Anforderungen ins Spiel kommen – etwa bedingte Hinweise oder mehrstufige Logik – stößt die formelbasierte Datenüberprüfung an ihre Grenzen. In solchen Fällen bietet die unten vorgestellte VBA-Methode deutlich mehr Flexibilität.
Mehrfache Datenüberprüfung in einer Zelle anwenden (Beispiel 2)
In diesem Szenario möchten Sie möglicherweise nur Dateneingaben zulassen, die eine der folgenden beiden Bedingungen erfüllen:
– Der eingegebene Wert ist exakt der Text „Kutools für Excel“
– Der eingegebene Wert ist ein Datum zwischen 12/1/2017 und 12/31/2017
Diese Art der mehrfachen Überprüfung ist sinnvoll, wenn Ihr Fragebogen oder Ihr Datenerfassungsformular entweder einen Bestätigungscode (eine exakte Textzeichenfolge) oder ein Datum innerhalb des Projektzeitraums erfordert.
1. Öffnen Sie das Dialogfeld Datenüberprüfung für Ihre Zielzelle(n). Führen Sie im Dialogfeld die folgenden Schritte aus:
- (1.) Wechseln Sie zur Registerkarte Einstellungen.
- (2.) Wählen Sie im Drop-down-Menü Zulassen die Option Benutzerdefiniert aus.
- (3.) Geben Sie diese Formel in das Feld Formelein:=ODER(A2=$C$2;UND(A2>=DATUM(2017;12;1);A2<=DATUM(2017;12;31)))
Hinweis: Hier bezieht sich A2 auf die Überprüfungszelle, C2 sollte den Zieltext „Kutools für Excel“ enthalten, und der Zeitraum des Datumsbereichs wird mit DATUM(2017,12,1) und DATUM(2017,12,31) definiert. Passen Sie die Bezüge entsprechend Ihrer Arbeitsblattstruktur an.
2. Bestätigen Sie durch Klicken auf OK. Die Zelle(n) akzeptieren nun ausschließlich den angegebenen Text oder ein Datum innerhalb des definierten Zeitraums. Alle anderen Eingabetypen oder Texte außerhalb dieser Grenzen werden blockiert und lösen sofortiges Feedback aus, wie hier gezeigt:
Dieser Ansatz eignet sich ideal für strenge Eingabeszenarien, in denen ausschließlich exakte Übereinstimmungen oder feste Datumsangaben zulässig sind. Falls Ihre Überprüfung komplexe Abhängigkeiten, Berechnungen oder Benutzerinteraktionen umfasst, empfiehlt sich eine VBA-basierte Lösung für mehr Kontrolle.
Mehrfache Datenüberprüfung in einer Zelle anwenden (Beispiel 3)
Im dritten Beispiel betrachten wir eine Situation, in der die Zelle nur Einträge mit bestimmten Anfangszeichen und zugehörigen Zeichenlängen zulassen soll:
– Die Zelle muss mit „KTE“ beginnen und genau 6 Zeichen lang sein
– Oder mit „www“ beginnen und genau 10 Zeichen lang sein
Solche Kriterien kommen häufig zum Einsatz, wenn Formatstandards für Codes oder URLs eingehalten werden müssen. Die Überprüfung von Zeichenlänge und Präfix reduziert Eingabefehler erheblich.
Richten Sie dazu die Datenüberprüfung mit der folgenden Formel ein:
1. Öffnen Sie das Dialogfeld Datenüberprüfung. Führen Sie auf der Registerkarte „Einstellungen“ die folgenden Schritte aus:
- (1.) Wählen Sie die Registerkarte Einstellungen.
- (2.) Wählen Sie im Dropdown-Menü „Zulassen“ die Option Benutzerdefiniert.
- (3.) Geben Sie im Feld „Formel“ Folgendes ein:=OR(AND(LEFT(A2,3)=„KTE",LEN(A2)=6),AND(LEFT(A2,3)="www",LEN(A2)=10))
Hinweis: Ersetzen Sie bei Bedarf A2 durch Ihren tatsächlichen Zellbezug. Sie können „KTE“, „www“ und die Zeichenlängen jederzeit an Ihren spezifischen Kontext anpassen.
2. Klicken Sie auf OK. Die Zelle akzeptiert nun nur noch Werte, die Ihren Präfix- und Längenregeln entsprechen. Eingaben, die eine der Bedingungen verletzen, lösen einen Überprüfungsfehler aus, wie hier dargestellt:
Tipp: Falls Sie weitere mehrfache Überprüfungskriterien im Sinn haben, können Sie mit den integrierten Funktionen von Excel benutzerdefinierte Formeln erstellen, die genau Ihren Anforderungen entsprechen.
Eine Einschränkung der formelbasierten Validierung liegt darin, dass sie schwer zu verwalten und anzupassen ist, sobald die Regeln komplexer oder interaktiver werden – etwa wenn Sie benutzerdefinierte Fehlermeldungen anzeigen oder dynamisch wechselnde Kriterien abbilden möchten. In solchen Fällen bietet eine VBA-Lösung deutlich mehr Flexibilität.
Mehrfache Datenüberprüfung mithilfe von VBA anwenden (Fortgeschritten)
In Fällen, in denen die formelbasierte Datenvalidierung nicht ausreicht – beispielsweise wenn die Validierung auf einer Kombination mehrerer Faktoren beruht, Regeln je nach anderen Zellwerten variieren oder benutzerdefinierte Echtzeit-Meldungen angezeigt werden sollen – können Sie VBA-Makros (Visual Basic for Applications) nutzen, um erweiterte oder dynamische Validierungsregeln direkt in einer Zelle anzuwenden.
Typische Szenarien umfassen:
- Eingabevalidierung basierend auf mehr als zwei gleichzeitigen Bedingungen
- Ermöglicht Benutzerinteraktionen, z. B. durch Pop-up-Meldungen mit detaillierten Anleitungen.
- Automatisches Rückgängigmachen ungültiger Daten und Bereitstellen benutzerdefinierter Anweisungen
Im Folgenden finden Sie ein Beispiel für eine VBA-Lösung, bei der die Dateneingabe in B2eine der folgenden Bedingungen erfüllen muss:
- Eine ganze Zahl zwischen 1 und 50 sein
- ODER eines der zulässigen Wörter im Bereich D2:D5
Hinweis: Sie können Kriterien, Zielbereiche oder die Validierungslogik im Code jederzeit an Ihre Bedürfnisse anpassen.
1. Drücken Sie Alt+F11, um den Visual Basic for Applications-Editor zu öffnen. Doppelklicken Sie im VBA-Editor im Projektfenster auf das Arbeitsblatt, dem Sie mehrere Datenvalidierungen hinzufügen möchten, und fügen Sie anschließend das folgende Makro in das Codefenster dieses Blatts ein:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValidList As Range
Dim InputValue As Variant
Dim IsValid As Boolean
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Only validate B2 (you can set this to your desired cell or range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
InputValue = Target.Value
Set ValidList = Range("D2:D5") ' Change as needed
IsValid = False
' Check for whole number between 1 and 50
If IsNumeric(InputValue) And InputValue = Int(InputValue) Then
If InputValue >= 1 And InputValue <= 50 Then
IsValid = True
End If
End If
' Check if input matches allowed list
If WorksheetFunction.CountIf(ValidList, InputValue) > 0 Then
IsValid = True
End If
If Not IsValid Then
MsgBox "Entry must be an integer between 1 and 50 OR one of the values listed in D2:D5.", vbExclamation, xTitleId
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub2. Geben Sie Werte in die Zelle B2 ein. Wenn Sie eine ganze Zahl zwischen 1 und 50 eingeben oder ein Wort, das im Bereich D2:D5 enthalten ist, bleibt Ihre Eingabe erhalten. Andernfalls erscheint eine Fehlermeldung und die ungültige Eingabe wird sofort entfernt. Passen Sie die Zielzelle(n) sowie den Bereich der gültigen Werte im VBA-Code ganz einfach an Ihre Anforderungen an.
Tipps und Fehlerbehebung:
- Speichern Sie Ihre Arbeitsmappe immer, bevor Sie VBA-Code ausführen – unbeabsichtigter Code kann sonst zu Datenverlust führen.
- Wenn Ihr Arbeitsblatt mehrere Validierungszellen enthält, können Sie den Code so anpassen, dass er jeden beliebigen Bereich validiert – nicht nur Zelle B2.
- Sollte der Code nicht ausgeführt werden, stellen Sie sicher, dass Makros aktiviert sind und der Code im richtigen Arbeitsblatt platziert wurde.
- Sie können den Code erweitern, um je nach Bedarf unterschiedliche Meldungen anzuzeigen oder ungültige Einträge zu protokollieren.
VBA-basierte Lösungen bieten äußerste Flexibilität und eignen sich ideal für anspruchsvolle Validierungsanforderungen. Beachten Sie jedoch, dass Makros vom Benutzer aktiviert werden müssen und unter bestimmten Sicherheitseinstellungen möglicherweise nicht zur Verfügung stehen.
Zusammenfassend: Wenn Sie in Excel mehrere Validierungsregeln für eine Zelle verwenden, können Sie je nach Anforderung entweder formelbasierte Methoden für einfachere Szenarien einsetzen oder auf VBA zurückgreifen, um dynamischere und anspruchsvollere Datenvalidierungsabläufe zu realisieren. Wählen Sie sorgfältig die Methode, die am besten zu Ihren Zielen, der Komplexität Ihrer Daten und der Benutzerumgebung passt – so erzielen Sie optimale Ergebnisse.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins