[Excel] Interpolation von Messwertreihen (ex: Schwieriger Excel Fall! Bitte um Hilfe)

[Konstantin1812]

Hallo,

es geht um folgende Excel-Tabelle. Brauche dringend Hilfe, bekomme das nicht gebacken.

Ich benötige eine Formel, welche mir die Werte ansteigen und absteigen lässt. Also, ich möchte, dass die Werte gleichmäßig ansteigen ohne die tatsächliche Summe aller 4 Werte aus Spalte B ausser acht zu lassen. Wenn dann die Stunde gewechselt wird also z.b. von Stunde 1 auf Stunde 2 und die Energiemenge ansteigt, darf der letzte Wert der Stunde 1 nicht höher sein als der erste in Stunde 2. Also die Werte müssen weiterhin gleichmäßig ansteigen.

Wenn nun bei einem Stundenwechsel die Energiemenge niedriger wird, sollen die Werte gleichmäßig absteigen. Wobei der erste wert mit der niedrigeren Energiemenge nicht höher sein darf, als der letzte Wert der vorherigen Stunde mit dem höheren Energiewert.

Versteht Ihr was ich meine??

Bitte um Lösungshilfe

Danke

Konstantin1812

[Eye-Q]

Versteht Ihr was ich meine??

Öhm, nein... :confused:

Wenn ich das richtig verstehe, soll da ein durchschnittlicher Stromverbrauch ausgerechnet werden, oder? Können die Werte auch innerhalb einer Stunde variieren oder bleibt z.B. Stunde 1 immer auf einem Wert bei "Energie/h"?

[Konstantin1812]

Die Werte innerhalb einer Stunde müssen sogar variieren, beziehungsweise sollen die gleichmäßig ansteigen.

Zur Verdeutlichung:

Summe Stunde 1 = 12

Werte Stunde mit Anstieg:

1. Wert 1,80

2. Wert 2,80

3. Wert 3,60

4. Wert 3,80

Die Summe aus diesen neuen Werten ist weiterhin 12

[Thanks-and-Goodbye]

Und woher kommen diese Werte?

[Konstantin1812]

Und woher kommen diese Werte?

Diese Werte habe ich erstellt.

Sämtliche Werte sind fiktiv.

Es geht darum, das diese Werte Prognosewerte für Windstromeinspeisung sind. Es wird ein Stundenwert übermittelt. Zum Beispiel am Tag X um 1 Uhr werden 3 MWh erzeugt. Weiter geht es darum diesen Wert in 1/4 Stunden darzustellen. Man könnte her gehen und den Stundenwert durch 4 teilen. Somit hätten wir 4 gleiche 1/4 Stundenwerte, was unrealistisch wäre. Daher sollen die Werte gleichmäßig ansteigen, wie etwa in meinem vorherigen Beitrag dargestellt.

[Thanks-and-Goodbye]

Genauso unrealistisch ist es aber, die Zahlen nach einem festen Schema ansteigen zu lassen.

Fragen wir doch mal weiter: was willst du denn durch die Verteilung der Werte erreichen?

[Konstantin1812]

Genauso unrealistisch ist es aber, die Zahlen nach einem festen Schema ansteigen zu lassen.

Fragen wir doch mal weiter: was willst du denn durch die Verteilung der Werte erreichen?

Es ist wahrscheinlicher, dass der Wind in kleinen Schritten seine Leistung vermehrt bzw. vermindert anstatt abrupt zwischen 2 unterschiedlichen Energiemengen hin und her schwankt.

Also zum Beispiel: Stunde 1: 3MWh

Stunde 2: 4MWh oder höher

Man kann dies auch glätten nennen.

Es ist schwierig zu erklären. Gibt es denn eine Möglichkeit dies mit Excel darzustellen?

[Thanks-and-Goodbye]

Also kurz: ihr habt stündliche Messewerte und wollt aus dem Kurvenverlauf der Messwerte das so glätten, als ob ihr viertelstündliche Messwerte habt?

Es ist schwierig zu erklären. Gibt es denn eine Möglichkeit dies mit Excel darzustellen?

Naja... wie soll denn hier jemand dir die ultimative Formel dafür geben, wenn du nicht in der Lage bist, das Problem zu beschreiben?

[Konstantin1812]

Also kurz: ihr habt stündliche Messewerte und wollt aus dem Kurvenverlauf der Messwerte das so glätten, als ob ihr viertelstündliche Messwerte habt?

Genau!

Ich habe das schon mittels Wenn-Funktion versucht aber so richtig hat das nicht funktioniert.

Bitte sag mir, ob mein Vorhaben in Excel umsetzbar ist

[flashpixx]

Mein Vorschlag wäre anhand der Messwerte und der zugrunde liegenden Funktion über einen Gradientenabstieg die Koeffizienten zu fitten und dann die Kurve zu berechnen. Dürfte wohl das genauste Verfahren sein.

Alles Andere erscheint mir als schlecht, da es die Originalwerte verfälscht

Phil

Bearbeitet 26. März 2009 von flashpixx

[Konstantin1812]

Ich glaube der Fachbegriff ist interpolieren!

Die Werte sollen linear an- bzw. absteigen. Es müsste halt ein Faktor gewählt werden, welcher die tatsächlich erzeugte Energie nicht verfälscht!

Können Excel-Spezialisten, welche es auf dieser Plattform sicher eine Menge gibt, am Beispiel meiner Tabelle aus meinem ersten Beitrag, dieses Problem lösen?

Ergänzender Hintergrund:

Da die Prognosewerte an der EEX verkauft werden und diese im Normalfall nie die Ist-Messung treffen, muss Energie zu oder verkauft werden. DIes nennt man Ausgleichsenergie. Diese wird in 1/4 Stunden gehandelt. Daher dieser ganze Aufwand!

[flashpixx]

Es ist die Frage wie Du interpolierst: linear, kubisch, durch Polynome usw.

Die Funktion, die zwischen zwei Werten existiert, ist hier entscheidend. Sprich der Verlauf zwischen zwei Werten muss Dir bekannt sein. Ist das nicht der Fall, wird Deine Interpolation falsch.

Phil

[Konstantin1812]

Mein Vorschlag wäre anhand der Messwerte und der zugrunde liegenden Funktion über einen Gradientenabstieg die Koeffizienten zu fitten und dann die Kurve zu berechnen. Dürfte wohl das genauste Verfahren sein.

Alles Andere erscheint mir als schlecht, da es die Originalwerte verfälscht

Phil

Vielen Dank für deine Antwort.

Ich habe aber keine Ahnung wie man das macht, was du beschrieben hast!

[flashpixx]

Ich habe aber keine Ahnung wie man das macht, was du beschrieben hast!

Als Grundlagen wären diese beiden Artikel:

http://de.wikipedia.org/wiki/Gradient_(Mathematik)

Gradientenverfahren ? Wikipedia

Du musst zunächst Deine Originaldaten in einer mathematischen Funktion beschreiben mit entsprechenden Parametern. Wenn Du diese Funktion hast, wird über den Gradientenabstieg der Fehlerfunktion diese Parameter optimiert, so dass Deine Funktion annähernd gut sich an die gemessenen Werte anpasst. Damit kannst Du dann jeden Wert (auch zwischen 2 gemessenen Werten) exakt bestimmen.

Phil

[Konstantin1812]

Als Grundlagen wären diese beiden Artikel:

http://de.wikipedia.org/wiki/Gradient_(Mathematik)

Gradientenverfahren ? Wikipedia

Du musst zunächst Deine Originaldaten in einer mathematischen Funktion beschreiben mit entsprechenden Parametern. Wenn Du diese Funktion hast, wird über den Gradientenabstieg der Fehlerfunktion diese Parameter optimiert, so dass Deine Funktion annähernd gut sich an die gemessenen Werte anpasst. Damit kannst Du dann jeden Wert (auch zwischen 2 gemessenen Werten) exakt bestimmen.

Phil

Also, ich werde mich da mal reindenken. Diese Formeln sehen schon sehr beängstigend und kompliziert aus. Wenn du mir zu meinem Beispiel eine praktische Hilfestellung leisten könntest, wäre ich dir sehr dankbar.

[Thanks-and-Goodbye]

Bitte keine unnötigen Vollzitate!

[Konstantin1812]

Der Wert zwischen zwei Werten soll 0,25 betragen

[hausenm]

Hallo zusammen,

hatte ein ähnliches Problem 2003. Sollte eine Steuerung schreiben für Foliendächer und über Zeit- Temperatur und Druck regeln- steuern.

Wir (1 Maschbauer und ich) haben die Werte in kleine Segmente geteilt und dann über einen Polygonzug angenähert. (Am Ende war die Abweichung kleiner 2%). Die "Formel" hatte die Struktur : Wert< Meßwert<max dann eine Geradengleichung usw. bis alle Punkte abgearbeitet wurden.

Hat den Kunden überzeuigt- und wurde vonTUM geprüft und für gut befunden.

Anstatt den Temeraturen kann man in deinem Fall die elektrische Abgabeleistung nehmen.

Viel Spaß beim Grübeln

[Konstantin1812]

Ich kappiere das nicht. Gibt es in excel keine funktion, in der man die parameter einfach eingibt??

[Konstantin1812]

So mit dem Anstieg klappt es jetzt. Nur habe ich wie ihr der graphik entnehmen könnt, klappt es mit dem Abstieg nicht. Hat jemand ne idee? Meine Formel lautet:

=NORMINV(0,08;B70:B73;0,2)

[Bubble]

Die Funktion, die zwischen zwei Werten existiert, ist hier entscheidend. Sprich der Verlauf zwischen zwei Werten muss Dir bekannt sein. Ist das nicht der Fall, wird Deine Interpolation falsch.

Für eine ganz normale Interpoltion muss der tatsächliche Verlauf zwischen zwei Stützstellen nicht bekannt sein - wäre er es, bräuchte man nicht zu interpolieren.

[Bubble]

So mit dem Anstieg klappt es jetzt. Nur habe ich wie ihr der graphik entnehmen könnt, klappt es mit dem Abstieg nicht.

Bisher hast Du scheinbar konstant interpoliert, sprich einem Intervall einen Messwert zugeordnet.

Die nächstbeste Variante wäre eine lineare Interpolation zwischen zwei Messwerten, die auch sehr einfach umzusetzen ist.

[flashpixx]

Für eine ganz normale Interpoltion muss der tatsächliche Verlauf zwischen zwei Stützstellen nicht bekannt sein - wäre er es, bräuchte man nicht zu interpolieren.

Die Art der Funktion zwischen zwei Stützstellen muss bekannt sein. Ich kann z.B. zwischen den Punkten [1,1] und [2,4] linear interpolieren, aber wenn es eine quadratische Funktion ist, ist der Fehler recht groß.

Die Steigung bei einer lineae Interpolation beträgt m = (4-1) / (2-1) = 3/1 = 3, die Steigung der quadratischen Funktion f(x)=x^2 somit f'(x) = 2x.

Natürlich kann aufgrund der Stetigkeit bzw der Definition des Differential zwischen zwei Punkten linear interpolieren, wenn sie sehr dicht beieinander liegen, womit wir letztendlich zur Ableitung der Funktion gelangen.

Ich würde mich zunächst einmal an die Originaldaten halten und schauen, ob die Funktion bekannt ist.

Phil

[Bubble]

Ich glaube, Du bringt ein paar Dinge durcheinander.

Die Art der Funktion zwischen zwei Stützstellen muss bekannt sein.

Das stimmt nicht, für eine Interpolation muss kein weiteres Wissen über die Funktion vorhanden sein, ausser den Funktionswerten an den Stützstellen. Meistens weiss man nicht mehr als das, was gemessen wurde. Welche Ordnung die verwendete Interpolierende haben soll, hängt nur von den Anforderungen an die rekonstruierte Funktion ab.

Ich kann z.B. zwischen den Punkten [1,1] und [2,4] linear interpolieren, aber wenn es eine quadratische Funktion ist, ist der Fehler recht groß.

Das kann durchaus sein, aber woher willst Du wissen, dass eine quadratische Funktion zugrunde liegt? Du kannst das gar nicht wissen. Es könnte sich genauso gut um eine stückweise konstante Funktion mit einem Sprung, der irgendwo zwischen den zwei gemessenen Werten liegt, handeln.

Wenn Du die Art der den Messwerten zugrundeliegenden Funktion kennen würdest (z.B. dass es sich um eine quadratische Funktion handelt), dann bräuchtest Du nicht zu interpolieren, sondern könntest die Konstanten bestimmen und beliebige weitere Funktionswerte berechnen.

[flashpixx]

Wenn Du die Art der den Messwerten zugrundeliegenden Funktion kennen würdest (z.B. dass es sich um eine quadratische Funktion handelt), dann bräuchtest Du nicht zu interpolieren, sondern könntest die Konstanten bestimmen und beliebige weitere Funktionswerte berechnen.

Eben, entweder ich kenne die zugrunde liegende Funktion und kann die Parameter mittels Gradientenabstieg fitten, oder ich muss über ein Netz (z.B. FFN oder RBF) versuchen anhand der Daten das Modell zu lernen.

Wenn ich einfach irgendwie auf meinen Daten interpoliere erhalte ich Werte die einen hohen Fehler zu der realen (durchaus nicht bekannten) Funktion aufweisen.

Die von Dir genannte stückweise Funktion fällt aus dem Rahmen, da sie unstetig ist, man müsste hier dann durch eine Summe von Sigmoiden o.ä. approximieren.

Phil

Bearbeitet 27. März 2009 von flashpixx