Como fazer uma Regressão Linear no Excel?

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 = 2x2 + 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?

• O que é Regressão Linear? Conheça como economizar milhões;
• Como fazer um Regressão Linear utilizando o Excel?
• A Regressão é Linear ou Não Linear?
• Tutorial: como escolher o tipo correto de análise de regressão?

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.

[caption id="" align="aligncenter" width="632"] Figura 1: Regressão Linear no Excel[/caption]

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:

[caption id="" align="aligncenter" width="415"] Figura 2[/caption]

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

[caption id="" align="aligncenter" width="417"] Figura 3[/caption]

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

[caption id="" align="aligncenter" width="544"] Figura 4[/caption]

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.

[caption id="" align="aligncenter" width="583"] Figura 5[/caption]

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.

[caption id="" align="aligncenter" width="469"] Figura 6[/caption]

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.

[caption id="" align="aligncenter" width="469"] Figura 7[/caption]

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