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

Транспортная задача

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

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

Цель задачи сводится к математическому моделированию минимизации грузооборотов.

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

Итак, рассмотрим решение на конкретном примере.

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

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

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

Склады Наличие удобрений, т.
Склад № 1 200
Склад № 2 190
Склад № 3 220
Склад № 4 145
Склад № 5 280

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

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

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

Пункт 1 Пункт 2 Пункт 3 Пункт 4
Склад № 1 6 4 5 11
Склад № 2 12 6 4
Склад № 3 15 7 10 4
Склад № 4 9 5 12 5
Склад № 5 3 7 12 11

Данные в таблицах. На пересечении столбца конкретного пункта доставки со строкой склада находится информация о расстоянии между этим пунктом доставки и складом. Например, расстояние между 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

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

Решение транспортной задачи в Excel обновлено: Октябрь 23, 2018 автором: Анатолий Бузов

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *