Was ist eine lineare Regression?
Die lineare Regression ist im Grunde eine statistische Modellierungstechnik, mit der die Beziehung zwischen einer abhängigen Variablen und einer oder mehreren unabhängigen Variablen dargestellt wird. Es ist eine der häufigsten Arten der prädiktiven Analyse. Diese Art der Verteilung bildet sich in einer Linie, daher wird dies als lineare Regression bezeichnet. In diesem Artikel werden die Beispiele der linearen Regressionsanalyse in Excel verwendet.
Um zuerst eine lineare Regressionsanalyse durchzuführen, müssen wir Excel-Add-Ins hinzufügen, indem wir die folgenden Schritte ausführen.
Klicken Sie auf Datei - Optionen (Dadurch wird das Excel-Options-Popup für Sie geöffnet).
Klicken Sie auf Add-Ins - Wählen Sie Excel-Add-Ins unter Dropdown in Excel verwalten aus und klicken Sie dann auf Los.
Dadurch wird das Popup-Fenster "Add-Ins" geöffnet. Wählen Sie Analysis ToolPak und klicken Sie auf OK.
Das Datenanalyse-Add-In wird auf der Registerkarte Einfügen angezeigt.
Lassen Sie uns anhand der folgenden Beispiele der linearen Regressionsanalyse in Excel verstehen.
Beispiele für lineare Regressionsanalysen
Beispiel 1
Angenommen, wir haben monatliche Verkäufe und Ausgaben für Marketing für das letzte Jahr, und jetzt müssen wir zukünftige Verkäufe auf der Grundlage der Ausgaben für Vertrieb und Marketing des letzten Jahres vorhersagen.
| Monat | Werbung | Der Umsatz |
| Jan. | 40937 | 502729 |
| Feb. | 42376 | 507553 |
| Beschädigen | 43355 | 516885 |
| Apr. | 44126 | 528347 |
| Kann | 45060 | 537298 |
| Jun | 49546 | 544066 |
| Jul | 56105 | 553664 |
| Aug. | 59322 | 563201 |
| Sep. | 59877 | 568657 |
| Okt. | 60481 | 569384 |
| Nov. | 62356 | 573764 |
| Dez. | 63246 | 582746 |
Klicken Sie auf der Registerkarte Daten auf Datenanalyse. Daraufhin wird das Datenanalyse-Popup für Sie geöffnet.
Wählen Sie nun Regression aus der Liste und klicken Sie auf OK.
Das Regressions-Popup wird geöffnet.
Wählen Sie im Feld Y-Achse den Umsatzbereich $ C $ 1: $ C $ 13 aus, da dies die abhängige Variable ist, und $ B $ 1: $ B $ 14 in der X-Achse, da die ausgegebene Werbung die unabhängige Variable ist.
Aktivieren Sie das Kontrollkästchen Beschriftungen, wenn Sie in den Daten Kopfzeilen ausgewählt haben. Andernfalls wird der Fehler angezeigt.
Wählen Sie Ausgabebereich, wenn Sie den Wert für den bestimmten Bereich im Arbeitsblatt erhalten möchten. Andernfalls wählen Sie Neue Arbeitsblattlage: Dadurch wird ein neues Arbeitsblatt hinzugefügt und Sie erhalten das Ergebnis.
Aktivieren Sie dann das Kontrollkästchen Residuen und klicken Sie auf OK.
Dadurch werden Arbeitsblätter hinzugefügt und Sie erhalten das folgende Ergebnis.
Lassen Sie uns die Ausgabe verstehen.
Zusammenfassende Ausgabe
Multiple R: Dies repräsentiert den Korrelationskoeffizienten. Der Wert 1 zeigt eine positive Beziehung und der Wert 0 zeigt keine Beziehung.
R-Quadrat: R-Quadrat repräsentiert den Bestimmungskoeffizienten. Dies zeigt Ihnen, wie viel Prozent der Punkte auf die Regressionslinie fallen. 0,49 bedeutet, dass 49% der Werte zum Modell passen
Angepasstes R-Quadrat : Dies ist ein angepasstes R-Quadrat, das erforderlich ist, wenn Sie mehr als eine X-Variable haben.
Standardfehler: Dies ist eine Schätzung der Standardfehlerabweichung. Dies ist die Genauigkeit, mit der der Regressionskoeffizient gemessen wird.
Beobachtungen: Dies ist die Anzahl der Beobachtungen, die Sie in einer Stichprobe gemacht haben.
ANOVA - Df: Freiheitsgrade
SS: Summe der Quadrate.
MS: Wir haben zwei MS
- Regression MS ist Regression SS / Regression Df.
- Rest-MS ist der mittlere quadratische Fehler (Rest-SS / Rest-Df).
F: F-Test für die Nullhypothese.
Signifikanz F: Mit Signifikanz verbundene P-Werte
Koeffizient: Der Koeffizient gibt Ihnen die Schätzung der kleinsten Quadrate.
T-Statistik: T-Statistik für die Nullhypothese gegenüber der Alternativhypothese.
P-Wert: Dies ist der p-Wert für den Hypothesentest.
Untere 95% und obere 95%: Dies sind die untere Grenze und die obere Grenze für das Konfidenzintervall
Residuenausgabe: Wir haben 12 Beobachtungen basierend auf den Daten. 2 nd Spalte stellt Voraussichtliche Umsatz- und 3 rd Spalte Residuen. Residuen sind im Grunde die Differenz der prognostizierten Verkäufe von den tatsächlichen.
Beispiel 2
Wählen Sie die Spalte für den prognostizierten Vertrieb und das Marketing aus
Wechseln Sie zur Diagrammgruppe unter der Registerkarte Einfügen. Wählen Sie das Streudiagrammsymbol
Dadurch wird das Streudiagramm in Excel eingefügt. Siehe Bild unten
Klicken Sie mit der rechten Maustaste auf einen beliebigen Punkt und wählen Sie dann Trendlinie in Excel hinzufügen. Dadurch wird Ihrem Diagramm eine Trendlinie hinzugefügt.
- Sie können die Trendlinie formatieren, indem Sie mit der rechten Maustaste auf eine beliebige Stelle auf der Trendlinie klicken und dann Trendlinie formatieren auswählen.
- Sie können das Diagramm weiter verbessern. dh Formatieren der Trendlinie, Farbe und Ändern des Titels usw.
- Sie können die Formel auch im Diagramm anzeigen, indem Sie die Anzeigeformel im Diagramm aktivieren und den R-Quadrat-Wert im Diagramm anzeigen.
Einige weitere Beispiele für die lineare Regressionsanalyse:
- Die Vorhersage des verkauften Regenschirms basierend auf dem Regen erfolgte in der Region.
- Vorhersage des verkauften Wechselstroms basierend auf der Temperatur im Sommer.
- Während der Prüfungssaison stiegen die Verkäufe von Stationary im Wesentlichen und die Verkäufe von Prüfungsleitfäden.
- Umsatzprognose, wenn Werbung auf der Grundlage der High TRP-Serie, in der eine Werbung geschaltet wird, der Beliebtheit des Markenbotschafters und der Fußgänger am Ort des Haltens, an dem eine Werbung veröffentlicht wird, erfolgt ist.
- Verkauf eines Hauses basierend auf Ort, Fläche und Preis.
Beispiel 3
Angenommen, wir haben neun Schüler mit ihrem IQ-Level und der Anzahl, die sie beim Test erzielt haben.
| Schüler | Prüfungsergebnis | IQ |
| RAM | 100 | 145 |
| Shyam | 97 | 140 |
| Kul | 93 | 130 |
| Kappu | 91 | 125 |
| Raju | 89 | 115 |
| Vishal | 86 | 110 |
| Vivek | 82 | 100 |
| Vinay | 78 | 95 |
| Kumar | 75 | 90 |
Schritt 1: Ermitteln Sie zunächst die abhängigen und unabhängigen Variablen. Hier ist die Testbewertung die abhängige Variable, und der IQ ist die unabhängige Variable, da die Testbewertung variiert, wenn sich der IQ ändert.
Schritt 2: Gehen Sie zur Registerkarte Daten - Klicken Sie auf Datenanalyse - Wählen Sie Regression aus - klicken Sie auf OK.
Dadurch wird das Regressionsfenster für Sie geöffnet.
Schritt 3. Eingabe-Test-Score-Bereich im Eingabe-Y-Bereichsfeld und IQ im Eingabe-X-Bereichsfeld. (Aktivieren Sie die Option Beschriftungen, wenn sich in Ihrem Datenbereich Überschriften befinden. Wählen Sie die Ausgabeoptionen aus und überprüfen Sie die gewünschten Restwerte. Klicken Sie auf OK.
Sie erhalten die im folgenden Bild gezeigte Zusammenfassungsausgabe.
Schritt 4: Analysieren der Regression anhand der Zusammenfassungsausgabe
Zusammenfassende Ausgabe
Multiple R: Hier beträgt der Korrelationskoeffizient 0,99, was sehr nahe an 1 liegt, was bedeutet, dass die lineare Beziehung sehr positiv ist.
R-Quadrat: Der R-Quadrat-Wert beträgt 0,983, was bedeutet, dass 98,3% der Werte zum Modell passen.
P-Wert: Hier ist der P-Wert 1,86881E-07, was sehr viel weniger als 0,1 ist, was bedeutet, dass der IQ signifikante Vorhersagewerte hat.
Siehe die Tabelle unten.
Sie können sehen, dass fast alle Punkte inline oder in einer nahe gelegenen Trendlinie liegen.
Beispiel 4
Wir müssen den Umsatz von AC basierend auf dem Umsatz und der Temperatur für einen anderen Monat vorhersagen.
| Monat | Temp | Der Umsatz |
| Jan. | 25 | 38893 |
| Feb. | 28 | 42254 |
| Beschädigen | 31 | 42845 |
| Apr. | 33 | 47917 |
| Kann | 37 | 51243 |
| Jun | 40 | 69588 |
| Jul | 38 | 56570 |
| Aug. | 37 | 50000 |
Führen Sie die folgenden Schritte aus, um das Regressionsergebnis zu erhalten.
Schritt 1: Ermitteln Sie zunächst die abhängigen und unabhängigen Variablen. Hier ist der Umsatz die abhängige Variable, und die Temperatur ist eine unabhängige Variable, da der Umsatz variiert, wenn sich die Temperatur ändert.
Schritt 2: Gehen Sie zur Registerkarte Daten - Klicken Sie auf Datenanalyse - Wählen Sie Regression aus - klicken Sie auf OK.
Dadurch wird das Regressionsfenster für Sie geöffnet.
Schritt 3. Geben Sie Verkäufe in das Feld Input Y Range und Temp in das Feld Input X Range ein. (Aktivieren Sie die Option Beschriftungen, wenn sich in Ihrem Datenbereich Überschriften befinden. Wählen Sie die Ausgabeoptionen aus und überprüfen Sie die gewünschten Restwerte. Klicken Sie auf OK.
Dies gibt Ihnen eine zusammenfassende Ausgabe wie unten.
Schritt 4: Analysieren Sie das Ergebnis.
Multiple R: Hier beträgt der Korrelationskoeffizient 0,877, was nahe bei 1 liegt, was bedeutet, dass die lineare Beziehung positiv ist.
R-Quadrat: Der R-Quadrat-Wert beträgt 0,770, was bedeutet, dass 77% der Werte zum Modell passen
P-Wert: Hier ist der P-Wert 1,86881E-07, was sehr viel weniger als 0,1 ist, was bedeutet, dass der IQ signifikante Vorhersagewerte hat.
Beispiel # 5
Lassen Sie uns nun eine Regressionsanalyse für mehrere unabhängige Variablen durchführen:
Sie müssen den Verkauf eines Mobiltelefons vorhersagen, das nächstes Jahr auf den Markt kommen wird. Sie haben den Preis und die Bevölkerung der Länder, die den Verkauf von Mobiltelefonen beeinflussen.
| Mobile Version | Der Umsatz | Menge | Population |
| UNS | 63860 | 858 | 823 |
| Vereinigtes Königreich | 61841 | 877 | 660 |
| KZ | 60876 | 873 | 631 |
| CH | 58188 | 726 | 842 |
| HN | 52728 | 864 | 573 |
| AU | 52388 | 680 | 809 |
| NZ | 51075 | 728 | 661 |
| RU | 49019 | 689 | 778 |
Führen Sie die folgenden Schritte aus, um das Regressionsergebnis zu erhalten.
Schritt 1. Ermitteln Sie zunächst die abhängigen und unabhängigen Variablen. Hier ist der Umsatz abhängig von Variable und Menge und Bevölkerung. Beide sind unabhängige Variablen, da der Umsatz je nach Menge und Bevölkerung des Landes variiert.
Schritt 2. Gehen Sie zur Registerkarte Daten - Klicken Sie auf Datenanalyse - Regression auswählen - Klicken Sie auf OK.
Dadurch wird das Regressionsfenster für Sie geöffnet.
Schritt 3. Geben Sie Verkäufe in das Feld Eingabe Y ein und wählen Sie Menge und Population in Feld Eingabe X Bereich aus. (Aktivieren Sie die Option Beschriftungen, wenn sich in Ihrem Datenbereich Überschriften befinden. Wählen Sie die Ausgabeoptionen aus und überprüfen Sie die gewünschten Restwerte. Klicken Sie auf OK.
Führen Sie nun die Regression mithilfe der Datenanalyse auf der Registerkarte Daten aus. Dies gibt Ihnen das folgende Ergebnis.
Zusammenfassende Ausgabe
Multiple R: Hier beträgt der Korrelationskoeffizient 0,93, was sehr nahe an 1 liegt, was bedeutet, dass die lineare Beziehung sehr positiv ist.
R-Quadrat: Der R-Quadrat-Wert beträgt 0,866, was bedeutet, dass 86,7% der Werte zum Modell passen.
Signifikanz F: Die Signifikanz F ist kleiner als 0,1, was bedeutet, dass die Regressionsgleichung einen signifikanten Vorhersagewert hat.
P-Wert : Wenn Sie sich den P-Wert für Menge und Bevölkerung ansehen, sehen Sie, dass die Werte kleiner als 0,1 sind, was bedeutet, dass Menge und Bevölkerung einen signifikanten Vorhersagewert haben. Die geringeren P-Werte bedeuten, dass eine Variable signifikantere Vorhersagewerte aufweist.
Sowohl die Menge als auch die Bevölkerung haben jedoch einen signifikanten Vorhersagewert. Wenn Sie jedoch den P-Wert für Menge und Bevölkerung betrachten, können Sie sehen, dass die Menge in Excel einen geringeren P-Wert als die Bevölkerung hat. Dies bedeutet, dass die Menge einen signifikanteren Vorhersagewert hat als die Bevölkerung.
Dinge, an die man sich erinnern sollte
- Überprüfen Sie immer die abhängigen und unabhängigen Variablen, wenn Sie Daten auswählen.
- Die lineare Regressionsanalyse berücksichtigt die Beziehung zwischen dem Mittelwert der Variablen.
- Dies modelliert nur die Beziehung zwischen den linearen Variablen
- Manchmal ist es nicht die beste Lösung für ein reales Problem. Zum Beispiel: (Alter und Löhne). Meistens steigt der Lohn mit zunehmendem Alter. Nach der Pensionierung steigt jedoch das Alter, aber die Löhne sinken.
