Die Regression als Werkzeug in der angewandten MathematikDie Methode der kleinsten Quadrate gerechnet mit dem Solver von Excel.Linz 2009Friedrich TinhofBHAK Eisenstadt

tinhof@wellcom.at

Die Regression als Werkzeug in der angewandten Mathematik

In der Praxis ist es oft notwendig einen formalen Zusammenhang zwischen zwei Variablen zu berechnen. Ein Beispiel dafür ist der Zusammenhang zwischen Körpergröße und Körpermasse.

Mit der Regressionsanalyse wird versucht, den Zusammenhang von quantitativen Merkmalen in Form einer mathematischen Funktion anzugeben.

Die Regression als Werkzeug in der angewandten Mathematik

Es geht dabei um: Erkennen und Nachweis von

ZusammenhängenBeispiel: „Beeinflusst die Einnahme ein neues Medikament die Höhe des Blutdruckes?“

Schätzung der Art und Größe von ZusammenhängenBeispiel: „Wie stark beeinflusst die Einnahme einer bestimmten Dosis ein neues Medikament die Höhe des Blutdruckes?“

Prognose fehlender oder zukünftiger WerteBeispiel: „Wie wird sich der Wert des Blutdrucks entwickeln, wenn man die Dosis des Medikamentes um 20% erhöht?“(Man nimmt dabei an, dass sich die gemessene/berechnete Entwicklung fortsetzt.)

Die Regression als Werkzeug in der angewandten Mathematik

Beispiel 1Von fünf zufällig gewählten Schülerinnen wurden Körpergröße X und Körpermasse (Gewicht) Y gemessen.Gibt es einen (linearen) Zusammenhang zwischen den Merkmalen Körpergröße und Körpermasse?

Welche Gerade ist für die Beschreibung des Zusammenhanges am besten geeignet?Gesucht ist eine „Trendlinie“, die den Zusammenhang der Merkmale X und Y am besten wiedergibt.

Die Regression als Werkzeug in der angewandten Mathematik

Die Frage, welche die beste Näherungsgerade ist, klären wir zunächst mit Excel und erklären dann die Hintergründe.

1.Schritt: Geeignetes Punkt(XY) – Diagramm erstellen.2.Schritt: Trendlinie hinzufügen

(Mit rechter Maustaste Punkt anklicken)

3.Schritt: Typ und Optionen wählen und Trendlinie zeichnen

Die Methode der kleinsten Quadrate Ein kurzer Blick auf die Hintergründe

Excel passt die Gerade mit der Gleichung y = a·x + b so an die Punkte des Streudiagrammes an, dass die Summe F der Fehlerquadrate ei (der vertikalen Abweichungen) minimal ist.Die Fehler ei heißen Residuen. Die Residuen sind die Differenz zwischen den Messwerten yi und den Modellwerten Yi = y(xi).

e1

e2

e3 e4

e5

Residuen ei lineare Funktion:

Modellwert

i

Messwert

