Rechnet die Excel-Funktion QUANTIL richtig?

Zur Problematik der Berechnung von Quantilen gab es im Blog bisher zwei Beiträge:

– Die statistische Excel-Funktion QUANTIL
– Die statistischen Funktionen QUANTIL.INKL und QUANTIL.EXKL

Desweiteren spielte das Quantil auch in diesem Beitrag eine Rolle:

– Wie du einen Ausreißer in deiner Datenreihe findest

Weshalb schreibe ich jetzt noch einen Beitrag?

Bei den Lesern kamen z.T. kritische Bemerkungen an der Richtigkeit der Ergebnisse der Excel-Funktionen auf.

Teilweise haben die Leser den Begriff Quartil verwendet, ich bleibe aber bei Quantil.

Deshalb habe ich nach einer Erklärung gesucht, warum Excel so, wie es rechnet, rechnet.

Und schließlich will ich aufzeigen, dass Excel eben doch richtig rechnet.

1. Was ist ein Quantil und was berechnet es?

Wikipedia [1] definiert ein Quantil so:

“Ein Quantil ist ein Lagemaß in der Statistik. Anschaulich ist ein Quantil ein Schwellenwert:
ein bestimmter Anteil der Werte ist kleiner als das Quantil, der Rest ist größer. Das 25-%-Quantil
beispielsweise ist der Wert, für den gilt, dass 25 % aller Werte kleiner sind als dieser Wert.”

Auf dem Blog “Crashkurs-Statistik” [2]heißt es:

“Quantile (oder genauer gesagt p -Quantile) sind Werte, die eine Menge von n Datenpunkten
in zwei Teile spalten, und zwar so, dass mindestens ein Anteil p kleiner oder gleich dem p -Quantil ist,
und mindestens ein Anteil 1−p größer oder gleich dem p -Quantil.”

Was berechnet wird, sagen schon die Definitionen.

Mit dem 25%-Quantil wird die Datenreihe in zwei Teile gespalten.
Der erste Teil (25%) umfasst die Werte, die kleiner oder gleich dem Quantil sind.

Der zweite Teil (75%) umfasst die Werte, die größer als das Quantil sind.

Das 50%-Quantil entspricht dem Median.

2. Die übliche Berechnungsweise

Ein Quantil kann theoretisch mit jedem Prozentwert (Alpha) berechnet werden.
Im Beitrag soll es ausschließlich um das 25%-, das 50%- und das 75%-Quantil gehen.

Erstelle Dir dazu eine Liste mit einer geraden Anzahl an Zahlenwerten.

25%-Quantil

Der 6. Wert ist 11, der 7. Wert ist 14.

Aus beiden Werten wird der Mittelwert gebildet.

=MITTELWERT(B8:B9)

=12,5

Warum wird der Mittelwert genommen?

Dazu heißt es auf crashkurs-Statistik  [2]:

“das Quantil liegt theoretisch „irgendwo“ zwischen dem ersten und zweiten Punkt. In der Praxis wird
meist der Mittelwert zwischen den beiden Punkten genommen.”

Aha, irgendwo dazwischen. Es könnten demnach auch z.B. die Werte 11,05 / 11,1 / 11,15 usw. bis 13,95 sein.
Der Mittelwert ist die einfachste Lösung und wird deshalb wohl gewählt.
50%-Quantil

Der 12. Wert ist 20, der 13. Wert ist 27.

Aus beiden Werten wird der Mittelwert gebildet.

=MITTELWERT(B14:B15) =MEDIAN(B3:B26)

=23,5

75%-Quantil

Der 18. Wert ist 44, der 19. Wert ist 54.

Aus beiden Werten wird der Mittelwert gebildet.

=MITTELWERT(B20:B21)

=49,0

3. Die Excel-Lösung

Mit Excel verwendest Du die Funktion QUANTIL.

Die Syntax lautet:  QUANTIL(Matrix;k)

Dabei gilt laut Excel-Hilfe:

• Matrix Erforderlich. Eine Matrix oder ein Datenbereich, die/der die relative Lage der Daten beschreibt
• Alpha Erforderlich. Der Quantilwert aus dem geschlossenen Intervall von 0 bis 1

Mit dieser Funktion berechnest Du nun die gefragten Quantile.

Die Werte weichen von denen nach der üblichen Methode berechneten ab.

Warum ist das so?
Excel arbeitet nach einem Interpolationsverfahren, der “Linearen Interpolation”.
Das Verfahren an sich wird z.B. auf dieser Seite [3] erläutert.

Worum geht es dabei?
Gesucht ist ein Schätzwert für y bei gegebenem Wert x, der nicht in der Tabelle steht.

Dazu werden die beiden Tabellenwerte x1, x2, die x umgeben (also x1 ≤ x ≤ x2) und die zugehörigen Werte y1, y2 gesucht.

Die y-Werte werden schließlich mit dieser Formel berechnet:

Für das 25%-Quantil soll nun ein x-Wert zwischen dem 6. und 7. Wert und der passende y-Wert gefunden werden.

Erstelle Dir dazu diese Hilfstabelle

Als (nicht vorhandene) x-Werte wählst Du Werte, die >6 und <7 sind.
Der Einfachheit halber bin ich im Beispiel in 0,05-Schritten vorgegangen.

Gib in Zelle F3 diese Formel ein:

=B$8+((B$9-B$8)/(A$9-A$8))*(D3-A$8)

und ziehe sie bis F21 herunter.

In F17 findest Du das mit der Funktion berechnete 25%-Quantil (13,25).
Der zugehörige x-Wert ist 6,75 – also ein Wert, der irgendwo zwischen 6 und 7 liegt.

Das 50%-Quantil entspricht dem Median, 23,50.

Für das 75%-Quantil soll nun ein x-Wert zwischen dem 18. und 19. Wert und der passende y-Wert gefunden werden.

Erstelle Dir dazu diese Hilfstabelle:

Als (nicht vorhandene) x-Werte wählst Du Werte, die >18 und <19 sind.
Der Einfachheit halber bin ich auch hier in 0,05-Schritten vorgegangen.

Gib in Zelle J3 diese Formel ein:

=B$20+((B$21-B$20)/(A$21-A$20))*(H3-A$20)

und ziehe sie bis J21 herunter.

In J7 findest Du das mit der Funktion berechnete 75%-Quantil (46,50).
Der zugehörige x-Wert ist 18,25 – also ein Wert, der irgendwo zwischen 18 und 19 liegt.

4. Schlußfolgerung

Für das 25%-Quantil gilt, sowohl nach der üblichen Berechnung als auch nach der Excel-Berechnung, dass das 25% der Werte (6 von 24) kleiner oder gleich dem Quantil sind.

Es gilt ebenso, dass 75% der Werte (18 von 24) größer oder gleich dem Quantil sind.

Für das 75%-Quantil gilt, sowohl nach der üblichen Berechnung als auch nach der Excel-Berechnung, dass das 75% der Werte (18 von 24) kleiner oder gleich dem Quantil sind.

Es gilt ebenso, dass 25% der Werte (6 von 24) größer oder gleich dem Quantil sind.

Des Weiteren ist eine Forderung, dass die Sprünge zwischen den x-Werten der Quantile gleich sein sollen.

Eine kleine Tabelle zeigt, dass es so ist:

Damit wäre wohl widerlegt, dass Excel falsch rechnet.

Quellen:

[1] https://de.wikipedia.org/wiki/Quantil_(Wahrscheinlichkeitstheorie)

[2] https://www.crashkurs-statistik.de/quantile/

[3] http://www.peter-junglas.de/fh/vorlesungen/thermodynamik1/html/app-a.html