Malprogreso en Excel: ekvacio ekzemploj. lineara malprogreso

Malprogresa analitiko - statistika studo metodo montri la dependecon de parametro de unu aŭ pli sendependaj variabloj. En la antaŭ-komputila epoko, ĝia uzo estis iom malfacila, precipe kiam ĝi venis al grandaj volumoj de datumoj. Hodiaŭ, lernante kiel konstrui malprogreso en Excel, vi povas solvi kompleksajn statistikaj problemoj en nur kelkaj minutoj. Jen specifaj ekzemploj de ekonomiko.

malprogreso tipoj

Tiu koncepto estis prezentita al la matematiko de Francis Galton en 1886. Malprogreso estas:

• lineara;
• parabola;
• potenco;
• eksponenciala;
• hiperbola;
• eksponenciala;
• logaritma.

EKZEMPLO 1

Konsideru la problemo de determini la dependecon de la nombro de demisioj de membroj de kunlaborantaro de la meza salajro en la 6 industriaj entreprenoj.

Tasko. Ses kompanioj analizis la meza monata salajro kaj la nombro de dungitoj, kiuj forlasis propra-vole. En tabular formo ni havas:

	A	B	C
1	X	Nombro de demisioj	salajro
2		y	30000 rubloj
3	1	60	35000 rubloj
4	2	35	40000 rubloj
5	3	20	45000 rubloj
6	4	20	50.000 rubloj
7	5	15	55000 rubloj
8	6	15	60000 rubloj

Por la problemo de determini la dependecon de la kvanto disiĝoj laboristojn de la meza salajro por 6 entreprenoj malprogresa modelo havas la formon de ekvacio Y = a ₀ + a ₁ x ₁ + ... + a _k x _k, kie x _mi - influi variablojn a _mi - malprogreso koeficientoj, ak - nombro de faktoroj.

Y por donita tasko - ĝi estas indikilo por fajro dungito, kontribuanta faktoro - la salajro, kiu estas signifita per X.

Realigi la potencon de "Excel" kalkultabelo

Malprogresa analitiko en Excel devus esti antaŭita de apliko al la ekzistanta tabelo datumoj korpigita funkcioj. Tamen, por ĉi tiuj celoj estas pli bone uzi tre utila aldoni-en "pakoj analizo". Por ebligi ĝin, vi devas:

• kun la langeton "Dosiero" iru al "Settings";
• en la fenestro kiu malfermas, elektu 'Aldoni ons';
• alklaku la butonon "Go", lokita en la dekstra malsupra de la linio "mastrumado";
• metita marko jam apud "Analizo ToolPak" kaj konfirmi vian agadon premante "OK".

Se farita ĝuste, la dekstra flanko de la "Datumoj" langeton, lokita super la laboroj folio "Excel", montras la deziratan butonon.

Lineara Regreso en Excel

Nun ke vi havas sub la mano ĉiujn necesajn virtuala iloj por econométricos kalkuloj, ni povas komenci trakti nian problemon. Por fari tion:

• butono estas alklakis la "Datumoj Analysis";
• alklaku la butonon "malprogreso" en la malfermita fenestro;
• langeto kiuj aperas enkonduki vicon da valoroj Y (la nombro de disiĝoj laboristoj) kaj X (lia esti);
• reaserti ilian agojn premante la «Bone» butonon.

Rezulte, la programo aŭtomate plenigas la nova folio kalkultabelo datumoj malprogresa analitiko. Atentu! En Excel, estas ŝanco por agordi la loko, kiun vi preferas por tiu celo. Ekzemple, ĝi povas esti la sama folio, kie la valoroj Y kaj X, aŭ eĉ nova libro, specife desegnita por la stokado de tiaj datumoj.

Malprogresa analitiko rezultoj por R-kvadrato

La Excel datumo akiris en la konsiderita ekzemplo datumoj havas la formon:

Unue, ni devus pagi atenton al la valoro de R-kvadratoj. Ĝi reprezentas la koeficiento de persistemo. En ĉi tiu ekzemplo, R-kvadrato = 0.755 (75,5%), m. E. La kalkulita parametroj de la modelo por klarigi la rilaton inter la parametroj konsiderata de 75,5%. La pli alta la valoro de la koeficiento de persistemo, la elektita modelo estas konsiderita esti pli utila por apartaj taskoj. Oni kredas al ĝuste priskribi la realan situacion en la R-kvadrata valoro super 0.8. Se la R-kvadrato <0,5, tiam malprogresa analitiko en Excel ne povas konsideri racian.

rilatumo analizo

Nombro 64,1428 montras kion estos la valoro de la Y, se ĉiuj variabloj xi en nia modelo estos reset. Alivorte, ĝi povas esti argumentis ke la valoro de la analizitaj parametron estas influita de aliaj faktoroj ol tiuj priskribitaj en la specifa modelo.

