Wie führt man eine Regressionsanalyse in Excel durch?

Inhaltsverzeichnis:

Anonim

Regression ist ein Analysetool, mit dem wir große Datenmengen analysieren und Prognosen und Vorhersagen in Microsoft Excel treffen.

Möchten Sie die Zukunft vorhersagen? Nein, wir werden keine Astrologie lernen. Wir stehen auf Zahlen und lernen heute die Regressionsanalyse in Excel.

Um zukünftige Schätzungen vorherzusagen, werden wir Folgendes untersuchen:

  • REGRESSIONSANALYSE MIT EXCEL-FUNKTIONEN (MANUELLE REGRESSIONSFINDUNG)
  • REGRESSIONSANALYSE MIT DEM ANALYSIS TOOLPAK-ADD-IN VON EXCEL
  • REGRESSIONSDIAGRAMM IN EXCEL

Lass es uns tun…

Szenario:

Nehmen wir an, Sie verkaufen Erfrischungsgetränke. Wie cool wird es sein, wenn Sie Folgendes vorhersagen können:

  • Wie viele Erfrischungsgetränke werden im nächsten Jahr auf Basis der Vorjahresdaten verkauft?
  • Welche Bereiche müssen fokussiert werden?
  • Und wie können Sie Ihren Umsatz steigern, indem Sie Ihre Strategie ändern?

Es wird profitabel sein. Richtig? … ich weiß. Also lasst uns anfangen.

Sie haben 11 Datensätze von Verkäufern und verkauften Erfrischungsgetränken.

Basierend auf diesen Daten möchten Sie nun die Anzahl der Verkäufer prognostizieren, die erforderlich sind, um 2000 Verkäufe von Erfrischungsgetränken zu erzielen.

Die Regressionsgleichung ist ein Werkzeug, um solche genauen Schätzungen vorzunehmen. Dazu müssen wir zuerst die Regression kennen.

REGRESSIONSANALYSE MIT EXCEL-FUNKTIONEN (MANUELLE REGRESSIONSFINDUNG)

Dieser Teil wird Ihnen helfen, die Regression besser zu verstehen, als nur das Excel-Regressionsverfahren zu erzählen.

Einführung:

Einfache lineare Regression:

Die Untersuchung der Beziehung zwischen zwei Variablen wird als einfache lineare Regression bezeichnet. Wobei eine Variable von der anderen unabhängigen Variablen abhängt. Die abhängige Variable wird oft mit Namen wie Driven, Response und Target Variable aufgerufen. Und die unabhängige Variable wird oft als Driving, Predictor oder einfach als unabhängige Variable ausgesprochen. Diese Namen beschreiben sie eindeutig.

Vergleichen wir dies nun mit Ihrem Szenario. Sie möchten wissen, wie viele Verkäufer Sie benötigen, um zu erreichen 2000 Verkäufe. Hier ist also die abhängige Variable die Anzahl der Verkäufer und die unabhängige Variable ist der Verkauf von Erfrischungsgetränken.

Die unabhängige Variable wird meistens bezeichnet als x und abhängige Variable als y.

In unserem Fall werden Softdrinks verkauft x und die anzahl der verkäufer ist y.

Wenn wir wissen möchten, wie viele Erfrischungsgetränke verkauft werden, wenn wir ernennen 200 Verkäufer, dann ist das Szenario umgekehrt.

Weiter gehts.

Die „einfache“ Mathematik der linearen Regressionsgleichung:

Nun, es ist nicht einfach. Aber Excel hat es einfach gemacht.

Wir müssen die erforderliche Anzahl von Verkäufern für alle 11 Fälle vorhersagen, um die zwölftnächste Vorhersage zu erhalten.

Sagen wir:

Softdrink verkauft ist x

Die Nummer der Verkäufer ist ja

Das vorhergesagte ja (Anzahl der Verkäufer) auch genannt Regressionsgleichung, wäre

x*Steigung+Achsenabschnitt (Entspann dich, ich habe es abgedeckt)

