Statistiska Excel-funktioner: RSQ

2025-05-27
Gäller för: Microsoft Office Excel 2007, Microsoft Office Excel 2003

Sammanfattning

I den här artikeln beskrivs RSQ-funktionen i Microsoft Office Excel 2003 och i senare versioner av Excel. Den här artikeln beskriver hur funktionen används och jämför resultatet av RSQ i dessa senare versioner av Excel med resultatet av RSQ i tidigare versioner av Excel.

Mer information

Funktionen RSQ(array1, array2) returnerar kvadraten för Pearson-Product-Moment korrelationskoefficient mellan två datamatriser.

Syntax

RSQ(array1, array2)

Argumenten, matris1 och matris2, måste vara antingen tal eller namn, matriskonstanter eller referenser som innehåller tal.

Den vanligaste användningen av RSQ innehåller två cellområden som innehåller data, till exempel RSQ(A1:A100, B1:B100).

Exempel på användning

Följ dessa steg för att illustrera RSQ-funktionen:

Skapa ett tomt Excel-kalkylblad och kopiera sedan följande tabell.

A	B	C	D
1		= 3 + 10^$D$2	Power of 10 för att lägga till data
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)			när D2 = 7,5
=PEARSON(A1:A6,B1:B6)^2		RSQ = PEARSON^2	0.492857142857143
=CORREL(A1:A6,B1:B6)^2		CORREL^2	0.509470304975923

			när D2 = 8
		RSQ = PEARSON^2	#DIV/0!
		CORREL^2	0.509470304975923

Välj cell A1 i ditt tomma Excel-kalkylblad och klistra sedan in posterna så att tabellen fyller cellerna A1:D13 i kalkylbladet.
När du har klistrat in tabellen i det nya Excel-kalkylbladet väljer du knappen Klistra in alternativ och väljer sedan Matcha målformatering. När det inklistrade intervallet fortfarande är valt använder du någon av följande procedurer, beroende på vilken version av Excel du kör:
- I Microsoft Office Excel 2007 väljer du fliken Start , väljer Format i gruppen Celler och väljer sedan Autopassa kolumnbredd.
- I Excel 2003 pekar du på Kolumn på menyn Format och väljer sedan Autopassning för markering.

Anmärkning

Du kanske vill formatera cellerna B1:B6 som Tal med 0 decimaler.

Cellerna A1:A6 och B1:B6 innehåller de två datamatriser som används i det här exemplet för att anropa RSQ, PEARSON och CORREL i cellerna A8:A10. RSQ beräknas genom att i huvudsak beräkna PEARSON och ta kvadraten av resultatet. Eftersom PEARSON och CORREL båda beräknar Pearson-Product-Moment korrelationskoefficient bör deras resultat överensstämma. RSQ skulle kunna implementeras (men var inte) som att i huvudsak beräkna CORREL och kvadrera resultatet.

I versioner av Excel som är tidigare än Excel 2003 kan PEARSON uppvisa avrundningsfel. Det här beteendet leder till avrundningsfel i RSQ. Pearsons och därmed RSQ:s beteende har förbättrats för Excel 2003 och för senare versioner av Excel. CORREL har alltid implementerats med hjälp av den förbättrade proceduren som finns i Excel 2003 och i senare versioner av Excel. Därför är ett alternativ till RSQ för en tidigare version av Excel att använda CORREL i stället och sedan kvadratera resultatet.

I versioner av Excel som är tidigare än Excel 2003 kan du använda kalkylbladet i den här artikeln för att köra ett experiment och identifiera när avrundningsfel inträffar. Om du lägger till en konstant i var och en av observationerna i B1:B6 ska värdena för RSQ, PEARSON^2 och CORREL^2 i cellerna A7:A9 inte påverkas. Om du ökar värdet i D2 läggs en större konstant till i B1:B6. Om D2 <= 7 finns det inga avrundningsfel som visas i A7:A9. Ändra nu värdet 7,25, 7,5, 7,75 och sedan 8. CORREL^2 i A9 påverkas inte, men RSQ och PEARSON^2 (dessa uttryck är alltid överens med varandra) visar avrundningsfel i A7:A8. D6:D13 visar värden för RSQ = PEARSON^2 och CORREL^2 när D2 = 7,5 respektive 8.

Observera att CORREL fortfarande är välfungerande, men avrundningsfel i PEARSON har blivit så allvarliga att division med 0 inträffar i RSQ och PEARSON^2 när D2 = 8.

Tidigare versioner av Excel har felaktiga svar i dessa fall eftersom effekterna av avrundningsfel är mer djupgående med den beräkningsformel som används av dessa versioner av Excel. Ändå kan de fall som används i det här experimentet ses som extrema.

Om du har Excel 2003 eller en senare version av Excel ser du inga ändringar i värdena för RSQ och PEARSON^2 om du provar experimentet. Cellerna D6:D13 visar dock avrundningsfel som du skulle ha fått med tidigare versioner av Excel.

Resultat i tidigare versioner av Excel

Om du namnger de två dataarrays X och Y använde tidigare versioner av Excel en enkel genomgång av data för att beräkna summan av kvadrater av X, summan av kvadrater av Y, summan av X, summan av Y, summan av XY och antalet observationer i varje array. Dessa kvantiteter kombinerades sedan i den beräkningsformel som anges i hjälpfilen i tidigare versioner av Excel. Hjälpfilen för RSQ visar formeln för Pearson-Product-Moment korrelationskoefficient. Det här resultatet upphöjs till två för att få fram RSQ.

Resultat i Excel 2003 och senare versioner av Excel

Proceduren som används i Excel 2003 och i senare versioner av Excel använder en tvåstegsprocess genom data. För det första beräknas summan av X och Y och antalet observationer i varje matris, och utifrån dessa kan medelvärdena för X- och Y-observationer beräknas. Sedan, vid det andra passet, hittas kvadratskillnaden mellan varje X och X-medelvärdet, och dessa kvadratiska skillnader summeras. Kvadratskillnaden mellan varje Y- och Y-medelvärde hittas och dessa kvadratiska skillnader summeras. Dessutom hittas produkterna (X – X medelvärde) * (Y– Y-medelvärde) för varje par datapunkter och summeras. Dessa tre summor kombineras i formeln för PEARSON. Observera att ingen av de tre summorna påverkas om du lägger till en konstant till varje värde i Y-matrisen (eller i X-matrisen). Det här beteendet beror på att samma värde läggs till i Y-medelvärdet (eller till X-medelvärdet). I de numeriska exemplen påverkas inte dessa tre summor, även med en hög effekt på 10 i cell D12, och resultatet av det andra passet är oberoende av posten i cell D2. Därför är resultaten i Excel 2003 och i senare versioner av Excel mer stabila numeriskt.

Slutsatser

Om du ersätter en one-pass-metod med två pass garanterar det bättre numeriska prestanda för PEARSON, och därför RSQ, i Excel 2003 och i senare versioner av Excel. Resultaten som du får i Excel 2003 och senare versioner av Excel blir aldrig mindre exakta än resultaten som du fick i tidigare versioner av Excel.

I de flesta praktiska exempel är det inte troligt att du ser någon skillnad mellan resultaten i senare versioner av Excel och resultaten i tidigare versioner av Excel. Det här beteendet beror på att typiska data sannolikt inte uppvisar den typ av ovanligt beteende som det här experimentet illustrerar. Numerisk instabilitet visas troligen i tidigare versioner av Excel när data innehåller ett stort antal signifikanta siffror i kombination med relativt liten variation mellan datavärden.

Förfarandet för att hitta summan av kvadratavvikelser om ett urvalsmedelvärde genom att hitta urvalsmedelvärdet, genom att beräkna varje kvadratavvikelse och genom att summera kvadratavvikelserna är mer exakt än det alternativa förfarandet. Den här alternativa proceduren kallades ofta "kalkylatorformeln" eftersom den var lämplig för användning av en kalkylator på några få datapunkter. Den alternativa proceduren använde följande procedur:

Hittade summan av kvadraterna för alla observationer, urvalsstorleken och summan av alla observationer
Beräknade summan av kvadraterna för alla observationer minus ([summan av alla observationer]^2)/stickprovsstorlek.

Det finns många andra funktioner som har förbättrats för Excel 2003 och senare versioner av Excel. Dessa funktioner förbättras eftersom senare versioner av Excel ersätter en-pass-proceduren med två-pass-proceduren som hittar exempel medelvärdet vid det första passet och sedan beräknar summan av kvadratavvikelser om exempel medelvärdet vid det andra passet.

Följande lista är en lista över sådana funktioner:

VAR
VARP
STDEV
STDEVP
DVAR
DVARP
DSTDEV
DSTDEVP
PROGNOS
BACKE
INTERCEPTERA
PEARSON
RSQ
STEYX

Liknande förbättringar gjordes i vart och ett av de tre analysverktygen för varians i Analysis ToolPak.