702 Shares 1765 views

Regressão no Excel: exemplos de equação. regressão linear

A análise de regressão – um método de estudo estatístico para mostrar a dependência de um parâmetro de uma ou mais variáveis independentes. Na era pré-computador, seu uso tem sido bastante difícil, especialmente quando se tratava de grandes volumes de dados. Hoje em dia, aprender a construir uma regressão no Excel, você pode resolver problemas estatísticos complexos em apenas alguns minutos. Abaixo estão alguns exemplos específicos da economia.

tipos de regressão

Este conceito foi introduzido para a matemática por Francis Galton em 1886. Regressão é:

  • linear;
  • parabólica;
  • poder;
  • exponencial;
  • hiperbólica;
  • exponencial;
  • logarítmica.

EXEMPLO 1

Considere o problema de determinar a dependência do número de demissões de membros do pessoal do salário médio nos 6 empresas industriais.

Tarefa. Seis empresas têm analisado o salário médio mensal eo número de funcionários que pararam voluntariamente. Em forma de tabela, temos:

A

B

C

1

X

Número de demissões

salário

2

y

30000 rublos

3

1

60

35000 rublos

4

2

35

40000 rublos

5

3

20

45000 rublos

6

4

20

50.000 rublos

7

5

15

55000 rublos

8

6

15

60000 rublos

Para o problema de determinar a dependência dos trabalhadores quantidade de separações a partir do salário médio de modelo de regressão 6 empresas tem a forma de equação Y = a 0 + a 1 x 1 + … + a k x k, onde x i – variáveis que influenciam, um i – coeficientes de regressão, ak – número de factores.

Y para uma determinada tarefa – é um indicador para despedir um empregado, um fator que contribui – o salário, que é representada por X.

Aproveitando o poder da planilha "Excel"

A análise de regressão no Excel deve ser precedido por um aplicativo para os dados da tabela existentes built-in funções. No entanto, para estes fins, é melhor usar uma "análise de pacotes" add-in muito útil. Para habilitá-lo, você precisa:

  • com o "File" guia vá em "Configurações";
  • Na janela que se abre, selecione 'Add-ons';
  • clique no botão "Go", localizada na parte inferior direita da linha "gestão";
  • colocar uma marca de verificação ao lado de "ferramentas de análise" e confirme a sua acção com a tecla "OK".

Se feito corretamente, o lado direito da guia "Dados", localizado acima da folha de trabalho "Excel", mostra o botão desejado.

Regressão Linear no Excel

Agora que você tem em mãos todas as ferramentas virtuais necessárias para cálculos econométricos, podemos começar a resolver o nosso problema. Para fazer isso:

  • botão é clicado em "Análise de Dados";
  • clique no botão "regressão" na janela aberta;
  • um separador que parece apresentar uma gama de valores de Y (o número de trabalhadores separações) e X (salário);
  • reafirmar suas ações, pressionando o botão «OK».

Como resultado, o programa irá preencher automaticamente a nova análise de regressão de dados folha de planilha. Preste atenção! No Excel, há uma oportunidade para definir o lugar que você preferir para esta finalidade. Por exemplo, pode ser a mesma folha, em que os valores de Y e X, ou mesmo um novo livro, especificamente concebido para o armazenamento de tais dados.

os resultados da análise de regressão de R-quadrado

Os dados do Excel obtido no exemplo de dados considerados ter a forma:

Primeiro de tudo, devemos prestar atenção para o valor de R-quadrado. Ela representa o coeficiente de determinação. Neste exemplo, R-quadrado = 0,755 (75,5%), m. E. Os parâmetros calculados do modelo para explicar a relação entre os parâmetros considerados por 75,5%. Quanto maior for o valor do coeficiente de determinação, o modelo seleccionado é considerado mais útil para tarefas particulares. Acredita-se para descrever corretamente a situação real no valor de R-quadrado acima de 0,8. Se o R-quadrado <0,5, em seguida, uma análise de regressão no Excel não pode ser considerado razoável.

análise proporção

Número 64,1428 mostra qual será o valor do Y, se todas as variáveis xi em nosso modelo será reposto. Em outras palavras, pode-se argumentar que o valor do parâmetro analisado é influenciado por outros factores que não os descritos no modelo específico.

O próximo fator -0,16285 localizado em célula B18, mostra a influência importante de variável X para Y. Isto significa que o salário médio de trabalhadores no interior do modelo afecta o número de renúncias a partir do peso de -0,16285, t. E. O grau do seu impacto em todos pequena. O sinal "-" indica que o coeficiente é negativo. É óbvio, pois todos sabemos que quanto mais salário na empresa, menos as pessoas manifestaram o desejo de rescindir o contrato de trabalho ou demitidos.

regressão múltipla

Sob este termo refere-se à equação de comunicação com várias variáveis independentes da forma:

y = f (x 1 + x 2 … x + m) + ε, onde y – é uma nota característica (a variável dependente), e x 1, x 2, … x m – são factores de sinais (variáveis independentes).

estimativa de parâmetros

Para a regressão múltipla (MR) é realizada utilizando um método de quadrados mínimos (LSM). Para equações lineares da forma Y = a + b 1 x 1 + … + b + m x m ε construção de um sistema de equações normais (centímetros. Abaixo)

Para entender o princípio do método, consideramos o caso de dois fatores. Em seguida, temos a situação descrita pela fórmula

Assim, obtém-se:

onde σ – é a variância da característica respectiva, que se reflecte no índice.

MNC é aplicável para a equação MR para standartiziruemom escala. Neste caso, temos a equação:

em que t y, t x 1, … t xm standartiziruemye variáveis para que os valores médios são 0; p i – coeficientes de regressão padronizados e desvio padrão – 1.

Por favor, note que todas p i neste caso definido como a normalizada e tsentraliziruemye, por conseguinte, uma comparação entre uma considerado válido e aceitável. Além disso, aceita-se a realização de triagem de fatores, descartando aqueles que têm os menores valores de βi.

O problema com o uso de equação de regressão linear

Suponha que você tenha uma tabela de dinâmica do preço de um determinado produto N durante os últimos 8 meses. É necessário decidir se a aquisição do seu partido ao preço de 1850 rublos. / T.

A

B

C

1

o mês

nome do mês

preço N

2

1

janeiro

1750 rublos por tonelada

3

2

fevereiro

1755 rublos por tonelada

4

3

março

1767 rublos por tonelada

5

4

abril

1760 rublos por tonelada

6

5

maio

1770 rublos por tonelada

7

6

Junho

1790 rublos por tonelada

8

7

Julho

1810 rublos por tonelada

9

8

Agosto

1840 rublos por tonelada

Para resolver este problema no processador tabular "Excel" necessário para usar já conhecido por exemplo função "Análise de Dados", apresentada acima. Em seguida, escolha a seção "Regressão" e parâmetros definidos. Devemos lembrar que na "Faixa de entrada Y» deve ser apresentado a uma gama de valores da variável dependente (neste caso, o preço das mercadorias em meses específicos do ano) e na "Entrada intervalo X» – para um independente (do mês). Nós confirmar a ação clicando em «OK». Em uma nova planilha (se assim for indicado), obtemos os dados para a regressão.

Estamos construindo sobre eles equação linear da forma y = ax + b, onde como os parâmetros a e b são os coeficientes do número da linha do mês e nome dos coeficientes e « "linha Y-intersecção da folha com os resultados da análise de regressão. Assim, a equação de regressão linear (EQ) 3 para o problema pode ser escrito como:

O preço dos bens N = 11714 * 1727,54 mês número +.

ou em notação algébrica

y = 11.714 x + 1727,54

análise de resultados

Para decidir se o recebeu equação de regressão linear usando adequadamente os vários coeficientes de correlação (CMC) e determinação, bem como teste e t-teste de Fisher. Na tabela de regressão "Excel", com os resultados que eles agem sob os nomes múltiplos R, R-quadrado, F-t-estatísticas e estatísticas, respectivamente.

