Bedste svar
Excel-trendlinjerne er mindst kvadrater, der passer til dine data. Du kan (og bør) bede Excel om at vise ligningen for trendlinjen såvel som R-kvadratstatistikken (tættere på 1, jo bedre).
Power-trendlinjen bruger en ligning af formen y = a * x ^ b, hvor a og b er konstanter fundet af regressionsanalysen udført af trendlinjeguiden.
Bemærk, at Excel ikke viser nok cifre i trendlineligningen. Hvis du prøver at beregne point ved hjælp af ligningen som oprindeligt vist, kan du fejlagtigt konkludere, at Excel ikke har en anelse. Faktisk er skoen på den anden fod – som du vil se, når du højreklikker på trendlinjeligning, vælg Formatér Trendline-etiket … fra pop op-vinduet, og øg derefter antallet af cifre efter decimaltegnet i den resulterende opgaverude.
Svar
Som andre har nævnt, tror jeg ikke, at VBA er nødvendigt i dette tilfælde.
For en simpel lineær tendenslinje kan du bestemme værdi af en hvilken som helst værdi Y baseret på en værdi X ved hjælp af formlen SLOPE og INTERCEPT. Formlen ville have form af
y = mx + b
hvor “y” er den afhængige variabel, “m” er hældningen på linjen, “x” er uafhængig variabel og “b” er Y-skæringen. Under forudsætning af X-værdier i cellerne A2: A10, Y-værdierne i cellerne B2: B10 og den ønskede “X” -variabel i celle C1, ville du indtaste dette som:
= HÆNGNING (B2: B10, A2: A10 ) * C1 + INTERCEPT (B2: B10, A2: A10)
Dit spørgsmål indikerer dog, at du er interesseret i en glidende gennemsnitstrendelinje. I dette tilfælde kan du beregne et hvilket som helst givet datapunkt ved at tage gennemsnittet af de tidligere “n” observationer, hvor “n” er antallet af perioder i dit glidende gennemsnit.
For eksempel at beregne en 2-periode glidende gennemsnit (“n” = 2), simpelthen gennemsnit de sidste to værdier. Et simpelt eksempel kopieres nedenfor.
Hvis du er indstillet på at bruge VBA, skal denne kode gøre tricket med mindre ændringer, som du finder passende. Som det er, skal det fungere for blot at vælge en række X / Y-par og køre makroen. Resultatet bliver et meget grundlæggende XY Scatter-diagram med diagrammetitlen, der angiver det næste XY-par i sekvensen.
Håber 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