Excel-Formeln für den

Rangkorrelationskoeffizienten nach Spearman

Spearman's rank correlation coefficient, Spearman's ρ

Eingestellt: 30.03.2009
Letzte Änderung: 30.07.2010


Keywords: Rangkorrelationskoeffizient • Spearman • RangzahlBindungen
Betrifft:
  Excel-Funktionen KORREL
RANG RANG.GLEICH • RANG.MITTELW
Verwandte Themen: Zusammenhangsmaße • Kontingenztafel • Kovarianz •
Korrelationskoeffizient nach Bravais-Pearson • Fechnersche Korrelationskoeffizient
• Kendalls τ


 

Definition

Der Zusammenhang zwischen zwei ordinal skalierten Merkmale und  kann mit dem Rangkorrelationkoeffizient nach Spearman  gemessen werden.

Dabei werden zunächst für die Ausprägungen  und  der Beobachtungspaare  getrennt voneinander die Ränge (Rangzahlen)  und  (bzw.  und ) berechnet.1

Treten gleiche Werte mehrfach auf, werden die Rangzahlen nach der Methode der Durchschnittsränge (Methode Mean) ermittelt.2  Unabhängig vom Vorliegen so genannter Rangbindungen (ties) ist die Summe der Ränge stets gleich , so dass für die arithmetischen Mittel der Ränge folgt.

In einem zweiten Schritt wird die Produkt-Moment-Korrelation für die Ränge berechnet.

Die Formel für den Rangkorrelationskoeffizienten nach Spearman  lautet:

Eine weitere, äquivalente Formel für  ist:

Liegen keine Rangbindungen vor, d.h sind alle  und  untereinander verschieden, so lässt sich vereinfachen zu:

 

Eigenschaften des Zusammenhangsmaßes

1. hat keine Benennung.

2. ändert sich nicht, wenn und  vertauscht werden.

3. ist invariant gegenüber streng monoton wachsenden Transformationen.

4. ist normiert auf .

 

Excel-Formeln für den Rangkorrelationskoeffizienten nach Spearman

- keine Rangbindungen

Excel stellt keine Excel-Funktion für den Rangkorrelationskoeffizienten nach Spearman zur Verfügung. Daher sollen zunächst die einzelnen Rechenschritte für das Zusammenhangsmaß nach Formel (2) mit Hilfe der nachstehenden Arbeitstabelle 1 dargestellt werden:


Arbeitstabelle 1 - keine Rangbindungen

wobei folgende Namen definiert sind:

x: =$B$2:$B$13
y: =$C$2:$C$13
n: =ANZAHL(x)
q: =ANZAHL(x)*(ANZAHL(x)+1)^2/4

Formeln in der Tabelle:
D2: =RANG(B2;B$2:B$13;1)
E2: =RANG(C2;C$2:C$13;1)
F2: =D2*E2
G2: =D2^2
H2: =E2^2
Formeln nach unten kopieren bis Zeile 13
D14: =SUMME(D2:D13)
E14: =SUMME(E2:E13)
F14: =SUMME(F2:F13)
G14: =SUMME(G2:G13)
H14: =SUMME(H2:H13)
G16: =ANZAHL(A2:A13)*(ANZAHL(A2:A13)+1)^2/4
G18: =(F14-G16)/((G14-G16)^0,5*(H14-G16)^0,5)

Im vorliegenden Beispiel sind die Werte aller Beobachtungen der Merkmale in Spalte B (bzw.  in Spalte C) verschieden, d.h. es liegen keine Rangbindungen vor. Die Ränge werden in Spalte D (bzw. Spalte E) über die Excel-Funktion RANG bestimmt.  In Spalte F sind die Produkte der Rangzahlen aufgeführt, in Spalte G (bzw. H) die quadrierten Rangzahlen der Merkmale (bzw. ).  Die zugehörigen Summen befinden sich in Zeile 14.

Der Term berechnet sich nach der Excel-Formel in Hilfszelle G16. Die abschließende Excel-Formel in Zelle G18 bestimmt den Rangkorrelationskoeffizient nach Formel (2).3

