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.
Excelente Publicación.. muchas gracias!!!!!
ResponderEliminaren 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
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.
EliminarAnte 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?
ResponderEliminarGracias por todo y espero su respuesta.
Un cordial saludo.
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.
EliminarEs 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
Muchas gracias por su respuesta. Leeré la referencia que muy amablemente me ha indicado y espero resolver mis dudas.
ResponderEliminarUn saludo.
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?
ResponderEliminarUn saludo y muchas gracias por su inestiimable ayuda.
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.
EliminarEn 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
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?
ResponderEliminarMuchas gracias, un saludo.
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.
EliminarUn saludo
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;
ResponderEliminarUn saludo!
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?
ResponderEliminarCabe 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.
Buenas. Ante todo gracias por su comentario.
EliminarIntentaré 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.
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?.
ResponderEliminarMuchas gracias anticipadas por su atención.
Buenas tardes y gracias por el comentario.
EliminarCreo 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
Muchas gracias me fue de mucha ayuda tu post!
ResponderEliminarGracias por tu comentario. Eso pretendo, que sea útil
EliminarMuchas gracias!! muy útil la información!!
ResponderEliminarGracias a usted por dejarme el comentario, así tengo la impresión de no estar solo. Saludos
Eliminargracias por ese aporte tan bueno. saludos.
ResponderEliminarGracias a usted por comentar. Así no me siento solo.
EliminarImpresionanre post :O esto sirve tanto para fisicos(como yo) o para quimicos! te lo agradezco con locura!
ResponderEliminarGracias por comentar. La intención era esa, que fuese útil
Eliminar¿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?
ResponderEliminarGracias.
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.
EliminarBuen 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.
ResponderEliminarYo 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):
EliminarPara 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
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.
EliminarEn 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.
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)
EliminarEn 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
Muchas Gracias, en efecto tengo y's comunes en cada par de datos, intentaré este método.
ResponderEliminarGracias por la explicación, realmente muy buena
ResponderEliminarGracias a ti por el comentario
Eliminary donde esta el analisis de los resultados?
ResponderEliminarSi 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.
Eliminargracias es una explicación excelsa,justo lo que estaba buscando
ResponderEliminarGracias por el comentario
EliminarRealmente 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".
ResponderEliminarMil gracias, un abrazo y sigue adelante!
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
ResponderEliminarMuchas 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?
ResponderEliminarUn abrazo, muchas gracias!
Se puede y en breve tendré una entrada al respecto. Saludos
EliminarYa está hecho en esta nueva entrada del blog
EliminarGracias por tu dedicación. este post me ha ayudado a resolver una cuestión que me estaba volviendo loco.
ResponderEliminarMe alegro de que te haya servido, es una satisfacción personal que me animéis a seguir.
Eliminarmuchas gracias me sirvioo muchisimoo !saludos!execelente blog
ResponderEliminarGracias a usted por comentar
EliminarBuenisimo 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!!
ResponderEliminarGracias por comentar. Feliz año
Eliminar¿Cómo se realiza la combinación de teclas en Mac?, porque a mi con ctrl+shift+enter no me sale :S
ResponderEliminarNo estoy familiarizado con Mac, pero mira este enlace a ver si te sirve.
EliminarPor cierto, en el enlace mira el vínculo que dice fórmula de matriz. La traducción no es muy buena, pero puede servir
EliminarSaludos estimado, quisiera saber como podría aplicar este proceso de calculo a los temas de calibración de presión o temperatura??
ResponderEliminarPor cierto Muy bueno el Blog
¿Podría concretar la pregunta? Esto se usa con con estimaciones lineales, para otro tipo de ajustes puede usar Solver (de MS Excel)
EliminarEn Mac = fn+cmd+intro
ResponderEliminarGracias por la información
EliminarMuchas gracias, me ayudaste a resolver un gran problema, no lograba activar el complemento.
ResponderEliminarSaludos :)
Gracias a ti por comentar. Un saludo
EliminarMuchí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.
ResponderEliminarAhora 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
Gracias por comentar
EliminarEn 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
Muy buen post, el único que he visto que lo explique tan bien y tan claro. ¡¡ GRACIAS Y ENHORABUENA !!
ResponderEliminarGracias por comentarlo, da sensación de que lo que se hace es útil. Saludos
EliminarHola, buenas.. he estado tratando de obtener mediante calculos la funcion exponencial de mi tabla de valores, la ecuacion de la linea de tendencia que obtengo de excel es exacta pero no se como llega a ese resultado.. me podrías ayudar con ello?
ResponderEliminarGracias por comentar. Toda ecuación obtenida a partir de la función "agregar linea de tendencia" se obtiene mediante la aplicación del método de mínimos cuadrados, es decir, calculando los parámetros que minimicen la suma de cuadrados de residuales. El problema de Excel es que a veces la solución de la gráfica está peor conseguida que la que se obtiene usando la herramienta SOLVER, que es la que implementa Excel para realizar cálculos iterativos. Quiero poner un ejemplo de esto en una entrada nueva del blog y aviasaré aquí, como de costumbre.
EliminarUn saludo
Gracias.. ya lo resolví con ese método..
EliminarFelices fiestas!
Hola,
ResponderEliminar¿como se calcula el punto de cruce de dos ecuaciones conocidas utilizando solver?
Gracias
Si las ecuaciones son de una recta, resolviendo el sistema de ecuaciones, mira la publicación Resolviendo sistemas de ecuaciones en Excel. Para otro tipo de ecuaciones habría que estudiarlo.
EliminarMil gracias
ResponderEliminarGracias por comentar
Eliminartras todo este tiempo sigue siendo algo muy pero muy util, muchas gracias :)
ResponderEliminarGracias a usted por el comentario. Saludos
EliminarBuen día, quería consultarle como se calcula el Fcritico en el análisis de varianza? Muchas por brindarnos este espacio para despejar las dudas
ResponderEliminarBuenas tardes. Si lo que quiere es el valor tabulado use la función =DISTR.F.INV(x,gl1,gl2), para probabilidad x, y grados de libertad gl1 y gl2. Si lo que quiere es saber como hacer el cálculo de ANOVA de un factor le recomiendo que lea Aplicación de Microsoft Excel a la Química Analítica: validación de
Eliminarmétodos analíticos . Un saludo y gracias por comentar
Hola, muchas gracias por la información !
ResponderEliminarLe consulto, cuando se evalúa una curva de calibración, cómo se interpretan los resultados de la ESTIMACION LINEAL ? Es decir, que significan los valores de residuales, F, los diferentes errores...? y si tienen algún límite de aceptación para la NO aceptación de dicha curva de calibración....
Muchas gracias !
Muchas gracias por el comentario. En el documento Aplicación de Microsoft Excel a la Química Analítica: validación de
Eliminarmétodos analíticos puede encontrar explicación de los parámetros. Hay que leer el apartado "3.5 Regresión" completo para entender qué es cada cosa.
La F calculada es el cociente entre la varianza de regresión (suma de cuadrados de regresión partido de un grado de libertad) y la varianza de residuales (suma de cuadrados de residuales partido de n-2), esta última también llamado varianza de residuales o error típico. Ese valor de F debe ser menor para un F tabulado para una significación de α=0.05 y 1, n-2 grados de libertad. Ese valor crítico de F se puede obtener con la función =DISTR.F.INV(). Espero que le sirva.
Muchas gracias por su respuesta y su tiempo !
EliminarMil gracias por la información, es muy útil.
ResponderEliminarGracias por comentar. Saludos
Eliminar