Resolviendo sistemas de ecuaciones en Excel

Para ilustrar este procedimiento me van a permitir que use un problema sencillo de los que aparecen en algunos libros de texto de análisis instrumental.

  Se tiene una mezcla de dos especies, A y B, que presentan espectros de absorción parcialmente superpuestos. Calcule la concentración de A y B en la mezcla a partir de los siguientes datos. El espesor de la cubeta de muestra es de 1 cm.

	C (M)	A (254 nm)	A (550 nm)
Patrón de A	1.50 x 10 -4	0.003	0.975
Patrón de B	1.80 x 10 -4	0.589	0.017
Mezcla		0.857	0.909

Para resolver el problema, primero se calculan los coeficientes de absortividad molar de A y B a ambas longitudes de onda usando los datos de los patrones por separado. Ya que para cada longitud de onda se cumple la Ley de Beer, se tendrá:

Patrón de especie A:

Patrón de especie B:

Para la mezcla se tendrá el siguiente sistema de ecuaciones:

Si se hubiese llamado Y a la absorbancia, X1 al producto del paso de luz por la absortividad molar  para el compuesto A  y X2 al producto del paso de luz por la absortividad molar de B, el problema se reduce a una calibración lineal múltiple con dos niveles para X1 y X2 (uno por cada longitud de onda a la que se mide) con unos coeficiente a ajustar que se corresponderán con las concentraciones de A y B en la mezcla. Así el modelo lineal múltiple a resolver será:

Aquí es donde entra la función ESTIMACION.LINEAL() de Excel. Para solucionar el sistema de ecuaciones se escribe en una primera columna (columna B en la imagen) los valores de absorbancia de la mezcla a cada longitud de onda. En una segunda columna (C) se escriben los valores para X1 a cada longitud de onda y en la tercera columna (D) lo mismo para X2.

Siguiendo la disposición de las celdas de la figura anterior se selecciona el rango B8:D8 y se inserta la función =ESTIMACION:LINEAL(). En el formulario de entrada se selecciona el rango B2:B3 para la Y y el rango C2:D3 para los valores de X (que son X1 y X2). El cuadro de constante debe aparecer con el valor cero o falso, puesto que el modelo propuesto no presenta término independiente. Esto es lógico si en las medidas de los espectros se ha hecho el cero con el blanco. El apartado de estadística lo pondremos con el valor lógico verdadero, aunque para este tipo de problemas no nos sirve para mucho, puesto que la solución del sistema es única. 

Para terminar pulsamos a la vez Ctrl + shift + enter y apareceran los valores ce CB en B6 y CA en C6. 

¡Cuidado, que si ponemos el orden de las columnas de datos con  la especie A primero y B después, Excel invierte el orden y devuelve primero el coeficiente (concentración en nuestro caso) de B y luego la de A!

Es decir, si ordenamos las columnas de los valores de X poniendo primero X1 y a su derecha X2, Excel devuelve en la matriz de resultados primero el coeficiente de X2 y luego el de X1.

Lo que hace mi Departamento... seguridad en el laboratorio

Os dejo un vídeo muy didáctico sobre seguridad en el laboratorio químico, dirigido por mi compañero Antonio José Fernandez Espinosa, Profesor Titular del Departamento de Química Analítica de la Universidad de Sevilla

Lo que hace mi Departamento... medicamentos en lodos de depuradora

Miembros del Departamento de Química Analítica han desarrollado un método de análisis de rutina para determinar  principios activos farmacológicos presentes en lodos de depuradora. El método analítico permite determinar simultáneamente 16 principios activos farmacológicos (antiinflamatorios principalmente) en lodos primarios, secundarios, digeridos y compost. Ver noticia.

Estos estudios no me son nuevos, pues se han llevado a cabo en el seno del grupo del Dr. Esteban Alonso (Análisis Químico Industrial y Medioambiental, FQM-344), siendo la base de la tesis doctoral de la Dra. Julia Martín Bueno. Por cierto, ello le ha valido el Premio de Investigación, Desarrollo de Medio Ambiente y Sostenibilidad (PIDMAS) de la Universidad de Alcalá de Henares (UAH), entre otros.

Enhorabuena Julia.

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!