Statistische Excel-Funktionen: RSQ

Zusammenfassung

In diesem Artikel wird die RSQ-Funktion in Microsoft Office Excel 2003 und 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 werden mit den Ergebnissen von RSQ in früheren Excel-Versionen verglichen.

Weitere Informationen

Die RSQ(array1, array2)-Funktion gibt das Quadrat des Pearson Product-Moment Korrelationskoeffizient zwischen zwei Arrays von Daten zurück.

Syntax

RSQ(array1, array2)

Die Argumente array1 und array2 müssen entweder 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	Potenz 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		vor 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. Wählen Sie zelle A1 in Ihrem leeren Excel-Arbeitsblatt aus, und fügen Sie dann die Einträge ein, damit die Tabelle die Zellen A1:D13 in Ihrem Arbeitsblatt ausfüllt.

3. Nachdem Sie die Tabelle in Ihr neues Excel-Arbeitsblatt eingefügt haben, klicken Sie auf die Schaltfläche Einfügeoptionen , und klicken Sie dann auf Zielformatierung übereinstimmen. Wenn der eingefügte Bereich noch ausgewählt ist, verwenden Sie eines der folgenden Verfahren, je nachdem, welche Excel-Version Sie ausführen:

   • Klicken Sie in Microsoft Office Excel 2007 auf die Registerkarte Start, klicken Sie in der Gruppe Zellen auf Format, und klicken Sie dann auf Spaltenbreite automatisch anpassen.
   • Zeigen Sie in Excel 2003 im Menü Format auf Spalte, und klicken Sie dann auf Auswahl automatisch anpassen.

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 berechnet, indem im Wesentlichen PEARSON berechnet und das Ergebnis quadratiert wird. Da PEARSON und CORREL beide den Pearson Product-Moment Korrelationskoeffizient berechnen, sollten ihre Ergebnisse übereinstimmen. RSQ hätte implementiert werden können (wurde aber nicht), da im Wesentlichen CORREL berechnet und das Ergebnis quadratiert wurde.

In Excel-Versionen vor Excel 2003 kann PEARSON Rundungsfehler aufweisen. Dieses Verhalten führt zu Rundungsfehlern in RSQ. Das Verhalten von PEARSON und damit von RSQ wurde für Excel 2003 und für spätere Excel-Versionen verbessert. CORREL wurde immer mithilfe der verbesserten Prozedur implementiert, die in Excel 2003 und 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 Excel-Versionen vor Excel 2003 können Sie das Arbeitsblatt in diesem Artikel verwenden, um ein Experiment auszuführen und zu ermitteln, 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 beeinflusst werden. Wenn Sie den Wert in D2 erhöhen, wird B1:B6 eine größere Konstante hinzugefügt. Wenn D2 <= 7 ist, gibt es keine Rundungsfehler, 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 einander immer zu) zeigen Rundungsfehler in A7:A8. D6:D13 zeigt Werte von RSQ = PEARSON^2 und CORREL^2 an, wenn D2 = 7,5 bzw. 8 ist.

Beachten Sie, dass SICH CORREL immer noch gut verhält, aber Rundungsfehler in PEARSON sind so schwerwiegend geworden, dass die Division durch 0 in RSQ und PEARSON^2 auftritt, wenn D2 = 8.

Frühere Excel-Versionen weisen in diesen Fällen falsche Antworten auf, da die Auswirkungen von Rundungsfehlern bei der Berechnungsformel, die von diesen Excel-Versionen verwendet wird, umfassender sind. 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 keine Änderungen an den Werten von RSQ und PEARSON^2 angezeigt, wenn Sie das Experiment ausprobieren. Die Zellen D6:D13 zeigen jedoch Abrundungsfehler an, 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, haben frühere Excel-Versionen einen einzelnen Durchlauf der Daten verwendet, um die Summe der X-Quadrate, die Summe der Quadrate von Y, die Summe der X-Werte, die Summe der X-Werte, die Summe der Y-Werte, die Summe der XY-Werte und die Anzahl der Beobachtungen in jedem Array zu berechnen. Diese Mengen wurden dann in der Berechnungsformel kombiniert, die in früheren Excel-Versionen in der Hilfedatei angegeben ist. Die Hilfedatei für RSQ zeigt die Formel für den Pearson-Product-Moment Korrelationskoeffizient. Dieses Ergebnis ist quadratisch, um RSQ zu erhalten.

