Obtener línea de tendencia polinómica matricialmente.

Hace ya bastante tiempo, al inicio de mi aventura como Blogger, expuse un sencillo ejemplo de la función ESTIMACION.LINEAL (ver), y cómo construir una línea de regresión. Hoy avanzaremos sobre este tema y veremos cómo conseguir, apoyándonos en constantes matriciales, una ecuación polinómica de regresión de diferente orden o grado.

En este ejemplo hay varias fórmulas que complementan nuestro objetivo, pero iremos por partes, centrándonos en la manera de conseguir nuestro polinomio.

haz clic en la imagen

Empecemos por los datos a partir de los cuales obtendremos nuestra ecuación. Los datos de la variable independiente 'datos_x' están en el rango C3:C13; mientras que los de la variable Y dependiente 'datos_y' los hallamos en el rango de celdas B3:B13.
El objetivo es, según nuestra elección en la celda E14, conseguir el polinomio que represente la línea de tendencia, de grado 1 a 6, junto a su coeficiente de determinación R², y a partir de la ecuación el valor estimado de y. Lo que se muestra en la imagen en las celdas F3:M8.

Importante también el uso de los nombres definidos, que serán los siguientes.
Para los datos del origen:
datos_x =Hoja1!$C$3:$C$13
datos_y =Hoja1!$B$3:$B$13

Para los coeficientes de las futuras ecuaciones:
coef_1 =Hoja1!$F$3:$G$3
coef_2 =Hoja1!$F$4:$H$4
coef_3 =Hoja1!$F$5:$I$5
coef_4 =Hoja1!$F$6:$J$6
coef_5 =Hoja1!$F$7:$K$7
coef_6 =Hoja1!$F$8:$L$8

Para los valores de las potencias del valor x a estimar:
x_1 =Hoja1!$K$10:$L$10
x_2 =Hoja1!$J$10:$L$10
x_3 =Hoja1!$I$10:$L$10
x_4 =Hoja1!$H$10:$L$10
x_5 =Hoja1!$G$10:$L$10
x_6 =Hoja1!$F$10:$L$10

Para ajustar el texto del encabezado en F2:L2:
para_1 =Hoja1!$P$8:$V$8
para_2 =Hoja1!$P$7:$V$7
para_3 =Hoja1!$P$6:$V$6
para_4 =Hoja1!$P$5:$V$5
para_5 =Hoja1!$P$4:$V$4
para_6 =Hoja1!$P$3:$V$3

Lógicamente no es el mismo número de parámetros de una ecuación de grado 1 que de grado 6, siendo sus formas:
Grado 1: y=mx + b (linea recta)
Grado 2: y=m₂x² + m₁x + b (parábola)
Grado 3: y=m₃x³ + m₂x² + m₁x + b
etc...

Así, para la ecuación de la línea recta (grado1), seleccionaremos el rango F3:L3 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;datos_x)
Para la ecuación de grado 2 seleccionaremos el rango F4:L4 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2}))
Para la ecuación de grado 3 seleccionaremos el rango F5:L5 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2\3}))
Para la ecuación de grado 4 seleccionaremos el rango F6:L6 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2\3\4}))
Para la ecuación de grado 5 seleccionaremos el rango F7:L7 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2\3\4\5}))
Para la ecuación de grado 6 seleccionaremos el rango F8:L8 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2\3\4\5\6}))

Como vemos la estructura es similar para las diferentes ecuaciones, basta adaptar la constante matricial al grado del polinomio.

Para los valores potenciados de x dado para estimar y en el rango de celdas F10:L10 introducimos y ejecutamos matricialmente:
=POTENCIA($C$14;{6\5\4\3\2\1\0})

Para el cálculo del R² para cada polinómio aplicamos la función INDICE a la función ESTIMACION.LINEAL, detallando qué 'estadístico' (qué situación) es el que nos interesa (fila 3 columna 1 de la matriz de estadísticos).
Por tanto para la ecuación de grado 1 tendríamos en la celda M3:
=INDICE(ESTIMACION.LINEAL(datos_y;datos_x;;1);3)
para la de grado 2 en M4:
=INDICE(ESTIMACION.LINEAL(datos_y;datos_x^{1\2};;1);3)
para la de grado 3 en M5:
=INDICE(ESTIMACION.LINEAL(datos_y;datos_x^{1\2\3};;1);3)
y sucesivamente hasta M8.

Con lo que tendríamos lo que buscamos, el valor y estimado para un x dado, según el grado del polinomio que nos interese seleccionándolo en la celda validada E14:

Vemos como del gráfico resultante (ajustado con macros) obtenemos la misma ecuación y el mismo coeficiente de determinación R².

Finalmente en la celda B14 es donde hemos conseguido el valor de y estimado aplicando la función SUMAPRODUCTO, ajustándola al grado del polinomio seleccionado:
=SUMAPRODUCTO(INDIRECTO("coef_"&$E$14);INDIRECTO("x_"&$E$14))
donde vemos que realizamos el producto elemento a elemento y luego la suma de la matriz de coeficientes con los de los valores de x potenciados... es decir, donde conseguimos el producto y la suma posterior resultante de la ecuación del tipo:
y=m₆x⁶ + ... +m₃x³ + m₂x² + m₁x + b

Otra forma de conseguirlo, sería empleando columnas auxiliares que representaran las potencias del valor x, como se ve en el ejemplo de la siguiente imagen para conseguir los coeficientes de la ecuación, al que hemos anexado el cálculo =B3^2 en el rango C3:C13:

haz clic en la imagen

En F3:J3 he añadido:
=ESTIMACION.LINEAL(A3:A13;B3:C13)

Vemos que con una función 'sencilla' ESTIMACION.LINEAL tomando como valores de x todas las columnas de x (x, x²) conseguimos los mismos datos que los calculados anteriormente...
Para ecuaciones de mayor grado bastaría agregar nuevas columnas auxiliares con la operación de potencia correspondiente x³, x⁴, etc... para luego incluirlas en la función ESTIMACION.LINEAL como parte del argumento 'datos de x'