Excel Solver հավելումը կատարում է մաթեմատիկական օպտիմալացում: Սա սովորաբար օգտագործվում է բարդ մոդելները տվյալներին համապատասխանեցնելու կամ խնդիրների կրկնվող լուծումներ գտնելու համար: Օրինակ, դուք կարող եք հարմարեցնել կորը որոշ տվյալների կետերի միջոցով՝ օգտագործելով հավասարումը: Լուծիչը կարող է գտնել հավասարման մեջ այն հաստատունները, որոնք լավագույնս համապատասխանում են տվյալներին: Մեկ այլ կիրառություն այն է, որտեղ դժվար է մոդելը վերադասավորել՝ պահանջվող արդյունքը հավասարման առարկա դարձնելու համար:
Որտե՞ղ է Solver-ը Excel-ում:
Solver հավելումը ներառված է Excel-ում, սակայն այն միշտ չէ, որ բեռնվում է որպես լռելյայն տեղադրման մաս: Ստուգելու համար, թե արդյոք այն բեռնված է, ընտրեք DATA ներդիրը և փնտրեք Solver պատկերակը Analysis բաժնում:.
Եթե դուք չեք կարող գտնել Solver-ը ՏՎՅԱԼՆԵՐԻ ներդիրում, ապա ձեզ հարկավոր է բեռնել հավելումը.
-
Ընտրեք FILE ներդիրը և այնուհետև ընտրեք Ընտրանքներ:
Image -
Ընտրանքներ երկխոսության վանդակում ձախ կողմի ներդիրներից ընտրեք Ավելացումներ:
Image -
Պատուհանի ներքևում ընտրեք Excel հավելումներ Կառավարել բացվող ցանկից և ընտրեք Գնալ…
Image -
Ստուգեք վանդակը Solver Add-in-ի կողքին և ընտրեք OK:
Image -
Solver հրամանն այժմ պետք է հայտնվի DATA ներդիրում: Դուք պատրաստ եք օգտագործել Solver-ը:
Image
Օգտագործելով Solver Excel-ում
Եկեք սկսենք պարզ օրինակից՝ հասկանալու համար, թե ինչ է անում Լուծողը: Պատկերացրեք, որ մենք ուզում ենք իմանալ, թե ինչ շառավիղ կտա 50 քառակուսի միավոր մակերես ունեցող շրջանագիծը: Մենք գիտենք շրջանագծի մակերեսի հավասարումը (A=pi r2): Մենք, իհարկե, կարող ենք վերադասավորել այս հավասարումը` տալով տվյալ տարածքի համար պահանջվող շառավիղը, բայց օրինակի համար եկեք ձևացնենք, որ չգիտենք, թե ինչպես դա անել:
Ստեղծեք աղյուսակ՝ B1-ի շառավղով և հաշվարկեք տարածքը B2-ում՝ օգտագործելով =pi ()B1^2.
Մենք կարող ենք ձեռքով կարգավորել արժեքը B1-ում, մինչև B2-ը ցույց տա արժեքը, որը բավական մոտ է 50-ին: Կախված նրանից, թե որքան ճշգրիտ ենք մենք պետք է լինի, սա կարող է լինել գործնական մոտեցում: Այնուամենայնիվ, եթե մենք պետք է շատ ճշգրիտ լինենք, ապա երկար ժամանակ կպահանջվի պահանջվող ճշգրտումները կատարելու համար:Փաստորեն, սա այն է, ինչ անում է Solver-ը: Այն ճշգրտումներ է կատարում որոշակի բջիջների արժեքներում և ստուգում է թիրախային բջիջի արժեքը՝
- Ընտրեք DATA ներդիրը և Solver ՝ Լուծվող Պարամետրեր երկխոսության տուփը բեռնելու համար
-
Նպատակը բջիջը սահմանեք որպես Տարածք, B2: Սա այն արժեքն է, որը կստուգվի՝ կարգավորելով մյուս բջիջները, մինչև այս մեկը հասնի ճիշտ արժեքին:
Image -
Ընտրեք կոճակը Արժեք՝ և սահմանեք արժեքը 50: Սա այն արժեքն է, որին պետք է հասնի B2-ը:
Image -
Վանդակում, որը վերնագրված է Փոփոխական բջիջները փոխելով. մուտքագրեք շառավիղը պարունակող բջիջը, B1.
Image -
Մյուս տարբերակները թողեք այնպես, ինչպես դրանք կան լռելյայն և ընտրեք Լուծել: Օպտիմալացումն իրականացվել է, B1-ի արժեքը ճշգրտվում է մինչև B2-ը դառնա 50, և ցուցադրվում է Solver Results երկխոսությունը::
Image -
Ընտրեք OK լուծումը պահպանելու համար:
Image
Այս պարզ օրինակը ցույց տվեց, թե ինչպես է աշխատում լուծիչը: Այս դեպքում մենք կարող էինք ավելի հեշտ լուծում ստանալ այլ ճանապարհներով։ Հաջորդիվ մենք կանդրադառնանք մի քանի օրինակների, որտեղ Solver-ը տալիս է լուծումներ, որոնք դժվար կլինի գտնել այլ ճանապարհ:
Կոմպլեքս մոդելի տեղադրում Excel Solver հավելվածի միջոցով
Excel-ն ունի ներկառուցված ֆունկցիա՝ իրականացնելու գծային ռեգրեսիա՝ մի շարք տվյալների միջով ուղիղ գիծ տեղադրելու համար: Շատ սովորական ոչ գծային ֆունկցիաներ կարող են գծայինացվել, ինչը նշանակում է, որ գծային ռեգրեսիան կարող է օգտագործվել այնպիսի գործառույթների համար, ինչպիսիք են էքսպոնենցիալը:Ավելի բարդ գործառույթների համար Լուծիչը կարող է օգտագործվել «նվազագույն քառակուսիների նվազագույնի հասցում» կատարելու համար: Այս օրինակում մենք կքննարկենք ax^b+cx^d ձևի հավասարումը ստորև ներկայացված տվյալներին:
Սա ներառում է հետևյալ քայլերը՝
- Դասավորեք տվյալների հավաքածուն A սյունակի x արժեքներով և B սյունակում y արժեքներով:
- Ստեղծեք 4 գործակիցների արժեքները (a, b, c և d) ինչ-որ տեղ աղյուսակի վրա, դրանք կարող են տրվել կամայական սկզբնական արժեքներ:
-
Ստեղծեք հարմարեցված Y արժեքների սյունակ՝ օգտագործելով ax^b+cx^d ձևի հավասարումը, որը հղում է անում 2-րդ քայլում ստեղծված գործակիցներին և Ա սյունակում x արժեքներին: Նկատի ունեցեք, որ բանաձևը ներքև պատճենելու համար սյունակում, գործակիցներին հղումները պետք է բացարձակ լինեն, մինչդեռ x արժեքներին հղումները պետք է հարաբերական լինեն:
Image -
Թեև էական չէ, դուք կարող եք տեսողական ցուցում ստանալ այն մասին, թե որքան լավ է համապատասխանում հավասարումը՝ գծելով երկու y սյունակները x արժեքների հետ մեկ XY ցրման գծապատկերում: Իմաստ է օգտագործել մարկերներ սկզբնական տվյալների կետերի համար, քանի որ դրանք աղմուկի հետ կապված դիսկրետ արժեքներ են, և հարմարեցված հավասարման համար օգտագործել գիծ:
Image -
Հաջորդը մեզ անհրաժեշտ է տվյալների և մեր հարմարեցված հավասարման միջև տարբերությունը քանակականացնելու եղանակ: Դա անելու ստանդարտ եղանակը քառակուսի տարբերությունների գումարը հաշվարկելն է: Երրորդ սյունակում, յուրաքանչյուր տողի համար, Y-ի սկզբնական տվյալների արժեքը հանվում է հարմարեցված հավասարման արժեքից, և արդյունքը քառակուսվում է: Այսպիսով, D2-ում արժեքը տրվում է =(C2-B2)^2-ով, այնուհետև հաշվարկվում է այս բոլոր քառակուսի արժեքների գումարը: Քանի որ արժեքները քառակուսի են, դրանք կարող են լինել միայն դրական:
Image -
Դուք այժմ պատրաստ եք կատարել օպտիմալացում Solver-ի միջոցով: Կան չորս գործակիցներ, որոնք պետք է ճշգրտվեն (a, b, c և d): Դուք նաև ունեք մեկ օբյեկտիվ արժեք՝ նվազագույնի հասցնելու համար՝ քառակուսի տարբերությունների գումարը: Գործարկեք լուծիչը, ինչպես վերևում, և սահմանեք լուծիչի պարամետրերը, որպեսզի հղում կատարեն այս արժեքներին, ինչպես ցույց է տրված ստորև:
Image -
Անջատեք ընտրանքը Անսահմանափակ փոփոխականները դարձնել ոչ բացասական, սա կստիպի բոլոր գործակիցներին ընդունել դրական արժեքներ:
Image -
Ընտրեք Լուծել և վերանայեք արդյունքները: Գծապատկերը կթարմացվի՝ լավ ցույց տալով համապատասխանության լավությունը: Եթե լուծիչը առաջին փորձից լավ չի համապատասխանում, կարող եք նորից փորձել այն գործարկել: Եթե համապատասխանությունը բարելավվել է, փորձեք լուծել ընթացիկ արժեքներից:Հակառակ դեպքում, դուք կարող եք փորձել ձեռքով բարելավել համապատասխանությունը նախքան լուծելը:
Image - Հենց լավ տեղավորվելուց հետո կարող եք դուրս գալ լուծիչից:
Մոդելի կրկնական լուծում
Երբեմն կա համեմատաբար պարզ հավասարում, որը տալիս է ելք որոշակի մուտքի առումով: Սակայն, երբ մենք փորձում ենք շրջել խնդիրը, հնարավոր չէ պարզ լուծում գտնել։ Օրինակ, մեքենայի սպառած հզորությունը մոտավորապես տրված է P=av + bv^3, որտեղ v-ն արագությունն է, a-ն շարժման դիմադրության գործակիցն է, իսկ b-ը գործակից է աերոդինամիկ քաշում. Թեև սա բավականին պարզ հավասարում է, հեշտ չէ վերադասավորել արագության հավասարումը, որը մեքենան կհասնի տվյալ էներգիայի մուտքագրման համար: Այնուամենայնիվ, մենք կարող ենք օգտագործել Solver այս արագությունը կրկնվող գտնելու համար: Օրինակ, գտե՛ք 740 Վտ մուտքային հզորությամբ ձեռք բերված արագությունը:
-
Կարգավորեք պարզ աղյուսակ՝ արագությամբ, a և b գործակիցներով և դրանցից հաշվարկված հզորությամբ:
Image -
Գործարկեք Լուծիչը և մուտքագրեք հզորությունը, B5, որպես նպատակ: Սահմանեք 740-ի օբյեկտիվ արժեքը և ընտրեք արագությունը՝ B2, որպես փոփոխվող փոփոխական բջիջ: Լուծումը սկսելու համար ընտրեք լուծել:
Image -
Լուծիչը կարգավորում է արագության արժեքը այնքան ժամանակ, մինչև հզորությունը շատ մոտ լինի 740-ին՝ ապահովելով մեզ անհրաժեշտ արագությունը։
Image - Մոդելների այս եղանակով լուծումը հաճախ կարող է ավելի արագ և ավելի քիչ սխալներ առաջացնել, քան բարդ մոդելները շրջելը:
Լուծիչում առկա տարբեր տարբերակները հասկանալը կարող է բավականին դժվար լինել:Եթե դուք դժվարանում եք խելամիտ լուծում ստանալ, ապա հաճախ օգտակար է սահմանային պայմաններ կիրառել փոփոխվող բջիջների նկատմամբ: Սրանք սահմանափակող արժեքներ են, որոնցից այն կողմ դրանք չպետք է ճշգրտվեն: Օրինակ, նախորդ օրինակում արագությունը չպետք է պակաս լինի զրոյից և հնարավոր կլիներ նաև վերին սահման դնել: Սա կլինի արագություն, որից վստահ եք, որ մեքենան չի կարող ավելի արագ ընթանալ: Եթե դուք ի վիճակի եք սահմաններ սահմանել փոփոխական փոփոխական բջիջների համար, ապա դա նաև թույլ է տալիս ավելի լավ աշխատել այլ ավելի առաջադեմ տարբերակների համար, օրինակ՝ բազմագործարկումը: Սա կգործարկի մի շարք տարբեր լուծումներ՝ սկսած փոփոխականների տարբեր սկզբնական արժեքներից:
Լուծման մեթոդի ընտրությունը նույնպես կարող է դժվար լինել: Simplex LP-ը հարմար է միայն գծային մոդելների համար, եթե խնդիրը գծային չէ, այն չի հաջողվի հաղորդագրությամբ, որ այս պայմանը չի բավարարվել: Մյուս երկու մեթոդները երկուսն էլ հարմար են ոչ գծային մեթոդներին: GRG Nonlinear-ը ամենաարագն է, բայց դրա լուծումը կարող է մեծապես կախված լինել սկզբնական մեկնարկային պայմաններից:Այն ունի ճկունություն, որ չի պահանջում փոփոխականների սահմանները սահմանելու համար: Էվոլյուցիոն լուծիչը հաճախ ամենահուսալին է, սակայն այն պահանջում է, որ բոլոր փոփոխականներն ունենան և՛ վերին, և՛ ստորին սահմաններ, ինչը կարող է դժվար լինել նախապես մշակել:
Excel Solver հավելումը շատ հզոր գործիք է, որը կարող է կիրառվել բազմաթիվ գործնական խնդիրների դեպքում: Excel-ի հզորությունը լիովին մուտք գործելու համար փորձեք համատեղել Solver-ը Excel մակրոների հետ:
