Mit Regressionsrechnung quadratische Funktion aufstellen

03.01.2015, 17:41

Jator08

Mit Regressionsrechnung quadratische Funktion aufstellen

Hallo,
ich brauche bitte Hilfe zur Nachfragefunktion. Und zwar habe ich bei der Aufgabenstellung 5 verschiedene Stück und 5 verschiedene Preiswerte (die x und y Werte in der Datei)
Aus diesen 10 Daten muss ich mithilfe der Regressionsrechnung im Excel sowohl eine lineare, als auch eine quadratische Funktion erstellen.

a und b der lineare Funktion kann ich mit den Excel-Funktionen "=steigung" und "=achsenabschnitt" ermitteln.

Aber wie berechne ich die quadratische Funktion? Gibt es da auch eigene Befehle im Excel? Oder muss ich die lineare Funktion zur Hilfe nehmen?

Danke

[attach]36641[/attach]

04.01.2015, 10:44

Huggy

Auf diesen Beitrag antworten »

RE: Mit Regressionsrechnung quadratische Funktion aufstellen
Das kannst du mit der Excelfunktion RGP erledigen. RGP macht im einfachsten Fall eine lineare Regression für Datenpaare $\begin{eqnarray*} (x, y) \end{eqnarray*}$ . d. h. es passt nach der Methode der kleinsten Quadrate an die Datenpaare eine Funktion

$\begin{eqnarray*} y= b+mx \end{eqnarray*}$

an. Ausgegeben werden der Achsenabschnitt $\begin{eqnarray*} b \end{eqnarray*}$ und die Steigung $\begin{eqnarray*} m \end{eqnarray*}$ und eventuell weitere statistische Daten über die Güte der Anpassung. Du hast statt der Funktion RGP die beiden Funktionen Steigung und Achsenabschnitt benutzt. Das ist in Ordnung, funktioniert aber nur, wenn man nur eine x-Variable hat.

RGP kann aber auch eine lineare Regression für Datentupel $\begin{eqnarray*} (x_1, ..., x_n,y) \end{eqnarray*}$ machen. Es passt dann eine lineare Funktion der Form

$\begin{eqnarray*} y=b+m_1x_1+ ... +m_nx_n \end{eqnarray*}$

an. Ausgegeben werden $\begin{eqnarray*} b \end{eqnarray*}$ und alle $\begin{eqnarray*} m_i \end{eqnarray*}$ und eventuell weitere statistische Daten. Das kann man auch benutzen, um Polynome anzupassen, denn die $\begin{eqnarray*} x_i \end{eqnarray*}$ müssen nicht voneinander unabhängige Variable sein. Man kann z.B. $\begin{eqnarray*} x_1 = x \end{eqnarray*}$ und $\begin{eqnarray*} x_2 = x^2 \end{eqnarray*}$ benutzen und bekommt dann eine quadratische Anpassung

$\begin{eqnarray*} y=b+m_1x+m_2x^2 \end{eqnarray*}$

also das, was du suchst. Dazu fügst du neben der Spalte mit den x_Werten einfach noch eine Spalte mit den Quadraten der x-Werte ein und wendest auf den gesamten Datenbereich die RGP-Funktion an.

RGP ist eine sogenannte Array-Funktion, also eine Funktion die nicht nur einen, sondern mehrere Ausgabewerte hat. Array-Funktionen erfordern eine besondere Form der Eingabe, sonst funktionieren sie nicht. Wie man Array-Funktionen eingibt, ist in der Excelhilfe bei der RGP-Funktion und bei jeder anderen Array-Funktion erklärt.

Die RGP-Funktion hat noch 2 Schalter. Mit dem einen entscheidet man, ob $\begin{eqnarray*} b \end{eqnarray*}$ zwangsweise Null gesetzt werden soll. Falls man das macht, geht die Anpaasung immer durch den Nullpunkt. Mit dem anderen entscheidet man, ob weitere statistische Daten ausgegeben werden sollen oder nicht.

04.01.2015, 12:21

Jator08

Auf diesen Beitrag antworten »

RE: Mit Regressionsrechnung quadratische Funktion aufstellen
Danke für deine ausführliche Erklärung!

Zitat:

Original von Huggy

$\begin{eqnarray*} y=b+m_1x+m_2x^2 \end{eqnarray*}$

also das, was du suchst. Dazu fügst du neben der Spalte mit den x_Werten einfach noch eine Spalte mit den Quadraten der x-Werte ein und wendest auf den gesamten Datenbereich die RGP-Funktion an.

Das habe ich nun gemacht. Aber da bekomme ich ja nur einen Wert raus (b)
Wie berechne ich die anderen beiden Werte (a, c) der quadratischen Funktion?

04.01.2015, 13:38

LordKelvin

Auf diesen Beitrag antworten »

