Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial zeigt, wie man „großes wenn“ oder „kleines wenn“ berechnet und den n-größten (oder kleinsten) Wert basierend auf Kriterien abruft.
GROSSE & KLEINE Funktionen
Die LARGE-Funktion wird verwendet, um den n-größten Wert (k) in einem Array zu berechnen, während die SMALL-Funktion den kleinsten n-ten Wert zurückgibt.
Um ein "Large If" zu erstellen, verwenden wir die LARGE-Funktion zusammen mit der IF-Funktion in einer Array-Formel.
GROß WENN
Durch die Kombination von LARGE (oder SMALL) und IF in einer Array-Formel können wir im Wesentlichen eine "LARGE IF"-Funktion erstellen, die ähnlich wie die integrierte SUMIF-Formel funktioniert. Gehen wir ein Beispiel durch.
Wir haben eine Liste der Noten, die die Schüler in zwei verschiedenen Fächern erreicht haben:
Angenommen, wir werden gebeten, die drei besten Noten für jedes Fach wie folgt zu ermitteln:
Um dies zu erreichen, können wir eine IF-Funktion mit dem Gegenstand als unsere Kriterien innerhalb der LARGE-Funktion wie folgt:
=GROSS(WENN(=, ),)
=GROSS(WENN($C$2:$C$10=$F3,$D$2:$D$10),G$2)
Wenn Sie Excel 2022 und früher verwenden, müssen Sie die Formel eingeben, indem Sie drücken STRG + UMSCHALT + EINGABETASTE um die geschweiften Klammern um die Formel zu erhalten.
Wie funktioniert die Formel?
Die Formel funktioniert, indem jede Zelle in unserem Kriterienbereich als WAHR oder FALSCH bewertet wird.
Den besten Notenwert (k=1) in Mathe finden:
=GROSS(WENN($C$2:$C$10=$F3,$D$2:$D$10),G$2)
=GROSS(WENN({WAHR; FALSCH;FALSCH; WAHR; FALSCH; WAHR; FALSCH; WAHR; FALSCH}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Als nächstes ersetzt die IF-Funktion jeden Wert durch FALSE, wenn seine Bedingung nicht erfüllt ist.
=GROSS({0.81;FALSCH;FALSCH;0.42;FALSCH;0.63;FALSCH;0.58;FALSCH},1)
Jetzt überspringt die LARGE-Funktion die FALSE-Werte und berechnet den größten (k=1) der verbleibenden Werte (0,81 sind die größten Werte zwischen 0,42 und 0,81).
KLEIN WENN
Die gleiche Technik kann stattdessen auch mit der SMALL-Funktion angewendet werden.
=KLEIN(WENN($C$2:$C$10=$F3,$D$2:$D$10),G$2)
GROßES WENN mit mehreren Kriterien
Um LARGE IF mit mehreren Kriterien zu verwenden (ähnlich der Funktionsweise der integrierten SUMIFS-Formel), verschachteln Sie einfach weitere IF-Funktionen wie folgt in die LARGE-Funktion:
=GROSS(WENN(=, WENN(=, )),)
=GROSS(IF($D$2:$D$18=$H3,IF($B$2:$B$18=$G3,$E$2:$E$18)),I$2)
Eine andere Möglichkeit, mehrere Kriterien einzuschließen, besteht darin, die Kriterien miteinander zu multiplizieren, wie in diesem Artikel gezeigt
Tipps und Tricks:
- Referenzieren Sie nach Möglichkeit immer die Position (k) aus einer Hilfszelle und sperren Sie die Referenz (F4), da dies das automatische Ausfüllen von Formeln erleichtert.
- Wenn Sie Excel 2022 oder neuer verwenden, können Sie die Formel ohne Strg + Umschalt + Eingabe eingeben.
- Um die Namen der Schüler mit den besten Noten abzurufen, kombinieren Sie diese mit INDEX MATCH
