Nejlepší odpověď
Trendové linie aplikace Excel jsou pro vaše data nejmenšími čtverci. Můžete (a měli byste) požádat Excel, aby zobrazil rovnici pro trendovou linii i statistiku R-kvadrát (blíže k 1, tím lépe).
Power trendová linie používá rovnici ve tvaru y = a * x ^ b, kde a a b jsou konstanty nalezené regresní analýzou provedenou průvodcem trendovou čarou.
Všimněte si, že Excel v rovnici trendové linie nezobrazuje dostatek číslic. Pokud se pokusíte vypočítat body pomocí rovnice, jak byla původně zobrazena, můžete chybně dojít k závěru, že Excel nemá ponětí. Ve skutečnosti je bota na druhé noze – jak uvidíte, když kliknete pravým tlačítkem na rovnici trendové čáry, ve vyskakovacím okně zvolíte Formátovat popisek čáry… a poté ve výsledném panelu úloh zvýšíte počet číslic za desetinnou čárkou.
Odpověď
Jak již zmínili ostatní, nemyslím si, že v tomto případě je VBA nezbytný.
U jednoduché lineární trendové linie můžete určit hodnota libovolné hodnoty Y na základě hodnoty X pomocí vzorců SLOPE a INTERCEPT. Vzorec by měl formu
y = mx + b
, kde „y“ je závislá proměnná, „m“ je sklon čáry, „x“ je nezávislá proměnná a „b“ je Y-průsečík. Za předpokladu, že hodnoty X v buňkách A2: A10, hodnoty Y v buňkách B2: B10 a požadovaná proměnná „X“ v buňce C1, zadáte jako:
= SLOPE (B2: B10, A2: A10 ) * C1 + INTERCEPT (B2: B10, A2: A10)
Vaše otázka však naznačuje, že máte zájem o trendovou linii klouzavého průměru. V tomto případě můžete vypočítat jakýkoli daný datový bod tak, že vezmete průměr z předchozích pozorování „n“, kde „n“ je počet období v klouzavém průměru.
Například pro výpočet Klouzavý průměr za 2 období („n“ = 2), jednoduše průměrujte poslední dvě hodnoty. Níže je zkopírován jednoduchý příklad.
Pokud jste nastaveni na používání VBA, pak by tento kód měl dělat trik s menšími úpravy, jak uznáš za vhodné. Mělo by to fungovat tak, že jednoduše vyberete rozsah dvojic X / Y a spustíte makro. Výsledkem bude velmi základní graf XY Scatter s názvem grafu označujícím další pár XY v pořadí.
Doufám, že to pomůž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