Wie interpoliert Excel? etwas länger

[Volker_Malchow]

Hallo,

da ich selten die Diagramm-Funktionen nutze, kann es durchaus sein, dass ich etwas falsch mache.

Um in Übung zu bleiben habe ich (aus einem anderen Thread entwickelt) folgende Aufgaben gebastelt:

a)Tabelle mit Formel (x,y) erstellt, quadratische Funktion, Interpoliere exponentiell

Da die Daten per Formel erstellt wurden sind sie nicht verrauscht, trotzdem weigert sich Excel eine exponentielle Interpolation vorzunehmen und bietet nur polynomische an.

Das ist zwar von den Daten her ok., aber ich habe z.B. solche Daten in einer Meßreihe erhalten und weiß, dass es eine e-Fkt. sein muss, wie bewege ich Excel dazu, mir eine Lösung anzubieten

b) wie a), aber zusätzlich z als zweite Fkt. eingeführt, hier die Tabelle:

x y z

0 2 -2,963
1 2 -2,939
2 6 -2,9
3 14 -2,835
4 26 -2,728
5 42 -2,552
6 62 -2,261
7 86 -1,782
8 114 -0,991
9 146 0,3115
10 182 2,46

Die Darstellung war ok.

Die Fkt. waren:
y = 2*x^2 - 2x + 2

z = 0,1* exp(0,5*(x - 2)) -3

Die Ergebnisse der Trendlinie:

y = 0,0917*x^2 - 0,477*x - 2,569

z = 2,43559*exp(0,4823*x)

Da ich von ungestörten Daten ausging, stimmen diese Ergebnisse schlicht nicht.

Was mache ich falsch?

Ich vermute bei der Potenzfkt., dass der extreme Unterschied zwischen den Minima und Maxima zu Problemen führt, also die Skalierung Excel überfordert, naja ich wollte natürlich Extreme testen.

Bei der Exp-Fkt. vermute ich, das eine in x und y-Richtung verschobene exp-Fkt schlicht nicht vorgesehen ist.

Vielleicht kann mir jemand einen Teil meiner Fragen beantworten.

Allein der Begriff „Tendlinie“ ist eigentlich ein Indiz dafür, dass es ausschließlich für kaufmännische Anwendungen gedacht ist und Mathe-Fkt. ein add-on sind.

Zur Trendlinie, sie wird nur dargestellt zwischen Anfangs- und Endpunkt, das wäre aber Interpolation, dieser Begriff taucht aber nirgends auf.

Natürlich kann ich dann mit der erhaltenen Formel eine neue Darstellung machen mit einem anderen Wertebereich, aber eigentlich sollte man direkt auf dem Schirm extrapolieren können, dann hätte der Begriff einen Sinn.

Danke für die Geduld.

LG Volker

[Jo1_a88223]

Hallo,

Ich habe es mit deinen Daten versucht und bekomme die korrekten Ergebnisse, nämlich:

y = 2*x^2 - 2x + 2

und

z = 0,0368 * exp(0,5*x) - 3

Bei der ersten Trendlinie must du einen Fehler gemacht haben: Dein Ergebnis bekomme ich, wenn ich die exponentielle Folge („z“) versuche, durch die Quadratische Funktion zu beschreiben. Du hast also offensichtlich für die Trendberechnung die falschen Werte gewählt. Achte darauf, dass im Diagramm auch die „y“-Werte gezeigt werden!

Bei der zweiten Trendlinie mußt du einen Trick anwenden:

Excel kennt kein absolutes Glied (additive Konstante) in einem exponentiellen Trend. Eine Exponentialfunktion ohne abs. Glied KANN aber keine negativen Werte liefern. Deine Daten enthalten negative Werte, daher bietet Excel dir für diese Daten gar keinen exponentiellen Trend an.

Die Formel war ja 0,1*EXP(0,5*(X-2)) -3
die „-3“ ist die störende additive Konstante.

Trick: Erstelle Daten, wo du die Konstante korrigierst (also +3 zum „z“-Wert addierst: Z’ = Z+3). Dann bekommst du folgende Werte:

0,04; 0,06; 0,10; 0,16 …

alle positiv. Sie gehorchen der Funktion Z’ = 0.1*EXP(0.5*(X-2)). Für diese Werten läßt du den exponentiellen Trend berechnen.

Als Ergebnis bekommst du von Excel z = 0,0368 * exp(0.5*x). Da mußt du jetzt die vorher addierte Konstnate (+3) wieder abziehen. Das fertige Ergebnis ist dann also z = 0,0368 * exp(0.5*x) - 3.

