Mejor respuesta
Las líneas de tendencia de Excel son ajustes por mínimos cuadrados a sus datos. Puede (y debe) pedirle a Excel que muestre la ecuación para la línea de tendencia, así como la estadística R-cuadrado (más cerca de 1, mejor).
La línea de tendencia de Power usa una ecuación de la forma y = a * x ^ b, donde ayb son constantes encontradas por el análisis de regresión realizado por el asistente de línea de tendencia.
Tenga en cuenta que Excel no muestra suficientes dígitos en la ecuación de línea de tendencia. Si intenta calcular puntos usando la ecuación como se muestra inicialmente, puede concluir erróneamente que Excel no tiene ni idea. De hecho, el zapato está en el otro pie, como verá cuando haga clic con el botón derecho en la ecuación de la línea de tendencia, elija Formato de etiqueta de línea de tendencia … en la ventana emergente y luego aumente el número de dígitos después del punto decimal en el panel de tareas resultante.
Respuesta
Como han mencionado otros, no creo que VBA sea necesario en este caso.
Para una línea de tendencia lineal simple, puede determinar el valor de cualquier valor Y basado en un valor X usando las fórmulas PENDIENTE e INTERCEPT. La fórmula tomaría la forma de
y = mx + b
donde «y» es la variable dependiente, «m» es la pendiente de la línea, «x» es la variable independiente y «b» es la intersección con el eje Y. Suponiendo valores de X en las celdas A2: A10, valores de Y en las celdas B2: B10 y la variable «X» deseada en la celda C1, ingresaría esto como:
= PENDIENTE (B2: B10, A2: A10 ) * C1 + INTERCEPT (B2: B10, A2: A10)
Sin embargo, su pregunta indica que está interesado en una línea de tendencia de media móvil. En este caso, puede calcular cualquier punto de datos dado tomando el promedio de las «n» observaciones anteriores, donde «n» es el número de períodos en su promedio móvil.
Por ejemplo, para calcular un Promedio móvil de 2 períodos (“n” = 2), simplemente promedia los dos últimos valores. A continuación se copia un ejemplo simple.
Si está configurado para usar VBA, entonces este código debería funcionar con menor modificaciones como mejor le parezca. Tal como está, debería funcionar simplemente para seleccionar un rango de pares X / Y y ejecutar la macro. El resultado será un gráfico de dispersión XY muy básico con el título del gráfico que indica el siguiente par XY en la secuencia.
Espero que esto ayude.
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