Excel - FORZAR LA ECUACIÓN DE LA LÍNEA DE TENDENCIA

Option Explicit

Option Private Module

'//By JuanC - 07 de Septiembre de 2007

Sub Test()

Dim Y$, sRangoDatos$, sHoja$, sTmp$

Dim c1$, c2$, c3$, c4$

'//Origen de los datos a graficar

sRangoDatos = "B2:C8"

'//Nombre de la hoja destino del gráfico

sHoja = "Hoja1"

Application.ScreenUpdating = False

'//Crear gráfico (polinomio de 3er grado)

Charts.Add

ActiveChart.Type = xlXYScatter

ActiveChart.SetSourceData Source:=Sheets(sHoja).Range(sRangoDatos), PlotBy:=xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:=sHoja

ActiveChart.SeriesCollection(1).Trendlines.Add Type:=xlPolynomial, Order:=3, Forward:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=False

'//Tomar fórmula del gráfico ( y = c1x3 + c2x2 + c3x + c4 )

Y = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Caption

'//Eliminar gráfico (opcional)

ActiveChart.ChartArea.Select

ActiveWindow.Visible = False

Selection.Delete

Application.ScreenUpdating = True

'//Extraer coeficientes (c1, c2, c3 y c4)

Y = LCase(Y)

Y = Replace(Y, " x3", "1x3")

Y = Replace(Y, " x2", "1x2")

Y = Replace(Y, " x", "1x")

sTmp = Split(Y, "x3")(0)

c1 = Replace(Mid(sTmp, InStr(1, sTmp, "=") + 1), " ", "")

c1 = Replace(c1, "+", "")

sTmp = Mid(Y, InStr(1, Y, "x3") + 2)

c2 = Replace(Split(sTmp, "x")(0), " ", "")

c2 = Replace(c2, "+", "")

sTmp = Mid(Y, InStr(1, Y, "x2") + 2)

c3 = Replace(Split(sTmp, "x")(0), " ", "")

c3 = Replace(c3, "+", "")

sTmp = Mid(Y, InStrRev(Y, " ") - 2)

c4 = Replace(sTmp, " ", "")

c4 = Replace(c4, "+", "")

'//Preparar mensaje para el usuario y mostrarlo

'//(obviamente pueden convertirse los coeficientes

'// en números y realizar cálculos)

sTmp = "Polinomio de ajuste (grado 3):" & vbCrLf & vbCrLf & Y & vbCrLf

sTmp = sTmp & vbCrLf & "C1: " & c1 & " (x^3)"

sTmp = sTmp & vbCrLf & "C2: " & c2 & " (x^2)"

sTmp = sTmp & vbCrLf & "C3: " & c3 & " (x^1)"

sTmp = sTmp & vbCrLf & "C4: " & c4 & " (x^0)"

MsgBox sTmp

End Sub