Quantile zu berechnen ist zum einen sehr einfach, zum anderen sind die Ergebnisse zugleich sehr aussagekräftig. Dies haben wir beispielhaft in unserem Blogartikel zu Quantile berechnen gezeigt. Früher gab es nur eine einzige Formel für die Berechnung von einem Excel Quantil, nämlich =QUANTIL(;). Doch seit der Version Excel 2010 gibt es zusätzlich zur urspünglichen Formel [nun =QUANTIL.INKL(;)] auch die Formel =QUANTIL.EXKL(;). Seither stehen User also vor der Wahl, welche der beiden Formeln sie verwenden sollen.
Wir erklären, welche Formel wann die richtige ist und wie die „neue“ Formel berechnet wird. Dabei gehen wir den Fragen nach:
- Was sind Quantile?
- Wie kann ich mit Excel Quantile berechnen?
- Was sagt die Alternativ-Formel aus?
- Welche Formel verwendet Excel für die alternative Quantilsberechnung?
Wenn Sie sich unsicher sind, nutzen Sie gerne auch unsere Statistik Beratung. Wir haben auf jeden Fall die richtigen Expertinnen und Experten für Sie.
Was sind Quantile?
Meist werden Quantile genutzt, um bestimmte Grenzen anzugeben, etwa das Einkommen, ab dem ein Haushalt zu den reichsten10 Prozent gehört. Man spircht auch vom oberstes Dezil (oder 90 Prozent Percentil). Für diesen Zweck ist die Formel =QUANTIL.INKL(;) die bessere Wahl. Man kann Quantile aber auch verwenden, um möglichst gleich große bzw. gleich breite Gruppen zu bilden. Dann kann die Alternativ-Formel =QUANTIL.EXKL(;) besser geeignet sein.
| Einige wichtige Quantile | Teilt in … Gruppen |
| Quartile | 4 |
| Quintile | 5 |
| Dezile | 10 |
| Perzentile | 100 |
Excel Quantil berechnen: ein Beispiel
Die Frage „Was sind Quantile“ lässt sich am besten mit einem Beispiel beantworten. Stellen wir uns eine Gruppe von 13 Personen im Alter von 18 bis 65 Jahre vor. Wir berechnen zunächst die Quartilswerte mit der Formel =QUANTIL.INKL(;).
Dazu tragen wir in der Klammer vor dem Strichpunkt den Datenbereich ein und danach den Wert 1/4 für das untere Quartil, 2/4 für das mittlere Quartil (also den Median) und 3/4 für das obere. Natürlich lässt sich statt 2/4 auch 1/2 schreiben und statt Brüchen können auch Kommazahlen eingetragen werden, also 0,25 bzw. 0,5.
Excel bestimmt als Position für das erste Quartil den vierten Wert, also 31, als Median den siebten Wert, also 43 und als drittes Quartil den zehnten Wert, also 53. Die Werte sind in der Grafik gelb markiert. Die genaue Formel, nach der Excel hier rechnet, wird im Blogartikel zu Quantile berechnen erläutert.
Wie wir sehen, liegen drei Werte unter dem 1. Quartil (31) und 9 darüber, also ein Viertel darunter und drei Viertel darüber. Genau das wünschen wir uns in den meisten Fällen. Konkret haben wir in der ersten Gruppe (unterhalb des 1. Quartils) drei Werte, zwischen dem 1. Quartil und dem Median sowie dem Median und dem 3. Quartil jeweils zwei Werte und oberhalb des 3. Quartils wieder drei Werte.
Was aber, wenn wir vier möglichst gleich große Gruppen bilden wollen?
Excel Quantil berechnen mit: =QUANTIL.EXKL(;)
Wollen wir möglichst gleich große (Teil-)Gruppen, dann verwenden wir die Formel =QUANTIL.EXKL(;). Die Daten werden wie bei der .INKL-Formel eingeben und wir erhalten als Ergebnis:
- Quartil: 29
- Quartil: 43
- Quartil: 56
Die Werte 29 und 56 gibt es in unserer Tabelle nicht, sie liegen genau zwischen dem dritten und vierten sowie zwischen dem zehnten und elften Wert.
Der Vorteil hier: wir haben jetzt vier gleich große (gleich breite) Gruppen, wenn wir die jeweiligen Quartilswerte nicht mitzählen. In diesem Fall ist das ohnehin nur der Median, das erste und dritte Quartil liegen zwischen zwei Werten.
So lässt sich auch der Zusatz .exkl verstehen. Lassen wir die Quartilswerte selbst außen vor, haben wir gleich große Gruppen, wir haben die Quartile also exkludiert (.EXKL).
Nachteil ist aber, dass jetzt drei Werte unterhalb des 1. Quartils liegen, dafür zehn Werte darüber, also etwas mehr als das Dreifache und nicht das Vierfache, wie wir es bei den meisten Betrachtungen wünschen. Deshalb ist diese Formel für die meisten Quantils-Betrachtungen nicht die erste Wahl.
So rechnen wir per Hand
Die Formel =QUANTIL.EXKL(;) hat zudem noch einen wichtigen zusätzlichen Vorteil: Ihre Berechnung von Hand ist einfacher.
Die Formel entspricht weitgehend der des Medians bei ungeraden Zahlen, nur dass wir nicht durch 2 teilen, sondern durch die Zahl der zu bildenden Gruppen, also 4 bei Quartilen.
Die Formel zur Ermittlung des Medians lautet bei ungeraden Zahlen bekanntermaßen: Medianposition = (n + 1) / 2
Analog rechnet Excel die Position des 1. Quartils für unser Beispiel mit 13 Werten
= (n + 1) / 4
= (13 + 1) / 4
= 14 / 4
= 3,5
Ein weiterer Vorteil: Die folgenden Positionen sind Vielfache der 1. Position. Für den 2. Quartilswert, also den Median, multiplizieren wir unser Ergebnis für die Position des 1. Quartils mit 2, für das 3. Quartil mit 3. Unser erstes Quartil liegt bei ‚3,5‘: wir suchen also für den Median die Position ‚7‘ und für das dritte Quartil die Position ‚10,5‘.
Um einen konkreten Wert für die Position ‚3,5‘ zu erhalten, nehmen wir das arithmetische Mittel aus dem dritten und vierten Wert, so wie wir es von der Berechnung des Medians bei geraden Zahlen kennen. Beim Wert zur Position ‚10,5‘ gehen wir analog vor.
Somit beträgt das Ergebnis für das 1. Quartil bei dieser Berechnung 29, für das 3. Quartil 56. Das 2. Quartil ändert sich nicht, so wie für alle Quantile, die gleichzeitig Median sind.
Bei anderen Zahlen und anderen Quartilen sind auch andere Kommawerte denkbar. Bei zwölf Fällen läge das erste Quartil bei (12 + 1) / 4 = 3,25. Dann suchen wir zunächst den dritten Wert und addieren ein Viertel (0,25) der Differenz zum vierten Wert.
Oder wir verwenden einfach Excel, dann müssen wir keine Berechnungen anstellen und überlassen dies unserem Softwareprogramm.
Quartile in Excel
Auch für Quartile gibt es eine spezielle Formel, die naheliegenderweise =QUARTILE.EXKL(;) heißt. Die Daten werden hier analog zur Quartilsformel mit der Endung .INKL eingegeben, also zunächst der Datenbereich und dann eine Zahl für das Quartil, also 1 für das erste Quartil, 2 für das zweite und 3 für das dritte.
Einzige Besonderheit ist, dass hier die Eingabe einer 0 nicht das Minimum und eine 4 nicht das Maximum ausgibt, sondern in diesem Fall die Fehlermeldung #ZAHL! aufscheint.
Fazit: Excel Quantil berechnen
Was sind Quantile? – Quantile sind Werte, die eine Gruppe in gleich große Teilgruppen aufspaltet. In diesem Artikel haben wir die zwei Formeln zur Berechnung von Quantilen in Excel vorgestellt. Zusammenfassend lässt sich sagen, dass die Formel =QUANTIL.INKL(;) fast immer die richtige Wahl ist. Die dahinter stehende Formel wird auch von den meisten anderen Statistikprogrammen zur Berechnung genutzt.
Wollen Sie bei einer R Programmierung Quantile berechnen wollen, kommt die Funktion quantile() zum gleichen Ergebnis wie =QUANTIL.INKL(;). Und hier können wir auch Aussagen darstellen wie „25 Prozent der Personen haben ein Einkommen von über…“.
Die Quantil-Formel mit dem Zusatz .EXKL(;) ist in Einzelfällen zu bevorzugen, etwa wenn gleich große Gruppen gebildet werden sollen oder die Quartilswerte selbst keiner Gruppe angehören.
Wenn Sie für eine Metaanalyse oder eine Analyse in Excel konkrete Unterstützung benötigen, nutzen Sie gerne unser schriftliches Formular für eine unverbindliche Kontaktanfrage!
Weiterführende Quelle
- Artikel im Spiegel