Korelační analýza v Excelu. Příklad provedení korelační analýzy

Korelační analýza je běžná výzkumná metoda používaná ke stanovení úrovně závislosti 1. hodnoty na 2. hodnotě. Tabulkový procesor má speciální nástroj, který vám umožňuje tento typ výzkumu realizovat.

Podstata korelační analýzy

Je nutné určit vztah mezi dvěma různými veličinami. Jinými slovy, odhaluje, kterým směrem (menší / větší) se hodnota mění v závislosti na změnách ve vteřině.

Účel korelační analýzy

Závislost vzniká, když začíná identifikace korelačního koeficientu. Tato metoda se liší od regresní analýzy, protože existuje pouze jeden ukazatel vypočítaný pomocí korelace. Interval se mění z +1 na -1. Pokud je kladná, pak zvýšení první hodnoty přispívá ke zvýšení 2. hodnoty. Pokud je záporná, pak zvýšení 1. hodnoty přispívá ke snížení 2. hodnoty. Čím vyšší je koeficient, tím silnější má jedna hodnota vliv na druhou.

Důležité! U 0. koeficientu není mezi veličinami žádný vztah.

Výpočet korelačního koeficientu

Pojďme analyzovat výpočet na několika vzorcích. Jde například o tabulková data, kde jsou výdaje na propagaci reklamy a objem prodeje popsány po měsících v samostatných sloupcích. Na základě tabulky zjistíme míru závislosti objemu prodeje na penězích vynaložených na propagaci reklamy.

Metoda 1: Určení korelace prostřednictvím Průvodce funkcí

CORREL – funkce, která umožňuje implementovat korelační analýzu. Obecná forma - CORREL(massiv1;massiv2). Podrobné pokyny:

1. Je nutné vybrat buňku, ve které se plánuje zobrazení výsledku výpočtu. Kliknutím na „Vložit funkci“ umístěnou nalevo od textového pole zadejte vzorec.

2. Otevře se Průvodce funkcí. Zde musíte najít CORREL, klikněte na něj a poté na „OK“.

3. Otevře se okno s argumenty. V řádku „Array1“ musíte zadat souřadnice intervalů 1. hodnoty. V tomto příkladu se jedná o sloupec Hodnota prodeje. Stačí vybrat všechny buňky, které jsou v tomto sloupci. Podobně je třeba přidat souřadnice druhého sloupce do řádku „Array2“. V našem příkladu se jedná o sloupec Náklady na reklamu.

4. Po zadání všech rozsahů klikněte na tlačítko „OK“.

Koeficient se zobrazil v buňce, která byla uvedena na začátku našich akcí. Získaný výsledek je 0,97. Tento ukazatel odráží vysokou závislost první hodnoty na druhé.

Metoda 2: Výpočet korelace pomocí analytických nástrojů

Existuje další metoda pro určení korelace. Zde je použita jedna z funkcí obsažených v analytickém balíčku. Před použitím je třeba nástroj aktivovat. Podrobné pokyny:

1. Přejděte do sekce „Soubor“.

2. Otevře se nové okno, ve kterém musíte kliknout na sekci „Nastavení“.
3. Klikněte na „Doplňky“.
4. Ve spodní části najdeme prvek „Management“. Zde musíte z kontextové nabídky vybrat „Doplňky aplikace Excel“ a kliknout na „OK“.

5. Otevřelo se speciální okno doplňků. Zaškrtněte prvek „Analytický balíček“. Klikneme na „OK“.
6. Aktivace proběhla úspěšně. Nyní pojďme k Data. Objevil se blok „Analýza“, ve kterém musíte kliknout na „Analýza dat“.
7. V nově zobrazeném okně vyberte prvek „Korelace“ a klikněte na „OK“.

