O método dos mínimos quadrados (LSM) baseia-se na minimização da soma dos desvios quadrados da função selecionada a partir dos dados em estudo. Neste artigo, aproximamos os dados disponíveis usando uma função lineary = uma x + b .

Método dos mínimos quadrados(Inglês) Ordinário Ao menos Quadrados , OLS) é um dos métodos básicos de análise de regressão em termos de estimativa de parâmetros desconhecidos modelos de regressão de acordo com os dados da amostra.

Considere a aproximação por funções dependendo de apenas uma variável:

• Linear: y=ax+b (este artigo)
• : y=a*Ln(x)+b
• : y=a*xm
• : y=a*EXP(b*x)+c
• : y=ax2 +bx+c

Observação: Casos de aproximação por um polinômio do 3º ao 6º grau são considerados neste artigo. A aproximação por um polinômio trigonométrico é considerada aqui.

Dependência linear

Estamos interessados ​​na relação de 2 variáveis X e y. Há uma suposição de que y depende de X de acordo com a lei linear y = machado + b. Para determinar os parâmetros dessa relação, o pesquisador fez observações: para cada valor de x i, foi feita uma medida de y i (ver arquivo de exemplo). Assim, sejam 20 pares de valores (х i ; y i).

Observação: Se a mudança passo a passo X é constante, então para construir gráficos de dispersão pode ser usado, se não, então você precisa usar o tipo de gráfico pontilhado .

É óbvio a partir do diagrama que a relação entre as variáveis ​​é quase linear. Para entender qual das muitas retas descreve mais "corretamente" a relação entre as variáveis, é necessário determinar o critério pelo qual as retas serão comparadas.

Como tal critério, usamos a expressão:

Onde ŷ eu = uma * XI + b ; n – número de pares de valores (no nosso caso n=20)

A expressão acima é a soma das distâncias quadradas entre os valores observados de y i e ŷ i e é frequentemente denotada como SSE ( soma de ao quadrado Erros (Resíduos), soma dos erros ao quadrado (resíduos)) .

Método dos mínimos quadradosé selecionar tal linha ŷ = machado + b, para o qual a expressão acima assume o valor mínimo.

Observação: Qualquer linha no espaço bidimensional é determinada exclusivamente pelos valores de 2 parâmetros: uma (inclinação) e b (mudança).

Acredita-se que quanto menor a soma das distâncias ao quadrado, melhor a linha correspondente se aproxima dos dados disponíveis e pode ainda ser usada para prever os valores de y a partir da variável x. É claro que, mesmo que na realidade não haja relação entre as variáveis ​​ou a relação seja não linear, os mínimos quadrados ainda selecionarão a “melhor” linha. Assim, o LSM não diz nada sobre a presença de uma relação real de variáveis, o método simplesmente permite que você escolha tais parâmetros de função uma e b , para o qual a expressão acima é mínima.

Tendo feito operações matemáticas não muito complexas (veja mais detalhes), você pode calcular os parâmetros uma e b :

Como pode ser visto na fórmula, o parâmetro uma é a razão de covariância e , então no MS EXCEL para calcular o parâmetro uma Você pode usar as seguintes fórmulas (consulte exemplo de folha de arquivo Linear):

= COVAR(B26:B45;C26:C45)/VAR.G(B26:B45) ou

= COVARIAÇÃO.B(B26:B45;C26:C45)/VAR.B(B26:B45)

Também para calcular o parâmetro uma você pode usar a fórmula = INCLINAÇÃO(C26:C45;B26:B45). Para parâmetro b usar fórmula = INTERCORTE(C26:C45;B26:B45) .

E, finalmente, a função PROJ.LIN() permite calcular os dois parâmetros de uma só vez. Para inserir uma fórmula PROJ.LIN(C26:C45;B26:B45) selecione 2 células seguidas e pressione CTRL + MUDANÇA + ENTRAR(ver artigo sobre). A célula da esquerda retornará o valor uma , a direita b .

Observação: Para não mexer na entrada fórmulas de matriz você precisará usar adicionalmente a função INDEX(). Fórmula = ÍNDICE(PROJ.LIN(C26:C45,B26:B45),1) ou apenas = PROJ.LIN(C26:C45;B26:B45) retornará o parâmetro responsável pela inclinação da linha, ou seja, uma . Fórmula = ÍNDICE(PROJ.LIN(C26:C45,B26:B45),2) retornará o parâmetro responsável pela interseção da linha com o eixo Y, ou seja, b .

Após calcular os parâmetros, gráfico de dispersão linha pode ser desenhada.

Outra maneira de desenhar uma linha reta usando o método dos mínimos quadrados é a ferramenta de gráfico linha de tendência. Para fazer isso, selecione o diagrama, selecione no menu Guia Layout, dentro Análise de grupo clique linha de tendência, então Aproximação linear .

Ao marcar a caixa "mostrar equação no diagrama" na caixa de diálogo, você pode certificar-se de que os parâmetros encontrados acima correspondem aos valores no diagrama.

Observação: para que os parâmetros correspondam, o tipo de gráfico deve ser . O fato é que ao construir um diagrama Cronograma Os valores do eixo x não podem ser definidos pelo usuário (o usuário só pode especificar rótulos que não afetem a localização dos pontos). Em vez de valores X, a sequência 1 é usada; 2; 3; … (para numeração de categoria). Portanto, se construir linha de tendência no diagrama de tipo Cronograma, então os valores desta sequência serão usados ​​em vez dos valores reais de X, o que levará a um resultado incorreto (a menos, é claro, que os valores reais de X não correspondam à sequência 1; 2 ; 3; ...).

Bem, no trabalho eles relataram à inspeção, o artigo foi escrito em casa para a conferência - agora você pode escrever no blog. Enquanto processava meus dados, percebi que não poderia deixar de escrever sobre um add-in muito legal e necessário no Excel, que se chama . Portanto, o artigo será dedicado a esse suplemento específico, e falarei sobre ele usando um exemplo de uso método dos mínimos quadrados(LSM) para procurar coeficientes desconhecidos da equação na descrição de dados experimentais.

Como habilitar o complemento "procurar uma solução"

Primeiro, vamos descobrir como habilitar esse complemento.

1. Vá para o menu "Arquivo" e selecione "Opções do Excel"

2. Na janela que aparece, selecione "Procurar uma solução" e clique em "ir".

3. Na janela seguinte, coloque uma marca de verificação na frente do item "procurar uma solução" e clique em "OK".

4. O add-in está ativado - agora ele pode ser encontrado no item de menu "Dados".

Método dos mínimos quadrados

Agora brevemente sobre método dos mínimos quadrados (LSM) e onde pode ser aplicado.

Digamos que temos um conjunto de dados depois de realizarmos algum experimento onde estudamos os efeitos do valor X no valor Y.

Queremos descrever matematicamente essa influência, para depois podermos usar essa fórmula e saber que se alterarmos tanto o valor de X, obteremos o valor de Y tal e tal...

Vamos dar um exemplo super-simples (veja a imagem).

Não é difícil pensar que os pontos estão localizados um após o outro como se estivessem em uma linha reta e, portanto, assumimos com segurança que nossa dependência é descrita por uma função linear y = kx + b. Ao mesmo tempo, temos a certeza de que quando X é igual a zero, o valor de Y também é igual a zero. Isso significa que a função que descreve a dependência será ainda mais simples: y=kx (lembre-se do currículo escolar).

Em geral, temos que encontrar o coeficiente k. Isto é o que vamos fazer com EMN usando o complemento "procurar uma solução".

O método é (aqui - atenção: você precisa pensar sobre isso) a soma das diferenças quadradas entre os valores obtidos experimentalmente e os correspondentes calculados foi mínima. Ou seja, quando X1=1 o valor real medido Y1=4,6, e o calculado y1=f (x1) for 4, o quadrado da diferença será (y1-Y1)^2=(4-4.6)^2= 0,36. O seguinte é o mesmo com o seguinte: quando X2=2, o valor real medido Y2=8,1 e o y2 calculado é 8, o quadrado da diferença será (y2-Y2)^2=(8-8.1)^ 2=0,01. E a soma de todos esses quadrados deve ser a menor possível.

Então, vamos começar a treinar sobre o uso do LSM e Suplementos do Excel "procurar solução" .

Aplicação de add-in encontrar solução

1. Se você não ativou o complemento "procurar uma solução", volte para a etapa Como habilitar o add-on "procurar uma solução" e habilitar 🙂

2. Na célula A1, insira o valor "1". Esta unidade será a primeira aproximação do valor real do coeficiente (k) da nossa dependência funcional y=kx.

3. Na coluna B temos os valores do parâmetro X, na coluna C - os valores do parâmetro Y. Nas células da coluna D inserimos a fórmula: “fator k vezes o valor de X”. Por exemplo, na célula D1, digite "=A1*B1", na célula D2, digite "=A1*B2" e assim por diante.

4. Acreditamos que o coeficiente k é igual a um e a função f (x) \u003d y \u003d 1 * x é a primeira aproximação para nossa solução. Podemos calcular a soma das diferenças quadradas entre os valores medidos de Y e os calculados usando a fórmula y=1*x. Podemos fazer tudo isso manualmente colocando as referências de célula apropriadas na fórmula: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... etc. No final, estão enganados e entendem que perdemos muito tempo. No Excel, para calcular a soma das diferenças ao quadrado, existe uma fórmula especial, "SUMQDIFF", que fará tudo por nós. Vamos inseri-la na célula A2 e definir o dados iniciais: o intervalo de valores medidos Y (coluna C) e o intervalo de valores Y calculados (coluna D).

4. A soma das diferenças dos quadrados foi calculada - agora vá para a aba "Dados" e selecione "Procurar uma solução".

5. No menu que aparece, selecione a célula A1 como a célula a ser alterada (aquela com o coeficiente k).

6. Como alvo, selecione a célula A2 e defina a condição "configurar igual ao valor mínimo". Lembre-se que esta é a célula onde calculamos a soma dos quadrados das diferenças entre os valores calculados e medidos, e esse valor deve ser mínimo. Apertamos "executar".

7. O coeficiente k é selecionado. Agora pode-se ver que os valores calculados agora estão muito próximos dos medidos.

P.S.

Em geral, é claro, para a aproximação de dados experimentais no Excel, existem ferramentas especiais que permitem descrever os dados usando uma função linear, exponencial, de potência e polinomial, portanto, muitas vezes você pode prescindir add-ons "Procurar uma solução". Eu falei sobre todos esses métodos de aproximação no meu artigo, então se você estiver interessado, dê uma olhada. Mas quando se trata de alguma função exótica com um coeficiente desconhecido ou problemas de otimização, então aqui superestrutura o melhor possível.

Suplemento "procurar uma solução" pode ser usado para outras tarefas, o principal é entender a essência: há uma célula onde selecionamos um valor e há uma célula alvo na qual uma condição é definida para selecionar um parâmetro desconhecido.
Isso é tudo! No próximo artigo vou contar um conto de fadas sobre férias, então para não perder o lançamento do artigo,

4.1. Usando funções integradas

Cálculo coeficientes de regressão realizado com a função

PROJ.LIN(Values_y; Valores_x; Konst; Estatisticas),

Values_y- matriz de valores y,

Valores_x- array opcional de valores x se matriz X omitido, assume-se que este é um array (1;2;3;...) do mesmo tamanho que Values_y,

Konst- um valor booleano que indica se a constante é necessária b era igual a 0. Se Konst tem o significado VERDADE ou omitido, então b calculado da maneira usual. Se o argumento Konsté FALSO, então bé assumido como 0 e os valores uma são escolhidos de modo que a relação y=ax.

Estatisticas- um valor booleano que indica se é necessário retornar estatísticas de regressão adicionais. Se o argumento Estatisticas tem o significado VERDADE, então a função PROJ.LIN retorna estatísticas de regressão adicionais. Se o argumento Estatisticas tem o significado FALSO ou omitido, então a função PROJ.LIN retorna apenas o coeficiente uma e permanente b.

Deve-se lembrar que o resultado das funções PROJ.LIN()é um conjunto de valores - um array.

Para cálculo coeficiente de correlação função é usada

CORREL(Matriz1;Matriz2),

retornando os valores do coeficiente de correlação, onde Matriz1- matriz de valores y, Matriz2- matriz de valores x. Matriz1 e Matriz2 deve ser do mesmo tamanho.

EXEMPLO 1. Vício y(x) é apresentado na tabela. Construir linha de regressão e calcular coeficiente de correlação.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Vamos inserir uma tabela de valores na planilha do MS Excel e construir um gráfico de dispersão. A planilha terá o formato mostrado na Fig. 2.

Para calcular os valores dos coeficientes de regressão uma e b selecionar células A7:B7, vamos ao assistente de funções e na categoria Estatística escolha uma função PROJ.LIN. Preencha a caixa de diálogo que aparece como mostrado na Fig. 3 e pressione OK.

Como resultado, o valor calculado aparecerá apenas na célula A6(Fig. 4). Para que um valor apareça em uma célula B6 você precisa entrar no modo de edição (tecla F2) e, em seguida, pressione a combinação de teclas CTRL+SHIFT+ENTER.

Para calcular o valor do coeficiente de correlação por célula C6 foi introduzida a seguinte fórmula:

C7=CORREL(B3:J3;B2:J2).

Conhecendo os coeficientes de regressão uma e b calcule os valores da função y=machado+b para dado x. Para isso, introduzimos a fórmula

B5=$A$7*B2+$B$7

e copie-o para o intervalo С5:J5(Fig. 5).

Vamos traçar a linha de regressão no diagrama. Selecione os pontos experimentais no gráfico, clique com o botão direito e selecione o comando Dados iniciais. Na caixa de diálogo que aparece (Fig. 5), selecione a guia Linha e clique no botão Adicionar. Preencha os campos de entrada, conforme mostrado na Fig. 6 e pressione o botão OK. Uma linha de regressão será adicionada ao gráfico de dados experimentais. Por padrão, seu gráfico será exibido como pontos não conectados por linhas de suavização.

Para alterar a aparência da linha de regressão, execute as etapas a seguir. Clique com o botão direito do mouse nos pontos que representam o gráfico de linhas, selecione o comando Tipo de Gráfico e defina o tipo de gráfico de dispersão, conforme mostrado na Fig. 7.

O tipo de linha, cor e espessura podem ser alterados da seguinte forma. Selecione a linha no diagrama, pressione o botão direito do mouse e selecione o comando no menu de contexto Formato da série de dados… Em seguida, faça as configurações, por exemplo, conforme mostrado na Fig. oito.

Como resultado de todas as transformações, obtemos um gráfico de dados experimentais e uma linha de regressão em uma área gráfica (Fig. 9).

4.2. Usando uma linha de tendência.

A construção de várias dependências de aproximação no MS Excel é implementada como uma propriedade do gráfico - linha de tendência.

EXEMPLO 2. Como resultado do experimento, foi determinada alguma dependência tabular.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Selecione e construa uma dependência aproximada. Construa gráficos de dependências analíticas tabulares e ajustadas.

A solução do problema pode ser dividida nas seguintes etapas: entrada dos dados iniciais, construção de um gráfico de dispersão e adição de uma linha de tendência a este gráfico.

Vamos considerar esse processo em detalhes. Vamos inserir os dados iniciais na planilha e plotar os dados experimentais. Em seguida, selecione os pontos experimentais no gráfico, clique com o botão direito e use o comando Adicionar eu linha de tendência(Fig. 10).

A caixa de diálogo que aparece permite que você construa uma dependência aproximada.

A primeira aba (Fig. 11) desta janela indica o tipo de dependência aproximada.

A segunda (Fig. 12) define os parâmetros de construção:

o nome da dependência aproximada;

Previsão para frente (para trás) em n unidades (este parâmetro determina quantas unidades para frente (para trás) é necessário estender a linha de tendência);

se deve mostrar o ponto de interseção da curva com a linha y=const;

se deve ou não mostrar a função de aproximação no diagrama (mostrar a equação no parâmetro do diagrama);

Se deve ou não colocar o valor do desvio padrão no diagrama (o parâmetro coloca o valor da confiabilidade da aproximação no diagrama).

Vamos escolher um polinômio de segundo grau como uma dependência aproximada (Fig. 11) e derivar uma equação descrevendo este polinômio no gráfico (Fig. 12). O diagrama resultante é mostrado na fig. treze.

Da mesma forma, com linhas de tendência você pode escolher os parâmetros de tais dependências como

linear y=a∙x+b,

logarítmico y=um ln(x)+b,

exponencial y=a∙eb,

potência y=a x b,

polinomial y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d e assim por diante, até e incluindo o polinômio de 6º grau,

Filtragem linear.

4.3. Usando a ferramenta de análise de opções: Encontrando uma solução.

De grande interesse é a implementação em MS Excel da seleção de parâmetros da dependência funcional pelo método dos mínimos quadrados utilizando a ferramenta de análise de opções: Procurar uma solução. Esta técnica permite que você escolha os parâmetros de uma função de qualquer tipo. Vamos considerar essa possibilidade no exemplo do problema a seguir.

EXEMPLO 3. Como resultado do experimento, a dependência z(t) apresentada na tabela

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Selecionar coeficientes de dependência Z(t)=Em 4 +Bt3 +Ct2 +Dt+K pelo método dos mínimos quadrados.

Este problema é equivalente ao problema de encontrar o mínimo de uma função de cinco variáveis

Considere o processo de resolução do problema de otimização (Fig. 14).

Deixe os valores MAS, NO, Com, D e Para armazenado nas células A7:E7. Calcule os valores teóricos da função Z(t)=At4+Bt3+Ct2+Dt+K para dado t(B2:J2). Para isso, na célula B4 insira o valor da função no primeiro ponto (célula B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Copie esta fórmula para o intervalo С4:J4 e obter o valor esperado da função em pontos, cujas abcissas são armazenadas nas células B2:J2.

Para celular B5 introduzimos uma fórmula que calcula o quadrado da diferença entre os pontos experimentais e calculados:

B5=(B4-B3)^2,

e copie-o para o intervalo С5:J5. Em uma célula F7 armazenaremos o erro quadrático total (10). Para isso, introduzimos a fórmula:

F7 = SOMA(B5:J5).

Vamos usar o comando Service®Procurar uma solução e resolver o problema de otimização sem restrições. Preencha os campos de entrada apropriados na caixa de diálogo mostrada na Fig. 14 e pressione o botão Correr. Se uma solução for encontrada, a janela mostrada na Fig. quinze.

O resultado do bloco de decisão será a saída para as células A7:E7valores de parâmetro funções Z(t)=At4+Bt3+Ct2+Dt+K. Nas células B4:J4 Nós temos valor esperado da função nos pontos de partida. Em uma célula F7 será mantido erro total ao quadrado.

Você pode exibir os pontos experimentais e a linha ajustada na mesma área gráfica se você selecionar o intervalo B2:J4, ligar Assistente de gráfico e formate a aparência dos gráficos resultantes.

Arroz. 17 exibe a planilha do MS Excel após os cálculos terem sido feitos.

Que encontra a mais ampla aplicação em vários campos da ciência e da prática. Pode ser física, química, biologia, economia, sociologia, psicologia e assim por diante. Pela vontade do destino, muitas vezes tenho que lidar com a economia e, portanto, hoje vou providenciar para você uma passagem para um país incrível chamado Econometria=) … Como você não quer isso?! É muito bom lá - você só tem que decidir! …Mas o que você provavelmente quer é aprender a resolver problemas mínimos quadrados. E leitores especialmente diligentes aprenderão a resolvê-los não apenas com precisão, mas também MUITO RÁPIDO ;-) Mas primeiro enunciado geral do problema+ exemplo relacionado:

