Como encontrar a equação de uma linha no Excel se você só tem os pontos de dados

Excel tem quatro maneiras de encontrar a equação de uma linha: Função LINEST (ou LOGEST), linha de tendência em um gráfico, análise de regressão no Data...Analysis ToolPak, e Solver. Cada um desses métodos usa o método dos mínimos quadrados para encontrar os coeficientes da equação.

I sugere olhar para a estatística do quadrado R quando você faz isso para ter certeza de que a equação está explicando a maior parte da variação em seus dados. R-quadrado varia entre 0 e 1, com 0 significando que os seus dados são completamente aleatórios e 1 significando que a equação explica completamente toda a variação. Para fins científicos e de engenharia, eu gosto de ver R-quadrado sobre 0.9.

LINEST

Eu normalmente uso LINEST. Se você tiver n variáveis desconhecidas, ele retorna um intervalo de resultados de 5 linhas por n+1 colunas, com os coeficientes em ordem inversa na primeira linha (ou seja, constante na extrema direita). A quarta linha contém a estatística do quadrado R. LINEST é projetado para equações lineares, mas funciona muito bem com polinômios, também.

Se você tiver valores y na coluna A e x na coluna B, você pode encontrar os coeficientes de um polinômio cúbico selecionando um intervalo de 5 linhas por 4 colunas de células e entrando com array-entering uma fórmula como mostrado abaixo. Note the use of the array constant in the curly braces to avoid the need for three columns of x values (one for x, one for x squared, and one for x cubed).

1. =LINEST(A2:A30,B2:B30^{3,2,1},TRUE,TRUE) 

Array-entering means to hold the Control and Shift keys down, then hitting Enter. O Excel responderá adicionando chaves de quadrado { } ao redor da sua fórmula e preenchendo cada célula na faixa 5x4 de células com valores diferentes.

Linhas de Tendência em um gráfico de dispersão

Plote seus dados em um gráfico de dispersão, clique com o botão direito em qualquer um dos pontos de dados e depois escolha Linha de Tendência no diálogo resultante. Você pode escolher entre vários tipos diferentes de linhas de tendência, incluindo linear, polinomial, exponencial e logarítmica. Eu gosto de marcar as caixas para exibir tanto a equação quanto a estatística do quadrado R.

A equação no gráfico freqüentemente não exibe números significativos o suficiente. Você pode aumentar o número de casas decimais selecionando a equação e depois usando as Ferramentas de Gráfico...Formatar...Item do menu Seleção de Formato para exibir uma barra de tarefas com opções de formatação. Clique no ícone do gráfico de colunas na barra de tarefas, depois mude a categoria de formato de números para Number e especifique o número de casas decimais.

Analysis ToolPak

Certifique-se de que o add-in Analysis ToolPak é carregado procurando o item de menu Data...Data Analysis. Se você não o vê na extrema direita da fita de dados, use o item de menu Developer...Excel Add-Ins para marcar sua caixa e carregá-lo. Não selecione o Analysis ToolPak - VBA, pois isso é algo diferente.

Once loaded, use o item de menu Data...Data Analysis...Regression para selecionar seus dados e dizer onde exibir os resultados.

Solver Add-In

O Add-in Solver é o método mais geral de obtenção de coeficientes de regressão, e pode se ajustar a qualquer tipo de equação que você possa imaginar. Você não obtém valores de R-quadrado, apenas os coeficientes.

Certifique-se de que o add-in Solver está carregado. Você deve vê-lo na fita de dados na extrema direita. Caso contrário, marque sua caixa no menu Add-ins do Desenvolvedor...Excel item.

Para configurar o problema para o Solver, você precisará listar seus dados x e y em colunas, juntamente com uma coluna para os valores y calculados. Esses valores y calculados devem fazer referência a células contendo suposições para os coeficientes de regressão. Solver will then vary these guesses to minimize the sum of the squares of the difference between actual y and calculated y. I like to use the SUMSQ function to add up these squares in an array-entered formula like:

1. =SUMSQ(ActualYvalues - CalculatedYvalues) 

Select the cell with the SUMSQ formula. Abra o item de menu Data...Solver, escolha Minimizar, e especifique as células com os palpites para os coeficientes como as células Change. Agora o Solver irá variar os coeficientes para encontrar os mínimos quadrados adequados.