Inhalt
- Beispiele für die Verwendung von VLOOKUP in Excel 2007 und 2010
- So verwenden Sie VLOOKUP zum Abrufen von Preisinformationen für Produkte in Excel 2007 und 2010
- Verwenden von VLOOKUP zum Vervollständigen der Adresse von Kunden in einer Bestellung unter Verwendung ihrer Kundennummer in Excel 2007 und 2010
- Verwenden der VLOOKUP-Funktion in einer Formel, wenn sich Ihre Daten in einer anderen Excel 2007- oder 2010-Arbeitsmappe befinden
- Excel 2007 und 2010 behandeln Ihre Daten als Tabelle, wenn Sie VLOOKUP verwenden
- Und jetzt ein Quiz, um zu sehen, was wir heute gelernt haben!
- Lösungsschlüssel
- Interpretieren Sie Ihre Punktzahl
- Fazit
Robbie schreibt hauptsächlich über Skyrim, beleuchtet aber gelegentlich auch die Kuriositäten von Microsoft-Anwendungen wie Excel und Outlook.
Sowohl Excel 2007 als auch 2010 verfügen über eine enorme Anzahl verfügbarer Funktionen. Heute werde ich mir eine der leistungsstärksten Excel-Funktionen, VLOOKUP, ansehen und wie sie in Formeln verwendet werden kann. Was VLOOKUP im Wesentlichen tut, ist, dass es nach einem Wert in einer Datentabelle sucht und einige spezifische Informationen zurückgibt, die sich auf diesen Wert beziehen. Um zu veranschaulichen, wie Sie die Leistung von VLOOKUP nutzen können, werden wir uns einige Beispiele ansehen:
- Erstens werden wir VLOOKUP verwenden, um Preisinformationen zu Produkten aus unserem Preiskatalog abzurufen, damit wir den Wert einer Bestellung bestimmen können.
- Zweitens wird VLOOKUP verwendet, um den Namen und die Adresse eines Benutzers in der Bestellung zu vervollständigen, indem die Kunden-ID des Kunden in unserer Kundendatenbank nachgeschlagen und seine Daten zurückgegeben werden.
- Zuletzt werden wir uns mit VLOOKUP befassen, um Daten in einer anderen Arbeitsmappe nachzuschlagen.
Beispiele für die Verwendung von VLOOKUP in Excel 2007 und 2010
Angenommen, Sie besitzen einen Musikladen / eine Musikwebsite. Sie haben eine Liste mit Bestellungen von Kunden in Ihrem Auftragsbuch, Ihren Katalog mit all Ihren Preisen und eine Datenbank mit Kundendaten.
Jedem Kunden wird nach dem Kauf eine Kundennummer zugewiesen und seine Daten werden in die Datenbank eingegeben. Wenn eine Bestellung eingeht, erfassen Sie diese Bestellung im Auftragsbuch unter Verwendung der Kundennummer in Ihrer Tabelle.
In unserem ersten Beispiel möchten wir, dass Excel Ihnen den Wert einer Kundenbestellung mitteilen kann. Sie wissen, dass der Kunde das Green-Album von R.E.M. zum Beispiel auf CD. Mit VLOOKUP vergleicht Excel 2007 oder 2010 Ihre Bestellung mit Ihrem Katalog und gibt den Preis zurück.
In unserem zweiten Beispiel soll Excel die Adresse eines Kunden automatisch abrufen, wenn wir seine Kunden-ID in eine Bestellung eingeben.
Schauen wir uns jetzt jedes Beispiel genauer an, bevor wir uns auch mit der Verwendung von VLOOKUP zum Abrufen von Daten aus einer anderen Arbeitsmappe befassen.
So verwenden Sie VLOOKUP zum Abrufen von Preisinformationen für Produkte in Excel 2007 und 2010
In unserem ersten Beispiel verwenden wir VLOOKUP, um den Preis unserer Produkte zu ermitteln (jedes Medium hat unterschiedliche Kosten). In meinem Beispiel unten habe ich die Preisdetails rechts und die Bestelldetails links. Wenn mein Musikgeschäft ein tatsächliches Geschäft wäre, würden sich die Daten wahrscheinlich auf verschiedenen Blättern in derselben Arbeitsmappe oder sogar in einer anderen Arbeitsmappe befinden, aber ich habe sie nebeneinander, um leichter erkennen zu können, wie die Formeln zusammenpassen.
Der nächste Schritt ist die Formel selbst. Ich werde zuerst die fertige Formel zeigen und dann jeden Teil erklären.
= VLOOKUP (C14, $ F $ 19: $ G $ 22,2, FALSE)
Die Formel besteht aus vier Teilen:
- Der erste Teil teilt Excel mit, wonach VLOOKUP suchen soll (in diesem Beispiel suche ich nach einer CD, damit ich Excel die Zelle gebe C14 Als Referenz).
- Der zweite Teil definiert den Zellbereich (in diesem Fall) $ F $ 19: $ G $ 22) Ich bitte Excel, nach dem Inhalt der Zelle zu suchen C14 (CD).
Hinweis:Das $ neben der Spalte und Zeile der Zelle teilt Excel mit, dass sie nicht von einer Formel zur nächsten wechseln, sondern immer konstant sind. Dadurch wird sichergestellt, dass Excel immer Ihren genauen Bereich für die VLOOKUP-Formel verwendetwenn Sie es in andere Zellen in Ihrer Arbeitsmappe kopieren.
- Der dritte Teil ist etwas kompliziert. Lassen Sie uns die Formel aufschlüsseln, um genau zu zeigen, was Excel tut und wie es den dritten Teil der Formel verwendet:
- Wir haben VLOOKUP gebeten, den Inhalt der Zelle zu finden C14(Teil 1) im Bereich $ F $ 19: $ G $ 22 (Teil 2).
- Excel sucht in der ersten Spalte des Bereichs ($ F19- $ F22) und findet CD in Zelle F19.
- Teil 3 Weist Excel an, in der zweiten Zeile nachzuschauen, wenn es eine CD findet, also wenn es eine CD findet F19gibt den Wert 10 zurück (G19) das ist in der zweiten Reihe und genau das, was wir wollten!
- Im letzten Teil wird Excel darüber informiert, ob Sie nach einer genauen Übereinstimmung (False) oder einer ungefähren Übereinstimmung (True) suchen. Da ich nach einer genauen Übereinstimmung suche, habe ich in diesem Fall Falsch gewählt.
Hinweis: Dieser Teil kann kompliziert sein und insbesondere bei Verwendung von True kann Excel gelegentlich falsche Ergebnisse zurückgeben. Ich habe einen Artikel, der zeigt, wie der wahre und der falsche Wert richtig verwendet werden, damit VLOOKUP jedes Mal ein korrektes Ergebnis zurückgibt. Dieser Artikel kann gefunden werden Hier.
In der Abbildung unten sehen Sie die vollständige Formel sowie alle Zellen, auf die verwiesen wird.
Verwenden von VLOOKUP zum Vervollständigen der Adresse von Kunden in einer Bestellung unter Verwendung ihrer Kundennummer in Excel 2007 und 2010
In unserem nächsten Beispiel gebe ich jedes Mal, wenn ein Kunde etwas in meinem Shop bestellt, seine Kundennummer in seine Bestellung ein und möchte, dass Excel seine Namen und Adressen mithilfe von VLOOKUP automatisch ausfüllt. Ich habe all diese Details in meiner Kundendatenbank gespeichert und möchte, dass Excel diese automatisch für mich abruft. Ich habe die Daten wie folgt eingerichtet, mit meinem Auftragsbuch links und der Kundendatenbank rechts.
Die Formel ist genauso aufgebaut wie im obigen Beispiel.
= VLOOKUP (A14, $ N $ 16: $ O $ 25,2, FALSE)
Die vier Schritte sind wieder dieselben wie im vorherigen Beispiel.
- 1 (enthalten in Zelle A14) ist das, wonach Sie in Excel 2007 oder 2010 suchen sollen.
- $ N $ 16: $ O $ 25 ist der Zellenbereich, in den Excel schauen soll.
- 2 ist die Zeile, die die Daten enthält, die Excel zurückgeben soll.
- FALSCH is weist Excel an, nur dann Daten zurückzugeben, wenn eine genaue Übereinstimmung gefunden wird.
Die folgende Abbildung zeigt die Formel und die Zellen, auf die sie verweist, damit Sie VLOOKUP in Aktion sehen können.
Verwenden der VLOOKUP-Funktion in einer Formel, wenn sich Ihre Daten in einer anderen Excel 2007- oder 2010-Arbeitsmappe befinden
Wenn sich einige Ihrer Daten, auf die VLOOKUP verweist, in einer anderen Arbeitsmappe befinden (z. B. wenn meine Kundendatenbank von einem anderen Mitglied meines Teams verwaltet und in eine andere Arbeitsmappe eingegeben wird), ist es ganz einfach, diesen Bereich einer neuen hinzuzufügen oder vorhandene Formel.
= VLOOKUP (E14, ’C: [Book1] Sheet1’! $ A $ 1: $ B $ 4,2, FALSE)
Das Format ist:
- Erstens den Speicherort der Datei (in meinem Fall C: ) mit einem ’vor, wie oben gezeigt
- Der Name der Datei in [] (in meinem Fall Book2.xlsx).
- Das Blatt, auf dem sich die Daten befinden (in meinem Fall Blatt 1), gefolgt von einem ’!
- Und schließlich der Datenbereich wie gewohnt.
Es gibt eine andere Möglichkeit, die viele leichter finden, da Sie keine Formeln ändern müssen.
Öffnen Sie beide Excel 2007-Arbeitsmappen und klicken Sie auf Fenster wiederherstellen Schaltfläche (wie unten gezeigt) für die Dokumente und nicht für Excel selbst, sodass die Arbeitsmappe nur einen Teil des gesamten Excel-Fensters einnimmt.
Öffnen Sie die zweite Arbeitsmappe und positionieren Sie sie so, dass sie sich neben der anderen geöffneten Arbeitsmappe befindet. Geben Sie wie gewohnt in den ersten Teil der Formel ein und wählen Sie dann bei Auswahl des Bereichs für den zweiten Teil die Daten in der zweiten Arbeitsmappe wie unten gezeigt aus. Sie werden feststellen, dass der Pfad in dieser Formel bei geöffneten Arbeitsmappen nicht ganz richtig aussieht. = VLOOKUP (E14, [Book1.xlsx] Sheet1! $ A $ 1: $ B $ 4,2, FALSE) Sobald Sie die Arbeitsmappe schließen, die den Bereich enthält (in meinem Fall Buch 1), ändert sich die Formel in etwas, das logischer aussieht. = VLOOKUP (E14, 'C: [Book1.xlsx] Sheet1'! $ A $ 1: $ B $ 4,2, FALSE)
Excel 2007 und 2010 behandeln Ihre Daten als Tabelle, wenn Sie VLOOKUP verwenden
Eine großartige Funktion von Excel 2007 und 2010 sowie der VLOOKUP-Funktion ist, dass Excel bei Verwendung von VLOOKUP Ihre Daten in eine Tabelle konvertiert. Es sieht immer noch wie normale Daten aus und fühlt sich auch so an, aber wenn ich beispielsweise in Zeile 24 eine neue Bestellung in meine Arbeitsmappe eingebe und die Kundennummer der Person eingebe, die eine neue Bestellung in Zelle A24 aufgegeben hat, füllt Excel die VLOOKUP-Daten automatisch aus (In meinem Fall geht es in Harry James: 15 Queens Avenue in B24 neben den fünf, die ich in Zelle A24 eingegeben habe). Sobald ich eingebe, was der Kunde bestellt hat, füllt Excel die Stückkosten automatisch aus und gibt sie für mich in Zelle F24 ein.
Wenn Sie dieses Verhalten unterbrechen möchten, lassen Sie eine leere Zeile und geben Sie dann neue Daten ein. Excel füllt Zellen nicht automatisch mit VLOOKUP (wenn ich 7 in Zelle A26 eingebe, wird Excel nicht automatisch gefüllt, da A25 leer ist es weiß, dass die Tabelle vervollständigt wurde).
Und jetzt ein Quiz, um zu sehen, was wir heute gelernt haben!
Wählen Sie für jede Frage die beste Antwort. Der Antwortschlüssel ist unten.
- Wie viele Teile enthält eine VLOOKUP-Formel?
- 2
- 4
- 3
- Was sagt ein $ vor einer Zellreferenz zu Excel?
- Dass sich die Zellreferenzen beim Kopieren nicht ändern
- Dass die Zelle Währungsinformationen enthält
- Dass die Zelle eine Formel enthält
- Was macht der vierte Teil einer VLOOKUP-Formel?
- Überprüft, ob die Formel eine numerische Antwort zurückgibt
- Überprüfen Sie, ob die Formel einen Fehler enthält
- Teilen Sie Excel mit, ob eine genaue oder eine ungefähre Übereinstimmung gesucht werden soll
Lösungsschlüssel
- 4
- Dass sich die Zellreferenzen beim Kopieren nicht ändern
- Teilen Sie Excel mit, ob eine genaue oder eine ungefähre Übereinstimmung gesucht werden soll
Interpretieren Sie Ihre Punktzahl
Wenn Sie 0 richtige Antworten erhalten haben: Hoppla, lesen Sie meinen Hub noch einmal und überprüfen Sie Ihre Antworten
Wenn Sie 1 richtige Antwort haben: Gut gemacht, fast da!
Wenn du 2 richtige Antworten hast: Du hast bestanden, nette!
Wenn Sie 3 richtige Antworten erhalten haben: Gut gemacht, Klassenbester, klopfen Sie sich virtuell auf den Rücken!
Fazit
VLOOKUP ist eine sehr leistungsstarke Formel, mit der Sie die Dateneingabe und die damit verbundenen Fehler drastisch reduzieren können, indem Sie automatisch Daten für Sie suchen und abrufen. Durch die schrittweise Bearbeitung der VLOOKUP-Funktion in zwei Beispielen hoffe ich, dass es mir gelungen ist, diese entmutigend aussehende Funktion für Sie zugänglich zu machen, und dass Sie sie jetzt in Ihren eigenen Tabellenkalkulationen verwenden können und dass Sie dadurch auch viel sparen Zeit und Tippen!
Dieser Artikel ist genau und nach bestem Wissen des Autors. Der Inhalt dient nur zu Informations- oder Unterhaltungszwecken und ersetzt nicht die persönliche Beratung oder professionelle Beratung in geschäftlichen, finanziellen, rechtlichen oder technischen Angelegenheiten.
