Լուծե՛ք հավասարումների համակարգ՝ օգտագործելով նվազագույն քառակուսիների մեթոդը Excel-ում: Excel-ում նվազագույն քառակուսիների մեթոդի կիրառում: Մի քանի խոսք կանխատեսման համար օգտագործվող սկզբնական տվյալների ճիշտության մասին

Մեթոդ նվազագույն քառակուսիներըգծային հավասարման կառուցման մաթեմատիկական ընթացակարգ է, որն առավելագույնս համապատասխանում է երկու շարք թվերի բազմությանը: Այս մեթոդի կիրառման նպատակն է նվազագույնի հասցնել ընդհանուր քառակուսի սխալը: Excel-ն ունի գործիքներ, որոնք կարող են օգնել ձեզ կիրառել այս մեթոդը ձեր հաշվարկներում: Եկեք պարզենք, թե ինչպես է դա արվում:

· Մեթոդի օգտագործումը Excel-ում

o Միացնելով «Solution Search» հավելումը

o Խնդիրային պայմաններ

o Լուծում

Օգտագործելով մեթոդը Excel-ում

Նվազագույն քառակուսիների մեթոդը (LSM) մի փոփոխականի մյուսից կախվածության մաթեմատիկական նկարագրությունն է: Այն կարող է օգտագործվել կանխատեսումների համար:

Միացնելով Find Solution հավելումը

Excel-ում MNC-ն օգտագործելու համար անհրաժեշտ է միացնել հավելումը «Լուծում գտնելը», որը լռելյայն անջատված է:

1. Գնացեք ներդիր «Ֆայլ».

2. Սեղմեք բաժնի անվան վրա «Ընտրանքներ».

3. Բացվող պատուհանում ընտրեք ենթաբաժինը «Հավելումներ».

4. Բլոկում «Վերահսկում», որը գտնվում է պատուհանի ներքևում, անջատիչը դրեք դիրքի վրա «Excel հավելումներ»(եթե այն այլ արժեք ունի) և սեղմեք կոճակի վրա «Գնա...».

5. Բացվում է փոքրիկ պատուհան։ Պարամետրի կողքին մենք տիզ ենք դնում «Լուծում գտնելը». Սեղմեք կոճակի վրա "ԼԱՎ".

Այժմ գործառույթը Լուծում գտնելը Excel-ում ակտիվացված է, և դրա գործիքները հայտնվում են ժապավենի վրա:

Դաս. Excel-ում լուծում գտնելը

Խնդրի պայմանները

Եկեք նկարագրենք LSM-ի կիրառումը կոնկրետ օրինակ. Մենք ունենք թվերի երկու շարք xԵվ y, որի հաջորդականությունը ներկայացված է ստորև ներկայացված նկարում։

Այս կախվածությունը կարելի է առավել ճշգրիտ նկարագրել գործառույթով.

Միաժամանակ հայտնի է, որ երբ x=0 yնույնպես հավասար 0 . Հետևաբար, այս հավասարումը կարելի է նկարագրել կախվածությամբ y=nx.

Մենք պետք է գտնենք տարբերության քառակուսիների նվազագույն գումարը։

Լուծում

Անցնենք մեթոդի ուղղակի կիրառման նկարագրությանը։

1. Առաջին արժեքից ձախ xդրեք թիվ 1 . Սա կլինի առաջին գործակցի արժեքի մոտավոր արժեքը n.

2. Սյունակի աջ կողմում yավելացնել ևս մեկ սյունակ - nx. Այս սյունակի առաջին բջիջում գրում ենք գործակիցը բազմապատկելու բանաձևը nառաջին փոփոխականի մեկ բջիջի համար x. Միևնույն ժամանակ մենք դաշտի կապը դարձնում ենք բացարձակ գործակցով, քանի որ այդ արժեքը չի փոխվի։ Սեղմեք կոճակի վրա Մուտքագրեք.

3. Օգտագործելով լրացման նշիչը, պատճենեք այս բանաձևը ներքևի սյունակի աղյուսակի ողջ տիրույթում:

4. Առանձին բջիջում հաշվարկեք արժեքների քառակուսիների տարբերությունների գումարը yԵվ nx. Դա անելու համար սեղմեք կոճակը «Տեղադրել գործառույթը».

5. Բացվածում «Ֆունկցիոնալ հրաշագործ»մուտքի որոնում «ՍՈՒՄՄԿՎԱՐՆԱ». Ընտրեք այն և սեղմեք կոճակը "ԼԱՎ".

6. Բացվում է արգումենտների պատուհանը: Դաշտում «Array_x» y. Դաշտում «Array_y»մուտքագրեք սյունակի բջիջների շրջանակը nx. Արժեքներ մուտքագրելու համար պարզապես տեղադրեք կուրսորը դաշտում և ընտրեք համապատասխան տիրույթը թերթիկի վրա: Մուտք գործելուց հետո սեղմեք կոճակը "ԼԱՎ".

7. Գնացեք ներդիր «Տվյալներ». Գործիքների տուփի ժապավենի վրա «Վերլուծություն»սեղմեք կոճակի վրա «Լուծում գտնելը».

8. Բացվում է այս գործիքի պարամետրերի պատուհանը: Դաշտում «Օպտիմալացնել նպատակային գործառույթը»նշեք բջիջի հասցեն բանաձևով «ՍՈՒՄՄԿՎԱՐՆԱ». Պարամետրում "Նախքան"համոզվեք, որ անջատիչը դրեք դիրքի վրա «Նվազագույն». Դաշտում «Բջիջների փոփոխություն»նշեք հասցեն գործակցի արժեքով n. Սեղմեք կոճակի վրա «Գտեք լուծում».

9. Լուծումը կցուցադրվի գործակցի վանդակում n. Այս արժեքը կլինի ֆունկցիայի նվազագույն քառակուսին: Եթե ​​արդյունքը բավարարում է օգտագործողին, ապա սեղմեք կոճակը "ԼԱՎ"լրացուցիչ պատուհանում:

Ինչպես տեսնում եք, նվազագույն քառակուսիների մեթոդի կիրառումը բավականին բարդ մաթեմատիկական ընթացակարգ է։ Մենք դա ցույց տվեցինք գործի մեջ՝ օգտագործելով պարզ օրինակ, բայց կան շատ ավելի բարդ դեպքեր։ Այնուամենայնիվ, Microsoft Excel գործիքները նախատեսված են հնարավորինս պարզեցնելու հաշվարկները:

http://multitest.semico.ru/mnk.htm

Ընդհանուր դրույթներ

Ինչպես քիչ թիվբացարձակ արժեքով, այնքան լավ է ընտրված ուղիղ գիծը (2): Որպես ուղիղ գիծ (2) ընտրելու ճշտության հատկանիշ՝ կարող ենք վերցնել քառակուսիների գումարը

S-ի համար նվազագույն պայմանները կլինեն

	(6)
	(7)

(6) և (7) հավասարումները կարելի է գրել հետևյալ կերպ.

	(8)
	(9)

(8) և (9) հավասարումներից հեշտ է գտնել a և b xi և y i փորձարարական արժեքներից: (8) և (9) հավասարումներով սահմանված տողը (2) կոչվում է նվազագույն քառակուսիների մեթոդով ստացված ուղիղ (այս անվանումն ընդգծում է, որ S քառակուսիների գումարը նվազագույն է)։ (8) և (9) հավասարումները, որոնցից որոշվում է ուղիղ գիծը (2), կոչվում են նորմալ հավասարումներ։

Դուք կարող եք նշել սովորական հավասարումներ կազմելու պարզ և ընդհանուր եղանակ: Օգտագործելով փորձնական կետերը (1) և հավասարումը (2), մենք կարող ենք գրել a-ի և b-ի հավասարումների համակարգ.

