Пастаноўка задачы
Выкажам здагадку, што кампанія, у якой вы працуеце, мае тры склада, адкуль тавары трапляюць у пяць вашых крам, раскіданых па Маскве.
Кожны магазін здольны прадаць пэўную колькасць вядомага нам тавару. Кожны са складоў мае абмежаваную ёмістасць. Стаіць задача рацыянальна выбраць, з якога склада ў якія магазіны даставіць тавар, каб мінімізаваць агульныя транспартныя выдаткі.
Перш чым прыступіць да аптымізацыі, неабходна скласці на аркушы Excel простую табліцу - нашу матэматычную мадэль, якая апісвае сітуацыю:
Зразумела, што:
- Светла-жоўтая табліца (C4:G6) апісвае кошт дастаўкі аднаго тавару з кожнага склада ў кожную краму.
- Фіялетавыя вочкі (C15:G14) апісваюць колькасць тавараў, неабходных для продажу кожнай краме.
- Чырвоныя клеткі (J10:J13) паказваюць ёмістасць кожнага склада - максімальную колькасць тавараў, якія можа змясціць склад.
- Жоўтыя (C13:G13) і сінія (H10:H13) клеткі з'яўляюцца сумай радкоў і слупкоў для зялёных клетак адпаведна.
- Агульны кошт дастаўкі (J18) разлічваецца як сума твораў колькасці тавараў і адпаведных выдаткаў на дастаўку – для разліку тут выкарыстоўваецца функцыя SUMPRODUCT (SUMPRODUCT).
Такім чынам, наша задача зводзіцца да падбору аптымальных значэнняў зялёных вочак. І каб агульная сума па радку (сінія клеткі) не перавышала ёмістасць склада (чырвоныя клеткі), і пры гэтым кожная крама атрымала тое колькасць тавару, якое яму трэба прадаць (сума па кожнай краме ў жоўтыя клеткі павінны быць максімальна набліжаны да патрабаванняў – фіялетавыя).
Рашэнне
У матэматыцы такія задачы выбару аптымальнага размеркавання рэсурсаў сфармуляваны і апісаны даўно. І, вядома, спосабы іх вырашэння даўно выпрацоўваюцца не тупым пералікам (а гэта вельмі доўга), а вельмі невялікай колькасцю ітэрацый. Excel прадастаўляе карыстачу такую функцыянальнасць з дапамогай надбудовы. Пошук рашэнняў (Рашальнік) з таб Дата (Дата):
Калі на таб Дата у вашым Excel такой каманды няма – нічога страшнага – гэта азначае, што надбудова проста яшчэ не падключана. Каб актываваць яго адкрыйце Размовы, А затым выберыце параметры - дапаўненні - аб (Options - Add-Ins - Go To). У якое адкрылася акне ставім галачку насупраць патрэбнай нам радкі Пошук рашэнняў (Рашальнік).
Давайце запусцім надбудову:
У гэтым акне неабходна задаць наступныя параметры:
- Аптымізацыя мэтавай функцыі (Усталяваць тгрошы клетка) – тут неабходна пазначыць канечную асноўную мэту нашай аптымізацыі, г.зн. ружовую скрынку з агульным коштам дастаўкі (J18). Мэтавую вочка можна мінімізаваць (калі гэта выдаткі, як у нашым выпадку), максымізаваць (калі гэта, напрыклад, прыбытак) або паспрабаваць давесці яе да зададзенага значэння (напрыклад, дакладна ўкласціся ў выдзелены бюджэт).
- Змена зменных вочак (By замена клеткі) – тут мы пазначаем зялёныя вочкі (C10: G12), вар’іруючы значэння якіх хочам дамагчыся нашага выніку – мінімальнай кошту дастаўкі.
- У адпаведнасці з абмежаваннямі (Прадмет у la Абмежаванні) – спіс абмежаванняў, якія неабходна ўлічваць пры аптымізацыі. Каб дадаць абмежаванні ў спіс, націсніце кнопку Дадаваць (дадаць) і ўвядзіце ўмова ў якое з'явілася акне. У нашым выпадку гэта будзе абмежаванне попыту:
і ліміт на максімальны аб'ём складоў:
Акрамя відавочных абмежаванняў, звязаных з фізічнымі фактарамі (ёмістасць складоў і транспартных сродкаў, бюджэтныя і часовыя абмежаванні і г.д.), часам даводзіцца дадаваць абмежаванні «спецыяльна для Excel». Так, напрыклад, Excel можа лёгка арганізаваць для вас «аптымізацыю» кошту дастаўкі, прапанаваўшы перавезці тавар з крамы назад на склад - выдаткі стануць адмоўнымі, гэта значыць мы атрымаем прыбытак! 🙂
Каб гэтага не адбылося, лепш пакінуць сцяжок уключаным. Зрабіце неабмежаваную колькасць зменных неадмоўнымі ці нават часам яўна прапісваюць такія моманты ў спісе абмежаванняў.
Пасля ўстаноўкі ўсіх неабходных параметраў акно павінна выглядаць так:
У выпадальным спісе Выберыце метад рашэння вам дадаткова неабходна выбраць адпаведны матэматычны метад рашэння з трох варыянтаў:
- Сімплексны метад гэта просты і хуткі метад рашэння лінейных задач, г.зн. задач, дзе выхад лінейна залежыць ад уваходу.
- Агульны паніжаны градыентны метад (OGG) – для нелінейных задач, дзе існуюць складаныя нелінейныя залежнасці паміж уваходнымі і выходнымі дадзенымі (напрыклад, залежнасць продажаў ад выдаткаў на рэкламу).
- Эвалюцыйны пошук рашэння – адносна новы метад аптымізацыі, заснаваны на прынцыпах біялагічнай эвалюцыі (прывітанне Дарвіну). Гэты метад працуе ў разы даўжэй першых двух, але можа вырашыць практычна любую задачу (нелінейную, дыскрэтную).
Наша задача выразна лінейная: аддаў 1 штуку – патраціў 40 рублёў, аддаў 2 штукі – патраціў 80 рублёў. і г.д., таму сімплексны метад - лепшы выбар.
Цяпер, калі дадзеныя для разліку ўведзены, націсніце кнопку знайсці рашэнне (Разгадаць)каб пачаць аптымізацыю. У цяжкіх выпадках з вялікай колькасцю зменлівых ячэек і абмежаванняў пошук рашэння можа заняць шмат часу (асабліва пры эвалюцыйным метадзе), але наша задача для Excel не будзе праблемай - праз некалькі імгненняў мы атрымаем наступныя вынікі :
Звярніце ўвагу на тое, наколькі цікава размеркаваліся аб'ёмы паставак па магазінах, не перавышаючы пры гэтым ёмістасць нашых складоў і задавальняючы ўсе заяўкі на неабходную колькасць тавараў для кожнага магазіна.
Калі знойдзенае рашэнне нас задавальняе, то мы можам яго захаваць, або адкаціцца да зыходных значэнняў і паўтарыць спробу з іншымі параметрамі. Вы таксама можаце захаваць выбраную камбінацыю параметраў як сцэнар. Па жаданні карыстальніка Excel можа будаваць тры тыпу Справаздачы па вырашаемай задачы на асобных лістах: справаздача аб выніках, справаздача аб матэматычнай устойлівасці рашэння і справаздача аб межах (абмежаваннях) рашэння, аднак у большасці выпадкаў цікавяць толькі спецыялістаў. .
Аднак бываюць сітуацыі, калі Excel не можа знайсці падыходнае рашэнне. Змадэляваць такі выпадак можна, калі паказаць у нашым прыкладзе патрэбы крам у аб'ёме, які перавышае агульную ёмістасць складоў. Затым, выконваючы аптымізацыю, Excel паспрабуе максімальна наблізіцца да рашэння, а потым адлюструе паведамленне аб тым, што рашэнне не знойдзена. Тым не менш, нават у гэтым выпадку мы маем шмат карыснай інфармацыі - у прыватнасці, мы можам убачыць «слабыя звёны» нашых бізнес-працэсаў і зразумець напрамкі паляпшэння.
Разгледжаны прыклад, вядома, адносна просты, але лёгка маштабуецца для вырашэння куды больш складаных задач. Напрыклад:
- Аптымізацыя размеркавання фінансавых рэсурсаў па артыкулах выдаткаў у бізнес-плане або бюджэце праекта. Абмежаваннямі, у дадзеным выпадку, будуць аб'ём фінансавання і тэрміны рэалізацыі праекта, а мэтай аптымізацыі з'яўляецца максімізацыя прыбытку і мінімізацыя выдаткаў праекта.
- Аптымізацыя раскладу супрацоўнікаў з мэтай мінімізацыі фонду заработнай платы прадпрыемства. Абмежаваннямі, у дадзеным выпадку, будуць пажаданні кожнага супрацоўніка ў адпаведнасці з графікам занятасці і патрабаваннямі штатнага раскладу.
- Аптымізацыя інвестыцыйных укладанняў – неабходнасць правільнага размеркавання сродкаў паміж некалькімі банкамі, каштоўных папер або акцый прадпрыемстваў з мэтай, зноў жа, максімізацыі прыбытку або (што важней) мінімізацыі рызык.
Ва ўсялякім разе, надбудова Пошук рашэнняў (Рашальнік) гэта вельмі магутны і прыгожы інструмент Excel і варты вашай увагі, так як ён можа дапамагчы ў многіх складаных сітуацыях, з якімі вам даводзіцца сутыкацца ў сучасным бізнэсе.