Rangzahlen: Excel-Funktion RANG & Co.

Eingestellt: 24.03.2009
Letzte Änderung: 19.02.2010


Keywords: Rangzahl • Bindungen • Bindungsgruppe • Ordnungsstatistik
Betrifft: Excel-Funktion RANG • RANG.GLEICH • RANG.MITTELW
Verwandte Themen: Zusammenhangsmaße • Kontingenztafel Kovarianz Rangkorrelationskoeffizient nach SpearmanKorrelationskoeffizient nach Bravais-Pearson • Fechnersche Korrelationskoeffizient Kendalls τ


 

Einleitung

Dieser Artikel diskutiert die Bestimmung von absoluten Rangzahlen bei einem Vorliegen von Rangbindungen (ties). Nach der Definition der Rangzahl und der Problemdarstellung wird auf fünf ausgewählte Methoden eingegangen, die sich anhand von drei Kriterien unterscheiden lassen.

Außer der Methode Continuous lehnen sich die anderen vier Methoden Low, High, Sequential und Mean an die in SPSS gleichnamig verwendeten Methoden an. Abschließend wird ein Überblick über die Methoden und deren Eigenschaften gegeben.

Ferner werden die zu einer jeden Methode korrespondierenden Excel-Formeln aufgezeigt. Zu beachten ist, dass es sich bei diesen teilweise um Arrayformeln handelt. Alle Excel-Formeln können den Rang einer Beobachtung aus einer unsortierten Urliste bestimmen. Kommen Leerzellen vor, so kann es sein, dass die angegebene Excel-Formel daraufhin angepasst oder auf eine andere ausgewichen werden muss.

Hinweis:
Ab Excel 2010 gibt es zwei Excel-Funktionen für
absolute Rangzahlen:
Während RANG.GLEICH Rangzahlen nach der Methode Low berechnet
und äquivalent mit der Excel-Funktion RANG ist,
bestimmt RANG.MITTELW Rangzahlen nach der Methode Mean.

 

Definition Rangzahl

Gegeben sei ein mindestens ordinal skaliertes Merkmal  mit Ausprägungen in einer ungeordneten Urliste , die aufsteigend  bzw. absteigend  geordnet werden kann.
Unter der Rangzahl einer Beobachtung , in Zeichen bzw. , versteht man die Position, die  in der geordneten Urliste einnimmt, d.h.

.

 

Problemdarstellung

Betrachtet sei zunächst Beispiel 1, bei dem die Beobachtungen 23, 41, 54, 37, 16 und 28 in einer ungeordneten Urliste vorliegen. Diese Beobachtungen werden in die Spalte B der Arbeitstabelle 1 übertragen. Zu beachten ist, dass alle Werte unterschiedlich sind.


Arbeitstabelle 1 - Beispiel 1

Formeln in der Tabelle:
C2: =KKLEINSTE($B$2:$B$7;ZEILE(A1))
D2: =KGRÖSSTE($B$2:$B$7;ZEILE(A1))
E2: =RANG(B2;$B$2:$B$7;1)
F2: =RANG(B2;$B$2:$B$7)
Formeln nach unten kopieren

In Spalte C (bzw. Spalte D) stehen die Ordnungsstatistiken bei aufsteigender (bzw. absteigender) Reihenfolge der Beobachtungen, welche mit Hilfe der Excel-Funktion KKLEINSTE (bzw. KGRÖSSTE) bestimmt werden. Die Ordnungsstatistiken bewirken, dass die Beobachtungen der Größe nach angeordnet und umbenannt werden. Demnach entspricht dem zweitkleinsten (bzw. dem fünftgrößten) Wert aus der Urliste, so dass  ist.

Mit der Excel-Funktion RANG werden die Rangzahlen in Spalte E (bzw. Spalte F) so bestimmt, als wäre Spalte B eine aufsteigende (bzw. absteigende) Ordnungsstatistik. Für die erste Beobachtung  ergibt sich (bzw.  ), da diese in der aufsteigenden (bzw. absteigenden) Reihenfolge der Beobachtungen in Spalte C (bzw. Spalte D) die zweite (bzw. fünfte) Position einnimmt.

Bei empirischen Daten können jedoch Beobachtungen mit gleichen Merkmalsausprägungen auftreten, die als Rangbindungen (auch: ties, gebundene Beobachtungen) bezeichnet werden. Die an einem Wert gebundenen Beobachtungen werden zu einer Bindungsgruppe zusammengefasst. Die Ränge sind dann nicht mehr eindeutig zu bilden.

In Beispiel 2 ist zu erkennen, dass  mehrere Beobachtungen mit dem Wert 9 (bzw. 25 bzw. 58) vorkommen. Die Beobachtungen 5 und 10 (bzw. 2, 3 und 7 bzw. 4, 6 und 12) bilden eine Bindungsgruppe. Die Frage ist, welche Rangzahl(en) diesen Beobachtungen zugeordnet werden soll(en).


Arbeitstabelle 2 - Beispiel 2 mit Ordnungsstatistiken

Formeln in der Tabelle:
C2: =KKLEINSTE(x;ZEILE(A1))
D2: =KGRÖSSTE(x;ZEILE(A1))
Formeln nach unten kopieren

Für den Bereich $B$2:$B$13 ist der Name x definiert.

Eine Berücksichtigung der Bindungen bei der Bestimmung der Rangzahlen kann auf unterschiedliche Weise erfolgen. Arbeitstabelle 2 dient im weiteren Verlauf des Artikels zur Veranschaulichung der hier vorgestellten Methoden, die sich anhand nachstehender Eigenschaften unterscheiden lassen.

 

Eigenschaften

1. Um eindeutige Ränge vergeben zu können, soll Beobachtungen, die den gleichen Wert haben, die selbe Rangzahl zugewiesen werden, d.h.



2. Die Rangfolge soll nicht fortlaufend sein, sofern Beobachtungen vorliegen, die gleiche Werte haben. Bei Rangbindungen sollen demnach je nach zugrunde liegender Methode in Frage kommende Rangzahlen unbesetzt bleiben.

3. Die Summe der Ränge soll unabhängig von Rangbindungen stets

  betragen.

 

Methode Continuous

Nach der Methode Continuous ergibt sich die Rangzahl der i-ten Beobachtung aus

bzw.

d.h. unabhängig vom Vorhandensein von Bindungen werden stets  unterschiedliche Ränge vergeben.1 Es wird also jedem Wert eine andere Rangzahl zugewiesen, so dass sich eine fortlaufende Rangfolge ergibt. Ferner ist die Summe der Ränge konstant .

Treten Bindungen wie in Beispiel 2 auf, so wird über die Anordnung an den betreffenden Stellen anhand ihres Auftretens in der Urliste entschieden.

In Beispiel 2 werden nach dieser Methode den drei Beobachtungen mit dem Wert 25 die Rangzahlen 4, 5 und 6 (bzw. 9, 8 und 7) zugeordnet.


Arbeitstabelle 2 - Methode Continuous

Formeln in der Tabelle:
E2: {=SUMME((x<B2)*1)+SUMME(($B$2:B2=B2)*1)}
oder
E2: =RANG(B2;x;1)+ZÄHLENWENN($B$2:B2;B2)-1

F2: {=SUMME((x>B2)*1)+SUMME((B2:$B$13=B2)*1)}
oder
F2: =RANG(B2;x)+ZÄHLENWENN(B2:$B$13;B2)-1
Formeln nach unten kopieren

 

Methode Low (Minimaler Rang, Excel-Funktion RANG)

Nach der Methode Low bestimmt sich die Rangzahl der i-ten Beobachtung mit

