Share
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.
Wussten Sie schon, 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-Skripte und Plugins auf Envato MarketWenn 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 enthaltene ZIP-Datei herunter, die eine aufgerufene Arbeitsmappe enthält vlookup example.xlsx.
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 das in der Quelldaten, Der Bezeichner muss in der ersten Spalte der Tabelle stehen.
Ein eindeutiger Bezeichner sollte wie eine Seriennummer aussehen, wobei keine zwei in derselben Tabelle identisch sind.Die Syntax der VLOOKUP-Funktion lautet:
= VLOOKUP (Nachschlagewert, Tabellenbereich, Spaltennummer, [true / false])
Das bedeuten diese Argumente:
Wir möchten Umsatzbeträge aus der Tabelle in der obigen Abbildung ermitteln. Daher verwenden wir folgende Argumente:
Syntax der VLOOKUP-FunktionIm Vlookup example.xlsx, schaue auf die Umsatzbeträge Arbeitsblatt. 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.
Das Namensfeld zeigt normalerweise die aktuelle Zellenadresse an. Klicken Sie hinein und geben Sie einen Namen ein, um einen Bereich zu definieren.In Google Sheets ist die Definition eines Namens etwas anders.
Definieren eines Bereichsnamens in Google SheetsUm die Formel einzugeben, gehen Sie zu Umsatzbeträge Arbeitsblatt und klicken Sie in B5.
Geben Sie die Formel ein:
= VLOOKUP (A5, Daten, 8, FALSCH)
Drücken Sie Eingeben.
Aufrufen der VLOOKUP-FunktionDas Ergebnis sollte 40 sein. Um die Werte in der Spalte auszufüllen, klicken Sie ggf. wieder auf B5. Setzen Sie den Mauszeiger auf die AutoFill Klicken Sie in der unteren rechten Ecke der Zelle auf einen Punkt, so dass der Mauszeiger zu einem Fadenkreuz wird.
Wenn Sie den Mauszeiger auf den Punkt in der rechten unteren Ecke einer Zelle platzieren, wird er zu einem AutoFill-FadenkreuzDoppelklicken Sie, um die Werte in der Spalte zu füllen.
Doppelklicken Sie auf das AutoFill-Fadenkreuz, um die Formel in die Spalte zu kopierenWenn Sie möchten, können Sie die Funktion VLOOKUP in den nächsten Spalten ausführen, um andere Felder wie Nachname oder Status zu extrahieren.
Die Funktion MATCH gibt den Datenwert 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, als würde man fragen, wo die Hauptstraße # 135 ist, und die Antwort bekommen, dass es die 4 istth die Straße hinunter bauen.
Die Syntax der MATCH-Funktion lautet:
= MATCH (Nachschlagewert, Tabellenbereich, [Übereinstimmungstyp])
Die Argumente sind:
| Nächster höchster Wert | -1 | Die Werte müssen in absteigender Reihenfolge sein. |
| Zielwert | 0 | Werte können in beliebiger Reihenfolge sein. |
| Nächster niedrigster Wert | 1 | Standardtyp 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. Gehe zum Quelldaten wählen Sie aus B4 (Spaltenüberschrift für Auftrag #) nach unten, klicken Sie in das Namensfeld über der Spalte A, und nennen Sie es Bestellnummer. Beachten Sie, dass die Werte aufsteigend sind.
Ein benannter Bereich kann nur eine Spalte, nur eine Zeile oder auch nur eine Zelle seinGehe zum Spiel Registerkarte des Arbeitsblatts. Im B5, Geben Sie die MATCH-Funktion ein:
= MATCH (A5, Bestellnummer, 1)
Aufruf der MATCH-FunktionWenn Sie keinen Bereichsnamen definiert haben, schreiben Sie die Funktion als:
= MATCH (A5, 'Quelldaten'! A5: A203,0)
So oder so kann man sehen, dass sich diese Position an der 14. Stelle befindetth Auftrag).
Ergebnis der MATCH-FunktionDie INDEX-Funktion ist das Gegenteil der MATCH-Funktion und ähnelt VLOOKUP. Sie teilen der Funktion mit, welche Zeile und Spalte der Daten Sie wünschen, und sie zeigt Ihnen den Wert des Inhalts der Zelle an.
Die Syntax der INDEX-Funktion lautet:
= INDEX (Datenbereich, Zeilennummer, [Spaltennummer])
Die Argumente sind:
In der Excel-Dokumentation erfahren Sie, dass die Spaltennummer Argument ist optional, aber die Zeilennummer ist auch optional. Wenn der Tabellenbereich nur eine Zeile oder eine Spalte enthält, müssen Sie das andere Argument nicht verwenden.
Gehe zum Index Blatt der Arbeitsmappe und klicken Sie in C6. Zuerst möchten wir herausfinden, was in Zeile 9, Spalte 3 der Tabelle enthalten ist. In der Formel verwenden wir den Bereichsnamen, den wir zuvor erstellt haben.
Geben Sie die Formel ein:
= INDEX (Daten, A6, B6)
Eingabe der IndexfunktionEs gibt den Nachnamen eines Kunden zurück: Strevell. Ändern Sie die Werte von A6 und B6, und das Ergebnis in C6 zeigt unterschiedliche Ergebnisse (beachten Sie, dass viele Zeilen dieselben Status- und Produktnamen haben)..
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.
Accentsconagua