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?

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 regressão linear 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 regressão linear 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 regressão linear 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 regressão linear 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 regressão linear 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 regressão linear Figura 7[/caption]