Решение транспортной задачи в Excel

Имеется несколько пунктов производства и пунктов потребления некоторого продукта. Для каждого из пунктов производства задан объем производства, а для каждого пункта потребления - объем потребления. Известна также стоимость перевозки из каждого пункта производства в каждый пункт потребления. Все пункты потребления должны быть обеспечены необходимой продукцией, но из каждого пункта производства не может вывозиться продукции больше, чем там производится, а стоимость перевозки должна была минимальной.

Фирме необходимо организовать перевозку продукции с трех складов в пять магазинов. Сведения о наличии продукции на складах, о потребности в этой продукции у магазинов и о стоимости перевозки единицы продукции с каждого склада во все магазины приведены в табл.7.2.8.

Таблица 7.2.8

Разработка начального плана решения

Обозначим

Х_ij - количество продукции, отправляемой со склада i в магазин j,

С_ij - стоимость перевозки единицы продукции со склада i в магазин j.

Начальный план решения приведен в табл. 7.2.9 - режим вычислений.

Таблица 7.2.9

1. Подготовим блок ячеек с исходными данными

В ячейках В4:В7 помещаем сведения о наличии продукции на складах. В ячейках C9:G9 - сведения о потребностях магазинов. В ячейках C5:G7 -данные о стоимости перевозок единицы продукции со складов в магазин.

2. Построим начальный план перевозок.

Считаем, что с каждого склада в каждый магазин везут одну единицу товара (ячейки C11:G13 заполним единицами).

3. Вычислим количество перевозимой продукции.

а) В ячейку В11 введем формулу для вычисления количества продукции, вывозимой с первого склада:

=СУММ(C11:G11).

Аналогично в ячейки В12, В13 введем формулы для вычисления количества продукции, вывозимой со второго и третьего складов:

=CУMM(C12:G12).

=CУMМ(C13:G13).

Для начального плана перевозок все суммы равны 5.

б) В ячейку С15 введем формулу для вычисления количества продукции, которую везем в первый магазин:

=СУММ(С11:С13).

Аналогично в ячейки D15:G15 введем формулы для вычисления количества продукции, которую везем во 2-й, 3-й, 4-й, 5-й магазины.

В ячейку D15 = СУММ (D11:D13).

В ячейку Е15 =СУММ (Е11:Е13).

В ячейку F15 =СУММ (F11:F13).

В ячейку G15 =СУММ (G11:G13).

4. Определим стоимость перевозок в каждый из магазинов: Для определения стоимости перевозок в 1-й магазин

Z₁=X₁₁C₁₁+X₁₂C₁₂+Х₁₃С₁₃

введем в ячейку С16 формулу

=СУММПРОИЗВ(С5:С7;С11:С13).

В ячейке D16 вычислим стоимость перевозок во второй магазин:

Z₂=X₁₂C₁₂+Х₂₂С₂₂+Х₃₂С₃₂, т.е. введем формулу

=СУММПРОИЗВ(D5:D7;D11:D13)

Аналогично в ячейки E16:G16 введем формулы для вычисления стоимости перевозок в остальные магазины.

В ячейку Е16 =СУММПРОИЗВ(Е5:Е7;Е11:Е13).

В ячейку F16 =СУММПРОИЗВ(F5:F7;F11:F13).

В ячейку G16 =СУММПРОИЗВ(G5:G7;G11:G13).

5. Определим общую стоимость перевозок (целевую функцию ЦФ):

Z=Z₁+Z₂+Z₃+Z₄+Z₅

Для этого введем в ячейку В17 формулу

=СУММ(C16:G16)

Для нашего начального плана получится стоимость, равная 44 денежным единицам.

Улучшение (оптимизация) плана перевозок

Используем режим Поиск решения Excel.

1) После выполнения команд Сервис, Поиск решения открывается диалоговое окно Поиск решения (рис. 7.2.3)

2) Введем данные:

Установить целевую ячейку В17

Равной: минимальному значению

Изменяя ячейки: C11:G13

3) Щелкнуть по кнопке Добавить для ввода ограничений.

Рисунок 7.2.3

4) В открывшемся окне Добавление ограничений (рис. 7.2.4) ввести ограничения.

C11:G13≥0.

С11:G13=целые.

В11:В13≤B5:B7.

C15:G15=C9:G9.

Рисунок 7.2.4

Левая часть каждого ограничения вводится в поле Ссылка на ячейку, правая часть ограничения - в поле Ограничение, знак выбирается в средней части окна. После ввода каждого ограничения нужно нажать кнопку Добавить, после ввода последнего ограничения нажать кнопку ОК.

5) Для запуска режима Поиск решения щелкнуть по кнопке Выполнить (см. рис. 7.2.3). Если все формулы и все ограничения введены правильно, появится окно с сообщением о том, что решение найдено. Щелкнув по кнопке ОК, получаем решение (табл. 7.2.10). В результате улучшения плана теперь мы имеем оптимальный план стоимости перевозок с целевой функцией, равной 121 единице.

Таблица 7.2.10