Excel je účinný nástroj, ktorý má pri práci so štatistikami niekoľko úžasných funkcií a funkcií.
Nájdenie korelácie medzi dvoma radmi údajov je jedným z najbežnejších štatistických výpočtov pri práci s veľkými množinami údajov,
Pred niekoľkými rokmi som pracoval ako finančný analytik, a hoci sme neboli veľmi zapojení do štatistických údajov, zisťovanie korelácie bolo niečo, čo sme stále museli robiť dosť často.
V tomto návode vám ukážem dva skutočne jednoduché spôsoby vypočítajte korelačný koeficient v programe Excel. Na to už je vstavaná funkcia a môžete tiež použiť nástroj na analýzu údajov.
Začnime teda!
Čo je korelačný koeficient?
Pretože nejde o triedu štatistík, dovoľte mi stručne vysvetliť, čo je korelačný koeficient, a potom prejdeme k časti, kde vypočítame korelačný koeficient v programe Excel.
Korelačný koeficient je hodnota, ktorá vám povie, ako úzko súvisia dve série údajov.
Bežne používaným príkladom je hmotnosť a výška 10 osôb v skupine. Ak pre týchto ľudí vypočítame korelačný koeficient pre údaje o výške a hmotnosti, dostaneme hodnotu medzi -1 a 1.
Hodnota nižšia ako nula znamená negatívnu koreláciu, čo znamená, že ak sa výška zvyšuje, hmotnosť sa znižuje alebo ak sa hmotnosť zvyšuje, potom sa výška znižuje.
A hodnota viac ako nula naznačuje pozitívnu koreláciu, čo znamená, že ak sa výška zvyšuje, hmotnosť sa zvyšuje a ak výška klesá, hmotnosť sa znižuje.
Čím bližšie je hodnota k 1, tým silnejšia je pozitívna korelácia. Hodnota 0,8 by teda naznačovala, že údaje o výške a hmotnosti sú v silnej korelácii.
Poznámka: Existujú rôzne typy korelačných koeficientov a štatistík, ale v tomto návode sa pozrieme na najbežnejší, ktorým je Pearsonov korelačný koeficient.Teraz sa pozrime, ako vypočítať tento korelačný koeficient v programe Excel.
Výpočet korelačného koeficientu v programe Excel
Ako som už spomenul, existuje niekoľko spôsobov, ako môžete vypočítať korelačný koeficient v programe Excel.
Použitie vzorca CORREL
CORREL je štatistická funkcia, ktorá bola zavedená v programe Excel 2007.
Predpokladajme, že máte súbor údajov, ako je uvedené nižšie, kde chcete vypočítať korelačný koeficient medzi výškou a hmotnosťou 10 ľudí.
Nasleduje vzorec, ktorý by to urobil:
= CORREL (B2: B12, C2: C12)
Vyššie uvedená funkcia CORREL má dva argumenty - sériu s údajmi o výške a sériu s údajmi o hmotnosti.
A je to!
Hneď ako stlačíte kláves Enter, Excel vykoná všetky výpočty v back-ende a poskytne vám jedno číslo Pearsonovho korelačného koeficientu.
V našom prípade je táto hodnota o niečo viac ako 0,5, čo naznačuje, že existuje pomerne silná pozitívna korelácia.
Túto metódu je najlepšie použiť, ak máte dve série a chcete iba korelačný koeficient.
Ak však máte viacero sérií a chcete zistiť korelačný koeficient všetkých týchto sérií, potom môžete tiež zvážiť použitie balíka nástrojov na analýzu údajov v programe Excel (popísaný ďalej)
Použitie balíka nástrojov na analýzu údajov
Program Excel má nástroj na analýzu údajov, ktorý je možné použiť na rýchly výpočet rôznych štatistických hodnôt (vrátane získania korelačného koeficientu).
Nástroj na analýzu údajov je však v programe Excel v predvolenom nastavení zakázaný. Prvým krokom by teda bolo povoliť nástroj na analýzu údajov späť a potom ho použiť na výpočet Pearsonovho korelačného koeficientu v programe Excel.
Povolenie balíka nástrojov na analýzu údajov
Nasledujú kroky na povolenie Nástroja na analýzu údajov v programe Excel:
- Kliknite na kartu Súbor
- Kliknite na Možnosti
- V dialógovom okne Možnosti programu Excel, ktoré sa otvorí, kliknite na možnosť Doplnky na table na bočnom paneli
- V rozbaľovacom zozname Spravovať vyberte položku Doplnky programu Excel
- Kliknite na Choď. Tým sa otvorí dialógové okno doplnkov
- Začiarknite možnosť Analysis Toolpak
- Kliknite na Ok
Vyššie uvedené kroky by pridali novú skupinu na karte Údaje na páse s nástrojmi programu Excel s názvom Analýza. V tejto skupine by ste mali možnosť Analýza údajov
Výpočet korelačného koeficientu pomocou nástroja na analýzu údajov
Teraz, keď máte analytický nástroj opäť k dispozícii na páse s nástrojmi, pozrime sa, ako pomocou neho vypočítať korelačný koeficient.
Predpokladajme, že máte súbor údajov, ako je uvedené nižšie, a chcete zistiť koreláciu medzi týmito tromi radmi (výška a hmotnosť, výška a príjem a hmotnosť a príjem)
Nasledujú kroky, ako to urobiť:
- Kliknite na kartu Údaje
- V skupine Analýza kliknite na možnosť Analýza údajov
- V dialógovom okne Analýza údajov, ktoré sa otvorí, kliknite na „Korelácia“
- Kliknite na tlačidlo OK. Tým sa otvorí dialógové okno Korelácia
- Pre vstupný rozsah vyberte tri série - vrátane hlavičiek
- V časti „Zoskupené podľa“ skontrolujte, či je vybratá možnosť „Stĺpce“
- Vyberte možnosť - „Označiť v prvom riadku“. Zaistíte tak, že vo výsledných údajoch budú mať rovnaké hlavičky a bude oveľa jednoduchšie porozumieť výsledkom
- V možnostiach výstupu vyberte, kde chcete výslednú tabuľku. Pôjdem s bunkou G1 na ten istý pracovný list. Môžete sa tiež rozhodnúť, že chcete svoje výsledky dostávať do nového pracovného hárka alebo nového zošita
- Kliknite na tlačidlo OK
Hneď ako to urobíte, Excel vypočíta korelačný koeficient pre všetky série a poskytne vám tabuľku, ako je uvedené nižšie:
Výsledná tabuľka je statická a neaktualizuje sa v prípade zmeny ktoréhokoľvek z údajových bodov vo vašej tabuľke. V prípade akejkoľvek zmeny budete musieť vyššie uvedené kroky znova zopakovať, aby ste vygenerovali novú tabuľku korelačných koeficientov.
Ide teda o dve rýchle a jednoduché metódy na výpočet korelačného koeficientu v programe Excel.
Dúfam, že ste našli tento návod užitočný!
