Cel mai bun răspuns
Liniile de trend Excel sunt cele mai mici pătrate care se potrivesc cu datele dvs. Puteți (și ar trebui) să solicitați Excelului să afișeze ecuația liniei de tendință, precum și statistica R-pătrat (mai aproape de 1 cu atât mai bine).
Linia de tendință Power utilizează o ecuație de forma y = a * x ^ b, unde a și b sunt constante găsite de analiza de regresie efectuată de vrăjitorul de linie de trend.
Rețineți că Excel nu afișează suficiente cifre în ecuația liniei de trend. Dacă încercați să calculați punctele folosind ecuația așa cum este afișată inițial, puteți concluziona în mod eronat că Excel nu are niciun indiciu. De fapt, pantoful se află pe celălalt picior – așa cum veți vedea când faceți clic dreapta pe ecuația liniei de tendință, alegeți Formatează eticheta liniei de tendință … din fereastra pop-up, apoi creșteți numărul de cifre după punctul zecimal din panoul de sarcini rezultat.
Răspuns
După cum au menționat alții, nu cred că VBA este necesar în acest caz.
Pentru o linie de tendință liniară simplă, puteți determina valoarea oricărei valori Y bazată pe o valoare X utilizând formulele SLOPE și INTERCEPT. Formula ar lua forma
y = mx + b
unde „y” este variabila dependentă, „m” este panta liniei, „x” este variabila independentă și „b” este interceptarea Y. Presupunând valori X în celulele A2: A10, valori Y în celulele B2: B10 și variabila „X” dorită în celula C1, veți introduce acest lucru ca:
= SLOPE (B2: B10, A2: A10 ) * C1 + INTERCEPT (B2: B10, A2: A10)
Cu toate acestea, întrebarea dvs. indică faptul că sunteți interesat de o linie de trend medie mobilă. În acest caz, puteți calcula orice punct de date dat luând media observațiilor anterioare „n”, unde „n” este numărul de perioade din media mobilă.
De exemplu, pentru a calcula un Media mișcătoare pe 2 perioade („n” = 2), pur și simplu mediați ultimele două valori. Un exemplu simplu este copiat mai jos.
Dacă sunteți setat să utilizați VBA, atunci acest cod ar trebui să facă trucul cu minor modificările după cum considerați potrivit. Ca atare, ar trebui să funcționeze pentru simpla selectare a unui interval de perechi X / Y și rularea macro-ului. Rezultatul va fi o diagramă de bază XY Scatter cu titlul graficului care indică următoarea pereche XY din secvență.
Sper că acest lucru va fi de ajutor.
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