Mögliche Gründe und wie man es behebt!

Wenn Sie mit arbeiten Formeln in ExcelFrüher oder später werden Sie 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 folgt Pareto-PrinzipWenn Sie nach häufigen Problemen suchen, werden wahrscheinlich 80% (oder sogar 90% oder 95% der Probleme, bei denen Formeln in Excel nicht funktionieren) behoben.

Und in diesem Artikel werde ich die häufigsten Probleme hervorheben, die wahrscheinlich die Ursache für Ihre Probleme sind 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 – z. B. die Anzahl der Argumente oder die Art der Argumente, die sie akzeptieren kann.

In vielen Fällen kann der Grund dafür, dass Ihre Excel-Formeln nicht funktionieren oder das falsche Ergebnis liefern, ein falsches Argument (oder fehlende Argumente) sein.

Zum Beispiel die VLOOKUP-Funktion akzeptiert drei obligatorische Argumente und ein optionales Argument.

Wenn Sie ein falsches Argument angeben oder das optionale Argument nicht angeben (wo es benötigt wird, damit die Formel funktioniert), erhalten Sie ein falsches Ergebnis.

Angenommen, Sie haben einen Datensatz wie unten gezeigt, in dem Sie die Punktzahl von Mark 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 (das nach der Spaltenindexnummer fragt) den falschen Wert verwende.

=VLOOKUP(A2,A2:C6,2,FALSE)

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

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

Da Sie ein optionales Argument verwenden müssen, um anzugeben, wo VLOOKUP eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung durchführen soll, kann es zu Problemen kommen, wenn Sie dies nicht angeben (oder das falsche Argument verwenden).

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

Wenn ich die folgende VLOOKUP-Formel verwende, wird für einige Namen ein Fehler angezeigt.

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

Dies geschieht, weil 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 wir in diesem Fall eine genaue Übereinstimmung vornehmen mussten, gibt die Formel für einige Namen einen Fehler zurück.

Während ich das Beispiel der VLOOKUP-Funktion genommen habe, kann dies in diesem Fall für viele Excel-Formeln gelten, die auch optionale Argumente haben.

Also read: Identify Errors Using Excel Formula Debugging

Zusätzliche Leerzeichen, die zu unerwarteten Ergebnissen führen

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

Wenn ich im folgenden Beispiel beispielsweise versuche, mit VLOOKUP die Punktzahl für Mark abzurufen, wird der Fehler # N / A angezeigt (der nicht verfügbare Fehler).

Obwohl ich sehen kann, dass die Formel korrekt ist und der Name „Markieren“ eindeutig in der Liste enthalten ist, kann ich nicht sehen, dass in der Zelle ein nachfolgendes Leerzeichen mit dem Namen vorhanden ist (in Zelle D2).

Excel betrachtet den Inhalt dieser beiden Zellen nicht als gleich und betrachtet ihn daher als nicht übereinstimmend, wenn der Wert mit VLOOKUP abgerufen wird (oder es könnte sich um eine andere Suchformel handeln).

Um dieses Problem zu beheben, müssen Sie Entfernen Sie diesen zusätzlichen Platz Zeichen.

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

1. Reinigen Sie die Zelle und Entfernen Sie alle führenden / nachfolgenden Leerzeichen bevor Sie es in Formeln verwenden
2. Verwenden Sie die TRIM-Funktion in 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.

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

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

Zum Beispiel, wenn ich die LEN-Funktion Um die Gesamtzahl der Zeichen in einer Zelle zu zählen, würden diese, wenn es führende oder nachfolgende Leerzeichen gibt, ebenfalls gezählt und ergeben das falsche Ergebnis.

Take away / Wie zu beheben: Wenn möglich, Bereinigen Sie Ihre Daten indem Sie führende / nachfolgende oder doppelte 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 gewährleisten.

Verwenden der manuellen Berechnung anstelle der automatischen

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

Excel hat zwei Berechnungsmodi – Automatisch und Handbuch.

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

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

In der automatischen Einstellung berechnet Excel bei jeder Änderung im Arbeitsblatt (z. B. Eingabe einer neuen Formel für Text in einer Zelle) automatisch alles neu (ja, alles).

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

Dies geschieht meistens, wenn Sie eine schwere 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 dies 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 könnte Sie denken lassen, dass Ihre Formel nicht berechnet.

In diesem Fall müssen Sie entweder die Berechnung entweder auf automatisch zurücksetzen oder eine Neuberechnung erzwingen, indem Sie die Taste F9 drücken.

Im Folgenden finden Sie die Schritte zum Ändern der Berechnung von manuell auf automatisch:

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

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

