Лабораторна робота № 10

Завдання: Засобами MS Excel розв`язати задачу знаходження оптимального розв`язку

Методичні вказівки

Розглянемо типову задачу оптимізації.

Задача. Фірма робить виріб трьох модифікацій (I,II,III). Витрати, пов'язані з виконанням кожної операції в процесі виготовлення одного виробу, наводяться в таблиці.

Таблиця

Операція	Затрати
	I	II	III
Виготовлення комплектуючих	20	10	10
Збір виробу	30	20	10
Тестування й пакування	20	30	20

Максимальні тижневі витрати фірми, зв'язані з виконаних робіт з виготовлення комплектуючих, по зборці виробів, по тестуванню й пакуванню виробів, складають 3600 грн., 2400 грн., 1800 грн. відповідно.

Прибутком від продажу одного виробу модифікації I, II, III складають відповідно 15, 22 і 19 грн.

Потрібно визначити оптимальний тижневий план виробництва продукції, тобто план випуску виробів, при якому фірма дістане максимальний прибуток.

Для розв`язування поставленої задачі побудуємо спочатку її математичну модель. Нехай х₁ х₂, х₃ — кількість виробів модифікації I, II, III відповідно в плані виробництва.

Оскільки витрати, пов'язані з виконанням робіт з виготовлення комплектуючих для виробів модифікацій I,II,III, по зборці цих виробів, по їхньому тестуванню й пакуванню, не перевершують 3600, 2400 і 1800 грн. відповідно, одержуємо такі обмеження:

Окрім того, х₁ х₂, х₃ ,можуть набувати тільки невід`ємні і цілочисельні значення, тобто: х₁0, х₂,0; х₃0; х₁ х₂, х₃ – цілі числа.

Отже, приходимо до такої системи обмежень:

(1)

Тижневий прибуток фірми від реалізації зробленого товару визначається цільовою функцією

(2)

Отже, розв`язання сформульованої задачі оптимізації зводиться до рішення задачі цілочислового лінійного програмування, а саме до визначення таких значень х₁ х₂, х₃, що задовольняють систему обмежень (1), при яких цільова функція (2) досягає максимуму.

П родемонструємо, як легко реалізується рішення такого роду задач у середовищі електронних таблиць Ехсеl.

Практичне завдання

1. Спочатку заповнюємо таблицю вихідних даних (рис. 3).

Рис. 3. Підготовлені дані на робочому листі Excel.

У діапазон комірок В2:В4 вводимо довільні початкові значення змінних х₁ х₂, х₃, наприклад нульові значення. У діапазон комірок Н2:Н4 уводимо значення прибутку від реалізації кожного виробу модифікації I,II,III. У діапазоні комірок С2:Е4 вводимо значення витрат, пов'язаних із виконанням кожної операції в процесі виготовлення одного виробу. У діапазон комірок З5:Е5 уводимо значення максимальних тижневих витрат фірми в процесі виготовлення виробів, зв'язаних з виконанням кожної операції відповідно.

Потім у комірку А8 заносимо значення цільової функції. Це значення визначимо з використанням вбудованої математичної функції СУММПРОИЗВ (1-й масив; 2-й масив). Перший масив складається зі значень прибутку від реалізації кожного виробу модифікації I, II, III (діапазон комірок Н2:Н4), другий масив – зі значень змінних х₁ х₂, х₃ (діапазон комірок В2:В4). Таким чином, у комірці А8 будемо мати формулу =СУММПРОИЗВ (Н2:Н4; У2:У4).

У діапазоні комірок С8:Е8 уводимо ліві частини нерівностей системи (1). У комірку же С8 – формулу =СУММПРОИЗВ (З2:З4; $В$2:$В$4).

Для випадків II, III модифікацій копіюємо цю формулу в комірки D8:E8, використовуючи маркер заповнення.

2. У меню Сервис вибираємо команду Поиск решения. Заповнюємо поля діалогового вікна, що з'явилося: адресу комірки цільової функції (цільової комірки), тип оптимізації (шукати максимум чи мінімум), адреси комірок зі змінними, обмежень. При заданні системи обмежень використовується кнопка Добавить. При її натисканні з'являється допоміжне діалогове вікно, в поля якого вводяться посилання на комірки й обмеження, що накладаються змінні в розглянутій задачі. Як видно з рис. 4, сформульовану систему обмежень надалі можна редагувати.

Р ис. 4 Діалогове вікно “Поиск решений”

Для цього служать кнопки Изменить і Удалитъ. Далі варто натиснути кнопку Выпол­нитъ, після чого буде здійснена процедура Поиск решения, за результатами якої виводи­ться повідомлення про знайдений розв`язок чи про неможливість його визначити (рис. 5).

Р ис. 5. Діалогове вікно “Результаты поиска решений”

Як видно з рис. 5, отримані результати можна зберегти (кнопка ОК), змінивши вміст комірок зі змінними (рис. 6); можна також відмовитися від збереження результатів (кнопка Отмена).

Р ис. 6. Результаті розрахунків на робочому листі Excel.

З овсім нескладно сформувати звіт із більш докладною інформацією про те, як проходив процес пошуку рішення (рис. 7). У розділі звіту Обмеження вказується стан усіх обмежень. Тип зв'язаний (связанный) означає, що дане обмеження задоволене, але при цьому відповідний параметр набув своє граничне значення, що уже далі нікуди і заміняти.

Рис. 7. Звіт за результатами на робочому листі Excel.

Потрібно зазначити, що якщо в результаті виконання процедури Поиск решения розв`язок не буде знайдено, хоча відомо, що такий розв`язок повинен існувати, то часто подібну проблему вдається розв`язати, змінивши одну чи кілька опцій у діалоговому вікні Параметри поиска решения і повторно запустити процедуру Поиск решения. Щоб з'явилося діалогове вікно Параметри пошуку розв`язку, клацніть у діалоговому вікні Поиск решения на кнопці Параметри. За допомогою діалогового вікна Параметр поиска решений можна контролювати багато аспектів процесу розв`язування задачі.

Таким чином, на рис. 6 одержали шукане рішення сформульоване для задачі оптимізації: максимальний прибуток у 2760 грн. фірма може одержати у випадку, якщо виготовить виробу модифікації I, II, III у кількості 100, 40, 20 екземплярів відповідно.