Regresión en Excel: exemplos de ecuación. regresión lineal

A análise de regresión - un método de estudo estatístico para mostrar a dependencia dun parámetro dunha ou máis variables independentes. Na era pre-ordenador, o seu uso foi moi difícil, especialmente cando se trataba de grandes volumes de datos. Hoxe en día, aprender a construír unha regresión en Excel, pode resolver problemas estatísticos complexos en só uns minutos. Abaixo amósanse algúns exemplos específicos da economía.

tipo de regresión

Este concepto foi introducido á matemática por Francis Galton en 1886. Regresión é:

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

Exemplo 1

Considere o problema de determinar a dependencia do número de despedimentos de membros do persoal do salario medio nos 6 empresas industriais.

Tarefa. Seis empresas analizaron o salario medio mensual eo número de funcionarios que deixaron voluntariamente. En forma de táboa, temos:

	A	B	C
1	X	Número de despedimentos	salario
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

Ao problema de determinar a dependencia dos traballadores cantidade de separacións desde salario medio de modelo de regresión 6 empresas ten a forma ecuación y = ₀ + a ₁ x ₁ + ... + a _K x _K, onde x _I - variables que inflúen, un _i - coeficientes de regresión, AK - número de factores.

Y para unha determinada tarefa - é un indicador para despedir un empregado, un factor que contribúe - o salario, que está representada por X.

Aproveitando o poder da folla "Excel"

A análise de regresión en Excel debe ser precedido por unha aplicación para os datos da táboa existente built-in funcións. Con todo, para estes fins, é mellor usar unha "análise de paquetes" add-in moi útil. Para activalas-lo, ten que:

• co "File" guía vaia en "Configuración";
• Na ventá que se abre, seleccione 'Add-ons'
• prema no botón "Go", situada na parte inferior dereita da liña "xestión";
• poñer unha marca de verificación ao lado de "ferramentas de análise" e confirme a súa acción coa tecla "Aceptar".

Se feito correctamente, o lado dereito da guía "Datos", situado por enriba da folla de traballo "Excel", mostra o botón que desexe.

Regresión lineal en Excel

Agora que ten en mans as ferramentas virtuais necesarias para cálculos econométricos, podemos comezar a resolver o noso problema. Para iso:

• botón é premendo en "Análise de Datos";
• prema no botón "regresión" na xanela aberta;
• un separador parecer presentar unha gama de valores de Y (o número de traballadores separacións) e X (salario);
• reafirmar as súas accións, preme o botón «Aceptar».

Como resultado, o programa pode cubrir automaticamente a nova análise de regresión de datos folla de folla de cálculo. Preste atención! No Excel, hai unha oportunidade para establecer o lugar que prefire para esta finalidade. Por exemplo, pode ser a mesma folla, en que os valores de Y e X, ou mesmo un novo libro, especialmente deseñado para o almacenamento de tales datos.

os resultados da análise de regresión de R ao cadrado

Os datos de Excel obtido no exemplo de datos considerados ter a forma:

Primeiro de todo, hai que prestar atención ao valor de R ao cadrado. Representa o coeficiente de determinación. Neste exemplo, R ao cadrado = 0,755 (75,5%), m. E. Os parámetros calculados do modelo para explicar a relación entre os parámetros considerados por 75,5%. Canto maior sexa o valor do coeficiente de determinación, o modelo escollido é considerado máis útil para tarefas particulares. Crese para describir correctamente a situación real no valor de R ao cadrado encima de 0,8. Se o R ao cadrado <0,5, a continuación, unha análise de regresión en Excel non pode ser considerado razoable.

análise proporción

Número 64,1428 mostra cal será o valor do Y, se todas as variables XI no noso modelo será reposto. Noutras palabras, pódese argumentar que o valor do parámetro analizado é influenciado por outros factores que non os descritos no modelo.

O seguinte factor -0,16285 situado célula B18, mostra a influencia importante de variable X para Y. Isto significa que o salario medio de traballadores no interior do modelo afecta o número de renuncias desde peso -0,16285, t. E. O grao do seu impacto en todos pequena. O signo "-" indica que o coeficiente é negativo. É evidente, pois todos sabemos que canto máis salario na empresa, menos as persoas manifestaron o desexo de rescindir o contrato de traballo ou despedidos.

regresión múltiple

Baixo este termo refírese á ecuación de comunicación con varias variables independentes da forma:

y = f (x ₁ + x ₂ ... x + _m) + ε, onde y - é unha nota característica (a variable dependente), e x _1, x _2, ... x _m - son factores de sinais (variables independentes).

Estimación de parámetros

Á regresión múltiple (MR) realízase utilizando un método de cadrados mínimos (LSM). Para ecuacións lineais de forma Y = a + b ₁ x _{1 + ... + b + m x m} ε construción dun sistema de ecuacións normais (cm. Abaixo)

Para entender o principio do método, consideramos o caso de dous factores. Logo temos a situación descrita pola fórmula

Así, obtense:

onde σ - é a varianza da característica respectiva, que se reflicte no índice.

MNC é aplicable para a ecuación MR para standartiziruemom escala. Neste caso, temos a ecuación:

en que t _y, t _{x 1, ...} t _XM - standartiziruemye variables para que os valores medios son 0; p _I - coeficientes de regresión estandarizados e desviación estándar - 1.

Por favor, teña en conta que todas p _i neste caso defínese como a normalizada e tsentraliziruemye, polo tanto, unha comparación entre unha considerado válido e aceptábel. Ademais, acéptase a realización de selección de factores, descartando aqueles que teñen os menores valores de βi.

O problema co uso de ecuación de regresión lineal

