0192-Regressão Linear utilizando o Excel

Utilizaremos o Excel e suas funcionalidades nativas, para determinar os parâmetros de uma regressão linear.
Sendo a regressão linear determinada por uma reta (Y = b + aX), calcularemos:

  • O coeficiente linear da reta (b)
  • O coeficiente angular da reta (a)
  • O coeficiente de determinação (r²)

Para construção do exemplo, utilizaremos o conjunto de dados a seguir:

Dados utilizados

Dados utilizados

É recomendável, observar graficamente se os dados parecem se comportar de acordo com uma reta.

Para tanto, vamos inserir um gráfico de dispersão:

Gráfico de dispersão

Gráfico de dispersão

Podemos dizer, com base na observação deste gráfico, que os dados aparentam se correlacionar linearmente.

A próxima etapa é determinar os parâmetros da reta e o grau de adequação do modelo.

Utilizaremos 3 funções nativas:

INTERCEPÇÃO(Val_Conhecidos_y;Val_Conhecidos_x): Cálculo de b
INCLINAÇÃO(Val_Conhecidos_y;Val_Conhecidos_x): Cálculo de a
RQUAD(Val_Conhecidos_y;Val_Conhecidos_x): Cálculo de r²

Todas as funções apresentam a mesma sintaxe, sendo seus argumentos, respectivamente, os valores de y (variável dependente) e os valores de x (variável independente)

Teremos então as seguintes fórmulas:

=INTERCEPÇÃO($B$2:$B$21;$A$2:$A$21)
=INCLINAÇÃO($B$2:$B$21;$A$2:$A$21)
=RQUAD($B$2:$B$21;$A$2:$A$21)

Vemos, a seguir, os valores calculados juntamente com a equação da reta:

Cálculos dos coeficientes

Cálculos dos coeficientes

Além da possibilidade de cálculo, também é possível determinar a equação e o valor de r² diretamente no gráfico de dispersão.

Clicando-se com o botão direito sobre os dados da série, seleciona-se a opção Adicionar Linha de Tendência:

Adicionar Linha de Tendência

Adicionar Linha de Tendência

Dentre as opções possíveis para a linha de tendência, nos interessa que seja uma reta (Linear) e que figurem no gráfico a equação e o valor de r².

Selecionar parâmetros

Selecionar parâmetros

Como podemos observar a seguir, os valores são os mesmos obtidos através das fórmulas do Excel.

Reta e parâmetros da regressão

Reta e parâmetros da regressão

Além dessas duas formas, é possível realizar a análise de regressão utilizando o suplemento Análise de Dados.

É necessário ativar esse suplemento através das opções do Excel.

Nas versões mais recentes, essa ferramenta está disponível na aba Dados, Seção Análise.

Análise de Dados

Análise de Dados

Há inúmeras análises estatísticas disponíveis, mas nos concentraremos na Regressão:

Analise de Dados: Regressão

Analise de Dados: Regressão

Além dos cálculos básicos é possível realizar a análise de resíduos e adequação à distribuição normal, cálculos importantes para análises estatísticas mais aprofundadas.

Definindo os parâmetros para cálculos

Definindo os parâmetros para cálculos

Automaticamente é gerado um relatório, cujos resultados essenciais para a nossa análise destacamos em amarelo:

Relatório gerado

Relatório gerado

Vemos que é possível obter os resultados de uma análise de regressão de diversas formas no Excel. Pelo menos uma delas deve se adequar às suas necessidades.

No artigo 0115-Podemos confiar nos cálculos estatísticos no Excel? analisamos com mais detalhes os fundamentos teóricos e os cálculos envolvidos.

Os dados utilizados e os resultados gerados estão disponíveis em: Regressão Linear

Anúncios

In Excel We Trust

Marcado com: , , , , , ,
Publicado em Nível: Intermediário
24 comentários em “0192-Regressão Linear utilizando o Excel
  1. Marconi Pierre de Vasconcelos disse:

    Digo, leio diariamente todos os seus artigos. Marconi

  2. Valdir disse:

    Muito bom . Excelente trabalho

  3. Show de bola !!!
    Coisa de maluco mais muito joia !!!
    Realmente a matemática explica tudo !!!

  4. Geovanny Castelo Branco disse:

    Excelente, parabéns!

  5. Janaina disse:

    Super didático!

  6. Thais :) disse:

    AAAAH MUITO OBRIGADA!!! :D <3

  7. ajuda bastante e é importante o passo a passo principalmente em calculadoras;

  8. Salio Bilihassane Biaque disse:

    bem explicado, ajudou-me tanto.

  9. Thiago Silveira disse:

    Muito bom. Não sabia que existia funções prontas para calcular a regressão.
    Uma dúvida: para uma regressão múltipla, as funções inclinação, intercepção e rquad se aplicariam? Se sim, como?

  10. Ailson disse:

    como obter 3 coeficientes?

  11. liuzp1 disse:

    Oi, como faço para obter os erros associados aos valores de a e b, sem a necessidade de um monte de colunas adicionais?

  12. Marcio Jose disse:

    nestas amostras ficaria como?pode me ajudar?
    x,5/5/6/8/8/10
    y,15/10/15/25/25/30

    • Colocando os valores de x no intervalo A2:A8 e os valores de Y no intervalo B2:B8, você poderá calcular da seguinte forma:
      Coeficiente Linear: =INTERCEPÇÃO($B$2:$B$8;$A$2:$A$8)
      Coeficiente Angular: =INCLINAÇÃO($B$2:$B$8;$A$2:$A$8)
      Coeficiente de Determinação: =RQUAD($B$2:$B$8;$A$2:$A$8)

  13. […] Se tiver dúvidas, consulte o artigo (0192-Regressão Linear utilizando o Excel) […]

  14. Juliana Perez disse:

    Muito obrigada, foi de grande valia encontrar sua postagem.

  15. Alex Nascimento disse:

    Olá? Obrigado pela explicação. Agora, depois de obter os resultados, eu preciso montar a equação de regressão. Você pode me dizer como montar a reta a partir desses resultados que o Excell me deu? Qual é o coeficiente linear e qual o ângular?

  16. Bom dia,

    Como faço pra saber o ponto que a reta da linha de tendência vai cruzar o eixo Y? Obrigado

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: