Tipos de linha de tendência, equações e fórmulas do Excel

Neste tutorial, você vai encontrar uma descrição detalhada de todas as opções de linha de tendência disponíveis no Excel e quando utiliza-las. Você também vai aprender como exibir uma equação de em um gráfico e encontrar a inclinação da linha de tendência.

É fácil inserir uma linha de tendência no Excel. O único verdadeiro desafio é escolher o tipo de linha que melhor corresponde ao tipo de dados que você está analisando. Aqui, vou te apresentar todas as opções de linha de tendência disponíveis no Excel. Se você está procurando como inserir uma linha de tendência em um gráfico do Excel, verifique o tutorial vinculado acima.

Tipos de linha de tendência do Excel

Quando adicionamos uma linha de tendência no Excel, temos 6 opções diferentes para escolher. Além disso, o Microsoft Excel permite exibir uma equação da linha de tendência e o valor de R ao quadrado em um gráfico:

A equação da linha de tendência é uma fórmula que encontra uma linha que melhor se ajusta aos pontos de dados.
O valor de R ao quadrado mede a confiabilidade da linha de tendência, ou seja, quanto mais próximo R² estiver de 1, melhor a linha de tendência se ajusta aos dados.

Abaixo, você vai encontrar uma breve descrição de cada tipo de linha de tendência com exemplos de gráficos.

Linha de tendência linear

A linha de tendência linear é melhor opção para ser usada com conjuntos de dados lineares, quando os pontos de dados em um gráfico se parecem com uma linha reta. Normalmente, uma linha de tendência linear descreve um aumento ou queda contínua ao longo do tempo.

Por exemplo, a seguinte linha de tendência linear mostra um aumento constante nas vendas ao longo de 6 meses. E o valor de R² de 0,9764 indica um ajuste muito bom dos valores estimados da linha de tendência com os dados reais.

Linha de tendência exponencial

A linha de tendência exponencial é uma linha curva, que ilustra um aumento ou queda nos valores de dados em uma taxa crescente. Portanto, a linha geralmente é mais curva de um lado. Este tipo de linha de tendência é freqüentemente usado em ciências, por exemplo, para visualizar o crescimento ou declínio da população humana em populações de animais selvagens.

Observe que uma linha de tendência exponencial não pode ser criada para dados que contenham zeros ou valores negativos.

Um bom exemplo de curva exponencial é a decadência de toda a população de tigres selvagens na Terra.

Linha de tendência logarítmica

A linha de ajuste logarítmica geralmente é usada para plotar dados que aumentam ou diminuem rapidamente. Ela pode incluir valores positivos e negativos.

Um exemplo de linha de tendência logarítmica pode ser uma taxa de inflação, que primeiro aumenta, mas depois se estabiliza.

Linha de tendência polinomial

A linha de tendência curvilínea polinomial funciona bem para grandes conjuntos de dados com valores oscilantes que têm mais de uma subida e descida.

Geralmente, um polinômio é classificado pelo grau do maior expoente. O grau da linha de tendência polinomial também pode ser determinado pelo número de curvas em um gráfico. Normalmente, uma linha de tendência polinomial quadrática tem uma dobra, um polinômio cúbico tem 1 ou 2 dobras e um polinômio quártico tem até 3 dobras.

Ao adicionar uma linha de tendência polinomial em um gráfico do Excel, você especifica o grau digitando o número correspondente na caixa Ordem no painel Linha de tendência do formato, que é 2 por padrão:

Por exemplo, a tendência polinomial quadrática é evidente no gráfico a seguir que mostra a relação entre o lucro e o número de anos que o produto está no mercado: aumento no início, pico no meio e queda próximo ao final.

Linha de tendência potência

A linha de tendência potência é muito semelhante à curva exponencial, só que possui um arco mais simétrico. É comumente usado para plotar medidas que aumentam a uma certa taxa.

Uma linha de tendência potência não pode ser adicionada a um gráfico do Excel que contenha valores zero ou negativos.

Como exemplo, vou te apresentar uma linha de tendência potência para visualizar a taxa de reação química. Observe o valor de R² igual a 0,9927, o que significa que a linha de tendência se ajusta aos dados quase perfeitamente.

Linha de tendência da média móvel

Quando os pontos de dados em seu gráfico têm muitos altos e baixos, uma linha de tendência de média móvel pode suavizar as flutuações extremas nos valores de dados para mostrar um padrão mais claro. Para isso, o Excel calcula a média móvel do número de períodos que você especifica (2 por padrão) e coloca esses valores médios como pontos na linha. Quanto mais alto o valor do período, mais suave é a linha.

Um bom exemplo prático é usar a linha de tendência da média móvel para revelar flutuações no preço de uma ação que, de outra forma, seriam difíceis de observar.

Equações e fórmulas de linha de tendência do Excel

Esta seção descreve as equações que o Excel usa para diferentes tipos de linha de tendência. Você não precisa construir essas fórmulas manualmente, simplesmente diga ao Excel para exibir a equação da linha de tendência em um gráfico.

Além disso, vamos discutir a fórmula para encontrar a inclinação de uma linha de tendência e outros coeficientes. As fórmulas assumem que você tem 2 conjuntos de variáveis: variável independente x e variável dependente y. Em suas planilhas, você pode usar essas fórmulas para obter os valores y previstos para quaisquer valores de x fornecidos.

Para consistência, usaremos o mesmo conjunto de dados com valores ligeiramente variáveis ​​para todos os exemplos. No entanto, lembre-se de que é apenas para fins de demonstração. Em suas planilhas, você deve escolher o tipo de linha de tendência correspondente ao seu tipo de dados.

Importante! As fórmulas de linha de tendência devem ser usadas apenas com gráficos de dispersão XY porque somente este gráfico plota os eixos x e y como valores numéricos.

Equação e fórmulas da linha de tendência linear

A equação da linha de tendência linear usa os métodos dos mínimos quadrados para buscar a inclinação e interceptar os coeficientes de modo que:

y= bx + a

Onde:

• b é a inclinação de uma linha de tendência.
• a é a interceptação y, que é o valor médio esperado de y quando todas as variáveis x são iguais a 0. Em um gráfico, é o ponto onde a linha de tendência cruza o eixo y.

Para regressão linear, o Microsoft Excel fornece funções especiais para obter os coeficientes de inclinação e interceptação.

Inclinação da linha de tendência
b: = INCLINAÇÃO (y; x)

Interceptação Y
a: = INTERCEPÇÃO (y; x)

Supondo que o intervalo x seja B2: B13 e o intervalo y seja C2: C13, as fórmulas são as seguintes:

= INCLINAÇÃO (C2: C13; B2: B13)

= INTERCEPÇÃO (C2: C13; B2: B13)

Os mesmos resultados podem ser obtidos usando a função PROJ.LIN como uma fórmula de matriz. Para isso, selecione 2 células adjacentes na mesma linha, insira a fórmula e pressione Ctrl + Shift + Enter para completá-la:

= PROJ.LIN (C2: C13, B2: B13)

Conforme mostrado na imagem abaixo, os coeficientes de inclinação e interceptação retornados pelas fórmulas estão perfeitamente alinhados com os coeficientes na equação da linha de tendência linear exibida no gráfico, apenas os últimos são arredondados para 4 casas decimais:

Equação e fórmulas da linha de tendência exponencial

Para a linha de tendência exponencial, o Excel usa a seguinte equação:

y = ae^(bx)

Onde a e b são coeficientes calculados e e é a constante matemática e (a base do logaritmo natural).

Os coeficientes podem ser calculados usando as fórmulas:

a: = EXP (ÍNDICE (PROJ.LIN (LN (y); x); 1; 2))

b: = ÍNDICE (PROJ.LIN (LN (y); x); 1)

Para nosso conjunto de dados de amostra, as fórmulas assumem a seguinte forma:

a: = EXP (ÍNDICE (PROJ.LIN (LN (C2: C13); B2: B13); 1; 2))

b: = ÍNDICE (PROJ.LIN (LN (C2: C13); B2: B13); 1)

Equação logarítmica da linha de tendência e fórmulas

Esta é a equação da linha de tendência logarítmica no Excel:

y = a * ln (x) + b

Onde a e b são constantes e ln é a função logarítmica natural.

Para obter as constantes, use estas fórmulas genéricas, que diferem apenas no último argumento:

a: = ÍNDICE (PROJ.LIN (y; LN (x)); 1)

b: = ÍNDICE (PROJ.LIN (y; LN (x)); 1; 2)

Para nosso conjunto de dados de amostra, usamos:

a: = ÍNDICE (PROJ.LIN (C2: C13; LN (B2: B13)); 1)

b: = ÍNDICE (PROJ.LIN (C2: C13; LN (B2: B13)); 1; 2)

Equação e fórmulas de linha de tendência polinomial

Para calcular a linha de tendência polinomial, o Excel usa esta equação:

y = b6x^6 +… + b2x² + b1x + a

Onde b1… b6 e a são constantes.

Dependendo do grau de sua linha de tendência polinomial, use um dos seguintes conjuntos de fórmulas para obter as constantes.

Linha de tendência polinomial quadrática (2ª ordem)

Equação:

y = b2x² + b1x + a

b2: = ÍNDICE (PROJ.LIN (y; x ^ {1;2}); 1)

b1: = ÍNDICE (PROJ.LIN (y; x ^ {1;2}); 1; 2)

a: = ÍNDICE (PROJ.LIN (y; x ^ {1;2}); 1; 3)

Linha de tendência polinomial cúbica (3ª ordem)

Equação:

y = b3x^3 + b2x² + b1x + a

b3: = ÍNDICE (PROJ.LIN (y; x ^ {1;2;3}); 1)

b2: = ÍNDICE (PROJ.LIN (y; x ^ {1;2;3}); 1; 2)

b1: = ÍNDICE (PROJ.LIN (y; x ^ {1;2;3}); 1; 3)

a: = ÍNDICE (PROJ.LIN (y; x ^ {1;2;3}); 1; 4)

As fórmulas para linhas de tendência polinomiais de grau superior podem ser construídas usando o mesmo padrão.

Para nosso conjunto de dados, a linha de tendência polinomial de 2ª ordem é mais adequada, então usando estas fórmulas:

b2: = ÍNDICE (PROJ.LIN (C2: C13; B2: B13 ^ {1;2}); 1)

b1: = ÍNDICE (PROJ.LIN (C2: C13; B2: B13 ^ {1;2}); 1; 2)

a: = ÍNDICE (PROJ.LIN (C2: C13; B2: B13 ^ {1;2}); 1; 3)

Equação e fórmulas da linha de tendência potência

Uma linha de tendência potência no Excel é desenhada com base nesta equação simples:

y = ax^b

Onde a e b são constantes, que podem ser calculadas com estas fórmulas:

a: = EXP (ÍNDICE (PROJ.LIN (LN (y); LN (x) ;;); 1; 2))

b: = ÍNDICE (PROJ.LIN (LN (y); LN (x) ;;); 1)

No nosso caso, as seguintes fórmulas funcionam muito bem:

a: = EXP (INDEX (PROJ.LIN (LN (C2: C13); LN (B2: B13) ;;); 1; 2))

b: = ÍNDICE (PROJ.LIN (LN (C2: C13); LN (B2: B13) ;;); 1)

A equação da linha de tendência do Excel está errada – razões e soluções

A equação da linha de tendência do Excel está correta apenas em gráficos de dispersão. As fórmulas de linha de tendência do Excel devem ser usadas apenas com gráficos XY (dispersão) porque somente neste tipo de gráfico os eixos y e x são plotados como valores numéricos.

Em gráficos de linhas, gráficos de colunas e barras, os valores numéricos são plotados apenas no eixo y. O eixo x é representado por uma série linear (1, 2, 3, …) independentemente de os rótulos dos eixos serem números ou texto. Quando você faz uma linha de tendência nesses gráficos, o Excel usa esses valores x assumidos na fórmula da linha de tendência.

Os números são arredondados na equação da linha de tendência do Excel. Para ocupar menos espaço no gráfico, o Excel exibe muito poucos dígitos significativos em uma equação de linha de tendência. Em termos de design, ele reduz significativamente a precisão da fórmula quando você fornece manualmente os valores x na equação.

Uma solução fácil é mostrar mais casas decimais na equação. Como alternativa, você pode calcular os coeficientes usando uma fórmula correspondente ao seu tipo de linha de tendência e formatar as células da fórmula para que mostrem um número suficiente de casas decimais. Para isso, basta clicar no botão Aumentar Decimal na guia Página Inicial do grupo Número.

É assim que você pode criar diferentes tipos de linha de tendência no Excel e obter suas equações. Agradeço a leitura e espero ver vocês por ai no blog. Até a próxima!

Conclusão

Neste artigo apresentei os diferentes tipos de linhas de tendência no Excel

Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado. Então, que tal praticar outros gráficos de artigos que já escrevi!

• Como Fazer um Gráfico de Gantt no Excel 2010, 2013 e 2016
• Como Fazer Gráfico de Linha no Excel
• Como Fazer Gráfico de Cascata no Excel
• Como Fazer Gráfico de Velocímetro no Excel
• Como Fazer Grafico de Pizza no Excel e Por que NUNCA Usa-lo
• Como Fazer um Gráfico de Área no Excel
• Como Fazer um Gráfico de Barras com Metas no Excel
• Como Fazer um Histograma no Excel e Todos os Detalhes

Quando você estiver dominando os principais gráficos do Excel você estará pronto para elaborar lindas interfaces para visualização de dados para seus usuários. Portanto, o próximo passo será ler este artigo aqui:

• Como Fazer um Dashboard no Excel

Para estar sempre atualizado continue lendo sobre gráficos no Excel aqui ou mais dicas e truques no Excel aqui.