Розв’язування задач ЛП в середовищі Microsoft Excel

2.3.1 Задача оптимального використання ресурсів в середовищі Microsoft Excel

Розв’яжемо приклад 1 про оптимальне використання ресурсів за допомогою Microsoft Excel.

Порядок розв’язування задачі ЛП:

1) створити документ Microsoft Excel, занести дані з табл. 2.1. В першому рядку записати назву задачі. В рядку №3 записуємо змінні: ; в рядку №4 – залишаємо місце для рішення. Записуємо обмеження та цільову функцію (рис. 2.1).

Рисунок 2.1 – Вихідні дані для розв’язання задачі ЛП з оптимального використання ресурсів

2) для лівої частини обмежень використовується функція СУММПРОИЗВ (дана функція обчислює суму добутків масивів для кожного обмеження у вигляді ), яку можна викликати через: Вставка → Функція → Математичні → СУММПРОИЗВ. В комірці D7 потрібно записати знак “=”, використати: Вставка → Функція → Математичні → СУММПРОИЗВ (В4:С4; В7:С7) → Ok (рис. 2.2).

Рисунок 2.2 – Розв’язування задачі ЛП з оптимального використання ресурсів за допомогою Microsoft Excel

Аналогічно, в комірці D8 потрібно записати знак “=”, використати: Вставка → Функція → Математичні → СУММПРОИЗВ (В4:С4; В8:С8) → Ok.

В комірці D9 потрібно записати знак “=”, використати: Вставка → Функція → Математичні → СУММПРОИЗВ (В4:С4; В9:С9) → Ok.

В комірці D10 потрібно записати знак “=” та цільову функцію (рис. 2.3).

Рисунок 2.3 – Розв’язування задачі ЛП з оптимального використання ресурсів за допомогою Microsoft Excel

3) Активізувати режим Сервіс → Надстройки → Поиск решения → Ok.

Відмітити комірку D10 і використати: Сервіс → Поиск решения. У вікні, яке з’явилося, потрібно вибрати: “Установить целевую ячейку” (вона має співпадати з цільовою функцією). Потрібно вибрати критерій оптимізації: максимальне значення, мінімальне значення чи інше значення. Далі, заповнити “Изменяя ячейки” з посиланням на блок $B$4:$C$4. А також заповнити “Ограничения”, використовуючи “Добавить” (рис. 2.4).

Рисунок 2.4 – Розв’язування задачі ЛП з оптимального використання ресурсів за допомогою Microsoft Excel

4) Заповнити вікно “Добавить ограничения”: відношеннями лівої та правої частин обмежень, натискаючи кнопку “Добавить”. У рядку знак вибрати той, який відповідає обраному обмеженню (рис. 2.5).

Рисунок 2.5 – Розв’язування задачі ЛП з оптимального використання ресурсів за допомогою Microsoft Excel

5) Отримаємо наступні результати (рис. 2.6):

Рисунок 2.6 – Розв’язування задачі ЛП з оптимального використання ресурсів за допомогою Microsoft Excel

6) Зайти на вкладку Параметри та вибрати режим: Линейная модель → Неотрицательные значения → Ok (рис. 2.7).

Рисунок 2.7 – Розв’язування задачі ЛП з оптимального використання ресурсів за допомогою Microsoft Excel

7) Виконавши ці дії, потрібно натиснути кнопку “Выполнить”. Після закінчення обчислень на екрані з’явиться вікно Результаты поиска решения, у відображено повідомлення про результати роботи. У цьому вікні потрібно вибрати “Сохранить найденное решение” → Ok (рис. 2.8).

Рисунок 2.8 – Розв’язування задачі ЛП з оптимального використання ресурсів за допомогою Microsoft Excel

Ми отримали оптимальний розв’язок: . Ресурси Р2 та Р3 використані повністю, а ресурс Р1 є в залишку 15 одиниць.

8) У вікні “ Результаты поиска решения”, а також можна вибрати “Сохранить сценарий”, назвати сценарій. Результати сценарію будуть відображені на іншому листі Excel (рис. 2.9).

Рисунок 2.9 – Розв’язування задачі ЛП з оптимального використання ресурсів за допомогою Microsoft Excel

2.3.2 Задача про суміші в середовищі Microsoft Excel

Розв’яжемо приклад 2 про добовий раціон споживання речовин за допомогою Microsoft Excel (на основі таблиці 2.5):

1) Для лівої частини обмежень використовується функція СУММПРОИЗВ (дана функція обчислює суму добутків масивів для кожного обмеження у вигляді ), яку можна викликати через: Вставка → Функція → Математичні → СУММПРОИЗВ.

В комірці Е7 потрібно записати знак “=”, використати: Вставка → Функція → Математичні → СУММПРОИЗВ (В4:D4; В7:D7) → Ok.

В комірці Е8 потрібно записати знак “=”, використати: Вставка → Функція → Математичні → СУММПРОИЗВ (В4:D4; В8:D8) → Ok.

В комірці E9 потрібно записати знак “=” та цільову функцію (рис. 2.10).

Рисунок 2.10 – Розв’язування задачі про суміші за допомогою Microsoft Excel

2) Активізувати режим Сервіс → Надстройки → Поиск решения → Ok. Відмітити комірку Е9 і використати: Сервіс → Поиск решения. Всі наступні дії, провести за пунктами 3-8, описаними для попередньої задачі. В даному прикладі потрібно мінімізувати значення цільової функції (рис. 2.11).

Рисунок 2.11 – Розв’язування задачі про суміші за допомогою Microsoft Excel

3) Отримані результати мають вигляд (рис. 2.12):

Рисунок 2.12 – Розв’язування задачі про суміші за допомогою Microsoft Excel

Тема 3 Транспортна задача та економічні задачі, що зводяться до задач транспортного типу

3.1. Транспортна задача

3.2. Транспортна задача в середовищі Microsoft Excel

3.3. Економічні задачі, що зводяться до задач транспортного типу

3.3.1 Однопродуктова модель галузевого планування

3.3.2 Задача формування штатного розпису підприємства

Дата добавления: 2014-01-03