Решение транспортной задачи с помощью табличного процессора Excel

Заводы некоторой автомобильной фирмы расположены в городах А, В и С. Основные центры распределения продукции сосредоточены в городах D и E. Объемы производства указанных трех заводов равняются 1000, 1300 и 1200 автомобилей ежеквартально. Величины квартального спроса в центрах распределения составляют 2300 и 1400 автомобилей соответственно. Стоимости перевозки автомобилей по железной дороге по каждому из возможных маршрутов приведены в таблице.

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

Построение математической модели. Определение переменных:

Обозначим количество автомобилей, перевозимых из i-го завода в j-й пункт потребления через .

Проверка сбалансированности задачи:

Проверим равенство суммарного производства автомобилей и суммарного спроса

откуда следует вывод – задача несбалансирована, поскольку спрос на автомобили превышает объем их производства. Для установления баланса введем дополнительный фиктивный завод с ежеквартальным объемом производства 200 шт. (). Фиктивные тарифы приравняем к нулю (т.к. перевозки в действительности производиться не будут).

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

Задание целевой функции:

Суммарные затраты в рублях на ежеквартальную перевозку автомобилей определяются по формуле

Задание ограничений:

[шт./квартал]

Решение помощью Microsoft Excel. Вводим на лист Microsoft Excel исходные данные – таблицу, содержащую объемы производства заводов, спрос в центрах распределения продукции и стоимости перевозки автомобилей по железной дороге по каждому из возможных маршрутов с учетом дополнительного фиктивного завода:

Добавляем на лист план перевозок. Примем начальные значения искомых переменных = 1. Также введем формулы для расчета левых частей ограничений:

В ячейку A18 введем формулу, рассчитывающую суммарные затраты в рублях на ежеквартальную перевозку автомобилей (целевую функцию):

=СУММПРОИЗВ(B3: C6; B11: C14)

В результате получим следующее содержание листа Excel:

Выполняем команду Сервис – Поиск решения и заполняем появившуюся экранную форму следующим образом:

- установить целевую ячейку A18 равной минимальному значению;

- изменяя ячейки $B$11: $C$14;

- при ограничениях $B$7=$B$15

$C$7=$C$15

$D$3=$D$11

$D$4=$D$12

$D$5=$D$13

$D$6=$D$14

$B$11: $C$14> =0

Щелкаем по кнопке Выполнить. Компьютер выдает сообщение о том, что решение найдено, все ограничения и условия оптимальности выполнены. Сохраняем найденное решение:

Делаем выводы. Оптимальный маршрут: 1000 автомобилей с завода A и 1300 автомобилей с завода B отправляем в центр распределения D, 1200 автомобилей с завода C отправляем в центр распределения E. Суммарные затраты в рублях на ежеквартальную перевозку автомобилей при таком маршруте составят 291600 ден. ед.

Задание для самостоятельного выполнения. Груз, хранящийся на трех складах и требующий для перевозки 60, 80, 106 автомашин соответственно, необходимо перевести в четыре магазина. Первому магазину требуется 44 машины груза, второму – 70, третьему – 50 и четвертому – 82 машины. Стоимость пробега одной автомашины за 1 км составляет 10 д.е. Расстояния от складов до магазинов указаны в следующей таблице:

С помощью Excel определите оптимальный по стоимости план перевозки груза от складов до магазинов.