Vermeiden defekter Formeln in Excel

Wenn Excel eine von Ihnen erstellte Formel nicht auflösen kann, erhalten Sie möglicherweise eine Fehlermeldung wie die folgende:

Leider bedeutet dies, dass Excel nicht verstehen kann, was Sie tun möchten. Sie müssen also Ihre Formel aktualisieren oder sicherstellen, dass Sie die Funktion richtig verwenden.

Kehren Sie zu der Zelle mit der fehlerhaften Formel zurück, die sich im Bearbeitungsmodus befindet, und Excel markiert die Stelle, an der das Problem auftritt. Wenn Sie immer noch nicht wissen, was Sie tun sollen und von vorne beginnen möchten, können Sie erneut ESC drücken oder die Schaltfläche Abbrechen in der Symbolleiste auswählen, die Sie aus dem Bearbeitungsmodus herausführt.

Wenn Sie weitermachen wollen, finden Sie in der folgenden Checkliste Schritte zur Problembehandlung, mit denen Sie herausfinden können, was schief gelaufen sein könnte. Wählen Sie die Überschriften aus, um weitere Informationen zu erhalten.

Verwenden Sie die richtigen Listentrennzeichen in der Formel?

Formeln mit mehr als einem Argument verwenden Listentrennzeichen, um ihre Argumente zu trennen. Welches Trennzeichen verwendet wird, kann je nach Betriebssystemgebietsschema und Excel-Einstellungen variieren. Die am häufigsten verwendeten Listentrennzeichen sind das Komma "," und das Semikolon ";".

Eine Formel wird unterbrochen, wenn eine ihrer Funktionen falsche Trennzeichen verwendet.

Weitere Informationen finden Sie unter: Formelfehler, wenn das Listentrennzeichen nicht ordnungsgemäß gesetzt ist

Wird in Excel-Formeln ein Nummernzeichen-Fehler (#) angezeigt?

Excel gibt eine Reihe von Nummernzeichen-Fehlern (#) aus, z. B. #VALUE!, #REF!, #NUM, #N/V, #DIV/0!, #NAME? und #NULL! um anzuzeigen, dass etwas in Ihrer Formel nicht richtig funktioniert. Der Fehler „#WERT!“ wird beispielsweise durch falsche Formatierung oder nicht unterstützte Datentypen in den Argumenten verursacht. Oder Sie erhalten die Fehlermeldung „#BEZUG!“, wenn sich eine Formel auf Zellen bezieht, die gelöscht oder durch andere Daten ersetzt wurden. Die Anleitungen zur Problembehandlung sind für jeden Fehler unterschiedlich.

Lesen Sie eines der folgenden Themen, das dem angezeigten Nummernzeichen-Fehler entspricht:

• Korrigieren einer #ZAHL! Fehler
• Korrigieren Sie einen „#WERT!“-Fehler Fehler
• Korrigieren eines Fehlerwerts vom Typ #NV
• Korrigieren Sie einen „#DIV/0!“-Fehler Fehler
• Korrigieren Sie einen „#BEZUG!“-Fehler Fehler
• Korrigieren eines Fehlerwerts vom Typ #NAME?
• Korrigieren Sie einen „#NULL!“-Fehler Fehler

Fehlerhafte Links in Excel-Formeln beheben

Jedes Mal, wenn Sie ein Arbeitsblatt öffnen, das Formeln enthält, die auf Werte in anderen Arbeitsblättern verweisen, werden Sie aufgefordert, die Verweise zu aktualisieren oder sie unverändert zu lassen.

Excel zeigt das obige Dialogfeld an, um sicherzustellen, dass die Formeln im aktuellen Arbeitsblatt immer auf den aktuellsten Wert verweisen, falls sich der Bezugswert geändert hat. Sie können auswählen, ob Bezüge aktualisiert werden sollen, oder ohne Aktualisierung fortfahren. Auch wenn Sie auswählen, dass die Bezüge nicht aktualisiert werden sollen, können Sie die Verknüpfungen in der Tabelle auf Wunsch jederzeit manuell aktualisieren.

Sie können jederzeit die Anzeige des Dialogfelds beim Öffnen deaktivieren. Gehen Sie dazu zu Dateioptionen >> Erweitert > Allgemein und deaktivieren Sie das Kontrollkästchen Aktualisieren von automatischen Verknüpfungen anfordern .

Formel zeigt in Excel Syntax anstelle eines Werts an

Wenn die Formel nicht den Wert anzeigt, gehen Sie folgendermaßen vor:

• Stellen Sie sicher, dass Excel so eingestellt ist, dass Formeln in Ihrer Arbeitsmappe angezeigt werden. Wählen Sie dazu die Registerkarte Formeln und in der Gruppe Formelüberwachung die Option Formeln anzeigen aus.

  Tipp

  Sie können auch die Tastenkombination STRG+` (die Taste links von der RÜCKTASTE) verwenden. Wenn Sie dies tun, werden Ihre Spalten automatisch erweitert, um Ihre Formeln anzuzeigen. Aber keine Sorge, wenn Sie wieder zur normalen Ansicht wechseln, wird die Größe Ihrer Spalten wieder angepasst.

• Wenn der obige Schritt das Problem immer noch nicht behebt, ist es möglich, dass die Zelle als Text formatiert ist. Sie können mit der rechten Maustaste auf die Zelle klicken und "Zellen allgemein formatieren" > (oder Strg + 1) auswählen und dann die F2-Eingabetaste > drücken, um das Format zu ändern.

• Wenn Sie eine Spalte mit einem großen Bereich an Zellen haben, die als Text formatiert sind, können Sie den Bereich auswählen, das Zahlenformat Ihrer Wahl anwenden und zu Datentext > bis Spaltenfinish >wechseln. Dadurch wird das Format auf alle markierten Zellen angewendet.

  

Automatische Arbeitsmappenberechnung aktivieren, wenn die Formel nicht in Excel berechnet wird

Wenn eine Formel nicht berechnet wird, müssen Sie überprüfen, ob die automatische Berechnung in Excel aktiviert ist. Formeln werden nicht berechnet, wenn die manuelle Berechnung aktiviert ist. Gehen Sie folgendermaßen vor, um die Automatische Berechnung zu überprüfen.

1. Wählen Sie die Registerkarte Datei aus, und wählen Sie dann unter Optionen die Kategorie Formeln aus.

2. Stellen Sie im Abschnitt Berechnungsoptionen unter Arbeitsmappenberechnung sicher, dass die Option Automatisch ausgewählt ist.

   

Weitere Informationen zu Berechnungen finden Sie unter Ändern der Neuberechnung, Iteration oder Genauigkeit.

Es gibt mindestens einen Zirkelbezug in der Formel.

Ein Zirkelbezug liegt vor, wenn eine Formel auf die Zelle verweist, in der sie sich befindet. Die Lösung besteht darin, entweder die Formel in eine andere Zelle zu verschieben oder die Formelsyntax so zu ändern, dass Zirkelbezüge vermieden werden. In einigen Szenarien benötigen Sie jedoch Zirkelbezüge, da sie dazu führen, dass sich Ihre Funktionen so lange wiederholen, bis eine bestimmte numerische Bedingung erfüllt ist. In solchen Fällen müssen Sie Entfernen oder Zulassen eines Zirkelbezugs aktivieren.

Weitere Informationen zu Zirkelbezügen finden Sie unter Entfernen oder Zulassen eines Zirkelbezugs.

Beginnt Ihre Funktion mit einem Gleichheitszeichen (=)?

Wenn Ihre Eingabe nicht mit einem Gleichheitszeichen beginnt, handelt es sich nicht um eine Formel und wird nicht berechnet – ein häufiger Fehler.

Wenn Sie etwas wie SUMME(A1:A10) eingeben, zeigt Excel die Zeichenfolge SUMME(A1:A10) anstelle eines Formelergebnisses an. Wenn Sie alternativ 11/2 eingeben, zeigt Excel ein Datum an, z. B. „02. Nov“ oder „02.11.2009“, anstatt 11 durch 2 zu teilen.

Sie können diese unerwünschten Ergebnisse vermeiden, indem Sie die Formel immer mit einem Gleichheitszeichen beginnen. Geben Sie zum Beispiel Folgendes ein: =SUMME(A1:A10) und =11/2.

Haben Sie beim Einfügen von Klammern darauf geachtet, dass diese geschlossen werden?

Wenn Sie in einer Formel eine Funktion verwenden, muss sich jede Klammer an der richtigen Position befinden, damit die Funktion korrekt funktioniert. Achten Sie darauf, dass alle Klammern Teil eines zusammen gehörenden Paares sind. Die Formel =WENN(B5<0),"Ungültig",B5*1,05) funktioniert beispielsweise nicht, da zwei schließende Klammern vorhanden sind, aber nur eine öffnende Klammer. Die richtige Formel würde so aussehen: =WENN(B5<0;"Ungültig";B5*1,05).

Sind alle erforderlichen Argumente in der Syntax vorhanden?

Excel-Funktionen weisen Argumente auf – Werte, die Sie eingeben müssen, damit die Funktion funktioniert. Nur wenige Formeln (wie etwa PI oder HEUTE) nehmen keine Argumente an. Überprüfen Sie die Formelsyntax, die angezeigt wird, wenn Sie der Eingabe der Funktion beginnen, um sicherzugehen, dass Die Funktion alle erforderlichen Argumente enthält.

Beispielsweise akzeptiert die Funktion GROSS nur eine Textzeichenfolge oder einen Zellbezug als Argument: =GROSS("hallo") oder =GROSS(C2)

Ferner erfordern einige Funktionen, z. B. SUM, nur numerische Argumente, während andere Funktionen, z. B. REPLACE, als mindestens eines ihrer Argumente einen Textwert erfordern. Wenn Sie den falschen Datentyp verwenden, können die Funktionen unerwartete Ergebnisse liefern oder einen #WERT!-Fehler anzeigen.

Wenn Sie die Syntax einer bestimmten Funktion schnell nachschlagen möchten, rufen Sie die Liste der Excel-Funktionen (nach Kategorie) auf.

Umgang mit unformatierten Zahlen in Excel-Formeln

Geben Sie in Formeln keine Zahlen ein, die mit Dollarzeichen ($) oder Dezimaltrennzeichen (,) formatiert sind, denn Dollarzeichen stehen für absolute Bezüge und Kommas für Argumenttrennzeichen (Letzteres gilt nur für das angloamerikanische Maßsystem). Geben Sie statt $1.000 nur 1000 in die Formel ein.

Wenn Sie in den Argumenten formatierte Zahlen verwenden, werden Sie unerwartete Berechnungsergebnisse erhalten, aber auch der #NUM !-Fehler kann auftreten. Wenn Sie beispielsweise die Formel =ABS(-2,134) eingeben, um den absoluten Wert von -2134 zu ermitteln, zeigt Excel den „#NUM!“-Fehler an, da die ABS-Funktion nur ein Argument akzeptiert und -2 und 134 als separate Argumente betrachtet.

Weisen Zellen, auf die Sie verwiesen haben, den korrekten Datentyp auf?

Ihre Formel gibt möglicherweise nicht die erwarteten Ergebnisse zurück, wenn der Datentyp der Zelle nicht in Berechnungen verwendet werden kann. Wenn Sie z. B. eine einfache Formel "=2+3" in eine Zelle eingeben, die als Text formatiert ist, kann Excel die von Ihnen eingegebenen Daten nicht berechnen. Dann sehen Sie in der Zelle lediglich =2+3. Dies können Sie beheben, indem Sie den Datentyp der Zelle wie nachfolgend dargestellt von "Text" in "Allgemein" ändern:

1. Wählen Sie die Zelle aus.
2. Wählen Sie Start aus und wählen Sie den Pfeil aus, um die Gruppe Zahl oder Zahlenformat zu erweitern (oder drücken Sie STRG + 1). Wählen Sie dann Allgemein aus.
3. Drücken Sie F2, um die Zelle in den Bearbeitungsmodus zu versetzen, und drücken Sie dann die EINGABETASTE, um die Formel zu übernehmen.

Ein Datumswert, den Sie in eine Zelle eingeben, die den Datentyp "Zahl " aufweist, wird möglicherweise als numerischer Datumswert statt als Datum angezeigt. Um eine Zahl als Datum anzuzeigen, wählen Sie im Katalog Zahlenformat ein Datumsformat aus.

Versuchen Sie zu multiplizieren, ohne das Symbol * zu verwenden?

Häufig wird x als Multiplikationsoperator in einer Formel verwenden, aber in Excel müssen Sie stattdessen das Sternsymbol (*) für die Multiplikation verwenden. Wenn Sie in Ihrer Formel Konstanten verwenden, zeigt Excel eine Fehlermeldung an und kann die Formel für Sie korrigieren, indem das x durch ein Sternchen (*) ersetzt wird.

Wenn Sie jedoch Zellbezüge verwenden, gibt Excel eine #NAME? Fehler.

Gibt es in der Formel Text, der nicht in Anführungszeichen eingeschlossen ist?

Wenn Sie eine Formel erstellen, die Text enthält, schließen Sie den Text in Anführungszeichen ein.

Beispielsweise kombiniert die Formel ="Heute ist der " & TEXT(HEUTE();"TT.MM.JJJJ") den Text "Heute ist der " mit den Ergebnissen der Funktionen TEXT und HEUTE und gibt etwas wie Heute ist der 31.03.2016 zurück.

In der Formel weist "Heute ist der " ein Leerzeichen vor dem schließenden Anführungszeichen auf, um das Leerzeichen bereitzustellen, das zwischen den Wörtern "Heute ist der " und "30.03.2016" angezeigt werden soll. Ohne die Anführungszeichen um den Text würde die Formel möglicherweise den Fehler #NAME? anzeigen.

Gibt es mehr als 64 Funktionen in einer Formel?

Sie können bis zu 64 Ebenen von Funktionen innerhalb einer Formel kombinieren (oder verschachteln).

Beispiel: Die Formel =WENN(WURZEL(PI())<2;"Weniger als zwei!";"Mehr als zwei!") hat 3 Funktionsebenen; Die PI-Funktion ist in der WURZEL-Funktion verschachtelt, die wiederum in der WENN-Funktion verschachtelt ist.

Sind die Namen von Arbeitsblättern in einfache Anführungszeichen eingeschlossen?

Wenn Sie einen Bezug auf Werte oder Zellen in einem anderen Arbeitsblatt eingeben und der Name des Arbeitsblatts nicht alphabetische Zeichen (wie eine Leerzeichen) enthält, schließen Sie den Namen in einfache Anführungszeichen (') ein.

Um zum Beispiel den Wert aus Zelle „D3“ in einem Arbeitsblatt mit dem Namen „Quartalsdaten“ in Ihrer Arbeitsmappe zurückzugeben, geben Sie ein: ='Quartalsdaten'!D3. Ohne die Anführungszeichen um den Blattnamen herum, zeigt die Formel den Fehler #NAME? an.

Sie können auch die Werte oder Zellen in einem anderen Blatt auswählen, um sich in Ihrer Formel darauf zu beziehen. Excel fügt dann automatisch in Anführungszeichen um die Blattnamen ein.

Korrigieren externer Arbeitsmappenpfade in Excel-Formeln

Wenn Sie einen Bezug auf Werte oder Zellen in einer anderen Arbeitsmappe eingeben, schließen Sie den Namen der Arbeitsmappe in eckige Klammer ([]) gefolgt vom Namen des Arbeitsblatts ein, in dem sich die Werte oder Zellen befinden.

Um beispielsweise auf die Zellen "A1" bis "A8" auf dem Blatt "Umsatz" in der Arbeitsmappe "Q2 Vorgänge" zu verweisen, die in Excel geöffnet ist, geben Sie Folgendes ein: =[Q2 Operations.xlsx]Umsatz! A1:A8. Ohne die eckigen Klammern zeigt die Formel den Fehler #REF! an.

Wenn die Arbeitsmappe nicht in Excel geöffnet ist, geben Sie den vollständigen Pfad zu der Datei ein.

Beispiel: =ZEILEN('C:\Meine Dateien\[Betrieb Q2.xlsx]Umsätze'!A1:A8).

Werden Zahlenwerten durch Null dividiert?

Das Teilen einer Zelle durch eine andere Zelle, die den Wert "Null" (0) oder keinen Wert aufweist, führt zu einem Fehler #DIV/0!.

Um diesen Fehler zu vermeiden, können Sie eine direkte Prüfung auf das Vorhandensein des Nenners vornehmen. Sie können Folgendes verwenden:

=WENN(B1;A1/B1;0)

Dies bedeutet: WENN(B1 vorhanden ist, wird A1 durch B1 dividiert; andernfalls wird 0 zurückgegeben).

Bezieht sich die Formel auf gelöschte Daten?

Überprüfen Sie immer, ob Sie Formeln haben, die sich auf Daten in Zellen, Bereichen, definierten Namen, Arbeitsblättern oder Arbeitsmappen beziehen, bevor Sie etwas löschen. Im Bedarfsfall können Sie diese Formeln durch Ergebnisse ersetzen, bevor Sie die Daten entfernen, auf die verwiesen wird.

Wenn Sie die Formeln nicht durch Ergebnisse ersetzen können, lesen Sie den folgenden Artikel zu Fehlermeldungen und möglichen Lösungen:

• Wenn sich eine Formel auf Zellen bezieht, die gelöscht oder durch andere Daten ersetzt wurden, und sie einen #REF!-Fehler zurückgibt, wählen Sie die Zelle mit dem #REF! 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!. Wählen Sie in der Formelleiste „#BEZUG!“ aus und löschen Sie es. Geben Sie dann den Bereich für die Formel erneut ein.
• Wenn ein definierter Name fehlt und die Formel, die diesen definierten Namen verwendet, den Fehler #NAME?zurückgibt, definieren Sie einen neuen Namen, der auf den gewünschten Bereich verweist, oder ändern Sie die Formel so, dass sie direkt auf den Zellbereich verweist (z. B. A2:D8).
• Wenn ein Arbeitsblatt fehlt und eine Formel, die sich darauf bezieht, einen „#REF!“-Fehler zurückgibt, gibt es leider keine Möglichkeit, dies zu beheben – ein Arbeitsblatt, das gelöscht wurde, kann nicht wiederhergestellt werden.
• Wenn eine Arbeitsmappe nicht vorhanden ist, bleibt eine Formel, die darauf verweist, solange gültig, bis Sie die Formel aktualisieren.
  Beispiel: Wenn Ihre Formel =[Arbeitsmappe1.xlsx]Blatt1'!A1 lautet und "Arbeitsmappe1" nicht mehr vorhanden ist, bleiben die Werte, auf die in dieser Arbeitsmappe verwiesen wird, verfügbar. Wenn Sie jedoch eine Formel bearbeiten und speichern, die sich auf diese Arbeitsmappe bezieht, zeigt Excel das Dialogfeld Werte aktualisieren an und fordert Sie auf, einen Dateinamen einzugeben. Wählen Sie "Abbrechen" aus und stellen Sie sicher, dass diese Daten nicht verloren gehen, indem Sie die Formeln, die sich auf die fehlende Arbeitsmappe beziehen, durch die Formelergebnisse ersetzen.

Haben Sie Zellen in einer Formel in einer Tabelle kopiert und eingefügt?

Manchmal möchten Sie, wenn Sie den Inhalt einer Zelle kopieren, nur den Wert einfügen und nicht die zugrunde liegende Formel, die in der Bearbeitungsleiste angezeigt wird.

Nehmen wir beispielsweise an, Sie möchten den Ergebniswert einer Formel in eine Zelle auf einem anderen Arbeitsblatt kopieren. Möglicherweise möchten Sie auch die Werte löschen, die Sie in einer Formel verwendet haben, nachdem Sie die Ergebniswerte in eine andere Zelle auf dem Arbeitsblatt kopiert haben. Beide Aktionen bewirken, dass in der Zielzelle ein Fehler vom Typ "Vulner Zellbezug" (#REF!) angezeigt wird, da die Zellen, die die Werte enthalten, die Sie in der Formel verwendet haben, nicht mehr referenziert werden können.

Sie können diesen Fehler vermeiden, indem Sie die resultierenden Werte von Formeln ohne die Formel in die Zielzellen einfügen.

1. Markieren Sie in einem Arbeitsblatt die Zellen, die die zu kopierenden Ergebniswerte einer Formel enthalten.

2. Wählen Sie auf der Registerkarte Start in der Gruppe Zwischenablage die Option kopieren aus.
   
   Tastenkombination: Drücken Sie STRG+C.

3. Klicken Sie auf die obere linke Zelle des Einfügebereichs.

   Tipp

   Um eine Auswahl auf ein anderes Arbeitsblatt oder eine andere Arbeitsmappe zu verschieben oder zu kopieren, wählen Sie eine andere Arbeitsblatt-Registerkarte aus oder wechseln Sie zu einer anderen Arbeitsmappe und wählen Sie dann die obere linke Zelle des Einfügebereichs aus.

4. Klicken Sie auf der Registerkarte Start in der Gruppe Zwischenablage auf Bild "Einfügen" und dann auf Werte einfügen oder drücken Sie die ALT > E > S > V > EINGABETASTE für Windows oder Option Befehl > V > V > Eingabe auf einem Mac.>

Wenn Sie eine verschachtelte Formel verwenden, sollte diese schrittweise ausgewertet werden.

Wenn Sie verstehen möchten, wie eine komplexe oder geschachtelte Formel das endgültige Ergebnis berechnet, können Sie die Formel auswerten.

1. Wählen Sie die Formel aus, die Sie auswerten möchten.

2. Formeln> auswählen Formelauswerten
   

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

4. Wenn der unterstrichene Teil der Formel ein Bezug auf eine andere Formel ist, wählen Sie die Option Einzelschritt 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 "Schrittweise " ist nicht verfügbar, wenn der Bezug zum zweiten Mal in der Formel erscheint – oder wenn sich die Formel auf eine Zelle in einer anderen Arbeitsmappe bezieht.

5. Fahren Sie fort, bis jeder Teil der Formel ausgewertet wurde.
   Das Tool „Formel auswerten“ sagt Ihnen nicht unbedingt, warum Ihre Formel fehlerhaft ist, aber es kann Ihnen helfen, herauszufinden, wo. Das Tool kann sich bei größeren Formeln als äußerst nützlich erweisen, in denen das Problem ansonsten nur schwer zu finden ist.

   Hinweis

   • Einige Teile der IF - und CHOOSE-Funktionen werden nicht ausgewertet, und möglicherweise wird der Fehler #N/ A-Fehler im Feld "Auswertung " angezeigt.
   • Leere Bezüge werden als Nullwerte (0) im Feld Auswertung angezeigt.
   • Einige Funktionen werden jedes Mal neu berechnet, wenn sich das Arbeitsblatt ändert. Diese Funktionen, einschließlich der Funktionen ZUFALLSZAHL, BEREICHE, INDEX, BEREICH.VERSCHIEBEN, ZELLE, INDIREKT, ZEILEN, SPALTEN, JETZT, HEUTE und ZUFALLSBEREICH können dazu führen, dass im Dialogfeld Formel auswerten Ergebnisse angezeigt werden, die sich von den eigentlichen Ergebnissen in der Zelle im Arbeitsblatt unterscheiden.

Benötigen Sie weitere Hilfe?

Sie können jederzeit einen Experten in der Excel Tech Community fragen oder in Communitys Unterstützung erhalten.

Siehe auch

Übersicht über Formeln in Excel

Excel-Hilfe & Lernen