Beitrag aus SmartTools Excel Weekly
Anzeige von 0 als Formelergebnis in Excel-Tabellen unterdrücken
Excel 365 2024 2021 2019 2016 2013
FRAGE Ich verwende eine Formel nach folgendem Muster: =BEREICH.VERSCHIEBEN(<Bezug>;0;5;1;1). Wenn die Zelle, auf die die BEREICH.VERSCHIEBEN-Funktion verweist, leer ist, erscheint als Formelergebnis eine 0. Genau das möchte ich vermeiden. Wenn die Zelle leer ist, soll auch die Zelle mit der Formel leer bleiben. Als "Notlösung" habe ich meine Formel in eine Abfrage mit der ISTLEER-Funktion eingebettet. Das funktioniert zwar, ist aber sehr fehleranfällig, weil <Bezug> ein Verweis auf ein anderes Tabellenblatt mit einem längeren Namen ist. Da ich den BEREICH.VERSCHIEBEN-Teil zweimal in die Gesamtformel eingeben muss, sind mir schon häufig Tippfehler unterlaufen. Kennen Sie einen einfacheren Weg, die Anzeige von 0-Werten zu unterdrücken?
P. Mingst
ANTWORT Wie wäre ein ganz anderer Weg: Behalten Sie Ihre Originalformel bei und setzen Sie anstelle einer komplexen Formelverschachtelung ein benutzerdefiniertes Zahlenformat ein, das Nullwerte prinzipiell unterdrückt. Zugegeben, das hat den Nachteil, dass auch Zellen leer bleiben, wenn Ihre BEREICH.VERSCHIEBEN-Formeln tatsächlich den Wert 0 liefern. Aber wenn diese Einschränkung akzeptabel ist, ist ein benutzerdefiniertes Zahlenformat eine interessante Alternative. Sie setzen es folgendermaßen ein:
- Markieren Sie die Zellen, in denen Ihre BEREICH.VERSCHIEBEN-Formeln stehen.
- Drücken Sie Strg + 1, um das Dialogfenster Zellen formatieren zu öffnen.
- Auf der Registerkarte Zahlen markieren Sie die Kategorie "Benutzerdefiniert".
- In das Textfeld unter Typ geben Sie folgendes benutzerdefiniertes Zahlenformat ein:
Standard;-Standard;
- Klicken Sie auf Ok.
Zur Erklärung: In einem benutzerdefinierten Zahlenformat können Sie die Anzeige verschiedener Werte festlegen:
- positive Werte
- negative Werte
- Nullwerte
- Textwerte
Die einzelnen Formatdefinitionen trennen Sie durch Semikolons. Im oben genannten Fall legen Sie beispielsweise fest, dass positive Werte in der Standardformatierung von Excel dargestellt werden, negative Werte in derselben Form, nur mit einem negativen Vorzeichen, und Nullwerte ausgeblendet bzw. leer bleiben. Für Textwerte wurde kein spezielles Format definiert.
Sie können den Zellen natürlich auch jedes andere gültige Zahlenformat zuweisen. Zum Unterdrücken von Nullwerten muss nur der dritte Parameter leer sein. Wenn Sie die Formelergebnisse zum Beispiel im Währungsformat ausgeben und im Falle eines Textwerts das Ergebnis rot einfärben wollen, definieren Sie folgendes Zahlenformat:
#.##0,00 €;-#.##0,00 €;;[Rot]@