Pozitivní a negativní korelace v excelu. Jak udělat korelaci v Excelu

V vědecký výzkumČasto je potřeba najít souvislost mezi výslednými a faktorovými proměnnými (výnos plodiny a množství srážek, výška a hmotnost člověka v homogenních skupinách podle pohlaví a věku, tepová frekvence a tělesná teplota atd.) .

Druhým jsou znaky, které přispívají ke změnám těch, které jsou s nimi spojené (první).

Pojem korelační analýzy

Existuje mnoho Na základě výše uvedeného můžeme říci, že korelační analýza je metodou používanou k testování hypotézy o statistická významnost dvě nebo více proměnných, pokud je výzkumník může změřit, ale nemůže je změnit.

Existují další definice dotyčného pojmu. Korelační analýza je metoda zpracování, která zahrnuje studium korelačních koeficientů mezi proměnnými. V tomto případě se porovnávají korelační koeficienty mezi jedním párem nebo mnoha páry charakteristik, aby se mezi nimi stanovily statistické vztahy. Korelační analýza je metoda pro studium statistické závislosti mezi náhodnými proměnnými s volitelnou přítomností striktně funkční povahy, ve které je dynamika jedné náhodná proměnná vede k dynamice matematické očekávání další.

Koncept falešné korelace

Při provádění korelační analýzy je třeba vzít v úvahu, že ji lze provádět ve vztahu k libovolnému souboru charakteristik, často ve vztahu k sobě navzájem absurdním. Někdy mezi sebou nemají žádnou příčinnou souvislost.

V tomto případě mluví o falešné korelaci.

Problémy korelační analýzy

Na základě výše uvedených definic lze formulovat následující úkoly popsané metody: získat informace o jedné z hledaných proměnných pomocí jiné; určit blízkost vztahu mezi studovanými proměnnými.

Korelační analýza zahrnuje určení vztahu mezi studovanými charakteristikami, a proto lze úkoly korelační analýzy doplnit o následující:

• identifikace faktorů, které mají největší vliv na výslednou charakteristiku;
• identifikace dříve neprozkoumaných příčin souvislostí;
• konstrukce korelačního modelu s jeho parametrickou analýzou;
• studium významu komunikačních parametrů a jejich intervalové hodnocení.

Vztah mezi korelační analýzou a regresí

Metoda korelační analýzy se často neomezuje pouze na zjištění blízkosti vztahu mezi studovanými veličinami. Někdy je doplněna o sestavení regresních rovnic, které jsou získány pomocí stejnojmenné analýzy a které představují popis korelační závislosti mezi výslednou a faktorovou (faktorovou) charakteristikou (vlastnostmi). Tato metoda spolu s uvažovanou analýzou tvoří metodu

Podmínky použití metody

Efektivní faktory závisí na jednom až několika faktorech. Metodu korelační analýzy lze použít v případě velkého počtu pozorování o hodnotě efektivních a faktorových ukazatelů (faktorů), přičemž zkoumané faktory musí být kvantitativní a reflektované v konkrétních zdrojích. První lze určit normálním zákonem - v tomto případě jsou výsledkem korelační analýzy Pearsonovy korelační koeficienty, nebo, pokud charakteristiky tomuto zákonu nevyhovují, použije se koeficient hodnostní korelace Spearman.

Pravidla pro výběr faktorů korelační analýzy

Při použití tato metoda je nutné určit faktory ovlivňující ukazatele výkonnosti. Jsou vybírány s ohledem na skutečnost, že mezi indikátory musí existovat vztahy příčiny a následku. V případě tvorby multifaktorového korelačního modelu se vybírají ty, které mají významný vliv na výsledný ukazatel, přičemž je vhodnější nezahrnout do korelačního modelu vzájemně závislé faktory s párovým korelačním koeficientem větším než 0,85, stejně jako ty u nichž vztah s výsledným parametrem nemá lineární nebo funkční charakter.

Zobrazení výsledků

Výsledky korelační analýzy lze prezentovat v textové i grafické podobě. V prvním případě jsou prezentovány jako korelační koeficient, ve druhém - ve formě bodového diagramu.

Při absenci korelace mezi parametry jsou body na diagramu umístěny chaoticky, průměrný stupeň spojení je charakterizován větší mírou uspořádanosti a je charakterizován víceméně jednotnou vzdáleností označených značek od mediánu. Silné spojení má tendenci být rovné a při r=1 je bodový graf rovná čára. Reverzní korelace se liší ve směru grafu z levého horního do pravého dolního, přímá korelace - z levého dolního do pravého horního rohu.

3D znázornění bodového grafu

Kromě tradičního 2D zobrazení bodového grafu se nyní používá 3D grafické znázornění korelační analýzy.

Používá se také matice bodového grafu, která zobrazuje všechny spárované grafy v jediném obrázku ve formátu matice. Pro n proměnných obsahuje matice n řádků a n sloupců. Graf umístěný na průsečíku i-tého řádku a j-tého sloupce je grafem proměnných Xi versus Xj. Každý řádek a sloupec je tedy jeden rozměr, jedna buňka zobrazuje bodový graf dvou rozměrů.

Posouzení těsnosti spoje

Těsnost korelačního spojení je určena korelačním koeficientem (r): silný - r = ±0,7 až ±1, střední - r = ±0,3 až ±0,699, slabý - r = 0 až ±0,299. Tato klasifikace není přísná. Obrázek ukazuje trochu jiný diagram.

Příklad použití metody korelační analýzy

Zajímavá studie byla provedena ve Velké Británii. Věnuje se souvislosti mezi kouřením a rakovinou plic a byl proveden pomocí korelační analýzy. Toto pozorování je uvedeno níže.

Počáteční data pro korelační analýzu

Profesní skupina		úmrtnost
Zemědělci, lesníci a rybáři
Horníci a dělníci v lomech
Výrobci plynu, koksu a chemikálií
Výrobci skla a keramiky
Pracovníci pecí, kováren, sléváren a válcoven
Pracovníci elektrotechniky a elektroniky
Strojírenství a příbuzné profese
Dřevozpracující průmysl
Kožedělníci
Textilní dělníci
Výrobci pracovních oděvů
Pracovníci v potravinářském, nápojovém a tabákovém průmyslu
Výrobci papíru a tisku
Výrobci jiných produktů
Stavitelé
Malíři a dekoratéři
Řidiči stacionárních motorů, jeřábů atd.
Pracovníci jinde neuvedení
Pracovníci dopravy a spojů
Skladníci, skladníci, baliči a pracovníci plnicích strojů
Pracovníci v kanceláři
Prodejci
Sportovní a rekreační pracovníci
Administrátoři a manažeři
Profesionálové, technici a umělci

Začneme korelační analýzou. Pro přehlednost je lepší začít s řešením grafická metoda, pro který sestrojíme bodový diagram.

Ukazuje přímou souvislost. Jen na základě grafické metody je však obtížné vyvodit jednoznačný závěr. Proto budeme v korelační analýze pokračovat. Příklad výpočtu korelačního koeficientu je uveden níže.

Pomocí softwaru (dále bude jako příklad popsán MS Excel) určíme korelační koeficient, který je 0,716, což znamená silnou vazbu mezi zkoumanými parametry. Stanovme statistickou spolehlivost získané hodnoty pomocí odpovídající tabulky, pro kterou potřebujeme odečíst 2 od 25 dvojic hodnot, ve výsledku dostaneme 23 a pomocí tohoto řádku v tabulce zjistíme r kritické pro p = 0,01 (protože jedná se o medicínská data, přísnější závislost, v ostatních případech stačí p=0,05), což je pro tuto korelační analýzu 0,51. Příklad ukázal, že vypočtené r je větší než kritické r a hodnota korelačního koeficientu je považována za statisticky spolehlivou.

Použití softwaru při provádění korelační analýzy

Popsaný typ statistického zpracování dat lze provádět pomocí software zejména MS Excel. Korelace zahrnuje výpočet následujících parametrů pomocí funkcí:

1. Korelační koeficient je určen pomocí funkce CORREL (pole1; pole2). Pole1,2 - buňka intervalu hodnot výsledných a faktorových proměnných.

Lineární korelační koeficient se také nazývá Pearsonův korelační koeficient, a proto počínaje Excelem 2007 můžete funkci používat se stejnými poli.

Grafické zobrazení korelační analýzy v Excelu se provádí pomocí panelu „Charts“ s volbou „Scatter Plot“.

Po upřesnění výchozích dat získáme graf.

2. Posouzení významnosti párového korelačního koeficientu pomocí Studentova t-testu. Vypočtená hodnota t-kritéria je porovnána s tabulkovou (kritickou) hodnotou tohoto ukazatele z odpovídající tabulky hodnot uvažovaného parametru, přičemž se bere v úvahu zadaná hladina významnosti a počet stupňů volnosti. Tento odhad se provádí pomocí funkce STUDISCOVER(pravděpodobnost; stupně_volnosti).

3. Matice párových korelačních koeficientů. Analýza se provádí pomocí nástroje Data Analysis, ve kterém je vybrána Korelace. Statistické posouzení párových korelačních koeficientů se provádí jejich porovnáním absolutní hodnota s tabulkovou (kritickou) hodnotou. Když vypočtený párový korelační koeficient překročí kritický, můžeme s přihlédnutím k danému stupni pravděpodobnosti říci, že nulová hypotéza o významnosti lineárního vztahu není zamítnuta.

Konečně

Použití metody korelační analýzy ve vědeckém výzkumu nám umožňuje určit vztah mezi různé faktory a výkonnostní ukazatele. Je nutné vzít v úvahu, že vysoký korelační koeficient lze získat z absurdní dvojice nebo souboru dat, a proto tenhle typ analýza musí být provedena na dostatečně velkém poli dat.

Po získání vypočtené hodnoty r je vhodné ji porovnat s kritickým r pro potvrzení statistické spolehlivosti určité hodnoty. Korelační analýzu lze provádět ručně pomocí vzorců nebo pomocí softwaru, zejména MS Excel. Zde můžete také sestavit rozptylový diagram pro účely vizuální reprezentace vztahu mezi studovanými faktory korelační analýzy a výslednou charakteristikou.

V dnešním článku promluvime si o tom, jak spolu mohou proměnné souviset. Pomocí korelace můžeme určit, zda existuje vztah mezi první a druhou proměnnou. Doufám, že vás tato aktivita bude bavit stejně jako ty předchozí!

Korelace měří sílu a směr vztahu mezi x a y. Obrázek ukazuje Různé typy korelace ve formě bodových grafů uspořádaných dvojic (x, y). Tradičně je proměnná x umístěna na horizontální osa, a y - na svislici.

Graf A je příkladem pozitivní lineární korelace: jak roste x, roste i y, a to lineárně. Graf B nám ukazuje příklad negativní lineární korelace, kde jak x roste, y lineárně klesá. V grafu C vidíme, že mezi x a y není žádná korelace. Tyto proměnné se navzájem nijak neovlivňují.

A konečně graf D je příkladem nelineárních vztahů mezi proměnnými. Jak se x zvyšuje, y nejprve klesá, pak mění směr a roste.

Zbytek článku se zaměřuje na lineární vztahy mezi závislými a nezávislými proměnnými.

Korelační koeficient

Korelační koeficient r nám poskytuje jak sílu, tak směr vztahu mezi nezávislými a závislými proměnnými. Hodnoty r se pohybují mezi -1,0 a + 1,0. Když je r kladné, je vztah mezi x a y kladný (graf A na obrázku), a když je r záporný, je vztah také záporný (graf B). Korelační koeficient blízký nule ukazuje, že mezi x a y není žádný vztah (graf C).

Síla vztahu mezi x a y je určena tím, zda se korelační koeficient blíží - 1,0 nebo +- 1,0. Prostudujte si následující nákres.

Graf A ukazuje dokonalou pozitivní korelaci mezi x a y při r = + 1,0. Graf B - ideální negativní korelace mezi x a y při r = - 1,0. Grafy C a D jsou příklady slabších vztahů mezi závislými a nezávislými proměnnými.

Korelační koeficient r určuje jak sílu, tak směr vztahu mezi závislými a nezávislými proměnnými. Hodnoty r se pohybují od - 1,0 (silně negativní vztah) do + 1,0 (silně pozitivní vztah). Když r = 0, mezi proměnnými x a y není žádná souvislost.

Skutečný korelační koeficient můžeme vypočítat pomocí následující rovnice:

Ale Ale! Vím, že tato rovnice vypadá jako děsivá změť podivných symbolů, ale než zpanikaříme, aplikujme na ni příklad známky ze zkoušky. Řekněme, že chci zjistit, zda existuje vztah mezi počtem hodin, které student věnuje studiu statistiky, a výsledkem závěrečné zkoušky. Níže uvedená tabulka nám pomůže rozdělit tuto rovnici na několik jednoduchých výpočtů a učinit je lépe zvládnutelnými.

Jak vidíte, existuje velmi silná pozitivní korelace mezi počtem hodin věnovaných studiu předmětu a známkou zkoušky. Učitelé o tom budou velmi rádi.

Jaká je výhoda navázání vztahů mezi podobnými proměnnými? Skvělá otázka. Pokud se zjistí, že existuje vztah, můžeme předpovědět výsledky zkoušky na základě určitého počtu hodin strávených studiem předmětu. Jednoduše řečeno, čím silnější spojení, tím přesnější bude naše předpověď.

