Eingabefehler vermeiden durch eine Datenüberprüfung 15

Zulässige Eingaben in Excel mit Hilfe der Datenüberprüfung festlegen
 

Zu einer anwenderfreundlichen Tabelle gehört unter anderem, dass man den Benutzer auf etwaige Fehleingaben bereits während der Datenerfassung hinweist.

Excel bietet dafür verschiedene Möglichkeiten. Eine davon ist die sogenannte Datenüberprüfung. Mit ihrer Hilfe lassen sich für die entsprechenden Tabellenbereiche Regeln definieren, die nur bestimmte Eingaben zulassen.

Dies ist ein wirksames Mittel, um Fehleingaben und damit unnötigen Datenmüll in seinen Arbeitsblättern zu verhindern.

Und so geht’s:

Die Datenüberprüfung in Excel bietet viele Möglichkeiten, die zulässigen Eingaben in eine Zelle auf einen gewünschten Bereich einzuschränken. Die Einstellungen dazu finden sich in der Registerkarte “Daten”, Schaltfläche “Datenüberprüfung”.

Datenüberprüfung aufrufen

Datenüberprüfung aufrufen

Hinweis: Alle Datenprüfungsregeln beziehen sich immer nur auf die Zellen, die man beim Erstellen der Regeln markiert hat.

Sehen wir uns die verschiedenen Optionen etwas näher an.

Zeiterfassung

Eine gängige Anwendung von Excel ist die Erfassung von Arbeitszeiten. Um Fehleingaben zu vermeiden, könnte man die zulässigen Uhrzeiten auf einen bestimmten Zeitrahmen beschränken.

Uhrzeiten definieren

Uhrzeiten definieren

Damit können in der markierten Zelle nur noch Uhrzeiten zwischen 08:00 und 17:00 Uhr eingegeben werden. Alles andere würde zu einer Fehlermeldung führen.

Über die entsprechende Auswahl im Feld “Daten” lassen sich auch andere Anforderungen abbilden, wie z.B. Uhrzeiten, die vor oder nach einer bestimmten Zeit liegen:

Unterschiedliche Kriterien

Unterschiedliche Kriterien

Datumserfassung

In die gleiche Richtung geht die Beschränkung auf bestimmte Datumswerte. Das folgende Beispiel verhindert Datumseingaben, die sich im Zeitraum 24.12.2015 – 31.12.2015 befinden:

Datumsangaben definieren

Datumsangaben definieren

Textlängen

Wenn es darum geht, Daten für einen späteren Import in ein Warenwirtschafts- oder CRM-System vorzubereiten, kommt es oftmals auf die maximale Feldlänge an. Hier bietet sich die Datenüberprüfung der Textlänge an. Folgende Einstellung verhindert z.B. die Erfassung von Daten mit mehr als 20 Zeichen:

Textlängen definieren

Textlängen definieren

Zahlenwerte

Sollen in einem Feld nur Zahlenwerte und keine Texte erlaubt sein, lässt sich auch dies mit der Datenüberprüfung umsetzen. Im folgenden Beispiel werden nur ganze Zahlen im Bereich zwischen 1 und 10 akzeptiert:

Nur ganze Zahlen

Nur ganze Zahlen

Selbstverständlich gibt es auch eine Einstellung, die Dezimalzahlen zulässt, wie im folgenden Bild zu sehen ist. Hier sind nur Werte zwischen 0 und 1 erlaubt:

Dezimalzahlen zulassen

Dezimalzahlen zulassen

Freie Regeln definieren

Wem diese vordefinierten Möglichkeiten noch nicht ausreichen, kann sich über mehr oder weniger beliebig komplexe Formeln eigene Gültigkeitsregeln definieren. Dazu wählt man den Eintrag “Benutzerdefiniert”:

Benutzerdefinierte Formel

Benutzerdefinierte Formel

In das Formelfeld können dann sowohl einfache Rechenformeln als auch beliebige Excelfunktionen eingegeben und miteinander kombiniert werden. In dieser benutzerdefinierten Regel sind in A13 nur Werte zulässig, die größer als 100 und gleichzeitig kleiner als die Summe der Werte in H10:H20 sind:

Beliebig komplexe Formeln

Beliebig komplexe Formeln

Hier sind (fast) nur durch deine Fantasie Grenzen gesetzt.

Finden von Datenüberprüfungen

Beim Nachträglichen Verändern von solchen Regeln wäre es natürlich hilfreich zu wissen, in welchen Zellen überhaupt eine Datenüberprüfung stattfindet. Dann wenn man die Eingabefelder nicht z.B. farblich markiert hat, sieht man es den Zellen leider nicht an.

Die Lösung ist aber ganz einfach und geht über die Suchfunktion:

Datenüberprüfungen finden

Datenüberprüfungen finden

Und schon werden sämtliche Zellen im Arbeitsblatt markiert, bei denen eine Regel zur Datenüberprüfung hinterlegt ist.

Wozu verwendest Du die Datenüberprüfung in Excel?

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

15 Gedanken zu “Eingabefehler vermeiden durch eine Datenüberprüfung

  • Frank Kössler

    Hallo,
    die Datenüberprüfung kann ich absolut empfehlen. Ich nutze sie insbesondere, wenn ich Excel-Tabellen herstelle, mit denen Kollegen später arbeiten (müssen). Z. B. bei der Ermittlung von Honorarzonen nach HOAI (Honorarordnung für Architekten und Ingenieure). Dort sind nur ganze Zahlen zwischen 1 und 3 bzw. zwischen 1 und 5 zulässig. Alle anderen Eingaben werden abgewiesen und würden ansonsten unsinnige Daten ergeben.
    Grüße, Frank

  • Andreas

    Hallo,
    Danke für den guten Artikel ,hat mir sehr geholfen.

    Ich bin nun leider auf ein Problem gestoßen. Ich hab in einer Spalte die Datenüberprüfungen aktiviert um zu lange Artikeltexte(35 Zeichen) zu vermeiden. Wenn ich die Eingabe von Hand mache, d.h. in das Feld klicken und direkt den Text eingeben, schlägt die Datenüberprüfung brav an und weißt mich darauf hin das der Text zu lang ist.
    Wenn ich nun aber aus einer anderen Quelle (z.B. einer anderen Exceltabelle ) den Wert durch kopieren und einfügen in das Feld eintragen schlägt die Datenüberprüfung nicht an. Teilweise löscht er dann auch die Datenüberprüfungsregel.

    Was muss man einstellen das Excel die Überprüfung durchführt auch wenn ich die Werte durch kopieren eintragen? Geht das überhaupt?

    Danke und Gruß

    Andreas

    • Martin Weiß Beitragsautor

      Hallo Andreas,

      die Datenüberprüfung funktioniert tatsächlich nur bei direkter Eingabe. Sobald Werte kopiert werden – egal wie – greift die Datenüberprüfung nicht mehr.

      Grüße,
      Martin

  • Marc

    Hallo Martin,
    ich nutze die Datenüberprüfung regelmäßig, wenn Tabellen von mehreren Mitarbeitern bearbeitet werden müssen.
    Jetzt stehe ich vor der Herausforderung dass ich mich auf ein gewisses Format beschränken muss. Also eine beliebige 6 stellige Zahl, der aber immer der Buchstabe D voransteht. Also “Dxxxxxx”, wobei die x immer eine Zahl zwischen 0 und 9 sein müssen. ist das in der Datenüberprüfung irgendwie kombinierbar, das niemand “Axxxxxx” eingeben kann oder nicht nur eine 5-Stellige Zahl?
    Gruß
    Marc

    • Martin Weiß Beitragsautor

      Hallo Marc,

      Du könntest zum Beispiel folgende Formel in der Datenüberprüfung verwenden:
      =UND(LINKS(A1;1)=”D”;LÄNGE(A1)=6;ISTZAHL(WERT(RECHTS(A1;5))))

      Damit wird sichergestellt, dass die Eingabe mit D beginnt, insgesamt genau 6 Zeichen lang ist und die letzten 5 Stellen auch tatsächlich eine Zahl sind.

      Schöne Grüße,
      Martin

  • Annette Niederbacher

    Wahrscheinlich ist es ganz einfach. Ich suche eine Einschränkung auf ganz durch 100 teilbare Werte.
    (Bei Budgeterfassung bitte keine krummen Zahlen….)
    Geht das auch über die freien Regeln mit einer Formel? Wie müsste diese aussehen?

  • Tom

    Hallo Martin,

    ich bräuchte eine Prüfung nach folgender Logik:
    Datum muss im Format tt.mm.jjjj eingegeben werden, der Tag darf aber auch “00” sein, weil dieser erst zu einem späteren Zeitpunkt (an anderer Stelle) ergänzt wird. Also z.B. 00.03.2016 muss zulässig sein, aber es soll eben auch eine Prüfung des Formats tt.mm.jjjj stattfinden. Grübel…

    Danke
    Tom

    • Martin Weiß Beitragsautor

      Hallo Tom,

      da es den Wert “00” nicht als zulässiges Datum gibt, lässt sich eine solche Überprüfung nur über komplexere Konstrukte lösen. Ein Beispiel:

      In Zelle A1 wird das Datum eingegeben
      Im Bereich B2:B33 werden alle zulässigen Tage gelistet, von 00 bis 31 (als Zahl formatiert, benutzerdefinertes Format 00)
      Im Bereich C2:C13 werden alle zulässigen Monate gelistet, von 01 bis 12 (als Zahl formatiert, benutzerdefinertes Format 00)
      Im Bereich D2:D32 werden alle zulässigen Jahre gelistet, z.B. von 2000 bis 2030

      In der Datenüberprüfung für Zelle A1 wählst Du “Benutzerdefiniert” und gibst folgende Formel ein:

      =ODER(UND(ISTZAHL(VERGLEICH(WERT(LINKS(A1;2));B2:B33;0)+VERGLEICH(WERT(TEIL(A1;4;2));C2:C13;0)+VERGLEICH(WERT(RECHTS(A1;4));D2:D32;0)));WENNFEHLER(UND(TAG(A1);MONAT(A1);JAHR(A1));FALSCH))

      Wenn als Tag “00” eingegeben wird, dann interpretiert Excel die gesamte Eingabe als Text. Daher prüft der erste Teil der Formel, ob die einzelnen Bestandteile des Textes in den drei genannten Referenzspalten enthalten sind.
      Falls ein echtes Datum eingegeben wurde, kommt der zweite Teil der ODER-Funktion zum Tragen.

      Schöne Grüße,
      Martin

      • Tom

        Danke für Deine Nachricht – ich habe selbst schon “unendlich” probiert und hab’s fast befürchtet. Letztlich müsste man den Algorithmus der Datenüberprüfung “Datum” nachbauen und dort bei “tt” auch “00” zulassen. Wenigstens habe ich jetzt Gewissheit, dass das einfach zu tricky ist.

        Herzliche Grüße
        Tom

      • Tom

        ?…irgendwie wurde bei mir vorhin nur ein Teil Deiner Antwort im Browser angezeigt. Erst jetzt sehe ich, dass Du eine für mich sehr (!) brauchbare Lösung gefunden hast. Wunderbar – ich muss nur die zulässigen Jahre erweitern und schon tut es das, wonach ich gesucht hatte.

        Herzlichen Dank!
        Tom

        • Tom

          Lieber Martin,

          Deine Antwort hat mich noch zu einer weiteren Lösung geführt, die m.E. meine Anforderung jetzt komplett abdeckt:

          =ODER((UND(ISTZAHL(A1);A1>0;A1<=2958465;LINKS(ZELLE("format“;A1);1)=„D“));(LINKS(G6;2)="00"))

          Zelle A1 ist als TT.MM.JJJJ formatiert.

          Servus
          Tom

          • Tom

            war leider noch ein Kopierfehler drin (“G6” anstelle von “A1”)

            =ODER((UND(ISTZAHL(A1);A1>0;A1<=2958465;LINKS(ZELLE("format“;A1);1)=„D“));(LINKS(A1;2)="00"))