Wie man Perzentile in Excel berechnet

Wie man Perzentile in Excel berechnet

Excel bietet zwei Hauptfunktionen zur Berechnung von Perzentilen: PERCENTILE.INC und PERCENTILE.EXC.

Streng genommen gibt es noch eine dritte Funktion - PERCENTILE -, die in alten Versionen der Software (Excel 2007 und früher) verwendet wurde und jetzt aus Kompatibilitätsgründen beibehalten wird. Diese alte Funktion funktioniert auf die gleiche Weise wie PERCENTILE.INC.

In diesem Lernprogramm konzentrieren wir uns auf die neueren Funktionen - PERCENTILE.INC und PERCENTILE.EXC.

PERCENTILE.INC Excel-Funktion

Syntax

PERCENTILE.INC(array, k)

wo:

• array - dasArray oder der Datenbereich, für den das Perzentil berechnet wird,
• k - derPerzentilwert im Bereich von 0 bis einschließlich 1.

Beide Argumente sind erforderlich.

Die Funktion PERCENTILE.INC gibt das k-te Perzentil der Werte in einem Bereich zurück, wobei k im Bereich von 0 bis 1 liegt, einschließlich der Werte 0 und 1. Mit anderen Worten: Diese Funktion umfasst den ersten und den letzten Wert des Arrays.

Algorithmus

Unter der Haube führt die Excel-Funktion PERCENTILE.INC die folgenden Schritte aus:

1. Sortiert die Werte des angegebenen Arrays in aufsteigender Reihenfolge.
2. Ermittelt den Perzentilwert anhand des Arguments k und der Anzahl N der Datenpunkte im Array mit der Formel: k(N-1) + 1.
3. Wenn das Ergebnis des vorherigen Schritts keine ganze Zahl ist, interpoliert der Algorithmus zwischen den nächstgelegenen Werten des Arrays.

Beispiel

Nehmen wir an, wir wollen das 30. Perzentil für eine Reihe ganzer Zahlen von 1 bis einschließlich 10 berechnen, indem wir die Excel-Funktion PERZENTIL.INC verwenden. So sieht unsere Formel aus:

Hinweis: Obwohl die Werte des obigen Arrays zur Veranschaulichung sortiert sind, müssen wir die Werte nicht im Voraus sortieren, da der Algorithmus dies im Hintergrund für uns erledigt.

Im Folgenden findest du das Ergebnis unserer Berechnung:

Mögliche Fehler

• #NUM!- wenn das angegebene Array leer ist, oder k < 0, oder k > 1.
• #VALUE!-wenn k nicht numerisch ist.

PERCENTILE.EXC Excel-Funktion

Syntax

PERCENTILE.EXC(array, k)

wo:

• array - dasArray oder der Datenbereich, für den das Perzentil berechnet wird,
• k - derPerzentilwert im Bereich von 0 bis 1, ausschließlich.

Beide Argumente sind erforderlich.

Die Funktion PERCENTILE.EXC gibt das k-te Perzentil der Werte in einem Bereich zurück, wobei k im Bereich von 0 bis 1 liegt und die Werte 0 und 1 ausgeschlossen sind. Genauer gesagt, sollte der Wert von k für ein Array mit N Datenpunkten beide der folgenden Bedingungen erfüllen:

1. K > 1/(N+1)
2. K < N/(N+1)

Einfach ausgedrückt, schließt die Funktion PERCENTILE.EXC den ersten und den letzten Wert des Arrays aus.

Algorithmus

Unter der Haube führt die Excel-Funktion PERCENTILE.EXC die folgenden Schritte aus:

1. Sortiert die Werte des angegebenen Arrays in aufsteigender Reihenfolge.
2. Ermittelt den Perzentilwert anhand des Arguments k und der Anzahl N der Datenpunkte im Array mit der Formel: k(N+1).
3. Wenn das Ergebnis des vorherigen Schritts keine ganze Zahl ist, interpoliert der Algorithmus zwischen den nächstgelegenen Werten des Arrays.

Beispiel

Nehmen wir an, wir wollen das 30. Perzentil für eine Reihe von ganzen Zahlen von 1 bis einschließlich 10 mit der Excel-Funktion PERZENTIL.EXC berechnen. So sieht unsere Formel aus:

Hinweis: Obwohl die Werte des obigen Arrays zur Veranschaulichung sortiert sind, müssen wir die Werte nicht im Voraus sortieren, da der Algorithmus dies im Hintergrund für uns erledigt.

Im Folgenden findest du das Ergebnis unserer Berechnung:

Mögliche Fehler

• #NUM!- wenn das angegebene Array leer ist, oder k <= 1/(N+1), oder k >= N/(N+1).
• #VALUE!-wenn k nicht numerisch ist.

Um andere beliebte Excel-Funktionen zu erlernen oder aufzufrischen, schau dir dieses Tutorial an: Die 15 grundlegenden Excel-Formeln, die jeder kennen muss.