Que sejam estudados indicadores em alguma área temática que tenham uma expressão quantitativa. Ao mesmo tempo, há todas as razões para acreditar que o indicador depende do indicador. Essa suposição pode ser tanto uma hipótese científica quanto baseada no senso comum elementar. Vamos deixar a ciência de lado, no entanto, e explorar áreas mais apetitosas - ou seja, mercearias. Denote por:

– espaço de varejo de uma mercearia, m²,
- volume de negócios anual de uma mercearia, milhões de rublos.

É bastante claro que quanto maior a área da loja, maior o seu volume de negócios na maioria dos casos.

Suponha que após realizar observações/experimentos/cálculos/dançar com um pandeiro, tenhamos à nossa disposição dados numéricos:

Com mercearias, acho que tudo está claro: - esta é a área da 1ª loja, - seu faturamento anual, - a área da 2ª loja, - seu faturamento anual, etc. A propósito, não é necessário ter acesso a materiais classificados - uma avaliação bastante precisa do volume de negócios pode ser obtida usando estatística matemática. Porém, não se distraia, o curso de espionagem comercial já está pago =)

Os dados tabulares também podem ser escritos na forma de pontos e representados da maneira usual para nós. sistema cartesiano .

Vamos responder a uma pergunta importante: quantos pontos são necessários para um estudo qualitativo?

Quanto maior melhor. O conjunto mínimo admissível consiste em 5-6 pontos. Além disso, com uma pequena quantidade de dados, resultados “anormais” não devem ser incluídos na amostra. Assim, por exemplo, uma pequena loja de elite pode ajudar ordens de grandeza mais do que “seus colegas”, distorcendo assim o padrão geral que precisa ser encontrado!

Se for bem simples, precisamos escolher uma função, cronograma que passa o mais próximo possível dos pontos . Tal função é chamada aproximando (aproximação - aproximação) ou função teórica . De um modo geral, aqui aparece imediatamente um "pretender" óbvio - um polinômio de alto grau, cujo gráfico passa por TODOS os pontos. Mas esta opção é complicada e muitas vezes simplesmente incorreta. (porque o gráfico vai “enrolar” o tempo todo e refletir mal a tendência principal).

Assim, a função desejada deve ser suficientemente simples e ao mesmo tempo refletir adequadamente a dependência. Como você pode imaginar, um dos métodos para encontrar essas funções é chamado mínimos quadrados. Primeiramente, vamos analisar sua essência de forma geral. Deixe alguma função aproximar os dados experimentais:


Como avaliar a precisão dessa aproximação? Vamos também calcular as diferenças (desvios) entre os valores experimentais e funcionais (estudamos o desenho). O primeiro pensamento que vem à mente é estimar quão grande é a soma, mas o problema é que as diferenças podem ser negativas. (Por exemplo, ) e os desvios resultantes de tal soma se cancelarão mutuamente. Portanto, como estimativa da precisão da aproximação, sugere-se tomar a soma módulos desvios:

ou em forma dobrada: (de repente, quem não sabe: é o ícone da soma, e é uma variável auxiliar - “contador”, que recebe valores de 1 a ).

Aproximando os pontos experimentais com diferentes funções, obteremos diferentes valores de , e é óbvio que onde essa soma é menor, aquela função é mais precisa.

Tal método existe e é chamado método de módulo mínimo. No entanto, na prática, tornou-se muito mais difundido. método dos mínimos quadrados, em que possíveis valores negativos são eliminados não pelo módulo, mas pelo quadrado dos desvios:

, após o que os esforços são direcionados para a seleção de tal função que a soma dos desvios quadrados foi o menor possível. Na verdade, daí o nome do método.

E agora voltamos a outro ponto importante: como observado acima, a função selecionada deve ser bastante simples - mas também existem muitas dessas funções: linear , hiperbólico, exponencial, logarítmico, quadrático etc. E, claro, aqui eu gostaria imediatamente de "reduzir o campo de atividade". Que classe de funções escolher para pesquisa? Técnica primitiva, mas eficaz:

- A maneira mais fácil de tirar pontos no desenho e analisar sua localização. Se eles tendem a ser em linha reta, você deve procurar equação de linha reta com valores ótimos e . Em outras palavras, a tarefa é encontrar TAIS coeficientes - de modo que a soma dos desvios quadrados seja a menor.

Se os pontos estiverem localizados, por exemplo, ao longo hipérbole, então fica claro que a função linear dará uma aproximação pobre. Neste caso, estamos procurando os coeficientes mais “favoráveis” para a equação da hipérbole - aqueles que dão a soma mínima dos quadrados .

Agora observe que em ambos os casos estamos falando de funções de duas variáveis, cujos argumentos são opções de dependência pesquisadas:

E, em essência, precisamos resolver um problema padrão - encontrar mínimo de uma função de duas variáveis.

Lembre-se do nosso exemplo: suponha que os pontos de "loja" tendam a estar localizados em uma linha reta e há todos os motivos para acreditar na presença dependência linear volume de negócios da área comercial. Vamos encontrar TAIS coeficientes "a" e "be" para que a soma dos desvios quadrados foi o menor. Tudo como de costume - primeiro derivadas parciais de 1ª ordem. De acordo com regra de linearidade você pode diferenciar logo abaixo do ícone de soma:

Se você quiser usar essas informações para um ensaio ou curso, ficarei muito grato pelo link na lista de fontes, você não encontrará cálculos tão detalhados em nenhum lugar:

Vamos fazer um sistema padrão:

Reduzimos cada equação por um “dois” e, além disso, “separamos” as somas:

Observação : analise independentemente por que "a" e "be" podem ser retirados do ícone de soma. A propósito, formalmente isso pode ser feito com a soma

Vamos reescrever o sistema em uma forma "aplicada":

após o qual o algoritmo para resolver nosso problema começa a ser desenhado:

Conhecemos as coordenadas dos pontos? Nós sabemos. Somas podemos encontrar? Facilmente. Nós compomos o mais simples sistema de duas equações lineares com duas incógnitas("a" e "beh"). Resolvemos o sistema, por exemplo, Método de Cramer, resultando em um ponto estacionário . Verificando condição suficiente para um extremo, podemos verificar que neste ponto a função atinge precisamente mínimo. A verificação está associada a cálculos adicionais e, portanto, a deixaremos nos bastidores. (se necessário, o quadro ausente pode ser visualizado). Tiramos a conclusão final:

Função a melhor maneira (pelo menos em comparação com qualquer outra função linear) aproxima pontos experimentais . Grosso modo, seu gráfico passa o mais próximo possível desses pontos. Na tradição econometria a função de aproximação resultante também é chamada equação de regressão linear pareada .

O problema em consideração é de grande importância prática. Na situação do nosso exemplo, a equação permite prever que tipo de rotatividade ("yig") estará na loja com um ou outro valor da área de venda (um ou outro significado de "x"). Sim, a previsão resultante será apenas uma previsão, mas em muitos casos será bastante precisa.

Analisarei apenas um problema com números "reais", pois não há dificuldades - todos os cálculos estão no nível do currículo escolar nas séries 7-8. Em 95% dos casos, você será solicitado a encontrar apenas uma função linear, mas no final do artigo mostrarei que não é mais difícil encontrar as equações para a hipérbole ótima, expoente e algumas outras funções.

Na verdade, resta distribuir os brindes prometidos - para que você aprenda a resolver esses exemplos não apenas com precisão, mas também rapidamente. Estudamos cuidadosamente o padrão:

Tarefa

Como resultado do estudo da relação entre dois indicadores, foram obtidos os seguintes pares de números:

Usando o método dos mínimos quadrados, encontre a função linear que melhor se aproxima da (com experiência) dados. Faça um desenho no qual, em um sistema de coordenadas retangulares cartesianas, trace pontos experimentais e um gráfico da função de aproximação . Encontre a soma dos desvios quadrados entre os valores empíricos e teóricos. Descubra se a função é melhor (em termos do método dos mínimos quadrados) pontos experimentais aproximados.

Observe que os valores "x" são valores naturais, e isso tem um significado significativo característico, sobre o qual falarei um pouco mais adiante; mas eles, é claro, podem ser fracionários. Além disso, dependendo do conteúdo de uma determinada tarefa, os valores "X" e "G" podem ser total ou parcialmente negativos. Bem, nos foi dada uma tarefa “sem rosto”, e começamos decisão:

Encontramos os coeficientes da função ótima como uma solução para o sistema:

Para fins de notação mais compacta, a variável “contador” pode ser omitida, pois já fica claro que a somatória é feita de 1 a .

É mais conveniente calcular os valores necessários em forma de tabela:


Os cálculos podem ser realizados em uma microcalculadora, mas é muito melhor usar o Excel - mais rápido e sem erros; assista a um pequeno vídeo:

Assim, obtemos o seguinte sistema:

Aqui você pode multiplicar a segunda equação por 3 e subtrair a 2ª da 1ª equação termo por termo. Mas isso é sorte - na prática, os sistemas geralmente não são dotados e, nesses casos, economiza Método de Cramer:
, então o sistema tem uma solução única.

Vamos fazer uma verificação. Eu entendo que não quero, mas por que pular erros onde você absolutamente não pode perdê-los? Substitua a solução encontrada no lado esquerdo de cada equação do sistema:

As partes certas das equações correspondentes são obtidas, o que significa que o sistema é resolvido corretamente.

Assim, a função de aproximação desejada: – de todas as funções lineares os dados experimentais são mais bem aproximados por ele.

Diferente Em linha reta dependência do faturamento da loja em sua área, a dependência encontrada é reverter (princípio "quanto mais - menos"), e este fato é imediatamente revelado pela negativa coeficiente angular. Função nos informa que com um aumento em um determinado indicador em 1 unidade, o valor do indicador dependente diminui média por 0,65 unidades. Como se costuma dizer, quanto maior o preço do trigo mourisco, menos vendido.

Para plotar a função de aproximação, encontramos dois de seus valores:

e execute o desenho:


A linha construída é chamada linha de tendência (ou seja, uma linha de tendência linear, ou seja, no caso geral, uma tendência não é necessariamente uma linha reta). Todos estão familiarizados com a expressão "estar na moda", e acho que esse termo dispensa comentários adicionais.

Calcular a soma dos desvios quadrados entre valores empíricos e teóricos. Geometricamente, esta é a soma dos quadrados dos comprimentos dos segmentos "carmesim" (dois dos quais são tão pequenos que você nem consegue vê-los).

Vamos resumir os cálculos em uma tabela:


Eles podem novamente ser realizados manualmente, caso eu dê um exemplo para o 1º ponto:

mas é muito mais eficiente fazer da forma já conhecida:

Vamos repetir: qual o significado do resultado? A partir de todas as funções lineares função o expoente é o menor, ou seja, é a melhor aproximação em sua família. E aqui, a propósito, a questão final do problema não é acidental: e se a função exponencial proposta será melhor aproximar os pontos experimentais?

Vamos encontrar a soma correspondente dos desvios quadrados - para distingui-los, vou designá-los com a letra "épsilon". A técnica é exatamente a mesma:


E novamente para cada cálculo de incêndio para o 1º ponto:

No Excel, usamos a função padrão EXP (A sintaxe pode ser encontrada na Ajuda do Excel).

Conclusão: , então a função exponencial aproxima os pontos experimentais pior do que a linha reta .

Mas deve-se notar aqui que "pior" é ainda não significa, o que está errado. Agora eu construí um gráfico dessa função exponencial - e ela também passa perto dos pontos - tanto que sem um estudo analítico é difícil dizer qual função é mais precisa.

Isso completa a solução, e volto à questão dos valores naturais do argumento. Em vários estudos, via de regra, econômicos ou sociológicos, meses, anos ou outros intervalos de tempo iguais são numerados com "X" natural. Considere, por exemplo, tal problema.

O método dos mínimos quadrados é um procedimento matemático para construir uma equação linear que mais se aproxima de um conjunto de duas séries de números. O objetivo deste método é minimizar o erro quadrático total. O Excel possui ferramentas que podem ser usadas para aplicar esse método em cálculos. Vamos ver como é feito.

Usando o método no Excel

o Ativando o complemento Solver

o Condições da tarefa

o Decisão

Usando um método no Excel

O método dos mínimos quadrados (LSM) é uma descrição matemática da dependência de uma variável em outra. Pode ser usado para previsão.

Ativar o suplemento Solver

Para usar o OLS no Excel, você precisa habilitar o suplemento "Buscar uma solução", que está desabilitado por padrão.

1. Vá para a guia "Arquivo".

2. Clique no nome da seção "Opções".

3. Na janela que se abre, pare a seleção na subseção "Complementos".

4. No bloco "Ao controle", que está localizado na parte inferior da janela, coloque o interruptor na posição "Suplementos do Excel"(se tiver um valor diferente) e clique no botão "Vai...".

5. Uma pequena janela se abre. Coloque uma marca de seleção ao lado da opção "Buscar uma solução". Clique no botão OK.

Agora a função Encontrando uma solução no Excel é ativado e suas ferramentas aparecem na faixa de opções.

Lição: Encontrar uma solução no Excel

Condições do problema

Vamos descrever a aplicação do LSM em um exemplo específico. Temos duas linhas de números x e y, cuja sequência é mostrada na imagem abaixo.

Essa dependência pode ser descrita com mais precisão pela função:

Ao mesmo tempo, sabe-se que x=0y também igual 0 . Portanto, esta equação pode ser descrita pela dependência y=nx.

Temos que encontrar a soma mínima dos quadrados da diferença.

Decisão

Passemos à descrição da aplicação direta do método.

1. À esquerda do primeiro valor x coloque um número 1 . Este será o valor aproximado do primeiro valor do coeficiente n.

2. À direita da coluna y adicionar outra coluna nx. Na primeira célula desta coluna escrevemos a fórmula para multiplicar o coeficiente n para a célula da primeira variável x. Ao mesmo tempo, fazemos o link para o campo com o coeficiente absoluto, pois esse valor não será alterado. Clicamos no botão Entrar.

3. Usando a alça de preenchimento, copie esta fórmula para todo o intervalo da tabela na coluna abaixo.

4. Em uma célula separada, calculamos a soma das diferenças dos quadrados dos valores y e nx. Para isso, clique no botão "Inserir Função".

5. Na abertura "Assistente de funções" procurando uma entrada "SUMMKVRAZN". Selecione-o e clique no botão OK.

6. A janela de argumentos é aberta. Em campo "Matriz_x" y. Em campo "Array_y" insira um intervalo de células de coluna nx. Para inserir valores, basta colocar o cursor no campo e selecionar o intervalo apropriado na planilha. Após entrar, clique no botão OK.

7. Vá para a guia "Dados". Na faixa de opções da caixa de ferramentas "Análise" clique no botão "Buscar uma solução".

8. A janela de parâmetros da ferramenta é aberta. Em campo "Otimizar a função objetivo" especifique o endereço da célula com a fórmula "SUMMKVRAZN". No parâmetro "Antes" certifique-se de colocar o interruptor na posição "Mínimo". Em campo "Mudando de Células" especifique o endereço com o valor do coeficiente n. Clique no botão "Achar uma solução".

9. A solução será exibida na célula do coeficiente n. É esse valor que será o menor quadrado da função. Se o resultado satisfizer o usuário, clique no botão OK em uma janela adicional.

Como você pode ver, a aplicação do método dos mínimos quadrados é um procedimento matemático bastante complicado. Mostramos em ação com o exemplo mais simples, mas existem casos muito mais complexos. No entanto, o kit de ferramentas do Microsoft Excel foi projetado para simplificar ao máximo os cálculos.

http://multitest.semico.ru/mnk.htm

Disposições gerais

Quanto menor o número em valor absoluto, melhor a linha reta (2) é escolhida. Como característica da precisão da seleção de uma linha reta (2), podemos tomar a soma dos quadrados

As condições mínimas para S serão

	(6)
	(7)

As equações (6) e (7) podem ser escritas da seguinte forma:

	(8)
	(9)

A partir das equações (8) e (9) é fácil encontrar a e b a partir dos valores experimentais x i e y i . A linha (2) definida pelas equações (8) e (9) é chamada de linha obtida pelo método dos mínimos quadrados (esse nome enfatiza que a soma dos quadrados S tem um mínimo). As equações (8) e (9), a partir das quais a reta (2) é determinada, são chamadas de equações normais.

É possível indicar uma forma simples e geral de compilar equações normais. Usando os pontos experimentais (1) e a equação (2), podemos escrever o sistema de equações para a e b

y 1 \u003d ax 1 +b,
y2=ax2+b, ...		(10)
yn=axn+b,

Multiplique as partes esquerda e direita de cada uma dessas equações pelo coeficiente na primeira incógnita a (ou seja, x 1 , x 2 , ..., x n) e adicione as equações resultantes, resultando na primeira equação normal (8).

Multiplicamos os lados esquerdo e direito de cada uma dessas equações pelo coeficiente da segunda incógnita b, ou seja, por 1 e adicione as equações resultantes, resultando na segunda equação normal (9).

Este método de obtenção de equações normais é geral: é adequado, por exemplo, para a função

é um valor constante e deve ser determinado a partir de dados experimentais (1).

O sistema de equações para k pode ser escrito:

Encontre a linha (2) usando o método dos mínimos quadrados.

Decisão. Nós achamos:

X i = 21, y i = 46,3, x i 2 = 91, x i y i = 179,1.

Escrevemos as equações (8) e (9)91a+21b=179,1,

21a+6b=46,3, daqui encontramos
a=0,98 b=4,3.