Korrelatsioonianalüüs Excelis. Näide korrelatsioonianalüüsi tegemisest

Korrelatsioonanalüüs on levinud uurimismeetod, mida kasutatakse 1. väärtuse sõltuvuse taseme määramiseks 2. väärtusest. Arvutustabelil on spetsiaalne tööriist, mis võimaldab seda tüüpi uuringuid rakendada.

Korrelatsioonianalüüsi olemus

On vaja määrata kahe erineva suuruse suhe. Teisisõnu, see näitab, millises suunas (väiksem / suurem) väärtus muutub sõltuvalt muutustest sekundis.

Korrelatsioonianalüüsi eesmärk

Sõltuvus tehakse kindlaks, kui algab korrelatsioonikordaja tuvastamine. See meetod erineb regressioonanalüüsist, kuna korrelatsiooni abil arvutatakse ainult üks näitaja. Intervall muutub +1-lt -1-le. Kui see on positiivne, aitab esimese väärtuse suurenemine kaasa 2. väärtuse suurenemisele. Kui negatiivne, siis 1. väärtuse suurenemine aitab kaasa 2. väärtuse vähenemisele. Mida suurem on koefitsient, seda tugevam on üks väärtus teist.

Tähtis! 0-nda koefitsiendi puhul pole suuruste vahel mingit seost.

Korrelatsioonikordaja arvutamine

Analüüsime arvutust mitmel proovil. Näiteks on olemas tabeliandmed, kus reklaamide edendamisele tehtud kulutused ja müügimaht on kuude kaupa eraldi veergudes kirjeldatud. Tabeli põhjal saame teada müügimahu sõltuvuse taseme reklaami edendamiseks kulutatud rahast.

1. meetod: korrelatsiooni määramine funktsiooniviisardi kaudu

CORREL – funktsioon, mis võimaldab rakendada korrelatsioonianalüüsi. Üldine vorm - CORREL(massiv1;massiv2). Üksikasjalikud juhised:

1. On vaja valida lahter, milles plaanitakse arvutuse tulemust kuvada. Valemi sisestamiseks klõpsake tekstiväljast vasakul asuvat nuppu "Lisa funktsioon".

2. Avaneb funktsiooniviisard. Siin peate leidma KORREL, klõpsake sellel ja seejärel "OK".

3. Avaneb argumentide aken. Reale “Array1” tuleb sisestada 1. väärtuste intervallide koordinaadid. Selles näites on see veerg Müügiväärtus. Peate lihtsalt valima kõik selles veerus olevad lahtrid. Samamoodi peate reale "Array2" lisama teise veeru koordinaadid. Meie näites on see veerg Reklaamikulud.

4. Pärast kõigi vahemike sisestamist klõpsake nuppu "OK".

Koefitsient kuvati lahtris, mis oli näidatud meie toimingute alguses. Saadud tulemus on 0,97. See indikaator peegeldab esimese väärtuse suurt sõltuvust teisest.

2. meetod: korrelatsiooni arvutamine Analysis ToolPaki abil

Korrelatsiooni määramiseks on veel üks meetod. Siin kasutatakse üht analüüsipaketis leiduvat funktsiooni. Enne selle kasutamist peate tööriista aktiveerima. Üksikasjalikud juhised:

1. Minge jaotisse "Fail".

2. Avaneb uus aken, kus peate klõpsama jaotist "Seaded".
3. Klõpsake nuppu "Lisandmoodulid".
4. Alt leiame elemendi "Juhtimine". Siin peate kontekstimenüüst valima "Exceli lisandmoodulid" ja klõpsama "OK".

5. Avanes spetsiaalne lisandmoodulite aken. Pange linnuke elemendi "Analüüsipakett" kõrvale. Me klõpsame "OK".
6. Aktiveerimine õnnestus. Nüüd läheme andmete juurde. Ilmus plokk "Analüüs", milles peate klõpsama nuppu "Andmete analüüs".
7. Ilmuvas uues aknas valige element "Korrelatsioon" ja klõpsake "OK".