Offenbar ist die Summe der Rangdifferenzen stets null, d.h.  bzw. {=SUMME((RANG(x;x;1)-RANG(y;y;1)))}=0
und für die Summe der quadrierten Rangzahlen gilt 
bzw.
G14: =n*(n+1)*(2*n+1)/6
H14: =n*(n+1)*(2*n+1)/6

Werden die Rechenschritte aus Arbeitstabelle 1 zusammengefasst, lässt sich mit Hilfe der definierten Namen, jedoch ohne Verwendung der Hilfsspalten D:H, das Zusammenhangsmaß nach Formel (2) mit folgender Arrayformel berechnen:

{=(SUMME(RANG(x;x;1)*RANG(y;y;1))-q)/
((SUMME(RANG(x;x;1)^2)-q)^0,5*(SUMME(RANG(y;y;1)^2)-q)^0,5)}

Nachfolgend wird davon ausgegangen, dass die Rangzahlen so bestimmt werden, als läge eine absteigende Ordnungsstatistik vor. Die Excel-Formel nach Formel (2) lässt sich dann verkürzen zu:

{=(SUMME(RANG(x;x)*RANG(y;y))-q)/
((SUMME(RANG(x;x)^2)-q)^0,5*(SUMME(RANG(y;y)^2)-q)^0,5)}

Ebenfalls ohne Rückgriff auf eine Arbeitstabelle kann der Rangkorrelationskoeffizient nach Formel (3) bestimmt werden mit:

{=1-6*SUMME((RANG(x;x)-RANG(y;y))^2)/(n^3-n)}

wobei der Nenner ausmultipliziert wurde.

Für den Produkt-Moment-Korrelationskoeffizienten stellt Excel die Excel-Funktion KORREL zur Verfügung, welche für den Rangkorrelationskoeffizienten nach Formel (1) wie folgt angewendet werden kann:

{=KORREL(RANG(x;x);RANG(y;y))}

Ab Excel 2010 ersetzt RANG.GLEICH die Excel-Funktion RANG, so dass geschrieben werden kann:

{=KORREL(RANG.GLEICH(x;x);RANG.GLEICH(y;y))}

 

- mit Rangbindungen

Sofern Bindungen vorliegen, ist Formel (3) nicht mehr zulässig.4 Eine Berechnung der Ränge erfolgt anhand der Methode der Durchschnittsränge (Methode Mean). Die einzelnen Rechenschritte für Formel (2) zur Bestimmung des Rangkorrelationskoeffizienten bei Bindungen können wie in Arbeitstabelle 2 durchgeführt werden:


Arbeitstabelle 2 - mit Rangbindungen

wobei folgende Namen definiert sind:

x: =B2:B13
y: =C2:C13
n: =ANZAHL(x)
q: =ANZAHL(x)*(ANZAHL(x)+1)^2/4

Formeln in der Tabelle:
D2: =RANG(B2;$B$2:$B$13;1)+(ZÄHLENWENN($B$2:$B$13;B2)-1)/2
E2: =RANG(C2;$C$2:$C$13;1)+(ZÄHLENWENN($C$2:$C$13;C2)-1)/2
F2: =D2*E2
G2: =D2^2
H2: =E2^2
Formeln nach unten kopieren
D14: =SUMME(D2:D13)
E14: =SUMME(E2:E13)
F14: =SUMME(F2:F13)
G14: =SUMME(G2:G13)
H14: =SUMME(H2:H13)
G16: =ANZAHL(A2:A13)*(ANZAHL(A2:A13)+1)^2/4
G18: =(F14-G16)/((G14-G16)^0,5*(H14-G16)^0,5)

Zu beachten ist, dass sich in Arbeitstabelle 2 lediglich die Werte in den Spalten B:C sowie die Excel-Formeln zur Bestimmung der Rangzahlen in den Spalten D:E geändert haben. Es liegen nunmehr drei Beobachtungen mit dem Wert 42 beim Merkmal  bzw. vier Beobachtungen mit dem Wert 56 beim Merkmal  vor. Ihnen wird die Rangzahl 5 bzw. 9,5 zugeordnet.

