Kollision zwischen Datenreihe und Trendlinie

Forumthread: Kollision zwischen Datenreihe und Trendlinie

06.07.2024 19:14:04

BastiUndExcel

Ich versuche so genau wie möglich meine Problemstellung zu formulieren. Meine Kenntnisse in VBA reichen leider nicht aus, um ein derartiges Makro zu erstellen. Ich lege meinen jetzigen Stand in Form einer xlsx-Datei an, um das Problem besser darzustellen.

- Ich habe eine Tabelle
- x=Tage des Jahres (ab Spalte A2)
- y=täglich gemessene Daten (ab Spalte B2)
- Aus den täglich gemessenen Daten ergeben sich im Laufe der Zeit Hochpunkte (y-Achse).
- Für die Trendlinie benötige ich nur die 2 höchsten Hochpunkte (jüngerer Hochpunkt ist niedriger als älterer Hochpunkt).
- In einer zusätzlichen Hilfsspalte (Spalte C) übertrage ich die 2 letzten höchsten Punkte, manuell, leider, da ich auch hier nicht weiß, wie es mittels Formel geht.
- In einem Diagramm lasse ich aus den x- und y-Werten ein Linie (=Liniendiagramm) zeichnen.
- In das Liniendiagramm lasse ich zusätzlich aus den 2 Hochpunkten (Hilfsspalte) eine lineare Trendlinie darstellen.
- Das eben genannte passiert in mehreren Tabellenblättern (bspw. 30 Stück)
- Die anliegende xlsx-Datei zeigt den Stand bis hierher (https://www.herber.de/bbs/user/170743.xlsx)

Meine Frage:
- Jeden Tag werden neue Daten (y-Achse) gemessen und in die Diagramme und Arbeitsblätter eingetragen, automatisch.
- An einem Tag kommt es dazu, dass die Trendlinie mit der Linie (aus Messwerten) kollidiert, insbesondere, bspw. wenn ein neuer Messwert einen höhere y-Werte im Diagramm produziert, die Linie steigt und in die Trendlinie gerät.
- Meine anliegende Datei zeigt beispielhaft eine Kollision am 18. Januar (siehe Tabelle1).
- Mir fehlt ein Makro, welches signalisiert, dass es zu einer Kollision zwischen der Trendlinie und der Punkt-Linie kommt.
- Die Kollision sollte dadurch kenntlich gemacht werden, dass sich die Hintergrundfarbe des Diagramms "rot" färbt und in einer Zelle, bspw. "F1" die Zahl 1 eingetragen wird (für späteres filtern).
- Diese Abfolge sollte auch in allen weiteren Arbeitsblättern durchgeführt werden (Schleife bis zum letzten Arbeitsblatt).
- Das Makro würde ich immer dann ausführen, sobald alle Messdaten automatisch eingetragen wurden.

Ich Danke bereits jetzt für etwaige Hilfe.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Kollision zwischen Datenreihe und Trendlinie

06.07.2024 23:02:50

Yal

Hallo Basti,

das Problem lässt sich eher mathematisch als per Makro lösen:
deine zwei höchste Punkte ermittelst Du mit KGrösste(),
daraus ergibt sich eine lineare Funktion (die Grundlage dazu, falls nicht vorhanden, kann man schnell zusammengoogeln).
Aus diese lineare Funktion gibt es für jeden Tag einen Wert.
Wenn der Tagesmesswert grösser ist als diese lineare Wert, dann hast Du eine "Kollision".

Das alles ist mit Excel Formeln erreichbar. Einzig die Färbung der Diagrammhintergrund müsste als Event programmiert werden.

Eine Programmierung wurde nichts anderes als diese mathematische Weg nachbilden. Ein "Lesen" der Punkte auf dem Diagram ist nicht notwendig (und ausserdem extrem aufwändig), weil die Eingangswert bekannt sind.

VG
Yal

AW: Kollision zwischen Datenreihe und Trendlinie

06.07.2024 23:38:57

BastiUndExcel

Hallo Yal,

danke für Antwort!

Mit KGrösste() habe ich zuvor experimentiert. Ich habe mir 2 Hilfsspalten erstellt. Eine für den höchsten Punkt und eine für den zweithöchsten Punkt. In die Zellen habe ich beispielhaft KGRÖSSTE(C2:C50;1) oder KGRÖSSTE(D2:D50;2) eingetragen. Die beiden höchsten Punkte konnte ich ausgeben lassen, jedoch habe ich den festen Bezug zur x-Achse verloren. Aus dem Grund habe ich gewechselt zur manuellen Eingabe der Hochpunkte. Aber wenn es noch eine andere Lösung gibt, die ich nicht sehe, dann würde mir das helfen.

Ich gehe auch davon aus, dass es mehr mathematisch ist.

Mit einer Funktion, die aus den 2 Punkten gebildet werden, damit hatte ich es auch mal probiert. Ich hatte es leider nur soweit, dass ich die Nullstelle auf der x-Achse ermittelt hatte. Mein Problem besteht darin, dass durch die täglichen Messwerte eine veränderliche Linie entsteht, gegenüber der getestet werden muss, ob eine Kollision mit der Trendlinie passiert. Auch hier bin ich an der Stelle nicht weiter gekommen. Vielleicht fehlt mir mehr Verständnis.

Die Idee, die Lösung mathematisch klären, anstatt optisch ist für mich eine neue Herangehensweise. Kann es mir die Formeln aber noch nicht vorstellen.
Wie kann soetwas aussehen?

AW: Kollision zwischen Datenreihe und Trendlinie

07.07.2024 15:19:16

BastiUndExcel

Hallo Yal,

danke für deinen Ansatz, eher mathematisch anstatt mittels Makro vorzugehen!
Das Tüfteln etwas gedauert, aber funktioniert. Mir wird jetzt in der Zeile gezeigt, ob eine Kollision entstanden ist.

AW: Kollision zwischen Datenreihe und Trendlinie

06.07.2024 23:06:13

Onur

Was hätst du von einer automatischen Trendlinie?

AW: Kollision zwischen Datenreihe und Trendlinie

07.07.2024 17:04:20

Oppawinni

Also ich kann mir die Sachen von Onur nicht wirklich anschauen, hab nur XL2016,
aber mal zur Fixierung deiner Trendlinie ein paar Gedanken.
Der Maximalwert ist sicherlich einer der Punkte für deine Trendline.
Der Zweite Punkt ist aber nicht unbedingt der mit dem zweitgrößten Wert.
Ich denke, dass man da Steigungen rechnen müsste.
Und zwar (Delta zum Maximalwert)/ Betrag(Abstand in Tagen zum Tag mit dem Maximalwert)
Wenn also am Tag 3 der Maximalwert 9 war und ich will die Steigung für den Tag 7 mit dem Wert von 7 berechnen,
dann ist die Steigung (9-7) / abs( 7 - 3 ) = -1/2
für den 3ten Tag, der ja den Maximalwert hat und zu sich selbst natürlich 0 Tage Abstand hat, ist die Steigung nicht definiert.
Der Tag mit der größten (negativen, bzw. max. 0) Steigung liefert den 2 Stützpunkt deiner Trendlinie....
Bissl Flickwerk:
https://www.herber.de/bbs/user/170751.xlsx

AW: Kollision zwischen Datenreihe und Trendlinie

06.07.2024 23:49:58

BastiUndExcel

Hallo Onur,

als ich die Trendlinie erstellt habe, habe ich automatisch gewählt innerhalb der Trendlinienoptionen.
Bei der Erstellung der Trendlinie habe ich bei der x-Achse die Tage ausgewählt und bei der y-Achse den Bereich mit den Hochpunkten.

Gibt es weitere Funktionen, welche eine automatische Trendline noch kann?

AW: Kollision zwischen Datenreihe und Trendlinie

07.07.2024 00:07:53

Onur

Was hältst du davon (Tabelle1) ?
https://www.herber.de/bbs/user/170747.xlsm

AW: Kollision zwischen Datenreihe und Trendlinie

07.07.2024 00:13:17

BastiUndExcel

Hallo Onur,

kannst du mir bitte den Makrotext hier in das Forum kopieren?

AW: Kollision zwischen Datenreihe und Trendlinie

07.07.2024 00:27:31

Onur

Hier ist eine zweite (viel bessere) Variante:
https://www.herber.de/bbs/user/170748.xlsm

Das ins Modul des Blattes:

Private Sub TextBox1_Change()

    [D5] = Alt

    Alt = [D2]

End Sub



Private Sub Worksheet_Change(ByVal Target As Range)

    Calculate

End Sub

Das in ein STANDARDMODUL (ggf neu erstellen):

Public Alt

Das ins Modul des Workbooks:

Private Sub Workbook_Open()

    Alt = [D2]

    [D5] = Alt

End Sub

AW: Kollision zwischen Datenreihe und Trendlinie

07.07.2024 00:42:36

Onur

Offenbar magst du keine Dateien mit Makros:
Hier ist meine Datei als xlsx:

https://www.herber.de/bbs/user/170749.xlsx

Beliebteste Forumthreads (12 Monate)

Kollision zwischen Datenreihe und Trendlinie

Forumthread: Kollision zwischen Datenreihe und Trendlinie

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Kollision zwischen Datenreihe und Trendlinie

Forumthread: Kollision zwischen Datenreihe und Trendlinie

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread