Nichtlineare Regression mit dem Solver

Probleme der nichtlinearen Regression lassen sich in vielen Fällen dadurch lösen, dass man die nichtlineare Vorhersagefunktion mit Hilfe von Variablensubstitutionen in eine lineare Funktion umformt und für diese dann die gängigen Verfahren der linearen Regression bzw. multiplen linearen Regression anwendet.

 

Doch nicht immer ist eine solche Umformung leicht möglich, so dass wir ein Verfahren benötigen, das in der Lage ist, die ursprüngliche nichtlineare Funktion zu schätzen. Excel stellt solche Verfahren im Rahmen des Solvers bereit.

Im folgenden Beispiel wird der Solver benutzt, um die Parameter α und µ der Funktion

 

mit Hilfe der Kleinstquadrate-Methode zu schätzen. Wir verfügen über zehn (λ, σ)-Wertepaare, die bei Versuchen ermittelt wurden und die in den ersten beiden Spalten der folgenden Tabelle enthalten sind.

 

 

In die Zellen der dritten Spalte setzen wir die Formel (s. oben), welche das σ ermittelt, das sich aus dem λ in der ersten Spalte, dem µ in Zelle D5 und dem α in Zelle D6 ergibt. Die Zellen D5 und D6 füllen wir zunächst mit beliebigen Werten im zulässigen Wertebereich. Beispielhaft sei die Formel in der Zelle E11 (in der Spalte σ Theorie direkt unter der Überschrift) angegeben:

 

=2*$D$5/$D$6*(C11^($D$6-1) - C11^(-0,5*$D$6-1))

 

In die vierte Spalte setzen wir Formeln, welche das Quadrat der Abweichung zwischen den Werten der zweiten und der dritten Spalte ermitteln. In F11 steht also:

 

=(D11-E11)^2

 

Nun müssen wir noch unterhalb der vierten Spalte eine Summenformel einsetzen, und der Rahmen für die Anwendung des Solvers steht. Wir können ihn nun aktivieren, indem wir in die Registerkarte Daten gehen. Das folgende Bild zeigt schon den fertigen Ansatz:

 

 

Als Zielzelle wählen wir die Zelle, welche die Summe der quadrierten Abweichungen enthält. Diese soll minimiert werden. Hierfür werden Werte für µ und α (veränderbare Zellen) so angepasst, dass sich das gewünschte Minimum ergibt. Die zulässigen Wertebereiche für µ und α grenzen wir durch geeignete Nebenbedingungen ein.

 

Nach Drücken der Taste Lösen führt der Solver die Optimierung durch. Die gesuchten Werte finden sich dann in den veränderbaren Zellen.

 

Das folgende Diagramm zeigt die geschätzte Kurve (durchgezogene rote Linie) im Vergleich zu den Wertepaaren, die beim Versuch ermittelt wurden (blaue Punkte). Wie man sieht, ist die Anpassung recht gut.