Korrelasie-analise in Excel. 'n Voorbeeld van die uitvoer van 'n korrelasie-analise

Korrelasie-analise is 'n algemene navorsingsmetode wat gebruik word om die vlak van afhanklikheid van die 1ste waarde op die 2de te bepaal. Die sigblad het 'n spesiale hulpmiddel wat jou toelaat om hierdie tipe navorsing te implementeer.

Die essensie van korrelasie-analise

Dit is nodig om die verband tussen twee verskillende hoeveelhede te bepaal. Met ander woorde, dit openbaar in watter rigting (kleiner / groter) die waarde verander na gelang van veranderinge in die sekonde.

Doel van korrelasie-analise

Afhanklikheid word vasgestel wanneer die identifikasie van die korrelasiekoëffisiënt begin. Hierdie metode verskil van regressie-analise, aangesien daar slegs een aanwyser is wat met behulp van korrelasie bereken word. Die interval verander van +1 na -1. As dit positief is, dra 'n toename in die eerste waarde by tot 'n toename in die 2de. Indien negatief, dra 'n toename in die 1ste waarde by tot 'n afname in die 2de. Hoe hoër die koëffisiënt, hoe sterker een waarde beïnvloed die tweede.

Belangrik! By die 0de koëffisiënt is daar geen verband tussen die hoeveelhede nie.

Berekening van die korrelasiekoëffisiënt

Kom ons ontleed die berekening op verskeie monsters. Daar is byvoorbeeld tabeldata, waar besteding aan advertensiepromosie en verkoopsvolume volgens maande in aparte kolomme beskryf word. Gebaseer op die tabel, sal ons uitvind die vlak van afhanklikheid van verkoopsvolume op die geld wat aan advertensiebevordering bestee word.

Metode 1: Bepaling van korrelasie deur die Function Wizard

CORREL – 'n funksie wat jou toelaat om 'n korrelasie-analise te implementeer. Algemene vorm - CORREL(massief1;massief2). Gedetailleerde instruksies:

1. Dit is nodig om die sel te kies waarin dit beplan word om die resultaat van die berekening te vertoon. Klik op "Voeg funksie in" aan die linkerkant van die teksveld om die formule in te voer.

2. Die Function Wizard maak oop. Hier moet jy vind CORREL, klik daarop en dan op “OK”.

3. Die argumente-venster maak oop. In die reël "Array1" moet jy die koördinate van die intervalle van die 1ste van die waardes invoer. In hierdie voorbeeld is dit die Verkoopwaarde-kolom. Jy hoef net al die selle wat in hierdie kolom is te kies. Net so moet jy die koördinate van die tweede kolom by die "Array2" -lyn voeg. In ons voorbeeld is dit die Advertensiekoste-kolom.

4. Nadat u al die reekse ingevoer het, klik op die "OK"-knoppie.

Die koëffisiënt is vertoon in die sel wat aan die begin van ons aksies aangedui is. Die resultaat wat verkry is, is 0,97. Hierdie aanwyser weerspieël die hoë afhanklikheid van die eerste waarde op die tweede.

Metode 2: Bereken korrelasie met behulp van die Analysis ToolPak

Daar is 'n ander metode om korrelasie te bepaal. Hier word een van die funksies wat in die ontledingspakket gevind word, gebruik. Voordat u dit gebruik, moet u die instrument aktiveer. Gedetailleerde instruksies:

1. Gaan na die afdeling "Lêer".

2. 'n Nuwe venster sal oopmaak waarin u op die afdeling "Instellings" moet klik.
3. Klik op "Byvoegings".
4. Ons vind die element "Bestuur" onderaan. Hier moet jy "Excel-byvoegings" in die konteks kieslys kies en "OK" klik.

5. 'n Spesiale byvoegingsvenster het oopgemaak. Plaas 'n regmerkie langs die "Analise Package" element. Ons klik "OK".
6. Aktivering was suksesvol. Kom ons gaan nou na Data. Die blokkie "Analise" het verskyn, waarin u op "Data-analise" moet klik.
7. In die nuwe venster wat verskyn, kies die "Korrelasie" element en klik op "OK".

8. Die venster vir analise-instellings het op die skerm verskyn. In die reël "Invoerinterval" is dit nodig om die reeks van absoluut alle kolomme wat aan die ontleding deelneem, in te voer. In hierdie voorbeeld is dit die kolomme “Verkoopwaarde” en “Advertensiekoste”. Die uitsetvertooninstellings is aanvanklik op Nuwe Werkblad gestel, wat beteken dat die resultate op 'n ander blad vertoon sal word. Opsioneel kan jy die uitsetligging van die resultaat verander. Nadat u al die instellings gemaak het, klik op "OK".

Die finale tellings is uit. Die resultaat is dieselfde as in die eerste metode – 0,97.

Definisie en berekening van meervoudige korrelasiekoëffisiënt in MS Excel

Om die vlak van afhanklikheid van verskeie hoeveelhede te identifiseer, word veelvuldige koëffisiënte gebruik. In die toekoms word die resultate in 'n aparte tabel opgesom, wat die korrelasiematriks genoem word.

Gedetailleerde gids:

1. In die "Data"-afdeling vind ons die reeds bekende "Analise"-blok en klik op "Data-analise".

2. In die venster wat verskyn, klik op die "Korrelasie" element en klik op "OK".
3. In die reël "Invoerinterval" ry ons in die interval vir drie of meer kolomme van die brontabel. Die reeks kan met die hand ingevoer word of eenvoudig met die LMB kies, en dit sal outomaties in die verlangde reël verskyn. In "Groepering" kies die toepaslike groeperingsmetode. In "Uitvoerparameter" spesifiseer die ligging waar die korrelasieresultate vertoon sal word. Ons klik "OK".

4. Klaar! Die korrelasiematriks is gebou.

Paar korrelasiekoëffisiënt in Excel

Kom ons vind uit hoe om die paarkorrelasiekoëffisiënt korrek in 'n Excel-sigblad te teken.

Berekening van paarkorrelasiekoëffisiënt in Excel

Byvoorbeeld, jy het x- en y-waardes.

X is die afhanklike veranderlike en y is die onafhanklike. Dit is nodig om die rigting en sterkte van die verhouding tussen hierdie aanwysers te vind. Stap-vir-stap instruksie:

1. Kom ons vind die gemiddelde waardes met behulp van die funksie HART.

2. Kom ons bereken elkeen х и xgemiddeld, у и gem deur die «-»-operateur te gebruik.

3. Ons vermenigvuldig die berekende verskille.

4. Ons bereken die som van die aanwysers in hierdie kolom. Die teller is die resultaat wat gevind is.

5. Bereken die noemers van die verskil х и x-gemiddeld, y и y-medium. Om dit te doen, sal ons die kwadratering uitvoer.

6. Gebruik die funksie AUTOSUMMA, vind die aanwysers in die gevolglike kolomme. Ons doen vermenigvuldiging. Gebruik die funksie WORTEL vierkant die resultaat.

7. Ons bereken die kwosiënt deur die waardes van die noemer en teller te gebruik.

8. CORREL is 'n geïntegreerde funksie wat jou toelaat om komplekse berekeninge te voorkom. Ons gaan na die "Function Wizard", kies CORREL en spesifiseer die skikkings van aanwysers х и у. Ons bou 'n grafiek wat die verkrygde waardes vertoon.

Matriks van paarsgewyse korrelasiekoëffisiënte in Excel

Kom ons ontleed hoe om die koëffisiënte van gepaarde matrikse te bereken. Daar is byvoorbeeld 'n matriks van vier veranderlikes.

Stap-vir-stap instruksie:

1. Ons gaan na die "Data-analise", geleë in die "Analise"-blok van die "Data"-oortjie. Kies Korrelasie uit die lys wat verskyn.
2. Ons stel al die nodige instellings. “Invoerinterval” – die interval van al vier kolomme. “Uitvoerinterval” – die plek waar ons die totale wil vertoon. Ons klik op die "OK" knoppie.
3. 'n Korrelasiematriks is op die gekose plek gebou. Elke snypunt van 'n ry en 'n kolom is 'n korrelasiekoëffisiënt. Die nommer 1 word vertoon wanneer die koördinate ooreenstem.

CORREL-funksie om verwantskap en korrelasie in Excel te bepaal

CORREL – 'n funksie wat gebruik word om die korrelasiekoëffisiënt tussen 2 skikkings te bereken. Kom ons kyk na vier voorbeelde van al die vermoëns van hierdie funksie.

Voorbeelde van die gebruik van die CORREL-funksie in Excel

Eerste voorbeeld. Daar is 'n bord met inligting oor die gemiddelde salarisse van die maatskappy se werknemers oor die loop van elf jaar en die wisselkoers van $. Dit is nodig om die verband tussen hierdie twee hoeveelhede te identifiseer. Die tabel lyk so:

Die berekeningsalgoritme lyk soos volg:

Die vertoonde telling is naby aan 1. Resultaat:

Bepaling van die korrelasiekoëffisiënt van die impak van aksies op die resultaat

Tweede voorbeeld. Twee bieërs het twee verskillende agentskappe genader vir hulp met 'n vyftien dae lange promosie. Elke dag is 'n sosiale peiling gedoen wat die mate van ondersteuning vir elke aansoeker bepaal het. Enige onderhoudvoerder kan een van die twee aansoekers kies of almal teenstaan. Dit is nodig om te bepaal hoeveel elke advertensiepromosie die mate van ondersteuning vir aansoekers beïnvloed het, watter maatskappy is meer doeltreffend.

Deur die formules hieronder te gebruik, bereken ons die korrelasiekoëffisiënt:

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

Results:

Uit die resultate wat verkry is, word dit duidelik dat die mate van ondersteuning vir die 1ste applikant toegeneem het met elke dag van advertensiebevordering, daarom benader die korrelasiekoëffisiënt 1. Toe advertensies geloods is, het die ander applikant 'n groot aantal vertroue gehad, en vir 5 dae was daar 'n positiewe tendens. Toe het die mate van vertroue afgeneem en teen die vyftiende dag het dit onder die aanvanklike aanwysers gedaal. Lae tellings dui daarop dat bevordering ondersteuning negatief beïnvloed het. Moenie vergeet dat ander gepaardgaande faktore wat nie in tabelvorm oorweeg word nie, ook die aanwysers kan beïnvloed.

Ontleding van inhoudgewildheid deur korrelasie van videokyke en herplasings

Derde voorbeeld. 'n Persoon om hul eie video's op YouTube-video-hosting te bevorder, gebruik sosiale netwerke om die kanaal te adverteer. Hy merk op dat daar 'n verband is tussen die aantal herplasings in sosiale netwerke en die aantal kyke op die kanaal. Is dit moontlik om toekomstige prestasie met behulp van sigbladnutsgoed te voorspel? Dit is nodig om die redelikheid van die toepassing van die lineêre regressievergelyking te identifiseer om die aantal videokyke te voorspel afhangende van die aantal herplasings. Tabel met waardes:

Nou is dit nodig om die teenwoordigheid van 'n verband tussen 2 aanwysers te bepaal volgens die formule hieronder:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Sterk direkte verwantskap”;”Sterk omgekeerde verwantskap”);”Swak of geen verwantskap”)' class='formula'>

As die resulterende koëffisiënt hoër as 0,7 is, is dit meer gepas om die lineêre regressiefunksie te gebruik. In hierdie voorbeeld doen ons:

Nou bou ons 'n grafiek:

Ons pas hierdie vergelyking toe om die aantal kyke by 200, 500 en 1000 aandele te bepaal: =9,2937*D4-206,12. Ons kry die volgende resultate:

funksie VOORSPELLING laat jou toe om die aantal kyke op die oomblik te bepaal, as daar byvoorbeeld tweehonderd-en-vyftig herplasings was. Ons pas toe: 0,7;VOORSPELLING(D7;B3:B8;A3:A8);”Die waardes is nie verwant nie”)' class='formula'>. Ons kry die volgende resultate:

Kenmerke van die gebruik van die CORREL-funksie in Excel

Hierdie funksie het die volgende kenmerke:

1. Leë selle word nie in ag geneem nie.
2. Selle wat Boole- en tekstipe inligting bevat, word nie in ag geneem nie.
3. Dubbele ontkenning "-" word gebruik om logiese waardes in die vorm van getalle te verantwoord.
4. Die aantal selle in die bestudeerde skikkings moet ooreenstem, anders sal die #N/A-boodskap vertoon word.

Assessering van die statistiese betekenisvolheid van die korrelasiekoëffisiënt

Wanneer die belangrikheid van 'n korrelasiekoëffisiënt getoets word, is die nulhipotese dat die aanwyser 'n waarde van 0 het, terwyl die alternatief nie. Die volgende formule word vir verifikasie gebruik:

Gevolgtrekking

Korrelasie-analise in 'n sigblad is 'n eenvoudige en outomatiese proses. Om dit uit te voer, hoef jy net te weet waar die nodige gereedskap geleë is en hoe om dit deur die programinstellings te aktiveer.