Supoña que teña unha táboa de dinámica do prezo dun determinado produto N durante os últimos 8 meses. Cómpre decidir se a adquisición do seu partido ao prezo de 1850 rublos. / T.

	A	B	C
1	o mes	nome do mes	prezo N
2	1	xaneiro	1750 rublos por tonelada
3	2	febreiro	1755 rublos por tonelada
4	3	marzo	1767 rublos por tonelada
5	4	abril	1760 rublos por tonelada
6	5	maio	1770 rublos por tonelada
7	6	xuño	1790 rublos por tonelada
8	7	xullo	1810 rublos por tonelada
9	8	agosto	1840 rublos por tonelada

Para solucionar este problema no procesador tabular "Excel" necesario para utilizar xa coñecido por exemplo función "Análise de Datos", presentada anteriormente. A continuación, escolla a sección "regresión" e parámetros definidos. Debemos lembrar que na "franxa de entrada Y» debe ser presentado a unha gama de valores da variable dependente (neste caso, o prezo das mercadorías en meses específicos do ano) e na "Entrada intervalo X» - para un independente (do mes). Nós confirmar a acción premendo no «Aceptar». Nunha nova folla (se é así indicado), obtemos os datos para a regresión.

Estamos construíndo sobre eles ecuación lineal da forma y = ax + b, onde como os parámetros a e b son os coeficientes do número da liña do mes e nome dos coeficientes e «" liña Y-intersección da folla cos resultados da análise de regresión. Así, a ecuación de regresión lineal (EQ) 3 para o problema pode ser escrito como:

O prezo dos bens N = 11714 * 1727,54 mes número +.

ou en notación alxébrica

y = 11.714 x + 1727,54

análise de resultados

Para decidir se o recibiu ecuación de regresión lineal mediante adecuadamente os distintos coeficientes de correlación (CMC) e determinación, así como proba e t-test de Fisher. Na táboa de regresión "Excel", cos resultados que actúan baixo os nomes múltiples R, R ao cadrado, F-t-estatísticas e estatísticas, respectivamente.

KMC R permite estimar a relación probabilística proximidade entre variables independentes e dependentes. O seu alto valor indica unha conexión moi forte entre a variable "Número do mes" e "N prezo do produto en rublos por 1 tonelada." Con todo, a natureza desta relación é descoñecida.

O cadrado do coeficiente de determinación ^R2 (RI) é unha característica numérica do a proporción de dispersión total, mostra unha dispersión de parte de datos experimentais, é dicir, Os valores da variable dependente correspondente a unha ecuación de regresión lineal. En este problema, este valor é de 84,8%, pf. E. Estatística cun elevado grao de precisión obtidos son descritos SD.

F-estatística, tamén coñecidos como criterio de Fisher emprega para avaliar a importancia da dependencia lineal ou refutando casos confirmando a súa existencia.

O valor da estatística t (proba t de Student) axuda a avaliar a importancia do coeficiente a calquera membro dependencia lineal descoñecido libre. Se o valor da proba t> t _CR, a hipótese dunha insignificancia ecuación lineal de libre termo é rexeitado.

Neste problema para un mandato libre a través de instrumentos "Excel" verificouse que t = 169,20903, e P = 2,89E-12, t. E. teñen unha probabilidade cero que os fieis serán rexeitou a hipótese da insignificancia do termo libre. Para coeficiente descoñecido en t = 5,79405, e P = 0,001158. Noutras palabras, a probabilidade de que unha hipótese correcta rexeitado vai insignificancia do coeficiente ao descoñecido, é de 0,12%.

Así, pódese argumentar que a ecuación de regresión lineal obtida de forma adecuada.

O problema da conveniencia de compra de accións

regresión múltiple realizouse en Excel usando a mesma ferramenta "Análise de Datos". Considere a aplicación específica.

Guía empresa «NNN» debe decidir se a mercar 20% das accións da JSC "MMM". Prezo do paquete (SP) é de 70 millóns de dólares americanos. Especialistas en «NNN» recolleron datos sobre transaccións similares. Decidiu-se avaliar o valor das accións en tales parámetros, expresados en millóns de dólares estadounidenses, tales como:

• contas a pagar (VK);
• volume de facturación anual (VO);
• recibir (VD);
• valor dos activos fixos (SOF).

Ademais, utiliza as débedas salariais de empresas (V3 U) en miles de dólares estadounidenses.

Os medios Excel procesador táboa de decisión

Primeiro ten que crear unha táboa de datos de entrada. É como segue:

seguinte:

• caixa chamada "análise de datos";
• seleccionado sección "regresión";
• o diálogo de entrada "intervalo Y» administrado alcance valores da variable dependente da columna G;
• prema na icona cunha frecha vermella á dereita da xanela "de entrada intervalo de X» e illado nun intervalo de folla de todos os valores da columna B, C, D, F.

Marque o punto "Nova folla" e prema en "OK".

Obter unha análise de regresión para esta tarefa.

Os resultados do estudo e as conclusións

"Collect" arredondada a partir dos datos presentados anteriormente na ecuación de regresión procesador Excel táboa de folla:

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

En forma matemática máis usual que pode ser escrita como:

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

Os datos para «MMM» JSC presentados na táboa seguinte:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102,5	535,5	45,2	41,5	21.55	64,72

Substituíndo os para a ecuación de regresión, obtivo unha figura de 64,72 millóns de dólares americanos. Isto significa que as accións de JSC "MMM" non debe mercar, porque o seu custo é moi caro en 70 millóns de dólares estadounidenses.

Como verás, o uso de follas de cálculo "Excel" ea ecuación de regresión permiso para tomar unha decisión informada sobre a conveniencia de transaccións moi específico.

Agora xa sabe o que é unha regresión. Exemplos a Excel, discutidos arriba, pode axudar na resolución de problemas prácticos de econometría.