Եկեք այս հավասարումներից յուրաքանչյուրի ձախ և աջ կողմերը բազմապատկենք առաջին անհայտ a գործակցով (այսինքն՝ x 1, x 2, ..., x n) և գումարենք ստացված հավասարումները՝ ստանալով առաջին նորմալ հավասարումը (8): .

Եկեք այս հավասարումներից յուրաքանչյուրի ձախ և աջ կողմերը բազմապատկենք երկրորդ անհայտ b-ի գործակցով, այսինքն. 1-ով և ավելացրեք ստացված հավասարումները, ստացվում է երկրորդ նորմալ հավասարումը (9):

Նորմալ հավասարումների ստացման այս մեթոդը ընդհանուր է. այն հարմար է, օրինակ, ֆունկցիայի համար

կա հաստատուն արժեք և այն պետք է որոշվի փորձարարական տվյալների հիման վրա (1):

k-ի հավասարումների համակարգը կարելի է գրել.

Գտե՛ք ուղիղ գիծ (2)՝ օգտագործելով նվազագույն քառակուսիների մեթոդը:

Լուծում.Մենք գտնում ենք.

X i =21, y i =46.3, x i 2 =91, x i y i =179.1:

Գրում ենք (8) և (9)91a+21b=179.1 հավասարումները,

21a+6b=46.3, այստեղից մենք գտնում ենք
a=0.98 b=4.3.

Որն ամենալայն կիրառությունն է գտնում գիտության և գործնական գործունեության տարբեր բնագավառներում։ Սա կարող է լինել ֆիզիկա, քիմիա, կենսաբանություն, տնտեսագիտություն, սոցիոլոգիա, հոգեբանություն և այլն, և այլն: Ճակատագրի կամքով, ես հաճախ ստիպված եմ զբաղվել տնտեսությամբ, և, հետևաբար, այսօր ես կկազմակերպեմ ձեզ ուղևորություն դեպի զարմանալի երկիր, որը կոչվում է. Էկոնոմետրիկա=) ...Ինչպես չուզես?! Այնտեղ շատ լավ է, պարզապես պետք է որոշում կայացնել: ...Բայց այն, ինչ դուք, հավանաբար, անպայման ցանկանում եք, սովորել, թե ինչպես լուծել խնդիրները նվազագույն քառակուսիների մեթոդ. Եվ հատկապես ջանասեր ընթերցողները կսովորեն լուծել դրանք ոչ միայն ճշգրիտ, այլև ՇԱՏ ԱՐԱԳ ;-) Բայց նախ. խնդրի ընդհանուր հայտարարություն+ ուղեկցող օրինակ.

Եկեք ուսումնասիրենք որոշակի առարկայական ոլորտի ցուցանիշներ, որոնք ունեն քանակական արտահայտություն: Միեւնույն ժամանակ, բոլոր հիմքերը կան ենթադրելու, որ ցուցանիշը կախված է ցուցանիշից։ Այս ենթադրությունը կարող է նման լինել գիտական ​​վարկած, և հիմնված լինեն հիմնական ողջախոհության վրա: Այնուամենայնիվ, եկեք մի կողմ թողնենք գիտությունը և ուսումնասիրենք ավելի ախորժելի ոլորտները, մասնավորապես, մթերային խանութները: Նշենք հետևյալով.

– մթերային խանութի մանրածախ տարածք, քմ.
- մթերային խանութի տարեկան շրջանառությունը, միլիոն ռուբլի:

Միանգամայն պարզ է, որ որքան մեծ է խանութի տարածքը, այնքան շատ դեպքերում դրա շրջանառությունն ավելի մեծ կլինի։

Ենթադրենք, որ դափով դիտարկումներ/փորձեր/հաշվարկներ/պարեր կատարելուց հետո մեր տրամադրության տակ կան թվային տվյալներ.

Մթերային խանութների դեպքում, կարծում եմ, ամեն ինչ պարզ է. - սա 1-ին խանութի տարածքն է, - նրա տարեկան շրջանառությունը, - 2-րդ խանութի տարածքը, - տարեկան շրջանառությունը և այլն: Ի դեպ, ամենևին էլ պարտադիր չէ դասակարգված նյութերին հասանելիություն ունենալ. առևտրի շրջանառության բավականին ճշգրիտ գնահատում կարելի է ստանալ մաթեմատիկական վիճակագրություն. Այնուամենայնիվ, եկեք չշեղվենք, առևտրային լրտեսության դասընթացն արդեն վճարված է =)

Աղյուսակային տվյալները կարող են գրվել նաև կետերի տեսքով և պատկերվել ծանոթ ձևով Դեկարտյան համակարգ .

Պատասխանենք մի կարևոր հարցի. Քանի՞ միավոր է անհրաժեշտ որակական ուսումնասիրության համար:

Որքան մեծ է, այնքան լավ: Նվազագույն ընդունելի հավաքածուն բաղկացած է 5-6 միավորից։ Բացի այդ, երբ տվյալների քանակը փոքր է, «անոմալ» արդյունքները չեն կարող ներառվել ընտրանքում: Այսպիսով, օրինակ, փոքր էլիտար խանութը կարող է ավելի մեծ պատվերներ վաստակել, քան «իր գործընկերները», դրանով իսկ աղավաղելով. ընդհանուր օրինաչափություն, որն այն է, ինչ դուք պետք է գտնեք:

Շատ պարզ ասած, մենք պետք է ընտրենք ֆունկցիա, ժամանակացույցըորը հնարավորինս մոտ է անցնում կետերին . Այս ֆունկցիան կոչվում է մոտավոր (մոտավորություն - մոտավորություն)կամ տեսական գործառույթ . Ընդհանրապես, այստեղ անմիջապես հայտնվում է ակնհայտ «հավակնորդ»՝ բազմանդամը բարձր աստիճան, որի գրաֆիկն անցնում է ԲՈԼՈՐ կետերով։ Բայց այս տարբերակը բարդ է և հաճախ պարզապես սխալ: (քանի որ գծապատկերը անընդհատ «պտտվելու է» և վատ է արտացոլում հիմնական միտումը).

Այսպիսով, փնտրվող գործառույթը պետք է լինի բավականին պարզ և միևնույն ժամանակ համարժեքորեն արտացոլի կախվածությունը: Ինչպես կարող եք կռահել, նման գործառույթներ գտնելու մեթոդներից մեկը կոչվում է նվազագույն քառակուսիների մեթոդ. Նախ, եկեք նայենք դրա էությանը ընդհանուր առումներով: Թող որոշ գործառույթներ մոտավոր փորձարարական տվյալներ ունենան.


Ինչպե՞ս գնահատել այս մոտավորության ճշգրտությունը: Հաշվարկենք նաև փորձարարական և ֆունկցիոնալ արժեքների տարբերությունները (շեղումները): (մենք ուսումնասիրում ենք նկարը). Առաջին միտքը, որ գալիս է գլխի, դա է գնահատել, թե որքան մեծ է գումարը, բայց խնդիրն այն է, որ տարբերությունները կարող են բացասական լինել (Օրինակ, ) և նման գումարման արդյունքում շեղումները կչեղարկեն միմյանց: Հետևաբար, որպես մոտարկման ճշգրտության գնահատում, այն խնդրում է վերցնել գումարը մոդուլներշեղումներ:

կամ փլուզված: (եթե որևէ մեկը չգիտի. – սա գումարի պատկերակն է, և – օժանդակ «հաշվիչ» փոփոխական, որն ընդունում է արժեքներ 1-ից մինչև ).

Փորձնական կետերը մոտավորելով տարբեր ֆունկցիաներով՝ կստանանք տարբեր իմաստներ, և ակնհայտորեն, որտեղ այս գումարն ավելի փոքր է, այդ ֆունկցիան ավելի ճշգրիտ է:

Նման մեթոդ գոյություն ունի, և այն կոչվում է նվազագույն մոդուլի մեթոդ. Սակայն գործնականում այն ​​շատ ավելի լայն տարածում է գտել նվազագույն քառակուսի մեթոդ, որի դեպքում հնարավոր բացասական արժեքները վերացվում են ոչ թե մոդուլի միջոցով, այլ շեղումները քառակուսիացնելով.

, որից հետո ջանքերն ուղղված են այնպիսի ֆունկցիա ընտրելուն, որ քառակուսի շեղումների գումարը հնարավորինս փոքր էր: Իրականում, այստեղից է գալիս մեթոդի անվանումը:

Եվ հիմա մենք վերադառնում ենք մեկ այլ բանի կարևոր կետԻնչպես նշվեց վերևում, ընտրված գործառույթը պետք է լինի բավականին պարզ, բայց կան նաև շատ նման գործառույթներ. գծային , հիպերբոլիկ, էքսպոնենցիալ, լոգարիթմական, քառակուսի և այլն: Եվ, իհարկե, այստեղ ես անմիջապես կցանկանայի «նվազեցնել գործունեության ոլորտը»։ Գործառույթների ո՞ր դասը պետք է ընտրեմ հետազոտության համար: Պարզունակ, բայց արդյունավետ տեխնիկա.

– Ամենահեշտ ճանապարհը կետերը պատկերելն է գծագրի վրա և վերլուծել դրանց գտնվելու վայրը: Եթե ​​նրանք հակված են վազել ուղիղ գծով, ապա դուք պետք է փնտրեք գծի հավասարում օպտիմալ արժեքներով և. Այսինքն՝ խնդիր է դրված գտնել այնպիսի գործակիցներ, որ քառակուսի շեղումների գումարը լինի ամենափոքրը։

Եթե ​​կետերը գտնվում են, օրինակ, երկայնքով հիպերբոլիա, ապա ակնհայտորեն պարզ է, որ գծային ֆունկցիան թույլ մոտավորություն կտա։ Այս դեպքում մենք փնտրում ենք հիպերբոլայի հավասարման առավել «բարենպաստ» գործակիցները - նրանք, որոնք տալիս են քառակուսիների նվազագույն գումարը .

Այժմ նշենք, որ երկու դեպքում էլ խոսքը գնում է երկու փոփոխականների ֆունկցիաներ, որոնց փաստարկներն են որոնված կախվածության պարամետրերը:

Եվ ըստ էության մենք պետք է լուծենք ստանդարտ խնդիր՝ գտնել երկու փոփոխականների նվազագույն ֆունկցիա.

Եկեք հիշենք մեր օրինակը. ենթադրենք, որ «պահեստի» կետերը հակված են ուղիղ գծի վրա, և բոլոր հիմքերը կան ենթադրելու, որ. գծային կախվածությունշրջանառություն մանրածախ տարածքից. Գտնենք «a» և «be» այնպիսի գործակիցներ, որ քառակուսի շեղումների գումարը. ամենափոքրն էր։ Ամեն ինչ սովորականի պես է՝ առաջինը 1-ին կարգի մասնակի ածանցյալներ. Համաձայն գծայինության կանոնԴուք կարող եք տարբերակել հենց գումարի պատկերակի տակ.

Եթե ​​ցանկանում եք օգտագործել այս տեղեկությունըէսսեի կամ դասընթացի համար - Ես շատ շնորհակալ կլինեմ աղբյուրների ցանկի հղման համար, դուք կգտնեք նման մանրամասն հաշվարկներ մի քանի վայրերում.

Եկեք ստեղծենք ստանդարտ համակարգ.

Մենք յուրաքանչյուր հավասարում կրճատում ենք «երկու»-ով և, բացի այդ, «կոտրում» ենք գումարները.

Նշում ինքնուրույն վերլուծել, թե ինչու «a»-ն և «be»-ը կարող են դուրս հանվել գումարի պատկերակից այն կողմ: Ի դեպ, ֆորմալ առումով դա կարելի է անել գումարով

Եկեք համակարգը վերաշարադրենք «կիրառական» ձևով.

որից հետո սկսում է ի հայտ գալ մեր խնդրի լուծման ալգորիթմը.

Գիտե՞նք արդյոք կետերի կոորդինատները։ Մենք գիտենք. Գումարներ կարո՞ղ ենք գտնել այն Հեշտությամբ. Եկեք ամենապարզը դարձնենք երկու գծային հավասարումների համակարգ երկու անհայտներում(«ա» և «լինել»): Մենք լուծում ենք համակարգը, օրինակ. Կրամերի մեթոդը, որի արդյունքում ստանում ենք անշարժ կետ։ Ստուգում բավարար պայման էքստրեմումի համար, մենք կարող ենք ստուգել, ​​որ այս պահին ֆունկցիան հասնում է ճշգրիտ նվազագույնը. Չեկը ներառում է լրացուցիչ հաշվարկներ, ուստի մենք այն կթողնենք կուլիսներում (անհրաժեշտության դեպքում, բացակայող շրջանակը կարելի է դիտել). Վերջնական եզրակացություն ենք անում.

Գործառույթ լավագույն միջոցը (առնվազն ցանկացած այլ գծային ֆունկցիայի համեմատ)մոտեցնում է փորձնական կետերը . Կոպիտ ասած, նրա գրաֆիկը հնարավորինս մոտ է անցնում այս կետերին։ Ավանդույթի համաձայն էկոնոմետրիկաստացված մոտավոր ֆունկցիան նույնպես կոչվում է զուգավորված գծային ռեգրեսիայի հավասարում .

Քննարկվող խնդիրն ունի մեծ գործնական նշանակություն. Մեր օրինակի իրավիճակում, հավասար. թույլ է տալիս կանխատեսել, թե ինչ ապրանքաշրջանառություն («Իգրեկ»)խանութը կունենա վաճառքի տարածքի այս կամ այն ​​արժեքով («x» բառի այս կամ այն ​​իմաստը). Այո, ստացված կանխատեսումը կլինի միայն կանխատեսում, բայց շատ դեպքերում այն ​​բավականին ճշգրիտ կստացվի։

Ես կվերլուծեմ ընդամենը մեկ խնդիր «իրական» թվերով, քանի որ դրանում դժվարություններ չկան. բոլոր հաշվարկները գտնվում են մակարդակի վրա: դպրոցական ծրագիր 7-8 դասարաններ. 95 տոկոս դեպքերում ձեզանից կպահանջվի գտնել միայն գծային ֆունկցիա, բայց հոդվածի հենց վերջում ես ցույց կտամ, որ ավելի դժվար չէ գտնել օպտիմալ հիպերբոլայի, էքսպոնենցիալ և մի քանի այլ ֆունկցիաների հավասարումները:

Փաստորեն, մնում է միայն բաժանել խոստացված բարիքները, որպեսզի դուք սովորեք լուծել նման օրինակները ոչ միայն ճշգրիտ, այլև արագ: Մենք ուշադիր ուսումնասիրում ենք ստանդարտը.

Առաջադրանք

Երկու ցուցանիշների փոխհարաբերությունների ուսումնասիրության արդյունքում ստացվել են թվերի հետևյալ զույգերը.

Օգտագործելով նվազագույն քառակուսիների մեթոդը, գտե՛ք գծային ֆունկցիան, որը լավագույնս մոտավոր է էմպիրիկին (փորձառու)տվյալները։ Կատարեք գծագիր, որի վրա կկառուցվեն փորձարարական կետեր և մոտավոր ֆունկցիայի գրաֆիկ դեկարտյան ուղղանկյուն կոորդինատային համակարգում . Գտե՛ք էմպիրիկ և տեսական արժեքների քառակուսի շեղումների գումարը: Պարզեք, թե արդյոք հատկանիշը ավելի լավ կլինի (նվազագույն քառակուսիների մեթոդի տեսանկյունից)մոտեցնել փորձնական կետերը.

Խնդրում ենք նկատի ունենալ, որ «x» իմաստները բնական են, և սա ունի բնորոշ իմաստային նշանակություն, որի մասին ես կխոսեմ մի փոքր ուշ. բայց դրանք, իհարկե, կարող են լինել նաև կոտորակային։ Բացի այդ, կախված որոշակի առաջադրանքի բովանդակությունից, և՛ «X», և՛ «խաղ» արժեքները կարող են լինել ամբողջությամբ կամ մասամբ բացասական: Դե, մեզ տրվել է «անդեմ» առաջադրանք, և մենք սկսում ենք այն լուծում:

Որպես համակարգի լուծում մենք գտնում ենք օպտիմալ ֆունկցիայի գործակիցները.

Ավելի կոմպակտ ձայնագրման նպատակով «հաշվիչ» փոփոխականը կարելի է բաց թողնել, քանի որ արդեն պարզ է, որ գումարումն իրականացվում է 1-ից մինչև .

Ավելի հարմար է պահանջվող գումարները հաշվարկել աղյուսակային ձևով.


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

Այսպիսով, մենք ստանում ենք հետևյալը համակարգ:

Այստեղ դուք կարող եք բազմապատկել երկրորդ հավասարումը 3-ով և 1-ին հավասարումից 2-րդը հանել անդամով. Բայց սա հաջողություն է. գործնականում համակարգերը հաճախ նվեր չեն, և նման դեպքերում դա խնայում է Կրամերի մեթոդը:
, ինչը նշանակում է, որ համակարգն ունի յուրահատուկ լուծում։

Եկեք ստուգենք. Ես հասկանում եմ, որ դուք չեք ցանկանում, բայց ինչու՞ բաց թողնել սխալները, որտեղ դրանք բացարձակապես չեն կարող բաց թողնել: Գտնված լուծումը փոխարինենք համակարգի յուրաքանչյուր հավասարման ձախ կողմում.

Ստացվում են համապատասխան հավասարումների աջ կողմերը, ինչը նշանակում է, որ համակարգը ճիշտ է լուծված։

Այսպիսով, ցանկալի մոտավոր գործառույթը. – from բոլոր գծային ֆունկցիաներըՀենց նա է լավագույնս մոտեցնում փորձարարական տվյալներին։

Ի տարբերություն ուղիղ խանութի շրջանառության կախվածությունն իր տարածքից, հայտնաբերված կախվածությունն է հակադարձ («Որքան շատ, այնքան քիչ» սկզբունքը), և այս փաստն անմիջապես բացահայտվում է բացասականով լանջին. Գործառույթ մեզ ասում է, որ որոշակի ցուցանիշի 1 միավորով աճի դեպքում կախված ցուցիչի արժեքը նվազում է միջին 0,65 միավորով: Ինչպես ասում են՝ ինչքան թանկանում է հնդկաձավարը, այնքան քիչ է վաճառվում։

Մոտավորվող ֆունկցիայի գրաֆիկը գծելու համար մենք գտնում ենք նրա երկու արժեքները.

և կատարիր գծագիրը.


Կառուցված ուղիղ գիծը կոչվում է միտում գիծ (մասնավորապես, գծային միտումի գիծ, ​​այսինքն, ընդհանուր դեպքում, միտումը պարտադիր չէ, որ ուղիղ գիծ լինի). Բոլորին է ծանոթ «լինել թրենդ» արտահայտությունը, և կարծում եմ, որ այս տերմինը լրացուցիչ մեկնաբանությունների կարիք չունի։

Հաշվենք քառակուսի շեղումների գումարը էմպիրիկ և տեսական արժեքների միջև։ Երկրաչափորեն սա «ազնվամորու» հատվածների երկարությունների քառակուսիների գումարն է (որոնցից երկուսն այնքան փոքր են, որ նույնիսկ տեսանելի չեն).

Եկեք ամփոփենք հաշվարկները աղյուսակում.


Կրկին, դրանք կարող են կատարվել ձեռքով, միայն այն դեպքում, ես օրինակ բերեմ 1-ին կետի համար.

բայց շատ ավելի արդյունավետ է դա անել արդեն հայտնի ձևով.

Եվս մեկ անգամ կրկնում ենք. Ո՞րն է ստացված արդյունքի իմաստը:Սկսած բոլոր գծային ֆունկցիաները y ֆունկցիան ցուցանիշը ամենափոքրն է, այսինքն՝ իր ընտանիքում լավագույն մոտարկումն է։ Եվ այստեղ, ի դեպ, պատահական չէ խնդրի վերջնական հարցը՝ իսկ եթե առաջարկվող էքսպոնենցիալ ֆունկցիան ավելի լավ կլինի՞ մոտեցնել փորձնական կետերը:

Գտնենք քառակուսի շեղումների համապատասխան գումարը՝ տարբերակելու համար դրանք կնշեմ «էպսիլոն» տառով։ Տեխնիկան միանգամայն նույնն է.


Եվ կրկին, ամեն դեպքում, 1-ին կետի հաշվարկները.

Excel-ում մենք օգտագործում ենք ստանդարտ ֆունկցիա ԺԱՄԱՆԱԿ (շարահյուսությունը կարելի է գտնել Excel Help-ում).

Եզրակացություն:, ինչը նշանակում է, որ էքսպոնենցիալ ֆունկցիան ուղիղ գծից ավելի վատ է մոտենում փորձարարական կետերին .

Բայց այստեղ պետք է նշել, որ ավելի վատն է դեռ չի նշանակում, ինչն է սխալ. Այժմ ես կառուցել եմ այս էքսպոնենցիոնալ ֆունկցիայի գրաֆիկը, և այն նույնպես անցնում է կետերին մոտ - այնքան, որ առանց վերլուծական հետազոտությունների դժվար է ասել, թե որ գործառույթն է ավելի ճշգրիտ։

Սա ավարտում է լուծումը, և ես վերադառնում եմ փաստարկի բնական արժեքների հարցին: Տարբեր ուսումնասիրություններում, սովորաբար տնտեսական կամ սոցիոլոգիական, բնական «X»-ներն օգտագործվում են ամիսների, տարիների կամ այլ հավասար ժամանակային միջակայքերը համարելու համար: Դիտարկենք, օրինակ, հետևյալ խնդիրը.

Նվազագույն քառակուսիների մեթոդը (OLS) պատկանում է ռեգրեսիոն վերլուծության ոլորտին։ Այն ունի բազմաթիվ կիրառումներ, քանի որ թույլ է տալիս մոտավոր ներկայացում տրված գործառույթըմյուսներն ավելի պարզ են: LSM-ը կարող է չափազանց օգտակար լինել դիտարկումների մշակման համար, և այն ակտիվորեն օգտագործվում է որոշ քանակություններ գնահատելու համար՝ հիմնվելով պատահական սխալներ պարունակող մյուսների չափումների արդյունքների վրա: Այս հոդվածում դուք կսովորեք, թե ինչպես իրականացնել նվազագույն քառակուսիների հաշվարկները Excel-ում:

Խնդրի շարադրանք՝ օգտագործելով կոնկրետ օրինակ

Ենթադրենք, որ կան երկու ցուցիչներ X և Y: Ավելին, Y-ը կախված է X-ից: Քանի որ OLS-ը մեզ հետաքրքրում է ռեգրեսիոն վերլուծության տեսանկյունից (Excel-ում դրա մեթոդներն իրականացվում են ներկառուցված գործառույթների միջոցով), մենք պետք է անմիջապես անցնենք դիտարկմանը. կոնկրետ խնդիր.

Այսպիսով, թող X-ը լինի մթերային խանութի մանրածախ տարածքը՝ չափված քառակուսի մետր, իսկ Y-ը տարեկան շրջանառությունն է, որը որոշվում է միլիոնավոր ռուբլով:

