Equação normal no Excel (estatísticas)

Veja como ficaria no Excel:

Eu tentei descrevê-lo em pseudo código:

1. Para cada registro no conjunto, calcule x*LN(21-y). Calcule a soma desses valores (chamaremos de "soma 1").
2. Para cada registro no conjunto, calcule x^2. Calcule a soma desses valores (chamaremos de "soma 2").
3. Divida a soma 1 pela soma 2.

De um colega:

i representa a observação particular. Todos esses cálculos pressupõem que há um número fixo, geralmente chamado de n , pares de observações. Por exemplo, você tinha 20 pares de observações em seus dados. Aqui, pares significa o valor de x e y juntos, geralmente denotado como (x, y), (0, 20), (1, 20)....(20, 2). O i representa o i 'ésimo par de observação entre todos os n pares.

Então, se i = 1, isso significa que estamos nos referindo ao primeiro par, (0, 20). Se i = 14, tomamos o 14º par, (14, 12). Em geral, matematicamente, o i - ésimo par de observações é (xi, yi), i está em subscrito.

O sinal sigma que diz i = 1 a n , significa essencialmente que estamos tomando todos os pares de observações a partir da 1ª observação até a última.

Aqui está uma alternativa que lida com o problema que você levantou em seu comentário para a resposta postada em math.stackexhange

Seu modelo é:

y = B - exp(a*x)

e você determinou, a priori , que B = 21.

O modelo não é uma relação exata entre os valores de y(i) e x(i), então é comum adicionar um termo de erro e(i) e representar o modelo como

y(i) = B - exp(a*x(i)) + e(i)

Os valores de y estimados pelo modelo para cada um dos valores de x são denotados por y'(i) onde

y'(i) = B - exp(a*x(i))

O método dos mínimos quadrados visa escolher o valor de a que minimiza a soma dos quadrados das diferenças entre os valores reais de y(i) e os correspondentes valores estimados ou y'(i).

y(i) - y'(i) = [B - exp(a* x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)

Então a soma dos quadrados das diferenças entre os valores de y(i) e y'(i) é

