Regression der Excel-Datenanalyse. Regression in Excel: Gleichung, Beispiele. Lineare Regression

Mit MS Excel können Sie eine Gleichung erstellen lineare Regression Die meiste Arbeit ist sehr schnell erledigt. Es ist wichtig zu verstehen, wie die erhaltenen Ergebnisse zu interpretieren sind.

Ein Add-On ist erforderlich, um zu funktionieren Analysepaket in den Menüpunkt aufzunehmen Dienst \ Add-Ins

In Excel 2007 müssen Sie zum Aktivieren des Analysepakets auf Gehe zu Block klicken Excel-Optionen indem Sie auf die Schaltfläche in der oberen linken Ecke und dann auf die Schaltfläche " Excel-Optionen"Am unteren Rand des Fensters:

Um ein Regressionsmodell zu erstellen, wählen Sie das Element Service \ Datenanalyse \ Regression... (In Excel 2007 ist dieser Modus in der Box Daten / Datenanalyse / Regression). Es erscheint ein Dialogfeld, das ausgefüllt werden muss:

1) Eingangsspanne Y¾ enthält einen Verweis auf Zellen, die die Werte des resultierenden Merkmals enthalten ja... Die Werte müssen sich in einer Spalte befinden;

2) Eingabeintervall X¾ enthält einen Verweis auf Zellen, die die Werte der Faktoren enthalten. Werte müssen in Spalten sein;

3) unterschreiben Stichworte gesetzt, wenn die ersten Zellen erklärenden Text (Datensignaturen) enthalten;

4) Zuverlässigkeitsniveau¾ Dies ist das Konfidenzniveau, das standardmäßig mit 95 % angenommen wird. Wenn Ihnen dieser Wert nicht zusagt, müssen Sie diese Funktion aktivieren und den erforderlichen Wert eingeben;

5) Zeichen Konstant-Null ist enthalten, wenn eine Gleichung erstellt werden muss, in der eine freie Variable;

6) Ausgabeoptionen bestimmen, wo die Ergebnisse platziert werden sollen. Erstellt standardmäßig den Modus Neues Arbeitsblatt;

7) Blockieren Reste ermöglicht Ihnen, die Ausgabe von Residuen und die Erstellung ihrer Diagramme zu aktivieren.

Als Ergebnis werden Informationen angezeigt, die alle notwendigen Informationen enthalten und in drei Blöcke gruppiert sind: Regressionsstatistik, ANOVA, Den Rest abheben... Betrachten wir sie genauer.

1. Regressionsstatistik:

mehrere R wird durch die Formel ( Korrelationskoeffizient nach Pearson);

R (Bestimmtheitsmaß);

Normalisiert R-Quadrat wird nach der Formel berechnet (verwendet für multiple Regression);

Standart Fehler S berechnet nach der Formel ;

Beobachtungen sind die Datenmenge n.

2. ANOVA, Linie Rückschritt:

Parameter df entspricht m(Anzahl der Faktorensätze x);

Parameter SS wird durch die Formel bestimmt;

Parameter FRAU wird durch die Formel bestimmt;

Statistiken F wird durch die Formel bestimmt;

Bedeutung F... Wenn die empfangene Zahl überschritten wird, wird die Hypothese akzeptiert (es besteht kein linearer Zusammenhang), andernfalls wird die Hypothese akzeptiert (es besteht ein linearer Zusammenhang).

3. ANOVA, Linie Rest:

Parameter df ist gleich;

Parameter SS ist definiert durch die Formel ;

Parameter FRAU wird durch die Formel definiert.

4. ANOVA, Linie Gesamt enthält die Summe der ersten beiden Spalten.

5. ANOVA, Linie Y-Kreuzung enthält den Wert des Koeffizienten, Standardfehler und T-Statistiken.

P-Wert ¾ ist der Wert der Signifikanzniveaus entsprechend dem berechneten T-Statistiker. Bestimmt durch die Funktion TDIST ( T-Statistiken; ). Ob P-Wert überschreitet, dann ist die entsprechende Variable statistisch nicht signifikant und kann aus dem Modell ausgeschlossen werden.

Untere 95% und Top 95 %¾ Dies sind die untere und obere Grenze der 95-Prozent-Konfidenzintervalle für die Koeffizienten der theoretischen linearen Regressionsgleichung. Wenn im Dateneingabeblock das Konfidenzniveau standardmäßig belassen wurde, werden die letzten beiden Spalten die vorherigen duplizieren. Wenn der Benutzer sein eigenes Konfidenzniveau eingegeben hat, enthalten die letzten beiden Spalten die Unter- und Obergrenze für das angegebene Konfidenzniveau.

6. ANOVA, die Zeilen enthalten die Werte der Koeffizienten, Standardfehler, T-Statistiker, P-Werte und Konfidenzintervalle für die entsprechenden.

7. Blockieren Den Rest abheben enthält die Werte der vorhergesagten ja(in unserer Notation ist es) und Reste.

Regression in Excel

Die statistische Datenverarbeitung kann auch über das Add-In Analysepaket im Menüpunkt „Service“ durchgeführt werden. Wenn Sie in Excel 2003 öffnen SERVICE, wir finden die Registerkarte nicht DATENANALYSE, dann durch Klicken mit der linken Maustaste öffnen Sie die Registerkarte AUFBAUTEN und entgegengesetzter Punkt ANALYSEPAKET Setzen Sie durch Klicken mit der linken Maustaste ein Häkchen (Abb. 17).

Reis. 17. Fenster AUFBAUTEN

Danach im Menü SERVICE eine Registerkarte erscheint DATENANALYSE.

In Excel 2007 zu installieren ANALYSEPAKET Sie müssen auf die Schaltfläche BÜRO in der oberen linken Ecke des Blatts klicken (Abb. 18a). Klicken Sie anschließend auf die Schaltfläche EXCEL-PARAMETER... Im erscheinenden Fenster EXCEL-PARAMETER klicke mit der linken Maustaste auf den Artikel AUFBAUTEN und wählen Sie auf der rechten Seite der Dropdown-Liste das Element aus ANALYSE-PAKET. Klicken Sie anschließend auf OK.

Excel-Optionen Office-Schaltfläche

Reis. 18. Installation ANALYSEPAKET in Excel 2007

Um das Analysepaket zu installieren, klicken Sie auf die Schaltfläche GEHEN, befindet sich am unteren Rand des geöffneten Fensters. Das in Abb. 12. Setzen Sie ein Häkchen gegenüber ANALYSE-PAKET. Auf der Registerkarte DATEN es erscheint ein Knopf DATENANALYSE(Abb. 19).

Aus den vorgeschlagenen Artikeln wählt er den Artikel " REGRESSION"Und klicken Sie mit der linken Maustaste darauf. Klicken Sie dann auf OK.

Das in Abb. 21

Analysetool " REGRESSION»Wird verwendet, um einen Graphen mit der Methode der kleinsten Quadrate an eine Reihe von Beobachtungen anzupassen. Die Regression wird verwendet, um die Wirkung der Werte einer oder mehrerer erklärender Variablen auf eine einzelne abhängige Variable zu analysieren. Beispielsweise beeinflussen mehrere Faktoren die sportliche Leistung eines Sportlers, einschließlich Alter, Größe und Gewicht. Sie können die Auswirkungen jedes dieser drei Faktoren auf die Leistung eines Athleten berechnen und dann die Daten verwenden, um die Leistung eines anderen Athleten vorherzusagen.

Das Regressionswerkzeug verwendet die Funktion LINEST.

Dialogfeld REGRESSION

Beschriftungen Aktivieren Sie das Kontrollkästchen, wenn die erste Zeile oder erste Spalte des Eingabebereichs Überschriften enthält. Deaktivieren Sie dieses Kontrollkästchen, wenn keine Titel vorhanden sind. In diesem Fall werden automatisch entsprechende Header für die Ausgabetabellendaten generiert.

Konfidenzstufe Aktivieren Sie das Kontrollkästchen, um eine zusätzliche Stufe in die Ausgabesummentabelle aufzunehmen. Geben Sie in das entsprechende Feld die anzuwendende Zuverlässigkeitsstufe zusätzlich zum Standardwert von 95 % ein.

Konstante - Null Aktivieren Sie das Kontrollkästchen, damit die Regressionsgerade durch den Ursprung geht.

Ausgabeabstand Geben Sie einen Verweis auf die obere linke Zelle des Ausgabebereichs ein. Weisen Sie mindestens sieben Spalten für die Ausgabesummentabelle zu, die Folgendes enthält: ANOVA-Ergebnisse, Koeffizienten, Standardfehler der Y-Berechnung, Standardabweichungen, Anzahl der Beobachtungen, Standardfehler für Koeffizienten.

Neues Arbeitsblatt Wählen Sie diesen Schalter, um ein neues Arbeitsblatt in der Arbeitsmappe zu öffnen und die Analyseergebnisse ab Zelle A1 einzufügen. Geben Sie bei Bedarf im Feld gegenüber der entsprechenden Schalterstellung einen Namen für das neue Blatt ein.

Neue Arbeitsmappe Klicken Sie auf den Schalter in diese Position, um eine neue Arbeitsmappe zu erstellen, in der die Ergebnisse einem neuen Arbeitsblatt hinzugefügt werden.

Residuen Aktivieren Sie das Kontrollkästchen, um Residuen in die Ausgabetabelle aufzunehmen.

Standardisierte Residuen Aktivieren Sie das Kontrollkästchen, um standardisierte Residuen in die Ausgabetabelle aufzunehmen.

Residuen darstellen Aktivieren Sie das Kontrollkästchen, um die Residuen für jede unabhängige Variable darzustellen.

Anpassungsdiagramm Aktivieren Sie das Kontrollkästchen, um die vorhergesagten im Vergleich zu den beobachteten Werten darzustellen.

Diagramm der normalen Wahrscheinlichkeit Aktivieren Sie das Kontrollkästchen, um ein Diagramm mit normaler Wahrscheinlichkeit zu erstellen.

Funktion LINEST

Um Berechnungen durchzuführen, wählen Sie mit dem Cursor die Zelle aus, in der der Mittelwert angezeigt werden soll, und drücken Sie die =-Taste auf der Tastatur. Geben Sie anschließend im Feld Name die gewünschte Funktion ein, z. B. DURCHSCHNITT(Abb. 22).

Reis. 22 Suchen von Funktionen in Excel 2003

Wenn im Feld NAME der Name der Funktion ist nicht erschienen, dann klicken Sie mit der linken Maustaste auf das Dreieck neben dem Feld, woraufhin ein Fenster mit einer Liste von Funktionen erscheint. Wenn diese Funktion nicht in der Liste enthalten ist, klicken Sie mit der linken Maustaste auf den Listeneintrag ANDERE FUNKTIONEN, ein Dialogfeld wird angezeigt MEISTER DER FUNKTIONEN, in dem Sie durch vertikales Scrollen die gewünschte Funktion auswählen, mit dem Cursor auswählen und auf klicken OK(Abb. 23).

Reis. 23. Funktionsassistent

Um in Excel 2007 nach einer Funktion zu suchen, kann im Menü eine beliebige Registerkarte geöffnet werden. Um Berechnungen durchzuführen, wählen Sie die Zelle aus, in der der Durchschnittswert angezeigt werden soll, und drücken Sie die Taste = auf der Tastatur. Geben Sie als Nächstes im Feld Name die Funktion an DURCHSCHNITT... Das Fenster zur Berechnung der Funktion ähnelt dem in Excel 2003.

Sie können auch die Registerkarte Formeln auswählen und mit der linken Maustaste auf die Schaltfläche im " EINFÜGEN-FUNKTION”(Abb. 24), es erscheint ein Fenster MEISTER DER FUNKTIONEN, dessen Typ Excel 2003 ähnelt. Auch im Menü können Sie sofort die Kategorie der Funktionen auswählen (zuletzt verwendet, Finanzen, Logik, Text, Datum und Uhrzeit, Mathematik, andere Funktionen), in denen wir nach den suchen gewünschte Funktion.

Andere Funktionen Referenzen und Arrays

Mathematik

Reis. 24 Auswählen einer Funktion in Excel 2007

Funktion LINEST Berechnet Statistiken für eine Reihe unter Verwendung der kleinsten Quadrate, um die gerade Linie zu berechnen, die den verfügbaren Daten am besten entspricht, und gibt dann ein Array zurück, das die resultierende gerade Linie beschreibt. Sie können die Funktion auch kombinieren LINEST mit anderen Funktionen, um andere Arten von Modellen zu berechnen, die in unbekannten Parametern (deren unbekannte Parameter linear sind) linear sind, einschließlich polynomischer, logarithmischer, exponentieller und Potenzreihen. Da ein Array von Werten zurückgegeben wird, muss die Funktion als Array-Formel angegeben werden.

Die Gleichung für eine Gerade lautet wie folgt:

(bei mehreren Bereichen von x-Werten),

wobei der abhängige Wert y eine Funktion des unabhängigen x-Werts ist, die m-Werte die Koeffizienten sind, die jeder unabhängigen Variablen x entsprechen, und b eine Konstante ist. Beachten Sie, dass y, x und m Vektoren sein können. Funktion LINEST gibt ein Array zurück . LINEST kann auch zusätzliche Regressionsstatistiken zurückgeben.

LINEST(bekannte_y; bekannte_x; konstant; Statistik)

Known_y's ist die Menge der y-Werte, die bereits für die Beziehung bekannt sind.

Wenn bekannte_y's eine Spalte haben, dann wird jede Spalte in bekannten_x's als separate Variable interpretiert.

Wenn bekannte_y's eine Zeile haben, dann wird jede Zeile in bekannten_x's als separate Variable interpretiert.

Known_x's sind ein optionaler Satz von x-Werten, die bereits für die Beziehung bekannt sind.

Known_x's können einen oder mehrere Variablensätze enthalten. Wenn nur eine Variable verwendet wird, können bekannte_y's und bekannte_x's jede beliebige Form haben, solange sie die gleiche Dimension haben. Wenn mehr als eine Variable verwendet wird, müssen die know_y's ein Vektor sein (d. h. eine Zeile hoch oder eine Spalte breit).

Wenn array_known_x's weggelassen wird, wird angenommen, dass dieses Array (1; 2; 3; ...) dieselbe Größe hat wie array_known_y's.

Const ist ein boolescher Wert, der angibt, ob die Konstante b 0 sein muss.

Wenn const TRUE ist oder weggelassen wird, wird die Konstante b wie üblich ausgewertet.

Wenn das Argument "const" FALSE ist, wird der Wert von b gleich 0 gesetzt und die Werte von m werden so gewählt, dass die Beziehung erfüllt ist.

Statistik ist ein boolescher Wert, der angibt, ob Sie zusätzliche Regressionsstatistiken zurückgeben möchten.

Wenn Statistics TRUE ist, gibt REST zusätzliche Regressionsstatistiken zurück. Das zurückgegebene Array sieht so aus: (mn; mn-1; ...; m1; b: sen; sen-1; ...; se1; seb: r2; sey: F; df: ssreg; ssresid).

Wenn Statistik FALSE ist oder weggelassen wird, gibt REST nur die Koeffizienten m und die Konstante b zurück.

Zusätzliche Regressionsstatistik.

Die Quantität Beschreibung se1, se2, ..., sen Standardfehlerwerte für die Koeffizienten m1, m2, ..., mn. seb Standardfehlerwert für Konstante b (seb = # N / A wenn const FALSE ist). r2 Koeffizient des Determinismus. Die tatsächlichen y-Werte werden mit den aus der Geradengleichung erhaltenen Werten verglichen; basierend auf den Vergleichsergebnissen wird der Determinismuskoeffizient berechnet, normalisiert von 0 auf 1. Wenn er gleich 1 ist, besteht eine vollständige Korrelation mit dem Modell, dh es gibt keinen Unterschied zwischen den tatsächlichen und den geschätzten Werten von y. Andernfalls, wenn der Determinismuskoeffizient 0 ist, macht es keinen Sinn, eine Regressionsgleichung zu verwenden, um y-Werte vorherzusagen. Weitere Informationen zur Berechnung von r2 finden Sie in den Anmerkungen am Ende dieses Abschnitts. sey Standardfehler für die Schätzung von y. F F-Statistik oder F-beobachteter Wert. Die F-Statistik wird verwendet, um zu bestimmen, ob die beobachtete Beziehung zwischen den abhängigen und unabhängigen Variablen zufällig ist. df Freiheitsgrade. Freiheitsgrade sind nützlich, um F-kritische Werte in einer statistischen Tabelle zu finden. Um die Zuverlässigkeit des Modells zu bestimmen, müssen Sie die Werte in der Tabelle mit der von LINEST zurückgegebenen F-Statistik vergleichen. Weitere Informationen zur Berechnung von df finden Sie in den Anmerkungen am Ende dieses Abschnitts. Beispiel 4 unten zeigt die Verwendung der F- und df-Werte. ssreg Regressionssumme der Quadrate. ssresid Restsumme der Quadrate. Weitere Informationen zum Berechnen der ssreg- und ssresid-Werte finden Sie in den Anmerkungen am Ende dieses Abschnitts.

Die folgende Abbildung zeigt die Reihenfolge, in der die zusätzlichen Regressionsstatistiken zurückgegeben werden.

Anmerkungen:

Jede Gerade kann durch ihre Steigung und den Schnittpunkt mit der y-Achse beschrieben werden:

Steigung (m): Um die Steigung einer geraden Linie zu bestimmen, die normalerweise mit m bezeichnet wird, müssen Sie zwei Punkte der geraden Linie nehmen und; die Steigung wird .

Y-Schnittpunkt (b): Der y-Schnittpunkt einer Linie, normalerweise mit b bezeichnet, ist der y-Wert des Punktes, an dem die Linie die y-Achse schneidet.

Die Geradengleichung hat die Form. Wenn Sie die Werte von m und b kennen, können Sie jeden Punkt auf der Linie berechnen, indem Sie die y- oder x-Werte in der Gleichung einsetzen. Sie können auch die TREND-Funktion verwenden.

Wenn es nur eine unabhängige Variable x gibt, können Sie die Steigung und den y-Achsenabschnitt direkt mit den folgenden Formeln ermitteln:

Steigung: INDEX (LINEST (bekannte_y; bekannte_x); 1)

Y-Schnittpunkt: INDEX (LINEST (bekannte_y; bekannte_x); 2)

Die Genauigkeit der LINEST-Linien-Approximation hängt vom Streuungsgrad der Daten ab. Je näher die Daten an einer geraden Linie liegen, desto genauer ist das LINEST-Modell. REST verwendet die Methode der kleinsten Quadrate, um die beste Anpassung an die Daten zu bestimmen. Wenn es nur eine unabhängige Variable x gibt, werden m und b mit den folgenden Formeln berechnet:

wobei x und y Stichprobenmittelwerte sind, wie z. B. x = MITTELWERT (bekannte_x) und y = MITTELWERT (bekannte_y).

Die Anpassungsfunktionen LINEST und LOGEST können die gerade oder exponentielle Kurve berechnen, die die Daten am besten beschreibt. Sie beantworten jedoch nicht die Frage, welches der beiden Ergebnisse zur Lösung der gestellten Aufgabe besser geeignet ist. Sie können auch TREND (bekannte_y; bekannte_x) für eine gerade Linie oder WACHSTUM (bekannte_y; bekannte_x) für eine exponentielle Kurve berechnen. Diese Funktionen geben, wenn Sie new_x_values nicht angeben, ein Array von berechneten y-Werten für die tatsächlichen x-Werte entlang einer geraden Linie oder Kurve zurück. Die berechneten Werte können dann mit den tatsächlichen Werten verglichen werden. Sie können auch Diagramme zum visuellen Vergleich erstellen.

Durch die Durchführung einer Regressionsanalyse, Microsoft Excel berechnet für jeden Punkt das Quadrat der Differenz zwischen dem vorhergesagten y-Wert und dem tatsächlichen y-Wert. Die Summe dieser quadrierten Differenzen wird als Residualsumme der Quadrate (ssresid) bezeichnet. Microsoft Excel berechnet dann die Gesamtsumme der Quadrate (stotal). Wenn const = TRUE oder kein Wert für dieses Argument angegeben ist, ist die Gesamtsumme der Quadrate die Summe der Quadrate der Differenz zwischen den tatsächlichen y-Werten und den mittleren y-Werten. Bei const = FALSE ist die Gesamtsumme der Quadrate gleich der Summe der Quadrate der reellen Werte von y (ohne den Mittelwert von y vom Quotientenwert von y zu subtrahieren). Die Regressionsquadratsumme kann dann wie folgt berechnet werden: ssreg = sstotal - ssresid. Je kleiner die Residualsumme der Quadrate ist, desto größer ist der Wert des Determinismuskoeffizienten r2, der zeigt, wie gut die mit der Regressionsanalyse erhaltene Gleichung die Beziehung zwischen den Variablen erklärt. Der r2-Koeffizient ist ssreg / sstotal.

In einigen Fällen haben eine oder mehrere X-Spalten (lassen Sie die Y- und X-Werte in den Spalten sein) keinen zusätzlichen prädikativen Wert in den anderen X-Spalten. Mit anderen Worten, das Löschen einer oder mehrerer X-Spalten kann zu Y-Werten führen mit der gleichen Genauigkeit berechnet. In diesem Fall werden redundante X-Spalten aus dem Regressionsmodell ausgeschlossen. Dieses Phänomen wird als "Kollinearität" bezeichnet, da redundante X-Spalten als Summe mehrerer nicht redundanter Spalten dargestellt werden können. LINEST prüft auf Kollinearität und entfernt alle redundanten X-Spalten aus dem Regressionsmodell, wenn sie gefunden werden. Die gelöschten X-Spalten können in der LINEST-Ausgabe durch einen Faktor von 0 und einen se-Wert von 0 identifiziert werden. Das Entfernen einer oder mehrerer Spalten als redundant ändert den df-Wert, da er von der Anzahl der X-Spalten abhängt, die tatsächlich für Vorhersagezwecke verwendet werden. Weitere Informationen zur Berechnung von df finden Sie unten in Beispiel 4. Wenn sich df aufgrund des Entfernens redundanter Spalten ändert, ändern sich auch sey und F. Von Kollinearität wird oft abgeraten. Es sollte jedoch verwendet werden, wenn einige der X-Spalten 0 oder 1 enthalten, um anzuzeigen, ob die Versuchsperson in einer separaten Gruppe ist. Wenn const = TRUE oder weggelassen wird, fügt REST eine zusätzliche X-Spalte ein, um den Schnittpunkt zu modellieren. Wenn es eine Spalte mit Werten von 1 für Männer und 0 für Frauen gibt und es auch eine Spalte mit Werten von 1 für Frauen und 0 für Männer gibt, dann wird die letzte Spalte entfernt, da ihre Werte sein können aus der Spalte mit dem „Indikator für das männliche Geschlecht“ erhalten.

Die Berechnung von df für Fälle, in denen Spalten von X aufgrund von Kollinearität nicht aus dem Modell entfernt werden, ist wie folgt: Wenn es k Spalten mit bekannten_xen gibt und der Wert von const = WAHR oder nicht angegeben ist, dann ist df = n - k - 1. Wenn const = FALSE, dann df = n - k. In beiden Fällen erhöht das Entfernen der X-Spalten aufgrund von Kollinearität den df-Wert um 1.

Formeln, die Arrays zurückgeben, müssen als Arrayformeln eingegeben werden.

Wenn Sie ein Array von Konstanten für beispielsweise bekannte_x eingeben, verwenden Sie ein Semikolon, um die Werte in derselben Zeile zu trennen, und einen Doppelpunkt, um die Zeilen zu trennen. Die Trennzeichen variieren je nach den Optionen, die im Fenster Sprache und Standards auf dem Bedienfeld eingestellt sind.

Es ist zu beachten, dass die von der Regressionsgleichung vorhergesagten y-Werte möglicherweise nicht korrekt sind, wenn sie außerhalb des Bereichs der y-Werte liegen, die zur Definition der Gleichung verwendet wurden.

Der in der Funktion verwendete Hauptalgorithmus LINEST, unterscheidet sich vom Hauptalgorithmus der Funktionen NEIGUNG und LINIENSEGMENT... Unterschiede zwischen den Algorithmen können bei undefinierten und kollinearen Daten zu unterschiedlichen Ergebnissen führen. Wenn beispielsweise die Datenpunkte von bekannten_y 0 und die Datenpunkte von bekannten_x 1 sind, dann:

Funktion LINEST gibt einen Wert gleich 0 zurück. Funktionsalgorithmus LINEST wird verwendet, um gültige Werte für kollineare Daten zurückzugeben, in diesem Fall kann mindestens eine Antwort gefunden werden.

Die Funktionen SLOPE und INTERCEPT geben den # DIV / 0!-Fehler zurück. Der Funktionsalgorithmus SLOPE und INTERCEPT wird verwendet, um nur nach einer Antwort zu suchen, und in diesem Fall können mehrere sein.

Neben der Berechnung von Statistiken für andere Regressionstypen kann RUND zum Berechnen von Bereichen für andere Regressionstypen verwendet werden, indem die Funktionen von x und y als Reihe von x und y für RUND eingegeben werden. Zum Beispiel die folgende Formel:

RUND (y-Werte, x-Werte ^ SPALTE ($ A: $ C))

funktioniert mit einer Spalte mit Y-Werten und einer Spalte mit X-Werten, um eine Annäherung an einen Würfel (3. Grades Polynom) der folgenden Form zu berechnen:

Die Formel kann geändert werden, um andere Regressionsarten zu berechnen, aber in einigen Fällen sind Anpassungen der Ausgabewerte und anderer Statistiken erforderlich.

Auch eine statistische Verarbeitung der Daten kann über das Add-In erfolgen ANALYSEPAKET(Abb. 62).

Aus den vorgeschlagenen Artikeln wählt er den Artikel " REGRESSION"Und klicken Sie mit der linken Maustaste darauf. Klicken Sie dann auf OK.

Das in Abb. 63.

Das Regressionswerkzeug verwendet die Funktion LINEST.

Dialogfeld REGRESSION

Konstante - Null Aktivieren Sie das Kontrollkästchen, damit die Regressionsgerade durch den Ursprung geht.

Neue Arbeitsmappe Klicken Sie auf den Schalter in diese Position, um eine neue Arbeitsmappe zu erstellen, in der die Ergebnisse einem neuen Arbeitsblatt hinzugefügt werden.

Residuen Aktivieren Sie das Kontrollkästchen, um Residuen in die Ausgabetabelle aufzunehmen.

Standardisierte Residuen Aktivieren Sie das Kontrollkästchen, um standardisierte Residuen in die Ausgabetabelle aufzunehmen.

Residuen darstellen Aktivieren Sie das Kontrollkästchen, um die Residuen für jede unabhängige Variable darzustellen.

Anpassungsdiagramm Aktivieren Sie das Kontrollkästchen, um die vorhergesagten im Vergleich zu den beobachteten Werten darzustellen.

Diagramm der normalen Wahrscheinlichkeit Aktivieren Sie das Kontrollkästchen, um ein Diagramm mit normaler Wahrscheinlichkeit zu erstellen.

Funktion LINEST

Die Gleichung für eine Gerade lautet wie folgt:

y = m 1 x 1 + m 2 x 2 +… + b (bei mehreren x-Wertebereichen),

wobei der abhängige Wert y eine Funktion des unabhängigen x-Werts ist, die m-Werte die Koeffizienten sind, die jeder unabhängigen Variablen x entsprechen, und b eine Konstante ist. Beachten Sie, dass y, x und m Vektoren sein können. Funktion LINEST gibt ein Array zurück (mn; mn-1;…; m 1; b). LINEST kann auch zusätzliche Regressionsstatistiken zurückgeben.

LINEST(bekannte_y; bekannte_x; konstant; Statistik)

Known_y's ist die Menge der y-Werte, die bereits für die Beziehung y = mx + b bekannt sind.

Wenn bekannte_y's eine Spalte haben, dann wird jede Spalte in bekannten_x's als separate Variable interpretiert.

Wenn bekannte_y's eine Zeile haben, dann wird jede Zeile in bekannten_x's als separate Variable interpretiert.

Known_x's sind ein optionaler Satz von x-Werten, die bereits für y = mx + b bekannt sind.

Wenn array_known_x's weggelassen wird, wird angenommen, dass dieses Array (1; 2; 3; ...) dieselbe Größe hat wie array_known_y's.

Const ist ein boolescher Wert, der angibt, ob die Konstante b 0 sein muss.

Wenn const TRUE ist oder weggelassen wird, wird die Konstante b wie üblich ausgewertet.

Ist das Argument "const" FALSE, dann wird der Wert von b gleich 0 gesetzt und die Werte von m werden so gewählt, dass die Beziehung y = mx erfüllt ist.

Statistik ist ein boolescher Wert, der angibt, ob Sie zusätzliche Regressionsstatistiken zurückgeben möchten.

Wenn Statistik FALSE ist oder weggelassen wird, gibt REST nur die Koeffizienten m und die Konstante b zurück.

Zusätzliche Regressionsstatistiken (Tabelle 17)

Die Quantität	Beschreibung
se1, se2, ..., sen	Standardfehlerwerte für die Koeffizienten m1, m2, ..., mn.
seb	Standardfehlerwert für Konstante b (seb = # N / A wenn const FALSE ist).
r2	Koeffizient des Determinismus. Die tatsächlichen y-Werte werden mit den aus der Geradengleichung erhaltenen Werten verglichen; basierend auf den Vergleichsergebnissen wird der Determinismuskoeffizient berechnet, normalisiert von 0 auf 1. Wenn er gleich 1 ist, besteht eine vollständige Korrelation mit dem Modell, dh es gibt keinen Unterschied zwischen den tatsächlichen und den geschätzten Werten von y. Andernfalls, wenn der Determinismuskoeffizient 0 ist, macht es keinen Sinn, eine Regressionsgleichung zu verwenden, um y-Werte vorherzusagen. Weitere Informationen zur Berechnung von r2 finden Sie in den Anmerkungen am Ende dieses Abschnitts.
sey	Standardfehler für die Schätzung von y.
F	F-Statistik oder F-beobachteter Wert. Die F-Statistik wird verwendet, um zu bestimmen, ob die beobachtete Beziehung zwischen den abhängigen und unabhängigen Variablen zufällig ist.
df	Freiheitsgrade. Freiheitsgrade sind nützlich, um F-kritische Werte in einer statistischen Tabelle zu finden. Um die Zuverlässigkeit des Modells zu bestimmen, müssen Sie die Werte in der Tabelle mit der von LINEST zurückgegebenen F-Statistik vergleichen. Weitere Informationen zur Berechnung von df finden Sie in den Anmerkungen am Ende dieses Abschnitts. Beispiel 4 unten zeigt die Verwendung der F- und df-Werte.
ssreg	Regressionssumme der Quadrate.
ssresid	Restsumme der Quadrate. Weitere Informationen zum Berechnen der ssreg- und ssresid-Werte finden Sie in den Anmerkungen am Ende dieses Abschnitts.

Die folgende Abbildung zeigt die Reihenfolge, in der die zusätzlichen Regressionsstatistiken zurückgegeben werden (Abbildung 64).

Anmerkungen:

Jede Gerade kann durch ihre Steigung und den Schnittpunkt mit der y-Achse beschrieben werden:

Steigung (m): Um die Steigung einer geraden Linie zu bestimmen, die normalerweise mit m bezeichnet wird, müssen Sie zwei Punkte der geraden Linie (x 1, y 1) und (x 2, y 2) nehmen; die Steigung ist (y 2 -y 1) / (x 2 -x 1).

Y-Schnittpunkt (b): Der y-Schnittpunkt einer Linie, normalerweise mit b bezeichnet, ist der y-Wert des Punktes, an dem die Linie die y-Achse schneidet.

Die Geradengleichung hat die Form y = mx + b. Wenn Sie die Werte von m und b kennen, können Sie jeden Punkt auf der Linie berechnen, indem Sie die y- oder x-Werte in der Gleichung einsetzen. Sie können auch die TREND-Funktion verwenden.

Wenn es nur eine unabhängige Variable x gibt, können Sie die Steigung und den y-Achsenabschnitt direkt mit den folgenden Formeln ermitteln:

Steigung: INDEX (LINEST (bekannte_y; bekannte_x); 1)

Y-Schnittpunkt: INDEX (LINEST (bekannte_y; bekannte_x); 2)

wobei x und y Stichprobenmittelwerte sind, wie z. B. x = MITTELWERT (bekannte_x) und y = MITTELWERT (bekannte_y).

Bei der Regressionsanalyse berechnet Microsoft Excel für jeden Punkt das Quadrat der Differenz zwischen dem vorhergesagten y-Wert und dem tatsächlichen y-Wert. Die Summe dieser quadrierten Differenzen wird als Residualsumme der Quadrate (ssresid) bezeichnet. Microsoft Excel berechnet dann die Gesamtsumme der Quadrate (stotal). Wenn const = TRUE oder dieses Argument weggelassen wird, ist die Gesamtsumme der Quadrate die Summe der Quadrate der Differenz zwischen den tatsächlichen y-Werten und den mittleren y-Werten. Wenn const = FALSE, ist die Gesamtsumme der Quadrate gleich der Summe der Quadrate der reellen Werte von y (ohne den Mittelwert von y vom Quotientenwert von y zu subtrahieren). Die Regressionsquadratsumme kann dann wie folgt berechnet werden: ssreg = sstotal - ssresid. Je kleiner die Residualsumme der Quadrate ist, desto größer ist der Wert des Determinismuskoeffizienten r2, der zeigt, wie gut die mit der Regressionsanalyse erhaltene Gleichung die Beziehung zwischen den Variablen erklärt. Der r2-Koeffizient ist ssreg / sstotal.

Formeln, die Arrays zurückgeben, müssen als Arrayformeln eingegeben werden.

RUND (y-Werte, x-Werte ^ SPALTE ($ A: $ C))

funktioniert mit einer Spalte mit Y-Werten und einer Spalte mit X-Werten, um eine Annäherung an einen Würfel (3. Grades Polynom) der folgenden Form zu berechnen:

y = m1x + m2x2 + m3x3 + b

Die Formel kann geändert werden, um andere Regressionsarten zu berechnen, aber in einigen Fällen sind Anpassungen der Ausgabewerte und anderer Statistiken erforderlich.

Die Regressionsgerade ist eine grafische Widerspiegelung der Beziehung zwischen Phänomenen. Sie können in Excel sehr anschaulich eine Regressionslinie erstellen.

Dafür braucht man:

1. Excel-Programm öffnen

2. Erstellen Sie Spalten mit Daten. In unserem Beispiel werden wir eine Regressionslinie oder Beziehung zwischen Aggressivität und Selbstzweifeln bei Erstklässlern aufbauen. An dem Experiment nahmen 30 Kinder teil, die Daten sind in der Excel-Tabelle dargestellt:

1 Spalte - Nr. des Themas

2 spaltig - Aggressivität in Punkten

3 Spalte - Selbstzweifel in Punkten

3. Dann müssen Sie beide Spalten auswählen (ohne den Namen der Spalte), klicken Sie auf die Registerkarte Einfügung , wählen Punkt , und wählen Sie aus den vorgeschlagenen Layouts das allererste aus Stelle mit Markierungen .

4.Also haben wir ein Leerzeichen für die Regressionsgerade - die sogenannte - Streudiagramm... Um zur Regressionslinie zu gelangen, müssen Sie auf die resultierende Zahl klicken und auf die Registerkarte klicken Konstrukteur, auf Panel finden Diagrammlayouts und wähle m aber ket9 , heißt es auch f(x)

5. Wir haben also eine Regressionsgerade. Der Graph zeigt auch seine Gleichung und das Quadrat des Korrelationskoeffizienten

6. Es bleibt noch der Name des Graphen, der Namen der Achsen hinzuzufügen. Wenn Sie möchten, können Sie auch die Legende entfernen, die Anzahl der horizontalen Rasterlinien reduzieren (Tab Layout , dann Netz ). Die wichtigsten Änderungen und Einstellungen werden in der Registerkarte vorgenommen Layout

Die Regressionslinie wurde in MS Excel erstellt. Jetzt kann es zum Text der Arbeit hinzugefügt werden.

Die Regressionsanalyse in Microsoft Excel ist am besten komplette Handbücherüber den Einsatz von MS Excel zur Lösung von Problemen der Regressionsanalyse im Bereich Business Intelligence. Konrad Karlberg erläutert verständlich die theoretischen Fragestellungen, deren Kenntnis Ihnen hilft, viele Fehler sowohl bei der eigenen Regressionsanalyse als auch bei der Auswertung fremder Analyseergebnisse zu vermeiden. Alle Materialien, von einfachen Korrelationen und t-Tests bis hin zu multiplen Kovarianzanalysen, basieren auf realen Beispielen und werden begleitet von detaillierte Beschreibung entsprechende Schritt-für-Schritt-Verfahren.

Das Buch diskutiert die Merkmale und Widersprüche, die mit verbunden sind Excel-Funktionen mit Regression zu arbeiten, untersucht die Implikationen jeder Option und jedes Arguments und erklärt, wie Regressionsmethoden in einer Vielzahl von Bereichen zuverlässig angewendet werden können, von der medizinischen Forschung bis zur Finanzanalyse.

Konrad Karlberg. Regressionsanalyse in Microsoft Excel. - M.: Dialektik, 2017.-- 400 S.

Laden Sie eine Notiz im Format oder Beispiele im Format herunter

Kapitel 1. Schätzung der Datenvariabilität

Den Statistikern stehen viele Variationsindikatoren (Variabilität) zur Verfügung. Eine davon ist die Summe der Quadrate der Abweichungen einzelner Werte vom Mittelwert. Excel verwendet dafür die Funktion SQUARE(). Aber Varianz wird häufiger verwendet. Die Varianz ist das mittlere Quadrat der Abweichungen. Die Varianz ist unempfindlich gegenüber der Anzahl der Werte im interessierenden Datensatz (während die Summe der quadrierten Abweichungen mit der Anzahl der Dimensionen zunimmt).

Excel bietet zwei Funktionen, die Varianz zurückgeben: VAR.G () und VAR.V ():

Verwenden Sie die Funktion DISP.G(), wenn die zu verarbeitenden Werte eine Population bilden. Das heißt, die im Bereich enthaltenen Werte sind die einzigen Werte, die Sie interessieren.
Verwenden Sie die Funktion VAR.In(), wenn die zu verarbeitenden Werte eine Stichprobe aus einer größeren Grundgesamtheit bilden. Es wird davon ausgegangen, dass es zusätzliche Werte gibt, deren Varianz Sie ebenfalls schätzen können.

Wenn eine Größe wie ein Mittelwert oder ein Korrelationskoeffizient basierend auf der Grundgesamtheit berechnet wird, wird dies als Parameter bezeichnet. Ein ähnlicher Wert, der anhand einer Stichprobe berechnet wird, wird als Statistik bezeichnet. Abweichungen auszählen vom Mittelwert In diesem Satz erhalten Sie die Summe der Quadrate der Abweichungen mit einer geringeren Größe, als wenn Sie sie von einem anderen Wert aus zählen würden. Eine ähnliche Aussage gilt für die Varianz.

Je größer der Stichprobenumfang, desto genauer ist der berechnete Statistikwert. Es gibt jedoch keine einzige Stichprobe, deren Größe kleiner ist als die der Gesamtbevölkerung, bei der Sie sicher sein können, dass der Wert der Statistik mit dem Wert des Parameters übereinstimmt.

Nehmen wir an, Sie haben einen Satz von 100 Wachstumswerten, deren Mittelwert vom Bevölkerungsmittelwert abweicht, so klein der Unterschied auch sein mag. Durch die Berechnung der Varianz für die Stichprobe erhalten Sie einen Wert, sagen wir 4. Dieser Wert ist kleiner als jeder andere, der durch Berechnung der Abweichung jedes der 100 Wachstumswerte relativ zu einem anderen Wert als dem Stichprobenmittelwert erhalten werden kann , einschließlich und relativ zum wahren Durchschnitt der Gesamtbevölkerung. Daher unterscheidet sich die berechnete Varianz in geringerem Maße von der Varianz, die Sie erhalten würden, wenn Sie irgendwie nicht den Stichprobenmittelwert, sondern einen Parameter der Gesamtbevölkerung kennen und verwenden würden.

Die mittlere Summe der Quadrate der Stichprobe ergibt die niedrigere Schätzung der Varianz der Grundgesamtheit. Die so berechnete Varianz heißt versetzt Bewertung. Es stellt sich heraus, dass es ausreicht, um die Abweichung zu beseitigen und eine unverzerrte Schätzung zu erhalten, die Summe der Quadrate der Abweichungen nicht durch . zu teilen n, wo n ist die Stichprobengröße, und auf n - 1.

Die Quantität n - 1 nennt man die Zahl (Anzahl) der Freiheitsgrade. Existieren verschiedene Wege Berechnung dieser Menge, obwohl sie alle entweder das Abziehen einer bestimmten Zahl von der Stichprobengröße oder das Zählen der Anzahl der Kategorien beinhalten, in die die Beobachtungen fallen.

Der wesentliche Unterschied zwischen den Funktionen DISP.G () und DISP.B () ist wie folgt:

Bei VARP.G() wird die Summe der Quadrate durch die Anzahl der Beobachtungen geteilt und stellt daher eine verzerrte Schätzung der Varianz dar, dem wahren Mittelwert.
In der VAR-Funktion.In() wird die Summe der Quadrate durch die Anzahl der Beobachtungen minus 1 dividiert, d.h. durch die Anzahl der Freiheitsgrade, was eine genauere, unverzerrte Schätzung der Varianz der Grundgesamtheit ergibt, aus der die Stichprobe entnommen wurde.

Standardabweichung (rus. Standardabweichung, SD) ist die Quadratwurzel der Varianz:

Beim Quadrieren von Abweichungen wird die Messskala in eine andere Metrik umgewandelt, die das Quadrat des Originals ist: Meter - in Quadratmeter, Dollar - in Quadrat-Dollar usw. Die Standardabweichung ist die Quadratwurzel der Varianz und führt uns daher zu unseren ursprünglichen Einheiten zurück. Was bequemer ist.

Es ist oft notwendig, die Standardabweichung nach einer gewissen Manipulation der Daten zu berechnen. Und obwohl die Ergebnisse in diesen Fällen zweifellos Standardabweichungen sind, werden sie normalerweise als . bezeichnet Standardfehler... Es gibt verschiedene Arten von Standardfehlern, einschließlich Standardfehler der Messung, Standardfehler des Anteils, Standardfehler des Mittelwerts.

Angenommen, Sie sammeln Größendaten von 25 zufällig ausgewählten erwachsenen Männern in jedem der 50 Bundesstaaten. Als nächstes berechnen Sie die durchschnittliche Größe der erwachsenen Männer in jedem Bundesstaat. Die erhaltenen 50 Durchschnittswerte wiederum können als Beobachtungen betrachtet werden. Daraus könnten Sie ihre Standardabweichung berechnen, die Standardfehler des Mittelwerts... Reis. 1.Vergleicht die Verteilung von 1.250 Basis-Einzelwerten (Daten zur Körpergröße von 25 Männern in jedem der 50 Bundesstaaten) mit der Verteilung der 50 Bundesstaatendurchschnitte. Formel zur Schätzung des Standardfehlers des Mittelwerts (d. h. die Standardabweichung des Mittelwerts, nicht einzelne Beobachtungen):

wo ist der Standardfehler des Mittelwerts; S- Standardabweichung der Baseline-Beobachtungen; n- die Anzahl der Beobachtungen in der Stichprobe.

Reis. 1. Die Streuung der Mittelwerte von Bundesland zu Bundesland ist deutlich geringer als die Streuung bei einzelnen Beobachtungen

In der Statistik besteht Einigkeit über die Verwendung griechischer und lateinischer Buchstaben zur Bezeichnung statistischer Größen. Es ist üblich, die Parameter der allgemeinen Bevölkerung in griechischen Buchstaben und die Stichprobenstatistiken in lateinischer Sprache zu bezeichnen. Wenn wir über die Standardabweichung der Gesamtbevölkerung sprechen, schreiben wir sie daher als σ; wenn die Standardabweichung der Stichprobe berücksichtigt wird, verwenden wir die Notation s. Was die Symbole zur Bezeichnung von Durchschnittswerten betrifft, so stimmen sie nicht gut miteinander überein. Der Bevölkerungsdurchschnitt wird mit dem griechischen Buchstaben μ bezeichnet. Das Symbol X̅ wird jedoch traditionell verwendet, um den Stichprobenmittelwert darzustellen.

Z-Score drückt die Position der Beobachtung in der Verteilung in Einheiten der Standardabweichung aus. Beispielsweise bedeutet z = 1,5, dass die Beobachtung 1,5 Standardabweichungen vom Mittelwert in Richtung höherer Werte beträgt. Begriff Z-Score für Einzelbewertungen verwendet, d.h. für Dimensionen, die einzelnen Artikeln in der Probe zugeordnet sind. Verwenden Sie für solche Statistiker (zum Beispiel den Landesdurchschnitt) den Begriff Z-Wert:

wobei X̅ der Mittelwert der Stichprobe ist, μ der Mittelwert der Gesamtbevölkerung ist, der Standardfehler der Mittelwerte der Stichprobenmenge ist:

wobei σ der Standardfehler der Gesamtbevölkerung ist (Einzelmessungen), n Ist die Stichprobengröße.

Nehmen wir an, Sie sind Lehrer in einem Golfclub. Sie haben schon lange die Möglichkeit, die Reichweite zu messen, und Sie wissen, dass der Durchschnitt 205 Yards beträgt und die Standardabweichung 36 Yards beträgt. Ihnen wurde ein neuer Schläger angeboten, der behauptet, Ihre Reichweite um 10 Yards zu erhöhen. Sie bitten jeden der nächsten 81 Clubbesucher, einen Testschlag mit einem neuen Schläger auszuprobieren und seine Reichweite aufzuzeichnen. Es stellte sich heraus, dass die durchschnittliche Schlagweite des neuen Clubs 215 Yards betrug. Wie groß ist die Wahrscheinlichkeit, dass die Differenz von 10 Yards (215 - 205) ausschließlich auf Stichprobenfehler zurückzuführen ist? Oder anders formuliert: Wie groß ist die Wahrscheinlichkeit, dass der neue Schläger bei größeren Tests keine Steigerung der Schlagweite über den bisherigen langjährigen Durchschnitt von 205 Yards hinaus zeigt?

Wir können dies testen, indem wir einen Z-Wert generieren. Standardfehler des Mittelwerts:

Dann ist der Z-Wert:

Wir müssen die Wahrscheinlichkeit ermitteln, dass der Stichprobenmittelwert 2,5σ vom Grundgesamtheitsmittelwert entfernt ist. Ist die Wahrscheinlichkeit gering, dann liegen die Unterschiede nicht am Zufall, sondern an der Qualität des neuen Vereins. Excel hat keine vorgefertigte Funktion zur Bestimmung der Wahrscheinlichkeit eines Z-Scores. Sie können jedoch die Formel = 1-NORM.ST.DIST (z-Wert; TRUE) verwenden, wobei NORM.ST.DIST () die Fläche unter der Normalkurve links vom z-Wert zurückgibt (Abbildung 2) .

Reis. 2. Die Funktion NORM.S.DIST() gibt die Fläche unter der Kurve links vom Z-Wert zurück; zum Vergrößern des Bildes darauf klicken Rechtsklick Maus und wählen Bild in einem neuen Tab öffnen

Das zweite Argument der Funktion NORM.ST.DIST () kann zwei Werte annehmen: TRUE - die Funktion gibt die Fläche der Fläche unter der Kurve links von dem durch das erste Argument angegebenen Punkt zurück; FALSE - Die Funktion gibt die Höhe der Kurve an dem durch das erste Argument angegebenen Punkt zurück.

Wenn Mittelwert (μ) und Standardabweichung (σ) der Grundgesamtheit nicht bekannt sind, wird der t-Wert verwendet (siehe Details). Die z- und t-Wert-Strukturen unterscheiden sich darin, dass die aus den Stichprobenergebnissen erhaltene Standardabweichung s verwendet wird, um den t-Wert zu finden, und nicht der bekannte Wert des Populationsparameters σ. Die Normalkurve hat eine einzige Form, und die Form der Verteilung der t-Werte variiert je nach Anzahl der Freiheitsgrade df (aus dem Englischen. Freiheitsgrade) der Probe, die es repräsentiert. Die Anzahl der Freiheitsgrade der Probe ist n - 1, wo n- Stichprobengröße (Abb. 3).

Reis. 3. Die Form der t-Verteilungen, die in Fällen auftreten, in denen der Parameter σ unbekannt ist, unterscheidet sich von der Form der Normalverteilung

Excel hat zwei Funktionen für die t-Verteilung, auch Student's t-Verteilung genannt: STUDENT.DIST () gibt die Fläche unter der Kurve links vom angegebenen t-Wert zurück und STUDENT.DIST.PX () gibt die Fläche zurück unter der Kurve nach rechts.

Kapitel 2. Korrelation

Korrelation ist ein Maß für die Beziehung zwischen den Elementen einer Menge geordneter Paare. Korrelation ist gekennzeichnet durch Korrelationskoeffizienten nach Pearson- R. Der Koeffizient kann Werte im Bereich von –1,0 bis +1,0 annehmen.

wo S x und S y- Standardabweichungen der Variablen NS und Ja, S xy- Kovarianz:

In dieser Formel wird die Kovarianz durch die Standardabweichungen der Variablen geteilt NS und Ja wodurch die einheitenbezogenen Skalierungseffekte aus der Kovarianz entfernt werden. Excel verwendet die Funktion CORREL(). Der Name dieser Funktion enthält nicht die qualifizierenden Elemente Г und В, die in den Namen von Funktionen wie STDEV (), DISP () oder COVARIATION () verwendet werden. Obwohl der Korrelationskoeffizient für die Stichprobe eine verzerrte Schätzung ist, ist der Grund für die Verzerrung ein anderer als bei Varianz oder Standardabweichung.

Abhängig vom Wert des allgemeinen Korrelationskoeffizienten (oft mit dem griechischen Buchstaben . bezeichnet) ρ ), Korrelationskoeffizient R liefert eine verzerrte Schätzung, wobei der Effekt der Verzerrung mit abnehmender Stichprobengröße zunimmt. Dennoch versuchen wir diesen Bias nicht so zu korrigieren, wie wir es beispielsweise bei der Berechnung der Standardabweichung getan haben, indem wir nicht die Anzahl der Beobachtungen, sondern die Anzahl der Freiheitsgrade in die entsprechende Formel eingesetzt haben. In Wirklichkeit hat die Anzahl der Beobachtungen, die zur Berechnung der Kovarianz verwendet werden, keinen Einfluss auf die Größe.

Der Standardkorrelationskoeffizient ist für die Verwendung mit linear verwandten Variablen vorgesehen. Das Vorhandensein von Nichtlinearitäten und / oder Fehlern in den Daten (Ausreißer) führen zu einer falschen Berechnung des Korrelationskoeffizienten. Streudiagramme werden für die Diagnose von Datenproblemen empfohlen. Es ist der einzige Diagrammtyp in Excel, der sowohl die horizontale als auch die vertikale Achse als Wertachsen behandelt. Ein Liniendiagramm hingegen definiert eine der Säulen als Kategorienachse, was das Bild der Daten verzerrt (Abb. 4).

Reis. 4. Regressionslinien scheinen gleich zu sein, aber vergleichen Sie ihre Gleichungen

Die zum Zeichnen des Liniendiagramms verwendeten Beobachtungen sind entlang der horizontalen Achse äquidistant. Die Teilstriche entlang dieser Achse sind nur Beschriftungen, keine numerischen Werte.

Während Korrelation oft Kausalität bedeutet, kann sie nicht als Beweis dafür verwendet werden. Statistiken werden nicht verwendet, um zu zeigen, ob eine Theorie wahr oder falsch ist. Um konkurrierende Erklärungen der Beobachtungsergebnisse auszuschließen, geplante Experimente... Statistiken werden verwendet, um die im Verlauf solcher Experimente gesammelten Informationen zusammenzufassen und die Wahrscheinlichkeit zu quantifizieren, dass eine getroffene Entscheidung angesichts der verfügbaren Evidenzbasis falsch sein könnte.

Kapitel 3. Einfache Regression

Wenn zwei Variablen miteinander in Beziehung stehen, so dass der Wert des Korrelationskoeffizienten beispielsweise 0,5 überschreitet, kann in diesem Fall (mit einiger Genauigkeit) der unbekannte Wert einer Variablen aus dem bekannten Wert der anderen vorhergesagt werden . Um prognostizierte Preiswerte basierend auf den in Abb. 5, jeder von mehreren kann verwendet werden mögliche Wege, aber Sie werden mit ziemlicher Sicherheit nicht die in Abb. 5. Sie sollten es aber kennen, denn keine andere Methode kann den Zusammenhang zwischen Korrelation und Vorhersage so deutlich aufzeigen wie diese. In Abb. 5, im Bereich B2: C12, wird eine Zufallsstichprobe von zehn Wohnungen präsentiert und die Wohnfläche (in Quadratfuß) jeder Wohnung und ihr Verkaufspreis angegeben.

Reis. 5. Prognostizierte Verkaufspreiswerte bilden eine Gerade

Ermitteln Sie Mittelwerte, Standardabweichungen und Korrelationskoeffizienten (Bereich A14: C18). Berechnen Sie die Z-Scores für die Fläche (E2: E12). Zelle EZ enthält beispielsweise die Formel: = (B3- $ B $ 14) / $ B $ 15. Berechnen Sie die Z-Scores des prognostizierten Preises (F2: F12). Zelle F3 enthält beispielsweise die Formel: = EZ * $ B $ 18. Konvertieren Sie Z-Scores in Dollarkurse (H2: H12). In Zelle НЗ lautet die Formel: = F3 * $ C $ 15 + $ C $ 14.

Bitte beachten Sie, dass sich der vorhergesagte Wert immer in Richtung des Mittelwerts von 0 bewegt. Je näher der Korrelationskoeffizient an Null ist, desto näher an Null ist der vorhergesagte Z-Score. In unserem Beispiel beträgt der Korrelationskoeffizient zwischen der Fläche und dem Verkaufspreis 0,67 und der prognostizierte Preis beträgt 1,0 * 0,67, d.h. 0,67. Dies entspricht dem Überschuss des Wertes über den Mittelwert, gleich zwei Drittel der Standardabweichung. Wenn der Korrelationskoeffizient gleich 0,5 wäre, wäre der vorhergesagte Preis 1,0 * 0,5, d.h. 0,5. Dies entspricht dem Überschuss des Wertes über den Mittelwert, der nur der halben Standardabweichung entspricht. Immer wenn der Wert des Korrelationskoeffizienten vom Ideal abweicht, d.h. größer als -1,0 und kleiner als 1,0 ist, sollte die Schätzung der vorhergesagten Variablen näher an ihrem Mittelwert liegen als die Schätzung der (unabhängigen) Prädiktorvariablen an ihrer eigenen. Dieses Phänomen wird als Regression zum Mittelwert oder einfach als Regression bezeichnet.

Excel verfügt über mehrere Funktionen zur Bestimmung der Koeffizienten der Regressionsliniengleichung (in Excel wird sie als Trendlinie bezeichnet) y =kx + B... Zur Bestimmung k dient Funktion

= SLOPE (bekannt_y's, bekannt_x's)

Hier bei Ist die vorhergesagte Variable und NS Ist die unabhängige Variable. Sie müssen diese Reihenfolge der Variablen strikt einhalten. Die Regressionssteigung, der Korrelationskoeffizient, die Standardabweichungen der Variablen und die Kovarianz hängen eng zusammen (Abbildung 6). INTERCEPT () gibt den von der Regressionslinie auf der vertikalen Achse abgeschnittenen Wert zurück:

= INTERCEPT (bekannte_y, bekannte_x)

Reis. 6. Das Verhältnis zwischen den Standardabweichungen wandelt die Kovarianz in den Korrelationskoeffizienten und die Steigung der Regressionsgeraden um

Beachten Sie, dass die Anzahl der x- und y-Werte, die den Funktionen SLOPE () und INTERCEPT () als Argumente bereitgestellt werden, gleich sein muss.

Bei der Regressionsanalyse wird ein weiterer wichtiger Indikator verwendet - R 2 (R-Quadrat) oder das Bestimmtheitsmaß. Es bestimmt, wie die Beziehung zwischen NS und bei... Excel verfügt dafür über die Funktion KVPIRSON(), die genau die gleichen Argumente wie die Funktion CORREL() verwendet.

Zwei Variablen mit einem Korrelationskoeffizienten ungleich Null zwischen ihnen sollen die Varianz erklären oder eine erklärte Varianz haben. Üblicherweise wird die erklärte Varianz in Prozent ausgedrückt. So R 2 = 0,81 bedeutet, dass 81% der Varianz (Spread) der beiden Variablen erklärt werden. Die restlichen 19% sind auf zufällige Schwankungen zurückzuführen.

Excel verfügt über eine TREND-Funktion, die Berechnungen erleichtert. TREND()-Funktion:

nimmt die bekannten Werte die du angibst NS und bekannte Werte bei;
berechnet die Steigung der Regressionsgerade und eine Konstante (Segment);
gibt vorhergesagte Werte zurück bei bestimmt durch Anwendung der Regressionsgleichung auf die bekannten Werte NS(Abb. 7).

Die Funktion TREND() ist eine Array-Funktion (wenn Sie solche Funktionen noch nicht kennen, empfehle ich sie).

Reis. 7. Die Verwendung der Funktion TREND () ermöglicht Ihnen, Berechnungen zu beschleunigen und zu vereinfachen, verglichen mit der Verwendung von TILT () und INTERCEPT () Funktionen

Um die TREND()-Funktion als Matrixformel in die Zellen G3: G12 einzugeben, wählen Sie den Bereich G3: G12, geben Sie die TREND-Formel ein (SZ: C12; OZ: B12), drücken und halten Sie die Tasten und erst dann die Taste drücken ... Beachten Sie, dass die Formel in geschweifte Klammern eingeschlossen ist: (und). So informiert Excel Sie, dass die angegebene Formel als Matrixformel interpretiert wird. Geben Sie die Klammern nicht selbst ein: Wenn Sie versuchen, sie selbst als Teil einer Formel einzugeben, interpretiert Excel Ihre Eingabe als normalen Textstring.

Die Funktion TREND() hat zwei weitere Argumente: new_x's und const... Ersteres ermöglicht Ihnen eine Prognose für die Zukunft, während letzteres die Regressionsgerade durch den Ursprung erzwingen kann (TRUE weist Excel an, eine berechnete Konstante zu verwenden, FALSE weist Excel an, eine Konstante = 0 zu verwenden). Excel ermöglicht es Ihnen, eine Regressionslinie in einem Diagramm zu zeichnen, sodass sie durch den Ursprung verläuft. Beginnen Sie mit dem Zeichnen eines Streudiagramms und klicken Sie dann mit der rechten Maustaste auf einen der Datenreihenmarker. Wählen Sie im geöffneten Kontextmenü Absatz Trendlinie hinzufügen; wähle eine Option Linear; Scrollen Sie ggf. im Panel nach unten, aktivieren Sie das Kontrollkästchen Kreuzung konfigurieren; Stellen Sie sicher, dass das zugehörige Textfeld auf 0.0 eingestellt ist.

Wenn Sie drei Variablen haben und die Korrelation zwischen zwei von ihnen bestimmen möchten, ohne den Einfluss der dritten auszuschließen, können Sie verwenden Teilkorrelation... Angenommen, Sie interessieren sich für die Beziehung zwischen dem Prozentsatz der Stadtbewohner mit Hochschulabschluss und der Anzahl der Bücher in den Stadtbibliotheken. Sie haben Daten für 50 Städte gesammelt, aber ... Das Problem ist, dass beide Parameter vom Wohlbefinden der Einwohner einer bestimmten Stadt abhängen können. Natürlich ist es sehr schwierig, die anderen 50 Städte mit genau dem gleichen Wohlbefinden zu finden.

Durch den Einsatz statistischer Verfahren, um die Auswirkungen des Wohlergehens sowohl auf die finanzielle Unterstützung von Bibliotheken als auch auf die Verfügbarkeit von Hochschulen auszuschließen, könnten Sie die Beziehung zwischen den interessierenden Variablen, nämlich der Anzahl der Bücher und der Anzahl der Absolventen, besser quantifizieren. Diese bedingte Korrelation zwischen zwei Variablen, wenn die Werte der anderen Variablen festgelegt sind, wird als Teilkorrelation bezeichnet. Eine Möglichkeit, es zu berechnen, besteht darin, die Gleichung zu verwenden:

Wo RCB . W- der Korrelationskoeffizient zwischen den Variablen College und Books mit dem ausgeschlossenen Einfluss (fester Wert) der Variablen Reichtum; RCB- der Korrelationskoeffizient zwischen den Variablen College und Books; RCW ist der Korrelationskoeffizient zwischen den Variablen Hochschule und Wohlfahrt; RBW ist der Korrelationskoeffizient zwischen den Variablen Bücher und Wohlfahrt.

Andererseits kann die Teilkorrelation basierend auf der Analyse von Residuen berechnet werden, d.h. Unterschiede zwischen den vorhergesagten Werten und den zugehörigen Ergebnissen der tatsächlichen Beobachtungen (beide Methoden sind in Abb. 8 dargestellt).

Reis. 8. Partielle Korrelation als Korrelation von Residuen

Um die Berechnung der Korrelationskoeffizientenmatrix (B16: E19) zu vereinfachen, verwenden Sie das Excel-Analysepaket (Menü Daten –> Analyse –> Datenanalyse). Standardmäßig ist dieses Paket in Excel nicht aktiv. Um es zu installieren, gehen Sie durch das Menü Datei –> Parameter –> Add-ons... Am unteren Rand des geöffneten Fensters ParameterExcel finde das Feld Steuerung, auswählen Add-onsExcel, klicken Gehe zu... Aktivieren Sie das Kontrollkästchen neben dem Add-on Analysepaket... Klicken Sie auf A Datenanalyse, Wähle eine Option Korrelation... Geben Sie $ B $ 2 ein: $ D $ 13 als Eingabebereich, aktivieren Sie das Kontrollkästchen Etiketten in der ersten Zeile, geben Sie $ B $ 16: $ E $ 19 als Ausgabeintervall ein.

Eine andere Möglichkeit besteht darin, eine halbprivate Korrelation zu definieren. Sie erforschen zum Beispiel den Einfluss von Größe und Alter auf das Gewicht. Sie haben also zwei Prädiktorvariablen, Größe und Alter, und eine Prädiktorvariable, Gewicht. Sie möchten den Einfluss einer Prädiktorvariablen auf eine andere eliminieren, aber nicht auf die vorhergesagte Variable:

wobei H die Höhe ist, W das Gewicht ist, A das Alter ist; im Index des halbprivaten Korrelationskoeffizienten werden Klammern verwendet, um anzugeben, welche Variable von welcher Variable entfernt wird. In diesem Fall zeigt die Notation W (NA) an, dass der Effekt der Variablen Alter von der Variablen Höhe, nicht aber von der Variablen Gewicht entfernt wird.

Man könnte den Eindruck gewinnen, dass das diskutierte Thema nicht wesentlich ist. Denn das Wichtigste ist, wie genau die allgemeine Regressionsgleichung funktioniert, während das Problem der relativen Beiträge einzelner Variablen zur gesamten erklärten Varianz von untergeordneter Bedeutung zu sein scheint. Dies ist jedoch nicht der Fall. Sobald Sie sich fragen, ob Sie überhaupt eine Variable in Ihrer multiplen Regressionsgleichung verwenden sollten, wird das Problem wichtig. Sie kann die Beurteilung der Richtigkeit der Wahl des Modells für die Analyse beeinflussen.

Kapitel 4. LINEST()-Funktion

LINEST () gibt 10 Regressionsstatistiken zurück. LINEST() ist eine Array-Funktion. Um es einzugeben, wählen Sie einen Bereich mit fünf Zeilen und zwei Spalten aus, geben Sie die Formel ein und drücken Sie (Abb. 9):

RUND (B2: B21; A2: A21; WAHR; WAHR)

Reis. 9. Funktion RUND (): a) wählen Sie den Bereich D2: E6, b) geben Sie die Formel wie in der Formelleiste angezeigt ein, c) drücken Sie

