Excel-Formeln funktionieren nicht: Mögliche Gründe und wie man es behebt!

Wenn Sie in Excel mit Formeln arbeiten, werden Sie früher oder später auf das Problem stoßen, dass Excel-Formeln überhaupt nicht funktionieren (oder das falsche Ergebnis liefern).

Es wäre zwar großartig gewesen, wenn es nur wenige mögliche Gründe für fehlerhafte Formeln gegeben hätte. Leider gibt es zu viele Dinge, die schief gehen können (und oft tun).

Aber da wir in einer Welt leben, die dem Pareto-Prinzip folgt, werden wahrscheinlich 80 % (oder vielleicht sogar 90 % oder 95 % der Probleme, bei denen Formeln in Excel nicht funktionieren) gelöst, wenn Sie nach einigen häufigen Problemen suchen.

Und in diesem Artikel werde ich die häufigsten Probleme hervorheben, die wahrscheinlich die Ursache für Ihre Excel-Formeln funktionieren nicht.

Also lasst uns anfangen!

Falsche Syntax der Funktion

Lassen Sie mich zunächst auf das Offensichtliche hinweisen.

Jede Funktion in Excel hat eine bestimmte Syntax – beispielsweise die Anzahl der Argumente, die sie akzeptieren kann oder die Art der Argumente, die sie akzeptieren kann.

Und in vielen Fällen kann der Grund, warum Ihre Excel-Formeln nicht funktionieren oder das falsche Ergebnis liefern, ein falsches Argument (oder fehlende Argumente) sein.

Die SVERWEIS-Funktion benötigt beispielsweise drei obligatorische Argumente und ein optionales Argument.

Wenn Sie ein falsches Argument angeben oder das optionale Argument nicht angeben (wo es für die Funktion der Formel erforderlich ist), erhalten Sie ein falsches Ergebnis.

Angenommen, Sie haben einen Datensatz wie unten gezeigt, in dem Sie die Punktzahl von Note in Prüfung 2 (in Zelle F2) kennen müssen.

Wenn ich die folgende Formel verwende, erhalte ich das falsche Ergebnis, da ich im dritten Argument den falschen Wert verwende (einer, der nach der Spaltenindexnummer fragt).

=SVERWEIS(A2,A2:C6,2,FALSCH)

In diesem Fall berechnet die Formel (da sie einen Wert zurückgibt), aber das Ergebnis ist falsch (anstelle der Punktzahl in Prüfung 2 ergibt sie die Punktzahl in Prüfung 1).

Ein weiteres Beispiel, bei dem Sie bei den Argumenten vorsichtig sein müssen, ist die Verwendung von SVERWEIS mit der ungefähren Übereinstimmung.

Da Sie ein optionales Argument verwenden müssen, um anzugeben, wo SVERWEIS eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung durchführen soll, kann die Nichtangabe (oder die Verwendung des falschen Arguments) zu Problemen führen.

Unten ist ein Beispiel, in dem ich die Notendaten für einige Schüler habe und ich möchte die Noten in Prüfung 1 für die Schüler in der Tabelle rechts extrahieren.

Wenn ich die folgende SVERWEIS-Formel verwende, erhalte ich bei einigen Namen einen Fehler.

=SVERWEIS(E2,$A$2:$C$6,2)

Dies geschieht, da ich das letzte Argument nicht angegeben habe (das verwendet wird, um zu bestimmen, ob eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung durchgeführt werden soll). Wenn Sie das letzte Argument nicht angeben, wird standardmäßig automatisch eine ungefähre Übereinstimmung durchgeführt.

Da in diesem Fall eine genaue Übereinstimmung erforderlich war, gibt die Formel für einige Namen einen Fehler zurück.

Obwohl ich das Beispiel der SVERWEIS-Funktion genommen habe, kann dies in diesem Fall auch für viele Excel-Formeln mit optionalen Argumenten gelten.

