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

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

В этом материале попробуем разобраться, как решить транспортную задачу в Excel. Среда решения – Excel. Данный материал подходит для версий программы: 2007, 2010, 2013, 2016.

Постановка задачи и подготовка таблиц

Цель задачи сводится к математическому моделированию минимизации грузопотоков. Довольно часто студенты пишут рефераты на тему поиска решения транспортной задачи. Этот пример можно взять за основу реферата. Рассмотрим решение на конкретном примере.

Задача

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

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

Наличие минеральных удобрений (либо иной продукции)  на складах.

СкладыНаличие удобрений, т.
Склад № 1200
Склад № 2190
Склад № 3220
Склад № 4145
Склад № 5280

Потребность в минеральных удобрениях на различных пунктах.

ПунктыПотребность в удобрениях
1 пункт200
2 пункт150
3 пункт220
4 пункт330

Расстояние между складами и пунктами доставки

Пункт 1Пункт 2Пункт 3Пункт 4
Склад № 164511
Склад № 21264
Склад № 3157104
Склад № 495125
Склад № 5371211

Данные в таблицах. На пересечении столбца конкретного пункта доставки со строкой склада находится информация о расстоянии между этим пунктом доставки и складом. Например, расстояние между 3 пунктом и складом № 3 равно 10 километрам.

Пошаговое решение в Excel

Подготовим таблицы для решения задачи.

Единичная матрица
Рисунок 1. Изменяемые ячейки.

Значения ячеек в столбце B с третьей по седьмую определяют сумму значения соответствующих строк со столбца C до столбца F.

Например, значение ячейки B3=СУММ(C4:F4)

Аналогично значения в восьмой строке, складываются из суммы соответствующих столбцов. Далее создадим еще одну таблицу.

Исходные данные
Рисунок 2. Исходная информация в Excel

 

В строке 16 по столбцам C-F определим грузооборот по каждому пункты доставки. Например, для пункта 1 (ячейка С16) это рассчитывается по формуле:

C16==C3*C11+C4*C12+C5*C13+C6*C14+C7*C15

Либо, это можно рассчитать с помощью функции СУММПРОИЗВ:

C16 =СУММПРОИЗВ(C3:C7;C11:C15)

В ячейке B4 находится количество минеральных удобрений, перевозимых со склада № 1 в 1 пункт доставки, а в ячейке C11 — расстояние от склада №1 до 1 пункта доставки. Соответственно первое слагаемое в формуле означает полный грузооборот  по данному маршруту. Вся же формула вычисляет полный грузооборот перевозок минеральных удобрений в 1 пункт доставки.

В ячейке B16 по формуле =СУММ(C16:F16) будет вычисляться общий объем грузооборота минеральных удобрений. Рабочий лист примет следующий вид.

Заготовка в Excel для решения транспортной задачи
Рисунок 3. Рабочий лист, приготовленный для решения транспортной задачи.

Для решения транспортной задачи воспользуемся процедурой Поиск решения, которая находится на вкладке Данные. Если у вас нет процедуры Поиск решения, необходимо зайти в Параметры Excel -> Надстройки — > Поиск решения.

После выбора данной процедуры на вкладке Данные откроется диалоговое окно.

Поиск решения в Excel
Рисунок 4. Диалоговое окно Поиск решения.

Выберем целевую ячейку $B$16, установим ее равной минимальному значению, что бы минимизировать значение конечной ячейки, путем изменения влияющих ячеек, изменяя ячейки, выберем диапазон с единицами $C$3:$F$7.

Условия для решения
Рисунок 5. Условия для решения транспортной задачи.

Если запустить процесс, то мы получим параметры равные нулям. Для получения необходимых значений установим некоторые ограничения:

  1. $B$3:$B$7 <= $B$11:$B$15
  2. $C$3:$F$7 >= 0
  3. $C$8:$F$8 >= $C$10:$F$10

После всех установок нажмем «Выполнить» и получаем результат.

Оставьте комментарий