d.h. eine Rangzahl ergibt sich aus der Anzahl der Beobachtungen, deren Wert kleiner (bzw. größer) ist als der Wert der betrachteten Beobachtung, plus eins. Demnach werden Beobachtungen, die den gleichen Wert aufweisen, die selbe Rangzahl zugeordnet und zwar stets die niedrigste in Frage kommende Rangzahl.

In Beispiel 2 wird nach dieser Methode den drei Beobachtungen mit dem Wert 25 die Rangzahl 4 (bzw. 7) zugewiesen, die Rangzahlen 5 und 6 (bzw.  8 und 9) bleiben dagegen unbesetzt. Die Summe der Ränge ist dabei abhängig von den Bindungen und stets kleiner gleich .

Die Methode Low entspricht der Excel-Funktion RANG.


Arbeitstabelle 2 - Methode Low

Formeln in der Tabelle:
G2: {=SUMME((x<B2)*1)+1}
oder
G2: =RANG(B2;x;1)
oder
G2: =ZÄHLENWENN(x;"<"&B2)+1
oder
G2: =RANG.GLEICH(B2;x;1)


H2: {=SUMME((x>B2)*1)+1}
oder
H2: =RANG(B2;x)
oder
H2: =ZÄHLENWENN(x;">"&B2)+1
H2: =RANG.GLEICH(B2;x;)
Formeln nach unten kopieren

Hinweis:
Ab Excel 2010 ersetzt die Excel-Funktion RANG.GLEICH
die alte Excel-Funktion RANG.

 

Methode High (Maximaler Rang)

Nach der Methode High ergibt sich die Rangzahl der i-ten Beobachtung aus

d.h. eine Rangzahl entspricht der Anzahl der Beobachtungen, deren Wert kleiner gleich (bzw. größer gleich) dem Wert der betrachteten Beobachtung ist. Somit werden Beobachtungen, die den gleichen Wert aufweisen, die selbe Rangzahl zugewiesen und zwar stets die größte in Frage kommende Rangzahl.

In Beispiel 2 wird nach dieser Methode den drei Beobachtungen mit dem Wert 25 die Rangzahl 6 (bzw. 9) zugewiesen, die Rangzahlen 4 und 5 (bzw.  7 und 8) bleiben dagegen unbesetzt. Die Summe der Ränge ist abhängig von Rangbindungen und stets größer gleich  .


Arbeitstabelle 2 - Methode High

Formeln in der Tabelle:
I2: {=SUMME((x<=B2)*1)}
oder
I2: =ZÄHLENWENN(x;"<="&B2)
oder
I2: =HÄUFIGKEIT(x;B2)

J2: {=SUMME((x>=B2)*1)}
oder
J2: =ZÄHLENWENN(x;">="&B2)
Formeln nach unten kopieren

 

Methode Sequential (Rangfolge fortlaufend vergeben)

Nach der Methode Sequential ergibt sich die Rangzahl der i-ten Beobachtung aus

d.h. eine Rangzahl entspricht der Anzahl der Beobachtungen mit unterschiedlichen Werten, die kleiner gleich (bzw. größer gleich) dem Wert der i-ten Beobachtung sind. Es ergibt sich eine fortlaufende Rangfolge, wobei Beobachtungen, die den gleichen Wert aufweisen, die selbe Rangzahl zugeordnet wird.

In Beispiel 2 wird nach dieser Methode den drei Beobachtungen mit dem Wert 25 die Rangzahl 3 (bzw. 5) zugewiesen. Die Rangzahlen 4 und 5 (bzw. 6 und 7) werden durch die nächstgrößeren (bzw. nächstkleineren) Beobachtungen besetzt. Die Summe der Ränge ist dabei abhängig von Rangbindungen und stets kleiner gleich . Im Gegensatz zu den anderen Methoden kann sich bei der Methode Sequential die Summe der Ränge nach der Art der Rangbestimmung unterscheiden. Werden die Ränge so bestimmt, als läge eine aufsteigende (bzw. absteigende) Ordnungsstatistik vor, dann ergibt sich in Beispiel 2 für die Summe der Ränge ein Wert von 47 (bzw. 49).


Arbeitstabelle 2 - Methode Sequential

Formeln in der Tabelle:
K2: {=SUMME((x<=B2)/ZÄHLENWENN(x;x))}
oder
K2: {=SUMME(N(HÄUFIGKEIT(x;WENN(x<B2;x))>0))}

L2: {=SUMME((x>=B2)/ZÄHLENWENN(x;x))}
oder
L2: {=SUMME(N(HÄUFIGKEIT(x;WENN(x>=B2;x))>0))}
Formeln nach unten kopieren

 

Methode Mean (Mittelwert, Methode der Durchschnittsränge, midrank)

Um eine mittlere Rangzahl zu bestimmen, wird zunächst auf die Ordnungsstatistik zurückgegriffen, bei der mehrfach vorkommende Werte der Rang zugewiesen wird, der ihrer Position entspricht. Anschließend wird für eine jede Bindungsgruppe der Durchschnitt aus den vorläufigen Rangzahlen ermittelt und diese als endgültige Rangzahl allen Elementen dieser Gruppe zugeordnet. Die Vorgehensweise kann wie in Arbeitstabelle 3 erfolgen:


Arbeitstabelle 3 - Vorgehensweise der Methode Mean

Formeln in der Tabelle:
C2: =RANG(B2;$B$2:$B$13;1)+ZÄHLENWENN($B$2:B2;B2)-1
D2: {=MITTELWERT(WENN($B$2:$B$13=B2;$C$2:$C$13))}
Formeln nach unten kopieren

In Spalte B der Arbeitstabelle 3 steht die Ordnungsstatistik für das Beispiel 2 in aufsteigender Reihenfolge. Es ist zu erkennen, dass drei Bindungsgruppen (rot, gelb und grün markiert) vorliegen. In Spalte C werden die Rangzahlen mit Bezug auf die Ordnungsstatistik nach der Methode Continuous berechnet. Um die Rangzahlen für die Methode Mean zu erhalten, muss in einem zweiten Schritt in Spalte D das arithmetische Mittel der jeweiligen Bindungsgruppe ermittelt werden:

Zur Anwendung dieser Methode stellt Excel mit (ANZAHL(x)+1-RANG(B2;x)-RANG(B2;x;1))/2 einen Korrekturfaktor für die Excel-Funktion RANG zur Verfügung.2  Eine wie in Arbeitstabelle 3 aufgezeigte Hilfsspaltenlösung ist somit nicht notwendig.

Der Korrekturfaktor lässt sich ferner noch durch eine Umformung verkürzen, so dass sich die Rangzahl der i-ten Beobachtung bestimmen lässt mit:

bzw.

Offensichtlich erfüllt die Methode Mean alle drei Eigenschaften.

In Arbeitstabelle 2 lassen sich folgende Excel-Formeln für die Methode Mean schreiben:


Arbeitstabelle 2 - Methode Mean

Formeln in der Tabelle:
M2:{=SUMME((x<B2)*1)+1+(SUMME((x=B2)*1)-1)/2}
oder
M2: =RANG(B2;x;1)+(ANZAHL(x)+1-RANG(B2;x)-RANG(B2;x;1))/2
oder
M2: =RANG(B2;x;1)+(ZÄHLENWENN(x;B2)-1)/2
oder
M2: =RANG.MITTELW(B2;x;1)

N2: {=SUMME((x>B2)*1)+1+(SUMME((x=B2)*1)-1)/2}
oder
N2: =RANG(B2;x)+(ANZAHL(x)+1-RANG(B2;x)-RANG(B2;x;1))/2
oder
N2: =RANG(B2;x)+(ZÄHLENWENN(x;B2)-1)/2
oder
N2: =RANG.MITTELW(B2;x;)
Formeln nach unten kopieren