LINEST () gibt Folgendes zurück:

Regressionskoeffizient (oder Steigung, Zelle D2);
Segment (oder Konstante, Zelle E3);
Standardfehler des Regressionskoeffizienten und der Konstanten (Bereich D3: E3);
Bestimmtheitsmaß R 2 für die Regression (Zelle D4);
Standardfehler der Schätzung (Zelle E4);
F-Test auf vollständige Regression (Zelle D5);
die Anzahl der Freiheitsgrade für die Restquadratsumme (Zelle E5);
Regressionssumme der Quadrate (Zelle D6);
Restsumme der Quadrate (Zelle E6).

Werfen wir einen Blick auf jede dieser Statistiken und wie sie interagieren.

Standart Fehler in unserem Fall ist es die für Stichprobenfehler berechnete Standardabweichung. Das heißt, dies ist eine Situation, in der die allgemeine Bevölkerung eine Statistik hat und die Stichprobe eine andere hat. Dividiert man den Regressionskoeffizienten durch den Standardfehler, erhält man 2,092 / 0,818 = 2,559. Mit anderen Worten, ein Regressionskoeffizient von 2,092 ist zweieinhalb Standardfehler von Null entfernt.

Wenn der Regressionskoeffizient null ist, ist der Mittelwert der vorhergesagten Variablen die beste Schätzung. Zweieinhalb Standardfehler sind eine ziemlich große Zahl, und Sie können sicher davon ausgehen, dass der Regressionskoeffizient für die Grundgesamtheit ungleich Null ist.

