[Excel] Mittelwerte unregelmäßiger Messungen

[Medium]

Nabönd DP!

Auch wenn es fachfremd ist, will ich es hier versuchen. Ich habe eine Excel-Tabelle, in der Messungen abgetragen sind. Es gibt einfach nur 2 Spalten: Datum+Uhrzeit, Wert. Man möchte nun von mir, dass in einem daraus erzeugten Diagramm eine Mittelwert-Kurve hinzu kommt.

Das Problem: Die Messungen sind extrem unregelmäßig. Es sind so ~3-5 pro Tag, immer zu unterschiedlichen Uhrzeiten - also faktisch völlig beliebig. Man möchte nun aber ablesen können, dass z.B. am 1. Juni 2010 um 12:00 Uhr über 24h gemittelt Wert X angesagt war, ohne dass zu diesem Zeitpunkt tatsächlich eine Messung vorliegt.

Klar ist bereits, dass eine Interpolation her muss. Linear ist denke ich ausreichend. Mein Problem ist jetzt, dass ich mit VB Makros keinerlei Erfahrung habe, und ich bezweifel, dass sich das halbwegs elegant händisch innerhalb der Tabelle lösen lassen wird. Vor allem ist für mich noch ein Rätsel, wie man es behandeln soll, dass von einem Zeitpunkt aus X Stunden in die Zukunft bzw. Vergangenheit verschieden viele Messpunkte betrachtet werden müssen.


Vom groben Ablauf her habe ich bereits folgende Idee:
Mehr analytisch:
1) Ermittle alle Messpunkte, die in das Intervall fallen
2) Bilde das Integral unter je zwei benachbarten Messpunkten (=> implizite lineare Interpolation), und dividiere durch die Zeitspanne zwischen den Punkten (=> Mittelwert in dem Unterintervall) - Nebenfrage: Was für eine Zeiteinheit zu Grunde legen?
3) Bei Unterintervallen die nicht ganz im gefragten Intervall liegen, den "fehlenden" Messpunkt linear interpolieren, und wie unter 2) verfahren
4) Den Krams aufaddieren, und irgendwo hin speichern, und zwar so, dass dem Ganzen auch noch ein Zeitpunkt zugeordnet werden kann, so dass es im Diagramm an der richtigen Stelle auftaucht

Diskret:
1) Wähle ein ausreichendes Raster zur Interpolation
2) Ermittle alle Messpunkte, die in das Intervall fallen
3) Berechne lineare Interpolation aller Wertepaare zum gewählten Raster
4) Addiere alle interpolierten Werte im gefragten Intervall, und dividiere durch ihre Anzahl
Auch hier die Problematik der Datumszuordnung und ggf. Speicherung, bzw. "Infusion" in das Diagramm (keine Ahnung ob Makros in ein Diagramm fummeln können, ohne eine Basis in einer Tabelle dafür zu bilden)

Prinzipiell sollte beides klappen, wobei die diskrete Variante Fehler einschleust sobald mit dem gewählten Raster nicht mehr alle tatsächlichen Messpunkte genau getroffen werden. (Da aber scheinbar nur Werte im Viertelstundenraster eingetragen sind, ist das ggf. handlebar.)

Wie aber bringe ich Excel bei eines der beiden Dinge zu tun, und zwar so, dass es nachher im Diagramm als Datenreihe auftaucht?

Besten Dank im Voraus!

[blackfin]

Es ist schon spät....

aber ohne jetzt direkt auf dein Problem einzugehen, ist mir ein Begriff dabei in den Kopf gekommen, nämlich die
Monte Carlo Simulation

Diese Simulation kann anhand einer Zufallsmenge (die in deinem Fall deine zufälligen Messergebnisse sind) durch ein Näherungsverfahren einen Mittelwert bilden oder eben fehlende Punkte interpolieren. Das Problem dabei dürfte aber vielleicht sein, dass du am Anfang nicht sonderlich viele Messpunkte hast?

Dazu habe ich sogar schon einmal eine Exceltabelle in der Hand gehabt, die sowas für eine Chance/Risiko-Verteilung anhand von einigen Eckdaten berechnet...uhm...mal sehen, ob ich die noch finde.
Ich glaube aber leider nicht


Edit:

Zitat:

Wie aber bringe ich Excel bei eines der beiden Dinge zu tun, und zwar so, dass es nachher im Diagramm als Datenreihe auftaucht?

Musst du das in Excel direkt machen, diese Sheet-Manipulation? Oder kann das auch durch ein externes Programm erfolgen?
VB Makros sind jetzt auch leider nicht so mein Ding..aber ich habe es mit einer kleinen .pas (XLSFile.pas ?!?) schonmal geschafft,
Excel Dateien in einer Delphi-App zu öffnen, zu bearbeiten und dann wieder rauszuspeichern.
Geht halt auch viel flotter als mit VB Makros. Vielleicht kommt die Möglichkeit auch in Betracht für dich?

[Medium]

Jetzt ist's noch viel später

Mit deiner Befürchtung hast du ziemlich Recht. Das Intervall soll wählbar sein, und durchaus auch mal 1h oder so betragen, so dass potentiell nicht ein einziger realer Messwert darin liegt. Auch sind teilweise Lücken von ~1 Tag in den Werten. (Die Sinnhaftigkeit bitte nicht in Frage stellen, daran bin ich auch schon gescheitert.)

Leider kann ich kein externes Programm bemühen, da die Tabelle nachher wartungsfrei von Leuten weiter geführt werden soll, die ausser diesem sonst mit PCs nichts zu schaffen haben. Das kann ich leider nicht an den Mann bringen . Wenn es ginge, hätte ich ja locker flott dank OLE-Automation eine der o.g. Varianten eben hinwursten können.

Was mir für die Umsetzung in Script hauptsächlich fehlt um einen Einstieg zu bekommen ist:
- Ansprechen von Spalten in Array-ähnlicher Weise, und fest stellen ob ein Wert darin steht (um das variable Ende der Spalten zu bestimmen)
- (bevorzugt) Ausgabe direkt in ein Diagramm oder
- Ausgabe in eine Tabelle, und Einbindung dieser Einträge als Datenreihe im Diagramm

Bei dem eigentlichen Verfahren bin ich auch noch sehr offen für Ideen, jedoch denke ich, dass stochastische Ansätze insbesondere wegen der geringen Anzahl an Daten eher problematisch sind. Und eben dass die Einträge so extrem unregelmäßig sind...

[alzaimar]

Ausgleichssplines über (Zeitpunkt, Wert) kommen nicht in Frage? Die Berechnung der Koeffizienten ist relativ harmlos und eine daraus resultierende Interpolation noch banaler.

[roadrunner-S51]

Das ist zwar vielleicht eine etwas primitive Idee, aber hast du schonmal an "Trendlinien" gedacht. Damit habe ich mal bei einfacheren Auswertungen gearbeitet. Aber es gibt da auch eine Reihe von Einstellmöglichkeiten.

[blackfin]

Uhm....

hilfe das vielleicht dabei weiter?: korrigierte Stichprobenvarianz

Um was für Werte handelt es sich dabei eigentlich? (Typ) Vielleicht lässt sich da ja etwas "schummeln", wenn man den Typ der Werte kennt


Wegen den VB Makros:

Also..wie gesagt, ist auch nicht mein Gebiet, aber auf die einzelnen Zellen kannst du mit

Code:

val = Cells(Row,Column).Value

zugreifen, schreibend und lesend.
Dann gibt es dabei noch ActiveCell und Selection, wenn du etwas ausgewählt hast.
Die Anzahl der Spalten und Reihen bekommst du mit

Code:

ActiveSheet.Rows.Count

bzw.

Code:

ActiveSheet.Columns.Count

Bei Diagrammen muss ich allerdings passen

[Medium]

Zitat von wikipedia zur korrigierten Stichprobenvarianz:

Sind die xi die Ausprägungen unabhängig identisch verteilte Zufallsvariablen

Das ist leider der Kasus Knacktus.
Die Werte sind Gewichtswerte aus denen ein Füllstand abgeleitet wird.
Danke dir für den VB-Einstieg! Evtl. kann ich damit schon mal ein wenig was ausprobieren.

Die Trendlinie ist mir neu! Die schaut schon garnicht mal so schlecht aus, mit etwas Glück reicht das ja sogar schon, danke dir! Schade ist, dass bei "Gleitender Durchschnitt" nur an tatsächlichen Datenpunkten welche gesetzt werden. Sonst dürfte das ca. genau das sein, was ich vor hatte.

