Jak najít korelaci v Excelu

Spearmanův korelační koeficient, rS, je nejstarším neparametrickým testem založeným na pořadí. Tento koeficient měří sílu a směr monotonní korelace mezi dvěma proměnnými. U vzorku jednotlivců, z nichž každý byl měřen na dvou proměnných v Excelu, je myšlenkou seřadit každé skóre v rámci vlastní proměnné. Poté pro každého jednotlivce odečtěte jednu hodnost od druhé. Pokud je korelace dokonalá (v kladném směru), jsou všechny rozdíly nulové.

Příklad použití

Níže uvedený obrázek ukazuje příklad. Průmyslový psycholog hodnotil družnost 20 zaměstnanců společnosti FarDrate Timepiece Corporation. Stupnice se pohybovala od 1 (nejméně společenský) do 100 (nejvíce společenský). Každý zaměstnanec FarDrate také hodnotil svou pracovní spokojenost na stupnici od 1 (nejmenší spokojenost) do 80 (nejvyšší spokojenost). Nulová hypotéza je, že sociabilita nekoreluje s pracovní spokojeností. Alternativní hypotéza je, že tyto dvě proměnné spolu korelují.

Data a výpočet

Údaje jsou ve sloupcích B a C a pořadí jsou ve sloupcích E a F. Rozdíly mezi každým párem pořadí jsou ve sloupci G.

Výpočet Spearmanova rS

Vzorec pro výpočet je:

Kde d je rozdíl mezi páry. Stejně jako v případě pravidelného korelačního koeficientu, pokud platí nulová hypotéza, hodnota rS by měla být kolem nuly.

Postup pro vypočítání pořadí

1. Pro výpočet pořadí ve sloupci E zadejte =RANK.AVG(B2;$B$2:$B$21;1) do E2 a automaticky vyplnte.
2. Pro hodnosti ve sloupci F zadejte =RANK.AVG(C2;$C$2:$C$21;1) do F2 a automaticky vyplnte.

Jednoduchý výpočet korelačního koeficientu

Pro výpočet korelačního koeficientu nemusíte do buňky J4 zadávat složitý vzorec Excelu. Stačí napsat:

=CORREL(E2:E21;F2:F21) do J4. Tímto způsobem získáte stejný výsledek bez nutnosti počítat rozdíly v pořadí mezi páry ve sloupci G.

Vypočítání p-hodnoty

N je počet párů a test má N-2 stupně volnosti. Můžete definovat Number_of_pairs jako název pro hodnotu v buňce I2. Takže pro hodnocení můžete zadat:

1. =J4*SQRT(Number_of_pairs-2)/SQRT(1-J4^2) do J6.
2. =T.DIST.2T(J6;Number_of_pairs-2) do J7 pro p-hodnotu.

Pokud předem neznáte směr korelace, můžete použít funkci dvoustranného rozdělení t. A ještě jednou, nízká hodnota p vám říká, abyste zamítli nulovou hypotézu.

Závěr

Pochopení metodiky výpočtu korelací v Excelu je zásadní pro analýzu dat a interpretaci výsledků. Tento článek vám poskytuje nezbytné informace a postupy pro efektivní využití tohoto nástroje. Pro další zdroje a informace se podívejte na naše další články.