Mit der Funktion

STUDENT.DIST.RF (t-Test = 2.559; Anzahl der Freiheitsgrade = 18)

Im Allgemeinen ist die Anzahl der Freiheitsgrade = n – k – 1, wobei n die Anzahl der Beobachtungen und k die Anzahl der Prädiktorvariablen ist.

Diese Formel gibt 0,00987 oder 1% gerundet zurück. Es sagt uns, dass bei einem Bevövon 0 % die Wahrscheinlichkeit, eine Stichprobe von 20 Personen zu erhalten, für die der geschätzte Regressionskoeffizient 2,092 beträgt, bescheidene 1 % beträgt.

Der F-Test (Zelle D5 in Abbildung 9) führt die gleiche Funktion bezüglich der vollständigen Regression aus wie der t-Test bezüglich des Koeffizienten der einfachen paarweisen Regression. Der F-Test wird verwendet, um zu testen, ob das Bestimmtheitsmaß R 2 für die Regression groß genug ist, um die Hypothese zu verwerfen, dass es in der Allgemeinbevölkerung einen Wert von 0,0 hat, was auf das Fehlen von Varianz hinweist, die durch den Prädiktor und die vorhergesagte Variable erklärt wird . Wenn es nur eine Prädiktorvariable gibt, ist der F-Test genau gleich dem Quadrat des t-Tests.

Bisher haben wir uns Intervallvariablen angesehen. Wenn Sie Variablen haben, die mehrere Werte annehmen können, die einfache Namen darstellen, zum Beispiel Mann und Frau oder Reptil, Amphibie und Fisch, stellen Sie sie als numerischen Code dar. Solche Variablen werden nominal genannt.

Statistik R 2 quantifiziert den Anteil der erklärten Varianz.

Standardfehler der Schätzung. In Abb. 4.9 zeigt die vorhergesagten Werte der Variablen Gewicht, die auf der Grundlage ihrer Beziehung zur Variablen Höhe erhalten wurden. Der Bereich E2: E21 enthält die Restwerte für die Variable Gewicht. Genauer gesagt werden diese Residuen Fehler genannt - daher der Begriff Standardfehler der Schätzung.

Reis. 10. Sowohl R 2 als auch der Standardfehler der Schätzung drücken die Genauigkeit der durch die Regression erhaltenen Vorhersagen aus

Je kleiner der Standardfehler der Schätzung ist, desto genauer ist die Regressionsgleichung und desto genauer stimmt jede Vorhersage mit der Gleichung mit der tatsächlichen Beobachtung überein, die Sie erwarten. Der Standardfehler einer Schätzung bietet eine Möglichkeit, diese Erwartungen zu quantifizieren. Das Gewicht von 95% der Menschen mit einer bestimmten Körpergröße liegt im Bereich:

(Höhe * 2.092 - 3.591) ± 2.092 * 21.118

F-Statistik Ist das Verhältnis der Varianz zwischen den Gruppen und der Varianz zwischen den Gruppen. Dieser Name wurde vom Statistiker George Snedecor zu Ehren von Sir geprägt, der Anfang des 20. Jahrhunderts die Varianzanalyse (ANOVA) entwickelte.

Das Bestimmtheitsmaß R 2 drückt den Anteil der Gesamtsumme der Quadrate aus, die mit der Regression verbunden sind. Der Wert (1 - R 2) drückt den Bruchteil der Gesamtsumme der Quadrate aus, die Residuen zugeordnet sind - Vorhersagefehler. Der F-Test kann unter Verwendung von REST (Zelle F5 in Abbildung 11), unter Verwendung von Quadratsummen (Bereich G10: J11) unter Verwendung von Varianzbrüchen (Bereich G14: J15) erhalten werden. Die Formeln können in der beigefügten Excel-Datei eingesehen werden.

Reis. 11. Berechnung des F-Kriteriums

Bei der Verwendung von Nominalvariablen wird eine Dummy-Codierung verwendet (Abb. 12). Zum Codieren von Werten ist es praktisch, die Werte 0 und 1 zu verwenden. Die Wahrscheinlichkeit F wird mit der Funktion berechnet:

F.DIST.RF (K2; I2; I3)

Hier gibt die Funktion F.VERT.RT () die Wahrscheinlichkeit zurück, ein F-Kriterium zu erhalten, das der zentralen F-Verteilung (Abbildung 13) für zwei Datensätze mit den in den Zellen I2 und I3 angegebenen Freiheitsgraden entspricht, deren Wert gleich ist als Wert in Zelle K2 angegeben.

Reis. 12. Regressionsanalyse mit Dummy-Variablen

Reis. 13. Zentrale F-Verteilung bei λ = 0

Kapitel 5. Multiple Regression