Hinweis:
Ab Excel 2010 können Rangzahlen nach der Methode Mean
mit Hilfe der Excel-Funktion RANG.MITTELW bestimmt werden.

Zusammenfassung

Einen Überblick über die unterschiedlichen Methoden zur Bestimmung der Rangzahlen bei Bindungen liefern die Arbeitstabellen 4 und 5, wobei jeweils für den Bereich B2:B13 der Name x definiert ist. In Arbeitstabelle 4 (bzw. 5) werden die Rangzahlen mit Bezug auf die aufsteigende (bzw. absteigende) Ordnungsstatistik aus Beispiel 2 bestimmt.


Arbeitstabelle 4 - Überblick Methoden (aufsteigende Ordnungsstatistik)

Formeln in der Tabelle:
C2: =RANG(B2;x;1)+ZÄHLENWENN($B$2:B2;B2)-1
D2: =RANG(B2;x;1)
E2: =ZÄHLENWENN(x;"<="&B2)
F2: {=SUMME((x<=B2)/ZÄHLENWENN(x;x))}
G2: =RANG(B2;x;1)+(ZÄHLENWENN(x;B2)-1)/2
Formeln nach unten kopieren

 


Arbeitstabelle 5 - Überblick Methoden (absteigende Ordnungsstatistik)

Formeln in der Tabelle:
C2: =RANG(B2;x)+ZÄHLENWENN(B2:$B$13;B2)-1
D2: =RANG(B2;x)
E2: =ZÄHLENWENN(x;">="&B2)
F2: {=SUMME((x>=B2)/ZÄHLENWENN(x;x))}
G2: =RANG(B2;x)+(ZÄHLENWENN(x;B2)-1)/2
Formeln nach unten kopieren

Welche Methode welche Eigenschaft erfüllt, ist in nachfolgender Tabelle mit einem Häckchen gekennzeichnet:

Für die Methoden Continuous und Mean folgt direkt aus Eigenschaft 3, dass das arithmetische Mittel der Ränge stets  beträgt.

Ferner gilt für beide Methoden .

Eine Rangbestimmung zur Berechnung des Rangkorrelationskoeffizienten nach Spearman erfolgt bei Vorliegen von Bindungen anhand der Methode Mean.

Alle fünf Methoden können ebenfalls angewendet werden, wenn keine Rangbindungen vorliegen, und liefern äquivalente Ergebnisse.

Weitere Excel-Formeln für die verwendeten Methoden sind natürlich denkbar.

 

1 Eine Variante der Methode Continuous mit den selben Eigenschaften ergibt sich aus einer Umindizierung der zweiten Summe mit 
bzw.
.
Die korrespondierenden Excel-Formeln lauten:
E2: {=SUMME((x<B2)*1)+SUMME((B2:$B$13=B2)*1)}

oder
E2: =RANG(B2;x;1)+ZÄHLENWENN(B2:$B$13;B2)-1

F2:
{=SUMME((x>B2)*1)+SUMME(($B$2:B2=B2)*1)}
oder
F2: =RANG(B2;x)+ZÄHLENWENN($B$2:B2;B2)-1
Formeln nach unten kopieren
Liegt bereits eine geordnete Urliste vor, so kann eine fortlaufende Rangfolge bspw. mit =ZEILE(A1) bzw. =ANZAHL(x)-ZEILE(A1)+1 erzeugt werden.

2 Vgl. Microsoft (2007): Excel statistical functions: Representing ties by using RANK, Article ID: 828678, Revision: 2.2.

 

Haben Sie Fragen oder Anregungen zu diesem Artikel?

Dann senden Sie einfach eine Nachricht an:

E-Mail:

 

Lesen Sie auch:
___________________________________________________________________

Excel-Formeln für den Rangkorrelationskoeffizienten nach Spearman