Benutzerdefinierte Formeln zur Datenüberprüfung

In diesem Tutorial zeigen wir Ihnen, wie Sie benutzerdefinierte Formeln bei der Datenüberprüfung in Excel und Google Sheets erstellen können.

Datenüberprüfung – Muss mit einem bestimmten Text beginnen – 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, z. B.: B3:B8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.

3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus und geben Sie die folgende Formel ein:

=IDENTISCH(LINKS(B3;4);“FRU-„)

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

4. Wenn Sie eine Eingabemeldung für Ihren Benutzer hinzufügen möchten, klicken Sie auf Eingabemeldung.

5. Vergewissern Sie sich, dass das Kontrollkästchen „Eingabemeldung anzeigen, wenn Zelle ausgewählt wird“ aktiviert ist und geben Sie dann den gewünschten Titel und die Meldung ein.
6. Wenn Sie eine Fehlermeldung hinzufügen möchten, klicken Sie auf Fehlermeldung.

7. Sie können den Typ von “ Stopp“ in “ Warnung“ oder “ Information“ ändern und dann den Titel und die Fehlermeldung eintippen.
8. Klicken Sie auf OK.
9. Wenn Sie die Option Eingabemeldung verwendet haben, wird ein Kommentar, der den Benutzer über die Regel informiert, auf dem Bildschirm angezeigt.

10. Geben Sie „FRI-124“ in Zelle B3 ein. Wenn Sie Stopp gewählt haben, wird die folgende Meldungsbox angezeigt.

11. Wenn Sie die Option Warnung gewählt haben, erscheint diese Meldungsbox. Damit können Sie fortfahren, wenn Sie entscheiden, dass die Daten korrekt sind.

12. Wenn Sie Informationen gewählt haben, erscheint diese Meldungsbox.

13. Wenn Sie auf OK klicken, können Sie mit den falschen Daten in der Zelle fortfahren.
14. Klicken Sie auf Abbrechen, um die Meldung zu verlassen, oder auf OK, um den Text in die Zelle einzugeben.

Datenüberprüfung in Excel – Nur Großbuchstaben zulassen

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

1. Markieren Sie den gewünschten Bereich, z. B.: B3:B8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert und geben Sie die folgende Formel ein:

=IDENTISCH(B3;GROSS(B3))

Die Formel verwendet 2 Funktionen, und zwar IDENTISCH und GROSS, um festzustellen, ob der in die Zelle eingegebene Text in Großbuchstaben geschrieben ist. Zellen mit einer Mischung aus Zahlen und Text werden als Text betrachtet und die Zahlen werden in der Regel ignoriert.

4. Klicken Sie auf OK.
5. Geben Sie „fru-124“ in Zelle B3 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu verlassen, oder auf Wiederholen, um den richtigen 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 Text handelt.

In unserem nächsten Beispiel werden wir sicherstellen, dass nur Text in Großbuchstaben in die Zelle eingegeben werden kann.

Datenüberprüfung in Excel – Nur Text in Großbuchstaben  zulassen

Wir können eine benutzerdefinierte Formel unter Datenüberprüfung schreiben, um sicherzustellen, dass die Daten in einer Zelle nur Text in Großbuchstaben zulassen.

HINWEIS: Wenn Sie Informationen, die mit einem Text beginnen, aber Zahlen enthalten,  in eine Zelle eingeben, betrachtet Excel diese als Text.

1. Markieren Sie den gewünschten Bereich, z. B.: B3:B8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie dann die folgende Formel ein

=UND(IDENTISCH(B3;GROSS(B3));ISTTEXT(B3))

Die Formel verwendet 4 Funktionen, und zwar UND, IDENTISCH, GROSS und TEXT, um festzustellen, ob der in die Zellen eingegebene Text in Großbuchstaben geschrieben ist und um zu prüfen, ob es sich bei den eingegebenen Informationen tatsächlich um Text und nicht um eine reine Zahl handelt.

4. Klicken Sie auf OK.
5. Geben Sie „fru-124“ in Zelle B3 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu verlassen, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.
8. Geben Sie „123456“ in Zelle B3 ein.
9. Sie erhalten erneut die Fehlermeldung.
10. Klicken Sie auf Abbrechen, um die Meldung zu beenden, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.

Datenüberprüfung in Excel – Leerzeichen  vermeiden

Wir können eine benutzerdefinierte Formel schreiben, um sicherzustellen, dass keine Leerzeichen in die Daten, die in einen Zellenbereich eingegeben werden, enthalten sind.

1. Markieren Sie den gewünschten Bereich, z. B.: B3:B8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert und geben Sie die folgende Formel ein:

=B3=WECHSELN(B3;“ „;““)

Die Formel verwendet die Funktion WECHSELN, um zu prüfen, ob keine Leerzeichen vorhanden sind.

4. Klicken Sie auf OK.
5. Geben Sie „FRU – 124“ in Zelle B4 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu verlassen, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.

Datenüberprüfung in Excel – Duplikate  vermeiden

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

1. Markieren Sie den gewünschten Bereich, z. B.: B3:B8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie dann die folgende Formel ein:

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

Die Formel verwendet die Funktion ZÄHLENWENN und verwendet Absolute Bezüge im Bereich B3:B8, um sicherzustellen, dass dies die Liste ist, die die Funktion ZÄHLENWENN betrachtet, wenn sie prüft, ob irgendwelche doppelten Werte vorhanden sind.

4. Klicken Sie auf OK.
5. Geben Sie „FRU-123“ in Zelle D4 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu verlassen, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.

Datenüberprüfung in Excel – Text in einer Liste vorhanden

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

1. Markieren Sie den gewünschten Bereich, z. B: D3:D8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie die folgende Formel ein:

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

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

4. Klicken Sie auf OK.
5. Geben Sie „Einzel“ in Zelle D4 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu beenden, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.

Datenüberprüfung in Excel – Text existiert nicht in der Liste

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

1. Markieren Sie den gewünschten Bereich, z. B: C3:C8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
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;C3)=0

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

4. Klicken Sie auf OK.
5. Geben Sie „Rind“ in Zelle C4 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu beenden, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.

Datenüberprüfung in Excel – Nur Zahlen zulassen

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

1. Markieren Sie den gewünschten Bereich, z. B.: F3:F8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie dann die folgende Formel ein:

=ISTZAHL(F3:F8)

Die Formel verwendet die Funktion ISTZAHL, um sicherzustellen, dass nur Zahl in die Zellen des Bereichs eingegeben werden können.

4. Klicken Sie auf OK.
5. Geben Sie „neun“ in Zelle F4 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu verlassen, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.

Datenüberprüfung in Excel – Überschreitet einen Wert nicht

Mit einer benutzerdefinierten Formel können wir sicherstellen, dass die in eine Zelle eingegebenen Zahlen einen bestimmten Wert nicht überschreiten.

1. Markieren Sie den gewünschten Bereich, z. B.: E3:E8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie dann die folgende Formel ein

=E3<=$G$6

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

4. Klicken Sie auf OK.
5. Geben Sie „9“ in Zelle E4 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu beenden, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.

Datenüberprüfung in Excel – Überschreitet den Gesamtwert nicht

Mit einer benutzerdefinierten Formel können wir sicherstellen, dass die in einen Zellenbereich eingegebenen Werte einen bestimmten Gesamtwert für den Bereich nicht überschreiten.

1. Markieren Sie den gewünschten Bereich, z. B: F3:F8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie dann die folgende Formel ein:

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

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

4. Klicken Sie auf OK.
5. Geben Sie „40“ in Zelle F4 ein.
6. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen, um die Meldung zu beenden, oder auf Wiederholen, um den richtigen Text in die Zelle erneut einzugeben.

Datenüberprüfung in Excel – Nur Werktage

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

1. Markieren Sie den gewünschten Bereich, z. B: G3:G8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie dann die folgende Formel ein:

=WOCHENTAG(F3;2)<6

Die WOCHENTAG-Funktion prüft, ob der im Datum enthaltene Tag ein Samstag oder ein Sonntag ist.

4. Ändern Sie das Datum in G5 so, dass ein Samstag angezeigt wird (z. B. 9. Mai 2020).
5. Wenn Sie die Option Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

7. Klicken Sie auf Abbrechen oder Erneut versuchen, um ein anderes Datum einzugeben.

Datenüberprüfung in Excel – Nur zukünftiges Datum

Wir können eine benutzerdefinierte Formel erstellen, die es dem Benutzer erlaubt, nur  ein Datum, das in der Zukunft liegt, einzugeben.

1. Markieren Sie den gewünschten Bereich, z. B: G3:G8.
2. Wählen Sie in der Menüleiste Daten > Datentools > Datenüberprüfung.
3. Wählen Sie im Dropdown-Feld Zulassen die Option Benutzerdefiniert aus, und geben Sie dann die folgende Formel ein:

=G3>HEUTE()

Die Formel verwendet die Funktion HEUTE, um zu prü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 Fehlermeldung verwendet haben, wird Ihre benutzerdefinierte Warn- und Fehlermeldung angezeigt. Wenn Sie diese Option nicht verwendet haben, wird die Standardwarnung angezeigt.

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

Datenüberprüfung in Google Sheets – Muss mit einem bestimmten Text beginnen

 

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

3. Der Zellenbereich ist bereits ausgefüllt.

4. Wählen Sie in der Dropdown-Liste Kriterien die Option Benutzerdefinierte Formel aus.

5. Geben Sie die Formel ein.

=IDENTISCH(LINKS(B3;4);“FRU-„)

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

7. Sie können bei Bedarf einen Validierungshilfetext eingeben.

8. Klicken Sie auf Speichern.

9. Klicken Sie auf B3, um den Validierungshilfetext anzuzeigen.

10. Geben Sie FRI-123 ein
11. Wenn Sie die Option Warnung zeigen gewählt haben, wird die folgende Meldung angezeigt:

12. Wenn Sie für ungültige Daten die Option Eingabe ablehnen gewählt haben, werden Sie an der Dateneingabe gehindert und die folgende Meldung erscheint auf dem Bildschirm:

Die übrigen Beispiele für benutzerdefinierte Formeln funktionieren in Google Sheets auf genau dieselbe Weise.