Indem Sie die VLOOKUP-Funktion von Excel mit der COLUMN-Funktion kombinieren, können Sie eine Suchformel erstellen, die mehrere Werte aus einer einzelnen Zeile einer Datenbank oder Datentabelle zurückgibt. Erfahren Sie, wie Sie eine Suchformel erstellen, die mehrere Werte aus einem einzelnen Datensatz zurückgibt.
Anweisungen in diesem Artikel gelten für Excel 2019, 2016, 2013, 2010; und Excel für Microsoft 365.
Bottom Line
Die Nachschlageformel erfordert, dass die COLUMN-Funktion innerhalb von SVERWEIS verschachtelt ist. Beim Verschachteln einer Funktion muss die zweite Funktion als eines der Argumente für die erste Funktion eingegeben werden.
Tutorialdaten eingeben
In diesem Tutorial wird die COLUMN-Funktion als Sp altenindexnummer-Argument für SVERWEIS eingegeben. Der letzte Schritt im Tutorial besteht darin, die Nachschlageformel in zusätzliche Sp alten zu kopieren, um zusätzliche Werte für den ausgewählten Teil abzurufen.
Der erste Schritt in diesem Tutorial besteht darin, die Daten in ein Excel-Arbeitsblatt einzugeben. Um den Schritten in diesem Tutorial zu folgen, geben Sie die im Bild unten gezeigten Daten in die folgenden Zellen ein:
- Geben Sie den oberen Datenbereich in die Zellen D1 bis G1 ein.
- Geben Sie den zweiten Bereich in die Zellen D4 bis G10 ein.
Die in diesem Tutorial erstellten Suchkriterien und Suchformeln werden in Zeile 2 des Arbeitsblatts eingetragen.
Dieses Tutorial enthält nicht die im Bild gezeigte grundlegende Excel-Formatierung, aber dies hat keinen Einfluss darauf, wie die Nachschlageformel funktioniert.
Erstellen Sie einen benannten Bereich 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 Daten einzugeben, geben Sie den Namen des Bereichs ein.
Ein zweiter Vorteil bei der Verwendung eines benannten Bereichs besteht darin, dass sich die Zellreferenzen für diesen Bereich nie ändern, selbst wenn die Formel in andere Zellen im Arbeitsblatt kopiert wird. Bereichsnamen sind eine Alternative zu absoluten Zellbezügen, um Fehler beim Kopieren von Formeln zu vermeiden.
Der Bereichsname enthält nicht die Überschriften oder Feldnamen für die Daten (wie in Zeile 4 gezeigt), sondern nur die Daten.
-
Markieren Sie Zellen D5 bis G10 im Arbeitsblatt.
Image -
Platzieren Sie den Cursor im Namensfeld über Sp alte A, geben Sie Table ein und drücken Sie dann Enter. Die Zellen D5 bis G10 haben den Bereichsnamen Table.
Image - Der Bereichsname für das SVERWEIS-Tabellen-Array-Argument wird später in diesem Tutorial verwendet.
SVERWEIS-Dialogfeld öffnen
Obwohl es möglich ist, die Nachschlageformel direkt in eine Zelle in einem Arbeitsblatt einzugeben, finden es viele Leute schwierig, die Syntax klar zu h alten - insbesondere bei einer komplexen Formel wie der in diesem Tutorial verwendeten.
Verwenden Sie alternativ das Dialogfeld VLOOKUP-Funktionsargumente. Fast alle Excel-Funktionen verfügen über ein Dialogfeld, in dem jedes Argument der Funktion in einer separaten Zeile eingegeben wird.
-
Wählen Sie Zelle E2 des Arbeitsblatts. An dieser Stelle werden die Ergebnisse der zweidimensionalen Suchformel angezeigt.
Image -
Wechseln Sie in der Multifunktionsleiste zur Registerkarte Formeln und wählen Sie Nachschlagen und Verweisen.
Image -
Wählen Sie SVERWEIS, um das Dialogfeld Funktionsargumente zu öffnen.
Image - Im Dialogfeld Funktionsargumente werden die Parameter der SVERWEIS-Funktion eingegeben.
Geben Sie das Suchwert-Argument ein
Normalerweise stimmt der Suchwert mit einem Datenfeld in der ersten Sp alte der Datentabelle überein. In diesem Beispiel bezieht sich der Suchwert auf den Namen des Teils, für den Sie Informationen suchen möchten. Die zulässigen Datentypen für den Nachschlagewert sind Textdaten, logische Werte, Zahlen und Zellbezüge.
Absolute Zellbezüge
Wenn Formeln in Excel kopiert werden, ändern sich die Zellreferenzen, um die neue Position widerzuspiegeln. In diesem Fall ändert sich D2, der Zellbezug für den Nachschlagewert, und erzeugt Fehler in den Zellen F2 und G2.
Absolute Zellbezüge ändern sich beim Kopieren von Formeln nicht.
Um die Fehler zu vermeiden, wandeln Sie den Zellbezug D2 in einen absoluten Zellbezug um. Um einen absoluten Zellbezug zu erstellen, drücken Sie die Taste F4. Dadurch werden Dollarzeichen um den Zellbezug herum hinzugefügt, z. B. $D$2.
-
Platzieren Sie im Dialogfeld Funktionsargumente den Cursor im Textfeld lookup_value. Wählen Sie dann im Arbeitsblatt cell D2 aus, um diesen Zellverweis zu lookup_value hinzuzufügen. In Zelle D2 wird der Teilename eingetragen.
Image -
Drücken Sie, ohne die Einfügemarke zu verschieben, die Taste F4, um D2 in den absoluten Zellbezug $D$2 umzuwandeln.
Image - Lassen Sie das SVERWEIS-Funktionsdialogfeld für den nächsten Schritt im Tutorial geöffnet.
Geben Sie das Tabellen-Array-Argument ein
Ein Tabellenarray ist die Datentabelle, die die Suchformel durchsucht, um die gewünschten Informationen zu finden. Das Tabellenarray muss mindestens zwei Datensp alten enth alten.
Die erste Sp alte enthält das Suchwertargument (das im vorherigen Abschnitt eingerichtet wurde), während die zweite Sp alte nach der Suchformel durchsucht wird, um die von Ihnen angegebenen Informationen zu finden.
Das Tabellen-Array-Argument muss entweder als Bereich mit den Zellreferenzen für die Datentabelle oder als Bereichsname eingegeben werden.
Um die Datentabelle zur SVERWEIS-Funktion hinzuzufügen, platzieren Sie den Cursor im Textfeld table_array im Dialogfeld und geben Sie Table einum den Bereichsnamen für dieses Argument einzugeben.
Sp altenfunktion verschachteln
Normalerweise gibt SVERWEIS nur Daten aus einer Sp alte einer Datentabelle zurück. Diese Sp alte wird durch das Argument der Sp altenindexnummer festgelegt. In diesem Beispiel gibt es jedoch drei Sp alten, und die Sp altenindexnummer muss geändert werden, ohne die Nachschlageformel zu bearbeiten. Verschachteln Sie dazu die COLUMN-Funktion innerhalb der SVERWEIS-Funktion als Col_index_num-Argument.
Beim Verschachteln von Funktionen öffnet Excel nicht das Dialogfeld der zweiten Funktion, um ihre Argumente einzugeben. Die COLUMN-Funktion muss manuell eingegeben werden. Die COLUMN-Funktion hat nur ein Argument, das Reference-Argument, das eine Zellreferenz ist.
Die COLUMN-Funktion gibt die Nummer der Sp alte zurück, die als Referenzargument angegeben ist. Es wandelt den Sp altenbuchstaben in eine Zahl um.
Um den Preis eines Artikels zu ermitteln, verwenden Sie die Daten in Sp alte 2 der Datentabelle. In diesem Beispiel wird Sp alte B als Referenz verwendet, um 2 in das Argument Col_index_num einzufügen.
-
Platzieren Sie im Dialogfeld Funktionsargumente den Cursor im Textfeld Col_index_num und geben Sie COLUMN(. (Achten Sie darauf, die offene runde Klammer einzufügen.)
Image -
Wählen Sie im Arbeitsblatt Zelle B1 aus, um diese Zellreferenz als Referenzargument einzugeben.
Image - Geben Sie eine schließende runde Klammer ein, um die COLUMN-Funktion abzuschließen.
Geben Sie das Suchargument für den SVERWEIS-Bereich ein
Das Range_lookup-Argument von VLOOKUP ist ein logischer Wert (TRUE oder FALSE), der angibt, ob SVERWEIS eine exakte oder ungefähre Übereinstimmung mit Lookup_value finden soll.
- TRUE oder Omitted: SVERWEIS gibt eine enge Übereinstimmung mit dem Lookup_value zurück. Wenn keine exakte Übereinstimmung gefunden wird, gibt SVERWEIS den nächstgrößten Wert zurück. Die Daten in der ersten Sp alte von Table_array müssen in aufsteigender Reihenfolge sortiert werden.
- FALSE: SVERWEIS verwendet eine exakte Übereinstimmung mit dem Lookup_value. Wenn es zwei oder mehr Werte in der ersten Sp alte von Table_array gibt, die mit dem Suchwert übereinstimmen, wird der erste gefundene Wert verwendet. Wenn keine exakte Übereinstimmung gefunden wird, wird ein NV-Fehler zurückgegeben.
In diesem Tutorial werden bestimmte Informationen zu einem bestimmten Hardwareelement nachgeschlagen, daher ist Range_lookup auf FALSE gesetzt.
Platzieren Sie im Dialogfeld Funktionsargumente den Cursor im Textfeld Range_lookup und geben Sie False ein, um VLOOKUP anzuweisen, eine exakte Übereinstimmung für die Daten zurückzugeben.
Wählen Sie OK, um die Suchformel abzuschließen und das Dialogfeld zu schließen. Zelle E2 enthält einen NV-Fehler, da die Suchkriterien nicht in Zelle D2 eingegeben wurden. Dieser Fehler ist vorübergehend. Es wird korrigiert, wenn die Suchkriterien im letzten Schritt dieses Tutorials hinzugefügt werden.
Nachschlageformel kopieren und Kriterien eingeben
Die Suchformel ruft Daten aus mehreren Sp alten der Datentabelle gleichzeitig ab. Dazu muss sich die Nachschlageformel in allen Feldern befinden, aus denen Sie Informationen abrufen möchten.
Um Daten aus den Sp alten 2, 3 und 4 der Datentabelle abzurufen (der Preis, die Teilenummer und der Name des Lieferanten), geben Sie einen Teil des Namens als Lookup_value ein.
Da die Daten im Arbeitsblatt in einem regelmäßigen Muster angeordnet sind, kopieren Sie die Nachschlageformel in Zelle E2 in Zellen F2 und G2 Während die Formel kopiert wird, aktualisiert Excel den relativen Zellbezug in der COLUMN-Funktion (Zelle B1), um die neue Position der Formel widerzuspiegeln. Excel ändert den absoluten Zellbezug (z. B. $D$2) und den benannten Bereich (Tabelle) nicht, wenn die Formel kopiert wird.
Es gibt mehr als eine Möglichkeit, Daten in Excel zu kopieren, aber die einfachste Möglichkeit ist die Verwendung des Ausfüllkästchens.
-
Wählen Sie Zelle E2, in der sich die Nachschlageformel befindet, um sie zur aktiven Zelle zu machen.
Image -
Ziehen Sie das Ausfüllkästchen auf Zelle G2. Die Zellen F2 und G2 zeigen den NV-Fehler an, der in Zelle E2 vorhanden ist.
Image -
Um die Nachschlageformeln zum Abrufen von Informationen aus der Datentabelle zu verwenden, wählen Sie im Arbeitsblatt Zelle D2, geben Sie Widget ein und drücken Sie Enter.
Image Die folgenden Informationen werden in den Zellen E2 bis G2 angezeigt.
- E2: 14,76 $ - der Preis eines Widgets
- F2: PN-98769 - die Teilenummer für ein Widget
- G2: Widgets Inc. - der Name des Anbieters für Widgets
-
Um die SVERWEIS-Matrixformel zu testen, geben Sie die Namen anderer Teile in Zelle D2 ein und beobachten Sie die Ergebnisse in den Zellen E2 bis G2.
Image - Jede Zelle, die die Suchformel enthält, enthält unterschiedliche Daten über das gesuchte Hardwareelement.
Die SVERWEIS-Funktion mit verschachtelten Funktionen wie COLUMN bietet eine leistungsstarke Methode zum Nachschlagen von Daten in einer Tabelle, wobei andere Daten als Nachschlagereferenz verwendet werden.
