Mathematische Inhalte:

Regressionsrechnung ohne Differenzialrechnung

Mit Excel bietet sich die Möglichkeit die Methode der kleinsten Quadrate auf einfache und verständliche Art einzuführen. Auf die Verwendung der Differenzialrechnung wird dabei verzichtet. Grundkenntnisse im Umgang mit Excel werden vorausgesetzt.

HAK 4 Jahrgang

ca. 2 Wochenstunden; abhängig von den Vorkenntnissen

PC; Excel ab Version 97

Der vorliegende Artikel stammt aus dem Lehrerbegleitheft zum Mathematiklehrbuch des 4. Jahrganges der Handelsakademien und der WB des Schulbuchverlages Dr. Rudolf TRAUNER. Die Lehrerbegleithefte enthalten Ergänzungen und zusätzliche Informationen zum Unterrichtsstoff und stehen allen Benutzern der Schulbücher des Trauner- Verlages kostenlos zur Verfügung.

Die im Lehrplan des vierten Jahrgangs der Handelsakademie explizit angeführte Methode der kleinsten Quadrate ist von der mathematischen Seite schwierig zu erklären und außerdem – auch bei bereits vorhandenen Formeln - rechnerisch sehr aufwendig auszuführen.

EXCEL bietet hier die Möglichkeit auf einfache und verständliche Art die Methode der kleinsten Quadrate anzuwenden. Die Berechnung der Gleichung der Rekursionskurve erfolgt ohne Verwendung der Differenzialrechnung und ist für (fast) beliebige Funktionstypen verwendbar.

Wir verwenden für die folgenden Berechnungen den SOLVER von EXCEL.

Das Hilfsmittel SOLVER rufen Sie auf, indem Sie auf den Menüpunkt Extras . Solver... klicken.

Anmerkung:

Der Solver wird von Excel standardmäßig nicht installiert. Sollten Sie den Solver noch nicht aktiviert haben, klicken Sie auf Extras . Add-In-Manager... und aktivieren Sie in der erscheinenden Maske den Solver.

þ Solver

Um den Zusammenhang mit dem Lehrbuch zu geben, beginnen wir mit einem einfachen Beispiel aus dem Bereich der linearen Regression. (Mathematik Trauner Verlag HAK Band 3; Seite 88)

Zur Berechnung mit Excel gehen Sie vor, wie folgt:

Geben Sie die Koordinaten der gegebenen x-Werte (in Spalte B) und die Koordinaten der y-Werte
(in Spalte C) ein.

Die Gleichung der Regressionsgeraden hat die Gestalt y = a x + b.
Die Größen a und b sind zu berechnen. Geben Sie in den Zellen H4 (Startwert für a=1) und H5 (Startwert
für b =1) geeignete Startwerte für die folgende Berechnung vor.

In Spalte D berechnen wir die Funktionswerte Y auf der momentanen Geraden durch die gegebene Punktewolke aufgrund der aktuellen Startwerte für a und b.

Zelle D4: = $H$4*B4+$H$5 (beachten Sie die absolute Adressierung!)

Kopieren Sie den Inhalt der Zelle D4 in die darunterliegenden Zellen.

Zelle D5: = $H$4*B5+$H$5
Zelle D6: = $H$4*B6+$H$5
Zelle D7: = $H$4*B7+$H$5

Durch die Eingabe neuer Werte für a (Zelle H4) und b (Zelle H5) ändern sich die berechneten
Y-Werte.

Im zweiten Schritt werden die Daten der Tabelle in einer Punktewolke grafisch dargestellt.
Klicken Sie in eine beliebige Zelle der Tabelle mit den darzustellenden Daten und rufen Sie dann den Diagramm-Assistenten auf, indem Sie auf das Icon  klicken.

Wählen Sie aus der Liste der vorgeschlagenen Diagrammtypen das Punkt (XY) Diagramm (siehe Bild).

Klicken Sie auf Weiter> und wählen Sie in den folgenden Schritten die gewünschten Eigenschaften der Grafik.
Im Normalfall liefert Excel mit dem Diagramm-Assistenten eine durchaus brauchbare Grafik, die nach den durchlaufenen vier Schritten noch weiter formatiert werden kann.

Um einen Tabellenteil zu formatieren klicken Sie einfach mit der rechten Maustaste auf den entsprechenden Bereich.
Nach einigen Änderungen könnte Ihre Grafik ähnlich der Grafik im Bild aussehen. Die gegebene Punktewolke wurde in Form von Einzelpunkten dargestellt. Die berechneten Punkte wurden durch eine dünne Linie verbunden.
Diese Linie soll nach dem nächsten Schritt zur Regressionsgeraden werden.

Im dritten Schritt wird in Spalte E die Differenz der y-Werte berechnet und quadriert (Y-y)^2.

Zelle E4: = (D4-C4)^2

