Wie man Perzentile in Excel berechnet

Wie man Perzentile in Excel berechnet

Excel hat zwei Hauptfunktionen zum Berechnen von Perzentilen: „ PERCENTILE.INC() “ und „ PERCENTILE.EXC() “.

Genau genommen gibt's noch die dritte Funktion –PERCENTILE()–, die in alten Versionen der Software (Excel 2007 und früher) benutzt wurde und jetzt aus Kompatibilitätsgründen beibehalten wird. Diese alte Funktion läuft genauso wie PERCENTILE.INC().

In diesem Tutorial schauen wir uns die neueren Funktionen an – „PERCENTILE.INC() “ und „ PERCENTILE.EXC() “.

PERCENTILE.INC Excel-Funktion

Syntax

PERCENTILE.INC(array, k)

wo:

• array– die Datenreihe oder der Datenbereich, für den der Perzentilwert berechnet wird,

• k—der Perzentilwert im Bereich von 0 bis 1, inklusive.

Beide Argumente sind nötig.

Die Funktion „ PERCENTILE.INC() “ gibt den k-ten Perzentilwert in einem Bereich zurück, wobei k zwischen 0 und 1 liegt, einschließlich der Werte 0 und 1. Also, diese Funktion nimmt den ersten und letzten Wert des Arrays mit rein.

Algorithmus

Im Hintergrund macht die Excel-Funktion „ PERCENTILE.INC() “ folgende Schritte:

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 vom letzten Schritt keine ganze Zahl ist, macht der Algorithmus eine Interpolation zwischen den nächsten Werten vom Array.

Beispiel

Nehmen wir mal an, wir wollen den 30. Perzentilwert für eine Reihe von ganzen Zahlen von 1 bis 10 inklusive berechnen, und zwar mit der Excel-Funktion „ PERCENTILE.INC() “. So wird unsere Formel aussehen:

Anmerkung: Die Werte des obigen Arrays sind zwar zur Veranschaulichung sortiert, aber eigentlich müssen wir die Werte nicht vorher sortieren, weil der Algorithmus das für uns im Hintergrund erledigt.

Hier ist das Ergebnis unserer Berechnung:

Mögliche Fehler

• #NUM!– wenn das angegebene Array leer ist oder k < 0 oder k > 1 ist.

• #VALUE!– wenn k kein numerischer Wert ist.

PERCENTILE.EXC Excel-Funktion

Syntax

PERCENTILE.EXC(array, k)

wo:

• array– die Datenreihe oder der Datenbereich, für den der Perzentilwert berechnet wird,

• k—der Prozentwert im Bereich von 0 bis 1, ohne 1.

Beide Argumente sind nötig.

Die Funktion „ PERCENTILE.EXC() “ gibt den k-ten Perzentilwert eines Bereichs zurück, wobei k zwischen 0 und 1 liegt, aber 0 und 1 nicht mitzählt. Genauer gesagt, für ein Array mit N Datenpunkten sollte der Wert von k die folgenden beiden Bedingungen erfüllen:

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

Einfach gesagt, die Funktion „ PERCENTILE.EXC() “ gibt nur Perzentile genau zwischen 0 und 1 an. Das heißt, es werden keine Werte für das absolute Minimum (0. Perzentil) oder Maximum (100. Perzentil) berechnet, obwohl alle Datenpunkte bei der Interpolation berücksichtigt werden.

Algorithmus

Im Hintergrund macht die Excel-Funktion „ PERCENTILE.EXC() “ folgende Schritte:

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 vom letzten Schritt keine ganze Zahl ist, macht der Algorithmus eine Interpolation zwischen den nächsten Werten vom Array.

Beispiel

Nehmen wir mal an, wir wollen den 30. Perzentil für eine Reihe von ganzen Zahlen von 1 bis 10 inklusive berechnen, und zwar mit der Excel-Funktion PERCENTILE.EXC. So wird unsere Formel aussehen:

Anmerkung: Die Werte des obigen Arrays sind zwar zur Veranschaulichung sortiert, aber eigentlich müssen wir die Werte nicht vorher sortieren, weil der Algorithmus das für uns im Hintergrund erledigt.

Hier ist 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 kein numerischer Wert ist.

Wenn du andere beliebte Excel-Funktionen lernen oder auffrischen willst, schau dir dieses Tutorial an: Die 15 grundlegenden Excel-Formeln, die jeder kennen sollte.

PERCENTILE.INC GEGEN PERCENTILE.EXC in Excel

