Statistische functies van Excel: RSQ

Samenvatting

In dit artikel wordt de functie RSQ in Microsoft Office Excel 2003 en in latere versies van Excel beschreven. In dit artikel wordt beschreven hoe de functie wordt gebruikt en worden de resultaten van RSQ in deze latere versies van Excel vergeleken met de resultaten van RSQ in eerdere versies van Excel.

Meer informatie

De functie RSQ(matrix1, matrix2) retourneert het kwadraat van de Pearson Product-Moment correlatiecoëfficiënt tussen twee matrices met gegevens.

Syntaxis

RSQ(array1, array2)

De argumenten matrix1 en matrix2 moeten getallen of namen, matrixconstanten of verwijzingen met getallen zijn.

Het meest voorkomende gebruik van RSQ omvat twee cellenbereiken die de gegevens bevatten, zoals RSQ(A1:A100, B1:B100).

Voorbeeld van gebruik

Voer de volgende stappen uit om de rsq-functie te illustreren:

1. Maak een leeg Excel-werkblad en kopieer de volgende tabel.

   A	B	C	D
   1		= 3 + 10^$D$2	Macht van 10 om toe te voegen aan gegevens
   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)			wanneer D2 = 7,5
   =PEARSON(A1:A6;B1:B6)^2		RSQ = PEARSON^2	0.492857142857143
   =CORREL(A1:A6;B1:B6)^2		CORREL^2	0.509470304975923
   			wanneer D2 = 8
   		RSQ = PEARSON^2	#DIV/0!
   		CORREL^2	0.509470304975923

2. Selecteer cel A1 in het lege Excel-werkblad en plak de vermeldingen zodat de tabel cellen A1:D13 in het werkblad vult.

3. Nadat u de tabel in het nieuwe Excel-werkblad hebt geplakt, klikt u op de knop Plakopties en klikt u vervolgens op Doelopmaak overeenkomen. Terwijl het geplakte bereik nog steeds is geselecteerd, gebruikt u een van de volgende procedures, voor de versie van Excel die u uitvoert:

   • Klik in Microsoft Office Excel 2007 op het tabblad Start , klik op Opmaak in de groep Cellen en klik vervolgens op Kolombreedte automatisch aanpassen.
   • Wijs in Excel 2003 kolom aan in het menu Opmaak en klik vervolgens op Selectie automatisch aanpassen.

Cellen A1:A6 en B1:B6 bevatten de twee gegevensmatrixen die in dit voorbeeld worden gebruikt om RSQ, PEARSON en CORREL aan te roepen in de cellen A8:A10. RSQ wordt berekend door pearson te berekenen en het resultaat te kwadraten. Omdat PEARSON en CORREL beide de Pearson Product-Moment correlatiecoëfficiënt berekenen, moeten hun resultaten overeenkomen. RSQ kan (maar is niet) geïmplementeerd als het berekenen van CORREL en het kwadraten van het resultaat.

In versies van Excel die ouder zijn dan Excel 2003, kan PEARSON afrondingsfouten vertonen. Dit gedrag leidt tot afrondingsfouten in RSQ. Het gedrag van PEARSON, en dus van RSQ, is verbeterd voor Excel 2003 en voor latere versies van Excel. CORREL is altijd geïmplementeerd met behulp van de verbeterde procedure die te vinden is in Excel 2003 en in latere versies van Excel. Daarom is een alternatief voor RSQ voor een eerdere versie van Excel het gebruik van CORREL en vervolgens om het resultaat te kwadrateren.

In versies van Excel die ouder zijn dan Excel 2003, kunt u het werkblad in dit artikel gebruiken om een experiment uit te voeren en te ontdekken wanneer er afrondingsfouten optreden. Als u een constante toevoegt aan elk van de waarnemingen in B1:B6, mogen de waarden van RSQ, PEARSON^2 en CORREL^2 in de cellen A7:A9 niet worden beïnvloed. Als u de waarde in D2 verhoogt, wordt er een grotere constante toegevoegd aan B1:B6. Als D2 <= 7, zijn er geen afrondingsfouten die worden weergegeven in A7:A9. Wijzig nu de waarde van 7,25, 7,5, 7,75 en vervolgens 8. CORREL^2 in A9 wordt niet beïnvloed, maar RSQ en PEARSON^2 (deze expressies komen altijd met elkaar overeen) geven afrondingsfouten weer in A7:A8. D6:D13 geeft waarden weer van RSQ = PEARSON^2 en CORREL^2 wanneer D2 = respectievelijk 7,5 en 8.

Houd er rekening mee dat CORREL nog steeds goed werkt, maar afrondingsfouten in PEARSON zijn zo ernstig geworden dat deling door 0 optreedt in RSQ en PEARSON^2 wanneer D2 = 8.

In eerdere versies van Excel worden in deze gevallen onjuiste antwoorden weergegeven omdat de effecten van afrondingsfouten dieper zijn met de rekenformule die wordt gebruikt door deze versies van Excel. Toch kunnen de gevallen die in dit experiment worden gebruikt, als extreem worden beschouwd.

Als u Excel 2003 of een latere versie van Excel hebt, ziet u geen wijzigingen in de waarden van RSQ en PEARSON^2 als u het experiment probeert. Cellen D6:D13 bevatten echter afrondingsfouten die u zou hebben verkregen met eerdere versies van Excel.

Resultaten in eerdere versies van Excel

Als u de twee gegevensmatrices X's en Y's noemt, gebruikten eerdere versies van Excel één pass through de gegevens om de som van kwadraten van X's, de som van kwadraten van Y's, de som van X's, de som van Y's, de som van XY's en het aantal waarnemingen in elke matrix te berekenen. Deze hoeveelheden zijn vervolgens gecombineerd in de rekenkundige formule die wordt vermeld in het Help-bestand in eerdere versies van Excel. Het Help-bestand voor RSQ bevat de formule voor de Pearson Product-Moment correlatiecoëfficiënt. Dit resultaat wordt kwadraat om RSQ te verkrijgen.

Resultaten in Excel 2003 en in latere versies van Excel

De procedure die wordt gebruikt in Excel 2003 en in latere versies van Excel maakt gebruik van een tweeledige procedure voor het doorlopen van de gegevens. Eerst worden de som van X's en Y's en het aantal waarnemingen in elke matrix berekend. Op basis hiervan kunnen de gemiddelden (gemiddelden) van X- en Y-waarnemingen worden berekend. Vervolgens wordt in de tweede pas het kwadratische verschil tussen elke X en het X-gemiddelde gevonden en worden deze kwadratische verschillen opgeteld. Het kwadratische verschil tussen elke Y en het Y-gemiddelde wordt gevonden en deze kwadratische verschillen worden opgeteld. Bovendien worden de producten (X - X-gemiddelde) * (Y - Y-gemiddelde) gevonden voor elk paar gegevenspunten en opgeteld. Deze drie sommen worden gecombineerd in de formule voor PEARSON. U ziet dat geen van de drie sommen wordt beïnvloed als u een constante toevoegt aan elke waarde in de Y-matrix (of in de X-matrix). Dit gedrag treedt op omdat dezelfde waarde wordt toegevoegd aan het Y-gemiddelde (of aan het X-gemiddelde). In de numerieke voorbeelden, zelfs met een hoog vermogen van 10 in cel D12, worden deze drie sommen niet beïnvloed en zijn de resultaten van de tweede doorgang onafhankelijk van de vermelding in cel D2. Daarom zijn de resultaten in Excel 2003 en in latere versies van Excel numeriek stabieler.

Conclusies

Het vervangen van een one-pass-benadering door een tweeledige benadering garandeert betere numerieke prestaties van PEARSON, en dus RSQ, in Excel 2003 en in latere versies van Excel. De resultaten die u in Excel 2003 en in latere versies van Excel verkrijgt, zijn nooit minder nauwkeurig dan resultaten die u in eerdere versies van Excel hebt verkregen.

In de meeste praktische voorbeelden ziet u waarschijnlijk geen verschil tussen de resultaten in latere versies van Excel en de resultaten in eerdere versies van Excel. Dit gedrag treedt op omdat typische gegevens waarschijnlijk niet het soort ongebruikelijk gedrag vertonen dat dit experiment illustreert. Numerieke instabiliteit treedt waarschijnlijk op in eerdere versies van Excel wanneer gegevens een groot aantal significante cijfers bevatten in combinatie met relatief weinig variatie tussen gegevenswaarden.

De procedure voor het vinden van de som van kwadratische afwijkingen over een steekproefgemiddelde door het steekproefgemiddelde te vinden, door elke kwadratische deviatie te berekenen en door de kwadratische deviaties op te sommen, is nauwkeuriger dan de alternatieve procedure. Deze alternatieve procedure werd vaak de 'rekenmachineformule' genoemd omdat deze geschikt was voor het gebruik van een rekenmachine op een klein aantal gegevenspunten. Voor de alternatieve procedure is de volgende procedure gebruikt:

• De som van de kwadraten van alle waarnemingen, de steekproefgrootte en de som van alle waarnemingen gevonden
• Berekende de som van kwadraten van alle waarnemingen min ([som van alle waarnemingen]^2)/steekproefgrootte).

Er zijn veel andere functies die zijn verbeterd voor Excel 2003 en voor latere versies van Excel. Deze functies zijn verbeterd omdat in latere versies van Excel de one-pass-procedure wordt vervangen door de tweeledige procedure die het steekproefgemiddelde op de eerste doorgeeft en vervolgens de som van kwadratische afwijkingen over het steekproefgemiddelde op de tweede doorgeeft.

De volgende lijst bevat een lijst met dergelijke functies:

• VAR
• VARP
• STDEV
• STDEVP
• DVAR
• DVARP
• DSTDEV
• DSTDEVP
• PROGNOSE
• HELLING
• ONDERSCHEPPEN
• PEARSON
• RSQ
• STEYX

Vergelijkbare verbeteringen zijn aangebracht in elk van de drie analyseprogramma's voor variantie in Analysis ToolPak.