змест
«Пошук рашэння» - надбудова Excel, з дапамогай якой можна выбраць найлепшае рашэнне задач з улікам зададзеных абмежаванняў. Функцыя дазваляе планаваць супрацоўнікаў, размяркоўваць выдаткі або інвестыцыі. Ведаючы, як працуе гэтая функцыя, вы зэканоміце час і сілы.
Што такое пошук рашэнняў
У спалучэнні з рознымі іншымі опцыямі ў Excel ёсць адна менш папулярная, але вельмі патрэбная функцыя «Пошук рашэння». Нягледзячы на тое, што знайсці яго няпроста, знаёмства з ім і выкарыстанне дапамагае вырашыць мноства праблем. Опцыя апрацоўвае дадзеныя і выдае аптымальнае рашэнне з дазволеных. У артыкуле апісваецца, як непасрэдна функцыянуе Пошук рашэння.
Як уключыць функцыю «Пошук рашэння».
Нягледзячы на эфектыўнасць, разгляданая опцыя не знаходзіцца на бачным месцы панэлі інструментаў або кантэкстнага меню. Большасць карыстальнікаў, якія працуюць у Excel, не падазраюць аб яго наяўнасці. Па змаўчанні гэтая функцыя адключаная, каб яе адлюстраваць, выканайце наступныя дзеянні:
- Адкрыйце «Файл», націснуўшы на адпаведную назву.
- Націсніце на раздзел «Настройкі».
- Затым абярыце падраздзел «Дадаткі». Тут будуць адлюстраваны ўсе дапаўненні праграмы, унізе з'явіцца надпіс «Упраўленне». Справа ад яго будзе ўсплывальнае меню, дзе вы павінны выбраць «Надбудовы Excel». Затым націсніце «Перайсці».
1 - На маніторы адлюструецца дадатковае акно «Надбудовы». Усталюйце сцяжок побач з патрэбнай функцыяй і націсніце OK.
- Патрэбная функцыя з'явіцца на стужцы справа ад раздзела «Дадзеныя».
Аб мадэлях
Гэтая інфармацыя будзе вельмі карысная для тых, хто толькі знаёміцца з паняццем «мадэль аптымізацыі». Перад выкарыстаннем «Пошуку рашэння» рэкамендуецца вывучыць матэрыялы па методыцы пабудовы мадэляў:
- разгляданы варыянт дазволіць вызначыць аптымальны спосаб размеркавання сродкаў на інвестыцыі, загрузку памяшканняў, пастаўку тавараў або іншыя дзеянні, дзе неабходна знайсці аптымальнае рашэнне.
- «Аптымальны метад» у такой сітуацыі будзе азначаць: павелічэнне даходаў, зніжэнне выдаткаў, павышэнне якасці і г.д.
Тыповыя задачы аптымізацыі:
- Вызначэнне вытворчага плана, пры якім прыбытак ад рэалізацыі выпушчанай прадукцыі будзе максімальнай.
- Вызначэнне транспартных карт, падчас якіх транспартныя выдаткі мінімізуюцца.
- Пошук размеркавання некалькіх машын для розных відаў работ, каб знізіць сабекошт вытворчасці.
- Вызначэнне найкарацейшага тэрміну выканання работ.
Важна! Для фармалізацыі задачы неабходна стварыць мадэль, якая адлюстроўвае асноўныя параметры прадметнай вобласці. У Excel мадэль - гэта набор формул, якія выкарыстоўваюць зменныя. Разгляданы варыянт шукае такія паказчыкі, каб мэтавая функцыя была больш (менш) або роўная названай велічыні.
Падрыхтоўчы этап
Перш чым размясціць функцыю на стужцы, вам трэба зразумець, як функцыя працуе. Напрыклад, ёсць інфармацыя аб рэалізацыі тавараў, указаных у табліцы. Ставіцца задача на кожны тавар прызначыць зніжку ў памеры 4.5 мільёна рублёў. Параметр адлюстроўваецца ўнутры ячэйкі пад назвай target. На яго аснове разлічваюцца іншыя параметры.
Нашай задачай будзе разлічыць зніжку, на якую памнажаюцца сумы пры продажы розных тавараў. Гэтыя 2 элемента злучаны формулай, запісанай так: =D13*$G$2. Дзе ў D13 запісана агульная колькасць для рэалізацыі, а $G$2 - адрас патрэбнага элемента.
Выкарыстанне функцыі і яе налада
Калі формула будзе гатовая, трэба выкарыстоўваць непасрэдна саму функцыю:
- Трэба перайсці ў раздзел «Дадзеныя» і націснуць «Пошук рашэння».
- Адкрыецца «Параметры», дзе задаюцца неабходныя параметры. У радку «Аптымізаваць мэтавую функцыю:» неабходна паказаць вочка, у якой адлюстроўваецца сума скідак. Каардынаты можна прапісаць самастойна або выбраць з дакумента.
- Далей трэба перайсці да налад іншых параметраў. У раздзеле «Каму:» можна ўсталяваць максімальны і мінімальны ліміты або дакладную лічбу.
- Затым запаўняецца поле «Змена значэнняў зменных:». Сюды ўводзяцца дадзеныя патрэбнай ячэйкі, якая змяшчае канкрэтнае значэнне. Каардынаты прапісваюцца самастойна або націскаецца на адпаведную ячэйку ў дакуменце.
- Затым рэдагуецца закладка «Згодна з абмежаваннямі:», дзе задаюцца абмежаванні на прымяняюцца даныя. Напрыклад, выключаюцца дзесятковыя дробы або адмоўныя лікі.
- Пасля гэтага адкрываецца акно, якое дазваляе дадаць абмежаванні ў разліках. Пачатковы радок змяшчае каардынаты ячэйкі або цэлага дыяпазону. Пасля ўмоў задання пазначаюцца дадзеныя патрэбнай ячэйкі, дзе адлюстроўваецца індыкатар скідкі. Затым вызначаецца знак параўнання. Ён усталяваны на «больш або роўны», каб канчатковае значэнне не было са знакам мінус. У гэтай сітуацыі "Ліміт", усталяваны ў радку 3, роўны 0. Таксама можна ўсталяваць ліміт з дапамогай «Дадаць». Наступныя крокі тыя ж.
- Калі апісаныя вышэй крокі выкананы, усталяваны ліміт з'явіцца ў самым вялікім радку. Спіс можа быць вялікім і будзе залежаць ад складанасці разлікаў, аднак у канкрэтнай сітуацыі дастаткова 1 ўмовы.
- Акрамя таго, ёсць магчымасць выбраць іншыя дадатковыя налады. Унізе справа ёсць опцыя «Параметры», якая дазваляе гэта зрабіць.
- У наладах вы можаце ўсталяваць «Дакладнасць абмежавання» і «Межы рашэння». У нашай сітуацыі няма неабходнасці выкарыстоўваць гэтыя варыянты.
- Пасля завяршэння налад запускаецца сама функцыя - націсніце «Знайсці рашэнне».
- Пасля таго, як праграма выканае неабходныя разлікі і выдасць канчатковыя разлікі ў патрэбныя вочкі. Затым адкрываецца акно з вынікамі, дзе вынікі захоўваюцца / адмяняюцца, або параметры пошуку наладжваюцца па новым. Калі дадзеныя адпавядаюць патрабаванням, знойдзенае рашэнне захоўваецца. Калі папярэдне паставіць галачку ў полі «Вярнуцца ў дыялогавае акно параметраў пошуку рашэння», адкрыецца акно з наладамі функцыі.
- Ёсць верагоднасць, што разлікі апынуліся памылковымі або ёсць неабходнасць змяніць зыходныя дадзеныя, каб атрымаць іншыя паказчыкі. У такой сітуацыі трэба зноўку адкрыць акно налад і яшчэ раз праверыць інфармацыю.
- Калі дадзеныя дакладныя, можна выкарыстоўваць альтэрнатыўны метад. Для гэтых мэтаў вам трэба націснуць на бягучую опцыю і выбраць найбольш прыдатны спосаб са спісу, які з'явіцца:
- Пошук рашэння з выкарыстаннем абагульненага градыенту для нелінейных задач. Па змаўчанні выкарыстоўваецца гэты параметр, але можна выкарыстоўваць і іншыя.
- Знаходжанне рашэнняў лінейных задач на аснове сімплекс-метаду.
- Выкарыстанне эвалюцыйнага пошуку для выканання задачы.
Увага! Калі пералічаныя варыянты не справіліся з пастаўленай задачай, варта яшчэ раз праверыць дадзеныя ў наладах, бо часта гэта з'яўляецца асноўнай памылкай у такіх задачах.
- Калі патрэбная зніжка атрымана, застаецца прымяніць яе, каб разлічыць суму скідак на кожны тавар. Для гэтага вылучаецца пачатковы элемент графы «Сума скідкі», запісваецца формула «=D2*$G$2» і націсніце «Enter». Знакі даляра ставяцца так, што пры расцягванні формулы на суседнія радкі G2 не змяняецца.
- Цяпер будзе атрымана сума скідкі на першапачатковы тавар. Затым варта навесці курсор на кут ячэйкі, калі ён становіцца «плюсам», націскаецца ЛКМ і формула расцягваецца да патрэбных радкоў.
- Пасля гэтага стол будзе канчаткова гатовы.
Загрузіць/захаваць параметры пошуку
Гэты параметр карысны пры ўжыванні розных параметраў абмежаванняў.
- У меню «Параметры пошуку рашэнняў» націсніце «Загрузіць/захаваць».
- Увядзіце дыяпазон для вобласці мадэлі і націсніце "Захаваць" ці "Загрузіць".
Пры захаванні мадэлі ўводзіцца спасылка на 1 ячэйку пустой калонкі, у якую будзе размяшчацца мадэль аптымізацыі. Падчас загрузкі мадэлі ўводзіцца спасылка на ўвесь дыяпазон, які змяшчае мадэль аптымізацыі.
Важна! Для захавання апошніх налад у меню «Параметры рашэння» захоўваецца працоўная кніга. Кожны аркуш у ім мае ўласныя параметры надбудовы Solver. Акрамя таго, можна ўсталяваць больш за 1 задачу для аркуша, націснуўшы кнопку «Загрузіць або захаваць», каб захаваць асобныя задачы.
Просты прыклад выкарыстання Solver
Загружаць тару кантэйнерамі трэба так, каб яе маса была максімальнай. Рэзервуар мае аб'ём 32 куб. м. Напоўненая скрынка мае масу 20 кг, яе аб'ём 0,15 куб. м. Каробка – 80 кг 0,5 куб. м. Патрабуецца, каб агульная колькасць кантэйнераў складала не менш за 110 шт. Дадзеныя арганізаваны так:
Зменныя мадэлі пазначаны зялёным колерам. Мэтавая функцыя выдзелена чырвоным колерам. Абмежаванні: па найменшай колькасці кантэйнераў (больш або роўна 110) і па вазе (=СУММАПРАДУКТ(B8:C8;B6:C6) – агульная маса тары ў кантэйнеры.
Па аналогіі лічым агульны аб'ём: =СУММАПРАДУКТ(B7:C7;B8:C8). Такая формула неабходная для ўстаноўкі абмежавання на агульны аб'ём тары. Затым праз «Пошук рашэння» ўводзяцца спасылкі на элементы са зменнымі, формуламі і самімі індыкатарамі (або спасылкі на пэўныя вочкі). Вядома, колькасць кантэйнераў з'яўляецца цэлым (гэта таксама абмежаванне). Націскаем «Знайсці рашэнне», у выніку чаго знаходзім такую колькасць кантэйнераў, калі агульная маса максімальная і ўсе абмежаванні ўлічаны.
Пошук рашэння не ўдалося знайсці рашэнні
Такое апавяшчэнне з'яўляецца, калі разгляданая функцыя не знайшла камбінацый балаў зменных, якія задавальняюць кожнаму абмежаванню. Пры выкарыстанні сімплекснага метаду цалкам магчыма, што рашэння няма.
Калі выкарыстоўваецца метад рашэння нелінейных задач, ва ўсіх выпадках зыходзячы з зыходных паказчыкаў зменных, гэта сведчыць аб тым, што магчымае рашэнне далёкае ад такіх параметраў. Калі вы запускаеце функцыю з іншымі пачатковымі індыкатарамі зменных, то, верагодна, ёсць рашэнне.
Напрыклад, пры выкарыстанні нелінейнага метаду не запаўняліся элементы табліцы са зменнымі, і функцыя не знаходзіла рашэнняў. Гэта не значыць, што рашэння няма. Цяпер з улікам вынікаў пэўнай ацэнкі ў элементы са зменнымі ўносяцца іншыя даныя, блізкія да атрыманых.
У любой сітуацыі вы павінны спачатку праверыць мадэль на адсутнасць канфлікту абмежаванняў. Часта гэта звязана з няправільным выбарам каэфіцыента або лімітавага паказчыка.
У прыведзеным вышэй прыкладзе максімальны паказчык аб'ёму роўны 16 куб. м замест 32, бо такое абмежаванне супярэчыць паказчыкам па мінімальнай колькасці месцаў, бо яна будзе адпавядаць колькасці 16,5 куб. м.
заключэнне
Зыходзячы з гэтага, опцыя «Пошук рашэння» ў Excel дапаможа ў вырашэнні канкрэтных задач, якія досыць складана ці немагчыма вырашыць звычайнымі спосабамі. Складанасць прымянення гэтага спосабу заключаецца ў тым, што першапачаткова гэтая опцыя схаваная, з-за чаго большасць карыстальнікаў не падазраюць аб яе наяўнасці. Акрамя таго, функцыя даволі складаная ў вывучэнні і выкарыстанні, але пры належным даследаванні яна прынясе вялікую карысць і палегчыць разлікі.