Excel Solver кошумчасы математикалык оптималдаштырууну аткарат. Бул, адатта, татаал моделдерди маалыматтарга ылайыкташтыруу же көйгөйлөргө итеративдик чечимдерди табуу үчүн колдонулат. Мисалы, сиз теңдемени колдонуп, кээ бир маалымат чекиттери аркылуу ийри сызыкты туура келтирүүнү кааласаңыз болот. Чечимчи теңдемеден берилиштерге эң туура туура келген константаларды таба алат. Дагы бир колдонмо, талап кылынган натыйжаны теңдеменин предмети кылуу үчүн моделди кайра иретке келтирүү кыйынга турат.
Excelде чечүүчү кайда?
Чечүүчү кошумчасы Excel менен камтылган, бирок ал ар дайым демейки орнотуунун бир бөлүгү катары жүктөлбөйт. Анын жүктөлгөндүгүн текшерүү үчүн VERILER өтмөгүн тандап, Анализ бөлүмүндөгү Чечүүчү сөлөкөтүн издеңиз..
Эгер сиз DATA өтмөгүнөн Чыгаруучуну таба албасаңыз, анда кошумча бөлүктү жүктөшүңүз керек болот:
-
ФАЙЛ өтмөгүн тандап, андан соң Параметрлер тандаңыз.
Image -
Параметрлер диалог кутусунда сол жактагы өтмөктөрдөн Кошумчаларды тандаңыз.
Image -
Терезенин ылдый жагында, Башкаруу ачылуучу тизмеден Excel Кошумчаларын тандаңыз жана Өтүү…
Image -
Чечүүчү кошумчанын жанындагы белги кутучасын белгилеп, OK тандаңыз.
Image -
Чечүүчү буйругу эми ДАТА өтмөгүндө пайда болушу керек. Чечимди колдонууга даярсыз.
Image
Excelде чечүүчүнү колдонуу
Чечүүчү эмне кыларын түшүнүү үчүн жөнөкөй мисалдан баштайлы. Элестеткиле, биз аянты 50 чарчы бирдик болгон айланага кандай радиус берерин билгибиз келет. Биз айлананын аянты үчүн теңдемени билебиз (A=pi r2). Биз, албетте, бул теңдемени берилген аймак үчүн талап кылынган радиусту берүү үчүн кайра иретке келтирсек болот, бирок мисал үчүн биз муну кантип кылууну билбейбиз деп коёлу.
Радиусу B1 менен электрондук жадыбал түзүңүз жана =pi(теңдемесин колдонуп B2 аянтын эсептеңиз)B1^2.
Биз B1 ичиндеги маанини B2 50гө жакын маанини көрсөткөнгө чейин кол менен тууралай алабыз. Канчалык так болгонубузга жараша болушу керек, бул практикалык мамиле болушу мүмкүн. Бирок, биз абдан так болушу керек болсо, анда талап кылынган түзөтүүлөрдү киргизүү үчүн көп убакыт талап кылынат. Чынында, бул Solver эмне кылат. Ал белгилүү бир уячалардагы маанилерге тууралоолорду киргизет жана максаттуу уячадагы маанини текшерет:
- DATA өтмөгүн жана Чечүүчү тандаңыз, Чечүүчү Параметрлер диалог кутучасын жүктөө
-
Максат коюңуз уячаны аймак болушу үчүн, B2. Бул туура мааниге жеткенге чейин башка уячалар туураланып, текшериле турган маани.
Image -
Мааниси: баскычын тандап, 50 маанисин коюңуз. Бул B2 жетиши керек болгон маани.
Image -
Өзгөрмө уячаларын өзгөртүү менен: деген кутучага радиусу бар уячаны киргизиңиз, B1.
Image -
Башка параметрлерди демейки боюнча калтырып, Чечүү тандаңыз. Оптималдаштыруу жүргүзүлөт, B1 мааниси B2 50 болгонго чейин туураланат жана Чечүү натыйжалары диалогу көрсөтүлөт.
Image -
Чечимди сактоо үчүн OK тандаңыз.
Image
Бул жөнөкөй мисал чечүүчү кантип иштээрин көрсөттү. Бул учурда, биз башка жолдор менен чечүүнү оңой эле таба алмакпыз. Эми биз Solver башка жолду табуу кыйын болгон чечимдерди берген мисалдарды карап чыгабыз.
Татаал моделди Excel Solver кошумчасы менен орнотуу
Excel сызыктуу регрессияны аткаруу үчүн орнотулган функцияга ээ, ал түз сызыкты берилиштер жыйындысы аркылуу өткөрөт. Көптөгөн жалпы сызыктуу эмес функциялар сызыктуу болушу мүмкүн, башкача айтканда сызыктуу регрессия экспоненциалдар сыяктуу функцияларды тууралоо үчүн колдонулушу мүмкүн. Татаал функциялар үчүн Чечимчи "эң аз квадраттарды кичирейтүүнү" аткаруу үчүн колдонулушу мүмкүн. Бул мисалда ax^b+cx^d түрүндөгү теңдемени төмөндө көрсөтүлгөн маалыматтарга тууралоону карайбыз.
Бул төмөнкү кадамдарды камтыйт:
- Дайындар топтомун A тилкесиндеги x маанилери жана В тилкесиндеги y-маанилери менен иретке келтириңиз.
- Электрондук жадыбалдын бир жеринде 4 коэффициенттин маанисин түзүңүз (a, b, c жана d), аларга ыктыярдуу баштапкы маанилер берилиши мүмкүн.
-
2-кадамда түзүлгөн коэффициенттерге жана А тилкесиндеги x маанилерине шилтеме берген ax^b+cx^d түрүндөгү теңдемени колдонуп, орнотулган Y маанилеринин тилкесин түзүңүз. Формуланы көчүрүү үчүн ылдый жагына буруңуз. тилкеде, коэффициенттерге шилтемелер абсолюттук, ал эми x маанилерине шилтемелер салыштырмалуу болушу керек.
Image -
Маанилүү болбосо да, бир XY чачыратуу диаграммасындагы x маанилерине каршы эки y тилкесин теңдөө менен теңдеме канчалык туура келгенин визуалдык көрсөткүчкө ала аласыз. Түпнуска берилиш чекиттери үчүн маркерлерди колдонуу мааниси бар, анткени алар ызы-чуу менен дискреттик маанилер жана орнотулган теңдеме үчүн сызыкты колдонуу.
Image -
Андан кийин бизге маалыматтар менен биздин орнотулган теңдеменин ортосундагы айырманы сандык эсептөөнүн жолу керек. Муну жасоонун стандарттуу жолу – квадраттык айырмалардын суммасын эсептөө. Үчүнчү тилкеде, ар бир сап үчүн Y үчүн баштапкы маалымат мааниси орнотулган теңдеменин маанисинен алынып салынат жана натыйжа квадратталат. Ошентип, D2, маани менен берилет=(C2-B2)^2 Андан кийин бардык бул квадраттык маанилердин суммасы эсептелет. Маанилердин квадраты болгондуктан, алар оң гана болушу мүмкүн.
Image -
Сиз эми чечүүчү аркылуу оптималдаштырууну аткарууга даярсыз. Түзөтүүнү талап кылган төрт коэффициент бар (a, b, c жана d). Ошондой эле сизде минималдаштыруу үчүн бирдиктүү объективдүү маани бар, квадраттык айырмалардын суммасы. Жогорудагыдай чечүүчүнү иштетиңиз жана төмөндө көрсөтүлгөндөй бул маанилерге шилтеме кылуу үчүн чечүүчү параметрлерди орнотуңуз.
Image -
Чексиз өзгөрмөлөрдү терс эмес кылуу опциясын алып салыңыз, бул бардык коэффициенттерди оң маанилерди алууга мажбурлайт.
Image -
Чечүү тандаңыз жана натыйжаларды карап чыгыңыз. Диаграмма жаңыртылат жана ылайыктуулугун жакшы көрсөтүп турат. Эгерде чечүүчү биринчи аракетте жакшы шайкеш келбесе, аны кайра иштетүүгө аракет кылсаңыз болот. Эгерде тууралоо жакшырса, учурдагы маанилерден чечип көрүңүз. Болбосо, чечүүдөн мурун тууралоону кол менен жакшыртып көрүңүз.
Image - Жакшы ылайыктуу болгондон кийин, чечүүчүдөн чыксаңыз болот.
Моделди кайталап чечүү
Кээде салыштырмалуу жөнөкөй теңдеме бар, ал кандайдыр бир киргизүү жагынан жыйынтык берет. Бирок, биз маселени тескери аракет кылганда, жөнөкөй чечим табуу мүмкүн эмес. Мисалы, унаа керектеген кубаттуулук болжол менен P=av + bv^3 менен берилет, мында v – ылдамдык, а – тоголонуу каршылыгы үчүн коэффициент, ал эми b – айлануу каршылыгы үчүн коэффициент. аэродинамикалык сүйрөө. Бул абдан жөнөкөй теңдеме болсо да, берилген кубаттуулук үчүн унаа жете турган ылдамдыктын теңдемесин берүү оңой эмес. Бирок, биз бул ылдамдыкты кайталап табуу үчүн Solverди колдоно алабыз. Мисалы, 740 Вт кубаттуулук менен жеткен ылдамдыкты табыңыз.
-
Ылдамдык, a жана b коэффициенттери жана алардан эсептелген кубаттуулук менен жөнөкөй жадыбалды түзүңүз.
Image -
Чечүүчүнү ишке киргизиңиз жана максат катары күчтү, B5 киргизиңиз. 740 объективдүү маанисин коюңуз жана өзгөрүлүүчү уячалар катары ылдамдыкты, B2 тандаңыз. Чечүүнү баштоо үчүн чечүү тандаңыз.
Image -
Чечүүчү ылдамдыктын маанисин кубат 740ка жакын болгонго чейин тууралап, биз талап кылган ылдамдыкты камсыздайт.
Image - Моделдерди ушундай жол менен чечүү татаал моделдерди тескери салууга караганда тезирээк жана азыраак ката кетириши мүмкүн.
Чечүүчүдө жеткиликтүү болгон ар кандай варианттарды түшүнүү бир топ кыйын болушу мүмкүн. Эгер сиз акылга сыярлык чечимди алууда кыйынчылыкка туш болуп жатсаңыз, анда өзгөрүлүүчү уячаларга чектик шарттарды колдонуу көп учурда пайдалуу. Бул чектүү маанилер, андан ашкандан кийин аларды жөнгө салууга болбойт. Мисалы, мурунку мисалда ылдамдык нөлдөн кем болбошу керек жана жогорку чекти коюуга да болот. Бул унаа ылдамыраак бара албайт деп ишенген ылдамдык болот. Эгерде сиз өзгөрүлүүчү өзгөрмө уячаларына чек коюуга жөндөмдүү болсоңуз, анда ал башка өркүндөтүлгөн параметрлерди жакшыраак иштетет, мисалы, көп баштоо. Бул өзгөрмөлөр үчүн ар кандай баштапкы маанилерден баштап бир катар ар кандай чечимдерди иштетет.
Чечүү ыкмасын тандоо да кыйын болушу мүмкүн. Simplex LP сызыктуу моделдер үчүн гана ылайыктуу, эгерде маселе сызыктуу болбосо, ал бул шарт аткарылган жок деген билдирүү менен ишке ашпай калат. Калган эки ыкма тең сызыктуу эмес методдорго ылайыктуу. GRG Nonlinear эң тез, бирок анын чечими баштапкы баштоо шарттарына абдан көз каранды болушу мүмкүн. Анын ийкемдүүлүгү бар, ал өзгөрмөлөрдүн чектерин коюуну талап кылбайт. Эволюциялык чечүүчү көбүнчө эң ишенимдүү, бирок ал бардык өзгөрмөлөрдүн үстүнкү жана төмөнкү чектерине ээ болушун талап кылат, аларды алдын ала иштеп чыгуу кыйын болушу мүмкүн.
Excel Solver кошумчасы көптөгөн практикалык көйгөйлөргө колдонула турган абдан күчтүү курал. Excel'дин күчүн толук колдонуу үчүн, Чечимди Excel макростору менен айкалыштырууга аракет кылыңыз.
