Freigeben über

Statistische Excel-Funktionen: RSQ

  • Gilt für:: Microsoft Office Excel 2007, Microsoft Office Excel 2003

Zusammenfassung

In diesem Artikel werden die RSQ-Funktion in Microsoft Office Excel 2003 und in höheren Versionen von Excel beschrieben. In diesem Artikel wird erläutert, wie die Funktion verwendet wird und die Ergebnisse von RSQ in diesen späteren Excel-Versionen mit den Ergebnissen von RSQ in früheren Excel-Versionen verglichen werden.

Mehr Informationen

Die FUNKTION RSQ(Array1, Array2) gibt das Quadrat des Pearson Product-Moment Korrelationskoeffizienten zwischen zwei Datenarrays zurück.

Syntax

RSQ(array1, array2)

Die Argumente, Array1 und Array2 müssen Zahlen oder Namen, Arraykonstanten oder Bezüge sein, die Zahlen enthalten.

Die häufigste Verwendung von RSQ umfasst zwei Zellbereiche, die die Daten enthalten, z. B. RSQ(A1:A100, B1:B100).

Beispiel für die Verwendung

Führen Sie die folgenden Schritte aus, um die RSQ-Funktion zu veranschaulichen:

  1. Erstellen Sie ein leeres Excel-Arbeitsblatt, und kopieren Sie dann die folgende Tabelle.

    Ein B C D
    1 = 3 + 10^$D$2 Leistung von 10 zum Hinzufügen zu Daten
    2 =4 + 10^$D$2 0
    3 =2 + 10^$D$2
    4 =5 + 10^$D$2
    5 =4+10^$D$2
    6 =7+10^$D$2 pre-Excel 2003
    =RSQ(A1:A6;B1:B6) wenn D2 = 7,5
    =PEARSON(A1:A6;B1:B6)^2 RSQ = PEARSON^2 0.492857142857143
    =CORREL(A1:A6;B1:B6)^2 CORREL^2 0.509470304975923
    wenn D2 = 8
    RSQ = PEARSON^2 #DIV/0!
    CORREL^2 0.509470304975923

  2. Markieren Sie Zelle A1 in Ihrem leeren Excel-Arbeitsblatt, und fügen Sie dann die Einträge ein, sodass die Tabelle die Zellen A1:D13 in Ihrem Arbeitsblatt ausfüllt.

  3. Nachdem Sie die Tabelle in ihr neues Excel-Arbeitsblatt eingefügt haben, wählen Sie die Schaltfläche " Einfügeoptionen " und dann " Zielformatierung abgleichen" aus. Wenn der eingefügte Bereich noch ausgewählt ist, verwenden Sie eines der folgenden Verfahren, je nachdem, welche Version von Excel Sie ausführen:

    • Wählen Sie in Microsoft Office Excel 2007 die Registerkarte " Start " aus, wählen Sie " Format " in der Gruppe "Zellen " und dann " Spaltenbreite automatisch anpassen" aus.
    • Zeigen Sie in Excel 2003 im Menü „Format“ auf „Spalte“, und wählen Sie dann „AutoAnpassen der Breite“ aus.

Hinweis

Sie können Zellen B1:B6 als Zahl mit 0 Dezimalstellen formatieren.

Die Zellen A1:A6 und B1:B6 enthalten die beiden Datenarrays, die in diesem Beispiel zum Aufrufen von RSQ, PEARSON und CORREL in den Zellen A8:A10 verwendet werden. RSQ wird im Wesentlichen dadurch berechnet, dass PEARSON berechnet und das Ergebnis quadriert wird. Da PEARSON und CORREL beide den Pearson Product-Moment Korrelationskoeffizient berechnen, sollten sich ihre Ergebnisse einigen. RSQ könnte (aber wurde nicht) implementiert werden, indem im Wesentlichen CORREL berechnet und das Ergebnis quadriert wird.

