Beste svaret
Excel-trendlinjene er minst kvadrater som passer til dataene dine. Du kan (og bør) be Excel om å vise ligningen for trendlinjen så vel som R-kvadratstatistikken (nærmere 1, jo bedre).
Power-trendlinjen bruker en ligning av formen y = a * x ^ b, der a og b er konstanter funnet av regresjonsanalysen utført av trendlinjeveiviseren.
Merk at Excel ikke viser nok sifre i trendlinjeligningen. Hvis du prøver å beregne poeng ved hjelp av ligningen som først vist, kan du feilaktig konkludere med at Excel ikke har peiling. Faktisk er skoen på den andre foten – som du vil se når du høyreklikker på trendlinjeligningen, velger Format Trendline Label … fra popup-vinduet, og øker deretter antall sifre etter desimaltegnet i den resulterende oppgaveruten.
Svar
Som andre har nevnt, tror jeg ikke at VBA er nødvendig i dette tilfellet.
For en enkel lineær trendlinje kan du bestemme verdi av hvilken som helst verdi Y basert på en verdi X ved hjelp av formlene SLOPE og INTERCEPT. Formelen vil ha form av
y = mx + b
der «y» er den avhengige variabelen, «m» er stigningen på linjen, «x» er uavhengig variabel og “b” er Y-skjæringspunktet. Forutsatt at X-verdiene i cellene A2: A10, Y-verdiene i cellene B2: B10 og ønsket «X» -variabel i celle C1, vil du angi dette som:
= HELLING (B2: B10, A2: A10 ) * C1 + INTERCEPT (B2: B10, A2: A10)
Spørsmålet ditt indikerer imidlertid at du er interessert i en trendlinje for glidende gjennomsnitt. I dette tilfellet kan du beregne et gitt datapunkt ved å ta gjennomsnittet av de forrige «n» -observasjonene, der «n» er antall perioder i det glidende gjennomsnittet.
For eksempel å beregne en 2-perioders glidende gjennomsnitt (“n” = 2), gjennomsnitt bare de to siste verdiene. Et enkelt eksempel kopieres nedenfor.
Hvis du er innstilt på å bruke VBA, bør denne koden gjøre susen med mindre modifikasjoner etter eget ønske. Som det er, skal det fungere for å bare velge et utvalg av X / Y-par og kjøre makroen. Resultatet blir et veldig grunnleggende XY Scatter-diagram med diagramtittelen som indikerer neste XY-par i sekvensen.
Håper dette hjelper.
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