Was man wissen sollte
- Erstellen Sie zuerst eine INDEX-Funktion und starten Sie dann die verschachtelte MATCH-Funktion, indem Sie das Lookup_value-Argument eingeben.
- Fügen Sie als Nächstes das Argument Lookup_array hinzu, gefolgt vom Argument Match_type, und geben Sie dann den Sp altenbereich an.
- Wandle die verschachtelte Funktion dann in eine Matrixformel um, indem du Strg+ Shift+ Enter drückst. Fügen Sie abschließend die Suchbegriffe zum Arbeitsblatt hinzu.
In diesem Artikel wird erläutert, wie Sie eine Suchformel erstellen, die mehrere Kriterien in Excel verwendet, um mithilfe einer Matrixformel Informationen in einer Datenbank oder Datentabelle zu finden. Die Array-Formel beinh altet das Verschachteln der MATCH-Funktion innerhalb der INDEX-Funktion. Die Informationen umfassen Excel für Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 und Excel für Mac.
Folgen Sie dem Tutorial
Um den Schritten in diesem Tutorial zu folgen, geben Sie die Beispieldaten in die folgenden Zellen ein, wie im Bild unten gezeigt. Die Zeilen 3 und 4 bleiben leer, um Platz für die Matrixformel zu schaffen, die in diesem Lernprogramm erstellt wurde. (Beachten Sie, dass dieses Tutorial nicht die Formatierung enthält, die im Bild zu sehen ist.)
- Geben Sie den oberen Datenbereich in die Zellen D1 bis F2 ein.
- Geben Sie den zweiten Bereich in die Zellen D5 bis F11 ein.
Erstellen Sie eine INDEX-Funktion in Excel
Die INDEX-Funktion ist eine der wenigen Funktionen in Excel, die mehrere Formen hat. Die Funktion hat ein Array-Formular und ein Referenz-Formular. Das Array-Formular gibt die Daten aus einer Datenbank oder Datentabelle zurück. Das Referenzformular gibt die Zellreferenz oder Position der Daten in der Tabelle an.
In diesem Tutorial wird das Array-Formular verwendet, um den Namen des Lieferanten für Titan-Widgets zu finden, und nicht die Zellreferenz zu diesem Lieferanten in der Datenbank.
Folgen Sie diesen Schritten, um die INDEX-Funktion zu erstellen:
- Zelle F3 auswählen, um sie zur aktiven Zelle zu machen. In diese Zelle wird die verschachtelte Funktion eingegeben.
-
Gehe zu Formeln.
Image - Wählen Sie Lookup & Reference, um die Dropdown-Liste der Funktionen zu öffnen.
- Wählen Sie INDEX, um das Dialogfeld Argumente auswählen zu öffnen.
- Choose array, row_num, column_num.
- Wählen Sie OK, um das Dialogfeld Funktionsargumente zu öffnen. In Excel für Mac wird der Formelgenerator geöffnet.
- Setzen Sie den Cursor in das Textfeld Array.
-
Markieren Sie die Zellen D6 bis F11 im Arbeitsblatt, um den Bereich in das Dialogfeld einzugeben.
Lassen Sie das Dialogfeld Funktionsargumente geöffnet. Die Formel ist noch nicht fertig. Sie werden die Formel in den Anweisungen unten vervollständigen.
Image
Verschachtelte MATCH-Funktion starten
Wenn eine Funktion in einer anderen verschachtelt wird, ist es nicht möglich, den Formelgenerator der zweiten oder verschachtelten Funktion zu öffnen, um die erforderlichen Argumente einzugeben. Die verschachtelte Funktion muss als eines der Argumente der ersten Funktion eingegeben werden.
Bei der manuellen Eingabe von Funktionen werden die Argumente der Funktion durch ein Komma voneinander getrennt.
Der erste Schritt zur Eingabe der verschachtelten MATCH-Funktion ist die Eingabe des Lookup_value-Arguments. Der Lookup_value ist der Ort oder die Zellreferenz für den Suchbegriff, der in der Datenbank abgeglichen werden soll.
Der Lookup_value akzeptiert nur ein Suchkriterium oder einen Begriff. Um nach mehreren Kriterien zu suchen, erweitern Sie den Lookup_value, indem Sie zwei oder mehr Zellreferenzen mit dem kaufmännischen Und-Symbol (&) verketten oder verbinden.
- Platzieren Sie im Dialogfeld Funktionsargumente den Cursor im Textfeld Row_num.
- Enter MATCH(.
- Zelle D3 auswählen, um diese Zellreferenz in das Dialogfeld einzugeben.
- Geben Sie & (das kaufmännische Und) nach der Zellreferenz D3 ein, um eine zweite Zellreferenz hinzuzufügen.
- Zelle E3 auswählen, um den zweiten Zellbezug einzugeben.
-
Enter , (ein Komma) nach dem Zellbezug E3, um die Eingabe des Lookup_value-Arguments der MATCH-Funktion abzuschließen.
Image Im letzten Schritt des Tutorials werden die Lookup_values in die Zellen D3 und E3 des Arbeitsblatts eingetragen.
Schließen Sie die verschachtelte MATCH-Funktion ab
Dieser Schritt behandelt das Hinzufügen des Lookup_array-Arguments für die verschachtelte MATCH-Funktion. Das Lookup_array ist der Zellbereich, den die MATCH-Funktion durchsucht, um das Lookup_value-Argument zu finden, das im vorherigen Schritt des Tutorials hinzugefügt wurde.
Da im Lookup_array-Argument zwei Suchfelder identifiziert wurden, muss dasselbe für das Lookup_array getan werden. Die MATCH-Funktion durchsucht nur ein Array nach jedem angegebenen Begriff. Um mehrere Arrays einzugeben, verwenden Sie das kaufmännische Und, um die Arrays miteinander zu verketten.
- Platzieren Sie den Cursor am Ende der Daten im Textfeld Row_num. Der Cursor erscheint nach dem Komma am Ende des aktuellen Eintrags.
- Markieren Sie die Zellen D6 bis D11 im Arbeitsblatt, um den Bereich einzugeben. Dieser Bereich ist das erste Array, das die Funktion durchsucht.
- Geben Sie & (ein kaufmännisches Und) nach den Zellreferenzen D6:D11 ein. Dieses Symbol bewirkt, dass die Funktion zwei Arrays durchsucht.
- Markieren Sie die Zellen E6 bis E11 im Arbeitsblatt, um den Bereich einzugeben. Dieser Bereich ist das zweite Array, das die Funktion durchsucht.
-
Geben Sie , (ein Komma) nach dem Zellbezug E3 ein, um die Eingabe des Lookup_array-Arguments der MATCH-Funktion abzuschließen.
Image - Lassen Sie das Dialogfeld für den nächsten Schritt im Tutorial geöffnet.
Argument vom Typ MATCH hinzufügen
Das dritte und letzte Argument der MATCH-Funktion ist das Match_type-Argument. Dieses Argument teilt Excel mit, wie der Lookup_value mit Werten im Lookup_array abgeglichen werden soll. Die verfügbaren Optionen sind 1, 0 oder -1.
Dieses Argument ist optional. Wenn es weggelassen wird, verwendet die Funktion den Standardwert 1.
- Wenn Match_type=1 oder weggelassen wird, findet MATCH den größten Wert, der kleiner oder gleich dem Lookup_value ist. Die Lookup_array-Daten müssen in aufsteigender Reihenfolge sortiert werden.
- Wenn Match_type=0, findet MATCH den ersten Wert, der gleich dem Lookup_value ist. Die Lookup_array-Daten können in beliebiger Reihenfolge sortiert werden.
- Wenn Match_type=-1, findet MATCH den kleinsten Wert, der größer oder gleich dem Lookup_value ist. Die Lookup_array-Daten müssen in absteigender Reihenfolge sortiert werden.
Geben Sie diese Schritte nach dem im vorherigen Schritt eingegebenen Komma in der Zeile Row_num in der INDEX-Funktion ein:
- Geben Sie 0 (eine Null) nach dem Komma in das Textfeld Row_num ein. Diese Zahl veranlasst die verschachtelte Funktion, exakte Übereinstimmungen mit den in den Zellen D3 und E3 eingegebenen Begriffen zurückzugeben.
-
Geben Sie ) (eine schließende runde Klammer) ein, um die MATCH-Funktion abzuschließen.
Image - Lassen Sie das Dialogfeld für den nächsten Schritt im Tutorial geöffnet.
Beenden Sie die INDEX-Funktion
Die MATCH-Funktion ist fertig. Es ist an der Zeit, zum Textfeld Column_num des Dialogfelds zu wechseln und das letzte Argument für die INDEX-Funktion einzugeben. Dieses Argument teilt Excel mit, dass die Sp altennummer im Bereich D6 bis F11 liegt. In diesem Bereich findet es die von der Funktion zurückgegebenen Informationen. In diesem Fall ein Lieferant für Titan-Widgets.
- Setzen Sie den Cursor in das Textfeld Column_num.
-
Geben Sie 3 (die Zahl Drei) ein. Diese Zahl weist die Formel an, nach Daten in der dritten Sp alte des Bereichs D6 bis F11 zu suchen.
Image - Lassen Sie das Dialogfeld für den nächsten Schritt im Tutorial geöffnet.
Array-Formel erstellen
Bevor Sie das Dialogfeld schließen, wandeln Sie die verschachtelte Funktion in eine Matrixformel um. Dieses Array ermöglicht der Funktion, nach mehreren Begriffen in der Datentabelle zu suchen. In diesem Tutorial werden zwei Begriffe abgeglichen: Widgets aus Sp alte 1 und Titanium aus Sp alte 2.
Um eine Matrixformel in Excel zu erstellen, drücken Sie CTRL, SHIFT und ENTERTasten gleichzeitig. Nach dem Drücken wird die Funktion von geschweiften Klammern umgeben, was darauf hinweist, dass die Funktion jetzt ein Array ist.
- Wählen Sie OK, um das Dialogfeld zu schließen. Wählen Sie in Excel für Mac Fertig.
- Zelle F3 auswählen, um die Formel anzuzeigen, dann den Cursor am Ende der Formel in der Formelleiste platzieren.
- Um die Formel in ein Array umzuwandeln, drücken Sie CTRL+ SHIFT+ ENTER.
- A NV Fehler erscheint in Zelle F3. Dies ist die Zelle, in die die Funktion eingegeben wurde.
-
Der Fehler NV erscheint in Zelle F3, weil die Zellen D3 und E3 leer sind. D3 und E3 sind die Zellen, in denen die Funktion sucht, um den Lookup_value zu finden. Nachdem diesen beiden Zellen Daten hinzugefügt wurden, wird der Fehler durch Informationen aus der Datenbank ersetzt.
Image
Suchkriterien hinzufügen
Der letzte Schritt besteht darin, die Suchbegriffe zum Arbeitsblatt hinzuzufügen. Dieser Schritt entspricht den Begriffen Widgets aus Sp alte 1 und Titanium aus Sp alte 2.
Wenn die Formel eine Übereinstimmung für beide Begriffe in den entsprechenden Sp alten der Datenbank findet, gibt sie den Wert aus der dritten Sp alte zurück.
- Zelle auswählen D3.
- Enter Widgets.
- Zelle auswählen E3.
- Geben Sie Titanium ein und drücken Sie Enter.
- Der Name des Lieferanten, Widgets Inc., erscheint in Zelle F3. Dies ist der einzige gelistete Lieferant, der Titanium Widgets verkauft.
-
Zelle auswählen F3. Die Funktion erscheint in der Bearbeitungsleiste über dem Arbeitsblatt.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
In diesem Beispiel gibt es nur einen Lieferanten für Titan-Widgets. Wenn es mehr als einen Lieferanten gegeben hat, wird der zuerst in der Datenbank aufgeführte Lieferant von der Funktion zurückgegeben.
Image
