Normale Gleichung in Excel (Statistik)

Question 1

Hier ist eine Alternative, die sich mit dem Problem befasst, das Sie in Ihrem Kommentar zu der Antwort aufmath.stackexhange

Ihr Modell ist:

y = B - exp(a*x)

und du hast,a priori, ermittelte B = 21.

Das Modell stellt keine exakte Beziehung zwischen den y(i)- und x(i)-Werten dar. Daher ist es üblich, einen Fehlerterm e(i) hinzuzufügen und das Modell wie folgt darzustellen:

y(i) = B - exp(a*x(i)) + e(i)

Die vom Modell für jeden der x-Werte geschätzten y-Werte werden mit y'(i) bezeichnet, wobei

y'(i) = B - exp(a*x(i))

DerMethode der kleinsten Quadratezielt darauf ab, den Wert von a auszuwählen, der die Summe der Quadrate der Differenzen zwischen den tatsächlichen y(i)-Werten und den entsprechenden geschätzten oder y'(i)-Werten minimiert.

y(i) - y'(i) = [B - exp(a*x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)

Die Summe der Quadrate der Differenzen zwischen den y(i)- und y'(i)-Werten ist also

Summe[(y(i) - y'(i))^2] = Summe[e(i)^2]

Die rechte Seite ist die Summe der quadrierten Fehlerterme und wird daher als Summe der Fehlerquadrate bezeichnet oderESS.

Der Prozess der Transformation der y(i)-Werte in z(i)-Werte über

z(i) = LN(21 - y(i))

erstellt ein lineares Modell

z = ax

Dies ermöglicht die Verwendung der LINESTFunktion zur Schätzung des Wertes "best fit" Wert vonA. Mit den Daten aus Ihrer Antwort (oder Ihrer Frage auf math.stackexchange) ist dieser beste Anpassungswert vonAbeträgt 0,147233 – dieselbe Antwort, die Sie in Ihrer Antwort durch Implementierung der Normalgleichung erhalten haben.

Das mit diesem Wert verbundene ESSAbeträgt 8,27991. Dieser Wert ist jedoch nicht der minimal erreichbare Wert des ESS. Dies tritt ein, wennAnimmt den Wert 0,149140 an und der entsprechende ESS ist 6,66073.

Der Screenshot unten zeigt die Berechnungen.

Die geschätzten y'(i)- und zugehörigen Fehlerquadrat- und ESS-Werte werden für zwei Versionen des Modells y = 21 – exp(a*x) angezeigt.

In Version 1Awird mit dem LINESTAnsatz abgeleitet, der auf dem transformierten Modell z = ax basiert. In Version 2Aist der Wert, der den ESS des (nicht transformierten) Modells minimiert. Mehr dazu, wie dieser Wert vonAerhalten wurde, finden Sie weiter unten.

Bei linearen Modellen wie y = mx + c bieten die Normalgleichungen eine bequeme Möglichkeit, die Werte von m und c abzuschätzen, die den ESS minimieren. Die Funktion LINESTimplementiert (unter anderem) die Normalgleichungen.

Für nichtlineare Modelle (wie y = 21 - exp(a*x)) gibt es solche bequemen Gleichungen im Allgemeinen nicht, daher müssen andere Methoden verwendet werden, um den Wert vonAwodurch das ESS minimiert wird.

Ein Ansatz besteht darin, Suchmethoden zu verwenden: im Wesentlichen versuchen Sie eine Reihe verschiedener Möglichkeiten fürAund wählen Sie diejenige aus, die zum kleinsten ESS führt.

Dies ist im Grunde das, was der nächste Screenshot zeigt. Es verwendet das, was Microsoft alsDatentabelle. Dies ist eine schlechte Namenswahl, da eine Datentabelle keine Datentabelle ist. Vielmehr handelt es sich um ein Tool, mit dem ermittelt werden kann, wie sich der Wert einer Berechnung ändert, wenn ein oder zwei Elemente innerhalb der Berechnung geändert werden. Sie finden es in der Multifunktionsleiste in der Gruppe „Prognose“ des Menüs „Daten“, indem Sie über das Symbol „Was-wäre-wenn-Analyse“ die Option „Datentabelle ...“ auswählen.

Die Dokumentation von Microsoft zum Erstellen und Verwenden einer Datentabelle ist ziemlich schrecklich, daher werde ich eine Art Kochbuchansatz bereitstellen.

Die Datentabelle selbst wird im Bereich N2:O23 bereitgestellt.
Zelle O2enthält den berechneten Wert, der untersucht werden soll. Diese Zelle enthält die Formel =J4, die das ESS ist, das mit dem Wert von verknüpft istAin der Zelle J3.
Die Möglichkeiten für die verschiedenen Werte vonAwerden in den Bereich gesetzt N3:N23und die Ergebnisse des ESS erscheinen in den Zellen O3:O23. Dies ergibt 21 mögliche Werte vonA. Dies ist eine willkürliche Auswahl, Datentabellen können eine größere oder kleinere Anzahl möglicher Werte enthalten.
Sobald dasAWenn die Werte vorhanden sind, wählen Sie den Bereich aus N2:O23und starten Sie das Dialogfeld „Datentabelle“, indem Sie im Symbol „Was-wäre-wenn-Analyse“ in der Gruppe „Prognose“ des Menüs „Daten“ die Option „Datentabelle ...“ auswählen.
Tragen Sie im Dialog $J$3in das Feld mit der Bezeichnung „Spalten-Eingabezelle:“ ein und drücken Sie die Schaltfläche „OK“.
Der Bereich O3:O23wird nun mit den ESS-Werten gefüllt, die den Werten von entsprechenAin N3:N23. Wenn Sie einen der Werte ändern, N3:N23werden die ESS-Werte in aktualisiert O3:O23.

DerAWerte N3:N23werden durch Formeln festgelegt und nicht durch Eingabe. Die Werte werden mithilfe einer Suchstrategie festgelegt, die immer feinere Wertesätze fürA.

Die 21AWerte in N3:N23basieren auf einemZentralWert in Position 11 - Zelle N13- wobei sich die Zellen darüber und darunter sukzessive um einenZuwachsBetrag, sodass der gesamte Bereich der 21 Werte in aufsteigender Reihenfolge ist.

Die Suchstrategie durchläuft mehrere Schritte, wobei die Schrittanzahl durch den Wert in der Zelle gesteuert wird O1.

In Schritt 1ZentralDer Wert wird auf 0,15 (in Zelle R3) gesetzt und derZuwachswird auf 0,001 (in Zelle S3) gesetzt, was Werte N3:N23von 0,14 bis 0,16 ergibt. Dieser Bereich wird auf der Grundlage des Wertes von Version 1 von gewähltA, mit der Erwartung, dass der minimale ESS-Wert in diesem Bereich liegen wird.

Dies ist tatsächlich der Fall. Für die 21 Werte vonAbeginnend bei 0,14 und ansteigend um 0,001 bis 0,16, beginnen die entsprechenden ESS-Werte bei über 39 (wennAbeträgt 0,14), abnehmend alsAerhöht sich bisAhat den Wert 0,149 (wenn der ESS 6,66972 ist) und steigt dann an und erreicht einen ESS-Wert von über 70, wennAbeträgt 0,16. Dies zeigt, dass der Wert vonAwodurch das ESS minimiert wird, liegt in der Nähe von 0,149.

(Hätte sich nicht herausgestellt, dass ein Minimum innerhalb des Wertebereichs gefunden wird, wären die ESS-Werte entweder alle gestiegen oder gesunken, so dass das Minimum an einem Ende des Bereichs liegt. In diesem FallZentralWert (in Zelle R3) muss angepasst werden, möglicherweise mit einer Erhöhung derZuwachs-Wert (in Zelle S3), bis ein Minimum im mittleren Bereich gefunden wird.)

Für jeden Wertebereich in N3:N23den Zellen O27und N27ermitteln Sie jeweils den minimalen ESS-Wert und den Wert vonAdas ergibt das Minimum.

Der Wert vonAdas Minimum zu produzieren, liefert die neueZentralWert für den nächsten Schritt der Suche. Der neueZuwachsist der bisherige Wert um den Faktor 10 reduziert. Diese neuenZentralUndZuwachsRDie Werte werden in den Spalten und manuell in die „Kontrolltabelle“ eingetragen und Sin der Zelle wird die Schrittnummer manuell um 1 erhöht O1.

Die Suche wird in mehreren Schritten fortgesetzt und endet, wenn keine praktische Reduzierung des ESS-Werts mehr erreicht werden kann.

Der Screenshot zeigt die Ergebnisse bei Schritt 2 der Suche.

Answer

Hier ist eine Alternative, die sich mit dem Problem befasst, das Sie in Ihrem Kommentar zu der Antwort aufmath.stackexhange

Ihr Modell ist:

y = B - exp(a*x)

und du hast,a priori, ermittelte B = 21.

Das Modell stellt keine exakte Beziehung zwischen den y(i)- und x(i)-Werten dar. Daher ist es üblich, einen Fehlerterm e(i) hinzuzufügen und das Modell wie folgt darzustellen:

y(i) = B - exp(a*x(i)) + e(i)

Die vom Modell für jeden der x-Werte geschätzten y-Werte werden mit y'(i) bezeichnet, wobei

y'(i) = B - exp(a*x(i))

DerMethode der kleinsten Quadratezielt darauf ab, den Wert von a auszuwählen, der die Summe der Quadrate der Differenzen zwischen den tatsächlichen y(i)-Werten und den entsprechenden geschätzten oder y'(i)-Werten minimiert.

y(i) - y'(i) = [B - exp(a*x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)

Die Summe der Quadrate der Differenzen zwischen den y(i)- und y'(i)-Werten ist also

Summe[(y(i) - y'(i))^2] = Summe[e(i)^2]

Die rechte Seite ist die Summe der quadrierten Fehlerterme und wird daher als Summe der Fehlerquadrate bezeichnet oderESS.

Der Prozess der Transformation der y(i)-Werte in z(i)-Werte über

z(i) = LN(21 - y(i))

erstellt ein lineares Modell

z = ax

Dies ermöglicht die Verwendung der LINESTFunktion zur Schätzung des Wertes "best fit" Wert vonA. Mit den Daten aus Ihrer Antwort (oder Ihrer Frage auf math.stackexchange) ist dieser beste Anpassungswert vonAbeträgt 0,147233 – dieselbe Antwort, die Sie in Ihrer Antwort durch Implementierung der Normalgleichung erhalten haben.

Das mit diesem Wert verbundene ESSAbeträgt 8,27991. Dieser Wert ist jedoch nicht der minimal erreichbare Wert des ESS. Dies tritt ein, wennAnimmt den Wert 0,149140 an und der entsprechende ESS ist 6,66073.

Der Screenshot unten zeigt die Berechnungen.

Die geschätzten y'(i)- und zugehörigen Fehlerquadrat- und ESS-Werte werden für zwei Versionen des Modells y = 21 – exp(a*x) angezeigt.

In Version 1Awird mit dem LINESTAnsatz abgeleitet, der auf dem transformierten Modell z = ax basiert. In Version 2Aist der Wert, der den ESS des (nicht transformierten) Modells minimiert. Mehr dazu, wie dieser Wert vonAerhalten wurde, finden Sie weiter unten.

Bei linearen Modellen wie y = mx + c bieten die Normalgleichungen eine bequeme Möglichkeit, die Werte von m und c abzuschätzen, die den ESS minimieren. Die Funktion LINESTimplementiert (unter anderem) die Normalgleichungen.

Für nichtlineare Modelle (wie y = 21 - exp(a*x)) gibt es solche bequemen Gleichungen im Allgemeinen nicht, daher müssen andere Methoden verwendet werden, um den Wert vonAwodurch das ESS minimiert wird.

Ein Ansatz besteht darin, Suchmethoden zu verwenden: im Wesentlichen versuchen Sie eine Reihe verschiedener Möglichkeiten fürAund wählen Sie diejenige aus, die zum kleinsten ESS führt.

Dies ist im Grunde das, was der nächste Screenshot zeigt. Es verwendet das, was Microsoft alsDatentabelle. Dies ist eine schlechte Namenswahl, da eine Datentabelle keine Datentabelle ist. Vielmehr handelt es sich um ein Tool, mit dem ermittelt werden kann, wie sich der Wert einer Berechnung ändert, wenn ein oder zwei Elemente innerhalb der Berechnung geändert werden. Sie finden es in der Multifunktionsleiste in der Gruppe „Prognose“ des Menüs „Daten“, indem Sie über das Symbol „Was-wäre-wenn-Analyse“ die Option „Datentabelle ...“ auswählen.

Die Dokumentation von Microsoft zum Erstellen und Verwenden einer Datentabelle ist ziemlich schrecklich, daher werde ich eine Art Kochbuchansatz bereitstellen.

Die Datentabelle selbst wird im Bereich N2:O23 bereitgestellt.
Zelle O2enthält den berechneten Wert, der untersucht werden soll. Diese Zelle enthält die Formel =J4, die das ESS ist, das mit dem Wert von verknüpft istAin der Zelle J3.
Die Möglichkeiten für die verschiedenen Werte vonAwerden in den Bereich gesetzt N3:N23und die Ergebnisse des ESS erscheinen in den Zellen O3:O23. Dies ergibt 21 mögliche Werte vonA. Dies ist eine willkürliche Auswahl, Datentabellen können eine größere oder kleinere Anzahl möglicher Werte enthalten.
Sobald dasAWenn die Werte vorhanden sind, wählen Sie den Bereich aus N2:O23und starten Sie das Dialogfeld „Datentabelle“, indem Sie im Symbol „Was-wäre-wenn-Analyse“ in der Gruppe „Prognose“ des Menüs „Daten“ die Option „Datentabelle ...“ auswählen.
Tragen Sie im Dialog $J$3in das Feld mit der Bezeichnung „Spalten-Eingabezelle:“ ein und drücken Sie die Schaltfläche „OK“.
Der Bereich O3:O23wird nun mit den ESS-Werten gefüllt, die den Werten von entsprechenAin N3:N23. Wenn Sie einen der Werte ändern, N3:N23werden die ESS-Werte in aktualisiert O3:O23.