Proceso de solución minimos cuadrados en Excel.pdf

Solución Mínimos cuadrados en Excel, Universidad de Córdoba, ingeniería de Alimentos (2023)

1. Proceso de solución en Solver
Resolveremos el siguiente problema:

Se deben determinar las contantes k y C del modelo.
Resolvemos para P: P=C*V
-k
(esta es la ecuación modelo)
Vemos que P es la variable dependiente (y) y V es la variable independiente (x)
Aplicaremos las ecuaciones dadas en los apuntes. SSE, SST y R
2

Proceso de solución:
Abrimos Excel.
Cargamos los datos experimentales de P y V (Pexp y Vexp).

- Colocamos las constantes k y C.

2. - Ingresamos el modelo. P mod=C*Vexp
-k
en la primera celda, para el primer valor de Vexp.

NOTA: Algo que hay que saber es sobre referencias absolutas y referencias relativas en
Excel (deben investigar esto). Las variables son referencias relativas y las constantes
son referencias absolutas. En Excel se trabaja con referencias a celdas, como una matriz
o dirección. La hoja de cálculo se compone de columnas y filas ( ej. El valor de k se
encuentra en B14 (esto se llama referencia), columna B y fila 14 como pueden observar).
Así como esta B14 es referencia relativa y si queremos que sea absoluta la colocamos así
$B$14. Esto último se logra automáticamente seleccionando la referencia y presionando
la tecla F4.

Ingresemos el modelo, utilizando referencias. Ver imagen (cuadros y colores).



Presionan la tecla Enter y regresan nuevamente a la celda.


Ahora aplicamos autorrelleno para el resto de celdas.

Pmod=C*Vexp
-k

3. Colocamos el cursor en la esquina inferior derecha hasta que aparezca una cruz de color
negro. Una vez ocurra esto, deben arrastrar con clic sostenido (botón izquierdo del
mouse) hasta el final de los datos:

Las demás formulas quedan copiadas automáticamente.

Nota: Otra forma para el autorrelleno es hacer doble clic cuando aparezca la cruz negra.

- Ingresar las ecuaciones de mínimos cuadrados (ver apuntes): S SE, SST y R
2


a la expresión (y
exp-ymod)
2
la denotaremos Er, la suma de estos errores es SSE
a la expresión (y
exp- y̅exp)
2
la llamaremos Et, la suma de estos errores es SST
Recuerde: P viene a ser la variable dependiente, reemplaza a y en las expresiones anteriores.
Ahora ingresamos la ecuación del cuadrado de los errores (y
exp-ymod)
2
.

Ahora se calcula la suma al final de la columna de Er .
se selecciona la celda en blanco al final de los datos y damos clic en el icono Σ autosuma.

+
Aplicamos
autorrelleno
para el resto de
las celdas.

4. Ingresamos la ecuación (y
exp- y̅exp)
2
.
El termino y̅
exp es el promedio de todos los datos experimentales. Este se calcula utilizando la
función promedio de Excel y debe ir como referencia absoluta (es un valor constante).
Ingresan la ecuación como se ve en la imagen, luego presionan tecla ENTER.
Seleccionamos la
celda
Luego damos clic
en este botón y
presionamos
ENTER
SSE: es la cantidad que se minimiza, en
solver es la función objetivo o
establecer objetivo

5. Aplican autorrelleno para el resto de celdas y luego calculan la suma al final (este valor es SST )




La suma al final, SST

6. Ingresamos R
2



R
2
= (SST-SSE)/SST
Quedando de esta manera



Organizamos de esta manera,
colocamos la etiqueta R
2
y al
frente como lo ven es donde
ingresamos la ecuación de R
2

7. Este valor negativo de R
2
indica que los valores de k y C son incorrectos y se deben encontrar
con Solver.
El valor de R
2
debe estar siempre entre 0 y 1. Así que después de aplicar el proceso en Solver
este valor debe cambiar y estar lo más cercano posible al valor de 1.

PROCESO DE SOLUCIÓ N EN SOLVER.
Solver se encuentra en la pestaña Datos en el extremo derecho.

Como aquí no aparece, hay que seguir los siguientes pasos para que el complemento se instale.
Archivo-Opciones-Complementos

Clic en el botón Ir…

8. Luego habilitamos Solver y Aceptar.



Damos clic en Solver…

9. Al presionar el botón Opciones aparece:






Aquí va la referencia de
celda del objetivo (SSE)
Lo que queremos que suceda con la
función objetivo, en nuestro caso sería
minimizar. Escogemos Mín
Aquí van las referencias de las celdas
que contienen las constantes a
determinar, en nuestro caso k y C. Son
los respectivos valores que tienen que
cambiar hasta minimizar el objetivo.
Existen procesos en ingeniería donde ciertas
constantes pueden tomas valores negativos.
Entonces esta opción debe deshabilitarse ,
porque si no se hace, las variables
cambiantes solo tomará valores positivos o
cero y puede que nunca encontremos la
solución.
Pestaña Todos los métodos: Aquí necesitamos cambiar
especialmente estos dos valores Tiempo e Iteraciones. Los valores a
colocar deben ser lo suficientemente grandes para permitir a solver
encontrar la solución y evitar que el algoritmo de solución interno de
solver se detenga antes de llegar al objetivo. En mi caso personal
siempre escojo 9999 segundos para el tiempo y 9999 para el número
de iteraciones.
Pestaña GRG Nonlinear: aquí escogemos las derivadas Central , esta
converge más rápido a la solución.

10. A cargar los datos:
Función objetivo

Minimizar

11. Cambiando las celdas de variables: colocamos el cursor en este espacio y seleccionamos las
dos variables (k y C) con clic izquierdo sostenido (aparecen las referencias de celdas).


Deshabilitamos esta opción

12. Presionamos el botón Opciones
Tiempo: 9999
Iteraciones: 9999

Pestaña GRG Nonlinear, escogemos derivada Central :

13. Presionar Aceptar y luego Resolver .


Aparece

Le dan Aceptar para conservar la solución de solver.
Ver los valores de k, C y de R2 (este se aproxima a 1)
Con estos datos el modelo quedaría asi: