Skip to content

Ajuste No-Lineal a un Conjunto de Datos usando Solver de Excel

mayo 29, 2008

Por Macario Hernández Garza

Sistemas de Optimización y Estadística, S. C. Copyright © 2008. Todos los derechos reservados.

Solver resulta útil para encontrar la curva que ajuste a un conjunto de datos, cuando estos son no lineales. Supongamos que tenemos el siguiente conjunto de datos:

Figura 1

Figura 1

Si graficamos los datos tendríamos:

Figura 2

Vamos a tratar de ajustar estos datos a la curva logística con ecuación:

Figura 3

Figura 3

Figura 4

En la Figura 4, tenemos que los valores en la columna A y B son los valores de los datos originales (X,Y). Los valores de la columna C, son los valores de Y estimada, denotada por Yest. Estos valores de Yest, se calculan mediante la ecuación de la Figura 3.

Los valores de a, b, c y d que aparecen en las celdas G1, G2, G3 y G4 respectivamente, son los valores iniciales, luego mediante Solver de Excel, trataremos de encontrar los valores óptimos, aquellos que mejor ajuste la curva a los datos.

En la Figura 5 se ven la gráfica de las coordenadas (X,Y), con la curva sigmoidal con coordenadas (X, Yest), la cual se tratará de ajustar los datos. Con los valores iniciales de a, b, c y d, que se muestran en la Figura 4.

Figura 5

Los valores de la columna D, son los errores cuadráticos, se calculan mediante la fórmula (Yest-Y)2. Por otra parte, los valores de Error = (Yest-Y), vienen representados para cada punto en la figura 4, como las rectas punteadas verticales. Entonces, deseamos encontrar los valores de a, b, c y d (Celdas G1, G2, G3 y G4), los parámetros de la curva logística dada por la ecuación de la Figura 3, que minimicen la suma cuadrática de los errores, celda G14, la fórmula en la celda G14, es =Sum(D2:D13).

Llamando el Solver de Excel

Debemos señalar que se debe tener instalado el Solver, el cual es un Add-inn de Excel. Teniendo instalado el Solver, lo llamamos mediante las opciones de Menú: Tools > Solver, después de lo cual aparece la forma del solver:

Figura 6

El parámetro Set Target Cell, debe ser $D$14, la celda que deseamos minimizar, podemos introducirla directamente o seleccionarla, oprimiendo con el ratón el cuadradito que está a la derecha y seleccionando la celda $D$14 en la hoja electrónica: Luego seleccionamos en Equal to, la opción de botón Min, ya que deseamos minimizar la celda objetivo o Target $D$14. Posteriormente en By Changing Cells, seleccionamos el rango $G$1:$G$4 que es donde están los valores de los parámetros de la recta, estos son los valores que Solver modificará, para encontrar los valores de G1, G2, G3 y G14 que minimicen, $D$14 (la suma de los errores cuadráticos). La forma con estos parámetros seleccionados luciría así:

Figura 7

Ahora, para que solver encuentre la solución, oprimimos con el ratón el botón Solve, después de lo cual Excel trabaja en hallar la solución. Cuando Excel halla la solución, pone una ventana como la siguiente, si aceptamos la solución hallada, oprimimos con el ratón el botón OK, y la solución encontrada se mantiene en la hoja de cálculo.

Figura 8

En este caso, la curva logística que mejor ajusta los datos es aquella con parámetros:

a = -1.06454, b = -1.85047, c = 896627 y d = 11.03581.

El escenario final con la curva logística que mejor ajusta los datos, usando el criterio de mínimos cuadrados, se muestra en seguida:

Figura 9

Finalmente tenemos los datos ajustados por la curva sigmoidal, con los parámetros a, b, c y d, encontrados por Solver de Excel:

Figura 10

About these ads
44 comentarios leave one →
  1. josebaltar permalink
    junio 10, 2014 6:14 am

    Buen día. Ud coloca fórmula EXCEL para calcular celda G14, pero … ¿Cuáles son las fórmulas en EXCEL de los parámetros a, b, c, d ? Gracias por contestar !

  2. Macario Hernández permalink*
    junio 10, 2014 11:40 am

    En cualquier método iterativo, los valores iniciales se deben buscar por “tanteo” por el usuario (a prueba y error, no hay de otra). Los valores que vayas ensayando verás si la curva se aleja o se acerca de los puntos observados. Entre más cerca se encuentren los valores iniciales más rápido encontrará la solución Solver.

  3. josebaltar permalink
    junio 13, 2014 2:53 am

    Revisé su vídeo y es muy efectivo como apoyo docente. Muchas gracias por su aporte !

  4. julio 15, 2014 5:11 am

    Gracias por su aportes !

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

%d personas les gusta esto: