Excel Quantile berechnen: Perzentile, Dezile und Co und der Unterschied zwischen .exkl und .inkl

Zur Berechnung von Quantilen in Excel gibt es viele Missverständnisse. Quartile lassen sich mit der entsprechenden Formel =quartile leicht berechnen. Allerdings gibt es seit 2010 dort zwei Varianten. Noch unklarer ist die Berechnung von Dezilen oder Perzentilen. Hier liest man manchmal, das sei nur auf dem Umweg über eine Quartilsberechnung möglich. Was allerdings falsch ist.

Zur Erinnerung: Was sind Quantile

Zur Erinnerung: Quantile unterteilen einen Datensatz in mehrere möglichst gleich große Teilgruppen. Bei einem Quartil (Quartil mit r, nicht mit n wie Quantil) sind das vier gleich große Gruppen, bei einem Quintil fünf, bei einem Dezil zehn und so weiter.

Die Quantilswerte sind jeweils die Grenze. Deshalb gibt es bei einem Quartil drei Werte, nämlich die Grenze vom unteren Viertel zum zweiten, vom zweiten zum dritten und vom dritten zum obersten.

Beispielsweise lässt sich mithilfe der Perzentile berechnen, ab welchem Einkommen jemand zum obersten 1,0 Prozent der Bevölkerung gehört. Der Ökonom Thomas Piketty hält solche Werte für deutlich besser, um beispielsweise die Entwicklung der Ungleichheit zu beschreiben als synthetische Indikatoren wie den Gini-Koeffizienten.

Quantile mit Excel rechnen

Es gibt viel Unwissen im Netz über die Berechnung von Quantilen mit Excel. Chip, der erste Treffer bei meiner Recherche, behauptet in einem Praxistipp beispielsweise, nur Quartile ließen sich problemlos in Excel berechnen, Perzentile dagegen sich nur als Bruchteile von Quartilen berechnen.

Der Beitrag enthält aber gleich mehrere Fehler. Wahr ist nur, dass es neben der für alle Quantile geltende Formel eine besondere Formel für Quartile gibt. Davon abgesehen ist so ziemlich alles falsch oder veraltet. So wird dort die veraltete Formel =quartile(;) verwendet statt der aktuellen Formen =quartile.ink(;) und =quartile.exkl(;). Beide neue Formeln berechnen Quartile, auf den Unterschied in der Berechnung komme ich gleich zu sprechen.

Außerdem ist es falsch, dass man einfach einen Bruchteil eines Quartils berechnen müsse. Der Gedanke ist naheliegend, dass das 1. Quartil das 25. Perzentil sei und man daher in der Formel statt einer 1 eine 1/25 schreiben müsse. Funktioniert aber nicht.

Formeln Quartile und Quantile

Wer Quantile berechnen will, muss sich zunächst klar darüber werden, wie er seine Daten unterteilen will. Will ich gleich große Gruppen bilden, bei Quartilen also vier Gruppen mit je gleich vielen Fällen? Dann brauche ich die Formel mit .exkl. Oder will ich das ein Viertel der Fälle unter dem ersten Quartil liegt und drei Viertel darüber? Dann brauche ich .inkl.

Aber warum ist das überhaupt ein Unterschied? Weil die Quantilswerte selbst ja keiner Gruppe angehören. Sehen wir uns das an einem Beispiel an.

Schauen wir uns das mal genauer an

Ich habe ein Beispiel mit 13 Werten gewählt. Die oberste Zeile ist die laufende Nummerierung, die untere mein eigentlicher Wert. Sagen wir mal, es ist die Zahl der Fehler in einer Klausur. Die Daten sind, wie man sieht sortiert, vom besten Ergebnis (zwei Fehler) bis zum schlechtesten (17 Fehler).

Wir sehen, dass wir vier Gruppen mit je drei Werten haben. Unser erstes Quartil liegt zwischen dem dritten und dem vierten Wert, wir nehmen deshalb die Mitte aus beiden Werten, also 5. Wie wir das bei größeren Datenmengen von Hand berechnen, erkläre ich unten. Wir haben also vier Gruppen mit je drei Werten, wobei die Quartile selbst nicht mit dabei sind.

Die Bedingung, dass drei Viertel der Werte über dem 1. Quartil liegen sollen gilt nur, wenn wir die Quartilsgrenzen – hier nur der siebte Wert – nicht mitzählen, ihn also exkludieren. Deshalb heißt die Formel in Excel auch =quantil.exkl.

Wenn ich Quartile habe, kann ich sowohl die Formel quantil.exkl als auch quartile.exkl (mit r und einem e vor dem Punkt) benutzen, bei allen anderen Quantilen nur quantil.exkl. Wie genau das geht, erkläre ich gleich.

Die Formel =quantil.inkl

Das sieht auf den ersten Blick ganz gut aus, hat aber auch einen Nachteil. Jetzt liegen drei Werte unter meinem ersten Quartal und zehn darüber, also mehr als dreimal so viele. Zumindest, wenn ich den siebten Wert inkludieren.

Nehme ich statt der Mitte aus dem dritten und vierten Wert den vierten Wert selbst als 1. Quartil, liegen dagegen genau drei Werte darunter und neun darüber, also ganz genau dreimal so viele. Richtig, die Formel für diesen Ansatz heißt in Excel deshalb =quantil.inkl beziehungsweise – wieder nur bei Quartilen – quartile.inkl.

quartile.inkl oder quartile.exkl – Welche Formel ist die bessere?

Ich muss mich also entscheiden, welchen Ansatz ich wähle. Bei 13 Werten ist der Unterschied nicht so groß, hätten wir elf, wäre der dritte Wert unser 1. Quartil. Dann wäre das Missverhältnis mit der ersten Formel noch extremer, es lägen mit der ersten Formel zwei Werte unter dem 1. Wert und acht darüber, also dreimal so viele.

Dafür sind meine einzelnen Gruppen jetzt unterschiedlich groß. Mein 1. Quartil ist der 4. Wert, das 2. Quartil(der Median) ist der 7. Wert und das 3. Quartil ist der 10. Wert. Meine zweite Gruppe umfasst also nur zwei Personen, ebenso die vierte.

Excel hat in der alten Variante übrigens ausschließlich die zweite Variante (=quantil.inkl beziehungsweise =quartile.inkl) verwendet. Mir persönlich ist diese Variante auch näher.

Es hängt auch ein bisschen davon ab, was man ausweisen möchte. Wollen wir eine Aussage treffen wie „Das reichste Prozent (also das reichste Perzentil) der Bevölkerung hat einen Anteil von … am Einkommen“, dann ist die Formel quantil.inkl intuitiver, stelle ich alle 99 Perzentile dar ist es schwieriger sich zu entscheiden.

„Risikohinweis!“

Ich muss zur Sicherheit sagen, dass ich mich hier nicht auf offizielle Angaben von Microsoft stütze. Die Hilfe macht nämlich zum Unterschied zwischen den Varianten mit .exkl und .inkl keine Angaben. Aber ich habe meine Hypothese, die ich hier dargestellte habe, natürlich mehrfach getestet. Mehr dazu im Kapitel „Für alle, die es genau wissen wollen“.

Quartile und Quantile in Excel berechnen

Wir müssen jetzt nur noch eine Kleinigkeit klären, nämlich welche Daten in die Formel eingegeben werden müssen.

Die Formel für Quartile

Bei Quartilen ist die Sache ganz einfach. Zunächst wird der Datenbereich markiert. Stehen meine Daten in den Zellen A2 bis A14 beginnt meine Formel also mit =quartile.inkl(A2:A14;…) – oder =quartile.exkl(A2:A14;…), wenn ich die erste der oben vorgestellten Varianten verwenden will.

Aber was seht nach dem Strichpunkt? Das ist ganz einfach. Suche ich das erste Quartil eine 1, für das zweite eine 2 und für das dritte eine 3. Bei der alten Formel =quartile(…;…) – also ohne den Zusatz .inkl oder .exkl – gibt es noch die Möglichkeit eine 0 für das Minimum und eine 4 für das Maximum einzugeben. Bei der neuen Formel gibt es das nicht mehr, aber dafür gibt es ja die Formeln =max() und =min().

Die Formel für Quantile

Bei den Formeln für Quantile ist es etwas schwerer, aber nicht viel. Auch hier muss ich den Datenbereich angeben. Hinter dem Strichpunkt schreibt man am einfachsten einen Bruch. Oben im Bruch steht das gesuchte Quantil, also das erste, zweite und so weiter.

Unter dem Bruch steht die Anzahl der Gruppen, also 4 bei Quartilen, 5 bei Quintilen und so weiter. Suche ich das erste Quartil steht da also 1/4, für das dritte Dezil 3/10 und für das 99. Perzentil 99/100. Natürlich kann man auch eine Dezimalzahl schreiben, statt 1/4 also 0,25. Aber ich finde einen Bruch einfacher.

Stellen wir uns eine ganz einfache Datenreihe vor. Sie besteht aus elf Zahlen von 1 bis 11. Nehmen wir einfach an, das wäre das Taschengeld von Kindern, vom Kind mit dem niedrigsten bis zum Kind mit dem höchsten Taschengeld. Die linke Spalte ist die laufende Nummer, die mittlere der Name und die rechte Spalte das Taschengeld. Hans und Hermine bekommen also kein Taschengeld, Marianne dagegen 10,- Euro.

Ein Beispiel

Wir haben einen Datensatz der von A1 bis A2524 reicht. Weil ich Perzentile berechnen will, brauche ich die Formel die mit =quantil. beginnt. Zuerst muss ich mich für eine Variante entscheiden. Ich finde die zweite Variante intuitiver bei der ein
Hundertstel der Daten über dem 99. Perzentil liegt und 99 Hundertstel darunter. Also verwende ich die Formel =quantil.inkl(). Ich interessiere mich aktuell nur für das 99. Perzentil.

Weil die Daten in den Zellen A1 bis A2524 stehen, schreibe ich
=quantil.inkl(A1:A2524;99/100). Würde ich das erste suchen, stände da
=quantil.inkl(A1:A2524;1/100), interessierte ich mich für das zweite Quartil
=quantil.inkl(A1:A2524;2/4). Oder im letzten Fall auch =quartile.inkl(A1:A2524;2).

Für alle die es ganz genau wissen wollen

Wie gesagt macht Microsoft keine Angaben zur Berechnung. Wie habe ich also untersucht, wie die Formel funktionieren und welche mathematischen Formeln stehen hinter den Excel-Formeln?

Hier haben wir eine Tabelle, die praktischerweise schon sortiert ist. Das ist sinnvoll, wenn ich die Berechnung händisch durchführen will.

lfd. Nr.Name des KindesTaschengeld
1Hans0,-
2Hermine0,-
3Xaver1,-
4Beat2,-
5Hildegard3,-
6Clotilde3,50
7Tusnelda4,-
8Ambrosius4,-
9Sigismund5,-
10Helene 7,-
11Marianne10,-

Was rechnet Excel bei den Formel =quantil.exkl und =quartile.exkl?

Berechnen wir zunächst mal Quartile für die Formeln mit dem Zusatz .exkl, das ist einfach. Oder noch einfacher, erst den Median, also den Wert, der unsere Gruppe in genau zwei gleich große Gruppen teilt. Bei 11 Werten ist deshalb der sechste Wert der Median, fünf Werte sind kleiner, fünf sind größer. Dafür können wir auch die Median-Formel für ungerade Werte nutzen, nämlich (n+1)/2, also (11+1)/2 = 6.

