РОЗВ’язування оптимізаційних задач за допомогою електонних таблиць Excel
Приклад. Підприємство виготовляє продукцію чотирьох видів, використовуючи ресурси трьох видів: трудові, сировина, фінанси. Норми затрат ресурсів, прибуток від реалізації одиниці кожного виду продукції та обсяг запасів ресурсів подані в табл. 6.1, потреба підприємства у виробництві продукції приведена в табл. 6.2
Визначити план виготовлення продукції, який забезпечить отримання максимального прибутку.
Таблиця 6.1
| Ресурс | Прод1 | Прод2 | Прод3 | Прод4 | Наявність ресурсу |
| Прибуток | |||||
| Трудові | 0,03 | 0,04 | 0,05 | 0,04 | |
| Сировина | 1,4 | 1,2 | 1,9 | ||
| Фінанси |
Таблиця 6.2
| Вид продукції | Потреба |
| Прод1 | |
| Прод3 | |
| Прод4 |
◆ Розв’язування.
Введемо позначення:
Прод1= x1, Прод2 = x2, Прод3 = x3, Прод4 = x4, z – прибуток.
Тоді математична модель задачі матиме наступний вигляд:
z = 2x1 + 3x2
+ 6x3+ 3x4
→ max,
0,03x1+ 0,04x2
+ 0,05x3 + 0,04x4
≤ 500,
1,4x1 + 1,2x2
+ 2x3 + 1,9x4
≤ 26500,
4x1+ 6x2
+ 4x3+ 7x4
≤ 10090,
x1≥ 500,
x3≥ 200,
x4≥ 800,
x2≥ 0.
Розв’яжемо цю задачу лінійного програмування з допомогою стандартної офісної програми Excel. Спочатку зробимо форму для вводу умов задачі у вигляді таблиці 6.3. Весь текст у подальшому є коментарем і на розв’язок задачі не впливає.
Таблиця 6.3
| A | B | C | D | E | F | G | H | ||||
| Змінні | |||||||||||
| ім’я | Прод1 | Прод2 | Прод3 | Прод4 | |||||||
| значення | |||||||||||
| нижн. гр. | |||||||||||
| верхн. гр. | ЦФ | напр | |||||||||
| коеф.в ЦФ | |||||||||||
| Обмеження | |||||||||||
| вид | ліва част | знак | права част | ||||||||
| Трудові | |||||||||||
| Сировина | |||||||||||
| Фінанси | |||||||||||
Другим кроком є введення вихідних даних в форму табл. 6.3 і залежностей математичної моделі. Необхідні дані представлені в табл. 6.1 та 6.2 Спочатку заносимо числові дані, потім залежності математичної моделі. Залежності вводяться таким чином.
1. Ввід залежності для цільової функції:
• Курсор в F6 (тут буде формуватися значення цільової функції).
• Курсор на кнопку «Мастер функций», ЛК (ліва клавіша).
• У вікні «Категория» вибираємо курсором категорію
«Математические», ЛК.
• Направляємо курсор у вікно функції «СУММПРОИЗВ», ЛК.
• Далее.
На екрані появляється діалогове вікно «Мастер функций – шаг 2
из 2».
• В «массив 1» ввести B$3:E$3 (в цих комірках будемо формувати значення продукції).
• В «массив 2» ввести B6:E6.
• Готово.
На екрані отримаємо дані у вигляді таблиці 6.4 (в формульному режимі – таблиця 6.5) в F6 буде значення цільової функції.
Таблиця 6.4
| A | B | C | D | E | F | G | H | |
| Змінні | ||||||||
| ім’я | Прод1 | Прод2 | Прод3 | Прод4 | ||||
| значення | ||||||||
| нижн. гр. | ||||||||
| верхн. гр. | ЦФ | напр | ||||||
| коеф.в ЦФ | ||||||||
| Обмеження | ||||||||
| вид | ліва част | знак | права част | |||||
| Трудові | 0,03 | 0,04 | 0,05 | 0,04 | <= | |||
| Сировина | 1,4 | 1,2 | 1,9 | <= | ||||
| Фінанси | <= |
2. Ввід залежностей для лівих частин обмежень:
• Курсор в F9.
• Курсор на кнопку «Мастер функций», ЛК.
• У вікні «Категория» вибираємо курсором категорію «Математические», ЛК.
• Направляємо курсор в вікно функції «СУММПРОИЗВ», ЛК.
• Далее.
На екрані появляється діалогове вікно «Мастер функций – шаг 2 из 2».
• В масив 1 ввести B$3:E$3.
• В масив 2 ввести B9:E9.
• Готово.
Цю ж процедуру можна виконати копіюванням формули з F6 в F9:
• Курсор в F6.
• Курсор на кнопку «Копировать в буфер», ЛК.
• Курсор в F9.
• Вставить из буфера.
Аналогічно копіюємо формулу в F10:F11.
На цьому введення даних закінчується.
Таблиця 6.5
| A | B | C | D | E | F | G | H | |
| Змінні | ||||||||
| ім’я | Прод1 | Прод2 | Прод3 | Прод4 | ||||
| значення | ||||||||
| нижн. гр. | ||||||||
| верхн. гр. | ЦФ | напр | ||||||
| коеф.в ЦФ | =СУММПРОИЗВ(B$3:E$3;B6:E6) | |||||||
| Обмеження | ||||||||
| вид | ліва част | знак | права част | |||||
| Трудові | 0,03 | 0,04 | 0,05 | 0,04 | =СУММПРОИЗВ(B$3:E$3;B9:E9) | <= | ||
| Сировина | 1,4 | 1,2 | 1,9 | =СУММПРОИЗВ(B$3:E$3;B10:E10) | <= | |||
| Фінанси | =СУММПРОИЗВ(B$3:E$3;B11:E11) | <= |
Далі переходимо до знаходження оптимального розв’язку.
Алгоритм 1. Знаходження оптимального розв’язку.
1. Сервис, Поиск решения...
На екрані: діалогове вікно «Поиск решения».
2. Назначаємо цільову функцію.
Для цього курсор у вікно «Установить целевую ячейку» і вводимо адресу F6.
Вводимо напрямок цільової функції: «Максимальному значению».
3. Вводимо адресу невідомих змінних:
Курсор у вікно «Изменяя ячейки», вводимо адресу B3:E3.
4. Вводимо обмеження:
• Добавить
На екрані: діалогове вікно «Добавление ограничения».
Спочатку вводимо обмеження невід’ємності змінних для Прод2 і мінімального обсягу для решти змінних:
• У вікні «Ссылка на ячейку» вводимо В3.
• Курсор на стрілку, ЛК (ліва клавіша).
• На екрані: «знаки для ввода ограничений».
• Курсор на знак « >=», ЛК.
• Курсор в праве вікно.
• Вводимо В4.
• Добавить.
На екрані знову вікно «Добавление ограничения». Аналогічно вводимо інші обмеження: C3>=C4, D3>=D4, E3>=E4.
Так само вводяться обмеження F9<=H9, F10<=H10, F11<=H11.
• Після вводу останнього обмеження натискаємо ОК.
На екрані: діалогове вікно «Поиск решения з введеными условиями».
З допомогою команд «Изменить» і «Удалить» можна за необхідності коректувати обмеження.
Оптимальний розв’язок знаходимо за таким алгоритмом:
Алгоритм 2. Розв’язування задачі.
1. Після вводу задачі, коли на екрані є вікно «Поиск решения», вибираємо «Параметры».
На екрані: діалогове вікно «Параметры поиска решения».
2. Встановлюємо відмітку «Линейная модель».
3. ОК.
На екрані: діалогове вікно «Поиск решения».
4. Выполнить.
На екрані: діалогове вікно «Результаты поиска решения» з повідомленням про те, що розв’язок знайдено. В комірках F6, В3:Е3 знаходиться оптимальний розв’язок (табл. 6.6).
Максимальний прибуток становить 7135 грн., обсяги виготовлення продукції: Прод1=500, Прод2=0, Прод3=622,5, Прод4=800.
Ресурси використовуються в такій кількості:
• трудові (див. F9) = 78,125;
• сировина = F10 = 3465;
• фінанси = F11 = 10090.
Таблиця 6.6
| A | B | C | D | E | F | G | H | ||
| Змінні | |||||||||
| ім’я | Прод1 | Прод2 | Прод3 | Прод4 | |||||
| значення | 622,5 | ||||||||
| нижн. гр. | |||||||||
| верхн. гр. | ЦФ | напр | |||||||
| коеф.в ЦФ | |||||||||
| Обмеження | |||||||||
| вид | ліва част | знак | права част | ||||||
| Трудові | 0,03 | 0,04 | 0,05 | 0,04 | 78,125 | <= | |||
| Сировина | 1,4 | 1,2 | 1,9 | <= | |||||
| Фінанси | <= | ||||||||