Regresión Lineal en Solver

 

 

Si ya ha utilizado las funciones propias del Excel para hacer una regresión lineal simple, puede parecerle que no tiene sentido utilizar el Solver, pues requeriría más trabajo. Pero esto se justifica más en la generalización del procedimiento para cualquier tipo de regresión, que para utilizarla simplemente en la regresión lineal simple.


Efectivamente, el procedimiento para utilizar Solver en una regresión lineal de más de una variable independiente (regresión compuesta), es el mismo que el que vamos a utilizar aquí; es más lo podemos generalizar para que se pueda generalizar para las regresiones cuadráticas, exponenciales, polinómicas, y en si para cualquier tipo de función.


Tomemos de nuevo el ejemplo del resorte que se estudio en la página principal de los mínimos cuadrados. De nuevo deseamos encontrar los parámetros a y b de la mejor recta de ajuste a los datos dados.

 

 

X (Peso) Y (Elongación)
0 0
5 1.55745384
10 1.29047259
15 3.15393595
20 4.22759777
25 4.660399
30 5.20125993
35 6.99640071
40 7.07405399
45 9.70686639
50 10.4643135
55 11.4457161
60 11.8000983
65 12.1885022
70 14.7024209
75 15.8981366
80 15.0221159
85 17.6232118
90 18.9858718
95 19.6699194
100 19.7012217
105 21.1212573

 

Procedimiento:


Lo más importante que hay que tener en cuenta es la explicación sobre la función de error, que se dió en la página principal de los mínimos cuadrados. Con esto en mente, lo que debemos hacer en Excel es apartar una columna que contendrá la función de error al cuadrado. Esta función de error dependerá de los parámetros que queremos hallar, según el modelo adoptado: lineal, cuadrático, polinómico, etc. Cada parámetro tendrá una celda particular, para que Excel Solver, las cambie encontrando el óptimo que será el mínimo de la suma de esta columna (que la dejaremos en una celda especial también).


Entonces, coloquemos en la columna B y C a partir de la fila 4, los valores medidos que encontramos en la tabla de arriba. En la columna D colocaremos la función de error. En este caso estamos ajustando a una recta Y' = a + b x. Entonces la función de error depende de los parámetros a y b, para los cuales le dejaremos las celdas $B$3 y $C$3 respectivamente. Como la función de error según lo que habiamos dicho es = (a + bx - Y)^2, entonces la fórmula quedará así (la escribimos en la celda D5 y la arrastramos para las demás de abajo) =($B$3+$C$3*B5-C5)^2. Pero eso sólo expresa un error puntual y lo que queremos es el error de todos sobre todos los datos medidos, o sea la sumatoria de los errores puntuales, eso lo podemos hacer tomando la suma en otra celda, digamos en la celda B1, entonces B1 =SUMA(D5:D26) Por que desde la fila 5 hasta la 26 estan los errores puntuales. Damos un poco de formato para una mejor legibilidad y debemos tener algo como esto:

 

 

Ahora invocamos el Solver (Herramientas, Opciones) y lo configuramos como se observa en la captura:
 
 
Luego de hacer click en resolver, se halla la solución que es:  
 


 

Contenido :

Social