Korrelaatioanalyysi Excelissä. Esimerkki korrelaatioanalyysin suorittamisesta

Korrelaatioanalyysi on yleinen tutkimusmenetelmä, jolla määritetään ensimmäisen arvon riippuvuustaso toisesta. Laskentataulukossa on erityinen työkalu, jonka avulla voit toteuttaa tämäntyyppisiä tutkimuksia.

Korrelaatioanalyysin ydin

On tarpeen määrittää kahden eri suuren välinen suhde. Toisin sanoen se paljastaa, mihin suuntaan (pienempi / suurempi) arvo muuttuu sekunnissa tapahtuvien muutosten mukaan.

Korrelaatioanalyysin tarkoitus

Riippuvuus todetaan, kun korrelaatiokertoimen tunnistaminen alkaa. Tämä menetelmä eroaa regressioanalyysistä, koska siinä on vain yksi korrelaatiolla laskettu indikaattori. Aikaväli muuttuu +1:stä -1:een. Jos se on positiivinen, ensimmäisen arvon lisäys myötävaikuttaa toisen arvon nousuun. Jos negatiivinen, 2. arvon kasvu vaikuttaa osaltaan 1. arvon laskuun. Mitä suurempi kerroin, sitä vahvempi yksi arvo vaikuttaa toiseen.

Tärkeää! 0. kertoimella määrien välillä ei ole suhdetta.

Korrelaatiokertoimen laskeminen

Analysoidaan laskelma useilla näytteillä. Esimerkiksi on olemassa taulukkotietoa, jossa mainonnan edistämiseen käytetyt menot ja myyntimäärät on kuvattu kuukausittain erillisissä sarakkeissa. Taulukon perusteella selvitämme myyntimäärän riippuvuuden tason mainonnan edistämiseen käytetystä rahasta.

Tapa 1: Korrelaation määrittäminen ohjatun funktiotoiminnon avulla

CORREL – funktio, jonka avulla voit toteuttaa korrelaatioanalyysin. Yleinen muoto - KORREL(massiv1;massiv2). Yksityiskohtaiset ohjeet:

1. On tarpeen valita solu, jossa laskennan tulos on tarkoitus näyttää. Napsauta tekstikentän vasemmalla puolella olevaa "Lisää funktio" syöttääksesi kaavan.

2. Ohjattu toimintotoiminto avautuu. Täältä sinun täytyy löytää KORRELAATIO, napsauta sitä ja sitten "OK".

3. Argumentit-ikkuna avautuu. Riville "Matriisi1" on syötettävä arvojen 1. välien koordinaatit. Tässä esimerkissä tämä on Myyntiarvo-sarake. Sinun tarvitsee vain valita kaikki solut, jotka ovat tässä sarakkeessa. Vastaavasti sinun on lisättävä toisen sarakkeen koordinaatit "Array2" -riville. Esimerkissämme tämä on Mainoskulut-sarake.

4. Kun olet syöttänyt kaikki alueet, napsauta "OK" -painiketta.

Kerroin näytettiin solussa, joka ilmoitettiin toimintojemme alussa. Saatu tulos on 0,97. Tämä indikaattori heijastaa ensimmäisen arvon suurta riippuvuutta toisesta.

Tapa 2: Laske korrelaatio Analysis ToolPakin avulla

On olemassa toinen menetelmä korrelaation määrittämiseksi. Tässä käytetään yhtä analyysipaketin funktioista. Ennen kuin käytät sitä, sinun on aktivoitava työkalu. Tarkemmat ohjeet:

1. Siirry "Tiedosto" -osioon.

2. Uusi ikkuna avautuu, jossa sinun on napsautettava "Asetukset" -osiota.
3. Napsauta "Lisäosat".
4. Löydämme alareunasta elementin "Management". Täällä sinun on valittava pikavalikosta "Excel-apuohjelmat" ja napsauta "OK".

5. Erityinen lisäosien ikkuna on avautunut. Aseta valintamerkki "Analysis Package" -elementin viereen. Napsautamme "OK".
6. Aktivointi onnistui. Siirrytään nyt Dataan. "Analysis" -lohko ilmestyi, jossa sinun on napsautettava "Data Analysis".
7. Valitse avautuvassa uudessa ikkunassa "Korrelaatio"-elementti ja napsauta "OK".

8. Analyysiasetusten ikkuna ilmestyi näytölle. Riville "Syöteväli" on syötettävä ehdottomasti kaikkien analyysiin osallistuvien sarakkeiden alue. Tässä esimerkissä nämä ovat sarakkeet "Myynnin arvo" ja "Mainoskulut". Tulosteen näyttöasetukset on alun perin asetettu New Worksheet -tilaan, mikä tarkoittaa, että tulokset näytetään eri arkilla. Vaihtoehtoisesti voit muuttaa tuloksen tulostuspaikkaa. Kun olet tehnyt kaikki asetukset, napsauta "OK".

Lopulliset pisteet ovat selvillä. Tulos on sama kuin ensimmäisessä menetelmässä – 0,97.

Monikorrelaatiokertoimen määrittely ja laskeminen MS Excelissä

Useiden suureiden riippuvuustason tunnistamiseksi käytetään useita kertoimia. Jatkossa tulokset kootaan erilliseen taulukkoon, jota kutsutaan korrelaatiomatriiksi.

Yksityiskohtainen opas:

1. "Data"-osiosta löydämme jo tunnetun "Analysis"-lohkon ja napsautamme "Data Analysis".

2. Napsauta näkyviin tulevassa ikkunassa "Korrelaatio" -elementtiä ja napsauta "OK".
3. Rivillä "Syöteväli" syötetään lähdetaulukon kolmen tai useamman sarakkeen aikaväli. Alue voidaan syöttää manuaalisesti tai yksinkertaisesti valita se LMB:llä, ja se ilmestyy automaattisesti halutulle riville. Valitse "Ryhmittäminen" -kohdasta sopiva ryhmittelytapa. Kohdassa "Output Parameter" määritetään sijainti, jossa korrelaatiotulokset näytetään. Napsautamme "OK".

4. Valmis! Korrelaatiomatriisi rakennettiin.

Parin korrelaatiokerroin Excelissä

Selvitetään, kuinka parin korrelaatiokerroin piirretään oikein Excel-laskentataulukkoon.

Parin korrelaatiokertoimen laskenta Excelissä

Sinulla on esimerkiksi x- ja y-arvot.

X on riippuvainen muuttuja ja y on riippumaton. On tarpeen löytää näiden indikaattoreiden välisen suhteen suunta ja vahvuus. Vaiheittaiset ohjeet:

1. Etsitään keskiarvot funktion avulla SYDÄN.

2. Lasketaan jokainen х и xavg, у и avg käyttämällä «-»-operaattoria.

3. Kerrotaan lasketut erot.

4. Laskemme tämän sarakkeen indikaattoreiden summan. Osoittaja on löydetty tulos.

5. Laske erotuksen nimittäjät х и x-keskiarvo, y и y-keskikokoinen. Tätä varten suoritamme neliöinnin.

6. Toiminnon käyttäminen AUTOSUMMA, etsi indikaattorit tuloksena olevista sarakkeista. Teemme kertolaskua. Toiminnon käyttäminen ROOT neliöi tuloksen.

7. Laskemme osamäärän käyttämällä nimittäjän ja osoittajan arvoja.

8. CORREL on integroitu toiminto, jonka avulla voit estää monimutkaiset laskelmat. Siirrymme "toimintovelhoon", valitse CORREL ja määritä indikaattorien ryhmät х и у. Rakennamme kaavion, joka näyttää saadut arvot.

Pareittainen korrelaatiokertoimien matriisi Excelissä

Analysoidaan kuinka lasketaan parillisten matriisien kertoimet. Esimerkiksi on neljän muuttujan matriisi.

Vaiheittaiset ohjeet:

1. Siirrymme "Tietojen analyysiin", joka sijaitsee "Data" -välilehden "Analyysi"-lohkossa. Valitse näkyviin tulevasta luettelosta Korrelaatio.
2. Asetamme kaikki tarvittavat asetukset. "Syöteväli" – kaikkien neljän sarakkeen väli. "Tulostusväli" – paikka, jossa haluamme näyttää kokonaissummat. Napsautamme "OK" -painiketta.
3. Valittuun paikkaan rakennettiin korrelaatiomatriisi. Jokainen rivin ja sarakkeen leikkauspiste on korrelaatiokerroin. Numero 1 näytetään, kun koordinaatit täsmäävät.

CORREL-funktio, joka määrittää suhteen ja korrelaation Excelissä

KORREL – funktio, jota käytetään korrelaatiokertoimen laskemiseen kahden taulukon välillä. Katsotaanpa neljä esimerkkiä tämän toiminnon kaikista kyvyistä.

Esimerkkejä CORREL-funktion käytöstä Excelissä

Ensimmäinen esimerkki. Siellä on levy, jossa on tiedot yrityksen työntekijöiden keskipalkoista yhdentoista vuoden ajalta ja dollarin vaihtokurssista. On tarpeen tunnistaa näiden kahden suuren välinen suhde. Taulukko näyttää tältä:

Laskenta-algoritmi näyttää tältä:

Näytetty pistemäärä on lähellä 1. Tulos:

Toimien tuloksen vaikutuksen korrelaatiokertoimen määrittäminen

Toinen esimerkki. Kaksi tarjoajaa pyysi kahdelta eri virastolta apua viidentoista päivän tarjouksessa. Joka päivä tehtiin sosiaalinen galluppi, jossa määritettiin kunkin hakijan tukiaste. Jokainen haastateltava voi valita yhden kahdesta hakijasta tai vastustaa kaikkia. On tarpeen määrittää, kuinka paljon kukin mainoskampanja vaikutti hakijoiden tukiasteeseen, mikä yritys on tehokkaampi.

Alla olevien kaavojen avulla laskemme korrelaatiokertoimen:

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

Tulokset:

Saaduista tuloksista käy selväksi, että 1. hakijan tukiaste nousi jokaisen mainonnan edistämispäivän myötä, joten korrelaatiokerroin lähestyy 1:tä. Mainonnan lanseerauksen yhteydessä toisella hakijalla oli suuri luottamus ja 5 päivää oli positiivinen suuntaus. Sitten luottamuksen aste laski ja viidentenätoista päivänä se putosi alkuperäisten indikaattoreiden alapuolelle. Alhaiset pisteet viittaavat siihen, että ylennys on vaikuttanut negatiivisesti tukeen. Älä unohda, että myös muut samanaikaiset tekijät, joita ei oteta huomioon taulukkomuodossa, voivat vaikuttaa indikaattoreihin.

Sisällön suosion analysointi videoiden katselukertojen ja uudelleenjulkaisujen korrelaatiolla

Kolmas esimerkki. Henkilö, joka mainostaa omia videoitaan YouTube-videopalvelussa, käyttää sosiaalisia verkostoja kanavan mainostamiseen. Hän huomaa, että sosiaalisten verkostojen uudelleenjulkaisujen ja kanavan katselukertojen välillä on jonkinlainen suhde. Onko mahdollista ennustaa tulevaa suorituskykyä taulukkolaskentatyökalujen avulla? On tarpeen tunnistaa lineaarisen regressioyhtälön käyttökelpoisuus ennustaa videon katselukertojen määrää uudelleenlähetysten lukumäärästä riippuen. Taulukko arvoineen:

Nyt on tarpeen määrittää kahden indikaattorin välinen suhde alla olevan kaavan mukaan:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"Vahva suora suhde";"Vahva käänteinen suhde");"Heikko tai ei suhde"' class='formula'>

Jos saatu kerroin on suurempi kuin 0,7, on tarkoituksenmukaisempaa käyttää lineaarista regressiofunktiota. Tässä esimerkissä teemme:

Nyt rakennamme kaaviota:

Käytämme tätä yhtälöä määrittämään näyttökertojen lukumäärän 200, 500 ja 1000 osuudella: =9,2937*D4-206,12. Saamme seuraavat tulokset:

Toiminto ENNUSTE voit määrittää katselukertojen määrän tällä hetkellä, jos niitä on esimerkiksi kaksisataaviisikymmentä uudelleenpostitusta. Haemme: 0,7;PREDICTION(D7;B3:B8;A3:A8);"Arvot eivät liity toisiinsa"' class='formula'>. Saamme seuraavat tulokset:

Excelin CORREL-funktion käytön ominaisuudet

Tällä toiminnolla on seuraavat ominaisuudet:

1. Tyhjiä soluja ei oteta huomioon.
2. Boolen ja tekstityypin tietoja sisältäviä soluja ei oteta huomioon.
3. Kaksoisnegatiota "-" käytetään loogisten arvojen huomioimiseen numeroiden muodossa.
4. Tutkittujen taulukoiden solujen lukumäärän on vastattava, muuten #N/A-viesti tulee näkyviin.

Korrelaatiokertoimen tilastollisen merkitsevyyden arviointi

Testattaessa korrelaatiokertoimen merkitsevyyttä nollahypoteesi on, että indikaattorin arvo on 0, kun taas vaihtoehdolla ei ole. Todentamiseen käytetään seuraavaa kaavaa:

Yhteenveto

Korrelaatioanalyysi laskentataulukossa on yksinkertainen ja automatisoitu prosessi. Suorittaaksesi sen, sinun tarvitsee vain tietää, missä tarvittavat työkalut sijaitsevat ja kuinka ne aktivoidaan ohjelman asetusten kautta.