In Excel könnte alles so schön einfach sein, wenn es da nicht immer den bösen Anwender gäbe.
Der macht leider nicht immer das, was er soll (oder zumindest was wir glauben, dass er machen soll) und bringt durch unerwünschte oder fehlerhafte Eingaben unsere schönen Kalkulationen aus dem Gleichgewicht.
Im heutigen Artikel zeige ich, wie man sicherstellt, dass bei einer Dateneingabe nur Großbuchstaben (oder nur Kleinbuchstaben) akzeptiert werden.
Und so geht’s:
Ein typischer Anwendungsfall dafür sind z.B. Artikelnummern. Hier wollen wir den Anwender mit Hilfe der Datenüberprüfung mehr oder weniger sanft zu einer korrekten Eingabe bewegen.
Die „harte“ Methode
Um sicherzustellen, dass in bestimmte Felder nur Großbuchstaben eingegeben werden, definieren wir für die betroffenen Felder eine Datenüberprüfung.
Also: Felder markieren, dann im Menüband das Register „Daten“ und dort die Schaltfläche „Datenüberprüfung“ auswählen:
Datenüberprüfung aufrufen
Nun wählen wir im Feld „Zulassen“ den Eintrag „Benutzerdefiniert“ und geben dann im Feld „Formel“ folgendes ein:
=IDENTISCH(B3;GROSS(B3))
Benutzerdefinierte Regel
Die hier verwendete Zelladresse muss natürlich mit der aktiven Zelle in deinem markierten Bereich übereinstimmen.
Diese Formel prüft, ob der in Zelle B3 eingegebene Wert identisch mit dem Wert ist, den die Funktion GROSS zurückliefert. Und dass ist eben bei Texten der Text in Großbuchstaben.
Um dem Anwender im Fehlerfall einen entsprechenden Hinweis zu geben, tragen wir im Register „Fehlermeldung“ die gewünschte Meldung ein:
Eigene Fehlermeldung
Wichtig:
Da als Typ standardmäßig „Stopp“ eingestellt ist, wird Excel den Anwender ganz hartnäckig solange mit der Fehlermeldung beglücken, bis wirklich korrekte Werte eingegeben wurden.
Zahlen und Zeichen werden ohne Probleme übernommen. Versucht man jedoch, Text mit kleinen Buchstaben einzugeben, erscheint die definierte Fehlermeldung:
Falsche Eingabe wird abgelehnt
Die „weiche“ Methode
Soll der Anwender lediglich auf die Falscheingabe hingewiesen aber diese dennoch akzeptiert werden, müssen wir nur einen anderen Fehlertyp einstellen. Anstelle eines harten „STOPP“ wählen wir nun den etwas weicheren Typ „Warnung“:
Statt Fehler nur eine Warnung
Nun erscheint zwar die gleiche Meldung wie zuvor, allerdings hat der Anwender nun die Möglichkeit, mit der Eingabe unverändert fortzufahren:
Eingabe kann fortgesetzt werden
Um im Nachgang auf einen Blick sämtliche Zellen zu erkennen, die trotz Warnmeldung eine fehlerhafte Eingabe enthalten, bietet Excel die Möglichkeit, die ungültigen Werte einzukreisen:
Ungültige Daten einkreisen
Und schon wird der ganze Pfusch offensichtlich:
Die markierten Einträge
Werden nun die fehlerhaften Zellen manuell nachbearbeitet, verschwinden auch die jeweiligen roten Kreise automatisch wieder. Alternativ können alle roten Markierungen wieder über die entsprechende Schaltfläche abgeschaltet werden:
Alle Markierungen entfernen
Die Falscheingaben werden dadurch allerdings nicht richtiger…
Selbstverständlich funktioniert die Datenprüfung auch, wenn statt Groß- nur Kleinbuchstaben erlaubt sein sollen. Hier muss lediglich die Regel anders lauten:
=IDENTISCH(B3;KLEIN(B3))
Wie du siehst, lässt sich mit wenig Aufwand die Datenqualität in unseren Tabellen zumindest ein klein wenig verbessern.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
mir gefaellt, dass du eine harte und weiche Methode vorstellst.
Oftmals, denke ich, wird man die harte Methode waehlen muessen, um nachfolgende Aktionen nicht zu gefarhrden.
Danke fuer den Tipp.
Viele Grueße
Gerhard
Hallo Martin,
aus leidvoller Erfahrung mit einer vergleichbaren Vorgabe zu Artikelnummern (in meinen Fall waren es Finanzpositionen) noch eine kleine Ergänzung.
Die Datenprüfung versagt leider vollkommen, wenn aus der Zwischenablage Werte eingefügt werden (unabhängig ob per STRG + V oder auch als Inhalte einfügen Werte.
Aber ansonsten stimme ich dir zu, dass die Methoden ihre Vorteile haben und gerade die Option „Ungültige Werte einkreisen“ sollte den Fehler mit eingefügten Werten relativ schnell identifizierbar machen 🙂
Viele Grüße
Andreas
Hallo Andreas,
das leidige Problem mit der Zwischenablage kann ich bestätigen. Das betrifft übrigens nicht nur die Datenüberprüfung, sondern auch etwaige anderen Zellformatierungen, die man mit dem Blattschutz eigentlich unveränderbar machen möchte. Warum hier der Blattschutz außer Kraft gesetzt wird, bleibt wohl Microsofts Geheimnis. Vielleicht steht es ja auf der Feature-Liste von Excel 2020…
Schöne Grüße,
Martin
Hallo Martin,
in der Sache stimme ich dir zu, dass hier Excel tatsächlich ein etwas merkwürdiges Verhalten gerade auch in Richtung Blattschutz aufweist. Auf der anderen Seite bin ich aus der täglichen Arbeit aber auch froh, dass der Blattschutz nicht absolut ist und bei manchen Tabellen ein mehr oder minder kreativer Weg vorhanden ist, um nicht seine Zeit in der Erstellung einer Voodoo Puppe für den Urheber einer Tabelle zu stecken.. ;-). Aus rein persönlichen Gründen würde ich daher hoffen, dass dein Feature Wunsch auch für 2020 noch nicht vollständig umgesetzt wird.
Viele Grüße
Andreas
Vielleicht wird dann die 2020er-Version eine „Excel VE“ (Voodoo-Edition) mit passender Puppe dazu 🙂
Ich sehe schon den Artikel im April als Exklusivmeldung auf tabellenexperte.de
Neue LeChuck Version von Excel erschienen, vorbestellbar nur heute. Kontaktieren Sie Stan. 🙂
Viele Grüße und ein großes Lob noch nachträglich an deinen MI Artikel von damals 🙂
Andreas
Ja, wer weiß… 🙂
Danke für den Interessanten Beitrag.
Ich bin seit längerem auf der Suche nach einer Möglichkeit, eine „doppelte“ Datenprüfung auszuführen.
Hintergrund: Ich habe eine Liste mit Texteinträgen als Dropdown-Feld:
ABC
DEF
GHI
…der Nutzer kann aber auch manuell „abc“ eingeben. Das soll mit der Datenprüfung verhindert werden.
Da ich jedoch die Dropdown-Liste schon als Gültigkeitskriterium bei der Datenprüfung habe, felht mir die Möglichkeit, hier eine zweite Datenprüfung für die Grossschreibung zu hinterlegen. Gibt es hier eine Idee?
Hallo Stefan,
Dropdown-Liste plus Großschreibung in der Datenüberprüfung wird vermutlich schwierig, da habe ich jetzt auch keine Lösung. Aber Du könntest klein geschriebenen Text per bedingter Formatierung einfach ausblenden (weiße Schriftart).
Formatierungsregel:
=NICHT(IDENTISCH(A1;GROSS(A1)))
Das verhindert zwar nicht die Eingabe, aber der Anwender wird zumindest nochmal drüber nachdenken…
Schöne Grüße,
Martin
Hallo Martin,
ich bin diejenige, die beeindruckt ist:))
Dein Fachwissen ist gefragt.. Vielleicht kannst Du mir helfen. Ich sitze an einer Urlaubstabelle und möchte nur die Großen U zählen und die kleinen u auslassen (noch nicht genehmigter Urlaub). Habe Zählenwenn Formel angewendet, leider ohne Erfolg (es werden immer noch alle Us gezählt).
Hast Du da vielleicht einen Tipp für mich.
Vielen herzlichen Dank
Natalja
Hallo Natalja,
den Bereich und/oder die Groß-Kleinschreibung müsstest du noch anpassen…
=SUMMENPRODUKT((IDENTISCH(A1:A21;“U“)*1))
liebe Grüße
Roswitha
Hallo Martin, ich möchte eine Formel definieren die nur Eingaben in einem bestimmten Format zugelassen sind. Das Muster dazu ist 756.####.####.##
Ich habe in der Datenüberprüfung diverse Formel wie „756.###.###.##“ eingegeben, es wird aber immer die von mir eingestellte Fehlermeldung ausgegeben.
Danke für Deine Hilfe
Viktor
Hallo Viktor,
eine Datenüberprüfung auf dieses Eingabeformat lässt sich meines Erachtens nicht umsetzen. Du müsstest dazu eine extrem komplexe benutzerdefinierte Formel erstellen, die jedes einzelne Element prüft. Also dass die ersten vier Stellen „756.“ lauten, danach zwischen den nächsten beiden Punkten eine dreistellige Zahl (oder auch Text?), dann das gleiche nochmal und nach dem letzten Punkt eine zweistellige Zahl (oder auch Text?). Das wird extrem komplex, daher kann ich dir hier keine Lösung bieten.
Schöne Grüße,
Martin
Hallo Martin,
die Methode gefällt mir. Ich versuche sie gerade zu kombinieren mit Kapital 10 aus Pivot Tabellen für Dummies (komme aber leider nicht so recht weiter). Da lege ich mithilfe einer Pivot-Tabelle (und den Funktionen BEREICH.VERSCHIEBEN und ANZAHL2 im Namensmanager) eine sich anpassende Dropdownliste an.
Ich habe jetzt die Zusatzanforderung, dass es „case sensitive“ sein soll. Ist in der Liste „Fruchthaltige Artikel“ enthalten, müsste ich eine Warnmeldung (über die „sanfte“ Methode) bekommen, wenn ich stattdessen „fruchthaltige Artikel“ eingebe.
Geht so etwas?
Ich freue mich über eine Antwort.
Viele Grüße
Christa
Hallo Christa,
ich fürchte, diese Anforderung lässt sich mit normalen Mitteln (also ohne VBA) nicht umsetzen. Denn dazu bräuchte man sozusagen eine doppelte Datenprüfung – einmal die besagte Liste und zusätzlich die Prüfung auf Groß-/Kleinschreibung. Beides lässt sich für sich genommen mit der Datenprüfung abbilden, aber eben nicht in Kombination.
Falls jedoch ein anderer Leser eine Lösung dafür hat, lasse ich mich gerne eines Besseren belehren 🙂
Schöne Grüße,
Martin
Wie kann ich Exel dazu bringen in gewissen Tabellen allein ein großes A oder B zu schreiben, ohne die Großtaste zu drücken.
Ich schreibe ein a und das soll allein ein großes A werden.
Hallo Michael,
die kurze Antwort: Gar nicht. Zumindest nicht, wenn es nur für bestimmte Tabellen oder Bereiche gelten soll und ohne Einsatz von VBA.
Die einzige Möglichkeit, die mir einfällt, wäre über die Autokorrektur (Menü Datei | Optionen | Dokumentprüfung | AutoKorrektur-Optionen…). Hier wäre es natürlich möglich, alle 26 Kleinbuchstaben in der Liste zu ergänzen. Aber das würde ich nicht ernsthaft empfehlen. Denn das gilt dann global in jeder Excel-Arbeitsmappe und somit wird jeder Kleinbuchstabe immer und überall zu einem Großbuchstaben.
Schöne Grüße,
Martin
Schöne Grüße,
Martin