Cómo hacer una curva de calibración lineal en Excel

Excel tiene funciones integradas que puede utilizar para mostrar sus datos de calibración y calcular una línea de mejor ajuste. Esto puede ser útil cuando está escribiendo un informe de laboratorio de química o programando 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 SLOPE e INTERCEPT para usar la ecuación de calibración en Excel.

¿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 uno 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 en 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 características 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» en función de cualquier medición.

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án la lectura que el instrumento proporcionó al medir cada solución química o la distancia medida de qué tan lejos del lanzador aterrizó 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».

dispersión» width=»314″ height=»250″ onload=»pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);» onerror=»this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);»>

Aparecerá un gráfico que contiene los puntos de datos de las dos columnas.

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 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 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 de 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.

diseño» width=»650″ height=»225″ onload=»pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);» onerror=»this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);»>

Haga clic en el menú desplegable «Agregar un elemento de gráfico».

Ahora, vaya a Títulos de eje> Horizontal principal.

horizontal primaria» width=»650″ height=»500″ onload=»pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);» onerror=»this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);»>

Aparecerá un título de eje.

Para cambiar el nombre del título del eje, primero seleccione el texto y luego escriba un nuevo título.

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 la estadística R-cuadrada usando las funciones PENDIENTE, INTERCEPCIÓN y CORREL incorporadas de Excel.

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ísticas> PENDIENTE.

Más funciones> Estadísticas> PENDIENTE» width=»650″ height=»435″ onload=»pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);» onerror=»this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);»>

Aparecerá la ventana de argumentos de función. En el campo «Known_ys», seleccione o escriba en las celdas de la columna Y-Value.

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’ importa en la función SLOPE.

Haga clic en Aceptar.» La fórmula final en la barra de fórmulas debería verse así:

=SLOPE(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 a Fórmulas> Más funciones> Estadística> INTERCEPTAR.

Más funciones> Estadística> INTERCEPTAR» width=»650″ height=»435″ onload=»pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);» onerror=»this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);»>

Aparecerá la ventana de argumentos de función. Seleccione o escriba en las celdas de la columna Y-Value para el campo «Known_ys».

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í:

=INTERCEPT(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ística> CORREL.

Más funciones> Estadística> CORREL» width=»650″ height=»435″ onload=»pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);» onerror=»this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);»>

Aparecerá la ventana de argumentos de función. Seleccione o escriba cualquiera de los dos rangos de celdas para el campo «Array1». A diferencia de SLOPE e INTERCEPT, el orden no afecta el resultado de la función 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 de «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 según la curva de calibración.

La ecuación de la línea de mejor ajuste tiene la forma «Y-value = SLOPE * X-value + INTERCEPT», por lo que la resolución del «Y-value» se realiza multiplicando el valor X y SLOPE 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.

Al ingresar cualquier valor de Y en la segunda celda de borde grueso, se obtendrá el valor de 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.