Excel VLOOKUP ist leistungsstark, funktioniert jedoch in bestimmten Situationen nicht. Heute ein Blick auf die Fehlerbehebung bei VLOOKUP.
VLOOKUP ist meine Lieblingsfunktion in Excel. Wenn Sie VLOOKUP ausführen können, können Sie viele Probleme in Excel lösen. Aber es gibt Dinge, die einen VLOOKUP auslösen können. In diesem Thema werden einige davon behandelt.
Aber zuerst die Grundlagen von VLOOKUP in einfachem Englisch.
Die Daten in A: C stammen aus der IT-Abteilung. Sie haben nach Artikel und Datum nach Verkäufen gefragt. Sie gaben Ihnen Artikelnummer. Sie benötigen eine Artikelbeschreibung. Anstatt darauf zu warten, dass die IT-Abteilung die Daten erneut ausführt, finden Sie die Tabelle in Spalte F: G.
Sie möchten, dass VLOOKUP das Element in A2 findet, während es die erste Spalte der Tabelle in $ F $ 3: $ G $ 30 durchsucht. Wenn VLOOKUP die Übereinstimmung in F7 findet, soll VLOOKUP die Beschreibung in der zweiten Spalte der Tabelle zurückgeben. Jeder VLOOKUP, der nach einer genauen Übereinstimmung sucht, muss mit False (oder Null, was False entspricht) enden. Die folgende Formel ist richtig eingerichtet.
Beachten Sie, dass Sie mit F4 der Adresse für die Nachschlagetabelle vier Dollarzeichen hinzufügen. Wenn Sie die Formel in Spalte D kopieren, muss die Adresse für die Nachschlagetabelle konstant bleiben. Es gibt zwei gängige Alternativen: Sie können die gesamten Spalten F: G als Nachschlagetabelle angeben. Oder Sie können F3: G30 mit einem Namen wie ItemTable benennen. Wenn Sie verwenden =VLOOKUP(A2,ItemTable,2,False), fungiert der benannte Bereich als absolute Referenz.
Jedes Mal, wenn Sie eine Reihe von VLOOKUPs ausführen, müssen Sie die Spalte der VLOOKUPs sortieren. Wenn Sie ZA sortieren, werden alle # N / A-Fehler nach oben verschoben. In diesem Fall gibt es einen. Artikel BG33-9 fehlt in der Nachschlagetabelle. Vielleicht ist es ein Tippfehler. Vielleicht ist es ein brandneues Produkt. Wenn es neu ist, fügen Sie eine neue Zeile in die Mitte Ihrer Nachschlagetabelle ein und fügen Sie das neue Element hinzu.
Es ist ziemlich normal, ein paar # N / A-Fehler zu haben. In der folgenden Abbildung gibt genau dieselbe Formel jedoch nur # N / A zurück. Wenn dies passiert, sehe ich, ob ich den ersten VLOOKUP lösen kann. Sie suchen nach dem BG33-8 in A2. Fahren Sie durch die erste Spalte der Nachschlagetabelle. Wie Sie sehen können, ist der Übereinstimmungswert eindeutig in F10. Warum können Sie das sehen, aber Excel kann es nicht sehen?
Gehen Sie zu jeder Zelle und drücken Sie die Taste F2. Hier ist F10. Beachten Sie, dass der Einfügecursor direkt nach der 8 angezeigt wird.
Hier ist Zelle A2 im Bearbeitungsmodus. Der Einfügecursor ist ein paar Leerzeichen von der 8 entfernt. Dies ist ein Zeichen dafür, dass diese Daten irgendwann in einem alten COBOL-Datensatz gespeichert wurden. Wenn in COBOL das Feld Element als 10 Zeichen definiert wurde und Sie nur 6 Zeichen eingegeben haben, würde COBOL es mit 4 zusätzlichen Leerzeichen auffüllen.
Die Lösung? Anstatt nach A2 zu suchen, schauen Sie nach TRIM(A2).
Die Funktion TRIM () entfernt führende und nachfolgende Leerzeichen. Wenn Sie mehrere Leerzeichen zwischen Wörtern haben, konvertiert TRIM diese in ein einzelnes Leerzeichen. In der folgenden Abbildung stehen in A1 Leerzeichen vor und nach beiden Namen. =TRIM(A1)Entfernt alle bis auf ein Leerzeichen in A3.
Was wäre übrigens, wenn das Problem Leerzeichen in Spalte F anstelle von Spalte A hinter sich gelassen hätte? Fügen Sie E eine Spalte mit TRIM () -Funktionen hinzu, die auf Spalte F zeigt. Kopieren Sie diese und fügen Sie sie als Werte in F ein, damit die Suchvorgänge wieder funktionieren.
Der andere sehr häufige Grund, warum VLOOKUP nicht funktioniert, wird hier gezeigt. Spalte F hat reelle Zahlen. Spalte A enthält Text, der wie Zahlen aussieht.
Wählen Sie die gesamte Spalte A aus. Drücken Sie alt = "" + D, E, F. Dies führt einen Standardtext in Spalten aus und konvertiert alle Textnummern in reelle Zahlen. Die Suche beginnt wieder zu funktionieren.
Schau Video
- VLOOKUP löst viele Probleme
- Häufige VLOOKUP-Probleme:
- Wenn VLOOKUP anfängt zu arbeiten, aber # N / A an Bedeutung gewinnt: $ in der Nachschlagetabelle vergessen
- Ein paar # N / A: Elemente fehlen in der Tabelle
- Keine der VLOOKUP-Funktionen: Suchen Sie nach nachgestellten Leerzeichen
- Entfernen Sie nachgestellte Leerzeichen mit TRIM
- Zahlen und Zahlen als Text gespeichert
- Wählen Sie beide Spalten aus und verwenden Sie alt = "" + DEF
- Die Folge enthält einen Witz, den sowohl Buchhalter als auch IT-Leute lustig finden, aber aus verschiedenen Gründen
Video-Transkript
Lernen Sie Excel aus dem Podcast, Folge 2027 - Fehlerbehebung bei VLOOKUP!
Okay, wenn Sie das gesamte Buch podcasten, klicken Sie auf das „i“ in der oberen rechten Ecke, um zur Wiedergabeliste zu gelangen!
VLOOKUP ist meine Lieblingsfunktion in ganz Excel, und ich erzähle diesen Witz immer in einem meiner Seminare, und es wird zum Lachen gebracht, ob Sie eine IT-Person sind oder nicht. Ich sage immer: „Schauen Sie, wir haben diesen Datensatz hier links, und ich kann mir diese Daten ansehen und feststellen, dass die Daten von der IT-Abteilung stammen, weil es genau das ist, wonach ich gefragt habe, aber nicht wirklich das, was ich brauchte. Ich habe nach Datum und Menge des Artikels gefragt, und sie haben mir Datum und Menge der Artikelnummer gegeben, aber sie haben sich nicht die Mühe gemacht, mir eine Beschreibung zu geben, oder? “ Und die Buchhalter lachen immer darüber, weil ihnen das die ganze Zeit passiert und die IT-Leute sagen: "Nun, ich habe dir gegeben, wonach du gefragt hast, es ist nicht meine Schuld!" Beide finden es aus verschiedenen Gründen lustig. Wenn der IT-Mitarbeiter beschäftigt ist, kann er die Abfrage nicht mehr umschreiben.Wir müssen also etwas tun, um dies selbst zu retten.
Und so sagt VLOOKUP in dieser kleinen Tabelle, die ich von einer anderen Stelle in meinem Computer kopiert habe, im Klartext: „Hey, wir haben eine Artikelnummer W25-6.“ Wir haben hier eine Tabelle. Ich möchte durch die erste Spalte der Tabelle fahren, bis ich diese Artikelnummer gefunden habe, und dann etwas aus dieser Zeile zurückgeben. Okay, in diesem Fall möchte ich die 2. Spalte aus dieser Zeile. Und dann müssen wir am Ende jedes VLOOKUP entweder FALSE oder 0 setzen. Nachdem ich den Bereich ausgewählt habe, drücke ich hier die Taste F4, und dann möchte ich die 2. Spalte und dann FALSE für eine genaue Spiel. Wählen Sie niemals eine ungefähre Übereinstimmung, niemals, niemals! Es ist keine ungefähre Übereinstimmung, es ist eine ganz besondere Sache, es funktioniert nicht immer. Wenn Sie Ihre Nummern an die Securities and Exchange Commission weitergeben möchten, können Sie diese auf jeden Fall verwenden.WAHR oder lass einfach das, FALSCH aus. Aber jeder VLOOKUP, den Sie jemals erstellen, sollte mit FALSE enden, oder, 0, 0 ist kürzer als FALSE. Sie sollten dort ein FALSE setzen, aber eine 0 ist dasselbe wie FALSE.
Okay, Fehlerbehebung, als erstes, wenn Sie eine ganze Reihe von VLOOKUPs machen, ist es ganz normal, ein paar # N / As zu haben, oder? Und ich finde die # N / A's immer, indem ich zu Data gehe, ZA, das die # N / As nach oben bringt, genau dort, BG33-9, entweder das ist ein Tippfehler oder es ist ein brandneues Produkt, okay, und wir haben es um es herauszufinden. Also hier rechts habe ich die neuen Daten, ich werde sie ausschneiden und dann Alt + IED, diese Zellen in die Mitte einfügen, es muss nicht alphabetisch sein, diese Tabelle muss nicht sortiert werden. Wenn Sie die FALSE-Version verwenden, ist die Tabelle nicht - Sie können sie einfach an einer beliebigen Stelle platzieren. Ich habe sie nicht am Ende eingefügt, weil ich die Formel nicht neu schreiben musste, sondern nur die Formel Arbeit. Okay, also ein paar # N / A's, extrem, extrem normal, aber probieren Sie es aus.
Wenn Sie mit Antworten beginnen, die funktionieren, aber dann die # N / A etwas häufig erscheinen und schließlich ganz nach unten erscheinen, ist dies ein sicheres Zeichen dafür, dass Sie die Tabellenreferenz nicht gesperrt haben. Okay, sehen Sie, hier bewegt sich die Tabelle, während ich die Formel nach unten kopiere, richtig, und ich habe das Glück, ein paar zu treffen, die am Ende der Liste standen. Aber irgendwann komme ich zu dem Punkt, an dem es hier in völlig leeren Zellen hinüberblickt, und natürlich wird nichts gefunden. Okay, das ist das Erste, du bekommst ein paar, die funktionieren, ein paar # N / A's, ein paar mehr, die funktionieren, und dann alle # N / A's den Rest des Weges - sicheres Zeichen, dass du das nicht gesetzt hast $ in.
Gehen Sie einfach zurück, F2 für den Bearbeitungsmodus, wählen Sie den Doppelpunkt aus, drücken Sie F4, um alle $ einzugeben, doppelklicken Sie, um das abzuschießen, und die Dinge beginnen wieder zu funktionieren. Die nächste, genau die gleiche Formel, die Formel ist perfekt, BG33-8, sehen wir, wir bekommen nichts davon richtig. Okay, also schau mal, BG33-8, hey, da ist es, es ist genau dort, es ist rot, ich hätte es sehen sollen! Also komme ich zu diesem auf der rechten Seite, drücke F2 und beobachte die blinkende Einfügemarke und sehe, es ist direkt nach der 8, so, und dann komme ich hierher und drücke F2, da sind einige nachgestellte Leerzeichen dort. Dies ist in den Tagen von COBOL sehr, sehr häufig. Sie sagten: „Weißt du, wir geben dir 10 Leerzeichen für die Artikelnummer, und wenn du nicht alle 10 Leerzeichen eingibst, füllen sie die Leerzeichen . ” Und so ist das sehr häufig,und die großartige Lösung besteht darin, diese führenden und nachfolgenden Leerzeichen mit der TRIM-Funktion zu entfernen. Anstelle von A2 verwenden Sie die TRIM (A2), doppelklicken Sie, um das abzuschießen, in Ordnung, immer noch ist die BG33-9 wieder in der anderen Tabelle.
Okay, nur damit Sie verstehen, wie TRIM funktioniert, habe ich eine Reihe von Leerzeichen eingegeben, John, eine Reihe von Leerzeichen, Durran und eine Reihe von Leerzeichen, und dann habe ich vorher und nachher ein * verkettet, damit Sie sehen können, wie es aussieht . Wenn ich nach dem TRIM (P4) frage, werden alle führenden Räume, alle nachfolgenden Räume entfernt, und dann werden die mehreren Innenräume auf einen Raum reduziert. Okay, damit Sie sehen können, stellen Sie sich dort vor, dass sie führende und nachfolgende Leerzeichen loswerden. Alle verdoppelten Leerzeichen in der Mitte werden zu einem einzigen solchen Raum. Also TRIM, tolles, tolles Werkzeug in deinem Arsenal, okay, hier ist ein weiteres wirklich verbreitetes.
Wenn es nicht die nachgestellten Leerzeichen sind, dann ist das häufigste, was ich gesehen habe, wo wir hier wahre Zahlen haben und hier Zahlen als Text gespeichert haben. Und VLOOKUP wird das nicht als Übereinstimmung sehen, obwohl 4399, dies eine Zahl ist, dies ist Text, nicht funktioniert. Der schnellste Weg, eine Textspalte in Zahlen umzuwandeln, wählen Sie die Spalte, 3 Buchstaben nacheinander, alt = "" DEF, und plötzlich beginnen unsere VLOOKUPs wieder zu funktionieren, ein großartiger Tipp von vor etwa 1500 Podcasts. Okay, das sind also die häufigsten VLOOKUP-Probleme. Entweder haben Sie das $ vergessen oder Sie haben nachgestellte Leerzeichen oder Sie haben Zahlen und Zahlen als Text gespeichert. Alle diese Tipps finden Sie in diesem Buch "MrExcel XL". Klicken Sie auf das "i" in der oberen rechten Ecke. Sie können das Buch kaufen.
Okay, kurze Zusammenfassung: VLOOKUP löst viele Probleme, wenn ein VLOOKUP funktioniert, aber # N / A! wird prominenter, Sie haben vergessen, das $ in die Nachschlagetabelle zu setzen. Wenn es ein paar # N / A gibt, fehlen nur Elemente in der Tabelle. Wenn keiner der VLOOKUPs funktioniert und es sich um Text handelt, suchen Sie nach nachgestellten Leerzeichen. Sie können die TRIM-Funktion verwenden. Wenn Sie Zahlen und Zahlen als Text gespeichert haben, wählen Sie eine der Spalten aus, die den Text enthält, und führen Sie dann alt = "" DEF aus, um alle diese wieder auf Zahlen zu setzen.
Okay, hey, ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von!
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2027.xlsx
