Korrelationsanalyse in Excel. Ein Beispiel für die Durchführung einer Korrelationsanalyse

Die Korrelationsanalyse ist eine gängige Forschungsmethode, um den Grad der Abhängigkeit des 1. Werts vom 2. Wert zu bestimmen. Die Tabelle verfügt über ein spezielles Tool, mit dem Sie diese Art von Recherche durchführen können.

Die Essenz der Korrelationsanalyse

Es ist notwendig, die Beziehung zwischen zwei verschiedenen Größen zu bestimmen. Mit anderen Worten, es zeigt an, in welche Richtung (kleiner / größer) sich der Wert in Abhängigkeit von Änderungen in der Sekunde ändert.

Zweck der Korrelationsanalyse

Die Abhängigkeit wird hergestellt, wenn die Identifizierung des Korrelationskoeffizienten beginnt. Diese Methode unterscheidet sich von der Regressionsanalyse, da nur ein Indikator anhand der Korrelation berechnet wird. Das Intervall ändert sich von +1 bis -1. Wenn es positiv ist, dann trägt eine Erhöhung des ersten Werts zu einer Erhöhung des 2. bei. Wenn negativ, dann trägt eine Erhöhung des 1. Wertes zu einer Verringerung des 2. Wertes bei. Je höher der Koeffizient, desto stärker wirkt sich ein Wert auf den zweiten aus.

Wichtig! Beim 0. Koeffizienten besteht kein Zusammenhang zwischen den Größen.

Berechnung des Korrelationskoeffizienten

Analysieren wir die Berechnung an mehreren Stichproben. Beispielsweise gibt es tabellarische Daten, in denen die Ausgaben für Werbemaßnahmen und das Verkaufsvolumen nach Monaten in separaten Spalten beschrieben werden. Anhand der Tabelle ermitteln wir den Grad der Abhängigkeit des Verkaufsvolumens vom Geld, das für die Werbeförderung ausgegeben wird.

Methode 1: Bestimmung der Korrelation durch den Funktionsassistenten

CORREL – eine Funktion, mit der Sie eine Korrelationsanalyse durchführen können. Generelle Form - KORREL(massiv1;massiv2). Detaillierte Anleitung:

1. Es ist notwendig, die Zelle auszuwählen, in der das Ergebnis der Berechnung angezeigt werden soll. Klicken Sie auf „Funktion einfügen“ links neben dem Textfeld, um die Formel einzugeben.

2. Der Funktionsassistent wird geöffnet. Hier müssen Sie finden KORREL, klicken Sie darauf und dann auf „OK“.

3. Das Argumente-Fenster öffnet sich. In der Zeile „Array1“ müssen Sie die Koordinaten der Intervalle des 1. der Werte eingeben. In diesem Beispiel ist dies die Spalte Verkaufswert. Sie müssen nur alle Zellen auswählen, die sich in dieser Spalte befinden. In ähnlicher Weise müssen Sie die Koordinaten der zweiten Spalte zur Zeile „Array2“ hinzufügen. In unserem Beispiel ist dies die Spalte Werbekosten.

4. Nachdem Sie alle Bereiche eingegeben haben, klicken Sie auf die Schaltfläche „OK“.

Der Koeffizient wurde in der Zelle angezeigt, die zu Beginn unserer Aktionen angegeben wurde. Das erhaltene Ergebnis ist 0,97. Dieser Indikator spiegelt die hohe Abhängigkeit des ersten Werts vom zweiten wider.

Methode 2: Korrelation mit dem Analysis ToolPak berechnen

Es gibt ein anderes Verfahren zur Bestimmung der Korrelation. Hier wird eine der im Analysepaket enthaltenen Funktionen verwendet. Bevor Sie es verwenden, müssen Sie das Tool aktivieren. Detaillierte Anleitung:

1. Gehen Sie zum Abschnitt „Datei“.

2. Es öffnet sich ein neues Fenster, in dem Sie auf den Abschnitt „Einstellungen“ klicken müssen.
3. Klicken Sie auf „Add-Ons“.
4. Ganz unten finden wir das Element „Verwaltung“. Hier müssen Sie im Kontextmenü „Excel-Add-Ins“ auswählen und auf „OK“ klicken.

5. Ein spezielles Add-ons-Fenster hat sich geöffnet. Setzen Sie ein Häkchen neben das Element „Analysepaket“. Wir klicken auf „OK“.
6. Die Aktivierung war erfolgreich. Kommen wir nun zu den Daten. Der Block „Analyse“ wurde angezeigt, in dem Sie auf „Datenanalyse“ klicken müssen.
7. Wählen Sie im neu erscheinenden Fenster das Element „Korrelation“ aus und klicken Sie auf „OK“.

8. Das Fenster mit den Analyseeinstellungen erschien auf dem Bildschirm. In der Zeile „Eingabeintervall“ muss der Bereich absolut aller an der Analyse beteiligten Säulen eingegeben werden. In diesem Beispiel sind dies die Spalten „Verkaufswert“ und „Werbekosten“. Die Ausgabeanzeigeeinstellungen sind anfänglich auf Neues Arbeitsblatt eingestellt, was bedeutet, dass die Ergebnisse auf einem anderen Blatt angezeigt werden. Optional können Sie den Ausgabeort des Ergebnisses ändern. Nachdem Sie alle Einstellungen vorgenommen haben, klicken Sie auf „OK“.

Die Endergebnisse sind raus. Das Ergebnis ist das gleiche wie bei der ersten Methode – 0,97.

Definition und Berechnung des multiplen Korrelationskoeffizienten in MS Excel

Um den Grad der Abhängigkeit mehrerer Größen zu identifizieren, werden mehrere Koeffizienten verwendet. Die Ergebnisse werden künftig in einer separaten Tabelle, der so genannten Korrelationsmatrix, zusammengefasst.

Detaillierte Anleitung:

1. Im Abschnitt „Daten“ finden wir den bereits bekannten Block „Analyse“ und klicken auf „Datenanalyse“.

2. Klicken Sie im erscheinenden Fenster auf das Element „Korrelation“ und klicken Sie auf „OK“.
3. In der Zeile „Intervall eingeben“ fahren wir das Intervall für drei oder mehr Spalten der Quelltabelle ein. Der Bereich kann manuell eingegeben oder einfach mit der linken Maustaste ausgewählt werden und erscheint automatisch in der gewünschten Zeile. Wählen Sie unter „Gruppierung“ die entsprechende Gruppierungsmethode aus. In „Ausgabeparameter“ wird der Ort angegeben, an dem die Korrelationsergebnisse angezeigt werden. Wir klicken auf „OK“.

4. Bereit! Die Korrelationsmatrix wurde erstellt.

Paarkorrelationskoeffizient in Excel

Lassen Sie uns herausfinden, wie der Paarkorrelationskoeffizient in einer Excel-Tabelle korrekt gezeichnet wird.

Berechnung des Paarkorrelationskoeffizienten in Excel

Beispielsweise haben Sie x- und y-Werte.

X ist die abhängige Variable und y ist die unabhängige. Es ist notwendig, die Richtung und Stärke der Beziehung zwischen diesen Indikatoren zu finden. Schritt-für-Schritt-Anleitung:

1. Lassen Sie uns die Durchschnittswerte mithilfe der Funktion finden HERZ.

2. Lassen Sie uns jeden berechnen х и xdurchschn, у и avg mit dem Operator «-».

3. Wir multiplizieren die berechneten Differenzen.

4. Wir berechnen die Summe der Indikatoren in dieser Spalte. Der Zähler ist das gefundene Ergebnis.

5. Berechne die Nenner der Differenz х и x-Durchschnitt, y и y-mittel. Dazu führen wir die Quadrierung durch.

6. Verwendung der Funktion AUTOSUMMA, finden Sie die Indikatoren in den resultierenden Spalten. Wir multiplizieren. Verwendung der Funktion ROOT quadriere das Ergebnis.

7. Wir berechnen den Quotienten aus den Werten von Nenner und Zähler.

8. CORREL ist eine integrierte Funktion, mit der Sie komplexe Berechnungen vermeiden können. Wir gehen zum „Function Wizard“, wählen CORREL und spezifizieren die Arrays von Indikatoren х и у. Wir erstellen ein Diagramm, das die erhaltenen Werte anzeigt.

Matrix paarweiser Korrelationskoeffizienten in Excel

Lassen Sie uns analysieren, wie die Koeffizienten von gepaarten Matrizen berechnet werden. Beispielsweise gibt es eine Matrix aus vier Variablen.

Schritt-für-Schritt-Anleitung:

1. Wir gehen zur „Datenanalyse“, die sich im Block „Analyse“ der Registerkarte „Daten“ befindet. Wählen Sie Korrelation aus der angezeigten Liste aus.
2. Wir nehmen alle notwendigen Einstellungen vor. „Eingabeintervall“ – das Intervall aller vier Spalten. „Ausgabeintervall“ – der Ort, an dem wir die Summen anzeigen möchten. Wir klicken auf die Schaltfläche „OK“.
3. An der gewählten Stelle wurde eine Korrelationsmatrix erstellt. Jeder Schnittpunkt einer Reihe und einer Spalte ist ein Korrelationskoeffizient. Die Zahl 1 wird angezeigt, wenn die Koordinaten übereinstimmen.

CORREL-Funktion zur Bestimmung von Beziehung und Korrelation in Excel

CORREL – eine Funktion zur Berechnung des Korrelationskoeffizienten zwischen 2 Arrays. Schauen wir uns vier Beispiele für alle Fähigkeiten dieser Funktion an.

Beispiele für die Verwendung der CORREL-Funktion in Excel

Erstes Beispiel. Es gibt eine Tafel mit Informationen über die durchschnittlichen Gehälter der Mitarbeiter des Unternehmens im Laufe von elf Jahren und den Wechselkurs von $. Es ist notwendig, die Beziehung zwischen diesen beiden Größen zu identifizieren. Die Tabelle sieht so aus:

Der Berechnungsalgorithmus sieht folgendermaßen aus:

Die angezeigte Punktzahl liegt nahe bei 1. Ergebnis:

Bestimmung des Korrelationskoeffizienten der Auswirkung von Aktionen auf das Ergebnis

Zweites Beispiel. Zwei Bieter baten zwei verschiedene Agenturen um Hilfe bei einer XNUMX-tägigen Werbeaktion. Jeden Tag wurde eine soziale Umfrage durchgeführt, die den Grad der Unterstützung für jeden Bewerber ermittelte. Jeder Befragte konnte sich für einen der beiden Bewerber entscheiden oder sich gegen alle stellen. Es muss ermittelt werden, wie stark jede Werbeaktion den Betreuungsgrad der Bewerber beeinflusst, welches Unternehmen effizienter ist.

Mit den folgenden Formeln berechnen wir den Korrelationskoeffizienten:

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

Ergebnisse:

Aus den erhaltenen Ergebnissen wird deutlich, dass der Grad der Unterstützung für den 1. Bewerber mit jedem Tag der Werbepromotion zunahm, daher nähert sich der Korrelationskoeffizient 1. Als die Werbung gestartet wurde, hatte der andere Bewerber eine große Anzahl von Vertrauen, und für 5 Tage gab es einen positiven Trend. Dann nahm der Grad des Vertrauens ab und fiel am fünfzehnten Tag unter die ursprünglichen Indikatoren. Niedrige Werte deuten darauf hin, dass die Beförderung die Unterstützung negativ beeinflusst hat. Vergessen Sie nicht, dass auch andere Begleitfaktoren, die nicht tabellarisch betrachtet werden, die Indikatoren beeinflussen können.

Analyse der Popularität von Inhalten durch Korrelation von Videoaufrufen und Reposts

Drittes Beispiel. Eine Person, die für ihre eigenen Videos auf YouTube-Videohosting werben möchte, verwendet soziale Netzwerke, um den Kanal zu bewerben. Er stellt fest, dass es einen Zusammenhang zwischen der Anzahl der Reposts in sozialen Netzwerken und der Anzahl der Aufrufe des Kanals gibt. Ist es möglich, die zukünftige Leistung mit Tabellenkalkulationstools vorherzusagen? Es ist notwendig, die Angemessenheit der Anwendung der linearen Regressionsgleichung zu identifizieren, um die Anzahl der Videoansichten in Abhängigkeit von der Anzahl der Reposts vorherzusagen. Tabelle mit Werten:

Nun ist es notwendig, das Vorhandensein einer Beziehung zwischen 2 Indikatoren gemäß der folgenden Formel zu bestimmen:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Starke direkte Beziehung”;”Starke inverse Beziehung”);”Schwache oder keine Beziehung”)' class='formula'>

Wenn der resultierende Koeffizient größer als 0,7 ist, ist es besser, die lineare Regressionsfunktion zu verwenden. In diesem Beispiel machen wir:

Jetzt bauen wir einen Graphen:

Wir wenden diese Gleichung an, um die Anzahl der Aufrufe bei 200, 500 und 1000 Shares zu bestimmen: =9,2937*D4-206,12. Wir erhalten folgende Ergebnisse:

Funktion PROGNOSE ermöglicht es Ihnen, die Anzahl der Aufrufe im Moment zu bestimmen, wenn es beispielsweise zweihundertfünfzig Reposts gab. Wir bewerben uns: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Die Werte sind nicht verwandt”)' class='formula'>. Wir erhalten folgende Ergebnisse:

Merkmale der Verwendung der CORREL-Funktion in Excel

Diese Funktion hat folgende Eigenschaften:

1. Leere Zellen werden nicht berücksichtigt.
2. Zellen, die Informationen vom Typ Boolean und Text enthalten, werden nicht berücksichtigt.
3. Die doppelte Negation „-“ wird verwendet, um logische Werte in Form von Zahlen zu berücksichtigen.
4. Die Anzahl der Zellen in den untersuchten Arrays muss übereinstimmen, andernfalls wird die Meldung #NV angezeigt.

Bewertung der statistischen Signifikanz des Korrelationskoeffizienten

Beim Testen der Signifikanz eines Korrelationskoeffizienten lautet die Nullhypothese, dass der Indikator einen Wert von 0 hat, während die Alternative dies nicht tut. Zur Überprüfung wird folgende Formel verwendet:

Zusammenfassung

Die Korrelationsanalyse in einer Tabellenkalkulation ist ein einfacher und automatisierter Prozess. Um es auszuführen, müssen Sie nur wissen, wo sich die erforderlichen Tools befinden und wie Sie sie über die Programmeinstellungen aktivieren.