Najlepsza odpowiedź
Linie trendu Excela to najmniejsze kwadraty pasujące do danych. Możesz (i powinieneś) poprosić Excela o wyświetlenie równania dla linii trendu, a także statystyki R-kwadrat (bliżej 1 tym lepiej).
Linia trendu Power używa równania w postaci y = a * x ^ b, gdzie a i b są stałymi znalezionymi podczas analizy regresji wykonanej przez kreator linii trendu.
Zauważ, że Excel nie wyświetla wystarczającej liczby cyfr w równaniu linii trendu. Jeśli spróbujesz obliczyć punkty za pomocą równania, jak pokazano na początku, możesz błędnie dojść do wniosku, że Excel nie ma pojęcia. W rzeczywistości but znajduje się na drugiej stopie – jak widać po kliknięciu prawym przyciskiem równania linii trendu, wybraniu opcji Formatuj etykietę linii trendu… z wyskakującego okienka, a następnie zwiększeniu liczby cyfr po przecinku w wynikowym okienku zadań.
Odpowiedź
Jak wspominali inni, nie sądzę, aby VBA był potrzebny w tym przypadku.
W przypadku prostej liniowej linii trendu można określić wartość dowolnej wartości Y na podstawie wartości X przy użyciu formuł NACHYLENIE i ODCIĘCIE. Wzór miałby postać
y = mx + b
gdzie „y” to zmienna zależna, „m” to nachylenie linii, „x” to zmienna niezależna, a „b” jest punktem przecięcia z osią Y. Zakładając wartości X w komórkach A2: A10, wartości Y w komórkach B2: B10 i żądaną zmienną „X” w komórce C1, wpiszesz to jako:
= NACHYLENIE (B2: B10, A2: A10 ) * C1 + INTERCEPT (B2: B10, A2: A10)
Jednak Twoje pytanie wskazuje, że interesuje Cię linia trendu średniej ruchomej. W takim przypadku możesz obliczyć dowolny punkt danych, biorąc średnią z poprzednich „n” obserwacji, gdzie „n” to liczba okresów w Twojej średniej ruchomej.
Na przykład, aby obliczyć Średnia krocząca z 2 okresów („n” = 2), po prostu uśrednij dwie ostatnie wartości. Prosty przykład jest skopiowany poniżej.
Jeśli korzystasz z języka VBA, ten kod powinien załatwić sprawę z niewielkimi modyfikacje według własnego uznania. W rzeczywistości powinno to działać po prostu wybierając zakres par X / Y i uruchamiając makro. Rezultatem będzie bardzo podstawowy wykres punktowy XY z tytułem wykresu wskazującym na następną parę XY w sekwencji.
Mam nadzieję, że to pomoże.
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