Wie berechnet man den Korrelationskoeffizienten zwischen zwei Variablen in Excel?
Wir verwenden normalerweise den Korrelationskoeffizienten (ein Wert zwischen -1 und 1), um die Stärke und Richtung der linearen Beziehung zwischen zwei Variablen anzuzeigen. Ein Korrelationskoeffizient ist eine weit verbreitete Statistik, die Ihnen hilft, Beziehungen zu verstehen, wie zum Beispiel den Zusammenhang zwischen Verkauf und Werbeausgaben, Temperatur und Eisverkauf oder anderen gepaarten Daten. In Excel gibt es mehrere einfache Methoden zur Berechnung des Korrelationskoeffizienten, einschließlich integrierter Funktionen und Analysewerkzeuge.
Methode A: Direkte Verwendung der KORREL-Funktion
Methode B: Anwenden der Datenanalyse und Ausgabe der Analyse
Methode C: Verwendung der PEARSON-Funktion als Alternative
Methode D: Verwendung von VBA-Code zur Berechnung von Korrelationskoeffizienten für mehrere Paare
Methode A: Direkte Verwendung der KORREL-Funktion
Betrachten Sie zwei Listen mit Daten, die jeweils eine Variable darstellen. Wenn Sie den Korrelationskoeffizienten zwischen diesen beiden Variablen in Excel berechnen möchten, ist diese Methode sowohl schnell als auch effizient.
Für die praktische Nutzung stellen Sie sicher, dass beide Datenbereiche numerisch sind und die gleiche Anzahl von Beobachtungen enthalten. Zum Beispiel, wenn Sie die folgenden gepaarten Daten haben:
Wählen Sie eine leere Zelle aus, in der Sie das Ergebnis anzeigen möchten. Geben Sie die folgende Formel ein, und drücken Sie dann die "Eingabetaste", um den Korrelationskoeffizienten zu berechnen:
=CORREL(A2:A7,B2:B7)
In dieser Formel repräsentieren A2:A7 und B2:B7 die beiden Listen mit den Variablen, die Sie analysieren möchten. Die Bereiche müssen gleich lang sein, und jedes Paar sollte der gleichen Beobachtung entsprechen.
Praktischer Tipp: KORREL ignoriert automatisch leere Zellen und Text, aber wenn es keine gültigen numerischen Paare in den beiden Spalten gibt, wird ein #DIV/0! Fehler zurückgegeben. Stellen Sie sicher, dass Ihre Daten ordnungsgemäß ausgerichtet sind und numerische Paare für eine genaue Korrelationsberechnung enthalten.
Sobald Sie den Korrelationskoeffizienten berechnet haben, können Sie ein Liniendiagramm einfügen, um die Beziehungen visuell zu beobachten und die Korrelation weiter zu interpretieren, wie unten gezeigt:
Diese Methode ist am besten für schnelle, manuelle Überprüfungen zwischen zwei kleinen Datensätzen oder beim interaktiven Arbeiten innerhalb Ihrer Tabelle geeignet. Sie ist gut für Benutzer geeignet, die ein sofortiges Ergebnis ohne die Notwendigkeit für fortgeschrittene statistische Ausgaben suchen.
Entfesseln Sie die Magie von Excel mit Kutools AI
- Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
- VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
- Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.
Methode B: Anwenden der Datenanalyse und Ausgabe der Analyse
Wenn Sie die Korrelation zwischen mehreren Variablen auf einmal analysieren müssen oder eine umfassendere Ausgabetabelle wünschen, bietet die Excel „Analyse-ToolPak“ eine nützliche Lösung. Dieses Add-In generiert eine Korrelationsmatrix und ermöglicht es Ihnen, mehrere Variablen in einem einzigen Schritt zu vergleichen, was für größere Datensätze oder die Erstellung statistischer Berichte wertvoll ist.
1. Wenn Sie das Add-In „Datenanalyse“ bereits zur Registerkarte „Daten“ hinzugefügt haben, können Sie direkt zu Schritt 3 gehen. Andernfalls klicken Sie auf Datei > Optionen. Wählen Sie im Dialogfeld „Excel Optionen“ Add-Ins aus dem linken Bereich aus, und klicken Sie dann auf die Schaltfläche Gehe zu neben dem Feld „Excel Add-ins“.
2. Aktivieren Sie im Dialogfeld „Add-Ins“ das Kontrollkästchen Analyse-Funktionenund klicken Sie dann auf OK. Dadurch wird die Gruppe „Datenanalyse“ zur Registerkarte Daten hinzugefügt.
3. Klicken Sie als Nächstes auf Daten > Datenanalyse. Wählen Sie im Popup-Dialogfeld „Datenanalyse“ Korrelation aus der Liste und klicken Sie dann auf OK.
4. Konfigurieren Sie im Korrelationsdialogfeld Folgendes:
1) Wählen Sie den Bereich aus, der Ihre Daten enthält.
2) Wählen Sie je nach der Organisation Ihrer Daten entweder die Option „Spalten“ oder „Zeilen“.
3) Wenn Ihre Daten Überschriften enthält, aktivieren Sie die Option „Beschriftungen in der ersten Zeile“.
4) Geben Sie einen Ausgabebereich in den „Ausgabeoptionen“ an, um die Ergebnisse anzuzeigen.
5. Klicken Sie auf OK um die Korrelationsanalysetabelle zu generieren. Die Korrelationskoeffizienten werden im angegebenen Bereich dargestellt.
Diese Methode ist geeignet, wenn Sie die Beziehungen zwischen mehr als zwei Variablen bewerten müssen oder eine Zusammenfassungstabelle für Berichtszwecke wünschen. Die Ausgabe der Datenanalyse ist prägnant, bietet jedoch keine zusätzlichen Signifikanzstatistiken. Wenn Sie unerwartete Ergebnisse erhalten, überprüfen Sie Ihre Daten auf Konsistenz, leere Zellen und korrekte Bereichsauswahl.
Methode C: Verwenden Sie die PEARSON-Funktion als Alternative
Neben KORREL bietet Excel die PEARSON-Funktion, die ebenfalls den Pearson-Korrelationskoeffizienten zwischen zwei Variablen berechnet. Funktionell liefern PEARSON und KORREL das gleiche Ergebnis. PEARSON hält sich jedoch streng an die ursprüngliche mathematische Formel, während KORREL für Excel-Umgebungen optimiert ist. Wenn Sie an statistische Theorie gewöhnt sind oder mit statistischen Tools außerhalb von Excel arbeiten, könnte PEARSON vertrauter erscheinen.
Zum Beispiel können Sie mit zwei numerischen Listen in A2:A7 und B2:B7 die Korrelation wie folgt berechnen:
1. Wählen Sie eine Zelle aus, in der Sie das Ergebnis anzeigen möchten, und geben Sie diese Formel ein:
=PEARSON(A2:A7,B2:B7)2. Drücken Sie die Eingabetaste, um die Berechnung abzuschließen. Wenn Sie zusätzliche Datensätze analysieren möchten, passen Sie die Zellbereiche entsprechend an oder ziehen Sie die Formel in andere Zellen.
Tipps: PEARSON ignoriert Text oder logische Werte, also stellen Sie sicher, dass beide Bereiche ausschließlich numerische Werte enthalten und gleich lang sind. Wenn in einer Spalte Daten fehlen, passen Sie Ihre Bereiche entsprechend an, um Fehler zu vermeiden.
Die Verwendung von PEARSON ist besonders praktisch für Benutzer, die von anderen statistischen Softwarepaketen migrieren, oder für akademische Umgebungen, in denen eine strikte Adhärenz an Terminologie erwartet wird. Sowohl KORREL als auch PEARSON liefern für typische Anwendungsfälle in Excel das gleiche Ergebnis.
Wenn Sie einen #DIV/0!-Fehler erhalten, überprüfen Sie, ob beide Bereiche gleich lang sind und keine nicht übereinstimmenden leeren oder nicht-numerischen Zellen enthalten.
Vorteile: Einfach zu benutzen, konsistent mit statistischer Software; Nachteile: Bietet keinen signifikanten Unterschied zu KORREL für die meisten Benutzer.
Methode D: Verwenden Sie VBA-Code zur Berechnung von Korrelationskoeffizienten für mehrere Paare
Wenn Sie die Berechnung von Korrelationskoeffizienten für mehrere Datensätze automatisieren müssen (z.B. bei der Arbeit mit vielen Variablenkombinationen), ist das Schreiben eines einfachen VBA-Makros eine effiziente Wahl. Diese Methode eignet sich gut für fortgeschrittene Benutzer, die große Datensätze verarbeiten oder repetitive Analysen automatisieren möchten.
1. Um diese Methode zu verwenden, öffnen Sie zunächst den VBA-Editor, indem Sie auf Entwickler > Visual Basic klicken. Gehen Sie im Fenster „Visual Basic for Applications“ zu Einfügen > Modul, und fügen Sie den folgenden Code in das Modul ein:
Sub BatchCalculateCorrelations()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim lastRow As Long
Dim i As Long
Dim resultCol As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
If rng1.Rows.Count <> rng2.Rows.Count Then
MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
For i = 1 To rng2.Columns.Count
resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
Next i
End Sub2. Nachdem Sie den Code eingefügt haben, schließen Sie den VBA-Editor. Drücken Sie in Excel Alt + F8, wählen Sie BatchCalculateCorrelations aus und klicken Sie auf Ausführen. Sie werden aufgefordert auszuwählen:
- Den ersten Variablenbereich (eine einzelne Spalte, z.B. A2:A7)
- Den zweiten Variablenbereich (eine oder mehrere Spalten, z.B. B2:D7)
- Die Zelle, wo Sie die Ergebnisse beginnen möchten (z.B. F2)
Das Makro berechnet dann den Korrelationskoeffizienten zwischen der ersten Variable und jeder Spalte im zweiten Bereich und zeigt die Ergebnisse horizontal beginnend von der gewählten Zelle an.
Vorteile: Automatisiert repetitive Berechnungen, spart viel Zeit bei großen Datensätzen und sorgt für Konsistenz.
Wenn Sie Probleme wie „Die beiden Datenbereiche müssen die gleiche Anzahl von Zeilen haben“ feststellen, stellen Sie sicher, dass alle ausgewählten Spalten genau die gleiche Zeilenanzahl haben und keine leeren Zeilen enthalten. Für die Fehlerbehebung überprüfen Sie, ob Makros aktiviert sind und die Bereiche korrekt ausgewählt wurden.
Bei der Arbeit mit Korrelationskoeffizienten in Excel hängt die Wahl der richtigen Methode von Ihrer Datenstruktur und Ihren Analyseanforderungen ab. Für einmalige, schnelle Berechnungen zwischen zwei Reihen sind Formeln wie KORREL oder PEARSON effizient und einfach zu verwenden. Für mehrere Variablen oder die Notwendigkeit von Zusammenfassungstabellen ist das Analyse-ToolPak sehr praktisch. Wenn Sie wiederholte Analysen auf großen Datensätzen benötigen oder benutzerdefinierte Workflows wünschen, sollten Sie die Automatisierung mit VBA in Betracht ziehen, um Zeit zu sparen und menschliche Fehler zu reduzieren.
Stellen Sie immer sicher, dass Ihre Datenbereiche ausgerichtet, sauber und frei von leeren oder nicht-numerischen Zellen sind, um Formelfehler zu vermeiden. Wenn Sie unerwartete Ergebnisse erhalten, überprüfen Sie die Auswahl und Datentypen.
Verwandte Artikel
- Berechnung der prozentualen Veränderung oder Differenz zwischen zwei Zahlen in Excel
Dieser Artikel handelt von der Berechnung der prozentualen Veränderung oder Differenz zwischen zwei Zahlen in Excel.
- Berechnen oder Zuweisen von Buchstabennoten in Excel
Das Zuweisen von Buchstabennoten für jeden Schüler basierend auf ihren Punktzahlen kann eine häufige Aufgabe für einen Lehrer sein. Zum Beispiel habe ich eine Bewertungsskala definiert, in der die Punktzahl 0-59 = F, 60-69 = D, 70-79 = C, 80-89 = B und 90-100 = A beträgt, siehe mehr.
- Berechnung der Rabattquote oder des Preises in Excel
Wenn Weihnachten kommt, muss es viele Sonderaktionen in den Einkaufszentren geben. Aber wenn unterschiedliche Artikel verschiedene Rabatte haben, wie können Sie die Rabattsätze oder Preise der verschiedenen Artikel berechnen?
- Zählen der Anzahl der Tage / Arbeitstage / Wochenenden zwischen zwei Daten in Excel
Vielleicht möchten Sie manchmal nur die Arbeitstage zwischen zwei Daten berechnen, und manchmal müssen Sie nur die Wochenendtage zwischen den zwei Daten zählen.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins