Generische Formel
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Zusammenfassung
Um den Maximalwert in einem Datensatz basierend auf mehr als einem Kriterium zu erhalten, können Sie eine Array-Formel verwenden, die auf den Funktionen MAX und IF basiert. In dem gezeigten Beispiel lautet die Formel in I6:
(=MAX(IF(color=G6,IF(item=H6,price))))
Mit einer Farbe von "Rot" und einem Gegenstand von "Hut" beträgt das Ergebnis 11,00 USD
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + eingegeben werden
Erläuterung
In diesem Beispiel werden die folgenden benannten Bereiche verwendet: "Farbe" = B6: B14, "Artikel" = C6: C14 und "Preis" = E6: E14. Ziel ist es, den Höchstpreis für eine bestimmte Farbe und einen bestimmten Artikel zu ermitteln.
Diese Formel verwendet zwei verschachtelte IF-Funktionen, die in MAX eingeschlossen sind, um den Maximalpreis mit zwei Kriterien zurückzugeben. Beginnend mit einem logischen Test der ersten IF-Anweisung, Farbe = G6, werden die Werte im benannten Bereich "Farbe" (B6: B14) mit dem Wert in Zelle G6 "Rot" verglichen. Das Ergebnis ist ein Array wie folgt:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Beim logischen Test für die zweite IF-Anweisung, item = H6, werden die Werte im benannten Bereich item (C6: C14) mit dem Wert in Zelle H6 "hat" verglichen. Das Ergebnis ist ein Array wie folgt:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
Der "Wert wenn wahr" für die 2. IF-Anweisung ist der benannte Bereich "Preise" (E6: E14), bei dem es sich um ein Array wie das folgende handelt:
(11;8;9;12;9;10;9;8;7)
Ein Preis wird für jeden Artikel in diesem Bereich nur zurückgegeben, wenn das Ergebnis der ersten beiden obigen Arrays für Artikel an entsprechenden Positionen WAHR ist. Im gezeigten Beispiel sieht das endgültige Array in MAX folgendermaßen aus:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Beachten Sie, dass die einzigen Preise, die "überleben", die Preise sind, bei denen die Farbe "rot" und der Artikel "Hut" ist.
Die MAX-Funktion gibt dann den höchsten Preis zurück und ignoriert automatisch FALSE-Werte.
Alternative Syntax mit boolescher Logik
Sie können auch die folgende Array-Formel verwenden, die nur eine IF-Funktion zusammen mit der Booleschen Logik verwendet:
(=MAX(IF((color=G6)*(item=H6),price)))
Der Vorteil dieser Syntax besteht darin, dass es einfacher ist, zusätzliche Kriterien hinzuzufügen, ohne zusätzliche verschachtelte IF-Funktionen hinzuzufügen. Wenn Sie eine ODER-Logik benötigen, verwenden Sie Addition anstelle von Multiplikation zwischen Bedingungen.
Mit MAXIFS
Die in Excel 2016 eingeführte MAXIFS-Funktion dient zur Berechnung von Maximalwerten basierend auf einem oder mehreren Kriterien, ohne dass eine Array-Formel erforderlich ist. Bei MAXIFS lautet die Formel in I6:
=MAXIFS(price,color,G6,item,H6)
Hinweis: MAXIFS ignoriert automatisch leere Zellen, die die Kriterien erfüllen. Mit anderen Worten, MAXIFS behandelt leere Zellen, die Kriterien erfüllen, nicht als Null. Andererseits gibt MAXIFS Null (0) zurück, wenn keine Zellen den Kriterien entsprechen.
