Zweiseitige ungefähre Übereinstimmung mit mehreren Kriterien
In diesem Tutorial werden wir darüber sprechen, wie man auf der Grundlage mehrerer Kriterien, die sowohl in Spalten als auch in Zeilen einer Excel-Tabelle aufgelistet sind, eine ungefähre Übereinstimmung findet, mit Hilfe der Funktionen INDEX, VERGLEICH und WENN.
Wie führt man eine zweiseitige ungefähre Übereinstimmung mit mehreren Kriterien in Excel durch?
Um die Kleidergröße für eine Frau zu ermitteln, die 165,5 cm groß ist und 55 kg wiegt, basierend auf der obigen Tabelle, können Sie zwei VERGLEICH-Funktionen zur Formel hinzufügen: eine mit einer WENN-Funktion erhält die Zeilennummer (Geschlecht und Größe), die andere erhält die Spaltennummer (Gewicht). Dann wird INDEX die entsprechende Größe anhand der Koordinaten finden. (Beachten Sie, dass die Zeilennummer vor der Spaltennummer in einer INDEX-Formel steht.)
Allgemeine Syntax
=INDEX(Rückgabebereich;VERGLEICH(Suchwert1;WENN(Sucharray2=Suchwert2;Sucharray1);Vergleichstyp);VERGLEICH(Suchwert3;Sucharray3;Vergleichstyp))
√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Enter eingeben müssen.
- Rückgabebereich: Der Bereich, aus dem die Kombinationsformel die Größe zurückgeben soll. Hier bezieht es sich auf den Größenbereich.
- Suchwert: Der Wert, den die Formel verwendet, um die Position der entsprechenden Größe zu lokalisieren. Hier bezieht es sich auf die Informationen über das angegebene Geschlecht, die Größe und das Gewicht.
- Sucharray: Der Zellbereich mit den Werten, die mit dem Suchwert verglichen werden sollen. Hier bezieht es sich auf die Bereiche für Geschlecht, Größe und Gewicht.
- Vergleichstyp: 1 oder -1.
1 oder weggelassen (Standard), VERGLEICH sucht den größten Wert, der kleiner oder gleich dem Suchwert ist. Die Werte im Sucharray müssen in aufsteigender Reihenfolge sortiert sein.
-1, VERGLEICH sucht den kleinsten Wert, der größer oder gleich dem Suchwert ist. Die Werte im Sucharray müssen in absteigender Reihenfolge sortiert sein.
Um die Kleidergröße für die Frau herauszufinden, die 165,5 cm groß ist und 55 kg wiegt, kopieren Sie bitte die folgende Formel in die Zelle M9, oder geben Sie sie ein, und drücken Sie Strg + Umschalt + Enter, um das Ergebnis zu erhalten:
=INDEX(D5:J16;VERGLEICH(M6;WENN(B5:B16=M5;C5:C16);1);VERGLEICH(M7;D4:J4;1))
Erklärung der Formel
=INDEX(D5:J16;VERGLEICH(M6;WENN(B5:B16=M5;C5:C16);1);VERGLEICH(M7;D4:J4;1))
- WENN(B5:B16=M5;C5:C16): Die WENN-Funktion überprüft jeden Wert in B5:B16, ob diese mit dem Wert in der Zelle M5, Weiblich, übereinstimmen. Wenn ja, behält die WENN-Funktion die entsprechenden Größen, die in C5:C16 aufgeführt sind; wenn nicht, gibt WENN FALSCH-Werte zurück. Die Formel gibt ein Array wie dieses zurück: {157;160;162,5;165;167,5;170;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}.
- VERGLEICH(M6;WENN(B5:B16=M5;C5:C16);1) = VERGLEICH(M6;{157;160;162,5;165;167,5;170;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH};1): Der Vergleichstyp 1 zwingt die VERGLEICH-Funktion, die Position des größten Werts zurückzugeben, der kleiner oder gleich 165,5 (der Wert in der Zelle M6) im Array ist. Daher gibt VERGLEICH 4 zurück, die Position des Werts 165.
- VERGLEICH(M7;D4:J4;1): Die VERGLEICH-Funktion gibt 3 zurück, da der größte Wert, der kleiner oder gleich dem Wert in M7, 55, ist, an der 3. Position des Bereichs D4:J4 liegt.
- INDEX(D5:J16D5:J16;VERGLEICH(M6;WENN(B5:B16=M5;C5:C16);1);VERGLEICH(M7;D4:J4;1)) = INDEX(D5:J16D5:J16;4;3): Die INDEX-Funktion ruft den Wert am Schnittpunkt der 4. Zeile und 3. Spalte im Größenbereich D5:J16 ab, welcher der Wert in der Zelle F8, M, ist.
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 VERGLEICH-Funktion sucht nach einem bestimmten Wert in einem Zellbereich und gibt die relative Position des Werts zurück.
Die WENN-Funktion ist eine der einfachsten und nützlichsten Funktionen in einer Excel-Arbeitsmappe. Sie führt einen einfachen logischen Test durch, der je nach Vergleichsergebnis entweder einen Wert zurückgibt, wenn das Ergebnis WAHR ist, oder einen anderen Wert, wenn das Ergebnis FALSCH ist.
Verwandte Formeln
Zweiseitige Suche mit INDEX und VERGLEICH
Um etwas in Excel sowohl in Zeilen als auch in Spalten zu suchen, oder um einen Wert an der Schnittstelle einer bestimmten Zeile und Spalte zu finden, können wir die Hilfe der INDEX- und VERGLEICH-Funktionen nutzen.
Nächstliegendes Ergebnis suchen
Um das nächstliegende Ergebnis eines Suchwerts in einem numerischen Datensatz in Excel zu finden, können Sie die INDEX-, VERGLEICH-, ABS- und MIN-Funktionen gemeinsam verwenden.
Nächstliegendes Ergebnis mit mehreren Kriterien suchen
In einigen Fällen müssen Sie möglicherweise das nächstliegende oder ungefähre Ergebnis basierend auf mehr als einem Kriterium suchen. Mit der Kombination von INDEX-, VERGLEICH- und WENN-Funktionen können Sie dies schnell in Excel erledigen.
Mehrfachkriterien-Suche mit INDEX und VERGLEICH
Wenn Sie mit einer großen Datenbank in einer Excel-Tabelle mit mehreren Spalten und Zeilenüberschriften arbeiten, ist es immer schwierig, etwas zu finden, das mehreren Kriterien entspricht. In diesem Fall können Sie eine Array-Formel mit den INDEX- und VERGLEICH-Funktionen verwenden.
Ungefähre Übereinstimmung mit INDEX und VERGLEICH
Es gibt Zeiten, in denen wir ungefähre Übereinstimmungen in Excel finden müssen, um die Leistung von Mitarbeitern zu bewerten, die Noten von Schülern zu berechnen, Porto basierend auf Gewicht zu berechnen usw. In diesem Tutorial werden wir darüber sprechen, wie man die INDEX- und VERGLEICH-Funktionen verwendet, um die benötigten Ergebnisse abzurufen.
Die besten Tools zur Büroproduktivität
Kutools für Excel - Hilft Ihnen, aus der Menge hervorzustechen
Kutools für Excel bietet über 300 Funktionen, sodass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab - Aktiviert tabbed Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- In einer Sekunde zwischen Dutzenden offener Dokumente wechseln!
- Reduziert Hunderte von Mausklicks für Sie jeden Tag, verabschieden Sie sich von der Maushand.
- Steigert Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs ins Office (inklusive Excel), genau wie bei Chrome, Edge und Firefox.