Fehlerwerte
Bedeutung, Vermeidung, Unterdrückung und positiver Nutzen bei Diagrammen

Mit dem Zeichen # zeigt Excel Fehlerwerte von Formelergebnissen an und diese sind auch in der Excel-Hilfe (F1) aufgeführt, aber nicht ihre Ursachen, Wege zur Unterdrückung oder Möglichkeiten, um sie positiv zu nutzen. Nachstehend dazu folgende Unterthemen:

• Fehlerausgabe und Fehlerzuordnung
• Fehlerwerte unsichtbar machen (auf dem Bildschirm und im Ausdruck)
• Fehlerwerte auf dem Bildschirm anzeigen aber nicht drucken
• ein Ausflug zu Excelformeln.de: Fehlerwerte suchen und zählen
• positiver Nutzen vom Fehlerwert #NV für Diagramme / Wie erreicht man das Gleiche unter Excel 2007?

Hinweis: Die englischen Bezeichnungen findet man hier: Fehlerwerte im Code - VBA spricht Englisch!

Zum Blattanfang

Fehlerausgabe und Fehlerzuordnung

An der Art des Fehlers können wir das vorliegende Problem eingrenzen und entsprechend darauf reagieren, indem wir die Formel oder die Bezugszellen entsprechend korrigieren.

mögliche Fehlerwerte: Fehlerwert #NULL! #DIV/0! #WERT! #BEZUG! #NAME? #ZAHL! #NV erscheint wenn Sie eine Schnittmenge von zwei Bereichen angeben, die sich nicht überschneiden bei Division durch 0 oder Leerzelle, weil man nicht durch 0 dividieren kann wenn der Bereich einen Text enthält oder einen Punkt eingegeben hat, anstatt einem Komma wenn Zellen gelöscht wurden, auf die sich die Formel bezieht wenn eine Funktion nicht korrekt geschrieben wurde oder sie unzulässige Argumente zwischen den Klammern enthält wenn ein Problem mit einer Zahl in einer Formel oder in einer Funktion aufgetreten ist. Sie haben ein unzulässiges Argument in einer Funktion verwendet, die ein numerisches Argument erfordert wenn ein Wert in einer Funktion oder Formel nicht verfügbar ist Rückgabe von FEHLER.TYP 1 2 3 4 5 6 7

FEHLER.TYP können sie in Formeln einbinden, um gezielt einen bestimmten Fehler zu suchen oder zu zählen.
Beispiel siehe: Excelformeln.de: Fehlerwerte suchen und zählen.

Zum Blattanfang

Fehlerwerte unsichtbar machen (auf dem Bildschirm und im Ausdruck)

Manchmal sollen aber auch Formeln, die Fehlerwerte ergeben, in Zellen erhalten bleiben, damit sie Ergebnisse anzeigen, wenn die Umstände in den Vorgängerzellen sich ändern. Aber: Solange sie nur Fehlerwerte ausgeben, sollen die Zellen leer erscheinen. Das können wir auf verschiedenen Wegen erreichen:


Variante 1: Durch bedingte Formatierung

Wenn sie die Schriftfarbe entsprechend der Hintergrundfarbe einstellen bei Fehlerwert, wird der Fehler unsichtbar, siehe Zellen, die Fehlerwerte enthalten, farblich unterlegen


Variante 2: Durch Formelerweiterung

Ausgehend von der Tatsache, dass die Zellen ja sowieso schon Formeln enthalten, ist diese Vorgehensweise nahe liegender.

Anmerkung:
Das "Leer" erzielt man durch "" (doppelte Anführungszeichen) in der Formel.

Die gängigen Formeln (siehe Folgebeispiele) prüfen

