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 contextualFerramentas 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.

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: http://1drv.ms/1npOpSO