miércoles, 7 de mayo de 2014

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.



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 R2, 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=m2x2 + m1x + b (parábola)
Grado 3: y=m3x3 + m2x2 + m1x + 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 R2 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:

Obtener línea de tendencia polinómica matricialmente.


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

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=m6x6 + ... +m3x3 + m2x2 + m1x + 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:


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, x2) 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 x3, x4, etc... para luego incluirlas en la función ESTIMACION.LINEAL como parte del argumento 'datos de x'

18 comentarios:

  1. Hola excelforo,
    Una consulta quisiera saber que fórmula tiene la celda C14 y que me explique un poco de como haz realizado la gráfica que es dinámica con la validación de datos osea la celda E14, me gustaría saber que pasos has realizado.
    de antemano gracias
    atentamente
    Ana
    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Ana,
      en C14 no hay ninguna fórmula es la variable independiente a partir de donde se calcula B14 (variable dependiente).

      Respecto a la gráfica 'dinámica' lleva asociada programación, en concreto un evento Change, que se ejecuta al variar la celda E14.
      En particular el código es:
      Private Sub Worksheet_Change(ByVal Target As Range)

      If Not Intersect(Target, Range("E14")) Is Nothing Then
      If Range("E14").Value > 1 Then
      ActiveSheet.ChartObjects("Gráfico 2").Activate
      With ActiveChart.SeriesCollection(1).Trendlines(1)
      .Type = xlPolynomial
      .Order = Range("E14").Value
      End With
      Else
      ActiveSheet.ChartObjects("Gráfico 2").Activate
      With ActiveChart.SeriesCollection(1).Trendlines(1)
      .Type = xlLinear
      End With
      End If
      Range("E14").Select
      End If

      End Sub

      Saludos

      Eliminar
    2. EN LA POTENCIA {1\2\3\4}, SON VALORES BALIDOS? POR QUE VALORES LOS SUSTITUYO?

      Eliminar
    3. Hola Gusvo,
      que tal estás?.. un placer saludarte igualmente.
      la matriz {1\2\3\4} representa los exponentes de los parámetros de una ecuación de cuarto grado... si requieres una línea polinómica de cuarto grado es lo que debes usar.

      Un saludo y gracias por no escribir en mayúsculas.

      Eliminar
  2. Hola Excelforo, muy buen aporte, me fue de gran utilidad seguir tus explicaciones. Pero tengo un problema, ya que quiero estimar un x de varias curvas para un valor y determinado y no se como hacerlo, con el comando objetivo no me sirve por tener diferentes curvas. Se puede solucionar de alguna manera??

    ResponderEliminar
    Respuestas
    1. Hola Anselmo,
      discúlpame pero no comprendo tu planteamiento...
      a qué te refieres con el 'comando objetivo'???

      Un saludo

      Eliminar
  3. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  4. Excelente aporte, muchas gracias! ¿Cómo lograste el cambio automático en el encabezado?

    ResponderEliminar
    Respuestas
    1. Gracias Jorge,
      el encabezado (celdas F2:L2) tienen una función matricial:
      =SI(INDIRECTO("para_"&$E$14)=0;"";INDIRECTO("para_"&$E$14))

      Un saludo

      Eliminar
    2. Nuevamente, muchas gracias, funciono perfecto! Aporte de mucha utilidad!

      Eliminar
  5. estaria excelente tener el archivo de excel que elaboraste para solucionar todas las dudas y errores que me surgieron al realizarlo

    ResponderEliminar
    Respuestas
    1. Hola Hector,
      como estás?, un placer saludarte igualmente...

      Solo debes seguir los pasos descritos, si te surge alguna duda, por supuesto, puedes plantearla aquí.

      Saludos

      Eliminar
  6. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  7. que tal, ya he realizado el ejercicio, pero aun no me queda, tego duda en la celda C14 y de como hacer para que varien mis datos en los rangos de F:L de l renglon 3 al 8 ne funcion de la celda E14, me gustaria que vieras mi archivo, pero no se donde enviartelo

    ResponderEliminar
  8. también para el caso del rango F2:L2 no me hace el cambio, ya aplique la función matricial, pero me manda #Valor

    ResponderEliminar
    Respuestas
    1. Hola Hector,
      puedes enviarme el fichero a
      excelforo@gmail.com
      Saludos

      Eliminar
  9. gracias por ver mi fichero, lamentablemente te envíe mi primer versión, pero aun así con lo que me comentaste por correo logre recrear el ejemplo, gracias, igual ya comprendí que es el valor de C14, muy buen ejemplo, en mi caso, como reto, no tengo permitido usar código VBA para la gráfica, tienes alguna recomendación para hacer la sin el.

    ResponderEliminar
    Respuestas
    1. ;-)
      Sin macros la actualización y cambio de tipo de línea se debería hacer manualmente

      Saludos

      Eliminar