Zweidimensionale ungefähre Übereinstimmung mit mehreren Kriterien
In diesem Tutorial wird beschrieben, wie Sie eine ungefähre Übereinstimmung anhand mehrerer Kriterien finden, die sowohl in Spalten als auch in Zeilen eines Excel-Arbeitsblatts aufgeführt sind, mithilfe der INDEX-,VERGLEICH-und WENN-Funktionen.
Wie führt man in Excel eine zweidimensionale ungefähre Übereinstimmung anhand mehrerer Kriterien durch?
Um die Kleidergröße für eine Frau, die 165,5 cm groß ist und 55 kg wiegt, anhand der obigen Tabelle zu ermitteln, können Sie zwei VERGLEICH-Funktionen in Ihre Formel einfügen: Eine innerhalb einer WENN-Funktion bestimmt die Zeilennummer (basierend auf Geschlecht und Körpergröße), die andere die Spaltennummer (basierend auf dem Gewicht). Anschließend liefert INDEX mithilfe dieser Koordinaten die passende Kleidergröße. (Beachten Sie: In einer INDEX-Formel steht stets die Zeilennummer vor der Spaltennummer.)
Allgemeine 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 Arrayformel, die mit Strg+Umschalttaste+Eingabetasteeingegeben werden muss.
- Rückgabebereich: Der Bereich, aus dem die Kombinationsformel die Größe zurückgeben soll – hier also der Größenbereich.
- Suchwert: Der Wert, anhand dessen die Formel die Position der entsprechenden Größe ermittelt. Hier bezieht sich das auf die Angaben zu Geschlecht, Körpergröße und Gewicht.
- Suchmatrix: Der Zellbereich mit den Werten, die mit dem Suchwert verglichen werden sollen. Hier bezieht sich dies auf die Bereiche für Geschlecht, Körpergröße und Gewicht.
- Vergleichstyp: 1 oder -1.
1 oder weggelassen (Standard): MATCH sucht den größten Wert, der kleiner als oder gleich dem Suchwert ist. Die Werte in der Suchmatrix müssen in aufsteigender Reihenfolge angeordnet sein.
-1: MATCH sucht den kleinsten Wert, der größer als oder gleich dem Suchwert ist. Die Werte in der Suchmatrix müssen in absteigender Reihenfolge angeordnet sein.
Um die Kleidergrößefür die Frau, die 165,5cm groß ist und 55kg wiegt, zu ermitteln, kopieren oder geben Sie bitte die folgende Formel in die Zelle M9 ein und drücken Sie Strg+Umschalttaste+Eingabetaste, um das Ergebnis zu erhalten:
=INDEX()D5:J16;VERGLEICH()M6;WENN()B5:B16=M5;C5:C16);1);VERGLEICH(M7;D4:J4;1))
Erläuterung der Formel
=INDEX()D5:J16,MATCH(M6,)IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- WENN(B5:B16=M5;C5:C16): Die WENN-Funktion prüft jeden Wert in B5:B16, ob er dem Wert in der Zelle M5, also „weiblich“, entspricht. Wenn ja, übernimmt die WENN-Funktion die entsprechenden Körpergrößen aus C5:C16; andernfalls gibt sie FALSCH-Werte zurück. Die Formel liefert ein Array wie folgt: {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 veranlasst die VERGLEICH-Funktion, die Position des größten Werts zurückzugeben, der kleiner als oder gleich 165,5(dem Wert in Zelle)M6) im Array ist. Daher gibt VERGLEICH 4 – die Position des Werts 165 – zurück.
- VERGLEICH(M7;D4:J4;1): Die VERGLEICH-Funktion gibt 3 zurück, da der größte Wert, der dem Wert in M7 (55) entspricht, an der 3. Position des Bereichs D4:J4 steht.
- INDEX()D5:J16,VERGLEICH(M6;)WENN(B5:B16=M5;C5:C16),1),VERGLEICH(M7;D4:J4;1)) = INDEX(D5:J16: Die INDEX-Funktion ruft den Wert an der Schnittstelle der 4. Zeile und der 3. Spalte im Bereich D5:J16 ab – also den Wert in Zelle F8, M.
Verwandte Funktionen
Die Excel-INDEX-Funktion gibt den Angezeigter Wert basierend auf einer vorgegebenen 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 dieses Werts zurück.
Die WENN-Funktion zählt zu den einfachsten und nützlichsten Funktionen in einer Excel-Arbeitsmappe: Sie führt einen logischen Test durch und gibt – je nachdem, ob das Ergebnis WAHR oder FALSCH ist – einen entsprechenden Wert zurück.
Verwandte Formeln
Zweidimensionale Suche mit INDEX und VERGLEICH
Um in Excel nach einem Wert sowohl zeilen- als auch spaltenweise zu suchen – also einen Wert an der Schnittstelle einer bestimmten Zeile und Spalte zu finden – können Sie die INDEX- und VERGLEICH-Funktionen kombinieren.
Nächste Übereinstimmung suchen
Um in Excel die nächstliegende Übereinstimmung eines Suchwerts in einem numerischen Datensatz zu ermitteln, kombinieren Sie einfach die Funktionen INDEX, VERGLEICH, ABS und MIN.
Nächste Übereinstimmung mit mehreren Kriterien suchen
In manchen Fällen müssen Sie den nächstgelegenen oder ungefähren Übereinstimmungswert anhand mehrerer Kriterien ermitteln. Mit der leistungsstarken Kombination aus INDEX-, VERGLEICH- und WENN-Funktionen gelingt Ihnen das in Excel im Handumdrehen.
Mehrfachkriterien-Suche mit INDEX und VERGLEICH
Bei der Arbeit mit einer umfangreichen Datenbank in einem Excel-Arbeitsblatt, das zahlreiche Spalten und Zeilenüberschriften enthält, fällt es oft schwer, Einträge zu finden, die mehrere Kriterien erfüllen. In solchen Fällen empfiehlt sich der Einsatz einer Arrayformel mit den Funktionen INDEX und VERGLEICH.
Ungefähre Übereinstimmung mit INDEX und VERGLEICH
Es gibt Situationen, in denen wir in Excel auf ungefähre Übereinstimmungen angewiesen sind – etwa um die Leistung von Mitarbeitern zu bewerten, Schülernoten zu berechnen oder das Porto anhand des Gewichts zu ermitteln. In diesem Tutorial erfahren Sie, wie Sie mit den Funktionen INDEX und VERGLEICH genau die gewünschten Ergebnisse erzielen.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.