Empleando SOLVER para cálculos de regresión. Funciones exponenciales.

Ya he hablado sobre ajustes lineales y polinómicos empleando la función "=ESTIMACION.LINEAL()" de Excel. Me preguntaba una lectora del blog ¿como hace Excel para obtener la ecuación de la curva de mejor ajuste, cuando agrega la línea de tendencia sobre los datos en una gráfica, en el caso de tener una función exponencial? La respuesta es simple, aplicando el método de mínimos cuadrados, es decir, calculando los parámetros de la función de manera que minimicen la suma de cuadrados de los residuales. ¿Y se puede obtener esos parámetros en una hoja Excel? Si, incluso cuando el método gráfico de Excel  no deja ajustar una exponencial, lo que ocurre si los datos tienen tendencia negativa. A veces gana el procedimiento gráfico, con un ajuste mejor, pero otras (la mayoría), el ajuste en la hoja de cálculo es más eficaz, es decir, lleva a un mayor coeficiente de determinación.

¿Cómo se hace? Es muy simple. Pondré un ejemplo que me facilite a explicación. 

Caso 1. Ajuste a una exponencial: Y=a*EXP(b*X) con "a>0"

Imaginemos que tenemos una serie de valores X e Y entre los que pensamos que existe una relación del tipo Y=a*EXP(b*X), donde EXP() se refiere al numero "e" elevado a la expresión que viene entre paréntesis y "a" y "b" son parámetros a determinar. Supongamos los siguientes datos:

En las celdas B14 y B15 introducimos valores iniciales de a y b, por ejemplo 1 y 1, respectivamente.
En la celda C2 escribimos =B$14*EXP(B$15*A2), esto se corresponde para el valor de Y estimado cuando se aplica la función de ajuste con los valores de a y b de las celdas B14 y B15. El símbolo $ se coloca delante de los números 14 y 15 para poder arrastrar esta celda desde C2 a C6, de manera que la misma fórmula se escriba en  cada celda de la columna pero variando el valor de X utilizado en cada fila.
En la celda D2 se escribe =B2-C2, es decir el residual (valor verdadero menos valor estimado) y se arrastra hasta D6.
Si todo va bien debe quedar:

Ahora en B9 escribimos =SUMA.CUADRADOS(D2:D6) y llamamos la herramienta SOLVER en Datos/Análisis/Solver. Si no estuviese activada se activa en Botón de Office/Opciones de Excel/ Complementos

Aquí se elige como celda objetivo la B9, donde estaba la suma de cuadrados de residuales, se elige  que su valor sea mínimo cambiando las celdas B14 y B15 (a y b). Se pulsa resolver y SOLVER realiza un cálculo iterativo de a y b para minimizar la suma de residuales.

Se elige utilizar la solución de Solver y debe quedar así:

Aquí ademas he añadido el ajuste de linea de tendencia de Excel (línea de ajuste negra) y nuestro ajuste (linea roja), la varianza de residuales en B10 (es la suma de cuadrados dividido ente grados de libertad, n-2) y la varianza de los valores reales de Y en B11. Con estos valores se calcula el coeficiente de determinación en B17 como R^2=1-(Varianza de regresión/Varianza de Y), es decir =1-B10/B11. 

Como puede verse, el cálculo sobre el gráfico de Excel no da buen resultado, porque da un valor de b de 0.999 cuando realmente es 0.9999

Caso 2. Ajuste a función del tipo Y=a*(1-EXP(b*X))

Colocando los datos en A2:B6, se hace igual que antes pero en C2 se escribe =B$14*(1-EXP(B$15*A2)).
Quedaría como sigue:

Como se ve, aquí el ajuste gráfico de Excel no es una opción adecuada.

Caso 3. Ajuste a la función tipo Y=a*EXP(b*X), con a negativo

En este caso Excel no deja agregar línea de tendencia. Se resuelve como en el caso 1, pero los valores de partida deben ser a y b deben ser, por ejemplo -1 y 1.

En resumidas cuentas:
1. La opción agregar línea de tendencia puede dar valores truncados no correctos.
2. Para funciones complejas es mejor usar SOLVER, pero debe cuidarse los valores de partida de lo parámetros.
3. Las exponenciales de tendencia negativa solo las soluciona SOLVER, no la herramienta gráfica

Espero os sirva

Analytical Chemistry 2.0 An Electronic Textbook for Introductory Courses in Analytical Chemistry

 El libro que David Harvey publicaba en McGraw-Hill en 1999 bajo el título Modern Analytical Chemistry es para mí uno de los más claros y mejor escritos para la materia Química Analítica. Mirad que alegría me he llevado al ver que el autor lo ofrece ahora totalmente gratis, revisado y ampliado bajo el nuevo nombre de Analytical Chemistry 2.0. Sin duda todo un regalo a la comunidad científica. ¡Gracias profesor Harvey!

Revista Ciencia - Universidad del Zulia

He recibido comunicación del equipo editorial de la revista Ciencia, revista electrónica que publica la Facultad Experimental de Ciencias de la Universidad del Zulia, indicando un nuevo enlace a sus contenidos. 

Recordar que se trata de una revista que publica trabajos en español dentro de las áreas científicas de Biología, Computación, Matemáticas, Física y Química. Además se oferta en abierto, con lo que sus contenidos son accesibles al que lo desee.