Skip to content

Ajuste Lineal a un Conjunto de Datos usando Solver de Excel

mayo 25, 2008

Por Macario Hernández Garza

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

Tenemos el siguiente conjunto de datos.

Figura 1

Figura 1

Si graficamos estos datos, nos quedaría:

Figura 2

Figura 2

Como se ve la configuración de los puntos, los datos graficados podrían ser ajustados por una línea recta, vamos entonces a tratar de encontrar mediante Solver de Excel, la recta que minimice la suma de las distancias cuadráticas de los puntos a la recta.

Como sabemos la ecuación de la recta es Y = A + B X

Figura 3

Figura 3

En la figura anterior, 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 recta, de tal forma que, por ejemplo el valor de C2, se calcula mediante: Yest = A + B X = 0.3 + 1.0 (A2) =0.3 + 1.0(0) = 0.3; y de la misma manera para el cálculo de los otros valores, la recta de la siguiente figura se grafica con las coordenadas (X,Yest). Los valores de B y A que aparecen en las celdas G1 y G2, respectivamente, son los valores iniciales, luego mediante Solver de Excel, trataremos de encontrar los valores óptimos, aquellos que mejor ajuste la recta a los datos.

Los valores de la columna D, son los errores cuadráticos, se calculan mediante la fórmula (YestY)2 . Por ejemplo, para el primer punto, en el renglón 2:

(Error)2 =(YestY)2 = (0.3-0.6182)2 = 0.10125124

Valor que aparece en la celda D2.

Por otra parte, los valores de Error = (YestY), vienen representados para cada punto en la figura 4, como las rectas punteadas verticales. Entonces, deseamos encontrar los valores de A y B (Celdas G1 y G2), los parámetros de la recta, que minimicen la suma cuadrática de los errores, celda G18, la fórmula en la celda G18, es =Sum(D2:D17).

Figura 4

Figura 4

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 5

Figura 5

El parámetro Set Target Cell, debe ser $D$18, 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$18 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$18. Posteriormente en By Changing Cells, seleccionamos el rango $G$1:$G$2 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 y G2 que minimicen, $D$18 (la suma de los errores cuadráticos). La forma con estos parámetros seleccionados luciría así:

Figura 6

Figura 6

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 hallar 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 7

En este caso, la recta que mejor ajusta los datos es aquella con parámetros: A = 0.345869 y B = 1.042646

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

Figura 8

Figura 8

Figura 9

Figura 9

No comments yet

Responder

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

A %d blogueros les gusta esto: