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:
