Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office

Extrahieren von Daten aus einer Kalkulationstabelle mit VLOOKUP, MATCH und INDEX

Read Time: 7 mins
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

German (Deutsch) translation by Tatsiana Bochkareva (you can also view the original English article)

Wenn Sie eine Datenspalte aus einer Tabelle suchen und extrahieren und in eine andere einfügen müssen, verwenden Sie die Funktion VLOOKUP. Diese Funktion funktioniert in jeder Version von Excel in Windows und Mac sowie in Google Sheets. Mit dieser Funktion können Sie Daten in einer Tabelle anhand eines Bezeichners suchen, den sie mit einer anderen Tabelle gemeinsam haben. Die beiden Tabellen können sich auf verschiedenen Arbeitsblättern oder sogar auf verschiedenen Arbeitsmappen befinden. Es gibt auch eine HLOOKUP-Funktion, die dasselbe tut, aber die Daten horizontal über Zeilen angeordnet sind.

Die Funktionen MATCH und INDEX eignen sich gut, wenn Sie sich mit dem Ort bestimmter Daten befassen, z. B. der Spalte oder Zeile, die den Namen einer Person enthält.

Premium-Optionen

Wussten Sie vor dem Einstieg in die Excel-Funktionen, dass Envato Market über eine Reihe von Excel-Skripts und Plugins verfügt, mit denen Sie erweiterte Funktionen ausführen können?

Zum Beispiel können Sie:

Excel scripts and plugins on Envato MarketExcel scripts and plugins on Envato MarketExcel scripts and plugins on Envato Market
Excel-Skripte und Plugins auf Envato Market

Screencast

Wenn Sie dieses Tutorial mit Ihrer eigenen Excel-Datei fortsetzen möchten, können Sie dies tun. Wenn Sie möchten, laden Sie die für dieses Lernprogramm mitgelieferte ZIP-Datei herunter, die eine Beispielarbeitsmappe namens vlookup example.xlsx enthält.

Wie kann man VLOOKUP verwenden?

Wenn VLOOKUP den Bezeichner findet, den Sie in den Quelldaten angeben, kann es jede Zelle in dieser Zeile finden und die Informationen an Sie zurückgeben. Beachten Sie, dass sich der Bezeichner in den Quelldaten in der ersten Spalte der Tabelle befinden muss.

unique identifiers for vlookupunique identifiers for vlookupunique identifiers for vlookup
Ein eindeutiger Bezeichner sollte wie eine Seriennummer aussehen, wobei keine zwei in derselben Tabelle identisch sind.

Syntax

Die Syntax der VLOOKUP-Funktion lautet so:

=VLOOKUP(lookup value, table range, column number, [true/false])

Was bedeuten diese Argumente:

  • Lookup value. Die Zelle mit der eindeutigen Kennung.
  • Table range. Der Zellenbereich, in dem sich der Bezeichner in der ersten Spalte befindet, gefolgt von den restlichen Daten in den anderen Spalten.
  • Column number. Die Nummer der Spalte mit den Daten, nach denen Sie suchen. Verwechseln Sie das nicht mit dem Brief der Kolumne. In der obigen Abbildung befinden sich die Zustände in Spalte 4.
  • True/False. Dieses Argument ist optional. True bedeutet, dass eine ungefähre Übereinstimmung zulässig ist, und False bedeutet, dass nur eine genaue Übereinstimmung zulässig ist.

Wir möchten Umsatzbeträge aus der Tabelle in der obigen Abbildung ermitteln. Dazu verwenden wir folgende Argumente:

syntax of vlookupsyntax of vlookupsyntax of vlookup
Syntax der VLOOKUP-Funktion

Definieren Sie einen Bereichsnamen, um eine absolute Referenz zu erstellen

Sehen Sie sich in Vlookup example.xlsx das Arbeitsblatt "Sales Amounts" an. Wir geben die Formel in B5 ein und verwenden dann die Funktion "AutoFill", um die Formel auf das Blatt zu kopieren. Das bedeutet, dass der Tabellenbereich in der Formel eine absolute Referenz sein muss. Eine gute Möglichkeit, dies zu tun, besteht darin, einen Namen für den Tabellenbereich zu definieren.

Definieren eines Bereichsnamens in Excel

  1. Gehen Sie vor der Eingabe der Formel zu source date für Quelldaten.
  2. Wählen Sie alle Zellen von A4 (Kopfzeile für die Spalte Bestellnummer) bis H203 aus. Sie können dies schnell tun, indem Sie auf A4 klicken und dann Strl-Schift-End (auf dem Mac drücken Sie Command-Schift-End)
  3. Klicken Sie in das Name Box über der Spalte A (im Namensfeld wird jetzt A4 angezeigt).
  4. Geben Sie date ein und drücken Sie Enter.
  5. Sie können jetzt die name date in der Formel anstelle von $A$4:$H$203 verwenden.
Name box displaying a range nameName box displaying a range nameName box displaying a range name
Das Namensfeld zeigt normalerweise die aktuelle Zellenadresse an. Klicken Sie hinein und geben Sie einen Namen ein, um einen Bereich zu definieren.

Definieren eines Bereichsnamens in Google Sheets

In Google Sheets ist die Definition eines Namens etwas anders.

  1. Klicken Sie auf die erste Spaltenüberschrift Ihrer Quelldaten, und drücken Sie dann Ctrl-Shift-Right Arrow (Command-Shift-Right Arrow auf Mac). Dadurch wird die Zeile der Spaltenüberschriften ausgewählt.
  2. Drücken Sie Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow auf dem Mac). Das wählt die tatsächlichen Daten aus.
  3. Klicken Sie auf das Data und wählen Sie Named and protected ranges.
  4. Geben Sie im Feld Name und geschützte Bereiche auf der rechten Seite Data ein, und klicken Sie dann auf Done.
Data menu in Google SheetsData menu in Google SheetsData menu in Google Sheets
Definieren eines Bereichsnamens in Google Sheets

Eingabe der Formel

Um die Formel einzugeben, wechseln Sie in "Sales Amounts" und klicken Sie in B5.

Geben Sie die Formel ein:

= VLOOKUP(A5,data,8,FALSE)

Drücken Sie Enter.

entering the vlookup functionentering the vlookup functionentering the vlookup function
Aufrufen der VLOOKUP-Funktion

Das Ergebnis sollte 40 sein. Um die Werte in der Spalte auszufüllen, klicken Sie ggf. wieder auf B5. Setzen Sie den Mauszeiger auf den AutoFill-Punkt in der rechten unteren Ecke der Zelle, damit der Mauszeiger zu einem Fadenkreuz wird.

mouse pointer on the autofill dotmouse pointer on the autofill dotmouse pointer on the autofill dot
Wenn Sie den Mauszeiger auf den Punkt in der rechten unteren Ecke einer Zelle platzieren, wird er zu einem AutoFill-Fadenkreuz

Doppelklicken Sie, um die Werte in der Spalte zu füllen.

worksheet with data after using autofillworksheet with data after using autofillworksheet with data after using autofill
Doppelklicken Sie auf das AutoFill-Fadenkreuz, um die Formel in die Spalte zu kopieren

Wenn Sie möchten, können Sie die VLOOKUP-Funktion in den nächsten Spalten ausführen, um andere Felder wie Nachname oder Status zu extrahieren.

Verwendung von MATCH

Die Funktion MATCH gibt den Wert der Daten nicht an Sie zurück. Sie geben den Wert an, nach dem Sie suchen, und die Funktion gibt die Position dieses Werts zurück. Es ist so, als würde man fragen, wo die Hauptstraße #135 ist, und man erhält die Antwort, dass es das vierte Gebäude in der Straße ist.

Syntax

Die Syntax der Funktion MATCH lautet:

=MATCH(lookup value, table range, [match type])

Die Argumente sind:

  • Lookup value. Die Zelle mit der eindeutigen Kennung.
  • Table range. Der Zellbereich, den Sie durchsuchen.
  • Match type. Optional. So legen Sie fest, wie nahe Sie eine Übereinstimmung haben möchten:

Nächster höchster Wert

-1

Die Werte müssen in absteigender Reihenfolge sein.

Target value

0

Werte können in beliebiger Reihenfolge sein.

Nächster niedrigster Wert

1

Default type. Die Werte müssen aufsteigend sein.

Wie bei der VLOOKUP-Funktion ist es wahrscheinlich einfacher, die MATCH-Funktion zu verwenden, wenn Sie einen Bereichsnamen anwenden. Gehen Sie zum Source Data, wählen Sie B4 (Spaltenüberschrift für Auftrag #) nach unten aus, klicken Sie in das Name Box über Spalte A und nennen Sie es order_number. Beachten Sie, dass die Werte aufsteigend sind.

name box with one column definedname box with one column definedname box with one column defined
Ein benannter Bereich kann nur eine Spalte, nur eine Zeile oder auch nur eine Zelle sein

Wechseln Sie zur Registerkarte Match des Arbeitsblatts. Geben Sie in B5 die MATCH-Funktion ein:

=MATCH(A5,order_number,1)

entering the match functionentering the match functionentering the match function
Aufruf der MATCH-Funktion

Wenn Sie keinen Bereichsnamen definiert haben, schreiben Sie die Funktion wie folgt:

=MATCH(A5,'Source Data'!A5:A203,0)

Auf jeden Fall können Sie sehen, dass sich diese Position an der 14. Stelle befindet (wodurch sie die 13.Ordnung ist).

completed match functioncompleted match functioncompleted match function
Ergebnis der MATCH-Funktion

Verwendung von INDEX

Die INDEX-Funktion ist das Gegenteil der MATCH-Funktion und ähnelt VLOOKUP. Sie teilen der Funktion mit, welche Zeile und Spalte der Daten Sie möchten, und sie geben den Wert der in der Zelle enthaltenen Daten an.

Syntax

Die Syntax der INDEX-Funktion lautet:

=INDEX(data range, row number, [column number])

Die Argumente sind:

  • Data range. Wie die beiden anderen Funktionen ist diese auch die Datentabelle.
  • Row number. Die Zeilennummer der Daten, die nicht unbedingt die Zeile des Arbeitsblatts ist. Wenn der Tabellenbereich in Zeile 10 des Blattes beginnt, ist dies Zeile 1.
  • Column number. Die Spaltennummer des Datenbereichs. Wenn der Bereich in Spalte E beginnt, ist dies Spalte 1.

In der Excel-Dokumentation erfahren Sie, dass column number optional ist, die Zeilennummer jedoch auch optional. Wenn der Tabellenbereich nur eine Zeile oder eine Spalte enthält, müssen Sie das andere Argument nicht verwenden.

Gehen Sie zum Indexblatt der Arbeitsmappe und klicken Sie C6. Wir möchten zunächst herausfinden, was in Zeile 9, Spalte 3 der Tabelle enthalten ist. In der Formel verwenden wir den zuvor erstellten Bereichsnamen.

Geben Sie die Formel ein:

=INDEX(data,A6,B6)

entering the index functionentering the index functionentering the index function
Eingabe der Indexfunktion

Es gibt den Nachnamen eines Kunden zurück: Strevell. Ändern Sie die Werte von A6 und B6. Das Ergebnis in C6 zeigt unterschiedliche Ergebnisse (beachten Sie, dass viele Zeilen dieselben Status- und Produktnamen haben).

Schlusswort

Die Fähigkeit eines Arbeitsblatts, ein anderes Arbeitsblatt anzusehen und Daten zu extrahieren, ist ein hervorragendes Werkzeug. Auf diese Weise können Sie über ein Blatt verfügen, das alle für viele Zwecke benötigten Daten enthält, und dann die für bestimmte Instanzen benötigten Informationen extrahieren.

Advertisement
Did you find this post useful?
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.