Soma[(y(i) - y'(i))^2] = Soma[e(i)^2]

O lado direito é a soma dos termos de erro ao quadrado, por isso é chamado de Error Sum of Squares ou ESS .

O processo de transformar os valores de y(i) em valores de z(i) via

z(i) = LN(21 - y(i))

cria um modelo linear

z = ax

que permite o uso da LINESTfunção para estimar o valor "melhor ajuste" de a . Com os dados fornecidos em sua resposta (ou sua pergunta no math.stackexchange), esse valor de melhor ajuste de a é 0,147233 - a mesma resposta que você derivou em sua resposta implementando a Equação Normal.

O ESS associado a este valor de a é 8,27991. No entanto, este valor não é o valor mínimo alcançável do ESS. Isso ocorre quando a assume o valor de 0,149140 e o ESS correspondente é 6,66073.

A captura de tela abaixo mostra os cálculos.

Os valores estimados de y'(i) e erro associado ao quadrado e ESS são mostrados para duas versões do modelo y = 21 - exp(a*x).

Na Versão 1, a é derivado usando a LINESTabordagem, com base no modelo transformado z = ax. Na Versão 2, a é o valor que minimiza o ESS do modelo (não transformado). Mais sobre como esse valor de a foi obtido é fornecido abaixo.

Com modelos lineares como y = mx + c, as Equações Normais fornecem uma maneira conveniente de estimar os valores de me c que minimizam o ESS. A função LINESTimplementa (entre outras coisas) as Equações Normais.

Para modelos não lineares (como y = 21 - exp(a*x)) tais equações convenientes geralmente não existem, então outros métodos precisam ser usados ​​para encontrar o valor de a que minimiza o ESS.

Uma abordagem é usar métodos de pesquisa: essencialmente, tente uma variedade de possibilidades diferentes para a e escolha aquela que resulta no menor ESS.

Isso é efetivamente o que a próxima captura de tela mostra. Ele usa o que a Microsoft chama de Data Table . Esta é uma má escolha de nome, pois uma Tabela de Dados não é uma tabela de dados. Em vez disso, é uma ferramenta para determinar como o valor de um cálculo muda à medida que um ou dois elementos dentro do cálculo são alterados. Ele é encontrado na faixa de opções do grupo Previsão do menu Dados selecionando "Tabela de dados..." no ícone "Análise de hipóteses".

A documentação da Microsft para criar e usar uma Tabela de Dados é bastante ruim, então vou fornecer uma abordagem de livro de receitas.

1. A própria tabela de dados é fornecida no intervalo N2:O23.
2. A célula O2contém o valor calculado a ser explorado. Esta célula contém a fórmula =J4que é o ESS associado ao valor de a na célula J3.
3. As possibilidades para os diferentes valores de a são colocadas no intervalo N3:N23e os resultados do ESS aparecerão nas células O3:O23. Isso fornece 21 valores possíveis de um . Esta é uma escolha arbitrária, as tabelas de dados podem envolver um número maior ou menor de valores possíveis.
4. Uma vez que os valores de a estejam no lugar, selecione o intervalo N2:O23e inicie a caixa de diálogo Tabela de Dados selecionando "Tabela de Dados..." no ícone "Análise What-if" no grupo Previsão do menu Dados.
5. Na caixa de diálogo, coloque $J$3no campo "Célula de entrada da coluna:" e pressione o botão "OK".
6. O intervalo O3:O23agora será preenchido com os valores de ESS correspondentes aos valores de a em N3:N23. Alterar qualquer um dos valores N3:N23atualizará os valores de ESS em O3:O23.

Os valores de a sãoN3:N23 definidos por fórmulas em vez de serem digitados. Os valores são definidos usando uma estratégia de pesquisa que analisa conjuntos de valores cada vez mais refinados para a .

Os valores de 21 aN3:N23 são baseados em torno de um valor Central na posição 11 - célula N13- com células acima e abaixo desta diferindo sucessivamente por um valor de Incremento , de modo que todo o intervalo de 21 valores esteja em ordem crescente.

A estratégia de pesquisa passa por várias etapas, sendo o número da etapa controlado pelo valor na célula O1.

Na etapa 1, o valor Central é definido como 0,15 (na célula R3) e o Incremento é definido como 0,001 (na célula S3), fornecendo valores N3:N23que variam de 0,14 a 0,16. Esse intervalo é escolhido com base no valor da Versão 1 de a , com uma antecipação de que o valor mínimo de ESS ficará dentro desse intervalo.

Isso prova ser o caso. Para os 21 valores de a começando em 0,14 e aumentando de 0,001 a 0,16, os valores de ESS correspondentes começam em mais de 39 (quando a é 0,14), diminuem à medida que a aumenta até a ter o valor de 0,149 (quando o ESS é 6,66972) e então aumente atingindo um valor de ESS acima de 70 quando a for 0,16. Isso mostra que o valor de a que minimiza o ESS está próximo de 0,149.

(Se não fosse o caso de um mínimo ser encontrado dentro do intervalo de valores, os valores de ESS teriam aumentado ou diminuído colocando o mínimo em uma extremidade do intervalo. Neste caso, o valor Central (na célula R3) exigirá ajuste com possivelmente um aumento no valor de Incremento (na célula S3) até que um mínimo de intervalo médio seja encontrado.)

Para qualquer intervalo de valores em N3:N23, células O27e , N27respectivamente, identifique o valor mínimo de ESS e o valor de a que produz o mínimo.

O valor de a produzindo o mínimo fornece o novo valor Central para a próxima etapa da pesquisa. O novo Incremento é o valor anterior reduzido por um fator de 10. Esses novos valores de Central e Incremento são inseridos manualmente na "tabela de controle" nas colunas Re So número do passo é aumentado manualmente em 1 na célula O1.

A busca prossegue por etapas sucessivas, terminando quando não for possível obter reduções práticas no valor de ESS.

A captura de tela mostra os resultados na etapa 2 da pesquisa.