8. Ekraanile ilmus analüüsi seadete aken. Reale “Sisestusintervall” tuleb sisestada absoluutselt kõigi analüüsis osalevate veergude vahemik. Selles näites on need veerud "Müügiväärtus" ja "Reklaamikulud". Väljundi kuva seaded on algselt seatud väärtusele Uus tööleht, mis tähendab, et tulemused kuvatakse teisel lehel. Soovi korral saate muuta tulemuse väljundi asukohta. Pärast kõigi seadistuste tegemist klõpsake nuppu "OK".

Lõplikud hinded on selgunud. Tulemus on sama, mis esimesel meetodil – 0,97.

Mitmekordse korrelatsioonikordaja defineerimine ja arvutamine MS Excelis

Mitme suuruse sõltuvuse taseme tuvastamiseks kasutatakse mitut koefitsienti. Tulevikus võetakse tulemused kokku eraldi tabelis, mida nimetatakse korrelatsioonimaatriksiks.

Üksikasjalik juhend:

1. Jaotises "Andmed" leiame juba tuntud ploki "Analüüs" ja klõpsake nuppu "Andmete analüüs".

2. Ilmuvas aknas klõpsake elemendil "Korrelatsioon" ja klõpsake "OK".
3. Real “Sisestusintervall” sisestame lähtetabeli kolme või enama veeru intervalli. Vahemiku saab sisestada käsitsi või lihtsalt valida LMB abil ja see kuvatakse automaatselt soovitud reale. Valige jaotises "Rühmitamine" sobiv rühmitusmeetod. Väljas "Väljundparameeter" määrab asukoha, kus korrelatsioonitulemused kuvatakse. Me klõpsame "OK".

4. Valmis! Koostati korrelatsioonimaatriks.

Paari korrelatsioonikordaja Excelis

Mõelgem välja, kuidas Exceli tabelis paari korrelatsioonikordaja õigesti joonistada.

Paari korrelatsioonikordaja arvutamine Excelis

Näiteks on teil x ja y väärtused.

X on sõltuv muutuja ja y on sõltumatu muutuja. Tuleb leida nende näitajate vahelise seose suund ja tugevus. Samm-sammult juhised:

1. Leiame funktsiooni abil keskmised väärtused SÜDA.

2. Arvutame igaüks х и xavg, у и avg kasutades operaatorit «-».

3. Korrutame arvutatud erinevused.

4. Arvutame selle veeru näitajate summa. Lugeja on leitud tulemus.

5. Arvutage erinevuse nimetajad х и x-keskmine, y и y-keskmine. Selleks teostame ruudustamist.

6. Funktsiooni kasutamine AUTOSUMMA, leidke saadud veergudest indikaatorid. Teeme korrutamist. Funktsiooni kasutamine JUUR tulemuse ruut.

7. Arvutame jagatise nimetaja ja lugeja väärtuste abil.

8. CORREL on integreeritud funktsioon, mis võimaldab vältida keerulisi arvutusi. Läheme funktsiooniviisardi juurde, valime CORREL ja täpsustame indikaatorite massiive х и у. Koostame graafiku, mis kuvab saadud väärtused.

Paaripõhise korrelatsioonikordaja maatriks Excelis

Analüüsime, kuidas arvutada paarismaatriksite koefitsiente. Näiteks on olemas neljast muutujast koosnev maatriks.

Samm-sammult juhised:

1. Me läheme jaotisse "Andmete analüüs", mis asub vahekaardi "Andmed" plokis "Analüüs". Valige kuvatavast loendist Korrelatsioon.
2. Seadsime kõik vajalikud seaded. “Sisestusintervall” – kõigi nelja veeru intervall. “Väljundi intervall” – koht, kus tahame summasid kuvada. Klõpsame nuppu "OK".
3. Valitud kohta koostati korrelatsioonimaatriks. Iga rea ​​ja veeru ristumiskoht on korrelatsioonikordaja. Kui koordinaadid ühtivad, kuvatakse number 1.

CORREL funktsioon Excelis seose ja korrelatsiooni määramiseks

CORREL – funktsioon, mida kasutatakse kahe massiivi vahelise korrelatsioonikordaja arvutamiseks. Vaatame nelja näidet selle funktsiooni kõigist võimalustest.

Näited funktsiooni CORREL kasutamisest Excelis

Esimene näide. Seal on plaat, millel on teave ettevõtte töötajate keskmise palga kohta üheteistkümne aasta jooksul ja dollari kursi kohta. Nende kahe suuruse vaheline seos on vaja kindlaks teha. Tabel näeb välja selline:

Arvutusalgoritm näeb välja selline:

Kuvatud skoor on 1 lähedal. Tulemus:

Tegevuste mõju korrelatsioonikordaja määramine tulemusele

Teine näide. Kaks pakkujat pöördusid kahe erineva agentuuri poole, et saada abi viieteistkümnepäevase müügiedendusega. Iga päev viidi läbi sotsiaalküsitlus, mis määras iga taotleja toetuse määra. Iga intervjueeritav võis valida kahest kandidaadist ühe või olla kõigile vastu. Tuleb kindlaks teha, kui palju iga reklaamikampaania mõjutas taotlejate toetuse määra, milline ettevõte on tõhusam.

Kasutades alltoodud valemeid, arvutame korrelatsioonikordaja:

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

Tulemused:

Saadud tulemustest selgub, et 1. taotleja toetusaste tõusis iga reklaami edendamise päevaga, seetõttu läheneb korrelatsioonikoefitsient 1-le. Reklaami käivitamisel oli teisel taotlejal palju usaldust ja 5 päeva oli positiivne trend. Seejärel usalduse määr langes ja viieteistkümnendaks päevaks langes see esialgsetest näitajatest allapoole. Madalad hinded viitavad sellele, et edutamine on avaldanud toetust negatiivselt. Ärge unustage, et näitajaid võivad mõjutada ka muud kaasnevad tegurid, mida tabelina ei käsitleta.

Sisu populaarsuse analüüs videovaatamiste ja korduspostituste korrelatsiooni järgi

Kolmas näide. Isik, kes reklaamib oma videoid YouTube'i videomajutuses, kasutab kanali reklaamimiseks sotsiaalvõrgustikke. Ta märkab, et suhtlusvõrgustikes korduspostituste arvu ja kanali vaatamiste arvu vahel on teatav seos. Kas arvutustabelitööriistade abil on võimalik tulevast jõudlust ennustada? Videovaatamiste arvu ennustamiseks olenevalt korduspostituste arvust on vaja tuvastada lineaarse regressioonivõrrandi rakendamise otstarbekus. Tabel väärtustega:

Nüüd on vaja kindlaks määrata seos kahe näitaja vahel vastavalt allolevale valemile:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"Tugev otsene seos";"Tugev pöördsuhe");"Nõrk või puudub seos"' class='valem'>

Kui saadud koefitsient on suurem kui 0,7, on sobivam kasutada lineaarse regressiooni funktsiooni. Selles näites teeme järgmist:

Nüüd koostame graafiku:

Kasutame seda võrrandit, et määrata vaatamiste arv 200, 500 ja 1000 jagamise korral: =9,2937*D4-206,12. Saame järgmised tulemused:

funktsioon PROGNOOS võimaldab määrata hetke vaatamiste arvu, kui korduspostitusi oleks näiteks kakssada viiskümmend. Rakendame: 0,7;PREDICTION(D7;B3:B8;A3:A8);"Väärtused ei ole seotud"" class='valem'>. Saame järgmised tulemused:

Funktsiooni CORREL kasutamise omadused Excelis

Sellel funktsioonil on järgmised funktsioonid:

1. Tühje lahtreid ei võeta arvesse.
2. Lahtreid, mis sisaldavad Boole'i ​​ja teksti tüüpi teavet, ei võeta arvesse.
3. Topelteitust “-” kasutatakse loogiliste väärtuste arvestamiseks numbrite kujul.
4. Uuritavate massiivide lahtrite arv peab ühtima, vastasel juhul kuvatakse teade #N/A.

Korrelatsioonikordaja statistilise olulisuse hindamine

Korrelatsioonikordaja olulisuse testimisel lähtutakse nullhüpoteesist, et indikaatori väärtus on 0, alternatiivil aga mitte. Kontrollimiseks kasutatakse järgmist valemit:

Järeldus

Arvutustabeli korrelatsioonianalüüs on lihtne ja automatiseeritud protsess. Selle teostamiseks pead vaid teadma, kus asuvad vajalikud tööriistad ja kuidas neid programmi sätete kaudu aktiveerida.