Решение транспортных задач в MS Excel 2013

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

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

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

$$C=\begin{pmatrix} 2 & 6 & 3 & 5\\ 9 & 4 & 2 & 7\\ 3 & 2 & 5 & 4 \end{pmatrix}$$

Ежедневно каждый из заводов может изготовить 100, 400 и 200 условных единиц кирпича (так называемые мощности поставщиков). Потребности в кирпиче на каждом из строящихся объектов ежедневно составляют 250, 300, 100 и 200 условных единиц (спрос потребителей). Т.к. суммарная мощность поставщиков не совпадает с суммарным спросом потребителей:

$$100+400+200=700\neq 850=250+300+100+200,$$

то имеем дело с открытой транспортной задачей. Т.к. спрос превышает предложение, то для обеспечения баланса необходимо ввести условного поставщика с мощностью в 150 у.е. и нулевыми транспортными издержками. В противном случае нужно было бы ввести условного потребителя. Для транспортных задач закрытого типа (есть баланс) ничего вводить не нужно.

Транспортная задача заключается в распределении груза для каждой пары «поставщик – потребитель» таким образом, чтобы спрос был удовлетворен и суммарные транспортные издержки были бы минимальными.

Введем исходные данные транспортной задачи в MS Excel.

Рабочий лист EXCEL с введенными исходными данными

Исходные данные транспортной задачи в MS Excel

Далее обращаемся к надстройке Поиск решения.

Подключение надстройки Поиск решения в Excel 2013

ФайлПараметры Надстройки – в выпадающем списке выбрать Надстройки Excel и нажать на кнопку Перейти… – поставить галочку напротив Поиск решения и нажать OK.

Надстройка Поиск решения в Excel


Вызов надстройки Поиск решения

Данные — Анализ — Поиск решения

Поиск решения Excel 2013

Заполняем диалоговое окно Параметры поиска решения

Диалоговое окно Параметры поиска решения

Оптимизировать целевую функцию: $B$17 (суммарные транспортные издержки)

До: Минимуму (суммарные транспортные издержки должны быть минимальными)

Изменяя ячейки переменных: $C$13:$F$16 (распределение грузов)

В соответствии с ограничениями (нажимаем добавить):

  • Соответствующие предложения поставщиков из 1 и 2 таблиц равны между собой

Поиск решения. Ограничение 1

  • Соответствующий спрос потребителей из 1 и 2 таблиц равны между собой

Поиск решения. Ограничение 2

  • Распределенные грузы должны быть целыми и неотрицательными

Поиск решения. Ограничение 3Поиск решения. Ограничение 4

Выберите метод решения: (Поиск решения нелинейных задач методом ОПГ, Поиск решения лин. задач симплекс-методом, Эволюционный поиск решения). Выбираем Поиск решения лин. задач симплекс-методомВ итоге получится следующее:

Параметры поиска решения. Симплекс-метод

В диалоговом окне Параметры поиска решения нажимаем на кнопку Найти решение, отмечаем сохранить найденное решение, нажимаем ОК и получаем оптимальное распределение грузов (диапазоне ячеек C13:F16) с минимальными суммарными транспортными издержками (ячейка B17).

Оптимальное решение

Поиск решения. Оптимальное решение

С уважением, Сергей Бондаренко.

Понравилось? Поделись с друзьями!