ii )bxa(ye

Summe der Fehlerquadrate F(a,b):

n

1i

2ii

n

1i

2i )bxay(e)b,a(F

Residuen ei allgemein:

Modellwert

i

Messwert

ii xyye

rateFehlerquadderSumme

n

1i

2ii

n

1i

2i )x(yye)b,a(F

Messwert y i

Modellwert Y i = y(x i)Schätzung des y-Wertes auf der

Trendlinie

TrendlinieRegressionsgerad

e

Die Methode der kleinsten Quadrate Ein kurzer Blick auf die Hintergründe

F wird minimal, wenn

Mit CAS können diese partiellen Ableitungen berechnet und das Gleichungssystem kann gelöst werden.

0b

)b,a(F0

a

)b,a(F

Wir berechnen zunächst die Streuung der Messwerte yi um den Mittelwert M der Messwerte.

Varianz der y-Werte:

Die Methode der kleinsten Quadrate Güte der Anpassung – das Bestimmtheitsmaß

Abweichung vom Mittelwert = yi - M

n

1iiy

n

1y M

n

1i

2i

2y yy

n

1s

Messwert yi

Für jeden einzelnen x-Wert der Datenpunkte existiert auch ein dazugehöriger Schätzwert (Modellwert y(xi)) auf der Regressionsgeraden.

Auch diese Modellwerte Yi = y(xi) streuen um den Mittelwert M.Diese Streuung wird nun berechnet.

Varianz der Modellwerte:

Die Methode der kleinsten Quadrate Güte der Anpassung – das Bestimmtheitsmaß

n

1iiy

n

1y M

Modellwert Yi = y(xi)

Regressionsgerade y(x)

n

1i

2i

2Y y)x(y

n

1s

Die Güte der Regression ergibt sich aus dem Quotienten der Varianz der Modellwerte und der Varianz der Messwerte.Dieser Quotient wird Bestimmtheitsmaß R² genannt.

Die Methode der kleinsten Quadrate Güte der Anpassung – das Bestimmtheitsmaß

n

1iiy

n

1y

n

1i

2i

n

1i

2i

2

yy

y)x(yR

Bestimmtheitsmaß R²

R² ist genau dann gleich 1, wenn alle Datenpunkte auf der Regressionskurve mit y(x) liegen.In diesem Fall stimmen Datenpunkte und Modellwerte genau überein.

n

1i

2i

n

1i

2i

2

yy

y)x(yR

Wenn die Streuung der Modellwerte gleich der Streuung der Datenpunkte ist, nimmt R² den maximal möglichen Wert 1 an.

Streuung Datenwerte

Streuung Modellwerte

Streuung Datenwerte = Streuung Modellwerte

R² = 1

Bestimmtheitsmaß R²

Je weiter die Messwerte von der Trendlinie entfernt sind, umso größer ist die die Streuung der Datenpunkte im Verhältnis zur Streuung der Modellwerte.Es gilt 0 ≤ R² ≤ 1.

R2 ist das Verhältnis von erklärter Varianz zur Gesamtvarianz.

Streuung der Residuen (nicht erklärte Streuung)

Streuung der Modellwerte (erklärte Streuung)

Gesamtstreuung

ianzvarGesamt

eModellwertderVarianz

yy

y)x(yR n

1i

2i

n

1i

2i

2

Regression mit EXCEL

Der Solver von Excel ist ein leistungsstarkes Werkzeug zur Lösung von Optimierungsproblemen. Wir verwenden den Solver als „Black Box“.

Der Solver wird bei der Standardinstallation von Excel (bis 2003) nicht automatisch installiert.

In >Extras>Add-Ins…>Verfügbare Add-Insmuss der Solver ein-malig aktiviert werden.

Regression mit dem Solver von EXCEL

Beispiel 1: Körpergröße

1. Schritt: Tabelle in Excel eingeben und den Mittelwert der Messwerte berechnen.

2. Schritt: Parameter a und b an geeigneter Stelle vorgeben. Die vorgegebenen Werte sind die Startwerte für den Solver.

3. Schritt: Spalte mit Modellwerten Y berechnen.

D3: =$I$3*B3+$I$4

Regression mit dem Solver von EXCEL

4. Schritt: Spalte mit den Fehlerquadraten (y – Y)² erstellen und Spaltensumme berechnen.E3: =(C3-D3)^2

5. Schritt: Solver aufrufen

6. Schritt: Minimierung Zielzelle: E9Veränderbare Zellen: I3:I4

Regression mit dem Solver von EXCEL

Optional ist die Berechnung von R² Spalte mit (Y – M)² berechnen.

(Streuung der Modellwerte)²F3: =(D3-$C$9)^2

Spalte mit (y – M)² berechnen.(Gesamtstreuung)²G3: =(C3-$C$9)^2

Spaltensummen und Quotienten der Spaltensummen berechnen.I5: =F8/G8

n

1i

2i

n

1i

2i

2

yy

y)x(yR

Regression mit dem Solver von EXCEL

Hinweis:In Excel 2007 finden Sie den Solver bei den Analyse-Tools.Die Funktionsweise ist identisch wie in Excel 2003.

Regression mit dem Solver von EXCEL

Beispiel 2: Berechnung der quadratischenNachfragefunktion. Welchen maximalen Eintritt sind Sie bereit für eine bestimmte Ausstellung zu bezahlen?

Regression mit dem Solver von EXCEL

Beispiel 2: Welchen maximalen Eintritt sind Sie bereit für eine bestimmte Ausstellung zu bezahlen?

Weiterführende Berechnungen für x ≤ 190:E(x) = p(x)·x ErlösfunktionK(x) = 3x +150GesamtkostenfunktionG(x) = E(x)- K(x) GewinnfunktonGewinnmaximum?Erlösmaximum?

Regression mit dem Solver von EXCEL

Beispiel 3: Logistische Regression Entwicklung der Anzahl der Leser einer Tageszeitung

Deb1

My

xk

Regression mit dem Solver von EXCEL

Beispiel 3: Logistische Regression

0102.0e6224.231

825.2y

x2001.0

Regression mit dem Solver von EXCEL

Beispiel 3: Weitere Berechnungen Für die folgenden Berechnungen verwenden wir die ermittelte Regressionsfunktion.

a) Wie hoch ist die Sättigungsmenge?M ≈ 2,8 Mio. Leser

b) Wann wurden 2 Mio. Leser erreicht?Für x = 20,14 wurden 2 Mio. Leser erreicht.Das war etwa 1980.

0102.0e6224.231

825.2y

x2001.0

Regression mit dem Solver von EXCEL

Beispiel 3: Weitere Berechnungen c) Wann war der Zuwachs der Leseranzahl maximal?Für x = 15,8 wurde ein Zuwachs von ca. 0,14 Mio. Leser erreicht.Das war ca.1975.

Regression mit dem Solver von EXCEL

Beispiel 4: Sinusregressionhttp://www.zamg.ac.at/klima/sonne_mond/index.php?jahr=2009&ort=eise

Ziel ist es eine Näherungsfunktion in der Form y = A·sin(B·x + C) +D für die Berechnung der Tageslänge zu finden.

Regression mit dem Solver von EXCEL

Beispiel 4: Sinusregression

