Ermitteln von Fehlern in Formeln

In der folgenden Tabelle sind einige der häufigsten Fehler zusammengefasst, die ein Benutzer beim Eingeben einer Formel machen kann. Außerdem wird erläutert, wie Sie diese beheben:

Sie können bestimmte Regeln implementieren, um Formeln auf Fehler zu überprüfen. Diese Regeln garantieren nicht, dass Ihr Arbeitsblatt fehlerfrei ist, sie können jedoch sehr hilfreich sein, um häufig auftretende Fehler zu finden. Sie können jede dieser Regeln einzeln aktivieren oder deaktivieren.

Fehler können auf zwei Arten markiert und behoben werden: immer jeweils ein Fehler (wie bei einer Rechtschreibprüfung) oder sofort bei ihrem Auftreten im Arbeitsblatt während der Dateneingabe.

Sie können einen Fehler mithilfe der in Excel angezeigten Optionen beheben, oder sie können den Fehler ignorieren, indem Sie Fehler ignorieren auswählen. Wenn Sie einen Fehler in einer bestimmten Zelle ignorieren, wird der Fehler in dieser Zelle in weiteren Fehlerüberprüfungen nicht mehr angezeigt. Sie können jedoch alle zuvor ignorierten Fehler zurücksetzen, damit sie wieder angezeigt werden.

1. Wechseln Sie für Excel unter Windows zu Datei > Optionen > Formeln, oder
   für Excel auf Mac, wählen sie das Menü Excel > Einstellungen > Fehlerüberprüfung. 

   Wählen Sie in Excel 2007die Schaltfläche Microsoft Office > Excel-Optionen > Formelnaus.

2. Aktivieren Sie unter Fehlerüberprüfung das Kontrollkästchen Fehlerüberprüfung im Hintergrund aktivieren. Jeder gefundene Fehler wird mit einem Dreieck in der oberen linken Ecke der Zelle markiert.

   

3. Zum Ändern der Farbe für das Dreieck, mit dem die Stelle markiert wird, an der ein Fehler auftritt, wählen Sie im Feld Fehler mit der folgenden Farbe kennzeichnen die gewünschte Farbe aus.

