"Nowhere would anyone grant that science and poetry can be united. They forgot that science arose from poetry, and did not see that when times change the two can meet again on a higher level as friends" -Wolfgang Goethe-


miércoles, 24 de agosto de 2011

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.

59 comentarios:

  1. Excelente Publicación.. muchas gracias!!!!!

    en verdad muchas gracias por darte el tiempo de poner todos estos procedimientos. Excelente explicación

    un gran saludo

    AngelMtzD Estudiante de Medicina (Farmacología)

    IPN México

    ResponderEliminar
    Respuestas
    1. No hay de qué. Realmente esto es algo que explico a mis alumnos. Solo tuve que tener paciencia para incrustar todas las capturas. Gracias a ti por tu comentario.

      Eliminar
  2. Ante todo gracias por este blog tan fantástico; me gustaría que me ayudases acerca de mis dudas de como se calcula la incertidumbre de los resultados de un análisis de ácidos grasos por cromatografia de gases; estoy llevando a cabo un estudio de validacion del método utilizando materiales de referencia certificados. Cómo se calcula la incertidumbre? con la fórmula de la incertidumbre expandida, U, o calculando un intervalo de confianza, o calculando la desviacion estandar es suficiente?
    Gracias por todo y espero su respuesta.
    Un cordial saludo.

    ResponderEliminar
    Respuestas
    1. Gracias a ti. Lo que preguntas es interesante. Si tienes CRMs deberías plantearte un estudio de reproducibilidad intra-laboratorio (o también llamada precisión intermedia). Se trata de un ANOVA del que puedes sacar el valor de la desviación estándar de precisión intermedia y la debida al sesgo (veracidad). Se suman ambas y se multiplica por un factor de cobertura k=2 para el 95% de nivel de confianza. A esto se le puede sumar, en caso de realizar un estudio de robustez,la incertidumbre asociada a la misma.
      Es difícil explicar aquí todo esto, pero te indicaré una referencia donde viene muy bien:
      A. G. González, M. A. Herrador. A practical guide to analytical method validation, including measurement uncertainty and accuracy profiles. Trends in Analytical Chemistry,26, 227-238

      Espero que te sirva, un saludo

      Eliminar
  3. Muchas gracias por su respuesta. Leeré la referencia que muy amablemente me ha indicado y espero resolver mis dudas.
    Un saludo.

    ResponderEliminar
  4. Hola otra vez; me propone un estudio de reproducibilidad intra-lab, pero como realizo el diseño? basta con tres niveles de concentración del CRM y cinco réplicas de cada nivel en tres días diferentes? Y como obtengo la matriz de datos para el ANOVA? tiene algún ejemplo de esto desarrollado en excel?
    Un saludo y muchas gracias por su inestiimable ayuda.

    ResponderEliminar
    Respuestas
    1. Buenas. Tres niveles de concentración me parece bien, cinco replicados también, pero quizá haga falta cinco días, por lo menos. Lo del ANOVA no es tan fácil de resolver. Si consideramos varios niveles en diferentes días deberíamos usar un diseño de ANOVA anidado que, lamentablemente no resuelve EXCEL. Cuando yo lo planteo uso STATISTICA y me consta que SPSS también implementa este cálculo. Otra cosa es como interpretar los resultados y como incluirlos en la fórmula que me va a dar el valor de reproducibilidad.
      En la referencia que propuse en un comentario anterior , se plantea obtener una varianza neta entre grupos que se sumará a la varianza de error puro. Esto mismo lo podrá encontrar en este documento, en la página 27. Claro que, aquí se plantea para un sólo nivel de concentración y entre días. Un ANOVA de un factor si se hace con EXCEL, como verá en el documento.
      El ANOVA anidado se lo debe construir uno mismo en EXCEL. Las fórmulas las puede encontrar en mi tesis doctoral, capítulo 2.
      Como tendrá niveles de concentración anidados en días, la varianza neta entre grupos se debería calcular a partir de la varianza del factor principal (entre días) y la de error puro. Lo demás es igual que al hacerlo con un solo factor.
      Espero no haberle liado mucho. Un saludo

      Eliminar
  5. Me parece fenomenal que existan blogs como estos porque realmente nos ayudan mucho. Actualmente estoy iniciandome en el tema de la validación de un método de cromatografia de gases-fid; he leido publicaciones sobre el tema pero no encuentro ninguna aplicacion practica en la que se explique como aplicar un ANOVA en excel para el estudio de la reproducibilidad intralab y la incertidumbre;dispone usted de algún ejemplo práctico en excel?
    Muchas gracias, un saludo.

    ResponderEliminar
    Respuestas
    1. Gracias por el comentario. Si lee usted la contestación a Ana (el comentario anterior al suyo) verá lo que se considera en el caso de hacer un intralaboratorio a varios niveles de concentración. Si es un solo nivel de concentración es más sencillo, pues el ANOVA se hace en EXCEL con una macros que trae el propio programa. Otro tipo de diseños de dos factores, hay que construirlos a mano.
      Un saludo

      Eliminar
  6. Buenos días! Muchas gracias por su respuesta; por el momento no dispongo de las aplicaciones (statistica y SPSS) que comenta; intentaré profundizar más en el tema pero entiendo que se necesita tiempo para entender la filosofia de un ANOVA;
    Un saludo!

    ResponderEliminar
  7. Buenas! Muy buen post! Sólo una pregunta... Cuando hago la regresión común (con la linea de tendencia, mostrando la ecuación y el R2 en el gráfico, señalando la intersección de la línea por el cero, me da un valor de R2; pero cuando lo hago con la matriz, señalando FALSO donde pide la ordenanada y VERDADERO donde pide los errores, me entrega otro valor de R2. A que se puede deber esto?
    Cabe aclarar que si pido que me calcule la ordenada en la fórmula matricial y no señalo una intersección en la recta de regresión, me entrega el mismo valor de R2.

    ResponderEliminar
    Respuestas
    1. Buenas. Ante todo gracias por su comentario.
      Intentaré responder sin que se enfade nadie. En principio, yo siempre parto de la premisa de que no te puedes fiar de los resultados de Excel en el menú de agregar línea de tendencia. En algún caso me ha dado incluso pendientes erróneas, cuando estas eran del orden de 10^6.
      He comprobado lo que dice. Según parece, en la solución gráfica, el coeficiente de determinación (R^2) lo calcula siempre igual, independientemente de que la recta pase por cero o no. De hecho, los dos R^2 son iguales.
      En la solución matricial (más fiable)los R^2 difieren dependiendo de si hacemos pasar la recta por cero o no. Esto es lo normal.
      Moraleja, para el que quiera entender. No hay que fiarse de la opción agregar línea de tendencia en Excel. Para obtener el gráfico es buena, para calcular, mejor usar funciones.
      Un saludo
      PD: Puede deberse a un error de programación.

      Eliminar
  8. Hola!Excelente post! En relación a la validación de métodos, me gustaría que me explicase como se obtiene la recta de regresión; me explico, tengo una matriz, que no es placebo o blanco, con unos valores de analito determinados; entonces para estudiar la linealidad del método (previamente he realizado el estudio de la linealidad del sistema con patrones)añado diferentes volumenes de patrón a la muestra (por ejemplo: 0, 50, 80, 100 y 120 mL) y después que represento en abscisas? Supongo que en ordenadas tengo que representar la relación de áreas de analito/patrón interno porque estoy usando la calibración con patrón interno ((Ai/Ap.i.)pero en abscisas,que represento? la concentración añadida (ug/mL) de patrón? o la relación de áreas?.
    Muchas gracias anticipadas por su atención.

    ResponderEliminar
    Respuestas
    1. Buenas tardes y gracias por el comentario.
      Creo entender que realiza una adición patrón (añadiendo cantidades crecientes de analito sobre muestras)combinada con un calibrado de patrón interno. Si es ese caso, represente relación de áreas de analito/patrón interno frente a concentración de analito añadida (siempre y cuando la concentración de patrón interno sea constante). Si la pregunta es otra, por favor, vuelva a formularla. Saludos

      Eliminar
  9. Muchas gracias me fue de mucha ayuda tu post!

    ResponderEliminar
    Respuestas
    1. Gracias por tu comentario. Eso pretendo, que sea útil

      Eliminar
  10. Muchas gracias!! muy útil la información!!

    ResponderEliminar
    Respuestas
    1. Gracias a usted por dejarme el comentario, así tengo la impresión de no estar solo. Saludos

      Eliminar
  11. gracias por ese aporte tan bueno. saludos.

    ResponderEliminar
    Respuestas
    1. Gracias a usted por comentar. Así no me siento solo.

      Eliminar
  12. Impresionanre post :O esto sirve tanto para fisicos(como yo) o para quimicos! te lo agradezco con locura!

    ResponderEliminar
    Respuestas
    1. Gracias por comentar. La intención era esa, que fuese útil

      Eliminar
  13. ¿Cómo se puee hacer incertidumbre de una medida? ¿Se puede calcular a partir de los errores de la pendiente y la ordenada en el origen de su recta de regresión? ¿Cómo?

    Gracias.

    ResponderEliminar
    Respuestas
    1. Me imagino que se refiere al error de interpolación de la señal de una muestra. Lea por favor las páginas 4-7 del enlace que le dejo o el capítulo 5 (punto 5.6) del libro "Estadística y Quimiometría para Química Analítica" de Miller & Miller.

      Eliminar
  14. Buen post, muy útil. Tengo una duda, por lo que veo este método se utiliza para correlacionar una serie de puntos x con otra serie de puntos y, de tal manera que se forman las parejas coordenadas (x,y) y se determina si tienen una correlación lineal. En mi caso yo tengo una serie de datos (xcal,ycal) y los quiero comparar contra otros datos (xteo,yteo) de tal manera que los datos cal (calculado) sea lo más parecido posible a los teóricos. ¿Hay alguna manera de saber qué tanto se acercan estos valores entre sí usando la R2? De antemano, Gracias.

    ResponderEliminar
    Respuestas
    1. Yo intentaría comparar la pendiente y la ordenada de ambas series de datos. Para ello te propongo un test t de Student donde (siendo b la pendiente y a la ordenada):
      Para la pendiente t=(b1/b2-1)/Scomb
      Para la ordenanda en el origen t=(a1-a2)/Scomb
      En cada caso Scomb es la combinación de los errores de las pendientes, o de las ordenadas en el caso del segundo test.
      Los grados de libertad para elegir el valor de t crítico los calcularía mediante la expresión de Welch-Satterthwaite .
      Un ejemplo de comparación de pendientes lo tienes en mi post sobre el efecto matriz. Espero que te sirva de algo

      Eliminar
    2. Creo que es muy parecido a lo que necesito, sin embargo mis series de datos no tienen un comportamiento lineal así que no podría trabajar con las pendientes.
      En sí tengo una serie de datos experimentales y trato de ajustarlos con un modelo que no es lineal (cinetica de una reaccion reversible A <-> B + C) y lo que quiero es determinar que tan acertado es el modelo comparando los varlores experimentales con los que arrojaría el modelo.
      Gracias por la respuesta, también muy interesante lo del efecto matriz.

      Eliminar
    3. Si es así, imagino que al menos los valores en uno de los ejes son comunes y está simulando la otra variable, que es lo que quiere comparar con los valores medidos. En ese caso pruebe un test de Student para valores emparejados (Ver Miller & Miller, Estadistica y Quimiometría para Química Analítica, Capítulo 3)
      En este se calculan las diferencias entre los valores "y" (si es que las "x" son las mismas). Se obtiene una media de esas diferencias (que debería ser cero si método y modelo son modelos son iguales)y una desviación estándar. Se calcula un valor de t como la media de las diferencias dividida entre la desviación estándar. Se compara con el valor tabulado de t de Student para n-1 grados de libertad (siendo n el número de pares de datos comparados). Si tcalc<ttab, no hay diferencia significativa (para el nivel de confianza seleccionado).

      espero que le sirva de algo

      Eliminar
  15. Muchas Gracias, en efecto tengo y's comunes en cada par de datos, intentaré este método.

    ResponderEliminar
  16. Gracias por la explicación, realmente muy buena

    ResponderEliminar
  17. y donde esta el analisis de los resultados?

    ResponderEliminar
    Respuestas
    1. Si se refiere a la opción Análisis de Datos hay que activar el complemento, como se dice en esta publicación. Si se refiere a la hoja de resultados, la genera la propia macros.

      Eliminar
  18. gracias es una explicación excelsa,justo lo que estaba buscando

    ResponderEliminar
  19. Realmente fantástico, fácilmente uno puede realizar todo el procedimiento paso a paso y sin equivocarse. Tu post está como decimos vulgarmente en Colombia "anti brutos".
    Mil gracias, un abrazo y sigue adelante!

    ResponderEliminar
  20. Gracias a ti por comentar.La verdad es que lo dejé listo para mis alumnos y por eso está tan "mascadito" como decimos en mi tierra. Un abrazo

    ResponderEliminar
  21. Muchas gracias por la explicación, quedatodo muy claro. Quería consultarte si se pueden hacer otro tipo de regresiones en EXCEL sin necesidad de realizar el método gráfico, por ej una regresión polinómica grado 2?
    Un abrazo, muchas gracias!

    ResponderEliminar
  22. Gracias por tu dedicación. este post me ha ayudado a resolver una cuestión que me estaba volviendo loco.

    ResponderEliminar
    Respuestas
    1. Me alegro de que te haya servido, es una satisfacción personal que me animéis a seguir.

      Eliminar
  23. muchas gracias me sirvioo muchisimoo !saludos!execelente blog

    ResponderEliminar
  24. Buenisimo post!! Me sirvió de mucha ayuda para realizar un ejercicio de química analítica. Muchas gracias por tus aclaraciones y sigue así que ayudaras a mas de uno como a mi. Saludos y buena suerte!!

    ResponderEliminar
  25. ¿Cómo se realiza la combinación de teclas en Mac?, porque a mi con ctrl+shift+enter no me sale :S

    ResponderEliminar
    Respuestas
    1. No estoy familiarizado con Mac, pero mira este enlace a ver si te sirve.

      Eliminar
    2. Por cierto, en el enlace mira el vínculo que dice fórmula de matriz. La traducción no es muy buena, pero puede servir

      Eliminar
  26. Saludos estimado, quisiera saber como podría aplicar este proceso de calculo a los temas de calibración de presión o temperatura??

    Por cierto Muy bueno el Blog

    ResponderEliminar
    Respuestas
    1. ¿Podría concretar la pregunta? Esto se usa con con estimaciones lineales, para otro tipo de ajustes puede usar Solver (de MS Excel)

      Eliminar
  27. Muchas gracias, me ayudaste a resolver un gran problema, no lograba activar el complemento.

    Saludos :)

    ResponderEliminar
  28. Muchísimas gracias por compartir este blog. Cuando hice mi tesis hace un montón de años tuve que hacerme las fórmulas en excel a mano!!!! Si hubiera tenido esto, la cantidad de tiempo que me hubiera ahorrado.

    Ahora trabajo en industria farmacéutica y esto me viene fenomenal, pero me surge una duda, ¿como se puede validar una hoja excel que realiza estos cálculos estadísiticos? Para poder utilizar hojas excel en rutina tenemos que validarlas, es decir, demostrar que calculan correctamente, ¿tienes algún protocolo o ejemplo?

    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Gracias por comentar
      En cuanto a la validación de las hojas de cálculo, tengo entendido que los auditores suelen pedir que se demuestre el buen funcionamiento de la hoja. Al ser procedimientos de cálculo, según me comentaron en un curso sobre acreditación de laboratorios, se debe poder comprobar a mano (y calculadora) cualquier cálculo.
      Yo desde entonces intento hacer siempre un ejemplo a mano para comprobar el buen funcionamiento de la hoja. Salvo que el calculo sea complicado, no debe haber problemas. Otra opción es realizar el cálculo con un paquete estadístico comercial, que es el procedimiento habitual cuando tengo un tipo de cálculo algo más complejo.
      Espero que sirvan mis comentarios


      Eliminar
  29. Muy buen post, el único que he visto que lo explique tan bien y tan claro. ¡¡ GRACIAS Y ENHORABUENA !!

    ResponderEliminar
    Respuestas
    1. Gracias por comentarlo, da sensación de que lo que se hace es útil. Saludos

      Eliminar

Haz tu comentario...