Korelačná analýza v Exceli. Príklad vykonania korelačnej analýzy

Korelačná analýza je bežná výskumná metóda používaná na určenie úrovne závislosti 1. hodnoty od 2. hodnoty. Tabuľka má špeciálny nástroj, ktorý vám umožňuje realizovať tento typ výskumu.

Podstata korelačnej analýzy

Je potrebné určiť vzťah medzi dvoma rôznymi veličinami. Inými slovami, prezrádza, ktorým smerom (menším/väčším) sa hodnota mení v závislosti od zmien v druhom.

Účel korelačnej analýzy

Závislosť vzniká na začiatku identifikácie korelačného koeficientu. Táto metóda sa líši od regresnej analýzy, pretože existuje iba jeden ukazovateľ vypočítaný pomocou korelácie. Interval sa mení z +1 na -1. Ak je kladná, potom zvýšenie prvej hodnoty prispieva k zvýšeniu 2. hodnoty. Ak je záporná, potom zvýšenie 1. hodnoty prispieva k poklesu 2. hodnoty. Čím vyšší je koeficient, tým silnejšia jedna hodnota ovplyvňuje druhú.

Dôležité! Pri 0. koeficiente nie je medzi veličinami vzťah.

Výpočet korelačného koeficientu

Analyzujme výpočet na niekoľkých vzorkách. Ide napríklad o tabuľkové údaje, kde sú výdavky na propagáciu reklamy a objem predaja popísané podľa mesiacov v samostatných stĺpcoch. Na základe tabuľky zistíme mieru závislosti objemu predaja od peňazí vynaložených na propagáciu reklamy.

Metóda 1: Určenie korelácie pomocou Sprievodcu funkciou

CORREL – funkcia, ktorá umožňuje realizovať korelačnú analýzu. Všeobecná forma - CORREL(massiv1;massiv2). Podrobné pokyny:

1. Je potrebné vybrať bunku, v ktorej sa plánuje zobraziť výsledok výpočtu. Kliknite na „Vložiť funkciu“ naľavo od textového poľa a zadajte vzorec.

2. Otvorí sa Sprievodca funkciami. Tu musíte nájsť CORREL, kliknite naň a potom na „OK“.

3. Otvorí sa okno s argumentmi. V riadku „Array1“ musíte zadať súradnice intervalov 1. hodnoty. V tomto príklade ide o stĺpec Hodnota predaja. Stačí vybrať všetky bunky, ktoré sú v tomto stĺpci. Podobne je potrebné pridať súradnice druhého stĺpca do riadku „Array2“. V našom príklade ide o stĺpec Náklady na reklamu.

4. Po zadaní všetkých rozsahov kliknite na tlačidlo „OK“.

Koeficient sa zobrazil v bunke, ktorá bola uvedená na začiatku našich akcií. Získaný výsledok je 0,97. Tento ukazovateľ odráža vysokú závislosť prvej hodnoty od druhej.

Metóda 2: Výpočet korelácie pomocou analytických nástrojov

Existuje ďalšia metóda na určenie korelácie. Tu sa používa jedna z funkcií, ktoré sa nachádzajú v analytickom balíku. Pred použitím je potrebné nástroj aktivovať. Podrobné pokyny:

1. Prejdite do sekcie „Súbor“.

2. Otvorí sa nové okno, v ktorom musíte kliknúť na časť „Nastavenia“.
3. Kliknite na „Doplnky“.
4. V spodnej časti nájdeme prvok „Management“. Tu musíte v kontextovej ponuke vybrať „Doplnky programu Excel“ a kliknúť na „OK“.

5. Otvorilo sa špeciálne okno doplnkov. Začiarknite políčko vedľa prvku „Analytický balík“. Klikneme na „OK“.
6. Aktivácia bola úspešná. Teraz poďme k údajom. Objavil sa blok „Analýza“, v ktorom musíte kliknúť na „Analýza údajov“.
7. V novom okne, ktoré sa zobrazí, vyberte prvok „Korelácia“ a kliknite na „OK“.

