„Oh mein Gott“ Excel-Formeln funktionieren nicht in meinem Bericht.
Kommt Ihnen das bekannt vor?
Wenn ja, machen Sie sich keine Sorgen, Sie sind nur einer von vielen Excel-Anwendern, die dieses Problem sehr oft haben.
Stellen Sie sich vor:
Sie haben ein Excel-Modell für die Umsatzprognose mit verschiedenen Logiken und ziemlich komplizierten Formeln erstellt.
Und wenn Sie versuchen, dieses Modell zu verwenden, sehen Sie, dass einige Ihrer Formeln entweder nicht funktionieren oder kaputt aussehen.
Grundsätzlich können hier zwei Dinge passieren:
Einerseits möchten Sie vielleicht etwas laut sagen.
Zweitens machen Sie sich die Mühe und reparieren alle Formeln.
Um diesen ganzen Ärger zu vermeiden, haben wir diesen ausführlichen Artikel geschrieben, der die meisten Gründe für nicht funktionierende Excel-Formeln abdeckt.
Und wie man sie behebt.
Excel-Formeln funktionieren nicht: Warum &Und wie man sie behebt
Nach dem Artikel der Washington Post über Excel-Fehler „fanden die australischen Forscher heraus, dass etwa 1 von 5 dieser Arbeiten Fehler in ihren Genlisten enthielt, die darauf zurückzuführen waren, dass Excel Gennamen automatisch in Dinge wie Kalenderdaten oder zufällige Zahlen konvertierte.“
Die ursprüngliche Untersuchung wurde vom Journal Genome Biology durchgeführt und ergab, dass es einen steigenden Trend von Jahr zu Jahr in Bezug auf die Anzahl der Fehler gibt.
Das komplette Forschungsmaterial kann von der SourceForge-Website (Open Source) heruntergeladen werden, um es weiter zu lesen.
Das obige Forschungspapier steht zwar nicht in direktem Zusammenhang mit Formeln, die in Excel nicht funktionieren, aber es gibt hier einen Punkt:
Sehr gut gebaute &getestete Software wie MS Excel kann Fehler machen, wenn es darum geht, unsere Daten zu verstehen – was ist mit Ihnen & mir?
So, hier sind unsere Top 10 Gründe
#1 Berechnungsoptionen
Die Berechnungsoption auf „manuell“ zu ändern, ist eine bewährte Methode, aber Sie müssen bei der Verwendung sehr vorsichtig sein.
Standardmäßig ist die Berechnungsoption in Excel auf „automatisch“ eingestellt.
Aber es gibt Fälle, in denen Sie die Option auf ‚manuell‘ gestellt haben, um den Prozess zu beschleunigen.
Wenn Sie diese Option auf ‚EIN‘ stellen, werden Ihre Formeln nicht automatisch aktualisiert, was wiederum die Geschwindigkeit der Tabellenkalkulation erhöht.
Sobald Sie fertig waren, müssen Sie auf ‚Speichern‘ oder ‚Arbeitsmappe aktualisieren‘ klicken, um neu zu berechnen. Wenn Sie das nicht getan haben, erhalten Sie am Ende falsche Ergebnisse.
Wie Sie dieses Problem beheben können:
Gehen Sie zu Formeln → Berechnung → Klicken Sie auf die Option Jetzt berechnen/Blatt berechnen.
Alternativ können Sie auch das Tastenkürzel F9 verwenden, um die Arbeitsmappe zu aktualisieren.
Hier ist unsere Videoanleitung zu den Excel-Berechnungsoptionen.
Tipps:
- Gewöhnen Sie sich an, Ctl+S zu drücken
- Nutzen Sie diese Option nicht für jede Arbeitsmappe, es sei denn, Sie haben eine große Datei mit vielen Formeln
- Diese Option ist arbeitsmappenspezifisch, daher müssen Sie sie in jeder Arbeitsmappe, die Sie verwenden möchten, ein-/ausschalten
#2 Excel-Formel nicht berechnen, nur Formel anzeigen
Formeln anzeigen ist eine Option in Excel, um alle Formeln der Arbeitsmappe mit einem einzigen Klick anzuzeigen.
Diese Option ist sehr nützlich, um alle Formeln in einem Arbeitsblatt herauszufinden.
Allerdings muss man bei der Verwendung der Option „Formeln anzeigen“ ein wenig vorsichtig sein, sonst könnte man am Ende etwas wie unten sehen.
Können Sie sehen, dass die Formel „Heute“ als Formel angezeigt wird, anstatt das tatsächliche Datum anzuzeigen?
Gleiches gilt auch für die Randbetween-Formeln.
Wie Sie dieses Problem beheben können:
Es ist ganz einfach:
Gehen Sie zu Formeln → Unter Formelprüfung → Klicken Sie auf „Formeln anzeigen“ (Beim ersten Klick werden die Formeln angezeigt und beim zweiten Klick verstecken sie sich hinter den Zellen und beginnen zu arbeiten)
Auch lesen: Die 51 besten Excel-Tipps und -Tricks
#3 Extra-Leerzeichen bereiten Kopfzerbrechen
Sie haben Daten im CSV-Format (comma separated values) extrahiert, dann sind Ihre Daten meist unerwünschten Leerzeichen ausgesetzt.
Wussten Sie, dass zusätzliche Leerzeichen bei der Verwendung von Vlookup-Formeln eine Menge Probleme verursachen?
Manchmal ist es ziemlich schwierig, diese Leerzeichen zu identifizieren.
Sagen wir zum Beispiel, dass Sie Vlookup mit dem Namen des Mitarbeiters als Basis durchführen möchten, so sieht der Name des Mitarbeiters wie folgt aus
Haben Sie es bemerkt?
Obwohl beide Namen mit bloßem Auge ähnlich aussehen, befindet sich im zweiten Namen ein Leerzeichen.
Dies würde die Kriterien für eine exakte Übereinstimmung in der Vlookup-Formel nicht erfüllen und führt daher zu einem #N/A-Fehler.
Wie Sie dieses Problem beheben können:
Das Problem der zusätzlichen Leerzeichen kann mit der Trim-Formel behoben werden.
Im Allgemeinen findet die Trim-Formel mehr als ein Leerzeichen zwischen den Wörtern heraus und löscht sie.
Tipps:
- Es ist immer eine gute Idee, die Trim-Formel vor Vlookup in beiden Tabellen zu verwenden (Lookup & Datentabelle)
- Die Trim-Funktion wird Leerzeichen zwischen Buchstaben ignorieren (funktioniert nur bei Wörtern)
#4 Nicht druckbare/versteckte Zeichen loswerden
Ähnlich wie bei führenden Leerzeichen, sind auch nicht druckbare Zeichen einer der Gründe dafür, dass Excel-Formeln nicht funktionieren.
Wenn Sie Daten mit diesen Zeichen haben, ist es besser, sie zu entfernen, bevor Sie weiterarbeiten.
Wie Sie dieses Problem beheben:
Ähnlich wie beim Trimmen können Sie die Formel CLEAN verwenden, um diese Zeichen zu löschen.
#5 Excel-Formatierung – Versuchen Sie nicht, Äpfel mit Orangen zu vergleichen
Es ist sehr wichtig, die richtige Formatierung zu haben, bevor Sie Formeln wie Vlookup, Hlookup und Match & Index verwenden.
Zahlen, die als Text formatiert sind, sind einer der häufigsten Gründe dafür, dass Formeln in Excel nicht funktionieren.
Obwohl sie wie Zahlen aussehen, sind sie es nicht.
Zum Beispiel können Sie auf der linken Seite sehen, dass die Kunden-ID ähnlich wie Zahlen aussieht.
Wenn Sie aber den obigen Formatierungsabschnitt betrachten, können Sie sehen, dass sie als Text formatiert wurden.
Dies wiederum löst beim Hinzufügen der Vlookup-Formel den Fehler #N/A aus.
Das Schlimmste ist, dass es ein bisschen schwierig zu identifizieren ist.
Wie Sie dieses Problem beheben können:
Stellen Sie sicher, dass alle Ihre Zahlen als Zahlen und nicht als Text formatiert sind. Es lohnt sich, die Lookup-Spalte neu zu formatieren, bevor Sie die Vlookup-Formel verwenden.
Tipps:
- In der Regel werden Zahlen immer am rechten Rand der Zelle ausgerichtet (in unserem obigen Beispiel sind sie das aber nicht)
- Manchmal sehen Sie einen kleinen grünen Hinweis in der linken oberen Ecke, der darauf hinweist, dass mit dieser Zelle etwas nicht stimmt
#6 Kreisförmige Bezüge (Fehler durch einen Endloskreis)
Einfach ausgedrückt, befindet sich Ihr Formelbereich in einer Endlosschleife oder er ist im Kreis.
Nach den Excel-Rechenregeln sollten Ihre Formeln keine Ergebniszellen als Bereich referenzieren. Wenn Sie das tun, würden kreisförmige Referenzfehler auftreten.
Persönlich habe ich schon oft mit dieser Art von Fehlern zu kämpfen gehabt.
Zum Beispiel müssen wir in unserer unten stehenden Tabelle „Ausgabenbudget“ einen Pfropfen (Anpassungszahl) hinzufügen, um mit den Zielen übereinzustimmen.
So haben wir den Differenzbetrag in Zeile 13 berechnet, und jetzt müssen wir ihn nur noch in Zeile 9 einfügen.
Eine einfache Möglichkeit ist, die Zellen der Zeile 13 in Zeile 9 zu verknüpfen.
Wenn Sie das tun, funktioniert Ihre Verknüpfungsformel nicht, stattdessen sehen Sie Null und eventuell auch blau eingefärbte Pfeile, die auf Zellbezüge hinweisen.
Im Allgemeinen sollte es funktionieren.
Technisch wird es aber ein Fehler sein, weil unsere Formelzellbezüge in einer Schleife stehen.
Wie Sie dieses Problem beheben können:
Zuerst müssen wir die Fehlerzellen identifizieren.
Gehen Sie zur Registerkarte „Formeln“ → klicken Sie auf den Dropdown-Pfeil „Fehlerprüfung“ → platzieren Sie den Cursor auf „Zellbezüge“ → jetzt sehen Sie den/die Fehlerbereich(e)
Zweitens, jetzt kennen Sie den Bereich mit zirkulären Bezügen, gehen Sie zu jeder Fehlerzelle und beheben Sie die Formeln.
#7 Doppelte Anführungszeichen falsch verwenden – menschlicher Fehler
Wir verwenden beim Schreiben von Formeln oft doppelte Anführungszeichen.
Aber es ist sehr wichtig zu verstehen, wann man sie verwendet und wann nicht.
Zum Beispiel können wir eine Formel wie die folgende verwenden, um zwei Zahlen zu verbinden.
=1234&5678 = 12345678
Um zwei Textstrings zu verbinden, können Sie nicht dasselbe tun, sondern müssen den Text in doppelten Anführungszeichen angeben.
=John&Smith: Falsche Art der Verwendung
=“John“&“Smith“ = Das ist die richtige Art.
Gleiches gilt für bedingte Formeln wie IF, AND, OR. Es ist sehr wichtig, den Text innerhalb von doppelten Anführungszeichen anzugeben.
Ansonsten ist dies einer der Gründe, warum Excel-Formeln nicht funktionieren.
#8 BODMAS – Grundregel für jede Berechnung
Wussten Sie, dass jede Berechnung in Excel auf der ‚BODMAS‘-Regel basiert?
Oh, Moment mal… was ist die BODMAS-Regel?
Es ist eine mathematische Regel, um eine Reihenfolge von Operationen zu bestimmen.
Ok, vereinfachen wir weiter.
Können Sie erraten, was die Antwort für die folgende Aufgabe ist?
10+10*2 =?
Ist das 40, herzlichen Glückwunsch, Sie liegen falsch.
Können Sie dieselbe Berechnung in Excel versuchen?
Sie werden höchstwahrscheinlich 30 sehen, das ist in der Tat die richtige Antwort… aber wie?
Nach der BODMAS-Regel erfolgt die Berechnung nach einer Reihenfolge, d.h. → B- Klammern, O- Ordnung, D- Division, M- Multiplikation, A- Addition & S- Subtraktion.
Die Reihenfolge der obigen Formel ist also → 10*2 = 20 → 20+10 =30.
Die Formeln können schief gehen, wenn Sie BODMAS nicht berücksichtigen.
Wie Sie dieses Problem beheben können:
Es ist sehr einfach, Sie müssen nur die Reihenfolge ändern, indem Sie Klammern wie unten verwenden.
=(30+40)*2
So wird alles, was in Klammern steht, zuerst berechnet und dann geht es zur nächsten Operation.
#9 Falsche Verwendung der „absoluten“ Referenzierung
Jeder, der Excel benutzt, weiß meistens, was eine absolute Referenzierung ist.
Wenn nicht, hören Sie bitte hier auf und lernen Sie es. Hier ist ein kurzes Tutorial über Zellbezüge.
Mit Hilfe der absoluten Referenzierung können Sie Ihren Formelbereich so fixieren, dass Sie von einer Zelle zur anderen kopieren können.
Das ist in der Tat eine sehr nützliche Funktion. Wenn Sie allerdings nicht wissen, wie man sie einsetzt, werden Ihre Formeln nicht so funktionieren, wie sie es beabsichtigen.
Das ist sicherlich einer der Hauptgründe dafür, dass Excel-Formeln beim Kopieren von einer Zelle in eine andere Zelle nicht funktionieren.
In unserer obigen Tabelle haben wir zum Beispiel eine absolute Zellreferenzierung verwendet, um uns das Kopieren von Formeln zu erleichtern.
Aber wir haben einen Fehler gemacht.
Anstatt ein partielles Einfrieren zu verwenden, haben wir die Tastenkombination F4 benutzt und den gesamten Bereich fixiert.
Das hat tatsächlich zu falschen Ergebnissen geführt.
Der korrekte Weg wäre also gewesen, den absoluten Bezug nur für eine Zeile zu verwenden, z. B. =SUMME(D$3:D$6).
Wie Sie dieses Problem beheben können:
Leider gibt es keine schnellen Lösungen für diese Art von Fehlern. Sie müssen es manuell tun, indem Sie alle Formeln analysieren.
Aber der beste Weg, den ich bisher gefunden habe, ist, die Ergebnisse von Formeln & zu überprüfen, sobald ich absolute & relative Referenzierung verwende.
Bitte lassen Sie mich an dieser Stelle Ihre Lösungsvorschläge wissen.
Tipps:
- Benutzen Sie nicht die F4-Tastenkombination für partielles Einfrieren
- Versuchen Sie, ein Dollarzeichen ($) manuell für einen Teilbereich einzufügen
#10 Falsche Formelargumente
Falsche Formelargumente können sich negativ auf die Formelergebnisse auswirken.
Zur Veranschaulichung wollen wir die folgende vlookup-Syntax verstehen, insbesondere die letzte.
Das Argument „Range Lookup“ hat zwei Optionen zur Auswahl.
- True – Ungefähre Übereinstimmung
- False – Exakte Übereinstimmung
Persönlich empfinde ich die Verwendung der Option „Ungefähre Übereinstimmung“ wie Selbstmord.
Denn eine ungefähre Übereinstimmung wird niemals genaue Ergebnisse liefern.
In unserem obigen Beispiel haben wir eine Vlookup-Formel in Zelle F3 hinzugefügt, um den entsprechenden Umsatzbetrag herauszufinden.
Wenn Sie genau hinsehen, ist der Umsatzbetrag 25k für die Kunden-ID 6378993 falsch. Stattdessen hätte es 65k sein müssen.
Was ist also schief gelaufen?
Wie bereits erwähnt, habe ich unter dem Argument „Range Lookup“ die „Ungefähre Übereinstimmung“ verwendet.
Die „Übereinstimmung“ ist eine weitere Formel, bei der Sie die Möglichkeit haben, ähnliche Argumente auszuwählen.
Wie Sie dieses Problem beheben können:
Verwenden Sie niemals ‚Approximate match‘ in Ihren vlookup-Formeln.
Jetzt sind Sie dran
Sind Sie der Meinung, dass Sie andere Gründe dafür haben, dass Excel-Formeln nicht funktionieren?
Oder, vielleicht haben Sie eine Frage zu stellen.