• entweder, ob bestimmte Zellen leer sind und dadurch ein Fehler erzeugt wird
• ob der Suchwert im durchsuchten Bereich vorkommt. Dies geht mit der Formel =WENN(ISTFEHLER
(VERGLEICH...
• oder, ob die Formel einen Fehlerwert erzeugt. Und dies mit der Formel =WENN(ISTFEHLER.... Dabei wird die Formel, die den Fehler erzeugt, praktisch wiederholt
• empfehlenswert ist der Einsatz von ZÄHLENWENN gem. Beispiel, da ZÄHLENWENN eine sehr schnelle Funktion ist

A B C D E F 1 2 Unterdrückung von #DIV/0! 3 Gesamtpreis Menge Einzelpreis Bemerkung 4 18,00 3 6,00 Ist Spalte C ausgefüllt, bringt die Formel die gewünschte Lösung 5 18,00 #DIV/0! solange in Spalte C kein Wert <> 0 steht bzw. diese leer ist, ergibt die Formel in Spalte D diesen Fehlerwert 6 18,00 Hier wird die Fehlerausgabe unterdrückt durch Prüfung, ob Zelle C5 leer ist 7 18,00 Hier wird die Fehlerausgabe unterdrückt durch Prüfung, ob die Formel einen Fehlerwert ausgibt 8 Formeln der Tabelle D4 : =B4/C4 D5 : =B5/C5 D6 : =WENN(C6="";"";B6/C6) D7 : =WENN(ISTFEHLER(B7/C7);"";B7/C7)

A B C D E F G 1 2 Unterdrückung von #NV 3 Suchwert Formelergebnis Bemerkung Suchbereich 4 2 8 Solange der Suchwert aus Spalte B im Suchbereich G4:G6, vorkommt, bringt die Formel ein Ergebnis 1 7 5 8 #NV Der Suchwert aus Spalte B kommt nicht im Suchbereich G4:G6 vor, es erscheint die Fehlermeldung #NV 2 8 6 8 Hier wird die Fehlerausgabe unterdrückt durch Prüfung, ob die Formel einen Fehlerwert ausgibt, wenn der Suchwert im Suchbereich nicht exisitiert; diese Formel mit VERGLEICH kann man in solchen Fällen allgemeingültig eingesetzen 3 9 7 8 Hier wird die Fehlerausgabe unterdrückt durch Prüfung, ob die Formel einen Fehlerwert ausgibt, =WENN(ISTFEHLER.... Dabei wird die Formel, die den Fehler erzeugt, praktisch wiederholt; Wenn die Formel sehr lang ist, greift man besser zur Lösung mit VERGLEICH (siehe Zeile 6) 8 8 Eine schnelle Variante der Fehlerunterdrückung sehen Sie in Zelle C8. Mit ZÄHLENWENN wird festgestellt, ob der Suchwert im Suchbereich vorhanden ist. 9 Formeln der Tabelle C4 : =SVERWEIS(B4;F4:G6;2;0) C5 : =SVERWEIS(B5;F4:G6;2;0) C6 : =WENN(ISTFEHLER(VERGLEICH(B6;F4:F6;0));"";SVERWEIS(B6;F4:G6;2;0)) C7 : =WENN(ISTFEHLER(SVERWEIS(B7;F4:G6;2;0));"";SVERWEIS(B7;F4:G6;2;0)) C8 : =WENN(ZÄHLENWENN(F4:F6;F8);SVERWEIS(B8;F4:G6;2;0);"")

Zum Blattanfang

Fehlerwerte auf dem Bildschirm anzeigen aber nicht drucken

Ab Excel 2002 können Sie beim Drucken bestimmen, ob bzw. in welcher Form Fehlerwerte dargestellt werden. Unter Menü>Datei>Seite einrichten>Tabelle, Fehlerwerte als, gibt es diesbezüglich vier Varianten zur Auswahl: Dargestellt, leer, -- und #NV. Die Bildschirmanzeige wird von dieser Einstellung nicht beeinflusst.

Zum Blattanfang

Positiver Nutzen vom Fehlerwert #NV für Diagramme

Mit dem Fehlerwert #NV bietet sich die Möglichkeit, Linienbrüche zu vermeiden, wenn Leerzellen in der Datenquelle des Diagramms sind.

Beispiel: Stellen Sie sich vor, dies wäre Ihre Datenquelle für ein Liniendiagramm:

A B C 1 2 Datenquelle für ein Diagramm 3 20 4 15 5 6 6 7 20 8

In B5 ist kein Wert enhalten, an der Stelle unterbricht Excel je nach Diagrammtyp nun die Linie oder stürzt auf 0. Um dies zu umgehen, können Sie mit einer Hilfsspalte arbeiten. Die Hilfsspalte kann ausgeblendet sein, wird aber als Datenquelle fürs Diagramm genutzt - die Formel aus C3 kann runterkopiert werden:

A B C D 1 2 Ausgangsdaten Hilfsspalte nun Datenquelle 3 20 20 4 15 15 5 #NV 6 6 6 7 20 20 8 Formeln der Tabelle C3 : =WENN(B3="";#NV;B3)

Durch den Fehlerwert #NV wird erreicht, dass Excel die Linie verbindet ohne auf 0 zu stürzen. Wir erreichen praktisch den gleichen Effekt, als hätten wir die Ausgangstabelle nach Nichtleeren gefiltert.

Ab Excel 2007 ist der "Trick" mit dem #NV nicht mehr notwendig:

Gehen Sie dort wie folgt vor:

• Markieren Sie das Liniendiagramm
• In der Multifunktionsleiste erscheint jetzt die Registerkarte Diagrammtools und darunter zusätzliche Registerkarten
• Wechseln Sie auf die Registerkarte Entwurf
• Klicken Sie in der Gruppe Daten auf das Symbol Daten auswählen
• In dem nun angezeigten Dialogfeld klicken Sie auf das Symbol Ausgeblendete und leere Zellen
• Wählen Sie die Option Datenpunkte mit einer Linie verbinden und bestätigen die Auswahl mit OK
• Schließen Sie das Dialogfeld Datenquelle auswählen mit OK

Danke an MVP Andreas Entenmann für diesen Hinweis!