Použití Excelu k výpočtu korelačních koeficientů

Jsem si jistý, že když se podíváte na tyto hrozné výpočty korelačních koeficientů, budete opravdu potěšeni, když zjistíte, že Excel program může udělat všechnu tuto práci za vás pomocí funkce CORREL s následujícími vlastnostmi:

CORREL (pole 1; pole 2),

pole 1 = rozsah dat pro první proměnnou,

pole 2 = datový rozsah pro druhou proměnnou.

Na obrázku je například znázorněna funkce CORREL použitá k výpočtu korelačního koeficientu pro příklad stupně zkoušky.

Korelační koeficient (příp lineární koeficient korelace) se označuje jako „r“ (ve vzácných případech jako „ρ“) a charakterizuje lineární korelace(tedy vztah, který je dán nějakou hodnotou a směrem) dvou nebo více proměnných. Hodnota koeficientu leží mezi -1 a +1, to znamená, že korelace může být kladná i záporná. Pokud je korelační koeficient -1, existuje dokonalá negativní korelace; pokud je korelační koeficient +1, existuje dokonalá kladná korelace. V jiných případech existuje pozitivní korelace, negativní korelace nebo žádná korelace mezi dvěma proměnnými. Korelační koeficient lze vypočítat ručně, pomocí bezplatných online kalkulaček nebo pomocí dobré grafické kalkulačky.

Kroky

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

Sbírat data. Než začnete počítat korelační koeficient, prostudujte si danou dvojici čísel. Je lepší si je zapsat do tabulky, kterou lze umístit svisle nebo vodorovně. Označte každý řádek nebo sloupec jako "x" a "y".

• Například jsou uvedeny čtyři dvojice hodnot (čísla) proměnných „x“ a „y“. Můžete vytvořit následující tabulku:
  • x || y
  • 1 || 1
  • 2 || 3
  • 4 || 5
  • 5 || 7

1. Vypočítejte aritmetický průměr "x". Chcete-li to provést, sečtěte všechny hodnoty „x“ a výsledný výsledek vydělte počtem hodnot.

   • V našem příkladu jsou uvedeny čtyři hodnoty proměnné „x“. Chcete-li vypočítat aritmetický průměr "x", sečtěte tyto hodnoty a pak vydělte součet 4. Výpočty budou zapsány takto:
   • μ x = (1 + 2 + 4 + 5) / 4 (\displaystyle \mu _(x)=(1+2+4+5)/4)
   • μ x = 12/4 (\displaystyle \mu _(x)=12/4)
   • μ x = 3 (\displaystyle \mu _(x)=3)

2. Najděte aritmetický průměr "y". Chcete-li to provést, běžte podobné akce, to znamená, sečtěte všechny hodnoty „y“ a poté součet vydělte počtem hodnot.

   • V našem příkladu jsou uvedeny čtyři hodnoty proměnné „y“. Sečtěte tyto hodnoty a poté součet vydělte 4. Výpočty budou zapsány takto:
   • μ y = (1 + 3 + 5 + 7) / 4 (\displaystyle \mu _(y)=(1+3+5+7)/4)
   • μ y = 16/4 (\displaystyle \mu _(y)=16/4)
   • μ y = 4 (\displaystyle \mu _(y)=4)

3. Vypočítejte směrodatnou odchylku "x". Po výpočtu průměrných hodnot „x“ a „y“ najděte směrodatné odchylky tyto proměnné. Směrodatná odchylka se vypočítá pomocí následujícího vzorce:

   • σ x = 1 n − 1 Σ (x − μ x) 2 (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(n-1))\Sigma (x-\mu _( x))^(2))))
   • σ x = 1 4 − 1 ∗ ((1 − 3) 2 + (2 − 3) 2 + (4 − 3) 2 + (5 − 3) 2) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(4-1))*((1-3)^(2)+(2-3)^(2)+(4-3)^(2)+(5-3) ^(2)))))
   • σ x = 1 3 ∗ (4 + 1 + 1 + 4) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(4+1+1+4)) ))
   • σ x = 1 3 ∗ (10) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(10))))
   • σ x = 10 3 (\displaystyle \sigma _(x)=(\sqrt (\frac (10)(3))))
   • σ x = 1,83 (\displaystyle \sigma _(x)=1,83)

4. Vypočítejte směrodatnou odchylku „y“. Postupujte podle kroků popsaných v předchozím kroku. Použijte stejný vzorec, ale nahraďte do něj hodnoty „y“.

   • V našem příkladu budou výpočty zapsány takto:
   • σ y = 1 4 − 1 ∗ ((1 − 4) 2 + (3 − 4) 2 + (5 − 4) 2 + (7 − 4) 2) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(4-1))*((1-4)^(2)+(3-4)^(2)+(5-4)^(2)+(7-4) ^(2)))))
   • σ y = 1 3 ∗ (9 + 1 + 1 + 9) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(9+1+1+9)) ))
   • σ y = 1 3 ∗ (20) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(20))))
   • σ y = 20 3 (\displaystyle \sigma _(y)=(\sqrt (\frac (20)(3))))
   • σ y = 2,58 (\displaystyle \sigma _(y)=2,58)

5. Zapište si základní vzorec pro výpočet korelačního koeficientu. Tento vzorec zahrnuje průměry, směrodatné odchylky a počet (n) párů čísel pro obě proměnné. Korelační koeficient je označen jako "r" (ve vzácných případech jako "ρ"). Tento článek používá vzorec pro výpočet Pearsonova korelačního koeficientu.

   • Zde a v jiných zdrojích mohou být množství označena odlišně. Například některé vzorce obsahují „ρ“ a „σ“, zatímco jiné obsahují „r“ a „s“. Některé učebnice uvádějí jiné vzorce, ale jsou to matematické analogy výše uvedeného vzorce.

6. Vypočítali jste průměry a směrodatné odchylky obou proměnných, takže můžete pomocí vzorce vypočítat korelační koeficient. Připomeňme, že „n“ je počet dvojic hodnot pro obě proměnné. Hodnoty ostatních veličin byly vypočteny dříve.

   • V našem příkladu budou výpočty zapsány takto:
   • ρ = (1 n − 1) Σ (x − μ x σ x) ∗ (y − μ y σ y) (\displaystyle \rho =\left((\frac (1)(n-1))\vpravo) \Sigma \left((\frac (x-\mu _(x))(\sigma _(x)))\right)*\left((\frac (y-\mu _(y))(\sigma _(y)))\vpravo))
   • ρ = (1 3) ∗ (\displaystyle \rho =\left((\frac (1)(3))\right)*)[ (1 − 3 1 , 83) ∗ (1 − 4 2 , 58) + (2 − 3 1 , 83) ∗ (3 − 4 2 , 58) (\displaystyle \left((\frac (1-3)( 1,83))\vpravo)*\doleva((\frac (1-4)(2,58))\vpravo)+\doleva((\frac (2-3)(1,83))\vpravo) *\doleva((\ frac (3-4) (2,58))\vpravo))
     + (4 − 3 1 , 83) ∗ (5 − 4 2 , 58) + (5 − 3 1 , 83) ∗ (7 − 4 2 , 58) (\displaystyle +\left((\frac (4-3) )(1,83))\vpravo)*\vlevo((\frac (5-4)(2,58))\vpravo)+\vlevo ((\frac (5-3)(1,83))\ vpravo)*\vlevo ( (\frac (7-4) (2,58))\right))]
   • ρ = (1 3) ∗ (6 + 1 + 1 + 6 4 , 721) (\displaystyle \rho =\left((\frac (1)(3))\right)*\left((\frac (6) +1+1+6)(4721))\vpravo))
   • ρ = (1 3) ∗ 2 , 965 (\displaystyle \rho =\left((\frac (1)(3))\right)*2,965)
   • ρ = (2 , 965 3) (\displaystyle \rho =\left((\frac (2,965)(3))\right))
   • ρ = 0,988 (\displaystyle \rho =0,988)

7. Analyzujte výsledek. V našem příkladu je korelační koeficient 0,988. Tato hodnota nějakým způsobem charakterizuje tuto množinu dvojic čísel. Věnujte pozornost znaménku a velikosti hodnoty.

   • Protože hodnota korelačního koeficientu je kladná, existuje kladná korelace mezi proměnnými „x“ a „y“. To znamená, že s rostoucí hodnotou „x“ roste i hodnota „y“.
   • Vzhledem k tomu, že hodnota korelačního koeficientu je velmi blízká +1, jsou hodnoty proměnných „x“ a „y“ velmi provázané. Pokud vykreslíte body na souřadnicové rovině, budou umístěny blízko určité přímky.

   Použití online kalkulaček pro výpočet korelačního koeficientu

   1. Najděte si na internetu kalkulačku pro výpočet korelačního koeficientu. Tento koeficient se ve statistikách poměrně často počítá. Pokud existuje mnoho dvojic čísel, je téměř nemožné vypočítat korelační koeficient ručně. Proto existují online kalkulačky pro výpočet korelačního koeficientu. Do vyhledávače zadejte „kalkulátor korelačních koeficientů“ (bez uvozovek).

   2. Zadejte údaje. Přečtěte si prosím pokyny na webových stránkách, abyste se ujistili, že zadáváte údaje (číselné páry) správně. Je nesmírně důležité zadat příslušné dvojice čísel; jinak dostanete nesprávný výsledek. Pamatujte, že různé webové stránky mají různé formáty zadávání dat.

      • Například na webu http://ncalculators.com/statistics/correlation-coefficient-calculator.htm jsou hodnoty proměnných „x“ a „y“ zadány ve dvou vodorovných řádcích. Hodnoty jsou odděleny čárkami. To znamená, že v našem příkladu jsou hodnoty „x“ zadány takto: 1,2,4,5 a hodnoty „y“ takto: 1,3,5,7.
      • Na jiné stránce, http://www.alcula.com/calculators/statistics/correlation-coefficient/, se data zadávají vertikálně; v tomto případě nezaměňujte odpovídající dvojice čísel.

   3. Vypočítejte korelační koeficient. Po zadání údajů jednoduše klikněte na tlačítko „Vypočítat“, „Vypočítat“ nebo podobné tlačítko, abyste získali výsledek.

      Použití grafické kalkulačky

      1. Zadejte údaje. Vezměte si grafickou kalkulačku, přejděte do statistického režimu a vyberte příkaz Upravit.

         • Různé kalkulačky vyžadují různé stisky kláves. Tento článek pojednává o kalkulačce Texas Instruments TI-86.
         • Pro přepnutí do režimu statistického výpočtu stiskněte – Stat (nad tlačítkem „+“). Poté stiskněte F2 – Upravit.

      2. Smazat dříve uložená data. Většina kalkulaček ukládá zadané statistiky, dokud je nevymažete. Abyste předešli záměně starých dat za nová, nejprve odstraňte všechny uložené informace.

         • Pomocí kláves se šipkami posuňte kurzor a zvýrazněte nadpis „xStat“. Poté stiskněte Clear a Enter pro odstranění všech hodnot zadaných ve sloupci xStat.
         • Pomocí kláves se šipkami zvýrazněte nadpis „yStat“. Poté stiskněte Clear a Enter pro vymazání všech hodnot zadaných ve sloupci yStat.

      3. Zadejte počáteční údaje. Pomocí kláves se šipkami přesuňte kurzor na první buňku pod nadpisem „xStat“. Zadejte první hodnotu a stiskněte Enter. V dolní části obrazovky se zobrazí „xStat (1) = __“, kde se místo mezery objeví zadaná hodnota. Po stisknutí Enter se zadaná hodnota objeví v tabulce a kurzor se přesune na další řádek; ve spodní části obrazovky se zobrazí „xStat (2) = __“.

         • Zadejte všechny hodnoty pro proměnnou "x".
         • Po zadání všech hodnot pro proměnnou x se pomocí kláves se šipkami přesuňte do sloupce yStat a zadejte hodnoty pro proměnnou y.
         • Po zadání všech dvojic čísel stiskněte Exit pro vymazání obrazovky a opuštění režimu statistického výpočtu.

      4. Vypočítejte korelační koeficient. Charakterizuje, jak blízko jsou data k určitému řádku. Grafický kalkulátor může rychle určit vhodnou čáru a vypočítat korelační koeficient.

         • Klikněte na Stat – Calc. Na TI-86 musíte stisknout – –.
         • Vyberte funkci "Lineární regrese". Na TI-86 stiskněte , což je označeno „LinR“. Na obrazovce se zobrazí řádek „LinR_“ s blikajícím kurzorem.
         • Nyní zadejte názvy dvou proměnných: xStat a yStat.
           • Na TI-86 otevřete seznam jmen; Chcete-li to provést, stiskněte – – .
           • Na spodním řádku obrazovky se zobrazí dostupné proměnné. Vyberte (pravděpodobně k tomu budete muset stisknout F1 nebo F2), zadejte čárku a poté vyberte .
           • Stiskněte Enter pro zpracování zadaných údajů.

      5. Analyzujte své výsledky. Po stisknutí klávesy Enter se na obrazovce zobrazí následující informace:

         • y = a + b x (\displaystyle y=a+bx): Toto je funkce, která popisuje přímku. Upozorňujeme, že funkce není zapsána ve standardním tvaru (y = kh + b).
         • a = (\displaystyle a=). Toto je souřadnice „y“ bodu, kde čára protíná osu Y.
         • b = (\displaystyle b=). Toto je sklon čáry.
         • corr = (\displaystyle (\text(corr))=). Toto je korelační koeficient.
         • n = (\displaystyle n=). Jedná se o počet dvojic čísel, které byly použity při výpočtech.

