Транспортная задача с закрытой моделью

В задачах с закрытой моделью запасы поставщиков совпадают с потребностями потребителей.

Содержательная постановка задачи

Определить план доставки грузов от поставщиков потребителям при условии минимальной суммарной стоимости всех перевозок.

Условие задачи представлено в Таблице 4.

Таблица 4

Исходные данные

В каждой из первых трех строк таблицы указаны поставщик, тарифы на перевозку к каждому потребителю и величина запаса. В нижней строке указаны потребности, причем, сумма по строке «Потребность» равна сумме по столбцу «Запасы на складе».

Математическая модель задачи

К примеру, подсчитаем стоимость отдельной перевозки 15 единиц продукта от Поставщика 2 к Потребителю 3. Она составляет 15 * 4 = 60, при этом у Поставщика 2 остается еще 25 единиц продукта, а Потребителю 3 необходимо привезти от других Поставщиков еще 20 единиц.

Общая стоимость перевозок равна сумме стоимостей всех перевозок – ЦФ:

F(x) = 2X₁₁ + 3X₁₂ +5X₁₃ + 4X₁₄ +

+ 3X₂₁ + 2X₂₂ + 4X₂₃ + 1X₂₄ +

+ 4X₃₁ + 3X₃₂ + 2X₃₃ + 6X₃₄ → min

Величины X_mn являются искомыми переменными.

Оптимизационное моделирование

Построение модели

1. Создайте на Листе Excel таблицу с исходными числовыми данными и рабочую таблицу с изменяемыми ячейками, в которые будут записываться искомые результаты плана перевозок, как на Рис. 33.

Рис 33. Фрагмент листа с Excel с исходными данными

2. В таблице «План доставки» продублированы столбец «Запасы на складе» и строка «Потребность» (с помощью ссылок – Рис. 36), добавлены:

· столбец «Использовано»,

· строка «Объем доставки»,

· ячейки «Общие суммы».

3. Заполните таблицу «План доставки» формулами, необходимыми для создания ограничений (Рис. 34, 36):

· ограничения на запасы – в ячейку F11 столбца «Использовано» введите =СУММ(B11:E11), а затем скопируйте эту формулу в ячейки F12:F13;

· ограничения на потребности – в ячейку B14 строки «Объем доставки» =СУММ(B11:B13) и скопируйте ее в ячейки C14:E14.

4. Запишите общие суммы по столбцам и строкам:

· В ячейку G14 - по столбцу «Запасы на складе» =СУММ(G11:G13);

· В ячейку F15 – по столбцу «Потребность» =СУММ(B15:E15);

· В ячейку G15 введите логическую формулу для контроля общих сумм: =ЕСЛИ(F15=G14;"совпадает";не совпадает). В задаче с закрытой моделью значение этой функции – «совпадает» (Рис.34, 36).

Рис 34. Диалоговое окно логической функции ЕСЛИ

5. В ячейку G16 запишите формулу для ЦФ

=СУММПРОИЗВ(B3:E5;B11:E13).

Результат выполнения пунктов 2 – 5 в числовом и формульном режимах показан на Рис. 35 и 36.

Рис. 35. Фрагмент рабочего листа в числовом режиме

Рис. 36. Фрагмент рабочего листа в режиме формул

6. Ограничения, накладываемые на ЦФ, приведены в Таблице 5.

Таблица 5

Ограничения на целевую функцию

Исследование модели

1. Для поиска оптимального набора значений параметров плана доставки, который соответствует минимальному значению ЦФ (общей стоимости всех перевозок), следует воспользоваться надстройкой Поиск решения. Заполните диалоговое окно, как на Рис. 37.

Рис. 37. Настройка диалоговое окно ПОИСК РЕШЕНИЯ

2. В окне Параметры поиска решения активизируйте флажки ЛИНЕЙНАЯ МОДЕЛЬ и НЕОТРИЦАТЕЛЬНЫЕ ЗНАЧЕНИЯ.

3. Нажав кнопку ВЫПОЛНИТЬ, получите результат (Рис. 38).

4. В окне «Результаты поиска решения» выберите все три отчета и сохраните полученный результат как сценарий (кнопка СОХРАНИТЬ СЦЕНАРИЙ) с именем «Закрытая_М».

5. На Рис. 38. и 39. приведен оптимальный план перевозок и диаграмма плана перевозок.

Рис. 38. Результат выполнения поиска решения

Рис. 39. Диаграмма плана перевозок