Lesen Sie auch: Identifizieren von Fehlern mit Excel-Formel-Debugging

Zusätzliche Leerzeichen, die unerwartete Ergebnisse verursachen

Führende, nachgestellte Leerzeichen sind schwer zu finden und können Probleme verursachen, wenn Sie eine Zelle verwenden, die diese in Formeln enthält.

Wenn ich beispielsweise im folgenden Beispiel versuche, SVERWEIS zu verwenden, um die Punktzahl für Mark abzurufen, erhalte ich den #N/A-Fehler (der nicht verfügbare Fehler).

Während ich sehen kann, dass die Formel korrekt ist und der Name "Mark" eindeutig ist, gibt es die Liste, aber ich kann nicht sehen, dass sich in der Zelle mit dem Namen (in Zelle D2) ein Leerzeichen befindet.

Excel betrachtet den Inhalt dieser beiden Zellen nicht als identisch und betrachtet es daher als Nichtübereinstimmung beim Abrufen des Werts mit SVERWEIS (oder es könnte eine andere Nachschlageformel sein).

Um dieses Problem zu beheben, müssen Sie diese zusätzlichen Leerzeichen entfernen.

Sie können dies mit einer der folgenden Methoden tun:

1. Reinigen Sie die Zelle und entfernen Sie alle führenden/nachgestellten Leerzeichen, bevor Sie sie in Formeln verwenden
2. Verwenden Sie die TRIM-Funktion innerhalb der Formel, um sicherzustellen, dass führende/nachfolgende/doppelte Leerzeichen ignoriert werden.

Im obigen Beispiel können Sie stattdessen die folgende Formel verwenden, um sicherzustellen, dass sie funktioniert.

=SVERWEIS(TRIM(D2),$A$2:$B$6,2,0)

Obwohl ich das SVERWEIS-Beispiel genommen habe, ist dies auch ein häufiges Problem bei der Arbeit mit TEXT-Funktionen.

Wenn ich beispielsweise die LEN-Funktion verwende, um die Gesamtzahl der Zeichen in einer Zelle zu zählen, werden führende oder nachfolgende Leerzeichen ebenfalls gezählt und liefern das falsche Ergebnis.

Mitnehmen / Wie man es repariert: Wenn möglich, bereinigen Sie Ihre Daten, indem Sie alle führenden/nachgestellten oder doppelten Leerzeichen entfernen, bevor Sie diese in Formeln verwenden. Wenn Sie die Originaldaten nicht ändern können, verwenden Sie die TRIM-Funktion in Formeln, um dies zu erledigen.

Verwenden der manuellen Berechnung anstelle der automatischen

Diese eine Einstellung kann Sie verrückt machen (wenn Sie nicht wissen, dass sie alle Probleme verursacht).

Excel hat zwei Berechnungsmodi - Automatisch und Handbuch.

Standardmäßig ist der automatische Modus aktiviert, d. h. wenn ich eine Formel verwende oder Änderungen an den vorhandenen Formeln vornehme, führt er automatisch (und sofort) die Berechnung durch und liefert mir das Ergebnis.

Dies ist die Einstellung, an die wir alle gewöhnt sind.

In der automatischen Einstellung berechnet Excel jedes Mal, wenn Sie eine Änderung im Arbeitsblatt vornehmen (z. alles).

In einigen Fällen aktivieren die Benutzer jedoch die manuelle Berechnungseinstellung.

Dies geschieht meistens, wenn Sie eine umfangreiche Excel-Datei mit vielen Daten und Formeln haben. In solchen Fällen möchten Sie möglicherweise nicht, dass Excel alles neu berechnet, wenn Sie kleine Änderungen vornehmen (da es einige Sekunden oder sogar Minuten dauern kann), bis diese Neuberechnung abgeschlossen ist.

Wenn Sie die manuelle Berechnung aktivieren, berechnet Excel nur, wenn Sie dies erzwingen.

Und dies kann dazu führen, dass Sie denken, dass Ihre Formel nicht berechnet wird.

Alles, was Sie in diesem Fall tun müssen, ist entweder die Berechnung wieder auf automatisch zu setzen oder eine Neuberechnung durch Drücken der Taste F9 zu erzwingen.

Im Folgenden sind die Schritte aufgeführt, um die Berechnung von manuell auf automatisch umzustellen:

1. Klicken Sie auf die Registerkarte Formel
2. Klicken Sie auf Berechnungsoptionen
3. Wählen Sie Automatisch

Wichtig: Falls Sie die Berechnung von manuell auf automatisch umstellen, ist es eine gute Idee, eine Sicherungskopie Ihrer Arbeitsmappe zu erstellen (nur für den Fall, dass Ihre Arbeitsmappe hängen bleibt oder Excel abstürzt).

Mitnehmen / Wie man es repariert: Wenn Sie feststellen, dass Ihre Formeln nicht das erwartete Ergebnis liefern, versuchen Sie etwas Einfaches in einer beliebigen Zelle (z mit F9.

Löschen von Zeilen/Spalten/Zellen, die zu #REF führen! Fehler

Eines der Dinge, die verheerende Auswirkungen auf Ihre vorhandenen Formeln in Excel haben können, ist das Löschen von Zeilen/Spalten, die in Berechnungen verwendet wurden.

In diesem Fall passt Excel manchmal die Referenz selbst an und stellt sicher, dass die Formeln einwandfrei funktionieren.

Und manchmal… es kann nicht.

Zum Glück ist ein klarer Hinweis, den Sie erhalten, wenn Formeln beim Löschen von Zellen/Zeilen/Spalten unterbrechen, die #REF! Fehler in den Zellen. Dies ist ein Referenzfehler, der Ihnen mitteilt, dass ein Problem mit den Referenzen in der Formel vorliegt.

Lassen Sie mich Ihnen anhand eines Beispiels zeigen, was ich meine.

Unten habe ich die SUM-Formel verwendet, um die Zellen A2: A6 hinzuzufügen.

Wenn ich nun eine dieser Zellen/Zeilen lösche, gibt die SUM-Formel ein #REF zurück! Error. Dies liegt daran, dass die Formel beim Löschen der Zeile nicht weiß, worauf sie jetzt verweisen soll.

Sie können sehen, dass das dritte Argument in der Formel #REF geworden ist! (was sich zuvor auf die Zelle bezog, die wir gelöscht haben).

Mitnehmen / Wie man es repariert: Bevor Sie Daten löschen, die in Formeln verwendet werden, stellen Sie sicher, dass nach dem Löschen keine Fehler aufgetreten sind. Es wird auch empfohlen, regelmäßig ein Backup Ihrer Arbeit zu erstellen, um sicherzustellen, dass Sie immer auf etwas zurückgreifen können.

Falsche Platzierung von Klammern (BODMAS)

Wenn Ihre Formeln größer und komplexer werden, ist es eine gute Idee, Klammern zu verwenden, um absolut klar zu sein, welcher Teil zusammengehört.

In einigen Fällen kann es sein, dass Sie die Klammer an der falschen Stelle haben, was entweder zu einem falschen Ergebnis oder einem Fehler führen kann.

In einigen Fällen wird empfohlen, Klammern zu verwenden, um sicherzustellen, dass die Formel versteht, was zuerst gruppiert und berechnet werden muss.

Angenommen, Sie haben die folgende Formel:

=5+10*50

In der obigen Formel ist das Ergebnis 505, da Excel zuerst die Multiplikation und dann die Addition durchführt (da es bei den Operatoren eine Rangfolge gibt).

Wenn Sie möchten, dass zuerst die Addition und dann die Multiplikation durchgeführt wird, müssen Sie die folgende Formel verwenden:

=(5+10)*50

In einigen Fällen kann die Rangfolge für Sie funktionieren, aber es wird dennoch empfohlen, die Klammern zu verwenden, um Verwirrung zu vermeiden.

Falls Sie interessiert sind, finden Sie unten auch die Rangfolge für verschiedene Operatoren, die häufig in Formeln verwendet werden:

Operator	Beschreibung	Rangfolge
: (Doppelpunkt)	Bereich	1
(Einzelraum)	Überschneidung	2
, (Komma)	Union	3
-	Negation (wie in -1)	4
%	Prozentsatz	5
^	Potenzierung	6
* und /	Multiplikation & Division	7
+ und -	Addition Subtraktion	8
&	Verkettung	9
= =	Vergleich	10

Mitnehmen / Wie man es repariert: Verwenden Sie immer Klammern, um Verwechslungen zu vermeiden, auch wenn Sie die Rangfolge kennen und richtig verwenden. Klammern erleichtern die Prüfung von Formeln.

Falsche Verwendung von absoluten/relativen Zellreferenzen

Wenn Sie Formeln in Excel kopieren und einfügen, werden die Referenzen automatisch angepasst. Manchmal ist dies genau das, was Sie wollen (meistens, wenn Sie Formeln in die Spalte kopieren) und manchmal möchten Sie nicht, dass dies geschieht.

Ein absoluter Bezug liegt vor, wenn Sie einen Zellbezug (oder einen Bereichsbezug) so fixieren, dass er sich beim Kopieren und Einfügen von Formeln nicht ändert, und ein relativer Bezug ändert sich.

Weitere Informationen zu absoluten, relativen und gemischten Bezügen finden Sie hier.

Sie können ein falsches Ergebnis erhalten, falls Sie vergessen, die Referenz in eine absolute zu ändern (oder umgekehrt). Das passiert mir ziemlich oft, wenn ich Nachschlageformeln verwende.

Lassen Sie mich Ihnen ein Beispiel zeigen.

Unten habe ich einen Datensatz, in dem ich die Punktzahl in Prüfung 1 für die Namen in Spalte E abrufen möchte (ein einfacher SVERWEIS-Anwendungsfall).

Unten ist die Formel, die ich in Zelle F2 verwende und dann in alle Zellen darunter kopiert:

=SVERWEIS(E2,A2:B6,2,0)

Wie Sie sehen, gibt diese Formel in einigen Fällen einen Fehler aus.

Dies geschieht, weil ich das Tabellenarrayargument nicht gesperrt habe - es ist A2: B6 in Zelle F2, obwohl es hätte sein sollen $A$2:$B$6

Indem ich diese Dollarzeichen vor der Zeilennummer und dem Spaltenalphabet in einem Zellbezug habe, zwinge ich Excel, diese Zellbezüge fest zu halten. Selbst wenn ich diese Formel nach unten kopiere, bezieht sich das Tabellenarray weiterhin auf A2: B6

Profi-Tipp: Um einen relativen Bezug in einen absoluten zu konvertieren, wählen Sie diesen Zellbezug innerhalb der Zelle aus und drücken Sie die Taste F4. Sie würden feststellen, dass es sich ändert, wenn Sie die Dollarzeichen hinzufügen. Sie können weiterhin F4 drücken, bis Sie die gewünschte Referenz erhalten.

Falscher Verweis auf Blatt-/Arbeitsmappennamen

Wenn Sie in einer Formel auf andere Blätter oder Arbeitsmappen verweisen, müssen Sie einem bestimmten Format folgen. Und falls das Format falsch ist, erhalten Sie eine Fehlermeldung.

Wenn ich beispielsweise auf Zelle A1 in Sheet2 verweisen möchte, wäre die Referenz =Blatt2!A1 (wo ein Ausrufezeichen nach dem Blattnamen steht)

Und falls der Tabellenname mehrere Wörter enthält (sagen wir Beispieldaten), wäre die Referenz =’Beispieldaten’!A1 (wobei der Name in einfache Anführungszeichen gefolgt von einem Ausrufezeichen steht).

Falls Sie sich auf eine externe Arbeitsmappe beziehen (angenommen, Sie beziehen sich auf Zelle A1 in "Beispielblatt" in der Arbeitsmappe mit dem Namen "Beispielarbeitsmappe"), lautet die Referenz wie folgt:

='[Example Workbook.xlsx]Beispielblatt'!$A$1

Und falls Sie die Arbeitsmappe schließen, ändert sich die Referenz und enthält den gesamten Pfad der Arbeitsmappe (wie unten gezeigt):

='C:\Users\sumit\Desktop\[Example Workbook.xlsx]Example Sheet'!$A$1

Falls Sie am Ende den Namen der Arbeitsmappe oder des Arbeitsblatts ändern, auf das sich die Formel bezieht, erhalten Sie eine #REF! Error.

Lesen Sie auch: So finden Sie externe Links und Referenzen in Excel

Rundschreiben

Ein Zirkelbezug liegt vor, wenn Sie (direkt oder indirekt) auf dieselbe Zelle verweisen, in der die Formel berechnet wird.

Unten ist ein einfaches Beispiel, in dem ich die Summenformel in Zelle A4 verwende, während ich sie in der Berechnung selbst verwende.

=SUMME(A1:A4)

Obwohl Excel Ihnen eine Aufforderung anzeigt, die Sie über den Zirkelbezug informiert, wird dies nicht in jedem Fall durchgeführt. Und dies kann zu einem falschen Ergebnis führen (ohne Vorwarnung).

Falls Sie einen Zirkelbezug im Spiel vermuten, können Sie überprüfen, welche Zellen ihn haben.

Klicken Sie dazu auf die Registerkarte Formel und in der Gruppe „Formelprüfung“ auf das Dropdown-Symbol Fehlerprüfung (der kleine nach unten zeigende Pfeil).

Bewegen Sie den Cursor über die Option Zirkelbezug und es wird die Zelle mit dem Zirkelbezugsproblem angezeigt.

Als Text formatierte Zellen

Wenn Sie sich in einer Situation befinden, in der Sie, sobald Sie die Formel mit der Eingabetaste eingeben, die Formel anstelle des Werts sehen, ist dies ein klarer Fall, dass die Zelle als Text formatiert ist.

Wenn eine Zelle als Text formatiert ist, betrachtet sie die Formel als Textzeichenfolge und zeigt sie unverändert an.

Es zwingt es nicht, zu berechnen und das Ergebnis zu liefern.

Und es hat eine einfache Lösung.

1. Ändern Sie das Format von "Text" in "Allgemein" (es befindet sich auf der Registerkarte "Startseite" in der Gruppe "Zahlen").
2. Gehen Sie zu der Zelle mit der Formel, wechseln Sie in den Bearbeitungsmodus (verwenden Sie F2 oder doppelklicken Sie auf die Zelle) und drücken Sie die Eingabetaste

Falls die obigen Schritte das Problem nicht lösen, ist eine weitere Sache zu überprüfen, ob die Zelle am Anfang ein Apostroph hat. Viele Leute fügen einen Apostroph hinzu, um Formeln und Zahlen in Text umzuwandeln.

Wenn ein Apostroph vorhanden ist, können Sie es einfach entfernen.

Text wird automatisch in Datumsangaben umgewandelt

Excel hat diese schlechte Angewohnheit, ein Datum zu konvertieren, das wie ein Datum aussieht, in ein tatsächliches Datum. Wenn Sie beispielsweise 1/1 eingeben, konvertiert Excel dies in den 01. Januar des aktuellen Jahres.

In einigen Fällen kann dies genau das sein, was Sie wollen, und in einigen Fällen kann dies gegen Sie funktionieren.

Und da Excel Datums- und Uhrzeitwerte als Zahlen speichert, wandelt es, sobald Sie 1/1 eingeben, in eine Zahl um, die den 1. Januar des aktuellen Jahres darstellt. In meinem Fall, wenn ich dies tue, wird es in die Nummer 43831 (für den 01.01.2020) umgewandelt.

Dies könnte Ihre Formeln durcheinander bringen, wenn Sie diese Zellen als Argument in einer Formel verwenden.

Wie kann man das beheben?

Auch hier möchten wir nicht, dass Excel automatisch das Format für uns auswählt, daher müssen wir das Format selbst eindeutig angeben.

Im Folgenden finden Sie die Schritte zum Ändern des Formats in Text, damit Text nicht automatisch in Datumsangaben konvertiert wird:

1. Wählen Sie die Zellen/den Bereich aus, in denen Sie das Format ändern möchten
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Zahlen auf das Dropdown-Menü Format
4. Klicken Sie auf Text

Wenn Sie jetzt etwas in die ausgewählten Zellen eingeben, wird es als Text betrachtet und nicht automatisch geändert.

Hinweis: Die obigen Schritte funktionieren nur für Daten, die nach einer Änderung der Formatierung eingegeben wurden. Es wird kein Text geändert, der in das Datum konvertiert wurde, bevor Sie diese Formatierungsänderung vorgenommen haben.

Ein weiteres Beispiel, bei dem dies sehr frustrierend sein kann, ist die Eingabe eines Textes/einer Zahl mit führenden Nullen. Excel entfernt diese führenden Nullen automatisch, da es diese für nutzlos hält. Wenn Sie beispielsweise 0001 in eine Zelle eingeben, ändert Excel dies in 1. Falls Sie diese führenden Nullen beibehalten möchten, verwenden Sie die obigen Schritte.

Ausgeblendete Zeilen/Spalten können zu unerwarteten Ergebnissen führen

Dies ist kein Fall von Formeln, die Ihnen das falsche Ergebnis liefern, sondern von der Verwendung der falschen Formel.

Angenommen, Sie haben ein Dataset wie unten gezeigt und ich möchte die Summe aller sichtbaren Zellen in Spalte C erhalten.

In Zelle C12 habe ich die SUM-Funktion verwendet, um den Gesamtverkaufswert für alle diese angegebenen Datensätze zu erhalten.

So weit, ist es gut!

Jetzt wende ich einen Filter auf die Artikelspalte an, um nur die Datensätze für Druckerverkäufe anzuzeigen.

Und hier ist das Problem - die Formel in Zelle C12 zeigt immer noch das gleiche Ergebnis - d. h. die Summe aller Datensätze.

Wie gesagt, die Formel liefert kein falsches Ergebnis. Tatsächlich funktioniert die SUM-Funktion einwandfrei.

Das Problem ist, dass wir hier die falsche Formel verwendet haben.

Die SUM-Funktion kann die gefilterten Daten nicht berücksichtigen und Ihnen das Ergebnis für alle Zellen (ausgeblendet oder sichtbar) anzeigen. Wenn Sie nur die Summe/Anzahl/Durchschnitt der sichtbaren Zellen erhalten möchten, verwenden Sie die Funktionen ZWISCHENSUMME oder AGGREGAT.

Schlüssel zum Mitnehmen - Verstehen Sie die korrekte Verwendung und Einschränkungen einer Funktion in Excel.

Dies sind einige der häufigsten Ursachen, die ich gesehen habe, wo Ihr Excel-Formeln funktionieren möglicherweise nicht oder liefern unerwartete oder falsche Ergebnisse. Ich bin mir sicher, dass es noch viele weitere Gründe dafür gibt, dass eine Excel-Formel nicht funktioniert oder aktualisiert wird.

Falls Sie einen anderen Grund kennen (vielleicht etwas, das Sie oft ärgert), lassen Sie es mich im Kommentarbereich wissen.

Ich hoffe, Sie fanden dieses Tutorial nützlich!