QUANTIL-WENN-Formel – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie Berechnungen mit QUANTIL-WENN durchführen können, d. h. das k-te Perzentil in einem Wertebereich mit Kriterien ermitteln.

Die QUANTIL-Funktion

Die QUANTIL-Funktion wird verwendet, um das k-te Perzentil der Werte in einem Bereich zu berechnen, wobei k der Perzentilwert zwischen 0 und einschließlich 1 darstellt.

=QUANTIL($D$2:$D$10;0,75)

Um ein „QUANTIL-WENN“ zu erstellen, verwenden wir die QUANTIL-Funktion zusammen mit der WENN-Funktion in einer Array-Formel.

PERZENTIL-WENN

Durch die Kombination von QUANTIL und WENN in einer Array-Formel können wir im Wesentlichen eine „PERZENTIL-WENN“-Funktion erstellen, die ähnlich wie die integrierte NITTELWERTWENN-Funktion funktioniert.

In diesem Beispiel haben wir eine Liste von Punkten, die von Schülern in zwei verschiedenen Fächern erreicht wurden:

Wir möchten die 75. Perzentile der in den einzelnen Fächern erzielten Punktzahlen ermitteln, und zwar folgendermaßen:

Um dies zu erreichen, können wir eine WENN-Funktion mit dem Fach als Kriterium in die QUANTIL-Funktion folgendermaßen einfügen:

=QUANTIL(WENN(<Kriterienbereich>=<Kriterien>; <Wertebereich>);<Perzentil>)

=QUANTIL(WENN($C$2:$C$10=$F3;$D$2:$D$10);0,75)

Wenn Sie Excel 2019 oder eine frühere Version verwenden, müssen Sie die Array-Formel durch Drücken von STRG + UMSCHALTTASTE + ENTER (statt nur ENTER) eingeben, um Excel mitzuteilen, dass es sich bei der Formel um eine Array-Formel handelt. Dass es sich um eine Array-Formel handelt, erkennen Sie an den geschweiften Klammern, die um die Formel herum erscheinen (siehe oberes Bild).

Wie funktioniert die Formel?

Die Wenn-Funktion wertet jede Zelle in unserem Kriterienbereich als WAHR oder FALSCH aus und erstellt zwei Arrays:

=QUANTIL(WENN({WAHR; FALSCH;FALSCH; WAHR; FALSCH; WAHR; FALSCH; WAHR; WAHR; FALSCH}; {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}); 0,75)

Als nächstes erstellt die WENN-Funktion ein einzelnes Array, wobei jeder Wert, bei dem die Bedingung nicht erfüllt ist,  durch FALSCH ersetzt wird.

=QUANTIL({0,81;FALSCH;FALSCH;0,42;FALSCH;0,63;FALSCH;0,58;FALSCH}; 0,75)

Jetzt überspringt die QUANTIL-Funktion die FALSCH-Werte und berechnet das 75. Perzentil der übrigen Werte (0,72 ist das 75. Perzentil).

PERZENTIL-WENN mit mehreren Kriterien

Um PERZENTIL-WENN mit mehreren Kriterien zu berechnen (ähnlich wie bei der eingebauten Funktion MITTELWERTWENNS), können Sie die Kriterien einfach miteinander multiplizieren:

=QUANTIL(WENN((<Kriterienbereich1>=<Kriterium1>)*(<Kriterienbereich2>=<Kriterium2>);<Wertebereich>);<Perzentil>)

=QUANTIL(WENN(($D$2:$D$10=$H2)*($C$2:$C$10=$G2);$E$2:$E$10);0,75)

Eine andere Möglichkeit, mehrere Kriterien einzubeziehen, besteht darin, mehrere WENN-Anweisungen in die Formel einzuschachteln.

Tipps und Tricks:

• Wenn möglich, verweisen Sie immer auf die Position (k) mit einer Hilfszelle 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 + ENTER eingeben.
• Um die Namen der Schüler, die die besten Noten erzielt haben, abzurufen, kombinieren Sie dies mit INDEX-VERGLEICH.

PERZENTIL-WENN in Google Sheets

Die QUANTIL-WENN-Formel funktioniert in Google Sheets genauso wie in Excel mit der Ausnahme, dass Sie die Formel in die ARRAYFORMULA-Funktion packen müssen, um Google Sheets mitzuteilen, dass es sich um eine Array-Formel handelt.