8. Na obrazovke sa objavilo okno s nastaveniami analýzy. V riadku „Interval vstupu“ je potrebné zadať rozsah úplne všetkých stĺpcov, ktoré sa zúčastňujú analýzy. V tomto príklade sú to stĺpce „Hodnota predaja“ a „Náklady na reklamu“. Nastavenia zobrazenia výstupu sú pôvodne nastavené na Nový pracovný hárok, čo znamená, že výsledky sa zobrazia na inom hárku. Voliteľne môžete zmeniť umiestnenie výstupu výsledku. Po vykonaní všetkých nastavení kliknite na „OK“.

Konečné skóre je vonku. Výsledok je rovnaký ako pri prvej metóde – 0,97.

Definícia a výpočet viacnásobného korelačného koeficientu v MS Excel

Na identifikáciu úrovne závislosti viacerých veličín sa používajú viaceré koeficienty. V budúcnosti budú výsledky zhrnuté v samostatnej tabuľke, ktorá sa nazýva korelačná matica.

Podrobný sprievodca:

1. V časti „Údaje“ nájdeme už známy blok „Analýza“ a klikneme na „Analýza údajov“.

2. V zobrazenom okne kliknite na prvok „Korelácia“ a kliknite na „OK“.
3. V riadku „Interval vstupu“ sa pohybujeme v intervale pre tri a viac stĺpcov zdrojovej tabuľky. Rozsah je možné zadať manuálne alebo ho jednoducho vybrať pomocou LMB a automaticky sa zobrazí v požadovanom riadku. V časti „Zoskupovanie“ vyberte vhodnú metódu zoskupovania. V časti „Parameter výstupu“ určuje miesto, kde sa zobrazia výsledky korelácie. Klikneme na „OK“.

4. Pripravený! Bola vytvorená korelačná matica.

Párový korelačný koeficient v Exceli

Poďme zistiť, ako správne nakresliť párový korelačný koeficient v tabuľke programu Excel.

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

Napríklad máte hodnoty x a y.

X je závislá premenná a y je nezávislá. Je potrebné nájsť smer a silu vzťahu medzi týmito ukazovateľmi. Pokyny krok za krokom:

1. Nájdite priemerné hodnoty pomocou funkcie SRDCE.

2. Vypočítajme každý х и xavg, у и avg pomocou operátora «-».

3. Vypočítané rozdiely vynásobíme.

4. Vypočítame súčet ukazovateľov v tomto stĺpci. Čitateľ je nájdený výsledok.

5. Vypočítajte menovateľov rozdielu х и x-priemer, y и y-stredné. Za týmto účelom vykonáme kvadratúru.

6. Pomocou funkcie AUTOSUMMA, nájdite ukazovatele vo výsledných stĺpcoch. Robíme násobenie. Pomocou funkcie ROOT umocnite výsledok.

7. Podiel vypočítame pomocou hodnôt menovateľa a čitateľa.

8. CORREL je integrovaná funkcia, ktorá vám umožňuje predísť zložitým výpočtom. Prejdeme do „Sprievodcu funkciami“, vyberieme CORREL a špecifikujeme polia indikátorov х и у. Zostavíme graf, ktorý zobrazí získané hodnoty.

Matica párových korelačných koeficientov v Exceli

Poďme analyzovať, ako vypočítať koeficienty párových matíc. Napríklad existuje matica štyroch premenných.

Pokyny krok za krokom:

1. Prejdeme na „Analýza údajov“, ktorá sa nachádza v bloku „Analýza“ na karte „Údaje“. Zo zobrazeného zoznamu vyberte Korelácia.
2. Nastavili sme všetky potrebné nastavenia. „Interval vstupu“ – interval všetkých štyroch stĺpcov. „Interval výstupu“ – miesto, kde chceme zobraziť súčty. Klikneme na tlačidlo „OK“.
3. Na zvolenom mieste bola vytvorená korelačná matica. Každý priesečník riadku a stĺpca je korelačný koeficient. Číslo 1 sa zobrazí, keď sa súradnice zhodujú.

Funkcia CORREL na určenie vzťahu a korelácie v Exceli

CORREL – funkcia používaná na výpočet korelačného koeficientu medzi 2 poliami. Pozrime sa na štyri príklady všetkých schopností tejto funkcie.

Príklady použitia funkcie CORREL v Exceli

