So verwenden Sie VLOOKUP in Excel

2023 Autor: Peter John Melton | [email protected]. Zuletzt bearbeitet: 2023-08-25 03:28

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

VLOOKUP ist ein Excel Funktion. In diesem Artikel wird davon ausgegangen, dass der Leser bereits über ein begrenztes Verständnis der Excel-Funktionen verfügt und grundlegende Funktionen wie SUMME, AVERAGE und TODAY verwenden kann. In der häufigsten Verwendung ist VLOOKUP a Datenbank Funktion, was bedeutet, dass es mit Datenbanktabellen funktioniert - oder einfacher: Listen Dinge in einem Excel-Arbeitsblatt. Was für Dinge? Gut, irgendein so etwas. Möglicherweise verfügen Sie über ein Arbeitsblatt, das eine Liste von Mitarbeitern, Produkten oder Kunden oder CDs in Ihrer CD-Sammlung oder Sterne 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 solche Listen 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 arbeiten kann, muss diese Liste eine Spalte enthalten, die die eindeutige Kennung (oder „Schlüssel“oder „ID“) und enthält Diese Spalte muss die erste Spalte in der Tabelle sein. Unsere Beispieldatenbank erfüllt dieses Kriterium.

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

VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.

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

Wenn Sie nur eine Information aus der Datenbank benötigen, wäre es sehr schwierig, eine Formel mit einer VLOOKUP-Funktion zu erstellen. Normalerweise würden Sie diese Art von Funktionalität in einer wiederverwendbaren Tabelle wie einer Vorlage verwenden. 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: Rechnungsvorlage dass wir in unserem fiktiven Unternehmen immer wieder wiederverwenden können.

Zuerst starten wir Excel und erstellen uns eine leere Rechnung:

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

Wir haben also unsere Produktdatenbank erstellt, die wie folgt aussieht:

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

Um die gesuchte zu finden, könnten wir einen Suchbegriff wie "Lookup" eingeben (weil die Funktion, an der wir interessiert sind, eine Sieh nach oben Funktion). Das System gibt uns eine Liste aller Funktionen in Excel, die sich auf die Suche beziehen. VLOOKUP ist der zweite in der Liste. Wählen Sie einen Klick aus OK.

Das Funktionsargumente Das Feld erscheint und fordert uns auf, alle Argumente (oder Parameter) benötigt, um die VLOOKUP-Funktion abzuschließen. Sie können sich diese Box als Funktion vorstellen, die uns die folgenden Fragen stellt:

1. Welche eindeutige Kennung suchen Sie in der Datenbank?
2. Wo ist die Datenbank?
3. Welche Informationen aus der Datenbank, die der eindeutigen Kennung zugeordnet sind, möchten Sie für Sie abrufen?

Die ersten drei Argumente werden gezeigt in Fettdruckzeigt an, dass sie es sind verpflichtend Argumente (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:

Das erste Argument, das wir zum Abschluss bringen müssen, ist das Lookup-Wert Streit. Die Funktion muss uns mitteilen, wo der eindeutige Bezeichner zu finden ist Produktcode In diesem Fall) sollte die Beschreibung von zurückgegeben werden. Wir müssen den zuvor eingegebenen Artikelcode (in A11) auswählen.

Klicken Sie auf das Auswahlsymbol rechts vom ersten Argument:

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

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

…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 mit unserem Artikelcode in A11 in Verbindung gebracht werden sollen und an uns zurückgegeben werden sollen. In diesem speziellen Beispiel möchten wir, dass der Artikel Beschreibung zu uns zurückgekehrt Wenn Sie in das Datenbank-Arbeitsblatt schauen, werden Sie feststellen, dass die Spalte "Beschreibung" die zweite Spalte in der Datenbank. Das bedeutet, dass wir den Wert "2" in das Feld eingeben müssen Col_index_num Box:

Es ist wichtig anzumerken, dass wir hier keine "2" eingeben, da sich die Spalte "Beschreibung" in der Spalte befindet B Spalte in diesem Arbeitsblatt. Wenn die Datenbank 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 Menge von Zellen ist, die wir ausgewählt haben, wenn Sie "Table_array" angeben.

