Jak vypočítat korelační koeficient v aplikaci Excel (2 snadné způsoby)

Excel je výkonný nástroj, který má při práci se statistikami několik úžasných funkcí a funkcí.

Nalezení korelace mezi dvěma datovými řadami je jedním z nejběžnějších statistických výpočtů při práci s velkými datovými sadami,

Před několika lety jsem pracoval jako finanční analytik, a přestože jsme se příliš nezabývali statistickými údaji, zjišťování korelace bylo něco, co jsme stále museli dělat docela často.

V tomto tutoriálu vám ukážu dva opravdu snadné způsoby vypočítat korelační koeficient v Excelu. K tomu již existuje vestavěná funkce a můžete také použít nástroj pro analýzu dat.

Začněme tedy!

Co je korelační koeficient?

Protože se nejedná o statistickou třídu, dovolte mi stručně vysvětlit, co je korelační koeficient, a poté přejdeme k části, kde vypočítáme korelační koeficient v Excelu.

Korelační koeficient je hodnota, která vám řekne, jak úzce spolu souvisí dvě datové řady.

Běžně používaným příkladem je hmotnost a výška 10 lidí ve skupině. Pokud pro tyto osoby vypočítáme korelační koeficient pro údaje o výšce a hmotnosti, dostaneme hodnotu mezi -1 a 1.

Hodnota menší než nula znamená negativní korelaci, což znamená, že pokud se výška zvyšuje, váha klesá nebo pokud se váha zvyšuje, pak výška klesá.

A hodnota více než nula naznačuje pozitivní korelaci, což znamená, že pokud se výška zvyšuje, váha se zvyšuje a pokud výška klesá, hmotnost klesá.

Čím blíže je hodnota k 1, tím silnější je pozitivní korelace. Hodnota 0,8 by tedy naznačovala, že údaje o výšce a hmotnosti jsou silně korelovány.

Poznámka: Existují různé typy korelačních koeficientů a statistik, ale v tomto tutoriálu se podíváme na ten nejběžnější, kterým je Pearsonův korelační koeficient

Nyní se podívejme, jak vypočítat tento korelační koeficient v aplikaci Excel.

Výpočet korelačního koeficientu v aplikaci Excel

Jak jsem zmínil, existuje několik způsobů, jak můžete vypočítat korelační koeficient v aplikaci Excel.

Použití vzorce CORREL

CORREL je statistická funkce, která byla zavedena v aplikaci Excel 2007.

Předpokládejme, že máte sadu dat uvedenou níže, kde chcete vypočítat korelační koeficient mezi výškou a hmotností 10 osob.

Níže je vzorec, který by to udělal:

= CORREL (B2: B12, C2: C12)

Výše uvedená funkce CORREL přebírá dva argumenty - řadu s datovými body výšky a řadu s body s údaji o hmotnosti.

A to je vše!

Jakmile stisknete Enter, Excel provede všechny výpočty v back-endu a poskytne vám jedno číslo Pearsonova korelačního koeficientu.

V našem případě je tato hodnota o něco více než 0,5, což naznačuje, že existuje poměrně silná pozitivní korelace.

Tuto metodu je nejlepší použít, pokud máte dvě řady a chcete pouze korelační koeficient.

Pokud ale máte více sérií a chcete zjistit korelační koeficient všech těchto řad, můžete také zvážit použití sady nástrojů pro analýzu dat v Excelu (dále popsáno)

Pomocí nástroje pro analýzu dat

Excel má nástroj pro analýzu dat, který lze použít k rychlému výpočtu různých statistických hodnot (včetně získání korelačního koeficientu).

Ale nástroj pro analýzu dat je ve výchozím nastavení v aplikaci Excel zakázán. Prvním krokem by tedy bylo povolit nástroj pro analýzu dat zpět a poté jej použít k výpočtu Pearsonova korelačního koeficientu v aplikaci Excel.

Povolení balíčku nástrojů pro analýzu dat

Níže jsou uvedeny kroky k povolení nástroje Data Analysis Toolpak v aplikaci Excel:

1. Klikněte na kartu Soubor
2. Klikněte na Možnosti
3. V dialogovém okně Možnosti aplikace Excel, které se otevře, klikněte na možnost Doplňky v podokně postranního panelu
4. V rozevíracím seznamu Spravovat vyberte doplňky aplikace Excel
5. Klikněte na Přejít. Otevře se dialogové okno doplňků
6. Zaškrtněte možnost Analysis Toolpak
7. Klikněte na Ok

Výše uvedené kroky by přidaly novou skupinu na kartě Data na pásu karet aplikace Excel s názvem Analýza. V rámci této skupiny byste měli možnost Analýza dat

Výpočet korelačního koeficientu pomocí nástroje pro analýzu dat

Nyní, když máte analytický nástroj zpět na pásu karet, podívejme se, jak pomocí něj vypočítat korelační koeficient.

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete zjistit korelaci mezi těmito třemi řadami (výška a váha, výška a příjem a hmotnost a příjem)

Níže jsou uvedeny kroky, jak to provést:

1. Klikněte na kartu Data
2. Ve skupině Analýza klikněte na možnost Analýza dat
3. V dialogovém okně Analýza dat, které se otevře, klikněte na „Korelace“
4. Klikněte na OK. Otevře se dialogové okno Korelace
5. Pro vstupní rozsah vyberte tři řady - včetně záhlaví
6. U položky Seskupeno podle zkontrolujte, zda je vybrána možnost Sloupce
7. Vyberte možnost - „Označit v prvním řádku“. Tím se zajistí, že ve výsledných datech budou mít stejná záhlaví a bude mnohem snazší porozumět výsledkům
8. V možnostech výstupu vyberte, kam chcete výslednou tabulku. Půjdu s buňkou G1 na stejný list. Můžete se také rozhodnout, že chcete výsledky zobrazit v novém listu nebo novém sešitu
9. Klikněte na OK

Jakmile to provedete, Excel vypočítá korelační koeficient pro všechny řady a poskytne vám tabulku, jak je uvedeno níže:

Výsledná tabulka je statická a neaktualizuje se v případě změny některého z datových bodů ve vaší tabulce. V případě jakékoli změny budete muset výše uvedené kroky znovu zopakovat, abyste vygenerovali novou tabulku korelačních koeficientů.

Jedná se tedy o dvě rychlé a snadné metody pro výpočet korelačního koeficientu v aplikaci Excel.

Doufám, že jste našli tento návod užitečný!