Jetzt fragst du dich bestimmt, wo die stat erhalten Sie die Steigung und den Schnittpunkt. Keine Sorge, Excel hat Funktionen dafür. Sie müssen nicht lernen, wie Sie die Steigung finden und manuell abfangen.

Wenn Sie möchten, bereite ich dafür ein separates Tutorial vor. Lass es mich im Kommentarbereich wissen. Dies sind einige wichtige Datenanalysetools.

Kommen wir nun zu unserer Berechnung:

Schritt 1: Bereiten Sie diesen kleinen Tisch vor

Schritt 2: Finden Sie die Steigung der Regressionsgeraden

Excel-Funktion für Pisten ist

=SLOPE(bekannt_y's,bekannt_x's)

Deine bekannten_y's sind in Reichweite B2: B12 und bekannt_x liegen im Bereich C2: C12

In der Zelle B16, schreibe die Formel unten

=STEIGUNG(B2:B12, C2:C12)

(Hinweis: Die Steigung wird in der Regressionsgleichung auch als Koeffizient von x bezeichnet)

Sie erhalten 0.058409. Runden Sie auf 2 Dezimalstellen auf und Sie erhalten 0.06.

Schritt 3: Finden Sie den Achsenabschnitt der Regressionsgeraden

Excel-Funktion für das Abfangen ist

=INTERCEPT(bekannte_y, bekannte_x)

Wir wissen, was unsere bekannte x und y

In der Zelle B17, schreibe diese Formel auf

=ABFANG(B2: B12, C2: C12)

Sie erhalten einen Wert von -1,1118969. Auf 2 Dezimalstellen aufrunden. Sie erhalten -1.11.

Unsere lineare Regressionsgleichung ist = x*0,06 + (-1,11). Jetzt können wir mögliches y in Abhängigkeit vom Ziel x leicht vorhersagen.

Schritt 4: Schreiben Sie in D2 die Formel unten

=C2*$B$16+$B$17(Regressionsgleichung)

Sie erhalten einen Wert von 13.55.

Wählen Sie D2 bis D13 und drücken Sie STRG+D um die Formel im Bereich auszufüllen D2: D13

In der Zelle D13 Sie haben Ihre erforderliche Anzahl von Verkäufern.

Um das Ziel zu erreichen, 2000 Verkauf von alkoholfreien Getränken, Sie benötigen eine Schätzung von 115,71 Verkäufern oder sagen wir 116, da es illegal ist, Menschen in Stücke zu schneiden.

Jetzt können Sie damit ganz einfach Was-wäre-wenn-Analysen in Excel durchführen. Ändern Sie einfach die Anzahl der Verkäufe und es wird Ihnen angezeigt, wie viele Verkäufer es brauchen, um dieses Verkaufsziel zu erreichen.

Spielen Sie herum, um herauszufinden:

Wie viel Personal benötigen Sie, um den Umsatz zu steigern?

Wie viele Verkäufe werden steigen, wenn Sie Ihre Verkäufer erhöhen?

Machen Sie Ihre Schätzung zuverlässiger:

Jetzt wissen Sie, dass Sie 116 Verkäufer benötigen, um 2000 Verkäufe zu tätigen.

In der Analytik wird nichts nur gesagt und geglaubt. Sie müssen einen Prozentsatz der Zuverlässigkeit Ihrer Schätzung angeben. Es ist, als würden Sie ein Zertifikat Ihrer Gleichung ausstellen.

Korrelationskoeffizient-Formel:

Als nächstes werden Sie gefragt, inwieweit diese beiden Variablen zusammenhängen. Statisch müssen Sie den Korrelationskoeffizienten angeben.

Excel-Funktion für Korrelation ist

=KORREL(Array1, Array2)

In Ihrem Fall sind bekannt_x und Know_y unabhängig voneinander array1 und array2.

Geben Sie in B18 diese Formel ein

=KORREL((B2: B12, C2: C12)

Du wirst haben 0.919090. Formatieren Sie Zelle B2 in den Prozentsatz. Jetzt hab 92% der Korrelation.

Nun, was ist das? 92% meint. Es bedeutet, da 92% der Verkaufschancen steigen, wenn Sie die Anzahl der Verkäufer erhöhen und 92% des Umsatzes sinken, wenn Sie die Anzahl der Verkäufer verringern. Es wird genannt Positiver Korrelationskoeffizient.

R Knappe (R^2) :

Der R Squire-Wert sagt Ihnen, um wie viel Prozent Ihre Regressionsgleichung kein Zufall ist. Wie viel es anhand der bereitgestellten Daten genau ist.

Die Excel-Funktion für R squire ist RSQ.

RSQ (bekannte_y, bekannte_x)

In unserem Fall erhalten wir den R squire-Wert in Zelle B19.

Geben Sie in B19 diese Formel ein

=RSQ(B2: B12, C2: C12)

Wir haben also 84% des r-Quadrat-Werts. Das ist eine sehr gute Erklärung für unsere Regression. Es sagt, dass 84 % unserer Daten einfach kein Zufall sind. Y (Anzahl der Verkäufer) hängt stark von X (Verkauf von Erfrischungsgetränken) ab.

Es gibt viele andere Tests, die wir mit diesen Daten durchführen können, um unsere Regression sicherzustellen. Aber manuell wird es ein komplexes und langwieriges Verfahren sein. Aus diesem Grund bietet Excel das Analyse-Toolpak an. Mit diesem Tool können wir diese Regressionsanalyse in Sekundenschnelle durchführen.

REGRESSION IN EXCEL MIT DEM ANALYSIS TOOLPAK-ADD-IN VON EXCEL

Wenn Sie bereits wissen, was Regressionsgleichungen sind, und Sie nur schnell Ihre Ergebnisse haben möchten, dann ist dieser Teil für Sie. Wenn Sie jedoch Regressionsgleichungen leicht verstehen möchten, scrollen Sie nach oben zu REGRESSIONSANALYSE MIT EXCEL-FUNKTIONEN (MANUELLE REGRESSIONSFINDUNG).

Excel bietet in seinem Analysis Toolpak eine ganze Reihe von Tools für die Analyse. Standardmäßig ist es auf der Registerkarte Daten nicht verfügbar. Sie müssen es hinzufügen. Also fügen wir es zuerst hinzu.

Hinzufügen von Analysis Toolpak zu Excel 2016

Wenn Sie nicht wissen, wo die Datenanalyse in Excel ist, befolgen Sie diese Schritte

Schritt 1: Gehen Sie zu Excel-Optionen: Datei? Optionen? Add-Ins

Schritt 2: Klicken Sie auf Add-Ins. Sie sehen eine Liste der verfügbaren Add-Ins.

Wählen Sie Analysis ToolPak und suchen Sie unten im Fenster nach manage. Wählen Sie in verwalten Excel Add-Ins und klicken Sie auf GO.

Das Add-In-Fenster wird geöffnet. Wählen Sie hier Analysis ToolPak aus. Klicken Sie dann auf die OK-Schaltfläche.

Jetzt können Sie über die Registerkarte Daten auf alle Funktionen des Datenanalyse-ToolPak zugreifen.

Verwenden des Analysis ToolPak für die Regression

Schritt 1: Gehen Sie zur Registerkarte Daten, suchen Sie nach Datenanalyse. Dann klicken Sie darauf.

Es öffnet sich ein Dialogfeld.

Schritt 2: Suchen Sie in der Liste der Analysetools nach „Regression“ und klicken Sie auf die Schaltfläche OK.

Der Rückschritt Eingabefenster öffnet sich. Sie sehen eine Reihe von verfügbaren Eingabeoptionen. Aber vorerst konzentrieren wir uns nur auf den Y-Bereich und den X-Bereich und lassen alles andere auf die Standardeinstellungen zurück.

Schritt 4: Eingaben bereitstellen:

Anzahl der Verkäufer ist Ja

Der Verkauf von Erfrischungsgetränken ist x

Somit

  • Y-Bereich = B2: B11

Und

  • X-Bereich = C2:C11

Für den Ausgabebereich habe ich E4 auf dem gleichen Blatt gewählt. Sie können ein neues Arbeitsblatt auswählen, um Ergebnisse für ein neues Arbeitsblatt in derselben Arbeitsmappe oder eine komplett neue Arbeitsmappe zu erhalten. Wenn Sie mit Ihren Eingaben fertig sind, klicken Sie auf die Schaltfläche OK.

Ergebnisse:

Sie erhalten eine Vielzahl von Informationen aus Ihren Daten. Lassen Sie sich nicht überfordern. Sie müssen nicht alle Gerichte verzehren.

Wir werden uns nur mit den Ergebnissen befassen, die uns helfen, die erforderliche Anzahl von Verkäufern abzuschätzen

Schritt 5: Wir kennen die Regressionsgleichung zur Schätzung von y, das ist

x*Neigung+Achsenabschnitt

Wir müssen nur lokalisieren Neigung und Abfangen im Ergebnis.

Und hier sind sie.

Der Intercept-Koeffizient wird deutlich erwähnt.

Die Steigung wird geschrieben als ‘X-Variable 1’, manchmal auch als Koeffizient von X bezeichnet. Runden Sie sie auf und wir erhalten -1.11 als Intercept und 0,06 als Steigung.

Schritt 6: Aus den Ergebnissen können wir die Regressionsgleichung steuern. Und das wäre

=x*(0,06) + (-1.11)

Bereiten Sie diese Tabelle in Excel vor.

Zur Zeit, x ist 2000, was sich in Zelle E2 befindet.

Geben Sie in Zelle F2 diese Formel ein

=E2*F21+F20

Sie erhalten ein Ergebnis von 115.7052757.

Das Aufrunden wird uns geben 116 erforderliche Verkäufer.

Wir haben also gelernt, wie man die Regressionsgleichung manuell und mit Analysis ToolPak bildet. Wie können Sie diese Gleichung verwenden, um zukünftige Statistiken zu schätzen?

Lassen Sie uns nun die Regressionsausgabe von Analysis Toolpak verstehen.

Verstehen der Regressionsausgabe:

Es hat keinen Vorteil, wenn Sie eine Regressionsanalyse mit dem Analyse-Tool-Pack in Excel durchführen und deren Bedeutung nicht interpretieren können.

Zusammenfassungsabschnitt:

Wie der Name schon sagt, handelt es sich um eine Zusammenfassung der Daten.

    1. Multiple R: Gibt an, wie gut die Regressionsgleichung an die Daten angepasst ist. Er wird auch Korrelationskoeffizient genannt.

In unserem Fall ist es 0.919090619 oder 0.92 (zusammenfassen). Dies bedeutet, dass die Wahrscheinlichkeit einer Umsatzsteigerung von 92% besteht, wenn wir die Anzahl unserer Verkäufer erhöhen.

    1. R-Quadrat: Es gibt die Zuverlässigkeit der gefundenen Regression an. Es sagt uns, wie viele Beobachtungen Teil unserer Regressionslinie sind. In unserem Fall ist es 0,844727566 oder 0,85. Das bedeutet, dass unsere Regression zu 85% angepasst ist.
    2. Angepasstes R-Quadrat: Das angepasste Quadrat ist nur eine mehr bezeugte Version des R-Quadrats. Hauptsächlich nützlich in der multiplen Regressionsanalyse.
    3. Standart Fehler: Während R. Squire Ihnen sagt, wie viele Datenpunkte in die Nähe der Regressionslinie fallen, sagt Ihnen der Standardfehler, wie weit ein Datenpunkt von der Regressionslinie entfernt sein kann.

In unserem Fall ist es 6.74.

  1. Beobachtung: Dies ist einfach die Anzahl der Beobachtungen, die in unserem Beispiel 11 beträgt.

Anova-Sektion:

Dieser Abschnitt wird in der linearen Regression kaum verwendet.

  1. df. Es ist ein Freiheitsgrad. Es wird verwendet, wenn die Regression manuell berechnet wird.
  2. SS. Quadratsumme. Es ist nur eine Summe von Quadraten der Varianzen. Wird verwendet, um R-Squire-Werte zu finden.
  3. FRAU. Dies bedeutet quadrierter Wert.
  4. Und 5. F und Signifikanz von F. Wenn die Signifikanz von F (p-Wert der Steigung) kleiner als der F-Test ist, können Sie die Nullhypothese verwerfen und Ihre Hypothese beweisen. In einfacher Sprache können Sie schlussfolgern, dass sich x auf y auswirkt, wenn es geändert wird.

In unserem Fall ist F 48,96264 und die Signifikanz von F ist 0,00063. Das bedeutet, dass unsere Regression zu den Daten passt.

Regressionsabschnitt:

In diesem Abschnitt haben wir die beiden wichtigsten Werte für unsere Regressionsgleichung.

  1. Achsenabschnitt: Wir haben hier einen Achsenabschnitt, der angibt, wo x auf Y schneidet. Dies ist ein wichtiger Teil der Regressionsgleichung. In unserem Fall ist es -1,11.
  2. X-Variable 1 (Neigung). Auch Koeffizient von x genannt. Sie definiert den Tangens der Regressionsgerade.

REGRESSIONSDIAGRAMM IN EXCEL

In Excel ist es einfach, ein Regressionsdiagramm zu zeichnen. Folgen Sie einfach diesen Schritten. Führen Sie diese einfachen Schritte aus, um ein Regressionsdiagramm in Excel 2016, 2013 und 2010 hinzuzufügen.

Schritt 1. Haben Sie Ihre bekannten x in der ersten Spalte und kennen Sie y in der zweiten.

In unserem Fall wissen wir, dass bekannte_ x verkaufte Softdrinks sind. Und bekannt_y's sind Verkäufer.

Schritt 2. Wählen Sie Ihren bekannten x- und y-Bereich aus.

Schritt 3: Gehen Sie zur Registerkarte Einfügen und klicken Sie auf das Streudiagramm.

Sie erhalten ein Diagramm, das wie folgt aussieht.

Schritt 4. Fügen Sie die Trendlinie hinzu: Gehe zum Layout und suche die Trendlinienoption im Analyseabschnitt.

Klicken Sie unter der Option Trendlinie auf Lineare Trendlinie.

Ihr Diagramm sieht so aus.

Dies ist Ihr Regressionsdiagramm.

Fügen Sie nun die folgenden Daten hinzu und erweitern Sie die ausgewählten Daten. Sie sehen eine Änderung in Ihrem Diagramm.

In unserem Beispiel haben wir 2000 zu den verkauften Erfrischungsgetränken hinzugefügt und das Feld Verkäufer leer gelassen. Und wenn wir den Bereich des Graphen erweitern, haben wir dies.

Es wird die erforderliche Anzahl von Verkäufern für 2000 Verkäufe von Erfrischungsgetränken in grafischer Form angeben. Was in der Grafik etwas unter 120 liegt. Und aus unserer Regressionsgleichung wissen wir, dass es 116 ist.

In diesem Artikel habe ich versucht, alles unter Excel-Regressionsanalyse abzudecken. Ich habe die Regression in Excel 2016 erklärt. Die Regression in Excel 2010 und Excel 2013 ist die gleiche wie in Excel 2016.

Für weitere Fragen zu diesem Thema verwenden Sie den Kommentarbereich. Stellen Sie eine Frage, geben Sie eine Meinung ab oder erwähnen Sie einfach meine Grammatikfehler. Alles ist willkommen. Zögern Sie nicht, den Kommentarbereich zu verwenden.

So berechnen Sie die MODE-Funktion in Excel

So berechnen Sie die Mittelwertfunktion in Excel

So erstellen Sie ein Standardabweichungsdiagramm

Beschreibende Statistik in Microsoft Excel 2016

So verwenden Sie die Excel-NORMDIST-Funktion

So verwenden Sie das Pareto-Diagramm und die Analyse

Populäre Artikel:

50 Excel-Shortcut zur Steigerung Ihrer Produktivität

So verwenden Sie die SVERWEIS-Funktion in Excel

So verwenden Sie die ZÄHLENWENN-Funktion in Excel 2016

So verwenden Sie die SUMIF-Funktion in Excel