Wenn Sie von einer einfachen paarweisen Regression mit einer Prädiktorvariablen zu einer multiplen Regression wechseln, fügen Sie eine oder mehrere Prädiktorvariablen hinzu. Speichern Sie die Werte von Prädiktorvariablen in benachbarten Spalten, beispielsweise Spalten A und B für zwei Prädiktoren oder A, B und C für drei Prädiktoren. Bevor Sie eine Formel eingeben, die die Funktion LINEST() enthält, wählen Sie fünf Zeilen und so viele Spalten aus, wie Prädiktorvariablen vorhanden sind, plus eine weitere für die Konstante. Bei einer Regression mit zwei Prädiktorvariablen kann folgende Struktur verwendet werden:

RUND (A2: A41; B2: C41 ;; WAHR)

Ebenso bei drei Variablen:

RUND (A2: A61; B2: D61 ;; WAHR)

Angenommen, Sie möchten die möglichen Auswirkungen von Alter und Ernährung auf LDL untersuchen, ein Lipoprotein geringer Dichte, von dem angenommen wird, dass es für die atherosklerotische Plaque verantwortlich ist, die Atherothrombose verursacht (Abbildung 14).

Reis. vierzehn. Multiple Regression

Das R 2 der multiplen Regression (wie in Zelle F13 widergespiegelt) ist größer als das R 2 jeder einfachen Regression (E4, H4). Bei der multiplen Regression werden mehrere Prädiktorvariablen gleichzeitig verwendet. In diesem Fall nimmt R 2 fast immer zu.

Für jeden einfachen Lineargleichung Regressionen mit einer Prädiktorvariablen zwischen den vorhergesagten Werten und den Prädiktorvariablenwerten haben immer eine perfekte Korrelation, da in einer solchen Gleichung die Prädiktorwerte mit einer Konstanten multipliziert werden und zu jedem Produkt eine andere Konstante hinzugefügt wird. Dieser Effekt bleibt bei multipler Regression nicht bestehen.

Zeigt die von LINEST () zurückgegebenen Ergebnisse für die multiple Regression an (Abbildung 15). Regressionskoeffizienten werden als Teil der von LINEST () zurückgegebenen Ergebnisse angezeigt. in umgekehrter Reihenfolge der Variablen(G – H – I entspricht C – B – A).

Reis. 15. Die Koeffizienten und ihre Standardfehler werden in angezeigt umgekehrte Reihenfolge ihre Folgen auf dem Arbeitsblatt

Die Prinzipien und Verfahren, die bei der Regressionsanalyse mit einer Prädiktorvariablen verwendet werden, können leicht angepasst werden, um mehrere Prädiktorvariablen zu berücksichtigen. Es stellt sich heraus, dass ein Großteil dieser Anpassung davon abhängt, den Einfluss der Prädiktorvariablen aufeinander zu eliminieren. Letztere ist mit partiellen und halbprivaten Korrelationen verbunden (Abb. 16).

Reis. 16. Multiple Regression kann durch paarweise Regression von Residuen ausgedrückt werden (siehe Formeln in Excel-Datei)

In Excel gibt es Funktionen, die Auskunft über T- und F-Verteilungen geben. Funktionen, deren Namen einen Teil eines DIST enthalten, wie STUDENT.DIST () und F.DIST (), nehmen einen t- oder F-Test als Argument und geben die Wahrscheinlichkeit zurück, den angegebenen Wert zu sehen. Funktionen, deren Namen einen Teil von OBR enthalten, wie STUDENT.OBR () und F.OBR (), nehmen einen Wahrscheinlichkeitswert als Argument und geben einen der angegebenen Wahrscheinlichkeit entsprechenden Kriteriumswert zurück.

Da wir nach den kritischen Werten der t-Verteilung suchen, die die Kanten ihrer Schwanzregionen abschneidet, übergeben wir 5% als Argument an eine der STUDENT.OBR()-Funktionen, die einen entsprechenden Wert zurückliefert Wahrscheinlichkeit (Abb. 17, 18).

Reis. 17. Zweiseitiger t-Test

Reis. 18. Einseitiger t-Test

Durch das Aufstellen einer Entscheidungsregel im Fall einer einseitigen Alpha-Region erhöhen Sie die statistische Aussagekraft des Tests. Wenn Sie zu Beginn Ihres Experiments sicher sind, dass Sie allen Grund haben, einen positiven (oder negativen) Regressionskoeffizienten zu erwarten, sollten Sie einen einseitigen Test durchführen. In diesem Fall ist die Wahrscheinlichkeit, dass Sie die richtige Entscheidung, die die Hypothese eines Null-Regressionskoeffizienten in der Allgemeinbevölkerung ablehnt, höher ausfallen.

Statistiker verwenden lieber den Begriff Richtungstest statt des Begriffs einseitiger Test und der Begriff ungerichteter Test statt des Begriffs doppelseitiger Test... Die Begriffe gerichtet und ungerichtet werden bevorzugt, da sie eher die Art der Hypothese als die Natur der Verteilungsenden betonen.

Ein modellbasierter Ansatz zur Bewertung des Einflusses von Prädiktoren. In Abb. 19 zeigt die Ergebnisse einer Regressionsanalyse, die den Beitrag der Diet-Variablen zur Regressionsgleichung testet.

Reis. 19. Vergleich der beiden Modelle durch Überprüfung der Unterschiede in ihren Ergebnissen

Die LINEST()-Ergebnisse (Bereich H2: K6) beziehen sich auf das, was ich das vollständige Modell nenne, das LDL über Ernährung, Alter und HDL regressiert. Im Bereich H9: J13 werden Berechnungen ohne Berücksichtigung der Prädiktorvariablen Ernährung dargestellt. Ich nenne das das limitierte Modell. Im vollständigen Modell werden 49,2 % der Varianz der LDL-abhängigen Variablen durch die Prädiktorvariablen erklärt. Im limitierten Modell werden nur 30,8% des LDL durch die Variablen Alter und HDL erklärt. Der Verlust von R 2 aufgrund des Ausschlusses der Ernährungsvariablen aus dem Modell beträgt 0,183. Im Bereich G15: L17 werden Berechnungen durchgeführt, die zeigen, dass der Einfluss der Variable Ernährung nur mit einer Wahrscheinlichkeit von 0,0288 zufällig ist. Bei den restlichen 97,1 % hat die Ernährung einen Einfluss auf das LDL.

Kapitel 6. Annahmen und Vorbehalte der Regressionsanalyse

Der Begriff „Annahme“ ist nicht streng definiert und seine Verwendung lässt vermuten, dass die Ergebnisse der gesamten Analyse zumindest fragwürdig oder möglicherweise ungültig sind, wenn die Annahme nicht erfüllt ist. Tatsächlich ist dies nicht der Fall, obwohl es natürlich Fälle gibt, in denen eine Verletzung der Annahme das Bild radikal verändert. Grundannahmen: a) die Residuen der Y-Variablen sind an jedem Punkt X entlang der Regressionsgerade normalverteilt; b) Y-Werte sind in lineare Beziehung aus X-Werten; c) die Varianz der Reste ist an jedem Punkt X ungefähr gleich; d) es besteht keine Abhängigkeit zwischen den Resten.

Wenn Annahmen keine wesentliche Rolle spielen, sprechen Statistiker von der Robustheit der Analyse in Bezug auf die Verletzung der Annahme. Insbesondere wenn Sie die Regression verwenden, um auf Unterschiede zwischen den Gruppenmittelwerten zu testen, ist die Annahme, dass die Y-Werte – und damit die Residuen – normalverteilt sind, nicht wesentlich: Die Tests sind robust gegen das Brechen der Normalitätsannahme. Allerdings ist es wichtig, die Daten mithilfe von Diagrammen zu analysieren. Zum Beispiel im Add-on enthalten Datenanalyse Werkzeug Rückschritt.

Wenn Ihre Daten nicht den Annahmen der linearen Regression entsprechen, haben Sie andere Ansätze als die lineare Regression. Eine davon ist die logistische Regression (Abbildung 20). Nahe der oberen und unteren Grenze der Prädiktorvariablen führt die lineare Regression zu unrealistischen Vorhersagen.

Reis. 20. Logistische Regression

In Abb. 6.8 zeigt die Ergebnisse zweier Methoden der Datenanalyse, die darauf abzielen, den Zusammenhang zwischen dem Jahreseinkommen und der Wahrscheinlichkeit eines Eigenheimkaufs zu untersuchen. Natürlich steigt die Kaufwahrscheinlichkeit mit steigendem Einkommen. Anhand von Diagrammen können Sie leicht den Unterschied zwischen den Ergebnissen erkennen, die die Wahrscheinlichkeit des Kaufs eines Eigenheims mithilfe der linearen Regression vorhersagen, und den Ergebnissen, die Sie möglicherweise mit einem anderen Ansatz erhalten.

In der Sprache der Statistiker wird das Verwerfen einer Nullhypothese, wenn sie tatsächlich wahr ist, als Fehler vom Typ I bezeichnet.

Im Aufbau Datenanalyse ein praktisches Werkzeug zum Generieren zufällige Zahlen, mit dem der Benutzer die gewünschte Form der Verteilung (z. B. Normal, Binomial oder Poisson) sowie den Mittelwert und die Standardabweichung angeben kann.

Unterschiede zwischen den Funktionen der STUDENT.DIST ()-Familie. Ab Excel 2010 gibt es drei verschiedene Formen eine Funktion, die den Anteil der Verteilung links und / oder rechts von einem gegebenen t-Testwert zurückgibt. Die Funktion STUDENT.VERT () gibt den Bruchteil der Fläche unter der Verteilungskurve links vom angegebenen t-Testwert zurück. Angenommen, Sie haben 36 Fälle, die Anzahl der zu analysierenden Freiheitsgrade beträgt also 34 und der t-Test beträgt 1,69. In diesem Fall ist die Formel

STUDENT DIST (+1,69; 34; WAHR)

gibt 0,05 oder 5 % zurück (Abbildung 21). Das dritte Argument der Funktion STUDENT.DIST() kann TRUE oder FALSE sein. Wenn auf TRUE gesetzt, gibt die Funktion die kumulierte Fläche unter der Kurve links vom angegebenen t-Test, ausgedrückt als Bruch, zurück. Bei FALSE gibt die Funktion die relative Höhe der Kurve an dem Punkt zurück, der den t-Test erfüllt. Andere Versionen der Funktion STUDENT.DIST () - STUDENT.DIST.PX () und STUDENT.DIST.2X () - nehmen als Argumente nur den Wert des t-Kriteriums und die Anzahl der Freiheitsgrade und erfordern keine Angabe das dritte Argument.

Reis. 21. Der dunklere Bereich im linken Rand der Verteilung entspricht dem Anteil der Fläche unter der Kurve links vom großen positiven t-Test-Wert

Um die Fläche rechts vom t-Kriterium zu bestimmen, verwenden Sie eine der Formeln:

1 - SHOODENT DIST (1, 69; 34; WAHR)

STUDENT.VERZ.PH (1.69; 34)

Die gesamte Fläche unter der Kurve muss 100 % betragen, also subtrahiert man von 1 den Bruchteil der Fläche links vom t-Test, den die Funktion zurückgibt, ergibt den Bruchteil der Fläche rechts vom t-Test. Möglicherweise empfiehlt es sich, den Flächenanteil, an dem Sie interessiert sind, direkt mit der Funktion STUDENT.DIST.RX () zu ermitteln, wobei RH für den rechten Rand der Verteilung steht (Abb. 22).

Reis. 22,5% Alpha-Bereich für Richtungstests

Die Verwendung der Funktionen STUDENT.DIST () oder STUDENT.DIST.PX () setzt voraus, dass Sie eine gerichtete Arbeitshypothese gewählt haben. Eine gerichtete Arbeitshypothese in Kombination mit einer Alpha-Einstellung von 5 % bedeutet, dass Sie alle 5 % am rechten Ende der Verteilungen platzieren. Sie müssen die Nullhypothese nur verwerfen, wenn die Wahrscheinlichkeit Ihres t-Test-Werts 5 % oder weniger beträgt. Richtungshypothesen führen in der Regel zu sensitiveren statistischen Tests (diese höhere Sensitivität wird auch als größere statistische Power bezeichnet).