Պահանջվում է կանխատեսում անել, թե ինչ շրջանառություն (Y) կունենա խանութը, եթե ունի այս կամ այն ​​մանրածախ տարածք։ Ակնհայտ է, որ Y = f (X) ֆունկցիան աճում է, քանի որ հիպերմարկետում ավելի շատ ապրանքներ են վաճառվում, քան կրպակը:

Մի քանի խոսք կանխատեսման համար օգտագործվող սկզբնական տվյալների ճիշտության մասին

Ենթադրենք, մենք ունենք աղյուսակ, որը կառուցվել է տվյալների օգտագործմամբ n խանութների համար:

Համաձայն մաթեմատիկական վիճակագրություն, արդյունքները քիչ թե շատ ճիշտ կլինեն, եթե ուսումնասիրվեն առնվազն 5-6 օբյեկտների վերաբերյալ տվյալներ։ Բացի այդ, «անոմալ» արդյունքները չեն կարող օգտագործվել: Մասնավորապես, էլիտար փոքր բուտիկը կարող է ունենալ մի քանի անգամ ավելի մեծ շրջանառություն, քան «masmarket» դասի խոշոր մանրածախ կետերի շրջանառությունը։

Մեթոդի էությունը

Աղյուսակի տվյալները կարելի է պատկերել դեկարտյան հարթության վրա M 1 (x 1, y 1), ... M n (x n, y n) կետերի տեսքով: Այժմ խնդրի լուծումը կկրճատվի y = f (x) մոտավոր ֆունկցիայի ընտրությամբ, որն ունի M 1, M 2, .. M n կետերին հնարավորինս մոտ անցնող գրաֆիկ:

Իհարկե, դուք կարող եք օգտագործել բարձր աստիճանի բազմանդամ, բայց այս տարբերակը ոչ միայն դժվար է իրականացնել, այլև պարզապես սխալ է, քանի որ այն չի արտացոլի հիմնական միտումը, որը պետք է հայտնաբերել: Ամենախելամիտ լուծումը y = ax + b ուղիղ գիծը փնտրելն է, որը լավագույնս մոտավոր է փորձարարական տվյալներին, ավելի ճիշտ՝ a և b գործակիցներին։

Ճշգրտության գնահատում

Ցանկացած մոտավորության դեպքում դրա ճշգրտության գնահատումը առանձնահատուկ նշանակություն ունի: Եկեք e i-ով նշենք x i կետի ֆունկցիոնալ և փորձարարական արժեքների տարբերությունը (շեղումը), այսինքն e i = y i - f (x i):

Ակնհայտ է, որ մոտարկման ճշգրտությունը գնահատելու համար կարող եք օգտագործել շեղումների գումարը, այսինքն՝ X-ի կախվածության Y-ից մոտավոր ներկայացման համար ուղիղ գիծ ընտրելիս պետք է նախապատվությունը տալ նրան, որն ունի ամենափոքր արժեքը: գումարել e i բոլոր քննարկվող կետերում: Այնուամենայնիվ, ամեն ինչ այնքան էլ պարզ չէ, քանի որ դրական շեղումների հետ միասին կլինեն նաև բացասական:

Խնդիրը կարելի է լուծել՝ օգտագործելով շեղման մոդուլները կամ դրանց քառակուսիները։ Վերջին մեթոդը ամենաշատ կիրառվողն է։ Այն օգտագործվում է բազմաթիվ ոլորտներում, ներառյալ ռեգրեսիոն վերլուծությունը (իրագործվում է Excel-ում՝ օգտագործելով երկու ներկառուցված գործառույթներ), և վաղուց ապացուցել է իր արդյունավետությունը:

Նվազագույն քառակուսի մեթոդ

Excel-ը, ինչպես գիտեք, ունի ներկառուցված AutoSum ֆունկցիա, որը թույլ է տալիս հաշվարկել ընտրված տիրույթում գտնվող բոլոր արժեքների արժեքները: Այսպիսով, ոչինչ չի խանգարի մեզ հաշվարկել արտահայտության արժեքը (e 1 2 + e 2 2 + e 3 2 + ... e n 2):

Մաթեմատիկական նշումով սա նման է.

Քանի որ ի սկզբանե որոշում է կայացվել մոտավորել ուղիղ գծով, մենք ունենք.

Այսպիսով, X և Y մեծությունների հատուկ կախվածությունը լավագույնս նկարագրող ուղիղ գիծը գտնելու խնդիրը հանգում է երկու փոփոխականների ֆունկցիայի նվազագույնի հաշվարկին.

Դա անելու համար հարկավոր է մասնակի ածանցյալները a և b նոր փոփոխականների նկատմամբ հավասարեցնել զրոյի և լուծել պարզունակ համակարգ, որը բաղկացած է երկու հավասարումներից 2 անհայտ ձևով.

Որոշ պարզ փոխակերպումներից հետո, ներառյալ բաժանումը 2-ի և գումարների շահարկումից հետո, մենք ստանում ենք.

Լուծելով այն, օրինակ, օգտագործելով Քրամերի մեթոդը, մենք ստանում ենք անշարժ կետ a * և b * որոշակի գործակիցներով: Սա նվազագույնն է, այսինքն՝ կանխատեսելու համար, թե ինչ շրջանառություն կունենա խանութը որոշակի տարածքի համար, հարմար է ուղիղ գիծը y = a * x + b *, որը ռեգրեսիոն մոդելխնդրո առարկա օրինակի համար. Իհարկե, դա թույլ չի տա ձեզ գտնել ճշգրիտ արդյունքը, բայց դա կօգնի ձեզ պատկերացում կազմել, թե արդյոք խանութի ապառիկով որոշակի տարածք գնելը կվճարի:

Ինչպես իրականացնել նվազագույն քառակուսիները Excel-ում

Excel-ն ունի նվազագույն քառակուսիների օգտագործմամբ արժեքները հաշվարկելու գործառույթ: Այն ունի հետևյալ ձևը. «TREND» (հայտնի Y արժեքներ; հայտնի X արժեքներ; նոր X արժեքներ; հաստատուն): Եկեք կիրառենք Excel-ում OLS-ի հաշվարկման բանաձևը մեր աղյուսակում:

Դա անելու համար մուտքագրեք «=» նշանը այն բջիջում, որտեղ պետք է ցուցադրվի Excel-ում նվազագույն քառակուսիների մեթոդով հաշվարկի արդյունքը և ընտրեք «TREND» ֆունկցիան: Բացվող պատուհանում լրացրեք համապատասխան դաշտերը՝ ընդգծելով.

• Y-ի հայտնի արժեքների միջակայք (in այս դեպքումտվյալներ առևտրի շրջանառության համար);
• միջակայք x 1, …x n, այսինքն՝ մանրածախ տարածքի չափը;
• x-ի և՛ հայտնի, և՛ անհայտ արժեքները, որոնց համար անհրաժեշտ է պարզել շրջանառության չափը (աշխատանքային թերթում դրանց գտնվելու վայրի մասին տեղեկությունների համար տե՛ս ստորև):

Բացի այդ, բանաձևը պարունակում է «Const» տրամաբանական փոփոխականը: Եթե ​​համապատասխան դաշտում մուտքագրեք 1, դա կնշանակի, որ դուք պետք է կատարեք հաշվարկները՝ ենթադրելով, որ b = 0:

Եթե ​​Ձեզ անհրաժեշտ է պարզել մեկից ավելի x արժեքի կանխատեսումը, ապա բանաձևը մուտքագրելուց հետո չպետք է սեղմել «Enter», այլ պետք է մուտքագրել «Shift» + «Control» + «Enter» համադրությունը ստեղնաշարի վրա:

Որոշ առանձնահատկություններ

Ռեգրեսիոն վերլուծությունը կարող է հասանելի լինել նույնիսկ կեղծիքների համար: Անհայտ փոփոխականների զանգվածի արժեքը կանխատեսելու Excel բանաձևը՝ TREND, կարող է օգտագործվել նույնիսկ նրանց կողմից, ովքեր երբեք չեն լսել նվազագույն քառակուսիների մասին: Բավական է միայն իմանալ նրա աշխատանքի որոշ առանձնահատկությունները։ Մասնավորապես:

• Եթե ​​դուք դասավորեք y փոփոխականի հայտնի արժեքների տիրույթը մեկ տողում կամ սյունակում, ապա յուրաքանչյուր տող (սյունակ) x-ի հայտնի արժեքներով ծրագրի կողմից կընկալվի որպես առանձին փոփոխական:
• Եթե ​​TREND պատուհանում նշված չէ հայտնի x-ով միջակայքը, ապա Excel-ում ֆունկցիան օգտագործելիս ծրագիրը այն կվերաբերվի որպես ամբողջ թվերից բաղկացած զանգված, որոնց թիվը համապատասխանում է տվյալ արժեքներով տիրույթին: փոփոխական y.
• «Կանխատեսված» արժեքների զանգված դուրս բերելու համար միտումը հաշվարկելու արտահայտությունը պետք է մուտքագրվի որպես զանգվածի բանաձև:
• Եթե ​​x-ի նոր արժեքները նշված չեն, ապա TREND ֆունկցիան դրանք համարում է հայտնիներին հավասար: Եթե ​​դրանք նշված չեն, ապա զանգված 1 ընդունվում է որպես արգումենտ; 2; 3; 4;…, որը համարժեք է արդեն իսկ միջակայքին տրված պարամետրեր y.
• Նոր x արժեքներ պարունակող միջակայքը պետք է ունենա նույն կամ ավելի տողեր կամ սյունակներ, ինչ տվյալ y արժեքները պարունակող միջակայքը: Այսինքն, այն պետք է համաչափ լինի անկախ փոփոխականներին։
• Հայտնի x արժեքներով զանգվածը կարող է պարունակել բազմաթիվ փոփոխականներ: Այնուամենայնիվ, եթե մենք խոսում ենք միայն մեկի մասին, ապա պահանջվում է, որ x և y-ի տրված արժեքներով միջակայքերը լինեն համաչափ: Մի քանի փոփոխականների դեպքում անհրաժեշտ է, որ տվյալ y արժեքներով միջակայքը տեղավորվի մեկ սյունակում կամ մեկ տողում։

PREDICTION ֆունկցիան

Excel-ում ռեգրեսիոն վերլուծությունն իրականացվում է մի քանի ֆունկցիաների միջոցով: Դրանցից մեկը կոչվում է «ԿԱՆԽԱՏԵՍՈՒԹՅՈՒՆ»: Այն նման է «TREND»-ին, այսինքն՝ տալիս է նվազագույն քառակուսիների մեթոդով հաշվարկների արդյունքը: Սակայն միայն մեկ X-ի համար, որի համար Y-ի արժեքը անհայտ է:

Այժմ դուք գիտեք բանաձևեր Excel-ում կեղծիքների համար, որոնք թույլ են տալիս կանխատեսել որոշակի ցուցանիշի ապագա արժեքը՝ գծային միտումի համաձայն:

Նվազագույն քառակուսիների մեթոդը (LS) հիմնված է հետազոտվող տվյալներից ընտրված ֆունկցիայի քառակուսի շեղումների գումարը նվազագույնի հասցնելու վրա: Այս հոդվածում մենք կմոտավորենք առկա տվյալները՝ օգտագործելով գծային ֆունկցիաy = ա x + բ .

Նվազագույն քառակուսի մեթոդ(անգլերեն) Սովորական Նվազագույնը Քառակուսիներ , Օ.Լ.Ս.) անհայտ պարամետրերի գնահատման առումով ռեգրեսիոն վերլուծության հիմնական մեթոդներից մեկն է ռեգրեսիայի մոդելներընտրանքի տվյալների համաձայն:

Դիտարկենք մոտարկումն ըստ ֆունկցիաների, որոնք կախված են միայն մեկ փոփոխականից.

• Գծային՝ y=ax+b (այս հոդվածը)
• y=a*Ln(x)+b
• y=a*x մ
• y=a*EXP(b*x)+с
• y=ax 2 +bx+c

ՆշումՍույն հոդվածում դիտարկվում են 3-րդից 6-րդ աստիճանի բազմանդամով մոտեցման դեպքերը։ Այստեղ դիտարկվում է մոտարկումը եռանկյունաչափական բազմանդամով։

Գծային կախվածություն

Մեզ հետաքրքրում է 2 փոփոխականների կապը XԵվ y. Ենթադրություն կա, որ yկախված Xգծային օրենքի համաձայն y = կացին + բ. Այս հարաբերությունների պարամետրերը որոշելու համար հետազոտողը կատարել է դիտարկումներ՝ x i-ի յուրաքանչյուր արժեքի համար կատարվել է y i-ի չափում (տե՛ս ֆայլի օրինակ): Համապատասխանաբար, թող լինի 20 զույգ արժեք (x i; y i):

Նշում:Եթե ​​փոփոխության քայլն է X մշտական ​​է, ապա կառուցել սփռել հողամասերըկարող է օգտագործվել, եթե ոչ, ապա դուք պետք է օգտագործեք գծապատկերի տեսակը Բիծ .

Դիագրամից ակնհայտ է, որ փոփոխականների միջև կապը մոտ է գծային: Հասկանալու համար, թե շատ ուղիղներից որն է առավել «ճիշտ» նկարագրում փոփոխականների միջև կապը, անհրաժեշտ է որոշել այն չափանիշը, որով կհամեմատվեն գծերը:

Որպես այդպիսի չափանիշ մենք օգտագործում ենք արտահայտությունը.

Որտեղ ŷ ես = ա * x i + բ ; n – արժեքների զույգերի քանակը (մեր դեպքում n=20)

Վերոնշյալ արտահայտությունը y i և ŷ i դիտարկված արժեքների միջև քառակուսի հեռավորությունների գումարն է և հաճախ նշվում է որպես SSE ( Գումար -ից Քառակուսի Սխալներ (Մնացորդներ), քառակուսի սխալների գումարը (մնացորդները)) .

Նվազագույն քառակուսի մեթոդայսպիսի տող ընտրելն է ŷ = կացին + բ, որի համար վերը նշված արտահայտությունը վերցնում է նվազագույն արժեքը։

Նշում:Երկչափ տարածության ցանկացած գիծ եզակիորեն որոշվում է 2 պարամետրի արժեքներով. ա (լանջի) և բ (հերթափոխ):

Ենթադրվում է, որ որքան փոքր է քառակուսի հեռավորությունների գումարը, այնքան համապատասխան տողը ավելի լավ է մոտեցնում առկա տվյալներին և կարող է հետագայում օգտագործվել x փոփոխականից y-ի արժեքները կանխատեսելու համար: Հասկանալի է, որ նույնիսկ եթե իրականում փոփոխականների միջև հարաբերություն չկա կամ հարաբերությունը ոչ գծային է, ապա OLS-ը դեռ կընտրի «լավագույն» տողը: Այսպիսով, նվազագույն քառակուսիների մեթոդը ոչինչ չի ասում փոփոխականների միջև իրական հարաբերությունների առկայության մասին, մեթոդը պարզապես թույլ է տալիս ընտրել նման գործառույթի պարամետրերը: ա Եվ բ , որի համար վերը նշված արտահայտությունը նվազագույն է։

Կատարելով ոչ շատ բարդ մաթեմատիկական գործողություններ (մանրամասների համար տե՛ս), կարող եք հաշվարկել պարամետրերը ա Եվ բ :

Ինչպես երևում է բանաձևից, պարամետրը ա ներկայացնում է կովարիանսի հարաբերակցությունը և, հետևաբար, MS EXCEL-ում՝ պարամետրը հաշվարկելու համար Ա կարող է օգտագործվել հետևյալ բանաձևերը(սմ. Գծային թերթիկի օրինակ ֆայլ):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45)կամ

