Excel-Datenvalidierung: Hinzufügen, Verwenden, Kopieren und Entfernen von Datenvalidierung in Excel

In Excel ist die Funktion Datenüberprüfung ein leistungsstarkes Werkzeug, mit dem Sie einschränken können, was ein Benutzer in eine Zelle eingeben kann. Zum Beispiel können Sie Regeln festlegen, um die Länge von Text zu begrenzen, Einträge auf bestimmte Formate zu beschränken, eindeutige Werte zu erzwingen oder sicherzustellen, dass Text mit bestimmten Zeichen beginnt oder endet. Diese Möglichkeiten helfen, die Integrität der Daten zu wahren und Fehler in Ihren Arbeitsblättern zu reduzieren.

In diesem Tutorial wird untersucht, wie Sie Datenüberprüfung in Excel hinzufügen, verwenden und entfernen können. Es behandelt sowohl grundlegende als auch fortgeschrittene Vorgänge und bietet detaillierte Schritt-für-Schritt-Anleitungen, um Ihnen zu helfen, diese Funktion effektiv auf Ihre Aufgaben anzuwenden.

1. Was ist Datenvalidierung in Excel?

Die Funktion "Datenüberprüfung" kann Ihnen helfen, die Eingabeinhalte in Ihrem Arbeitsblatt einzuschränken. Normalerweise können Sie einige Validierungsregeln erstellen, um zu verhindern oder nur bestimmte Arten von Daten in einer Liste ausgewählter Zellen zuzulassen.

Einige grundlegende Anwendungen der Datenüberprüfungs-Funktion:

1. „Beliebiger Wert“: Es wird keine Validierung durchgeführt, Sie können alles in die angegebenen Zellen eingeben. 2. "Ganzzahliger Wert": Es sind nur ganze Zahlen erlaubt. 3. "Dezimal": Erlaubt die Eingabe von ganzen Zahlen sowie Dezimalzahlen. 4. „Liste“: Es dürfen nur Werte aus der vordefinierten Liste eingegeben oder ausgewählt werden. Die Werte werden in einer Dropdown-Liste angezeigt. 5. "Datum": Es sind nur Datumswerte erlaubt. 6. "Zeit": Es sind nur Uhrzeiten erlaubt. 7. "Textlänge": Es darf nur Text mit einer bestimmten Länge eingegeben werden. 8. "Benutzerdefiniert": Erstellen Sie benutzerdefinierte Formelregeln zur Überprüfung der Eingaben der Benutzer.

 2. Wie fügt man in Excel eine Datenvalidierung hinzu?

In einem Excel-Arbeitsblatt können Sie die Datenüberprüfung mit den folgenden Schritten hinzufügen:

1. Wählen Sie eine Liste von Zellen aus, in denen Sie die Datenüberprüfung festlegen möchten, und klicken Sie dann auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“. Siehe Screenshot:

2. Im Dialogfeld „Datenüberprüfung“ erstellen Sie unter der Registerkarte „Optionen“ bitte Ihre eigenen Überprüfungsregeln. In den Kriterienfeldern können Sie beliebige der folgenden Typen angeben:

• "Werte": Geben Sie Zahlen direkt in die Kriterienfelder ein;
• "Zellbezug": Verweisen Sie auf eine Zelle im Arbeitsblatt oder in einem anderen Arbeitsblatt;
• "Formeln": Erstellen Sie komplexere Formeln als Bedingungen.

Als Beispiel werde ich eine Regel erstellen, die nur ganze Zahlen zwischen 100 und 1000 erlaubt, hier sind die Kriterien wie im folgenden Screenshot gezeigt einzustellen:

3. Nachdem Sie die Bedingungen konfiguriert haben, können Sie zum Reiter „Eingabeaufforderung“ oder „Fehlerwarnung“ gehen, um die Eingabenachricht oder Fehlerwarnung für die zu überprüfenden Zellen nach Belieben einzustellen. (Wenn Sie keine Warnung einstellen möchten, klicken Sie bitte auf „OK“, um den Vorgang direkt abzuschließen.)

3.1) Eingabemeldung hinzufügen (optional):

Sie können eine Nachricht erstellen, die angezeigt wird, wenn eine Zelle mit Datenüberprüfung ausgewählt wird. Diese Nachricht hilft dem Benutzer zu erinnern, was in die Zelle eingegeben werden kann.

Gehen Sie zum Reiter „Eingabeaufforderung“ und führen Sie Folgendes aus:

Aktivieren Sie die Option „Eingabeaufforderung anzeigen, wenn Zelle ausgewählt ist“; Geben Sie den gewünschten Titel und die Erinnerungsnachricht in die entsprechenden Felder ein; Klicken Sie auf „OK“, um dieses Dialogfeld zu schließen.

Wenn Sie nun eine validierte Zelle auswählen, wird eine Meldungsbox wie folgt angezeigt:

3.2) Erstellen Sie aussagekräftige Fehlermeldungen (optional):

Neben der Erstellung der Eingabenachricht können Sie auch Fehlerwarnungen anzeigen, wenn ungültige Daten in eine Zelle mit Datenvalidierung eingegeben werden.

Gehen Sie zum Tab "Fehlerwarnung" im Dialogfeld "Datenüberprüfung", und führen Sie folgende Schritte aus:

Aktivieren Sie die Option „Fehlerwarnung anzeigen, nachdem ungültige Daten eingegeben wurden“; Wählen Sie in der Dropdown-Liste "Stil" den gewünschten Alarmtyp aus, den Sie benötigen: "Stop (Standard)": Diese Warnung verhindert, dass Benutzer ungültige Daten eingeben. "Warnung": Benutzer werden gewarnt, dass die Daten ungültig sind, aber es wird nicht verhindert, dass sie eingegeben werden. "Information": Informiert Benutzer nur über ungültige Dateneingaben. Geben Sie den gewünschten Titel und die Warnmeldung in die entsprechenden Felder ein; Klicken Sie auf „OK“, um das Dialogfeld zu schließen.

Wenn ein ungültiger Wert eingegeben wird, erscheint ein Warnfenster, wie im folgenden Screenshot dargestellt:

"Stopp"-Option: Klicken Sie auf "Wiederholen", um einen neuen Wert einzugeben, oder auf "Abbrechen", um den Eintrag zu verwerfen.

"Warnung"-Option: Klicken Sie auf "Ja", um den ungültigen Eintrag zu akzeptieren, "Nein", um ihn zu ändern, oder "Abbrechen", um ihn zu verwerfen.

"Information"-Option: Klicken Sie auf "OK", um den ungültigen Eintrag zu akzeptieren, oder auf "Abbrechen", um ihn zu verwerfen.

Hinweis: Wenn Sie keine eigene benutzerdefinierte Nachricht im Feld "Fehlerwarnung" festlegen, wird ein Standard-"Stopp"-Warnfeld angezeigt, wie unten dargestellt:

 3. Grundlegende Beispiele für die Datenvalidierung

Wenn Sie die Funktion Datenüberprüfung verwenden, stehen Ihnen 8 integrierte Optionen zur Verfügung, um die Datenüberprüfung festzulegen. Dazu gehören: beliebiger Wert, ganze Zahlen und Dezimalzahlen, Datum und Uhrzeit, Liste, Textlänge und benutzerdefinierte Formel. In diesem Abschnitt werden wir besprechen, wie man einige der integrierten Optionen in Excel verwendet.

3.1 Datenüberprüfung für ganze Zahlen und Dezimalzahlen

1. Wählen Sie eine Liste von Zellen aus, in denen Sie nur ganze Zahlen oder Dezimalzahlen zulassen möchten, und klicken Sie dann auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“.

2. Führen Sie im Dialogfeld „Datenüberprüfung“ unter der Registerkarte „Einstellungen“ die folgenden Schritte aus:

• Wählen Sie im Dropdown-Menü „Erlauben“ den entsprechenden Eintrag „Ganzzahl“ oder „Dezimalzahl“ aus.
• Wählen Sie dann eines der Kriterien im Feld "Daten" aus (in diesem Beispiel wähle ich die Option "zwischen").
• Tipps: Die Kriterien umfassen: zwischen, nicht zwischen, gleich, nicht gleich, größer als, kleiner als, größer als oder gleich, kleiner als oder gleich.
• Geben Sie als Nächstes die benötigten "Minimal"- und "Maximal"-Werte ein (in diesem Fall Zahlen zwischen 0 und 100).
• Klicken Sie zum Schluss auf die Schaltfläche "OK".

3. Nun können in den ausgewählten Zellen nur ganze Zahlen zwischen 0 und 100 eingegeben werden.

3.2 Datenüberprüfung für Datum und Uhrzeit

Um ein bestimmtes Datum oder eine bestimmte Uhrzeit zu validieren, die eingegeben werden sollen, ist dies ganz einfach mit dieser „Datenüberprüfung“. Bitte gehen Sie wie folgt vor:

1. Wählen Sie eine Liste von Zellen aus, in denen Sie nur bestimmte Datums- oder Zeitangaben zulassen möchten, und klicken Sie dann auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“.

2. Führen Sie im Dialogfeld „Datenüberprüfung“ unter der Registerkarte „Einstellungen“ die folgenden Schritte aus:

• Wählen Sie den entsprechenden Eintrag "Datum" oder "Uhrzeit" im Dropdown-Menü "Zulassen" aus.
• Wählen Sie dann eines der Kriterien im Feld "Daten" aus (hier wähle ich die Option "größer als").
• Tipps: Die Kriterien umfassen: zwischen, nicht zwischen, gleich, nicht gleich, größer als, kleiner als, größer als oder gleich, kleiner als oder gleich.
• Geben Sie als Nächstes das benötigte "Startdatum" ein (ich möchte die Daten größer als 20.08.2021).
• Klicken Sie zum Schluss auf die Schaltfläche „OK“.

3. Nun können in den ausgewählten Zellen nur noch Datumsangaben größer als 20.08.2021 eingegeben werden.

3.3 Datenüberprüfung für Textlänge

Wenn Sie die Anzahl der Zeichen begrenzen müssen, die in eine Zelle eingegeben werden können, zum Beispiel, um den Inhalt für einen bestimmten Bereich auf nicht mehr als 10 Zeichen zu beschränken, kann Ihnen diese „Datenüberprüfung“ ebenfalls helfen.

1. Wählen Sie eine Liste von Zellen aus, in denen Sie die Textlänge begrenzen möchten, und klicken Sie dann auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“.

2. Im Dialogfeld „Datenüberprüfung“ führen Sie unter der Registerkarte „Optionen“ die folgenden Schritte aus:

• Wählen Sie „Textlänge“ aus dem Dropdown-Menü „Zulassen“ aus.
• Wählen Sie dann eines der Kriterien im Feld "Daten" aus (in diesem Beispiel wähle ich die Option "kleiner als").
• Tipps: Die Kriterien umfassen: zwischen, nicht zwischen, gleich, nicht gleich, größer als, kleiner als, größer als oder gleich, kleiner als oder gleich.
• Geben Sie als Nächstes die "Maximale" Anzahl ein, die Sie begrenzen möchten (ich möchte, dass die Textlänge nicht mehr als 10 Zeichen beträgt).
• Klicken Sie zum Schluss auf die Schaltfläche "OK".

3. Jetzt dürfen in die ausgewählten Zellen nur Textzeichenfolgen mit weniger als 10 Zeichen eingegeben werden.

3.4 Datenvalidierungsliste (Dropdown-Liste)

Mit dieser leistungsstarken Funktion „Datenüberprüfung“ können Sie auch schnell und einfach eine Dropdown-Liste in Zellen erstellen. Gehen Sie dazu wie folgt vor:

1. Wählen Sie die Zellen aus, in denen die Dropdown-Liste eingefügt werden soll, und klicken Sie dann auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“.

2. Führen Sie im Dialogfeld „Datenüberprüfung“ unter der Registerkarte „Einstellungen“ die folgenden Schritte aus:

• Wählen Sie „Liste“ aus der Dropdown-Liste „Zulassen“ aus.
• Geben Sie im Textfeld "Quelle" die Listenelemente direkt durch Kommas getrennt ein. Um beispielsweise die Benutzereingabe auf drei Auswahlmöglichkeiten zu beschränken, geben Sie Nicht gestartet,In Bearbeitung,Abgeschlossen ein, oder Sie können eine Liste von Zellen auswählen, die die Werte enthalten, um die Dropdown-Liste darauf basierend einzufügen.
• Klicken Sie zum Schluss auf die Schaltfläche "OK".

3. Nun wurde die Dropdown-Liste wie im folgenden Screenshot gezeigt in die Zellen erstellt:

Klicken Sie hier, um weitere detaillierte Informationen zur Dropdown-Liste zu erhalten…

4. Erweiterte benutzerdefinierte Regeln für die Datenvalidierung

In diesem Abschnitt werde ich vorstellen, wie man fortgeschrittene benutzerdefinierte Datenvalidierungsregeln erstellt, um verschiedene Probleme zu lösen, wie zum Beispiel: Validierungsformeln erstellen, die nur Zahlen oder Textzeichenfolgen, nur eindeutige Werte, nur bestimmte Telefonnummern, E-Mail-Adressen und so weiter zulassen.

4.1 Datenüberprüfung erlaubt nur Zahlen oder Texte

Erlauben Sie nur die Eingabe von Zahlen mit der Datenüberprüfungs-Funktion

Um nur Zahlen in einem Zellbereich zuzulassen, gehen Sie wie folgt vor:

1. Wählen Sie einen Zellenbereich aus, in den nur Zahlen eingegeben werden sollen.

2. Klicken Sie auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" gehen Sie bitte wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein. ("A2" ist die erste Zelle des ausgewählten Bereichs, den Sie einschränken möchten.)

  =ISNUMBER(A2)

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

3. Ab sofort können in die ausgewählten Zellen nur noch Zahlen eingegeben werden.

Hinweis: Diese "ISTZAHL"-Funktion erlaubt beliebige numerische Werte in validierten Zellen, einschließlich Ganzzahlen, Dezimalzahlen, Brüchen, Datums- und Zeitangaben.

Erlauben Sie nur die Eingabe von Textzeichenfolgen mit der Datenüberprüfungsfunktion

Um die Zelleingaben auf Text zu beschränken, können Sie die Funktion "Datenüberprüfung" mit einer benutzerdefinierten Formel verwenden, die auf der "ISTEXT"-Funktion basiert. Gehen Sie dazu wie folgt vor:

