Excel -Index übereinstimmen mit mehreren Kriterien - Formel Beispiele

Das Tutorial zeigt, wie man mit mehreren Kriterien in Excel mit Index und Übereinstimmung und einigen anderen Möglichkeiten nachgibt.

Obwohl Microsoft Excel spezielle Funktionen für die vertikale und horizontale Suche bietet, ersetzen fachkundige Benutzer sie normalerweise durch die Indexübereinstimmung, die Vlookup und Hlookup in vielerlei Hinsicht überlegen ist. Unter anderem kann es zwei oder mehr Kriterien in Spalten und Zeilen nachschlagen. In diesem Tutorial wird die Syntax und die innere Mechanik ausführlich erläutert, damit Sie die Formel für Ihre besonderen Anforderungen problemlos anpassen können. Um die Beispiele zu erleichtern, können Sie unser Beispiel -Arbeitsbuch herstellen.

• Indexübereinstimmung mit mehreren Kriterien
• Nicht-Array-Indexübereinstimmung mit zwei oder mehr Kriterien
• Index übereinstimmen mehrere Kriterien in Zeilen und Spalten (Matrix -Lookup)

Excel Index übereinstimmen mit mehreren Kriterien

Wenn Sie mit großen Datenbanken arbeiten, befinden Sie sich manchmal in einer Situation, in der Sie etwas finden müssen, aber keine eindeutige Kennung für die Suche haben. In diesem Fall ist die Suche mit mehreren Bedingungen die einzige Lösung.

Verwenden Sie diese generische Formel, um einen Wert zu suchen, der auf mehreren Kriterien in separaten Spalten basiert:

Oder​​​​

Wo:

• Return_range ist der Bereich, aus dem ein Wert zurückgegeben werden kann.
• Kriterien1 , Kriterien2 ,… sind die Bedingungen, die erfüllt werden müssen.
• Bereich1 , Bereich2 ,… sind die Bereiche, auf denen die entsprechenden Kriterien getestet werden sollten.

Wichtiger Hinweis! Dies ist eine Array -Formel und muss mit der Strg -Schichteingabetaste abgeschlossen werden. Dadurch wird Ihre Formel in {Curly Brackets} eingeschlossen, was ein visuelles Zeichen einer Array -Formel in Excel ist. Versuchen Sie nicht, die Klammern manuell zu tippen, das funktioniert nicht!

Die Formel ist eine erweiterte Version des ikonischen Index -Spiels, die ein Spiel basierend auf einem einzigen Kriterium zurückgibt. Um mehrere Kriterien zu bewerten, verwenden wir den Multiplikationsvorgang, der als und Operator in Array -Formeln funktioniert. Im Folgenden finden Sie ein echtes Beispiel und die detaillierte Erklärung der Logik.

Tipp. In Excel 365 und 2021 können Sie die Xlookup -Formel mit mehreren Kriterien verwenden.

Indexübereinstimmung mit mehreren Kriterien - Formel Beispiel

In diesem Beispiel werden wir eine Tabelle im sogenannten "Flat-File" -Format mit jeder separaten Kriterienkombination (in unserem Fall Region-Monatsmonatsmonatem) in unserer eigenen Reihe verwenden. Unser Ziel ist es, die Verkaufszahlen für einen bestimmten Artikel in einer bestimmten Region und einem bestimmten Monat abzurufen.

Mit den Quelldaten und Kriterien in den folgenden Zellen:

• Return_range (sales) - d2: d13
• Kriterien1 (Zielregion) - G1
• Kriterien2 (Zielmonat) - G2
• Kriterien3 (Zielelement) - G3
• Bereich1 (Regionen) - A2: A13
• Bereich2 (Monate) - B2: B13
• Bereich3 (Elemente) - C2: C13

Die Formel hat die folgende Form:

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Geben Sie die Formel in G4 ein, indem Sie sie durch Drücken der Strg -Schalteingänge ausfüllen, und Sie erhalten das folgende Ergebnis:

Wie diese Formel funktioniert

Der schwierigste Teil ist die Spielfunktion. Lassen Sie uns zuerst herausfinden:

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Wie Sie sich vielleicht erinnern, sucht übereinstimmende (Lookup_Value, Lookup_array, [Match_type]) nach dem Suchwert im Such -Array und gibt die relative Position dieses Wertes im Array zurück.

In unserer Formel sind die Argumente wie folgt:

