Lineare Regression in Excel berechnen
Steigt der Umsatz, wenn du mehr für Werbung ausgibst, und um wie viel? Eine lineare Regression legt die Gerade durch deine Daten, die am besten passt, und beantwortet beide Fragen mit Zahlen.
Kurz gesagt: Die Regressionsgerade y = a + b·x bekommst du mit =STEIGUNG(Y_Werte;X_Werte) für b und =ACHSENABSCHNITT(Y_Werte;X_Werte) für a. =BESTIMMTHEITSMASS(Y_Werte;X_Werte) sagt, wie gut sie passt, und =PROGNOSE.LINEAR(x;Y_Werte;X_Werte) schätzt einen neuen Wert. Die y-Werte stehen immer zuerst.
Probier es gleich aus
Sieben Monate Werbebudget und Umsatz, beides in T€. Wie viel Umsatz bringt jeder zusätzliche Tausender Werbung im Schnitt? Klick in die gelbe Zelle und schreib die Formel.
Aufgabe Um wie viel steigt der Umsatz im Schnitt, wenn das Werbebudget um 1 T€ wächst? Berechne in F2 die Steigung der Regressionsgeraden mit dem Umsatz als y und der Werbung als x.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Monat | Werbung T€ | Umsatz T€ | |||
| 2 | Jan | 2 | 41 | Steigung | ||
| 3 | Feb | 3,5 | 52 | |||
| 4 | Mrz | 1,5 | 38 | |||
| 5 | Apr | 4 | 63 | |||
| 6 | Mai | 3 | 47 | |||
| 7 | Jun | 5 | 70 | |||
| 8 | Jul | 2,5 | 49 |
Die Regressionsfunktionen im Überblick
Excel rechnet nach der Methode der kleinsten Quadrate: Die Gerade wird so gelegt, dass die Summe der quadrierten senkrechten Abstände zu den Punkten möglichst klein ist. Alle Funktionen nehmen zuerst die abhängige Größe (y, hier der Umsatz in C2:C8) und dann die erklärende Größe (x, hier die Werbung in B2:B8).
| Funktion | Bedeutung | Im Beispiel |
|---|---|---|
=STEIGUNG(C2:C8;B2:B8) | Um wie viel y steigt, wenn x um 1 steigt | 9,27 |
=ACHSENABSCHNITT(C2:C8;B2:B8) | Der Wert der Geraden bei x = 0 | 22,95 |
=BESTIMMTHEITSMASS(C2:C8;B2:B8) | R², der Anteil der Schwankung von y, den die Gerade erklärt, zwischen 0 und 1 | 0,94 |
=KORREL(B2:B8;C2:C8) | Korrelationskoeffizient r zwischen -1 und 1, die Reihenfolge ist egal | 0,97 |
=PROGNOSE.LINEAR(6;C2:C8;B2:B8) | Der Wert der Geraden für ein neues x | 78,58 |
Die Gerade lautet also Umsatz = 22,95 + 9,27 · Werbung. R² = 0,94 heißt: Die Gerade erklärt rechnerisch 94 % der Schwankung im Umsatz. Bei nur einer x-Größe ist R² genau das Quadrat von KORREL.
Den Achsenabschnitt solltest du nur deuten, wenn x = 0 in der Nähe deiner Daten liegt. Hier hat kein Monat ohne Werbung stattgefunden. Ob der Umsatz dann wirklich bei 22,95 T€ läge, geben die Daten nicht her.
Noch eine Übung: einen Wert schätzen mit PROGNOSE.LINEAR
Sieben Filialen mit Fläche und Monatsumsatz. Welcher Umsatz ist für eine neue Filiale mit der Fläche in E2 zu erwarten?
Aufgabe Eine neue Filiale soll die Fläche aus E2 bekommen. Schätze in F2 mit einer linearen Regression über die sieben bestehenden Filialen, welchen Monatsumsatz sie erwarten lässt.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Filiale | Fläche m² | Umsatz T€ | Neue Fläche | Prognose T€ | |
| 2 | Berlin | 80 | 21,5 | 120 | ||
| 3 | Hamburg | 120 | 29,8 | |||
| 4 | Bremen | 65 | 19,6 | |||
| 5 | München | 150 | 35,2 | |||
| 6 | Köln | 95 | 26,7 | |||
| 7 | Leipzig | 110 | 27,1 | |||
| 8 | Dresden | 140 | 33 |
Diese Übung gehört zu Premium.
Mit Premium öffnest du alle Übungen jeder Lektion, übst unbegrenzt mit neuen Aufgaben und kannst den Abschlusstest mit Zertifikat machen.
Premium ansehen Schon Premium? Anmelden
Die erste Übung dieser Lektion ist kostenlos.
PROGNOSE.LINEAR rechnet intern dasselbe wie =ACHSENABSCHNITT(C2:C8;B2:B8)+STEIGUNG(C2:C8;B2:B8)*E2. Liegt das neue x innerhalb der vorhandenen Werte, ist das eine Interpolation und meist brauchbar. Außerhalb davon, etwa bei der Werbung mit 6 T€ aus der Tabelle oben, extrapolierst du: Excel nimmt an, dass der Zusammenhang einfach weiterläuft. Das ist eine Annahme, keine Erkenntnis aus den Daten.
RGP: alle Kennzahlen auf einmal
=RGP(C2:C8;B2:B8) gibt Steigung und Achsenabschnitt nebeneinander zurück. In Microsoft 365 laufen die Ergebnisse automatisch in die Nachbarzelle über. In älteren Versionen markierst du vorher zwei Zellen nebeneinander und schließt die Formel mit Strg+Umschalt+Eingabe ab.
Mit =RGP(C2:C8;B2:B8;WAHR;WAHR) liefert Excel einen Block mit fünf Zeilen und zwei Spalten: in Zeile 1 Steigung und Achsenabschnitt, in Zeile 2 deren Standardfehler (hier 1,01 und 3,31), in Zeile 3 R² und den Standardfehler der Schätzung, in Zeile 4 den F-Wert und die Freiheitsgrade, in Zeile 5 die Quadratsummen. Mit mehreren x-Spalten rechnet RGP auch eine multiple Regression. In den Übungen auf dieser Seite geht es um eine x-Größe.
Einen ausführlichen Bericht mit p-Werten und Residuen erzeugt das Tool Regression aus den Analyse-Funktionen (Daten, Datenanalyse). Wie du die Analyse-Funktionen aktivierst, steht auf der Seite zur ANOVA.
Trendlinie im Diagramm
Am anschaulichsten ist die Regression als Bild. Markiere B1:C8 und füge ein Punktdiagramm (Punkt (XY)) ein, wie im Kapitel Diagramme erstellen beschrieben. Dann:
- Diagramm anklicken und über das Plus-Symbol oben rechts (Diagrammelemente) Trendlinie wählen, oder auf der Registerkarte Diagrammentwurf über Diagrammelement hinzufügen.
- Bei Weitere Optionen die Art Linear lassen.
- Häkchen bei Formel im Diagramm anzeigen und Bestimmtheitsmaß im Diagramm darstellen setzen.
Die angezeigte Gleichung hat dieselbe Steigung und denselben Achsenabschnitt wie STEIGUNG und ACHSENABSCHNITT. Der Unterschied: Die Werte im Diagramm kannst du nicht weiterverwenden, die Formeln in Zellen schon.
Korrelation ist keine Kausalität
Eine hohe Korrelation zeigt, dass zwei Größen gemeinsam steigen und fallen. Sie zeigt nicht, dass die eine die andere verursacht. Vielleicht wurde in umsatzstarken Monaten wie dem Weihnachtsgeschäft einfach mehr geworben, dann treibt die Saison beides. Mit sieben Datenpunkten ist zudem jede Gerade unsicher, wie die Standardfehler aus RGP zeigen. Ob Werbung wirkt, klärt am ehesten ein Versuch, bei dem du das Budget gezielt veränderst und den Rest gleich lässt.
Typische Fehler
x und y vertauscht
STEIGUNG(B2:B8;C2:C8) beantwortet die umgekehrte Frage und liefert eine ganz andere Zahl. Erst y, dann x. Nur bei KORREL ist die Reihenfolge egal.
Unterschiedlich lange Bereiche
Haben die Bereiche nicht gleich viele Zellen, gibt STEIGUNG #NV zurück.
Hohes R² als Beweis
Auch ein R² von 0,94 sagt nichts über Ursache und Wirkung und nichts darüber, ob die Gerade außerhalb der Daten noch gilt.
Regressionsfunktionen auf Englisch
| Deutsch | Englisch |
|---|---|
=STEIGUNG(C2:C8;B2:B8) | =SLOPE(C2:C8,B2:B8) |
| ACHSENABSCHNITT, BESTIMMTHEITSMASS | INTERCEPT, RSQ |
| KORREL, PEARSON | CORREL, PEARSON |
| PROGNOSE.LINEAR | FORECAST.LINEAR |
| RGP | LINEST |
Im englischen Excel trennst du die Argumente mit Komma, WAHR heißt TRUE.
In welchen Excel-Versionen gibt es diese Funktionen?
STEIGUNG, ACHSENABSCHNITT, BESTIMMTHEITSMASS, KORREL und RGP gibt es in allen aktuellen Versionen. PROGNOSE.LINEAR kam laut Microsoft mit Excel 2016 und ist für Microsoft 365, Excel 2024, 2021, 2019 und 2016 dokumentiert, auch für den Mac. Die ältere Fassung ohne .LINEAR im Namen rechnet gleich und bleibt zur Kompatibilität erhalten.
Häufige Fragen
Wie mache ich eine Regressionsanalyse in Excel?
Für die Gerade reichen STEIGUNG, ACHSENABSCHNITT und BESTIMMTHEITSMASS. Für einen vollständigen Bericht mit p-Werten aktivierst du die Analyse-Funktionen und wählst unter Daten, Datenanalyse das Tool Regression.
Was ist ein guter Wert für R²?
Das hängt vom Fachgebiet ab. In technischen Messungen sind Werte über 0,9 üblich, bei Daten über menschliches Verhalten ist 0,3 oft schon viel. Wichtiger als eine Schwelle ist, ob der Zusammenhang inhaltlich Sinn ergibt.
Wie interpoliere ich in Excel zwischen zwei Werten?
Linear geht das mit PROGNOSE.LINEAR, wenn du nur die beiden Nachbarpunkte als Y_Werte und X_Werte angibst. Mit allen Punkten bekommst du dagegen den Wert der Regressionsgeraden.
Was ist der Unterschied zwischen KORREL und BESTIMMTHEITSMASS?
KORREL liefert r mit Vorzeichen, also auch die Richtung des Zusammenhangs. BESTIMMTHEITSMASS liefert r², immer zwischen 0 und 1.