Ergebnisse in Excel 2003 und höheren Versionen von Excel

Die Prozedur, die in Excel 2003 und höheren 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 jedem Array berechnet, aus denen die Mittelwerte (Mittelwerte) von X- und Y-Beobachtungen berechnet werden können. Im zweiten Durchlauf wird dann die quadratische Differenz zwischen jedem X und dem X-Mittelwert gefunden, und diese quadratischen Unterschiede werden summiert. Die quadratische Differenz zwischen jedem Y- und Y-Mittelwert wird gefunden, und diese quadratischen Unterschiede werden summiert. Darüber hinaus werden die Produkte (X – X Mittelwert) * (Y – Y-Mittelwert) für jedes Paar von Datenpunkten gefunden 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, weil derselbe Wert dem Y-Mittelwert (oder dem X-Mittelwert) hinzugefügt wird. In den numerischen Beispielen sind diese drei Summen selbst bei einer hohen Leistung von 10 in Zelle D12 nicht betroffen, und die Ergebnisse des zweiten Durchgangs sind unabhängig vom Eintrag in Zelle D2. Daher sind die Ergebnisse in Excel 2003 und in höheren Versionen von Excel numerisch stabiler.

Schlussfolgerungen

Das Ersetzen eines One-Pass-Ansatzes durch einen Zweidurchlauf-Ansatz garantiert eine bessere numerische Leistung von PEARSON und damit RSQ in Excel 2003 und in späteren Excel-Versionen. Die Ergebnisse, die Sie in Excel 2003 und höheren Versionen von Excel erhalten, sind nie weniger genau als Ergebnisse, die Sie in früheren Excel-Versionen erhalten haben.

In den meisten praktischen Beispielen sehen Sie wahrscheinlich keinen Unterschied zwischen den Ergebnissen in späteren Versionen von Excel und den Ergebnissen in früheren Versionen von Excel. Dieses Verhalten tritt auf, da typische Daten wahrscheinlich nicht das ungewöhnliche Verhalten aufweisen, das dieses Experiment veranschaulicht. Numerische Instabilität tritt wahrscheinlich in früheren Versionen von Excel auf, wenn Daten eine hohe Anzahl signifikanter Ziffern in Kombination mit relativ geringen Abweichungen zwischen Datenwerten enthalten.

Das Verfahren, die Summe der quadratischen Abweichungen über einen Stichprobenmittelwert zu ermitteln, indem man den Stichprobenmittelwert bestimmt, jede quadratische Abweichung berechnet und die Quadratabweichungen summiert, ist genauer als das alternative Verfahren. Dieses alternative Verfahren wurde häufig als "Rechnerformel" bezeichnet, da es für die Verwendung eines Rechners für eine kleine Anzahl von Datenpunkten geeignet war. Die alternative Prozedur hat das folgende Verfahren verwendet:

• Die Summe der Quadrate aller Beobachtungen, die Stichprobengröße und die Summe aller Beobachtungen gefunden
• Berechnet 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 wurden verbessert, da in späteren Excel-Versionen die 1-Durchlauf-Prozedur durch die Zweidurchlauf-Prozedur ersetzt wird, die den Stichprobenmittelwert beim ersten Durchlauf findet und dann die Summe der quadratischen Abweichungen über den Stichprobenmittelwert im zweiten Durchlauf berechnet.

Die folgende Liste enthält eine Liste solcher Funktionen:

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

Ähnliche Verbesserungen wurden bei jedem der drei Analyse-Abweichungs-Tools im AnalysetoolPak vorgenommen.