Index Match in Excel für die zweidimensionale Suche

Das Tutorial zeigt einige verschiedene Formeln, um zwei dimensionale Suche in Excel durchzuführen. Schauen Sie einfach die Alternativen durch und wählen Sie Ihren Favoriten :)

Bei der Suche nach etwas in Ihren Excel -Tabellenkalkulationen würden Sie die meiste Zeit vertikal in Spalten oder horizontal in Zeilen nachschlagen. Aber manchmal müssen Sie sowohl Zeilen als auch Spalten schauen. Mit anderen Worten, Sie möchten einen Wert an den Schnittpunkt einer bestimmten Zeile und Spalte finden. Dies wird als Matrix-Lookup (auch bekannt als 2-dimensional oder 2-Wege-Lookup ) bezeichnet, und dieses Tutorial zeigt, wie es auf 4 verschiedene Arten geht.

• Indexübereinstimmung mit der Suche in Zeilen und Spalten
• Zwei-Wege-Lookup mit Vlookup
• 2-Wege-Lookup mit Xlookup
• Zweidimensionale Suche mit Sumprodukt
• Matrix -Lookup mit den benannten Bereichen (explizite Kreuzung)

Excel Index -Match -Match -Match -Formel

Die beliebteste Möglichkeit, eine Zwei-Wege-Suche in Excel durchzuführen, ist die Verwendung der Index-Match-Match. Dies ist eine Variation der klassischen Index -Match -Formel, zu der Sie eine weitere Übereinstimmungsfunktion hinzufügen, um sowohl die Zeilen- als auch die Spaltennummern zu erhalten:

Index ( Data_array , Match ( vlookup_value , Lookup_column_Range , 0), Match ( Hlookup -Wert , Lookup_Row_Range , 0)))

Lassen Sie uns beispielsweise eine Formel erstellen, um eine Population eines bestimmten Tieres in einem bestimmten Jahr aus der folgenden Tabelle zu ziehen. Für den Anfang definieren wir alle Argumente:

• Data_Array - B2: E4 (Datenzellen, ohne Zeilen- und Spaltenkopfer)
• Vlookup_value - H1 (Zieltier)
• Lookup_Column_Range - A2: A4 (Zeilenkopfzeile: Tiernamen) - A3: A4
• Hlookup_Value - H2 (Zieljahr)
• Lookup_row_Range - B1: E1 (Spaltenüberschriften: Jahre)

Stellen Sie alle Argumente zusammen und Sie erhalten diese Formel für die Aufsuchung von Zwei-Wege:

=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))

Wenn Sie mit mehr als zwei Kriterien eine Zwei-Wege-Suche durchführen müssen, sehen Sie sich diesen Artikel an: Indexübereinstimmung mit mehreren Kriterien in Zeilen und Spalten.

Wie diese Formel funktioniert

Während es auf den ersten Blick etwas komplex aussieht, ist die Logik der Formel wirklich einfach und leicht zu verstehen. Die Indexfunktion holt einen Wert aus dem Datenarray basierend auf den Zeilen- und Spaltennummern, und zwei Übereinstimmungsfunktionen liefern die folgenden Zahlen:

INDEX(B2:E4, row_num, column_num)

Hier nutzen wir die Fähigkeit von Match (Lookup_Value, Lookup_array, [match_type]), eine relative Position von Lookup_Value in Lookup_array zurückzugeben.

Um die Zeilennummer zu erhalten, suchen wir nach dem Tier von Interesse (H1) über die Zeilenüberschriften (A2: A4):

MATCH(H1, A2:A4, 0)

Um die Spaltennummer zu erhalten, suchen wir nach dem Zieljahr (H2) über die Spaltenüberschriften (B1: E1):

MATCH(H2, B1:E1, 0)

In beiden Fällen suchen wir nach exakter Übereinstimmung, indem wir das 3. Argument auf 0 festlegen.

In diesem Beispiel gibt das erste Spiel 2 zurück, da unser Vlookup -Wert (Polarbär) in A3 gefunden wird, der ^2. Zelle in A2: A4. Das zweite Match gibt 3 zurück, da der Hlookup -Wert (2000) in D1 gefunden wird, der 3 ^-Rd -Zelle in B1: E1.

Angesichts der oben genannten Formel reduziert sich die Formel auf:

INDEX(B2:E4, 2, 3)

Und geben Sie einen Wert an der Kreuzung der ^2. Zeile und 3 ^RD -Spalte im Datenarray B2: E4 zurück, was in der Zelle D3 ein Wert ist.

Vlookup- und Match-Formel für die 2-Wege-Suche

Eine andere Möglichkeit, eine zweidimensionale Suche in Excel durchzuführen, besteht darin, eine Kombination aus Vlookup- und Übereinstimmungsfunktionen zu verwenden:

Vlookup ( vlookup_value , table_array , Match ( hlookup_value , Lookup_row_Range , 0), Falsch)

