já discuti regressão linear neste blog antes, mas muitas vezes uma linha reta não é a melhor maneira de representar seus dados. Para estas situações específicas, podemos aproveitar algumas das ferramentas disponíveis para realizar regressão não linear ou ajuste de curva no Excel.Lembras-te do nosso velho amigo LINEST? Embora LINEST seja curto para” estimativa linear”, nós também podemos usá-lo para dados não lineares com alguns ajustes simples.digamos que temos alguns dados de queda de Pressão vs. fluxo através de uma válvula de água, e depois de traçar os dados em um gráfico vemos que os dados são quadráticos.
embora estes dados não sejam lineares, a função LINEST também pode ser usada aqui para encontrar a melhor curva de ajuste para estes dados. Para uma equação polinomial, nós fazemos isso usando constantes de array.
uma vantagem para usar LINEST para obter os coeficientes que definem a equação polinomial é que podemos retornar os coeficientes diretamente para as células. Assim não temos de Os transferir manualmente para fora do gráfico.
Uma vez que a equação é quadrática, ou um polinômio de segunda ordem, existem três coeficientes, um para X ao quadrado, um para x e uma constante. Então precisamos começar criando um espaço para armazenar os três coeficientes para a equação.
usando a linha de regressão não linear no Excel
a função LINEST devolve uma série de coeficientes e estatísticas de regressão opcionais. Então vamos precisar introduzi-lo como uma fórmula de matriz selecionando todas as três células para os coeficientes antes de introduzir a fórmula.
Se as células que contém a pressão e de fluxo de dados são chamados de “fluxo” e “pressão”, a fórmula parece com isso:
=PROJ(pressão, vazão^{1,2},TRUE, FALSE)
O y conhecidos, neste caso, são as medições de pressão, e o x conhecidos são as medições de fluxo aumentado para o primeiro e o segundo poder. Os suportes curly, ” {“e”}”, indicam uma constante de array no Excel. Basicamente, estamos dizendo ao Excel para criar duas matrizes: uma de fluxo e outra de fluxo ao quadrado, e para encaixar a pressão em ambas as matrizes juntas.
finalmente, os argumentos verdadeiro e falso dizem à função LINEST para calcular a ordenada na origem do y normalmente (em vez de forçá-la a zero) e não para retornar estatísticas de regressão adicionais, respectivamente.
Uma vez que é uma fórmula de array, precisamos introduzi-la escrevendo Ctrl+Shift+Enter.
a função então retorna os coeficientes de x2 e x, bem como uma constante (porque escolhemos permitir que LINEST calcule a interceptação em y).
Os coeficientes são idênticos aos gerados pelo gráfico de tendência ferramenta, mas eles estão em células, agora, o que os torna muito mais fácil para uso em cálculos subsequentes.
para qualquer equação polinomial, LINEST retorna o coeficiente para a ordem mais alta da variável independente no lado esquerdo, seguido pelo próximo mais alto e assim por diante, e finalmente a constante.
uma técnica similar pode ser usada para ajuste exponencial, logarítmico e curva de função de potência no Excel também.
ajuste de uma Curva Logarítmica de Dados
Um logarítmica função tem a forma:
podemos usar ainda PROJ.LIN para encontrar o coeficiente de m e a constante b, para esta equação inserindo ln(x) como o argumento para a val_conhecidos_x:
=PROJ(y_values,ln(x_values),TRUE,FALSE)
claro, este método aplica-se para qualquer equação logarítmica, independentemente do número base. Assim, poderia ser aplicado a uma equação contendo log10 ou log2 tão facilmente.
Encontrar os Coeficientes de um Melhor Ajuste de Curva Exponencial
Uma função exponencial tem a forma:
É um pouco mais complicado para obter os coeficientes a e b, para esta equação, porque primeiro precisamos fazer um pouco de álgebra para tornar a equação de assumir um “linear” do formulário. Primeiro, tome o logaritmo natural de ambos os lados da equação para obter o seguinte:
Agora podemos usar PROJ.LIN para obter ln(a) e b inserindo ln(y) como o argumento para a y_values:
=PROJ(ln(y_values),x_values,TRUE,FALSE)
segundo O valor retornado por esta fórmula de matriz é ln(a), de modo a obter apenas “um”, poderíamos simplesmente usar a função exponencial:
Que, no Excel, traduz-se para:
=EXP(número)
ajuste de uma Função de Potência de Dados
Uma função de potência curva pode ser ajuste aos dados usando PROJ.LIN no muito a mesma maneira que nós fazemos isso por uma função exponencial. Uma função de potência tem a forma:
mais uma vez, podemos “linearizar” – o tomando a base 10 log de ambos os lados da equação para obter:
Com a equação desta forma, a função PROJ.LIN para voltar b e log10(a) pode ser definido como:
=PROJ(LOG10(yvalues),LOG10(xvalues),TRUE,FALSE)
Desde que a função PROJ.LIN devolve b e log10(a), nós vamos ter que encontrar uma com a seguinte fórmula:
No Excel, que a fórmula é:
=10^(Número)
é isso por agora. Como você pode ver, há uma série de maneiras de usar a função LINEST para ajuste da curva não-linear no Excel.