Die Formeln KGRÖSSTE-WENN & KKLEINSTE-WENN – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie die Kombinationen KGRÖSSTE-WENN oder KKLEINSTE-WENN berechnen und damit den n-ten größten (oder kleinsten) Wert basierend auf Kriterien ermitteln können.

Die Funktionen KGRÖSSTE & KKLEINSTE

Die KGRÖSSTE-Funktion wird verwendet, um den n-ten größten Wert (k) in einer Matrix zu berechnen, während die KKLEINSTE-Funktion den kleinsten n-ten Wert zurückgibt.

=KGRÖSSTE($D$2:$D$10;1)

Um eine KGRÖSSTE-WENN-Kombination zu erstellen, verwenden wir die KGRÖSSTE-Funktion zusammen mit der WENN-Funktion in einer Array-Formel.

KGRÖSSTE-WENN

Durch die Kombination von KGRÖSSTE (oder KKLEINSTE) mit WENN in einer Array-Formel können wir im Wesentlichen eine „KGRÖSSTE-WENN“-Funktion erstellen, die ähnlich wie die integrierte SUMMEWENN-Funktion funktioniert. Lassen Sie uns ein Beispiel durchgehen.

Wir haben eine Liste von Noten, die von Studenten in zwei verschiedenen Fächern erreicht wurden:

Angenommen, wir werden gebeten, die drei besten Noten für jedes Fach wie folgt zu ermitteln:

Um dies zu erreichen, können wir eine WENN-Funktion mit dem Fach als Kriterium innerhalb der KGRÖSSTE-Funktion folgendermaßen verschachteln:

=KGRÖSSTE(WENN(<Kriterienbereich>=<Kriterien>; <Wertebereich>);<Position>)

=KGRÖSSTE(WENN($C$2:$C$10=$F3;$D$2:$D$10);G$2)

Wenn Sie Excel 2019 oder eine ältere Version verwenden, müssen Sie die Formel durch Drücken von STRG + UMSCHALTTASTE + ENTER eingeben, um die geschweiften Klammern um die Formel herum zu erhalten.

Wie funktioniert die Formel?

Die Formel funktioniert, indem sie jede Zelle in unserem Kriterienbereich als WAHR oder FALSCH auswertet.

Die Suche nach dem höchsten Wert (k=1) in Mathe:

=KGRÖSSTE(WENN($C$2:$C$10=$F3;$D$2:$D$10);G$2)

=KGRÖSSTE(WENN({WAHR; FALSCH;FALSCH; WAHR; FALSCH; WAHR; FALSCH; WAHR; WAHR; FALSCH}; {0.81; 0.8; 0.93; 0.42; 0.87; 0.63; 0.71; 0.58; 0.73}); 1)

Anschließend ersetzt die WENN-Funktion jeden Wert durch FALSCH, wenn die Bedingung nicht erfüllt ist.

=KGRÖSSTE({0.81;FALSCH;FALSCH;0.42;FALSCH;0.63;FALSCH;0.58;FALSCH};1)

Nun überspringt die Funktion KGRÖSSTE die FALSCH-Werte und berechnet den größten (k=1) der verbleibenden Werte (0,81 ist der größte Wert zwischen 0,42 und 0,81).

KKLEINSTE-WENN

Die gleiche Technik kann stattdessen auch mit der KKLEINSTE-Funktion angewendet werden.

=KKLEINSTE(WENN($C$2:$C$10=$F3;$D$2:$D$10);G$2)

KGRÖSSTE-WENN mit mehreren Kriterien

Um KGRÖSSTE-WENN mit mehreren Kriterien zu verwenden (ähnlich wie die eingebaute SUMMEWENNS-Formel), verschachteln Sie einfach mehrere WENN-Funktionen innerhalb der KGRÖSSTE-Funktion wie folgt:

=KGRÖSSTE(WENN(<Kriterienbereich1>=<Kriterium1>; WENN(<Kriterienbereich2>=<Kriterium2>; <Wertebereich>));<Position>)

=KGRÖSSTE(WENN($D$2:$D$18=$H3;WENN($B$2:$B$18=$G3;$E$2:$E$18));I$2)

Eine andere Möglichkeit, mehrere Kriterien einzubeziehen, besteht darin, die Kriterien miteinander zu multiplizieren, wie im Artikel über die Berechnung von MEDIAN-WENN gezeigt.

Tipps und Tricks:

• Wenn möglich, verweisen Sie immer auf  eine Hilfszelle mit der Position (k) und sperren Sie den Bezug (F4), da dies das automatische Ausfüllen von Formeln erleichtert.
• Wenn Sie Excel 2019 oder eine neuere Version verwenden, können Sie die Formel ohne STRG + UMSCHALTTASTE + EINGABE eingeben.
• Um die Namen der Studenten, die die besten Noten erzielt haben, abzurufen, kombinieren Sie dies mit INDEX/VERGLEICH

 

KGRÖSSTE-WENN & KKLEINSTE-WENN – Google Sheets

Diese Formeln funktionieren in Google Sheets genau so wie in Excel.