Հետընթաց է Excel. Հավասարման օրինակներով. գծային ռեգրեսիայի

Հետադիմություն վերլուծություն - վիճակագրական ուսումնասիրություն մեթոդը է ցույց տալ կախվածությունը մի պարամետր մեկ կամ ավելի անկախ փոփոխականների. Նախընտրական համակարգչային դարաշրջանի, դրա օգտագործումը եղել է բավականին դժվար է, հատկապես, երբ այն գալիս է մեծ ծավալների տվյալների: Այսօր, սովորում, թե ինչպես պետք է կառուցել հետընթացը Excel- ում, դուք կարող եք լուծել բարդ վիճակագրական խնդիրներ ընդամենը մի քանի րոպե: Ստորեւ բերված են կոնկրետ օրինակներ են տնտեսագիտության.

ռեգրեսիայի տեսակները

Այս հայեցակարգը ներկայացվել է մաթեմատիկայի Ֆրենսիս Galton - ի 1886 թ. Հետընթացը:

• գծային;
• Այլաբանական;
• Power;
• exponential;
• hyperbolic;
• exponential;
• logarithmic:

ՕՐԻՆԱԿ 1

Խնդիրը համարում որոշելու կախվածությունը թվի հրաժարականների անդամների աշխատակազմի միջին աշխատավարձի մեջ 6 արդյունաբերական ձեռնարկություններում:

Խնդիր է. Վեց ընկերությունները վերլուծել է միջին ամսական աշխատավարձը եւ աշխատողների թիվը, ովքեր պաշտոնից ազատվել կամովին: Է աղյուսակային տեսքով մենք ունենք:

	A	B	C
1	X	Միավորների քան-հրաժարականների	աշխատավարձ
2		y	30000 ռուբլի
3	1	60	35000 ռուբլի
4	2	35	40000 ռուբլի
5	3	20	45000 ռուբլի
6	4	20	50000 ռուբլի
7	5	15	55000 ռուբլի
8	6	15	60000 ռուբլի

Խնդրի որոշելու կախվածությունը գումարը բաժանումները աշխատողների միջին աշխատավարձի համար 6 ձեռնարկությունների ռեգրեսիոն մոդելի ունի ձեւ հավասարման Y = a ₀ + է ₁ x ₁ + ... + մի _k x _k, որտեղ x _i - influencing փոփոխականները, մի _i - ռեգրեսիայի գործակիցները, ak - գործոններ:

Y համար տվյալ առաջադրանքի - դա մի ցուցանիշ է կրակել աշխատակից, որը նպաստող գործոն է, աշխատավարձը, որը մատնանշում է X.

Harnessing իշխանության «Excel» աղյուսակների

Հետադիմություն վերլուծություն եւ Excel պետք է նախորդի դիմում է առկա աղյուսակի տվյալների ներկառուցված գործառույթների. Սակայն, այդ նպատակների համար ավելի լավ է օգտագործել շատ օգտակար ավելացնել- in «փաթեթային վերլուծություն»: , Որպեսզի այն, դուք պետք է:

• հետ էջանիշը «Պատկեր» գնալ «կայանքներով».
• պատուհանից, որ բացվի, ընտրել «Հավելումների ';
• սեղմեք կոճակը «Go», որը գտնվում է ներքեւի աջ գծի «կառավարման».
• դնում չեկային նշանի կողքին «Վերլուծություն ToolPak» եւ հաստատեք ձեր ակցիա սեղմելով «OK»:

Եթե արել է ճիշտ, որ աջ կողմը «Դատա» ներդիրում, որը գտնվում է բարձր Աշխատանքային թերթիկի «Excel», ցույց է տալիս, ցանկալի կոճակը:

Գծային ռեգրեսիոն Excel- ում

Այժմ, որ դուք ունեք վրա ձեռքը բոլոր անհրաժեշտ վիրտուալ գործիքների համար էկոնոմետրիկ հաշվարկների, մենք կարող ենք սկսել անդրադառնալ մեր խնդիրը. Որպեսզի դա անել:

• կոճակը է կտտացրել է «տվյալների վերլուծության».
• Սեղմիր կոճակը «ռեգրեսիայի» բաց պատուհանից.
• մի թերթ, որը հայտնվում է ներկայացնել մի շարք արժեքների Y (թիվը բաժանումները աշխատողների) եւ X (իրենց աշխատավարձը),
• վերահաստատում են իրենց գործողությունները, սեղմելով «OK» կոճակը:

Որպես հետեւանք, ծրագիրը ավտոմատ կերպով լրացնել նոր թերթ աղյուսակի տվյալները հետադիմություն վերլուծություն: Ուշադրություն Excel- ում, կա հնարավորություն է սահմանել տեղը, որ դուք նախընտրում այդ նպատակի համար. Օրինակ, դա կարող է լինել նույնը թերթ, որտեղ արժեքները Y եւ X, կամ նույնիսկ մի նոր գիրք, հատուկ նախագծված պահեստավորման համար այդպիսի տվյալների:

Հետընթաց վերլուծության արդյունքները R-հրապարակում

The Excel ստացված տվյալների համարվում օրինակ տվյալների ունեք ձեւը:

Առաջին հերթին, մենք պետք է ուշադրություն դարձնել, որ արժեքի R-Squared. Այն իրենից ներկայացնում է գործակիցը որոշման: Այս օրինակում, R-քառակուսի = 0.755 (75.5%), մ. Ե հաշվարկված մոդելի պարամետրերը է բացատրել հարաբերությունները պարամետրերի համարվում է 75.5%: Որքան բարձր է արժեքը գործակցի որոշման, ընտրված մոդելը համարվում է ավելի օգտակար որոշակի առաջադրանքներ. Ենթադրվում է, ճիշտ նկարագրել իրական վիճակը ժամը R քառակուսի արժեքը գերազանցում 0.8. Եթե R քառակուսի <0.5, ապա հետադիմություն վերլուծություն Excel- ում չի կարելի համարել ողջամիտ:

հարաբերակցությունը վերլուծություն

Միավորների քան-64,1428 ցույց է տալիս, թե ինչ է լինելու արժեքը Y, եթե բոլոր փոփոխականները xi մեր մոդելը կլինի զրոյացնել. Այլ կերպ ասած, կարելի է պնդել, որ արժեքը վերլուծվում պարամետրի վրա ազդում է նաեւ այլ գործոնների, քան նրանք, ովքեր նկարագրված է կոնկրետ մոդելի.

Հաջորդ գործոնը -0,16285 գտնվում է բջջային B18, ցույց է տալիս, որ կարեւոր ազդեցությունը փոփոխական X Ե Սա նշանակում է, որ միջին աշխատավարձը կազմում մոդելի ազդում համարը հրաժարականների է ծանրությունից -0.16285, t. E. աստիճանը իր ազդեցության բոլորը փոքր. The նշանը »-« ցույց է տալիս, որ այդ գործակիցը բացասական: Ակնհայտ է, քանի որ մենք բոլորս գիտենք, որ ավելի շատ աշխատավարձը ձեռնարկության, այնքան քիչ մարդ ցանկություն հայտնեց դադարեցնելու պայմանագիրը զբաղվածության կամ ազատվել:

բազմակի ռեգրեսիայի

Սույն տերմինը վերաբերում է կապի հավասարման հետ մի քանի անկախ փոփոխականների ձեւով:

y = f (x ₁ + x ₂ + ... x _մ) + ε, որտեղ y - ը առանձնահատկությունն հաշիվը (կախյալ փոփոխական), եւ x _1, x _2, ... x _մ են նշաններ գործոնները (անկախ փոփոխականների):

պարամետր գնահատական

Համար բազմակի ռեգրեսիայի (MR), այն իրականացվում է օգտագործելով փոքրագույն քառակուսիների մեթոդը (LSM): Համար գծային հավասարումների ձեւի Y = a + b ₁ x _{1 + ... + b մ x մ +} ε կառուցելու համակարգ նորմալ հավասարումների (սմ. Ստորեւ)

Որպեսզի հասկանանք, թե սկզբունքը մեթոդով, մենք համարում ենք, երկգործոն գործը. Ապա մենք այն իրավիճակը բնութագրվում է բանաձեւով

Հետեւաբար, մենք ձեռք:

որտեղ σ - ն փոփոխականությունը համապատասխան հնարավորությունը, արտացոլված է ցուցանիշից:

MNC կիրառելի է հավասարման MR է standartiziruemom մասշտաբների: Այս դեպքում, մենք ստանում հավասարումը:

որուն մէջ տ _y, տ _{x 1 ...} տ _XM - standartiziruemye փոփոխականներ, որոնք միջին արժեքներ են, 0: բետա _i - ստանդարտացված հետադարձի գործակիցների եւ ստանդարտ շեղումը - 1.

Խնդրում ենք նկատի ունենալ, որ բոլորը բետա _i այս դեպքում սահմանվում է որպես կարգավորվեն եւ tsentraliziruemye, հետեւաբար, համեմատություն միջեւ համարել վավեր եւ ընդունելի: Ի լրումն, այն ընդունված է իրականացնել ցուցադրությունը գործոններ, հեռացնելով նրանց, որոնք ունեն ամենացածր արժեքները βi:

Խնդիրն այն է, օգտագործելով գծային ռեգրեսիայի հավասարման

Ենթադրենք, դուք ունեք մի սեղան դինամիկայի գնի որոշակի ապրանքի N վերջին 8 ամիսների ընթացքում: Անհրաժեշտ է որոշել, թե արդյոք ձեռք բերելու իր կուսակցության արժեքով 1850 ռուբլի: / T.

	A	B	C
1	այն ամիսն	Անունը ամսվա	գին N
2	1	հունվարի	1750 ռուբլի մեկ տոննայի դիմաց
3	2	փետրվար	1755 ռուբլի մեկ տոննայի դիմաց
4	3	երթ	1767 ռուբլի մեկ տոննայի դիմաց
5	4	ապրիլ	1760 ռուբլի մեկ տոննայի դիմաց
6	5	մայիս	1770 ռուբլի մեկ տոննայի դիմաց
7	6	հունիսի	1790 ռուբլի մեկ տոննայի դիմաց
8	7	հուլիսի	1810 ռուբլի մեկ տոննայի դիմաց
9	8	օգոստոս	1840 ռուբլի մեկ տոննայի դիմաց

Որպեսզի լուծել այս խնդիրը աղյուսակային մշակողը «Excel» պահանջվում է օգտագործել արդեն հայտնի է օրինակ գործիք "Տվյալների վերլուծություն» ներկայացված է վերեւում: Հաջորդը, ընտրել "Հետընթաց» բաժինը եւ սահմանված պարամետրերի. Մենք պետք է հիշենք, որ այդ «ներածում տիրույթում Y» պետք է ծանոթանալ մի շարք արժեքների կախյալ փոփոխականի (այս դեպքում գինը ապրանքի կոնկրետ ամիսների տարվա) եւ ՀՀ «ներածում ընդմիջումից X» - ի համար անկախ (այդ ամսվա): Մենք հաստատում ենք, որ ակցիան սեղմելով «OK»: Մի նոր worksheet (եթե այդպես է նշված), մենք ձեռք տվյալների համար ռեգրեսիայի:

Մենք կառուցում ենք նրանց վրա գծային հավասարումը ձեւի y = ax + b, որտեղ, ինչպես պարամետրերի A եւ B են գործակիցները են գծի համարը, ամսվա եւ անունից գործակիցների եւ «Y-հատման» գծի թերթիկ արդյունքների հետ է հետընթացը վերլուծության Այսպիսով, գծային ռեգրեսիայի հավասարման (EQ) 3 խնդրի կարելի է գրել նաեւ:

Գինը ապրանքի N = 11,714 * 1727.54 ամիս համարը +.

կամ հանրահաշվական նշագրմամբ

y = 11.714 x + 1727,54

վերլուծություն արդյունքների

Պետք է որոշի, թե արդյոք ստացել համարժեք գծային ռեգրեսիայի հավասարման օգտագործելով բազմակի հարաբերակցությունը գործակիցների (CMC) եւ վճռականություն, ինչպես նաեւ քննությունը եւ Ֆիշերի t-թեստը: Ի սեղանի «Excel» ռեգրեսիայի արդյունքների հետ, նրանք գործում են անուններով բազմակի R, R-հրապարակ, F-T վիճակագրություն եւ վիճակագրություն, համապատասխանաբար.

KMC R հնարավորություն է տալիս գնահատելու համար մերձավորության հավանականային միջեւ հարաբերությունները անկախ եւ կախյալ փոփոխականների. Նրա բարձր արժեք ցույց է տալիս ուժեղ բավականաչափ կապը փոփոխական »թվով ամսվա» եւ «N Ապրանքի գինը ռուբլի, 1 տոննայի»: Սակայն, բնույթը այդ փոխհարաբերությունների անհայտ է:

Հրապարակը գործակցի որոշման R ² (RI) հանդիսանում է թվային բնորոշ համամասնությամբ ընդհանուր ցրման եւ ցույց է տալիս ցրումը փորձարարական տվյալների մասի, այսինքն, արժեքները կախյալ փոփոխականի համապատասխանում է գծային ռեգրեսիայի հավասարման. Այս խնդրին, այս արժեքը 84.8%, mp. Ե. Վիճակագրության հետ բարձր աստիճանի ճշտության ձեռք բերված նկարագրված են SD:

F վիճակագրություն, ինչպես նաեւ հայտնի է որպես Fisher չափանիշ օգտագործվում է գնահատել նշանակությունը գծային կախվածության կամ հերքել են այն վարկածը, հաստատելով իր գոյությունը:

