Stellen Sie sich vor, Sie arbeiten an einer wichtigen Excel-Tabelle und werden ständig von der Fehlermeldung “#NV” unterbrochen. Diese Meldung signalisiert, dass eine Formel etwas nicht finden konnte, was sie gesucht hat. Für unerfahrene Nutzer kann dies verwirrend sein und den Eindruck erwecken, dass etwas mit Ihrer Tabelle nicht stimmt. Hier kommt die leistungsstarke IFNA-Funktion ins Spiel, die speziell dafür entwickelt wurde, diesen Fehlertyp abzufangen und durch eine benutzerfreundlichere Nachricht oder einen leeren Wert zu ersetzen. Mit IFNA verwandeln Sie kryptische Fehlermeldungen in klare Informationen und verbessern die Lesbarkeit und Professionalität Ihrer Arbeitsblätter erheblich.
Die IFNA-Funktion in Excel verstehen
Die Excel Ifna-Funktion ist eine spezialisierte Funktion zur Fehlerbehandlung, die ausschließlich den Fehlerwert #NV (Nicht Verfügbar) abfängt. Wenn eine Formel zu #NV ausgewertet wird, fängt IFNA diesen Fehler ab und ersetzt ihn durch einen von Ihnen angegebenen benutzerdefinierten Wert. Andernfalls gibt die Funktion das normale Ergebnis der Formel zurück. Dies macht sie zu einem unverzichtbaren Werkzeug, insbesondere wenn Sie mit Suchfunktionen arbeiten, die häufig #NV-Fehler verursachen können, wenn der gesuchte Wert nicht gefunden wird.
Was ist die IFNA-Funktion?
Im Kern ist die IFNA-Funktion eine elegante Lösung, um #NV-Fehler zu “verschönern”. Während andere Fehlerbehandlungsfunktionen alle Arten von Fehlern abfangen, konzentriert sich IFNA ausschließlich auf den #NV-Fehler. Dies ist besonders vorteilhaft, da der #NV-Fehler oft bedeutet, dass ein Wert einfach nicht vorhanden ist, und nicht, dass die Formel an sich fehlerhaft ist. Durch das Abfangen dieses spezifischen Fehlers können Sie Ihren Nutzern präzise Rückmeldungen geben, ohne andere potenzielle Probleme in Ihren Formeln zu überdecken.
Syntax der IFNA-Funktion
Die Syntax der IFNA-Funktion ist denkbar einfach und besteht aus nur zwei Argumenten:
IFNA(Wert, Wert_wenn_NV)
- Wert (erforderlich): Dies ist die Formel, der Wert oder der Verweis, der auf einen #NV-Fehler überprüft werden soll. In der Regel ist dies die Formel, die den #NV-Fehler verursachen könnte, wie zum Beispiel SVERWEIS, VERGLEICH oder INDEX.
- Wert_wenn_NV (erforderlich): Dies ist der Wert, der zurückgegeben werden soll, wenn ein #NV-Fehler erkannt wird. Hier können Sie eine benutzerdefinierte Textnachricht, eine Zahl oder einen leeren Text (“”) angeben, um eine leere Zelle zurückzugeben.
Wichtige Hinweise zur Verwendung
Die IFNA-Funktion ist ein leistungsstarkes Werkzeug, das jedoch einige Besonderheiten aufweist, die Sie beachten sollten. Der wichtigste Punkt ist, dass sie ausschließlich #NV-Fehler behandelt. Das bedeutet, dass sie andere Fehler wie #NAME?, #WERT! oder #BEZUG! nicht unterdrückt. Dies kann ein Vorteil sein, da es Ihnen ermöglicht, echte Formelfehler zu erkennen, während Sie gleichzeitig die erwarteten “#NV”-Meldungen elegant handhaben.
Wenn das Argument Wert eine Matrixformel ist, gibt IFNA ein Array von Ergebnissen zurück, eines pro Zelle, was besonders nützlich in modernen Excel-Versionen mit dynamischen Arrays ist. Die IFNA-Funktion wurde erstmals in Excel 2013 eingeführt und ist in allen nachfolgenden Versionen, einschließlich Excel 2016, Excel 2019, Excel 2021 und Microsoft 365, verfügbar. In älteren Versionen können Sie #NV-Fehler mit einer Kombination aus den Funktionen WENN und ISTNV abfangen.
Praktische Anwendung der IFNA-Funktion in Excel
Um IFNA in Excel effektiv einzusetzen, können Sie einem einfachen generischen Ansatz folgen. Zuerst platzieren Sie in dem ersten Argument (Wert) die Formel, die potenziell einen #NV-Fehler erzeugen könnte. Im zweiten Argument (Wert_wenn_NV) geben Sie dann den Text ein, den Sie anstelle der Standardfehlernotation zurückgeben möchten. Um eine leere Zelle zurückzugeben, wenn nichts gefunden wird, verwenden Sie einen leeren String (“”).
Allgemeine Vorgehensweise
Um einen benutzerdefinierten Text zurückzugeben, lautet die generische Formel:
IFNA(Formel(), "Benutzerdefinierter Text")
Um eine leere Zelle zurückzugeben, lautet die generische Formel:
IFNA(Formel(), "")
Betrachten wir ein einfaches Beispiel: In der untenstehenden Tabelle möchten Sie den Rang eines bestimmten Studenten ermitteln. Da die Daten nach der Spalte “Score” von hoch nach niedrig sortiert sind, entspricht der Rang der relativen Position des Studenten in der Tabelle. Um die Position zu erhalten, können Sie die VERGLEICH-Funktion in ihrer einfachsten Form verwenden:
=VERGLEICH(E1; A2:A10; 0)
Da der Suchwert (z.B. “Neal”) im Sucharray (A2:A10) nicht verfügbar ist, tritt ein #NV-Fehler auf.
Ein #NV-Fehler in Excel, da der Suchwert nicht gefunden wurde.
Wenn Sie auf diesen Fehler stoßen, könnten unerfahrene Benutzer denken, dass etwas mit der Formel nicht stimmt, und Sie als Ersteller der Arbeitsmappe würden viele Fragen erhalten. Um dies zu vermeiden, können Sie explizit angeben, dass die Formel korrekt ist, sie kann lediglich den gesuchten Wert nicht finden. Daher verschachteln Sie die VERGLEICH-Formel im ersten Argument von IFNA und geben im zweiten Argument Ihren benutzerdefinierten Text, in diesem Fall “Nicht gefunden”, ein:
=IFNA(VERGLEICH(E1; A2:A10; 0); "Nicht gefunden")
Jetzt wird anstelle der Standardfehlernotation Ihr eigener Text in einer Zelle angezeigt, der die Benutzer darüber informiert, dass der Suchwert im Datensatz nicht vorhanden ist. Dies sorgt für eine wesentlich klarere Kommunikation und ein professionelleres Erscheinungsbild Ihrer Tabellen, da unnötige Fragen und Verwirrung vermieden werden.
Verwendung der IFNA-Funktion in Excel zur Anzeige einer benutzerdefinierten Meldung anstelle von #NV.
IFNA in Kombination mit SVERWEIS
Am häufigsten tritt der #NV-Fehler bei Funktionen auf, die etwas suchen, wie SVERWEIS, HVERWEIS, VERWEIS und VERGLEICH. Die folgenden Beispiele decken einige typische Anwendungsfälle ab, um die Vielseitigkeit von IFNA in Verbindung mit diesen Suchfunktionen zu demonstrieren und Ihre Arbeitsblätter robuster zu gestalten.
Beispiel 1: Grundlegende IFNA-SVERWEIS-Formel
Um #NV-Fehler abzufangen, die auftreten, wenn SVERWEIS keine Übereinstimmung finden kann, überprüfen Sie dessen Ergebnis mit IFNA und geben Sie den Wert an, der stattdessen angezeigt werden soll. Die gängige Praxis besteht darin, die IFNA-Funktion um Ihre bestehende SVERWEIS-Formel herum zu legen, wobei diese Syntax verwendet wird:
IFNA(SVERWEIS(); "Ihr Text")
Angenommen, Sie möchten in unserer Beispieltabelle die Punktzahl eines bestimmten Studenten (E1) abrufen. Dafür verwenden Sie diese klassische SVERWEIS-Formel:
=SVERWEIS(E1; A2:B10; 2; FALSCH)
Das Problem ist, dass Neal die Prüfung nicht abgelegt hat, daher ist sein Name nicht in der Liste, und SVERWEIS findet offensichtlich keine Übereinstimmung.
SVERWEIS findet keine Übereinstimmung und gibt den #NV-Fehler zurück.
Um den Fehler auszublenden, verpacken wir SVERWEIS in IFNA wie folgt:
=IFNA(SVERWEIS(E1; A2:B10; 2; FALSCH); "Nicht an der Prüfung teilgenommen")
Jetzt sieht das Ergebnis für den Benutzer nicht mehr so einschüchternd aus und ist viel informativer. Diese Anpassung macht Ihre Excel-Tabellen nicht nur benutzerfreundlicher, sondern auch professioneller, da sie klare und verständliche Rückmeldungen anstelle von Fehlermeldungen liefern.
IFNA-SVERWEIS-Formel in Excel, die eine informative Meldung anzeigt.
Beispiel 2: SVERWEIS über mehrere Blätter hinweg mit IFNA
Die IFNA-Funktion erweist sich auch als nützlich, um sogenannte sequentielle oder verkettete Suchvorgänge über mehrere Blätter oder verschiedene Arbeitsmappen hinweg durchzuführen. Die Idee ist, dass Sie einige verschiedene IFNA(SVERWEIS(…)) Formeln auf diese Weise ineinander verschachteln:
IFNA(SVERWEIS(…); IFNA(SVERWEIS(…); IFNA(SVERWEIS(…); "Nicht gefunden")))
Wenn ein primäres SVERWEIS nichts findet, führt dessen IFNA-Funktion das nächste SVERWEIS aus, bis der gewünschte Wert gefunden wird. Schlagen alle Suchvorgänge fehl, gibt die Formel den angegebenen Text zurück.
Angenommen, Sie haben die Punktzahlen verschiedener Klassen in verschiedenen Blättern (benannt Klasse A, Klasse B und Klasse C) aufgeführt. Ihr Ziel ist es, die Punktzahl eines bestimmten Schülers zu erhalten, dessen Name in Zelle B1 in Ihrem aktuellen Arbeitsblatt eingegeben wird. Um die Aufgabe zu erfüllen, verwenden Sie diese Formel:
=IFNA(SVERWEIS(B1; 'Klasse A'!A2:B5; 2; FALSCH); IFNA(SVERWEIS(B1; 'Klasse B'!A2:B5; 2; FALSCH); IFNA(SVERWEIS(B1; 'Klasse C'!A2:B5; 2; FALSCH); "Nicht gefunden")))
Die Formel sucht den angegebenen Namen sequenziell in drei verschiedenen Blättern in der Reihenfolge, in der die SVERWEIS-Funktionen verschachtelt sind, und liefert die erste gefundene Übereinstimmung. Dies ermöglicht eine robuste und flexible Datensuche über komplexe Datenstrukturen hinweg.
IFNA-SVERWEIS zur Suche über mehrere Blätter hinweg, um den ersten gefundenen Wert zurückzugeben.
IFNA mit INDEX und VERGLEICH verwenden
Auf ähnliche Weise kann IFNA #NV-Fehler abfangen, die von anderen Suchfunktionen generiert werden. Als Beispiel verwenden wir es zusammen mit der INDEX MATCH-Formel, die oft als flexiblere Alternative zu SVERWEIS angesehen wird:
=IFNA(INDEX(B2:B10; VERGLEICH(E1; A2:A10; 0)); "Nicht gefunden")
Der Kern der Formel ist derselbe wie in allen vorherigen Beispielen: INDEX MATCH führt eine Suche durch, und IFNA wertet das Ergebnis aus und fängt einen #NV-Fehler ab, falls der referenzierte Wert nicht gefunden wird. Dies gewährleistet, dass Ihre Tabellen auch bei komplexen Suchvorgängen eine saubere und verständliche Ausgabe liefern, anstatt störender Fehlermeldungen. Die Kombination aus INDEX MATCH und IFNA ist eine sehr leistungsstarke Methode, um Daten effizient und fehlerfrei zu verwalten.
Verwendung von IFNA mit INDEX und VERGLEICH, um den #NV-Fehler abzufangen.
IFNA für die Rückgabe mehrerer Ergebnisse (Dynamische Arrays)
Falls die innere Funktion (d.h. die Formel, die im Wert-Argument platziert ist) mehrere Werte zurückgibt, testet IFNA jeden zurückgegebenen Wert einzeln und gibt ein Array von Ergebnissen aus. Dies ist besonders nützlich in modernen Excel-Versionen mit dynamischen Arrays, die die automatische Ausbreitung von Ergebnissen ermöglichen. Zum Beispiel:
=IFNA(SVERWEIS(D2:D4; A2:B10; 2; FALSCH); "Nicht gefunden")
In Dynamic Array Excel (Microsoft 365 und Excel 2021) breitet eine reguläre Formel in der obersten Zelle (E2) alle Ergebnisse automatisch in den benachbarten Zellen aus (im Excel-Jargon wird dies als Spill-Bereich bezeichnet). Dies vereinfacht die Arbeit mit mehreren Suchwerten erheblich, da Sie nicht mehr für jede Zelle eine separate Formel eingeben müssen. Das Ergebnis ist eine saubere und effiziente Lösung für die Verarbeitung von Listen von Suchwerten.
Die Excel IFNA-Funktion kann mehrere Ergebnisse zurückgeben, wenn sie mit dynamischen Arrays verwendet wird.
In älteren Versionen (Excel 2019 und niedriger) kann ein ähnlicher Effekt durch die Verwendung einer mehrzelligen Matrixformel erzielt werden, die mit der Tastenkombination Strg + Umschalt + Eingabe abgeschlossen wird. Obwohl dies etwas mehr Aufwand erfordert als dynamische Arrays, bietet es dennoch eine Möglichkeit, mehrere Ergebnisse mit IFNA zu verarbeiten und die Lesbarkeit der Tabelle zu verbessern.
Verwendung der IFNA-Funktion in einer Matrixformel in älteren Excel-Versionen.
IFNA vs. IFERROR: Wann welche Funktion nutzen?
Je nach Ursache des Problems kann eine Excel-Formel verschiedene Fehler auslösen, wie #NV, #NAME?, #WERT!, #BEZUG!, #DIV/0!, #ZAHL! und andere. Die IFERROR-Funktion fängt all diese Fehler ab, während IFNA auf #NV beschränkt ist. Welche ist die bessere Wahl? Das hängt von der Situation ab.
Wenn Sie jede Art von Fehler unterdrücken möchten, verwenden Sie die IFERROR-Funktion. Sie ist besonders nützlich bei komplexen Berechnungen, wenn eine Formel mehrere Funktionen enthält, die unterschiedliche Fehler erzeugen können.
Bei Suchfunktionen sollten Sie besser IFNA verwenden, da es ein benutzerdefiniertes Ergebnis nur dann anzeigt, wenn ein Suchwert nicht gefunden wird, und keine zugrunde liegenden Probleme mit der Formel selbst verbirgt.
Um den Unterschied zu verdeutlichen, kehren wir zu unserer grundlegenden IFNA-SVERWEIS-Formel zurück und buchstabieren den Funktionsnamen “versehentlich” falsch (z.B. VLOKUP anstelle von SVERWEIS).
=IFNA(VLOKUP(E1; A2:B10; 2; FALSCH); "Nicht an der Prüfung teilgenommen")
IFNA unterdrückt diesen Fehler nicht, sodass Sie deutlich sehen können, dass etwas mit einem der Funktionsnamen nicht stimmt. Dies ist ein entscheidender Vorteil, da es Ihnen ermöglicht, echte Tippfehler oder Syntaxfehler in Ihren Formeln zu erkennen und zu korrigieren, anstatt diese zu maskieren.
Der #NAME?-Fehler wird von IFNA nicht behandelt, was auf ein Problem mit der Formel hinweist.
Sehen wir uns nun an, was passiert, wenn Sie IFERROR verwenden:
=IFERROR(VLOKUP(E1; A2:B10; 2; FALSCH); "Nicht an der Prüfung teilgenommen")
Hmm… es heißt, dass Olivia nicht an der Prüfung teilgenommen hat, was nicht stimmt! Dies liegt daran, dass die IFERROR-Funktion den #NAME?-Fehler abfängt und stattdessen den benutzerdefinierten Text zurückgibt. In dieser Situation liefert sie nicht nur falsche Informationen, sondern verschleiert auch das Problem mit der Formel. Daher ist IFNA bei Suchfunktionen oft die sicherere und informativere Wahl.
Die IFERROR-Funktion fängt alle Fehler ab, was in bestimmten Fällen irreführend sein kann.
Zusammenfassend lässt sich sagen, dass IFNA ein präzises Werkzeug für spezifische Fehler (insbesondere in Suchfunktionen) ist, während IFERROR ein allgemeineres Werkzeug zur Unterdrückung aller Fehlertypen ist. Die Wahl der richtigen Funktion hängt von der Art des Fehlers ab, den Sie behandeln möchten, und davon, wie detailliert Ihre Fehlerbehandlung sein soll.
Mit der IFNA-Funktion können Sie Ihre Excel-Tabellen professioneller und benutzerfreundlicher gestalten, indem Sie #NV-Fehler in klare und informative Meldungen umwandeln. Nutzen Sie IFNA, um die Übersichtlichkeit Ihrer Daten zu verbessern und Missverständnisse bei den Nutzern Ihrer Arbeitsblätter zu vermeiden. Beginnen Sie noch heute damit, Ihre Tabellen von störenden #NV-Fehlern zu befreien!