Студопедия

КАТЕГОРИИ:


Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748)

Пошук розв’язку та оптимізація




Підбір параметра зручний для пошуку значень однієї змінної. В більш складних випадках варто використовувати команду «Поиск решения» з меню «Сервис», що дає змогу розв’язувати задачі з багатьма невідомими і знаходити значення цих невідомих, при яких значення деякої функції досягає максимуму чи мінімуму. Тут також можна задати обмеження, яким повинне задовольняти розв’язання задачі. В даному розділі ми лише торкнемося роботи з компонентою пошуку розв’язання. Більш докладно з цим могутнім інструментом студенти можуть познайомитися по спеціальній літературі [5 ].

Відзначимо, що компонента пошуку розв’язання (на англійській Solver) як і пакет аналізу є доповненням (надбудовою) програми Excel і для роботи з нею необхідна команда «Поиск решения» у меню «Сервис». Якщо цієї команди немає в меню, то установка надбудови здійснюється так само, як це було описано вище для пакета аналізу.

По-перше, процедуру пошуку розв’язання можна використовувати для розв’язання рівнянь від декількох змінних . Для цього у вікні команди «Сервис ® Поиск решения» (див. рис. 15) потрібно встановити селекторну кнопку «Равной» у позицію «значению 0». В цьому випадку в якості цільової вказується комірка, в якій записана функція f, а в полі «Изменяя ячейки» вказуються адреси, де розташовані значення змінних . Іншими словами, пошук розв’язання є узагальненням процедури підбору параметра для багатьох змінних. Аналогічно за допомогою цього інструменту можна розв’язувати системи рівнянь від декількох змінних. Для розв’язання рівнянь в Excel використовується алгоритм нелінійної оптимізації градієнтного типу (GRG2), розроблений американськими вченими.

Крім того, процедуру пошуку розв’язання можна використовувати для розв’язання оптимізаційних задач на максимум чи мінімум. Загальна задача оптимізації (оптимізаційна модель) ставиться таким чином. Потрібно знайти максимум функції

, при обмеженнях

Функція f називається цільовою, а функції - функціями обмежень. Якщо функції f і лінійні, то відповідна задача називається задачею лінійного програмування. Якщо потрібно, щоб значення змінних були цілими, то відповідна задача називається задачею цілочисельного програмування. Для розв’язання лінійних і цілочисельних задач оптимізації в Excel використовується відповідно симплекс метод і алгоритм спрямованого перебору (гілок і меж), розроблені фахівцями компанії Frontline Systems Inc.

Як приклад розв’язання задачі оптимізації розглянемо наступну задачу.

Приклад розв’язку 3. З однакової сировини компанія може випускати два види продукції "А" і "Б". Потрібно визначити, в якій кількості виробляти продукцію кожного виду, щоб дістати максимальний прибуток, якщо відомі собівартості та ціни для кожного виду продукції, розміри накладних витрат, а також нижня і верхня межа їх збуту.

Розв’язання. Почнемо розв’язання зі створення, так званої, оптимізаційної моделі. Запишемо на робочому аркуші в діапазоні В3:Е11 наступну таблицю.

B C D E

    А Б Усього
  Кількість продукції      
  Собівартість одиниці продукції      
  Собівартість партії      
  Накладні витрати      
  Повна вартість      
  Ціна продукції      
  Отримано при продажі      
  Прибуток      

 

Далі, в комірці Е4 запишемо формулу =С4+D4, щоб визначити обсяг усієї продукції, а в комірки С6 і D6 запишемо відповідно формули =C4*C5 і =D4*D5, щоб обчислити майбутню собівартість партії продукції кожного виду. В комірки С8 і D8 запишемо формули =С6+C7 і =D6+D7, щоб обчислити повну вартість кожної партії. В комірки С10 і D10 запишемо відповідно формули =C4*C9 і =D4*D9, щоб визначити повний виторг, що буде отриманий при продажі кожної партії. Нарешті, в комірки С11 і D11 запишемо формули =С10-C8 і =D10-D8, щоб визначити майбутній прибуток, а в комірку Е11 запишемо формулу =С11+D11 для визначення загального прибутку, що буде отриманий від продажу продукції. На цьому створення оптимізаційної моделі можна вважати завершеним.

Після цього виконуємо команду «Сервис ® Поиск решения» і у вікні цієї команди, що показане на рис. 15, вказуємо в якості цільової комірку Е11, в якій записана цільова функція. Селекторну кнопку «Равной» встановлюємо в положення «максимальному значению». В полі «Изменяя ячейки» вказуємо діапазон С4:D4, де розташовані невідомі значення, а потім вводимо обмеження.

Рис. Вікно пошуку розв’язання

Для введення обмеження потрібно натиснути кнопку «Добавить». Як обмеження вказуємо межі збуту продукції, що дорівніють відповідно 50000 і 400000, і верхню межу на загальний обсяг продукції, що дорівнює 500000. Натискаємо на кнопку «Выполнить» і програма видає результат С4 = 100000 і D4 = 400000. При цьому буде отриманий прибуток, що дорівнює 1297500. Задача розв’язана.

В загальному випадку оптимізаційна модель повинна містити інформацію про цільову комірку, вихідні дані, змінні й обмеження задачі, а також формули, що пов'язують основні параметри задачі. Формули обмежень можна записувати на робочому аркуші чи вказувати безпосередньо у вікні пошуку розв’язання. Для складних формул перший спосіб більш зручний, оскільки в цьому випадку у вікні обмежень можна вказати просто адресу комірки з формулою. Оскільки опис оптимізаційної моделі може забирати значний час програма Excel дозволяє зберігати і завантажувати їх. Для цього за допомогою кнопки «Параметры» у вікні пошуку розв’язання потрібно відкрити додаткове вікно й у ньому натиснути кнопку «Сохранить модель». При збереженні моделі варто вказати весь діапазон, у якому знаходяться вихідні дані і формули.

Зауваження, зроблені нами щодо точності і часу розв’язання при підборі параметра, ще в більшій мірі відносяться до пошуку розв’язання, оскільки тут використовуються більш складні чисельні алгоритми. Дуже часто при розв’язанні рівнянь з багатьма змінними програма не може знайти розв’язання за прийнятний час. Тут також може виявитися істотним вибір початкового наближення, що у більшості випадків за замовчуванням передбачається рівним нулю. Крім того, на змінні задачі можна накласти ц ілочисельні обмеження, що ще більше ускладнює задачу і робить її перебірною.




Поделиться с друзьями:


Дата добавления: 2015-05-24; Просмотров: 454; Нарушение авторских прав?; Мы поможем в написании вашей работы!


Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет



studopedia.su - Студопедия (2013 - 2024) год. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав! Последнее добавление




Генерация страницы за: 0.013 сек.