Für unsere Probentabelle hat die Formel die folgende Form:

=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

Wo:

• TABLE_Array - A2: E4 (Datenzellen einschließlich Zeilenheader)
• Vlookup_value - H1 (Zieltier)
• Hlookup_Value - H2 (Zieljahr)
• Lookup_row_Range - A1: E1 (Spaltenüberschriften: Jahre)

Wie diese Formel funktioniert

Der Kern der Formel ist die VLOOKUP -Funktion, die für die genaue Übereinstimmung konfiguriert ist (das letzte Argument auf False), das nach dem Suchwert (H1) in der ersten Spalte des Tabellenarrays (A2: E4) sucht und einen Wert aus einer anderen Spalte in derselben Zeile zurückgibt. Um festzustellen, aus welcher Spalte ein Wert zurückgegeben werden soll, verwenden Sie die Übereinstimmungsfunktion, die auch für die genaue Übereinstimmung konfiguriert ist (das letzte Argument auf 0):

MATCH(H2, A1:E1, 0)

Übereinstimmen die Suche nach dem Wert in H2 über die Spaltenhoitzen (A1: E1) und gibt die relative Position der gefundenen Zelle zurück. In unserem Fall befindet sich das Zieljahr (2010) in E1, das ^5. im Lookup -Array ist. Die Nummer 5 geht also zum Argument von vlookup COL_INDEX_NUM :

VLOOKUP(H1, A2:E4, 5, FALSE)

Vlookup nimmt es von dort aus, findet eine genaue Übereinstimmung für seinen Suchwert in A2 und gibt einen Wert aus der ^5. Spalte in derselben Zeile zurück, der Zelle E2.

Wichtiger Hinweis! Damit die Formel korrekt funktioniert, muss Table_Array (A2: E4) von Vlookup und Lookup_Array der Übereinstimmung (A1: E1) die gleiche Anzahl von Spalten haben, ansonsten ist die von der Übereinstimmung an col_index_num übergebene Nummer falsch (entspricht nicht der Position der Spalte in Tabelle_Array ).

Xlookup -Funktion, um in Zeilen und Spalten zu suchen

Kürzlich hat Microsoft eine weitere Funktion in Excel eingeführt, die alle vorhandenen Suchfunktionen wie Vlookup, Hlookup und Index Match ersetzen soll. Xlookup kann unter anderem den Schnittpunkt einer bestimmten Zeile und Spalte betrachten:

Xlookup ( vlookup_value , vlookup_column_range , xlookup ( hlookup_value , hlookup_row_range , data_array ))

Für unseren Beispieldatensatz lautet die Formel wie folgt:

=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))

Notiz. Die Xlookup -Funktion ist nur für Microsoft 365, Excel 2021 und Excel für das Web erhältlich.

Wie diese Formel funktioniert

Die Formel verwendet die Fähigkeit von Xlookup, eine ganze Zeile oder Spalte zurückzugeben. Die innere Funktion sucht nach dem Zieljahr in der Header -Zeile und gibt alle Werte für dieses Jahr zurück (in diesem Beispiel für Jahr 1980). Diese Werte gehen zum Argument return_array des äußeren Xlookups:

XLOOKUP(H1, A2:A4, {22000;25000;700}))

Die äußere Xlookup -Funktion sucht nach dem Zieltier über die Spaltenüberschriften und gibt den Wert in derselben Position aus dem return_array zurück.

Sumproduktformel für die Aufsuchung von Zwei-Wege

Die Sumproduktfunktion ist wie ein Schweizer Messer in Excel - sie kann so viele Dinge über den bestimmten Zweck hinaus tun, insbesondere wenn es um die Bewertung mehrerer Kriterien geht.

Verwenden Sie diese generische Formel, um zwei Kriterien in Zeilen und Spalten nachzuschlagen:

Sumproduct ( vlookup_column_range = vlookup_value ) * ( hlookup_row_range = hlookup_value ), data_array )

Um eine 2-Wege-Suche in unserem Datensatz durchzuführen, lautet die Formel wie folgt:

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)

Die folgende Syntax funktioniert auch:

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)

Wie diese Formel funktioniert

Im Zentrum der Formel vergleichen wir zwei Suchwerte mit den Zeilen- und Säulenheadern (das Zieltier in H1 mit allen Tiernamen in A2: A4 und das Zieljahr in H2 gegen alle Jahre in B1: E1):

(A2:A4=H1) * (B1:E1=H2)

Dies führt zu 2 Arrays wahrer und falscher Werte, wobei die True -Übereinstimmungen darstellen:

{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}

Der Multiplikationsvorgang zwingt die wahren und falschen Werte in 1er und 0 und erzeugt ein zweidimensionales Array von 4 Spalten und 3 Zeilen (Zeilen werden durch Semikolons und jede Datenspalte durch ein Komma getrennt):

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

