Глава 5. Использование MS Excel для решения транспортной задачи

Пример.

Три подразделения объединения специализируются на производстве крепежа, который поставляется в четыре подразделения поставщикам. Необходимые данные приведены в таблице.

Изготовитель	Тариф на перевозку, руб.	Количество изготовленных комплектов, шт.
Цех 1
Цех 2
Цех 3
Требуется потребителю, шт.

Нужно минимизировать затраты на перевозку продукции.

По исходным данным формулируем математическую модель.

Затраты на перевозку продукции потребителям вычисляются по формуле , где − объем перевозки от i-го поставщика (цеха) j-му потребителю, − тариф на перевозку. Необходимо составить план перевозок, т.е. найти значения так, чтобы затраты были минимальны.

Запишем ограничения задачи:

1) , так как это объем перевозок и он не может быть отрицательным.

2) − количество товара, привезенное j-му потребителю должно равнять его потребности в этом товаре (не можем привезти больше, чем надо).

3) − количество товара, увезенное от i-го поставщика должно равнять его наличию у этого поставщика (не можем увезти больше, чем есть).

Теперь создаем таблицу в Excel.

Далее нужно установить числовой целый формат во всех ячейках, содержащих числа (B5:E8 и B13:E16).

В ячейки F13, F 14, F15 вводим формулу суммы по соответствующим строкам.

В ячейки В16, С16, D16, E16 вводим формулу суммы по соответствующим столбцам.

В ячейку вводим формулу для вычисления стоимости перевозки, которую нужно минимизировать

Далее надо воспользоваться сервисом «Поиск решения». Выбираем пункт меню Сервис\Поиск решения. Если этого пункта нет, то его надо включить в Сервис\Надстройки.

В открывшемся окне заполняем поля необходимыми данными:

1) Целевая ячейка – это стоимость перевозки (В18);

2) Установить переключатель на Минимальное значение;

3) Изменяя ячейки – это диапазон плана перевозок, то есть искомые значения (В13:Е15)

Ограничения:

1) Нажать кнопку «Добавить», в открывшемся окне ввести первое условие (это условие );

2) Снова нажать кнопку «добавить», в открывшемся окне ввести второе условие

3) Снова нажать кнопку «добавить», в открывшемся окне ввести третье условие .

В результате должны получиться следующие данные

Нажать кнопку «Выполнить». После этого будет выведено окно .

Нажать кнопку «ОК». Получим следующие результаты

В результате получили следующий план перевозок:

1) из Цеха 1 нужно отправить 30 комплектов Поставщику 1 и 40 комплектов Поставщику 2;

2) из Цеха 2 нужно отправить 20 комплектов Поставщику 1 и 60 комплектов Поставщику 4;

3) из Цеха 3 нужно отправить 50 комплектов Поставщику 2 и 100 комплектов Поставщику 3.

Общая стоимость перевозки составит 38900 руб.