Wichtig: Bei komplizierteren Regressionsrechnungen

findet der Solver von Excel die optimalen Werte nur

nach Vorgabe geeigneter Startwerte!

Regression mit dem Solver von EXCEL

Beispiel 4: Sinusregression

y = 224,077·sin(-0,01681·x -1,8169) + 729,719

Regression mit dem Solver von EXCEL

Beispiel 4: Berechnungen mit der ermittelten Funktiony(x) = 224,077·sin(-0,01681·x -1,8169) +

729,719 a) An welchem Tag ist nach diesem Rechenmodell der längste Tag?Maximale Tageslänge am 173. Tag. Das ist der 22 Juni.

Regression mit dem Solver von EXCEL

Beispiel 4: Weitere Berechnungeny(x) = 224,077·sin(-0,01681·x -1,8169) + 729,719 b) An welchem Tag ist nach diesem Rechenmodell der die größte Zunahme/Abnahme der Tageslänge zu beobachten?Größte Zunahme der Tageslänge am 79. Tag. Das ist der 20. März.Größte Abnahme der Tageslänge am 266. Tag. Das ist der 23. 09.

dx

)x(dy)x('y:Zunahme

Regression mit dem Solver von EXCEL

Beispiel 5: Arzneistoffe in Körper Ein Arzneistoff wird einmalig oral verabreicht. A(t) ist die Arzneistoffmenge, die zur Zeit t am Ort der Resorption (Depot; Mund, Magen, Darm) zur Aufnahme zur Verfügung steht. E(t) ist die Gesamtmenge des bereits ausgeschiedenen Arzneistoffes. Im Kompartiment X (Blut) erfolgt eine Absorption aus dem Depot A mit der Absorptionskonstanten ka. Gleichzeitig kommt es aber auch zu einer Elimination des Arzneistoffes mit der Eliminationskonstanten ke.

Regression mit dem Solver von EXCEL

Beispiel 5: Arzneistoffe in Körper Für den zeitlichen Verlauf der Konzentration C(t) des Arzneistoffs im Blut gilt die Bateman-Funktion:

)ee(kk

kC)t(C tktk

ea

a0

ae

Regression mit dem Solver von EXCEL

Beispiel 5: Propranolol oral Berechnung der Bateman–Funktion: Einer Versuchsperson wird einmalig oral eine Dosis des Betablockers Propranolol verabreicht. Die Konzentration C(t) des Arzneimittels im Blut wird gemessen und ist in der Tabelle rechts abzulesen.Die Bateman-Funktion istzu ermitteln.Quelle: ETH Zürich 2006; Urs Kirchgraber und Heidi Wunderli–Allenspach; www.educeth.ch

Regression mit dem Solver von EXCEL

Beispiel 5: Propranolol oral Berechnung der Bateman–Funktion

Regression mit dem Solver von EXCEL

Beispiel 5: Propranolol oral Berechnung der Bateman–Funktion

C3: =$H$2*$H$3/($H$3-$H$4)*(EXP(-$H$4*A4)-EXP(-$H$3*A4))

)ee(kk

kC)t(C tktk

ea

a0

ae

Regression mit dem Solver von EXCEL

Beispiel 5: Propranolol oral Berechnung der Bateman–Funktion Für Propranolol ergaben sich die Werte ka ≈ 0,5 h–1; ke ≈ 0,18 h–1 und C0 ≈ 62 μg/l.

)ee(18.05.0

5.062)t(C t5.0t18.0

Regression mit dem Solver von EXCEL

Beispiel 5: Berechnungen mit der ermittelten Funktion

a) Wann ist die Konzentration im Blut am höchsten? Wie hoch ist sie? Die maximale Konzentration ist nach ca. 3,19 Stunden mit 34.9 μg/l erreicht.

b) Wann ist die Konzentration unter die Nachweisgrenze von 0,2 μg/l gesunken?

Nach ca. 34,3 Stunden ist die Konzentration unter 0.2 μg/l gefallen.

Regression mit dem Solver von EXCEL

Beispiel 5: Weitere Berechnungen c) Wie lange wirkt das Medikament,

wenn dazu eine Mindestkonzentration von 5 μg/l im Blut benötigt wird? Berechnen Sie die Wirkzeit.

Wirkzeit ≈ 16.44 – 0.17 = 16.27 Stunden

Latentzeit ≈ 0.17 Stunden (≈10 Min)

Regression mit dem Solver von EXCEL

Beispiel 5: Propranolol oral d) Zu welchem Zeitpunkt ist die Aufnahmerate

(Zunahme der Konzentration) am höchsten? Wann die Ausscheidungsrate (Abnahme der Konzentration)? Zu berechnen ist hier die maximale Änderung der Konzentration C. Wir berechnen die Extrema der ersten Ableitung der Bateman–Funktion. Die Aufnahmerate ist zum Zeitpunkt t = 0 maximal. Die Ausscheidungsrate ist nach t ≈ 6.39 h maximal (Wendepunkt).

Regression mit dem Solver von EXCEL

Danke für Ihre Aufmerksamkeit!

tinhof@wellcom.at