Hnignun í Excel: dæmum jöfnu. línulega aðhvarfsgreiningu

Aðhvarfsgreining - tölfræðileg rannsókn aðferð til að sýna ósjálfstæði viðfang af einni eða fleiri breytur. Í the pre-tölva tímabil, notkun þess hefur verið frekar erfitt, sérstaklega þegar það kom að mikið magn gagna. Í dag, að læra hvernig á að byggja upp afturför í Excel er hægt að leysa flókin tölfræðileg vandamál í örfáum mínútum. Hér að neðan eru tiltekin dæmi um hagfræði.

aðhvarfsgreining tegundir

Þetta hugtak var kynnt stærðfræði eftir Francis Galton árið 1886. Hnignun er:

• línuleg;
• fleygboga;
• máttur;
• veldisvísis;
• breiðboga;
• veldisvísis;
• lógaritmískum.

EXAMPLE 1

Íhuga vandamál að ákvarða háður fjölda resignations í starfsmenn af meðaltali launa í 6 atvinnufyrirtækja.

Verkefni. Sex félög hafa greint meðaltali mánaðarlaun og fjölda starfsmanna sem hætta sjálfviljugir. Í töfluformi sem við höfum:

	A	B	C
1	X	Fjöldi resignations	laun
2		Y	30000 rúblur
3	1	60	35000 rúblur
4	2	35	40000 rúblur
5	3	20	45000 rúblur
6	4	20	50.000 rúblur
7	5	15	55000 rúblur
8	6	15	60000 rúblur

Fyrir því vandamáli að ákvarða háður inn upphæðina sem aðskilnaður starfsmanna frá meðallaun fyrir 6 fyrirtæki aðhvarfellkan hefur formi jöfnu Y = a ₀ + a ₁ x ₁ + ... + a _k x _k-, þar sem x _I - sem hafa áhrif á breytur, a _i - aðhvarfsstuðul hvaríhraðakúrvu, ak - fjölda þátta.

Y fyrir tiltekið verkefni - það er vísir að skjóta starfsmann, meðorsakir - laun, sem er táknað með X.

Virkjun krafti "Excel" töflureikni

Aðhvarfsgreining í Excel skal undan umsókn um gildandi töflu gögn innbyggður-í aðgerðir. Hins vegar í þessum tilgangi það er betra að nota mjög gagnlegt Bæta við í "Packet greiningu". Til að gera það, þú þarft að:

• með tab "File" fara "Stillingar";
• í glugganum sem opnast, veldu 'Bæta við-ons';
• smelltu á hnappinn "fara", staðsett á the botn réttur af the lína "stjórnun";
• setja merkið við hliðina á "Analysis ToolPak" og staðfesta aðgerðina með því að ýta á "OK".

Ef gert á réttan hátt, á hægri hlið "Data" flipanum fyrir ofan vinnuplaggs "Excel", sýnir hnappinn óskað.

Aðhvarfsgreining í Excel

Nú þegar þú hefur á hendi allar nauðsynlegar raunverulegur verkfæri fyrir tölfræðilega útreikninga, getum við byrjað að takast á vandamál okkar. Til að gera þetta:

• smellt er á "Data Analysis";
• smelltu á hnappinn "afturför" í opinn glugga;
• a flipi sem virðist að taka upp svið gilda Y (fjöldi Seperations starfsmanna) og X (laun þeirra);
• staðfesta aðgerðir sínar með því að ýta á «Ok» hnappinn.

Þess vegna, the program vilja á sjálfvirkan hátt fylla nýja blaði töflureikni gögn aðhvarfsgreiningu. Gaum! Í Excel, það er tækifæri til að setja fram sem þú kýst í þessum tilgangi. Til dæmis getur það verið sama blaði, þar sem gildi Y og X, eða jafnvel ný bók, sérstaklega hönnuð fyrir geymslu á slíkum gögnum.

Aðfallsgreiningin Niðurstöður R-square

Excel Gögnin, sem fást í talin td gögn hafa mynd:

Fyrst af öllu, ættum við að borga eftirtekt til the gildi R í öðru veldi. Það táknar ákvörðunarstuðul. Í þessu dæmi, R-Square = 0,755 (75,5%), m. E. Útreiknuðu breytur líkan til að útskýra tengslin á milli Mælistærðirnar sem með því að 75,5%. Því hærra gildi á ákvörðunarstuðul þá er virki líkanið er talin vera meira gagni fyrir tiltekin verk. Það er talið að rétt að lýsa raunverulegar aðstæður í R--Square gildis hér á undan 0,8. Ef R-veldi <0,5, þá aðhvarfsgreiningu þar sem Excel getur ekki talist getur eðlilegt.

hlutfall greining