Take away / Wie zu beheben: Wenn Sie feststellen, dass Ihre Formeln nicht das erwartete Ergebnis liefern, versuchen Sie es in einer beliebigen Zelle mit etwas Einfachem (z. B. Hinzufügen von 1 zu einer vorhandenen Formel. Wenn Sie das Problem als das Problem identifiziert haben, bei dem der Berechnungsmodus geändert werden muss, führen Sie eine Kraftberechnung durch mit F9.

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

Eines der Dinge, die sich verheerend auf Ihre vorhandenen Formeln in Excel auswirken 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 ordnungsgemäß 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 unterbrochen werden, das #REF! Fehler in den Zellen. Das ist ein Referenzfehler Das sagt Ihnen, dass es ein Problem mit den Referenzen in der Formel gibt.

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

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

Wenn ich nun eine dieser Zellen / Zeilen lösche, gibt die SUMME-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 früher auf die Zelle verwies, die wir gelöscht haben).

Take away / Wie zu beheben: Stellen Sie vor dem Löschen von Daten, die in Formeln verwendet werden, sicher, dass nach dem Löschen keine Fehler vorliegen. Es wird außerdem empfohlen, regelmäßig ein Backup Ihrer Arbeit zu erstellen, um sicherzustellen, dass Sie immer auf etwas zurückgreifen können.

Falsche Platzierung der Klammern (BODMAS)

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

In einigen Fällen befindet sich die Klammer möglicherweise an der falschen Stelle, was entweder zu einem falschen Ergebnis oder zu 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 lautet das Ergebnis 505, da Excel zuerst die Multiplikation und dann die Addition durchführt (da es bei 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 funktioniert die Rangfolge möglicherweise für Sie. Es wird jedoch empfohlen, die Klammer zu verwenden, um Verwirrung zu vermeiden.

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

Operator	Beschreibung	Rangfolge
: (Doppelpunkt)	Angebot	1
(einzelnes Leerzeichen)	Überschneidung	2
, (Komma)	Union	3
– –	Negation (wie in –1)	4
%.	Prozentsatz	5
^	Potenzierung	6
* und /	Multiplikation & Division	7
+ und –	Addition Subtraktion	8
&	Konzentration	9
= < > <= >= <>	Vergleich	10

Take away / Wie zu beheben: Verwenden Sie immer Klammern, um Verwirrung zu vermeiden, auch wenn Sie die Rangfolge kennen und diese korrekt 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 möchten (meistens, wenn Sie Formeln in die Spalte einfügen), und manchmal möchten Sie nicht, dass dies geschieht.

Eine absolute Referenz ist, wenn Sie eine Zellreferenz (oder Bereichsreferenz) so festlegen, dass sie sich beim Kopieren und Einfügen von Formeln nicht ändert, und eine relative Referenz ändert sich.

Sie können mehr darüber lesen absolute, relative und gemischte Referenzen hier.

Möglicherweise erhalten Sie ein falsches Ergebnis, wenn Sie vergessen, die Referenz in eine absolute zu ändern (oder umgekehrt). Dies passiert mir ziemlich oft, wenn ich Suchformeln 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 VLOOKUP-Anwendungsfall).

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

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

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

Dies geschieht, weil ich das Tabellenarray-Argument nicht gesperrt habe A2: B6 in Zelle F2, während es hätte sein sollen $ A $ 2: $ B $ 6

Indem ich diese Dollarzeichen vor der Zeilennummer und dem Spaltenalphabet in einer Zellreferenz habe, zwinge ich Excel, diese Zellreferenzen fest zu halten. Selbst wenn ich diese Formel nach unten kopiere, verweist das Tabellenarray weiterhin auf A2: B6

Pro Tipp: Um eine relative Referenz in eine absolute zu konvertieren, wählen Sie diese Zellreferenz innerhalb der Zelle aus und drücken Sie die Taste F4. Sie werden feststellen, dass sich dies durch Hinzufügen der Dollarzeichen ändert. 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 Blatt 2 verweisen möchte, lautet die Referenz = Sheet2! A1 (wo nach dem Blattnamen ein Ausrufezeichen steht)

Und falls der Blattname mehrere Wörter enthält (sagen wir, es handelt sich um Beispieldaten), lautet die Referenz = „Beispieldaten“! A1 (wobei der Name in einfache Anführungszeichen gefolgt von einem Ausrufezeichen eingeschlossen ist).

Wenn Sie sich auf eine externe Arbeitsmappe beziehen (nehmen wir an, Sie beziehen sich auf Zelle A1 in „Beispielblatt“ in der Arbeitsmappe mit dem Namen „Beispielarbeitsmappe“), lautet die Referenz wie folgt:

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

