Wie man

Verwendung von VLOOKUP in Excel

VLOOKUP ist eine der nützlichsten Funktionen von Excel und eine der am wenigsten verstandenen. In diesem Artikel entmystifizieren wir VLOOKUP anhand eines Beispiels aus der Praxis. Wir erstellen ein brauchbares für ein fiktives Unternehmen.

VLOOKUP ist ein Excel. In diesem Artikel wird davon ausgegangen, dass der Leser bereits über ein umfassendes Verständnis der Excel-Funktionen verfügt und grundlegende Funktionen wie SUMME, DURCHSCHNITTLICH und HEUTE verwenden kann. In der gebräuchlichsten Verwendung ist VLOOKUP eine Funktion, dh es funktioniert mit Datenbanktabellen – oder einfacher mit Dingen in einem Excel-Arbeitsblatt. Was für Dinge? So etwas. Möglicherweise verfügen Sie über ein Arbeitsblatt, das eine Liste von Mitarbeitern, Produkten, Kunden, CDs in Ihrer CD-Sammlung oder Sternen am Nachthimmel enthält. Es ist nicht wirklich wichtig.

Hier ist ein Beispiel für eine Liste oder Datenbank. In diesem Fall handelt es sich um eine Liste von Produkten, die unser fiktives Unternehmen verkauft:

Normalerweise haben Listen wie diese eine eindeutige Kennung für jedes Element in der Liste. In diesem Fall befindet sich die eindeutige Kennung in der Spalte «Artikelcode». Hinweis: Damit die VLOOKUP-Funktion mit einer Datenbank / Liste funktioniert, muss diese Liste eine Spalte enthalten, die die eindeutige Kennung (oder den „Schlüssel“ oder die „ID“) enthält. Unsere obige Beispieldatenbank erfüllt dieses Kriterium.

Der schwierigste Teil bei der Verwendung von VLOOKUP besteht darin, genau zu verstehen, wofür es ist. Mal sehen, ob wir das zuerst klarstellen können:

VLOOKUP ruft Informationen aus einer Datenbank / Liste basierend auf einer bereitgestellten Instanz der eindeutigen Kennung ab.

Im obigen Beispiel würden Sie die VLOOKUP-Funktion in eine andere Tabelle mit einem Artikelcode einfügen und entweder die Beschreibung des entsprechenden Artikels, seinen Preis oder seine Verfügbarkeit (die Menge auf Lager) wie in Ihrem Original beschrieben an Sie zurücksenden Liste. Welche dieser Informationen werden Sie zurückgeben? Nun, Sie können dies entscheiden, wenn Sie die Formel erstellen.

Wenn Sie nur eine Information aus der Datenbank benötigen, ist es sehr schwierig, eine Formel mit einer VLOOKUP-Funktion zu erstellen. Normalerweise verwenden Sie diese Art von Funktionalität in einer wiederverwendbaren Tabelle, z. B. einer Vorlage. Jedes Mal, wenn jemand einen gültigen Artikelcode eingibt, ruft das System alle erforderlichen Informationen zum entsprechenden Artikel ab.

Lassen Sie uns ein Beispiel dafür erstellen: Ein Beispiel, das wir in unserer fiktiven Firma immer wieder verwenden können.

Zuerst starten wir Excel und erstellen uns eine leere Rechnung:

So wird es funktionieren: Die Person, die die Rechnungsvorlage verwendet, gibt eine Reihe von Artikelcodes in Spalte „A“ ein und das System ruft die Beschreibung und den Preis jedes Artikels aus unserer Produktdatenbank ab. Diese Informationen werden verwendet, um die Zeilensumme für jeden Artikel zu berechnen (vorausgesetzt, wir geben eine gültige Menge ein).

Um dieses Beispiel einfach zu halten, suchen wir die Produktdatenbank auf einem separaten Blatt in derselben Arbeitsmappe:

In der Realität ist es wahrscheinlicher, dass sich die Produktdatenbank in einer separaten Arbeitsmappe befindet. Es macht wenig Unterschied für die VLOOKUP-Funktion, die sich nicht wirklich darum kümmert, ob sich die Datenbank auf demselben Blatt, einem anderen Blatt oder einer völlig anderen Arbeitsmappe befindet.

Also haben wir unsere Produktdatenbank erstellt, die so aussieht:

Um die VLOOKUP-Formel zu testen, die wir schreiben möchten, geben wir zunächst einen gültigen Artikelcode in Zelle A11 unserer leeren Rechnung ein:

Als Nächstes verschieben wir die aktive Zelle in die Zelle, in der Informationen gespeichert werden sollen, die von VLOOKUP aus der Datenbank abgerufen wurden. Interessanterweise ist dies der Schritt, den die meisten Menschen falsch machen. Zur weiteren Erläuterung: Wir sind dabei, eine VLOOKUP-Formel zu erstellen, die die Beschreibung abruft, die dem Artikelcode in Zelle A11 entspricht. Wo soll diese Beschreibung stehen, wenn wir sie bekommen? Natürlich in Zelle B11. Hier schreiben wir also die VLOOKUP-Formel: in Zelle B11. Wählen Sie jetzt Zelle B11 aus.

Wir müssen die Liste aller verfügbaren Funktionen finden, die Excel zu bieten hat, damit wir VLOOKUP auswählen und Unterstützung beim Ausfüllen der Formel erhalten können. Dies finden Sie, indem Sie zuerst auf klicken Formeln Klicken Sie auf die Registerkarte und dann auf Funktion einfügen::

Es erscheint ein Feld, in dem wir alle in Excel verfügbaren Funktionen auswählen können.

Um den gesuchten zu finden, können wir einen Suchbegriff wie «Nachschlagen» eingeben (da die Funktion, an der wir interessiert sind, eine Funktion ist). Das System würde uns eine Liste aller nachschlagebezogenen Funktionen in Excel zurückgeben. VLOOKUP ist der zweite in der Liste. Wählen Sie es mit einem Klick aus OK.

Das Funktionsargumente Das Feld wird angezeigt, in dem Sie aufgefordert werden, alle (oder) erforderlichen Informationen einzugeben, um die VLOOKUP-Funktion abzuschließen. Sie können sich dieses Feld als die Funktion vorstellen, die uns die folgenden Fragen stellt:

Die ersten drei Argumente werden angezeigt fett gedrucktDies zeigt an, dass es sich um Argumente handelt (die VLOOKUP-Funktion ist ohne sie unvollständig und gibt keinen gültigen Wert zurück). Das vierte Argument ist nicht fett, was bedeutet, dass es optional ist:

Wir werden die Argumente der Reihe nach von oben nach unten vervollständigen.

Das erste Argument, das wir vervollständigen müssen, ist das Lookup-Wert Streit. Die Funktion muss uns mitteilen, wo sich die eindeutige Kennung befindet (in diesem Fall die), deren Beschreibung zurückgegeben werden soll. Wir müssen den zuvor eingegebenen Artikelcode auswählen (in A11).

Klicken Sie auf das Auswahlsymbol rechts neben dem ersten Argument:

Klicken Sie dann einmal auf die Zelle mit dem Artikelcode (A11) und drücken Sie Eingeben::

Der Wert von «A11» wird in das erste Argument eingefügt.

Jetzt müssen wir einen Wert für die eingeben Table_array Streit. Mit anderen Worten, wir müssen VLOOKUP mitteilen, wo sich die Datenbank / Liste befindet. Klicken Sie auf das Auswahlsymbol neben dem zweiten Argument:

Suchen Sie nun die Datenbank / Liste und wählen Sie die gesamte Liste aus – ohne die Kopfzeile. In unserem Beispiel befindet sich die Datenbank in einem separaten Arbeitsblatt. Klicken Sie daher zunächst auf die Registerkarte Arbeitsblatt:

Als nächstes wählen wir die gesamte Datenbank ohne die Kopfzeile aus:

…und drücke Eingeben. Der Zellenbereich, der die Datenbank darstellt (in diesem Fall «Produktdatenbank»! A2: D7 «), wird für uns automatisch in das zweite Argument eingegeben.

Jetzt müssen wir das dritte Argument eingeben, Col_index_num. Wir verwenden dieses Argument, um VLOOKUP anzugeben, welche Informationen aus der Datenbank, die mit unserem Artikelcode in A11 verknüpft sind, an uns zurückgegeben werden sollen. In diesem Beispiel möchten wir, dass der Artikel an uns zurückgesandt wird. Wenn Sie sich das Datenbankarbeitsblatt ansehen, werden Sie feststellen, dass die Spalte «Beschreibung» die Spalte in der Datenbank ist. Dies bedeutet, dass wir einen Wert von «2» in das eingeben müssen Col_index_num Box:

Es ist wichtig zu beachten, dass wir hier keine „2“ eingeben, da sich die Spalte „Beschreibung“ in der befindet B. Spalte in diesem Arbeitsblatt. Wenn die Datenbank zufällig in der Spalte gestartet wurde K. des Arbeitsblatts würden wir immer noch eine «2» in dieses Feld eingeben, da die Spalte «Beschreibung» die zweite Spalte in der Gruppe von Zellen ist, die wir bei der Angabe des «Table_array» ausgewählt haben.

Schließlich müssen wir entscheiden, ob ein Wert in das endgültige VLOOKUP-Argument eingegeben werden soll. Range_lookup. Dieses Argument erfordert entweder a wahr oder falsch Wert, oder es sollte leer gelassen werden. Wenn Sie VLOOKUP mit Datenbanken verwenden (wie es in 90% der Fälle der Fall ist), können Sie sich wie folgt vorstellen, wie Sie entscheiden, was in dieses Argument eingefügt werden soll:

Wenn die erste Spalte der Datenbank (die Spalte, die die eindeutigen Bezeichner enthält) in aufsteigender Reihenfolge alphabetisch / numerisch sortiert ist, kann ein Wert von eingegeben werden wahr in dieses Argument, oder lassen Sie es leer.

Wenn die erste Spalte der Datenbank sortiert oder in absteigender Reihenfolge sortiert ist, geben Sie den Wert von ein falsch in dieses Argument

Wenn die erste Spalte unserer Datenbank sortiert ist, geben wir ein falsch in dieses Argument:

Das ist es! Wir haben alle Informationen eingegeben, die VLOOKUP benötigt, um den von uns benötigten Wert zurückzugeben. Drücke den OK Klicken Sie auf die Schaltfläche und beachten Sie, dass die Beschreibung für den Artikelcode „R99245“ korrekt in Zelle B11 eingegeben wurde:

Die Formel, die für uns erstellt wurde, sieht folgendermaßen aus:

Wenn wir einen Artikelcode in Zelle A11 eingeben, sehen wir die Leistungsfähigkeit der VLOOKUP-Funktion: Die Beschreibungszelle ändert sich entsprechend dem neuen Artikelcode:

Wir können einen ähnlichen Satz von Schritten ausführen, um den Artikel in Zelle E11 zurückzugeben. Beachten Sie, dass die neue Formel in Zelle E11 erstellt werden muss. Das Ergebnis sieht folgendermaßen aus:

… Und die Formel sieht folgendermaßen aus:

Beachten Sie, dass der einzige Unterschied zwischen den beiden Formeln das dritte Argument ist (Col_index_num) hat sich von einer „2“ in eine „3“ geändert (weil wir möchten, dass Daten aus der 3. Spalte in der Datenbank abgerufen werden).

Wenn wir uns entscheiden würden, 2 dieser Artikel zu kaufen, würden wir in Zelle D11 eine „2“ eingeben. Wir würden dann eine einfache Formel in Zelle F11 eingeben, um die Zeilensumme zu erhalten:

= D11 * E11

… Was so aussieht…

Vervollständigen der Rechnungsvorlage

Wir haben bisher viel über VLOOKUP gelernt. Tatsächlich haben wir alles gelernt, was wir in diesem Artikel lernen werden. Es ist wichtig zu beachten, dass VLOOKUP neben Datenbanken auch unter anderen Umständen verwendet werden kann. Dies ist weniger häufig und wird möglicherweise in zukünftigen How-To-Geek-Artikeln behandelt.

Unsere Rechnungsvorlage ist noch nicht vollständig. Um dies zu vervollständigen, würden wir Folgendes tun:

Wir würden den Beispielartikelcode aus Zelle A11 und die „2“ aus Zelle D11 entfernen. Dadurch werden in unseren neu erstellten VLOOKUP-Formeln Fehlermeldungen angezeigt:

Wir können dies durch den umsichtigen Einsatz von Excel beheben WENN() und IST LEER() Funktionen. Wir ändern unsere Formel daraus … = VLOOKUP (A11, ‘Produktdatenbank’! A2: D7,2, FALSE)… Dazu…= IF (ISBLANK (A11), ””, VLOOKUP (A11, ‘Produktdatenbank’! A2: D7,2, FALSE))
Wir würden die Formeln in den Zellen B11, E11 und F11 bis zum Rest der Artikelzeilen der Rechnung kopieren. Beachten Sie, dass die resultierenden Formeln in diesem Fall nicht mehr korrekt auf die Datenbanktabelle verweisen. Wir könnten dies beheben, indem wir die Zellreferenzen für die Datenbank in Zellreferenzen ändern. Alternativ – und noch besser – könnten wir eine für die gesamte Produktdatenbank (z. B. „Produkte“) erstellen und diesen Bereichsnamen anstelle der Zellreferenzen verwenden. Die Formel würde sich davon ändern … = IF (ISBLANK (A11), ””, VLOOKUP (A11, ‘Produktdatenbank’! A2: D7,2, FALSE))… Dazu… = IF (ISBLANK (A11), ””, VLOOKUP (A11, Produkte, 2, FALSE))… Und kopieren Sie die Formeln in die restlichen Rechnungspostenzeilen.
Wir würden wahrscheinlich die Zellen, die unsere Formeln enthalten (oder besser gesagt die Zellen), «sperren» und dann das Arbeitsblatt schützen, um sicherzustellen, dass unsere sorgfältig erstellten Formeln nicht versehentlich überschrieben werden, wenn jemand kommt, um die Rechnung auszufüllen.
Wir würden die Datei als speichern, damit sie von allen in unserem Unternehmen wiederverwendet werden kann

Wenn wir uns schlau fühlen würden, würden wir eine Datenbank aller unserer Kunden in einem anderen Arbeitsblatt erstellen und dann die in Zelle F5 eingegebene Kunden-ID verwenden, um den Namen und die Adresse des Kunden in den Zellen B6, B7 und B8 automatisch einzugeben.