Durch die Kombination von ExcelVLOOKUP Funktion mit derSÄULE Mit dieser Funktion können Sie eine Suchformel erstellen, mit der Sie mehrere Werte aus einer einzelnen Zeile einer Datenbank oder Datentabelle zurückgeben können.
01 von 10Mehrere Werte mit Excel VLOOKUP zurückgeben
Wenn Sie die unten aufgeführten Schritte ausführen, wird die in der Abbildung oben dargestellte Suchformel erstellt, die mehrere Werte aus einem einzigen Datensatz zurückgibt.
Die Suchformel erfordert, dass die SÄULE Funktion ist in verschachtelt VLOOKUP. Beim Verschachteln einer Funktion muss die zweite Funktion als eines der Argumente für die erste Funktion eingegeben werden.
In diesem Tutorial wird der SÄULE Funktion wird als eingegeben Spaltenindex Nummer Argument für VLOOKUP. Im letzten Schritt des Lernprogramms müssen Sie die Suchformel in zusätzliche Spalten kopieren, um zusätzliche Werte für den ausgewählten Teil abzurufen.
Geben Sie die Lerndaten ein
Der erste Schritt in diesem Lernprogramm besteht darin, die Daten in ein Excel-Arbeitsblatt einzugeben. Um die Schritte des Tutorials zu befolgen, geben Sie die im Bild oben angezeigten Daten in die folgenden Zellen ein:
- Geben Sie den oberen Datenbereich in ein Zellen D1 zu G1.
- Geben Sie den zweiten Bereich ein Zellen D4 zu G10.
Die Suchkriterien und die in diesem Lernprogramm erstellte Suchformel werden eingegeben Reihe 2 des Arbeitsblatts.
Das Tutorial enthält nicht die Formatierung, die im Bild angezeigt wird. Dies hat jedoch keinen Einfluss auf die Funktionsweise der Suchformel. Informationen zu den oben gezeigten Formatierungsoptionen finden Sie in diesem grundlegenden Excel-Formatierungs-Lernprogramm.
03 von 10Erstellen eines benannten Bereichs für die Datentabelle
Ein benannter Bereich ist eine einfache Möglichkeit, auf einen Datenbereich in einer Formel zu verweisen. Anstatt die Zellreferenzen für die Daten einzugeben, können Sie einfach den Namen des Bereichs eingeben.
Ein zweiter Vorteil bei der Verwendung eines benannten Bereichs besteht darin, dass sich die Zellverweise für diesen Bereich nicht ändern, selbst wenn die Formel in andere Zellen des Arbeitsblatts kopiert wird. Bereichsnamen sind daher eine Alternative zur Verwendung absoluter Zellbezüge, um Fehler beim Kopieren von Formeln zu vermeiden.
Der Bereichsname enthält nicht die Überschriften oder Feldnamen für die Daten (Reihe 4) aber nur die Daten selbst.
- Markieren Zellen D5 zu G10 im Arbeitsblatt, um sie auszuwählen.
- Klicke auf das Namensfeld oben gelegen Spalte A.
- Art Tabelle in die Namensfeld.
- Drücken Sie die Eingeben Taste auf der Tastatur.
- Zellen D5 zu G10 habe jetzt den Bereichsnamen von Tabelle. Wir werden den Namen für die verwenden VLOOKUP Tabellen-Array Argument später im Tutorial.
Öffnen des VLOOKUP-Dialogfelds
Obwohl es möglich ist, unsere Nachschlagformel direkt in eine Zelle in einem Arbeitsblatt einzugeben, ist es für viele Menschen schwierig, die Syntax gerade zu halten - insbesondere für eine komplexe Formel wie die, die wir in diesem Lernprogramm verwenden.
Eine Alternative ist in diesem Fall die Verwendung der VLOOKUP Dialogbox. Fast alle Funktionen von Excel verfügen über ein Dialogfeld, in dem Sie die einzelnen Argumente der Funktion in einer separaten Zeile eingeben können.
Tutorial-Schritte
- Klicke auf Zelle E2 des Arbeitsblatts - der Ort, an dem die Ergebnisse der zweidimensionalen Suchformel angezeigt werden.
- Klicke auf das Registerkarte "Formeln" des Band.
- Klicke auf das Nachschlagen & Nachschlagen Option in der Band um das Funktions-Dropdown zu öffnen.
- Klicke auf VLOOKUP in der Liste, um das Dialogfeld der Funktion zu öffnen.
Eingabe des Nachschlagewertarguments
Normalerweise die Sieh nach oben Wert stimmt mit einem Datenfeld in der ersten Spalte der Datentabelle überein. In unserem Beispiel das Sieh nach oben Wert bezieht sich auf den Namen des Hardwareteils, über den wir Informationen suchen möchten. Die zulässigen Datentypen für die Sieh nach oben Wert sind Textdaten, logische Werte, Zahlen und Zellbezüge.
Absolute Zellreferenzen
Wenn Formeln in Excel kopiert werden, ändern sich die Zellreferenzen normalerweise entsprechend ihrer neuen Position. Wenn das passiert, D2die Zellreferenz für die Sieh nach oben Wert, ändert sich, wenn die Formel kopiert wird, wodurch Fehler in erstellt werden Zellen F2 und G2.
Absolute Zellbezüge ändern sich nicht, wenn Formeln kopiert werden.
Um die Fehler zu vermeiden, konvertieren wir die Zellenreferenz D2 in eine absolute Zellreferenz. Absolute Zellbezüge werden durch Drücken von erstellt F4 Taste auf der Tastatur. Dadurch werden Dollarzeichen um die Zellreferenz hinzugefügt, wie z $ D $ 2.
- Klicke auf das Lookup-Wert Zeile im Dialogfeld.
- Klicke auf Zelle D2 um diese Zellenreferenz in die Lookup-Wert Zeile - Dies ist die Zelle, in die der Teilname eingegeben wird, über den wir Informationen suchen
- Drücken Sie, ohne die Einfügemarke zu verschieben F4 Taste auf der Tastatur zum Konvertieren D2 in die absolute Zellreferenz $ D $ 2.
- Lassen Sie die VLOOKUP Funktionsdialogfeld für den nächsten Schritt des Lernprogramms geöffnet.
Eingabe des Tabellen-Array-Arguments
Das Tabellenarray ist die Tabelle der Daten, nach denen die Suchformel die gewünschten Informationen sucht.Das Tabellenarray muss mindestens zwei Datenspalten enthalten.
Die erste Spalte enthält das Nachschlagewertargument (vorhergehender Schritt im Lernprogramm), während die zweite Spalte von der Nachschlagformel durchsucht wird, um die von uns angegebenen Informationen zu finden.
Das Tabellenarrayargument muss entweder als Bereich eingegeben werden, der die Zellreferenzen für die Datentabelle enthält, oder als Bereichsname.
- Klicke auf das table_array Zeile im Dialogfeld.
- Art Tabelle um den Bereichsnamen für dieses Argument einzugeben.
- Lassen Sie die VLOOKUP Funktionsdialogfeld für den nächsten Schritt des Lernprogramms geöffnet.
Verschachteln der COLUMN-Funktion
Normalerweise VLOOKUP gibt nur Daten aus einer Spalte einer Datentabelle zurück, und diese Spalte wird von der gesetzt Spaltenindexnummer Streit.
In diesem Beispiel haben wir jedoch drei Spalten, aus denen wir Daten zurückgeben möchten, sodass wir eine Möglichkeit zum einfachen Ändern der Daten benötigen Spaltenindexnummer ohne unsere Lookup-Formel zu bearbeiten - das ist, wo die SÄULE Funktion kommt herein.
Durch Eingabe als Spaltenindexnummer Das Argument ändert sich, wenn die Suchformel kopiert wird Zelle D2 zu Zellen E2 und F2 später im Tutorial.
Verschachtelungsfunktionen
Das SÄULE Funktion fungiert daher als VLOOKUP's Spaltenindexnummer Streit; Dies wird durch Verschachteln des erreicht SÄULE Funktion innerhalb von VLOOKUP in dem Col_index_num Zeile des Dialogfelds.
Manuelle Eingabe der COLUMN-Funktion
Beim Verschachteln von Funktionen können Sie in Excel das Dialogfeld der zweiten Funktion nicht öffnen, um deren Argumente einzugeben. Das SÄULE Funktion muss daher manuell in die eingegeben werden Col_index_num Linie. Das SÄULE Funktion hat nur ein Argument - das Referenz Argument, das eine Zellreferenz ist.
Das Referenzargument der COLUMN-Funktion auswählen
Das SÄULE Die Aufgabe der Funktion ist es, die Nummer der als Referenz Streit. Mit anderen Worten, es wandelt den Spaltenbuchstaben in eine Zahl mit um Spalte A die erste spalte sein, Spalte B die zweite und so weiter.
Seit dem ersten Datenfeld möchten wir den Preis des Artikels zurückgeben, der sich in Spalte 2 der Datentabelle befindet. Wir können die Zellenreferenz für jede Zelle in auswählen Spalte B als die Referenz Argument, um die Nummer zu erhalten 2 für die Col_index_num Streit.
- In dem VLOOKUP Klicken Sie im Funktionsdialogfeld auf Col_index_num Linie.
- Geben Sie den Funktionsnamen ein Säule gefolgt von einem runde Klammer öffnen.
- Klicke auf Zelle B1 Geben Sie im Arbeitsblatt diese Zellreferenz als ein Referenz Streit.
- Tippe A runde Klammer schließen zu vervollständigen SÄULE Funktion
- Lassen Sie die VLOOKUP Funktionsdialogfeld für den nächsten Schritt des Lernprogramms geöffnet.
Eingabe des VLOOKUP-Bereichsucharguments
VLOOKUP's Range_lookup Argument ist ein logischer Wert (WAHR oder FALSCH) zeigt an, ob Sie möchten VLOOKUP eine genaue oder ungefähre Übereinstimmung mit der Lookup-Wert.
- Ob WAHR oder wenn dieses Argument weggelassen wird: VLOOKUP gibt entweder eine genaue Übereinstimmung mit der zurück Lookup-WertOder, wenn keine exakte Übereinstimmung gefunden wird, VLOOKUP gibt den nächstgrößeren Wert zurück. Für die Formel dazu die Daten in der ersten Spalte von Table_array muss aufsteigend sortiert sein.
- Ob FALSCH: VLOOKUP verwendet nur eine exakte Übereinstimmung mit der Lookup-Wert. Wenn zwei oder mehr Werte in der ersten Spalte von enthalten sind Table_array Wenn der Suchwert übereinstimmt, wird der erste gefundene Wert verwendet. Wenn keine exakte Übereinstimmung gefunden wird, a #N / A Fehler wird zurückgegeben.
Da wir in diesem Lernprogramm nach bestimmten Informationen zu einem bestimmten Hardwareelement suchen, werden wir festlegen Range_lookup gleich Falsch.
- Klicke auf das Range_lookup Zeile im Dialogfeld.
- Schreibe das Wort Falsch In dieser Zeile zeigen wir an, dass wir wollen VLOOKUP um eine exakte Übereinstimmung für die gesuchten Daten zu erhalten.
- Klicken OK um die Suchformel abzuschließen und das Dialogfeld zu schließen.
- Da wir die Suchkriterien noch nicht eingegeben haben Zelle D2 ein # N / A Fehler wird anwesend sein in Zelle E2.
- Dieser Fehler wird korrigiert, wenn wir im letzten Schritt des Lernprogramms die Suchkriterien hinzufügen.
Kopieren der Suchformel
Die Suchformel soll Daten gleichzeitig aus mehreren Spalten der Datentabelle abrufen. Dazu muss sich die Suchformel in allen Feldern befinden, aus denen Informationen abgerufen werden sollen.
In diesem Tutorial möchten wir, dass Daten von abgerufen werden Spalten 2, 3, und 4 der Datentabelle - das ist der Preis, die Teilenummer und der Name des Lieferanten, wenn wir einen Teilnamen als eingeben Lookup-Wert.
Da die Daten im Arbeitsblatt in einem regelmäßigen Muster angeordnet sind, können wir die Suchformel in kopieren Zelle E2 zu Zellen F2 und G2.
Wenn die Formel kopiert wird, aktualisiert Excel die relative Zellenreferenz in SÄULE Funktion (Zelle B1), um den neuen Standort der Formel wiederzugeben.
Außerdem ändert Excel die absolute Zellreferenz nicht $ D $ 2 und der benannte Bereich Tabelle wie die Formel kopiert wird.
Es gibt mehr als eine Möglichkeit, Daten in Excel zu kopieren, aber die einfachste Möglichkeit ist die Verwendung von Griff füllen.
- Klicke auf Zelle E2, wo sich die Suchformel befindet, um sie zur aktiven Zelle zu machen.
- Stelle das Mauszeiger über dem schwarzes Quadrat In der rechten unteren Ecke ändert sich der Zeiger in ein Pluszeichen, um den Füllpunkt zu kennzeichnen.
- Klicken Sie mit der linken Maustaste und Ziehen Sie den Füllgriff über zu Zelle G2.
- Veröffentlichung die Maustaste und Zelle F3 sollte die zweidimensionale Suchformel enthalten.
- Wenn richtig gemacht, Zellen F2 und G2 sollte jetzt auch die enthalten #N / A Fehler, der in vorhanden ist Zelle E2.
Suchkriterien eingeben
Nachdem die Suchformel in die erforderlichen Zellen kopiert wurde, kann sie zum Abrufen von Informationen aus der Datentabelle verwendet werden. Geben Sie dazu die Name des Artikels Sie möchten in die Lookup-Wert Zelle (D2) und drücken Sie die Eingeben Taste auf der Tastatur.
Sobald dies geschehen ist, sollte jede Zelle, die die Suchformel enthält, andere Daten über das gesuchte Hardwareelement enthalten.
- Klicke auf Zelle D2 im Arbeitsblatt.
- Art Widget in Zelle D2 und drücken Sie die Eingeben Taste auf der Tastatur.
- Die folgenden Informationen sollten in angezeigt werden Zellen E2 zu G2.
- E2: 14,76 $ - der Preis eines Widgets
- F2: PN-98769 - Die Teilenummer für ein Widget
- G2: Widgets Inc. - der Name des Lieferanten für Widgets
- Testen Sie das VLOOKUP Array-Formel weiter, indem Sie den Namen anderer Teile in eingeben Zelle D2 und Beobachtung der Ergebnisse in Zellen E2 zu G2
