Korelacijska analiza v Excelu. Primer izvedbe korelacijske analize

Korelacijska analiza je pogosta raziskovalna metoda, ki se uporablja za ugotavljanje stopnje odvisnosti 1. vrednosti od 2. vrednosti. Preglednica ima posebno orodje, ki vam omogoča izvajanje te vrste raziskav.

Bistvo korelacijske analize

Treba je določiti razmerje med dvema različnima količinama. Z drugimi besedami, razkrije, v katero smer (manjšo/večjo) se vrednost spreminja glede na spremembe v drugi.

Namen korelacijske analize

Odvisnost se ugotovi, ko se začne ugotavljanje korelacijskega koeficienta. Ta metoda se razlikuje od regresijske analize, saj obstaja samo en indikator, izračunan s korelacijo. Interval se spreminja od +1 do -1. Če je pozitiven, potem povečanje prve vrednosti prispeva k povečanju druge vrednosti. Če je negativna, potem povečanje 2. vrednosti prispeva k zmanjšanju 1. vrednosti. Višji kot je koeficient, močneje ena vrednost vpliva na drugo.

Pomembno! Pri 0. koeficientu med količinama ni povezave.

Izračun korelacijskega koeficienta

Analizirajmo izračun na več vzorcih. Na primer, obstajajo tabelarični podatki, kjer so izdatki za promocijo oglaševanja in obseg prodaje opisani po mesecih v ločenih stolpcih. Na podlagi tabele bomo ugotovili stopnjo odvisnosti obsega prodaje od denarja, porabljenega za promocijo oglaševanja.

1. način: Določanje korelacije prek čarovnika za funkcije

CORREL – funkcija, ki vam omogoča izvedbo korelacijske analize. Splošni obrazec – CORREL(masiv1;masiv2). Podrobna navodila:

1. Treba je izbrati celico, v kateri je načrtovan prikaz rezultata izračuna. Za vnos formule kliknite »Vstavi funkcijo« na levi strani besedilnega polja.

2. Odpre se čarovnik za funkcije. Tukaj morate najti KOREL, kliknite nanj in nato na »V redu«.

3. Odpre se okno z argumenti. V vrstico "Array1" morate vnesti koordinate intervalov 1. vrednosti. V tem primeru je to stolpec Prodajna vrednost. Samo izbrati morate vse celice, ki so v tem stolpcu. Podobno morate v vrstico »Array2« dodati koordinate drugega stolpca. V našem primeru je to stolpec Stroški oglaševanja.

4. Po vnosu vseh razponov kliknite na gumb »V redu«.

Koeficient je bil prikazan v celici, ki je bila navedena na začetku naših dejanj. Dobljeni rezultat je 0,97. Ta indikator odraža visoko odvisnost prve vrednosti od druge.

2. način: Izračunajte korelacijo z orodjem Analysis ToolPak

Obstaja še ena metoda za določanje korelacije. Tukaj je uporabljena ena od funkcij, ki jih najdete v paketu analize. Preden ga uporabite, morate orodje aktivirati. Podrobna navodila:

1. Pojdite na razdelek »Datoteka«.

2. Odpre se novo okno, v katerem morate klikniti razdelek »Nastavitve«.
3. Kliknite »Dodatki«.
4. Na dnu najdemo element "Upravljanje". Tukaj morate v kontekstnem meniju izbrati »Excel Add-ins« in klikniti »V redu«.

5. Odprlo se je posebno okno z dodatki. Postavite kljukico poleg elementa »Analysis Package«. Kliknemo "V redu".
6. Aktivacija je bila uspešna. Zdaj pa pojdimo na Podatke. Pojavil se je blok »Analiza«, v katerem morate klikniti »Analiza podatkov«.
7. V novem oknu, ki se prikaže, izberite element »Korelacija« in kliknite »V redu«.