Bei den Splines hätte ich leider noch das gleiche Problem wie vorher. Als Mittelwert-annähernde Interpolation allerdings eine schöne Idee!

[blackfin]

Hmm....es geht also darum, herauszufinden, wie sich ein Füllstand in einem gegebenen Intervall wahrscheinlich ändert, oder?
Da die Werte völlig unterschiedlich sind, ist wohl ein normales arithmetisches Mittel nicht ganz der Weg, zu gehen.

Vielleicht ist es ja ein Lösungsansatz, dass du als Basis für die Mittelwert-Bildung auch das gewählte Intervall der Anzeige nimmst und ggf auch noch die Uhrzeiten mit einrechnest. (Gibt der Kunde nur an "Intervall 1 Stunde" oder eine "echte" von-bis Zeit?)

Ich nehme jetzt mal an, du hast schon ein paar Messpunkte und der Kunde will wissen, wie sich der Füllstand am Montag zwischen 08:00 und 09:00 Uhr ändert (Zukunft).
Dabei könntest du nachsehen, ob du für andere, bereits erfasste Tage schon Messpunkte in diesem Intervall (8-9 Uhr) hast und ermittelst dann das arithmetische Mittel aus diesen Werten und zeigst diese Kurve an.
Findest du auch keine Messpunkte im gewünschten Intervall an anderen Tagen, erweiterst du das zu betrachtende Intervall gleichmäßig, bis du mindestens zwei Messpunkte hast, durch die du dann rechnerisch eine Linie ziehen kannst. Diese Linie interpolierst du dann auf den Zeitraum des Anzeige-Intervalls.

Liegt der gewünschte Messpunkt in der Vergangenheit, schaust du nach, ob in diesem Intervall Messpunkte verfügbar sind und gewichtest diese höher, ansonsten verfährst du genauso wie oben. Du kannst ja auch zwei Kurven anzeigen, einmal die "reale Änderung", falls es in der Vergangenheit liegt und einmal die gemittelte bzw. wahrscheinliche.

So würde ich denke ich unterscheiden, ob es eben eine Vergangenheits-Anzeige ist (Messpunkte im exakten Intervall einbezogen) oder ob es in der Zukunft liegt (Mittelwert aus den Intervallen, die dem Zeitraum der Uhrzeiten entsprechen und bereits geloggt wurden).
Inwieweit man dies noch verfeinert, ist wohl grenzenlos. Dazu müsste man wahrscheinlich wissen, wie der Produktionsablauf ist und welche Zeitbasis auch relevant ist. Es kann ja z.B. auch gut sein, dass immer Montags eine Grosse Produktion gefahren wird, aber am Dienstag das ganze so gut wie stillsteht. Ebenso, wann aufgefüllt wird usw. Solche Parameter sind dann natürlich noch zu berücksichtigen, um die Wahrscheinlichkeit auch annäherend zu berechnen.


....ganz den Sinn dieser Aktion verstehe ich allerdings nicht, wenn die Messpunkte völlig ohne Relevanz zueinander sind und keine Gleichmäßigkeit aufweisen, ist das ganze ja eher für den popo. Aber, das darf ich ja hier nicht hinterfragen, hast du ja gesagt

[user0815]

Hmmm, das ganze hört sich für mich nach Statisktik hier Poisson-Verteilung an.

Zitat:

Die Poisson-Verteilung liefert also Voraussagen über die Anzahl (k) des Eintretens seltener, zufälliger und voneinander unabhängiger Ereignisse innerhalb eines bestimmten Intervalls, wenn aus vorangehender Beobachtung bereits bekannt ist, wie viele Ereignisse man im Mittel innerhalb dieses Intervalls erwartet (λ).

google books

Zitat:

Typische Beispiele für Zufallsvariable, die sehr häufig poissonverteilt sind:
- Anzahl der Telefonanrufe pro Stunde,
- Anzahl der Druckfehler pro Seite,
- Anzahl der eintreffenden Kunden pro Stunde,
- Anzahl der Arbeitsunfälle pro Tag.

[blackfin]

Zitat:

....wenn aus vorangehender Beobachtung bereits bekannt ist, wie viele Ereignisse man im Mittel innerhalb dieses Intervalls erwartet

Eben das trifft ja für den Fall nicht zu, da keinerlei Festlegungen bestehen, wann etwas gemessen wird und dies kann laut Medium ja auch völlig unregelmäßig geschehen.