Kopieren Sie den Inhalt der Zelle E4 in die darunterliegenden Zellen.

Zelle E5: = (D5-C5)^2
Zelle E6: = (D6-C6)^2
Zelle E7: = (D7-C7)^2

Zelle E8: = SUMME(E4:E7) Summe der Abweichungsquadrate in Zelle E8.

Unsere Aufgabe ist es nun die Werte der Zellen H4 (Steigung a) und H5 (b) so zu ändern, dass der Wert in Zelle E8 minimal wird.
Wir berechnen das Minimum der Summe der quadratischen Abweichungen.

(Die Summe der quadratischen Abweichungen steht in Zelle E8.)
Diese Aufgabe übernimmt der Solver.

Rufen Sie den Solver auf, indem Sie auf Extras . Solver... klicken.

Es öffnet sich ein Fenster, in dem Sie die gewünschten Werte wie folgt eintragen:

Zielzelle: $E$8

Dieser Wert soll minimal werden.

Zielwert: ¥ Min

Minimum aktivieren

Veränderbare Zellen:
$H$4:$H$5

In diesen Zellen stehen die zu ändernden Parameter a und b.

Klicken Sie auf Lösen und Sie erhalten die Werte für a und b, für die Summe der quadratischen Abweichungen minimal ist. Die Grafik wird automatisch angepasst.

Anmerkung: Auf die Verwendung der Tabellenfunktion SUMMEMY2(Matrix_x;Matrix_y) wird wegen der besseren Verständlichkeit hier verzichtet.

Die Gleichung der Regressionsgeraden lautet: y = 1,212x – 57,24.

Eventuelle Ungenauigkeiten sind auf den Solver zurückzuführen und hängen stark von den Startwerten ab. Führen Sei die Rechnung mit folgenden Startwerte nochmals aus: a= 1,2 ; b= -57 .

Sollte der Solver keine Lösung finden, beginnen Sie mit anderen Startwerten oder klicken Sie auf Optionen... und erhöhen Sie die Zahl der Iterationen.

Excel stellt für viele Typen der Regression eine einfache Möglichkeit zur Verfügung, sogenannte TRENDLINIEN zu berechnen. Auf diese Option wird im Mathematikbuch Band 4 (Trauner-Verlag) näher eingegangen.

Hier sollen Typen der Regression gezeigt werden, die von Excel selbst nicht zur Verfügung gestellt werden.

Logistische Regression

Die logistische Regression ist besonders für Wachstumsprozesse, die eine natürliche Obergrenze haben, von besonderer Bedeutung.
Als Ausgangsgleichung für die logistische Regression verwenden wir:

 

Woche n	Gesamtzahl der verkauften PCs
1	100
5	400
10	1350
15	3200
16	3600
50	5000

Es liegt eine Verkaufsstatistik eines PC-Herstellers vor. Angegeben ist die Zahl der verkauften PCs nach n Wochen. Die maximale Absatzmenge nach ca. 50 Wochen wird auf 5000 Stück geschätzt.

Geben Sie zunächst in einer neuen Exceltabelle die gegebenen Punkte ein.

Die Koordinaten der gegebenen x-Werte stehen in Spalte B und die Koordinaten der y-Werte stehen in Spalte C.

Die Gleichung der Regressionsgeraden hat die Gestalt y = c/(1+a*e^(-b*x).
Die Größen a, b und c sind zu berechnen. Geben Sie in den Zellen H5, H6 und H7 geeignete Startwerte für die folgende Berechnung vor.

In Spalte D berechnen wir die Funktionswerte Y auf der Kurve durch die gegebene Punktewolke aufgrund der aktuellen Startwerte für a, b und c. (Startwerte a = b = c = 1)

Zelle D4: = $H$7/(1+$H$5*EXP((-$H$6)*B4)) (Beachten Sie die absolute Adressierung!)

Kopieren Sie den Inhalt der Zelle D4 in die darunterliegenden Zellen.

Durch die Eingabe neuer Werte für a (Zelle H4) und b (Zelle H5) ändern sich die berechneten
Y-Werte.
In Spalte E steht wieder das Quadrat der Abweichungen (Y – y)^2 und die Spaltensumme.

Zelle E4: = (D4-C4)^2

Kopieren Sie den Inhalt der Zelle E4 in die darunterliegenden Zellen.

Zelle E10: = SUMME(E4:E9) Summe der Abweichungsquadrate in Zelle E10.

Rufen Sie den Solver auf, indem Sie auf Extras . Solver... klicken.

Es öffnet sich ein Fenster, in dem Sie die gewünschten Werte wie folgt eintragen:

Zielzelle: $E$10

Dieser Wert soll minimal werden.

Zielwert: ¥ Min

Minimum aktivieren

Veränderbare Zellen:
$H$5:$H$7

In diesen Zellen stehen die zu ändernden Parameter a, b, c.

Klicken Sie auf Lösen und Sie erhalten die Werte für a, b und c, für die die Summe der quadratischen Abweichungen minimal ist.

Führen Sie die Rechnung mit den verbesserten Startwerten (zB: a = 63,1 ; b = 0,31 und c = 5000) nochmals aus, um ein exakteres Ergebnis zu erhalten!

(Wegen der größeren Genauigkeit wird diese Vorgangsweise dringend empfohlen!)

Lösung: 

Die grafische Darstellung der gegebenen und berechneten Daten ergibt kein gutes Bild der Realität.

Excel zeichnet nur die wenigen in der Tabelle gegebenen Punkte und verbindet diese durch eine Kurve.

Die dargestellte Kurve entspricht dem logistischen Wachstum nicht.

Um ein realistischeres Bild zu erhalten, ist das Einfügen weiterer Punkte, die aus den Werten der Rekursionskurve berechnet werden.

Fügen Sie zunächst nach Zeile 8 weitere Zeilen in das Arbeitsblatt und ergänzen Sie die Liste der x-Werte.

Kopieren Sie danach den Inhalt der Zelle D8 nach unten. Markieren Sie dann die Zellen B3 bis D13 der Tabelle und klicken Sie auf .

Zeichnen und formatieren Sie die Grafik mit Hilfe des Diagramm-Assistenten.

Die neue Grafik entspricht dem erwarteten logistischen Wachstum.

Anwendung aus der Kosten-Preistheorie

An die Grenzen der Möglichkeiten des Solvers soll uns die folgende Aufgabe aus dem Bereich der Kosten-Preistheorie führen:
Wir nehmen die Gesamtkostenkurve eines Produktes an durch die Gleichung . Es sind also fünf Größen zu bestimmen.

Gegeben sind die Gesamtkosten bei verschiedenen Produktionsmengen (siehe Excel-Tabelle).

Geben Sie in einem neuen Arbeitsblatt zunächst die Produktionsmenge x (Spalte B), die Gesamtkosten W (Spalte C) und die Startwerte der Parameter (Spalte H) ein.

In Spalte D berechnen wir die Funktionswerte Y auf der Kurve durch die gegebene Punktewolke aufgrund der aktuellen Startwerte für a, b, c, d und k. (Wir setzen zunächst alle Startwerte 1)

Zelle D4: =$H$5*B4^2+$H$6*B4+$H$7-$H$9/(B4+$H$8)

Kopieren Sie den Inhalt der Zelle D4 in die darunterliegenden Zellen.

Durch die Eingabe neuer Werte für die Parameter ändern sich die berechneten Y-Werte.
In Spalte E steht das Quadrat der Abweichungen (Y – y)^2 und die Spaltensumme.

Zelle E4: =(D4-C4)^2

Zelle E5: =SUMME(E4:E9)

Rufen Sie den Solver auf, indem Sie auf Extras . Solver... klicken.

Es öffnet sich wieder ein Fenster, in dem Sie die gewünschten Werte wie folgt eintragen:

Zielzelle: $E$10

Dieser Wert soll minimal werden.

Zielwert: ¥ Min

Minimum aktivieren

Veränderbare Zellen:
$H$5:$H$9

In diesen Zellen stehen die zu ändernden Parameter a, b, c, d, k.

Klicken Sie auf Lösen und Sie erhalten die Werte für die Parameter, für die die Summe der quadratischen Abweichungen minimal ist. Es ist, wegen der größeren Genauigkeit, notwendig, den Vorgang mit verbesserten Startwerten zu wiederholen!

1. Schritt: Berechnung mit den gegebenen Startwerten.

2. Schritt: Ausgehend von den im ersten Schritt ermittelten Parameterwerten für a bis k wird der Solver nochmals anwendet.

Es zeigt sich, dass sich die Abweichungen und die Summe der Abweichungsquadrate weiter verkleinert haben; eine nochmalige Anwendung des Solvers bringt allerdings kaum eine weitere Änderung der Parameter und der Summe. (Die Zahl der Iterationen kann unter Optionen auf zB. 10000 erhöht werden)

Markieren Sie dann die Zellen B3 bis D9 der Tabelle und klicken Sie auf  .

Zeichnen und formatieren Sie die Grafik mit Hilfe des Diagramm-Assistenten.

Setzt man die folgenden Werte (siehe folgende Exceltabelle) für die Parameter ein, ist die Summe der Abweichungsquadrate null.

Der Solver von Excel schafft es nicht, diesen Minimalwert zu berechnen!

Literatur:

Lehrerbegleitheft zum Mathematikbuch Band 3 für die 4. HAK/ 4. WB; Trauner Verlag

Mathematik für Handelsakademien Band 3 für die 4. HAK; Trauner Verlag

Microsoft Excel Funktionsverzeichnis; Microsoft Corporation

Peter Gäng; Excel für Naturwissenschaft und Technik; Data Becker