1. Wählen Sie einen Zellbereich aus, in den nur Textzeichenfolgen eingegeben werden sollen.

2. Klicken Sie auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" gehen Sie bitte wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein. ("A2" ist die erste Zelle des ausgewählten Bereichs, den Sie einschränken möchten.)

  =ISTEXT(A2)

• Klicken Sie auf die Schaltfläche „OK“, um dieses Dialogfeld zu schließen.

3. Jetzt können bei der Eingabe von Daten in die spezifischen Zellen nur Daten im Textformat erlaubt werden.

4.2 Datenüberprüfung erlaubt nur alphanumerische Werte

Für bestimmte Zwecke möchten Sie möglicherweise nur Buchstaben und numerische Werte zulassen, während Sie Sonderzeichen wie ~, %, $ oder Leerzeichen einschränken. Dieser Abschnitt stellt einige nützliche Methoden vor.

 Erlauben Sie nur alphanumerische Werte mit der Datenüberprüfungsfunktion

Um Sonderzeichen zu verhindern und nur alphanumerische Werte zuzulassen, erstellen Sie eine benutzerdefinierte Formel in der Funktion "Datenüberprüfung", indem Sie diese Schritte befolgen:

1. Wählen Sie einen Zellbereich aus, in den nur alphanumerische Werte eingegeben werden sollen.

2. Klicken Sie auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" gehen Sie bitte unter dem Reiter "Einstellungen" wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.

  =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In den obigen Formeln ist "A2" die erste Zelle des ausgewählten Bereichs, den Sie einschränken möchten.

3. Nun sind nur noch Buchstaben und numerische Werte erlaubt, und die Sonderzeichen werden beim Tippen eingeschränkt, wie im folgenden Screenshot gezeigt:

 Erlauben Sie nur alphanumerische Werte durch die Verwendung einer erstaunlichen Funktion

Die obige Formel mag kompliziert zu verstehen und zu merken sein. Hier möchte ich eine praktische Funktion namens "Eingabe begrenzen" von "Kutools für Excel" vorstellen, die diese Aufgabe erheblich vereinfacht.

1. Wählen Sie einen Zellbereich aus, in den nur alphanumerische Werte eingegeben werden sollen.

2. Klicken Sie dann auf "Kutools" > "Eingabe begrenzen" > "Eingabe begrenzen", siehe Screenshot:

3. Wählen Sie im aufgepoppten Dialogfeld „Eingabe begrenzen“ die Option „Eingabe von Sonderzeichen verbieten“, siehe Screenshot:

4. Klicken Sie dann auf die Schaltfläche „OK“, und in den folgenden Dialogfeldern klicken Sie auf „Ja“ > „OK“, um den Vorgang abzuschließen. Nun sind in den ausgewählten Zellen nur Buchstaben und numerische Werte erlaubt, siehe Screenshot:

4.3 Datenvalidierung erlaubt Texte, die mit bestimmten Zeichen beginnen oder enden

Wenn alle Werte in einem bestimmten Bereich mit einem bestimmten Zeichen oder Teilstring beginnen oder enden müssen, können Sie eine Datenüberprüfung mit einer benutzerdefinierten Formel verwenden, die auf den Funktionen EXACT, LEFT, RIGHT oder COUNTIF basiert.

 Erlaubt Texte, die mit bestimmten Zeichen beginnen oder enden, mit nur einer Bedingung

Wenn Sie möchten, dass Texteinträge in bestimmten Zellen mit „CN“ beginnen oder enden, folgen Sie diesen Schritten:

1. Wählen Sie einen Zellbereich aus, der nur Texte zulässt, die mit bestimmten Zeichen beginnen oder enden.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" gehen Sie bitte unter dem Reiter "Einstellungen" wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.
  Nur das Eingeben von Texten erlauben, die mit CN beginnen:

  =EXACT(LEFT(A2,2),"CN")

  Nur das Eingeben von Text erlauben, der mit CN endet:

  =EXACT(RIGHT(A2,2),"CN")

• Klicken Sie auf die Schaltfläche „OK“, um dieses Dialogfeld zu schließen.

Hinweis: In den obigen Formeln ist "A2" die erste Zelle des ausgewählten Bereichs, die Zahl "2" ist die Anzahl der Zeichen, die Sie angegeben haben, und "CN" ist der Text, mit dem Sie beginnen oder enden möchten.

3. Ab sofort können in die ausgewählten Zellen nur noch Textzeichenfolgen eingegeben werden, die mit den angegebenen Zeichen beginnen oder enden. Andernfalls wird eine Warnung angezeigt, wie im folgenden Screenshot dargestellt:

=COUNTIF(A2,"CN*")

=COUNTIF(A2,"*CN")

Hinweis: Das Sternchen * ist ein Platzhalter, der ein oder mehrere Zeichen entspricht.

 Erlauben Sie Texte, die mit bestimmten Zeichen beginnen oder enden, mit mehreren Kriterien (ODER-Logik)

Wenn Sie möchten, dass Texteinträge beispielsweise mit „CN“ oder „UK“ beginnen oder enden, wie im folgenden Screenshot gezeigt, müssen Sie eine weitere Instanz von IDENTISCH hinzufügen, indem Sie ein Pluszeichen (+) verwenden. Gehen Sie dazu wie folgt vor:

1. Wählen Sie einen Zellbereich aus, der nur Texte zulässt, die mit mehreren Kriterien beginnen oder enden.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" gehen Sie unter dem Reiter "Einstellungen" wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.
  Erlauben Sie nur die Eingabe von Text, der mit CN oder UK beginnt:

  =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")

  Erlauben Sie nur die Eingabe von Text, der mit CN oder UK endet:

  =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")

• Klicken Sie auf die Schaltfläche „OK“, um dieses Dialogfeld zu schließen.

Hinweis: In den obigen Formeln ist "A2" die erste Zelle des ausgewählten Bereichs, die Zahl "2" ist die Anzahl der Zeichen, die Sie angegeben haben, und "CN" sowie "UK" sind die spezifischen Texte, mit denen Sie beginnen oder enden möchten.

3. Nun können nur Textzeichenfolgen, die mit den angegebenen Zeichen beginnen oder enden, in die ausgewählten Zellen eingegeben werden.

=COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")

=COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")

Hinweis: Das Sternchen * ist ein Platzhalter, der ein oder mehrere Zeichen entspricht.

4.4 Datenüberprüfung erlaubt Einträge, die bestimmten Text enthalten / nicht enthalten müssen

In diesem Abschnitt werde ich darüber sprechen, wie man die Datenüberprüfung anwendet, um Werte zu erlauben, die einen bestimmten Teilstring enthalten müssen oder nicht enthalten dürfen, oder einen von mehreren Teilstrings in Excel.

 Eingaben müssen einen oder mehrere bestimmte Texte enthalten

Eingaben müssen einen bestimmten Text enthalten

Um Einträge zuzulassen, die eine bestimmte Textzeichenfolge enthalten, zum Beispiel sollten alle eingegebenen Werte den Text „KTE“ enthalten, wie im folgenden Screenshot gezeigt, können Sie die Datenüberprüfung mit einer benutzerdefinierten Formel basierend auf den Funktionen FINDEN und ISTZAHL anwenden. Bitte gehen Sie wie folgt vor:

1. Wählen Sie einen Zellenbereich aus, der nur Texte zulässt, die bestimmte Textteile enthalten.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" gehen Sie bitte unter der Registerkarte "Einstellungen" wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie anschließend eine der folgenden Formeln in das Textfeld "Formel" ein.
  Groß-/Kleinschreibung beachten:

  =ISNUMBER(FIND("KTE",A2)) 

  Groß-/Kleinschreibung nicht beachten:

  =ISNUMBER(SEARCH("KTE",A2))

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In den obigen Formeln ist "A2" die erste Zelle des ausgewählten Bereichs, der Text "KTE" ist die Zeichenfolge, die die Einträge enthalten müssen.

3. Jetzt wird ein Warnhinweisfenster angezeigt, wenn der eingegebene Wert den erforderlichen Text nicht enthält.

Eingaben müssen einen von mehreren bestimmten Texten enthalten

Die obige Formel funktioniert nur für eine Textzeichenfolge. Wenn in den Zellen mehrere Textzeichenfolgen erlaubt sein sollen, wie im folgenden Screenshot gezeigt, sollten Sie die Funktionen SUMMENPRODUKT, FINDEN und ISTZAHL zusammen verwenden, um eine Formel zu erstellen.

1. Wählen Sie einen Zellbereich aus, der nur Texte zulässt, die eines von vielen Elementen enthalten.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" gehen Sie bitte unter dem Reiter "Einstellungen" wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann eine der folgenden Formeln je nach Bedarf in das Textfeld "Formel" ein.
  Groß-/Kleinschreibung beachten:

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0

  Groß-/Kleinschreibung nicht beachten:

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0

• Klicken Sie dann auf "OK", um das Dialogfeld zu schließen.

Hinweis: In den obigen Formeln ist "A2" die erste Zelle des ausgewählten Bereichs, "C2:C4" ist die Liste der Werte, von denen Einträge einen davon enthalten dürfen.

3. Und jetzt können nur Einträge, die einen der Werte in der spezifischen Liste enthalten, eingegeben werden.

Eingaben dürfen keinen oder einen von vielen spezifischen Texten enthalten

Eingaben dürfen einen bestimmten Text nicht enthalten

Um zu validieren, dass die Einträge bestimmten Text nicht enthalten dürfen, zum Beispiel um Werte zuzulassen, die den Text „KTE“ in einer Zelle nicht enthalten sollen, können Sie die Funktionen ISERROR und FIND verwenden, um eine Datenvalidierungsregel zu erstellen. Gehen Sie wie folgt vor:

1. Wählen Sie einen Zellenbereich aus, der nur Texte zulässt, die bestimmte Texte nicht enthalten.

2. Klicken Sie anschließend auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“. Im erscheinenden Dialogfeld „Datenüberprüfung“ gehen Sie bitte unter der Registerkarte „Einstellungen“ wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie anschließend eine der folgenden Formeln in das Textfeld "Formel" ein.
  Groß-/Kleinschreibung beachten:

  =ISERROR(FIND("KTE",A2))

  Groß-/Kleinschreibung nicht beachten:

  =ISERROR(SEARCH("KTE",A2))

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In den obigen Formeln ist "A2" die erste Zelle des ausgewählten Bereichs, der Text "KTE" ist die Zeichenfolge, die die Einträge nicht enthalten dürfen.

3. Jetzt wird die Eingabe von Einträgen, die den spezifischen Text enthalten, verhindert.

Eingaben dürfen keinen der vielen spezifischen Texte enthalten

Um zu verhindern, dass einer von vielen Textstrings in einer Liste eingegeben wird, wie im Screenshot unten gezeigt, folgen Sie diesen Schritten:

1. Wählen Sie einen Zellbereich aus, in dem bestimmte Texte verhindert werden sollen.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" gehen Sie unter dem Reiter "Einstellungen" wie folgt vor:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.
  Groß-/Kleinschreibung beachten:

  =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0

  Groß-/Kleinschreibung nicht beachten:

  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0

• Klicken Sie anschließend auf "OK", um das Dialogfeld zu schließen.

Hinweis: In den obigen Formeln ist "A2" die erste Zelle des ausgewählten Bereichs und "C2:C4" ist die Liste der Werte, die Sie verhindern möchten, wenn Einträge eines davon enthalten.

3. Ab sofort wird die Eingabe von Einträgen, die bestimmte Texte enthalten, verhindert.

4.5 Datenüberprüfung erlaubt nur eindeutige Werte

Wenn Sie verhindern möchten, dass doppelte Daten in einen Zellenbereich eingegeben werden, stellt dieser Abschnitt einige schnelle Methoden zur Lösung dieser Aufgabe in Excel vor.

Erlauben Sie nur eindeutige Werte durch die Verwendung der Datenüberprüfungs-Funktion

Normalerweise kann Ihnen die Funktion Datenüberprüfung mit einer benutzerdefinierten Formel, basierend auf der COUNTIF-Funktion, helfen. Bitte folgen Sie den folgenden Schritten:

1. Wählen Sie die Zellen oder Spalte aus, in die nur eindeutige Werte eingegeben werden sollen.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.

  =COUNTIF($A$2:$A$9,A2)=1

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist „A2:A9“ der Bereich von Zellen, in dem nur eindeutige Werte erlaubt sind, und „A2“ ist die erste Zelle des ausgewählten Bereichs.

3. Jetzt können nur noch eindeutige Werte eingegeben werden, und es wird eine Warnmeldung angezeigt, wenn doppelte Daten eingegeben werden, wie im folgenden Screenshot gezeigt:

 Erlauben Sie nur eindeutige Werte durch die Verwendung von VBA-Code

Der folgende VBA-Code kann Ihnen ebenfalls helfen, das Eingeben doppelter Werte zu verhindern. Gehen Sie dazu wie folgt vor:

1. Klicken Sie mit der rechten Maustaste auf die Registerkarte des Arbeitsblatts, in dem Sie nur eindeutige Werte zulassen möchten, und wählen Sie "Code anzeigen" aus dem Kontextmenü. Im erscheinenden Fenster "Microsoft Visual Basic for Applications" kopieren Sie bitte den folgenden Code und fügen ihn in das leere Modul ein:

VBA-Code: Nur eindeutige Werte in einem Zellbereich zulassen:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Hinweis: Im obigen Code sind "A1:A100" und "A:A" die Zellen in der Spalte, in denen Sie Duplikate verhindern möchten. Bitte ändern Sie sie entsprechend Ihren Anforderungen.

2. Speichern und schließen Sie diesen Code dann. Jetzt erscheint eine Warnung, wenn Sie einen doppelten Wert in den Zellen A1:A100 eingeben, wie in dem Screenshot unten gezeigt.

Erlauben Sie nur eindeutige Werte durch die Verwendung einer praktischen Funktion

Wenn Sie "Kutools für Excel" haben, können Sie mit der Funktion "Duplikate verhindern" schnell eine Datenvalidierung einrichten, um Duplikate in einem Zellbereich mit nur wenigen Klicks zu verhindern.

1. Wählen Sie den Zellbereich aus, in dem Sie doppelte Werte verhindern und nur eindeutige Daten zulassen möchten.

2. Klicken Sie dann auf „Kutools“ > „Eingabe begrenzen“ > „Duplikate verhindern“. Siehe Screenshot:

