Excel VLOOKUP - TechTV Artikel

Viele Leute versuchen, Excel als Datenbank zu verwenden. Während es als Datenbank funktionieren kann, sind einige der Aufgaben, die in einem Datenbankprogramm sehr einfach wären, in Excel ziemlich komplex. Eine dieser Aufgaben besteht darin, zwei Listen basierend auf einem gemeinsamen Feld abzugleichen. Dies kann einfach mit Excel VLOOKUP erreicht werden. Die Funktion VLOOKUP ist äußerst nützlich. Sehen Sie sich unten ein Beispiel an, wann und wie diese Funktion verwendet wird.

Angenommen, Ihr Reisebüro sendet Ihnen einen Monatsabschlussbericht über alle Orte, an denen Ihre Mitarbeiter gereist sind. Der Bericht verwendet Flughafencodes anstelle von Städtenamen. Es wäre hilfreich, wenn Sie einfach den Namen der realen Stadt anstelle nur des Codes eingeben könnten.

Im Internet finden und importieren Sie eine Liste mit dem Städtenamen für jeden Flughafencode.

Aber wie erhalten Sie diese Informationen zu jedem Datensatz im Bericht?

1. Verwenden Sie die VLOOKUP-Funktion. VLOOKUP steht für "Vertical Lookup". Es kann jederzeit verwendet werden, wenn Sie eine Datenliste mit dem Schlüsselfeld in der Spalte ganz links haben.
2. Beginnen Sie mit der Eingabe der Funktion =VLOOKUP(. Geben Sie Strg + A ein, um Hilfe bei der Funktion zu erhalten.
3. VLOOKUP benötigt vier Parameter. Erstens ist der Stadtcode im Originalbericht. In diesem Beispiel wäre das Zelle D4
4. Der nächste Parameter ist der Bereich mit Ihrer Nachschlagetabelle. Markieren Sie den Bereich. Verwenden Sie unbedingt F4, um den Bereich absolut zu machen. (Eine absolute Referenz hat ein Dollarzeichen vor der Spalten- und Zeilennummer. Wenn die Formel kopiert wird, zeigt die Referenz weiterhin in Richtung I3: J351.
5. Der 3. Parameter teilt Excel mit, in welcher Spalte der Städtename gefunden wird. Im Bereich von I3: J351 befindet sich der Städtename in Spalte 2. Geben Sie für diesen Parameter eine 2 ein.
6. Der 4. Parameter teilt Excel mit, ob eine "enge" Übereinstimmung in Ordnung ist. In diesem Fall ist dies nicht der Fall. Geben Sie also False ein.
7. Klicken Sie auf OK, um die Formel zu vervollständigen. Ziehen Sie den Füllpunkt, um die Formel nach unten zu kopieren.
8. Da Sie die absoluten Formeln sorgfältig eingegeben haben, können Sie Spalte E in Spalte D kopieren, um die Zielstadt zu erhalten. In diesem Fall sind alle Abflüge vom Pearson International Airport in Toronto.

Während dieses Beispiel perfekt funktioniert hat, bedeutet es bei der Verwendung von VLOOKUP durch die Betrachter normalerweise, dass sie Listen abgleichen, die aus verschiedenen Quellen stammen. Wenn Listen aus verschiedenen Quellen stammen, kann es immer subtile Unterschiede geben, die die Übereinstimmung der Listen erschweren. Hier sind drei Beispiele dafür, was schief gehen kann und wie man sie korrigiert.

1. Eine Liste enthält Bindestriche und die andere Liste nicht. Verwenden Sie die =SUBSTITUTE()Funktion, um die Bindestriche zu entfernen. Wenn Sie das VLOOKUP zum ersten Mal ausprobieren, werden N / A-Fehler angezeigt.

   Verwenden Sie die SUBSTITUTE-Formel, um die Bindestriche mit einer Formel zu entfernen. Verwenden Sie 3 Argumente. Das erste Argument ist die Zelle, die den Wert enthält. Das nächste Argument ist der Text, den Sie ändern möchten. Das letzte Argument ist der Ersatztext. In diesem Fall möchten Sie Bindestriche in nichts ändern, daher lautet die Formel =SUBSTITUTE(A4,"-","").

   Sie können diese Funktion in das VLOOKUP einbinden, um die Beschreibung zu erhalten.

2. Dieser ist subtil, aber sehr häufig. Eine Liste enthält nach dem Eintrag ein Leerzeichen. Verwenden Sie = TRIM (), um überschüssige Leerzeichen zu entfernen. Wenn Sie die Formel zum ersten Mal eingeben, stellen Sie fest, dass alle Antworten N / A-Fehler sind. Sie wissen sicher, dass die Werte in der Liste enthalten sind und mit der Formel alles in Ordnung aussieht.

   Eine Standardüberprüfung besteht darin, in die Zelle mit dem Suchwert zu wechseln. Drücken Sie F2, um die Zelle in den Bearbeitungsmodus zu versetzen. Im Bearbeitungsmodus können Sie sehen, dass sich der Cursor ein Leerzeichen vom letzten Buchstaben entfernt befindet. Dies zeigt an, dass der Eintrag ein nachfolgendes Leerzeichen enthält.

   Verwenden Sie die TRIM-Funktion, um das Problem zu lösen. =TRIM(D4)entfernt führende und nachfolgende Leerzeichen und ersetzt alle internen doppelten Leerzeichen durch ein einzelnes Leerzeichen. In diesem Fall funktioniert TRIM perfekt, um den nachgestellten Speicherplatz zu entfernen. =VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)ist die Formel.

3. Ich erwähnte einen Bonus-Tipp in den Shownotizen: Wie man das # N / A-Ergebnis für fehlende Werte durch ein Leerzeichen ersetzt. Wenn sich Ihr Nachschlagewert nicht in der Nachschlagetabelle befindet, gibt VLOOKUP einen N / A-Fehler zurück.

   Diese Formel verwendet die =ISNA()Funktion, um festzustellen, ob das Ergebnis der Formel ein N / A-Fehler ist. Wenn Sie den Fehler erhalten, weist das zweite Argument in der IF-Funktion Excel an, einen beliebigen Text einzugeben.

   =IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))

Mit VLOOKUP können Sie Zeit sparen, wenn Sie Datenlisten abgleichen. Nehmen Sie sich Zeit, um die grundlegende Verwendung zu erlernen, und Sie können weitaus leistungsfähigere Aufgaben in Excel ausführen.