MAX WENN (Maximalwert mit Bedingung) – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie den größten Wert, der bestimmte Bedingungen erfüllt, in Excel und Google Sheets finden können.

Die MAXWENNS-Funktion

Benutzer von Google Sheets und Excel 2019 oder höher können die einzelne MAXWENNS-Funktion verwenden.

In diesem Beispiel werden die Funktionen MAXWENNS und DATUM verwendet, um das größte Bestellvolumen für jeden Geschäftsnamen für Bestelldaten vor dem 30.4.2021 anzuzeigen:

=MAXWENNS(D3:D8;B3:B8;"A";C3:C8;"<"&DATUM(2021;4;30))

Bei der MAXWENNS-Funktion muss der Benutzer die Tasten STRG + UMSCHALTTASTE + ENTER bei der Eingabe der Formel nicht drücken.

MAX WENN – Array-Funktion

Wenn Sie keinen Zugriff auf die MAXWENNS-Funktion haben, können Sie eine Array-Formel erstellen, um diese zu simulieren. Wir werden dies im Folgenden erläutern.

Die MAX-Funktion ermittelt den größten Wert in einer Reihe von Zahlen.

=MAX(B2:B11)

 

Wir können die MAX-Funktion in Kombination mit einer WENN-Funktion verwenden, um den größten Wert, der eine bestimmte Bedingung erfüllt, zu ermitteln.

In diesem Beispiel werden die Funktionen MAX und WENN in einer Array-Formel verwendet, um das größte Bestellvolumen für jeden Geschäftsnamen zu ermitteln

{=MAX(WENN(B3:B8="A";D3:D8))}

In Office 365 und Excel-Versionen nach 2019 können Sie die obige Formel einfach wie gewohnt eingeben (durch Drücken von ENTER).

In Excel 2019 und früheren Versionen müssen Sie die Formel jedoch durch Drücken von STRG + UMSCHALTTASTE + ENTER eingeben. Danach werden Sie feststellen, dass geschweifte Klammern um die Formel herum erscheinen.

Um zu zeigen, wie diese Formel funktioniert, zerlegen wir sie in einzelne Schritte.

Dies ist unsere endgültige Formel (ohne die automatisch hinzugefügten Array-Formelklammern):

=MAX(WENN(B3:B8="A";D3:D8))

Zunächst werden die Werte des Zellenbereichs als Arrays in die Formel eingefügt:

=MAX(WENN({"A"; "B"; "A"; "B"; "A"; "B"}="A";{500; 400; 300; 700; 600; 200}))

Als nächstes erzeugt die Bedingung Geschäftsname =“A“ ein Array von WAHR- bzw. FALSCH-Werten:

=MAX(WENN({WAHR; FALSCH; WAHR; FALSCH; WAHR; FALSCH};{500; 400; 300; 700; 600; 200}))

Dann ändert die WENN-Funktion alle WAHR-Werte in das entsprechende Bestellvolumen:

=MAX({500; FALSCH; 300; FALSCH; 600; FALSCH})

Die MAX-Funktion identifiziert die größte Zahl im Array und ignoriert dabei alle FALSCH-Werte, um das größte Bestellvolumen für den Geschäftsnamen = „A“ anzuzeigen:

=600

MAX WENN – Mehrere Kriterien

Mit Hilfe der booleschen Logik kann der größte Wert auch auf der Grundlage mehrerer Kriterien ermittelt werden.

In diesem Beispiel wird das größte Bestellvolumen für jeden Geschäftsnamen, aber für Bestelldaten vor dem 30.4.2021 durch Verwendung der Funktionen MAX, WENN und DATUM angezeigt:

{=MAX(WENN((B3:B8="A")*(C3:C8<DATUM(2021;4;30));D3:D8))}

Beachten Sie, dass wir hier zwei Sätze von WAHR- bzw. FALSCH-Kriterien miteinander multiplizieren:

(B3:B8="A") * (C3:C8<DATUM(2021;4;30))

Wenn beide Kriterien WAHR sind, wird die gesamte Bedingung als WAHR berechnet, aber wenn eines der Kriterien (oder mehrere) FALSCH ist, wird sie als FALSCH berechnet.

Mit dieser Methode ist es möglich, viele verschiedene Kriterien zu dieser Formel hinzuzufügen.

MAX WENN – Mehrere Kriterien mit Zellenbezügen

Normalerweise ist es keine gute Praxis, Werte in Formeln „hart“ zu kodieren. Stattdessen ist es flexibler, die Kriterien in separaten Zellen zu definieren.

Um den Geschäftsnamen mit dem in Spalte F angezeigten Wert abzugleichen, können wir die Formel wie folgt aktualisieren:

{=MAX(WENN((B3:B8=F3)*(C3:C8<DATUM(2021;4;30));D3:D8))}

 

Zellenbezüge sperren

Um unsere Formeln besser lesbar zu machen, haben wir die Formeln ohne gesperrte Zellenbezüge dargestellt:

{=MAX(WENN((B3:B8=F3)*(C3:C8<DATUM(2021;4;30));D3:D8))}

Diese Formeln funktionieren jedoch nicht richtig, wenn sie kopiert und an anderer Stelle in Ihrer Datei eingefügt werden. Stattdessen sollten Sie gesperrte Zellenbezüge wie diese verwenden:

{=MAX(WENN(($B$3:$B$8=F3)*($C$3:$C$8<DATUM(2021;4;30));$D$3:$D$8))}

Lesen Sie unseren Artikel über das Sperren von Zellenbezügen, um mehr zu erfahren.

MAX WENN (Maximalwert mit Bedingung) in Google Sheets

Die oben gezeigten Beispiele funktionieren in Google Sheets genauso wie in Excel, allerdings steht die Funktion MAXWENNS hier zur Verfügung. Daher empfehlen wir, diese zu verwenden, anstatt die Funktionen MAX und WENN zu kombinieren.

Wenn Sie die MAX/WENN-Array-Formel verwenden möchten, geben Sie sie in die ARRAYFORMULA-Funktion ein (oder verwenden Sie einfach STRG + UMSCHALTTASTE + ENTER und Google Sheets fügt diese Funktion für Sie hinzu).

=ARRAYFORMULA(MAX(WENN((B3:B8="A")*(C3:C8<DATUM(2021;4;30));D3:D8)))