Beispiele zur linearen Regression mit Excel-Formeln

Dieser Artikel befasst sich mit verschiedenen Beispielen zur Anwendung von Regressionsformeln in Excel. Es werden einfache und multiple lineare Regressionen sowie die Verwendung von Statistiken wie r², F und t zur Bewertung der Modellergebnisse erläutert. Die Beispiele sind darauf ausgelegt, Nutzern zu helfen, die Genauigkeit und Zuverlässigkeit ihrer Datenanalysen in Excel zu verbessern. Wir werden uns mit der Anwendung der RGP-Funktion und anderer statistischer Funktionen befassen, um aussagekräftige Erkenntnisse aus Datensätzen zu gewinnen.

Beispiel 1: Steigung und y-Achsenabschnitt

Dieses Beispiel zeigt, wie die Steigung und der y-Achsenabschnitt einer linearen Beziehung mithilfe der RGP-Funktion in Excel bestimmt werden.

Kopieren Sie die Beispieldaten in der folgenden Tabelle und fügen Sie sie in Zelle A1 eines neuen Excel-Arbeitsblatts ein.

Um die Ergebnisse der Formeln anzuzeigen, markieren Sie die Zellen, drücken Sie F2 und dann die EINGABETASTE. Im Bedarfsfall können Sie die Breite der Spalten anpassen, damit alle Daten angezeigt werden.

Beispiel 2: Einfache lineare Regression zur Umsatzprognose

Dieses Beispiel demonstriert die einfache lineare Regression, um zukünftige Umsätze auf Basis historischer Daten vorherzusagen.

Kopieren Sie die Beispieldaten in der folgenden Tabelle und fügen Sie sie in Zelle A1 eines neuen Excel-Arbeitsblatts ein.

Die Formel berechnet den geschätzten Umsatz für den neunten Monat auf Grundlage der Umsätze in den Monaten 1 bis 6. Um die Ergebnisse der Formeln anzuzeigen, markieren Sie sie, drücken Sie F2 und dann die EINGABETASTE.

Beispiel 3: Multiple lineare Regression für Immobilienbewertung

Dieses Beispiel veranschaulicht die multiple lineare Regression zur Schätzung des Wertes von Bürogebäuden basierend auf mehreren Faktoren. Dies ist ein gutes Beispiel dafür, wie vielfältig die Anwendungen von Formeln in Excel sind.

Kopieren Sie die Beispieldaten in der folgenden Tabelle und fügen Sie sie in Zelle A1 eines neuen Excel-Arbeitsblatts ein.

Um die Ergebnisse der Formeln anzuzeigen, markieren Sie sie, drücken Sie F2 und dann die EINGABETASTE.

Beispiel 4: Verwenden der F- und r²-Statistik zur Modellbewertung

Im vorherigen Beispiel beträgt der Bestimmungskoeffizient oder r² 0,99675 (siehe Zelle A17 in der Ausgabe für LINEST), was auf eine starke Beziehung zwischen den unabhängigen Variablen und dem Verkaufspreis hindeutet. Sie können die F-Statistik verwenden, um zu bestimmen, ob diese Ergebnisse mit einem so hohen r²-Wert zufällig aufgetreten sind.

Die F- und df-Werte in der Ausgabe der LINEST-Funktion können verwendet werden, um die Wahrscheinlichkeit zu bewerten, dass zufällig ein höherer F-Wert auftritt. Die FDIST-Funktion mit der Syntax FDIST(F,v1,v2) gibt die Wahrscheinlichkeit zurück, dass ein größerer F-Wert zufällig auftritt. In diesem Beispiel: df = 6 (Zelle B18) und F = 459,753674 (Zelle A18). Angenommen, ein Alpha-Wert von 0,05, v1 = 11 – 6 – 1 = 4 und v2 = 6, ist die kritische Ebene von F 4,53. Da F = 459,753674 viel höher als 4,53 ist, ist es äußerst unwahrscheinlich, dass ein F-Wert dieser Höhe zufällig aufgetreten ist. Sie können entweder durch Ermitteln der kritischen Ebene von F in einer Tabelle oder mithilfe der Funktion FDIST schlussfolgern, dass die Regressionsgleichung nützlich ist, um den bewerteten Wert von Bürogebäuden in diesem Bereich vorherzusagen.

Beispiel 5: Berechnen der t-Statistik zur Variablenauswahl

Mithilfe einer anderen Hypothese kann festgestellt werden, ob die einzelnen Steigungskoeffizienten geeignet sind, den Schätzwert eines der in Beispiel 3 aufgeführten Bürogebäude zu berechnen. Um zum Beispiel den Koeffizienten für das Gebäudealter bezüglich der statistischen Wahrscheinlichkeit (Sicherheit) zu prüfen, dividieren Sie –234,24 (Steigungskoeffizient für das Alter) durch 13,268 (der in Zelle 15 stehende Standardfehler des Alterskoeffizienten). Daraus ergibt sich der folgende t-Wert:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

Wenn Sie die entsprechende Tabelle eines Statistikhandbuchs zu Rate ziehen, werden Sie feststellen, dass der kritische t-Wert bei einem zweiseitigen Test mit sechs Freiheitsgraden und Alpha = 0,05 den Wert 2,447 hat. Dieser kritische Wert kann auch mithilfe der TINV-Funktion in Excel ermittelt werden. TINV(0,05.6) = 2,447. Da der Absolutwert von t (17,7) größer als 2,447 ist, ist Alter eine zuverlässige Variable, um den Schätzwert eines Bürogebäudes zu ermitteln.

In der folgenden Tabelle sind die Absolutwerte der vier berechneten t-Werte dargestellt:

Alle Werte haben einen Absolutwert, der größer als 2,447 ist. Daher sind alle Variablen, die in der Regressionsgleichung verwendet werden, geeignet, den Schätzwert eines zum fraglichen Büroviertel gehörenden Bürogebäudes zu bestimmen. Diese Beispiele verdeutlichen die Leistungsfähigkeit von Excel für fortgeschrittene statistische Analysen.