¿Qué es una curva de calibración y qué utilidad tiene Excel al crear una?
Para realizar una calibración, se comparan 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 usará 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°Para 0°C) y uno en agua hirviendo (212°Por 100°C). Cuando trazas esos dos pares de datos como puntos y dibujas una línea entre ellos (la curva de calibración), suponiendo que la respuesta del termómetro es lineal, puedes elegir cualquier punto en la línea que corresponda al valor que muestra el termómetro, y podría encontrar la temperatura «verdadera» correspondiente.
Entonces, la línea esencialmente completa la información entre los dos puntos conocidos para que pueda estar razonablemente seguro al estimar la temperatura real cuando el termómetro marca 57.2 grados, pero cuando nunca has medido un «estándar» que corresponda 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 visualización, pero también puede calcular la fórmula de la línea usando las funciones PENDIENTE e INTERCEPCIÓN de Excel. Cuando ingresa 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 constará 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 usando 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 proporcionó el instrumento 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» según la lectura del instrumento o decidir qué entrada debemos darle al programa para que la La canica aterriza a cierta distancia del lanzador.
Paso uno: cree su gráfico
Nuestra sencilla hoja de cálculo de ejemplo consta de dos columnas: Valor X y 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 Valor Y.
Vaya a la pestaña «Insertar».
Navegue hasta el menú «Gráficos» y seleccione la primera opción en el menú desplegable «Dispersión».
Aparecerá un gráfico que contiene los puntos de datos de las dos columnas.
Selecciona la serie haciendo clic en uno de los puntos azules. Una vez seleccionado, Excel delinea los puntos que serán delineados.
Haga clic 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 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 disminuye, con 0siendo .000 el valor más bajo posible.
La ecuación y la estadística R cuadrado de la línea de tendencia aparecerán en el gráfico. Note 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 la personalización del 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 a los ejes x e y, primero navegue hasta Herramientas de gráficos > Diseño.
Haga clic en el menú desplegable «Agregar un elemento de gráfico».
Ahora, navega hasta Títulos de ejes > Horizontal primario.
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 ejes > Vertical principal.
Aparecerá un título de eje.
Cambie el nombre de este título seleccionando el texto y escribiendo un nuevo título.
Su gráfico ya está completo.
Paso dos: calcular la ecuación lineal y la estadística R cuadrada
Ahora calculemos la ecuación lineal y el estadístico R cuadrado usando las funciones PENDIENTE, INTERCEPCIÓN y CORREL integradas de Excel.
A nuestra hoja (en 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.
Navegue a Fórmulas > Más funciones > Estadística > PENDIENTE.
Aparece la ventana Argumentos de función. En el campo «Known_ys», seleccione o escriba las celdas de la columna Valor Y.
En el campo «Known_xs», seleccione o escriba las celdas de la columna Valor X. El orden de los campos ‘Known_ys’ y ‘Known_xs’ es importante en la función PENDIENTE.
Haga clic en Aceptar.» La fórmula final en la barra de fórmulas debería verse así:
=SLOPE(C3:C12,B3:B12)
Note que el valor devuelto por la función PENDIENTE en la celda A15 coincide con el valor mostrado en el gráfico.
Luego, seleccione la celda B15 y luego navegue hasta Fórmulas > Más funciones > Estadística > INTERCEPTAR.
Aparece la ventana Argumentos de función. Seleccione o escriba las celdas de la columna Valor Y para el campo «Known_ys».
Seleccione o escriba las celdas de la columna Valor X para el campo «Known_xs». El orden de los campos ‘Known_ys’ y ‘Known_xs’ también importa 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)
Note que el valor devuelto por la función INTERCEPT coincida con la intersección y que se muestra en el gráfico.
Luego, seleccione la celda C15 y navegue hasta Fórmulas > Más funciones > Estadística > CORREL.
Aparece la ventana 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)
Note que el valor devuelto por la función CORREL no coincide con el valor «r cuadrado» del 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 elevar al cuadrado 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: configurar 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 «valor Y = PENDIENTE * valor X + INTERCEPCIÓN», por lo que resolver el «valor Y» se realiza multiplicando el valor X y la PENDIENTE y luego agregando la INTERCEPCIÓN.
Como ejemplo, ponemos cero como valor de 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.
Para resolver el valor de X basándose en un valor de Y se resta la INTERCEPCIÓN del valor de Y y se divide el resultado por la PENDIENTE:
X-value=(Y-value-INTERCEPT)/SLOPE
Como ejemplo, utilizamos INTERCEPT como valor de 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 de 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 Y en la segunda celda de borde grueso se obtendrá el valor X correspondiente. Esta fórmula es la que usarías para calcular la concentración de esa solución o qué insumo se necesita para lanzar la canica a cierta distancia.
En este caso, el instrumento dice «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 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.
