Тема 24 (10): Практична робота №2: Використання надбудови Розв’язувач для розв’язування задач

(Практична робота по підручнику рівня стандарту - Пр №3 с. 79-82)

Тест 10-23

Завдання: за допомогою надбудови Розв’язувач розв’язати транспортну задачу.

Обладнання: комп’ютер зі встановленим табличним процесором.

Загальний опис транспортної задачі

Є N пунктів виробництва і M пунктів споживання продукції. Вартість перевезення Cij одиниці продукції з і-го пункту виробництва в j-й центр споживання зазвичай подають у таблиці, де рядки — це пункти виробництва (фірми), а стовпці — пункти споживання. Крім того, у цій таблиці в і-му рядку подають обсяг виробництва і-ї фірми, а в j-му стовпці зазначено попит в j-му центрі споживання. Необхідно скласти план перевезень від фірм до пунктів споживання з мінімізацією сумарних транспортних витрат.

На рис. 1 наведено таблицю MS Excel із конкретними значеннями умови задачі. Із таблиці видно, що є 4 фірми виробництва і 4 пункти споживання. У стовпці F указано обсяг виробництва, а в рядку 7 граничний обсяг споживання. Діапазон В3:Е6 містить значення вартості перевезень від кожної фірми до кожного пункту споживання.

Хід роботи

Під час роботи з комп’ютером дотримуйте правил безпеки.

1. Уведіть у таблицю Excel дані умови задачі.

2. Складіть математичну модель розв’язування задачі. У математичній моделі цільовою функцією буде вартість усіх перевезень від усіх фірм до всіх пунктів споживання. Оптимізаційними параметрами буде кількість перевезень від кожної фірми до кожного споживача — таких параметрів буде:

16 (4 × 4 = 16).

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

Урахуйте обмеження на дані задачі.

• Загальна кількість перевезень від кожної фірми має бути меншою або дорівнювати відповідно 20, 30, 50 і 20.

• Загальна кількість перевезень до кожного пункту споживання має бути меншою або дорівнювати відповідно 30, 20, 60 і 15.

• Кількість перевезень — ціле число, яке більше або дорівнює 1.

3. У діапазон А8:G13 додайте значення відповідно до рис. 2:

а) діапазон В9:Е12 (на рисунку ці клітинки виділено блакитним кольором) призначено для оптимізаційних параметрів — у кожну клітинку введіть значення 1;

б) у клітинку F9 введіть формулу обчислення вартості перевезень фірми 1 до всіх споживачів =B9*B3+C9*C3+D9*D3+E9*E3;

в) скопіюйте формулу з клітинки F9 у F10:F12;

г) у клітинку В13 уведіть формулу обчислення перевезень до пункту споживання 1 від усіх фірм =B9*B3+B10*B4+B11*B5+B12*B6;

д) скопіюйте формулу з клітинки В13 у С13:Е13;

е) у клітинку F13 уведіть формулу підрахунку суми F9:F12.

4. Перейдіть до стрічки Дані.

5. Запустіть надбудову Розв’язувач.

6. У вікні Параметри розв’язувача введіть посилання на клітинку з цільовою функцією $F$13.

7. Зазначте метод оптимізації — До мінімуму.

8. Укажіть діапазон клітинок із параметрами $B$9:$E$12.

9. Уведіть обмеження на значення параметрів відповідно до рис. 3

10. Активізуйте кнопку Розв’язати.