Рішення транспортної задачі в excel, mslegko

Транспортна задача - математична задача лінійного програмування спеціального вигляду про пошук оптимального розподілу однорідних об'єктів з акумулятора до приймачів з мінімізацією витрат на переміщення. Для простоти розуміння розглядається як задача про оптимальний план перевезень вантажів з пунктів відправлення в пункти споживання, з мінімальними витратами на перевезення.

Коли сумарний обсяг пропозицій (вантажів, наявних в пунктах відправлення) НЕ дорівнює загальному обсягу попиту на товари (вантажі), запитувані пунктами споживання, транспортна задача називається незбалансованою (відкритої).

Коли сумарний обсяг пропозиції дорівнює обсягу попиту, транспортна задача закритого типу або називається закритою.

Транспортна задача (класична) - задача про оптимальний план перевезень однорідного продукту з однорідних пунктів наявності в однорідні пункти споживання на однорідних транспортних засобах (зумовленості кількості) зі статичними даними та лінеарному підході (це основні умови задачі).

Для вирішення таких завдань Excel має спеціальний інструмент «Пошук рішення». Надбудова «Пошук рішення» в Microsoft Excel дозволяє безпосередньо знаходити оптимальне рішення транспортної задачі.

Для додавання надбудови «Пошук рішення», якщо на вкладці «Дані» цього пункту немає перейдіть: Файл - Параметри. Зліва виберіть меню «Надбудови». В основній частині виділіть «Пошук рішення». Потім нижче, натисніть «Перейти». У вікні відзначте пункт «Пошук рішення» і натисніть «Ok». У вкладці «Дані» з'явився відповідний однойменний пункт.

Загальна умова транспортної задачі:

Знайти m * n невід'ємних чисел Xij - обсяг перевезень від i-ого постачальника до j-ому споживачеві, що мінімізують транспортні витрати з перевезення однорідних вантажів постачальників з потужностями (запасами) А1, А2 ... Ам до споживачів з потребами В1, В2 ... Вn. якщо відомі матриця витрат Сij - витрати перевезення одиниці вантажу від i-ого постачальника до j-ому споживачеві.

Математична постановка задачі:

^&space;X_&space;=&space;A "> для i = 1,2 ... .m

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

^&space;X_&space;=&space;Bj "> для j = 1,2 ... .n

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

Приклад завдання і її рішення в Ms Excel.

Дано 5 виробників А1, А2. А3, А4. А5. потужність (запаси) яких відповідно дорівнює (рівні): 20, 45, 25, 30,20.

І чотири споживача В1. В 2. У 3. В 4. потреба яких в продукті становить відповідно: 45, 50, 20, 25.

Також відома матриця витрат Сij - витрати перевезення одиниці вантажу від i-ого постачальника до j-ому споживачеві.

Її можна представити таблицею:

У інтервал осередків A2: A6 ввести запаси постачальників - Ai:

У інтервал осередків B1: E1 ввести кількість відповідний транспорт Bj -го споживача:

Для подальшої зручності виділимо осередки різними кольорами і встановимо жирну кордон:

У інтервал осередків B2: E6 ввести матрицю Cij витрат перевезення одиниці вантажу від i-ого постачальника до j-ому споживачеві:

У осередок Н1 введіть формулу: СУММAПРОІЗВ (В2: E6; B9: E13)

У осередок A9 введіть формулу СУММ (B9: F9) і розтягніть її до А13 (як розтягнути формулу см. Тут -> Тиц):

У осередок B8 введіть формулу СУММ (B9: B13) і скопіюйте її в діапазон від B8 до E8:

Для вирішення завдання на панелі вкладок виберіть вкладку «Дані», а потім «Пошук рішення»:

Заповніть відкрилося вікно у відповідність з малюнком і натисніть Знайти рішення:

В діапазоні B9: E13 Ви отримаєте результат вирішення транспортної задачі (тобто значення в осередку відповідає кількості вантажу перевезеного від i-ого постачальника до j-ому споживачеві).

В діапазоні A9: A13 кількість вантажу, яке необхідно вивезти від постачальників.

В діапазоні B8: E8 кількість яке буде доставлено споживачам згідно знайденому рішенням.

В осередку H1 значення цільової функції при знайденому рішенні (мінімально можливий). Це значення отримано в результаті множення вартості перевезення від від i-ого постачальника до j-ому споживачеві на кількість одиниць вантажу, які необхідно перевезти між ними.

Оформимо отриманий результат і отримаємо наступне:

Завантажити файл, створений в процесі проходження уроку Ви можете по тут -> Transportation_problem