Allá por julio de 2015 proponía una entrada sobre la resolución de sistemas de ecuaciones con Excel en la que se usaba como ejemplo la cuantificación en una mezcla de dos sustancias, previa medida de patrones de ambas sustancias por separado. Aquel era un ejemplo simplista, fácil de encontrar en manuales de Química Analítica. A veces la realidad es otra, y para resolver una mezcla de dos o más sustancias no basta con medir los patrones de cada una por separado para obtener unos coeficientes de absortividad molar y resolver así el sistema de ecuaciones. Generalmente se suele medir una serie de patrones, mezcla de los componentes a determinar, registrando la absorbancias a varias longitudes onda. Con estos datos se puede obtener un modelo de regresión lineal múltiple que permita relacionar mediante una función la concentración de cada analito con las absorbancias medidas a las distintas longitudes de onda y cuantificarlos así en una muestra.
El siguiente ejemplo es una simulación hecha en Excel para tres componentes (C1, C2 y C3) en concentraciones molares, midiendo la absorbancia (A1, A2, A3) a tres longitudes de onda. En la simulación se ha empleado un error aleatorio para las señales de un 2% de media.
| C1 | C2 | C3 | A1 | A2 | A3 |
| 0.0075 | 0.0075 | 0.0075 | 1.076 | 1.08 | 0.646 |
| 0.0025 | 0.0075 | 0.0075 | 0.55 | 0.965 | 0.64 |
| 0.0075 | 0.0025 | 0.0075 | 0.981 | 0.615 | 0.591 |
| 0.0025 | 0.0025 | 0.0075 | 0.475 | 0.55 | 0.63 |
| 0.0075 | 0.0075 | 0.0025 | 1.031 | 0.96 | 0.346 |
| 0.0025 | 0.0075 | 0.0025 | 0.54 | 0.91 | 0.325 |
| 0.0075 | 0.0025 | 0.0025 | 0.936 | 0.55 | 0.316 |
| 0.0025 | 0.0025 | 0.0025 | 0.465 | 0.435 | 0.285 |
| 0.005 | 0.005 | 0.005 | 0.736 | 0.775 | 0.465 |
| 0.005 | 0.005 | 0.005 | 0.731 | 0.785 | 0.465 |
Los datos se introducen en la matriz A1:G11, incluyendo encabezados de columna y fila.
 |
| Datos de calibración para el ejemplo propuesto |
Tendremos que construir tres modelos de regresión lineal múltiple, uno por cada analito, para relacionar las absorbancias medidas (variables independientes en nuestro modelo) con las concentraciones (variables dependientes). Para ello empleamos la fórmula matricial =ESTIMACION.LINEAL().
En el caso de C1, seleccionamos la matriz K2:N6 e introducimos la fórmula =ESTIMACION.LINEAL() desde el menú Formulas/ Insertar función. Como valores de Conocido_y introducimos la matriz B2:B11, que se corresponde con los valores de C1. Como Conocido_x se introducen los valores para A1, A2 y A3, es decir, la matriz E2:G11. Se emplea Constante 1 (VERDADERO) si el modelo contempla un término independiente. En principio lo dejaremos así, si se quisiese obviar se introduce 0 (FALSO). En Estadística introducimos 1 para que calcule errores de los coeficientes, coeficiente de determinación, error de residuales y otros parámetros ya explicados en la entrada sobre regresión lineal en Excel, como el valor F de Fisher, los grados de libertad, suma de cuadrados de regresión y suma de cuadrados de residuales. Si se prefiere se puede escribir directamente =ESTIMACION.LINEAL(B2:B11,E2:G11,1,1) con la matriz K2:N6 seleccionada previamente. De cualquiera de las formas, pulsar al mismo tiempo "Ctrl+Shift+Enter".
 |
| Formulario de entrada para la función =ESTIMACION.LINEAL() |
Si todo ha ido bien, la matriz K2:N6 queda rellena con una serie de valores. En la siguiente figura aparecen dichos valores con unos encabezados explicativos. C1 (en la celda J1) se refiere a la especie 1. C_A1, C_A2 y C_A3 se refiere a los coeficientes que relacionan cada absorbancia con C1. Constant se refiere al termino independiente. En la matriz K2:N2 están los valores de los coeficientes y en la matriz K3:N3 sus errores. El coeficiente de determinación aparece en K4 y el error de residuales en L4. El valor de F, grados de libertad, suma de cuadrados de regresión y suma de cuadrados de residuales aparecen en K5, L5, K6 y L6, respectivamente. Además, más abajo, se incluye el valor calculado de t (valor del coeficiente dividido entre su error) y la probabilidad p de que ese coeficiente valga cero. La explicación de esta prueba la podéis encontrar en una entrada anterior. En este ejemplo, t se calcula en valor absoluto.
 |
| Resultados del modelo de regresión lineal para la especie C1 |
De acuerdo a la prueba t, el coeficiente para A3 (para una probabilidad de 0.05) podría ser obviado, y el modelo recalculado con menos parámetros. Para no alargar la entrada, las pruebas eleminando coeficientes no han sido realizadas.
Algo que conviene recordar es el hecho que los coeficientes par A1, A2 y A3 aparecen en la matriz de resultados en orden inverso a como estén ordenandas las columnas en los datos.
Los resultados para C2 y C3 se realizarían con la misma función. En mi hoja de cálculo lo hice en K18:N22 y K32:N36, respectivamente.
 |
| Resultados para C2 y C3 |
De esta forma se tienen las ecuaciones:
C1 = 0.0103*A1- 0.0019*A2 - 0.00035*A3 + 0.0009
C2 = - 0.002*A1 + 0.0119*A2 - 0.0032*A3 +0.0006
C3 = 7.15*10^(-5)*A1 - 0.0013*A2 + 0.0164 *A3 + 0.002
Los coeficientes de correlación son de 0.996, 0.992 y 0.992 para los tres modelos. El ajuste parece ser adecuado.
Para comprobar la calidad del modelo se han simulado las absorbancias para tres muestras con concentraciones nominales conocidas de la misma forma que se hicieron los patrones. Con las ecuaciones anteriores se calcularon las concentraciones experimentales. Se calculan recuperaciones como (Valor calculado/valor nominal *100). Se observa que los valores de recuperación oscilan entre 93% y 112%, debido al error aleatorio que se le introdujo a las señales. Estos resultados podrían mejorarse si se incluyese un mayor número de medidas de absorbancias y un mayor número de patrones. Pero ese no es el objeto de esta entrada.
 |
| Muestras simuladas con sus concentraciones nominales, señales, concentraciones calculadas con el modelo y recuperaciones. |
Hemos decidido calcular el modelo con la función matricial =ESTIMACION.LINEAL(), pero el mismo cálculo se podría haber hecho empleando la herramienta de Regresión del complemento Análisis de datos. Por ejemplo, para el compuesto C1:
 |
| Herramienta Regresión |
 |
| Entrada de datos para el compuesto C1 |
 |
| Resultados par C1 |
La ventaja es que, si se seleccionan los rótulos en el formulario, en la matriz de resultados queda claramente establecido que coeficiente corresponde a cada variable. Además de poder ver el ANOVA y los gráficos de residuales (y la representación de los valores reales y los estimados para los patrones, en caso de seleccionar la Curva de regresión ajustada. Otra ventaja es que se presentan los resultados de significación de cada coeficiente directamente (Probabilidad tras el valor del Estadístico t). En este caso, t no se obtiene en valor absoluto, pero la probabilidad si se obtiene para el valor positivo de t.
 |
| Resultados para C1, detalle de los coeficientes |
En cuanto a los residuales, es otra ventaja el no tener que calcularlos a mano. En este caso se observa una distribución aleatoria de los mismos.
 |
| Detalle de los gráficos de residuales para C1 |
El mismo procedimiento podría llevarse a cabo para las concentraciones de C2 y C3. La herramienta a emplear es elección de quien realiza los cálculos. Aquí no continuaremos con ello, pero el lector puede comprobar los resultados por sí mismo.
Nota: En este ejemplo hemos generado tres modelos (uno para cada sustancia) que relacionan la concentración de la sustancia con absorbancias medidas a varias longitudes de onda (tres en este caso). Un ejemplo parecido a este, con esta misma forma de proceder se puede encontrar en Miller y Miller, 2002. Está forma de relacionar las variables facilita mucho el cálculo posterior en la muestra, al obtener directamente la concentración de cada analito mediante una función. Si se hubiesen relacionado las absorbancias con las concentraciones podríamos haber obtenido los coeficientes de absortividad molar para cada sustancia a cada longitud de onda. En ese caso, al medir cada muestra nos quedaría un sistema con tres ecuaciones (tantas como absorbancias medidas) de las que habría que despejar las concentraciones. Esto complica el cálculo, pues primero habría que solucionar el ajuste lineal múltiple y luego el sistema de ecuaciones. Por eso parece más lógico relacionar directamente la concentración de cada sustancia con las absorbancias medidas. Ver: J. N. Miller, J. C. Miller, Estadística y Quimiometría para Química Analítica, Prentice Hall, Madrid, 2002, pp. 239-242