Como fazer uma Regressão Linear no Excel?

04 de fevereiro de 2018
Última modificação: 21 de julho de 2021

Autor: Virgilio Marques Dos Santos
Categorias: Análise de dados, Blog

Como fazer um Regressão Linear no Excel?

A regressão linear no Excel pode ser usada como uma forma de representar visualmente a relação entre as variáveis ​​independentes (x) e dependentes (y) no gráfico. Uma linha reta retrata uma tendência linear nos dados (ou seja, a equação que descreve a linha é de primeira ordem. Por exemplo, y = 3x + 4. Não há variáveis ​​em quadrado ou em cubo nesta equação). Uma linha curva representa uma tendência descrita por uma equação de ordem superior (por exemplo, y = 2×2 + 5x – 8). É importante que você possa defender o uso de uma linha de regressão direta ou curva. Ou seja, a teoria subjacente ao seu problema deve indicar se a relação das variáveis ​​independente e dependente deve ser linear ou não linear.

Como a estatística pode lhe ajudar?

Além de descrever visualmente a tendência nos dados com uma linha de regressão, você também pode calcular a equação da linha de regressão. Esta equação pode ser vista em uma caixa de diálogo e/ou mostrada no seu gráfico. O quão bem esta equação descreve os dados (o “ajuste”), é expressa como um coeficiente de correlação, R² (R-quadrado). Quanto mais o R² é próximo de 1,00, melhor o ajuste. Isso também pode ser calculado e exibido no gráfico.

Os dados abaixo foram introduzidos no módulo gráfico básico e são de um laboratório de uma indústria de tintas e investiga absorção de luz por diferentes soluções. A Lei de Beer afirma que existe uma relação linear entre a concentração de um composto colorido na solução e a absorção de luz da solução. Esse fato pode ser usado para calcular a concentração de soluções desconhecidas, considerando suas leituras de absorção. Isso é feito ajustando uma linha de regressão linear aos dados coletados.

Você costuma trabalhar com planilhas? Está sempre em contato com o Excel? Se sim, então você vai gostar do curso de Projeto Seis Sigma com Excel da FM2S, uma exclusividade da Assinatura FM2S. Confira a aula aberta de apresentação da planilha deste curso!

Criando um gráfico de dispersão inicial

Antes de criar uma linha de regressão, um gráfico deve ser produzido a partir dos dados. Tradicionalmente, isso seria um gráfico de dispersão.

Regressão Linear no Excel

Figura 1: Regressão Linear no Excel

Criando uma linha de regressão linear no Excel (Linha de Tendência)

Quando a janela do gráfico é destacada, você pode adicionar uma linha de regressão ao gráfico, escolhendo Gráfico> Adicionar linha de tendência …

Aparece uma caixa de diálogo (Figura 2). Selecione o tipo Linear Trend / Regression:

Figura 2 regressão linear

Figura 2

Escolha a aba Opções e selecione Exibir equação no gráfico (Figura 3):

Figura 3 regressão linear

Figura 3

Clique em OK para fechar o diálogo. O gráfico agora exibe a linha de regressão (Figura 4)

Figura 4 regressão linear

Figura 4

Como usar a Equação de Regressão Linear para Calcular Concentrações?

A equação linear mostrada no gráfico representa a relação entre Concentração (x) e Absorvência (y) para o composto em solução. Assim, a linha de regressão pode ser considerada uma estimativa aceitável da relação verdadeira entre concentração e absorvência. Por fim, recebemos as leituras de absorvência para duas soluções de concentração desconhecida.

Usando a equação linear (rotulada A na Figura 5), ​​uma célula da planilha pode ter uma equação associada a isso para fazer o cálculo para nós. Assim nós temos um valor para y (Absorvência) e precisamos resolver para x (Concentração). Abaixo estão as equações algébricas que elaboram este cálculo:

  • y = 2071,9x + 0,111
  • logo, y – 0,0111 = 2071,9x
  • (y – 0.0111) / 2071.9 = x

Agora, temos que converter essa equação final em um código em uma célula de planilha. A equação associada à célula da planilha parecerá o que é rotulado como C na Figura 8. “B12” na equação representa y (a absorvência do desconhecido). A solução para x (Concentração) é então exibida na célula ‘C12’.

  • Destaque uma célula de planilha para manter ‘x’, o resultado da equação final (célula C12, rotulada como B na Figura 5).
  • Clique na área da equação (rotulada como C, figura 5)
  • Digite um sinal de igual e, em seguida, um parêntese
  • Clique na célula que representa ‘y’ em sua equação (célula B12 na Figura 5) para colocar esta etiqueta celular em sua equação
  • Por fim, concluir digitando sua equação

Nota: Se a sua equação for diferente daquele neste exemplo, use sua equação

Duplique sua equação para o outro valor desconhecido.

  • Destaque a célula da equação original (C12 na Figura 5) e a célula abaixo dela (C13)
  • Escolha Editar> Preencher> Para baixo

Observe que se você destacar sua nova equação em C13, a referência à célula B12 também aumentou para a célula B13.

Figura 5 regressão linear

Figura 5

Como utilizar o cálculo do coeficiente R² para estimar o ajuste?

Clique duas vezes na linha de tendência, escolha a aba Opções na caixa de diálogo “Formatar linhas de tendências” e por fim verifique o valor r-quadrado na caixa de gráfico. Seu gráfico agora deve se parecer com a Figura 6. Além disso, observe o valor de R-quadrado no gráfico: quanto mais perto de 1,0, melhor o ajuste da linha de regressão. Ou seja, quanto mais a linha passa por todos os pontos.

Figura 6 regressão linear

Figura 6

Agora vamos ver outro conjunto de dados feitos para este laboratório (Figura 7). Observe que a equação para a linha de regressão é diferente do que estava na Figura 6. Assim, uma equação diferente calcularia uma concentração diferente para as duas incógnitas. Qual linha de regressão representa melhor a relação “verdadeira” entre absorção e concentração?

Veja quão perto a linha de regressão passa pelos pontos da Figura 7. Parece “encaixar”, bem como na Figura 6? Não, até porque o valor R-quadrado confirma isso. É 0.873 na Figura 7, em comparação com 0.995 na Figura 6. Embora devêssemos levar em conta informações como o número de pontos de dados coletados para fazer uma previsão estatística precisa sobre o quão bem a linha de regressão representa a verdadeira relação, nós geralmente podemos dizer que a Figura 6 representa uma melhor representação da relação de absorção e concentração.

Figura 7 regressão linear

Figura 7

Aprofunde-se mais nesse assunto, faça parte da Assinatura FM2S clicando no banner abaixo:

Conheça a assinatura FM2S

Deixe seu comentário

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

dezessete − dois =