Das Tutorial erklärt die Grundlagen der Regressionsanalyse und zeigt verschiedene Möglichkeiten zur linearen Regression in Excel.

Stellen Sie sich Folgendes vor: Sie erhalten eine ganze Menge verschiedener Daten und werden gebeten, die Verkaufszahlen für Ihr Unternehmen im nächsten Jahr vorherzusagen. Sie haben Dutzende, vielleicht sogar Hunderte von Faktoren entdeckt, die möglicherweise die Zahlen beeinflussen können. Aber woher wissen Sie, welche wirklich wichtig sind? Führen Sie die Regressionsanalyse in Excel aus. Es wird Ihnen eine Antwort auf diese und viele weitere Fragen geben: Welche Faktoren sind wichtig und welche können ignoriert werden? Wie eng hängen diese Faktoren zusammen? Und wie sicher können Sie über die Vorhersagen sein?

• Regressionsanalyse in Excel
• Lineare Regression in Excel mit Analyse-ToolPak
• Zeichnen eines linearen Regressionsgraphen
• Regressionsanalyse in Excel mit Formeln

Regressionsanalyse in Excel – die Grundlagen

In der statistischen Modellierung wird die Regressionsanalyse verwendet, um die Beziehungen zwischen zwei oder mehr Variablen zu schätzen:

Die abhängige Variable (auch bekannt als Kriteriumsvariable) ist der Hauptfaktor, den Sie verstehen und vorhersagen möchten.

Unabhängige Variablen (auch erklärende Variablen oder Prädiktoren genannt) sind die Faktoren, die die abhängige Variable beeinflussen können.Die Regressionsanalyse hilft Ihnen zu verstehen, wie sich die abhängige Variable ändert, wenn eine der unabhängigen Variablen variiert, und ermöglicht es Ihnen, mathematisch zu bestimmen, welche dieser Variablen wirklich einen Einfluss hat.Technisch gesehen basiert ein Regressionsanalysemodell auf der Summe der Quadrate, was eine mathematische Methode ist, um die Streuung von Datenpunkten zu ermitteln. Das Ziel eines Modells ist es, die kleinstmögliche Summe von Quadraten zu erhalten und eine Linie zu zeichnen, die den Daten am nächsten kommt.

In der Statistik wird zwischen einer einfachen und einer multiplen linearen Regression unterschieden. Einfache lineare Regression modelliert die Beziehung zwischen einer abhängigen Variablen und einer unabhängigen Variablen unter Verwendung einer linearen Funktion. Wenn Sie zwei oder mehr erklärende Variablen verwenden, um die abhängige Variable vorherzusagen, haben Sie es mit einer multiplen linearen Regression zu tun. Wenn die abhängige Variable als nichtlineare Funktion modelliert wird, weil die Datenbeziehungen keiner geraden Linie folgen, verwenden Sie stattdessen die nichtlineare Regression. Der Fokus dieses Tutorials liegt auf einer einfachen linearen Regression.Nehmen wir als Beispiel die Verkaufszahlen für Regenschirme der letzten 24 Monate und ermitteln Sie den durchschnittlichen monatlichen Niederschlag für denselben Zeitraum. Zeichnen Sie diese Informationen auf einem Diagramm auf, und die Regressionsgerade zeigt die Beziehung zwischen der unabhängigen Variablen (Niederschlag) und der abhängigen Variablen (Regenschirmverkäufe):

Lineare Regressionsgleichung

Mathematisch wird eine lineare Regression durch diese Gleichung definiert:

Wobei:

• x eine unabhängige Variable ist.
• y ist eine abhängige Variable.
• a ist der Y-Achsenabschnitt, der der erwartete Mittelwert von y ist, wenn alle x Variablen gleich 0 sind. In einem Regressionsdiagramm ist dies der Punkt, an dem die Linie die Y-Achse kreuzt.
• b ist die Steigung einer Regressionsgeraden, die die Änderungsrate für y ist, wenn sich x ändert.
• ε ist der Zufallsfehlerterm, der die Differenz zwischen dem tatsächlichen Wert einer abhängigen Variablen und ihrem vorhergesagten Wert darstellt.

Die lineare Regressionsgleichung hat immer einen Fehlerterm, da Prädiktoren im wirklichen Leben niemals vollkommen präzise sind. Einige Programme, einschließlich Excel, führen die Berechnung des Fehlerterms jedoch hinter den Kulissen durch. In Excel führen Sie also eine lineare Regression mit der Methode der kleinsten Quadrate durch und suchen nach den Koeffizienten a und b, sodass:

In unserem Beispiel hat die lineare Regressionsgleichung die folgende Form:

Umbrellas sold = b * rainfall + a

Es gibt eine Handvoll verschiedener Möglichkeiten, a und b zu finden. Die drei Hauptmethoden zur Durchführung einer linearen Regressionsanalyse in Excel sind:

• Regressionswerkzeug im Analysis ToolPak enthalten
• Streudiagramm mit einer Trendlinie
• Lineare Regressionsformel

Nachfolgend finden Sie detaillierte Anweisungen zur Verwendung der einzelnen Methoden.

Lineare Regression in Excel mit Analysis ToolPak durchführen

In diesem Beispiel wird gezeigt, wie die Regression in Excel mit einem speziellen Tool ausgeführt wird, das im Analysis ToolPak-Add-In enthalten ist.

Add-In Analysis ToolPak aktivieren

Analysis ToolPak ist in allen Versionen von Excel 2019 bis 2003 verfügbar, aber standardmäßig nicht aktiviert. Sie müssen es also manuell einschalten. So geht’s:

1. Klicken Sie in Ihrem Excel auf Datei > Optionen.
2. Wählen Sie im Dialogfeld Excel-Optionen in der linken Seitenleiste Add-Ins aus, stellen Sie sicher, dass Excel-Add-Ins im Feld Verwalten ausgewählt ist, und klicken Sie auf Los.
   
3. Kreuzen Sie im Dialogfeld Add-Ins Analysis Toolpak an und klicken Sie auf OK:
   

Dadurch werden die Datenanalysetools zur Registerkarte Daten Ihres Excel-Menübands hinzugefügt.

Regressionsanalyse ausführen

In diesem Beispiel führen wir eine einfache lineare Regression in Excel durch. Was wir haben, ist eine Liste der durchschnittlichen monatlichen Niederschläge der letzten 24 Monate in Spalte B, die unsere unabhängige Variable (Prädiktor) ist, und die Anzahl der verkauften Regenschirme in Spalte C, die die abhängige Variable ist. Natürlich gibt es viele andere Faktoren, die den Umsatz beeinflussen können, aber im Moment konzentrieren wir uns nur auf diese beiden Variablen:

Führen Sie bei aktiviertem Analysis Toolpak die folgenden Schritte aus, um eine Regressionsanalyse in Excel durchzuführen:

1. Klicken Sie auf der Registerkarte Daten in der Gruppe Analyse auf die Schaltfläche Datenanalyse.
   
2. Wählen Sie Regression und klicken Sie auf OK.
   
3. Konfigurieren Sie im Dialogfeld Regression die folgenden Einstellungen:
   • Wählen Sie den Eingabe-Y-Bereich aus, der Ihre abhängige Variable ist. In unserem Fall handelt es sich um Regenschirmverkäufe (C1: C25).
   • Wählen Sie den Eingabe-X-Bereich, d. h. Ihre unabhängige Variable. In diesem Beispiel ist es der durchschnittliche monatliche Niederschlag (B1: B25).

   Wenn Sie ein multiples Regressionsmodell erstellen, wählen Sie zwei oder mehr benachbarte Spalten mit unterschiedlichen unabhängigen Variablen aus.

   • Aktivieren Sie das Kontrollkästchen Beschriftungen, wenn sich oben in Ihren X- und Y-Bereichen Überschriften befinden.
   • Wählen Sie Ihre bevorzugte Ausgabeoption, in unserem Fall ein neues Arbeitsblatt.
   • Aktivieren Sie optional das Kontrollkästchen Residuen, um die Differenz zwischen den vorhergesagten und den tatsächlichen Werten zu ermitteln.
     
4. Klicken Sie auf OK und beobachten Sie die von Excel erstellte Regressionsanalyse-Ausgabe.

Ausgabe der Regressionsanalyse interpretieren

Wie Sie gerade gesehen haben, ist die Ausführung der Regression in Excel einfach, da alle Berechnungen automatisch durchgeführt werden. Die Interpretation der Ergebnisse ist etwas schwieriger, da Sie wissen müssen, was hinter jeder Zahl steckt. Nachfolgend finden Sie eine Aufschlüsselung der 4 Hauptteile der Regressionsanalyse.

Ausgabe der Regressionsanalyse: Ausgabe der Zusammenfassung

In diesem Teil erfahren Sie, wie gut die berechnete lineare Regressionsgleichung zu Ihren Quelldaten passt.

Hier ist, was jede Information bedeutet:

Multiple R. Es ist der Korrelationskoeffizient, der die Stärke einer linearen Beziehung zwischen zwei Variablen misst. Der Korrelationskoeffizient kann ein beliebiger Wert zwischen -1 und 1 sein, und sein Absolutwert gibt die Beziehungsstärke an. Je größer der Absolutwert, desto stärker die Beziehung:

• 1 bedeutet eine starke positive Beziehung
• -1 bedeutet eine starke negative Beziehung
• 0 bedeutet überhaupt keine Beziehung

R Quadrat. Es ist der Bestimmungskoeffizient, der als Indikator für die Passgenauigkeit verwendet wird. Es zeigt, wie viele Punkte auf die Regressionsgerade fallen. Der R2-Wert wird aus der Gesamtsumme der Quadrate berechnet, genauer gesagt ist es die Summe der quadratischen Abweichungen der Originaldaten vom Mittelwert.

In unserem Beispiel ist R2 0,91 (gerundet auf 2 Ziffern), was ziemlich gut ist. Dies bedeutet, dass 91% unserer Werte dem Regressionsanalysemodell entsprechen. Mit anderen Worten, 91% der abhängigen Variablen (y-Werte) werden durch die unabhängigen Variablen (x-Werte) erklärt. Im Allgemeinen wird ein R-Quadrat von 95% oder mehr als gute Passform angesehen.

Angepasstes R-Quadrat. Es ist das R-Quadrat, das an die Anzahl der unabhängigen Variablen im Modell angepasst ist. Sie möchten diesen Wert anstelle von R square für die multiple Regressionsanalyse verwenden.

Standardfehler. Es ist ein weiteres Maß für die Anpassungsgüte, das die Genauigkeit Ihrer Regressionsanalyse angibt – je kleiner die Zahl, desto sicherer können Sie Ihre Regressionsgleichung sein. Während R2 den Prozentsatz der Varianz abhängiger Variablen darstellt, der durch das Modell erklärt wird, ist der Standardfehler ein absolutes Maß, das den durchschnittlichen Abstand der Datenpunkte von der Regressionsgeraden angibt.

Beobachtungen. Es ist einfach die Anzahl der Beobachtungen in Ihrem Modell.

Ausgabe der Regressionsanalyse: ANOVA

Der zweite Teil der Ausgabe ist die Varianzanalyse (ANOVA):

Grundsätzlich wird die Summe der Quadrate in einzelne Komponenten aufgeteilt, die Informationen über die Variabilitätsgrade innerhalb Ihres Regressionsmodells liefern:

• df ist die Anzahl der Freiheitsgrade, die den Varianzquellen zugeordnet sind.
• SS ist die Summe der Quadrate. Je kleiner die Rest-SS im Vergleich zur Gesamt-SS ist, desto besser passt Ihr Modell zu den Daten.
• MS ist das mittlere Quadrat.
• F ist die F-Statistik oder der F-Test für die Nullhypothese. Es wird verwendet, um die Gesamtbedeutung des Modells zu testen.
• Signifikanz F ist der P-Wert von F.

Der ANOVA-Teil wird selten für eine einfache lineare Regressionsanalyse in Excel verwendet, aber Sie sollten sich unbedingt die letzte Komponente genau ansehen. Der Signifikanz-F-Wert gibt eine Vorstellung davon, wie zuverlässig (statistisch signifikant) Ihre Ergebnisse sind. Wenn Bedeutung F ist weniger als 0,05 (5%), ihr modell ist OK. Wenn es größer als 0,05 ist, sollten Sie wahrscheinlich eine andere unabhängige Variable wählen.

Ausgabe der Regressionsanalyse: Koeffizienten

Dieser Abschnitt enthält spezifische Informationen zu den Komponenten Ihrer Analyse:

Die nützlichste Komponente in diesem Abschnitt sind Koeffizienten. Es ermöglicht Ihnen, eine lineare Regressionsgleichung in Excel zu erstellen:

Für unseren Datensatz, wobei y die Anzahl der verkauften Regenschirme und x die durchschnittliche monatliche Niederschlagsmenge ist, lautet unsere lineare Regressionsformel wie folgt:

Y = Rainfall Coefficient * x + Intercept

Ausgestattet mit a- und b-Werten, die auf drei Dezimalstellen gerundet sind, wird:

Y=0.45*x-19.074

Bei einem durchschnittlichen monatlichen Niederschlag von 82 mm beträgt der Regenschirmumsatz beispielsweise ungefähr 17,8:

0.45*82-19.074=17.8

Auf ähnliche Weise können Sie herausfinden, wie viele Regenschirme mit einem anderen von Ihnen angegebenen monatlichen Niederschlag (x Variable) verkauft werden.

Ausgabe der Regressionsanalyse: Residuen

Wenn Sie die geschätzte und tatsächliche Anzahl der verkauften Regenschirme vergleichen, die dem monatlichen Niederschlag von 82 mm entsprechen, werden Sie feststellen, dass diese Zahlen geringfügig abweichen:

• Geschätzt: 17.8 (oben berechnet)
• Aktuell: 15 (Zeile 2 der Quelldaten)

Warum ist der Unterschied? Weil unabhängige Variablen niemals perfekte Prädiktoren der abhängigen Variablen sind. Und die Residuen können Ihnen helfen zu verstehen, wie weit die tatsächlichen Werte von den vorhergesagten Werten entfernt sind:

So erstellen Sie ein lineares Regressionsdiagramm in Excel

Wenn Sie die Beziehung zwischen den beiden Variablen schnell visualisieren müssen, zeichnen Sie ein lineares Regressionsdiagramm. Das ist ganz einfach! So geht’s:

1. Wählen Sie die beiden Spalten mit Ihren Daten aus, einschließlich der Kopfzeilen.
2. Klicken Sie auf der Registerkarte Einfügen in der Gruppe Chats auf das Symbol Streudiagramm und wählen Sie das Miniaturbild Streudiagramm (das erste) aus:
   

   Dadurch wird ein Streudiagramm in Ihr Arbeitsblatt eingefügt, das diesem ähnelt:
   

3. Jetzt müssen wir die Regressionslinie der kleinsten Quadrate zeichnen. Klicken Sie dazu mit der rechten Maustaste auf einen beliebigen Punkt und wählen Sie Trendlinie hinzufügen … aus dem Kontextmenü.
   
4. Wählen Sie im rechten Bereich die lineare Trendlinienform aus und aktivieren Sie optional die Option Gleichung im Diagramm anzeigen, um Ihre Regressionsformel zu erhalten:
   

   Wie Sie vielleicht feststellen, entspricht die von Excel für uns erstellte Regressionsgleichung der linearen Regressionsformel, die wir basierend auf der Ausgabe der Koeffizienten erstellt haben.

5. Wechseln Sie zur Registerkarte Fill & Line und passen Sie die Linie nach Ihren Wünschen an. Sie können beispielsweise eine andere Linienfarbe wählen und eine durchgezogene Linie anstelle einer gestrichelten Linie verwenden (wählen Sie Durchgezogene Linie im Feld Bindestrich-Typ):
   

Zu diesem Zeitpunkt sieht Ihr Diagramm bereits wie ein anständiges Regressionsdiagramm aus:

Dennoch möchten Sie vielleicht noch ein paar Verbesserungen vornehmen:

• Ziehen Sie die Gleichung an eine beliebige Stelle.
• Achsentitel hinzufügen (Schaltfläche für Diagrammelemente > Achsentitel).
• Wenn Ihre Datenpunkte wie in diesem Beispiel in der Mitte der horizontalen und / oder vertikalen Achse beginnen, möchten Sie möglicherweise den übermäßigen Leerraum entfernen. Der folgende Tipp erklärt, wie Sie dies tun: Skalieren Sie die Diagrammachsen, um den Leerraum zu reduzieren.

  Und so sieht unser verbessertes Regressionsdiagramm aus:
  

  Wichtiger Hinweis! Im Regressionsdiagramm sollte sich die unabhängige Variable immer auf der X-Achse und die abhängige Variable auf der Y-Achse befinden. Wenn Ihr Diagramm in umgekehrter Reihenfolge gezeichnet wird, tauschen Sie die Spalten in Ihrem Arbeitsblatt aus und zeichnen Sie das Diagramm erneut. Wenn Sie die Quelldaten nicht neu anordnen dürfen, können Sie die X- und Y-Achse direkt in einem Diagramm wechseln.

Regression in Excel mithilfe von Formeln

Microsoft Excel verfügt über einige statistische Funktionen, mit denen Sie lineare Regressionsanalysen durchführen können, z. B. LINEST, SLOPE, INTERCPET und CORREL.

Die Funktion LINEST verwendet die Regressionsmethode der kleinsten Quadrate, um eine gerade Linie zu berechnen, die die Beziehung zwischen Ihren Variablen am besten erklärt, und gibt ein Array zurück, das diese Linie beschreibt. Die ausführliche Erklärung der Syntax der Funktion finden Sie in diesem Tutorial. Lassen Sie uns zunächst eine Formel für unseren Beispieldatensatz erstellen:

=LINEST(C2:C25, B2:B25)

Da die LINEST-Funktion ein Array von Werten zurückgibt, müssen Sie es als Array-Formel eingeben. Wählen Sie zwei benachbarte Zellen in derselben Zeile, E2:F2 Geben Sie in unserem Fall die Formel ein und drücken Sie Strg + Umschalt + Eingabetaste, um sie abzuschließen.

Die Formel gibt den b-Koeffizienten (E1) und die a-Konstante (F1) für die bereits bekannte lineare Regressionsgleichung zurück:

y = bx + a

Wenn Sie die Verwendung von Array-Formeln in Ihren Arbeitsblättern vermeiden, können Sie a und b einzeln mit regulären Formeln berechnen:

Holen Sie sich den Y-Schnittpunkt (a):

=INTERCEPT(C2:C25, B2:B25)

Holen Sie sich die Steigung (b):

=SLOPE(C2:C25, B2:B25)

Zusätzlich finden Sie den Korrelationskoeffizienten (Multiples R in der Ausgabe der Zusammenfassung der Regressionsanalyse), der angibt, wie stark die beiden Variablen miteinander verwandt sind:

=CORREL(B2:B25,C2:C25)

Der folgende Screenshot zeigt alle diese Excel-Regressionsformeln in Aktion:

So machen Sie die lineare Regression in Excel. Beachten Sie jedoch, dass Microsoft Excel kein statistisches Programm ist. Wenn Sie eine Regressionsanalyse auf professioneller Ebene durchführen müssen, sollten Sie gezielte Software wie XLSTAT, RegressIt usw. verwenden.

Verfügbare Downloads:

Um einen genaueren Blick auf unsere linearen Regressionsformeln und andere in diesem Tutorial diskutierte Techniken zu werfen, können Sie gerne unsere Beispiel-Regressionsanalyse in Excel-Arbeitsmappe herunterladen.

• Verwendung des Solvers in Excel mit Beispielen
• Berechnung des Zinseszinses in Excel
• Berechnung der CAGR (Compound Annual Growth Rate) in Excel