Irren ist (nicht nur) menschlich. Oder: Fehlerbehandlung in Excel 19

Artikelbild-307-gross
So werden Fehlerwerte in Excel-Funktionen gezielt abgefangen und sinnvoll behandelt.
 

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

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

Der Fehler verschwindet nur bei gefüllten Zellen

Nun fangen wir den Fehler in einer WENN-Formel ab:

Fehler abfangen mit WENN und ISTFEHLER

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

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

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

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.

 

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

19 Gedanken zu “Irren ist (nicht nur) menschlich. Oder: Fehlerbehandlung in Excel

  • Avatar-Foto
    Der Tourenplaner

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Michael Krüger

    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.

  • Avatar-Foto
    Matthias

    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

  • Avatar-Foto
    Ralf Rothe

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Andreas Unkelbach

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Klaus-Dieter Körwers

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Th. Bauer

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Marcus Tschoepe

    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

    • Avatar-Foto
      Martin Weiß

      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