= ԿՈՎԱՐԻԱՆՍ.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Նաև պարամետրը հաշվարկելու համար Ա Դուք կարող եք օգտագործել բանաձևը = TILT (C26:C45;B26:B45). Պարամետրի համար բ օգտագործեք բանաձևը = LEG (C26:C45;B26:B45) .

Վերջապես, LINEST() ֆունկցիան թույլ է տալիս միաժամանակ հաշվարկել երկու պարամետրերը: Բանաձև մուտքագրելու համար LINEST(C26:C45;B26:B45)Դուք պետք է ընտրեք 2 բջիջ անընդմեջ և սեղմեք CTRL + SHIFT + ՄՏՆԵԼ(տես հոդվածի մասին): Արժեքը կվերադարձվի ձախ բջիջում Ա , աջ կողմում - բ .

ՆշումՆերածման հետ խառնաշփոթությունից խուսափելու համար զանգվածի բանաձևերդուք պետք է լրացուցիչ օգտագործեք INDEX() ֆունկցիան: Բանաձև = INDEX(LINEST(C26:C45,B26:B45),1)կամ պարզապես = LINEST(C26:C45;B26:B45)կվերադարձնի գծի թեքության համար պատասխանատու պարամետրը, այսինքն. Ա . Բանաձև = INDEX(LINEST(C26:C45,B26:B45),2)կվերադարձնի Y առանցքի հետ գծի հատման համար պատասխանատու պարամետրը, այսինքն. բ .

Հաշվարկելով պարամետրերը, ցրման դիագրամկարող եք գծել համապատասխան գիծը:

Նվազագույն քառակուսիների մեթոդով ուղիղ գիծ գծելու մեկ այլ եղանակ է գրաֆիկի գործիքը Թրենդային գիծ. Դա անելու համար ընտրեք դիագրամը, ընտրեք ընտրացանկից Դասավորության ներդիր, Վ խմբային վերլուծությունսեղմել Թրենդային գիծ, ապա Գծային մոտարկում .

Նշելով երկխոսության վանդակում «ցուցադրել հավասարումը դիագրամում» վանդակը, կարող եք համոզվել, որ վերը նշված պարամետրերը համընկնում են դիագրամի արժեքներին:

ՆշումՈրպեսզի պարամետրերը համընկնեն, դիագրամի տեսակը պետք է լինի: Բանն այն է, որ դիագրամ կառուցելիս Ժամանակացույց X-առանցքի արժեքները չեն կարող սահմանվել օգտագործողի կողմից (օգտագործողը կարող է նշել միայն պիտակներ, որոնք չեն ազդում կետերի գտնվելու վայրի վրա): X արժեքների փոխարեն օգտագործվում է 1 հաջորդականությունը. 2; 3; ... (համարակալման կատեգորիաների համար): Հետեւաբար, եթե դուք կառուցեք միտում գիծտիպային դիագրամի վրա Ժամանակացույց, ապա X-ի իրական արժեքների փոխարեն կօգտագործվեն այս հաջորդականության արժեքները, ինչը կհանգեցնի սխալ արդյունքի (եթե, իհարկե, փաստացի արժեքներ X-ը չեն համապատասխանում 1-ին հաջորդականությանը; 2; 3; ...).

Այն ունի բազմաթիվ կիրառություններ, քանի որ թույլ է տալիս տրված ֆունկցիայի մոտավոր ներկայացում այլ ավելի պարզ գործառույթներով։ LSM-ը կարող է չափազանց օգտակար լինել դիտարկումների մշակման համար, և այն ակտիվորեն օգտագործվում է որոշ քանակություններ գնահատելու համար՝ հիմնվելով պատահական սխալներ պարունակող մյուսների չափումների արդյունքների վրա: Այս հոդվածում դուք կսովորեք, թե ինչպես իրականացնել նվազագույն քառակուսիների հաշվարկները Excel-ում:

Խնդրի շարադրանք՝ օգտագործելով կոնկրետ օրինակ

Ենթադրենք, որ կան երկու ցուցիչներ X և Y: Ավելին, Y-ը կախված է X-ից: Քանի որ OLS-ը մեզ հետաքրքրում է ռեգրեսիոն վերլուծության տեսանկյունից (Excel-ում դրա մեթոդներն իրականացվում են ներկառուցված գործառույթների միջոցով), մենք պետք է անմիջապես անցնենք դիտարկմանը. կոնկրետ խնդիր.

Այսպիսով, թող X-ը լինի մթերային խանութի մանրածախ տարածքը՝ չափված քառակուսի մետրերով, իսկ Y-ը՝ միլիոնավոր ռուբլով չափվող տարեկան շրջանառությունը:

Պահանջվում է կանխատեսում անել, թե ինչ շրջանառություն (Y) կունենա խանութը, եթե ունի այս կամ այն ​​մանրածախ տարածք։ Ակնհայտ է, որ Y = f (X) ֆունկցիան աճում է, քանի որ հիպերմարկետում ավելի շատ ապրանքներ են վաճառվում, քան կրպակը:

Մի քանի խոսք կանխատեսման համար օգտագործվող սկզբնական տվյալների ճիշտության մասին

Ենթադրենք, մենք ունենք աղյուսակ, որը կառուցվել է տվյալների օգտագործմամբ n խանութների համար:

Ըստ մաթեմատիկական վիճակագրության՝ արդյունքները քիչ թե շատ ճիշտ կլինեն, եթե հետազոտվեն առնվազն 5-6 օբյեկտների վերաբերյալ տվյալներ։ Բացի այդ, «անոմալ» արդյունքները չեն կարող օգտագործվել: Մասնավորապես, էլիտար փոքր բուտիկը կարող է ունենալ մի քանի անգամ ավելի մեծ շրջանառություն, քան «masmarket» դասի խոշոր մանրածախ կետերի շրջանառությունը։

Մեթոդի էությունը

Աղյուսակի տվյալները կարելի է պատկերել դեկարտյան հարթության վրա M 1 (x 1, y 1), ... M n (x n, y n) կետերի տեսքով: Այժմ խնդրի լուծումը կկրճատվի y = f (x) մոտավոր ֆունկցիայի ընտրությամբ, որն ունի M 1, M 2, .. M n կետերին հնարավորինս մոտ անցնող գրաֆիկ:

Իհարկե, դուք կարող եք օգտագործել բարձր աստիճանի բազմանդամ, բայց այս տարբերակը ոչ միայն դժվար է իրականացնել, այլև պարզապես սխալ է, քանի որ այն չի արտացոլի հիմնական միտումը, որը պետք է հայտնաբերել: Ամենախելամիտ լուծումը y = ax + b ուղիղ գիծը փնտրելն է, որը լավագույնս մոտավոր է փորձարարական տվյալներին, ավելի ճիշտ՝ a և b գործակիցներին։

Ճշգրտության գնահատում

Ցանկացած մոտավորության դեպքում դրա ճշգրտության գնահատումը առանձնահատուկ նշանակություն ունի: Եկեք e i-ով նշենք x i կետի ֆունկցիոնալ և փորձարարական արժեքների տարբերությունը (շեղումը), այսինքն e i = y i - f (x i):

Ակնհայտ է, որ մոտարկման ճշգրտությունը գնահատելու համար կարող եք օգտագործել շեղումների գումարը, այսինքն՝ X-ի կախվածության Y-ից մոտավոր ներկայացման համար ուղիղ գիծ ընտրելիս պետք է նախապատվությունը տալ նրան, որն ունի ամենափոքր արժեքը: գումարել e i բոլոր քննարկվող կետերում: Այնուամենայնիվ, ամեն ինչ այնքան էլ պարզ չէ, քանի որ դրական շեղումների հետ միասին կլինեն նաև բացասական:

Խնդիրը կարելի է լուծել՝ օգտագործելով շեղման մոդուլները կամ դրանց քառակուսիները։ Վերջին մեթոդը ամենաշատ կիրառվողն է։ Այն օգտագործվում է բազմաթիվ ոլորտներում, ներառյալ ռեգրեսիոն վերլուծությունը (իրագործվում է Excel-ում՝ օգտագործելով երկու ներկառուցված գործառույթներ), և վաղուց ապացուցել է իր արդյունավետությունը:

Նվազագույն քառակուսի մեթոդ

Excel-ը, ինչպես գիտեք, ունի ներկառուցված AutoSum ֆունկցիա, որը թույլ է տալիս հաշվարկել ընտրված տիրույթում գտնվող բոլոր արժեքների արժեքները: Այսպիսով, ոչինչ չի խանգարի մեզ հաշվարկել արտահայտության արժեքը (e 1 2 + e 2 2 + e 3 2 + ... e n 2):

Մաթեմատիկական նշումով սա նման է.

Քանի որ ի սկզբանե որոշում է կայացվել մոտավորել ուղիղ գծով, մենք ունենք.

Այսպիսով, X և Y մեծությունների հատուկ կախվածությունը լավագույնս նկարագրող ուղիղ գիծը գտնելու խնդիրը հանգում է երկու փոփոխականների ֆունկցիայի նվազագույնի հաշվարկին.

Դա անելու համար հարկավոր է մասնակի ածանցյալները a և b նոր փոփոխականների նկատմամբ հավասարեցնել զրոյի և լուծել պարզունակ համակարգ, որը բաղկացած է երկու հավասարումներից 2 անհայտ ձևով.

Որոշ պարզ փոխակերպումներից հետո, ներառյալ բաժանումը 2-ի և գումարների շահարկումից հետո, մենք ստանում ենք.

Լուծելով այն, օրինակ, օգտագործելով Քրամերի մեթոդը, մենք ստանում ենք անշարժ կետ a * և b * որոշակի գործակիցներով: Սա նվազագույնն է, այսինքն՝ կանխատեսելու համար, թե ինչ շրջանառություն կունենա խանութը որոշակի տարածքի համար, հարմար է y = a * x + b * ուղիղ գիծը, որը ռեգրեսիոն մոդել է տվյալ օրինակի համար։ Իհարկե, դա թույլ չի տա ձեզ գտնել ճշգրիտ արդյունքը, բայց դա կօգնի ձեզ պատկերացում կազմել, թե արդյոք խանութի ապառիկով որոշակի տարածք գնելը կվճարի:

Ինչպես իրականացնել նվազագույն քառակուսիները Excel-ում

Excel-ն ունի նվազագույն քառակուսիների օգտագործմամբ արժեքները հաշվարկելու գործառույթ: Այն ունի հետևյալ ձևը. «TREND» (հայտնի Y արժեքներ; հայտնի X արժեքներ; նոր X արժեքներ; հաստատուն): Եկեք կիրառենք Excel-ում OLS-ի հաշվարկման բանաձևը մեր աղյուսակում:

Դա անելու համար մուտքագրեք «=» նշանը այն բջիջում, որտեղ պետք է ցուցադրվի Excel-ում նվազագույն քառակուսիների մեթոդով հաշվարկի արդյունքը և ընտրեք «TREND» ֆունկցիան: Բացվող պատուհանում լրացրեք համապատասխան դաշտերը՝ ընդգծելով.

• Y-ի հայտնի արժեքների շրջանակը (այս դեպքում՝ առևտրի շրջանառության տվյալներ).
• միջակայք x 1, …x n, այսինքն՝ մանրածախ տարածքի չափը;
• x-ի և՛ հայտնի, և՛ անհայտ արժեքները, որոնց համար անհրաժեշտ է պարզել շրջանառության չափը (աշխատանքային թերթում դրանց գտնվելու վայրի մասին տեղեկությունների համար տե՛ս ստորև):

Բացի այդ, բանաձևը պարունակում է «Const» տրամաբանական փոփոխականը: Եթե ​​համապատասխան դաշտում մուտքագրեք 1, դա կնշանակի, որ դուք պետք է կատարեք հաշվարկները՝ ենթադրելով, որ b = 0:

Եթե ​​Ձեզ անհրաժեշտ է պարզել մեկից ավելի x արժեքի կանխատեսումը, ապա բանաձևը մուտքագրելուց հետո չպետք է սեղմել «Enter», այլ պետք է մուտքագրել «Shift» + «Control» + «Enter» համադրությունը ստեղնաշարի վրա:

Որոշ առանձնահատկություններ

Ռեգրեսիոն վերլուծությունը կարող է հասանելի լինել նույնիսկ կեղծիքների համար: Անհայտ փոփոխականների զանգվածի արժեքը կանխատեսելու Excel բանաձևը՝ TREND, կարող է օգտագործվել նույնիսկ նրանց կողմից, ովքեր երբեք չեն լսել նվազագույն քառակուսիների մասին: Բավական է միայն իմանալ նրա աշխատանքի որոշ առանձնահատկությունները։ Մասնավորապես:

• Եթե ​​դուք դասավորեք y փոփոխականի հայտնի արժեքների տիրույթը մեկ տողում կամ սյունակում, ապա յուրաքանչյուր տող (սյունակ) x-ի հայտնի արժեքներով ծրագրի կողմից կընկալվի որպես առանձին փոփոխական:
• Եթե ​​TREND պատուհանում նշված չէ հայտնի x-ով միջակայքը, ապա Excel-ում ֆունկցիան օգտագործելիս ծրագիրը այն կվերաբերվի որպես ամբողջ թվերից բաղկացած զանգված, որոնց թիվը համապատասխանում է տվյալ արժեքներով տիրույթին: փոփոխական y.
• «Կանխատեսված» արժեքների զանգված դուրս բերելու համար միտումը հաշվարկելու արտահայտությունը պետք է մուտքագրվի որպես զանգվածի բանաձև:
• Եթե ​​x-ի նոր արժեքները նշված չեն, ապա TREND ֆունկցիան դրանք համարում է հայտնիներին հավասար: Եթե ​​դրանք նշված չեն, ապա զանգված 1 ընդունվում է որպես արգումենտ; 2; 3; 4;…, որը համաչափ է արդեն նշված y պարամետրերով տիրույթին:
• Նոր x արժեքներ պարունակող միջակայքը պետք է ունենա նույն կամ ավելի տողեր կամ սյունակներ, ինչ տվյալ y արժեքները պարունակող միջակայքը: Այսինքն, այն պետք է համաչափ լինի անկախ փոփոխականներին։
• Հայտնի x արժեքներով զանգվածը կարող է պարունակել բազմաթիվ փոփոխականներ: Այնուամենայնիվ, եթե մենք խոսում ենք միայն մեկի մասին, ապա պահանջվում է, որ x և y-ի տրված արժեքներով միջակայքերը լինեն համաչափ: Մի քանի փոփոխականների դեպքում անհրաժեշտ է, որ տվյալ y արժեքներով միջակայքը տեղավորվի մեկ սյունակում կամ մեկ տողում։

PREDICTION ֆունկցիան

Իրականացվում է մի քանի գործառույթների միջոցով: Դրանցից մեկը կոչվում է «ԿԱՆԽԱՏԵՍՈՒԹՅՈՒՆ»: Այն նման է «TREND»-ին, այսինքն՝ տալիս է նվազագույն քառակուսիների մեթոդով հաշվարկների արդյունքը: Սակայն միայն մեկ X-ի համար, որի համար Y-ի արժեքը անհայտ է:

Այժմ դուք գիտեք բանաձևեր Excel-ում կեղծիքների համար, որոնք թույլ են տալիս կանխատեսել որոշակի ցուցանիշի ապագա արժեքը՝ գծային միտումի համաձայն: