Cálculo de regresión en Excel 2007

En esta entrada veremos como calcular la pendiente y ordenada en el origen de una serie de datos que se correlacionan linealmente mediante el método de mínimos cuadrados (es imprescindible para la aplicación del calibrado en Química Analítica). Además se obtendrán los errores de estos parámetros y el coeficiente de correlación, entre otros parámetros interesantes.

 Intentaré ser lo más gráfico posible. Una explicación más detallada sobre la regresión en Excel (pero era 2003) lo podéis encontrar en Aplicación de Microsoft Excel a la Química Analítica: validación de métodos analíticos. Aquí, lo haré de una forma más simple, explicando sólo lo que hay que ir haciendo con este software.

1) Solución gráfica (la que todos conocemos)

Se seleccionan las columnas para los valores X e Y (deben estar en ese orden) y vamos al menú Insertar, seleccionando Dispersión.

Obtenemos una gráfica de dispersión. Para dibujar la recta de mejor ajuste se pincha sobre los puntos de la gráfica y se hace click con el botón derecho del ratón. Seleccionamos la opción Agregar linea de tendencia.

Se escogen las opciones: tipo lineal, presentar la ecuación en el gráfico y el valor de R2.

Y quedará así

El problema es que no podemos conocer los errores de la pendiente y ordenada en el origen,  requeridos en Química Analítica para el calculo de la incertidumbre de los resultados.

2) Mediante fórmulas
Nos situamos en una celda. Se emplea el menú Fórmulas/Insertar función. Se seleccionan las fórmulas que aparecen en la imagen de abajo.

Conocido_y se refiere a los valores de Y y Conocido_X a los de X (Señal y Concentración en nuestro caso). Se pueden escribir en la celda las fórmulas directamente, e ir señlecionando las X y las Y. O se puede usar el menú insertar función.

El problema de nuevo es que no se obtienen los errores.

3. Fórmula matricial

La fórmula se introduce de igual modo que las anteriores, pero tiene truco.

Primero seleccionamos 2x5 celdas (una matriz) e insertamos la función ESTIMACION.LINEAL(). Se seleccionan las Y y las X, se introduce un 1 o VERDADERO en las otras dos cajas del formulario.

Importante, no pulsar aceptar

Pulsar en el teclado y a la vez las teclas Control (Ctrl), la flecha de mayúsculas (encima de la de Control) y Entrar (Intro o como la llaméis cada uno)  

La matriz queda rellena de la siguiente forma:

Y aquí si obtenemos los errores de la pendiente y de la ordenada, así como el error típico.

4. Usando la herramienta "Análisis de datos" de Excel

Sí, Excel tiene una macros muy buena para estos menesteres, pero hay que saber interpretarla. Lo primero (para el que no lo tenga) es activar la herramienta. Botón de Office, Opciones de Excel, Complementos. Administrar complementos de Excel, Ir.

Activar herramientas para análisis

Una vez hecho esto (solo si no se instaló la herramienta anteriormente) se puede usar desde Menú/Datos/Análisis de Datos y luego seleccionar Regresión

En el formulario seleccionamos los datos de entrada. Si hemos seleccionado los rótulos marcamos esta opción. Seleccionamos si queremos obtener gráficos de residuales, etc (leed el documento). Yo siempre prefiero los resultados en una hoja nueva.

Y se tienen estos resultados:

Pendiente y ordenada con sus errores.

Los valores de los residuales

Coeficiente de correlación

El ANOVA de regresión

Esta forma es muy completa.

Espero que os sirva

Recomiendo el libro Statistics and Chemometrics for analytical Chemistry (Miller) para el tema de la aplicación del método de los mínimos cuadrados.

Diferenciación de vinos blancos españoles (con D.O.) a partir de su aroma

Este trabajo es antiguo, de 2008, pero quiero recordarlo ahora porque tengo entre manos una segunda parte que ya comentaré cuando lo acepten. Además, ahora también participo con mis amigos en un blog que está muy relacionado con esta entrada, pero que no es de corte científico. El trabajo completo, para suscriptores, se puede encontrar en Analytical and Bioanalytical Chemistry.

Se consideraron cuatro Denominaciones de Origen, Condado de Huelva, Penedés, Ribeiro y Rueda. Los vinos de Condado de Huelva eran monovarietales de Zalema y los de Rueda de Verdejo. Con estos cuatro tipos nos aseguramos que la geografía y el variado clima españoles están representados en los grupos considerados. Al trabajar con compuestos volátiles, se usan descriptores químicos muy relacionados con lo que el consumidor percibe y que, dependen sobre todo de la variedad de uva y su madurez (muy influenciada por el clima).

Los volátiles se determinaron mediante microextracción en fase solida del espacio de cabeza (HS-SPME) de las muestras. Cierta cantidad de vino, con un patrón interno añadido, se introduce en un vial cerrado dejando un poco de aire en la parte superior del mismo (espacio de cabeza). Se calienta la muestra durante un tiempo para que el espacio de cabeza se haga rico en componentes volátiles. Luego se introduce una fibra impregnada en un polímero. Los compuestos se adsorben sobre el polímero. Esa fibra se introduce en el portal de inyección de un cromatógrafo de gases y los compuestos, debido a la temperatura elevada del inyector, se desprenden y son arrastrados por el gas portador al cromatógrafo, donde se separan y cuantifican. Para más información sobre HS-SPME, consultar en la documentación que ofrece SUPELCO, propuesta por uno de los suministradores de fibras. Para empezar, recomiendo este.

Entre los componentes estudiados encontramos alcoholes superiores y ésteres (responsables de ciertos aromas afrutados), principalmente. Se confirmó la presencia de los mismos mediante la comparación de  sus espectros de masas con los de patrones.

Las técnicas de reconocimiento de patrones aplicadas fueron análisis de componentes principales, análisis discriminante lineal y redes neuronales artificiales de perceptrones multicapa. El PCA permite ver ciertas tendencias. Solo se observa una buena separación de los vinos de Rueda. Ribeiro y Penedés se mezclan entre sí, pero todos se separan más o menos claramente de los de Condado de Huelva. Esto se puede explicar debido a los contenidos más altos de acetatos de etilo, isoamilo, hexilo y 2-feniletilo de los vinos Rueda y los menores contenidos en hexanoato, decanoato y laurato de etilo que presentan los del Condado.

El LDA se llevó a cabo añadiendo al modelo las variables más discriminantes de manera sucesiva (ya hablaremos de ello en otra ocasión). Las variables seleccionadas por el modelo debido a su poder discriminante fueron acetato de etilo, hexanoato de etilo, acetato de hexilo, octanoato de etilo, decanoato de etilo, laurato de etilo,  miristato de etilo, palmitato de etilo, acetato de isoamilo, octanoato de isoamilo, 3-metil-butanol y 2-feniletanol. En principio parece que la separación es buena, con un 100% de capacidad de reconocimiento (ver imagen), pero cuando se valida el modelo con el método de leave-one-out se obtienen capacidades de predicción del 81 % para Penedés, 91% para Ribeiro y 95% para Condado de Huelva. Los de Rueda se diferencian bien.

Visto que el modelo lineal no es del todo satisfactorio, se propone un modelo basado en redes neuronales artificiales de perceptrones multicapa. El conjunto de datos se dividen en subconjuntos de entrenamiento (para crear el modelo), verificación (para evitar sobreentrenamiento) y test (para calcular el rendimiento del modelo). Se parte de las variables seleccionadas por el LDA y se usa un algoritmo que seleccionará a las de mayor poder diferenciador. El modelo retiene a las variables acetato de etilo, hexanoato de etilo, acetato de hexilo, 2-feniletanol, decanoato de etilo y miristato de etilo. Se obtuvo un 100% de predicción para todas las clases al aplicar el modelo sobre el subconjunto de test.

Para las MLP-NN recomiendo el libro de Bishop, Neural Networks for Pattern Recognition.

Actualización del blog

Una segunda parte de este trabajo realizada a partir del perfil metálico está disponible también. 

Análisis de componentes principales

El análisis de componentes principales (principal component analysis, PCA) es un método de reconocimiento de patrones que obtiene combinaciones lineales de las variables originales que definen a un conjunto de puntos. Estas nuevas variables, denominadas componentes principales (PCs), se obtienen de forma que el primer PC explica el mayor porcentaje posible de la varianza de los datos originales y cada nuevo PC representará la mayor cantidad posible de la variabilidad residual. De esta forma puede emplearse el PCA para reducir el número de variables necesarias para describir un conjunto de datos (empleando solo los primeros PCs) y poder obtener representaciones gráficas donde se visualice la tendencia de los datos.

Para entender mejor esto, supongamos un conjunto de datos pertenecientes a dos categorías distintas (A y B) que queda definido mediante tres variables.

El PCA obtendrá tres PCs, el primero de ellos explicará la mayor parte de la varianza del conjunto de datos. Es decir, si las tres variable originales presentan diferencias para las dos categorías, la primera combinación lineal obtenida presentará también dichas diferencias. En este caso ficticio el primer PC explica un 98.3% de la variabilidad original y se puede observar la separación natural de las dos categorías.

Además, el PCA permite saber que variables contribuyen más a dicha separación natural, las más correlacionadas con los primeros PCs. En este caso, las tres variables están muy correlacionadas con PC1.

En el área de Química Ánalítica se emplea el PCA para reducir el número de variables que definen a un conjunto de muestras, por ejemplo concentración en determinadas sustancias, que reciben el nombre de descriptores químicos. Téngase en cuenta que podemos partir de más de tres variables (incluso cientos de ellas), con lo que puede resultar muy difícil observar tendencias en los datos originales. Esto se facilita al reducir las variables a los dos o tres primeros PCs. Un ejemplo de su empleo puede encontrarse en una entrada antigua del blog . 

Para una información más técnica sobre este método, se puede consultar el texto Principal Component Analysis (Jolliffe). También se puede consultar mi tesis doctoral (Capítulo 2).

Actualización del blog

Recomiendo entrada sobre uso del PCA en estudios medioambientales

Un día sin Química

¿Qué sería de nosotros en un mundo sin Química? Creo que este vídeo de la American Chemical Society es muy esclarecedor.

Libros on-line

Esta entrada puede que sobre para la mayoría, pues casi todos sabemos de la existencia de Google Books. Yo me he hecho mi biblioteca (hace ya tiempo, y puse un enlace en el blog). Es una forma cómoda de acceder a muchos libros y de forma gratuita (el problema es que no están completos, les faltan siempre algunas hojas, pero entre unos y otros se puede consultar casi de todo). Yo recomiendo algunas joyas de Rose, Fresenius o Lavoisier (descargables en pdf, para aquellos que lo descargan todo).

Está claro que esta herramienta puede hacer mucho por la divulgación del conocimiento en general, y de la ciencia en particular. Si habeis visto alguna otra entrada del blog, suelo usar esta herramienta para la bibliografía que recomiendo.

Un saludo