So korrigieren Sie einen #NV-Fehler in der SVERWEIS-Funktion
In diesem Thema werden die häufigsten VLOOKUP- Gründe für ein fehlerhaftes Ergebnis der Funktion beschrieben und Vorschläge für die Verwendung von INDEX und MATCH stattdessen bereitgestellt.
Tipp: Lesen Sie auch die Quick Reference Card: SVERWEIS-Tipps zur Fehlerbehebung, die die häufigsten Gründe für #NA-Probleme in einer praktischen PDF-Datei darstellt. Sie können das PDF mit anderen teilen oder für Ihre eigene Referenz ausdrucken.
Problem: Der Suchwert befindet sich nicht in der ersten Spalte des Arguments table_array
Eine Einschränkung von SVERWEIS besteht darin, dass es nur nach Werten in der Spalte ganz links im Tabellenarray suchen kann. Wenn sich Ihr Suchwert nicht in der ersten Spalte des Arrays befindet, wird der Fehler #NV angezeigt.
In der folgenden Tabelle möchten wir die Anzahl der verkauften Einheiten für Grünkohl abrufen.
Der #NV-Fehler tritt auf, weil der Suchwert „Kale" in der zweiten Spalte (Produce) des table_array- Arguments A2:C10 erscheint. In diesem Fall sucht Excel in Spalte A und nicht in Spalte B.
Lösung : Sie können versuchen, dies zu beheben, indem Sie Ihr SVERWEIS so anpassen, dass es auf die richtige Spalte verweist. Wenn das nicht möglich ist, versuchen Sie, Ihre Spalten zu verschieben. Das kann auch höchst unpraktisch sein, wenn Sie große oder komplexe Tabellenkalkulationen haben, in denen Zellenwerte Ergebnisse anderer Berechnungen sind – oder vielleicht gibt es andere logische Gründe, warum Sie die Spalten einfach nicht verschieben können. Die Lösung besteht darin, eine Kombination aus INDEX- und MATCH-Funktionen zu verwenden, die einen Wert in einer Spalte unabhängig von seiner Position in der Nachschlagetabelle nachschlagen können. Siehe nächster Abschnitt.
Erwägen Sie stattdessen die Verwendung von INDEX/MATCH
INDEX und MATCH sind gute Optionen für viele Fälle, in denen SVERWEIS nicht Ihren Anforderungen entspricht. Der Hauptvorteil von INDEX/MATCH besteht darin, dass Sie einen Wert in einer Spalte an jeder Stelle in der Nachschlagetabelle nachschlagen können. INDEX gibt einen Wert aus einer angegebenen Tabelle/einem angegebenen Bereich zurück – entsprechend seiner Position. MATCH gibt die relative Position eines Werts in einer Tabelle/einem Bereich zurück. Verwenden Sie INDEX und MATCH zusammen in einer Formel, um einen Wert in einer Tabelle/einem Array nachzuschlagen, indem Sie die relative Position des Werts in der Tabelle/dem Array angeben.
Die Verwendung von INDEX/MATCH anstelle von SVERWEIS bietet mehrere Vorteile:
Bei INDEX und MATCH muss sich der Rückgabewert nicht in derselben Spalte wie die Nachschlagespalte befinden. Dies unterscheidet sich von SVERWEIS, bei dem der Rückgabewert im angegebenen Bereich liegen muss. Wie wichtig ist das? Bei SVERWEIS muss die Spaltennummer bekannt sein, die den Rückgabewert enthält. Dies scheint zwar keine Herausforderung zu sein, kann jedoch umständlich sein, wenn Sie eine große Tabelle haben und die Anzahl der Spalten zählen müssen. Auch wenn Sie eine Spalte in Ihrer Tabelle hinzufügen/entfernen, müssen Sie das Argument col_index_num neu zählen und aktualisieren. Bei INDEX und MATCH ist keine Zählung erforderlich, da sich die Lookup-Spalte von der Spalte mit dem Rückgabewert unterscheidet.
Mit INDEX und MATCH können Sie entweder eine Zeile oder eine Spalte in einem Array angeben – oder beides. Das bedeutet, dass Sie Werte sowohl vertikal als auch horizontal nachschlagen können.
INDEX und MATCH können verwendet werden, um Werte in jeder Spalte nachzuschlagen. Im Gegensatz zu SVERWEIS – bei dem Sie nur zu einem Wert in der ersten Spalte einer Tabelle nachschlagen können – funktionieren INDEX und VERGLEICH, wenn sich Ihr Nachschlagewert in der ersten Spalte, der letzten oder irgendwo dazwischen befindet.
INDEX und MATCH bieten die Flexibilität, dynamisch auf die Spalte zu verweisen, die den Rückgabewert enthält. Das bedeutet, dass Sie Ihrer Tabelle Spalten hinzufügen können, ohne INDEX und MATCH zu beschädigen. Andererseits wird SVERWEIS unterbrochen, wenn Sie der Tabelle eine Spalte hinzufügen müssen, da es einen statischen Verweis auf die Tabelle erstellt.
INDEX und MATCH bieten mehr Flexibilität bei Übereinstimmungen. INDEX und MATCH können eine genaue Übereinstimmung oder einen Wert finden, der größer oder kleiner als der Suchwert ist. SVERWEIS sucht nur nach einer engsten Übereinstimmung mit einem Wert (standardmäßig) oder einem exakten Wert. SVERWEIS geht außerdem standardmäßig davon aus, dass die erste Spalte im Tabellenarray alphabetisch sortiert ist, und angenommen, Ihre Tabelle ist nicht so eingerichtet, gibt SVERWEIS die erste engste Übereinstimmung in der Tabelle zurück, die möglicherweise nicht die Daten sind, nach denen Sie suchen.
Syntax
Um eine Syntax für INDEX/MATCH zu erstellen, müssen Sie das Array/Referenz-Argument aus der INDEX-Funktion verwenden und die MATCH-Syntax darin verschachteln. Diese nehmen die Form an:
=INDEX(Array oder Referenz, MATCH(lookup_value,lookup_array,[match_type])
Lassen Sie uns INDEX/MATCH verwenden, um VLOOKUP aus dem obigen Beispiel zu ersetzen. Die Syntax sieht folgendermaßen aus:
=INDEX(C2:C10,VERGLEICH(B13,B2:B10,0))
Im einfachen Englisch bedeutet es:
=INDEX(gibt einen Wert von C2:C10 zurück, der MATCH(Kale, der sich irgendwo im B2:B10-Array befindet, in dem der Rückgabewert der erste Wert ist, der Grünkohl entspricht))
Die Formel sucht nach dem ersten Wert in C2:C10, der Grünkohl (in B7) entspricht, und gibt den Wert in C7 ( 100 ) zurück, der der erste Wert ist, der Grünkohl entspricht.
Problem: Die genaue Übereinstimmung wird nicht gefunden
Wenn das Argument range_lookup FALSE ist und SVERWEIS keine exakte Übereinstimmung in Ihren Daten finden kann, wird der Fehler #NV zurückgegeben.
Lösung : Wenn Sie sicher sind, dass die relevanten Daten in Ihrer Tabelle vorhanden sind und SVERWEIS sie nicht erfasst, nehmen Sie sich Zeit, um zu überprüfen, ob die referenzierten Zellen keine versteckten Leerzeichen oder nicht druckbaren Zeichen enthalten. Stellen Sie außerdem sicher, dass die Zellen dem richtigen Datentyp folgen. Beispielsweise sollten Zellen mit Zahlen als Zahl und nicht als Text formatiert werden.
Ziehen Sie auch in Betracht, entweder die Funktion CLEAN oder TRIM zu verwenden, um Daten in Zellen zu bereinigen.
Problem: Der Suchwert ist kleiner als der kleinste Wert im Array
Wenn das Argument range_lookup auf TRUE gesetzt ist und der Suchwert kleiner als der kleinste Wert im Array ist, wird der Fehler #NV angezeigt. TRUE sucht nach einer ungefähren Übereinstimmung im Array und gibt den nächsten Wert zurück, der kleiner als der Suchwert ist.
Im folgenden Beispiel ist der Suchwert 100 , aber es gibt keine Werte im Bereich B2:C10, die kleiner als 100 sind; daher der fehler.
Lösung :
Korrigieren Sie den Suchwert nach Bedarf.
Wenn Sie den Nachschlagewert nicht ändern können und mehr Flexibilität beim Abgleichen von Werten benötigen, ziehen Sie die Verwendung von INDEX/MATCH anstelle von SVERWEIS in Betracht – siehe Abschnitt oben in diesem Artikel. Mit INDEX/MATCH können Sie Werte suchen, die größer, kleiner oder gleich dem Suchwert sind. Weitere Informationen zur Verwendung von INDEX/MATCH anstelle von SVERWEIS finden Sie im vorherigen Abschnitt dieses Themas.
Problem: Die Nachschlagespalte ist nicht in aufsteigender Reihenfolge sortiert
Wenn das Argument range_lookup auf TRUE gesetzt ist und eine Ihrer Nachschlagespalten nicht in aufsteigender Reihenfolge (AZ) sortiert ist, wird der Fehler #NV angezeigt.
Lösung :
Ändern Sie die SVERWEIS-Funktion, um nach einer genauen Übereinstimmung zu suchen. Setzen Sie dazu das Argument range_lookup auf FALSE . Bei FALSE ist keine Sortierung notwendig.
Verwenden Sie die INDEX/MATCH-Funktion, um einen Wert in einer unsortierten Tabelle nachzuschlagen.
Problem: Der Wert ist eine große Fließkommazahl
Wenn Sie Zeitwerte oder große Dezimalzahlen in Zellen haben, gibt Excel aufgrund der Gleitkommagenauigkeit den Fehler #NV zurück. Fließkommazahlen sind Zahlen, die nach einem Dezimalpunkt folgen. (Excel speichert Zeitwerte als Gleitkommazahlen.) Excel kann keine Zahlen mit sehr großen Gleitkommazahlen speichern, damit die Funktion korrekt funktioniert, müssen die Gleitkommazahlen auf 5 Dezimalstellen gerundet werden.
Lösung : Verkürzen Sie die Zahlen, indem Sie sie mit der RUNDEN- Funktion auf fünf Dezimalstellen runden.
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in der Answers-Community erhalten.
No comments:
Post a Comment