() translation by (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:
- Exportieren von WordPress-Daten nach Excel
- Analysieren und Abrufen von Daten aus Excel mithilfe einer PHP-Klasse
- Konvertieren Sie eine Excel-Tabelle in eine responsive HTML-Tabelle
- Konvertieren Sie Excel-Dateien in eine .NET-Datentabelle
- und vieles mehr
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.
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:
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
- Gehen Sie vor der Eingabe der Formel zu source date für Quelldaten.
- 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)
- Klicken Sie in das Name Box über der Spalte A (im Namensfeld wird jetzt A4 angezeigt).
- Geben Sie date ein und drücken Sie Enter.
- Sie können jetzt die name date in der Formel anstelle von $A$4:$H$203 verwenden.
Definieren eines Bereichsnamens in Google Sheets
In Google Sheets ist die Definition eines Namens etwas anders.
- 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.
- Drücken Sie Ctrl-Shift-Down Arrow (Command-Shift-Down Arrow auf dem Mac). Das wählt die tatsächlichen Daten aus.
- Klicken Sie auf das Data und wählen Sie Named and protected ranges.
- Geben Sie im Feld Name und geschützte Bereiche auf der rechten Seite Data ein, und klicken Sie dann auf Done.
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.
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.
Doppelklicken Sie, um die Werte in der Spalte zu füllen.
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.
Wechseln Sie zur Registerkarte Match des Arbeitsblatts. Geben Sie in B5 die MATCH-Funktion ein:
=MATCH(A5,order_number,1)
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).
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)
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.
