Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Statistik

Beitrag: Herleitung einer linearen und quadratischen Regressionsfunktion

Aufgabe
Mittels Diagramm-Trendlinie einfügen kann im Diagramm die Gleichung einer Kurve angezeigt werden.
Geht das auch mit einer Formel?
Aus einer Reihe von X und Y Werten soll eine lineare bzw. quadratische Funktion (polynomisch) hergeleitet werden.

       A               B               C               D               E               F               G       
1 x  y    x  y    3  
2 18,5    21    -12  
3 22    18    30  
4 25,5    21     
5 29    30     
6 32,5    45     
7              
8              
9 y=3,5x+15,0            y=3,0x²-12,0x+30,0 

Lösung
Lineare Regression:

In A2 bis A6 stehen die X-Werte, z.B. 1 bis 5.

In B2 bis B6 stehen die Y-Werte, schreibe in z.B. B2
=3,5*A2+15

und kopiere das bis B6.

Die Regressionsgerade lautet
="y = "&RUNDEN(INDEX(RGP(B2:B6;A2:A6);1);3)&"x "&TEXT(INDEX(RGP(B2:B6;A2:A6);2);"+ 0,0##; - 0,0##")

Die Genauigkeit kann durch den Parameter "Anzahl_Stellen" der RUNDEN-Funktion (für x) bzw. den Formatierungsanzeiger 0,0## der TEXT-Funktion (für c) eingestellt werden.

Quadratische Regression:

In D2 bis D6 stehen die X-Werte, z.B. 1 bis 5.

In E2 bis E6 stehen die Y-Werte, schreibe in z.B. E2
=3*D2^2-12*D2+30

Schreibe in G1
=INDEX(RGP(E$2:E$6;D$2:D$6^{0.1.2};0;0);ZEILE())
und kopiere nach G2 und G3

Die quadratische Regressionsfunktion lautet
="y = " &TEXT(G1;"0,0##x²;- 0,0##x²;""""")&" "&TEXT(G2;"+ 0,0##x;- 0,0##x;""""")&" "&TEXT(G3;"+ 0,0##;- 0,0##;""""")

Zur Einstellung der Genauigkeit siehe oben.

Siehe dazu auch das erweiterte Modell zur dynamischen Polynomregression von Klaus Kühnlein.