In einem vorangegangenen Beitrag hast du etwas über die Funktion QUANTIL erfahren können. Mit der Excel-Version 2010 kamen zwei neue Funktionen dazu, QUANTIL.INKL und QUANTIL.EXKL.
Dieser Beitrag wird sich diesen Funktionen widmen und die Unterschiede aufzeigen.
Diese Datentabelle hast du im letzten Beitrag verwendet, sie soll auch hier als Basis dienen, jetzt ist sie aber der Größe nach sortiert.
Berechne daraus jetzt die Funktionen QUANTIL, QUANTIL.INKL und QUANTIL.EXKL. Dies müssten die Ergebnisse sein:
Du siehst, der Median (50%-Quantil) ist in diesem Beispiel bei allen drei Funktionen identisch. Mit QUANTIL und QUANTIL.INKL errechnen sich auch gleiche 25%- und 75%-Quantile.
QUANTIL.EXKL weicht bei 25% und 75% ab.
Warum ist das so?
Die Excel-Hilfe ist hier keine wirkliche Hilfe, um die Unterschiede zu erklären. Dies ist zu lesen:
“QUANTIL.EXKL interpoliert, wenn der Wert für das angegebene Quantil zwischen zwei Werten im Array liegt. Wenn keine Interpolation für das Quantil möglich und k angegeben ist, gibt Excel den Fehlerwert #ZAHL! zurück.”
Damit ist nicht viel anzufangen. Wie rechnet Excel denn nun wirklich?
Das 25%-Quantil liegt zwischen dem 4. und 5. Wert in der sortierten Liste, das 75%-Quantil zwischen dem 11. und 12. Wert.
Für QUANTIL und QUANTIL.INKL könnte so gerechnet werden:
Beide Ergebnisse hast du oben mit der Funktion QUANTIL errechnet.
Für QUANTIL.EXKL könnte so gerechnet werden:
Zusammengefasst lässt sich für dieses Beispiel feststellen:
QUANTIL.INKL bildet Mittelwerte aus den jeweils blau und grün markierten Werten.
QUANTIL.EXKL wählt die blau markierten Werte aus.
Ist dies eine allgemein gültige Regel?
Nein! Ein anderes Beispiel, dass ich außerhalb des Beitrags gerechnet habe, bestätigt dies nicht ganz, aber fast. Dort wurde QUANTIL.INKL und QUANTIL.EXKL vermutlich durch weitere Interpolation ermittelt.
Nach welchen Regeln dies geschieht, war mir nicht möglich, herauszufinden.
Dennoch könnte der Beitrag helfen, die neuen Funktionen etwas mehr zu verstehen.
Ich bin gespannt auf deinen Kommentar. Schreib doch bitte auf, wenn du mehr Hintergründe kennst. Es würde sicherlich viele Leser auch interessieren.
Entdecke mehr von Clevercalcul
Subscribe to get the latest posts sent to your email.
Hallo, ich habe mich mit der Frage auch schon länger herumgeschlagen und folgende Antwort herausgefunden:
QUANTIL.INKL
Die kleinste und größte Beobachtung werden als absolutes Minimum (Index 0) bzw. Maximum (Index n-1) betrachtet. Dazwischen liegen n-1 Intervalle. Für ein alpha Quantil wird interpoliert zwischen den Beobachtungen an den Indices x=abgerundet[(n-1)*a] und x+1. Das Quantil ergibt sich als Daten[x]*(1-((n-1)*a-x)) +Daten[(x+1)]*((n-1)*a-x)=Daten[x]+(Daten[x+1]-Daten[x])*((n-1)*a-x)
QUANTIL.EXKL
Das absolute Minimum (Index 0) und das absolute Maximum (Index n+1) werden nicht beobachtet. Dazwischen liegen n+1 Intervalle. Für ein alpha Quantil wird interpoliert zwischen den Beobachtungen an den Indices x=abgerundet[(n+1)*a] und x+1. Das Quantil ergibt sich als Daten[x]*(1-((n+1)*a-x)) + Daten[x+1]*((n+1)*a-x)=Daten[x]+(Daten[x+1]-Daten[x])*((n+1)*a-x)
BEISPIEL: Die Zahlen von 1 bis 10
Alpha=0,01
QUANTIL.INKL
x=abgerundet[(10-1)*0,01]=abgerundet[0,09]=0
y=…=1
q=Daten[0]*(1-0,09)+Daten[1]*(0,09)=1*0,91+2*0,09=1,09
QUANTIL.EXKL
x=abgerundet[(10+1)*0,01]=abgerundet[0,11]=0
y=…=1
q=Daten[0]+… -> Fehler #ZAHL! , da keine Daten zum Index 0 definiert
Alpha=0,1
QUANTIL.INKL
x=abgerundet[(10-1)*0,1]=abgerundet[0,9]=0
y=…=1
q=Daten[0]*(1-0,9)+Daten[1]*(0,9)=1*0,1+2*0,9=1,9
QUANTIL.EXKL
x=abgerundet[(10+1)*0,1]=abgerundet[1,1]=1
y=…=2
q=Daten[1]*(1-((10+1)*0,1-1))+Daten[2]*((10+1)*0,1-1)=1*(1-(1,1-1))+2*(1,1-1)=1*0,9+2*0,1=1,1
Für gewöhnlich unterscheidet man 9 verschiedene Arten, ein Quantil zu berechnen:
z.B. http://mathworld.wolfram.com/Quantile.html
QUANTIL.INKL entspricht Q7
QUANTIL.EXKL entspricht Q6, sog. Weibull Quantil
Hallo, da danke ich Dir sehr für Deine umfangreiche Erklärung. Dein Kommentar wird anderen Lesern die Funktionen ganz sicher etwas näher bringen, so wie mir. Vielen Dank
Gerhard
Gern geschehen. Leider beschränken sich die Kommentare auf Text. Darunter leidet die Darstellung. LG Rainer