Die Sumproduktfunktionen multipliziert die Elemente des obigen Arrays mit den Elementen von B2: E4 in denselben Positionen:

{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}

Und weil sich die Multiplizieren mit Null null ergibt, überlebt nur das Element, das 1 entspricht, im ersten Array:

SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})

Schließlich summiert Sumproduct die Elemente des resultierenden Arrays und gibt einen Wert von 2000 zurück.

Notiz. Wenn Ihre Tabelle mehr als eine Zeile oder/und Spaltenheader mit demselben Namen enthält, enthält das endgültige Array mehr als eine andere Zahl als Null, und alle diese Zahlen werden addiert. In diesem Ergebnis erhalten Sie eine Summe von Werten, die beide Kriterien erfüllen. Es ist das, was die Sumproduktformel von der Index Match Match und Vlookup unterscheidet, die das erste gefundene Spiel zurückgeben.

Matrix -Lookup mit den benannten Bereichen (explizite Kreuzung)

Eine erstaunlich einfache Möglichkeit, eine Matrix -Lookup in Excel durchzuführen, besteht darin, benannte Bereiche zu verwenden. So wie: wie:

Teil 1: Nennen Sie Spalten und Zeilen

Der schnellste Weg, um jede Zeile und jede Spalte in Ihrer Tabelle zu benennen, ist Folgendes:

1. Wählen Sie die gesamte Tabelle aus (A1: E4 in unserem Fall).
2. Klicken Sie auf der Registerkarte "Formeln" in der Gruppe definierter Namen aus der Auswahl erstellen oder drücken Sie die T -T -T -Verknüpfung von Strg Shift F3 .
3. Wählen Sie im Dialogfeld "Auswahlnamen erstellen" , wählen Sie die obere Zeile und die Spalte der linken Zeile und klicken Sie auf OK.

   

Dadurch werden automatisch Namen basierend auf den Zeilen- und Spaltenkopfzeilen erstellt. Es gibt jedoch einige Einschränkungen:

• Wenn Ihre Spalten- und/oder Zeilen -Header Zahlen sind oder bestimmte Zeichen enthalten, die in Excel -Namen nicht zulässig sind, werden die Namen für solche Spalten und Zeilen nicht erstellt. Öffnen Sie den Namensmanager ( Strg F3 ), um eine Liste erstellter Namen anzuzeigen. Wenn einige Namen fehlen, definieren Sie sie manuell, wie in der Benennung eines Bereichs in Excel erläutert.
• Wenn einige Ihrer Zeilen- oder Spaltenkopfzeile Räume enthalten, werden die Leerzeichen durch Unterstriche ersetzt, z. B. polar_bear .

Für unsere Beispieltabelle hat Excel automatisch nur die Zeilennamen erstellt. Die Spaltennamen müssen manuell erstellt werden, da die Spaltenüberschriften Zahlen sind. Um dies zu überwinden, können Sie die Zahlen einfach mit Unterstrichen vornehmen, wie _1990 .

Als Ergebnis haben wir die folgenden benannten Bereiche:

Teil 2: Machen Sie eine Matrix -Lookup -Formel

Um einen Wert an der Schnittstelle einer bestimmten Zeile und einer bestimmten Spalte zu ziehen, geben Sie einfach eins der folgenden generischen Formeln in eine leere Zelle ein:

= row_name column_name

Oder umgekehrt:

= column_name row_name

Um beispielsweise die Population von Blauwalen im Jahr 1990 zu bekommen, ist die Formel so einfach:

=Blue_whale _1990

Wenn jemand detailliertere Anweisungen benötigt, führen Sie die folgenden Schritte durch den Prozess:

1. Geben Sie in einer Zelle, in der das Ergebnis erscheint, das Gleichstellungszeichen (=) ein.
2. Geben Sie den Namen der Zielzeile ein, sagen Sie Blue_Whale . Nachdem Sie ein paar Zeichen eingegeben haben, zeigt Excel alle vorhandenen Namen an, die Ihrer Eingabe entsprechen. Doppelklicken Sie auf den gewünschten Namen, um ihn in Ihre Formel einzugeben:

   

3. Geben Sie nach dem Zeilennamen einen Speicherplatz ein, der in diesem Fall als Schnittbetreiber fungiert.
4. Geben Sie den Namen der Zielspalten ein ( _1990 in unserem Fall).

   

5. Sobald sowohl die Zeile als auch die Spaltennamen eingegeben werden, wird Excel die entsprechende Zeile und Spalte in Ihrer Tabelle hervorheben und die Eingabetaste drücken, um die Formel zu vervollständigen:

   

Ihre Matrix -Lookup erfolgt und der folgende Screenshot zeigt das Ergebnis:

So schauen Sie in Zeilen und Spalten in Excel nach. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Verfügbare Downloads

2-dimensionale Such-Beispiel Arbeitsbuch

Das obige ist der detaillierte Inhalt vonIndex Match in Excel für die zweidimensionale Suche. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!