Inhalte
- #N/A-Fehler in der VLOOKUP-Funktion in Excel behoben
- 1. Der gewünschte Wert ist falsch geschrieben
- 2. #NV-Fehler bei der Suche nach einer ungefähren Übereinstimmung mit SVERWEIS
- 3. Fehler #NV bei der Suche nach einer exakten Übereinstimmung mit SVERWEIS
- 4. Die Suchspalte befindet sich nicht ganz links
- 5. Zahlen werden als Text formatiert
- 6. Am Anfang oder am Ende steht ein Leerzeichen
- Fehler #WERT! in Formeln mit SVERWEIS
- Fehler #NAME? im SVERWEIS
- SVERWEIS funktioniert nicht (Einschränkungen, Vorbehalte und Entscheidungen)
- SVERWEIS – Arbeiten mit den Funktionen IFERROR und ISERROR
Diese Lektion erklärt, wie man schnell mit einer Situation umgeht, in der eine Funktion VPR (VLOOKUP) will nicht in Excel 2013, 2010, 2007 und 2003 funktionieren und wie man häufige Fehler identifiziert und behebt und Einschränkungen überwindet VPR.
In mehreren früheren Artikeln haben wir die verschiedenen Facetten der Funktion untersucht VPR im Excel-Format. Wenn Sie sie sorgfältig gelesen haben, sollten Sie jetzt ein Experte auf diesem Gebiet sein. Das glauben jedoch nicht ohne Grund viele Excel-Experten VPR eine der komplexeren Funktionen. Es hat eine Reihe von Einschränkungen und Funktionen, die zur Quelle vieler Probleme und Fehler werden.
In diesem Artikel finden Sie einfache Erklärungen zu Fehlern #at (#N / A), # NAME? (#Name und #WERT! (#WERT!), die beim Arbeiten mit der Funktion erscheinen VPR, sowie Techniken und Methoden, mit ihnen umzugehen. Wir beginnen mit den häufigsten Fällen und den offensichtlichsten Gründen dafür. VPR funktioniert nicht, daher ist es besser, die Beispiele in der Reihenfolge zu studieren, in der sie im Artikel angegeben sind.
#N/A-Fehler in der VLOOKUP-Funktion in Excel behoben
In Formeln mit VPR Fehlermeldung #at (#N/A) bedeutet nicht vorhanden (keine Daten) – erscheint, wenn Excel den gesuchten Wert nicht finden kann. Dies kann mehrere Gründe haben.
1. Der gewünschte Wert ist falsch geschrieben
Gute Idee, diesen Artikel zuerst zu überprüfen! Tippfehler treten häufig auf, wenn Sie mit sehr großen Datenmengen arbeiten, die aus Tausenden von Zeilen bestehen, oder wenn der gesuchte Wert in eine Formel geschrieben wird.
2. #NV-Fehler bei der Suche nach einer ungefähren Übereinstimmung mit SVERWEIS
Wenn Sie eine Formel mit einer ungefähren Suchbedingung verwenden, dh argument range_lookup (range_lookup) TRUE oder nicht angegeben ist, meldet Ihre Formel möglicherweise einen Fehler #N / A in zwei Fällen:
- Der nachzuschlagende Wert ist kleiner als der kleinste Wert in dem nachzuschlagenden Array.
- Die Suchspalte ist nicht aufsteigend sortiert.
3. Fehler #NV bei der Suche nach einer exakten Übereinstimmung mit SVERWEIS
Wenn Sie nach einer genauen Übereinstimmung suchen, dh nach einem Argument range_lookup (range_lookup) FALSE ist und der genaue Wert nicht gefunden wurde, meldet die Formel ebenfalls einen Fehler #N / A. Erfahren Sie mehr darüber, wie Sie mit einer Funktion nach genauen und ungefähren Übereinstimmungen suchen VPR.
4. Die Suchspalte befindet sich nicht ganz links
Wie Sie wahrscheinlich wissen, eine der wichtigsten Einschränkungen VPR Es ist so, dass es nicht nach links zeigen kann, daher muss die Nachschlagespalte in Ihrer Tabelle ganz links sein. In der Praxis vergessen wir das oft, was zu einer nicht funktionierenden Formel und einem Fehler führt. #N / A.
Entscheidung: Wenn es nicht möglich ist, die Datenstruktur so zu ändern, dass die Suchspalte ganz links steht, können Sie eine Kombination von Funktionen verwenden INDEX (INDEX) und AUSGESETZTER (MATCH) als flexiblere Alternative für VPR.
5. Zahlen werden als Text formatiert
Eine weitere Fehlerquelle #N / A in Formeln mit VPR sind Zahlen im Textformat in der Haupttabelle oder Nachschlagetabelle.
Dies geschieht normalerweise, wenn Sie Informationen aus externen Datenbanken importieren oder wenn Sie vor einer Zahl ein Apostroph eingeben, um die führende Null beizubehalten.
Die offensichtlichsten Zeichen einer Zahl im Textformat sind in der folgenden Abbildung dargestellt:
Außerdem können Zahlen im Format gespeichert werden Allgemeines (Allgemein). In diesem Fall gibt es nur ein auffälliges Merkmal – die Zahlen werden am linken Rand der Zelle ausgerichtet, während sie standardmäßig am rechten Rand ausgerichtet sind.
Entscheidung: Wenn es sich um einen einzelnen Wert handelt, klicken Sie einfach auf das Fehlersymbol und wählen Sie es aus In Nummer konvertieren (In Zahl umwandeln) aus dem Kontextmenü.
Wenn dies bei vielen Nummern der Fall ist, wählen Sie sie aus und klicken Sie mit der rechten Maustaste auf den ausgewählten Bereich. Wählen Sie im erscheinenden Kontextmenü aus Format Cells (Zellen formatieren) > Registerkarte Nummer (Zahl) > Format Nummer (Numerisch) und drücken Sie OK.
6. Am Anfang oder am Ende steht ein Leerzeichen
Dies ist der am wenigsten offensichtliche Grund für den Fehler. #N / A in Funktion VPR, da diese zusätzlichen Leerzeichen visuell schwer zu erkennen sind, insbesondere bei der Arbeit mit großen Tabellen, wenn die meisten Daten außerhalb des Bildschirms angezeigt werden.
Lösung 1: Zusätzliche Leerzeichen in der Haupttabelle (wo sich die SVERWEIS-Funktion befindet)
Wenn in der Haupttabelle zusätzliche Leerzeichen erscheinen, können Sie sicherstellen, dass die Formeln korrekt funktionieren, indem Sie das Argument einschließen Lookup-Wert (lookup_value) in eine Funktion TRIMMEN (TRIMMEN):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Lösung 2: Zusätzliche Leerzeichen in der Nachschlagetabelle (in der Nachschlagespalte)
Wenn zusätzliche Leerzeichen in der Suchspalte sind – einfache Möglichkeiten #N / A in der Formel mit VPR lässt sich nicht vermeiden. Anstatt von VPR Sie können eine Matrixformel mit einer Kombination von Funktionen verwenden INDEX (INDEX), AUSGESETZTER (MATCH) und TRIMMEN (TRIMMEN):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Da dies eine Matrixformel ist, vergessen Sie nicht zu drücken Strg + Umschalt + Enter statt des üblichen Enterum die Formel richtig einzugeben.
Fehler #WERT! in Formeln mit SVERWEIS
In den meisten Fällen meldet Microsoft Excel einen Fehler #WERT! (#WERT!), wenn der in der Formel verwendete Wert nicht mit dem Datentyp übereinstimmt. Betreffend VPR, dann gibt es in der Regel zwei Ursachen für den Fehler #WERT!.
1. Der gesuchte Wert ist länger als 255 Zeichen
Achtung: Funktion VPR kann nicht nach Werten suchen, die mehr als 255 Zeichen enthalten. Wenn der gesuchte Wert diese Grenze überschreitet, erhalten Sie eine Fehlermeldung. #WERT!.
Entscheidung: Verwenden Sie eine Reihe von Funktionen INDEX+VERGLEICH (INDEX + ÜBEREINSTIMMUNG). Unten ist eine Formel, die für diese Aufgabe gut geeignet ist:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Der vollständige Pfad zur Sucharbeitsmappe ist nicht angegeben
Wenn Sie Daten aus einer anderen Arbeitsmappe abrufen, müssen Sie den vollständigen Pfad zu dieser Datei angeben. Genauer gesagt müssen Sie den Namen der Arbeitsmappe (einschließlich der Erweiterung) in eckige Klammern [ ] einfügen, gefolgt vom Namen des Blatts, gefolgt von einem Ausrufezeichen. Alle diese Konstruktionen müssen in Apostrophe eingeschlossen werden, falls der Buch- oder Blattname Leerzeichen enthält.
Hier ist die vollständige Struktur der Funktion VPR um in einem anderen Buch zu suchen:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Die richtige Formel könnte so aussehen:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Diese Formel sucht nach dem Zellenwert A2 in einer Spalte B auf dem Blatt Sheet1 im Arbeitsheft Neue Preise und extrahieren Sie den entsprechenden Wert aus der Spalte D.
Wenn ein Teil des Tabellenpfads ausgelassen wird, wird Ihre Funktion VPR funktioniert nicht und meldet einen Fehler #WERT! (auch wenn die Arbeitsmappe mit der Nachschlagetabelle gerade geöffnet ist).
Weitere Informationen zur Funktion VPRVerweisen auf eine andere Excel-Datei finden Sie in der Lektion: Durchsuchen einer anderen Arbeitsmappe mit SVERWEIS.
3. Argument Column_num ist kleiner als 1
Es ist schwer vorstellbar, dass jemand einen Wert kleiner als eingibt 1um die Spalte anzugeben, aus der der Wert extrahiert werden soll. Obwohl es möglich ist, wenn der Wert dieses Arguments von einer anderen darin verschachtelten Excel-Funktion berechnet wird VPR.
Also, wenn es passiert, dass das Argument col_index_num (column_number) kleiner als 1Funktion VPR wird auch einen Fehler melden #WERT!.
Wenn die Argumentation col_index_num (column_number) ist größer als die Anzahl der Spalten im angegebenen Array, VPR wird einen Fehler melden BEZUG! (#SSYL!).
Fehler #NAME? im SVERWEIS
Der einfachste Fall ist ein Irrtum # NAME? (#NAME?) – erscheint, wenn Sie versehentlich einen Funktionsnamen mit einem Fehler schreiben.
Die Lösung liegt auf der Hand – prüfen Sie Ihre Rechtschreibung!
SVERWEIS funktioniert nicht (Einschränkungen, Vorbehalte und Entscheidungen)
Neben der ziemlich komplizierten Syntax, VPR hat mehr Einschränkungen als jede andere Excel-Funktion. Aufgrund dieser Einschränkungen sind scheinbar einfache Formeln mit VPR führen oft zu unerwarteten Ergebnissen. Nachfolgend finden Sie Lösungen für mehrere gängige Szenarien, in denen VPR ist falsch.
1. Beim SVERWEIS wird die Groß-/Kleinschreibung nicht beachtet
Funktion VPR unterscheidet nicht zwischen Groß- und Kleinschreibung und akzeptiert Klein- und Großbuchstaben als gleich. Wenn es also mehrere Elemente in der Tabelle gibt, die sich nur in der Groß-/Kleinschreibung unterscheiden, gibt die VLOOKUP-Funktion unabhängig von der Groß-/Kleinschreibung das erste gefundene Element zurück.
Entscheidung: Verwenden Sie eine andere Excel-Funktion, die eine vertikale Suche durchführen kann (LOOKUP, SUMPRODUCT, INDEX und MATCH) in Kombination mit GENAUEin unterscheidender Fall. Weitere Einzelheiten finden Sie in der Lektion – 4 Möglichkeiten, um SVERWEIS in Excel zwischen Groß- und Kleinschreibung zu unterscheiden.
2. SVERWEIS gibt den ersten gefundenen Wert zurück
Wie du bereits weißt, VPR gibt den Wert aus der angegebenen Spalte zurück, der der ersten gefundenen Übereinstimmung entspricht. Sie können jedoch die 2., 3., 4. oder jede andere Wiederholung des gewünschten Werts extrahieren. Wenn Sie alle doppelten Werte extrahieren müssen, benötigen Sie eine Kombination von Funktionen INDEX (INDEX), AM WENIGSTEN (KLEIN) und LINE (DIE ZEILE).
3. Eine Spalte wurde der Tabelle hinzugefügt oder daraus entfernt
Leider die Formeln VPR jedes Mal aufhören zu arbeiten, wenn eine neue Spalte zur Nachschlagetabelle hinzugefügt oder daraus entfernt wird. Dies geschieht aufgrund der Syntax VPR erfordert, dass Sie den gesamten Suchbereich und die spezifische Spaltennummer für die Datenextraktion angeben. Natürlich ändern sich sowohl der angegebene Bereich als auch die Spaltennummer, wenn Sie eine Spalte löschen oder eine neue einfügen.
Entscheidung: Und wieder haben Funktionen es eilig zu helfen INDEX (INDEX) und AUSGESETZTER (PASSEN). In der Formel INDEX+VERGLEICH Sie definieren Such- und Abrufspalten separat und können so viele Spalten löschen oder einfügen, wie Sie möchten, ohne sich Gedanken darüber machen zu müssen, alle zugehörigen Suchformeln aktualisieren zu müssen.
4. Zellbezüge werden beim Kopieren einer Formel verstümmelt
Diese Überschrift erklärt das Wesentliche des Problems erschöpfend, richtig?
Entscheidung: Verwenden Sie immer absolute Zellbezüge (mit dem Symbol $) auf zeichnet beispielsweise den Bereich auf $ A $ 2: $ C $ 100 or $ A: $ C.. In der Formelleiste können Sie den Linktyp schnell durch Klicken wechseln F4.
SVERWEIS – Arbeiten mit den Funktionen IFERROR und ISERROR
Wenn Sie Benutzer nicht mit Fehlermeldungen erschrecken möchten #N / A, #WERT! or # NAME?, können Sie eine leere Zelle oder Ihre eigene Nachricht anzeigen. Sie können dies tun, indem Sie platzieren VPR in eine Funktion IFERROR (IFERROR) in Excel 2013, 2010 und 2007 oder verwenden Sie eine Reihe von Funktionen WENN+FEHLER (IF+ISERROR) in früheren Versionen.
SVERWEIS: Arbeiten mit der IFERROR-Funktion
Funktionssyntax IFERROR (IFERROR) ist einfach und spricht für sich:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Das heißt, für das erste Argument fügen Sie den Wert ein, der auf einen Fehler überprüft werden soll, und für das zweite Argument geben Sie an, was zurückgegeben werden soll, wenn ein Fehler gefunden wird.
Diese Formel gibt beispielsweise eine leere Zelle zurück, wenn der gesuchte Wert nicht gefunden wird:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Wenn Sie anstelle der Standardfehlermeldung der Funktion eine eigene Meldung anzeigen möchten VPR, setzen Sie es in Anführungszeichen, etwa so:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
SVERWEIS: Arbeiten mit der ISERROR-Funktion
Da die Funktion IFERROR erschien in Excel 2007, wenn Sie in früheren Versionen arbeiten, müssen Sie die Kombination verwenden IF (IF) und EOSCHIBKA (ISERROR) so:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Zum Beispiel die Formel WENN+FEHLER+SVERWEIS, ähnlich der Formel WENNFEHLER+SVERWEISoben gezeigt:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Das ist alles für heute. Ich hoffe, dieses kurze Tutorial hilft Ihnen, mit allen möglichen Fehlern umzugehen. VPR und sorgen dafür, dass Ihre Formeln richtig funktionieren.