Fjöldi 64,1428 sýnir hvað verður verðmæti Y, ef allar breytur xi í líkaninu okkar mun vera endurstilla. Með öðrum orðum, það má segja að verðmæti greindu breytu er undir áhrifum af öðrum þáttum en þeim sem lýst er í tiltekna líkan.

Næsta þáttur -,16285 staðsett í hólfi B18, sýnir mikilvæg áhrif breytu X til Y. Þetta þýðir að meðallaun starfsmanna innan líkansins hefur áhrif á fjölda brottfall úr þyngd -0.16285, t. E. hve áhrif hennar á öllum lítil. The merki "-" táknar að stuðullinn er neikvæð. Það er augljóst, þar sem við vitum öll að meiri laun í fyrirtækinu, því minna fólk hafa lýst yfir löngun til að segja ráðningarsamningi eða vísað frá.

Fjölbreytuaðhvarfs

Undir þessum tíma átt við samskipti jöfnu með mörgum sjálfstæðum breytur í formi:

Y = f (x ₁ + x ₂ + ... x _m) + ε, þar sem Y - er a lögun skora (sem háða breytan), og x _1, x _2, ... x _m - eru merki vaxtarþáttum (frumbreytur).

parameterestimering

For aðhvarfsgreiningar (MR) er hún framkvæmd með því að nota aðferð minnstu kvaðrata (LSm). Fyrir línulegar jöfnur á forminu y = a + b ₁ x _{1 + ... + b m X m +} ε að byggja upp kerfi við venjulegt jöfnur (cm. Hér fyrir neðan)

Til að skilja regluna um aðferðina, hugleiðum við tveggja-þáttur mál. Þá höfum við ástandið lýst með formúlu

Þess vegna fáum við:

hvar σ - er dreifni viðkomandi eiginleika, endurspeglast í vísitölunni.

MNC er við að jafna MR í standartiziruemom mælikvarða. Í þessu tilfelli, fáum við jöfnuna:

þar sem _{y t,} t _{x 1, ...} T _XM - standartiziruemye breytur sem meðalgildi eru 0; • P _i - stöðluð aðhvarfsstuðul hvaríhraðakúrvu og staðalfrávik - 1.

Vinsamlegast athugið að allar beta _i í þessu tilfelli skilgreind sem sú eðlileg og tsentraliziruemye, því samanburður milli a teljast gild og ásættanlegt. Þar að auki er það tekið að framkvæma skimun af þáttum, hent þá sem hafa lægstu βi.

Vandinn við að nota línulegu aðhvarfsjöfnunnar

Segjum sem svo að þú sért með töflu á gangverki verð á tiltekinni vöru N fyrir síðustu 8 mánuði. Það er nauðsynlegt að ákveða hvort kaupa á aðila hans á genginu 1850 rúblur. / T.

	A	B	C
1	mánuð	Heiti mánaðarins	Verð N
2	1	janúar	1750 rúblur á tonn
3	2	febrúar	1755 rúblur á tonn
4	3	mars	1767 rúblur á tonn
5	4	apríl	1760 rúblur á tonn
6	5	maí	1770 rúblur á tonn
7	6	júní	1790 rúblur á tonn
8	7	júlí	1810 rúblur á tonn
9	8	ágúst	1840 rúblur á tonn

Til að leysa þetta vandamál í tabular örgjörva "Excel" sem þarf til að nota þegar þekkt til dæmis tól "Data Analysis" kynnt hér fyrir ofan. Næst skaltu velja "Regression" hluti og setja breytur. Við verðum að muna að í "Input bilinu Y» ætti að vera kynnt fyrir ýmsum gildum framfæri breytu (í þessu tilviki verð á vörum í tilteknum mánuði ársins) og í "Input bil X» - fyrir sjálfstætt (mánuður). Við staðfesta með því að smella «Ok». Í nýrri verkstæði (ef svo er tilgreindur), fá við gögn fyrir aðhvarfsgreiningu.

Við erum að byggja á þeim línulega jöfnu á forminu y = ax + B, þar sem eins og breytum A og B eru stuðlarnir frá línu fjölda mánaðar og nafni stuðlum og «Y-gatnamótum" lína þynnunnar með niðurstöðum úr aðhvarfsgreiningu. Þannig, línulegu aðhvarfsjöfnunnar (EQ) 3 fyrir vandamálinu má setja fram sem:

Verð á vöru N = 11.714 * 1727,54 mánuði númer +.

eða í reiknisegða tákn

y = 11714 x + 1727,54

greiningu á niðurstöðum

Til að ákveða hvort fengið nægilega línulega aðhvarfsgreiningu jöfnu með því að nota margar stuðlana fylgni (CMC) og ákvörðun auk próf og Fisher t-próf. Í töflunni "Excel" aðhvarfsgreiningu með niðurstöðum sem þeir starfa undir nafni margfeldi R, R-torg, F-t-tölfræði og tölfræði, í sömu röð.

KMC R gerir að meta nálægð probabilistic tengsl óháðra og háðra breytur. hátt gildi hennar gefur til kynna sterka tengingu á milli breytilegs "Fjöldi mánaðarins" og "N Vöruverð í rúblur á 1 tonn." Hins vegar er eðli þessa sambands er óþekkt.

Annað veldi af ákvörðunarstuðul R ² (RI) er tölulegur einkennandi fyrir hlutfalli af heildar dreifa út og sýnir hafa dreifst á tilraunagögnum um hluta, þ.e.a.s., values of framfæri breytu sem svarar til línulegu aðhvarfsjöfnunnar. Í þessu dæmi er, þetta gildi er 84,8%, bm. E. Statistics með mikla nákvæmni sem fengust er lýst SD.

F-statistics, einnig þekktur sem Fisher viðmiðun notuð til að meta mikilvægi línulega ósjálfstæði eða disproving tilgátuna sem staðfestir tilvist hennar.

Verðmæti t-tölfræði (T nemanda próf) hjálpar meta mikilvægi stuðlinum hvenær frjálsa óþekktum línulegri fíknar félagi. Ef verðmæti t-prófi> t _cr, að Tilgáta línulegri jöfnu insignificance frjálsra tíma er hafnað.

Í þessu vandamáli fyrir frjáls senn gegnum hljóðfæri "Excel" það kom í ljós að t = 169,20903, og p = 2,89E-12, t. E. Hafa núll líkur á að hinir trúuðu verði hafnað tilgátu um insignificance frjálsa tíma. Fyrir óþekktum stuðlinum við t = 5,79405, og p = 0,001158. Með öðrum orðum, líkurnar á því að hafna rétt tilgáta mun lítilvægur stuðulsins fyrir hið óþekkta, er 0,12%.

Þannig má segja að fæst línulegri aðhvarfsgreiningu jöfnu nægilega.

Vandamálið um réttmæti þess að kaupa hlutabréf

Fjölbreytuaðhvarfs var gerð í Excel með því að nota sömu "Data Analysis" tól. Lítið á tiltekið forrit.

Leiðbeiningar fyrirtæki «NNN» verður að ákveða hvort að kaupa 20% hlut í JSC «MMM». Pakki verð (SP) er 70 milljónir Bandaríkjadala. Sérfræðingar «NNN» safnað gögnum um sams konar viðskiptum. Það var ákveðið að meta verðmæti hlutabréfa á slíkum breytum, tilgreind í milljónum Bandaríkjadala, svo sem:

• skammtímaskuldir (VK);
• árleg velta bindi (VO);
• kröfur (VD);
• gildi fastafjármuna (SOF).

Að auki, nota launa skuldir fyrirtækja (V3 U) í þúsundum Bandaríkjadala.

Ákvörðunin borð örgjörva Excel leið

Fyrst þú þarft til að búa til borð af gögnum inntak. Það er eins og hér segir:

Next:

• kalla kassi "greiningu gagna";
• valið "Hnignun" hlutanum;
• Í glugganum "Inntak interval Y» gefið á bilinu háða breytan gildi eru frá g-dálki;
• smellur á táknið með rauðri ör til hægri á texta í "Input bilinu x» og einangrað á blaði svið af fyrir öll möguleg gildi dálki B, C, D, F.

Mark punkturinn "New verkstæði" og smelltu á "Ok".

Fá aðhvarfsgreiningu fyrir þetta verkefni.

Niðurstöður rannsóknarinnar og niðurstöður

"Safna" rúnnuð út frá gögnunum var hér að framan á blaðinu borðið Excel örgjörva aðhvarfsjöfnunnar:

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

Í meira venjulegum stærðfræði formi það er hægt að skrifa sem:

y = 0.103 * x1 + 0541 * x2 - 0031 * X3 + 0405 * x4 + 0691 * x5 - 265844

Gögn fyrir «MMM» JSC fram í töflunni hér á eftir:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102,5	535,5	45,2	41.5	21,55	64,72

Skipta þeim í aðhvarfsjöfnunnar, fengið mynd af 64.72 milljónir Bandaríkjadala. Þetta þýðir að hlutabréf JSC «MMM» ættir ekki að kaupa, vegna þess að kostnaður þeirra er alveg overpriced á 70 milljónir Bandaríkjadala.

Eins og þú geta sjá, notkun töflureiknis "Excel" og afturför jöfnu heimilt að taka upplýsta ákvörðun um hvort ráðlegt alveg sérstaka færslu.

Nú þú veist hvað afturför. Dæmi í Excel, rætt hér að ofan, mun hjálpa þér að leysa hagnýt vandamál hagrannsóknir.