Որի արժեքը t-վիճակագրական (Ստյուդենտի փորձարկման համար) օգնում գնահատել նշանակությունը գործակցով ցանկացած ազատ անհայտ գծային կախվածության անդամ: Եթե արժեքի T-test> T _cr, վարկած է գծային հավասարման աննշան ազատ ժամկետի մերժվում է:

Այս խնդրի համար ազատ ժամկետով միջոցով գործիքների "Excel" պարզվել է, որ T = 169,20903, եւ p = 2,89E-12, ք. E. Ունեն զրոյական հավանականություն, որ հավատարիմ կմերժվի այն վարկածը, որ անցնում է ազատ ժամկետով: Համար անհայտ գործակից t = 5,79405, եւ p = 0,001158. Այլ կերպ ասած, այն հավանականությունը, որ մերժել ճիշտ վարկածը չի անիմաստություն Գործակցի համար անհայտ է, 0.12%:

Այսպիսով, կարելի է պնդել, որ ձեռք է բերել գծային ռեգրեսիայի հավասարման համարժեք:

Խնդիրը նպատակահարմարության գնելու բաժնետոմսերը

Բազմակի ռեգրեսիայի կատարվել է Excel- ում, օգտագործելով նույն «Տվյալների վերլուծություն" գործիք. Դիտարկենք, օրինակ, կոնկրետ դիմումը:

Ուղեցույց ընկերությունը «NNN» պետք է որոշի, թե արդյոք ձեռք բերել 20% բաժնետոմսերի ՓԲԸ «MMM»: Փաթեթի արժեք (SP) 70 մլն ԱՄՆ դոլար: Մասնագետները «NNN» հավաքագրված տվյալների վրա նմանատիպ գործարքների. Որոշվեց է գնահատել արժեքը բաժնետոմսերի վրա այնպիսի չափանիշների, արտահայտվում է միլիոնավոր դոլարների, ինչպիսիք են:

• կրեդիտորական (VK),
• տարեկան շրջանառությունը ծավալը (VO);
• դեբիտորական պարտքեր (VD);
• արժեքը հիմնական միջոցների (SOF):

Բացի այդ, օգտագործեք աշխատավարձի պարտքերը ձեռնարկությունների (V3 U) եւ հազարավոր դոլարների:

Որոշմամբ սեղան processor Excel միջոցները

Առաջին, դուք պետք է ստեղծել մի աղյուսակը մուտքային տվյալները. Այն հետեւյալն է.

Հաջորդ:

• Զանգահարեք արկղ "data վերլուծություն».
• Ընտրված «Հետընթաց» բաժինը.
• պատուհանը «Մուտքագրման ընդմիջումից Y» կառավարվում հեռահար կախյալ փոփոխական արժեքները column G.
• սեղմեք պատկերակը կարմիր սլաքը աջ պատուհանի »Input ինտերվալը X» եւ մեկուսացված է մի թերթիկ շարք բոլոր արժեքներին սյունակի B, C, D, Ֆ.

Ընդգծել կետը, «Նոր թերթիկ" եւ սեղմեք "OK":

Ստացեք մի հետադիմություն վերլուծություն այս խնդիրը.

Հետազոտության արդյունքները եւ եզրակացությունները

«Հավաքել« կլորացվում է ներկայացված տվյալների վերեւում Հատիկային սեղանի Excel պրոցեսոր ռեգրեսիայի հավասարման մեջ.

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

Ի ավելի սովորական մաթեմատիկական ձեւով, այն կարելի է գրել նաեւ:

y = 0.103 * x1 + 0,541 * x2 - 0.031 * x3 + 0,405 * x4 + 0,691 * X5 - 265.844

Տվյալների համար «MMM» ԲԸ ներկայացված է ստորեւ բերված աղյուսակում:

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

Փոխարինող նրանց մեջ հետընթացը հավասարման, ձեռք բերել գործիչ 64.72 մլն ԱՄՆ դոլար: Սա նշանակում է, որ բաժնետոմսերը ԲԲԸ «MMM» չպետք է գնել, քանի որ նրանց արժեքը բավականին overpriced է 70 մլն ԱՄՆ դոլար:

Ինչպես դուք կարող եք տեսնել, որ օգտագործումը աղյուսակի "Excel" եւ հետընթացը հավասարման թույլատրվում է կատարել տեղեկացված որոշում նպատակահարմարության բավականին կոնկրետ գործարքը:

Այժմ դուք գիտեք, թե ինչ է հետընթացը: Օրինակներ դեպի Excel, նշվեց վերեւում, կօգնի ձեզ լուծել գործնական խնդիրները էկոնոմետրիկայի: