Regressão Linear no Excel

Observe a tabela abaixo abaixo. Ela representa a valorização de uma ação com o passar dos anos:

Vamos plotar a tabela num gráfico de dispersão. Selecione o intervalo A1:B11 e clique no menu Inserir, grupo Gráficos, botão Dispersão, botão Dispersão Somente com Marcadores:

Adicionando um título ao gráfico (selecione o gráfico, clique no menu contextual Ferramentas de Gráfico, menu Layout, grupo Rótulos, botão Título do Gráfico, opção Acima do Gráfico. Em seguida, clique no título para editá-lo), obteremos:

Observe que existe uma tendência de crescimento linear (reta) em torno da valorização dessa ação. O Excel possui uma ferramenta nativa para se mostrar essa linha de tendência. Para visualizá-la, basta selecionar o gráfico, clicar no menu contextual Ferramentas de Gráfico, menu Layout, grupo Análise, botão Linha de Tendência, opção Linha de Tendência Linear:

O gráfico ficará como se mostra abaixo:

Obter Constantes da Regressão Linear

Como esse gráfico é linear, ele é da forma:

y = a.x + b

Como obter as constantes a e b?

Por uma função pronta do Excel, basta usarmos a forma na tabela abaixo:

Para a:

=INCLINAÇÃO(B2:B11;A2:A11)

Para b:

=INTERCEPÇÃO(B2:B11;A2:A11)

Alternativamente, você pode construir essa regressão linear por fórmulas do Excel, de acordo com as definições das constantes da regressão linear, que são:

a =

b =

Por fórmulas mais básicas, temos para a:

=(CONT.VALORES(A2:A11) * SOMA(A2:A11*B2:B11) – SOMA(A2:A11) * SOMA(B2:B11)) / (CONT.VALORES(A2:A11) * SOMA(A2:A11^2) – SOMA(A2:A11)^2) (fórmula matricial)

Para b:

=(SOMA(A2:A11^2) * SOMA(B2:B11) – SOMA(A2:A11*B2:B11) * SOMA(A2:A11)) / (CONT.VALORES(A2:A11) * SOMA(A2:A11^2) – SOMA(A2:A11)^2) (fórmula matricial)

Note que ambas as fórmulas são matriciais, ou seja, para entrá-las, o usuário deve pressionar Ctrl+Shift+Enter na célula. Para saber mais sobre fórmulas matriciais, clique aqui.

Os resultados de a e b tanto pela fórmula nativa do Excel ou por fórmulas da regressão linear são iguais.

Verificar Constantes da Regressão Linear em Gráfico

Vamos verificar se os valores de a e b coincidem com uma reta que coincide com a regrassão linear nativa do Excel.

Se criarmos uma nova série de valores chamada y (Tendência) como mostrado abaixo:

A fórmula em C2 é:

=A2*$G$6+$G$7

Basta copiá-la e colá-la até C11.

Clique com o botão direito do mouse no gráfico e em seguida no botão Selecionar Dados…:

Na janela que aparecer, clique em Adicionar:

Na janela que aparecer, entre com os valores:

Nome da série=Blog!$C$1

Valores de X da série=Blog!$A$2:$A$11

Valores de Y da série=Blog!$C$2:$C$11

Em seguida, clique em OK.

Você retornará à janela Selecionar Fonte de Dados. Clique em OK para sair.

O resultado final se encontra abaixo:

Observe que a tendência criada manualmente (seja pela fórmula nativa do Excel ou pelas fórmulas matriciais) coincidem com a tendência de gráfico nativa do Excel.

Para baixar um exemplo pronto, clique aqui: https://skydrive.live.com/redir?resid=FB206A2D510E0661!771&authkey=!AN-_djbzX6GFmqI

Esse post foi publicado em Excel, Fórmulas, Gráficos e marcado , , , . Guardar link permanente.

17 respostas para Regressão Linear no Excel

  1. Antonio Márcio disse:

    muito bom o tutorial parabens

  2. Paulo disse:

    Muito bom mesmo.

  3. Ronaldo Schwartzkopf disse:

    Muito bom o tutorial, parabéns!

  4. Leoanrdo disse:

    Não acredito que eu estava fazendo tudo isso na unha!
    Obrigado, cara!

  5. Dred disse:

    meu puto!!! es pro, brigadao!!!!!!!

  6. Joanna disse:

    Fantastico !!! obrigada pela ajuda

  7. Eleonora disse:

    excelente o tutorial, parabéns!

  8. Wesley disse:

    Eu poderia apenas por a equação da reta na linha de tendência, e a partir dela determinar quem seria “a” e “b”?

  9. Wladimir Melo disse:

    Amigo, o seu trabalho é fantástico. Parabéns! Se precisar de instruções sobre financiamentos empresariais, conte comigo.

  10. Rafael disse:

    a formula matricial da equacao A não está correta, o excel nao aceita… (nao testei a B)

    poderia atualiza-la? preciso usar a formula matricial para o desenvolvimento de um projeto.

  11. G disse:

    Como faço para fazer regressao com mais de 2 variáveis/?

  12. Pedro Amaro disse:

    Ajudou sobremaneira, obrigado!

  13. Marcus disse:

    E para o R2 da regressão, há uma maneira de calcular via expressão?

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