RE: Mit Regressionsrechnung quadratische Funktion aufstellen
Hallo
Ich habe das mal mit einem anderem Programm gelöst und kam

auf:
$\begin{eqnarray*} ax^2+bx+c a= -1,42 b= 41,429 c= -57 \end{eqnarray*}$
Ich habe die Tabellenwerte verwendet die oben angegeben waren.

Ansonsten kann man das als LGS mit den Werten sehr schnell bestimmen.

Also
$\begin{eqnarray*} 1. 16^2a+16b+c=240 \end{eqnarray*}$
usw.
dann kann man das mit Papier und Bleistift ausrechnen
Vg LordK

04.01.2015, 17:12

Huggy

Auf diesen Beitrag antworten »

RE: Mit Regressionsrechnung quadratische Funktion aufstellen

Zitat:

Original von Jator08
Das habe ich nun gemacht. Aber da bekomme ich ja nur einen Wert raus (b)

Das passiert, wenn man RGP nicht korrekt als Arrayfunktion eingibt. Hast du dir durchgelesen, wie man das macht und die Anweisungen exakt befolgt?

In der Excelhilfe gibt es Beispiele, die man in ein Tabellenblatt kopieren kann. Damit kann man kontrollieren. ob man alles richtig gemacht hat.

04.01.2015, 18:13

Jator08

Auf diesen Beitrag antworten »

Ah okay, jetzt funktionierts. Man muss einfach 3 Felder markieren, und am Ende dann mit "STRG, SHIFT, ENTER" bestätigen.

Ich bekomme die gleichen Daten wie LordKelvin raus. Danke Wink

Noch eine Frage:
Kann man mit diesem Befehl auch eine kubische Funktion aufstellen? Hab nämlich zusätzlich noch eine Spalte mit "x³" eingefügt, und dann halt 4 Felder markiert.
Aber bei x kommt da "-4,5924E-0,5 raus

05.01.2015, 10:29

Huggy

Auf diesen Beitrag antworten »

Zitat:

Original von Jator08
Kann man mit diesem Befehl auch eine kubische Funktion aufstellen? Hab nämlich zusätzlich noch eine Spalte mit "x³" eingefügt, und dann halt 4 Felder markiert.

Ja.

Zitat:

Aber bei x kommt da "-4,5924E-0,5 raus

Meinst du den Koeffizienten von $\begin{eqnarray*} x^3 \end{eqnarray*}$ ? Da bekomme ich $\begin{eqnarray*} 1.187\cdot10^{-14} \end{eqnarray*}$ . Die anderen Koeffzienten bleiben gegenüber der quadratischen Anpassung fast unverändert. Diese ist schon so gut, dass man mit einer kubischen Anpassung praktisch keine Verbesserung mehr bekommt. Deswegen wird der kubische Koeffizient so klein.

Es gibt übrigens noch eine andere Möglichkeit, die Regression mit Excel durchzuführen. Die erspart es einem sogar, die zusätzlichen Spalten mit den Quadraten, Kuben usw. einzufügen. Man erstellt mit den (x,y)-Werten zunächst mit Excel ein (x, y)-Diagramm. Dem fügt man einfach den gewünschten Typ Trendlinie hinzu. Wie man das macht, kann für verschiedene Excelversionen etwas unterschiedlich sein. Bei mir (Office 2007) geht es so:
Man markiert das (xy)-Diagramm, falls es nicht schon markiert ist. Dann geht man zu dem Hauptmenü Layout. Dort findet man entweder direkt oder als Unterpunkt zu Analyse das Menü Trend. Dort geht man auf "Weitere Trendlinienoptionen" und wählt die gewünschte Trendlinie aus. Bei einem Polynom wird mit dem ominösen Punkt "Reihenfolge" der Grad des Polynoms angegeben. Damit man die Koeffizienten sieht, klickt man noch "Formel im Diagramm anzeigen an" an. Das Ergebnis sieht bei einem Polynom 3.Grades so aus:

[attach]36666[/attach]

05.01.2015, 15:32

Jator08

Auf diesen Beitrag antworten »

Zitat:

Original von Huggy

Meinst du den Koeffizienten von $\begin{eqnarray*} x^3 \end{eqnarray*}$ ? Da bekomme ich $\begin{eqnarray*} 1.187\cdot10^{-14} \end{eqnarray*}$ . Die anderen Koeffzienten bleiben gegenüber der quadratischen Anpassung fast unverändert. Diese ist schon so gut, dass man mit einer kubischen Anpassung praktisch keine Verbesserung mehr bekommt. Deswegen wird der kubische Koeffizient so klein.

Ahja, bekomme ich jetzt auch raus. Hab für x³ versehentlich x² mit x² multipliziert, anstatt x² * x

Danke für deine Mühe smile

Neue Frage »

Antworten »

Mit Regressionsrechnung quadratische Funktion aufstellen

Verwandte Themen