Welcher Excel-Anwender ist nicht schon mal auf eine Formel gestoßen, die nur ein unschönes „#NV“, „DIV/0!“ oder „#WERT!“ anzeigt.
Es hat natürlich grundsätzlich schon seine Richtigkeit, wenn eine Formel aufgrund von falschen oder fehlenden Eingaben kein vernünftiges Ergebnis liefern kann und stattdessen eine dieser Fehlermeldungen ausgibt. Aber es sieht eben nicht besonders schön aus und verwirrt weniger versierte Anwender unnötig.
Noch problematischer ist es, wenn man beispielsweise mit einer SUMME-Funktion auf einen Datenbereich zugreift, der solche Fehlerwerte enthält. Denn dann liefert auch die SUMME (oder andere Formeln) wieder nur einen Fehler.
Excel bietet verschiedene Funktionen zur Fehlerbehandlung an, von denen ich an dieser Stelle die folgenden vorstellen möchte:
- ISTFEHLER
- WENNFEHLER
- FEHLER.TYP
Und so geht’s:
ISTFEHLER
Vereinfacht gesagt prüft diese Funktion, ob eine Formel einen Fehlerwert ergibt (#DIV/0!, #NV, #NAME?, #WERT!, #BEZUG!, #NULL!). Wenn ja, dann liefert die ISTFEHLER()-Funktion den logischen Wert „WAHR“ und ansonsten (also bei keinem Fehler) den Wert „FALSCH“ zurück. Das können wir uns nun zunutze machen und eine etwaige Fehlermeldung einfach unterdrücken.
In meiner Beispieltabelle möchte ich eine Auswertung der durchschnittlichen Krankheitstage in meiner Belegschaft vornehmen. Solange die Tabelle noch nicht oder nicht vollständig befüllt ist, kommt es in den Berechnungsformeln zu den unschönen „#DIV/0!“-Meldungen:
Division durch Null führt zu einem Fehler
Sobald ich die ersten Werte eingebe, verschwinden die Fehler zwar in den befüllten Zeilen, der Rest bleibt aber weiterhin erhalten:
Der Fehler verschwindet nur bei gefüllten Zellen
Nun fangen wir den Fehler in einer WENN-Formel ab:
Fehler abfangen mit WENN und ISTFEHLER
Im Klartext:
WENN die Funktion ISTFEHLER() wahr ist (das heißt, ein Fehler wurde gefunden), DANN gib stattdessen eine Null aus.
ANSONSTEN führe die Berechnung durch (D3/C3).
Sieht doch gleich viel besser aus!
WENNFEHLER
Noch geschmeidiger geht es mit der Funktion WENNFEHLER, denn die kombiniert die beiden zuvor beschriebenen Funktionen WENN und ISTFEHLER in einer einzigen Formel.
Die allgemeine Syntax lautet:
=WENNFEHLER(Wert; Wert falls Fehler)
Im ersten Argument gibt man also die Berechnung an, die man überprüfen lassen möchte. Wenn die Prüfung zu keinem Fehler führt, dann wird das Ergebnis auch direkt ausgegeben. Ansonsten wird eben das ausgegeben, was mit dem zweiten Argument festgelegt wurde.
Bleiben wir bei dem Beispiel von oben. Anstatt
=WENN(ISTFEHLER(C3/B3);0;C3/B3)
wäre jetzt die verkürzte Version
=WENNFEHLER(C3/B3;0)
Eleganter geht es mit WENNFEHLER
Wie man sieht, ist diese Variante deutlich kürzer und besser lesbar. Vor allem bei komplexeren Berechnungen mit langen Formeln macht sich das positiv in der Lesbarkeit bemerkbar. Außerdem dürfte die Performance besser sein, da die Berechnung in jedem Fall nur noch einmal ausgeführt werden muss.
Anstelle einer Null könnte natürlich auch ein Hinweistext angegeben werden, der dann in Anführungszeichen gesetzt werden muss:
=WENNFEHLER(C3/B3;"Division durch Null")
FEHLER.TYP
Da jeder Fehler eine Excel-interne Fehlernummer hat, kann man dieses Wissen nutzen, um dem Anwender gezielte Rückmeldungen zu geben. Die Funktion FEHLER.TYP() liefert mir genau diesen numerischen Wert. In der folgenden Übersicht habe ich in Spalte A die verschiedenen Fehler aufgelistet. In Spalte B steht der von der Funktion FEHLER.TYP() ermittelte numerische Wert:
Liste der Fehlertypen mit Ursache
Nun habe ich meine Krankheitstabelle aus dem vorigen Beispiel etwas „aufgebohrt“. In der Spalte E lasse ich mir von FEHLER.TYP() den jeweiligen Fehlerwert aus der Spalte D berechnen. Und über eine SVERWEIS()-Funktion in Spalte F gebe ich zu diesem Fehlerwert nun einen etwas aussagefähigeren Hinweistext aus:
Hinweistexte aus einer Referenztabelle
Noch eine kurze Erläuterung der Formeln:
=WENNFEHLER(FEHLER.TYP(D4);"-")
Wenn also in Zelle E4 ein Fehlerwert enthalten ist, dann wird die Fehlertyp-Nummer zurückgeliefert. Wenn nicht, dann soll einfach ein Bindestrich ausgegeben werden.
=SVERWEIS(E4;$E$18:$F$25;2;FALSCH)
Hier nehme ich den in Zelle E4 ermittelten Fehlerwert und suche in meiner Referenztabelle E18:F25 den Hinweistext heraus, der sich in der zweiten Spalte befindet.
Natürlich lassen sich die beiden Formeln in den Spalten F und G auch zusammenfassen. Ich habe sie hier nur zum besseren Verständnis getrennt.
Damit hat man nun alle Möglichkeiten an der Hand, die etwas kryptischen Standardfehlermeldungen abzufangen und entweder zu unterdrücken oder durch sprechende Hinweise zu ersetzen.
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.
Pingback: Mehr Infos, bitte! Informationsfunktionen in Excel | Der Tabellen-Experte
Danke für die informative Übersicht! Kleine Ergänzung: Ein Fehlerwert, der jetzt mit den neuen Funktionen wie Xverweis, Filter etc. aufgekommen ist, fehlt in der Auflistung leider:
#ÜBERLAUF!
Dieser Fehler entsteht, wenn die Formel eine Matrix zurückgibt, die so groß ist, dass sie auch bereits befüllte Zellen überschreiben würde. Ist das so verständlich? Also wenn der XVerweis z. B. drei Spalten zurückgebe würde, aber neben der Spalte mit der Formel ist nur eine Spalte frei, dann wird in der Spalte mit der Formel nur dieser Fehlerwert Wert zurückgegeben. Hat mich bei ersten Mal etwas Zeig gekostet, bis ich es geblickt habe
Hallo Tourenplaner,
vielen Dank für diese gute und richtige Ergänzung. Und es stimmt, wenn man zum ersten Mal mit #ÜBERLAUF! konfrontiert wird, dann zuckt man erst einmal zurück.
Schöne Grüße,
Martin
Sehr schön, vielen Dank! Das wird sofort in mein aktives Repertoire übernommen. Die Fehlertypen haben mich in der Tat schon oft verwirrt, auch weil sie sich dann aus der ursprünglich falschen Zelle in Weiterberechnungen und Übersichten durchziehen können.
Sehr gern geschehen!
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die Übersicht der Fehlerbehandlung. Sehr hilfreich 😉
Zwei Anmerkungen zum Thema „Fehler.Typ“:
1. Bei Typ 6 hast du geschrieben, Zahl zu klein bzw. zu groß für Excel und dann -1^307 bzw. 1^307. Mathematisch ist das aber wieder -1 bzw. 1… Wo liegen denn die Grenzen für Excel?
2. Bei der Erklärung der Formel hast du ein E4 zu viel. =WENNFEHLER(E4;FEHLER.TYP(E4);“-„) müsste IMHO =WENNFEHLER(FEHLER.TYP(E4);“-„) heißen.
Gruß,
Matthias
Hallo Matthias,
vielen Dank für den Hinweis auf den Erklärungsfehler, den habe ich jetzt im Artikel gleich korrigiert.
Die Grenzen von Excel kannst du auf dieser Seite bei Microsoft nachlesen:
Spezifikationen und Beschränkungen in Excel
Schöne Grüße,
Martin
Hallo,
Ihre Hinweise lese ich immer und sie haben mir an vielen Stellen auch geholfen. Die Funktion „WENNFEHLER“ hatte ich bisher nicht genutzt.
In diesem Zusammenhang eine Frage, ich arbeite oft mit Diagrammen und da stört es sehr, wenn dort, wo Fehlermeldungen auftreten, in den Grafiken die Kurve auf Null gezeichnet wird (auch, wenn im Feld „“ steht. Ich helfe mir dann, indem ich dort den Wert NV() setzen lasse. Dann sind die Kurven zwar okay, aber Summen oder Mittelwerte lassen sich nicht mehr berechnen, zumindest nicht mehr so einfach.
Haben Sie auch für dieses Problem einen Tipp?
Mit freundlichen Grüßen
Ralf Rothe
Hallo Herr Rothe,
das stimmt, Funktionen wie SUMME oder MITTELWERT können mit Fehlerwerten nicht umgehen. Für diese Fälle empfehle ich Ihnen die AGGREGAT-Funktion. Die vereinigt eine ganze Reihe anderer Funktionen und kann obendrein Fehlerwerte ignorieren.
Hier ein Blogartikel dazu: Viele Funktionen in einer: AGGREGAT
Schöne Grüße,
Martin
Hallo Martin,
kennst du die Gegenüberstellung „die üblichen Verdächtigen…“ von Excel Nova auf
excelnova.org/excel-formel-fehler-erklart-und-loesung/? Daran musste ich gerade dran denken und bin danach sehr begeistert von deiner Lösung zur Erläuterung zur jeweiligen Fehlermeldung per FEHLER.TYP. Bisher nutze ich die Funktion WENNFEHLER immer, um hier einen 0 Wert bzw. „“ auszugeben, auf die Idee unterstützend einzugreifen und Anwendende auch auf ihre Fehler hinzuweisen bin ich bisher noch nicht gekommen, überlege mir dieses aber für die Zukunft.Insgesamt fällt mir bei deinen Artikeln immer wieder positiv auf, dass du diese sowohl aus Sicht Excel-Experten als auch aus Sicht der Anwendende gestaltet und hier ein Werkzeug für beide Zielgruppen angewendet und sinnvoll eingesetzt wird.
Vielen Dank dafür, dass du hier Anregungen bietest und ich merke an meinen eigenen Tabellen immer stärker, dass diese auch mehr und mehr auf die Zielgruppe orientiert ist.
Viele Grüße
Andreas
Hallo Andreas,
nein, diese Gegenüberstellung bei Excel Nova kannte ich noch nicht. Vielen Dank für diese hilfreiche Ergänzung! Leider hat der Websitebetreiber Lukas Rohr ja kürzlich den „Betrieb“ eingestellt, aber ich hoffe, dass die Seiten noch eine Weile abrufbar bleiben.
Es kommt wie immer darauf an, was man genau bezwecken möchte. Auch ich beschränke mich in aller Regel auf die WENNFEHLER-Funktion. Aber in bestimmten Fällen kann die konkrete Behandlung über FEHLER.TYP ja doch mal hilfreich sein.
Schöne Grüße,
Martin
Klasse! Wie so oft superhilfreich, Martin
Hi Kathrin,
vielen Dank, freut mich zu hören!
Schöne Grüße,
Martin
Hallo Martin,
danke für alle deine Hinweise, fürmich meistens sehr nützlich. Dieser hier interessierte mich besonders, daher habe ich ihn – zum besseren Mehren – mal nachgebildet und bin (vielleicht) auf einen Fehler in deiner Darstellung gestoßen:
In der oben nochmals zitierten Formel „=SVERWEIS(F4;$F$18:$G$25;2;FALSCH)“ muss es bezogen auf die Tabelle wohl „E4“ statt „F4“ heißen… – oder irre ich?
VG
Hallo Klaus-Dieter,
vielen Dank für den berechtigten Hinweis. Meine Formel passt nicht zum Bild, das habe ich jetzt korrigiert.
Schöne Grüße,
Martin
Passt vielleicht nicht ganz hierein, ist aber mein „Excel-Fehlerproblem“
Wenn ich in einer Zelle mit der Formel „NETTOARBEITSTAGE.INTL“. arbeite, so wird die Zelle auch ohne Datumsbezug bei manchen Wochenarbeitskonstellationen mit einer „1“ statt mit „0“ befüllt, was dazu führt, dass beim integrierten Urlaubstagerechner auch diese „1“ als Urlaubstag mitgerechnet wird. Bin schon tagelang dran das zu beheben.
Leider ohne Erfolg
Bsp: =NETTOARBEITSTAGE.INTL(NK14;NL14;“0001111″;Feiertage!$D$5:$D$20) ist gleich 3-Tagewoche, hier wird der Wert korrekt mit „0“ angezeigt. =NETTOARBEITSTAGE.INTL(NK12;NL12;“0100000″;Feiertage!$D$5:$D$20) ist gleich 6-Tagewoche hier wird der Wert mit „1“ angezeigt obwohl noch kein Datumsbezug vorhanden ist? Wissen Sie Rat?
Freundlich Th. Bauer
Im Moment kann ich noch nicht ganz folgen, was Sie mit 1 oder 0 als Ergebnis meinen. Die Funktion liefert ja die Anzahl der Arbeitstage zurück, und nicht nur den Wert 1 oder 0. Oder habe ich Sie hier missverstanden?
Schöne Grüße,
Martin
Hallo Martin,
der #BEZUG! Fehler der entsteht, wenn Zellen, die in einer Formel referenziert werden durch den User gelöscht werden ist mein Problem. Ich möchte verhindern, dass eine Arbeitsmappe vom User abgespeichert werden kann, sofern ein #BEZUG! Fehler vorliegt.
Da mit dem Abspeichern mit dem #BEZUG! Fehler an der Stelle der Bezug oftmals nicht ohne erheblichen Aufwand „wiedergefunden“ werden kann.
Ein Pop-Up Hinweis „kann nicht abgespeichert werden“ da ein #BEZUG! Fehler vorliegt, o.ä. wäre toll. Gibt es so etwas?
Gruß, Marcus
Hallo Marcus,
nein, eine Funktion, die in solchen Fällen das Speichern verhindern würde, gibt es leider nicht in Excel. So etwas müsste man mit VBA programmieren.
Schöne Grüße,
Martin