✔ EXCEL - Diagramm mit Trendlinie und die vermeiledeite Mathematik

[Zvoni]

Mahlzeit,

ich steh gerade voll auf dem Schlauch.
Ich habe ein Diagramm mit zusätzlicher Trendlinie
(Zur Verdeutlichung habe ich mal die Trendlinien-Formel eingeblendet)


X-Achse sind Monate, Y-Achse sind "Stück"
Wie bekomme ich den Trend jetzt in "Prozent" hin?
Für Prozent brauche ich ja zweimal die gleiche "Einheit"
(Autobahn "7% Steigung" = Auf 100 Meter horizontal fahre ich 7 Meter nach oben")

Oder geht das gar nicht?

 

[Technipion]

Die Einheit deiner Steigung leitet sich aus [dy] / [dx] ab, also Stück pro Monat, wobei Stück dimensionslos ist, und Monat die Einheit Zeit hat. Die Steigung ist also "pro Zeit", bzw. gekürzt 1/s oder 1 Hz. Macht ja auch Sinn, wenn du Stücke abzählst tust du das mit einer gewissen "Frequenz".

Wie bekomme ich den Trend jetzt in "Prozent" hin?

Was genau möchtest du denn mit dem Trend machen? Im Moment sagt Excel dir ja, dass du etwa 581 "Stück pro Monat" als Steigung hast. (Der Ordinatenabschnitt bedeutet hierbei, dass die Linie zum Zeitpunkt 0 auf -340 Stück fallen würde; du hast also dem Trend nach mit -340 Stück gestartet)

Ich glaube, du hast dich einfach nur total selbst verwirrt

Gruß Technipion

 

[Zvoni]

Ich hole mir mit der "LinEST"-Funktion (bzw. RGP auf Deutsch) die zwei Parameter im VBA-Code ab
1. Rückgabewert = Steigung
2. Rückgabewert = Ordinaten-Abschnitt

Das Problem: Die Excel-Tabelle hat bisweilen mehrere 100 (oder sogar tausende) Zeilen, welche für eine Bedarfs-Analyse benutzt werden soll, und bei mehreren 100/1000 Zeilen können die Mitarbeiter nicht dauernd ins Diagramm schauen, um die Ausreisser zu finden (Bedarf geht durch die Decke bzw. geht in Keller).
Deshalb habe ich die Anforderung in einer zusätzlichen Spalte den Trend eben in "Prozent" auszudrücken, wodurch die Mitarbeiter dann bequem hin- und her-sortieren, -filtern usw. können.

Und für die Prozent-Berechnung habe ich eben dauernd das "Autobahn"-Bild vor Augen (Einfache Dreiecks-Trigonometrie=Gegenkathete geteilt durch Ankathete).
Allein vom gezeigten Beispiel erwarte ich irgendwie ein Ergebnis von ca. 500%, wobei sich das auch irgendwie falsch anfühlt, weil ich ja keine 500% Steigerung von M01 zu M02 habe usw.

Auf der anderen Seite habe ich im Beispiel einen ca. Winkel von 45 Grad (welches im Autobahn-Beispiel 100% wären), wobei mir klar ist, dass dies der Darstellung / Massstab geschuldet ist

 

[Technipion]

Ja ich glaube du hast dich definitiv in die totale Verwirrung gesteigert

Denk dran: % ist lediglich ein Multiplikator. Anstatt die Steigung "in Prozent" auszudrücken, könntest du sie genau so gut in "100 mal so viel" ausdrücken. Wenn du auf der Zeitachse nicht Monate sondern Tage angibst stimmen die Zahlwerte dann hinten und vorne nicht mehr, weil alles um einen Faktor von 30 skaliert wird.
Wichtig ist hierbei nur die Dimension beizubehalten. Die Steigung deiner Trendlinie hat die Dimension "pro Zeit". Das ist halt so. Sonst würde die Gleichung y = m*x + b hier ja gar keinen Sinn machen. y darf keine Dimension haben; b ist dimensionslos und x hat die Dimension Zeit (weil x in Monaten eingesetzt wird). Also muss m die Dimension pro Zeit haben, damit sich die Zeit herauskürzt.

Die Excel-Tabelle hat bisweilen mehrere 100 (oder sogar tausende) Zeilen, welche für eine Bedarfs-Analyse benutzt werden soll

Au weiha. Also mit dem Thema Trendanalyse/-prognose hast du dich aber Tief in die Fänge der Stochastik verstrickt. Ich habe leider gerade keine Zeit, aber falls es bei dir nicht eilt, kann ich gerne gegen Ende der Woche hier nochmal etwas genauer auf das Thema eingehen.

Gruß Technipion

 

[Zvoni]

Yo, kann warten bis Ende der Woche
Hab gerade genug andere Sachen auf der Pfanne.
Thx

 

[Zvoni]

OK, bin vorwärts gekommen.
Was ich eigentlich brauche ist eine "umgekehrte" Zinseszins-Rechnung.
Also im Prinzip EndWert/Startwert ^ ???
Irgendwo habe ich gesehen "EndWert / StartWert ^ (1/12)" --> 12. Wurzel?

Jetzt muss ich nur noch rausfinden, wie ich negative Werte "normalisieren" kann, da "gerade" Wurzel auf Negativ nicht funktioniert.

EDIT: Hmmm... Falls Startwert negativ ist, rutsche solange einen Monat nach vorne bis Wert positiv ist, und nehme dann diesen "Monatswert" als Startwert, wobei dann eben nicht 12. Wurzel, sonder 10. Wurzel, 6. Wurzel oder was auch immer)

EDIT2: Dieselbe Überlegung gilt auch für den Endwert (wenn der Trend nämlich nach unten zeigt --> negative Steigung). In dem Fall (negativen) EndWert nehmen, und jeweils einen Monat zurück rutschen bis es positiv wird, und dann eben die Xte Wurzel was übrig bleibt

 

[Zvoni]

Ich glaub ich habs.
rngTargetSection sind die 12 Zellen, welche meine Monatsdaten pro Zeile haben (Bsp. K6:V6)

Public Sub SetTrendValue(ByRef Tabelle As Worksheet, ByRef rngTargetSection As Range, ByVal Zeile As Long)
Dim s As Double
Dim a As Double
Dim i As Long
    s = WorksheetFunction.LinEst(rngTargetSection)(1)
    a = WorksheetFunction.LinEst(rngTargetSection)(2)
    If s + a = 0 Then  'Um Division durch 0 zu vermeiden
        Tabelle.Cells(Zeile, 24) = 0
        Exit Sub
    End If
    If s >= 0 Then 'Positive Steigung --> Extrapolation kann links im negativen beginnen
        i = 1
        Do
            If i * s + a > 0 Then
                Tabelle.Cells(Zeile, 24) = ((12 * s + a) / (i * s + a)) ^ (1 / (13 - i)) - 1
                Exit Do
            Else
                i = i + 1  'Einen Monat nach rechts (plus 1 Monat)
            End If
        Loop Until i > 12
    Else   'Negative Steigung --> Extrapolation kann rechts im negativen enden
        i = 12
        Do
            If i * s + a > 0 Then
                Tabelle.Cells(Zeile, 24) = ((i * s + a) / (s + a)) ^ (1 / i) - 1
                Exit Do
            Else
                i = i - 1  'Einen Monat nach links (minus 1 Monat)
            End If
        Loop Until i < 1
    End If
End Sub

 

[Zvoni]

Ich bin ein Trottel.
O.g. Code ermittelt mir die Darstellung in Prozent zwischen Start und Endwert.
Ein Trend soll ja in die Zukunft extrapolieren (wenn dies der Wert in Monat 11 und dies der Wert in Monat 12 war, was kann ich in Monat 13 erwarten?)
Noch einfacher:

If s >= 0 Then
    Tabelle.Cells(Zeile, 24) = (12 * s + a) / (11 * s + a) - 1
Else
    Tabelle.Cells(Zeile, 24) = (2 * s + a) / (s + a) - 1
End If

EDIT: Wobei mir klar ist, dass hier auch eine "Unschärfe" drin ist