Correlatieanalyse in Excel. Een voorbeeld van het uitvoeren van een correlatieanalyse

Correlatieanalyse is een veelgebruikte onderzoeksmethode die wordt gebruikt om de mate van afhankelijkheid van de 1e waarde op de 2e te bepalen. De spreadsheet heeft een speciale tool waarmee je dit soort onderzoek kunt uitvoeren.

De essentie van correlatieanalyse

Het is noodzakelijk om de relatie tussen twee verschillende grootheden te bepalen. Met andere woorden, het onthult in welke richting (kleiner / groter) de waarde verandert afhankelijk van veranderingen in de tweede.

Doel van correlatieanalyse

Afhankelijkheid wordt vastgesteld wanneer de identificatie van de correlatiecoëfficiënt begint. Deze methode verschilt van regressieanalyse, omdat er slechts één indicator wordt berekend met behulp van correlatie. Het interval verandert van +1 naar -1. Als het positief is, draagt ​​​​een toename van de eerste waarde bij aan een toename van de 2e. Indien negatief, dan draagt ​​een stijging van de 1e waarde bij aan een daling van de 2e. Hoe hoger de coëfficiënt, hoe sterker de ene waarde de tweede beïnvloedt.

Belangrijk! Bij de 0-coëfficiënt is er geen verband tussen de grootheden.

Berekening van de correlatiecoëfficiënt

Laten we de berekening op verschillende monsters analyseren. Er zijn bijvoorbeeld gegevens in tabelvorm, waarbij de uitgaven voor reclame-promotie en het verkoopvolume in afzonderlijke kolommen per maand worden beschreven. Op basis van de tabel zullen we achterhalen in hoeverre het verkoopvolume afhankelijk is van het geld dat wordt besteed aan reclamepromotie.

Methode 1: Correlatie bepalen via de functiewizard

CORREL – een functie waarmee u een correlatieanalyse kunt uitvoeren. Algemene vorm - CORREL(massief1;massief2). Gedetailleerde instructies:

1. Het is noodzakelijk om de cel te selecteren waarin het resultaat van de berekening moet worden weergegeven. Klik op "Functie invoegen" links van het tekstveld om de formule in te voeren.

2. De Functiewizard wordt geopend. Hier moet je vinden CORREL, klik erop en vervolgens op "OK".

3. Het argumentenvenster wordt geopend. In de regel "Array1" moet u de coördinaten van de intervallen van de 1e van de waarden invoeren. In dit voorbeeld is dit de kolom Verkoopwaarde. U hoeft alleen alle cellen in deze kolom te selecteren. Op dezelfde manier moet u de coördinaten van de tweede kolom toevoegen aan de regel "Array2". In ons voorbeeld is dit de kolom Advertentiekosten.

4. Nadat u alle bereiken hebt ingevoerd, klikt u op de knop "OK".

De coëfficiënt werd weergegeven in de cel die werd aangegeven aan het begin van onze acties. Het verkregen resultaat is 0,97. Deze indicator weerspiegelt de grote afhankelijkheid van de eerste waarde van de tweede.

Methode 2: Correlatie berekenen met behulp van het Analysis ToolPak

Er is een andere methode om correlatie te bepalen. Hier wordt een van de functies uit het analysepakket gebruikt. Voordat u het gebruikt, moet u de tool activeren. Gedetailleerde instructies:

1. Ga naar het gedeelte "Bestand".

2. Er wordt een nieuw venster geopend waarin u op het gedeelte "Instellingen" moet klikken.
3. Klik op "Add-ons".
4. Onderaan vinden we het element “Management”. Hier moet u "Excel-invoegtoepassingen" selecteren in het contextmenu en op "OK" klikken.

5. Er is een speciaal add-onsvenster geopend. Plaats een vinkje naast het element "Analysepakket". Wij klikken op "OK".
6. Activering is gelukt. Laten we nu naar Gegevens gaan. Het blok "Analyse" verscheen, waarin u op "Gegevensanalyse" moet klikken.
7. Selecteer in het nieuwe venster dat verschijnt het element "Correlatie" en klik op "OK".

8. Het venster met analyse-instellingen verscheen op het scherm. In de regel "Invoerinterval" is het noodzakelijk om het bereik in te voeren van absoluut alle kolommen die aan de analyse deelnemen. In dit voorbeeld zijn dat de kolommen “Verkoopwaarde” en “Advertentiekosten”. De instellingen voor uitvoerweergave zijn aanvankelijk ingesteld op Nieuw werkblad, wat betekent dat de resultaten op een ander blad worden weergegeven. Optioneel kunt u de uitvoerlocatie van het resultaat wijzigen. Nadat u alle instellingen heeft gemaakt, klikt u op "OK".

De eindscores zijn bekend. Het resultaat is hetzelfde als bij de eerste methode - 0,97.

Definitie en berekening van meerdere correlatiecoëfficiënten in MS Excel

Om de mate van afhankelijkheid van verschillende grootheden te identificeren, worden meerdere coëfficiënten gebruikt. In de toekomst worden de resultaten samengevat in een aparte tabel, de correlatiematrix.

Gedetailleerde gids:

1. In de sectie "Data" vinden we het al bekende blok "Analyse" en klikken op "Data-analyse".

2. Klik in het venster dat verschijnt op het element "Correlatie" en klik op "OK".
3. In de regel “Invoerinterval” rijden we het interval in voor drie of meer kolommen van de brontabel. Het bereik kan handmatig worden ingevoerd of eenvoudig met de LMB worden geselecteerd, waarna het automatisch op de gewenste regel verschijnt. Selecteer in “Groeperen” de juiste groeperingsmethode. In "Uitvoerparameter" specificeert u de locatie waar de correlatieresultaten worden weergegeven. Wij klikken op "OK".

4. Klaar! De correlatiematrix werd gebouwd.

Koppelcorrelatiecoëfficiënt in Excel

Laten we eens kijken hoe we de correlatiecoëfficiënt van het paar correct kunnen tekenen in een Excel-spreadsheet.

Berekening van paarcorrelatiecoëfficiënt in Excel

U hebt bijvoorbeeld x- en y-waarden.

X is de afhankelijke variabele en y is de onafhankelijke. Het is noodzakelijk om de richting en sterkte van de relatie tussen deze indicatoren te vinden. Stapsgewijze instructie:

1. Laten we de gemiddelde waarden vinden met behulp van de functie HART.

2. Laten we elk berekenen х и helaas, у и avg met de operator «-».

3. We vermenigvuldigen de berekende verschillen.

4. We berekenen de som van de indicatoren in deze kolom. De teller is het gevonden resultaat.

5. Bereken de noemers van het verschil х и x-gemiddeld, y и y-medium. Om dit te doen, zullen we de kwadratuur uitvoeren.

6. De functie gebruiken AUTOSUMMA, zoek de indicatoren in de resulterende kolommen. We doen vermenigvuldiging. De functie gebruiken: ROOT het resultaat kwadrateren.

7. We berekenen het quotiënt met behulp van de waarden van de noemer en teller.

8. CORREL is een geïntegreerde functie waarmee u complexe berekeningen kunt voorkomen. We gaan naar de "Functiewizard", selecteren CORREL en specificeren de arrays van indicatoren х и у. We bouwen een grafiek die de verkregen waarden weergeeft.

Matrix van paarsgewijze correlatiecoëfficiënten in Excel

Laten we analyseren hoe we de coëfficiënten van gepaarde matrices kunnen berekenen. Er is bijvoorbeeld een matrix van vier variabelen.

Stapsgewijze instructie:

1. We gaan naar de "Gegevensanalyse", die zich in het blok "Analyse" van het tabblad "Gegevens" bevindt. Selecteer Correlatie in de lijst die verschijnt.
2. We stellen alle benodigde instellingen in. "Invoerinterval" - het interval van alle vier de kolommen. "Uitvoerinterval" - de plaats waar we de totalen willen weergeven. Wij klikken op de knop "OK".
3. Op de gekozen plaats is een correlatiematrix gebouwd. Elk snijpunt van een rij en een kolom is een correlatiecoëfficiënt. Het cijfer 1 wordt weergegeven als de coördinaten overeenkomen.

CORREL-functie om relatie en correlatie in Excel te bepalen

CORREL – een functie die wordt gebruikt om de correlatiecoëfficiënt tussen 2 arrays te berekenen. Laten we eens kijken naar vier voorbeelden van alle mogelijkheden van deze functie.

Voorbeelden van het gebruik van de CORREL-functie in Excel

Eerste voorbeeld. Er is een bord met informatie over de gemiddelde salarissen van de werknemers van het bedrijf in de loop van elf jaar en de wisselkoers van $. Het is noodzakelijk om de relatie tussen deze twee grootheden te identificeren. De tabel ziet er als volgt uit:

Het rekenalgoritme ziet er als volgt uit:

De weergegeven score ligt dicht bij 1. Resultaat:

Bepaling van de correlatiecoëfficiënt van de impact van acties op het resultaat

Tweede voorbeeld. Twee bieders benaderden twee verschillende bureaus voor hulp bij een vijftiendaagse actie. Elke dag werd er een sociale peiling gehouden, die de mate van steun voor elke aanvrager vaststelde. Elke geïnterviewde kon een van de twee kandidaten kiezen of zich tegen alle kandidaten verzetten. Het is noodzakelijk om te bepalen hoeveel elke reclamepromotie de mate van ondersteuning voor aanvragers heeft beïnvloed, welk bedrijf efficiënter is.

Met behulp van onderstaande formules berekenen we de correlatiecoëfficiënt:

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

Resultaten:

Uit de verkregen resultaten wordt duidelijk dat de mate van steun voor de 1e aanvrager toenam met elke dag van reclamepromotie, daarom nadert de correlatiecoëfficiënt 1. Toen de reclame werd gelanceerd, had de andere aanvrager een groot aantal vertrouwenspersonen, en voor 5 dagen was er een positieve trend. Daarna nam de mate van vertrouwen af ​​en op de vijftiende dag zakte het onder de aanvankelijke indicatoren. Lage scores suggereren dat promotie de steun negatief heeft beïnvloed. Vergeet niet dat andere bijkomende factoren die niet in tabelvorm worden beschouwd, ook van invloed kunnen zijn op de indicatoren.

Analyse van de populariteit van inhoud door correlatie van videoweergaven en reposts

Derde voorbeeld. Een persoon die zijn eigen video's op YouTube-videohosting promoot, gebruikt sociale netwerken om voor het kanaal te adverteren. Hij merkt dat er een relatie is tussen het aantal reposts in sociale netwerken en het aantal views op het kanaal. Is het mogelijk om toekomstige prestaties te voorspellen met behulp van spreadsheettools? Het is noodzakelijk om de redelijkheid te identificeren van het toepassen van de lineaire regressievergelijking om het aantal videoweergaven te voorspellen, afhankelijk van het aantal reposts. Tabel met waarden:

Nu is het noodzakelijk om de aanwezigheid van een relatie tussen 2 indicatoren te bepalen volgens de onderstaande formule:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Sterke directe relatie”;”Sterke inverse relatie”);”Zwak of geen relatie”)' class='formule'>

Als de resulterende coëfficiënt hoger is dan 0,7, dan is het beter om de lineaire regressiefunctie te gebruiken. In dit voorbeeld doen we:

Nu bouwen we een grafiek:

We passen deze vergelijking toe om het aantal views bij 200, 500 en 1000 shares te bepalen: =9,2937*D4-206,12. We krijgen de volgende resultaten:

Functie VOORUITZICHT stelt u in staat om het aantal views op dit moment te bepalen, als er bijvoorbeeld tweehonderdvijftig reposts waren. Wij passen toe: 0,7;PREDICTION(D7;B3:B8;A3:A8);”De waarden zijn niet gerelateerd”)' class='formula'>. We krijgen de volgende resultaten:

Kenmerken van het gebruik van de CORREL-functie in Excel

Deze functie heeft de volgende kenmerken:

1. Er wordt geen rekening gehouden met lege cellen.
2. Er wordt geen rekening gehouden met cellen die Booleaanse en teksttype-informatie bevatten.
3. Dubbele ontkenning "-" wordt gebruikt om rekening te houden met logische waarden in de vorm van getallen.
4. Het aantal cellen in de bestudeerde arrays moet overeenkomen, anders wordt het bericht #N/A weergegeven.

Beoordeling van de statistische significantie van de correlatiecoëfficiënt

Bij het testen van de significantie van een correlatiecoëfficiënt is de nulhypothese dat de indicator een waarde van 0 heeft, terwijl het alternatief dat niet heeft. De volgende formule wordt gebruikt voor verificatie:

Conclusie

Correlatieanalyse in een spreadsheet is een eenvoudig en geautomatiseerd proces. Om het uit te voeren, hoeft u alleen te weten waar de benodigde tools zich bevinden en hoe u ze kunt activeren via de programma-instellingen.