Дослідження оптимального розв’язку засобами Excel

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

Аналіз оптимального розв’язку починається після успішного розв’язання задачі, коли на екрані комп'ютера з'являється діалогове вікно Результати пошуку розв’язку (Результаты поиска решения) з повідомленням Розв’язок знайдено (Решение найдено). У цьому ж діалоговому вікні (рис. 1.6) є можливість викликати надавані Пошуком розв’язку звіти трьох типів: Результати ; Стійкість (Устойчивость) і Межі (Пределы). Для створення звіту необхідно клацнути мишкою по його назві. Можна одночасно клацнути по всіх назвах, щоб одержати звіти всіх типів у вигляді інформації на окремих листах Excel з відповідною назвою на ярличку. Для виклику й перегляду звіту необхідно клацнути по ярличку з його назвою.

1. Пошук оптимального розв’язку

Задача 1. Для виготовлення різних виробів A,B,C підприємство використовує три різних види сировини. Норми витрати сировини на виробництво одного виробу кожного виду, ціна одного виробу , а також загальна кількість сировини кожного виду, що може бути використана підприємством, наведені в таблиці. Вироби можуть виготовлятися в будь-яких співвідношеннях, але виробництво обмежене кількістю виділеної підприємству сировини. Скласти оптимальний план виробництва, при якому загальна вартість всієї виготовленої підприємством продукції буде максимальною.

Вид сировини	Норми витрат сировини, кг			Загальна кількість сировини , кг
	A	B	C
I II III	18 6 5	15 4 3	12 8 3	360 192 180
Ціна одного виробу, грн	9	10	16

• Складемо математичну модель задачі. Керовані змінні - кількість виробів A,B,C -- x₁ , x₂ , x₃ відповідно. Загальна вартість всіх виробів :

f( x₁ , x₂ , x₃ ) -= 9x₁ + 10x₂ + 16x₃ (1.1)

повинна бути максимізована. Обмеження на ресурси сировини приведуть до наступних нерівностей :

18x₁ + 15x₂ + 12 x₃ ≤ 360

6x₁ + 4x₂ + 8 x₃ ≤ 192 (1.2)

5x₁ + 3x₂ + 3 x₃ ≤ 180

Вироби можуть вироблятися в будь-яких співвідношеннях, тому інших обмежень не буде, крім обмежень на знак : x₁ , x₂ , x₃ ≥ 0.

• Створимо форму для уведення умов задачі, тобто розподілимо комірки робочого листа Excel під інформацію, що випливає зі змісту задачі.

На рис. 1.1 наведене розташування інформації , необхідної для розв’язання задачі. Слід зазначити, що це не єдино можливий варіант розміщення даних. Необхідно відрізняти комірки, що містять написи, тобто текст, і комірки, у які вводяться формули. На даному рисунку для формул відведені комірки F4; F6; F7; F8. Комірки C4; C5; C6 приділяються під значення керованих змінних X₁, X₂, X₃, які будуть уведені програмою «Пошук розв’язку» після розв’язання. Перед початком розв’язання задачі в ці комірки або вводяться нульові значення, або не вводиться взагалі ніяка інформація. Всі інші комірки заповнюються написами відповідно до рисунка.

• Введемо вихідні дані (рис.1.1 ).

В комірки C6:E8 вводимо норми витрати сировини, тобто коефіцієнти обмежень (1.2) математичної моделі. В комірки G6:G8 вводимо запаси кожного виду сировини, що є в наявності, тобто праві частини обмежень (1.2) математичної моделі. В комірки C6:E6 вводимо вартість одиниці продукції кожного виду, тобто коефіцієнти цільової функції (1.1) математичної моделі.

• Введемо залежності математичної моделі (рис.1.1, 1.2).

Рис.1.1. Розташування даних на робочому листі Excel.

В комірку F4 вводимо формулу , за якою обчислюється значення цільової функції, тобто загальна вартість продукції. Ця формула являє собою записану засобами Excel функцію (1.1). В комірки F6:F8 вводимо формули, за якими обчислюється витрата кожного виду сировини на вироблену продукцію. Ці формули відповідають лівим частинам обмежень (1.2) математичної моделі .

Загальна вартість продукції

=СУММПРОИЗВ(C4:E4;C9:E9)

Витрати

=СУММПРОИЗВ(C$4:E$4;C6:E6)

=СУММПРОИЗВ(C$4:E$4;C7:E7)

=СУММПРОИЗВ(C$4:E$4;C8:E8)

Рис.1.2. Введення формул

На цьому підготовка до розв’язання задачі за допомогою надбудови «Пошук розв’язку» закінчена. Для розв’язання необхідно виконати наступні дії:

• установити курсор на цільовій комірці;

• увійти в меню й виконати послідовно команди Сервіс – Пошук розв’язку;

• заповнити поля в діалоговому вікні Пошук розв’язку, що з'явилося;

• установити параметри Пошуку розв’язку, для чого клацнути мишкою по кнопці Параметри й заповнити поля діалогового вікна, що з'явилося, після чого клацнути по кнопці ОК;

• в діалоговому вікні Пошук розв’язку клацнути по кнопці Виконати;

• результат прочитати в комірках, відведених під значення керованих змінних й у діалоговому вікні Результати пошуку розв’язку (Результаты поиска решения), що з'явилося, клацнути по кнопці Зберегти розв’язок (Сохранить решение); якщо з якої-небудь причини розв’язок не знайдено, появиться відповідне повідомлення.

Знайдемо розв’язок задачі 1 засобами надбудови Excel Пошук розв’язку відповідно до наведеного алгоритму.

• Ставимо курсор на цільовій комірці F4 на робочому листі (рис. 1.1).

• Виконуємо Сервіс – Пошук розв’язку. Якщо в підменю Сервіс відсутня команда Пошук розв’язку, необхідно виконати наступні дії : увійти в підменю Сервіс і вибрати команду Надбудови (Надстройки), клацнувши мишкою; після цього в переліку доступних надбудов ,що з’явився, знайти Пошук розв’язку (Поиск решения) й відзначити її, клацнувши мишею, потім клацнути по кнопці ОК.

Якщо такої надбудови немає в переліку, необхідно установити повну версію Excel.

• Заповнюємо поля в діалоговому вікні Пошук розв’язку ( рис. 1.3).

Перевіряємо, чи дійсно в полі Встановити цільову комірку (Установить целевую ячейку) стоїть адреса цільової функції(для задачі 1 F4). Якщо ж ні, то потрібно внести виправлення, клацнувши мишкою по потрібній комірці, поки поле не буде виділене , як показано на рис.1.3.

У полі Рівної клацнути мишкою по опції максимальному значенню, щоб у кружечку ліворуч з'явилася позначка (крапка).

У поле Змінюючи комірки (Изменяя ячейки) ввести адреси керованих змінних ( для задачі 1 C4, D4, E4 або просто діапазон C4:D4).

Рис.1.3. Введення даних у діалогове вікно Пошук розв’язку.

У поле Обмеження (Ограничения) ввести обмеження (1.2) , але в адресному вигляді, тобто за допомогою посилань на комірки. Тут також можна використати діапазони для стислості запису. Введення починаємо із клацання по кнопці Додати (Добавить).

У діалоговому вікні Додавання обмеження (Добавление ограничения) , що з'явилося (рис.1.4), заповнюємо поля. У поле Посилання на комірку (Ссылка на ячейку) вводимо номер комірки або діапазон. Вибираємо знак нерівності, клацнувши по стрілці в правому кінці цього поля. У поле Обмеження (Ограничение) вводимо або число, або номер комірки ,або діапазон комірок.

Рис.1.4. Уведення обмежень.

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

• Установлюємо параметри пошуку розв’язку, для чого клацнемо по кнопці Параметри в діалоговому вікні Пошук розв’язку.

Інформація у вікні повинна виглядати як на рис.1.5.

Необхідно простежити, щоб при розв’язанні задач лінійного програмування було позначено, як показано на рис. 1.5, поле Лінійна модель, чого можна домогтися, клацнувши по віконцю ліворуч від назви поля в діалоговому вікні Параметри пошуку розв’язку (Параметры поиска решения).

Рис.1.5. Установка параметрів пошуку розв’язку.

Клацнувши по кнопці ОК, повертаємося у вікно Пошук розв’язку.

• Тепер можна приступати до розв’язання задачі, для чого клацнемо мишкою по кнопці Виконати (Выполнить).

• Якщо розв’язок знайдено , ми одержимо повідомлення такого вигляду

Рис.1.6. Результати пошуку розв’язку.

Необхідно клацнути по кнопці ОК, щоб зберегти знайдений розв’язок. Якщо є необхідність повернутися до вихідних даних, треба спочатку активізувати опцію Відновити вихідні дані (Восстановить исходные значения), клацнувши по кружечку ліворуч від її імені , і потім клацнути по кнопці ОК.

Результати розв’язку задачі, як ми вже відзначали, з'являться у відведених для них комірках робочого листа Excel. Як видно з рис.1.7, оптимальний план випуску продукції складається з 8 одиниць продукції типу B (комірка D4) і 20 одиниць продукції типу C (комірка E4) , продукцію типу A виробляти взагалі не слід (комірка C4). При цьому загальна вартість виробленої продукції складе 400 грн. (комірка F4).Комірки F6, F7, F8 містять кількість витраченого відповідного виду сировини. Як видно, сировина першого й другого виду витрачається повністю , тому що вміст комірок F6 й G6 та F7 й G7 збігаються. Сировина третього виду витрачається не вся і її залишок дорівнює різниці між умістом комірок G8 (запаси) і F8 (витрата), тобто 96 кг.

Рис. 1.7. Оптимальний розв’язок.