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.