Eingabe auf zwei Dezimalstellen beschränken

Preise, Stundensätze oder Beträge sollen mit höchstens zwei Nachkommastellen erfasst werden. Statt die Eingabe später zu runden, lässt die Datenüberprüfung von vornherein nur passende Werte zu.

Die Kurzlösung

  1. Zellen markieren, etwa B5:B100. B5 muss die aktive Zelle sein.
  2. Daten > Datenüberprüfung > Zulassen: Benutzerdefiniert.
  3. Formel:
=RUNDEN(B5;2)=B5
  1. Auf der Registerkarte Fehlermeldung einen Text eintragen, etwa „Bitte höchstens zwei Nachkommastellen eingeben.“

Die Formel prüft, ob die Zahl nach dem Runden auf zwei Stellen unverändert bleibt. Das trifft genau dann zu, wenn sie höchstens zwei Nachkommastellen hat. Für eine Stelle ersetzen Sie die 2 durch 1, für ganze Zahlen durch 0.

Warum nicht REST

Häufig wird diese Formel empfohlen:

=REST(B5*10^2;1)=0

Sie sieht logisch aus: Mal 100, und es darf kein Rest bleiben. Doch Excel speichert Dezimalzahlen binär, und viele scheinbar glatte Werte sind intern nicht exakt. 1,15 mal 100 ergibt intern 114,99999999999999. Ein Beispiel:

EingabeREST-FormelRUNDEN-Formel
1,23erlaubterlaubt
1,15abgelehnterlaubt
0,29abgelehnterlaubt
4,35abgelehnterlaubt
1,234abgelehntabgelehnt
2,675abgelehntabgelehnt

Die REST-Formel weist also gültige Beträge zurück, was Anwender zu Recht verärgert. RUNDEN vergleicht mit derselben internen Darstellung und hat das Problem nicht.

Per VBA einrichten

Application.Goto ThisWorkbook.Worksheets("Eingabe").Range("B5")   ' relativer Bezug gilt ab der aktiven Zelle
With ThisWorkbook.Worksheets("Eingabe").Range("B5:B100").Validation
    .Delete
    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=ROUND(B5,2)=B5"
    .ErrorMessage = "Bitte höchstens zwei Nachkommastellen eingeben."
End With

Validation.Add nimmt die Formel nur in englischer Schreibweise mit Komma an. In der Oberfläche erscheint sie danach wieder deutsch als =RUNDEN(B5;2)=B5.

Typische Fehler

  • Falsche Bezugszelle: Die Formel bezieht sich auf die aktive Zelle der Markierung. Ist beim Einrichten nicht B5 aktiv, prüft Excel versetzte Zellen.
  • Einfügen umgeht die Prüfung: Werte, die per Kopieren und Einfügen in die Zellen kommen, prüft die Datenüberprüfung nicht. Mit Daten > Datenüberprüfung > Ungültige Daten einkreisen finden Sie solche Werte nachträglich.
  • Nur die Anzeige gerundet: Ein Zahlenformat mit zwei Stellen zeigt 1,234 als 1,23 an, rechnet aber mit 1,234 weiter. Die Datenüberprüfung verhindert das.

Passende Beiträge

Lernen oder machen lassen?

Excel-Formeln, die im Alltag sitzen.

In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.