В этом материале попробуем разобраться, как решить транспортную задачу в Excel. Среда решения – Excel. Данный материал подходит для версий программы: 2007, 2010, 2013, 2016.
Постановка задачи и подготовка таблиц
Цель задачи сводится к математическому моделированию минимизации грузопотоков. Довольно часто студенты пишут рефераты на тему поиска решения транспортной задачи. Этот пример можно взять за основу реферата. Рассмотрим решение на конкретном примере.
Задача
В хозяйстве имеются 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
Подготовим таблицы для решения задачи.
Значения ячеек в столбце B с третьей по седьмую определяют сумму значения соответствующих строк со столбца C до столбца F.
Например, значение ячейки B3=СУММ(C4:F4)
Аналогично значения в восьмой строке, складываются из суммы соответствующих столбцов. Далее создадим еще одну таблицу.
В строке 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 -> Надстройки — > Поиск решения.
После выбора данной процедуры на вкладке Данные откроется диалоговое окно.
Выберем целевую ячейку $B$16, установим ее равной минимальному значению, что бы минимизировать значение конечной ячейки, путем изменения влияющих ячеек, изменяя ячейки, выберем диапазон с единицами $C$3:$F$7.
Если запустить процесс, то мы получим параметры равные нулям. Для получения необходимых значений установим некоторые ограничения:
- $B$3:$B$7 <= $B$11:$B$15
- $C$3:$F$7 >= 0
- $C$8:$F$8 >= $C$10:$F$10
После всех установок нажмем «Выполнить» и получаем результат.