Excel-Datenvalidierung: Datenvalidierung in Excel hinzufügen, verwenden, kopieren und entfernen

In Excel ist die Datenvalidierung eine nützliche Funktion, mit der Sie einschränken können, was ein Benutzer in eine Zelle eingeben kann. Die Datenvalidierungsfunktion kann Ihnen beispielsweise dabei helfen, die Länge von Textzeichenfolgen oder Textanfang/-ende mit bestimmten Zeichen oder einzugebenden eindeutigen Werten usw. zu begrenzen.

In diesem Tutorial werden wir darüber sprechen, wie Sie die Datenvalidierung in Excel hinzufügen, verwenden und entfernen. Einige grundlegende und erweiterte Operationen dieser Funktion werden ebenfalls im Detail demonstriert.

---

1. Was ist die Datenvalidierung in Excel?

Das Datenvalidierung -Funktion kann Ihnen helfen, den Eingabeinhalt in Ihrem Arbeitsblatt einzuschränken. Normalerweise können Sie einige Validierungsregeln erstellen, um die Eingabe bestimmter Daten in eine Liste ausgewählter Zellen zu verhindern oder zuzulassen.

Einige grundlegende Verwendungen der Datenvalidierungsfunktion:

1. Beliebiger Wert: Es wird keine Validierung durchgeführt, Sie können alles in die angegebenen Zellen eingeben. 2. Gesamtwert: Es sind nur ganze Zahlen erlaubt. 3. Dezimalzahl: 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: nur Daten sind erlaubt. 6. Zeit: Es sind nur Zeiten erlaubt. 7. Textlänge: erlaubt nur die Eingabe einer bestimmten Länge des Textes. 8. Brauch: Erstellen Sie benutzerdefinierte Formelregeln zum Überprüfen der Benutzereingaben.

---

2. Wie füge ich eine Datenvalidierung in Excel hinzu?

Im Excel-Arbeitsblatt können Sie die Datenvalidierung mit den folgenden Schritten hinzufügen:

1. Wählen Sie eine Liste von Zellen aus, in denen Sie die Datenvalidierung festlegen möchten, und klicken Sie dann auf Datum > Datenvalidierung > Datenvalidierung, siehe Screenshot:

2. In dem Datenvalidierung Dialogfeld unter Einstellungen erstellen Sie bitte Ihre eigenen Validierungsregeln. In den Kriterienfeldern können Sie einen der folgenden Typen angeben:

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

Als Beispiel werde ich eine Regel erstellen, die nur die Eingabe von ganzen Zahlen zwischen 100 und 1000 zulässt, hier die Kriterien wie unten gezeigt einstellen:

3. Nachdem Sie die Bedingungen konfiguriert haben, können Sie zum Eingabemeldung or Fehlerwarnung Registerkarte, um die Eingabenachricht oder den Fehleralarm für die Validierungszellen nach Belieben festzulegen. (Wenn Sie die Benachrichtigung nicht einstellen möchten, klicken Sie bitte auf OK direkt beenden.)

3.1) Eingabenachricht hinzufügen (optional):

Sie können eine Nachricht erstellen, die angezeigt wird, wenn Sie eine Zelle auswählen, die eine Datenvalidierung enthält. Diese Nachricht hilft, den Benutzer daran zu erinnern, was er in die Zelle eingeben kann.

Gehen Sie zum Eingabemeldung Registerkarte und gehen Sie wie folgt vor:

Prüfen Sie die Eingabemeldung anzeigen, wenn Zelle ausgewählt ist Option; Geben Sie den gewünschten Titel und die gewünschte Erinnerungsnachricht in die entsprechenden Felder ein; Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK um dieses Dialogfeld zu schließen.

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

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

Zusätzlich zum Erstellen der Eingabenachricht können Sie auch Fehlermeldungen anzeigen, wenn ungültige Daten in eine Zelle mit Datenvalidierung eingegeben werden.

Gehen Sie zum Fehlerwarnung Registerkarte der Datenvalidierung Dialogfeld, gehen Sie bitte wie folgt vor:

Prüfen Sie die Fehlermeldung anzeigen, nachdem ungültige Daten eingegeben wurden Option; Im Design Dropdown-Liste, wählen Sie einen gewünschten Benachrichtigungstyp aus, den Sie benötigen: Stopp (Standard): Dieser Warnungstyp verhindert, dass Benutzer ungültige Daten eingeben. Warnung: Warnt Benutzer, dass die Daten ungültig sind, verhindert jedoch nicht die Eingabe. Informationen: Informiert Benutzer nur über eine ungültige Dateneingabe. Geben Sie den gewünschten Titel und die gewünschte Warnmeldung in die entsprechenden Felder ein; Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK um das Dialogfeld zu schließen.

Und jetzt, wenn Sie einen ungültigen Wert eingeben, wird das Meldungsfeld angezeigt, wie im folgenden Screenshot gezeigt:

Stoppen Option: Sie können klicken Wiederholen einen anderen Wert eingeben oder Abbrechen um den Eintrag zu entfernen.

Warnung Option: Klick Ja um den ungültigen Eintrag einzugeben, Nein um es zu ändern, oder Abbrechen um den Eintrag zu entfernen.

Informationen Option: Klick OK um den ungültigen Eintrag einzugeben oder Abbrechen um den Eintrag zu entfernen.

Note: Wenn Sie keine eigene benutzerdefinierte Nachricht im Fehlerwarnung Box, eine Standardeinstellung Stoppen Das Eingabeaufforderungsfeld für Warnungen wird wie unten gezeigt angezeigt:

---

3. Grundlegende Beispiele für die Datenvalidierung

Wenn Sie diese Datenvalidierungsfunktion verwenden, stehen Ihnen 8 integrierte Optionen zum Festlegen der Datenvalidierung zur Verfügung. Zum Beispiel: beliebiger Wert, ganze Zahlen und Dezimalzahlen, Datum und Uhrzeit, Liste, Textlänge und benutzerdefinierte Formel. In diesem Abschnitt werden wir besprechen, wie Sie einige der integrierten Optionen in Excel verwenden.

3.1 Datenvalidierung 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 Datum > Datenvalidierung > Datenvalidierung.

2. In dem Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Wählen Sie den entsprechenden Artikel aus Ganze Zahl or Dezimal der Erlauben Dropdown-Feld.
• Wählen Sie dann eines der Kriterien aus, die Sie im Datum box (In diesem Beispiel wähle ich zwischen Möglichkeit).
• Tips: Die Kriterien enthalten: zwischen, nicht zwischen, gleich, ungleich, größer, kleiner, größer oder gleich, kleiner oder gleich.
• Als nächstes geben Sie die Mindestens und Maximal Werte, die Sie benötigen (ich möchte die Zahlen zwischen 0 und 1 00).
• Endlich, klick OK .

3. In Ihren ausgewählten Zellen dürfen jetzt nur die ganzen Zahlen von 0 bis 100 eingegeben werden.

---

3.2 Datenvalidierung für Datum und Uhrzeit

Um ein einzugebendes Datum oder eine bestimmte Uhrzeit zu bestätigen, ist es einfach, dies zu verwenden DatenvalidierungBitte gehen Sie wie folgt vor:

1. Wählen Sie eine Liste von Zellen aus, in denen Sie nur bestimmte Daten oder Uhrzeiten zulassen möchten, und klicken Sie dann auf Datum > Datenvalidierung > Datenvalidierung.

2. In dem Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Wählen Sie den entsprechenden Artikel aus Datum or Uhrzeit der Erlauben Dropdown-Feld.
• Wählen Sie dann eines der Kriterien aus, die Sie im Datum Box (Hier wähle ich größer als Möglichkeit).
• Tips: Die Kriterien enthalten: zwischen, nicht zwischen, gleich, ungleich, größer, kleiner, größer oder gleich, kleiner oder gleich.
• Als nächstes geben Sie die Startdatum Sie benötigen (ich möchte die Daten größer als 8).
• Endlich, klick OK .

3. Jetzt dürfen nur noch Daten größer als 8 in Ihre ausgewählten Zellen eingegeben werden.

---

3.3 Datenvalidierung für Textlänge

Wenn Sie die Anzahl der Zeichen begrenzen müssen, die in eine Zelle eingegeben werden können. Um beispielsweise den Inhalt für einen bestimmten Bereich auf nicht mehr als 10 Zeichen zu beschränken, ist dies Datenvalidierung kann dir auch einen Gefallen tun.

1. Wählen Sie eine Liste von Zellen aus, in denen Sie die Textlänge begrenzen möchten, und klicken Sie dann auf Datum > Datenvalidierung > Datenvalidierung.

2. In dem Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Textlänge von dem Erlauben Dropdown-Feld.
• Wählen Sie dann eines der Kriterien aus, die Sie im Datum box (In diesem Beispiel wähle ich weniger als Möglichkeit).
• Tips: Die Kriterien enthalten: zwischen, nicht zwischen, gleich, ungleich, größer, kleiner, größer oder gleich, kleiner oder gleich.
• Als nächstes geben Sie die Maximal Anzahl, die Sie begrenzen müssen (ich möchte, dass die Textlänge nicht mehr als 10 Zeichen beträgt).
• Endlich, klick OK .

3. Jetzt erlauben die ausgewählten Zellen nur die Textzeichenfolge mit weniger als 10 Zeichen einzugeben.

---

3.4 Datenvalidierungsliste (Dropdown-Liste)

Mit diesem kraftvollen Datenvalidierung Funktion können Sie auch schnell und einfach Dropdown-Listen in Zellen erstellen. Bitte gehen Sie wie folgt vor:

1. Wählen Sie die Zielzellen aus, in die die Dropdown-Liste eingefügt werden soll, und klicken Sie dann auf Datum > Datenvalidierung > Datenvalidierung.

2. In dem Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Liste von dem Erlauben Dropdown-Liste.
• Im Quelle Geben Sie im Textfeld 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 enthält, auf denen die Dropdown-Liste eingefügt werden soll.
• Endlich, klick OK .

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

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

---

4. Erweiterte benutzerdefinierte Regeln für die Datenvalidierung

In diesem Abschnitt werde ich vorstellen, wie Sie einige erweiterte benutzerdefinierte Datenvalidierungsregeln erstellen, um Ihre Art von Problemen zu lösen, wie zum Beispiel: Validierungsformeln erstellen, um nur Zahlen oder Textzeichenfolgen, nur eindeutige Werte, nur bestimmte Telefonnummern, E-Mail-Adressen usw .

4.1 Datenvalidierung nur Zahlen oder Texte zulassen

 Erlauben Sie nur die Eingabe von Zahlen mit der Datenvalidierungsfunktion

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

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

2. Klicken Sie Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann diese Formel ein: =ISNUMMER(A2) in die Formel Textfeld. ((A2 ist die erste Zelle des ausgewählten Bereichs, den Sie einschränken möchten)
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

Note: Diese ISNUMBER -Funktion lässt beliebige numerische Werte in validierten Zellen zu, einschließlich Ganzzahlen, Dezimalzahlen, Brüche, Datums- und Uhrzeitangaben.

---

 Erlauben Sie nur die Eingabe von Textzeichenfolgen mit der Datenvalidierungsfunktion

Um Zelleneinträge nur auf Text zu beschränken, können Sie die Datenvalidierung Funktion mit einer benutzerdefinierten Formel basierend auf der ISTEXT Funktion gehen Sie bitte wie folgt vor:

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

2. Klicken Sie Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann diese Formel ein: =ISTEXT(A2) in die Formel Textfeld. ((A2 ist die erste Zelle des ausgewählten Bereichs, den Sie einschränken möchten)
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

3. Bei der Eingabe von Daten in die spezifischen Zellen können jetzt nur Textformatdaten zugelassen werden.

---

4.2 Datenvalidierung erlaubt nur alphanumerische Werte

Für manche Zwecke möchten Sie nur die Eingabe von Buchstaben und Zahlenwerten zulassen, aber die Sonderzeichen wie ~,%,$, Leerzeichen usw. einschränken. In diesem Abschnitt werden einige Tricks für Sie vorgestellt.

 Nur alphanumerische Werte mit Datenvalidierungsfunktion zulassen

Um die Sonderzeichen zu verhindern, aber nur alphanumerische Werte zuzulassen, sollten Sie eine benutzerdefinierte Formel in die Datenvalidierung Funktion, bitte wie folgt vorgehen:

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

2. Klicken Sie Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

Note: In den obigen Formeln, A2 ist die erste Zelle des ausgewählten Bereichs, die Sie begrenzen möchten.

3. Jetzt dürfen nur noch die Buchstaben und Zahlenwerte eingegeben werden, und die Sonderzeichen werden bei der Eingabe eingeschränkt, wie im folgenden Screenshot gezeigt:

---

 Erlaube nur alphanumerische Werte mit einer erstaunlichen Funktion

Vielleicht ist die obige Formel für uns kompliziert zu verstehen und zu erinnern, hier werde ich eine praktische Funktion vorstellen – Tippfehler verhindern of Kutools for Excel, mit dieser Funktion können Sie diese Aufgabe schnell und einfach lösen.

Nach der Installation Kutools for ExcelBitte machen Sie Folgendes:

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

2. Dann klick Kutoolen > Tippfehler verhindern > Tippfehler verhindern, siehe Screenshot:

3. In der herausgesprungen Tippfehler verhindern Dialogfeld auswählen Verhindern Sie die Eingabe von Sonderzeichen Option, siehe Screenshot:

4. Dann klick Ok Klicken Sie in den folgenden Eingabeaufforderungsfeldern auf Ja > OK um die Operation zu beenden. In den ausgewählten Zellen sind jetzt nur noch die Alphabete und Zahlenwerte erlaubt, siehe Screenshot:

---

4.3 Datenvalidierung erlauben, dass Texte mit bestimmten Zeichen beginnen oder enden

Wenn alle Werte in einem bestimmten Bereich mit einem bestimmten Zeichen oder einer Teilzeichenfolge beginnen oder enden sollen, können Sie die Datenvalidierung mit einer benutzerdefinierten Formel basierend auf der Funktion EXACT, LEFT, RIGHT oder COUNTIF verwenden.

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

Zum Beispiel möchte ich, dass die Texte beim Eingeben der Textzeichenfolgen in bestimmte Zellen mit "CN" beginnen oder enden müssen, gehen Sie bitte wie folgt vor:

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• Begin with: =EXACT(LEFT(A2,2),"CN")
  End with: =EXACT(RIGHT(A2,2),"CN")
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

Note: In den obigen Formeln, A2 ist die erste Zelle des ausgewählten Bereichs, die Zahl 2 ist die Anzahl der von Ihnen angegebenen Zeichen. CN ist der Text, mit dem Sie beginnen oder enden möchten.

3. Von nun an kann nur noch der Textstring, der mit den angegebenen Zeichen beginnt oder endet, in die ausgewählten Zellen eingegeben werden. Andernfalls wird eine Warnmeldung ausgegeben, um Sie wie unten gezeigt daran zu erinnern:

Note: Der Stern * ist ein Platzhalter, der einem oder mehreren Zeichen entspricht.

---

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

Wenn Sie beispielsweise möchten, dass die Texte wie unten gezeigt mit "CN" oder "UK" beginnen oder enden müssen, müssen Sie eine weitere Instanz von EXACT mit einem Pluszeichen (+) hinzufügen. Bitte führen Sie die folgenden Schritte aus:

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
  End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

3. In die markierten Zellen kann nun nur noch die Textzeichenfolge eingegeben werden, die mit den angegebenen Zeichen beginnt oder endet.

Note: Der Stern * ist ein Platzhalter, der einem oder mehreren Zeichen entspricht.

---

4.4 Datenvalidierung erlauben Eingaben müssen / dürfen keinen bestimmten Text enthalten

In diesem Abschnitt werde ich darüber sprechen, wie Sie die Datenvalidierung anwenden, um zuzulassen, dass Werte eine bestimmte Teilzeichenfolge oder eine von vielen Teilzeichenfolgen in Excel enthalten oder nicht enthalten dürfen.

 Einträge zulassen müssen einen oder einen von vielen bestimmten Texten enthalten

Einträge zulassen müssen einen bestimmten Text enthalten

Um beispielsweise Einträge zuzulassen, die eine bestimmte Textzeichenfolge enthalten, sollten alle eingegebenen Werte den Text „KTE“ enthalten, wie unten im Screenshot gezeigt. Sie können die Datenvalidierung mit einer benutzerdefinierten Formel basierend auf den Funktionen FIND und ISNUMBER anwenden. Bitte gehen Sie wie folgt vor:

1. Wählen Sie einen Zellbereich aus, der nur Texte zulässt, die bestimmten Text enthalten.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert aus der Dropdown-Liste Zulassen.
• Geben Sie dann eine der folgenden Formeln in das Formel Textfeld ein.
• =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
  =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

3. Wenn der eingegebene Wert jetzt nicht den entworfenen Text enthält, wird eine Warnmeldung angezeigt.

---

Einträge zulassen müssen einen von vielen spezifischen Texten enthalten

Die obige Formel funktioniert nur für eine Textzeichenfolge. Wenn Sie wie im folgenden Screenshot gezeigt eine von vielen Textzeichenfolgen in den Zellen verwenden möchten, sollten Sie die Funktionen SUMMENPRODUKT, FINDEN und ISTNUMMER 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. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann nach Bedarf eine der folgenden Formeln in das Formel Textfeld ein.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
• Und dann klicken OK um das Dialogfeld zu schließen.

Note: In den obigen Formeln, A2 ist die erste Zelle des ausgewählten Bereichs, C2: C4 ist die Liste der Werte, die Sie zulassen möchten, dass Einträge einen davon enthalten.

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

---

 Einträge zulassen dürfen keinen oder einen von vielen bestimmten Texten enthalten

Einträge zulassen dürfen keinen bestimmten Text enthalten

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

1. Wählen Sie einen Zellbereich aus, der nur Texte zulässt, die keinen bestimmten Text enthalten.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann eine der folgenden Formeln in das Formel Textfeld ein.
• =ISERROR(FIND("KTE",A2))                  (Case sensitive)
  =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

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

---

Einträge zulassen dürfen keinen von vielen bestimmten Texten enthalten

Um zu verhindern, dass eine von vielen Textzeichenfolgen in einer Liste eingegeben wird, wie im folgenden Screenshot gezeigt, sollten Sie wie folgt vorgehen:

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie die folgenden Schritte aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
• Und dann klicken OK um das Dialogfeld zu schließen.

Note: In den obigen Formeln, A2 ist die erste Zelle des ausgewählten Bereichs, C2: C4 ist die Liste der Werte, die Sie verhindern möchten, wenn Einträge einen dieser Werte enthalten.

3. Von nun an wird die Eingabe von Einträgen, die einen der spezifischen Texte enthalten, verhindert.

---

4.5 Datenvalidierung erlaubt nur eindeutige Werte

Wenn Sie verhindern möchten, dass doppelte Daten in einen Zellbereich eingegeben werden, werden in diesem Abschnitt einige schnelle Methoden zum Lösen dieser Aufgabe in Excel vorgestellt.

 Nur eindeutige Werte mit Datenvalidierungsfunktion zulassen

Normalerweise kann Ihnen die Datenvalidierungsfunktion mit einer benutzerdefinierten Formel basierend auf der ZÄHLENWENN-Funktion helfen. Bitte führen Sie die folgenden Schritte aus:

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• =COUNTIF($A$2:$A$9,A2)=1
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

Note: In der obigen Formel A2: A9 ist der Zellenbereich, für den Sie nur eindeutige Werte zulassen möchten, und A2 ist die erste Zelle des ausgewählten Bereichs.

3. Jetzt können nur noch eindeutige Werte eingegeben werden und eine Warnmeldung wird angezeigt, wenn doppelte Daten eingegeben werden, siehe Screenshot:

---

 Nur eindeutige Werte mit VBA-Code zulassen

Der folgende VBA-Code kann Ihnen auch helfen, die Eingabe doppelter Werte zu verhindern, gehen Sie bitte wie folgt vor:

1. Klicken Sie mit der rechten Maustaste auf die Blattregisterkarte, für die Sie nur eindeutige Werte zulassen möchten, und wählen Sie Code anzeigen aus dem Kontextmenü in der herausgesprungenen Microsoft Visual Basic für Applikationen Bitte kopieren Sie den folgenden Code und fügen Sie 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

Note: Im obigen Code ist die A1: A100 und A: A sind die Zellen in der Spalte, deren Duplikate verhindert werden sollen, ändern Sie sie bitte nach Bedarf.

2. Speichern und schließen Sie dann diesen Code. Wenn Sie nun einen doppelten Wert in die Zelle A1: A100 eingeben, wird eine Warnmeldung angezeigt, wie im folgenden Screenshot gezeigt:

---

 Erlaube nur eindeutige Werte mit einer praktischen Funktion

Wenn Sie Kutools for ExcelMit seinen Duplizieren verhindern -Funktion können Sie die Datenvalidierung so einstellen, dass Duplikate für einen Zellbereich mit nur wenigen Klicks verhindert werden.

Nach der Installation Kutools for ExcelBitte machen Sie Folgendes:

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

2. Dann klick Kutoolen > Tippfehler verhindern > Duplizieren verhindern, siehe Screenshot:

3. Eine Warnmeldung wird angezeigt, um Sie daran zu erinnern, dass die Datenvalidierung entfernt wird, wenn diese Funktion angewendet wird. Klicken Sie auf Ja und klicken Sie in der folgenden Eingabeaufforderung auf OK, siehe Screenshots:

4. Wenn Sie nun einige doppelte Daten in Ihre angegebenen Zellen eingeben, wird eine Eingabeaufforderung angezeigt, um Sie daran zu erinnern, dass die doppelten Daten nicht gültig sind, siehe Screenshot:

---

4.6 Datenvalidierung erlaubt nur Groß-/Kleinschreibung / richtige Groß-/Kleinschreibung

Diese Datenvalidierung ist eine leistungsstarke Funktion. Sie kann auch dazu beitragen, dass ein Benutzer nur Groß-, Klein- oder Großbuchstaben in einem Zellbereich eingeben kann. Bitte führen Sie die folgenden Schritte aus:

1. Wählen Sie den Zellbereich aus, in den nur Groß-, Klein- oder Großbuchstaben eingegeben werden sollen.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann eine der folgenden Formeln ein, die Sie benötigen, in das Formel Textfeld ein.
• =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

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

---

4.7 Datenvalidierung erlaubt Werte, die in einer anderen Liste vorhanden / nicht vorhanden sind

Zuzulassen, dass die Werte in einer anderen Liste existieren oder nicht existieren, um in einen Bereich von Zellen eingegeben zu werden, kann für die meisten von uns ein schmerzhaftes Problem sein. Tatsächlich können Sie die Datenvalidierungsfunktion mit einer einfachen Formel verwenden, die auf der Funktion ZÄHLENWENN basiert, um damit umzugehen.

Zum Beispiel möchte ich, dass nur die Werte im Bereich C2: C4 in einen Zellbereich 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 Datenvalidierung anwenden möchten.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann eine der folgenden Formeln ein, die Sie benötigen, in das Formel Textfeld ein.
• =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
  =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

Note: In der obigen Formel A2 ist die erste Zelle der Spalte, die Sie verwenden möchten, C2: C4 ist die Liste der Werte, die Sie verhindern oder zulassen möchten, wenn Einträge darunter sind.

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

---

4.8 Datenvalidierung erzwingt nur die Eingabe des Telefonnummernformats

Wenn Sie die Informationen Ihrer Firmenmitarbeiter eingeben, muss eine Spalte die Telefonnummer eingeben, um sicherzustellen, dass Telefonnummern schnell und genau eingegeben werden. In diesem Fall können Sie die Datenvalidierung für die Telefonnummern festlegen. Ich möchte zum Beispiel nur, dass die Telefonnummer in diesem Format (123) 456-7890 in ein Arbeitsblatt eingegeben werden darf. In diesem Abschnitt werden zwei schnelle Tricks zur Lösung dieser Aufgabe vorgestellt.

 Nur Telefonnummernformat mit Datenvalidierungsfunktion erzwingen

Um nur die Eingabe eines bestimmten Telefonnummernformats zuzulassen, gehen Sie wie folgt vor:

1. Wählen Sie die Liste der Zellen aus, in die ein bestimmtes Telefonnummernformat eingegeben werden soll, und klicken Sie dann mit der rechten Maustaste, wählen Sie Format Cells Siehe im Kontextmenü Screenshot:

2. In dem Format Cells Dialogfeld unter Nummer Registerkarte, wählen Sie Maßgeschneidert in der linken Kategorie Listenfeld, und geben Sie dann das gewünschte Telefonnummernformat in das Textfeld Typ ein, zum Beispiel werde ich dies verwenden (###) ### - #### Format, siehe Screenshot:

3. Dann klick OK um das Dialogfeld zu schließen.

4. Nachdem Sie die Zellen formatiert haben, wählen Sie die Zellen weiter aus und öffnen Sie dann das Datenvalidierung Dialogbox per Klick Datum > Datenvalidierung > Datenvalidierung, im Popup-Dialog, unter dem Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann diese Formel ein = UND (ISNUMMER (A2), LEN (A2) = 10) in das Textfeld Formel ein.
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

Note: In der obigen Formel A2 ist die erste Zelle der Spalte, in der Sie die Telefonnummer überprüfen möchten.

5. Wenn Sie nun eine 10-stellige Nummer eingeben, wird diese bei Bedarf automatisch in das spezifische Telefonnummernformat umgewandelt, siehe Screenshots:

Note: Wenn die eingegebene Zahl keine 10-stelligen ist, wird ein Warnmeldungsfenster angezeigt, um Sie daran zu erinnern, siehe Screenshot:

---

 Erzwinge nur das Telefonnummernformat mit einer nützlichen Funktion

Kutools for Excel Telefonnummer bestätigen Diese Funktion kann Ihnen auch dabei helfen, mit nur wenigen Klicks nur die Eingabe des Telefonnummernformats zu erzwingen.

Nach der Installation Kutools for ExcelBitte machen Sie Folgendes:

1. Wählen Sie die Liste der Zellen aus, die nur eine bestimmte Telefonnummer zulassen, und klicken Sie dann auf Kutoolen > Tippfehler verhindern > Telefonnummer bestätigen, siehe Screenshot:

2. In dem Telefonnummer Wählen Sie im Dialogfeld das gewünschte Telefonnummernformat aus oder erstellen Sie Ihre eigene Formatierung, indem Sie auf . klicken Speichern Schaltfläche, siehe Screenshot:

3. Klicken Sie nach Auswahl oder Einstellung der Telefonnummernformatierung auf OK, jetzt kann nur die Telefonnummer mit der spezifischen Formatierung eingegeben werden, ansonsten erscheint eine Warnmeldung, um Sie daran zu erinnern, siehe Screenshot:

---

4.9 Datenvalidierung nur erzwingen Einzugebende E-Mail-Adressen

Angenommen, Sie müssen mehrere E-Mail-Adressen in eine Spalte eines Arbeitsblatts eingeben, um zu verhindern, dass einige falsche E-Mail-Adressenformatierungen eingegeben werden. Normalerweise können Sie eine Datenvalidierungsregel festlegen, die nur die Formatierung von E-Mail-Adressen zulässt.

 Nur Format für E-Mail-Adressen mit Datenvalidierungsfunktion erzwingen

Durch die Verwendung der Datenvalidierungsfunktion mit einer benutzerdefinierten Formel können Sie eine Regel erstellen, um zu verhindern, dass ungültige E-Mail-Adressen schnell eingegeben werden. Gehen Sie wie folgt vor:

1. Wählen Sie die Zellen aus, in die nur E-Mail-Adressen eingegeben werden sollen, und klicken Sie dann auf Datum > Datenvalidierung > Datenvalidierung.

2. In der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann diese Formel ein = ISNUMBER (MATCH ("*@*.?*", A2,0)) in die Formel Textfeld ein.
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

3. Wenn der eingegebene Text nun kein E-Mail-Adressformat hat, wird ein Warnmeldungsfeld angezeigt, um Sie daran zu erinnern, siehe Screenshot:

---

 Erzwinge nur das Format von E-Mail-Adressen mit einer praktischen Funktion

Kutools for Excel unterstützt eine erstaunliche Funktion – E-mail Adresse bestätigen, mit diesem Dienstprogramm können Sie die ungültigen E-Mail-Adressen mit nur einem Klick verhindern.

Nach der Installation Kutools for ExcelBitte gehen Sie wie folgt vor:

1. Wählen Sie die Zellen aus, in denen nur E-Mail-Adressen eingegeben werden dürfen, und klicken Sie dann auf Kutoolen > Tippfehler verhindern > E-mail Adresse bestätigen. Siehe Screenshot:

2. Und dann darf nur die E-Mail-Adressformatierung eingegeben werden, ansonsten erscheint eine Warnmeldungsbox, um Sie daran zu erinnern, siehe Screenshot:

---

4.10 Datenvalidierung erzwingt nur die Eingabe von IP-Adressen

In diesem Abschnitt werde ich einige schnelle Tricks vorstellen, um die Datenvalidierung so einzustellen, dass nur IP-Adressen in einem Bereich von Zellen akzeptiert werden.

 Erzwingen Sie nur das IP-Adressenformat mit der Datenvalidierungsfunktion

Lassen Sie nur die Eingabe von IP-Adressen in einen bestimmten Zellbereich zu. Gehen Sie wie folgt vor:

1. Wählen Sie die Zellen aus, in die nur die IP-Adresse eingegeben werden soll, und klicken Sie dann auf Datum > Datenvalidierung > Datenvalidierung.

2. In der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

3. Wenn Sie nun eine ungültige IP-Adresse in die Zelle eingeben, wird ein Warnmeldungsfeld angezeigt, um Sie daran zu erinnern, wie im folgenden Screenshot gezeigt:

---

 Nur IP-Adressenformat mit VBA-Code erzwingen

Hier kann auch der folgende VBA-Code helfen, nur die Eingabe von IP-Adressen zuzulassen und andere Eingaben einzuschränken, gehen Sie bitte wie folgt vor:

1. Klicken Sie mit der rechten Maustaste auf die Registerkarte Blatt und klicken Sie auf Code anzeigen aus dem Kontextmenü, in der Eröffnung Microsoft Visual Basic für Applikationen Fenster, 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

Note: Im obigen Code, A2: A10 ist der Zellenbereich, in dem Sie nur IP-Adressen akzeptieren möchten.

2. Dann speichern und schließen Sie diesen Code, jetzt können nur noch die richtigen IP-Adressen in die spezifischen Zellen eingegeben werden.

---

 Erzwingen Sie nur das IP-Adressenformat mit einer einfachen Funktion

Wenn Sie Kutools for Excel in Ihrer Arbeitsmappe installiert ist, Überprüfen Sie die IP-Adresse Diese Funktion kann Ihnen auch bei der Lösung dieser Aufgabe helfen.

Nach der Installation Kutools for ExcelBitte machen Sie Folgendes:

1. Wählen Sie die Zellen aus, in denen nur IP-Adressen eingegeben werden dürfen, und klicken Sie dann auf Kutoolen > Tippfehler verhindern > Überprüfen Sie die IP-Adresse. Siehe Screenshot:

2. Nach dem Anwenden dieser Funktion kann jetzt nur noch die IP-Adresse eingegeben werden, andernfalls wird eine Warnmeldung angezeigt, um Sie daran zu erinnern, siehe Screenshot:

---

4.11 Datenvalidierung schränkt Werte ein, die den Gesamtwert überschreiten

Angenommen, Sie haben eine monatliche Spesenabrechnung und das Gesamtbudget beträgt 18000 US-Dollar. Nun muss der Gesamtbetrag in der Ausgabenliste den voreingestellten Gesamtbetrag von 18000 US-Dollar nicht überschreiten, wie unten gezeigt. In diesem Fall können Sie mithilfe der SUM-Funktion eine Datenvalidierungsregel erstellen, um zu verhindern, dass die Summe der Werte eine voreingestellte Summe überschreitet.

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• =SUM($B$2:$B$7)<=18000
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

Note: In der obigen Formel B2: B7 ist der Zellenbereich, für den Sie Einträge einschränken möchten.

3. Wenn nun die Werte im Bereich B2:B7 eingegeben werden und die Summe der Werte weniger als 18000 US-Dollar beträgt, ist die Validierung erfolgreich. Wenn ein Wert den Gesamtwert von $18000 überschreitet, wird ein Warnmeldungsfenster eingeblendet, um Sie daran zu erinnern.

---

4.12 Datenvalidierung beschränkt den Zelleneintrag basierend auf einer anderen Zelle

Wenn Sie Dateneinträge in einer Liste von Zellen basierend auf dem Wert in einer anderen Zelle begrenzen möchten, kann die Datenvalidierungsfunktion auch bei dieser Aufgabe helfen. Wenn die Zelle C1 beispielsweise der Text "Ja" ist, darf im Bereich A2: A9 alles eingegeben werden, aber wenn die Zelle C1 ein anderer Text ist, darf im Bereich A2: A9 nichts eingegeben werden, wie unten in den Screenshots gezeigt :

Um diese Lösung zu lösen, gehen Sie bitte wie folgt vor:

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• =$C$1="Yes"
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

Note: In der obigen Formel C1 enthält die Zelle den spezifischen Text, den Sie verwenden möchten, und der Text „Ja” ist der Text, auf den Sie die Zellen beschränken möchten. Bitte ändern Sie ihn nach Ihren Wünschen.

3. Wenn Zelle C1 den Text "Ja" enthält, kann alles in den Bereich A2: A9 eingegeben werden. Wenn Zelle C1 einen anderen Text enthält, können Sie keinen Wert eingeben, siehe folgende Demo:

---

4.13 Datenvalidierung erlaubt nur die Eingabe von Wochentagen oder Wochenenden

Wenn Sie nur Wochentage (von Montag bis Freitag) oder Wochenenden (Samstag und Sonntag) in eine Zellenliste eingeben müssen, Datenvalidierung kann Ihnen auch helfen, gehen Sie bitte mit den folgenden Schritten vor:

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann eine der folgenden Formeln in das Formel Textfeld nach Bedarf.
• =WEEKDAY(A2,2)<6                      (allow only weekdays)
  =WEEKDAY(A2,2)>5                      (allow only weekends)
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

3. Jetzt können Sie das Datum des Wochentags oder des Wochenendes nur nach Bedarf in die spezifischen Zellen eingeben.

---

4.14 Datenvalidierung erlaubt eingegebenes Datum basierend auf dem heutigen Datum

Manchmal müssen Sie möglicherweise nur die Datumsangaben größer oder kleiner als heute in eine Zellenliste eingeben. Die Datenvalidierung Funktion mit dem HEUTE bestellen Funktion kann dir einen Gefallen tun. Bitte gehen Sie wie folgt vor:

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• =A2>Today()
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

3. In die Zellen können jetzt nur die Daten eingegeben werden, die größer als das heutige Datum sind, andernfalls wird eine Warnmeldung angezeigt, um Sie daran zu erinnern, siehe Screenshot:

---

4.15 Datenvalidierung erlaubt eingegebene Zeit basierend auf der aktuellen Zeit

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

1. Wählen Sie die Zellenliste aus, in die nur die Zeiten vor oder nach der aktuellen Zeit eingegeben werden sollen.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Uhrzeit von dem Erlauben Dropdown-Liste.
• Dann wähle weniger als um nur Zeiten vor der aktuellen Uhrzeit zuzulassen, oder größer als um Zeiten nach der aktuellen Uhrzeit zuzulassen, wie Sie es von der Datum Dropdown-Liste.
• Und dann in der Endzeit or Startzeit Geben Sie in das Feld die folgende Formel ein:
• =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

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

---

4.16 Datenvalidierung das Datum eines bestimmten oder aktuellen Jahres

Damit nur Daten in einem bestimmten Jahr oder im laufenden Jahr eingegeben werden können, können Sie die Datenvalidierung mit einer benutzerdefinierten Formel basierend auf der Funktion JAHR verwenden.

1. Wählen Sie die Zellenliste aus, in die nur die Daten eines bestimmten Jahres eingegeben werden sollen.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Maßgeschneidert von dem Erlauben Dropdown-Liste.
• Geben Sie dann die folgende Formel in das Formel Textfeld ein.
• =YEAR(A2)=2020
• Klicken Sie auf Kostenlos erhalten und dann auf Installieren. OK Schaltfläche, um diesen Dialog zu schließen.

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

3. Und dann können nur die Daten im Jahr 2020 eingegeben werden, wenn nicht, wird eine Warnmeldung angezeigt, wie im folgenden Screenshot gezeigt:

---

4.17 Datenvalidierung das Datum in der aktuellen Woche oder im Monat

Wenn Sie dem Benutzer ermöglichen möchten, die Daten der aktuellen Woche oder des aktuellen Monats in bestimmte Zellen einzugeben, werden in diesem Abschnitt einige Formeln für diese Aufgabe in Excel vorgestellt.

 Geben Sie das Datum der aktuellen Woche ein

1. Wählen Sie die Zellenliste aus, in die nur die Daten der aktuellen Woche eingegeben werden sollen.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Datum von dem Erlauben Dropdown-Liste.
• Und dann wählen Sie zwischen von dem Datum Dropdown-Liste.
• Im Startdatum Textfeld, geben Sie diese Formel ein: =HEUTE()-WOCHENTAG(HEUTE(),3)
• Im Enddatum Textfeld, geben Sie diese Formel ein: =HEUTE()-WOCHENTAG(HEUTE(),3)+6
• Endlich, klick OK .

3. Dann können nur die Daten innerhalb der aktuellen Woche eingegeben werden, andere Daten werden wie unten gezeigt verhindert:

---

 Erlaube, das Datum des aktuellen Monats einzugeben

Damit nur die Daten des aktuellen Monats eingegeben werden können, gehen Sie bitte wie folgt vor:

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

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, in der herausgesprungen Datenvalidierung Dialogfeld unter Einstellungen Führen Sie auf der Registerkarte die folgenden Vorgänge aus:

• Auswählen Datum von dem Erlauben Dropdown-Liste.
• Und dann wählen Sie zwischen den Datum Dropdown-Liste.
• Im Startdatum Textfeld, geben Sie diese Formel ein: =DATUM(JAHR(HEUTE()),MONAT(HEUTE()),1)
• Im Enddatum Textfeld, geben Sie diese Formel ein: =DATUM(JAHR(HEUTE()),MONAT(HEUTE()),TAG(DATUM(JAHR(HEUTE()),MONAT(HEUTE())+1,1)-1))
• Endlich, klick OK .

3. Von nun an können nur noch die Daten des aktuellen Monats in die ausgewählten Zellen eingegeben werden.

---

5. Wie bearbeite ich die Datenvalidierung in Excel?

Um die bestehende Regel der Datenvalidierung zu bearbeiten oder zu ändern, führen Sie bitte die folgenden Schritte aus:

1. Wählen Sie eine der Zellen mit der Datenvalidierungsregel aus.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung auf dem Sprung Datenvalidierung Bearbeiten oder ändern Sie die Regeln nach Bedarf im Dialogfeld, und aktivieren Sie dann Wenden Sie diese Änderungen auf alle anderen Zellen mit den gleichen Einstellungen an Option, diese neue Regel auf alle anderen Zellen mit den ursprünglichen Validierungskriterien anzuwenden. Siehe Screenshot:

3. Klicken Sie OK um die Änderungen zu speichern.

---

6. Wie finde und wähle ich Zellen mit Datenvalidierung in Excel aus?

Wenn Sie in Ihrem Arbeitsblatt mehrere Datenvalidierungsregeln erstellt haben, müssen Sie jetzt die Zellen suchen und auswählen, die die Datenvalidierungsregeln angewendet haben Gehe zu Spezial -Befehl kann Ihnen helfen, alle Arten von Datenvalidierungen oder bestimmte Arten von Datenvalidierungen auszuwählen.

1. Aktivieren Sie das Arbeitsblatt, das Sie suchen möchten, und wählen Sie die Zellen mit Datenvalidierung aus.

2. Dann klick Home > Suchen & Auswählen > Gehe zu Spezial, siehe Screenshot:

3. In dem Gehe zu Spezial Dialogfeld auswählen Datenvalidierung > Alle, siehe Screenshot:

4. Und alle Zellen mit Datenvalidierung wurden gleichzeitig im aktuellen Arbeitsblatt ausgewählt.

---

7. Wie kopiert man die Datenvalidierungsregel in andere Zellen?

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

1. Klicken Sie, um eine Zelle mit der zu verwendenden Validierungsregel auszuwählen, und drücken Sie dann Ctrl + C um es zu kopieren.

2. Wählen Sie dann die Zellen aus, die Sie validieren möchten. Um mehrere nicht benachbarte Zellen auszuwählen, halten Sie die . gedrückt Ctrl Taste beim Auswählen der Zellen.

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

4. In dem Einfügen Dialogfeld auswählen Validierung Option, siehe Screenshot:

5. Klicken Sie OK Schaltfläche, jetzt wird die Validierungsregel in die neuen Zellen kopiert.

---

8. Wie verwende ich die Datenvalidierung, um ungültige Einträge in Excel einzukreisen?

Manchmal müssen Sie möglicherweise Datenvalidierungsregeln für vorhandene Daten erstellen. In diesem Fall können einige ungültige Daten im Zellbereich erscheinen. Wie überprüfe ich die ungültigen Daten und ändere sie? In Excel können Sie die Ungültige Daten einkreisen um die ungültigen Daten mit einem roten Kreis hervorzuheben.

Um die benötigten ungültigen Daten einzukreisen, sollten Sie die Datenvalidierung -Funktion, um eine Regel für den Datenbereich festzulegen. Bitte führen Sie die folgenden Schritte aus:

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

2. Dann klick Datum > Datenvalidierung > DatenvalidierungIn der Datenvalidierung Dialogfeld, stellen Sie die Validierungsregel auf Ihre Bedürfnisse ein, zum Beispiel hier werde ich die Werte größer als 500 validieren, siehe Screenshot:

3. Dann klick OK um das Dialogfeld zu schließen. Klicken Sie nach dem Festlegen der Datenvalidierungsregel auf Datum > Datenvalidierung > Ungültige Daten einkreisen, dann wurden alle ungültigen Werte, die kleiner als 500 sind, mit einem roten Oval eingekreist. Siehe Screenshots:

Anmerkungen:

• 1. Sobald Sie ungültige Daten korrigieren, verschwindet der rote Kreis automatisch.
• 2. Diese Ungültige Daten einkreisen Feature kann höchstens 255 Zellen umkreisen. Wenn Sie die aktuelle Arbeitsmappe speichern, werden alle roten Kreise entfernt.
• 3. Diese Kreise sind nicht druckbar.
• 4. Sie können die roten Kreise auch durch Klicken entfernen Datum > Datenvalidierung > Validierungskreise löschen.

---

9. Wie entferne ich die Datenvalidierung in Excel?

Um die Datenvalidierungsregeln aus einem Zellbereich, dem aktuellen Arbeitsblatt oder der gesamten Arbeitsmappe zu entfernen, können Ihnen die folgenden Methoden einen Gefallen tun.

 Entfernen Sie die Datenvalidierung im ausgewählten Bereich mit der Datenvalidierungsfunktion

1. Wählen Sie die Zellen mit der Datenvalidierung aus, die Sie entfernen möchten.

2. Dann klick Datum > Datenvalidierung > Datenvalidierung, im Popup-Dialogfeld unter dem Einstellungen Tab, klicken Sie auf Alle löschen Schaltfläche, siehe Screenshot:

3. Dann klick OK Schaltfläche, um dieses Dialogfeld zu schließen. Und die auf den ausgewählten Bereich angewendete Datenvalidierungsregel wurde sofort entfernt.

Tips: Um die Datenvalidierung 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 Datenvalidierung im ausgewählten Bereich mit einer praktischen Funktion

Wenn Sie Kutools for Excel, seine Deaktivieren Sie die Einschränkungen für die Datenvalidierung Diese Funktion kann auch helfen, die Datenvalidierungsregeln aus dem ausgewählten Bereich oder dem gesamten Arbeitsblatt zu entfernen.

Nach der Installation Kutools for ExcelBitte machen Sie Folgendes:

1. Wählen Sie den Zellbereich aus oder das gesamte Arbeitsblatt enthält die Datenvalidierung, die Sie entfernen möchten.

2. Dann klick Kutoolen > Tippfehler verhindern > Deaktivieren Sie die Einschränkungen für die Datenvalidierung, siehe Screenshot:

3. Klicken Sie in der eingeblendeten Eingabeaufforderung auf OK, und die Datenvalidierungsregel wurde nach Bedarf gelöscht.

---

 Entfernen Sie die Datenvalidierung aus allen Arbeitsblättern mit VBA-Code

Um die Datenvalidierungsregeln aus der gesamten Arbeitsmappe zu entfernen, sind die obigen Methoden zeitaufwendig, wenn viele Arbeitsblätter vorhanden sind. Hier kann Ihnen der folgende Code helfen, diese Aufgabe schnell zu bewältigen.

1. Halten Sie die Taste gedrückt ALT + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.

2. Dann klick Insert > Modulund fügen Sie das folgende Makro in das Feld ein Modul Fenster.

VBA-Code: Entfernen Sie Datenvalidierungsregeln in allen Arbeitsblättern:

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

3. Dann drücken F5 key, um diesen Code auszuführen, und alle Datenvalidierungsregeln wurden sofort aus der gesamten Arbeitsmappe gelöscht.