Rangfolge mit RANK und PERCENTILE

Oft möchten Sie nicht nur den Wert kennen, sondern auch seine Position innerhalb der Gruppe. Ist diese Verkäuferin oder dieser Verkäufer auf Platz 1 oder 12? Gehört dieses Ergebnis zu den besten 10 %?

Zwei Funktionsfamilien liefern die Antwort: RANK gibt die Rangposition an, und PERCENTILE liefert den Wert an einem bestimmten Grenzwert. Gemeinsam verwandeln sie Rohdaten in Ranglisten und Vergleichswerte.

RANK.EQ gibt die Position eines Werts innerhalb eines Bereichs an. Sie übergeben den Wert, den Bereich und ein Sortierreihenfolge-Argument.

• Die Reihenfolge 0 (oder weggelassen) sortiert absteigend, also vom größten zum kleinsten Wert.
• Die Reihenfolge 1 sortiert aufsteigend, also vom kleinsten zum größten Wert.

Hier erhält der höchste Umsatz den Rang 1.

Beachten Sie die Dollarzeichen: $B$2:$B$20. Der Bereich muss beim Kopieren der Formel nach unten unverändert bleiben, während sich der Wertbezug B2 zu B3, B4 und so weiter verschiebt.

Wenn Sie die Dollarzeichen vergessen, verschiebt sich der Bereich mit jeder Zeile, wodurch unsinnige Rangwerte entstehen. Absolute Bezüge für den Bereich sind hier unerlässlich.

RANK.EQ weist gleichen Werten denselben Rang zu und überspringt anschließend den nächsten Rang. Wenn zwei Werte gemeinsam auf Platz 2 liegen, erhalten beide den Rang 2 und der nächste Wert den Rang 4 (es gibt keinen dritten Platz).

Wenn Sie bei Gleichständen stattdessen den durchschnittlichen Rang vergeben möchten, verwenden Sie RANK.AVG. Zwei Werte, die gemeinsam auf den Plätzen 2 und 3 liegen, erhalten dann jeweils den Rang 2,5.

Um auch bei Gleichständen eindeutige Ränge zu erzwingen, fügen Sie einen COUNTIF-Tiebreaker hinzu. Er zählt, wie viele gleiche Werte oberhalb der aktuellen Zeile vorkommen, und addiert diesen Versatz.

Dadurch erhalten Sie garantiert eine lückenlose Folge 1, 2, 3, 4 ohne doppelte Ränge, was für Ranglisten nützlich ist.

PERCENTILE.INC beantwortet die Frage: Welcher Wert liegt an einem bestimmten Prozentsatz? Übergeben Sie den Bereich und einen Bruch zwischen 0 und 1.

Die folgende Formel gibt das 90. Perzentil zurück, also den Wert, unter dem 90 % der Daten liegen. Damit lassen sich hervorragend Grenzwerte wie „die besten 10 %“ festlegen.

Quartile teilen Daten in vier Teile. Die Funktion QUARTILE.INC ist eine Abkürzung: Quartil 1 entspricht dem 25. Perzentil, Quartil 2 dem 50. (dem Median) und Quartil 3 dem 75. Perzentil.

Die folgende Formel gibt das dritte Quartil zurück, identisch mit PERCENTILE.INC(A2:A101,0.75).

Es gibt zwei Varianten: PERCENTILE.INC (inklusiv, akzeptiert 0 und 1) und PERCENTILE.EXC (exklusiv, nur zwischen 0 und 1, niemals an den Extrempunkten).

Für die meisten alltäglichen Berichte ist .INC die übliche Wahl. Verwenden Sie .EXC, wenn Sie statistischen Konventionen folgen, die die Grenzpunkte ausschließen.

RANK liefert eine Position, PERCENTILE einen Wert an einem Grenzwert. PERCENTRANK.INC macht das Gegenteil von PERCENTILE: Sie geben einen Wert vor und erhalten das zugehörige Perzentil.

Wenn eine Schülerin oder ein Schüler 88 Punkte erreicht und PERCENTRANK den Wert 0,92 zurückgibt, war diese Person besser als 92 % der Gruppe. Multiplizieren Sie den Wert mit 100, um ihn als Prozentsatz anzuzeigen.

RANK und PERCENTILE funktionieren nur mit Zahlen. Textbeschriftungen und leere Zellen innerhalb des Bereichs werden ignoriert, sodass eine versehentlich enthaltene Überschrift Ihre Rangfolge nicht beeinträchtigt.

Bei als Text gespeicherten Zahlen ist jedoch Vorsicht geboten: Sie werden vollständig übersprungen, wodurch sich jeder Rang und jedes Perzentil verschiebt. Wenn ein Rang unplausibel wirkt, prüfen Sie, ob alle Werte tatsächlich Zahlen und nicht linksbündiger Text sind.

Sie haben 20 Vertriebsmitarbeiter mit Verkaufszahlen in Spalte B. Sie möchten für jeden Mitarbeiter den Rang und feststellen, ob er zum obersten Quartil gehört.

• Rang: =RANK.EQ(B2,$B$2:$B$21,0)
• Das Flag für das oberste Quartil vergleicht die Verkaufszahl mit dem 75. Perzentil.

Die folgende Formel gibt für jeden Mitarbeiter abhängig vom Grenzwert „Top“ oder „Standard“ zurück.

Testen Sie Ihr Verständnis von Rangfunktionen.

Sie haben gelernt, Werte innerhalb einer Gruppe einzuordnen:

• RANK.EQ gibt die Rangposition an; die Reihenfolge 0 beginnt mit dem größten, 1 mit dem kleinsten Wert. Fixieren Sie den Bereich mit $.
• RANK.AVG bildet bei gleichen Rängen den Durchschnitt; fügen Sie COUNTIF hinzu, um eindeutige Ränge zu erhalten.
• PERCENTILE.INC gibt den Wert an einem Grenzwert zurück; QUARTILE.INC ist eine Kurzform für 25/50/75.
• PERCENTRANK.INC funktioniert umgekehrt: Es gibt das Perzentil eines bestimmten Werts zurück.