Korrelationsanalys i Excel. Ett exempel på att utföra en korrelationsanalys

Korrelationsanalys är en vanlig forskningsmetod som används för att bestämma graden av beroende av 1:a värdet på 2:an. Kalkylarket har ett speciellt verktyg som låter dig implementera den här typen av forskning.

Kärnan i korrelationsanalys

Det är nödvändigt att bestämma förhållandet mellan två olika kvantiteter. Med andra ord avslöjar den i vilken riktning (mindre / större) värdet ändras beroende på förändringar i tvåan.

Syfte med korrelationsanalys

Beroende etableras när identifieringen av korrelationskoefficienten börjar. Denna metod skiljer sig från regressionsanalys, eftersom det bara finns en indikator som beräknas med hjälp av korrelation. Intervallet ändras från +1 till -1. Om det är positivt bidrar en ökning av det första värdet till en ökning av det andra. Om det är negativt, bidrar en ökning av det första värdet till en minskning av det andra. Ju högre koefficient, desto starkare ett värde påverkar det andra.

Viktigt! Vid den 0:e koefficienten finns det inget samband mellan kvantiteterna.

Beräkning av korrelationskoefficienten

Låt oss analysera beräkningen på flera prover. Till exempel finns det tabelldata, där utgifterna för reklamkampanjer och försäljningsvolymen beskrivs med månader i separata kolumner. Baserat på tabellen kommer vi att ta reda på graden av beroende av försäljningsvolymen på pengarna som spenderas på reklamkampanjer.

Metod 1: Bestämma korrelation genom funktionsguiden

CORREL – en funktion som låter dig implementera en korrelationsanalys. Allmän form – CORREL(massiv1;massiv2). Detaljerade instruktioner:

1. Det är nödvändigt att välja cellen där det är planerat att visa resultatet av beräkningen. Klicka på "Infoga funktion" till vänster om textfältet för att ange formeln.

2. Funktionsguiden öppnas. Här måste du hitta KORREL, klicka på den och sedan på "OK".

3. Argumentfönstret öppnas. På raden "Array1" måste du ange koordinaterna för intervallen för det 1:a av värdena. I det här exemplet är detta kolumnen Försäljningsvärde. Du behöver bara markera alla celler som finns i den här kolumnen. På samma sätt måste du lägga till koordinaterna för den andra kolumnen till raden "Array2". I vårt exempel är detta kolumnen Annonseringskostnader.

4. När du har angett alla intervall klickar du på knappen "OK".

Koefficienten visades i cellen som indikerades i början av våra åtgärder. Det erhållna resultatet är 0,97. Denna indikator reflekterar det första värdets höga beroende av det andra.

Metod 2: Beräkna korrelation med hjälp av Analysis ToolPak

Det finns en annan metod för att bestämma korrelation. Här används en av funktionerna som finns i analyspaketet. Innan du använder det måste du aktivera verktyget. Detaljerade instruktioner:

1. Gå till avsnittet "Arkiv".

2. Ett nytt fönster öppnas där du måste klicka på avsnittet "Inställningar".
3. Klicka på "Tillägg".
4. Vi hittar elementet "Management" längst ner. Här måste du välja "Excel-tillägg" från snabbmenyn och klicka på "OK".

5. Ett speciellt tilläggsfönster har öppnats. Placera en bock bredvid elementet "Analysis Package". Vi klickar på "OK".
6. Aktiveringen lyckades. Låt oss nu gå till Data. Blocket "Analys" dök upp, där du måste klicka på "Dataanalys".
7. I det nya fönstret som visas, välj elementet "Korrelation" och klicka på "OK".

8. Fönstret för analysinställningar dök upp på skärmen. På raden "Inmatningsintervall" är det nödvändigt att ange intervallet för absolut alla kolumner som deltar i analysen. I det här exemplet är det kolumnerna "Försäljningsvärde" och "Annonskostnader". Utdatavisningsinställningarna är initialt inställda på Nytt arbetsblad, vilket innebär att resultaten kommer att visas på ett annat ark. Alternativt kan du ändra utgångsplatsen för resultatet. När du har gjort alla inställningar klickar du på "OK".

Slutresultaten är ute. Resultatet är detsamma som i den första metoden – 0,97.

Definition och beräkning av multipel korrelationskoefficient i MS Excel

För att identifiera graden av beroende av flera kvantiteter används flera koefficienter. I framtiden sammanfattas resultaten i en separat tabell, kallad korrelationsmatrisen.

Detaljerad guide:

1. I avsnittet "Data" hittar vi det redan kända "Analys"-blocket och klickar på "Dataanalys".

2. I fönstret som visas klickar du på elementet "Korrelation" och klickar på "OK".
3. På raden "Inmatningsintervall" kör vi i intervallet för tre eller flera kolumner i källtabellen. Området kan matas in manuellt eller helt enkelt välja det med LMB, och det kommer automatiskt att visas på önskad rad. I "Gruppering" väljer du lämplig grupperingsmetod. I "Output Parameter" anger du platsen där korrelationsresultaten kommer att visas. Vi klickar på "OK".

4. Redo! Korrelationsmatrisen byggdes.

Parkorrelationskoefficient i Excel

Låt oss ta reda på hur man korrekt ritar parkorrelationskoefficienten i ett Excel-kalkylblad.

Beräkning av parkorrelationskoefficient i Excel

Till exempel har du x- och y-värden.

X är den beroende variabeln och y är den oberoende. Det är nödvändigt att hitta riktningen och styrkan i förhållandet mellan dessa indikatorer. Steg-för-steg-instruktion:

1. Låt oss hitta medelvärdena med funktionen HJÄRTA.

2. Låt oss beräkna var och en х и xavg, у и avg med operatorn «-».

3. Vi multiplicerar de beräknade skillnaderna.

4. Vi beräknar summan av indikatorerna i denna kolumn. Täljaren är resultatet som hittats.

5. Beräkna skillnadens nämnare х и x-medel, y и y-medium. För att göra detta kommer vi att utföra kvadreringen.

6. Använda funktionen AUTOSUMMA, hitta indikatorerna i de resulterande kolumnerna. Vi multiplicerar. Använder funktionen ROT kvadrera resultatet.

7. Vi beräknar kvoten med hjälp av värdena för nämnaren och täljaren.

8. CORREL är en integrerad funktion som låter dig förhindra komplexa beräkningar. Vi går till "Funktionsguiden", väljer CORREL och anger uppsättningarna av indikatorer х и у. Vi bygger en graf som visar de erhållna värdena.

Matris av parvisa korrelationskoefficienter i Excel

Låt oss analysera hur man beräknar koefficienterna för parade matriser. Till exempel finns det en matris med fyra variabler.

Steg-för-steg-instruktion:

1. Vi går till "Dataanalys", som finns i "Analys"-blocket på fliken "Data". Välj Korrelation från listan som visas.
2. Vi ställer in alla nödvändiga inställningar. "Inmatningsintervall" – intervallet för alla fyra kolumnerna. "Utmatningsintervall" - platsen där vi vill visa totalerna. Vi klickar på knappen "OK".
3. En korrelationsmatris byggdes på den valda platsen. Varje skärningspunkt mellan en rad och en kolumn är en korrelationskoefficient. Siffran 1 visas när koordinaterna matchar.

CORREL-funktion för att bestämma relation och korrelation i Excel

CORREL – en funktion som används för att beräkna korrelationskoefficienten mellan 2 arrayer. Låt oss titta på fyra exempel på alla förmågor för denna funktion.

Exempel på användning av CORREL-funktionen i Excel

Första exemplet. Det finns en skylt med information om de genomsnittliga lönerna för företagets anställda under loppet av elva år och växelkursen på $. Det är nödvändigt att identifiera sambandet mellan dessa två kvantiteter. Tabellen ser ut så här:

Beräkningsalgoritmen ser ut så här:

Den visade poängen är nära 1. Resultat:

Bestämning av korrelationskoefficienten för inverkan av åtgärder på resultatet

Andra exemplet. Två anbudsgivare kontaktade två olika byråer för att få hjälp med en femton dagar lång kampanj. Varje dag genomfördes en social undersökning som fastställde graden av stöd för varje sökande. Varje intervjuperson kunde välja en av de två sökandena eller motsätta sig alla. Det är nödvändigt att avgöra hur mycket varje reklamkampanj påverkade graden av stöd för sökande, vilket företag är mer effektivt.

Med hjälp av formlerna nedan beräknar vi korrelationskoefficienten:

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

Resultat:

Av de erhållna resultaten blir det tydligt att graden av stöd för den första sökanden ökade för varje dag av reklamfrämjande, därför närmar sig korrelationskoefficienten 1. När reklam lanserades hade den andra sökanden ett stort förtroende, och för 1 dagar var det en positiv trend. Sedan minskade graden av tillit och på den femtonde dagen sjönk den under de ursprungliga indikatorerna. Låga poäng tyder på att marknadsföring har påverkat stödet negativt. Glöm inte att andra samtidiga faktorer som inte beaktas i tabellform också kan påverka indikatorerna.

Analys av innehållets popularitet genom korrelation av videovisningar och reposter

Tredje exemplet. En person för att marknadsföra sina egna videor på YouTube-videohotell använder sociala nätverk för att marknadsföra kanalen. Han märker att det finns ett visst samband mellan antalet reposter i sociala nätverk och antalet visningar på kanalen. Är det möjligt att förutsäga framtida prestanda med hjälp av kalkylbladsverktyg? Det är nödvändigt att identifiera rimligheten i att tillämpa den linjära regressionsekvationen för att förutsäga antalet videovisningar beroende på antalet reposter. Tabell med värden:

Nu är det nödvändigt att bestämma närvaron av ett förhållande mellan 2 indikatorer enligt formeln nedan:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Starkt direkt samband”;”Starkt omvänt samband”);”Svagt eller inget samband”)' class='formula'>

Om den resulterande koefficienten är högre än 0,7 är det lämpligare att använda den linjära regressionsfunktionen. I det här exemplet gör vi:

Nu bygger vi en graf:

Vi använder denna ekvation för att bestämma antalet visningar vid 200, 500 och 1000 delningar: =9,2937*D4-206,12. Vi får följande resultat:

Funktion PROGNOS låter dig bestämma antalet visningar för tillfället, om det till exempel fanns tvåhundrafemtio reposter. Vi tillämpar: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Värdena är inte relaterade”)' class='formula'>. Vi får följande resultat:

Funktioner för att använda CORREL-funktionen i Excel

Denna funktion har följande funktioner:

1. Tomma celler tas inte med i beräkningen.
2. Celler som innehåller information om boolesk och texttyp tas inte med i beräkningen.
3. Dubbel negation "-" används för att redogöra för logiska värden i form av siffror.
4. Antalet celler i de studerade arrayerna måste matcha, annars kommer #N/A-meddelandet att visas.

Bedömning av korrelationskoefficientens statistiska signifikans

När man testar signifikansen av en korrelationskoefficient är nollhypotesen att indikatorn har värdet 0, medan alternativet inte har det. Följande formel används för verifiering:

Slutsats

Korrelationsanalys i ett kalkylblad är en enkel och automatiserad process. För att utföra det behöver du bara veta var de nödvändiga verktygen finns och hur du aktiverar dem genom programinställningarna.