 Дата Клас
Лабораторна робота № 8
Тема: “Засоби “Пошук рішення” та “Підбір параметрів””.
Мета: навчитися використовувати засоби “Пошук рішення” та “Підбір параметрів” для знаходження розв’язків задач моделювання.
Матеріально-технічне забезпечення: роздатковий матеріал, ПК з програмою Excel.
Теоретичні відомості
Пошук рішення є частиною блоку задач, який інколи називають аналіз “що-якщо”. Процедура дозволяє знайти оптимальне значення формули, яка міститься в цільовій комірці. Вона працює із групою комірок, які певним чином пов’язані з формулою в цільовій комірці. Для того щоб отримати по формулі в цільовій комірці заданий результат, процедура ПР змінює значення у комірках, значення яких впливає на значення цільової комірки. Для того щоб звузити множину значень, які використовуються в моделі, застосовуються обмеження на значення змінюючи комірок, кінцевих комірок або інших комірок, що задаються при постановці задачі. Ці обмеження можуть посилатися на інші впливаючі комірки.
Перегляд проміжних результатів пошуку рішень
Сформулюйте задачу.
У полі Оптимізувати цільову функцію введіть посилання на комірку або ім’я кінцевої комірки, яка має містити формулу.
Виконайте одну із наступних дій:
- для того щоб максимізувати значення кінцевої комірки шляхом змінення значення впливаючих комірок, встановіть перемикач в положення Максимум.
- для того щоб мінімізувати значення кінцевої комірки шляхом змінення значення впливаючих комірок, встановіть перемикач в положення Мінімум.
- щоб встановити значення в кінцевій комірці рівне деякому числу, встановіть перемикач в положення Значення і введіть у відповідне поле потрібне число.
4. У полі Змінюючи комірки змінних введіть імена або посилання на змінюючі комірки, розділяючи їх комами. Ці комірки мають бути пов’язані із кінцевою коміркою. Допускається задання до 200 змінюючих комірок.
5. У полі У відповідності з обмеженнями введіть всі обмеження, які накладаються на пошук рішення.
Практичне завдання
Оптимізаційне моделювання використовують для пошуку оптимального рішення деякої функції при певних заданих умовах. Для прикладу розглянемо задачу оптимізації випуску фірмою двох видів фарб.
Задача про виробництво фарб
Невелика фірма виготовляє два види фарб: для внутрішніх робіт (В) і зовнішніх робіт (З). Продукція обох видів поступає в оптову торгівлю. Для виробництва фарб використовуються два вихідних продукти – А і Б. Максимально допустимі добові запаси цих продуктів становлять 6 і 8 т відповідно.
Вихідний продукт
|
Витрати сировини (в тонах)
на тону фарби
|
Максимально можливий запас, т
|
Фарба З
|
Фарба В
|
А
|
1
|
2
|
6
|
Б
|
2
|
1
|
8
|
Вивчення ринку збуту показало, що добовий попит на фарбу В ніколи не перевищує попиту на фарбу З більш ніж на 1 тону. Крім того, встановлено, що попит на фарбу В ніколи не перевищує 2 т за добу. Оптові ціни однієї тони фарб дорівнюють: 3 тис. грн. для фарби З, 2 тис. грн. для фарби В. Яку кількість фарби кожного виду повинна виробляти фірма, щоб прибуток від реалізації продукції був максимальним.
Отже, в даному випадку потрібно визначити об’єми виробництва кожної з фарб, які максимізують прибуток від реалізації продукції, з врахуванням обмежень на попит і витрати вихідних продуктів.
Математична модель задачі:
Визначення величин, для яких повинна бути побудована модель (як ідентифікувати змінні).
Визначення мети, для досягнення якої з усіх припустимих змінних потрібно вибрати ті, що будуть відповідати оптимальному рішенню задачі.
Визначення обмежень, які потрібно накласти на змінні, щоб виконувалися умови, характерні для системи, що моделюється.
1.ЗМІННІ,ЯКІ НЕОБХІДНО ЗНАЙТИ:
Оскільки потрібно визначити об’єми виробництва кожного виду фарби, змінними в моделі є:
ФарбаВ – добовий об’єм виробництва фарби для внутрішніх потреб.
ФарбаЗ - добовий об’єм виробництва фарби для зовнішніх потреб.
2. ЦІЛЬОВА ФУНКЦІЯ:
Оскільки вартість 1 т фарби зовнішньої рівна 3 тис. грн.., добовий прибуток від її реалізації складе:
3*ФарбаЗ
Оскільки вартість 1 т фарби внутрішньої рівна 2 тис. грн.., добовий прибуток від її реалізації складе:
2*ФарбаВ
Позначимо сукупний дохід (в тис. грн.) через Z.
Тоді математичне формулювання цільової функції:
Визначити припустимі значення змінних ФарбаЗ і ФарбаВ, максимізуючи величину загального прибутку:
max Z=3*ФарбаЗ + 2*Фарба3.
3. ОБМЕЖЕННЯ ЗАДАЧІ
- максимально можливий запас продукту А: ФарбаЗ + 2*ФарбаВ <=6.
- максимально можливий запас продукту В: 2*ФарбаЗ + ФарбаВ <=8
- перевищення попиту на фарбу В відносно попиту на фарбу З: ФарбаВ-ФарбаЗ<=1
- попит на фарбу В: ФарбаВ<=1.
Неявні обмеження задачі (об’єми виробництва не можуть приймати від’ємних значень):
- ФарбаВ>=0.
- ФарбаЗ>=0.
Для знаходження оптимального рішення використовуємо засіб Пошук рішення – додатковий інструмент програми. Для доступу до засобу використовуємо команду Пошук рішення в меню Сервіс.
1. Створіть розділ Шукані змінні, в якому:
- В стовпці А вводимо імена змінних
- В стовпці В початкові значення змінних (щоб почати роботу з засобом Пошук рішення, не суттєво, які саме значення будуть введені в комірки).
2. Створіть розділ Цільова функція і в комірку праворуч (в стовпці В) вводимо формулу цільової функції.
3. Створіть розділ Обмеження, в якому:
- В стовпці А введіть назви обмежень
- В стовпці В вводимо формули обмежень
- В стовпці С вводимо сталі правої частини обмежень
Оптимізація цільової функції:
1. Вкладка Данные → Поиск решения.
2. В діалоговому вікні Пошук рішення вкажіть:
- комірку з цільовою функцією у полі Оптимізувати цільову функцію вікна Пошук рішення
- перемикач До встановіть в положення, що відповідає меті оптимізації. У випадку максимізації прибутку в положення максимальному значенню.
- комірки, значення в яких засіб Пошук рішення повинен змінювати в процесі оптимізації (так звані змінювані комірки).
3. Визначте обмеження моделі. Для цього відкрийте діалогове вікно Додавання обмежень, натиснувши на кнопці Додати вікна Пошук рішення. Для додання обмежень:
- натисніть на полі Посилання на комірку;
  - натисніть на комірці аркуша, яка містить формулу обмеження;
