Verhindern von Formelfehlern in Excel mithilfe von IFERROR oder IFNA. Diese sind besser als die alten ISERROR ISERR und ISNA. Erfahren Sie hier mehr.
Formelfehler können häufig auftreten. Wenn Sie einen Datensatz mit Hunderten von Datensätzen haben, wird ab und zu eine Division durch Null oder ein # N / A angezeigt.
In der Vergangenheit waren zur Verhinderung von Fehlern herkulische Anstrengungen erforderlich. Nicken Sie wissentlich mit dem Kopf, wenn Sie jemals ausgeknockt haben =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Diese Lösung ist nicht nur sehr langwierig, sondern erfordert auch, dass Excel doppelt so viele VLOOKUPs ausführt. Zuerst führen Sie einen VLOOKUP durch, um festzustellen, ob der VLOOKUP einen Fehler erzeugt. Dann führen Sie denselben VLOOKUP erneut aus, um das fehlerfreie Ergebnis zu erhalten.
In Excel 2010 wurde das stark verbesserte = IFERROR (Formula, Value If Error) eingeführt. Ich weiß, dass IFERROR wie die alten alten ISERROR-, ISERR- und ISNA-Funktionen klingt, aber es ist völlig anders.
Dies ist eine brillante Funktion : =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Wenn Sie 1.000 VLOOKUPs haben und nur 5 # N / A zurückgeben, benötigt der 995, der funktioniert hat, nur einen einzigen VLOOKUP. Nur die 5, die # N / A zurückgegeben hat, müssen zum zweiten Argument von IFERROR übergehen.
Seltsamerweise hat Excel 2013 die Funktion IFNA () hinzugefügt. Es ist genau wie IFERROR, sucht aber nur nach # N / A-Fehlern. Man könnte sich eine seltsame Situation vorstellen, in der der Wert in der Nachschlagetabelle gefunden wird, die resultierende Antwort jedoch eine Division durch 0 ist. Wenn Sie den Fehler zum Teilen durch Null aus irgendeinem Grund beibehalten möchten, wird IFNA () dies tun.
Natürlich sollte die Person, die die Nachschlagetabelle erstellt hat, IFERROR verwendet haben, um die Division durch Null zu verhindern. In der folgenden Abbildung ist "nm" der Code eines früheren Managers für "nicht aussagekräftig".
Vielen Dank an Justin Fishman, Stephen Gilmer und Excel von Joe.
Schau Video
- In den alten Tagen würden Sie verwenden
=IF(ISNA(Formula),0,Formula) - Ab Excel 2010
=IFERROR(Formula,0) - Aber IFERROR behandelt DIV / 0-Fehler genauso wie # N / A! Fehler
- Verwenden Sie ab Excel 2013 die
=IFNA(Formula,0)Option, um nur # N / A-Fehler zu erkennen - Vielen Dank an Justin Fishman, Stephen Gilmer und Excel von Joe.
Video-Transkript
Lerne Excel aus Podcast, Folge 2042 - IFERROR und IFNA!
Ich werde alle meine Tipps aus diesem Buch podcasten. Klicken Sie auf das "i" in der oberen rechten Ecke, um zur gesamten Wiedergabeliste zu gelangen!
Okay, lassen Sie uns zu den alten Zeiten zurückkehren, Excel 2007 oder früher, wenn Sie die # N / A verhindern mussten! Aus einer VLOOKUP-Formel wie dieser haben wir diese verrückte Sache gemacht. Scoop alle Zeichen von VLOOKUP verwechseln sich mit Ausnahme von =, Strg + C, um es in die Zwischenablage zu setzen, = IF (ISNA (, drücken Sie die End-Taste, um zum Ende zu gelangen, wenn es # N / A! Ist, dann wir sag "Nicht gefunden", Komma, sonst machen wir den VLOOKUP! Richtig, ich füge ihn dort ein und schaue, wie lange diese Formel ist, Strg + Eingabetaste, füge a) genau dort hinzu, Strg + Eingabetaste, okay, sieh mal, das ist süß. Okay, aber das Problem ist, während es das Problem der # N / A löst! Jede einzelne Formel führt den VOOKUP zweimal durch. Sie möchte nicht, dass wir sagen: „Hey, ist das ein Fehler? Oh, nein, das ist es nicht Ein Fehler. Machen wir es noch einmal! “Die Ausführung all dieser VLOOKUPs dauert also doppelt so lange. In Excel 2010Sie geben uns die fantastische Formel von IFERROR!
Jetzt weiß ich, dass das so klingt, wie wir es vorher hatten, ISERROR oder ISERR, aber das ist IFERROR. Und wie es funktioniert, nehmen Sie eine Formel, die einen Fehler zurückgeben könnte, und dann sagen Sie = IFERROR, es gibt die Formel, Komma, was zu tun ist, wenn es ein Fehler ist, also "Nicht gefunden". Okay, das Schöne daran ist, nehmen wir an, Sie hatten tausend VLOOKUPs zu erledigen, und 980 davon funktionieren. Beim 980 wird nur der VLOOKUP ausgeführt, es ist kein Fehler, es wird die Antwort zurückgegeben, es wird nie der zweite VLOOKUP ausgeführt, das ist das Schöne an IFERROR. Der IFERROR von Excel wurde in Excel 2010 eingeführt, aber das einzig wirklich dumme Problem hierbei ist natürlich, dass die Tabelle selbst einen Fehler zurückgibt. Richtig, jemand hatte 0 Menge, Umsatz / Menge, und so bekommen wir eine # DIV / 0! Fehler dort, und dieser Fehler wird auch von IFERROR als Fehler erkannt. In Ordung,und es wird so aussehen, als ob das nicht gefunden wird, er wird gefunden, es ist nur so, dass derjenige, der diesen Tisch gebaut hat, keine gute Arbeit geleistet hat, diesen Tisch zu bauen.
Also gut, Wahl Nr. 1, Excel 2013 oder neuer, wechseln Sie zu der Funktion, die sie 2013 hinzugefügt haben und die nicht nach allen Fehlern sucht, sondern nur nach Nr. N / A! Strg + Eingabetaste, und jetzt erhalten wir das Not Found for ExcelIsFun, geben aber das # DIV / 0 zurück! Error. Wirklich, was wir tun sollten, ist hier in dieser Formel, wir sollten diese Formel behandeln, um diese Division durch Null überhaupt zu verhindern. Also = IFERROR, das funktioniert für alle möglichen Dinge. Drücken Sie die End-Taste, um zum Ende zu gelangen, Komma, und was tun? Ich würde hier immer eine Null als Durchschnittspreis setzen.
Ich hatte einmal einen Manager, Susanna (?), "Das ist mathematisch nicht korrekt, Sie müssen" nm "eingeben, um nicht aussagekräftig zu sein." Genau so ist es verrückt, wie lange mein Manager mich mit ihrer verrückten Bitte verrückt gemacht hat. Nicht gefunden “wird vom IFERROR gefunden, und das„ nicht aussagekräftige “ist tatsächlich das Ergebnis des VLOOKUP. Okay, also ein paar verschiedene Wege, wahrscheinlich ist es hier am sichersten, IFNA zu verwenden, vorausgesetzt, Sie haben Excel 2013 und jeder, mit dem Sie Ihre Arbeitsmappe teilen, hat Excel 2013. Dieses Buch und eine ganze Reihe anderer sind in diesem Buch, tropft mit würzigen Tipps, 200 Seiten, leicht zu lesen, voller Farbe genial, geniales Buch. Probieren Sie es aus und klicken Sie auf das "I" in der oberen rechten Ecke.Sie können das Buch kaufen.
Okay, Episodenrückblick: Früher verwendeten wir ein langes Format = IF (ISNA (die Formel 0, ansonsten die Formel, die Formel wurde zweimal berechnet, was für VLOOKUPs schrecklich ist. Ab Excel 2010 = IFERROR) Geben Sie die Formel nur einmal ein, Komma, was zu tun ist, wenn es sich um einen Fehler handelt. IFERROR behandelt jedoch # DIV / 0! -Fehler genauso wie # N / A! -Fehler. Wenn Sie also Excel 2013 starten, funktioniert die IFNA-Funktion genau wie IFERROR. Es werden jedoch nur die # N / A! -Fehler erkannt.
Dieser Tipp übrigens, vorgeschlagen von den Lesern Justin Fishman, Stephen Gilmer und Excel von Joe. Vielen Dank, dass Sie dies vorgeschlagen haben, und vielen Dank, dass Sie vorbeischauen. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2042.xlsm
