Benutzerdefinierte Formeln für die Datenvalidierung

Dieses Tutorial zeigt Ihnen, wie Sie benutzerdefinierte Formeln in der Datenvalidierung in Excel und Google Sheets erstellen

Datenvalidierung - Muss beginnen mit - Excel

Wir können eine benutzerdefinierte Formel schreiben, um sicherzustellen, dass die Daten in einer Zelle mit einem bestimmten Text beginnen.

1. Markieren Sie den gewünschten Bereich zB: B3:B8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.

3. Auswählen Benutzerdefiniert aus dem Dropdown-Feld Zulassen, und geben Sie dann die folgende Formel ein:

=GENAU(LINKS(B3,4),“FRU-“)

Die Formel verwendet 2 Funktionen GENAU und LINKS um festzustellen, ob die ersten 4 in die Zelle eingegebenen Zeichen mit „FRU-“ identisch sind

4. Wenn Sie Ihrem Benutzer eine Eingabenachricht hinzufügen möchten, klicken Sie auf Eingabenachricht.

5. Stellen Sie sicher, dass das Kontrollkästchen „Eingabenachricht anzeigen, wenn Zelle ausgewählt ist“ ist ein Häkchen und geben Sie dann den gewünschten Titel und die gewünschte Nachricht ein.
6. Wenn Sie eine Fehlermeldung hinzufügen möchten, klicken Sie auf Fehlermeldung.

7. Sie können den Stil ändern von Halt entweder Warnung oder Information Wenn Sie dies wünschen, und geben Sie dann Ihren Titel und Ihre Fehlermeldung ein.
8. Klicken OK.
9. Wenn Sie die Eingang Nachrichtenoption wird ein Kommentar auf dem Bildschirm angezeigt, der den Benutzer über die Regel informiert.

10. Geben Sie "FRI-124" in Zelle B3 ein. Wenn Sie ausgewählt haben Halt, wird das folgende Meldungsfeld angezeigt.

11. Wenn Sie ausgewählt haben Warnung, dann wird dieses Meldungsfeld angezeigt. Auf diese Weise können Sie fortfahren, wenn Sie feststellen, dass die Daten korrekt sind.

12. Wenn Sie ausgewählt haben Information, dann wird dieses Meldungsfeld angezeigt.

13. Wenn du klickst OK, können Sie mit den in die Zelle eingegebenen falschen Daten fortfahren.
14. Klicken Abbrechen um die Nachricht zu verlassen oder OK um den Text in die Zelle einzugeben.

Datenvalidierung Großbuchstaben nur in Excel zulassen

Wir können eine benutzerdefinierte Formel schreiben, um sicherzustellen, dass die Daten in einer Zelle nur Großbuchstaben zulassen, wenn Text in die Zelle eingegeben wird.

1. Markieren Sie den gewünschten Bereich zB: B3:B8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Auswählen Benutzerdefiniert aus dem Dropdown-Feld Zulassen, und geben Sie dann die folgende Formel ein:

=GENAU(B3,OBERE(B3))

Die Formel verwendet 2 Funktionen GENAU und OBERER, HÖHER um festzustellen, ob der in die Zelle eingegebene Text in Großbuchstaben ist. Zellen mit einer Mischung aus Zahlen und Text werden als Text betrachtet und die Zahlen werden in der Regel ignoriert.

4. Klicken OK.
5. Geben Sie "fru-124" in Zelle B3 ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.
8. Geben Sie "123456" in Zelle B3 ein.
9. Dies ist zulässig, da es sich um eine Zahl und nicht um einen Text handelt.

In unserem nächsten Beispiel stellen wir sicher, dass nur Großbuchstaben in die Zelle eingegeben werden können.

Datenvalidierung Nur Großbuchstaben in Excel zulassen

Wir können eine benutzerdefinierte Formel in der Datenvalidierung schreiben, um sicherzustellen, dass die Daten in einer Zelle nur Großbuchstaben zulassen

HINWEIS: Wenn Sie Informationen in eine Zelle eingeben, die mit Text beginnt, aber Zahlen enthält, berücksichtigt Excel den Informationstext.

1. Markieren Sie den gewünschten Bereich zB: B3:B8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Auswählen Benutzerdefiniert aus dem Dropdown-Feld Zulassen, und geben Sie dann die folgende Formel ein:

=UND(GENAU(B3,OBERE(B3)),ISTEXT(B3))

Die Formel verwendet 4 Funktionen UND, GENAU, OBER und TEXT um festzustellen, ob der in die Zellen eingegebene Text in Großbuchstaben ist UND um festzustellen, ob es sich bei den eingegebenen Informationen tatsächlich um Text und nicht um eine reine Zahl handelt.

4. Klicken OK.
5. Geben Sie "fru-124" in Zelle B3 ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.
8. Geben Sie "123456" in Zelle B3 ein.
9. Sie erhalten erneut die Fehlermeldung.
10. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.

Datenvalidierung Leerzeichen in Excel verhindern

Wir können eine benutzerdefinierte Formel schreiben, um sicherzustellen, dass keine Leerzeichen in die Daten eingegeben werden, die in einem Zellbereich eingegeben werden.

1. Markieren Sie den gewünschten Bereich zB: B3:B8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Auswählen Benutzerdefiniert aus dem Dropdown-Feld Zulassen, und geben Sie dann die folgende Formel ein:

=B3=ERSETZEN(B3, ” ” , “”)

Die Formel verwendet die SUBSTITUTE-Funktion, um zu überprüfen, ob keine Leerzeichen vorhanden sind.

4. Klicken OK.
5. Geben Sie "FRU - 124" in Zelle B4 ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.

Datenvalidierung Verhindern Sie Duplikate in Excel

Wir können eine benutzerdefinierte Formel schreiben, um zu verhindern, dass wir doppelte Informationen in einen Zellbereich eingeben.

1. Markieren Sie den gewünschten Bereich zB: B3:B8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Wählen Sie Kunde aus dem Dropdown-Feld Zulassen aus, und geben Sie dann die folgende Formel ein:

=ZÄHLENWENN($B$3:$B$8,B3)<2

Die Formel verwendet die ZÄHLENWENN Funktion und Verwendung ABSOLUTES im Bereich B3:B8, um sicherzustellen, dass dies die Liste ist, die die ZÄHLENWENN Die Funktion prüft, ob es doppelte Werte gibt.

4. Klicken OK.
5. Geben Sie "FRU-123" in Zelle D4 ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.

Datenvalidierung existiert in Liste in Excel

Wir können eine benutzerdefinierte Formel schreiben, um sicherzustellen, dass nur bestimmter Text in eine Zelle eingegeben wird.

1. Markieren Sie den gewünschten Bereich zB: D3:D8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie dann die folgende Formel ein:

=ZÄHLENWENN($F$6:$F$8,D3)>0

Die Formel verwendet die ZÄHLENWENN Funktion und Verwendung ABSOLUTES im Bereich F3:F8, um sicherzustellen, dass dies die Liste ist, die die ZÄHLENWENN Funktion prüft, wenn sie prüft, ob der richtige Text eingegeben wird.

4. Klicken OK.
5. Geben Sie „Single“ in Zelle D4 ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.

Datenvalidierung existiert nicht in Liste in Excel

Wir können eine benutzerdefinierte Formel verwenden, um sicherzustellen, dass kein bestimmter Text in eine Zelle eingegeben wird.

1. Markieren Sie den gewünschten Bereich zB: C3:C8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Wählen Sie Kunde aus dem Dropdown-Feld Zulassen aus, und geben Sie dann die folgende Formel ein:

=ZÄHLENWENN($F$6:$F$8,C3)=0

Die Formel verwendet die ZÄHLENWENN Funktion und Verwendung ABSOLUTES im Bereich F3:F8, um sicherzustellen, dass dies die Liste ist, die die ZÄHLENWENN Funktion prüft, wenn sie prüft, ob der richtige Text eingegeben wird.

4. Klicken OK.
5. Geben Sie in Zelle C4 „Rindfleisch“ ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.

In Excel werden nur Zahlen zur Datenvalidierung eingegeben

Wir können eine benutzerdefinierte Formel verwenden, um sicherzustellen, dass nur eine Zahl in eine Zelle eingegeben wird.

1. Markieren Sie den gewünschten Bereich zB: E3:E8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Wählen Sie Kunde aus dem Dropdown-Feld Zulassen aus, und geben Sie dann die folgende Formel ein:

=ISNUMMER(F3:F8)

Die Formel verwendet die Funktion ISNUMBER, um sicherzustellen, dass eine Zahl in die Zellen des Bereichs eingegeben wird.

4. Klicken OK.
5. Geben Sie „neun“ in Zelle F4 ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.

Datenvalidierung überschreitet den Wert in Excel nicht

Wir können eine benutzerdefinierte Formel verwenden, um sicherzustellen, dass die in eine Zelle eingegebenen Werte einen angegebenen Wert nicht überschreiten.

1. Markieren Sie den gewünschten Bereich zB: E3:E8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Wählen Sie Kunde aus dem Dropdown-Feld Zulassen aus, und geben Sie dann die folgende Formel ein:

=E3<=$G$6

Die Formel an ABSOLUT im Bereich G6, um sicherzustellen, dass dies der Wert ist, den die Regel prüft, wenn Daten in E3 eingegeben werden.

4. Klicken OK.
5. Geben Sie „9“ in Zelle E4 ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.

Die Datenvalidierung überschreitet die Gesamtzahl in Excel nicht

Wir können eine benutzerdefinierte Formel verwenden, um sicherzustellen, dass die in einen Zellbereich eingegebenen Werte einen angegebenen Gesamtwert für den Bereich nicht überschreiten

1. Markieren Sie den gewünschten Bereich zB: F3:F8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Wählen Sie Kunde aus dem Dropdown-Feld Zulassen aus, und geben Sie dann die folgende Formel ein:

=SUMME($F$3:$F$8)<=$H$6

Die Formel verwendet die SUMME Funktion und Verwendung ABSOLUTES im Bereich F3:F8, um sicherzustellen, dass dies die Liste ist, die die SUMME Funktion prüft, wenn sie prüft, ob die Summe des Bereichs nicht größer als der in H6 eingegebene Wert ist.

4. Klicken OK.
5. Geben Sie "40" in Zelle F4 ein.
6. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Nachricht zu schließen, oder auf Wiederholen, um den korrekten Text erneut in die Zelle einzugeben.

Datenvalidierung Wochentagsdaten Nur in Excel.

Wir können eine benutzerdefinierte Formel verwenden, um sicherzustellen, dass nur Wochentage eingegeben werden, wenn Datumsangaben in Excel verwendet werden.

1. Markieren Sie den gewünschten Bereich zB: G3:G8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Wählen Sie Kunde aus dem Dropdown-Feld Zulassen aus, und geben Sie dann die folgende Formel ein:

=WOCHENTAG(F3,2)<6

Die Wochentagfunktion prüft, ob der im Datum enthaltene Tag kein Samstag oder Sonntag ist.

4. Ändern Sie das Datum in G5, um einen Samstag anzuzeigen (z. B. 9^NS Mai 2022).
5. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen oder Wiederholen, um ein alternatives Datum einzugeben.

Datenvalidierung zukünftiges Datum nur in Excel

Wir können eine benutzerdefinierte Formel erstellen, damit der Benutzer nur ein Datum eingeben kann, das in der Zukunft liegt.

1. Markieren Sie den gewünschten Bereich zB: G3:G8.
2. Wählen Sie in der Multifunktionsleiste Daten > Datentools > Datenvalidierung.
3. Wählen Sie Kunde aus dem Dropdown-Feld Zulassen aus, und geben Sie dann die folgende Formel ein:

=G3>HEUTE()

Die Formel verwendet die HEUTE Funktion, um zu überprüfen, ob das in die Zelle eingegebene Datum größer als das heutige Datum ist.

4. Ändern Sie das Datum in G5 auf gestern.
5. Wenn Sie die Option Error Alert verwendet haben, werden Ihre benutzerdefinierten Warn- und Fehlermeldungen angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

6. Klicken Sie auf Abbrechen oder Wiederholen, um ein alternatives Datum einzugeben.

Die Datenvalidierung muss mit in Google Sheets beginnen

1. Markieren Sie den gewünschten Bereich zB: B3:B8.
2. Wählen Sie im Menü Daten > Datenvalidierung.

3. Der Zellbereich wird bereits ausgefüllt.

4. Auswählen Benutzerdefinierte Formel stammt aus der Dropdown-Liste Kriterien.

5. Geben Sie die Formel ein.

=GENAU(LINKS(B3,4),“FRU-“)

6. Wählen Sie entweder Warnung anzeigen oder Eingabe ablehnen wenn die Daten ungültig sind.

7. Sie können bei Bedarf einen Hilfetext zur Validierung eingeben.

8. Klicken Sie auf Speichern.

9. Klicken Sie in B3, um den Hilfetext zur Validierung anzuzeigen

10. Typ FR-123
11. Wenn Sie ausgewählt haben Warnung anzeigen, wird die folgende Meldung angezeigt.

12. Alternativ, wenn Sie ausgewählt haben Eingabe von ungültigen Daten ablehnen, werden Sie an der Eingabe der Daten gehindert und die folgende Meldung erscheint auf dem Bildschirm.

Der Rest der benutzerdefinierten Formelbeispiele in Google Tabellen funktioniert genauso.