Natürlich ist nicht 6 unser Median, sondern der sechstgrößte Wert, also Clotilde mit 3,50 Euro (unsere Daten sind ja sortiert, sonst geht das nicht so schön).

Quartile in Excel berechnen

Unser 1. Quartil ist ja wiederum die Hälfte der unteren Hälfte. Also teilen wir noch einmal durch zwei. Oder anders ausgedrückt: Wir teilen die Zahl durch 4 statt durch 2. Unsere Formel für das 1. Quartil ist also (11+1)/4 = 3.

Für den zweiten und dritten Wert multiplizieren wie die Position jetzt einfach mit 2 oder 2. Das zweite Quartil (Median) ist also der sechste, das dritte der neunte Wert. Also 3,50 Euro und 5,- Euro.

Die Formel allgemein formuliert

Für unterschiedliche Quantile lautet die Rechenformel, die hinter den Excel-Formeln mit dem Zusatz .exkl steht also

(n+1)/z*q

Dabei ist

  • n = die Fallzahl, in unserem Fall also 11,
  • z = die Zahl der Gruppen, bei Quartilen also 4, bei Perzentilen 100 und so weiter,
  • q = das Quantil, also 1 für das erste Quantil, 2, für das zweite und so weiter. Wollen wir das dritte Quartil wissen, müsste hier eine 3 stehen.

Nicht vergessen: Das Ergebnis gibt die Position unseres Quantil an, wir müssen bei der Rechnung von Hand erst in der Liste nachsehen. Rechnen wir mit Excel, wird uns direkt der Wert angegeben. Für Excel müssen wir die Daten auch nicht sortieren, das macht Excel selbst.

Jetzt bleiben noch zwei Fragen: Was macht Excel, wenn es nicht so schön aufgeht und wie rechne ich bei geraden Zahlen.

Was, wenn das Ergebnis eine Kommazahl ist?

Natürlich habe ich das Beispiel mit elf Werten ausgesucht, weil (11+1)/4 genau 3 ergibt. Was aber, wenn wir beispielsweise 13 Werte hätten? Dann ist unser Ergebnis (13+1)/4 = 3,5.

lfd. Nr.Name des KindesTaschengeld
1Hans0,-
2Hermine0,-
3Xaver1,-
4Beat2,-
5Hildegard3,-
6Clotilde3,50
7Tusnelda4,-
8Ambrosius4,-
9Sigismund5,-
10Helene 7,-
11
Gesine8,-
12Giesbert9,-
13Marianne10,-

Die Antwort liegt auf der Hand, wir nehmen einfach die Mitte aus dem 3. und dem 4. Wert. Oder anders ausgedrückt: Wir berechnen die Differenz vom 3. zum 4. Wert, multiplizieren sie mit 0,5 und addieren das Ergebnis zum dritten Wert. Diese zweite Betrachtungsweise hat den Vorteil, dass sie analog auch für alle anderen Kommazahlen funktioniert.

Am Beispiel oben: Unser dritter Wert ist Xaver mit 1,- Euro Taschengeld, der vierte Beat mit 2,- Euro. Die Mitte zwischen beiden ist 1,50 Euro. Hätten wir Quintile, wäre unser erstes Quintil (13+1)/5 = 2,8. Dann müssen wir den zweiten, etwas umständlicheren Berechnungswert wählen. Wir nehmen den 2. Wert, weil unsere Zahl vor dem Komma die 2 ist, also 0,- Euro. Hinter dem Komma steht eine 0,8, daher nehmen wir die Differenz zum nächstgrößeren Wert und rechnen sie mal 0,8. Weil der nächstgrößere Wert 1,- Euro ist und die Differenz von 0,- Euro zu 1,- Euro – wie jeder ziemlich leicht ausrechnen kann – genau 1,- Euro, rechnen wir 0,- Euro + (1,- Euro * 0,8), was 0,80 Euro ist. Unser erstes Quintil ist also 0,8.

Kleiner Test in Excel mit der Formel =QUANTIL.EXKL(A2:A14;1/5) für unsere Qunitilsberechnung: Excel kommt zum gleichen Ergebnis.

Die weiteren Werte sind ganz einfach zu bestimmen, sie sind immer Vielfache. Für das 1. Quartil haben wir 3,5 (also die Mitte aus dem 3. und dem 4. Wert), dann bekommen wir für das 2. Quartil genau den 7. Wert (=2*3,5) und für das 3. Quartil 3*3,5 =10,5 – also die Mitte aus dem 10. und dem 11. Wert.

Wie funktioniert das bei geraden Zahlen?

Das funktioniert analog für jede andere Kommazahl. Beim Median haben wir ja bekanntlich für eine gerade Zahl von Fällen eine andere Formel. Statt (n+1)/2 rechnen wir dort mit n/2 den Untermedian aus und mit n/2 + 1 den Obermedian.

Aber das stört uns nicht, denn wenn wir bei Kommazahlen wie soeben erklärt vorgehen, kommen wir genau zum gleichen Ergebnis. Wenn wir bei 100 Werten (100+1)/2 rechnen kommen wir auf 50,5. Nehmen wir nun den 50. Wert und addieren noch 0,5 * die Differenz zum 50. zum 51. Wert erhalten wir das gleiche Ergebnis, wie wenn wir n/2 = 50 rechnen und dann das arithmetische Mittel aus diesem Wert und dem n/2 + 1 = 51. Wert nehmen.

Die Formeln quantil.inkl und quartile.inkl

Wie gesagt bekommen wir bei der oben stehenden Berechnung und mit der Formel quartile.exkl(…;1) beziehungsweise quintil.exkl(…;1/4) auf einen Wert von 1,50 Euro für das erste Quartil.

Die Formel =quantil.inkl(;) kommt dagegen auf 2,00 Euro statt der 1,50 Euro der Formeln mit .exkl, nimmt also genau den vierten Wert. Denn darunter liegen genau drei Werte, darüber neun. Aber wir rechnet Excel hier?

Wie rechnet Excel bei quantil.exkl(;)?

Bei der Formel =quartile.inkl(;) ist es etwas komplizierter. Kurz gesagt rechnen wir hier nicht n+1, sondern n+3. Warum 3? Weil wir drei Quartile haben. Wir addieren also immer eine Zahl weniger als wir Gruppen haben bei Dezilen (10 Gruppen) also +1, bei Perzentilen (100 Gruppen) +99 und so weiter.

Wir rechnen für das erste Quartil deshalb (n+1)/4 sondern (n+3)/4. n ist in unserem Beispiel ja 13, also (13+4-1)/4 = 16/4 = 4.

Leider ist die Position des zweiten Quartils nicht einfach das Doppelte des ersten Quartils, wie das bei der Formel für =quantil.exkl der Fall ist. Vielmehr müssen wir die Zahl, die wir addieren, noch die Nummer unseres Quartils teilen, beim 2. Quartil also durch 2 und beim 3. Quartal 3. Erst danach multiplizieren wir sie. Wir rechnen also für das 3. Quartil (das zweite ist zu einfach, weil der Median)

(13+4/3-1)/4*3

Oder etwas allgemeiner ausgedrückt

(n+(z-1)/qp)/z*qp

wobei gilt

n = Anzahl der Fälle

z = Anzahl der Gruppen in die wir unterteilen, also bei Quartilen 4

qp = die Postion unseres Quantils, suchen wir das 3. Quantil also 3

Es lässt sich leicht ausrechnen, dass (13+3/3)/4*3 = 14/4*3

Tagged with: ,

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

*

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahren Sie mehr darüber, wie Ihre Kommentardaten verarbeitet werden .