Wenn Sie die Arbeitsmappe schließen, ändert sich die Referenz und enthält den gesamten Pfad der Arbeitsmappe (siehe Abbildung unten):

='C:UserssumitDesktop[Example Workbook.xlsx]Example Sheet'!$A$1

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

Also read: How to Find External Links and References in Excel

Rundschreiben

EIN zirkulärer Verweis Dies ist der Fall, wenn Sie (direkt oder indirekt) auf dieselbe Zelle verweisen, in der die Formel berechnet wird.

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

=SUM(A1:A4)

Obwohl Excel eine Eingabeaufforderung anzeigt, die Sie über den Zirkelverweis informiert, wird dies nicht für jede Instanz ausgeführt. Und dies kann zu einem falschen Ergebnis führen (ohne Vorwarnung).

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

Klicken Sie dazu auf die Registerkarte „Formel“ und klicken Sie in der Gruppe „Formelüberwachung“ auf das Dropdown-Symbol „Fehlerprüfung“ (den kleinen nach unten zeigenden Pfeil).

Bewegen Sie den Mauszeiger über die Option „Rundschreiben“, um die Zelle mit dem Problem mit dem Rundschreiben anzuzeigen.

Als Text formatierte Zellen

Wenn Sie sich in einer Situation befinden, in der Sie, sobald Sie die Formel als Eingabetaste eingeben, eingeben siehe die Formel anstelle des WertesEs ist ein klarer Fall, dass die Zelle als Text formatiert wird.

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

Es wird nicht gezwungen, das Ergebnis zu berechnen und anzugeben.

Und es hat eine einfache Lösung.

1. Ändern Sie das Format von „Text“ in „Allgemein“ (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 oben genannten Schritte das Problem nicht lösen, müssen Sie auch prüfen, ob die Zelle am Anfang ein Apostroph hat. Viele Leute fügen ein Apostroph hinzu, um Formeln und Zahlen in Text umzuwandeln.

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

Text wird automatisch in Datumsangaben konvertiert

Excel hat die schlechte Angewohnheit, ein Datum in ein tatsächliches Datum umzuwandeln. Wenn Sie beispielsweise 1/1 eingeben, konvertiert Excel diese in den 01. Januar des aktuellen Jahres.

In einigen Fällen kann dies genau das sein, was Sie möchten, und in einigen Fällen kann dies gegen Sie wirken.

Und da Excel Datums- und Zeitwerte als Zahlen speichert, wird es bei Eingabe von 1/1 in eine Zahl umgewandelt, die den 1. Januar des aktuellen Jahres darstellt. In meinem Fall wird es in die Nummer 43831 (für den 01.01.2020) umgewandelt.

Dies könnte Ihre Formeln beeinträchtigen, wenn Sie diese Zellen als Argument in einer Formel verwenden.

Wie kann ich das beheben?

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

Im Folgenden finden Sie die Schritte zum Ändern des Formats in Text, damit es nicht automatisch erfolgt Text in Datumsangaben konvertieren:

1. Wählen Sie die Zellen / Bereiche aus, in denen Sie das Format ändern möchten
2. Klicken Sie auf die Registerkarte Home
3. Klicken Sie in der Gruppe Nummer auf die Dropdown-Liste Format
4. Klicken Sie auf Text

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

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

Ein anderes 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 diese in 1. Wenn Sie diese führenden Nullen beibehalten möchten, führen Sie die obigen Schritte aus.

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

Hierbei handelt es sich nicht um eine Formel, die zu einem falschen Ergebnis führt, sondern um die Verwendung einer falschen Formel.

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

In Zelle C12 habe ich die verwendet SUM-Funktion 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 den Druckerverkauf 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 nicht das falsche Ergebnis. Tatsächlich funktioniert die SUMME-Funktion einwandfrei.

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

Die SUMME-Funktion kann die gefilterten Daten nicht berücksichtigen und gibt Ihnen das Ergebnis für alle Zellen (versteckt oder sichtbar). Wenn Sie nur die Summe / Anzahl / den Durchschnitt der sichtbaren Zellen erhalten möchten, verwenden Sie die Funktionen SUBTOTAL oder AGGREGATE.

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 Ihre Excel-Formeln funktionieren möglicherweise nicht oder führen zu unerwarteten oder falschen Ergebnissen. Ich bin sicher, es gibt noch viele weitere Gründe, warum 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 hilfreich!

Möglicherweise gefallen Ihnen auch die folgenden Excel-Tutorials:

Source link

Der Beitrag Mögliche Gründe und wie man es behebt! erschien zuerst auf Anfänger Kurse.

from
https://fuer-anfaenger.info/moegliche-gruende-und-wie-man-es-behebt/