Bisher können wir echt viele Gemeinsamkeiten zwischen den Excel-Funktionen PERCENTILE.INC und PERCENTILE.EXC erkennen. Es gibt aber auch deutliche Unterschiede zwischen ihnen, die sie zu zwei getrennten Funktionen machen und zu unterschiedlichen Ergebnissen führen. Lass uns nochmal zusammenfassen, wie die Funktionen PERCENTILE.INC und PERCENTILE.EXC in Excel ähnlich sind und wie sie sich unterscheiden.

Similarities:

1. Funktionssyntax.
2. Allgemeine Logik des Algorithmus (außer der Formel, mit der der Perzentilwert berechnet wird).
3. Mögliche Fehler (#NUM!, wenn das Array leer ist oder k außerhalb des möglichen Bereichs liegt, und #VALUE!, wenn k kein numerischer Wert ist).

Unterschiede:

1. Der mögliche Bereich für das Argument k:

• PERCENTILE.INC()—von 0 bis 1, inklusive.

• PERCENTILE.EXC()—von 1/(N+1) bis N/(N+1), nicht eingeschlossen.

2. Behandlung der Werte eines Arrays:

• PERCENTILE.INC()—beinhaltet den ersten und letzten Wert.

• PERCENTILE.EXC()—definiert keine Perzentile für den ersten und letzten Wert (0 % und 100 %), bezieht aber trotzdem alle Daten in die Interpolation mit ein.

3. Formel zur Berechnung des Perzentilwerts:

• PERCENTILE.INC()—k(N-1) + 1.

• PERCENTILE.EXC()—k(N+1).

Schauen wir uns mal die Excel-Funktionen „ PERCENTILE.INC() “ und „ PERCENTILE.EXC() “ in Aktion an, indem wir beide auf dieselbe Reihe von ganzen Zahlen von 1 bis 10 anwenden und mit verschiedenen Werten für k herumspielen:

Die obige Tabelle zeigt, dass die Funktion „ PERCENTILE.EXC() “ für k-Werte außerhalb ihres gültigen Bereichs (k ≤ 1/(N+1) oder k ≥ N/(N+1)) nicht definiert ist. Das ist kein Fehler. Es spiegelt die mathematische Definition von exklusiven Perzentilen wider. Die kleinen Unterschiede zwischen den Ergebnissen von .INC() und .EXC() in den Randbereichen kommen daher, wie jede Funktion die Perzentilpositionen interpoliert.

Andererseits sollten wir bedenken, dass in den allermeisten Fällen zu niedrige oder zu hohe Werte von k aus statistischer Sicht für uns nicht besonders wichtig sind. Deshalb sind diese Einschränkungen normalerweise kein Problem. Außerdem müssen wir im echten Leben meistens Perzentile für viel größere Arrays als das aus unserem Beispiel oben berechnen, was die Unterschiede im Ergebnis ausgleicht.

Meistens ist es besser, die Excel-Funktion „ PERCENTILE.INC() “ zu nehmen, die auch mit den Standardformeln übereinstimmt, die in vielen Programmiersprachen, wie Python und R, zum Berechnen von Perzentilen verwendet werden.

Wenn wir aber vermeiden wollen, Perzentile ganz am Rand des Datensatzes zu bestimmen, ist die Funktion „ PERCENTILE.EXC() “ die richtige Wahl. Es berechnet zwar keine Ergebnisse für das 0. oder 100. Perzentil, nutzt aber trotzdem alle Datenpunkte, wenn es zwischen den Rängen interpoliert.

Es ist echt wichtig, saubere und repräsentative Daten zu haben, um daraus wertvolle geschäftliche Erkenntnisse zu gewinnen. Der Kurs „Datenaufbereitung in Excel“ und das Cheat Sheet „Datenbearbeitung in Excel“ sind super Hilfsmittel, um zu lernen, wie du deine Daten effizient für die weitere Analyse vorbereiten kannst.

Fazit

Wir haben die grundlegende Statistiktheorie hinter Perzentilen, ihre Definition, die wichtigsten Anwendungsbereiche und die beiden Hauptfunktionen zur Berechnung von Perzentilen in Excel besprochen, einschließlich ihrer Syntax, Algorithmen, Berechnungsbeispiele und möglichen Fehlerquellen.

Wir haben auch die beiden Excel-Funktionen verglichen, ihre Gemeinsamkeiten und wichtigen Unterschiede aufgezeigt, beide Funktionen auf denselben Datenbereich angewendet, um die Ergebnisse zu vergleichen, und das beste Szenario für jede der beiden Funktionen ermittelt.

Um das volle Potenzial von Excel auszuschöpfen, solltest du einen anfängerfreundlichen Excel -Lernpfad zu den Grundlagen von Excel machen. Dort lernst du durch praktische Übungen sowohl grundlegende als auch fortgeschrittene Funktionen und Formeln für die Datenaufbereitung, -visualisierung und -analyse in Excel kennen.