Melhor resposta
As linhas de tendência do Excel são mínimos quadrados se ajustam aos seus dados. Você pode (e deve) pedir ao Excel para exibir a equação para a linha de tendência, bem como a estatística R-quadrado (mais próximo de 1, melhor).
A linha de tendência Power usa uma equação da forma y = a * x ^ b, onde aeb são constantes encontradas pela análise de regressão realizada pelo assistente de linha de tendência.
Observe que o Excel não exibe dígitos suficientes na equação da linha de tendência. Se você tentar calcular pontos usando a equação exibida inicialmente, pode concluir erroneamente que o Excel não tem a menor ideia. Na verdade, o sapato está no outro pé – como você verá ao clicar com o botão direito na equação da linha de tendência, escolher Formatar rótulo da linha de tendência … no pop-up e, em seguida, aumentar o número de dígitos após o ponto decimal no painel de tarefas resultante.
Resposta
Como outros mencionaram, não acho que o VBA seja necessário neste caso.
Para uma linha de tendência linear simples, você pode determinar o valor de qualquer valor Y com base em um valor X usando as fórmulas SLOPE e INTERCEPT. A fórmula teria a forma de
y = mx + b
onde “y” é a variável dependente, “m” é a inclinação da linha, “x” é o variável independente e “b” é o intercepto Y. Assumindo valores X nas células A2: A10, valores Y nas células B2: B10 e sua variável “X” desejada na célula C1, você deve inserir isso como:
= SLOPE (B2: B10, A2: A10 ) * C1 + INTERCEPÇÃO (B2: B10, A2: A10)
No entanto, sua pergunta indica que você está interessado em uma linha de tendência de média móvel. Neste caso, você pode calcular qualquer dado ponto de dados tomando a média das observações anteriores de “n”, onde “n” é o número de períodos em sua média móvel.
Por exemplo, para calcular um Média móvel de 2 períodos (“n” = 2), simplesmente calcule a média dos dois últimos valores. Um exemplo simples é copiado abaixo.
Se você está usando o VBA, então este código deve resolver o problema com o menor modificações como você achar necessário. Como está, deve funcionar simplesmente selecionando um intervalo de pares X / Y e executando a macro. O resultado será um gráfico de dispersão XY muito básico com o título do gráfico indicando o próximo par XY na sequência.
Espero que ajude.
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
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