La sekva faktoro -0,16285 lokita en ĉelo B18, montras la gravan influon de variablo X al Y. Tio signifas, ke la meza salajro de oficistoj ene de la modelo influas la nombro de demisioj de la pezo de -0,16285, t. E. La grado de ĝia efiko ajn malgranda. La signo "-" indikas ke la koeficiento estas negativaj. Estas evidente, ĉar ĉiuj scias ke la pli salajro en la entrepreno, des malpli homoj esprimis deziron fini la kontrakton de sia okupo aŭ adiaŭita.

multoblajn malprogreso

Sub tiu termino rilatas al la komunikado ekvacio kun pluraj sendependaj variabloj de la formo:

y = f (x ₁ + x ₂ + ... x _m) + ε, kie y - estas trajto interpunkcio (la dependa variablo), kaj x _1, x _2, ... x _m - estas signoj faktoroj (sendependaj variabloj).

parametron taksado

Por multoblajn malprogreso (MR) estas farita uzante kvadrataj minimumoj metodo (LSM). Por linearaj ekvacioj de la formo Y = a + b ₁ x _{1 + ... + b m x m +} ε konstruado sistemo de normalaj ekvacioj (cm. Sube)

Por kompreni la principon de la metodo, ni konsideru la du-faktoro kazo. Tiam ni la situacio priskribita de la formulo

De ĉi tie, ni akiras:

kie σ - estas la varianco de la respektivaj trajto, reflektita en la indekso.

MNC estas aplikebla al la ekvacio MR al standartiziruemom skalo. En ĉi tiu kazo, ni akiras la ekvacion:

kiuj t _y, t _{x 1, ...} t _xm - standartiziruemye variabloj por kiuj duonaj valoroj estas 0; beta _i - normigita malprogreso koeficientoj kaj varianca devio - 1.

Bonvolu noti, ke ĉiu beta _i en ĉi tiu kazo difinita kiel la ununormigita kaj tsentraliziruemye do komparo inter konsiderita valida kaj akceptebla. Krome, ĝi akceptis realigi projekcio de faktoroj, forĵetante tiuj kiu havas la plej malalta valoroj de βi.

La problemo kun uzanta lineara malprogreso ekvacio

Supozu ke vi havas tablon de la dinamiko de la prezo de specifa produkto N por la lasta 8 monatoj. Estas necese decidi ĉu la akiraĵo de sia partio ĉe la prezo de 1850 rubloj. / T.

	A	B	C
1	la monato	nomo de la monato	prezo N
2	1	januaro	1750 rubloj por tuno
3	2	februaro	1755 rubloj por tuno
4	3	marto	1767 rubloj por tuno
5	4	aprilo	1760 rubloj por tuno
6	5	majo	1770 rubloj por tuno
7	6	junio	1790 rubloj por tuno
8	7	julio	1810 rubloj por tuno
9	8	aŭgusto	1840 rubloj por tuno

Por solvi tiun problemon en la tabular procesoron "Excel" necesa por uzi jam konata ekzemple ilo "Datumoj Analizo" prezentita supre. Sekva, elektu "Regreso" sekcio kaj starigis parametrojn. Ni devas memori ke en la "Eniro gamo Y» devus esti prezentita al gamo de valoroj de la dependa variablo (en ĉi tiu kazo la prezo de la varoj en specifaj monatoj de la jaro) kaj en la "Eniro intervalo X» - por sendependa (la monato). Ni konfirmas la ago alklakante «Bone». En nova laborfolio (okazo indikita), ni ricevi la datumoj por la regreso.

Ni konstruas sur ili lineara ekvacio de la formo y = ax + b, kie kiel la parametroj a kaj b estas la koeficientoj de la numero de linioj de la monato kaj nomo de la koeficientoj kaj «Y-intersekciĝo" linio de la folio kun la rezultoj de la malprogresa analitiko. Tiel, la regresión lineal ekvacio (EQ) 3 por la problemo povas esti skribita kiel:

La prezo de varoj N = 11,714 * 1727.54 monato nombro +.

aŭ en algebra notacio

y = 11.714 x + 1727,54

analizo de rezultoj

Decidi ĉu la ricevita adekvate regresión lineal ekvacio uzas la multoblajn korelacio koeficientoj (CMC) kaj determino kaj ankaŭ testo kaj Fiŝista t-testo. En la tabelo "Excel" malprogreso kun la rezultoj ili agas sub la nomoj multoblajn R, R-Placo, F-t-statistiko kaj statistiko, respektive.

KMC R ebligas taksi la proksimeco probableca rilato inter sendependaj kaj dependaj variabloj. Pli alta valoro indikas sufiĉe forta ligo inter la variablo "Numero de la monato" kaj "N produkto prezo en rubloj po 1 tuno." Tamen, la naturo de tiu rilato estas nekonata.

La kvadrato de la koeficiento de persistemo R ² (RI) estas nombra karakterizaĵon de la proporcio de totala dispelas kaj ĝi montras disjxetu de eksperimentaj datumoj parton, tio estas:, valoroj de la dependa variablo korespondas al lineara malprogreso ekvacio. En ĉi tiu problemo, tiu valoro estas 84,8%, mp. E. Statistiko kun alta grado de precizeco akiris estas priskribita SD.

F-statistikoj, ankaŭ konata kiel Fisher kriterio uzita por taksi la signifon de la lineara dependeco aŭ malpruvi hipotezo konfirmante lia ekzisto.

La valoro de t-statistiko (Studenta t testo) helpas taksi la signifon de la koeficiento kiam ajn libera nekonata lineara dependeco membro. Se la valoro de t-testo> t _cr, la hipotezo de lineara ekvacio sensignifeco de libera esprimo estas malakceptita.

En ĉi tiu problemo por libera esprimo tra instrumentoj "Excel" oni trovis, ke t = 169,20903 kaj p = 2,89E-12, t. E. Havi nulo probablo ke la fidela estos malakceptis la hipotezon de la sensignifeco de la libera esprimo. Por nekonataj koeficiento je t = 5,79405 kaj p = 0,001158. Alivorte, la probablo kiu malakceptis ĝusta hipotezo estos senvalorigante de la koeficiento por la nekonata, estas 0.12%.

Tiel, ĝi povas esti argumentis ke la akiris regresión lineal ekvacio adekvate.

La problemo de konsilindas aĉeti akciojn

Multoblaj malprogreso estis farita en Excel uzas la saman "Datumoj Analizo" ilo. Konsideru la specifa apliko.

Gvidilo kompanio «NNN» devas decidi ĉu aĉeti 20% de la agoj de JSC «MMM». Pako prezo (SP) estas 70 milionoj da usonaj dolaroj. Specialistoj de «NNN» kolektis datumoj sur similaj transakcioj. Estis decidite por taksi la valoron de akcioj en tiaj parametroj, esprimita en milionoj da usonaj dolaroj, kiel ekzemple:

• payables (VK);
• jara spezo volumo (VO);
• receivables (VD);
• valoro de fiksaj aktivoj (SOF).

Krome, uzi la salajro ŝuldoj de entreprenoj (V3 U) en miloj da usonaj dolaroj.

La decido tablo procesoron Excel rimedoj

Unue vi devas krei tabelon de enigaĵo. Ĝi estas la jena:

sekva:

• alvoko skatolo "datumoj analizo";
• elektitaj "Regreso" sekcio;
• la fenestro "Importa intervalo Y» administris gamo dependa variablo valoroj de kolumno G;
• alklaku la piktogramon kun ruĝa sago dekstre de la fenestro "Importa intervalo X» kaj izolita sur folio gamo de ĉiuj valoroj de kolumno B, C, D, F.

Marko la punkto "Nova laborfolio" kaj alklaku "Bone".

Akiri malprogresa analitiko por tiu tasko.

La studo rezultoj kaj konkludoj

"Kolektu" rondeta de la datumoj prezentitaj supre sur la folio tablo Excel procesoron malprogreso ekvacio:

SD = 0,103 * SOF + 0,541 * VO - 0.031 * VK + 0,405 * VD + 0,691 * VZP - 265.844.

En la pli kutima matematika formo ĝi povas esti skribita kiel:

y = 0.103 * x1 + 0,541 * x 2 - 0.031 * x3 + 0,405 * x4 + 0,691 * x5 - 265.844

Datumoj por «MMM» JSC prezentitaj en la tabelo malsupre:

SOF, USD	VO, USD	VK, USD	VD: USD	VZP, USD	JV: USD
102.5	535,5	45.2	41.5	21,55	64,72

Anstataŭiganta ilin en la regresian ekvacio, akiris figuro de 64,72 milionoj da usonaj dolaroj. Tio signifas, ke la agoj de JSC «MMM» ne devus aĉeti, ĉar ilia kosto estas tute trokarigita je 70 milionoj da usonaj dolaroj.

Kiel vi povas vidi, la uzo de kalkultabelo "Excel" kaj la malprogreso ekvacio permesita fari informita decidon pri konsilindas tute specifaj transakcioj.

Nun vi scias kia regreso. Ekzemploj por Excel, diskutita supre, helpos vin solvi praktikajn problemojn de ekonometrio.