Одной из самых распространенных проблем во всех областях экономики является транспортировка груза или товара с минимальными материальными и временными затратами. Так как огромное количество возможных вариантов перевозок затрудняет получение самого экономичного плана эмпирическим или экспертным путем, то появилась необходимость разработки специальной теории, позволяющей быстро решать подобные задачи с помощью алгоритмизации. Применение математических методов в планировании перевозок дает большой экономический эффект. Такие транспортные задачи очень просто можно решать при помощи 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 с введенными исходными данными
Далее обращаемся к надстройке Поиск решения.
Подключение надстройки Поиск решения в Excel 2013
Файл – Параметры – Надстройки – в выпадающем списке выбрать Надстройки Excel и нажать на кнопку Перейти… – поставить галочку напротив Поиск решения и нажать OK.
Вызов надстройки Поиск решения
Данные – Анализ – Поиск решения
Заполняем диалоговое окно Параметры поиска решения
Диалоговое окно Параметры поиска решения
Оптимизировать целевую функцию: $B$17 (суммарные транспортные издержки)
До: Минимуму (суммарные транспортные издержки должны быть минимальными)
Изменяя ячейки переменных: $C$13:$F$16 (распределение грузов)
В соответствии с ограничениями (нажимаем добавить):
- Соответствующие предложения поставщиков из 1 и 2 таблиц равны между собой
- Соответствующий спрос потребителей из 1 и 2 таблиц равны между собой
- Распределенные грузы должны быть целыми и неотрицательными
Выберите метод решения: (Поиск решения нелинейных задач методом ОПГ, Поиск решения лин. задач симплекс-методом, Эволюционный поиск решения). Выбираем Поиск решения лин. задач симплекс-методомВ итоге получится следующее:
В диалоговом окне Параметры поиска решения нажимаем на кнопку Найти решение, отмечаем сохранить найденное решение, нажимаем ОК и получаем оптимальное распределение грузов (диапазоне ячеек C13:F16) с минимальными суммарными транспортными издержками (ячейка B17).
Оптимальное решение
