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

O Excel tem recursos internos que você pode usar para exibir seus dados de calibração e calcular uma 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, vamos ver como usar o Excel para criar um gráfico, plotar um curva de calibração linear, exiba a fórmula da curva de calibração e, em seguida, configure 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) a 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 a partir 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ê traça esses dois pares de dados como pontos e desenha uma linha entre eles (a curva de calibração), então supondo que a resposta do termômetro seja 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.

Assim, a linha é essencialmente preencher as informações entre os dois pontos conhecidos para você, para que você possa ter certeza 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 tem recursos que permitem plotar graficamente os pares de dados 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ê poderá calcular automaticamente o valor "verdadeiro" com base em qualquer medida.

Vamos ver um exemplo

Para este exemplo, nós desenvolverá uma curva de calibração a partir de uma série de dez pares de dados, cada um consistindo de 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 lançadora de mármore.

Os valores Y serão as “respostas”, e representariam a leitura do instrumento fornecido ao medir cada solução química ou a distância medida de quão longe do lançador o mármore 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. que o mármore fica a uma certa distância do lançador.

Primeiro passo: Crie seu gráfico

Nossa simples planilha de exemplo consiste em duas colunas: valor X e valor Y. / p>

IMAG E_1

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

Primeiro, selecione as células da coluna 'Valor X'.

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

Vá para a guia "Inserir".

Navegue até o menu "Gráficos" e selecione a primeira opção na lista suspensa "Dispersão".

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

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

Clique com o botão direito do mouse 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 o valor de R ao quadrado no gráfico". O valor de R ao quadrado é uma estatística que informa a que distância a linha se encaixa nos dados. O melhor valor de R ao quadrado é 1.000, o que significa que todos os pontos de dados tocam na linha. À medida que as diferenças entre os pontos de dados e a linha crescem, o valor de r-quadrado cai, com 0,000 sendo o menor valor possível.

A equação e a estatística de R-quadrado 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-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á completa, vamos trabalhar na personalização do gráfico editando o título e adicionando os títulos dos eixos. / p>

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 x e y, primeiro, navegue até Ferramentas de gráfico > Design.

Clique no menu suspenso "Adicionar um elemento gráfico".

Agora, navegue até Títulos da Axis > Horizontal Principal.

Um título de eixo aparecerá.

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

Agora, vá para Axis Titles > Vertical Principal.

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 de linha e o quadrado Estatística

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

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

Primeiro, calcularemos o SLOPE. Selecione a célula A15.

Navegue até Fórmulas > Mais funções > Estatística > SLOPE.

A janela Argumentos da Função é exibida. No campo "Known_ys", selecione ou digite as células da coluna Y-Value.

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

Clique em "OK". A fórmula final na barra de fórmulas deve ter esta aparência:

= 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 para Fórmulas > Mais funções > Estatística > INTERCEPT.

A janela Argumentos da Função é exibida. Selecione ou digite as células da coluna Valor Y para o campo “Known_ys”.

Selecione ou digite as células da coluna Valor X para o campo “Known_xs”. A ordem dos campos "Known_ys" e "Known_xs" também é importante na função INTERCEPT.

Clique em "OK". A fórmula final na barra de fórmulas deve ficar assim: :

= INTERCEPTTO (C3: C12, B3: B12)

Observe que o valor retornado pela função INTERCEPTO corresponde ao intercepto y exibido 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 "Array1". 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 "Array2".

Clique em "OK". A fórmula deve ficar assim 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-squared" no gráfico. A função CORREL retorna “R”, então devemos fazer um quadrado para calcular “R-quadrado”.

Clique dentro da barra de funções e adicione “^ 2” ao final. da fórmula para ajustar o valor retornado pela função CORREL. A fórmula completa agora deve ter esta aparência:

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

Pressione Enter.

Após alterar a fórmula, o valor de "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 dessa solução "desconhecida" ou qual entrada devemos inserir no código para que o mármore percorra uma certa distância.

Estes passos irão configurar as fórmulas necessárias para poder introduzir 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 "Y-value = SLOPE * X-value + INTERCEPT", então a resolução para o "Y-value" é feita multiplicando-se o valor-X e SLOPE e, em seguida, adicionando o INTERCEPT.

Como exemplo, colocamos zero como o valor-X. O valor Y retornado deve ser igual ao INTERCEPTO da linha de melhor ajuste. Ele corresponde, por isso sabemos que a fórmula está funcionando corretamente.

Resolver o valor X com base em um valor Y é feito subtraindo o INTERCEPTO do valor Y e dividindo o resultado pelo SLOPE:

 Valor-X = (valor-Y-INTERCEPTO) / SLOPE 

Como exemplo, Nós 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 INTERCEPTO ao digitar o valor. A fórmula está funcionando corretamente, porque o resultado da fórmula é 0,00000314934, que é essencialmente zero.

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

Inserir qualquer valor Y na segunda célula de borda espessa fornecerá o valor X correspondente. valor. 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 a uma certa distância.

Neste caso, o instrumento lê “ 5 ”, então a calibração sugeriria uma concentração de 4,94 ou queremos que o mármore percorra cinco unidades de distância para que a calibração sugira que entramos em 4,94 como a variável de entrada para o programa que controla o lançador de mármore. Podemos estar razoavelmente confiantes nesses resultados por causa do alto valor de R-quadrado neste exemplo.

Via: How to Geek