8. Na obrazovce se objevilo okno nastavení analýzy. V řádku „Interval vstupu“ je nutné zadat rozsah absolutně všech sloupců účastnících se analýzy. V tomto příkladu se jedná o sloupce „Hodnota prodeje“ a „Náklady na reklamu“. Nastavení zobrazení výstupu je zpočátku nastaveno na Nový list, což znamená, že výsledky se zobrazí na jiném listu. Volitelně můžete změnit umístění výstupu výsledku. Po provedení všech nastavení klikněte na „OK“.

Konečné skóre je venku. Výsledek je stejný jako u první metody – 0,97.

Definice a výpočet vícenásobného korelačního koeficientu v MS Excel

Pro identifikaci úrovně závislosti více veličin se používá více koeficientů. V budoucnu budou výsledky shrnuty v samostatné tabulce, nazvané korelační matice.

Podrobný průvodce:

1. V sekci „Data“ najdeme již známý blok „Analýza“ a klikneme na „Analýza dat“.

2. V zobrazeném okně klikněte na prvek „Korelace“ a klikněte na „OK“.
3. V řádku „Interval vstupu“ najíždíme na interval pro tři a více sloupců zdrojové tabulky. Rozsah lze zadat ručně nebo jej jednoduše vybrat pomocí LMB a automaticky se zobrazí v požadovaném řádku. V „Seskupování“ vyberte vhodnou metodu seskupování. V „Parametr výstupu“ určuje místo, kde se zobrazí výsledky korelace. Klikneme na „OK“.

4. Připraveno! Byla vytvořena korelační matice.

Párový korelační koeficient v Excelu

Pojďme zjistit, jak správně nakreslit párový korelační koeficient v tabulce Excel.

Výpočet párového korelačního koeficientu v Excelu

Například máte hodnoty x a y.

X je závislá proměnná a y je nezávislá. Je třeba najít směr a sílu vztahu mezi těmito ukazateli. Návod krok za krokem:

1. Pojďme zjistit průměrné hodnoty pomocí funkce SRDCE.

2. Pojďme si každý spočítat х и xavg, у и avg pomocí operátoru «-».

3. Vypočtené rozdíly vynásobíme.

4. Vypočítáme součet ukazatelů v tomto sloupci. Čitatel je nalezený výsledek.

5. Vypočítejte jmenovatele rozdílu х и x-průměr, y и y-střední. K tomu provedeme kvadraturu.

6. Pomocí funkce AUTOSUMMA, najděte ukazatele ve výsledných sloupcích. Děláme násobení. Pomocí funkce ROOT čtverec výsledek.

7. Podíl vypočítáme pomocí hodnot jmenovatele a čitatele.

8. CORREL je integrovaná funkce, která umožňuje předcházet složitým výpočtům. Přejdeme do „Průvodce funkcí“, vybereme CORREL a určíme pole indikátorů х и у. Sestavíme graf, který zobrazí získané hodnoty.

Matice párových korelačních koeficientů v Excelu

Pojďme si rozebrat, jak vypočítat koeficienty párových matic. Například existuje matice čtyř proměnných.

Pokyny krok za krokem:

1. Přejdeme na „Analýza dat“, která se nachází v bloku „Analýza“ na kartě „Data“. Ze seznamu, který se zobrazí, vyberte možnost Korelace.
2. Nastavíme všechna potřebná nastavení. „Interval vstupu“ – interval všech čtyř sloupců. „Interval výstupu“ – místo, kde chceme zobrazit součty. Klikneme na tlačítko „OK“.
3. Na zvoleném místě byla vytvořena korelační matice. Každý průsečík řádku a sloupce je korelační koeficient. Číslo 1 se zobrazí, když se souřadnice shodují.

Funkce CORREL pro určení vztahu a korelace v Excelu

CORREL – funkce sloužící k výpočtu korelačního koeficientu mezi 2 poli. Podívejme se na čtyři příklady všech schopností této funkce.

Příklady použití funkce CORREL v Excelu

První příklad. Je tam tabulka s údaji o průměrných platech zaměstnanců firmy v průběhu jedenácti let a směnném kurzu $. Je nutné identifikovat vztah mezi těmito dvěma veličinami. Tabulka vypadá takto:

Algoritmus výpočtu vypadá takto:

Zobrazené skóre se blíží 1. Výsledek:

Stanovení korelačního koeficientu dopadu akcí na výsledek

Druhý příklad. Dva uchazeči oslovili dvě různé agentury o pomoc s patnáctidenní propagací. Každý den probíhala společenská anketa, která určovala míru podpory pro každého žadatele. Každý dotazovaný si mohl vybrat jednoho ze dvou uchazečů nebo se postavit všem. Je třeba určit, jak moc každá reklamní propagace ovlivnila míru podpory pro žadatele, která firma je efektivnější.

Pomocí níže uvedených vzorců vypočítáme korelační koeficient:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

výsledky:

Ze získaných výsledků je zřejmé, že míra podpory pro 1. žadatele se s každým dnem propagace reklamy zvyšovala, korelační koeficient se proto blíží 1. Při spuštění inzerce měl druhý žadatel velkou důvěru a pro 5 dní byl pozitivní trend. Poté míra důvěry klesla a do patnáctého dne klesla pod počáteční ukazatele. Nízké skóre naznačuje, že propagace negativně ovlivnila podporu. Nezapomeňte, že ukazatele mohou ovlivnit i další doprovodné faktory, které nejsou zohledněny v tabulkové formě.

Analýza popularity obsahu podle korelace zhlédnutí videa a repostů

Třetí příklad. Osoba, která propaguje svá vlastní videa na hostování videa YouTube, využívá k inzerci kanálu sociální sítě. Všiml si, že existuje určitý vztah mezi počtem repostů na sociálních sítích a počtem zobrazení na kanálu. Je možné předvídat budoucí výkon pomocí tabulkových procesorů? Je nutné identifikovat přiměřenost použití lineární regresní rovnice pro predikci počtu zhlédnutí videa v závislosti na počtu repostů. Tabulka s hodnotami:

Nyní je nutné určit přítomnost vztahu mezi 2 indikátory podle níže uvedeného vzorce:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Silný přímý vztah”;”Silný inverzní vztah”);”Slabý nebo žádný vztah”)' class='vzorec'>

Pokud je výsledný koeficient vyšší než 0,7, pak je vhodnější použít lineární regresní funkci. V tomto příkladu provedeme:

Nyní vytváříme graf:

Tuto rovnici použijeme k určení počtu zobrazení při 200, 500 a 1000 sdíleních: =9,2937*D4-206,12. Dostáváme následující výsledky:

funkce PŘEDPOVĚĎ umožňuje určit počet zobrazení v tuto chvíli, pokud by bylo například dvě stě padesát repostů. Aplikujeme: 0,7;PREDICTION(D7;B3:B8;A3:A8);„Hodnoty spolu nesouvisí“)' class='vzorec'>. Dostáváme následující výsledky:

Vlastnosti použití funkce CORREL v Excelu

Tato funkce má následující vlastnosti:

1. Prázdné buňky se neberou v úvahu.
2. Buňky obsahující informace typu Boolean a Text nejsou brány v úvahu.
3. Dvojitá negace „-“ se používá pro zohlednění logických hodnot ve formě čísel.
4. Počet buněk ve studovaných polích se musí shodovat, jinak se zobrazí zpráva #N/A.

Posouzení statistické významnosti korelačního koeficientu

Při testování významnosti korelačního koeficientu platí nulová hypotéza, že indikátor má hodnotu 0, zatímco alternativa nikoliv. Pro ověření se používá následující vzorec:

Proč investovat do čističky vzduchu?

Korelační analýza v tabulkovém procesoru je jednoduchý a automatizovaný proces. K jeho provedení potřebujete pouze vědět, kde se potřebné nástroje nacházejí a jak je aktivovat prostřednictvím nastavení programu.