Методичні вказівки до лабораторної роботи №12 - Оптимізаційні задачи, функція Поиск решения

ЛАБОРАТОРНА РОБОТА № 12

Тема: Оптимізаційні задачи. Функція «Поиск решения»

ЗАВДАННЯ

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

Приклад розв’язання

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

Далі, в комірці Е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 для визначення загального прибутку, що буде отриманий від продажу продукції. На цьому створення оптимізаційної моделі можна вважати завершеним.

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

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

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

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

З повагою ІЦ "KURSOVIKS"!