Maximum, wenn mehrere Kriterien

Um den Maximalwert in einem Datensatz basierend auf mehr als einem Kriterium zu erhalten, können Sie eine Matrixformel basierend auf den MAX- und IF-Funktionen verwenden. Im gezeigten Beispiel lautet die Formel in I6:

{= MAX ( IF (rng1=criteria1, IF (rng2=criteria2,values)))}

Bei einer Farbe 'Rot' und 'Hut' beträgt das Ergebnis 11,00 USD

Hinweis: Dies ist ein Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden

In diesem Beispiel wird Folgendes verwendet benannte Bereiche : 'Farbe' = B6:B14, 'Artikel' = C6:C14 und 'Preis' = E6:E14. Das Ziel ist es, den maximalen Preis für eine bestimmte Farbe und einen bestimmten Artikel zu finden.

Diese Formel verwendet zwei verschachtelte IF-Funktionen, die in MAX eingeschlossen sind, um den maximalen Preis mit zwei Kriterien zurückzugeben. Beginnend mit einem logischen Test der ersten IF-Anweisung, color = G6, werden die Werte im benannten Bereich 'color' (B6:B14) gegen den Wert in Zelle G6 'red' geprüft. Das Ergebnis ist ein Array wie dieses:

 
{= MAX ( IF (color=G6, IF (item=H6,price)))}

Beim logischen Test für die zweite IF-Anweisung item = H6 werden die Werte im benannten Bereichselement (C6:C14) gegen den Wert in Zelle H6, 'hat', geprüft. Das Ergebnis ist ein Array wie dieses:

 
{TRUETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSE}

Der 'value if true' für die 2. IF-Anweisung ist der benannte Bereich 'prices' (E6:E14), der ein Array wie folgt ist:

 
{TRUETRUETRUETRUETRUETRUEFALSEFALSEFALSE}

Für jeden Artikel in diesem Bereich wird ein Preis zurückgegeben nur wenn das Ergebnis der ersten beiden obigen Arrays TRUE für Elemente an entsprechenden Positionen ist. Im gezeigten Beispiel sieht das letzte Array in MAX so aus:

 
{118912910987}

Beachten Sie, dass die einzigen Preise, die 'überleben', die in einer Position sind, in der 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 Matrixformel verwenden, die nur eine IF-Funktion zusammen mit verwendet Boolesche Logik :

wie man den Durchschnitt in Excel nimmt

 
{1189FALSEFALSEFALSEFALSEFALSEFALSE}

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 die Addition statt der Multiplikation zwischen den Bedingungen.

Mit MAXIFS

Die MAXIFS-Funktion , eingeführt in Excel 2016, wurde entwickelt, um Höchstwerte basierend auf einem oder mehreren Kriterien zu berechnen, ohne dass eine Matrixformel erforderlich ist. Bei MAXIFS lautet die Formel in I6:

 
{= MAX ( IF ((color=G6)*(item=H6),price))}

Hinweis: MAXIFS wird automatisch ignorieren leere Zellen, die die Kriterien erfüllen. Mit anderen Worten, MAXIFS behandelt leere Zellen, die die Kriterien erfüllen, nicht als Null. Auf der anderen Seite MAXIFS Wille Null (0) zurückgeben, wenn keine Zellen den Kriterien entsprechen.