Tabellen verbinden (VLOOKUP)

Was ist VLOOKUP

Die integrierte Funktion VLOOKUP ist eine der leistungsstärksten Funktionen in Excel. Es gehört zu den drei beliebtesten in Excel – nach SUM und AVERAGE. Ihre Aufgabe besteht darin, den gewünschten Wert in der Datentabelle zu finden und ihn in der angegebenen Zelle anzuzeigen.

Die Funktionssyntax umfasst einen Suchwert (was Sie suchen), eine Tabelle mit einer oder mehreren Spalten (wo gesucht werden soll), eine Spaltenindexnummer (aus welcher Spalte Daten zurückgegeben werden sollen) und einen Suchtyp (ein zusätzliches Argument). mit der Sie ein ungefähres oder genaues Ergebnis wählen können).

Die Funktion VLOOKUP wird häufig für Finanzanalysen, Datenverwaltung und die Arbeit mit Datenbanken verwendet. Mit dieser Funktion können Benutzer schnell und einfach Informationen in einem großen Datensatz finden, was Zeit sparen und die Genauigkeit verbessern kann.

Die Funktion VLOOKUP sucht Werte vertikal, also zwischen Zeilen (dies wird durch den ersten Buchstaben V – Vertical angezeigt). In Excel gibt es auch eine ähnliche Funktion HLOOKUP, die eine ähnliche Funktionalität hat, aber eine horizontale (H – Horizontal) Suche nach Spalten durchführt.

Ein Beispiel für ein Problem

Angenommen, wir haben zwei Tabellen mit Daten - Kredittabelle und Sicherheitentabelle :

Für die weitere Berichterstattung müssen wir Sicherheiten basierend auf der Vertragsnummer automatisch in die Kredittabelle einfügen.

Lösung

In Excel im Standardsatz von Funktionen in der Kategorie Nachschlagen und Referenzieren ist eine Funktion VLOOKUP . Diese Funktion sucht den angegebenen Wert (in unserem Beispiel die Vertragsnummer) in der Spalte ganz links der angegebenen Tabelle (Sicherheitstabelle) von oben nach unten und zeigt beim Finden den Wert der angrenzenden Zelle (Art der Kreditsicherheit) an). Schematisch sieht die Funktionsweise der Funktion so aus:

Also nutzen wir die Funktion VLOOKUP . Wählen Sie die Zelle aus, in die es eingegeben werden soll ( E2 ) und öffnen Sie den Formelassistenten (Menü Insert - Function ). In der Kategorie (Suche und Referenz) Finden Sie die Funktion VLOOKUP und drücke OK . Es erscheint ein Fenster zur Eingabe von Funktionsargumenten:

Füllen Sie sie einzeln aus:

• Lookup Value – die Vereinbarungsnummer, nach der die Funktion in der Spalte ganz links der Bereitstellungstabelle sucht. In unserem Fall die Vertragsnummer „KF568“ aus der Zelle C2 .
• Table Array - Die Tabelle, aus der wir die erforderlichen Werte erhalten, ist unsere Versorgungstabelle. Als Referenz verwenden wir einen Bereich $G$2:$H$11 (Der Bereich wird durch ein Dollarzeichen festgelegt ($) , damit es beim Kopieren der Formel nicht nach unten wandert).
• Column index number - die laufende Nummer (kein Buchstabe!) der Spalte in der Bereitstellungstabelle, aus der wir den Namen der Bereitstellung entnehmen. Die erste Spalte der Bereitstellungstabelle hat die laufende Nummer 1, daher müssen wir die Art der Bereitstellung aus der zweiten Spalte (Nummer 2) entnehmen.
• Range Lookup - Wir können in dieses Feld nur zwei Werte eingeben: TRUE oder FALSE:
  
  1) Wenn wir einen Wert eingeben 0 oder FALSE , dann bedeutet das eigentlich, dass nur die Suche erlaubt ist mit einer genauen Übereinstimmung , das heißt, wenn die Funktion die Vertragsnummer nicht in der Bereitstellungstabelle findet (sofern sie nicht vorhanden ist), wird ein Fehler angezeigt #N / A (keine Daten).
  
  2) Wenn wir einen Wert eingeben 1 oder TRUE Dies bedeutet, dass nicht nur genau, sondern auch gesucht werden darf ungefährer Zufall . In den meisten Fällen kann eine solche ungefähre Substitution zu einem falschen Ergebnis führen VLOOKUP wird die Werte anderer Vereinbarungen ersetzen. Daher sollten Sie die Näherungssuche nur für numerische Werte verwenden (aufsteigend vorsortiert).

Es bleibt zu drücken OK und kopieren Sie die eingegebene Funktion über die gesamte Spalte.

Nachteile der Funktion VLOOKUP

Der Hauptnachteil besteht darin, dass die Suche nach dem gewünschten Wert nur in der ersten Spalte des angegebenen Bereichs erfolgen kann und die Funktion den gewünschten Wert nur aus den Spalten rechts davon zurückgeben kann.

Der zweite Nachteil von VLOOKUP besteht darin, dass die Funktion nicht mehr funktioniert, wenn Sie eine Spalte zur Nachschlagetabelle entfernen oder hinzufügen. Ein eingefügtes oder gelöschtes Element ändert das Ergebnis der Formel, da die Funktionssyntax erfordert, dass Sie den gesamten Bereich und die spezifische Spaltennummer angeben, aus der Sie Daten extrahieren möchten.

Außerdem hat die Funktion VLOOKUP eine Suchlängenbeschränkung von 255 Zeichen, andernfalls wird der Fehler #VALUE zurückgegeben!

Probleme beim Arbeiten mit der Funktion

Funktion VLOOKUP gibt einen Fehler zurück (#N / A) Wenn:

1. Exakte Suche ist erlaubt (Argument Range Lookup=0) und der gesuchte Wert ist nicht in der Tabelle (Table) .
2. Ungefähre Suche erlaubt (Range Lookup=1), aber in der Tabelle (Table) , in dem wir nach Werten suchen, werden die Namen nicht aufsteigend sortiert.
3. Das Format der Zelle, aus der wir die Transaktionsnummer entnehmen (z. B. C2 in unserem Fall) und das Zellenformat der ersten Spalte (G2:G11) Tabellen sind unterschiedlich (z. B. numerisch und Text). Diese Situation ist besonders typisch, wenn numerische Codes anstelle von Textnamen (Kontonummern, Identifikationscodes, Daten usw.) verwendet werden. In diesem Fall können Sie Funktionen verwenden VALUE Und TEXT um Datenformate zu konvertieren. Es sieht aus wie das:
   =VLOOKUP(TEXT(C2);$G$2:$H$11;0) .
4. Die Funktion findet keinen Wert, da der Code Leerzeichen und unsichtbare nicht druckbare Zeichen (Bandvorschub usw.) enthält. In diesem Fall können Sie die Textfunktion nutzen (TRIM) Und (CLEAN) um sie zu entfernen:
   =VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0) .

Fehlerunterdrückung

Um die Fehlermeldung zu unterdrücken (#N / A) Wenn die Funktion keine exakte Übereinstimmung finden kann, können Sie die Funktion verwenden IFERROR . Diese Funktion prüft, ob ein Fehler vorliegt (#N / A) ist das Ergebnis der Arbeit VLOOKUP , und wenn ja, gibt es ein leeres Band aus ( „“ ) oder Null, wenn nicht - das Ergebnis der Arbeit VLOOKUP .

Artikel zum Thema:

• Verbesserte Funktion VLOOKUP (VLOOKUP2)
• VLOOKUP zur Kombination nach zwei Bedingungen (VLOOKUP3)
• Schnelle Zusammenführung großer Tabellen (VLOOKUP2D)