HAW / Dept.F+F / J. Abulawi Regressionsrechnung mit Excel SS2010 1/5

Versuchsauswertung mit Polynom-Regression in Excel Aufgabenstellung:

Gegeben sei die in Bild 1 gezeigte Excel-Tabelle mit Messwertepaaren yi und xi. Aufgrund bekannter physikalischer Zusammenhnge wird davon ausgegangen, dass zwischen yi und xi folgender Zusammenhang besteht:

2

210 ** iii xcxccy ++= (1)

Gesucht sind Werte fr die Koeffizienten c0, c1 und c2, fr die sich eine minimale Summe der Fehlerquadrate ergibt.

Lsungsweg 1: Diagrammanalyse mit der Funktion Trendlinie

Vorgehensweise:

Die Messwerte werden in einem Diagramm dargestellt. Anschlieend wird mit der Regressionsfunktion Trendlinie in das Diagramm ein die Messwerte annherndes Polynom 2. Grades eingefgt.

1. Den Excel-Zellbereich mit den Messwerten mit der Maus selektieren. 2. Den Diagrammassistenten aufrufen mit: Einfgen -> Diagramm. 3. Zunchst den Diagrammtyp festlegen (Schritt 1 von 4):

Diagrammtyp: Punkt (XY) Untertyp: Punkte mit interpolierten Linien

4. Die Auswahl mit Weiter> besttigen. 5. Die Quelldaten des Diagramms kontrollieren (Schritt 2 von 4)

Hierzu die Registerkarte Reihe anklicken und die Zellbezge fr die X- und Y-Werte berprfen. Fr die Bezeichnung der Datenreihe kann statt eines Zellbezugs ein benutzerdefinierter Text in das Feld Name: eingegeben werden.

Bild 1: Messwerte im Excel-Arbeitsblatt

Bild 2: Erste Eingaben im Excel-Diagramm-Assistenten

HAW / Dept.F+F / J. Abulawi Regressionsrechnung mit Excel SS2010 2/5

6. Die Eingaben mit Weiter> besttigen. 7. Nun die weiteren Diagrammoptionen

definieren (Schritt 3 von 4):

Titel: Hier werden die Diagramm-beschriftungen eingetragen.

Achsen Gitternetzlinien Standardwerte Legende bernehmen Datenbeschriftungen

8. Die Eingaben mit Weiter> besttigen.

9. Schlielich wird das Diagramm durch Klicken auf Ende als Objekt in das aktive Tabellenblatt eingefgt (Schritt 4 von 4).

10. Im eingefgten Diagramm mit der Maus langsam ber die angezeigte Kurve fahren, bis der Hilfetext Reihe Messwerte erscheint. Dann mit der rechten Maustaste das Kontextmen einblenden und dort die Funktion Trendlinie hinzufgen anwhlen.

11. Als Typ fr die Trendlinie Polynomisch mit der Ordnung 2 anwhlen.

12. Im Men Optionen sollte Gleichung im Diagramm darstellen angewhlt werden. Die automatische Bezeichnung der Trendlinie kann im Men Optionen durch einen benutzerdefinierten Text ersetzt werden.

13. Einstellungen mit OK besttigen. 14. Ggf. im Diagramm das Textfeld mit der Regressions-

formel an eine geeignete Stelle verschieben.

15. Das fertige Diagramm ist in Bild 4 gezeigt.

Bild 3: Mens zur Diagramm- und Trendlinienerstellung

Bild 4 (links):

Resultierendes Diagramm mit Messwerten und polynomischer Regressionskurve.

HAW / Dept.F+F / J. Abulawi Regressionsrechnung mit Excel SS2010 3/5

berprfung der per Trendlinie ermittelten Koeffizienten

16. Nun werden die von Excel ermittelten Regressionskoeffizienten berprft. Dazu werden einer neuen Spalte Y-Werte mit der Trendlinienformel berechnet siehe Bild 5.

(Die drei Koeffizienten wurden hier in die Zellen E1 bis E3 eingetragen, sie htten aber auch als Zahlenwerte direkt in der Formel stehen knnen.)

Bild 5: Formeleingabe fr die Kontrollrechnung Die berechnete Datenreihe wird nun zur Kontrolle ins Diagramm einfgt.

17. Hierzu mit der rechten Maustaste auf den Diagrammrand klicken und im Kontext-men Datenquelle anwhlen.

18. Im Men Reihe unter dem Feld Datenreihe auf die Schaltflche Hinzufgen klicken.

19. In das Feld Name einen Titel fr die neue Datenreihe eingeben.

20. Anschlieend in das Feld X-Werte klicken und dann in der Exceltabelle die Zellen mit den X-Werten markieren.

21. Analog den Zellbezug fr die berechneten Y-Werte eingeben.

22. Mit OK besttigen. Wenn im Diagramm die Anzeige der Datenpunkte bei der neu berechneten Regressionskurve strt, kann dies folgendermaen korrigiert werden.

Bild 6: Einfgen der Kontrollrechnung in das Diagramm

23. Rechtsklick auf die Datenreihe Regressionsrechnung und Menpunkt Datenreihen formatieren anwhlen.

24. Im Men Muster fr Markierung die Einstellung Ohne anwhlen.

Das resultierende Diagramm mit den Mess-werten und den beiden Regressionskurven ist als Bild 8 auf der folgenden Seite abgebildet. Obwohl die mit Regressionsrechnung bezeichnete Datenreihe mit der von Excel ermittelten Trendlinien-Formel berechnet wurde, weicht die manuell berechnete Regressionskurve im oberen Wertebereich von der automatisch erzeugten Trendlinie ab.

Dies liegt daran, dass die drei Koeffizienten c0, c1 und c2 der Trendlinien-Formel von Excel stark gerundet angezeigt wurden.

Bild 7: Formatierung der Datenreihe

HAW / Dept.F+F / J. Abulawi Regressionsrechnung mit Excel SS2010 4/5

Fazit: Die automatisch von Excel eingestellte Genauigkeit fr die Anzeige der mit der Trendlinienfunktion ermittelten Koeffizienten reicht nicht aus!

Bild 8: Excel-Diagramm mit Messwerten, Trendlinie und ungenauer Regressionskurve

Erforderliche Abhilfe bzw. Korrektur:

25. . Auf die im Diagramm eingefgte Gleichung mit der rechten Maustaste klicken und dann im Kontextmen die Funktion Datenbeschriftungen formatieren anklicken.

26. Im erscheinenden Menfenster kann in der Kategorie Zahlen nun die Zahlenformatierung von 'Standard' auf 'Zahl' gendert und eine geeignete Anzahl von Dezimalstellen (z.B. 9 oder 10) festgelegt werden.

27. Nun werden die berechneten

Regressionskoeffizienten mit ausreichender Genauigkeit im Diagramm angezeigt:

HINWEIS:

Dieselben Regressionskoeffizienten kann man auch ohne Diagramm direkt aus der Wertetabelle berechnen, wenn man die Excel-Arbeitsblattfunktion RGP() benutzt. Wie das geht, ist auf der nachfolgenden Seite als Lsungsweg 2 beschrieben.

Diese Kurve ist zu

ungenau!

HAW / Dept.F+F / J. Abulawi Regressionsrechnung mit Excel SS2010 5/5

Lsungsweg 2 Datenanalyse mit der Funktion RGP

Vorgehensweise:

Die gesuchten Koeffizienten sollen ohne den Umweg der Diagrammerstellung direkt mit der Funktion RGP zur Analyse linearer Trends ermittelt werden. Die Ausgangsgleichung der Aufgabenstellung (1) entspricht hier dem Ansatz der multiplen linearen Regression, fr die Excel folgende Formel zugrunde legt:

bxmxmy ++= 2211 ** (2)

Die Gegenberstellung mit der Ausgangsformel (1) ergibt folgende quivalenzen:

iyy = (3)

0cb = (4)

11 cm = (5)

ixx =1 (6)

22 cm = (7)

2

2 ixx = (8)

Excel bentigt demzufolge fr die Auswertung eine Tabelle, die auer den Spalten mit den x- und y-Werten noch eine Spalte mit den Werten x enthlt.

Mit der oben rechts abgebildeten Tabelle kann die RGP-Funktion folgendermaen eingesetzt werden. (Die Microsoft-Excel-Hilfe bietet ausfhrliche Informationen zur Anwendung der Funktion RGP.)

1. In der Exceltabelle wird ein Zellenfeld aus 3 x 5 Zellen markiert. 2. In das Eingabefeld wird folgende Matrixformel eingegeben: =RGP(E6:E35;B6:C35;WAHR;WAHR) 3. Achtung: Die Eingabe der Matrixformel wird mit STRG+UMSCHALT+EINGABE abgeschlossen. 4. Die Ergebnisse der RGP-Funktion erscheinen in folgender Reihenfolge im markierten Zellenfeld:

Die gesuchten Koeffizienten lauten demzufolge:

0112,00 == cb (9)

00012,011 == cm (10)

0000275,022 == cm (11)

Ergebnis: Die mit der RGP-Funktion berechneten die Koeffizienten stimmen mit der im Diagramm angezeigten Trendlinie berein, wenn letztere mit ausreichend hoher Genauigkeit angezeigt wird.