Como fazer uma curva de calibração linear no Excel

O Excel possui recursos integrados que você pode usar para exibir seus dados de calibração e calcular a linha de melhor ajuste. Isso pode ser útil quando você está escrevendo um relatório de laboratório de química ou programando um fator de correção em um equipamento.

Neste artigo, veremos como usar o Excel para criar um gráfico, traçar uma curva de calibração linear, exibir a fórmula da curva de calibração e, em seguida, configurar fórmulas simples com as funções SLOPE e INTERCEPT para usar a equação de calibração no Excel.

O que é uma curva de calibração e como o Excel é útil ao criar uma?

Para realizar uma calibração, você compara as leituras de um dispositivo (como a temperatura que um termômetro exibe) com valores conhecidos chamados padrões (como os pontos de congelamento e ebulição da água). Isso permite criar uma série de pares de dados que você usará para desenvolver uma curva de calibração.

Uma calibração de dois pontos de um termômetro usando os pontos de congelamento e ebulição da água teria dois pares de dados: um de quando o termômetro é colocado em água gelada (32 ° F ou 0 ° C) e um em água fervente (212 ° F ou 100 ° C). Quando você plota esses dois pares de dados como pontos e desenha uma linha entre eles (a curva de calibração), então assumindo que a resposta do termômetro é linear, você pode escolher qualquer ponto na linha que corresponda ao valor que o termômetro exibe, e você poderia encontrar a temperatura “verdadeira” correspondente.

Portanto, a linha está essencialmente preenchendo as informações entre os dois pontos conhecidos para você, de modo que você possa estar razoavelmente certo ao estimar a temperatura real quando o termômetro está lendo 57,2 graus, mas quando você nunca mediu um “padrão” que corresponde a essa leitura.

O Excel possui recursos que permitem traçar os pares de dados graficamente em um gráfico, adicionar uma linha de tendência (curva de calibração) e exibir a equação da curva de calibração no gráfico. Isso é útil para uma exibição visual, mas você também pode calcular a fórmula da linha usando as funções SLOPE e INTERCEPT do Excel. Ao inserir esses valores em fórmulas simples, você será capaz de calcular automaticamente o valor “verdadeiro” com base em qualquer medição.

Vejamos um exemplo

Para este exemplo, desenvolveremos uma curva de calibração de uma série de dez pares de dados, cada um consistindo em um valor X e um valor Y. Os valores X serão nossos “padrões” e podem representar qualquer coisa, desde a concentração de uma solução química que estamos medindo usando um instrumento científico até a variável de entrada de um programa que controla uma máquina de lançamento de mármore.

Os valores Y serão as “respostas” e representariam a leitura que o instrumento forneceu ao medir cada solução química ou a distância medida de quão longe do lançador a bola de gude pousou usando cada valor de entrada.

Depois de representar graficamente a curva de calibração, usaremos as funções SLOPE e INTERCEPT para calcular a fórmula da linha de calibração e determinar a concentração de uma solução química “desconhecida” com base na leitura do instrumento ou decidir qual entrada devemos dar ao programa para que o o mármore cai a uma certa distância do lançador.

Etapa um: crie seu gráfico

Nosso exemplo simples de planilha consiste em duas colunas: valor X e valor Y.

Vamos começar selecionando os dados a serem plotados no gráfico.

Primeiro, selecione as células da coluna ‘X-Value’.

Agora pressione a tecla Ctrl e clique nas células da coluna Y-Value.

Vá para a guia “Inserir”.

Navegue até o menu “Gráficos” e selecione a primeira opção no menu suspenso “Dispersão”.

Um gráfico aparecerá contendo os pontos de dados das duas colunas.

Selecione a série clicando em um dos pontos azuis. Uma vez selecionado, o Excel descreve os pontos que serão delineados.

Clique com o botão direito em um dos pontos e selecione a opção “Adicionar linha de tendência”.

Uma linha reta aparecerá no gráfico.

No lado direito da tela, o menu “Format Trendline” aparecerá. Marque as caixas ao lado de “Exibir equação no gráfico” e “Exibir valor de R ao quadrado no gráfico”. O valor de R ao quadrado é uma estatística que informa o quão próximo a linha se ajusta aos dados. O melhor valor de R ao quadrado é 1,000, o que significa que cada ponto de dados toca a linha. Conforme as diferenças entre os pontos de dados e a linha aumentam, o valor de r quadrado cai, com 0,000 sendo o valor mais baixo possível.

A equação e a estatística R-quadrada da linha de tendência aparecerão no gráfico. Observe que a correlação dos dados é muito boa em nosso exemplo, com um valor de R ao quadrado de 0,988.

A equação está na forma “Y = Mx + B,” onde M é a inclinação e B é a interceptação do eixo y da linha reta.

Agora que a calibração está concluída, vamos trabalhar na personalização do gráfico, editando o título e adicionando títulos de eixo.

Para alterar o título do gráfico, clique nele para selecionar o texto.

Agora digite um novo título que descreva o gráfico.

Para adicionar títulos aos eixos xey, primeiro navegue até Ferramentas de gráfico> Design.

Clique no menu suspenso “Adicionar um elemento de gráfico”.

Agora, navegue até Axis Titles> Primary Horizontal.

Um título de eixo aparecerá.

Para renomear o título do eixo, primeiro selecione o texto e, a seguir, digite um novo título.

Agora, vá para Axis Titles> Primary Vertical.

Um título de eixo aparecerá.

Renomeie este título selecionando o texto e digitando um novo título.

Seu gráfico agora está completo.

Etapa dois: calcular a equação da linha e a estatística R-quadrada

Agora vamos calcular a equação da linha e a estatística R-quadrada usando as funções SLOPE, INTERCEPT e CORREL integradas do Excel.

Em nossa planilha (na linha 14), adicionamos títulos para essas três funções. Realizaremos os cálculos reais nas células abaixo desses títulos.

Primeiro, vamos calcular o SLOPE. Selecione a célula A15.

Navegue para Fórmulas> Mais funções> Estatística> INCLINAÇÃO.

A janela Argumentos da função é exibida. No campo “Sys_conhecidos”, selecione ou digite nas células da coluna Y-Value.

No campo “Known_xs”, selecione ou digite nas células da coluna X-Value. A ordem dos campos ‘Known_ys’ e ‘Known_xs’ é importante na função SLOPE.

Clique OK.” A fórmula final na barra de fórmulas deve ser semelhante a esta:

=SLOPE(C3:C12,B3:B12)

Observe que o valor retornado pela função SLOPE na célula A15 corresponde ao valor exibido no gráfico.

Em seguida, selecione a célula B15 e navegue até Fórmulas> Mais funções> Estatística> INTERCEPÇÃO.

A janela Argumentos da função é exibida. Selecione ou digite nas células da coluna Y-Value para o campo “Known_ys”.

Selecione ou digite nas células da coluna X-Value para o campo “Known_xs”. A ordem dos campos ‘Known_ys’ e ‘Known_xs’ também é importante na função INTERCEPT.

Clique OK.” A fórmula final na barra de fórmulas deve ser semelhante a esta:

=INTERCEPT(C3:C12,B3:B12)

Observe que o valor retornado pela função INTERCEPT corresponde à interceptação y exibida no gráfico.

Em seguida, selecione a célula C15 e navegue até Fórmulas> Mais funções> Estatística> CORREL.

A janela Argumentos da função é exibida. Selecione ou digite um dos dois intervalos de células para o campo “Matriz1”. Ao contrário de SLOPE e INTERCEPT, a ordem não afeta o resultado da função CORREL.

Selecione ou digite o outro dos dois intervalos de células para o campo “Matriz2”.

Clique OK.” A fórmula deve ser semelhante a esta na barra de fórmulas:

=CORREL(B3:B12,C3:C12)

Observe que o valor retornado pela função CORREL não corresponde ao valor “r-quadrado” no gráfico. A função CORREL retorna “R”, portanto, devemos elevá-la ao quadrado para calcular “R ao quadrado”.

Clique dentro da Barra de Funções e adicione “^ 2” ao final da fórmula para elevar ao quadrado o valor retornado pela função CORREL. A fórmula concluída agora deve ter a seguinte aparência:

=CORREL(B3:B12,C3:C12)^2

Pressione Enter.

Depois de alterar a fórmula, o valor “R ao quadrado” agora corresponde ao exibido no gráfico.

Etapa três: configurar fórmulas para calcular valores rapidamente

Agora podemos usar esses valores em fórmulas simples para determinar a concentração daquela solução “desconhecida” ou qual entrada devemos inserir no código para que a bola de gude voe uma certa distância.

Essas etapas irão configurar as fórmulas necessárias para que você possa inserir um valor X ou um valor Y e obter o valor correspondente com base na curva de calibração.

A equação da linha de melhor ajuste está na forma “valor Y = INCLINAÇÃO * valor X + INTERCEPÇÃO”, portanto, resolver o “valor Y” é feito multiplicando o valor X e INCLINAÇÃO e, em seguida, adicionando o INTERCEPT.

Como exemplo, colocamos zero como o valor X. O valor Y retornado deve ser igual ao INTERCEPÇÃO da linha de melhor ajuste. Corresponde, então sabemos que a fórmula está funcionando corretamente.

A resolução para o valor X com base em um valor Y é feita subtraindo o INTERCEPT do valor Y e dividindo o resultado pelo SLOPE:

Valor X = (valor Y-INTERCEPÇÃO) / INCLINAÇÃO

Como exemplo, usamos o INTERCEPT como um valor Y. O valor X retornado deve ser igual a zero, mas o valor retornado é 3.14934E-06. O valor retornado não é zero porque inadvertidamente truncamos o resultado INTERCEPT ao digitar o valor. A fórmula está funcionando corretamente, porém, porque o resultado da fórmula é 0,00000314934, que é essencialmente zero.

Você pode inserir qualquer valor de X que desejar na primeira célula de borda espessa e o Excel calculará o valor de Y correspondente automaticamente.

Inserir qualquer valor Y na segunda célula de borda espessa dará o valor X correspondente. Esta fórmula é o que você usaria para calcular a concentração dessa solução ou qual entrada é necessária para lançar a bola de gude a certa distância.

Nesse caso, o instrumento lê “5”, então a calibração sugere uma concentração de 4,94 ou queremos que a bola de gude percorra cinco unidades de distância, então a calibração sugere inserir 4,94 como a variável de entrada para o programa que controla o lançador de bola de gude. Podemos estar razoavelmente confiantes nesses resultados por causa do alto valor de R ao quadrado neste exemplo.