4. Aktivieren oder deaktivieren Sie unter Regeln für die Fehlerüberprüfung die Kontrollkästchen für die folgenden Regeln:

   • Zellen mit Formeln, die zu einem Fehler führen: Eine Formel verwendet nicht die erwartete Syntax, die erwarteten Argumente oder Datentypen. Zu den Fehlerwerten gehören #DIV/0!, #N/V, #NAME?, #NULL!, #ZAHL!, #BEZUG! und #WERT!. Jeder dieser Fehlerwerte hat unterschiedliche Ursachen und wird auf unterschiedliche Weise behoben.

     Hinweis: Wenn Sie einen Fehlerwert direkt in eine Zelle eingeben, wird er als dieser Fehlerwert gespeichert, aber nicht als Fehler markiert. Wenn jedoch in einer Formel in einer anderen Zelle auf diese Zelle Bezug genommen wird, gibt die Formel den Fehlerwert aus dieser Zelle zurück.

   • Inkonsistente berechnete Spaltenformel in Tabellen: Eine berechnete Spalte kann einzelne Formeln enthalten, die sich von der Masterspaltenformel unterscheiden, was zu einer Ausnahme führt. Wenn Sie eine der folgenden Aktionen ausführen, werden Ausnahmen für berechnete Spalten erstellt:

     • Geben Sie andere Daten als eine Formel in eine berechnete Spaltenzelle ein.

     • Geben Sie eine Formel in eine berechnete Spaltenzelle ein, und drücken Sie dann STRG+Z, oder wählen Sie Rückgängig auf der Symbolleiste für den Schnellzugriffaus.

     • Geben Sie eine neue Formel in eine berechnete Spalte ein, die bereits eine oder mehrere Ausnahmen enthält.

     • Kopieren Sie Daten in die berechnete Spalte, die nicht mit der Formel der berechneten Spalte übereinstimmen. Wenn die kopierten Daten eine Formel enthalten, werden die Daten in der berechneten Spalte durch diese Formel überschrieben.

     • Verschieben oder löschen Sie eine Zelle in einem anderen Arbeitsblattbereich, auf den eine der Zeilen in einer berechneten Spalte verweist.

   • Zellen, die zweistellige Jahreszahlen enthalten: Die Zelle enthält einen Textdatumswert, bei dem das Jahrhundert falsch interpretiert werden kann, wenn er in Formeln verwendet wird. Beispielsweise könnte es sich bei dem Datum in der Formel =JAHR("1.1.31") um 1931 oder 2031 handeln. Verwenden Sie diese Regel, um Zellen auf zweideutige Textdatumswerte zu überprüfen.

   • Zahlen, die als Text formatiert sind oder denen ein Apostroph vorangestellt ist: Die Zelle enthält Zahlen, die als Text gespeichert sind. Dies ist normalerweise der Fall, wenn Daten aus anderen Quellen importiert werden. Als Text gespeicherte Zahlen können zu unerwarteten Sortierergebnissen führen, und es empfiehlt sich daher, sie in Zahlen zu konvertieren. '=SUMME(A1:A10) wird als Text betrachtet.

   • Formeln inkonsistent mit anderen Formeln im Bereich: Die Formel stimmt nicht mit dem Muster anderer Formeln in der Nähe überein. In vielen Fällen unterscheiden sich Formeln neben anderen Formeln nur in den verwendeten Verweisen. Im folgenden Beispiel mit vier benachbarten Formeln zeigt Excel einen Fehler neben der Formel =SUMME(A10:C10) in Zelle D4 an, da die angrenzenden Formeln um eine Zeile erhöht werden, und diese um 8 Zeilen – Excel erwartet die Formel =SUMME (A4:C4).

     

     Wenn die in einer Formel verwendeten Bezüge nicht mit denen der angrenzenden Formeln konsistent sind, zeigt Excel einen Fehler an.

   • Formeln, die sich nicht auf alle Zellen im Bereich beziehen: Eine Formel enthält möglicherweise nicht automatisch Bezüge auf die Daten, die Sie zwischen dem ursprünglichen Datenbereich und der Zelle einfügen, die die Formel enthält. Mit dieser Regel wird der Bezug in einer Formel mit dem tatsächlichen Zellbereich verglichen, der an die Zelle grenzt, die die Formel enthält. Wenn die angrenzenden Zellen zusätzliche Werte enthalten und nicht leer sind, zeigt Excel neben der Formel einen Fehler an.

     Bei Anwendung dieser Regel fügt Excel beispielsweise einen Fehler neben der Formel =SUMME(D2:D4) ein, da die Zellen D5, D6 und D7 an die Zellen angrenzen, auf die in der Formel verwiesen wird, und die Zelle, die die Formel (D8) enthält, und diese Zellen Daten enthalten, auf die in der Formel verwiesen werden sollte.

     

   • Nicht gesperrte Zellen, die Formeln enthalten: Die Formel ist nicht zu ihrem Schutz gesperrt. Standardmäßig sind alle Zellen in einem Arbeitsblatt gesperrt, sodass sie nicht geändert werden können, wenn das Arbeitsblatt geschützt wird. Dies kann unbeabsichtigte Fehler wie das versehentliche Löschen oder Ändern von Formeln vermeiden. Dieser Fehler gibt an, dass die Zelle auf entsperrt festgelegt, das Blatt jedoch nicht geschützt wurde. Stellen Sie sicher, dass die Zelle nicht gesperrt werden soll.

   • Formeln, die sich auf leere Zellen beziehen: Die Formel enthält einen Bezug auf eine leere Zelle. Dies kann zu unbeabsichtigten Ergebnissen führen, wie es im folgenden Beispiel dargestellt ist.

     Angenommen, Sie möchten den Mittelwert der Zahlen in der folgenden Zellenspalte berechnen. Ist die dritte Zelle leer, wird sie nicht in die Berechnung einbezogen, und das Ergebnis ist 22,75. Enthält die dritte Zelle eine 0, ist das Ergebnis 18,2.

     

   • In eine Tabelle eingegebene Daten sind ungültig: Es ist ein Fehler bei der Gültigkeitsprüfung in einer Tabelle aufgetreten. Prüfen Sie die Einstellung der Gültigkeitsprüfung für die Zelle, indem Sie zur Registerkarte Daten wechseln und in der Gruppe Datentools auf Datenüberprüfung klicken.

1. Wählen Sie das Arbeitsblatt aus, das auf Fehler überprüft werden soll.

2. Wenn das Arbeitsblatt manuell berechnet wird, drücken Sie F9, damit es neu berechnet wird.

   Wenn das Dialogfeld Fehlerüberprüfung nicht angezeigt wird, wählen Sie Formeln> Formelüberwachung > Fehlerüberprüfung.

3. Wenn Sie zuvor Fehler ignoriert haben, können Sie diese Fehler erneut überprüfen. Gehen Sie dazu wie folgt vor: Wechseln sie zu Datei > Optionen > Formeln. Wählen Sie für Excel für Mac das Excel-Menü > Einstellungen > Fehlerüberprüfung aus.

   Wählen Sie im Abschnitt FehlerüberprüfungIgnorierte Fehler zurücksetzen > OK aus.

   

   Hinweis: Durch das Zurücksetzen ignorierter Fehler werden alle Fehler in allen Blättern der aktiven Arbeitsmappe zurückgesetzt.

   Tipp: Es kann hilfreich sein, das Dialogfeld Fehlerüberprüfung direkt unterhalb der Bearbeitungsleiste zu platzieren.

   

4. Wählen Sie eine der Aktionsschaltflächen auf der rechten Seite des Dialogfelds aus. Die verfügbaren Aktionen sind je nach Art des Fehlers verschieden.

5. Wählen Sie Weiter aus.

1. Wählen Sie neben der Zelle Fehlerüberprüfung aus, und wählen Sie dann die gewünschte Option aus. Die verfügbaren Befehle sind je nach Art des Fehlers unterschiedlich, und im ersten Eintrag ist der Fehler beschrieben.

   Wenn SieFehler ignorierenauswählen, wird der Fehler bei jeder aufeinanderfolgenden Überprüfung als ignoriert markiert.

   

Wenn eine Formel ein Ergebnis nicht richtig auswerten kann, zeigt Excel einen Fehlerwert an, beispielsweise #####, #DIV/0!, #N/V, #NAME?, #NULL!, #NUM!, #BEZUG! und #WERT!. Für jeden Fehlertyp gibt es andere Ursachen und Lösungen.

Die folgende Tabelle enthält Links zu Artikeln, in denen diese Fehler detailliert beschrieben werden, sowie eine kurze Beschreibung der ersten Schritte.

Thema	Beschreibung
Korrigieren eines Fehlerwerts vom Typ #####	Dieser Fehler wird in Excel angezeigt, wenn eine Spalte nicht breit genug ist, um den gesamten Inhalt einer Zelle anzuzeigen, oder wenn eine Zelle negative Datums- oder Uhrzeitwerte enthält. Eine Formel, die ein Datum in der Zukunft von einem Datum in der Vergangenheit subtrahiert, also z. B. =15.6.2008-1.7.2008, führt zu einem negativen Datumswert. Tipp: Versuchen Sie, die Breite der Zelle automatisch anzupassen, indem Sie zwischen den Spaltenüberschriften doppelklicken. Wenn ### angezeigt wird, weil Excel nicht alle Zeichen anzeigen kann, wird dies korrigiert.
Korrigieren Sie einen #DIV/0!-Fehler Fehler	Dieser Fehler wird in Excel angezeigt, wenn eine Zahl entweder durch Null (0) oder durch eine Zelle geteilt wird, die keinen Wert enthält. Tipp: Fügen Sie einen Fehlerhandler wie im folgenden Beispiel hinzu, d. h. =WENN(C2;B2/C2;0)
Korrigieren eines Fehlerwerts vom Typ #NV	Dieser Fehler wird in Excel angezeigt, wenn ein Wert für eine Funktion oder Formel nicht verfügbar ist. Wenn Sie eine Funktion wie SVERWEIS verwenden, gibt es für den von Ihnen gesuchten Nachschlagewert eine Übereinstimmung im Nachschlagebereich? Dies ist meistens nicht der Fall. Versuchen Sie es mit WENNFEHLER, um den Fehler #NV zu unterdrücken. Verwenden Sie in diesem Fall folgende Formel: =WENNFEHLER(SVERWEIS(D2;$D$6:$E$8;2;WAHR);0)
Korrigieren Sie einen #NAME?-Fehler Fehler	Dieser Fehler wird angezeigt, wenn Text in einer Formel von Excel nicht erkannt wird. Beispielsweise könnte ein Bereichsname oder der Name einer Funktion falsch geschrieben sein. Hinweis: Wenn Sie eine Funktion verwenden, vergewissern Sie sich, dass der Funktionsname richtig geschrieben ist. In diesem Fall ist SUMME falsch geschrieben. Entfernen Sie das „e“, und Excel korrigiert es.
Korrigieren Sie einen #NULL!-Fehler Fehler	Dieser Fehler wird in Excel angezeigt, wenn Sie eine Schnittmenge von zwei Bereichen angeben, die sich nicht überschneiden. Der Schnittmengen-Operator ist ein Leerzeichen zwischen Bezügen in einer Formel. Hinweis: Stellen Sie sicher, dass Ihre Bereiche ordnungsgemäß getrennt sind – die Bereiche C2:C3 und E4:E6 überschneiden sich nicht, daher wird bei Eingabe der Formel =SUMME(C2:C3 E4:E6) #NULL zurückgegeben! Wenn die von Ihnen angegebene Position vor dem ersten oder nach dem letzten Element im Feld liegt, führt die Formel zu einem Fehler des Typs #BEZUG!. Wenn Sie ein Komma zwischen den Bereichen C und E setzen, wird es korrigiert =SUMME(C2:C3;E4:E6)
Korrigieren einer #ZAHL! Fehler	Dieser Fehler wird in Excel angezeigt, wenn eine Formel oder Funktion ungültige numerische Werte enthält. Verwenden Sie eine Formel, die iteriert, wie IKV oder ZINS? Falls ja, wird der Fehler #NUM! wahrscheinlich angezeigt, weil die Funktion kein Ergebnis findet. Schritte zur Lösung des Problems finden Sie im entsprechenden Hilfethema.
Korrigieren Sie einen „#BEZUG!“-Fehler Fehler	Dieser Fehler wird in Excel angezeigt, wenn ein Zellbezug ungültig ist. Sie haben beispielsweise Zellen gelöscht, auf die von anderen Formeln verwiesen wurde, oder Sie haben Zellen eingefügt, die Sie über Zellen verschoben haben, auf die von anderen Formeln verwiesen wurde. Haben Sie versehentlich eine Zeile oder Spalte gelöscht? In dieser Formel =SUMME(A2;B2;C2) haben wir Spalte B gelöscht. Mal sehen, was passiert. Verwenden Sie entweder Rückgängig (STRG+Z) um die Löschung rückgängig zu machen, erstellen Sie die Formel neu, oder verwenden Sie einen zusammenhängenden Bereichsbezug wie =SUMME(A2:C2), wenn Spalte B gelöscht wurde.
Korrigieren Sie einen „#WERT!“-Fehler Fehler	Excel kann diese Fehlermeldung anzeigen, wenn Ihre Formel Zellen mit unterschiedlichen Datentypen enthält. Verwenden Sie mathematische Operatoren (+, -, *, /, ^) mit unterschiedlichen Datentypen? Falls ja, versuchen Sie stattdessen mit einer Funktion. In diesen Fall würde =SUMME(F2:F5) das Problem beheben.

Wenn Zellen auf einem Tabellenblatt nicht angezeigt werden, können Sie diese Zellen und deren Formeln in der Symbolleiste des Überwachungsfensters ansehen. Mit dem Überwachungsfenster können Sie Berechnungen von Formeln und Ergebnisse in umfangreichen Arbeitsblättern bequem überprüfen. Wenn Sie das Überwachungsfenster verwenden, müssen Sie nicht ständig blättern oder zu unterschiedlichen Teilen des Arbeitsblatts wechseln.

Diese Symbolleiste kann wie jede andere Symbolleiste verschoben oder verankert werden. Beispielsweise können Sie sie am unteren Rand des Dialogfelds verankern. Die Symbolleiste verfolgt die folgenden Zelleigenschaften nach: 1) Arbeitsmappe, 2) Blatt, 3) Name (wenn die Zelle einen entsprechenden benannten Bereich aufweist), 4) Zellenadresse, 5) Wert und 6) Formel.

Hinzufügen von Zellen zum Überwachungsfenster

1. Markieren Sie die Zellen, die Sie überwachen möchten.

   Um alle Zellen in einem Arbeitsblatt mit Formeln auszuwählen, gehen Sie zu Startseite > Bearbeiten > wählen Sie Suchen und Auswählen (oder Sie können Strg+G oder Strg+G auf dem Mac verwenden)> Wechseln Sie zu Spezielle > Formeln.

   

2. Wechseln Sie zu Formeln > Formelüberwachung > wählen Sie Überwachungsfenster aus.

3. Wählen Sie Überwachung hinzufügen aus.

   

4. Vergewissern Sie sich, dass Sie alle Zellen ausgewählt haben, die Sie ansehen möchten, und wählen Sie Hinzufügen aus.

   

5. Wenn Sie die Breite eines Überwachungsfensters ändern möchten, ziehen Sie die Begrenzungslinie auf der rechten Seite der Spaltenüberschrift.

6. Wenn Sie die Zelle anzeigen möchten, auf die sich ein Eintrag im Überwachungsfenster bezieht, doppelklicken Sie auf den Eintrag.

   Hinweis: Zellen mit externen Bezügen zu anderen Arbeitsmappen werden nur dann auf der Symbolleiste "Überwachungsfenster" angezeigt, wenn die anderen Arbeitsmappen geöffnet sind.

Entfernen von Zellen aus dem Überwachungsfenster

1. Wenn die Symbolleiste des Überwachungsfensters nicht angezeigt wird, wechseln Sie zu Formeln > Formelüberwachung > wählen Sie Überwachungsfenster aus.

2. Markieren Sie die Zellen, die Sie entfernen möchten.

   Um mehrere Zellen auszuwählen, drücken Sie STRG, und wählen Sie dann die Zellen.

3. Wählen Sie Überwachung löschen aus.

   

Manchmal lässt es sich nur schwer nachvollziehen, wie eine geschachtelte Formel das Endergebnis berechnet, da mehrere Zwischenberechnungen und Wahrheitsprüfungen durchgeführt werden. Mithilfe des Dialogfelds Formel auswerten können Sie jedoch die verschiedenen Teile einer geschachtelten Formel in der Reihenfolge auswerten, in der die Formel berechnet wird. Beispielsweise ist die Formel =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) einfacher zu verstehen, wenn die folgenden Zwischenergebnisse angezeigt werden:

1. Markieren Sie die Zelle, die Sie auswerten möchten. Es kann immer nur jeweils eine Zelle ausgewertet werden.

2. Wechseln Sie zu Formeln> Formelüberwachung > Formel auswerten.

3. Wählen Sie Auswerten aus, um den Wert des unterstrichenen Bezugs zu untersuchen. Das Ergebnis der Auswertung wird kursiv dargestellt.

   Wenn es sich bei dem unterstrichenen Teil der Formel um einen Verweis auf eine andere Formel handelt, wählen Sie Schritt in aus, um die andere Formel im Feld Auswertung anzuzeigen. Wählen Sie die Option Rückschritt aus, um zur vorherigen Zelle und Formel zurückzukehren.

   Die Schaltfläche Einzelschritt ist nicht für einen Bezug verfügbar, wenn der Bezug zum zweiten Mal in der Formel auftritt oder wenn sich die Formel auf eine Zelle in einer anderen Arbeitsmappe bezieht.

4. Fahren Sie mit der Auswahl vonAuswerten fort, bis jeder Teil der Formel ausgewertet wurde.

5. Um die Auswertung erneut anzuzeigen, wählen Sie Neustartaus.

6. Um die Auswertung zu beenden, wählen Sie Schließenaus.