Statistické funkce Excelu: RSQ

Souhrn

Tento článek popisuje funkci RSQ v Aplikaci Microsoft Office Excel 2003 a v novějších verzích aplikace Excel. Tento článek popisuje, jak se funkce používá, a porovnává výsledky funkce RSQ v těchto novějších verzích aplikace Excel s výsledky funkce RSQ v dřívějších verzích aplikace Excel.

Další informace

Funkce RSQ(matice1; matice2) vrátí druhou mocninu Pearsonova Product-Moment korelačního koeficientu mezi dvěma maticemi dat.

Syntaxe

RSQ(array1, array2)

Argumenty, matice1 a matice2, musí být čísla nebo názvy, maticové konstanty nebo odkazy obsahující čísla.

Nejběžnější použití rsq zahrnuje dvě oblasti buněk, které obsahují data, například RSQ(A1:A100; B1:B100).

Příklad použití

Chcete-li znázornit funkci RSQ, postupujte takto:

1. Vytvořte prázdný excelový list a zkopírujte následující tabulku.

   A	B	C	D
   1		= 3 + 10^$D $2	Výkon 10 pro přidání k datům
   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)			když D2 = 7,5
   =PEARSON(A1:A6;B1:B6)^2		RSQ = PEARSON^2	0.492857142857143
   =CORREL(A1:A6;B1:B6)^2		KOREL^2	0.509470304975923
   			když D2 = 8
   		RSQ = PEARSON^2	#DIV/0!
   		KOREL^2	0.509470304975923

2. Vyberte buňku A1 v prázdném excelovém listu a vložte položky tak, aby tabulka vyplnila buňky A1:D13 v listu.

3. Po vložení tabulky do nového excelového listu klikněte na tlačítko Možnosti vložení a potom klikněte na Přizpůsobit cílové formátování. Se stále vybranou vkládanou oblastí použijte jeden z následujících postupů podle verze aplikace Excel, kterou používáte:

   • V aplikaci Microsoft Office Excel 2007 klikněte na kartu Domů , klikněte na Formát ve skupině Buňky a potom klikněte na Přizpůsobit šířku sloupce.
   • V excelu 2003 přejděte v nabídce Formát na Sloupec a potom klikněte na Přizpůsobit výběr.

Buňky A1:A6 a B1:B6 obsahují dvě pole dat, která se v tomto příkladu používají k volání RSQ, PEARSON a CORREL v buňkách A8:A10. RSQ se vypočítá v podstatě výpočtem PEARSON a dřepováním výsledku. Vzhledem k tomu, že PEARSON i CORREL počítají Pearsonův Product-Moment korelační koeficient, měly by se jejich výsledky shodovat. RSQ mohlo být (ale nebylo) implementováno jako v podstatě výpočet CORREL a squaring výsledku.

Ve verzích aplikace Excel, které jsou starší než Excel 2003, pearson může vykazovat chyby zaokrouhlování. Toto chování vede k chybám zaokrouhlování v RSQ. Chování PEARSON, a proto RSQ, bylo vylepšeno pro Excel 2003 a novější verze aplikace Excel. CORREL byl vždy implementován pomocí vylepšeného postupu, který se nachází v aplikaci Excel 2003 a novějších verzích aplikace Excel. Proto alternativou k RSQ pro starší verzi Aplikace Excel je použít correl místo a pak kvadratit výsledek.

Ve verzích aplikace Excel, které jsou starší než Excel 2003, můžete pomocí listu v tomto článku spustit experiment a zjistit, kdy dojde k chybám zaokrouhlování. Pokud ke každému pozorování v buňce B1:B6 přidáte konstantu, hodnoty RSQ, PEARSON^2 a CORREL^2 v buňkách A7:A9 by neměly být ovlivněny. Pokud zvýšíte hodnotu v D2, přidá se do B1:B6 větší konstanta. Pokud D2 <= 7, nedochází v A7:A9 k žádným chybám zaokrouhlení. Teď změňte hodnotu 7,25, 7,5, 7,75 a pak 8. CORREL^2 v A9 není ovlivněn, ale RSQ a PEARSON^2 ( (tyto výrazy se vždy shodují) zobrazují chyby zaokrouhlit v A7:A8. D6:D13 zobrazí hodnoty RSQ = PEARSON^2 a CORREL^2, když D2 = 7,5 a 8.

Všimněte si, že correl je stále dobře-chovat, ale chyby zaokrouhlit v PEARSON jsou tak závažné, že dělení o 0 nastane v RSQ a PEARSON^2, když D2 = 8.

Dřívější verze Excelu vykazují v těchto případech nesprávné odpovědi, protože účinky chyb zaokrouhlování jsou hlubší s výpočetním vzorcem, který tyto verze Excelu používají. Přesto lze případy, které se v tomto experimentu používají, považovat za extrémní.

Pokud máte Excel 2003 nebo novější verzi Excelu, při pokusu o experiment se nezobrazí žádné změny hodnot RSQ a PEARSON^2. Buňky D6:D13 ale zobrazují chyby zaokrouhlování, které byste získali ve starších verzích Excelu.

Výsledky v dřívějších verzích Excelu

Pokud pojmenujete dvě pole dat X a Y, použily dřívější verze Excelu jedno předávání dat k výpočtu součtu druhých mocnin X, součtu druhých mocnin Y, součtu X, součtu hodnot Y, součtu hodnot X, součtu hodnot XY a počtu pozorování v každé matici. Tyto množství se pak zkombinovaly ve výpočetním vzorci, který je uveden v souboru nápovědy v dřívějších verzích aplikace Excel. Soubor nápovědy pro RSQ zobrazuje vzorec pearsonova Product-Moment korelačního koeficientu. Tento výsledek je na druhou mocninu pro získání RSQ.

Výsledky v Excelu 2003 a novějších verzích Excelu

Procedura, která se používá v aplikaci Excel 2003 a novějších verzích aplikace Excel, používá proces dvou průchodů daty. Nejprve se vypočítá součet hodnot X a Y a počet pozorování v každé matici a z nich lze vypočítat průměry (průměry) pozorování X a Y. Potom se při druhém průchodu zjistí kvadratický rozdíl mezi jednotlivými X a středními hodnotami X a tyto kvadratický rozdíly se sečtou. Zjistí se kvadratický rozdíl mezi jednotlivými středními hodnotami Y a Y a tyto kvadratický rozdíly se sečtou. Kromě toho se pro každou dvojici datových bodů najdou produkty (X – střední hodnota X) * (střední hodnota Y – Y) a sečtou se. Tyto tři součty jsou sloučeny ve vzorci pro PEARSON. Všimněte si, že žádný ze tří součtů není ovlivněn, pokud ke každé hodnotě v matici Y (nebo v matici X) přidáte konstantu. K tomuto chování dochází, protože stejná hodnota je přidána k střední hodnotě Y (nebo ke střední hodnotě X). V číselných příkladech nejsou tyto tři součty ovlivněny ani při vysokém výkonu 10 v buňce D12 a výsledky druhého průchodu jsou nezávislé na položce v buňce D2. Proto jsou výsledky v excelu 2003 a novějších verzích aplikace Excel číselně stabilnější.

Závěry

Nahrazení přístupu jedním průchodem za přístup se dvěma průchody zaručuje lepší číselný výkon pearsonu, a tedy RSQ, v excelu 2003 a novějších verzích aplikace Excel. Výsledky, které získáte v aplikaci Excel 2003 a novějších verzích aplikace Excel, nebudou nikdy méně přesné než výsledky, které jste získali v dřívějších verzích aplikace Excel.

Ve většině praktických příkladů pravděpodobně neuvidíte rozdíl mezi výsledky v novějších verzích Excelu a výsledky ve starších verzích Excelu. K tomuto chování dochází, protože typická data pravděpodobně vykazují druh neobvyklého chování, které tento experiment ukazuje. Číselná nestabilita se v dřívějších verzích Excelu s největší pravděpodobností objeví, když data obsahují velký počet významných číslic v kombinaci s relativně malými odchylkami mezi hodnotami dat.

Postup zjištění součtu kvadratických odchylek o střední hodnotě vzorku vyhledáním střední hodnoty výběru, výpočtem každé kvadradratní odchylky a součtem kvadratových odchylek je přesnější než alternativní postup. Tento alternativní postup se často jmenoval "vzorec kalkulačky", protože byl vhodný pro použití kalkulačky na malém počtu datových bodů. Alternativní postup použil následující postup:

• Byl nalezen součet druhých mocnin všech pozorování, velikost vzorku a součet všech pozorování.
• Vypočítá součet čtverců všech pozorování minus ([součet všech pozorování]^2)/velikost vzorku).

Existuje mnoho dalších funkcí, které byly vylepšeny pro Excel 2003 a novější verze Excelu. Tyto funkce jsou vylepšené, protože novější verze Excelu nahradí jednoprůchodovou proceduru dvěma průchody, která najde střední hodnotu vzorku při prvním průchodu a pak vypočítá součet kvadratických odchylek o střední hodnotě vzorku při druhém průchodu.

Následující seznam je seznam takových funkcí:

• VAR
• VARP
• STDEV
• STDEVP
• DVAR
• DVARP
• DSTDEV
• DSTDEVP
• POČASÍ
• SVAHU
• ZACHYTIT
• PEARSON
• RSQ
• STEYX

Podobná vylepšení byla provedena v každém ze tří nástrojů Analýza rozptylu v doplňku Analytické nástroje.