8. Na zaslonu se prikaže okno z nastavitvami analize. V vrstici "Vnosni interval" je potrebno vnesti obseg absolutno vseh stolpcev, ki sodelujejo pri analizi. V tem primeru sta to stolpca »Prodajna vrednost« in »Stroški oglaševanja«. Nastavitve prikaza izhoda so prvotno nastavljene na Nov delovni list, kar pomeni, da bodo rezultati prikazani na drugem listu. Po želji lahko spremenite izhodno lokacijo rezultata. Ko opravite vse nastavitve, kliknite »V redu«.

Končni rezultati so objavljeni. Rezultat je enak kot pri prvi metodi - 0,97.

Definicija in izračun multiplih korelacijskih koeficientov v MS Excelu

Za določitev stopnje odvisnosti več količin se uporablja več koeficientov. V prihodnje so rezultati povzeti v ločeni tabeli, imenovani korelacijska matrika.

Podroben vodnik:

1. V razdelku »Podatki« najdemo že znani blok »Analiza« in kliknemo »Analiza podatkov«.

2. V oknu, ki se prikaže, kliknite na element »Korelacija« in kliknite »V redu«.
3. V vrstici "Input interval" vozimo v intervalu za tri ali več stolpcev izvorne tabele. Obseg lahko vnesete ročno ali pa ga preprosto izberete z LMB in samodejno se prikaže v želeni vrstici. V »Združevanje« izberite ustrezen način združevanja. V »Izhodnem parametru« določa mesto, kjer bodo prikazani rezultati korelacije. Kliknemo "V redu".

4. pripravljena! Zgrajena je bila korelacijska matrika.

Parni korelacijski koeficient v Excelu

Ugotovimo, kako pravilno narisati korelacijski koeficient para v Excelovi preglednici.

Izračun parnega korelacijskega koeficienta v Excelu

Na primer, imate vrednosti x in y.

X je odvisna spremenljivka in y je neodvisna. Treba je najti smer in moč razmerja med temi indikatorji. Navodila po korakih:

1. Poiščimo povprečne vrednosti s funkcijo SRCE.

2. Izračunajmo vsakega х и xavg, у и pov z uporabo operatorja «-».

3. Izračunane razlike pomnožimo.

4. Izračunamo vsoto kazalnikov v tem stolpcu. Števec je najdeni rezultat.

5. Izračunajte imenovalce razlike х и x-povprečje, y и y-medij. Da bi to naredili, bomo izvedli kvadraturo.

6. Uporaba funkcije AUTOSUMMA, poiščite indikatorje v nastalih stolpcih. Izvajamo množenje. Uporaba funkcije ROOT kvadrirajte rezultat.

7. Količnik izračunamo z uporabo vrednosti imenovalca in števca.

8. CORREL je integrirana funkcija, ki vam omogoča, da preprečite zapletene izračune. Gremo v "Čarovnik za funkcije", izberemo CORREL in določimo nize indikatorjev х и у. Izdelamo graf, ki prikazuje dobljene vrednosti.

Matrika parnih korelacijskih koeficientov v Excelu

Analizirajmo, kako izračunati koeficiente parnih matrik. Na primer, obstaja matrika štirih spremenljivk.

Navodila po korakih:

1. Gremo na »Analiza podatkov«, ki se nahaja v bloku »Analiza« na zavihku »Podatki«. Na seznamu, ki se prikaže, izberite Korelacija.
2. Nastavili smo vse potrebne nastavitve. “Vnosni interval” – interval vseh štirih stolpcev. “Interval izpisa” – mesto, kjer želimo prikazati vsote. Kliknemo na gumb "V redu".
3. Na izbranem mestu je bila zgrajena korelacijska matrika. Vsako presečišče vrstice in stolpca je korelacijski koeficient. Ko se koordinate ujemajo, se prikaže številka 1.

Funkcija CORREL za določanje razmerja in korelacije v Excelu

CORREL – funkcija, ki se uporablja za izračun korelacijskega koeficienta med 2 poljema. Oglejmo si štiri primere vseh zmožnosti te funkcije.

Primeri uporabe funkcije CORREL v Excelu

