Regressió en Excel: exemples equació. regressió lineal

L'anàlisi de regressió - un mètode d'estudi estadístic per mostrar la dependència d'un paràmetre d'una o més variables independents. En l'era pre-ordinador, el seu ús ha estat força difícil, sobretot quan es tracta de grans volums de dades. Avui dia, l'aprenentatge de com construir una regressió en Excel, pot resoldre els problemes estadístics complexos en tan sols uns minuts. A continuació es presenten exemples específics de l'economia.

tipus de regressió

Aquest concepte va ser introduït a les matemàtiques per Francis Galton el 1886. La regressió és:

• lineal;
• parabòlica;
• de potència;
• exponencial;
• hiperbòlica;
• exponencial;
• logarítmica.

exemple 1

Penseu el problema de determinar la dependència del nombre de renúncies dels membres del personal del salari mitjà en les 6 empreses industrials.

Tasca. Sis empreses han analitzat el salari mensual mitjà i el nombre d'empleats que deixen voluntàriament. En forma de taula tenim:

	la	B	C
1	X	Nombre de renúncies	salari
2		i	30000 rubles
3	1	60	35000 rubles
4	2	35	40000 rubles
5	3	20	45000 rubles
6	4	20	50.000 rubles
7	5	15	55000 rubles
8	6	15	60000 rubles

Per al problema de determinar la dependència dels treballadors quantitat Separacions al salari mitjà de 6 empreses model de regressió té la forma de l'equació I = a ₀ + a ₁ x ₁ + ... + a _k x _k, on x _i - les variables d'influència, a _i - coeficients de regressió, a k - nombre de factors.

I per a una tasca determinada - és un indicador per acomiadar un empleat, un factor que contribueix - el salari, que es denota per X.

Aprofitar el poder del full de càlcul "Excel"

L'anàlisi de regressió en Excel ha d'anar precedida d'una aplicació a les dades de les taules existents funcions integrades. No obstant això, a aquests efectes, és millor utilitzar un complement "anàlisi de paquets" molt útil. Per activar-lo, cal:

• amb el "arxiu" fitxa aneu a "Configuració";
• a la finestra que s'obre, selecciona 'Add-ons';
• feu clic al botó "Go", que es troba a la part inferior dreta de la línia "gestió";
• posar una marca de verificació al costat de "Eines per a anàlisi" i confirmeu l'acció prement la tecla "OK".

Si es fa correctament, la part dreta de la pestanya "Dades", que es troba per sobre del full de treball "Excel", mostra el botó desitjat.

Regressió lineal en Excel

Ara que té a la seva disposició totes les eines virtuals necessaris per als càlculs economètrics, podem començar a resoldre el nostre problema. Per a això:

• es fa clic al "Anàlisi de dades";
• feu clic al botó "regressió" a la finestra oberta;
• una pestanya que apareix a introduir un interval de valors de I (el nombre de treballadors cessaments) i X (el seu salari);
• reafirmar les seves accions fent clic al botó «Ok».

Com a resultat, el programa va a omplir automàticament el nou anàlisi de regressió de dades de fulla de fulla de càlcul. Fer atenció! En Excel, hi ha una oportunitat per establir el lloc que prefereixi per a aquest propòsit. Per exemple, pot ser la mateixa fulla, on els valors de Y i X, o fins i tot un nou llibre, dissenyats específicament per a l'emmagatzematge d'aquestes dades.

els resultats de l'anàlisi de regressió per a R-quadrat

Les dades d'Excel obtinguts en les dades d'exemple considerat tenen la forma:

En primer lloc, hem de prestar atenció al valor de R quadrat. Representa el coeficient de determinació. En aquest exemple, R-quadrat = 0,755 (75,5%), m. E. Els paràmetres calculats del model per explicar la relació entre els paràmetres considerats per 75,5%. Com més gran sigui el valor del coeficient de determinació, el model seleccionat es considera que és més útil per a tasques particulars. Es creu per descriure correctament la situació real en el valor de R al quadrat per sobre de 0,8. Si l'R-quadrat <0,5, a continuació, una anàlisi de regressió en Excel no pot considerar raonable.

anàlisi de la relació

Nombre 64.1428 mostra el que serà el valor de la I, si tot el xi les variables en el nostre model es posarà a zero. En altres paraules, es pot argumentar que el valor del paràmetre analitzat està influenciada per altres factors diferents dels descrits en el model específic.

El següent factor -,16285 situat a B18 cel·lular, mostra la influència important de la variable X a Y. Això vol dir que el salari mitjà dels empleats dins del model afecta el nombre de dimissions pel pes de -,16285, t. I. El grau del seu impacte en absolut petita. El signe "-" indica que el coeficient és negatiu. És obvi, ja que tots sabem que com més salari a l'empresa, menys persones han expressat el seu desig de rescindir el contracte de treball o acomiadats.

regressió múltiple

Sota aquest terme es refereix a l'equació de comunicació amb diverses variables independents de la forma:

i = f (x ₁ + x ₂ + ... x _m) + ε, on i - és una puntuació de característiques (la variable dependent), i x _1, x _2, ... x _m - són signes factors (variables independents).

estimació de paràmetres

Per a la regressió múltiple (MR) es realitza usant un mètode de mínims quadrats (LSM). Per a les equacions lineals de la forma I = a + b ₁ x _{1 + ... + b m x m +} ε construcció d'un sistema d'equacions normals (cm. A continuació)

Per entendre el principi del mètode, es considera el cas de dos factors. Llavors tenim la situació descrita per la fórmula

Per tant, s'obté:

on σ - és la variància de la funció respectiva, que es reflecteix en l'índex.

MNC és aplicable a la equació de MR a standartiziruemom escala. En aquest cas, obtenim l'equació:

en la qual T _i, t _{x 1, ...} t _xm - standartiziruemye variables per les quals els valors mitjans són 0; ß _i - coeficients de regressió estandarditzats i desviació estàndard - 1.

Recordeu que tots els ß _i en aquest cas es defineix com el normalitzat i tsentraliziruemye, per tant, una comparació entre un considerat vàlid i acceptable. A més, s'accepta per dur a terme la detecció de factors, descartant aquelles que tenen els valors més baixos de βi.

El problema amb l'ús de l'equació de regressió lineal

Suposeu que té una taula de la dinàmica del preu d'un producte N particular per als últims 8 mesos. Cal decidir si l'adquisició del seu partit al preu de 1.850 rubles. / T.

	la	B	C
1	el mes	nom del mes	preu N
2	1	gener	1750 rubles per tona
3	2	febrer	1755 rubles per tona
4	3	març	1767 rubles per tona
5	4	abril	1760 rubles per tona
6	5	maig	1770 rubles per tona
7	6	juny	1790 rubles per tona
8	7	juliol	1810 rubles per tona
9	8	agost	1840 rubles per tona

Per resoldre aquest problema en el processador de taula "Excel" necessari per utilitzar ja conegut, per exemple, l'eina "Anàlisi de Dades", presentat anteriorment. A continuació, escolliu la secció "Regressió" i els paràmetres establerts. Cal recordar que al "Rang d'entrada I» ha de ser introduït en un rang de valors de la variable dependent (en aquest cas el preu de la mercaderia en mesos específics de l'any) i en la "Entrada interval de X» - per a una organització independent (el mes). Confirmem l'acció fent clic a «Ok». En un nou full de treball (si així s'indica), obtenim les dades de la regressió.

Estem construint-hi equació lineal de la forma y = ax + b, on com els paràmetres a i b són els coeficients de la línia número del mes i el nom dels coeficients i la línia «I-intersecció" del full amb els resultats de l'anàlisi de regressió. Per tant, l'equació de regressió lineal (EQ) 3 pel problema pot ser escrit com:

El preu dels béns N = 11.714 * 1.727,54 mesos + nombre.

o en la notació algebraica

i = 11,714 x + 1.727,54

anàlisi dels resultats

Per decidir si la van rebre equació de regressió lineal adequadament utilitzant els coeficients de correlació múltiple (CMC) i determinació, així com la prova i la prova t de Fisher. A la taula de regressió "Excel" amb els resultats que actuen sota els noms múltiples R, R-quadrat, F-t-estadístiques i estadístiques, respectivament.

KMC R permet estimar la relació probabilística proximitat entre les variables independents i dependents. El seu alt valor indica una connexió prou forta entre la variable "Nombre del mes" i "N Preu del producte en rubles per 1 tona." No obstant això, la naturalesa d'aquesta relació és desconeguda.

El quadrat del coeficient de determinació R ² (RI) és una característica numèric de la proporció de dispersió total i mostra una dispersió de la porció de dades experimentals, és a dir, els valors de la variable dependent que correspon a una equació de regressió lineal. En aquest problema, aquest valor és 84,8%, pf. E. Estadístiques amb un alt grau de precisió obtinguts es descriuen SD.

F-estadístiques, també coneguts com a criteri de Fisher utilitzen per avaluar la importància de la dependència lineal o refutar la hipòtesi de confirmar la seva existència.

El valor de t-estadístic (prova de la t d'Student) ajuda a avaluar la significació del coeficient en qualsevol membre de dependència lineal desconeguda lliure. Si el valor de la prova t> t _cr, es rebutja la hipòtesi d'una insignificança equació lineal de la llibertat d'expressió.

En aquest problema per un període lliure a través dels instruments "Excel" es va trobar que t = 169,20903, ip = 2,89E-12, t. I. no hi ha probabilitat que els fidels es rebutja la hipòtesi de la insignificança del terme lliure. Per coeficient desconegut en t = 5,79405, i p = 0,001158. En altres paraules, la probabilitat que una hipòtesi correcta rebutjat serà insignificança del coeficient per a la desconeguda, és de 0,12%.

Per tant, es pot argumentar que l'equació de regressió lineal obtingut adequadament.

El problema de la conveniència de la compra d'accions

La regressió múltiple es va realitzar en Excel fent servir la mateixa eina "Anàlisi de Dades". Penseu en la aplicació específica.

Guia empresa «NNN» ha de decidir si compra el 20% de les accions de JSC «MMM». El preu del paquet (SP) és de 70 milions de dòlars americans. Especialistes de «NNN» van recol·lectar dades sobre transaccions similars. Es va decidir avaluar el valor de les accions de tals paràmetres, expressat en milions de dòlars nord-americans, com ara:

• comptes a pagar (VK);
• volum de facturació anual (VO);
• cobrar (VD);
• valor dels actius fixos (SOF).

A més, utilitzar els deutes salarials d'empreses (V3 O) en milers de dòlars americans.

Els mitjans de processador d'Excel taula de decisió

El primer que necessita per crear una taula de dades d'entrada. És com segueix:

següent:

• caixa anomenada "anàlisi de dades";
• secció "Regressió" seleccionat;
• la finestra de "I» administrat abast valors de les variables dependents d'interval d'entrada de la columna G;
• feu clic a la icona amb una fletxa vermella a la dreta de la finestra "Entrada interval de X» i aïllat en un rang de fulls de tots els valors de la columna B, C, D, F.

Marqueu el punt "Nou full de treball" i feu clic a "D'acord".

Obtenir una anàlisi de regressió per a aquesta tasca.

Els resultats de l'estudi i conclusions

"Suma" arrodonit partir de les dades presentades anteriorment en la taula de fulla equació de regressió processador Excel:

SD = 0,103 * 0,541 * SOF + VO - 0031 * 0405 * VK + VD + 0691 * VZP - 265.844.

En la forma matemàtica més habitual que es pot escriure com:

i = 0,103 * x1 + 0,541 * x2 - 0031 * x3 + x4 0405 * + 0691 * x5 - 265.844

Les dades per a «MMM» JSC presentats en la següent taula:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45.2	41.5	21.55	64.72

Substituint en l'equació de regressió, que s'obté una figura de 64.720.000 de dòlars americans. Això significa que les accions de JSC «MMM» no han de comprar, a causa de que el seu cost és bastant car a 70 milions de dòlars americans.

Com es pot veure, l'ús del full de càlcul "Excel" i l'equació de regressió permet prendre una decisió informada sobre la conveniència de transacció bastant específica.

Ara vostè sap el que és una regressió. Els exemples de superació, discutits anteriorment, que l'ajudarà en la solució de problemes pràctics de l'econometria.