Prvý príklad. Je tam tabuľka s údajmi o priemerných platoch zamestnancov firmy v priebehu jedenástich rokov a kurze $. Je potrebné identifikovať vzťah medzi týmito dvoma veličinami. Tabuľka vyzerá takto:

Algoritmus výpočtu vyzerá takto:

Zobrazené skóre sa blíži k 1. Výsledok:

Stanovenie korelačného koeficientu vplyvu akcií na výsledok

Druhý príklad. Dvaja uchádzači oslovili dve rôzne agentúry so žiadosťou o pomoc s pätnásťdňovou propagáciou. Každý deň prebiehala spoločenská anketa, ktorá určovala mieru podpory pre každého žiadateľa. Každý účastník pohovoru si mohol vybrať jedného z dvoch uchádzačov alebo oponovať všetkým. Je potrebné určiť, do akej miery každá reklamná propagácia ovplyvnila mieru podpory pre žiadateľov, ktorá firma je efektívnejšia.

Pomocou nižšie uvedených vzorcov vypočítame korelačný koeficient:

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

Výsledky:

Zo získaných výsledkov je zrejmé, že miera podpory pre 1. žiadateľa sa každým dňom propagácie reklamy zvyšovala, preto sa korelačný koeficient približuje k 1. Pri spustení inzercie mal druhý žiadateľ veľkú dôveru a pre 5 dní bol pozitívny trend. Potom miera dôvery klesla a do pätnásteho dňa klesla pod počiatočné ukazovatele. Nízke skóre naznačuje, že propagácia negatívne ovplyvnila podporu. Nezabudnite, že ukazovatele môžu ovplyvniť aj iné sprievodné faktory, ktoré nie sú zohľadnené v tabuľkovej forme.

Analýza popularity obsahu podľa korelácie zhliadnutí videa a opätovných príspevkov

Tretí príklad. Osoba, ktorá propaguje svoje vlastné videá na hosťovaní videa YouTube, používa sociálne siete na propagáciu kanála. Všimol si, že existuje určitý vzťah medzi počtom repostov na sociálnych sieťach a počtom zobrazení na kanáli. Je možné predpovedať budúcu výkonnosť pomocou tabuľkových nástrojov? Je potrebné identifikovať primeranosť použitia rovnice lineárnej regresie na predpovedanie počtu zhliadnutí videa v závislosti od počtu opakovaní. Tabuľka s hodnotami:

Teraz je potrebné určiť prítomnosť vzťahu medzi 2 ukazovateľmi podľa nižšie uvedeného vzorca:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Silný priamy vzťah”;”Silný inverzný vzťah”);”Slabý alebo žiadny vzťah”)' class='vzorec'>

Ak je výsledný koeficient vyšší ako 0,7, potom je vhodnejšie použiť lineárnu regresnú funkciu. V tomto príklade robíme:

Teraz vytvárame graf:

Túto rovnicu použijeme na určenie počtu zobrazení pri 200, 500 a 1000 XNUMX zdieľaniach: =9,2937*D4-206,12. Získame nasledujúce výsledky:

Funkcie PROGNÓZA umožňuje určiť počet zobrazení v súčasnosti, ak existuje napríklad dvestopäťdesiat repostov. Aplikujeme: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Hodnoty spolu nesúvisia”)' class='vzorec'>. Získame nasledujúce výsledky:

Vlastnosti použitia funkcie CORREL v Exceli

Táto funkcia má nasledujúce vlastnosti:

1. Prázdne bunky sa neberú do úvahy.
2. Bunky obsahujúce boolovské informácie a informácie o type textu sa neberú do úvahy.
3. Dvojitá negácia „-“ sa používa na zohľadnenie logických hodnôt vo forme čísel.
4. Počet buniek v študovaných poliach sa musí zhodovať, inak sa zobrazí hlásenie #N/A.

Posúdenie štatistickej významnosti korelačného koeficientu

Pri testovaní významnosti korelačného koeficientu platí nulová hypotéza, že ukazovateľ má hodnotu 0, zatiaľ čo alternatíva nie. Na overenie sa používa nasledujúci vzorec:

záver

Korelačná analýza v tabuľkovom procesore je jednoduchý a automatizovaný proces. Na jeho vykonanie potrebujete iba vedieť, kde sa nachádzajú potrebné nástroje a ako ich aktivovať prostredníctvom nastavení programu.