Bei Rangbindungen werden die Ränge nach der Methode Mean bestimmt, so dass weiterhin für die arithmetischen Mittel der Ränge gilt und die Summe der Rangdifferenzen null ist, d.h.  bzw. {=SUMME((RANG(x;x;1)+(ZÄHLENWENN(x;x)-1)/2)-(RANG(y;y;1) +(ZÄHLENWENN(y;y)-1)/2))}=0.
Demgegenüber kann sich die Summe der quadrierten Rangzahlen eines Merkmals von der des anderen Merkmals bei Bindungen unterscheiden, wobei bzw. gilt.

Werden wiederum die Rechenschritte aus Arbeitstabelle 2 zusammengefasst, lässt sich mit Hilfe der definierten Namen, jedoch ohne Verwendung der Hilfsspalten D:H, das Zusammenhangsmaß nach Formel (2) durch eine Arrayformel aufstellen:

{=(SUMME((RANG(x;x;1)+(ZÄHLENWENN(x;x)-1)/2)*
(RANG(y;y;1)+(ZÄHLENWENN(y;y)-1)/2))-q)/
((SUMME((RANG(x;x;1)+(ZÄHLENWENN(x;x)-1)/2)^2)-q)^0,5*
(SUMME((RANG(y;y;1)+(ZÄHLENWENN(y;y)-1)/2)^2)-q)^0,5)}

Werden die Rangzahlen nachfolgend so bestimmt werden, als läge eine absteigende Ordnungsstatistik vor, lässt sich die Excel-Formel nach Formel (2) verkürzen zu:

{=(SUMME((RANG(x;x)+(ZÄHLENWENN(x;x)-1)/2)*
(RANG(y;y)+(ZÄHLENWENN(y;y)-1)/2))-q)/
((SUMME((RANG(x;x)+(ZÄHLENWENN(x;x)-1)/2)^2)-q)^0,5*
(SUMME((RANG(y;y)+(ZÄHLENWENN(y;y)-1)/2)^2)-q)^0,5)}

Wesentlich kürzere Excel-Formeln finden sich, wenn die Excel-Funktion KORREL herangezogen wird:

{=KORREL(RANG(x;x)+(ANZAHL(x)+1-RANG(x;x)-RANG(x;x;1))/2;
RANG(y;y)+(ANZAHL(y)+1-RANG(y;y)-RANG(y;y;1))/2)}

 oder

{=KORREL(RANG(x;x)+(ZÄHLENWENN(x;x)-1)/2;
RANG(y;y)+(ZÄHLENWENN(y;y)-1)/2)}

Diese Excel-Formeln bestimmen den Rangkorrelationskoeffizienten nach Spearman allgemein, d.h. sie können ebenfalls dann eingesetzt werden, wenn keine Rangbindungen vorliegen.

Ab Excel 2010 verkürzt sich die letztgenannte Excel-Formel auf Grund der Excel-Funktion RANG.MITTELW zu:

{=KORREL(RANG.MITTELW(x;x);RANG.MITTELW(y;y))}

 

1 Dabei ist es unerheblich, ob für beide Merkmale eine aufsteigende oder absteigende Ordnungsstatistik zugrunde gelegt wird. Die verwendete Notation und die Arbeitstabellen der beiden Beispiele beziehen sich auf Rangzahlen, die so bestimmt werden, als läge eine aufsteigende Ordnungstatistik vor. Demgegenüber greifen die weiterführenden Excel-Formeln auf eine absteigende Ordnungsstatistik zurück. Somit muss in der Syntax der Excel-Funktion RANG die Reihenfolge nicht belegt werden.

2 Vgl. Excel-Funktion RANG & Co.

3 Ergebnisse sind auf fünf Stellen nach dem Komma gerundet.

4 Es kann gezeigt werden, dass eine um Korrekturterme erweiterte Formel (3) den Rangkorrelationskoeffizienten nach Spearman auch bei Rangbindungen bestimmen kann. Für diese modifizierte Formel (3) lässt sich ebenfalls eine korrespondierende Excel-Formel finden, welche jedoch länger ist als die hier vorgestellten Excel-Formeln. Auf eine Darstellung wird daher bewusst verzichtet.

 

Haben Sie Fragen oder Anregungen zu diesem Artikel?

Dann senden Sie einfach eine Nachricht an:

E-Mail:

 

Lesen Sie auch:
___________________________________________________________________

Excel-Formel für den Gini-Koeffizienten