Regresní a korelační analýza jsou statistické výzkumné metody. Toto jsou nejběžnější způsoby, jak ukázat závislost parametru na jedné nebo více nezávislých proměnných.

Níže o konkrétních praktické příklady Podívejme se na tyto dvě mezi ekonomy velmi oblíbené analýzy. Uvedeme také příklad získání výsledků při jejich kombinování.

Regresní analýza v Excelu

Ukazuje vliv některých hodnot (nezávislých, nezávislých) na závisle proměnnou. Například jak závisí počet ekonomicky aktivního obyvatelstva na počtu podniků, mzdách a dalších parametrech. Nebo: jak zahraniční investice, ceny energií atd. ovlivňují výši HDP.

Výsledek analýzy vám umožní zvýraznit priority. A na základě hlavních faktorů předvídat, plánovat rozvoj prioritních oblastí a činit manažerská rozhodnutí.

Regrese se děje:

• lineární (y = a + bx);
• parabolický (y = a + bx + cx 2);
• exponenciální (y = a * exp(bx));
• mocnina (y = a*x^b);
• hyperbolický (y = b/x + a);
• logaritmické (y = b * ln(x) + a);
• exponenciální (y = a * b^x).

Podívejme se na konstrukci jako příklad regresní model v Excelu a interpretace výsledků. Vezměme lineární typ regrese.

Úkol. U 6 podniků průměr měsíčně mzda a počet zaměstnanců, kteří odešli. Je třeba určit závislost počtu odcházejících zaměstnanců na průměrné mzdě.

Modelka lineární regrese má následující podobu:

Y = a 0 + a 1 x 1 +…+ak x k.

Kde a jsou regresní koeficienty, x jsou ovlivňující proměnné, k je počet faktorů.

V našem příkladu je Y indikátorem odchodu zaměstnanců. Ovlivňujícím faktorem jsou mzdy (x).

Excel má vestavěné funkce, které vám pomohou vypočítat parametry lineárního regresního modelu. Ale doplněk „Analysis Package“ to udělá rychleji.

Aktivujeme výkonný analytický nástroj:

Po aktivaci bude doplněk dostupný na kartě Data.

Nyní udělejme samotnou regresní analýzu.

Nejprve věnujeme pozornost R-squared a koeficientům.

R-squared je koeficient determinace. V našem příkladu – 0,755 nebo 75,5 %. To znamená, že vypočtené parametry modelu vysvětlují 75,5 % vztahu mezi studovanými parametry. Čím vyšší je koeficient determinace, tím lepší je model. Dobré - nad 0,8. Špatná – méně než 0,5 (takovou analýzu lze stěží považovat za rozumnou). V našem příkladu – „není špatné“.

Koeficient 64,1428 ukazuje, jaké bude Y, pokud se všechny proměnné v uvažovaném modelu rovnají 0. To znamená, že hodnota analyzovaného parametru je ovlivněna i dalšími faktory, které nejsou v modelu popsány.

Koeficient -0,16285 ukazuje váhu proměnné X na Y. To znamená, že průměrná měsíční mzda v rámci tohoto modelu ovlivňuje počet odcházejících s váhou -0,16285 (to je malá míra vlivu). Znak „-“ označuje negativní dopad: čím vyšší plat, tím méně lidí skončí. Což je spravedlivé.



Korelační analýza v Excelu

Korelační analýza pomáhá určit, zda existuje vztah mezi ukazateli v jednom nebo dvou vzorcích. Například mezi dobou provozu stroje a náklady na opravy, cenou zařízení a dobou provozu, výškou a hmotností dětí atd.

Pokud existuje souvislost, pak zvýšení jednoho parametru vede ke zvýšení (pozitivní korelace) nebo snížení (negativní) druhého. Korelační analýza pomáhá analytikovi určit, zda lze hodnotu jednoho ukazatele použít k predikci možný význam další.

Korelační koeficient se značí r. Pohybuje se od +1 do -1. Klasifikace korelací pro různé oblasti bude jiný. Když je koeficient 0 lineární závislost mezi vzorky neexistuje.

Podívejme se, jak najít korelační koeficient pomocí Excelu.

K nalezení párových koeficientů se používá funkce CORREL.

Cíl: Zjistit, zda existuje vztah mezi provozní dobou soustruhu a náklady na jeho údržbu.

Umístěte kurzor do libovolné buňky a stiskněte tlačítko fx.

1. V kategorii „Statistické“ vyberte funkci CORREL.
2. Argument „Pole 1“ – první rozsah hodnot – provozní doba stroje: A2:A14.
3. Argument „Pole 2“ – druhý rozsah hodnot – náklady na opravu: B2:B14. Klepněte na tlačítko OK.

Chcete-li určit typ připojení, musíte se podívat na absolutní číslo koeficientu (každý obor činnosti má svou vlastní stupnici).

Pro korelační analýzu několika parametrů (více než 2) je výhodnější použít „Data Analysis“ (doplněk „Analysis Package“). Musíte vybrat korelaci ze seznamu a označit pole. Všechno.

Výsledné koeficienty se zobrazí v korelační matici. Takhle:

Korelační a regresní analýza

V praxi se tyto dvě techniky často používají společně.

Příklad:

Nyní jsou data regresní analýzy viditelná.

Kvantitativní charakteristiku vztahu lze získat výpočtem korelačního koeficientu.

Korelační analýza v Excelu

Funkce samotná má obecná forma CORREL(pole1; pole2). Do pole „Array1“ zadejte souřadnice rozsahu buněk jedné z hodnot, jejichž závislost by měla být určena. Jak vidíte, korelační koeficient ve formě čísla se objeví v buňce, kterou jsme předtím vybrali. Otevře se okno s parametry korelační analýzy. Na rozdíl od předchozí metody do pole „Interval vstupu“ zadáváme interval nikoli každého sloupce zvlášť, ale všech sloupců, které se analýzy účastní. Jak vidíte, aplikace Excel nabízí dvě metody korelační analýzy najednou.

Korelační graf v excelu

6) První prvek výsledné tabulky se objeví v levé horní buňce vybrané oblasti. Proto je hypotéza H0 zamítnuta, to znamená, že regresní parametry a korelační koeficient nejsou náhodně odlišné od nuly, ale jsou statisticky významné. 7. Získané odhady regresní rovnice umožňují její použití pro prognózování.

Jak vypočítat korelační koeficient v Excelu

Pokud je koeficient 0, znamená to, že mezi hodnotami není žádný vztah. Chcete-li najít vztah mezi proměnnými a y, použijte vestavěnou funkci Microsoft Excel"CORREL". Například pro "Array1" vyberte hodnoty y a pro "Array2" vyberte hodnoty x. V důsledku toho obdržíte korelační koeficient vypočítaný programem. Dále musíte vypočítat rozdíl mezi každým x a xav a yav. Do vybraných buněk napište vzorce x-x, y-. Nezapomeňte připnout buňky s průměry. Získaným výsledkem bude požadovaný korelační koeficient.

Výše uvedený vzorec pro výpočet Pearsonova koeficientu ukazuje, jak náročný je tento proces, pokud se provádí ručně. Za druhé, doporučte, jaký typ korelační analýzy lze použít pro různé vzorky s velkým rozptylem dat? Jak mohu statisticky prokázat, že mezi skupinou nad 60 let a všemi ostatními je významný rozdíl?

DIY: Výpočet měnových korelací pomocí Excelu

Používáme například Microsoft Excel, ale postačí jakýkoli jiný program, ve kterém můžete použít korelační vzorec. 7. Poté vyberte buňky s údaji v EUR/USD. 9. Stiskněte Enter pro výpočet korelačního koeficientu pro EUR/USD a USD/JPY. Nemá cenu aktualizovat čísla každý den (no, pokud nejste posedlí měnovými korelacemi).

Již jste se setkali s potřebou vypočítat míru spojení mezi dvěma statistické veličiny a určit vzorec, podle kterého korelují? K tomu jsem použil funkci CORREL - zde jsou o ní nějaké informace. Vrací stupeň korelace mezi dvěma rozsahy dat. Teoreticky lze korelační funkci zpřesnit jejím převedením z lineární na exponenciální nebo logaritmickou. Analýza dat a korelačních grafů může velmi výrazně zlepšit její spolehlivost.

Předpokládejme, že buňka B2 obsahuje samotný korelační koeficient a buňka B3 obsahuje počet úplných pozorování. Máte rusky mluvící kancelář?Mimochodem, také jsem našel chybu - pro negativní korelace se významnost nepočítá. Pokud jsou obě proměnné metrické a mají normální distribuce, pak byla volba provedena správně. A je možné charakterizovat kritérium podobnosti křivek pouze pomocí jednoho CC?Nemáte podobnost „křivek“, ale podobnost dvou řad, které lze v principu popsat křivkou.