Розв'язання задач транспортного типу з використанням можливостей програми Microsoft Excel

Мета роботи: навчитися в середовищі програми Microsoft Excel знаходити розв’язок технологічних задач на основі моделі транспортної задачі.

Короткі теоретичні відомості.

Математична модель завдання: нехай загальний об'єм ресурсів, що поставляються, дорівнює загальному об'єму споживаних ресурсів, тобто.

(1).

У такій постановці задача має назву збалансованої і в цьому випадку її можна вирішити методом потенціалів.

Обмеження задачі:

Ресурси всіх джерел (пунктів відправлення) мають бути реалізовані:

; (2).

Запити всіх пунктів споживання мають бути задоволені:

;(3).

Цільова функція: транспортні витрати мають бути мінімальні.

.

де x_ij — кількість продукції, що перевозиться з i-го пункту виробництва в j, — пункт вжитку.

i = 1. n, j = 1.m. (4).

Завдання (2) -(4) є спеціальним завданням лінійного програмування і називається транспортним завданням.

Приклад виконання роботи На трьох хлібокомбінатах щодня виробляється 110, 190 і 90 т муки. Ця мука споживається чотирма хлібозаводами, щоденні потреби яких рівні відповідно 80, 60, 170 і 80 т. Тарифи перевезень, грн, 1 т муки з хлібокомбінатів до кожного з хлібозаводів задаються матрицею.

Скласти, використовуючи Microsoft Excel такий план перевезення муки, при якому загальна вартість перевезень буде мінімальною.

Розв’язок. Для вихідної задачі математична модель має наступний вигляд: цільова функція:

.

Задачі такого типу також розв’язуються в редакторі електронних таблиць Microsoft Excel за допомогою надбудови Поиск решения (пункт меню Сервис). Для цього на робочому аркуші Microsoft Excel створимо форму (рис. 3.1), увівши в неї вихідні дані. У вікні Поиск решения вводимо відповідні обмеження (рис. 3.2).

Рис. 3.1 Вихідний вигляд форми для введення даних і залежностей

Рис. 3.2 Вигляд вікна «Поиск решения»

Результат розв’язання надбудовою задачі зображений на рис. 3.3.

Рис. 3.3 Результат розв’язання транспортної задачі в MS Excel

Порядок виконання роботи з використанням програми Microsoft Excel.

• 1. Ознайомитися з короткими теоретичними відомостями.
• 2. Відкрити робочу книгу Microsoft Excel.
• 3. Зберегти відкриту робочу книгу.
• 4. Розглянути приклад виконання роботи. Увести на аркуші робочої книги форму вихідних даних (рис. 3.1) за прикладом виконання роботи. У вікні Поиск решения ввести обмеження моделі.
• 5. Увести на наступному аркуші робочої книги форму вихідних даних і результатів для свого варіанта за наведеною методикою.

Варіанти завдань для самостійного виконання.

• 1. На чотирьох складах — А, Б, В і Г знаходиться 40, 60, 70, 100 т пального відповідно. Перевезення однієї тонни пального із складу, А в пункти 1, 2, 3 відповідно стоїть 1, 3 і 5 грн., із складу Б в ті ж пункти — 4, 6 і 3 грн., із складу В — 2, 9 і 5 грн., із складу Г — 1, 5 і 3 грн. У кожен пункт треба доставити по однаковій кількості тонн пального.
• 2. На трьох складах — А, Б і В знаходиться по 120 т пального. У пункт 1 треба доставити 150 тонн пального, в пункт 2 — 90 тонн пального, в пункт 3 — 120 тонн пального. Перевезення однієї тонни пального із складу, А в пункти 1, 2, 3 відповідно стоїть 4, 2 і 1 грн., а перевезення однієї тонни із складу Б в ті ж пункти — відповідно 2, 6 і 3 грн., із складу В в ті ж пункти — відповідно 3, 6 і 5 грн.
• 3. У резерві трьох залізничних станцій А, В і С знаходиться відповідно 60, 80 і 100 вагонів. Пункту 1 вантаження хліба необхідно 40 вагонів, пункту 2 — 60 вагонів, пункту 3 — 80 вагонів і пункту 4 — 60 вагонів. Вартості перегонів одного вагону із станції А у вказані пункти відповідно рівні 1, 2, 3, 4 грн., із станції В — 4, 3, 2, 6 грн. і із станції С — 7, 2, 2, 1 грн.
• 4. Завод має три цехи — А, В, С і чотири склади — № 1, № 2, № 3, № 4. Цех, А виробляє 30 тис. шт. виробів, цех В — 40 тис. шт., цех С — 20 тис. шт. Пропускна спроможність складів за той же час характеризується наступними показниками: склад № 1- 20 тис. шт. № 2 — 30 тис. шт., склад № 3 — 30 тис. шт., склад № 4 — 10 тис. шт. Вартість перевезення 1 тис. шт. виробів з цеху, А в склади № 1, № 2, № 3, № 4 відповідно рівна 2, 3, 2, 4 грн., з цеху В — 3, 2, 5, 1 грн., з цеху С — 4, 3, 2, 6 грн.
• 5. У трьох сховищах пального щодня зберігається 175, 125 і 140 т бензину. Цей бензин щодня отримують чотири заправні станції в кількостях відповідно 180, 160, 60 і 40 т. Вартості перевезень 1 т бензину з сховищ до заправних станцій задаються матрицею

.

Зміст звіту.

• 1. Мета роботи.
• 2. Короткі теоретичні відомості.
• 3. Опис усіх етапів виконання роботи.
• 4. Опис отриманих результатів.
• 5. Висновки за результатами роботи.

Контрольні питання.

• 1. Постановка транспортної задачі, умова існування її розв’язку.
• 2. Пошук початкового опорного плану: методи північно-західного кута і мінімального елемента.
• 3. Пошук оптимального опорного плану перевезень за методом потенціалів.
• 4. Методика розв’язування транспортної задачі у Microsoft Excel.

Література: [1−3, 5, 6].