Задание №6. Решение транспортной задачи с помощью MS Excel.

Задание: Решить транспортную задачу.

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

Таблица 1 - Исходные данные для «0» варианта: Расстояния между складами и пунктами доставки.

Таблица 2 - Исходные данные для «0» варианта. Наличие минеральных удобрений на складах.

Таблица 3 - Исходные данные для «0» варианта. Потребность в минеральных удобрений на складах.

Методические указания для решения транспортной задачи по «0» варианту.

Транспортная задача является классической задачей исследования операций[О1]. Множество задач распределения ресурсов сводится именно к этой задаче.

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

Рис. 1 Изменяемые ячейки.

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

Например, значение ячейки В4=СУММ(С4:С8)

Значения ячеек по 9 строке по столбцам от С до F определяются суммированием данных ячеек соответствующих столбцов с 4 по 8 строки.

Например, значение ячейки С9=СУММ(С4:С8)

Каждое значение в ячейках на пересечении столбца конкретного пункта доставки и строки склада означает количество тонн, поставляемых с этого склада в данный пункт потребления. В нижней строке (9 строка) суммируется общее количество минеральных удобрений, поставляемых в определенный пункт доставки, а во втором столбце (столбец В) суммируется количество доставленного с конкретного склада минеральных удобрений.

Теперь, используя исходные данные, введем на этом же листе требуемые объемы поставок на расстояния между складами и пунктами доставки.

Рис. 2 Исходная информация.

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

С16=С4*С11+С5*С12+С6*С13+С7*С14+С8*С15

либо можно использовать функцию СУММПРОИЗВ

С16=СУММПРОИЗВ(С4:С8;С11:С15)

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

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

Таким образом информация примет следующий вид (рис.3)

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

Для решения транспортной задачи воспользуемся процедурой Поиск решения, которая находится в меню Сервис [О2].

После выбора данной команды появится диалоговое окно (рис.4).

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

Поскольку в качестве критерия оптимизации нами выбрана минимизация грузооборота, в поле Установить целевую ячейку введите ссылку на ячейку, содержащую формулу расчета общего объема грузооборота минеральных удобрений. В нашем случае это ячейка $B$16. Чтобы минимизировать значение конечной ячейки переключатель установите в положение минимальному значению. В поле Изменяя ячейки введите, разделяя их запятыми, либо, если ячейки находятся рядом, указывая первую и последнюю ячейку, разделяя их двоеточием. В группе поле Ограничения нажмите кнопку Добавить. Появится диалог Добавление ограничений (рис.5)

Рис.5 Диалоговое окно Добавление ограничения

После ввода одного ограничения следует нажать кнопку Добавить и ввести следующее. По окончании ввода всех ограничений нажмите на кнопку ОК.

Рис.6 Диалоговое окно Поиск решения с заполненными полями

Для изменения и удаления ограничений в списке Ограничения диалогового окна Поиск решения укажите ограничение, которое требуется изменить или удалить. Выберите команду Изменить и внести изменения либо нажмите кнопку Удалить.

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

После нахождения решения появится диалог Результаты поиска решения. (рис.7)

Рис.7 Диалоговое окно Результаты поиска решения

Нажав кнопку ОК, вы занесете вариант решения на рабочий лист (рис.8)

Рис.8 Решенная транспортная задача

Ответ: получено решение транспортной задачи с помощью MS Excel. Минимальный грузооборот перевозок при соблюдении всех условий составил 3710 т.-км.

[О1]Для базового уровня СПО не корректно использование таких словосочетаний

[О2]После активации данной функции в «Надстройках»