• Lookup_Value : 1
• Lookup_Array : (g1 = a2: a13) * (g2 = b2: b13) * (g3 = c2: c13)
• MATCH_TYPE : 0

Das 1 ^-st -Argument ist kristallklar - Die Funktion sucht nach der Nummer 1. Das 3 ^-RD -Argument, das auf 0 gesetzt ist, bedeutet eine "genaue Übereinstimmung", dh die Formel gibt den ersten gefundenen Wert zurück, der genau dem Suchwert entspricht.

Die Frage ist: Warum suchen wir nach "1"? Um die Antwort zu erhalten, schauen wir uns das Lookup -Array genauer an, in dem wir jedes Kriterium mit dem entsprechenden Bereich: Die Zielregion in G1 mit allen Regionen (A2: A13), dem Zielmonat in G2 gegen alle Monate (B2: B13) und dem Zielelement in G3 gegen alle Elemente (C2: C13) vergleichen. Ein Zwischenergebnis sind 3 Arrays von True und False, bei denen True Werte darstellt, die dem getesteten Zustand entsprechen. Um dies zu visualisieren, können Sie die einzelnen Ausdrücke in der Formel auswählen und die F9 -Taste drücken, um zu sehen, wie jeder Ausdruck bewertet wird:

Die Multiplikationsoperation verwandelt die wahren und falschen Werte in 1 und 0er:

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

Und weil sich multiplizieren mit 0 immer 0 ergibt, hat das resultierende Array nur 1 in den Zeilen, die alle Kriterien erfüllen:

{0;0;1;0;0;0;0;0;0;0;0;0}

Das obige Array geht zum Argument der Übereinstimmung von Lookup_array . Mit Lookup_Value von 1 gibt die Funktion die relative Position der Zeile zurück, für die alle Kriterien wahr sind (Zeile 3 in unserem Fall). Wenn es mehrere 1 im Array gibt, wird die Position der ersten zurückgegeben.

Die von der Übereinstimmung zurückgegebene Nummer geht direkt an das Argument row_num des Index (Array, row_num, [column_num])):

=INDEX(D2:D13, 3)

Und es ergibt ein Ergebnis von 115 US -Dollar, was der ^3. Wert im D2: D13 -Array ist.

Nicht-Array-Index-Übereinstimmungsformel mit mehreren Kriterien

Die im vorherige Beispiel diskutierte Array -Formel funktioniert gut für erfahrene Benutzer. Wenn Sie jedoch eine Formel für jemand anderen erstellen und dass jemand Array -Funktionen nicht kennt, kann er sie versehentlich brechen. Ein Benutzer kann beispielsweise auf Ihre Formel klicken, um sie zu untersuchen, und dann die Eingabetaste anstelle einer STRG -Schalteingabetaste drücken. In solchen Fällen wäre es ratsam, Arrays zu vermeiden und eine reguläre Formel zu verwenden, die kugelsicherer ist:

Index ( return_range , Match (1, Index (( Kriterien) = Bereich1 ) * ( Kriterien2 = Bereich2 ) * (..), 0, 1), 0))

Für unseren Beispieldatensatz lautet die Formel wie folgt:

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Wie diese Formel funktioniert

Da die Indexfunktion Arrays nativ verarbeiten kann, fügen wir einen weiteren Index hinzu, um das Array von 1 und 0 zu verarbeiten, das durch Multiplizieren von zwei oder mehr wahren/falschen Arrays erstellt wird. Der zweite Index ist mit 0 row_num -Argument für die Formel konfiguriert, um das gesamte Spaltenarray und nicht mit einem einzelnen Wert zurückzugeben. Da es ohnehin ein einspaltiges Array ist, können wir 1 für Column_Num sicher liefern:

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

Dieses Array wird an die Match -Funktion übergeben:

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

Match findet die Zeilennummer, für die alle Kriterien wahr sind (genauer gesagt die relative Position dieser Zeile im angegebenen Array) und übergeben diese Zahl an das Argument row_num des ersten Index:

=INDEX(D2:D13, 3)

Indexübereinstimmung mit mehreren Kriterien in Zeilen und Spalten

Dieses Beispiel zeigt, wie Sie nach zwei oder mehr Kriterien in Zeilen und Spalten suchen. Tatsächlich ist es ein komplexerer Fall der sogenannten "Matrix-Lookup" oder "Zwei-Wege-Lookup" mit mehr als einer Header-Reihe.

Hier ist die generische Index -Match -Formel mit mehreren Kriterien in Zeilen und Spalten:

Oder​​​​​​

Wo:

TABLE_Array - Die Karte oder der Bereich, der in innerhalb der Suchen, dh alle Datenwerte ohne Spalte und Zeilen Header.

Vlookup_value - Der Wert, den Sie in einer Spalte vertikal suchen.

Lookup_Column - Der Spaltenbereich, in dem man suchen soll, normalerweise die Zeilenheader.

Hlookup_value1, hlookup_value2,… - Die Werte, nach denen Sie in Zeilen horizontal suchen.

Lookup_row1, Lookup_row2,… - Die Zeilenbereiche, die nach suchen, normalerweise die Spaltenkopfzeile.

Wichtiger Hinweis! Damit die Formel korrekt funktioniert, muss sie als Array -Formel mit einer STRG -Verschiebung eingegeben werden.

Es ist eine Variation der klassischen Zwei-Wege-Lookup-Formel, die nach einem Wert an der Schnittstelle einer bestimmten Zeile und Spalte sucht. Der Unterschied besteht darin, dass Sie mehrere Hlookup -Werte und -bereiche verkettet, um mehrere Spaltenkopfzeile zu bewerten. Um die Logik besser zu verstehen, betrachten Sie bitte das folgende Beispiel.

Matrix -Lookup mit mehreren Kriterien - Beispiel für Formel

In der folgenden Beispieltabelle suchen wir nach einem Wert, der auf den Zeilenkopfzeilen (Elementen) und 2 Spaltenüberschriften (Regionen und Anbieter) basiert. Um die Formel zu erleichtern, lassen Sie uns zunächst alle Kriterien und Bereiche definieren:

• Table_array - b3: e4
• Vlookup_value (Zielelement) - H1
• Lookup_Column (Zeilenkopfzeile: Elemente) - A3: A4
• Hlookup_value1 (Zielregion) - H2
• Hlookup_value2 (Zielanbieter) - H3
• Lookup_row1 (Spaltenüberschriften 1: Regionen) - B1: E1
• Lookup_row2 (Spaltenüberschriften 2: Anbieter) - B2: E2

Und jetzt geben Sie die Argumente in die oben erläuterte generische Formel, und Sie erhalten dieses Ergebnis:

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

Denken Sie daran, die Formel zu vervollständigen, indem Sie die STRL Shift Enter -Verknüpfung drücken, und Ihre Matrix -Lookup mit mehreren Kriterien wird erfolgreich durchgeführt:

Wie diese Formel funktioniert

Da wir vertikal und horizontal suchen, müssen wir sowohl die Zeilen- als auch die Spaltennummern für die Funktion des Index (Array, row_num, column_num) angeben.

Row_num wird von Match (H1, A3: A5, 0) geliefert, das das Zielelement (Äpfel) in H1 mit den Zeilenkopfzeilen in A3: A5 vergleicht. Dies ergibt ein Ergebnis von 1, da "Äpfel" das erste Element im angegebenen Bereich ist.

Column_Num wird durch Verkettung von 2 Suchwerten und 2 Lookup -Arrays ausgearbeitet: Übereinstimmung (H2 & H3, B1: E1 & B2: E2, 0))

Der Schlüsselfaktor für den Erfolg ist, dass die Suchwerte genau mit den Spaltenkopfzeilen übereinstimmen und in derselben Reihenfolge verkettet werden sollten. Um dies zu visualisieren, wählen Sie die ersten beiden Argumente in der Match -Formel aus, drücken Sie F9 und Sie werden sehen, was jedes Argument bewertet:

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

Da "Northvendor 2" das zweite Element im Array ist, gibt die Funktion 2 zurück.

Zu diesem Zeitpunkt verwandelt sich unsere langwierige zweidimensionale Index-Match-Formel in dieses einfache:

=INDEX(B3:E5, 1, 2)

Und gibt einen Wert an der Kreuzung der 1. Zeile und der 2. Spalte im Bereich B3: E5 zurück, was der Wert in der Zelle C3 ist.

So suchen Sie in Excel mehrere Kriterien an. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Üben Sie die Arbeitsmappe zum Download

Excel Index entspricht mehreren Kriterien (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonExcel -Index übereinstimmen mit mehreren Kriterien - Formel Beispiele. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!