Решение транспортных задач ЛП в Excel
На этой странице разберем подробные решения транспортной задачи (алгоритм и примеры разных типов) с использованием пакета электронных таблиц MS Excel (надстройка Поиск решения).
Как решить транспортную задачу в Excel
Ручное решение транспортной задачи занимает очень много времени и сил (скажем, даже для учебной задачи типа 3*5 решение может составлять от 4 до 10 страниц расчетов!). Тогда как решение в Эксель для задачи размерности как 3*3, так и 5*7 потребует буквально 10-15 минут и немного опыта (правда, если уже составлена математическая модель).
Использовать можно любую версию программы - 2003, 2007, 2010 и так далее, главное, включить использование надстройки Поиск решения (интерфейс может немного отличаться в разных версиях).
Алгоритм решения ТЗ в Эксель
- Составить математическую модель транспортной задачи - то есть получить таблицу со стоимостью перевозок, запасами груза у поставщиков и потребностями потребителей (и, возможно, дополнительными ограничениями).
- Если задача открытая (несбалансированная), то добавить потребителя или поставщика с нулевыми тарифами перевозки.
- Внести на лист таблицы Excel данную модель в виде матрицы тарифов (затрат).
- Создать рядом на листе еще одну таблицу, где будут выводиться искомые перевозки (такой же размерности, что и таблица тарифов). Просуммировать перевозки по строкам и столбцам (чтобы сравнивать с аналогичными ячейками - предельными ограничениями задачи - запасами поставщиков и потребностями потребителей).
- Ввести в ячейку формулу, подсчитывающую суммарную стоимость перевозок (это число мы должны минимизировать по смыслу транспортной задачи)
В режиме формул таблица будет выглядеть так:
- Запустить надстройку Поиск решения и указать а) ячейку, которую мы минимизируем, б) все ограничения на запасы поставщиков и потребности потребителей, в) дополнительные ограничения (иногда бывают запреты перевозок или требования по минимальному объему груза между определенными пунктами, как в данном случае).
- Получить решение транспортной задачи: в целевой ячейке вы увидите минимальную стоимость перевозок (в примере 435), а в таблице перевозок - искомые значения объема перевозимого груза (см. желтые ячейки).
- Проанализировать решение, если требуется и записать более подробно, например
Минимальные затраты на перевозку составят 435. План перевозок:
Из 1 карьера 10 тонн везем на 1-й участок, 15 тонн на 3-й.
Из 2 карьера 20 тонн везем на 1-й участок.
Из 3 карьера 20 тонн везем на 3-й.
Из 4 карьера 10 тонн везем на 1-й участок, 20 тонн на 2-й, 5 тонн на 3-й.
Транспортные задачи: примеры в Excel
Задача 1. Решить транспортную задачу вручную (методом потенциалов) и в программе Эксель.
Задача 2. Исходные данные задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху - мощности потребителей.
Сформулировать экономико-математическую модель исходной транспортной задачи, найти оптимальный план закрепления поставщиков за потребителями, установить единственность или не единственность оптимального плана, используя Поиск решений.
Задача 3. Имеется 3 нефтеперерабатывающих завода, 4 спиртовых завода, 3 завода по производству синтетического каучука.
Схема кооперационных связей (см. файл).
Далее приведены производственные показатели предприятий.
Также заданы расстояния между предприятиями.
Необходимо найти решение транспортной задачи с ориентацией на спрос СК и минимизацией транспортных суммарных затрат.
Задача 4. Используя метод потенциалов, решить транспортную задачу. Выполнить проверку, используя табличный редактор Microsoft Excel Компания владеет тремя заводами А1, А2, А3. Соответствующие объемы производства равны 600, 300 и 330 единиц продукции. Компания обязалась поставить в города В1, В2, В3 и В4 соответственно 350, 350, 230 и 300 единиц. При заданных в таблице стоимостях перевозок единицы продукции составьте план ее распределения, чтобы общая стоимость перевозок была наименьшей.
Задача 5. Свести задачу к виду ТЗ и решить с помощью надстройки «Поиск решения»
Четыре ремонтные мастерские могут за год отремонтировать соответственно 400, 500, 450 и 550 машин при себестоимости ремонта одной машины в 500, 700, 650 и 600 рублей. Планируется годовая потребность в ремонте пяти автобаз: 550, 350, 300, 375 и 400 машин.
Ремонт машин с 1 автобазы должен осуществляться в 100% случаев силами ремонтных мастерских.
На 4 АБ возможно самостоятельное проведение ремонтных работ (бесплатное) в объеме, не превышающем 8% от планируемой годовой потребности этой мастерской. Платное (на стороне) - совсем не возможно.
Вторая, третья и пятая АБ могут «ремонтироваться» на стороне, стоимость ремонта +трансп.расходы каждой машины в таком случае составит 695 руб.
Дана матрица, характеризующая транспортные расходы на доставку машины с j-й автобазы в i-ю ремонтную мастерскую. Определить минимальную годовую потребность в кредитах на выполнение указанного объема работ по всем автобазам
