Excel tiene funciones integradas que puede utilizar para mostrar sus datos de calibración y calcular una línea de mejor ajuste. Esto puede resultar útil cuando se redacta un informe de laboratorio de química o se programa un factor de corrección en un equipo.
En este artículo, veremos cómo usar Excel para crear un gráfico, trazar una curva de calibración lineal, mostrar la fórmula de la curva de calibración y luego configurar fórmulas simples con las funciones PENDIENTE e INTERCEPT para usar la ecuación de calibración en Excel.
Tabla de contenido
¿Qué es una curva de calibración y cómo es útil Excel al crear una?
Para realizar una calibración, compara las lecturas de un dispositivo (como la temperatura que muestra un termómetro) con valores conocidos llamados estándares (como los puntos de congelación y ebullición del agua). Esto le permite crear una serie de pares de datos que luego utilizará para desarrollar una curva de calibración.
Una calibración de dos puntos de un termómetro usando los puntos de congelación y ebullición del agua tendría dos pares de datos: uno de cuando el termómetro se coloca en agua helada (32 ° F o 0 ° C) y otro en agua hirviendo (212 ° F o 100 ° C). Cuando traza esos dos pares de datos como puntos y dibuja una línea entre ellos (la curva de calibración), asumiendo que la respuesta del termómetro es lineal, puede elegir cualquier punto de la línea que corresponda al valor que muestra el termómetro, y usted podría encontrar la temperatura «verdadera» correspondiente.
Por lo tanto, la línea esencialmente está completando la información entre los dos puntos conocidos para que pueda estar razonablemente seguro al estimar la temperatura real cuando el termómetro está leyendo 57.2 grados, pero cuando nunca ha medido un «estándar» que corresponde a esa lectura.
Excel tiene funciones que le permiten trazar los pares de datos gráficamente en un gráfico, agregar una línea de tendencia (curva de calibración) y mostrar la ecuación de la curva de calibración en el gráfico. Esto es útil para una presentación visual, pero también puede calcular la fórmula de la línea usando las funciones PENDIENTE e INTERCEPCIÓN de Excel. Cuando ingrese estos valores en fórmulas simples, podrá calcular automáticamente el valor «verdadero» basado en cualquier medida.
Veamos un ejemplo
Para este ejemplo, desarrollaremos una curva de calibración a partir de una serie de diez pares de datos, cada uno de los cuales consta de un valor X y un valor Y. Los valores X serán nuestros «estándares» y podrían representar cualquier cosa, desde la concentración de una solución química que estamos midiendo con un instrumento científico hasta la variable de entrada de un programa que controla una máquina lanzadora de canicas.
Los valores Y serán las «respuestas» y representarían la lectura que el instrumento proporcionó al medir cada solución química o la distancia medida de qué tan lejos del lanzador cayó la canica usando cada valor de entrada.
Después de representar gráficamente la curva de calibración, usaremos las funciones PENDIENTE e INTERCEPCIÓN para calcular la fórmula de la línea de calibración y determinar la concentración de una solución química «desconocida» en base a la lectura del instrumento o decidir qué entrada debemos dar al programa para que el el mármol aterriza a cierta distancia del lanzador.
Paso uno: crea tu gráfico
Nuestra sencilla hoja de cálculo de ejemplo consta de dos columnas: Valor X e Valor Y.
Comencemos seleccionando los datos para trazar en el gráfico.
Primero, seleccione las celdas de la columna ‘Valor X’.
Ahora presione la tecla Ctrl y luego haga clic en las celdas de la columna Y-Value.
Vaya a la pestaña «Insertar».
Navegue hasta el menú «Gráficos» y seleccione la primera opción en el menú desplegable «Dispersión».
Seleccione la serie haciendo clic en uno de los puntos azules. Una vez seleccionado, Excel describe los puntos que se describirán.
Haga clic con el botón derecho en uno de los puntos y luego seleccione la opción «Agregar línea de tendencia».
Aparecerá una línea recta en el gráfico.
En el lado derecho de la pantalla, aparecerá el menú «Formatear línea de tendencia». Marque las casillas junto a «Mostrar ecuación en el gráfico» y «Mostrar valor R-cuadrado en el gráfico». El valor de R cuadrado es una estadística que le indica qué tan cerca se ajusta la línea a los datos. El mejor valor de R cuadrado es 1.000, lo que significa que cada punto de datos toca la línea. A medida que aumentan las diferencias entre los puntos de datos y la línea, el valor de r cuadrado cae, siendo 0.000 el valor más bajo posible.
La ecuación y la estadística R-cuadrada de la línea de tendencia aparecerán en el gráfico. Tenga en cuenta que la correlación de los datos es muy buena en nuestro ejemplo, con un valor R cuadrado de 0,988.
La ecuación tiene la forma “Y = Mx + B”, donde M es la pendiente y B es la intersección del eje y de la línea recta.
Ahora que la calibración está completa, trabajemos en personalizar el gráfico editando el título y agregando títulos de eje.
Para cambiar el título del gráfico, haga clic en él para seleccionar el texto.
Ahora escriba un nuevo título que describa el gráfico.
Para agregar títulos al eje xy al eje y, primero navegue a Herramientas de gráficos> Diseño.
Ahora, vaya a Títulos de eje> Horizontal principal.
Para cambiar el nombre del título del eje, primero seleccione el texto y luego escriba un título nuevo.
Ahora, dirígete a Títulos de eje> Vertical principal.
Aparecerá un título de eje.
Cambie el nombre de este título seleccionando el texto y escribiendo un título nuevo.
Su gráfico ahora está completo.
Paso dos: Calcule la ecuación lineal y el estadístico R cuadrado
Ahora calculemos la ecuación lineal y el estadístico R-cuadrado usando las funciones integradas PENDIENTE, INTERCEPCIÓN y CORREL.
A nuestra hoja (en la fila 14) hemos agregado títulos para esas tres funciones. Realizaremos los cálculos reales en las celdas debajo de esos títulos.
Primero, calcularemos la PENDIENTE. Seleccione la celda A15.
Vaya a Fórmulas> Más funciones> Estadística> PENDIENTE.
En el campo «Known_xs», seleccione o escriba en las celdas de la columna X-Value. El orden de los campos ‘Known_ys’ y ‘Known_xs’ es importante en la función SLOPE.
Haga clic en Aceptar.» La fórmula final en la barra de fórmulas debería verse así:
= PENDIENTE (C3: C12, B3: B12)
Tenga en cuenta que el valor devuelto por la función PENDIENTE en la celda A15 coincide con el valor que se muestra en el gráfico.
A continuación, seleccione la celda B15 y luego navegue hasta Fórmulas> Más funciones> Estadística> INTERCEPTAR.
Seleccione o escriba en las celdas de la columna X-Value para el campo «Known_xs». El orden de los campos ‘Known_ys’ y ‘Known_xs’ también es importante en la función INTERCEPT.
Haga clic en Aceptar.» La fórmula final en la barra de fórmulas debería verse así:
= INTERCEPTO (C3: C12, B3: B12)
Tenga en cuenta que el valor devuelto por la función INTERCEPT coincide con la intersección con el eje y que se muestra en el gráfico.
A continuación, seleccione la celda C15 y navegue hasta Fórmulas> Más funciones> Estadísticas> CORREL.
Seleccione o escriba el otro de los dos rangos de celdas para el campo «Array2».
Haga clic en Aceptar.» La fórmula debería verse así en la barra de fórmulas:
= CORREL (B3: B12, C3: C12)
Tenga en cuenta que el valor devuelto por la función CORREL no coincide con el valor «r-cuadrado» en el gráfico. La función CORREL devuelve «R», por lo que debemos elevarla al cuadrado para calcular «R-cuadrado».
Haga clic dentro de la barra de funciones y agregue “^ 2” al final de la fórmula para cuadrar el valor devuelto por la función CORREL. La fórmula completa ahora debería verse así:
= CORREL (B3: B12, C3: C12) ^ 2
Presione Entrar.
Después de cambiar la fórmula, el valor «R-cuadrado» ahora coincide con el que se muestra en el gráfico.
Paso tres: Configure fórmulas para calcular valores rápidamente
Ahora podemos usar estos valores en fórmulas simples para determinar la concentración de esa solución “desconocida” o qué entrada debemos ingresar en el código para que la canica vuele una cierta distancia.
Estos pasos configurarán las fórmulas necesarias para que pueda ingresar un valor X o un valor Y y obtener el valor correspondiente en función de la curva de calibración.
La ecuación de la línea de mejor ajuste tiene la forma «valor Y = PENDIENTE * valor X + INTERCEPT», por lo que la resolución del «valor Y» se realiza multiplicando el valor X y PENDIENTE y luego agregando el INTERCEPT.
Como ejemplo, ponemos cero como valor X. El valor Y devuelto debe ser igual a la INTERCEPCIÓN de la línea de mejor ajuste. Coincide, por lo que sabemos que la fórmula funciona correctamente.
La resolución del valor X basado en un valor Y se realiza restando la INTERCEPCIÓN del valor Y y dividiendo el resultado por la PENDIENTE:
X-value=(Y-value-INTERCEPT)/SLOPE
Como ejemplo, usamos INTERCEPT como un valor Y. El valor X devuelto debe ser igual a cero, pero el valor devuelto es 3,14934E-06. El valor devuelto no es cero porque sin darnos cuenta truncamos el resultado INTERCEPT al escribir el valor. Sin embargo, la fórmula funciona correctamente porque el resultado de la fórmula es 0.00000314934, que es esencialmente cero.
Puede ingresar cualquier valor X que desee en la primera celda de borde grueso y Excel calculará el valor Y correspondiente automáticamente.
Ingresar cualquier valor Y en la segunda celda de borde grueso dará el valor X correspondiente. Esta fórmula es lo que usaría para calcular la concentración de esa solución o qué entrada se necesita para lanzar la canica a cierta distancia.
En este caso, el instrumento lee “5” por lo que la calibración sugeriría una concentración de 4.94 o queremos que la canica recorra cinco unidades de distancia por lo que la calibración sugiere que ingresemos 4.94 como la variable de entrada para el programa que controla el lanzador de canicas. Podemos tener una confianza razonable en estos resultados debido al alto valor de R cuadrado en este ejemplo.