KMC R permite estimar a relação probabilística proximidade entre variáveis independente e dependente. Seu alto valor indica uma ligação bastante forte entre a variável "Número do mês" e "N Preço do produto em rublos por 1 tonelada." No entanto, a natureza dessa relação é desconhecida.

O quadrado do coeficiente de determinação R2 (RI) é uma característica numérica do a proporção de dispersão total, mostra uma dispersão de parte de dados experimentais, isto é, Os valores da variável dependente correspondente a uma equação de regressão linear. Em este problema, este valor é de 84,8%, pf. E. Estatística com um elevado grau de precisão obtidos são descritos SD.

F-estatística, também conhecidos como critério de Fisher utilizado para avaliar a significância da dependência linear ou refutando hipóteses confirmando sua existência.

O valor da estatística t (teste t de Student) ajuda a avaliar a significância do coeficiente a qualquer membro dependência linear desconhecido livre. Se o valor do teste t> t cr, a hipótese de uma insignificância equação linear de livre termo é rejeitado.

Neste problema para um mandato livre através de instrumentos "Excel" verificou-se que t = 169,20903, e p = 2,89E-12, t. E. têm uma probabilidade zero que os fiéis serão rejeitou a hipótese da insignificância do termo livre. Para coeficiente desconhecido em t = 5,79405, e p = 0,001158. Em outras palavras, a probabilidade de que uma hipótese correta rejeitado vai insignificância do coeficiente para o desconhecido, é de 0,12%.

Assim, pode-se argumentar que a equação de regressão linear obtida de forma adequada.

O problema da conveniência de compra de ações

regressão múltipla foi realizada em Excel usando a mesma ferramenta "Análise de Dados". Considere a aplicação específica.

Guia empresa «NNN» deve decidir se a comprar 20% das ações da JSC «MMM». Preço do pacote (SP) é de 70 milhões de dólares americanos. Especialistas de «NNN» recolheram dados sobre transações similares. Decidiu-se avaliar o valor das ações em tais parâmetros, expressos em milhões de dólares norte-americanos, tais como:

  • contas a pagar (VK);
  • volume de facturação anual (VO);
  • receber (VD);
  • valor dos activos fixos (SOF).

Além disso, use as dívidas salariais de empresas (V3 U) em milhares de dólares norte-americanos.

Os meios Excel processador tabela de decisão

Primeiro você precisa criar uma tabela de dados de entrada. É como segue:

Seguinte:

  • caixa chamada "análise de dados";
  • selecionado seção "Regressão";
  • a janela de entrada "intervalo Y» administrado alcance valores da variável dependente da coluna G;
  • clique no ícone com uma seta vermelha para a direita da janela "de entrada intervalo de X» e isolado em um intervalo de folha de todos os valores da coluna B, C, D, F.

Marque o ponto "Nova planilha" e clique em "Ok".

Obter uma análise de regressão para esta tarefa.

Os resultados do estudo e as conclusões

"Collect" arredondada a partir dos dados apresentados acima na equação de regressão processador Excel tabela de folha:

SD = 0,103 * SOF + 0.541 * VO – 0031 * VK + 0.405 * VD + 0.691 * VZP – 265.844.

Na forma matemática mais usual que pode ser escrita como:

y = 0,103 * x 1 + 0541 * x2 – x3 + 0,031 * 0,405 * 0,691 * x4 + x5 – 265844

Os dados para «MMM» JSC apresentados na tabela abaixo:

SOF, USD

VO, USD

VK, USD

VD, USD

VZP, USD

JV, USD

102,5

535,5

45,2

41,5

21.55

64,72

Substituindo-os para a equação de regressão, obteve uma figura de 64,72 milhões de dólares americanos. Isto significa que as ações da JSC «MMM» não deve comprar, porque seu custo é muito caro em 70 milhões de dólares norte-americanos.

Como você pode ver, o uso de planilhas "Excel" e a equação de regressão permissão para tomar uma decisão informada sobre a conveniência de transações bastante específico.

Agora você sabe o que é uma regressão. Exemplos para o Excel, discutidos acima, irá ajudá-lo na solução de problemas práticos de econometria.