Практична робота
Тема: Розв’язання оптимізаційних задач різних предметних галузей засобами ІТ
Завдання: за допомогою надбудови Розв’язувач розв’язати транспортну задачу.
Обладнання: комп’ютер із ОС Windows 7 і вище, табличний процесор Excel.
Загальний опис транспортної задачі
Є N пунктів виробництва і М пунктів споживання продукції. Вартість перевезення Сij одиниці продукції з і-го пункту виробництва в j-й центр споживання зазвичай подається в таблиці, де рядки — це пункти виробництва (фірми), а стовпці — пункти споживання. Крім того, у цій таблиці в і-му рядку подається обсяг виробництва i-ї фірми, а в j-му стовпці зазначено попит в j-му центрі споживання. Складіть план перевезень від фірм до пунктів споживання з мінімізацією сумарних транспортних витрат.
На рис. 1 наведено таблицю MS Excel із конкретними значеннями умови задачі. Із таблиці видно, що є 4 фірми виробництва і 4 пункти споживання. У стовпці F вказано обсяг виробництва, а в рядку 7 — граничний обсяг споживання. Діапазон ВЗ:Е6 містить значення вартості перевезень кожною фірмою до кожного пункту споживання.
Рис. 1. Таблиця даних до транспортної задачі
Хід роботи
Під час роботи з комп’ютером дотримуйтесь правил безпеки.
1. Уведіть у таблицю Excel дані умови задачі (див. рис. 1).
2. Складіть математичну модель розв’язування задачі.
У математичній моделі цільовою функцією буде вартість усіх перевезень від усіх фірм до всіх пунктів споживання. Оптимізаційними параметрами буде кількість перевезень від кожної фірми до кожного споживача — таких параметрів буде:
16 (4 x 4 = 16).
Якщо знайти вартість перевезень від однієї фірми до кожного споживача та скласти отримані значення для всіх фірм, то отримаємо формулу для обчислення цільової функції.
Врахуйте обмеження на дані задачі.
- Загальна кількість перевезень від кожної фірми має бути меншою або дорівнювати відповідно 20, 30, 50 і 20.
- Загальна кількість перевезень до кожного пункту споживання має бути меншою або дорівнювати відповідно 30, 20, 60 і 15.
- Кількість перевезень — ціле число, яке більше або дорівнює 1.
3. У діапазон А8:F13 додайте значення відповідно до рис. 2.
Рис. 2. Початкові дані для розв’язування транспортної задачі
а) Діапазон В9:Е12 (на рис. 2 ці клітинки позначено рожевим кольором) призначено для оптимізаційних параметрів. У кожну клітинку введіть значення 1.
б) У клітинку F9 уведіть формулу підрахунку кількості перевезень фірми 1 до всіх споживачів =СУММ(В9:E9)
в) Скопіюйте формулу з клітинки F9 у діапазон F10:F12.
г) У клітинку В13 уведіть формулу підрахунку перевезень до пункту споживання 1 від усіх фірм: =СУММ(В9:B12)
ґ) Скопіюйте формулу з клітинки В13 у С13:Е13.
д) У клітинку F1З уведіть формулу підрахунку суми витрат на всі перевезення =СУММПРОИЗВ(B9:E12;B3:E6)
е) Поміркуйте, як за допомогою надбудови Розв’язувач буде використана оновлена таблиця.
ж) Чи задовольняють значення в діапазоні В13:Е13 умову задачі? Якщо ні, то які мають бути значення?
4. Перейдіть до стрічки Дані.
5. Запустіть надбудову Розв’язувач (Поиск решений).
6. У вікні Параметри розв’язувача введіть посилання на клітинку з цільовою функцією $F$13.
7. Зазначте метод оптимізації — До мінімуму. Чому обрано такий метод?
8. Укажіть діапазон клітинок із параметрами $В$9:$Е$12.
9. Уведіть обмеження на значення параметрів відповідно до рис. 3.
Рис. 3. Обмеження на дані задачі
10. Активізуйте кнопку Розв’язати (Найти решение).
11. Порівняйте отриманий результат із даними, наведеними на рис. 4.
Рис. 4. Варіант розв’язування транспортної задачі
12. Зробіть висновок за результатами виконання роботи.
