Zwei-Wege-Annäherungsübereinstimmung mit mehreren Kriterien
In diesem Tutorial werden wir darüber sprechen, wie Sie anhand mehrerer Kriterien, die sowohl in Spalten als auch in Zeilen in einer Excel-Tabelle aufgeführt sind, mit Hilfe von nach einer ungefähren Übereinstimmung suchenf INDEX, PASSEN ZU und IF Funktionen.
Wie führt man eine ungefähre Zwei-Wege-Übereinstimmung mit mehreren Kriterien in Excel durch?
Um das herauszufinden Kleidergröße für eine Frau wer 165.5cm groß und wiegt 55kg gemäß obiger Tabelle können Sie der Formel zwei MATCH-Funktionen hinzufügen: eine mit einer IF-Funktion erhält die Zeilennummer (Geschlecht und Höhe), die andere erhält die Spaltennummer (Gewicht). Dann findet INDEX anhand der Koordinaten die entsprechende Größe. (Beachten Sie, dass die Zeilennummer in einer INDEX-Formel vor der Spaltennummer steht.)
Generische Syntax
=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Ctrl + Shift + Enter.
- Rückgabebereich: Der Bereich, aus dem die Kombinationsformel die Größe zurückgeben soll. Hier bezieht sich auf den Größenbereich.
- Lookup-Wert: Der Wert, den die Formel verwendet hat, um die Position der entsprechenden Größe zu ermitteln. Hier bezieht sich auf die Angaben über das angegebene Geschlecht, Größe und Gewicht.
- lookup_array: Der Zellbereich mit den zu vergleichenden Werten Lookup-Wert. Hier bezieht sich auf die Geschlechts-, Größen- und Gewichtsbereiche.
- Übereinstimmungstyp: 1 oder -1.
1 oder weggelassen (Standard), MATCH findet den größten Wert, der kleiner oder gleich dem ist Lookup-Wert. Die Werte im lookup_array müssen in aufsteigender Reihenfolge platziert werden.
-1, MATCH findet den kleinsten Wert, der größer oder gleich dem Lookup-Wert. Die Werte im lookup_array muss in absteigender Reihenfolge platziert werden.
Um das herauszufinden Kleidergröße für die Frau wer 165.5cm groß und wiegt 55kg, bitte kopieren oder geben Sie die Formel unten in die Zelle M9 ein und drücken Sie Ctrl + Shift + Enter um das Ergebnis zu erhalten:
=INDEX(D5: J16,SPIEL(M6,WENN(B5: B16=M5,C5: C16),1),VERGLEICH(M7,D4: J4, 1))
Erklärung der Formel
=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): Die IF-Funktion prüft jeden Wert in B5: B16 wenn sie mit dem Wert in der Zelle übereinstimmen M5, Female. Wenn dies der Fall ist, behält die IF-Funktion die entsprechenden Höhen bei, die in aufgeführt sind C5: C16; Wenn nicht, kehrt IF zurück FALSCH. Die Formel gibt ein Array wie folgt zurück: {157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- SPIEL(M6,IF(B5:B16=M5,C5:C16), 1) = SPIEL(M6,{157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, 1): Das Übereinstimmungstyp 1 zwingt die MATCH-Funktion, die Position des größten Werts zurückzugeben, der kleiner oder gleich 1 . ist65.5 (der Wert in der Zelle M6) im Array. Also, MATCH wird zurückkehren 4, die Position des Wertes 165.
- SPIEL(M7,D4:J4,1): Die MATCH-Funktion gibt zurück 3, da der größte Wert kleiner oder gleich dem Wert in M7, 55, ist am 3rd Position des Sortiments D4: J4.
- INDEX(D5: J16,SPIEL(M6,IF(B5:B16=M5,C5:C16), 1),SPIEL(M7,D4:J4,1)) = INDEX(D5: J16,4,3): Die INDEX-Funktion ruft den Wert am Schnittpunkt der 4th Reihe und 3rd Spalte im Größenbereich D5: J16, das ist der Wert in der Zelle F8, M.
Verwandte Funktionen
Die Excel-INDEX-Funktion gibt den angezeigten Wert basierend auf einer bestimmten Position aus einem Bereich oder einem Array zurück.
Die Excel MATCH-Funktion sucht in einem Zellbereich nach einem bestimmten Wert und gibt die relative Position des Werts zurück.
Die IF-Funktion ist eine der einfachsten und nützlichsten Funktionen in der Excel-Arbeitsmappe. Es führt einen einfachen logischen Test durch, der vom Vergleichsergebnis abhängt, und gibt einen Wert zurück, wenn ein Ergebnis TRUE ist, oder einen anderen Wert, wenn das Ergebnis FALSE ist.
Verwandte Formeln
Zwei-Wege-Suche mit INDEX und MATCH
Um in Excel sowohl zeilen- als auch spaltenübergreifend nach etwas zu suchen, oder um einen Wert am Schnittpunkt der jeweiligen Zeile und Spalte zu suchen, können wir die Hilfe der INDEX- und MATCH-Funktionen verwenden.
Nächste Übereinstimmung suchen
Um in Excel in einem numerischen Dataset nach der engsten Übereinstimmung eines Nachschlagewerts zu suchen, können Sie die Funktionen INDEX, MATCH, ABS und MIN zusammen verwenden.
Suchen Sie den nächstgelegenen Übereinstimmungswert mit mehreren Kriterien
In einigen Fällen müssen Sie möglicherweise basierend auf mehr als einem Kriterium den nächstgelegenen oder ungefähren Übereinstimmungswert suchen. Mit der Kombination von INDEX-, MATCH- und IF-Funktionen können Sie dies schnell in Excel erledigen.
Suche nach mehreren Kriterien mit INDEX und MATCH
Bei einer großen Datenbank in einer Excel-Tabelle mit mehreren Spalten und Zeilenbeschriftungen ist es immer schwierig, etwas zu finden, das mehrere Kriterien erfüllt. In diesem Fall können Sie eine Matrixformel mit den Funktionen INDEX und MATCH verwenden.
Ungefähre Übereinstimmung mit INDEX und MATCH
Es gibt Zeiten, in denen wir ungefähre Übereinstimmungen in Excel finden müssen, um die Leistung der Mitarbeiter zu bewerten, die Ergebnisse der Schüler zu bewerten, das Porto basierend auf dem Gewicht zu berechnen usw. In diesem Tutorial werden wir darüber sprechen, wie die Funktionen INDEX und MATCH zum Abrufen der Ergebnisse, die wir brauchen.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