Bei einem ungerichteten Test bleibt der Alpha-Wert auf dem gleichen 5%-Niveau, aber die Verteilung ist anders. Da Sie zwei Ergebnisse berücksichtigen müssen, muss die Wahrscheinlichkeit eines falsch positiven Ergebnisses auf die beiden Enden der Verteilung verteilt werden. Es wird allgemein akzeptiert, diese Wahrscheinlichkeit gleichmäßig zu verteilen (Abb. 23).

Verwenden Sie die Formel

STUDENT.ABW. 2X (1,69; 34)

STUDENT.DIST.2X() gibt ohne besonderen Grund den #NUM!-Fehlercode zurück, wenn als erstes Argument ein negativer t-Test angegeben wird.

Wenn die Stichproben unterschiedliche Datenmengen enthalten, verwenden Sie den t-Test mit zwei Stichproben mit unterschiedlichen Varianzen, die im Paket enthalten sind Datenanalyse.

Kapitel 7. Verwenden der Regression zum Testen von Unterschieden zwischen Gruppenmitteln

Variablen, die zuvor als vorhergesagte Variablen bezeichnet wurden, werden in diesem Kapitel als Ergebnisvariablen bezeichnet, und der Begriff Faktorvariablen wird anstelle von Prädiktorvariablen verwendet.

Der einfachste Ansatz zum Codieren einer Nominalvariablen ist Dummy-Codierung(Abb. 24).

Reis. 24. Regressionsanalyse basierend auf Dummy-Codierung

Bei der Verwendung von Dummy-Codierung jeglicher Art sind folgende Regeln zu beachten:

Die Anzahl der für neue Daten reservierten Spalten muss gleich der Anzahl der Faktorstufen minus . sein
Jeder Vektor repräsentiert eine Faktorstufe.
Probanden einer Ebene, die oft eine Kontrollgruppe ist, erhalten in allen Vektoren den Code 0.

Die Formel in den Zellen F2: H6 = LINEST (A2: A22; C2: D22 ;; TRUE) gibt Regressionsstatistiken zurück. Zum Vergleich Abb. 24 zeigt die Ergebnisse der traditionellen Varianzanalyse, die vom Tool zurückgegeben wird Einweg-ANOVA Aufbauten Datenanalyse.

Effektcodierung. In einer anderen Art der Codierung namens Codierungseffekte, der Mittelwert jeder Gruppe wird mit dem Mittelwert der Gruppenmittelwerte verglichen. Dieser Aspekt der Effektcodierung beruht auf der Verwendung von -1 anstelle von 0 als Code für eine Gruppe, die in allen Codevektoren denselben Code empfängt (Abbildung 25).

Reis. 25. Codierungseffekte

Wenn eine Dummy-Codierung verwendet wird, ist der von LINEST() zurückgegebene konstante Wert der Durchschnitt der Gruppe, der die Nullcodes in allen Vektoren zugewiesen sind (normalerweise die Kontrollgruppe). Bei Kodierungseffekten ist die Konstante gleich dem Gesamtdurchschnitt (Zelle J2).

Das allgemeine lineare Modell ist eine nützliche Methode, um die Komponenten des Wertes der resultierenden Variablen zu konzeptualisieren:

Y ij = μ + α j + ε ij

Die Verwendung griechischer statt lateinischer Buchstaben in dieser Formel unterstreicht die Tatsache, dass sie sich auf die Grundgesamtheit bezieht, aus der die Stichproben gezogen wurden, aber sie kann umgeschrieben werden, um anzuzeigen, dass sie sich auf die Stichproben bezieht, die aus der veröffentlichten Grundgesamtheit entnommen wurden:

Y ij = Y̅ + a j + e ij

Die Idee ist, dass jede Beobachtung Y ij als Summe der folgenden drei Komponenten betrachtet werden kann: Gesamtmittelwert μ; Verarbeitungseffekt j, a j; der Wert e ij, der die Abweichung des einzelnen quantitativen Indikators Y ij vom kombinierten Wert des Gesamtdurchschnitts darstellt und Wirkung des j-th Verarbeitung (Abb. 26). Das Ziel der Regressionsgleichung besteht darin, die Summe der Quadrate der Residuen zu minimieren.

Reis. 26. In Komponenten des allgemeinen linearen Modells zerlegte Beobachtungen

Faktorenanalyse. Wird der Zusammenhang zwischen der effektiven Variablen und zwei oder mehr Faktoren gleichzeitig untersucht, spricht man in diesem Fall von der Verwendung der Faktorenanalyse. Das Hinzufügen eines oder mehrerer Faktoren zur univariaten ANOVA kann die statistische Aussagekraft erhöhen. Bei der univariaten ANOVA wird die Variation einer Ergebnisvariablen, die keinem Faktor zugeschrieben werden kann, in das Residuenmittelwertquadrat aufgenommen. Es kann aber gut sein, dass diese Variante mit einem anderen Faktor gestrickt ist. Dann kann diese Variation aus dem quadratischen Mittelfehler entfernt werden, wobei eine Verringerung zu einer Erhöhung der Werte des F-Kriteriums und damit zu einer Erhöhung der statistischen Power des Tests führt. Überbau Datenanalyse enthält ein Werkzeug, das zwei Faktoren gleichzeitig verarbeitet (Abb. 27).

Reis. 27. Tool Zwei-Wege-ANOVA mit Wiederholungen des Analysepakets

Das in dieser Abbildung verwendete ANOVA-Tool ist insofern nützlich, als es den Mittelwert und die Varianz der Ergebnisvariablen sowie den Zählerwert für jede im Plan enthaltene Gruppe zurückgibt. In der Tabelle ANOVA es werden zwei Parameter angezeigt, die in der Ausgabe des Werkzeugs der einseitigen ANOVA nicht vorhanden sind. Auf Variationsquellen achten Stichprobe und Säulen in den Zeilen 27 und 28. Quelle der Abweichung Säulen bezieht sich auf das Geschlecht. Quelle der Variation Stichprobe bezieht sich auf jede Variable, deren Werte in verschiedenen Zeilen stehen. In Abb. 27 Werte für die KursLech1-Gruppe befinden sich in den Zeilen 2-6, die KursLech2-Gruppe - in den Zeilen 7-11 und die KursLechZ-Gruppe - in den Zeilen 12-16.

Der Hauptpunkt ist, dass beide Faktoren, Geschlecht (Überschrift Spalten in Zelle E28) und Behandlung (Überschrift Probe in Zelle E27) als Variationsquellen in der ANOVA-Tabelle enthalten sind. Die Durchschnittswerte für Männer unterscheiden sich von den Durchschnittswerten für Frauen, was zu Schwankungen führt. Auch die Mittel für die drei Behandlungen unterscheiden sich - hier ist eine weitere Variationsquelle. Es gibt auch eine dritte Quelle, Interaktion, die sich auf die kombinierte Wirkung der Variablen Geschlecht und Behandlung bezieht.

Kapitel 8. Kovarianzanalyse

Die Kovariationsanalyse (ANCOVA) reduziert Verzerrungen und erhöht die statistische Aussagekraft. Lassen Sie mich daran erinnern, dass eine der Möglichkeiten zur Beurteilung der Zuverlässigkeit Regressionsgleichung sind die F-Tests:

F = MS-Regression / MS-Rest

wobei MS (Mean Square) das mittlere Quadrat ist und die Regressions- und Residualindizes die Regressions- bzw. Residuenkomponenten angeben. Die Berechnung von MS Residual erfolgt nach der Formel:

MS-Rest = SS-Rest / df-Rest

wobei SS (Sum of Squares) die Summe der Quadrate und df die Anzahl der Freiheitsgrade ist. Wenn Sie einer Regressionsgleichung Kovarianz hinzufügen, wird ein Teil der Gesamtsumme der Quadrate nicht in SS-ResiduaI, sondern in SS-Regression eingeschlossen. Dies führt zu einer Abnahme von SS-Residual und damit von MS-Residual. Je niedriger der MS-Rest, desto höher der F-Wert und desto wahrscheinlicher werden Sie die Nullhypothese ablehnen, dass es keine Unterschiede zwischen den Mittelwerten gibt. Als Ergebnis verteilen Sie die Variabilität der resultierenden Variablen neu. Wenn die Kovarianz bei der ANOVA nicht berücksichtigt wird, wird die Volatilität zu einem Fehler. Aber in ANCOVA wird ein Teil der Variabilität, die zuvor dem Fehler zugeschrieben wurde, der Kovariate zugeordnet und wird Teil der SS-Regression.

Betrachten Sie ein Beispiel, in dem der gleiche Datensatz zuerst mit ANOVA und dann mit ANCOVA analysiert wird (Abbildung 28).

Reis. 28. ANOVA-Analyse zeigt an, dass die Ergebnisse der Regressionsgleichung unzuverlässig sind

Die Studie verglich die relativen Auswirkungen von Training, das die Muskelkraft aufbaut, und kognitivem Training (Kreuzworträtsel lösen), das die Gehirnaktivität stimuliert. Die Probanden wurden nach dem Zufallsprinzip zwei Gruppen zugeteilt, so dass sich beide Gruppen zu Beginn des Experiments in den gleichen Bedingungen befanden. Nach drei Monaten wurden die kognitiven Eigenschaften der Probanden gemessen. Die Ergebnisse dieser Messungen sind in Spalte B aufgeführt.

Der Bereich A2: C21 enthält die Anfangsdaten, die an die Funktion LINEST() übergeben werden, um eine Analyse unter Verwendung von Codierungseffekten durchzuführen. Die Ergebnisse der LINEST()-Funktion werden im Bereich E2: F6 angezeigt, wobei Zelle E2 den Regressionskoeffizienten anzeigt, der dem Stimulusvektor zugeordnet ist. Zelle E8 enthält einen t-Test = 0,93, und Zelle E9 testet die Zuverlässigkeit dieses t-Tests. Der Wert in Zelle E9 zeigt an, dass die Wahrscheinlichkeit, auf einen Unterschied zwischen den in diesem Experiment beobachteten Gruppenmittelwerten zu stoßen, 36% beträgt, wenn die Gruppenmittelwerte in der Gesamtbevölkerung gleich sind. Nur wenige halten dieses Ergebnis für statistisch signifikant.

In Abb. 29 zeigt, was passiert, wenn Kovariaten zur Analyse hinzugefügt werden. In diesem Fall habe ich das Alter jedes Probanden zum Datensatz hinzugefügt. Das Bestimmtheitsmaß R 2 für die Regressionsgleichung, die die Kovariate verwendet, beträgt 0,80 (Zelle F4). Der R 2 -Wert im Bereich F15: G19, in dem ich die ANOVA-Ergebnisse ohne Verwendung der Kovariate reproduziert habe, beträgt nur 0,05 (Zelle F17). Daher sagt eine Regressionsgleichung, die eine Kovariate enthält, die Werte der Cognitive Score-Variablen viel genauer voraus als die alleinige Verwendung des Impact-Vektors. Für ANCOVA beträgt die Wahrscheinlichkeit, den in Zelle F5 angezeigten F-Score zufällig zu erhalten, weniger als 0,01 %.

Reis. 29. ANCOVA bringt ein ganz anderes Bild zurück

Siegel

Auch interessant:

Chinesische Smartphones mit starkem Akku

Weitere Funktionen dieses Smartphones sind

Microsoft Touch Mouse: berührungsstark, komplett kabelloses Erscheinungsbild und Layout

Wir empfehlen zu lesen:

2021-08-11 17:38:07

Nokia X2 - Spezifikationen

2021-08-11 17:38:07

Nokia X2 - Spezifikationen

2021-08-11 17:38:07

CD-RW-Brenner-Laufwerke Was Sie zum Brennen von CDs benötigen

Fortsetzung des Themas:

Router

Überprüfung und Test des Motherboards ASUS P8Z68-V LX

02.12.2011 | Meister | (35) 1 – Look and Feel 2 – UEFI-Funktionen. Zusatzsoftware 3 - Übertaktung. Testen. Schlussfolgerungen Anzeige als eine Seite Nicht so ...