Die Formel sieht anders aus als die Vorgabe (z = 0,1* exp(0,5*(x - 2)) - 3) - aber etwas umformung zeigt, dass es das selbe ist:

Setzen wir beide gleich:

0,0368 * exp(0.5*x) - 3 = 0,1 * exp(0,5*(x - 2)) - 3

Der Ausdruck exp(0,5*(x - 2)) läst sich umformen:

exp(0,5*(x - 2)) = exp(0,5*x - 1) = exp(0.5*x)*exp(-1)

Der Ausdruck exp(-1) ist eine Konstante, nämlich 0,36787…
Also sieht unsere Gleichung folgendermaßen aus:

0,0368 * exp(0.5*x) - 3 = 0,1 * exp(0,5*x)*0,368 - 3

Die beiden konstanten Faktoren 0,1 und 0,368 rechts kann man multiplizieren (=0.0368) und bekommt

0,0368 * exp(0.5*x) - 3 = 0,03680 * exp(0,5*x) - 3

Wie du siehst, steht auf beiden Seiten das selbe! q.e.d.

LG
Jochen

[Volker_Malchow]

Hallo Jochen,

erstmal ganz herzlichen Dank für Deine ausführliche Antwort, sie hilft mir schon sehr viel weiter.

y = 2*x^2 - 2x + 2

Bekomme ich auch heraus, wenn ich die polynomische Interpolation wähle. Also bei Beispiel a), aber warum weigert sich Excel mir zu ermöglichen exponentiell zu interpolieren?

Es kann doch sein, dass ich weiß es handelt sich um eine e-Fkt., ok ich habe jetzt der Einfachheit nur quadratische Daten erzeugt, natürlich hätte ich eine e-Fkt. nehmen können und die Daten „verrauschen“, hab ich noch nicht probiert, werde ich aber auch noch machen oder kann Excel nicht ausgleichen? Hm, hab ich noch nie versucht.

z = 0,0368 * exp(0,5*x) - 3

Das muss ich mir nochmal genau anschauen, dass ich die e-Fkt aufspalten kann, habe ich übersehen, da ich mich auf die Verschiebung in x-Richtung konzentriert habe, die ist in meiner Darstellung offensichtlich.

Excel kennt kein absolutes Glied (additive Konstante) in einem
exponentiellen Trend. Eine Exponentialfunktion ohne abs. Glied
KANN aber keine negativen Werte liefern. Deine Daten enthalten
negative Werte, daher bietet Excel dir für diese Daten gar
keinen exponentiellen Trend an.

Ok, dass habe ich natürlich voraus gesetzt, da es keinen vernünftigen Grund gibt, dieses nicht zu erlauben.

Trick: Erstelle Daten, wo du die Konstante korrigierst (also

Der Trick ist verständlich!

Deine Umformung ist verständlich, s.o. hab ich übersehen.
Wie ich die Formel gewinnen kann ist also klar, aber mein Diagramm entspricht ja nicht den Daten, die ich vorgegeben habe.

Ich muss jetzt also das Diagramm insgesamt um (-3) verschieben, dann ist aber die e-Fkt. wieder im neg. Bereich. Geht das denn?

Der Hintergrund ist der:

ich bin oft gefragt worden, weshalb ich nicht Excel für meine Berechnungen verwende. Aber bei naturwissenschaftlichen Anwendungen bin ich halt immer mal wieder an solche Grenzen gestoßen. Deshalb schreib ich mir dann lieber schnell ein Pascal-Programm oder nutze einen Fkt.-Plotter.

Nur die Kaufleute wollen immer alles in Excel haben, da sie den anderen Programmen nicht trauen.

Für´s Kaufmännische ist es sicher ein gutes Programm, aber …

Nur ich muss mal wieder damit arbeiten, zuletzt habe ich es wohl vor 5 Jahren benutzt und man vergißt viel.

Ich wünsche Dir ein schönes Wochenende, ein Sternchen wird gleich abgeschickt.

Gruß Volker

[Jo1_a88223]

Hallo,

y = 2*x^2 - 2x + 2

Bekomme ich auch heraus, wenn ich die polynomische
Interpolation wähle. Also bei Beispiel a), aber warum weigert
sich Excel mir zu ermöglichen exponentiell zu interpolieren?

Tut es nicht. Du kannst diese Daten euch mit einer e-Funktion interpolieren, das Ergebnis ist dann y = 2.4359*EXP(0.4823*x). Man erkennt aber gut, dass diese Lösung die Daten nicht wirklich beschreibt.

und die Daten „verrauschen“, hab ich noch nicht probiert,
werde ich aber auch noch machen oder kann Excel nicht
ausgleichen?

Aber sicher geht das. Genau dazu ist die Regression ja da.

Ich muss jetzt also das Diagramm insgesamt um (-3)
verschieben, dann ist aber die e-Fkt. wieder im neg. Bereich.
Geht das denn?

Wenn du mit Excel die Trendlinie im Diagramm ausrechnen lassen willst (also OHNE die additive Konstante) UND sie aber gleichzietig MIT additiver Konstante im Diagramm anzeigen lassen willst, dann mußt du leider wieder tricksen:

Entweder:

Du machst 2 Datenreihen: (1) die Origional-Daten und (2) die um die additive Konstante korrigierten Daten. Du erstellst 2 Diagramme, je eines mit den original-Daten (1) und eins mit den korrigierten Daten (2). Im das Diagramm (2) läßt du dir die exp. Trendlinie berechnen. Dann erstellst du eine dritte Datenreihe (3), in der du für jeden x-Wert anhand der Parameter aus Diagramm (2) die Werte der Trendlinie MIT additiver Konstante berechnen läßt. Diese berechneten Werte sind nun die dritte Datenreihe, die du im Diagramm (1) als Trendlinie anzeigen läßt (Datenreihe hinzufügen; Format: keine Punkte, dicke Linie, Linie glätten). Fertig.

Oder:

Du rechnest die Parameter des Modells aus den um die additive Konstante korrigierten Daten mit der Funkton RKP, berechnest daraus die Y-Werte der Trendlinie und zeigst die berechneten Trendlinien-Werte als Datenreihe im Diagramm an (so wie im obigen Beispiel). Die Parameter der Exponentialfunktion Y=B*Exp(A*X) bekommst du statt aus einem extra-Diagramm direkt aus den X- und Y-Werten mit

A = LN(INDEX(RKP(Y-WERTE;Y-WERTE);1))
B = INDEX(RKP(Y-WERTE;Y-WERTE);2)

Oder:

Mit dem Solver von Excel (unter Menü: Extras/Solver…; evtl musst du bei Extras/Add-Inns… erst ein Häkchen bei „Solver machen“).
Hier kannst du BELIEBIGE Modelle lösen lassen, also auch gleich Exponentielle Modelle MIT additiver Konstante, oder Schwingungen, oder oder oder! Das Prinzip ist schnell erklärt, die Details solltest du dir aber selbst beibiegen. Im Internet und der nächsten Bibliothek gibt’s mehr als genug dazu. Das Prinzip: Du hast ein Modell wie zB. Y=B*exp(A*X)+C (hier also mit C als Konstante). Das Modell hat unbekannte, anhand der Daten zu bestimmende Parameter (hier: A, B und C; C ist die Konstante, mit der die Excel-Trendfunktion nicht klarkam). Du gibst irgendwelche Schätzwerte für die Parameter vor, die du in bestimmte Zellen ins Tabellenblatt schreibst. Anhand dieser Werte werden nun für jeden X-Wert der Daten ein zugehöriger Y’-Wert-berechnet. Dann werden die Abweichungsquadrate (Y-Y’)² berechnet und diese Abweichungsquadrate werden alle aufsummiert. Diese Summe steht dann wiederum in einer Zelle in der Tabelle. Dem Solver teilst du dann mit, dass du den Wert in dieser Zelle (die Abweichungsquadrate) minimieren lassen willst, wobei die Werte in den Zellen, wo die Parameter stehen, variiert werden dürfen. Der Solver sucht dann eine Lösung, die du allerdings auf ihre Sinnhaftigkeit prüfen solltest (also im Diagramm anzeigen lassen!).

ich bin oft gefragt worden, weshalb ich nicht Excel für meine
Berechnungen verwende. Aber bei naturwissenschaftlichen
Anwendungen bin ich halt immer mal wieder an solche Grenzen
gestoßen. Deshalb schreib ich mir dann lieber schnell ein
Pascal-Programm oder nutze einen Fkt.-Plotter.

Tipp: http://cran.r-project.org/

Nur die Kaufleute wollen immer alles in Excel haben, da sie
den anderen Programmen nicht trauen.

Oh man, die Kaufleute… als würde Excel alles richtig machen…

Für´s Kaufmännische ist es sicher ein gutes Programm, aber …

Hmmm, wenn’s darum geht, aus nichtssagenden Werten werbewirksame Business-Charts zu machen vielleicht…

LG
Jochen