- виберіть із списку, що розкривається, необхідний оператор порівняння, який буде використовуватися при порівнянні результату обчислення за формулою обмеження і заданої сталої обмеження;
- натисніть в полі Обмеження вікна Додавання обмеження;
- натисніть в комірці робочого аркуша, яка містить сталу обмеження
4. Для знаходження оптимального рішення натисніть на кнопці Знайти рішення. Програма почне пошук.
У випадку успішного завершення пошуку на екрані з’явиться діалогове вікно Результати пошуку рішення з пропозицією зберегти знайдене рішення.
Натисніть на кнопці ОК вікна Результати пошуку для заміни початкових значень змінних значеннями, знайденими в процесі оптимізації.
Контрольні запитання
Що таке процес пошуку рішення?
В якому випадку застосовується надбудова Пошук рішення?
Яким чином здійснити запуск надбудови Пошук рішень?
Що таке цільова комірка?
Для чого потрібно використовувати обмеження?
Як задати цільову комірку, обмеження та змінні комірки?
Як зберегти результати пошуку?
Чи можливо перервати процес пошуку рішення; якщо так, то яким чином?
Яким чином можна застосувати стандартні значення параметрів пошуку рішення
Що таке Підбір параметрів ?
Як запустити процедуру Підбір параметрів?
З яких полів складається діалогове вікно Підбір параметрів?
|