PERCENTILE.INC VS. PERCENTILE.EXC in Excel

Bis jetzt können wir viele Ähnlichkeiten zwischen den Excel-Funktionen PERCENTILE.INC und PERCENTILE.EXC erkennen. Es gibt jedoch auch deutliche Unterschiede zwischen ihnen, die sie zu zwei verschiedenen Funktionen machen und zu unterschiedlichen Ergebnissen führen. Fassen wir noch einmal zusammen, wie sich die Funktionen PERCENTILE.INC und PERCENTILE.EXC in Excel ähneln und wie sie sich unterscheiden.

Ähnlichkeiten:

1. Funktionssyntax.
2. Gesamtlogik des Algorithmus (mit Ausnahme der Formel zur Ermittlung des Perzentilwerts).
3. Mögliche Fehler (#NUM! wenn das Array leer ist oder k außerhalb seines möglichen Bereichs liegt und #VALUE! wenn k nicht numerisch ist).

Unterschiede:

1. Der mögliche Bereich für das Argument k:
• PERCENTILE.INC - von 0 bis 1, einschließlich.
• PERCENTILE.EXC - von 1/(N+1) bis N/(N+1), exklusive.
2. Behandlung der Werte eines Arrays:
• PERCENTILE.INC - enthält den ersten und letzten Wert.
• PERCENTILE.EXC - schließt den ersten und letzten Wert aus.
3. Formel zur Ermittlung des Perzentilwerts:
• PERCENTILE.INC-k(N-1) + 1.
• PERCENTILE.EXC-k(N+1).

Sehen wir uns die Excel-Funktionen PERCENTILE.INC und PERCENTILE.EXC in Aktion an, indem wir beide auf dasselbe Array ganzer Zahlen von 1 bis einschließlich 10 anwenden und mit verschiedenen Werten von k spielen:

Die obige Tabelle zeigt die Grenzen der Funktion PERCENTILE.EXP auf, wenn es um die Berechnung von Perzentilen für zu niedrige oder zu hohe Werte von k geht (genauer gesagt, wenn k <= 1/(N+1) oder k >= N/(N+1)), sowie paarweise Unterschiede in den Ergebnissen für jeden Wert von k, die zu den Extremwerten von k hin zunehmen und sich durch die rechnerischen Unterschiede der zugrunde liegenden Algorithmen erklären lassen.

Andererseits sollten wir bedenken, dass in der überwiegenden Mehrheit der Fälle zu niedrige oder zu hohe Werte von k für uns aus Sicht der Statistik keinen besonderen Wert haben. Daher sind diese Einschränkungen normalerweise kein Problem. Außerdem müssen wir im wirklichen Leben normalerweise Perzentile für viel größere Arrays als die aus unserem Beispiel oben berechnen, was die Ergebnisunterschiede abschwächt.

In den meisten Fällen ist es sinnvoller, sich für die Excel-Funktion PERCENTILE.INC zu entscheiden, die auch den Standardformeln zur Berechnung von Perzentilen in vielen Programmiersprachen, einschließlich Python und R, entspricht.

Wenn wir jedoch, aus welchen Gründen auch immer, den ersten und letzten Werten unseres Datensatzes nicht trauen, weil wir sie für statistisch nicht repräsentativ halten, kann die Funktion PERCENTILE.EXP eine bessere Wahl sein, da sie diese Werte von den Berechnungen ausschließt.

Es ist wichtig, saubere und repräsentative Daten zu haben, um daraus wertvolle Geschäftserkenntnisse zu gewinnen. Der Kurs Datenvorbereitung in Excel und der Spickzettel zur Datenbearbeitung in Excel sind hilfreiche Ressourcen, um zu lernen, wie du deine Daten effizient für weitere Analysen vorbereiten kannst.

Fazit

Wir haben die grundlegende statistische Theorie hinter Perzentilen, ihre Definition, die wichtigsten Anwendungsbereiche und die beiden wichtigsten Funktionen zur Berechnung von Perzentilen in Excel besprochen, einschließlich ihrer Syntax, Algorithmen, Berechnungsbeispiele und möglicher Fehler.

Außerdem haben wir die beiden Excel-Funktionen miteinander verglichen, ihre Gemeinsamkeiten und wichtigen Unterschiede herausgearbeitet, beide Funktionen mit demselben Datenfeld in Aktion gesetzt, um die Ergebnisse zu vergleichen, und das beste Szenario für jede der beiden Funktionen gelernt.

Um das volle Potenzial von Excel auszuschöpfen, solltest du einen einsteigerfreundlichen Lernpfad zu den Excel-Grundlagen besuchen. Dort lernst du anhand praktischer Übungen sowohl grundlegende als auch fortgeschrittene Funktionen und Formeln für die Datenaufbereitung, -visualisierung und -analyse in Excel.