Share
EIN VLOOKUP, Abkürzung für "Vertical Lookup" ist eine Formel in Microsoft Excel zum Abgleichen von Daten aus zwei Listen. Anstatt zwischen Tabellenkalkulationen zu springen und passende Daten einzugeben, können Sie eine VLOOKUP-Formel schreiben, um den Prozess zu automatisieren.
Das Kombinieren von zwei Listen ist ideal, um ein VLOOKUP zu verwenden. Auf der linken Seite (im Bild oben) haben wir Informationen zur Schichtarbeit. Wir möchten den Schichtdaten die Berufsbezeichnung des Mitarbeiters hinzufügen. Mit einer separaten Liste der Mitarbeiter und ihrer Berufsbezeichnungen können wir eine VLOOKUP-Formel schreiben, um den Titel aus einer Nachschlageliste zu ziehen.
Ein erfolgreiches VLOOKUP benötigt drei Dinge:
VLOOKUP ist eine Microsoft Excel-Formel, die für das Arbeiten mit mehreren Datensätzen unerlässlich ist. In diesem Tutorial werde ich Ihnen zeigen, wie Sie es beherrschen und anwenden können.
Beispiel für eine VLOOKUP-Formel, die zum Abrufen von Mitarbeiterdaten verwendet wird.Anhand des obigen Beispiels habe ich nun eine VLOOKUP-Formel geschrieben, die die ID des Mitarbeiters nachschlägt und die Berufsbezeichnung in die Schichtdaten einfügt. Da beide Arbeitsblätter eine Mitarbeiter-ID haben, kann Excel den entsprechenden Jobtitel nachschlagen. Das Beste an VLOOKUP ist, dass ich jetzt die gleiche Formel nach unten ziehen kann und die einzelnen Jobtitel nachschlagen werden.
Bevor wir weitermachen, stellen Sie sicher, dass Laden Sie den kostenlosen Anhang herunter folgen. Es ist eine von mir erstellte Arbeitsmappe mit Tabellenkalkulation, die wir verwenden, um dieses Lernprogramm durchzugehen.
Der schnellste Weg, die Grundlagen der VLOOKUP-Formel zu erlernen, finden Sie im folgenden Screencast. In dem Video werden mehrere Beispiele der VLOOKUP-Formel anhand der Beispielarbeitsmappe beschrieben.
Lesen Sie weiter, um die schriftlichen Anweisungen durchzugehen, und lernen Sie einige zusätzliche Techniken kennen, die im Screencast nicht behandelt werden.
Verwenden Sie für dieses Excel-VLOOKUP-Beispiel die Registerkarten "Zutatenbestellungen" und "Lieferantenliste".
Nehmen wir an, wir führen ein Restaurant und geben unsere wöchentlichen Bestellungen an Lieferanten ab. Die Köche haben uns eine Liste mit Zutaten für die Bestellung gegeben, und wir müssen Informationen über den Lieferanten einfügen.
Für jede Bestellung müssen drei Artikel hinzugefügt werden:
In dieser Arbeitsmappe gibt es zwei Registerkarten:
Oben haben wir unsere Bestelldaten, die sich auf der Registerkarte "Zutatenbestellungen" befinden. Nachfolgend finden Sie die "Lieferantenliste", die als Nachschlageliste dient.Das gemeinsame Feld zwischen den beiden Tabellen ist das Zutat Tab. Verwenden Sie es, um jedes der drei Felder nachzuschlagen und zur Bestellliste hinzuzufügen.
Auf der Zutatenbestellungen Klicken Sie auf die erste leere Lieferantenzelle, F5, und drücken Sie das Gleichheitszeichen, um die VLOOKUP-Formel zu starten. Dann tippe " VLOOKUP ( " um die Formel zu beginnen.
= VLOOKUP (
Denken Sie daran, dass unser primärer Schlüssel, der in beiden Listen auftaucht, der Schlüssel ist Zutat, also werden wir es für die Suche verwenden. Klicken Sie entweder auf die Zelle B5, oder geben Sie es in die Formel ein. Fügen Sie nach "B5" ein Komma ein, damit wir den nächsten Teil der Formel eingeben können.
= VLOOKUP (B5,
Nun müssen wir der Formel unsere Nachschlageliste geben. Klicken Sie bei geöffneter Formel auf die Schaltfläche Lieferantenliste Tab. Klicken Sie nun auf Zelle A3 und klicken und ziehen Sie, um hervorzuheben und auszuwählen A3 zu G13, die ganze Nachschlagetabelle. Vergewissern Sie sich und drücken Sie das F4-Taste auf Ihrer Tastatur um die Formel zu einer absoluten Referenz zu machen (dazu später mehr). Geben Sie schließlich ein anderes Komma ein.
Zeigen Sie Excel auf die Nachschlageliste.Nachdem Sie das Komma für die Suchzelle eingegeben haben, wechseln Sie die Registerkarten und zeigen Sie mit Excel auf die Suchliste. Klicken und ziehen Sie zwischen den Zellen A3 und G3, um die Daten auszuwählen, aus denen gesucht werden soll. Vergewissern Sie sich und drücken Sie F4 Erstellen Sie auf dieser Tastatur in diesem Schritt eine absolute Referenz, die die Zellen für die Suche einschließt.
= VLOOKUP (B5, 'Lieferantenliste'!) $ A $ 3: $ G $ 13,
Als nächstes müssen wir Excel mitteilen, aus welcher Spalte er abrufen soll. Denken Sie daran, dass unser erster Artikel der ist Lieferant Name, der sich in der zweiten Spalte der Nachschlageliste befindet. Fügen Sie die Nummer hinzu 2 auf die Formel aus der zweiten Spalte der Suche und ein weiteres Komma.
= VLOOKUP (B5, 'Lieferantenliste'!) $ A $ 3: $ G $ 13,2,
Zum Schluss fügen wir FALSE hinzu, um genau nachschlagen zu können, und schließen dann die Klammern:
= VLOOKUP (B5, "Lieferantenliste"! $ A $ 3: $ G $ 13,2, FALSE)
Unsere Formel funktioniert perfekt und zieht den Lieferanten für die Kartoffeln an. Die gute Nachricht ist, dass wir diese Formel nicht immer wieder neu schreiben müssen, sondern nur einen Doppelklick in die rechte untere Ecke der Zelle F5 (Sie können das kleine grüne Kästchen an der Ecke der Zelle sehen), Um die Formel nach unten zu verlängern (wie oben gezeigt).
Die Formel funktioniert perfekt! Okay, ziehen wir nun die Daten für die anderen beiden Felder ein: die Telefonnummer des Lieferanten und den Liefertag.
Da wir eine absolute Referenz verwendet haben, können wir grundsätzlich dieselbe Formel wiederverwenden, die wir mit einem kleinen Tweak geschrieben haben. Fügen wir als nächstes die Lieferantentelefonnummer hinzu.
Wir beginnen mit dem Kopieren und Einfügen von Zellen F5 (unsere Lieferantenzelle) zur Zelle G5. Normalerweise verwende ich nur die Tastenkombinationen Strg + C und Strg + V Kopieren und Einfügen der gesamten Zelle.Zunächst wird das nicht funktionieren, und Sie sehen eine N / A in der Zelle.
Kopieren und Einfügen von Lookup mit Anpassungen in Excel.Es ist viel einfacher, unsere Formel zu kopieren und in eine andere Zelle einzufügen, aber es bedarf einiger Anpassungen. Excel sucht zunächst nach Zelle C5 für die Suche, aber wir müssen sie in der Formelleiste auf "B5" einstellen. Sobald wir das tun, wird die Suche fast funktionieren.
Wir müssen zur Formelleiste gehen und den ersten Teil der Formel von ändern C5 zurück zu B5. Als wir den zogen Formel um eine Spalte überschrieben, hat Excel andere Teile der Formel aktualisiert. Wir haben ein "N / A" erhalten, weil Excel versucht hat, die Menge (Spalte C) mit unserer Suchliste abzugleichen, aber unsere Suchliste enthält nicht die Menge.
Bisher sollte unsere Formel also lauten:
= VLOOKUP (B5, "Lieferantenliste"! $ A $ 3: $ G $ 13,2, FALSE)
Beachten Sie jedoch im obigen Screenshot, dass wir das falsche Datenelement in die Spalte "Telefon" ziehen. Wir ziehen immer noch die zweite Spalte in der Nachschlageliste mit dem "2 " in der Formel. Wir müssen dies mit dem Kontakttelefon unseres Lieferanten in die Spaltennummer ändern.
Gehen wir zurück und schauen uns die Lookup-Liste an. Sie werden feststellen, dass sich die Telefonnummer in der 7. Spalte der Nachschlageliste befindet. Gehen wir zurück zu unserer Excel-Formel und aktualisieren Sie die Suche, um sie aus der 7. Spalte zu ziehen.
Wir müssen die "2" in unserer Excel-Formel auf "7" aktualisieren, um aus der 7. Spalte unserer Nachschlageliste zu gelangen.Alles, was wir tun müssen, ist, den Spaltenabschnitt unserer Nachschlageliste von "2" auf "7" zu aktualisieren, und es funktioniert jetzt großartig!
Beachten Sie, dass das Telefon des Anbieters jetzt gut funktioniert, nachdem wir unsere Formel für die Verwendung der 7. Spalte der Nachschlageliste aktualisiert haben. Ziehen Sie die Formel einfach nach unten, um sie für alle Zellen zu aktualisieren.Unsere endgültige Formel für die Suche nach Lieferantennummern:
= VLOOKUP (B5, 'Lieferantenliste'!) $ A $ 3: $ G $ 13,7, FALSE)
Schließlich arbeiten wir in der Spalte Supplier Delivery Day. Zelle kopieren und einfügen G5 in die Zelle H5. Wieder müssen wir unsere Formel durch Änderung korrigieren C5 zurück zu B5 um die Zutat als Primärschlüssel zu verwenden. Dann aktualisieren Sie einfach die "7" auf eine "5", um die fünfte Spalte aus unserer Nachschlagetabelle zu verwenden.
Die endgültige Lookup-Formel des Lieferantenlieferungs-Tages wurde angewendet.Unsere endgültige Formel für den Lieferanten-Liefertag:
= VLOOKUP (B5, 'Lieferantenliste'!) $ A $ 3: $ G $ 13,5, FALSE)
Das ist es! Wir haben im Wesentlichen eine Formel geschrieben und sie angepasst, um jedes Datenelement einzuholen, das wir für die nächste Bestellung benötigen.
Sie haben also die VLOOKUP-Formel nach den Anweisungen Schritt für Schritt geschrieben, aber sie funktioniert immer noch nicht. Excel nimmt die Dinge recht wörtlich, daher müssen wir mit den Daten und der VLOOKUP-Formel vorsichtig sein. Schauen wir uns einige Ideen zur Korrektur einer VLOOKUP-Formel an, die einfach nicht funktioniert.
Eines der häufigsten Probleme bei VLOOKUP ist, wenn Ihre Suchliste mehrere Übereinstimmungen enthält. Wenn Sie ein VLOOKUP verwenden, stimmt es mit dem ersten Eintrag in der Liste überein.
Unser VLOOKUP sagt, dass Carrie Richard unser Marketingleiter ist, aber wir wissen, dass sie kürzlich zum Präsidenten des Unternehmens befördert wurde. Warum funktioniert unsere Suche nicht??
Whoops, es scheint, als gäbe es zwei Einträge für Carrie Richard in der Suchliste - eines für Director of Marketing und eines für President. Sobald wir die Zeile "Marketingdirektor" aus der Suchliste löschen, sind unsere Daten korrekt.Der Sinn von VLOOKUP besteht darin, ein übereinstimmendes Element anhand einer Liste nachzuschlagen. Die Nachschlageliste sollte keine Duplikate für die enthalten Primärschlüssel, das Element, das Sie zur Übereinstimmung verwenden. Andernfalls wird Excel verwirrt und zeigt nur die erste Übereinstimmung an.
Ein weiteres häufiges Problem ist, dass unsere Daten in den Augen von Excel möglicherweise nicht wirklich übereinstimmen. Daten mit einem Leerzeichen, bevor gesagt wird, dass sie ein "führendes" Leerzeichen haben, während Daten mit einem Leerzeichen dahinter ein "nachfolgendes Leerzeichen" haben.
Dieses VLOOKUP ist perfekt geschrieben, funktioniert aber nicht. Sie können deutlich erkennen, dass sich der Name in der Suchliste befindet, aber Excel gibt keine Übereinstimmung zurück. Lesen Sie weiter, um herauszufinden warum.Wenn Ihre übereinstimmenden Daten ein Leerzeichen vor oder nach ihnen enthalten, werden diese beiden Daten in Excel völlig unterschiedlich angezeigt und liefern keine Übereinstimmung. Excel betrachtet "_Andrew", "Andrew_" und "Andrew" als drei eindeutige Datenelemente, die in VLOOKUP nicht übereinstimmen.
Es stellt sich heraus, dass es ein "Leerzeichen" oder ein einzelnes Leerzeichen nach dem Namen in der Zelle gibt. Es ist nicht sichtbar, aber es kann zu einem VLOOKUP führen, da "Alyssa Reddall" und "Alyssa Reddall (Leerzeichen)" in Excel unterschiedlich behandelt werden. Wenn Sie den Speicherplatz gelöscht haben, funktioniert das VLOOKUP normal.Wenn Ihre Daten führende oder nachgestellte Leerzeichen enthalten, verwenden Sie die TRIMMEN Funktion in Excel, um es aufzuräumen. VLOOKUP sollte nach dem Entfernen von führenden und nachgestellten Leerzeichen gut funktionieren.
Wie Sie vielleicht wissen, können Sie eine Zelle am Griff ziehen, um die Formel in andere Zellen einzufügen. Dies unterbricht jedoch manchmal unser VLOOKUP, da sich die Zellverweise ändern.
In diesem Screenshot habe ich das VLOOKUP in der Liste nach unten gezogen, um es auf andere Zellen anzuwenden. Beachten Sie jedoch, dass sich die Referenz für die Suche von A2 zu B15 und zu A16 zu B29 geändert hat, weshalb die Formel nicht funktioniert. Meine Nachschlageliste befindet sich tatsächlich in A2 bis B15, daher ist die Suche jetzt unterbrochen.Wenn Sie die Formeln nach unten ziehen, wird die Referenz für die Nachschlagetabelle aus der Ausrichtung gezogen. Plötzlich fehlen in der Nachschlagetabelle Zeilen aus der Nachschlagetabelle, und unser VLOOKUP funktioniert nicht.
Die Lösung besteht darin, die Formel anzugleichen absoluter Bezug, Wenn Sie die Formel nach unten ziehen, ändert sich die Liste, auf die sie zeigt, nicht. Klicken Sie in die Zelle, in die Sie Ihr VLOOKUP geschrieben haben, und klicken Sie dann irgendwo in die Referenzliste. Dann drücken F4 . Sie werden feststellen, dass die Formel Dollarzeichen enthält.
Beachten Sie, dass die Formel (oben in der Screenshot-Leiste in der Formelleiste) jetzt Dollarzeichen in der Referenzliste für die Nachschlagliste enthält, was darauf hinweist, dass sie ein absoluter Bezug. Wenn ich jetzt die Formel nach unten ziehe, wird die Formel für die Nachschlageliste gesperrt und funktioniert einwandfrei.VLOOKUP ist eine dieser wesentlichen Formeln, um ein produktiver Excel-Benutzer zu sein. Es ist einfach nicht genug Zeit, um manuell nach Daten zu suchen und sie immer wieder neu einzugeben. Formeln wie VLOOKUP sind daher wichtig zu lernen.
Wenn Sie Probleme mit der VLOOKUP-Formel haben, können Sie unten einen Kommentar zur Problembehandlung hinterlassen.
Accentsconagua