Выкарыстоўваем пошук рашэнняў у Excel 2010 для вырашэння складаных задач

Значная частка задач, якія вырашаюцца з дапамогай электронных табліц, мяркуюць, што для выяўлення патрэбнага выніку ў карыстальніка ўжо ёсць хоць нейкія зыходныя дадзеныя. Аднак Exсel 2010 размяшчае неабходнымі інструментамі, з дапамогай якіх можна вырашыць гэтую задачу наадварот - падабраць патрэбныя дадзеныя, каб атрымаць неабходны вынік.

«Пошук рашэнні» і з'яўляецца адным з такіх інструментаў, максімальна зручных для «задач аптымізацыі». І калі раней вам яшчэ не даводзілася яго выкарыстоўваць, то цяпер самы час выправіць гэта.

Такім чынам - пачынаем з ўстаноўкі дадзенай надбудовы (паколькі самастойна яна не з'явіцца). На шчасце зараз зрабіць гэта можна досыць проста і хутка - адкрываем меню «Сэрвіс», а ўжо ў ім «Надбудовы»

Застанецца толькі ў графе «Упраўленне» пазначыць «Надбудовы Excel», а пасля націснуць кнопачку «Перайсці».

Пасля гэтага нескладанага дзеяння кнопка актывацыі «Пошуку рашэнні» будзе адлюстроўвацца ў «Дадзеных». Як і паказана на малюнку

Давайце разгледзім, як правільна выкарыстоўваецца пошук рашэнняў у Excel 2010 г., на некалькіх простых прыкладах.

Прыклад першы.

Дапусцім, што вы займаеце пасаду начальніка буйнога аддзела вытворчасці і неабходна правільна размеркаваць прэміі супрацоўнікам. Дапусцім, агульная сума прэмій складае 100 000 рублёў, і неабходна, каб прэміі былі прапарцыйныя акладаў.

Гэта значыць, зараз нам неабходна падабраць правільны каэфіцыент прапарцыйнасці, каб вызначыць памер прэміі адносна акладу.

У першую чаргу неабходна хутка скласці (калі яе яшчэ няма) табліцу, дзе будуць захоўвацца зыходныя формулы і дадзеныя, паводле якіх і можна будзе атрымаць жаданы вынік. Для нас гэты вынік - сумарная велічыня прэміі. А зараз увага - мэтавая вочка С8 павінна быць з дапамогай формул звязаная з шуканай змянянай ячэйкай пад адрасам Е2. Гэта крытычна. У прыкладзе мы звязваем іх выкарыстоўваючы прамежкавыя формулы, якія і адказваюць за вылічэннем прэміі кожнаму супрацоўніку (С2: С7).

Цяпер можна актываваць «Пошук рашэнняў». Адкрыецца новае акенца, у якім нам неабходна ўказаць неабходныя параметры.

Пад «1» пазначаная наша мэтавая вочка. Яна можа быць толькі адна.

«2» - гэта магчымыя варыянты аптымізацыі. За ўсё можна выбраць «Максімальнае», «Мінімальны» або «Канкрэтнае» магчымыя значэнні. І калі вам неабходна менавіта канкрэтнае значэнне, то яго трэба паказаць у адпаведнай графе.

«3» - змяняных вочак можа быць некалькі (цэлы дыяпазон ці ж асобна названыя адрасы). Бо менавіта з імі і будзе працаваць Excel, перабіраючы варыянты так, каб атрымалася значэнне, зададзенае ў мэтавай вочку.

«4» - Калі спатрэбіцца задаць абмежаванні, то варта скарыстацца кнопкай «Дадаць», але мы гэта разгледзім крыху пазней.

«5» - кнопка пераходу да інтэрактыўным вылічэннях на аснове зададзенай намі праграмы.

Але зараз вернемся да магчымасці змяняць наша заданне, скарыстаўшыся кнопкай «Дадаць». Дадзены этап з'яўляецца даволі адказным (не менш чым пабудова формул), паколькі менавіта абмежаванне дазваляюць атрымаць правільны вынік на выхадзе. Тут усё зроблена максімальна зручна, так што задаць іх вы зможаце не толькі для ўсяго дыяпазону адразу, але і для пэўных вочак.

Для гэтага можна выкарыстоўваць шэраг пэўных (і знаёмых ўсім карыстальнікам Excel 2010) знакаў «=», «> =", "<=», а таксама варыянты «цэлы» (ад «цэлае»), «бін» ( «бінарнае» або жа «двайковае»), «раз» ( «ўсе розныя»).

Але ў нашым прыкладзе абмежаванне можа быць толькі адно - станоўчы каэфіцыент. Задаць яго, вядома, можна некалькімі спосабамі - альбо выкарыстоўваючы «Дадаць» (што называюць «відавочна паказаць абмежаванне»), альбо проста адзначыць дзеючай функцыю «Зрабіць зменныя без абмежаванняў неадмоўнага». Гэта можна зрабіць у надбудове «Пошук рашэнні», націснуўшы на кнопачку «Параметры».

Дарэчы, пасля пацверджання параметраў і запуску праграмы (кнопачка «Выканаць»), вы зможаце ў табліцы праглядзець атрыманы вынік. Тады праграма прадэманструе акенца «вынікаў пошуку».

Калі прадэманстраваны вынік цалкам вам пасуе, тады застанецца толькі зноў пацвердзіць яго (кнопачка «ОК»), што зафіксуе вынік у вашай табліцы. Калі ж нешта ў разліках вас не задавальняе, то неабходна адмяніць вынік (кнопачка «Адмена»), вернуцца да папярэдняга нашай табліцы і выправіць дапушчаныя памылкі.

Правільнае рашэнне задачы прыкладу павінна атрымацца вось такім

Вельмі важна - каб атрымаць правільны вынік нават пры найменшым змене зыходных даных неабходна перазапусціць «Пошук рашэнняў».

Каб больш падрабязна зірнуць на тое, як дзейнічае дадзеная праграма, давайце разбярэм яшчэ адзін прыклад.

Дапусцім, вы з'яўляецеся уладальнікам буйнога мэблевага прадпрыемства і неабходна наладзіць вытворчасць такім чынам, каб атрымаць максімальна магчымую прыбытак. Вы робіце толькі кніжныя паліцы, пры гэтым усяго двух мадэляў - «А» і «У», вытворчасць якіх абмяжоўваецца выключна наяўнасцю (або адсутнасцю) высакаякасных дошак, а таксама машынным часам (апрацоўка на станку).

Мадэль «А» патрабуе 3 м3 дошак, а мадэль «У» - на 1 м3 больш (гэта значыць - 4). Ад сваіх пастаўшчыкоў вы за тыдзень атрымліваеце максімум 1700 м3 дошак. Пры гэтым мадэль «А» ствараецца за 12 хвілін працы станка, а "В" - за 30 хвілін. Усяго ў тыдзень станок можа працаваць не больш за 160 гадзін.

Пытанне - колькі ўсяго вырабаў (і якой мадэлі), павінна выпускаць фірма за тыдзень, каб атрымаць максімальна магчымую прыбытак, калі палічка «А» дае 60 рублёў прыбытку, а "В" - 120?

Паколькі парадак дзеяння вядомы, то пачынаем ствараць неабходную нам табліцу з дадзенымі і формуламі. Размяшчэнне вочак, як і раней, вы можаце ўсталяваць на сваё меркаванне. Ці ж скарыстацца нашым

Любым зручным спосабам запускаем наш «Пошук рашэнняў», ўводзім дадзеныя, вырабляем наладу.

Такім чынам, разгледзім тое, што мы маем. У мэтавай вочку F7 змяшчаецца формула, якая і разлічыць прыбытак. Параметр аптымізацыі ўсталёўваем на максімум. Сярод змяняных вочак у нас значыцца «F3: G3». Абмежаванні - усе выяўленыя значэнні павінны быць цэлымі лікамі, неадмоўнага, агульная колькасць выдаткаванага машыннага часу не перавышае адзнаку 160 (наша ячэйка D9), колькасць сыравіны не перавышае 1700 (вочка D8).

Вядома, у гэтым выпадку можна было не указваць адрасы вочак, а напрамую прапісаць неабходныя лічбавыя значэння, аднак калі выкарыстоўваць адрасы, то змены абмежаванняў можна будзе праводзіць і ў табліцы, што дапаможа разлічваць прыбытак гэтага прадпрыемства ў будучыні, пры змене зыходных дадзеных.

Актывуецца праграму, і яна падрыхтоўвае рашэнне.

Зрэшты, гэта не адзінае рашэнне і ў вас цалкам можа выскачыць іншы вынік. Гэта можа адбыцца нават у тым выпадку, калі ўсе дадзеныя былі пазначаны дакладна і памылак у формулах таксама не было

Так. Гэта можа адбыцца нават у тым выпадку, калі мы сказалі праграме шукаць цэлы лік. І калі гэта раптам адбылося, то неабходна проста правесці дадатковую наладу «Пошуку рашэнняў». Адкрываем акно «Пошуку рашэнняў» і ўваходзім у «Параметры».

Наш верхні параметр адказвае за дакладнасць. Чым ён менш, тым вышэй дакладнасць і ў нашым выпадку гэта значна павышае шанцы атрымаць цэлы лік. Другі параметр ( "Ігнараваць цэлалікавых абмежаванні») і дае адказ на пытанне, як мы змаглі атрымаць такі адказ з тым, што ў запыце відавочна паказалі цэлы лік. «Пошук рашэнняў» проста праігнараваў гэтае абмежаванне ў сувязі з тым, што так яму сказалі пашыраныя налады.

Так што будзьце вельмі ўважлівыя ў будучыні.

Трэці і, мабыць, апошні прыклад. Паспрабуем мінімізаваць затраты транспартнай кампаніі выкарыстоўваючы пошук рашэнняў у Excel 2010.

Такім чынам, будаўнічая кампанія дае заказ на перавозку пяску, які бярэцца ад 3 пастаўшчыкоў (кар'ераў). Яго неабходна даставіць 5 розных спажыўцам (якімі выступаюць будаўнічыя пляцоўкі). Кошт дастаўкі грузу ўключана ў сабекошт аб'екта, так што наша задача забяспечыць дастаўку грузу на будпляцоўкі з мінімальнымі выдаткамі.

Мы маем - запас пяску ў кар'еры, патрэба будпляцовак ў пяску, выдатак на транспарціроўку «пастаўшчык-спажывец».

Неабходна знайсці схему аптымальнай перавозкі грузу (куды і адкуль), пры якой агульная выдатак на перавозку была б мінімальнай.

Шэрыя вочкі нашай табліцы ўтрымліваюць формулы сумы па слупках і радках, а мэтавая вочка - формула для агульнага падліку выдаткі на дастаўку грузу. Запускаем наш «Пошук рашэнні» і ўносім неабходныя налады

Пасля гэтага прыступаем да пошуку рашэння гэтай задачы

Зрэшты, не будзем забываць, што досыць часта транспартныя задачы могуць быць ўскладненыя некаторымі дадатковымі абмежавальнікамі. Дапусцім, паўстала ўскладненне на дарозе і цяпер з кар'ера 2 проста тэхнічна немагчыма даставіць груз на будпляцоўку 3. Каб ўлічыць гэта, неабходна проста дапісаць дадатковае абмежаванне «$ D $ 13 = 0». І калі цяпер запусціць праграму, то вынік будзе іншым

Напрыканцы засталося сказаць толькі аб выбары метаду рашэнні. І калі задачка сапраўды вельмі складаная, то каб атрымаць неабходны вынік, хутчэй за ўсё, спатрэбіцца падабраць неабходны метад рашэння.

Вось і ўсё па гэтым пытанні.

Мы выканалі пошук рашэнняў у Excel 2010 г. - для вырашэння складаных задач

Глядзі таксама:

Вам спадабаўся матэрыял?
Поделитeсь:

Вярнуцца ў пачатак артыкула Выкарыстоўваем пошук рашэнняў у Excel 2010 для вырашэння складаных задач