Laden Sie die Beispielarbeitsmappe herunter
In diesem Tutorial wird gezeigt, wie Sie den größten Zellenwert finden, der bestimmte Bedingungen in Excel und Google Sheets erfüllt.
Max. Wenn Array-Funktion
Die MAX-Funktion identifiziert den größten Wert in einer Reihe von Zahlen.
Wir können die MAX-Funktion in Kombination mit einer IF-Funktion verwenden, um den größten Wert zu identifizieren, der eine bestimmte Bedingung erfüllt.
Benutzern von Google Sheets und Excel 2022 oder höher wird empfohlen, die einfachere MAXIFS-Funktion zu verwenden. Dies wird in einem späteren Abschnitt erklärt.
In diesem Beispiel werden die MAX- und IF-Funktionen in einer Arrayformel verwendet, um die größte zu identifizieren Bestellgröße für jeden Name des Geschäfts
| 1 | {=MAX(IF(B3:B8="A",D3:D8))} |
In Office 365 und Excel-Versionen nach 2022 können Sie die obige Formel einfach wie gewohnt eingeben (durch Drücken der EINGABETASTE).
Für Excel 2022 und früher müssen Sie die Formel jedoch eingeben, indem Sie STRG + UMSCHALT + EINGABETASTE drücken. Danach werden Sie feststellen, dass um die Formel geschweifte Array-Klammern angezeigt werden.
Um zu zeigen, wie diese Formel funktioniert, teilen wir sie in Schritte auf.
Dies ist unsere endgültige Formel (dargestellt ohne die automatisch hinzugefügten Klammern für die Array-Formel):
| 1 | =MAX(WENN(B3:B8="A",D3:D8)) |
Zuerst werden die Zellenbereichswerte als Arrays zur Formel hinzugefügt:
| 1 | =MAX(IF({"A"; "B"; "A"; "B"; "A"; "B"}="A",{500; 400; 300; 700; 600; 200})) |
Als nächstes Name des Geschäfts =”A”-Bedingung erzeugt ein Array von TRUE/FALSE-Werten:
| 1 | =MAX(IF({WAHR; FALSCH; WAHR; FALSCH; WAHR; FALSCH},{500; 400; 300; 700; 600; 200})) |
Dann ändert die IF-Funktion alle TRUE-Werte in die relevanten Bestellgröße:
| 1 | =MAX({500; FALSCH; 300; FALSCH; 600; FALSCH}) |
Die MAX-Funktion identifiziert die größte Zahl im Array, ignoriert jedoch alle FALSE-Werte, um die größte anzuzeigen Bestellgröße für die Name des Geschäfts = „A“:
| 1 | =600 |
Max. Wenn - Mehrere Kriterien
Wir können den größten Wert auch basierend auf mehreren Kriterien identifizieren, indem wir boolesche Logik verwenden.
Dieses Beispiel zeigt die größte Bestellgröße für jeden Name des Geschäfts, aber für Bestelldaten vor dem 30.04.2021 mit den Funktionen MAX, IF und DATE:
| 1 | {=MAX(IF((B3:B8="A")*(C3:C8 |
Beachten Sie, dass wir hier zwei Sätze von WAHR/FALSCH-Kriterien miteinander multiplizieren:
| 1 | (B3:B8="A") * (C3:C8 |
Wenn beide Kriterien WAHR sind, wird die Gesamtbedingung als WAHR berechnet, aber wenn ein (oder mehrere) Kriterium FALSCH ist, wird sie als FALSCH berechnet.
Mit dieser Methodik ist es möglich, dieser Formel viele verschiedene Kriterien hinzuzufügen.
Max. Wenn - Mehrere Kriterien mit Zellbezügen
Normalerweise ist es nicht empfehlenswert, Werte in Formeln fest zu codieren. Stattdessen ist es flexibler, separate Zellen zum Definieren der Kriterien zu verwenden.
Passend zu den Name des Geschäfts auf den in Spalte F angezeigten Wert können wir die Formel wie folgt aktualisieren:
| 1 | {=MAX(IF((B3:B8=F3)*(C3:C8 |
Zellenreferenzen sperren
Um unsere Formeln leichter lesbar zu machen, haben wir die Formeln ohne gesperrte Zellbezüge gezeigt:
| 1 | {=MAX(IF((B3:B8=F3)*(C3:C8 |
Diese Formeln funktionieren jedoch nicht richtig, wenn Sie sie an anderer Stelle in Ihrer Datei kopieren und einfügen. Stattdessen sollten Sie gesperrte Zellbezüge wie folgt verwenden:
| 1 | {=MAX(IF(($B$3:$B$8=F3)*($C$3:$C$8 |
Lesen Sie unseren Artikel zum Sperren von Zellreferenzen, um mehr zu erfahren.
MAXIFS-Funktion
Benutzer von Google Tabellen und Excel 2022 oder höher können die einzelne MAXIFS-Funktion verwenden, um das Verhalten der MAX- und IF-Funktionen zu replizieren, die in den früheren Beispielen gezeigt wurden.
Dieses nächste Beispiel verwendet die Funktionen MAXIFS und DATE, um die größten Bestellgröße für jeden Name des Geschäfts zum Bestelldaten vor dem 30.04.2021:
| 1 | =MAXIFS(D3:D8,B3:B8,"A",C3:C8,"<"&DATUM(2021,4,30)) |
Die MAXIFS-Funktion erfordert nicht, dass der Benutzer beim Eingeben der Formel STRG + UMSCHALT + EINGABE drückt.
Max. Wenn (Max. Wert mit Bedingung) in Google Tabellen
Die oben gezeigten Beispiele funktionieren in Google Sheets genauso wie in Excel, aber da die MAXIFS-Funktion verfügbar ist, wird empfohlen, diese einzelne Funktion zu verwenden, anstatt die MAX- und IF-Funktionen zu kombinieren.
Wenn die Beispiele mit MAX- und IF-Funktionen verwendet werden sollen, müssen Sie diese in Google Tabellen als Arrayformeln eingeben. Anstatt die Formel mit geschweiften Excel-Array-Klammern { } anzuzeigen, wird durch Drücken von STRG + UMSCHALT + EINGABE automatisch die ARRAYFORMULA-Funktion um die Formel hinzugefügt:
| 1 | =ARRAYFORMULA(MAX(IF((B3:B8="A")*(C3:C8 |
