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
50 comentarios leave one →
  1. Macario Hernández permalink*
    septiembre 9, 2014 11:20 am

    En este post se utiliza el método de mínimos cuadrados. Yo no he trabajado con los algoritmos de Levenberg-Marquadt y Gauss-Newton, pero supongo que estos dos últimos algoritmos son más eficientes. Trata de Consultar el siguiente link en Wikipedia: http://en.wikipedia.org/wiki/Levenberg%E2%80%93Marquardt_algorithm.

    Saludos

  2. Yhosmary permalink
    septiembre 9, 2014 6:22 pm

    ok muchas gracias

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: