Koreliacijos analizė programoje Excel. Koreliacinės analizės atlikimo pavyzdys

Koreliacinė analizė yra dažnas tyrimo metodas, naudojamas 1-osios reikšmės priklausomybės nuo 2-osios lygiui nustatyti. Skaičiuoklėje yra specialus įrankis, leidžiantis įgyvendinti tokio tipo tyrimus.

Koreliacinės analizės esmė

Būtina nustatyti ryšį tarp dviejų skirtingų dydžių. Kitaip tariant, ji atskleidžia, kuria kryptimi (mažesnė / didesnė) vertė keičiasi priklausomai nuo pokyčių sekundėje.

Koreliacinės analizės tikslas

Priklausomybė nustatoma, kai prasideda koreliacijos koeficiento identifikavimas. Šis metodas skiriasi nuo regresinės analizės, nes yra tik vienas rodiklis, apskaičiuotas naudojant koreliaciją. Intervalas keičiasi nuo +1 iki -1. Jei jis teigiamas, tada pirmosios vertės padidėjimas prisideda prie antrosios vertės padidėjimo. Jei neigiamas, tada 2-osios vertės padidėjimas prisideda prie 1-osios sumažėjimo. Kuo didesnis koeficientas, tuo stipresnė viena reikšmė paveikia antrąją.

Svarbu! Esant 0 koeficientui, nėra ryšio tarp dydžių.

Koreliacijos koeficiento apskaičiavimas

Išanalizuokime skaičiavimus pagal kelis pavyzdžius. Pavyzdžiui, yra lentelės duomenys, kur išlaidos reklamos skatinimui ir pardavimų apimtys aprašomos mėnesiais atskirais stulpeliais. Remdamiesi lentele išsiaiškinsime pardavimų apimties priklausomybės nuo reklamos skatinimui išleistų pinigų lygį.

1 būdas: koreliacijos nustatymas naudojant funkcijų vedlį

CORREL – funkcija, leidžianti įgyvendinti koreliacijos analizę. Bendra forma - CORREL(massiv1;massiv2). Išsamios instrukcijos:

1. Būtina pasirinkti langelį, kuriame planuojama rodyti skaičiavimo rezultatą. Norėdami įvesti formulę, spustelėkite „Įterpti funkciją“, esančią teksto lauko kairėje.

2. Atsidaro funkcijų vedlys. Čia reikia rasti CORREL, spustelėkite jį, tada – „Gerai“.

3. Atsidaro argumentų langas. Eilutėje „Array1“ turite įvesti 1-osios reikšmių intervalų koordinates. Šiame pavyzdyje tai yra stulpelis Pardavimo vertė. Jums tereikia pažymėti visas langelius, esančius šiame stulpelyje. Panašiai turite pridėti antrojo stulpelio koordinates į eilutę „Array2“. Mūsų pavyzdyje tai yra stulpelis Reklamos išlaidos.

4. Įvedę visus diapazonus, spustelėkite mygtuką „Gerai“.

Koeficientas buvo rodomas langelyje, kuris buvo nurodytas mūsų veiksmų pradžioje. Gautas rezultatas yra 0,97. Šis rodiklis atspindi didelę pirmosios reikšmės priklausomybę nuo antrosios.

2 būdas: koreliacijos apskaičiavimas naudojant analizės įrankių paketą

Yra ir kitas koreliacijos nustatymo metodas. Čia naudojama viena iš analizės pakete esančių funkcijų. Prieš naudodami įrankį turite suaktyvinti. Išsamios instrukcijos:

1. Eikite į skyrių „Failas“.

2. Atsidarys naujas langas, kuriame turėsite spustelėti skyrių „Nustatymai“.
3. Spustelėkite „Priedai“.
4. Apačioje randame elementą „Valdymas“. Čia reikia kontekstiniame meniu pasirinkti „Excel“ priedai ir spustelėkite „Gerai“.

5. Atsidarė specialus priedų langas. Pažymėkite varnelę šalia elemento „Analytics Package“. Spaudžiame "Gerai".
6. Aktyvinimas buvo sėkmingas. Dabar pereikime prie duomenų. Atsirado blokas „Analizė“, kuriame reikia spustelėti „Duomenų analizė“.
7. Atsidariusiame naujame lange pasirinkite elementą „Koreliacija“ ir spustelėkite „Gerai“.

8. Ekrane pasirodė analizės nustatymų langas. Eilutėje „Įvesties intervalas“ reikia įvesti absoliučiai visų analizėje dalyvaujančių stulpelių diapazoną. Šiame pavyzdyje tai yra stulpeliai „Pardavimo vertė“ ir „Reklamos išlaidos“. Išvesties rodymo parametrai iš pradžių nustatomi į Naujas darbalapis, o tai reiškia, kad rezultatai bus rodomi kitame lape. Pasirinktinai galite pakeisti rezultato išvesties vietą. Atlikę visus nustatymus, spustelėkite „Gerai“.

Galutiniai balai paskelbti. Rezultatas toks pat kaip ir pirmuoju metodu – 0,97.

Daugialypės koreliacijos koeficiento apibrėžimas ir skaičiavimas programoje MS Excel

Norint nustatyti kelių dydžių priklausomybės lygį, naudojami keli koeficientai. Ateityje rezultatai apibendrinami atskiroje lentelėje, vadinamoje koreliacijos matrica.

Išsamus vadovas:

1. Skiltyje „Duomenys“ randame jau žinomą „Analizė“ bloką ir spustelėkite „Duomenų analizė“.

2. Atsidariusiame lange spustelėkite elementą „Koreliacija“ ir spustelėkite „Gerai“.
3. Eilutėje „Įvesties intervalas“ nurodome trijų ar daugiau šaltinio lentelės stulpelių intervalą. Diapazoną galima įvesti rankiniu būdu arba tiesiog pasirinkti jį naudojant LMB, ir jis automatiškai atsiras norimoje eilutėje. Skiltyje „Grupavimas“ pasirinkite tinkamą grupavimo metodą. Skiltyje „Išvesties parametras“ nurodoma vieta, kurioje bus rodomi koreliacijos rezultatai. Spaudžiame "Gerai".

4. Pasiruošę! Sukurta koreliacinė matrica.

Poros koreliacijos koeficientas programoje Excel

Išsiaiškinkime, kaip teisingai nubrėžti poros koreliacijos koeficientą „Excel“ skaičiuoklėje.

Poros koreliacijos koeficiento skaičiavimas programoje Excel

Pavyzdžiui, turite x ir y reikšmes.

X yra priklausomas kintamasis, o y yra nepriklausomas. Būtina rasti šių rodiklių ryšio kryptį ir stiprumą. Žingsnis po žingsnio instrukcija:

1. Raskime vidutines reikšmes naudodami funkciją ŠIRDIS.

2. Paskaičiuokime kiekvieną х и xavg, у и vid naudojant „-“ operatorių.

3. Apskaičiuotus skirtumus padauginame.

4. Skaičiuojame šio stulpelio rodiklių sumą. Skaitiklis yra rastas rezultatas.

5. Apskaičiuokite skirtumo vardiklius х и x-vidurkis, y и y-vidutinis. Norėdami tai padaryti, atliksime kvadratą.

6. Naudojant funkciją AUTOSUMMA, raskite rodiklius gautuose stulpeliuose. Mes atliekame dauginimą. Naudojant funkciją ŠAKNIAVAISIAI rezultatą kvadratu.

7. Dalinį apskaičiuojame naudodami vardiklio ir skaitiklio reikšmes.

8. CORREL yra integruota funkcija, leidžianti išvengti sudėtingų skaičiavimų. Einame į „Funkcijų vedlį“, pasirenkame CORREL ir nurodome indikatorių masyvus х и у. Sudarome grafiką, kuriame pateikiamos gautos reikšmės.

Porinių koreliacijos koeficientų matrica programoje Excel

Išanalizuokime, kaip apskaičiuoti porinių matricų koeficientus. Pavyzdžiui, yra keturių kintamųjų matrica.

Žingsnis po žingsnio instrukcija:

1. Einame į „Duomenų analizę“, esančią skirtuko „Duomenys“ bloke „Analizė“. Pasirodžiusiame sąraše pasirinkite Koreliacija.
2. Mes nustatome visus reikiamus nustatymus. „Įvesties intervalas“ – visų keturių stulpelių intervalas. „Išvesties intervalas“ – vieta, kurioje norime rodyti sumas. Spaudžiame mygtuką „Gerai“.
3. Pasirinktoje vietoje buvo sudaryta koreliacinė matrica. Kiekviena eilutės ir stulpelio sankirta yra koreliacijos koeficientas. Kai koordinatės sutampa, rodomas skaičius 1.

CORREL funkcija, skirta nustatyti ryšį ir koreliaciją programoje „Excel“.

CORREL – funkcija, naudojama koreliacijos koeficientui tarp 2 masyvų apskaičiuoti. Pažvelkime į keturis visų šios funkcijos galimybių pavyzdžius.

„Excel“ funkcijos CORREL naudojimo pavyzdžiai

Pirmas pavyzdys. Ten yra lentelė su informacija apie vidutinius įmonės darbuotojų atlyginimus per vienuolika metų ir USD kursą. Būtina nustatyti ryšį tarp šių dviejų dydžių. Lentelė atrodo taip:

Skaičiavimo algoritmas atrodo taip:

Rodomas rezultatas yra artimas 1. Rezultatas:

Veiksmų įtakos rezultatui koreliacijos koeficiento nustatymas

Antras pavyzdys. Du konkurso dalyviai kreipėsi į dvi skirtingas agentūras, prašydami padėti penkiolikos dienų akcijai. Kiekvieną dieną buvo vykdoma socialinė apklausa, kurios metu buvo nustatytas paramos laipsnis kiekvienam pareiškėjui. Bet kuris pašnekovas galėjo pasirinkti vieną iš dviejų kandidatų arba prieštarauti visiems. Būtina nustatyti, kiek kiekviena reklamos akcija turėjo įtakos paramos pareiškėjams laipsniui, kuri įmonė yra efektyvesnė.

Naudodami toliau pateiktas formules apskaičiuojame koreliacijos koeficientą:

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

Rezultatai:

Iš gautų rezultatų aiškėja, kad paramos 1-ajam pareiškėjui laipsnis didėjo su kiekviena reklamos skatinimo diena, todėl koreliacijos koeficientas artėja prie 1. Pradėjus reklamuotis, kitas pareiškėjas turėjo didelį pasitikėjimą, o 5 dienas buvo teigiama tendencija. Tada pasitikėjimo laipsnis sumažėjo ir penkioliktą dieną nukrito žemiau pradinių rodiklių. Žemi balai rodo, kad paaukštinimas neigiamai paveikė paramą. Nepamirškite, kad kiti susiję veiksniai, kurie nėra įtraukti į lentelę, taip pat gali turėti įtakos rodikliams.

Turinio populiarumo analizė pagal vaizdo įrašų peržiūrų ir pakartotinių įrašų koreliaciją

Trečias pavyzdys. Asmuo, norėdamas reklamuoti savo vaizdo įrašus „YouTube“ vaizdo įrašų priegloboje, naudoja socialinius tinklus kanalui reklamuoti. Jis pastebi, kad yra tam tikras ryšys tarp pakartotinių įrašų skaičiaus socialiniuose tinkluose ir peržiūrų skaičiaus kanale. Ar įmanoma numatyti būsimą našumą naudojant skaičiuoklės įrankius? Būtina nustatyti tiesinės regresijos lygties taikymo pagrįstumą norint numatyti vaizdo įrašo peržiūrų skaičių, atsižvelgiant į pakartotinių įrašų skaičių. Lentelė su reikšmėmis:

Dabar reikia nustatyti ryšį tarp 2 rodiklių pagal toliau pateiktą formulę:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"Tvirtas tiesioginis ryšys";"Tvirtas atvirkštinis ryšys");"Silpnas ryšys arba jo nėra"' class='formula'>

Jei gautas koeficientas didesnis nei 0,7, tuomet tikslingiau naudoti tiesinės regresijos funkciją. Šiame pavyzdyje mes darome:

Dabar sudarome grafiką:

Taikome šią lygtį norėdami nustatyti peržiūrų skaičių esant 200, 500 ir 1000 bendrinimų: =9,2937*D4-206,12. Gauname tokius rezultatus:

Funkcija PROGNOZĖ leidžia nustatyti peržiūrų skaičių šiuo metu, jei buvo, pavyzdžiui, du šimtai penkiasdešimt pakartotinių pranešimų. Mes taikome: 0,7;PREDICTION(D7;B3:B8;A3:A8);"Vertės nesusijusios"" class='formula'>. Gauname tokius rezultatus:

„Excel“ funkcijos CORREL naudojimo ypatybės

Ši funkcija turi šias funkcijas:

1. Į tuščius langelius neatsižvelgiama.
2. Į langelius, kuriuose yra Būlio ir Teksto tipo informacija, neatsižvelgiama.
3. Dvigubas neigimas „-“ naudojamas norint atsižvelgti į logines reikšmes skaičių pavidalu.
4. Tiriamuose masyvuose esančių langelių skaičius turi sutapti, kitaip bus rodomas pranešimas #N/A.

Koreliacijos koeficiento statistinio reikšmingumo įvertinimas

Tikrinant koreliacijos koeficiento reikšmingumą, nulinė hipotezė yra ta, kad rodiklio reikšmė yra 0, o alternatyva neturi. Patikrinti naudojama ši formulė:

Išvada

Koreliacijos analizė skaičiuoklėje yra paprastas ir automatizuotas procesas. Norėdami tai atlikti, tereikia žinoti, kur yra reikalingi įrankiai ir kaip juos suaktyvinti per programos nustatymus.