Prvi primer. Tam je tabla s podatki o povprečnih plačah zaposlenih v podjetju v enajstih letih in menjalni tečaj $. Treba je ugotoviti razmerje med tema dvema količinama. Tabela izgleda takole:

Algoritem izračuna izgleda takole:

Prikazani rezultat je blizu 1. Rezultat:

Določitev korelacijskega koeficienta vpliva dejanj na rezultat

Drugi primer. Dva ponudnika sta se za pomoč pri petnajstdnevni akciji obrnila na dve različni agenciji. Vsak dan je bila izvedena socialna anketa, ki je določala stopnjo podpore za vsakega prosilca. Vsak sogovornik je lahko izbral enega od dveh kandidatov ali pa nasprotoval vsem. Ugotoviti je treba, koliko je posamezna promocija oglaševanja vplivala na stopnjo podpore prosilcem, katero podjetje je bolj učinkovito.

S spodnjimi formulami izračunamo korelacijski koeficient:

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

Rezultati:

Iz dobljenih rezultatov je razvidno, da je stopnja podpore 1. prijavitelju naraščala z vsakim dnem promocije oglaševanja, zato se korelacijski koeficient približuje 1. Ob začetku oglaševanja je imel drugi prijavitelj veliko zaupanje, za 5 dni je bil pozitiven trend. Nato se je stopnja zaupanja zmanjšala in do petnajstega dne padla pod začetne kazalnike. Nizke ocene kažejo, da je napredovanje negativno vplivalo na podporo. Ne pozabite, da lahko na kazalnike vplivajo tudi drugi spremljajoči dejavniki, ki niso upoštevani v obliki tabele.

Analiza priljubljenosti vsebine s korelacijo ogledov videoposnetkov in ponovnih objav

Tretji primer. Oseba, ki promovira svoje videoposnetke na video gostovanju YouTube, uporablja družbena omrežja za oglaševanje kanala. Opaža, da obstaja neka povezava med številom ponovnih objav v družbenih omrežjih in številom ogledov na kanalu. Ali je mogoče predvideti prihodnjo uspešnost z orodji za preglednice? Ugotoviti je treba smiselnost uporabe enačbe linearne regresije za napovedovanje števila ogledov videa glede na število ponovnih objav. Tabela z vrednostmi:

Zdaj je treba ugotoviti prisotnost razmerja med dvema indikatorjema po spodnji formuli:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Močno neposredno razmerje”;”Močno inverzno razmerje”);”Šibko ali brez razmerja”)' class='formula'>

Če je dobljeni koeficient višji od 0,7, je primernejša uporaba linearne regresijske funkcije. V tem primeru naredimo:

Zdaj gradimo graf:

To enačbo uporabimo za določitev števila ogledov pri 200, 500 in 1000 skupnih rabah: =9,2937*D4-206,12. Dobimo naslednje rezultate:

funkcija PREDVEDITE vam omogoča, da določite število ogledov v tem trenutku, če je bilo na primer dvesto petdeset ponovnih objav. Uporabljamo: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Vrednosti niso povezane”)' class='formula'>. Dobimo naslednje rezultate:

Značilnosti uporabe funkcije CORREL v Excelu

Ta funkcija ima naslednje lastnosti:

1. Prazne celice se ne upoštevajo.
2. Celice, ki vsebujejo logične in besedilne informacije, niso upoštevane.
3. Dvojno zanikanje "-" se uporablja za upoštevanje logičnih vrednosti v obliki številk.
4. Število celic v preučevanih nizih se mora ujemati, sicer bo prikazano sporočilo #N/A.

Ocena statistične pomembnosti korelacijskega koeficienta

Pri testiranju pomembnosti korelacijskega koeficienta je ničelna hipoteza, da ima indikator vrednost 0, alternativa pa ne. Za preverjanje se uporablja naslednja formula:

zaključek

Korelacijska analiza v preglednici je preprost in avtomatiziran postopek. Če ga želite izvesti, morate le vedeti, kje se nahajajo potrebna orodja in kako jih aktivirati prek nastavitev programa.