Beste antwoord
De Excel-trendlijnen zijn de kleinste kwadraten die bij uw gegevens passen. U kunt (en zou moeten) Excel vragen om zowel de vergelijking voor de trendlijn als de R-kwadraatstatistiek weer te geven (dichter bij 1, hoe beter).
De Power-trendlijn gebruikt een vergelijking in de vorm y = a * x ^ b, waarbij a en b constanten zijn die zijn gevonden door de regressieanalyse uitgevoerd door de trendlijnwizard.
Merk op dat Excel niet genoeg cijfers weergeeft in de trendlijnvergelijking. Als u punten probeert te berekenen met behulp van de vergelijking zoals die aanvankelijk werd weergegeven, zou u ten onrechte kunnen concluderen dat Excel geen idee heeft. In feite bevindt de schoen zich aan de andere voet – zoals u zult zien wanneer u met de rechtermuisknop op de trendlijnvergelijking klikt, Trendlijnlabel opmaken kiest… uit de pop-up en vervolgens het aantal cijfers achter de komma in het resulterende taakvenster verhoogt.
Antwoord
Zoals anderen al hebben gezegd, denk ik niet dat VBA in dit geval nodig is.
Voor een eenvoudige lineaire trendlijn kun je de waarde van elke waarde Y op basis van een waarde X met behulp van de formules HELLING en INTERCEPT. De formule zou de vorm aannemen van
y = mx + b
waarbij y de afhankelijke variabele is, m de helling van de lijn, x de onafhankelijke variabele en “b” is het Y-snijpunt. Ervan uitgaande dat X-waarden in de cellen A2: A10, Y-waarden in de cellen B2: B10 en uw gewenste “X” -variabele in cel C1, voert u dit in als:
= SLOPE (B2: B10, A2: A10 ) * C1 + INTERCEPT (B2: B10, A2: A10)
Uw vraag geeft echter aan dat u geïnteresseerd bent in een trendlijn voor zwevend gemiddelde. In dit geval kunt u een bepaald gegevenspunt berekenen door het gemiddelde te nemen van de vorige n waarnemingen, waarbij n het aantal perioden in uw voortschrijdend gemiddelde is.
Om bijvoorbeeld een Voortschrijdend gemiddelde met 2 perioden (“n” = 2), het gemiddelde van de laatste twee waarden. Een eenvoudig voorbeeld wordt hieronder gekopieerd.
Als je VBA wilt gebruiken, dan zou deze code het moeten doen met minor wijzigingen naar eigen inzicht. Zoals het is, zou het moeten werken om eenvoudig een reeks X / Y-paren te selecteren en de macro uit te voeren. Het resultaat zal een zeer eenvoudige XY-spreidingsdiagram zijn met de kaarttitel die het volgende XY-paar in de reeks aangeeft.
Ik hoop dat dit helpt.
Ben
Sub AddXYScatter()
Dim r As Range
Dim c As Chart
Dim x As Long
Dim s As String
Set r = Selection
If r.Columns.Count 2 Then
MsgBox "Please select your X/Y pairs and try again"
Exit Sub
End If
x = Application.InputBox("Number of Periods?", "Periods?", 2, , , , , 1)
If r.Rows.Count <= x Then
MsgBox "Not enough observations, please select more rows or reduce periods."
Exit Sub
End If
ActiveSheet.Shapes.AddChart(xlXYScatter).Select
Set c = ActiveChart
With c
.SetSourceData Source:=r
.SeriesCollection(1).Trendlines.Add
With .SeriesCollection(1).Trendlines(1)
.Type = xlMovingAvg
.Period = x
End With
.SetElement (msoElementChartTitleAboveChart)
s = "Next X | Y Pair: "
s = s & Format(WorksheetFunction.Average(r.Offset(r.Rows.Count - x, 0).Resize(x, 1)), "0.00")
s = s & " | " & Format(WorksheetFunction.Average(r.Offset(r.Rows.Count - x, 1).Resize(x, 1)), "0.00")
.ChartTitle.Text = s
End With
Set r = Nothing
Set c = Nothing
End Sub