In Versionen von Excel, die älter als Excel 2003 sind, kann PEARSON Abrundenfehler aufweisen. Dieses Verhalten führt zu Rundungsfehlern in RSQ. Das Verhalten von PEARSON und daher von RSQ wurde für Excel 2003 und für spätere Versionen von Excel verbessert. CORREL wurde immer mithilfe des verbesserten Verfahrens implementiert, das in Excel 2003 und in späteren Versionen von Excel zu finden ist. Daher besteht eine Alternative zu RSQ für eine frühere Version von Excel darin, stattdessen CORREL zu verwenden und dann das Ergebnis zu quadratieren.

In Versionen von Excel, die älter als Excel 2003 sind, können Sie das Arbeitsblatt in diesem Artikel verwenden, um ein Experiment auszuführen und zu erörtern, wann Rundungsfehler auftreten. Wenn Sie jeder der Beobachtungen in B1:B6 eine Konstante hinzufügen, sollten die Werte von RSQ, PEARSON^2 und CORREL^2 in den Zellen A7:A9 nicht betroffen sein. Wenn Sie den Wert in D2 erhöhen, wird eine größere Konstante zu B1:B6 hinzugefügt. Wenn D2 <= 7, gibt es keine Abrundungsfehler, die in A7:A9 angezeigt werden. Ändern Sie nun den Wert 7,25, 7,5, 7,75 und dann 8. CORREL^2 in A9 ist nicht betroffen, aber RSQ und PEARSON^2 (diese Ausdrücke stimmen immer miteinander überein) zeigen Rundungsfehler in A7:A8. D6:D13 zeigt werte von RSQ = PEARSON^2 und CORREL^2, wenn D2 = 7,5 bzw. 8.

Beachten Sie, dass CORREL weiterhin korrekt funktioniert, aber die Rundungsfehler in PEARSON sind so schwerwiegend, dass die Division durch 0 in RSQ und PEARSON^2 erfolgt, wenn D2 = 8.

In früheren Excel-Versionen gibt es in diesen Fällen falsche Antworten, da die Auswirkungen von Abrundenfehlern mit der Berechnungsformel, die von diesen Excel-Versionen verwendet wird, tiefer greifen. Dennoch können die Fälle, die in diesem Experiment verwendet werden, als extrem angesehen werden.

Wenn Sie über Excel 2003 oder eine höhere Version von Excel verfügen, werden beim Testen des Experiments keine Änderungen an den Werten von RSQ und PEARSON^2 angezeigt. In den Zellen D6:D13 werden jedoch Rundungsfehler angezeigt, die Sie mit früheren Excel-Versionen erhalten hätten.

Ergebnisse in früheren Versionen von Excel

Wenn Sie die beiden Datenarrays X und Y benennen, verwendeten frühere Excel-Versionen einen einzelnen Durchlauf durch die Daten, um die Summe der Quadrate von X zu berechnen, die Summe der Quadrate von Y, die Summe der X-Werte, die Summe der Y-Werte, die Summe der XY-Werte, die Summe der XY-Werte und die Anzahl der Beobachtungen in den einzelnen Arrays. Diese Mengen wurden dann in der Berechnungsformel kombiniert, die in der Hilfedatei in früheren Versionen von Excel angegeben wird. Die Hilfedatei für RSQ zeigt die Formel für den Pearson Product-Moment Korrelationskoeffizienten an. Dieses Ergebnis wird quadriert, um RSQ zu erhalten.

Ergebnisse in Excel 2003 und höheren Versionen von Excel

Die Prozedur, die in Excel 2003 und späteren Versionen von Excel verwendet wird, verwendet einen Zweidurchlaufprozess durch die Daten. Zunächst werden die Summen der X- und Y-Werte und die Anzahl der Beobachtungen in den einzelnen Arrays berechnet, und aus diesen können die Mittelwerte (Mittelwerte) von X- und Y-Beobachtungen berechnet werden. Anschließend wird beim zweiten Durchgang der quadratische Unterschied zwischen jedem X- und dem X-Mittel gefunden, und diese quadratischen Unterschiede werden addiert. Der quadratische Unterschied zwischen jedem Y und dem Y-Mittelwert wird gefunden, und diese quadratischen Unterschiede werden addiert. Darüber hinaus werden die Produkte (X – X Mittelwert) * (Y – Y Mittelwert) für jedes Paar von Datenpunkten berechnet und summiert. Diese drei Summen werden in der Formel für PEARSON kombiniert. Beachten Sie, dass keine der drei Summen betroffen ist, wenn Sie jedem Wert im Y-Array (oder im X-Array) eine Konstante hinzufügen. Dieses Verhalten tritt auf, da dieser Wert dem Y-Mittelwert (oder dem X-Mittelwert) hinzugefügt wird. In den numerischen Beispielen werden diese drei Summen auch bei einer hohen Potenz von 10 in der Zelle D12 nicht beeinflusst, und die Ergebnisse des zweiten Durchgangs sind unabhängig vom Eintrag in der Zelle D2. Daher sind die Ergebnisse in Excel 2003 und in späteren Versionen von Excel numerisch stabiler.

Fazit

Durch das Ersetzen eines 1-Pass-Ansatzes durch einen zweistufigen Ansatz wird eine bessere numerische Leistung von PEARSON und daher RSQ in Excel 2003 und späteren Versionen von Excel gewährleistet. Die Ergebnisse, die Sie in Excel 2003 und späteren Versionen von Excel erhalten, sind nie weniger genau als Ergebnisse, die Sie in früheren Excel-Versionen erhalten haben.

In den meisten praktischen Beispielen wird wahrscheinlich kein Unterschied zwischen den Ergebnissen in späteren Excel-Versionen und den Ergebnissen in früheren Excel-Versionen angezeigt. Dieses Verhalten tritt auf, da typische Daten nicht die Art des ungewöhnlichen Verhaltens aufweisen, das dieses Experiment veranschaulicht. Numerische Instabilität wird in früheren Excel-Versionen am wahrscheinlichsten angezeigt, wenn Daten eine hohe Anzahl signifikanter Ziffern enthalten, kombiniert mit relativ geringen Abweichungen zwischen Datenwerten.

Das Verfahren zum Auffinden der Summe der Quadratabweichungen zu einem Stichprobenmittelwert durch Ermitteln des Stichprobenmittels, durch Berechnen jeder quadratischen Abweichung und durch Summieren der quadratischen Abweichungen ist genauer als das alternative Verfahren. Dieses alternative Verfahren wurde häufig als "Rechnerformel" bezeichnet, da es für die Verwendung eines Rechners auf einigen Datenpunkten geeignet war. Das alternative Verfahren hat das folgende Verfahren verwendet:

  • Es wurden die Summe der Quadrate aller Beobachtungen, die Stichprobengröße und die Summe aller Beobachtungen gefunden.
  • Berechnete die Summe der Quadrate aller Beobachtungen minus ([Summe aller Beobachtungen]^2)/Stichprobengröße).

Es gibt viele andere Funktionen, die für Excel 2003 und für spätere Versionen von Excel verbessert wurden. Diese Funktionen werden verbessert, da spätere Versionen von Excel die Ein-Pass-Prozedur durch die Zwei-Pass-Prozedur ersetzen, bei der im ersten Durchlauf der Stichprobenmittelwert ermittelt wird und im zweiten Durchlauf dann die Summe der quadrierten Abweichungen vom Stichprobenmittelwert berechnet wird.

Die folgende Liste ist eine Liste solcher Funktionen:

  • VAR
  • VARP
  • STDEV
  • STDEVP
  • DVAR
  • DVARP
  • DSTDEV
  • DSTDEVP
  • PROGNOSE
  • STEIGUNG
  • ABFANGEN
  • PEARSON
  • RSQ
  • STEYX

Ähnliche Verbesserungen wurden in jedem der drei Varianzanalyse-Tools im Analyse-ToolPak vorgenommen.