Gerne beraten wir Sie auch telefonisch & geben Ihnen eine kostenfreie persönliche Auskunft zu Ihrem Projekt.

X

Excel Quantil: Darum gibt es zwei Formeln zur Berechnung

Quantile zu berechnen ist zum einen sehr einfach, zum anderen sind die Ergebnisse zugleich sehr aussagekräftig. Dies haben wir beispielhaft in einem der letzten 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.

Lassen Sie uns Ihre Anforderungen wissen & wir erstellen Ihnen innerhalb weniger Stunden ein kostenfreies Angebot.


Jetzt unverbindlich anfragen

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 statistische Beratung vom Experten. 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 (beispielhaft sei auf folgenden Spiegel-Artikel verwiesen). 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
Quartile4
Quintile5
Dezile10
Perzentile100

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:

  1. Quartil: 29
  2. Quartil: 43
  3. 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 Quantile in R berechnenwollen, 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 Analyse in Excel konkrete Unterstützung benötigen, nutzen Sie gerne unser Formular für eine unverbindliche Kontaktanfrage!

Jetzt Anrufen Jetzt anfragen