Рішення стандартних транспортних завдань в excel - студопедія

Цільова функція задає загальну вартість перевезень

Створюємо на Листі Excel таблицю з вихідними числовими даними і робочу таблицю із змінними комірками, в які будуть записуватися шукані результати плану перевезень (рис.1).

Заповнюємо таблицю «План доставки» формулами, необхідними для створення обмежень:

1. Обмеження на запаси - в клітинку F 11 вводимо = СУММ (В 11: Е 11). Потім копіюємо цю формулу в комірки F 12: F 13.

2. Обмеження на потреби - в клітинку У 14 ведіть = СУММ (В 11: У 13) і скопіюйте її в комірки З 14: Е 14.

3. Загальні суми запасів на складі - в клітинку G 14 введіть = СУММ (G 11: G 13).

4. Загальна сума потреб - в клітинку F 15 вводимо = СУММ (В 15: Е 15).

5. У осередок G 15 введіть логічну формулу для контролю загальних сум: = ЕСЛИ (F 15 = G 14; ²совпадает²; ²не совпадает²).

6. У осередок G 16 запишіть формулу для цільової функції = СУММПРОИЗВ (В 3: Е 5; В 11: Е 13).

Результат виконання пунктів 1-6 в числовому режимі показаний на рис. 1.

Мал. 1. Фрагмент робочого листа в числовому режимі

Мал. 2. Вікно «Пошук рішення»

Кнопка Припустити служить для автоматичного вибору змінюваних осередків. Далі натискаємо кнопку Параметри і у вікні встановлюємо прапорці Лінійна модель і позитивне значення. Решта значення можна залишити без зміни. Натискаємо кнопку ОК. Для вирішення завдання в вікні Пошук рішення натискаємо кнопку Виконати. Отриманий результат представлений на рис. 3.

Мал. 3. Результат виконання пошуку рішення

Після знаходження рішення з'являється діалогове вікно для вибору типу звіту (рис. 4). Виберіть всі три звіти і збережіть отриманий результат як сценарій з ім'ям «Закрита модель». Звіти з'являються на окремих аркушах книги Excel. При несумісною системі обмежень задачі в діалоговому вікні з'явиться повідомлення «Пошук не може знайти відповідного рішення». При необмеженість цільової функції в заданому напрямку в діалоговому вікні з'явиться повідомлення «Значення цільової осередки не сходяться».

Мал. 4. Діалогове вікно для вибору типу звіту

Отримане рішення оптимально, але може бути не єдиним. Отримані звіти дозволяють знайти інші можливі рішення.

Звіт Результати складається з трьох таблиць і має такий вигляд (рис. 5):

Мал. 5. Звіт за результатами

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

Звіт Стійкість містить дві таблиці (рис. 6).

Перша з цих таблиць «Змінні комірки» містить діапазони зміни коефіцієнтів цільової функції, в яких не відбувається зміни оптимального рішення. Тут наведено також нормований градієнт, який вказує, на скільки зміниться цільова функція, якщо значення в даній комірці збільшиться на одиницю. Для транспортної задачі нормований коефіцієнт дорівнює різниці тарифу і непрямого тарифу для даного осередку. Непрямий тариф визначається як сума потенціалів вільних осередків. Якщо всі оцінки вільних осередків ³0, то такий план поліпшити не можна, оскільки збільшення значення в будь-якому осередку призведе до збільшення цільової функції. Наявність оцінки £ 0 в якийсь вільної осередку вкаже на неоптимальність плану. Чим більше значення оцінки, тим менше перспективна ця група для включення її в план перевезень.

Мал. 6. Звіт по стійкості

У другій таблиці «Обмеження» наводяться тіньові ціни ресурсів і діапазони зміни обсягів доставки, в яких тіньові ціни залишаються незмінними. Значення 1E + 30, наведені в таблиці, означають можливість нескінченного збільшення правих частин відповідних обмежень без зміни тіньових цін.

Звіт Межі містить дві таблиці (рис. 7).

Мал. 7. Звіт по межах

У першій зазначено значення цільової функції. А в другій наведено список впливають осередків, їх значень, а також нижніх і верхніх меж. Для транспортної задачі із закритою моделлю нижні і верхні межі збігаються.

Приклад 4. Транспортна задача з відритої моделлю.

У моделях з відкритою моделлю запаси постачальників не рівні потребам споживачів.

Нехай запаси постачальників більше потреб споживачів (таблиця 18).

На Лист Excel із закритою моделлю прикладу 3 видаліть з плану доставки отримані результати і введіть зміни у вихідні дані згідно з таблицею 19. Чи зміниться загальна сума запасів на складі (90) і потреб (105). Значення комірки G 15 «не збігається».

Виконайте пошук рішення в нових умовах, змінивши обмеження (рис. 10). Вираз $ B $ 14: $ E $ 14<=$B $15:$E $15 означает условие неполного удовлетворения потребностей.

Мал. 10. Заповнення діалогового вікна Пошук рішення

Результат рішення (рис. 11) збережіть у вигляді сценарію з ім'ям «Відкрита модель 2».

Мал. 11. Результати пошуку рішення

1. Яка постановка стандартної транспортної задачі?

2. Розкрийте поняття збалансованості транспортної задачі.

3. Що таке фіктивні і забороняють тарифи?

4. Поясніть, в чому полягає метод північно-західного кута?

5. Яким чином перевіряється оптимальність плану?

6. Як вибираються цикли?

7. Як вибирається величина, яка переміщується по клітинам?

8. Які завдання ставляться до транспортних завдань з відкритою моделлю?

9. Які завдання ставляться до транспортних завдань із закритою моделлю?

10. Розробіть алгоритм пошуку рішення відкритої транспортної задачі за допомогою Excel