Excel Solver қондырмасы математикалық оңтайландыруды орындайды. Бұл әдетте күрделі үлгілерді деректерге сәйкестендіру немесе мәселелердің қайталанатын шешімдерін табу үшін пайдаланылады. Мысалы, теңдеуді пайдаланып, кейбір деректер нүктелері арқылы қисық сызықты сәйкестендіру қажет болуы мүмкін. Шешуші деректерге ең жақсы сәйкестікті беретін теңдеудегі тұрақтыларды таба алады. Басқа қолданба - қажетті нәтижені теңдеу тақырыбына айналдыру үшін үлгіні қайта реттеу қиын.
Excelде шешуші қайда?
Шешуші қондырмасы Excel бағдарламасына кіреді, бірақ ол әрқашан әдепкі орнатудың бөлігі ретінде жүктелмейді. Оның жүктелгенін тексеру үшін DATA қойындысын таңдап, Талдау бөліміндегі Шешуші белгішесін іздеңіз..
Егер ДЕРЕКТЕР қойындысынан Шешуші таба алмасаңыз, қондырманы жүктеп алуыңыз керек:
-
ФАЙЛ қойындысын таңдап, содан кейін Параметрлер таңдаңыз.
Image -
Параметрлер диалогтық терезесінде сол жақтағы қойындылардан Қосымшалар таңдаңыз.
Image -
Терезенің төменгі жағында Басқару ашылмалы тізімінен Excel қондырмалары таңдаңыз және Өту…
Image -
Шешуші қондырмасы жанындағы құсбелгіні қойып, OK таңдаңыз.
Image -
Шешуші пәрмені енді DATA қойындысында пайда болуы керек. Сіз шешуші қолданбаны пайдалануға дайынсыз.
Image
Excel бағдарламасында шешуші пайдалану
Шешуші не істейтінін түсіну үшін қарапайым мысалдан бастайық. Біз ауданы 50 шаршы бірлік шеңберге қандай радиус беретінін білгіміз келетінін елестетіп көріңіз. Біз шеңбер ауданының теңдеуін білеміз (A=pi r2). Әрине, берілген аумаққа қажетті радиусты беру үшін бұл теңдеуді қайта реттей аламыз, бірақ мысал үшін мұны қалай істеу керектігін білмейміз делік.
Радиусы B1 болатын электрондық кесте жасаңыз және =pi(теңдеуін пайдаланып B2 ауданын есептеңіз)B1^2.
Біз B1 ішіндегі мәнді B2 50-ге жеткілікті жақын мәнді көрсеткенше қолмен реттей аламыз. Қаншалықты дәлдікке байланысты қажет болса, бұл практикалық тәсіл болуы мүмкін. Дегенмен, өте дәл болу керек болса, қажетті түзетулерді енгізу үшін көп уақыт қажет. Шын мәнінде, бұл шешуші жасайды. Ол белгілі бір ұяшықтардағы мәндерге түзетулер жасайды және мақсатты ұяшықтағы мәнді тексереді:
- DATA қойындысын және Шешуші таңдаңыз, Шешуші параметрлері диалогтық терезесін
-
Мақсат ұяшықты Аймақ, B2 етіп қойыңыз. Бұл дұрыс мәнге жеткенше басқа ұяшықтарды реттеп, тексерілетін мән.
Image -
Мәні: түймешігін таңдап, 50 мәнін орнатыңыз. Бұл B2 жетуі керек мән.
Image -
Айнымалы ұяшықтарды өзгерту арқылы: радиусы бар ұяшықты енгізіңіз, B1.
Image -
Басқа опцияларды әдепкі бойынша қалдырыңыз және Шешу таңдаңыз. Оңтайландыру орындалды, B1 мәні B2 50 болғанша реттеледі және Нәтижелерді шешу диалогы көрсетіледі.
Image -
Шешімді сақтау үшін OK таңдаңыз.
Image
Бұл қарапайым мысал шешуші қалай жұмыс істейтінін көрсетті. Бұл жағдайда шешімді басқа жолдармен оңайырақ таба алар едік. Әрі қарай шешуші басқа жолмен табу қиын болатын шешімдерді беретін кейбір мысалдарды қарастырамыз.
Excel шешуші қондырмасының көмегімен күрделі үлгіні орнату
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 - жылдамдық, a - айналу кедергісі үшін коэффициент, b - айналу кедергісі үшін коэффициент. аэродинамикалық кедергі. Бұл өте қарапайым теңдеу болғанымен, берілген қуат кірісіне көліктің жететін жылдамдығының теңдеуін беру үшін қайта реттеу оңай емес. Дегенмен, біз бұл жылдамдықты итеративті түрде табу үшін шешуші пайдалана аламыз. Мысалы, 740 Вт қуатпен қол жеткізілген жылдамдықты табыңыз.
-
Жылдамдық, a және b коэффициенттері және олардан есептелген қуат көрсетілген қарапайым электрондық кестені орнатыңыз.
Image -
Шешушіні іске қосыңыз және мақсат ретінде қуатты енгізіңіз, B5. 740 объективті мәнін орнатыңыз және өзгеретін ұяшықтар ретінде жылдамдықты, B2 таңдаңыз. Шешімді бастау үшін шешу таңдаңыз.
Image -
Шешуші жылдамдық мәнін қуат 740-қа жақын болғанша реттеп, бізге қажетті жылдамдықты қамтамасыз етеді.
Image - Модельдерді осылай шешу күрделі үлгілерді инверттеуден гөрі жиі жылдамырақ және қателікке бейім болуы мүмкін.
Шешушіде қолжетімді әртүрлі опцияларды түсіну өте қиын болуы мүмкін. Ақылға қонымды шешімді алуда қиындықтар туындаса, өзгермелі ұяшықтарға шекаралық шарттарды қолдану жиі пайдалы. Бұл шекті мәндер, одан тыс оларды реттеуге болмайды. Мысалы, алдыңғы мысалда жылдамдық нөлден кем болмауы керек және жоғарғы шекті орнатуға болады. Бұл көліктің жылдам жүре алмайтынына сенімді болатын жылдамдық болар еді. Егер сіз өзгермелі айнымалы ұяшықтар үшін шекараларды орната алсаңыз, ол сонымен қатар мультистарт сияқты басқа кеңейтілген опциялардың жұмысын жақсартады. Бұл айнымалылар үшін әртүрлі бастапқы мәндерден басталатын бірнеше түрлі шешімдерді іске қосады.
Шешу әдісін таңдау да қиын болуы мүмкін. Simplex LP тек сызықтық модельдер үшін жарамды, егер мәселе сызықтық болмаса, бұл шарт орындалмағаны туралы хабарламамен сәтсіздікке ұшырайды. Қалған екі әдіс те сызықтық емес әдістерге сәйкес келеді. GRG Nonlinear – ең жылдам, бірақ оның шешімі бастапқы іске қосу шарттарына өте тәуелді болуы мүмкін. Оның икемділігі бар, ол айнымалы мәндерге шектеулер қоюды қажет етпейді. Эволюциялық шешуші жиі ең сенімді болып табылады, бірақ ол барлық айнымалы мәндердің жоғарғы және төменгі шекараларының болуын талап етеді, оларды алдын ала анықтау қиын болуы мүмкін.
Excel Solver қондырмасы - көптеген практикалық мәселелерге қолдануға болатын өте қуатты құрал. Excel мүмкіндігін толық пайдалану үшін Шешуші бағдарламасын Excel макростарымен біріктіріп көріңіз.
