So vermeiden Sie fehlerhafte Formeln
Wenn Excel eine Formel, die Sie zu erstellen versuchen, nicht auflösen kann, erhalten Sie möglicherweise eine Fehlermeldung wie diese:
Leider bedeutet dies, dass Excel nicht verstehen kann, was Sie zu tun versuchen, sodass Sie vielleicht einfach von vorne beginnen möchten.
Beginnen Sie mit OK oder drücken Sie ESC , um die Fehlermeldung zu schließen.
Sie kehren zu der Zelle mit der fehlerhaften Formel zurück, die sich im Bearbeitungsmodus befindet, und Excel hebt die Stelle hervor, an der das Problem auftritt. Wenn Sie immer noch nicht wissen, was Sie von dort aus tun sollen, und von vorne beginnen möchten, können Sie erneut ESC drücken oder die Schaltfläche Abbrechen in der Formelleiste auswählen, wodurch Sie den Bearbeitungsmodus verlassen.
Wenn Sie fortfahren möchten, enthält die folgende Checkliste Schritte zur Fehlerbehebung, mit denen Sie herausfinden können, was möglicherweise schief gelaufen ist.
Hinweis: Wenn Sie Office für das Web verwenden, werden möglicherweise nicht dieselben Fehler angezeigt, oder die Lösungen gelten möglicherweise nicht.
Excel gibt eine Vielzahl von Nummernfehlern (#) aus, z. B. #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? und #NULL!, um anzuzeigen, dass etwas in Ihrer Formel nicht stimmt funktioniert nicht richtig. Zum Beispiel der #WERT! Fehler wird durch falsche Formatierung oder nicht unterstützte Datentypen in Argumenten verursacht. Oder Sie sehen das #REF! Fehler, wenn eine Formel auf Zellen verweist, die gelöscht oder durch andere Daten ersetzt wurden. Die Anleitung zur Fehlerbehebung unterscheidet sich für jeden Fehler.
Hinweis: #### ist kein formelbezogener Fehler. Es bedeutet nur, dass die Spalte nicht breit genug ist, um den Zellinhalt anzuzeigen. Ziehen Sie einfach die Spalte, um sie zu verbreitern, oder gehen Sie zu Start > Format > Spaltenbreite automatisch anpassen .
Schlagen Sie in einem der folgenden Themen nach, die dem Pfund-Fehler entsprechen, den Sie sehen:
Jedes Mal, wenn Sie eine Tabelle öffnen, die Formeln enthält, die auf Werte in anderen Tabellen verweisen, werden Sie aufgefordert, die Verweise zu aktualisieren oder unverändert zu lassen.
Excel zeigt das obige Dialogfeld an, um sicherzustellen, dass die Formeln in der aktuellen Tabelle immer auf den aktuellsten Wert verweisen, falls sich der Referenzwert geändert hat. Sie können die Referenzen aktualisieren oder überspringen, wenn Sie keine Aktualisierung wünschen. Auch wenn Sie die Referenzen nicht aktualisieren möchten, können Sie die Links in der Tabelle jederzeit manuell aktualisieren.
Sie können jederzeit deaktivieren, dass das Dialogfeld beim Start angezeigt wird. Gehen Sie dazu zu Datei > Optionen > Erweitert > Allgemein und deaktivieren Sie das Kontrollkästchen Automatische Links aktualisieren .
Wichtig: Wenn Sie zum ersten Mal mit defekten Links in Formeln arbeiten, eine Auffrischung zum Auflösen defekter Links benötigen oder nicht wissen, ob Sie die Verweise aktualisieren sollen, lesen Sie Steuern, wenn externe Verweise (Links) vorhanden sind aktualisiert .
Wenn die Formel den Wert nicht anzeigt, gehen Sie folgendermaßen vor:
Stellen Sie sicher, dass Excel so eingestellt ist, dass Formeln in Ihrer Tabelle 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 über der Tabulatortaste) verwenden. Wenn Sie dies tun, werden Ihre Spalten automatisch verbreitert, um Ihre Formeln anzuzeigen, aber keine Sorge, wenn Sie zurück zur normalen Ansicht wechseln, ändert sich die Größe Ihrer Spalten.
Wenn der obige Schritt das Problem immer noch nicht löst, ist die Zelle möglicherweise als Text formatiert. Sie können mit der rechten Maustaste auf die Zelle klicken und Zellen formatieren > Allgemein (oder Strg + 1 ) auswählen und dann F2 > Eingabe drücken, um das Format zu ändern.
Wenn Sie eine Spalte mit vielen Zellen haben, die als Text formatiert sind, können Sie den Bereich auswählen, das Zahlenformat Ihrer Wahl anwenden und zu Daten > Text in Spalte > Fertig stellen gehen. Dadurch wird das Format auf alle ausgewählten Zellen angewendet.
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. Befolgen Sie diese Schritte, um nach Automatische Berechnung zu suchen.
Wählen Sie die Registerkarte Datei , wählen Sie Optionen und dann die Kategorie Formeln aus.
Stellen Sie im Abschnitt Berechnungsoptionen unter Arbeitsmappenberechnung sicher, dass die Option Automatisch ausgewählt ist.
Weitere Informationen zu Berechnungen finden Sie unter Neuberechnung, Iteration oder Genauigkeit von Formeln ändern .
Ein Zirkelbezug tritt auf, wenn eine Formel auf die Zelle verweist, in der sie sich befindet. Die Lösung besteht darin, die Formel entweder in eine andere Zelle zu verschieben oder die Formelsyntax so zu ändern, dass Zirkelbezüge vermieden werden. In einigen Szenarien benötigen Sie jedoch möglicherweise Zirkelverweise, da sie dazu führen, dass Ihre Funktionen iterieren – wiederholen Sie dies, bis eine bestimmte numerische Bedingung erfüllt ist. In solchen Fällen müssen Sie Entfernen aktivieren oder einen Zirkelverweis zulassen .
Weitere Informationen zu Zirkelverweisen finden Sie unter Entfernen oder Zulassen eines Zirkelverweises .
Wenn Ihr Eintrag nicht mit einem Gleichheitszeichen beginnt, ist es keine Formel und wird nicht berechnet – ein häufiger Fehler.
Wenn Sie etwas wie SUM(A1:A10) eingeben, zeigt Excel die Textzeichenfolge SUM(A1:A10) anstelle eines Formelergebnisses an. Wenn Sie alternativ 11/2 eingeben, zeigt Excel ein Datum an, z. B. 2. November oder 02.11.2009, anstatt 11 durch 2 zu teilen.
Um diese unerwarteten Ergebnisse zu vermeiden, starten Sie die Funktion immer mit einem Gleichheitszeichen. Geben Sie beispielsweise Folgendes ein: = SUMME(A1:A10) und =11/2 .
Wenn Sie eine Funktion in einer Formel verwenden, benötigt jede öffnende Klammer eine schließende Klammer, damit die Funktion ordnungsgemäß funktioniert. Stellen Sie sicher, dass alle Klammern Teil eines übereinstimmenden Paares sind. Beispielsweise funktioniert die Formel =IF(B5<0),"Nicht gültig",B5*1,05) nicht, weil es zwei schließende Klammern, aber nur eine öffnende Klammer gibt. Die korrekte Formel würde so aussehen: =IF(B5<0,"Not valid",B5*1.05) .
Excel-Funktionen haben Argumente – Werte, die Sie angeben müssen, damit die Funktion funktioniert. Nur wenige Funktionen (wie PI oder TODAY ) akzeptieren keine Argumente. Überprüfen Sie die Formelsyntax, die angezeigt wird, wenn Sie mit der Eingabe der Funktion beginnen, um sicherzustellen, dass die Funktion über die erforderlichen Argumente verfügt.
Beispielsweise akzeptiert die UPPER- Funktion nur eine Textzeichenfolge oder einen Zellbezug als Argument: =UPPER("hello") oder =UPPER(C2)
Hinweis: Die Argumente der Funktion werden während der Eingabe in einer schwebenden Funktionsreferenz-Symbolleiste unterhalb der Formel angezeigt.
Außerdem erfordern einige Funktionen, wie z. B. SUM , nur numerische Argumente, während andere Funktionen, wie z. B. REPLACE , einen Textwert für mindestens eines ihrer Argumente erfordern. Wenn Sie den falschen Datentyp verwenden, geben Funktionen möglicherweise unerwartete Ergebnisse zurück oder zeigen einen #VALUE! Error.
Wenn Sie die Syntax einer bestimmten Funktion schnell nachschlagen müssen, sehen Sie sich die Liste der Excel-Funktionen (nach Kategorie) an .
Geben Sie in Formeln keine Zahlen ein, die mit Dollarzeichen ($) oder Dezimaltrennzeichen (,) formatiert sind, da Dollarzeichen auf absolute Referenzen hinweisen und Kommas Argumenttrennzeichen sind. Statt 1.000 $ einzugeben, geben Sie 1000 in die Formel ein.
Wenn Sie formatierte Zahlen in Argumenten verwenden, erhalten Sie unerwartete Berechnungsergebnisse, aber Sie sehen möglicherweise auch die #ZAHL! Error. Wenn Sie beispielsweise die Formel =ABS(-2.134) eingeben , um den absoluten Wert von -2134 zu finden, zeigt Excel die #ZAHL! Fehler, da die ABS-Funktion nur ein Argument akzeptiert und -2 und 134 als separate Argumente betrachtet.
Hinweis: Sie können das Formelergebnis mit Dezimaltrennzeichen und Währungssymbolen formatieren, nachdem Sie die Formel mit unformatierten Zahlen (Konstanten) eingegeben haben. Es ist im Allgemeinen keine gute Idee, Konstanten in Formeln einzufügen, da sie schwer zu finden sein können, wenn Sie später aktualisieren müssen, und sie eher dazu neigen, falsch eingegeben zu werden. Es ist viel besser, Ihre Konstanten in Zellen zu platzieren, wo sie offen liegen und leicht referenziert werden können.
Ihre Formel gibt möglicherweise nicht die erwarteten Ergebnisse zurück, wenn der Datentyp der Zelle nicht in Berechnungen verwendet werden kann. Wenn Sie beispielsweise eine einfache Formel =2+3 in eine Zelle eingeben, die als Text formatiert ist, kann Excel die eingegebenen Daten nicht berechnen. Alles, was Sie in der Zelle sehen, ist =2+3 . Um dies zu beheben, ändern Sie den Datentyp der Zelle wie folgt von Text in Allgemein :
Wählen Sie die Zelle aus.
Wählen Sie Start und dann den Pfeil aus, um die Gruppe „ Zahl " oder „ Zahlenformat " zu erweitern (oder drücken Sie Strg + 1 ). Wählen Sie dann Allgemein aus.
Drücken Sie F2 , um die Zelle in den Bearbeitungsmodus zu versetzen, und drücken Sie dann die Eingabetaste , um die Formel zu akzeptieren.
Ein Datum, das Sie in eine Zelle eingeben, die den Datentyp Zahl verwendet, wird möglicherweise als numerischer Datumswert anstelle eines Datums angezeigt. Um eine Zahl als Datum anzuzeigen, wählen Sie ein Datumsformat in der Zahlenformat -Galerie aus.
Es ist ziemlich üblich, x als Multiplikationsoperator in einer Formel zu verwenden, aber Excel akzeptiert nur das Sternchen (*) für die Multiplikation. Wenn Sie Konstanten in Ihrer Formel verwenden, zeigt Excel eine Fehlermeldung an und kann die Formel für Sie korrigieren, indem das x durch das Sternchen (*) ersetzt wird.
Wenn Sie jedoch Zellbezüge verwenden, gibt Excel ein #NAME? Error.
Wenn Sie eine Formel erstellen, die Text enthält, schließen Sie den Text in Anführungszeichen ein.
Zum Beispiel kombiniert die Formel ="Today is " & TEXT(TODAY(),"dddd, mmmm dd") den Text "Today is " mit den Ergebnissen der TEXT- und TODAY -Funktionen und gibt so etwas wie Today is Monday zurück. 30. Mai .
In der Formel hat „Heute ist" ein Leerzeichen vor dem abschließenden Anführungszeichen, um das gewünschte Leerzeichen zwischen den Wörtern „Heute ist" und „Montag, 30. Mai" bereitzustellen. Ohne Anführungszeichen um den Text zeigt die Formel möglicherweise den Fehler #NAME? Fehler .
Sie können bis zu 64 Funktionsebenen innerhalb einer Formel kombinieren (oder verschachteln).
Zum Beispiel hat die Formel =IF(SQRT(PI())<2,"Weniger als zwei!","Mehr als zwei!") 3 Funktionsebenen; Die PI-Funktion ist in der SQRT-Funktion verschachtelt, die wiederum in der IF-Funktion verschachtelt ist.
Wenn Sie einen Verweis auf Werte oder Zellen in einem anderen Arbeitsblatt eingeben und der Name dieses Blatts ein nicht alphabetisches Zeichen (z. B. ein Leerzeichen) enthält, schließen Sie den Namen in einfache Anführungszeichen (') ein.
Um beispielsweise den Wert aus Zelle D3 in einem Arbeitsblatt namens Quarterly Data in Ihrer Arbeitsmappe zurückzugeben, geben Sie Folgendes ein: ='Quarterly Data'!D3 . Ohne die Anführungszeichen um den Blattnamen zeigt die Formel den Fehler #NAME? Fehler .
Sie können auch die Werte oder Zellen in einem anderen Blatt auswählen, um in Ihrer Formel darauf zu verweisen. Excel fügt dann automatisch die Anführungszeichen um die Blattnamen ein.
Wenn Sie einen Verweis auf Werte oder Zellen in einer anderen Arbeitsmappe eingeben, schließen Sie den Namen der Arbeitsmappe in eckige Klammern ([]) ein, gefolgt vom Namen des Arbeitsblatts, das die Werte oder Zellen enthält.
Um beispielsweise auf die Zellen A1 bis A8 im Blatt „Sales" in der in Excel geöffneten Arbeitsmappe „Q2 Operations" zu verweisen, geben Sie Folgendes ein: =[Q2 Operations.xlsx]Sales!A1:A8 . Ohne die eckigen Klammern zeigt die Formel den #REF! Fehler .
Wenn die Arbeitsmappe nicht in Excel geöffnet ist, geben Sie den vollständigen Pfad zur Datei ein.
Beispiel: =ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'!A1:A8) .
Hinweis: Wenn der vollständige Pfad Leerzeichen enthält, schließen Sie den Pfad in einfache Anführungszeichen ein (am Anfang des Pfads und nach dem Namen des Arbeitsblatts vor dem Ausrufezeichen).
Tipp: Am einfachsten erhalten Sie den Pfad zu der anderen Arbeitsmappe, indem Sie die andere Arbeitsmappe öffnen, dann in Ihrer ursprünglichen Arbeitsmappe = eingeben und mit Alt+Tab zur anderen Arbeitsmappe wechseln. Wählen Sie eine beliebige Zelle auf dem gewünschten Blatt aus und schließen Sie dann die Quellarbeitsmappe. Ihre Formel wird automatisch aktualisiert, um den vollständigen Dateipfad und Blattnamen zusammen mit der erforderlichen Syntax anzuzeigen. Sie können den Pfad sogar kopieren und einfügen und verwenden, wo immer Sie ihn brauchen.
Das Teilen einer Zelle durch eine andere Zelle, die Null (0) oder keinen Wert hat, führt zu einem #DIV/0! Fehler .
Um diesen Fehler zu vermeiden, können Sie ihn direkt ansprechen und auf die Existenz des Nenners testen. Du könntest benutzen:
=WENN(B1,A1/B1,0)
Was besagt, WENN (B1 existiert, dann A1 durch B1 teilen, andernfalls eine 0 zurückgeben).
Überprüfen Sie immer, ob Sie Formeln haben, die auf Daten in Zellen, Bereichen, definierten Namen, Arbeitsblättern oder Arbeitsmappen verweisen, bevor Sie etwas löschen. Sie können diese Formeln dann durch ihre Ergebnisse ersetzen, bevor Sie die referenzierten Daten entfernen.
Wenn Sie die Formeln nicht durch ihre Ergebnisse ersetzen können, lesen Sie diese Informationen zu den Fehlern und möglichen Lösungen:
Wenn sich eine Formel auf Zellen bezieht, die gelöscht oder durch andere Daten ersetzt wurden, und wenn sie ein #REF! error , wählen Sie die Zelle mit dem #REF! Error. Wählen Sie in der Bearbeitungsleiste #REF! und lösche es. Geben Sie dann den Bereich für die Formel erneut ein.
Wenn ein definierter Name fehlt und eine Formel, die sich auf diesen Namen bezieht, ein #NAME? error , definieren Sie einen neuen Namen, der sich auf den gewünschten Bereich bezieht, 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 darauf verweist, einen #REF! Fehler, es gibt leider keine Möglichkeit, dies zu beheben – ein Arbeitsblatt, das gelöscht wurde, kann nicht wiederhergestellt werden.
Wenn eine Arbeitsmappe fehlt, bleibt eine Formel, die darauf verweist, intakt, bis Sie die Formel aktualisieren.
Wenn Ihre Formel beispielsweise =[Book1.xlsx]Sheet1'!A1 lautet und Book1.xlsx 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 dann sicher, dass diese Daten nicht verloren gehen, indem Sie die Formeln, die auf die fehlende Arbeitsmappe verweisen, durch die Formelergebnisse ersetzen.
Wenn Sie den Inhalt einer Zelle kopieren, möchten Sie manchmal nur den Wert einfügen und nicht die zugrunde liegende Formel, die in der Bearbeitungsleiste angezeigt wird.
Beispielsweise möchten Sie möglicherweise den Ergebniswert einer Formel in eine Zelle auf einem anderen Arbeitsblatt kopieren. Oder Sie möchten die Werte löschen, die Sie in einer Formel verwendet haben, nachdem Sie den resultierenden Wert in eine andere Zelle auf dem Arbeitsblatt kopiert haben. Beide Aktionen führen dazu, dass in der Zielzelle ein ungültiger Zellbezugsfehler (#REF!) angezeigt wird, da auf die Zellen, die die in der Formel verwendeten Werte enthalten, nicht mehr verwiesen werden kann.
Sie können diesen Fehler vermeiden, indem Sie die Ergebniswerte von Formeln ohne die Formel in die Zielzellen einfügen.
Wählen Sie auf einem Arbeitsblatt die Zellen aus, die die resultierenden Werte einer Formel enthalten, die Sie kopieren möchten.
Wählen Sie auf der Registerkarte Start in der Gruppe Zwischenablage die Option Kopieren aus
. 
Tastaturkürzel: Drücken Sie STRG+C.
Wählen Sie die obere linke Zelle des Einfügebereichs aus.
Tipp: Um eine Auswahl in ein anderes Arbeitsblatt oder eine andere Arbeitsmappe zu verschieben oder zu kopieren, wählen Sie eine andere Arbeitsblattregisterkarte aus oder wechseln Sie zu einer anderen Arbeitsmappe und wählen Sie dann die obere linke Zelle des Einfügebereichs aus.
Wählen Sie auf der Registerkarte Start in der Gruppe Zwischenablage die Option Einfügen aus
, und wählen Sie dann Werte einfügen aus oder drücken Sie Alt > E > S > V > Eingabe für Windows oder Option > Befehl > V > V > Eingabe auf einem Mac.
Um zu verstehen, wie eine komplexe oder verschachtelte Formel das Endergebnis berechnet, können Sie diese Formel auswerten.
Wählen Sie die Formel aus, die Sie auswerten möchten.
Wählen Sie Formeln > Formel auswerten aus .
Wählen Sie Evaluate aus, um den Wert der unterstrichenen Referenz zu untersuchen. Das Ergebnis der Auswertung ist kursiv dargestellt.
Wenn der unterstrichene Teil der Formel ein Verweis auf eine andere Formel ist, wählen Sie Einzelschritt aus, um die andere Formel im Auswertungsfeld anzuzeigen. Wählen Sie Schritt aus , um zur vorherigen Zelle und Formel zurückzukehren.
Die Schaltfläche Einzelschritt ist nicht verfügbar, wenn die Referenz zum zweiten Mal in der Formel erscheint – oder wenn die Formel auf eine Zelle in einer anderen Arbeitsmappe verweist.
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 dabei helfen, herauszufinden, wo. Dies kann ein sehr praktisches Werkzeug in größeren Formeln sein, wo es andernfalls schwierig sein könnte, das Problem zu finden.
Anmerkungen:
Einige Teile der IF- undCHOOSE- Funktionen werden nicht ausgewertet, und im Auswertungsfeld wird möglicherweise der Fehler #NV angezeigt.
Blanko-Referenzen werden im Auswertungsfeld als Nullwerte (0) angezeigt.
Einige Funktionen werden jedes Mal neu berechnet, wenn sich das Arbeitsblatt ändert. Diese Funktionen, einschließlich der Funktionen RAND , AREAS , INDEX ,OFFSET , CELL , INDIRECT , ROWS , COLUMNS , NOW , TODAY und RANDBETWEEN , können dazu führen, dass im Dialogfeld „ Formel auswerten" Ergebnisse angezeigt werden, die von den tatsächlichen Ergebnissen in der Zelle abweichen das Arbeitsblatt.
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in der Answers-Community erhalten.
Siehe auch
Übersicht über Formeln in Excel
No comments:
Post a Comment