Bästa svaret
Excel-trendlinjerna är minst kvadrater som passar dina data. Du kan (och borde) be Excel att visa ekvationen för trendlinjen såväl som R-kvadratstatistiken (närmare 1 desto bättre).
Power-trendlinjen använder en ekvation av formen y = a * x ^ b, där a och b är konstanter som hittats av regressionsanalysen som utförs av trendlinjeguiden.
Observera att Excel inte visar tillräckligt med siffror i trendlinjekvationen. Om du försöker beräkna poäng med hjälp av ekvationen som ursprungligen visas kan du felaktigt dra slutsatsen att Excel inte har en aning. Faktum är att skon är på andra sidan – som du ser när du högerklickar på trendlinjekvationen, väljer Format Trendline-etikett … från popup-fönstret och sedan ökar antalet siffror efter decimaltecken i den resulterande uppgiftsfönstret.
Svar
Som andra har nämnt tror jag inte att VBA är nödvändigt i detta fall.
För en enkel linjär trendlinje kan du bestämma värde för valfritt värde Y baserat på ett värde X med hjälp av formlerna SLOPE och INTERCEPT. Formeln skulle ha formen av
y = mx + b
där ”y” är den beroende variabeln, ”m” är linjens lutning, ”x” är oberoende variabel och ”b” är Y-avlyssningen. Förutsatt att X-värden i cellerna A2: A10, Y-värdena i cellerna B2: B10 och önskad ”X” -variabel i cell C1, skulle du ange detta som:
= SLOPE (B2: B10, A2: A10 ) * C1 + INTERCEPT (B2: B10, A2: A10)
Din fråga indikerar dock att du är intresserad av en trendlinje för glidande medelvärde. I det här fallet kan du beräkna vilken datapunkt som helst genom att ta genomsnittet av de tidigare ”n” -observationerna, där ”n” är antalet perioder i ditt glidande medelvärde.
Till exempel för att beräkna en 2-periodens glidande medelvärde (“n” = 2), genomsnitt bara de två sista värdena. Ett enkelt exempel kopieras nedan.
Om du är inställd på att använda VBA, bör den här koden göra tricket med mindre modifieringar som du tycker passar. Som det är bör det fungera för att helt enkelt välja ett intervall av X / Y-par och köra makrot. Resultatet blir ett mycket grundläggande XY Scatter-diagram med diagrammets titel som visar nästa XY-par i sekvensen.
Hoppas att det hjälper.
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