3. Eine Warnmeldung wird angezeigt, die Sie darüber informiert, dass die Datenüberprüfung entfernt wird, wenn Sie diese Funktion anwenden. Klicken Sie auf „Ja“, und im darauffolgenden Dialogfeld klicken Sie auf „OK“, wie in den folgenden Screenshots gezeigt:

4. Jetzt wird, wenn Sie in Ihren angegebenen Zellen doppelte Daten eingeben, ein Hinweisfeld angezeigt, das Sie daran erinnert, dass die doppelten Daten ungültig sind, siehe Screenshot:

4.6 Datenüberprüfung erlaubt nur Großbuchstaben / Kleinbuchstaben / Ersten Buchstaben groß

Die Funktion "Datenüberprüfung" ist ein leistungsstarkes Werkzeug, mit dem Benutzer die Eingabe von Großbuchstaben, Kleinbuchstaben oder der richtigen Schreibweise in einem Zellbereich erzwingen können. Gehen Sie wie folgt vor:

1. Wählen Sie den Zellbereich aus, in den nur Großbuchstaben, Kleinbuchstaben oder Text in korrekter Schreibweise eingegeben werden soll.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann eine der folgenden Formeln, die Sie benötigen, in das Textfeld "Formel" ein.
  nur Großbuchstaben-Text erlauben:

  =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))

  Nur Kleinbuchstaben-Text erlauben

  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))

  Nur Text in korrekter Groß- und Kleinschreibung erlauben

  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, die Sie verwenden möchten.

3. Nun werden nur noch die Einträge akzeptiert, die der von Ihnen erstellten Regel entsprechen.

4.7 Datenüberprüfung erlaubt Werte, die in einer anderen Liste vorhanden / nicht vorhanden sind

Das Zulassen oder Verhindern von Werten auf Basis ihrer Präsenz in einer anderen Liste kann für viele Benutzer eine Herausforderung darstellen. Tatsächlich können Sie die Datenüberprüfungs-Funktion mit einer einfachen Formel, basierend auf der COUNTIF-Funktion, verwenden, um damit umzugehen.

Zum Beispiel möchte ich, dass nur die Werte im Bereich C2:C4 in einen Zellenbereich eingegeben werden, wie im folgenden Screenshot gezeigt. Um diese Aufgabe zu lösen, gehen Sie bitte wie folgt vor:

1. Wählen Sie den Zellbereich aus, auf den Sie die Datenüberprüfung anwenden möchten.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann eine der folgenden Formeln, die Sie benötigen, in das Textfeld "Formel" ein.
  Nur Werte zulassen, die in einer anderen Spalte vorhanden sind

  =COUNTIF($C$2:$C$4,A2)>0

  Verhindern, dass Werte in einer anderen Spalte vorhanden sind

  =COUNTIF($C$2:$C$4,A2)=0

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, die Sie verwenden möchten, und "C2:C4" ist die Liste der Werte, deren Eingabe Sie entweder verhindern oder erlauben möchten, wenn sie einer davon sind.

3. Jetzt können nur noch Einträge, die der von Ihnen erstellten Regel entsprechen, eingegeben werden; andere werden verhindert.

4.8 Datenüberprüfung erzwingt die Eingabe nur im Telefonnummerformat

Wenn Sie die Informationen Ihrer Mitarbeiter eingeben, muss in einer Spalte die Telefonnummer eingegeben werden. Um sicherzustellen, dass Telefonnummern schnell und genau eingegeben werden, können Sie in diesem Fall eine Datenvalidierung für die Telefonnummern festlegen. Zum Beispiel möchte ich nur Telefonnummern in diesem Format (123) 456-7890 erlauben, die in einem Arbeitsblatt eingegeben werden können. Dieser Abschnitt stellt zwei schnelle Tricks zur Lösung dieser Aufgabe vor.

 Erzwingen Sie nur das Telefonnummerformat mit der Datenvalidierungsfunktion

Um nur ein bestimmtes Telefonnummerformat eingeben zu können, folgen Sie diesen Schritten:

1. Wählen Sie die Liste der Zellen aus, in die Sie ein bestimmtes Telefonnummerformat eingeben möchten, und klicken Sie dann mit der rechten Maustaste. Wählen Sie "Zellen formatieren" aus dem Kontextmenü, siehe Screenshot:

2. Wählen Sie im Dialogfeld „Zellen formatieren“ unter der Registerkarte „Zahl“ in der linken Liste „Kategorie“ die Option „Benutzerdefiniert“ aus, und geben Sie dann das gewünschte Telefonnummerformat in das Textfeld „Typ“ ein. Zum Beispiel werde ich dieses Format „(###) ###-####“ verwenden, siehe Screenshot:

3. Klicken Sie dann auf „OK“, um das Dialogfeld zu schließen.

4. Nachdem Sie die Zellen formatiert haben, wählen Sie sie erneut aus und öffnen Sie das Dialogfeld „Datenüberprüfung“, indem Sie auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“ klicken. Führen Sie im erscheinenden Dialogfeld unter der Registerkarte „Einstellungen“ die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Formeltextfeld ein.

  =AND(ISNUMBER(A2),LEN(A2)=10)

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, in der Sie die Telefonnummer validieren möchten.

5. Jetzt wird beim Eingeben einer 10-stelligen Zahl diese automatisch in das spezifische Telefonnummerformat umgewandelt, wie Sie es benötigen, siehe Screenshots:

Hinweis: Wenn die eingegebene Zahl nicht 10 Stellen hat, wird ein Warnhinweis angezeigt, um Sie daran zu erinnern, siehe Screenshot:

 Erzwingen Sie das Telefonnummerformat nur mit einem nützlichen Feature

Die Funktion "Validate Phone Number" von "Kutools for Excel" kann Ihnen auch dabei helfen, nur Telefonnummern im richtigen Format mit wenigen Klicks eingeben zu lassen.

1. Wählen Sie die Liste der Zellen aus, die nur bestimmte Telefonnummern zulassen, und klicken Sie dann auf „Kutools“ > „Eingabe begrenzen“ > „Telefonnummer validieren“. Siehe Screenshot:

2. Wählen Sie im Dialogfeld „Telefonnummer“ das gewünschte Telefonnummerformat aus, oder erstellen Sie Ihr eigenes Format, indem Sie auf die Schaltfläche „Hinzufügen“ klicken. Siehe Screenshot:

3. Nachdem Sie die Telefonnummer-Formatierung ausgewählt oder festgelegt haben, klicken Sie auf „OK“. Nun kann nur noch eine Telefonnummer im spezifischen Format eingegeben werden, andernfalls wird eine Warnmeldung angezeigt, um Sie daran zu erinnern, siehe Screenshot:

4.9 Datenüberprüfung erzwingt die Eingabe nur von E-Mail-Adressen

Angenommen, Sie müssen mehrere E-Mail-Adressen in einer Spalte eines Arbeitsblatts eingeben. Um zu verhindern, dass falsch formatierte E-Mail-Adressen eingegeben werden, können Sie normalerweise eine Datenvalidierungsregel festlegen, die nur die Eingabe von E-Mail-Adressen im richtigen Format erlaubt.

Erzwingen Sie das Format von E-Mail-Adressen nur durch die Verwendung der Datenüberprüfungs-Funktion

Durch die Verwendung der Datenüberprüfungs-Funktion mit einer benutzerdefinierten Formel können Sie schnell eine Regel erstellen, um die Eingabe ungültiger E-Mail-Adressen zu verhindern. Folgen Sie diesen Schritten:

1. Wählen Sie die Zellen aus, in die nur E-Mail-Adressen eingegeben werden sollen, und klicken Sie dann auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“.

2. Im aufgepoppten Dialogfeld „Datenüberprüfung“ führen Sie unter der Registerkarte „Optionen“ die folgenden Vorgänge aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“.
• Geben Sie dann die folgende Formel in das Formel-Textfeld ein:

  =ISNUMBER(MATCH("*@*.?*",A2,0))

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, die Sie verwenden möchten.

3. Jetzt wird eine Warnmeldung angezeigt, wenn der eingegebene Text nicht dem E-Mail-Adressformat entspricht, um Sie zu benachrichtigen, siehe Screenshot:

 Erzwingen Sie nur E-Mail-Adressenformat mithilfe einer praktischen Funktion

"Kutools für Excel" bietet eine erstaunliche Funktion – "Es können nur E-Mail-Adressen eingegeben werden". Mit diesem Hilfsmittel können Sie ungültige E-Mail-Adressen mit nur einem Klick verhindern.

1. Wählen Sie die Zellen aus, in die nur E-Mail-Adressen eingegeben werden dürfen, und klicken Sie dann auf „Kutools“ > „Eingabe begrenzen“ > „Es können nur E-Mail-Adressen eingegeben werden“. Siehe Screenshot:

2. Danach ist nur die Eingabe von E-Mail-Adressen im richtigen Format erlaubt, andernfalls wird ein Warnhinweis angezeigt, um Sie daran zu erinnern, siehe Screenshot:

4.10 Datenvalidierung erzwingt die Eingabe nur von IP-Adressen

In diesem Abschnitt werde ich einige schnelle Tricks vorstellen, um die Datenvalidierung so einzurichten, dass sie nur IP-Adressen in einem Zellbereich akzeptiert.

Erzwingen Sie nur das IP-Adressformat mit der Datenvalidierungsfunktion

Um nur IP-Adressen in einem bestimmten Zellbereich eingeben zu können, führen Sie folgende Schritte aus:

1. Wählen Sie die Zellen aus, in die nur IP-Adressen eingegeben werden sollen, und klicken Sie dann auf „Daten“ > „Datenüberprüfung“ > „Datenüberprüfung“.

2. Im aufgepoppten Dialogfeld „Datenüberprüfung“ führen Sie unter der Registerkarte „Einstellungen“ die folgenden Vorgänge aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.

  =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, die Sie verwenden möchten.

3. Jetzt wird eine Warnmeldung angezeigt, wenn eine ungültige IP-Adresse in die Zelle eingegeben wird, wie im folgenden Screenshot dargestellt:

 Erzwingen Sie nur das IP-Adressformat mit VBA-Code

Hier kann der folgende VBA-Code ebenfalls helfen, nur die Eingabe von IP-Adressen zuzulassen und andere Eingaben zu beschränken. Gehen Sie wie folgt vor:

1. Klicken Sie mit der rechten Maustaste auf die Registerkarte des Arbeitsblatts und wählen Sie "Code anzeigen" aus dem Kontextmenü. Im sich öffnenden Fenster "Microsoft Visual Basic for Applications" kopieren Sie den folgenden VBA-Code hinein.

VBA-Code: Zellen validieren, um nur IP-Adressen zu akzeptieren

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Hinweis: Im obigen Code ist "A2:A10" der Zellbereich, in dem nur IP-Adressen akzeptiert werden sollen.

2. Speichern Sie dann diesen Code und schließen Sie ihn. Jetzt können nur gültige IP-Adressen in die festgelegten Zellen eingegeben werden.

Erzwingen Sie das Format nur für IP-Adressen mithilfe einer einfachen Funktion

Wenn Sie „Kutools for Excel“ in Ihrer Arbeitsmappe installiert haben, kann dessen Funktion „Validate IP Address“ (IP-Adresse validieren) Ihnen ebenfalls helfen, diese Aufgabe zu lösen.

1. Wählen Sie die Zellen aus, in die nur IP-Adressen eingegeben werden dürfen, und klicken Sie dann auf „Kutools“ > „Eingabe begrenzen“ > „Es können nur IP-Adressen eingegeben werden“. Siehe Screenshot:

2. Nachdem Sie diese Funktion angewendet haben, können jetzt nur noch IP-Adressen eingegeben werden, andernfalls wird ein Warnhinweis angezeigt, um Sie daran zu erinnern, siehe Screenshot:

4.11 Datenüberprüfung beschränkt Werte, die den Gesamtwert überschreiten

Angenommen, Sie haben einen monatlichen Ausgabenbericht und das Budget beträgt insgesamt 18.000 USD. Sie müssen sicherstellen, dass die Gesamtsumme in der Ausgabenliste diesen voreingestellten Betrag nicht überschreitet, wie im Screenshot unten dargestellt. In diesem Fall können Sie eine Datenvalidierungsregel erstellen, indem Sie die SUM-Funktion verwenden, um zu verhindern, dass die Summe der Werte den voreingestellten Gesamtbetrag überschreitet.

1. Wählen Sie die Liste der Zellen aus, in denen die Werte begrenzt werden sollen.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.

  =SUM($B$2:$B$7)<=18000

• Klicken Sie auf die Schaltfläche „OK“, um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "B2:B7" der Bereich von Zellen, in dem Sie die Eingaben einschränken möchten.

3. Jetzt wird bei der Eingabe von Werten im Bereich B2:B7 die Validierung bestanden, wenn die Summe der Werte weniger als 18.000 USD beträgt. Wenn ein Wert dazu führt, dass die Summe 18.000 USD überschreitet, erscheint eine Warnmeldung, um Sie zu benachrichtigen.

4.12 Datenüberprüfung beschränkt Zelleingabe basierend auf einer anderen Zelle

Wenn Sie Dateneingaben in einer Liste von Zellen basierend auf dem Wert in einer anderen Zelle einschränken möchten, kann die Funktion Datenüberprüfung ebenfalls helfen, diese Aufgabe zu lösen. Zum Beispiel: Wenn die Zelle C1 den Text „Ja“ enthält, erlaubt der Bereich A2:A9 beliebige Eingaben. Wenn jedoch die Zelle C1 einen anderen Text enthält, werden Einträge im Bereich A2:A9 eingeschränkt, wie in den folgenden Screenshots dargestellt:

Um dieses Problem zu lösen, gehen Sie bitte wie folgt vor:

1. Wählen Sie die Liste der Zellen aus, in denen die Werte begrenzt werden sollen.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.

  =$C$1="Yes"

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "C1" die Zelle, die den spezifischen Text enthält, den Sie verwenden möchten, und der Text „Ja“ ist der Text, auf dessen Basis Sie die Zellen einschränken möchten. Bitte passen Sie sie entsprechend Ihren Anforderungen an.

3. Jetzt kann bei „Ja“ im Zellen C1 in den Bereich A2:A9 beliebig etwas eingegeben werden. Wenn Zelle C1 anderen Text enthält, können Sie keinen Wert eingeben, wie in der folgenden Demo zu sehen ist:

4.13 Datenüberprüfung erlaubt nur die Eingabe von Wochentagen oder Wochenenden

Wenn Sie in einer Liste von Zellen nur Wochentage (von Montag bis Freitag) oder Wochenenden (Samstag und Sonntag) eingeben möchten, kann Ihnen die "Datenüberprüfung" ebenfalls helfen. Bitte folgen Sie den folgenden Schritten:

1. Wählen Sie die Liste der Zellen aus, in die Sie die Wochentage eingeben möchten.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann eine der folgenden Formeln in das Textfeld "Formel" ein, je nach Bedarf.
  Nur Wochentage zulassen

  =WEEKDAY(A2,2)<6

  Nur Wochenenden erlauben

  =WEEKDAY(A2,2)>5

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, die Sie verwenden möchten.

3. Nun können Sie basierend auf Ihrer Auswahl nur Wochentags- oder Wochenenddaten in den angegebenen Zellen eingeben.

4.14 Datenüberprüfung erlaubt eingegebenes Datum basierend auf dem heutigen Datum

Manchmal müssen Sie möglicherweise nur Datumsangaben größer oder kleiner als heute in einer Liste von Zellen zulassen. Die Funktion "Datenüberprüfung" zusammen mit der "TODAY"-Funktion kann Ihnen dabei helfen. Gehen Sie wie folgt vor:

1. Wählen Sie die Liste der Zellen aus, in denen nur das zukünftige Datum (Datum größer als heute) eingegeben werden soll.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“ aus.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.

  =A2>Today()

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, die Sie verwenden möchten.

3. Jetzt können nur noch Datumsangaben später als heute in die Zellen eingegeben werden. Andernfalls wird ein Warnhinweis angezeigt, um Sie zu benachrichtigen, siehe Screenshot:

=A2<Today()

=AND(A2>TODAY(),A2<=(TODAY()+30))

4.15 Datenüberprüfung erlaubt eingegebene Zeit basierend auf der aktuellen Zeit

Wenn Sie Daten auf Basis der aktuellen Uhrzeit validieren möchten, zum Beispiel nur Uhrzeiten vor oder nach der aktuellen Uhrzeit in die Zellen eingegeben werden können, können Sie Ihre eigene Datenvalidierungsformel erstellen. Gehen Sie dazu wie folgt vor:

1. Wählen Sie die Liste der Zellen aus, in denen nur Uhrzeiten vor oder nach der aktuellen Zeit eingegeben werden sollen.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Zeit“ aus der Dropdown-Liste „Erlauben“.
• Wählen Sie dann "Kleiner als", um nur Uhrzeiten vor der aktuellen Zeit zuzulassen, oder "Größer als", um Uhrzeiten nach der aktuellen Zeit zuzulassen, je nach Bedarf aus dem Dropdown-Menü "Daten".
• Geben Sie dann in das Feld „Endzeit“ oder „Startzeit“ die folgende Formel ein:

  =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, die Sie verwenden möchten.

3. Nun können nur noch Zeiten vor oder nach der aktuellen Uhrzeit in die spezifischen Zellen eingegeben werden.

4.16 Datumsvalidierung für ein spezifisches oder das aktuelle Jahr

Um nur Datumsangaben in einem bestimmten Jahr oder im aktuellen Jahr zu erlauben, können Sie eine Datenüberprüfung mit einer benutzerdefinierten Formel verwenden, die auf der YEAR-Funktion basiert.

1. Wählen Sie die Liste der Zellen aus, in denen nur Datumsangaben eines bestimmten Jahres eingegeben werden sollen.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Benutzerdefiniert“ aus der Dropdown-Liste „Erlauben“.
• Geben Sie dann die folgende Formel in das Textfeld "Formel" ein.

  =YEAR(A2)=2020

• Klicken Sie auf die Schaltfläche "OK", um dieses Dialogfeld zu schließen.

Hinweis: In der obigen Formel ist "A2" die erste Zelle der Spalte, die Sie verwenden möchten, und "2020" ist die Jahreszahl, die Sie einschränken möchten.

3. Anschließend können nur Datumsangaben im Jahr 2020 eingegeben werden, andernfalls wird eine Warnmeldung wie im folgenden Screenshot angezeigt:

=YEAR(A2)=YEAR(TODAY())

4.17 Datumsvalidierung für das aktuelle Woche oder Monat

Wenn Sie Benutzern erlauben möchten, Datumsangaben für die aktuelle Woche oder den aktuellen Monat in bestimmte Zellen einzugeben, stellt dieser Abschnitt einige Formeln vor, um diese Aufgabe in Excel zu bewältigen.

Erlauben Sie die Eingabe des Datums der aktuellen Woche

1. Wählen Sie die Liste der Zellen aus, in denen nur Datumsangaben der aktuellen Woche eingegeben werden sollen.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Datum“ aus der Dropdown-Liste „Erlauben“.
• Wählen Sie dann "zwischen" aus dem Dropdown-Menü "Daten" aus.
• Geben Sie in das Textfeld „Startdatum“ diese Formel ein:

  =TODAY()-WEEKDAY(TODAY(),3)

• Geben Sie im Textfeld "Enddatum" diese Formel ein:

  =TODAY()-WEEKDAY(TODAY(),3)+6

• Klicken Sie zum Schluss auf die Schaltfläche "OK".

3. Anschließend können nur noch Datumsangaben innerhalb der aktuellen Woche eingegeben werden, andere Daten werden wie im folgenden Screenshot gezeigt verhindert:

 Erlaubt die Eingabe des Datums des aktuellen Monats

Um nur die Eingabe von Datumsangaben des aktuellen Monats zu erlauben, gehen Sie bitte wie folgt vor:

1. Wählen Sie die Liste der Zellen aus, in die nur Datumsangaben des aktuellen Monats eingegeben werden sollen.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld "Datenüberprüfung" führen Sie unter der Registerkarte "Einstellungen" die folgenden Schritte aus:

• Wählen Sie „Datum“ aus der Dropdown-Liste „Zulassen“.
• Wählen Sie dann zwischen den Optionen aus dem Dropdown-Menü "Daten".
• Geben Sie in das Textfeld "Startdatum" diese Formel ein:

  =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

• Geben Sie im Textfeld „Enddatum“ diese Formel ein:

  =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1))

• Klicken Sie zum Schluss auf die Schaltfläche "OK".

3. Ab sofort können in die ausgewählten Zellen nur noch Datumsangaben innerhalb des aktuellen Monats eingegeben werden.

5. Wie bearbeitet man die Datenvalidierung in Excel?

Um eine bestehende Datenvalidierungsregel zu bearbeiten oder zu ändern, folgen Sie den untenstehenden Schritten:

1. Wählen Sie eine beliebige Zelle aus, die die Datenvalidierungsregel enthält.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung", um zum Dialogfeld "Datenüberprüfung" zu gelangen. Bearbeiten oder ändern Sie im Feld die Regeln nach Bedarf, und aktivieren Sie dann die Option "Diese Änderungen auf alle anderen Zellen mit denselben Einstellungen anwenden", um diese neue Regel auf alle anderen Zellen mit den ursprünglichen Überprüfungsbedingungen anzuwenden. Siehe Screenshot:

3. Klicken Sie auf „OK“, um die Änderungen zu speichern.

6. Wie findet und wählt man Zellen mit Datenvalidierung in Excel aus?

Wenn Sie in Ihrem Arbeitsblatt mehrere Datenüberprüfungsregeln erstellt haben und nun die Zellen finden und auswählen müssen, auf die diese Regeln angewendet wurden, kann Ihnen der Befehl "Gehe zu Spezial" helfen, alle Arten von Datenüberprüfungen oder einen bestimmten Typ von Datenüberprüfung auszuwählen.

1. Aktivieren Sie das Arbeitsblatt, in dem Sie die Zellen mit Datenüberprüfung finden und auswählen möchten.

2. Klicken Sie anschließend auf "Start" > "Suchen & Auswählen" > "Gehe zu speziell", siehe Screenshot:

3. Wählen Sie im Dialogfeld "Gehe zu speziell" die Option "Datenüberprüfung" > "Alle", siehe Screenshot:

4. Alle Zellen mit Datenüberprüfung wurden nun im aktuellen Arbeitsblatt ausgewählt.

Tipp: Wenn Sie eine bestimmte Art von Datenüberprüfung auswählen möchten, wählen Sie zuerst eine Zelle aus, die die gewünschte Datenüberprüfung enthält, gehen Sie dann zum Dialogfeld "Gehe zu speziell", und wählen Sie "Datenüberprüfung" > "Gleich".

7. Wie kopiert man die Datenüberprüfungsregel in andere Zellen?

Angenommen, Sie haben eine Datenvalidierungsregel für eine Liste von Zellen erstellt, und jetzt müssen Sie dieselbe Datenvalidierungsregel auf andere Zellen anwenden. Anstatt die Regel erneut zu erstellen, können Sie die vorhandene Regel schnell und einfach auf andere Zellen kopieren und einfügen.

1. Klicken Sie auf eine Zelle mit der gewünschten Validierungsregel, und drücken Sie dann "Strg + C", um sie zu kopieren.

2. Wählen Sie dann die Zellen aus, die Sie validieren möchten. Um mehrere nicht angrenzende Zellen auszuwählen, halten Sie die Taste "Strg" gedrückt, während Sie die Zellen auswählen.

3. Klicken Sie dann mit der rechten Maustaste auf die Auswahl und wählen Sie die Option "Inhalte einfügen", siehe Screenshot:

4. Wählen Sie im Dialogfeld "Inhalte einfügen" die Option "Validierung" aus, siehe Screenshot:

5. Klicken Sie auf die Schaltfläche „OK“, jetzt wurde die Validierungsregel in die neuen Zellen kopiert.

8. Wie verwendet man die Datenüberprüfung, um ungültige Einträge in Excel zu markieren?

Manchmal müssen Sie möglicherweise Datenvalidierungsregeln für bestehende Daten erstellen. In diesem Fall können einige ungültige Daten im Zellbereich auftauchen. Wie können Sie die ungültigen Daten überprüfen und ändern? In Excel können Sie die Funktion "Ungültige Daten markieren" verwenden, um ungültige Daten mit einem roten Kreis hervorzuheben.

Um die ungültigen Daten, die Sie benötigen, zu markieren, sollten Sie die Funktion "Datenüberprüfung" anwenden, um eine Regel für den Datenbereich festzulegen. Bitte folgen Sie den folgenden Schritten:

1. Wählen Sie den Datenbereich aus, in dem Sie ungültige Daten umkreisen möchten.

2. Klicken Sie anschließend auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im Dialogfeld "Datenüberprüfung" legen Sie die Überprüfungsregel nach Bedarf fest. Zum Beispiel werde ich hier die Werte über 500 validieren, siehe Screenshot:

3. Klicken Sie dann auf "OK", um das Dialogfeld zu schließen. Nachdem Sie die Datenüberprüfungsregel festgelegt haben, klicken Sie auf "Daten" > "Datenüberprüfung" > "Ungültige Daten markieren", dann werden alle ungültigen Werte, die kleiner als 500 sind, mit einem roten Oval markiert. Siehe Screenshots:

Hinweise:

• 1. Sobald Sie eine ungültige Daten korrigieren, wird der rote Kreis automatisch verschwinden.
• 2. Diese Funktion "Ungültige Daten einkreisen" kann maximal 255 Zellen umkreisen. Wenn Sie das aktuelle Arbeitsblatt speichern, werden alle roten Kreise entfernt.
• 3. Diese Kreise sind nicht druckbar.
• 4. Sie können die roten Kreise auch entfernen, indem Sie auf "Daten" > "Datenüberprüfung" > "Überprüfungs-Kreise löschen" klicken.

9. Wie entfernt man die Datenüberprüfung in Excel?

Um Datenüberprüfungsregeln aus einem Zellenbereich, dem aktuellen Arbeitsblatt oder der gesamten Arbeitsmappe zu entfernen, verwenden Sie die folgenden Methoden.

Datenüberprüfung im ausgewählten Bereich mit der Datenüberprüfungsfunktion entfernen

1. Wählen Sie die Zellen mit Datenüberprüfung aus, die Sie entfernen möchten.

2. Klicken Sie dann auf "Daten" > "Datenüberprüfung" > "Datenüberprüfung". Im erscheinenden Dialogfeld klicken Sie unter der Registerkarte "Optionen" auf die Schaltfläche "Alle löschen", siehe Screenshot:

3. Klicken Sie dann auf die Schaltfläche „OK“, um dieses Dialogfeld zu schließen. Die Datenvalidierungsregel, die auf den ausgewählten Bereich angewendet wurde, wird sofort entfernt.

Tipp: Um die Datenüberprüfung aus dem aktuellen Arbeitsblatt zu entfernen, wählen Sie bitte zuerst das gesamte Blatt aus und wenden Sie dann die obigen Schritte an.

 Entfernen Sie die Datenüberprüfung im ausgewählten Bereich mit einer praktischen Funktion

Wenn Sie "Kutools für Excel" haben, kann auch die Funktion "Datenüberprüfungsbeschränkungen löschen" helfen, die Datenvalidierungsregeln aus dem ausgewählten Bereich oder dem gesamten Arbeitsblatt zu entfernen.

1. Wählen Sie den Zellbereich oder das gesamte Arbeitsblatt aus, das die zu entfernende Datenüberprüfung enthält.

2. Klicken Sie dann auf "Kutools" > "Eingabe begrenzen" > "Datenüberprüfungsbeschränkungen löschen", siehe Screenshot:

3. Klicken Sie im erscheinenden Dialogfeld auf „OK“, und die Datenüberprüfungsregel wird wie gewünscht gelöscht.

 Datenüberprüfung aus allen Arbeitsblättern mit VBA-Code entfernen

Um die Datenüberprüfungsregeln aus der gesamten Arbeitsmappe zu entfernen, können die oben genannten Methoden zeitaufwendig sein, wenn es viele Arbeitsblätter gibt. Der folgende Code kann Ihnen helfen, diese Aufgabe schnell zu erledigen.

1. Halten Sie die Tasten "ALT + F11" gedrückt, um das Fenster "Microsoft Visual Basic for Applications" zu öffnen.

2. Klicken Sie dann auf „Einfügen“ > „Modul“ und fügen Sie das folgende Makro im „Modul“-Fenster ein.

VBA-Code: Datenüberprüfungsregeln in allen Arbeitsblättern entfernen:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Drücken Sie dann die Taste "F5", um diesen Code auszuführen, und alle Datenüberprüfungsregeln werden sofort aus der gesamten Arbeitsmappe gelöscht.