Schließlich müssen wir entscheiden, ob Sie einen Wert in das letzte VLOOKUP-Argument eingeben. Range_lookup. Dieses Argument erfordert entweder a wahr oder falsch Wert, oder es sollte leer bleiben. Bei der Verwendung von VLOOKUP mit Datenbanken (wie in 90% der Fälle) ist die Art und Weise der Entscheidung, was in dieses Argument eingefügt werden soll, wie folgt denkbar:

If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.

If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument

Als erste Spalte unserer Datenbank steht nicht sortiert, wir treten ein falsch in dieses argument:

Das ist es! Wir haben alle erforderlichen Informationen eingegeben, damit VLOOKUP den erforderlichen Wert zurückgibt. Drücke den OK und beachten Sie, dass die Beschreibung, die dem Artikelcode "R99245" entspricht, korrekt in Zelle B11 eingegeben wurde:

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

Wir können ähnliche Schritte ausführen, um die Artikel zu erhalten Preis zurück in Zelle E11. Beachten Sie, dass die neue Formel in Zelle E11 erstellt werden muss. Das Ergebnis sieht so aus:

Beachten Sie, dass der einzige Unterschied zwischen den beiden Formeln das dritte Argument ist (Col_index_num) hat sich von "2" in "3" geändert (da Daten aus der dritten Spalte der Datenbank abgerufen werden sollen).

Wenn wir uns entschieden haben, 2 dieser Artikel zu kaufen, geben wir in Zelle D11 eine "2" ein. Wir geben dann eine einfache Formel in die Zelle F11 ein, um die Zeilensumme zu erhalten:

=D11*E11

… Was so aussieht…

Rechnungsvorlage abschließen

Wir haben bisher viel über VLOOKUP gelernt. In der Tat haben wir alles gelernt, was wir in diesem Artikel lernen werden. Es ist wichtig zu wissen, dass VLOOKUP neben Datenbanken auch in anderen Situationen verwendet werden kann. Dies ist weniger üblich und wird möglicherweise in zukünftigen How-To-Geek-Artikeln behandelt.

Unsere Rechnungsvorlage ist noch nicht vollständig. Um es abzuschließen, würden wir Folgendes tun:

1. 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:

   

   Abhilfe können wir schaffen, indem wir Excel OB() und IST LEER() Funktionen. Wir ändern unsere Formel davon… = VLOOKUP (A11, "Produktdatenbank"! A2: D7,2, FALSE) … Dazu… = WENN (ISBLANK (A11),””, VLOOKUP (A11,”Produktdatenbank”! A2: D7,2, FALSE))

2. Wir würden die Formeln in den Zellen B11, E11 und F11 bis in die restlichen Artikelzeilen der Rechnung kopieren. Beachten Sie, dass in diesem Fall die resultierenden Formeln nicht mehr korrekt auf die Datenbanktabelle verweisen. Wir können dies beheben, indem Sie die Zellverweise für die Datenbank in ändern absolut Zellreferenzen. Alternativ - und noch besser - könnten wir eine erstellen Bereichsname für die gesamte Produktdatenbank (z. B. "Produkte") und verwenden Sie diesen Bereichsnamen anstelle der Zellverweise. Die Formel würde sich davon ändern… = WENN (ISBLANK (A11),””, VLOOKUP (A11,”Produktdatenbank”! A2: D7,2, FALSE)) … Dazu… = WENN (ISBLANK (A11),””, VLOOKUP (A11, Produkte, 2, FALSCH)) …und dann Kopieren Sie die Formeln in die restlichen Zeilen der Rechnungsposition.
3. Wir würden wahrscheinlich die Zellen "sperren", die unsere Formeln enthalten (oder besser gesagt) Freischalten das andere Zellen), und schützen Sie dann das Arbeitsblatt, um sicherzustellen, dass unsere sorgfältig erstellten Formeln nicht versehentlich überschrieben werden, wenn jemand die Rechnung ausfüllt.
4. Wir würden die Datei als Vorlage, damit es von jedem in unserem Unternehmen wiederverwendet werden kann

Wenn wir uns fühlten Ja wirklich Es wäre klug, wir erstellen eine Datenbank aller unserer Kunden in einem anderen Arbeitsblatt und verwenden dann die in Zelle F5 eingegebene Kundennummer, um den Namen und die Adresse des Kunden automatisch in die Zellen B6, B7 und B8 einzugeben.