Statistische functies van Excel: RSQ

Samenvatting

In dit artikel wordt de rsQ-functie in Microsoft Office Excel 2003 en in latere versies van Excel beschreven. In dit artikel wordt beschreven hoe de functie wordt gebruikt en de resultaten van RSQ in deze latere versies van Excel worden 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 zijn die getallen bevatten.

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.

   Een	B	C	D
   1		= 3 + 10^$D$2	Gebruik 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 items zodat de tabel de cellen A1:D13 in het werkblad vult.

3. Nadat u de tabel in het nieuwe Excel-werkblad hebt geplakt, selecteert u de knop Plakopties en kiest u vervolgens Doelopmaak overeen laten komen. Als het geplakte bereik nog steeds is geselecteerd, gebruikt u een van de volgende procedures, passend bij de versie van Excel die u gebruikt:

   • Selecteer in Microsoft Office Excel 2007 het tabblad Start , selecteer Opmaak in de groep Cellen en selecteer vervolgens Kolombreedte autoaanpassen.
   • Wijs in Excel 2003 de optie Kolom aan in het menu Opmaak en kies Selectie Autofit.

Opmerking

U kunt cellen B1:B6 opmaken als getal met 0 decimalen.

De cellen A1:A6 en B1:B6 bevatten de twee gegevensmatrices die in dit voorbeeld worden gebruikt om RSQ, PEARSON en CORREL aan te roepen in de cellen A8:A10. RSQ wordt berekend door in wezen PEARSON te berekenen en het resultaat te kwadraeren. Omdat PEARSON en CORREL beide de Pearson Product-Moment correlatiecoëfficiënt berekenen, moeten hun resultaten overeenkomen. RSQ zou kunnen worden geïmplementeerd door CORREL te berekenen en het resultaat te kwadrateren, maar dat werd niet gedaan.

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 daarom 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 wordt gevonden 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, treden er geen afrondingsfouten op 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 overeen met elkaar) geven afrondingsfouten weer in A7:A8. D6:D13 toont waarden van RSQ = PEARSON^2 en CORREL^2 wanneer D2 = respectievelijk 7,5 en 8.

Let op dat CORREL nog steeds correct functioneert, maar afrondingsfouten in PEARSON zo ernstig zijn 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 groter zijn door de rekenkundige formule die door deze versies van Excel wordt gebruikt. Toch kunnen de gevallen die in dit experiment worden gebruikt, als extreem worden gezien.

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

Resultaten in eerdere versies van Excel

Als u de twee gegevensrijen X's en Y's noemt, gebruikten eerdere versies van Excel één doorgang door 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 de producten van X en Y en het aantal waarnemingen in elke rij te berekenen. Deze hoeveelheden werden vervolgens gecombineerd in de rekenkundige formule die wordt gegeven in het Help-bestand in eerdere versies van Excel. In het Help-bestand voor RSQ ziet u de formule voor de Pearson Product-Moment Correlatiecoëfficiënt. Dit resultaat is gekwadrateerd 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 proces met twee stappen door de gegevens. Ten eerste worden de sommen van X's en Y's en het aantal waarnemingen in elke reeks berekend, en van deze kunnen de gemiddelden van X- en Y-waarnemingen worden berekend. Vervolgens wordt op 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. Daarnaast worden de producten (X - X-gemiddelde) * (Y - Y-gemiddelde) gevonden voor elk paar datapunten 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 hoge macht van 10 in cel D12, worden deze drie sommen niet beïnvloed en zijn de resultaten van de tweede pas onafhankelijk van de invoer in cel D2. Daarom zijn de resultaten in Excel 2003 en in latere versies van Excel numeriek stabieler.

Conclusies

Het vervangen van een een-pass-benadering door een tweegangsbenadering garandeert betere numerieke prestaties van PEARSON, en daarom 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 de resultaten die u hebt verkregen in eerdere versies van Excel.

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 geen ongebruikelijk gedrag vertonen dat dit experiment illustreert. Numerieke instabiliteit wordt waarschijnlijk weergegeven 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 berekenen, door elke kwadratische afwijking te berekenen en door de kwadratische afwijkingen 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 paar gegevenspunten. De alternatieve procedure heeft de volgende procedure gebruikt:

• Gevonden: de som van de kwadraten van alle waarnemingen, de steekproefgrootte en de som van alle waarnemingen.
• Berekent 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 latere versies van Excel de één-doorloopprocedure vervangen door de twee-doorloopprocedure die bij de eerste doorloop het steekproefgemiddelde vindt en vervolgens bij de tweede doorloop de som van kwadraatafwijkingen rondom het steekproefgemiddelde berekent.

De volgende lijst is een lijst met dergelijke functies:

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

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