Решение транспортной задачи в MS Excel (фиктивный поставщик или потребитель)

Что такое транспортная задача и самый простой ее подвид, когда запасы соответствуют потребностям нами было рассмотрено ранее.

Рассмотрим решение транспортной задачи, когда есть дисбаланс между потребностями и запасами.

Поставщик

Потребитель

Запас

В1 В2 В2
А1 6 5 2 250
А2 3 7 4 100
А3 7 8 1 80
А4 2 2 3 80

Потребность

150 150 250

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

Однако, можно цену поставок оставить равной нулевой, в этом случае, вычитать из суммы перевозок ничего не придется поскольку умножение на ноль дает ноль.

Для последующего решения задачи выберем вариант с ненулевой ценой перевозки.

Поставщик

Потребитель

Запас

В1 В2 В2
А1 6 5 2 250
А2 3 7 4 100
А3 7 8 1 80
А4 2 2 3 80
А5(ф) 100 100 100 40

Потребность

150 150 250

Решение задачи.

Сбалансировав саму задачи решаем ее стандартным способом. Для начал составим две таблицы: одна с данными, вторая – без.

Для решения транспортной задачи потребуются функции: СУММПРОИЗВСУММ и надстройка "Поиск решения".

На рисунке выше, обратите внимание, курсор находится ф ячейке с функцией, которую следует минимизировать. В самой функции уже отнимается цена фиктивных перевозок. Цену перевозок можно отнять отдельно, важно просто не забыть это сделать.

Дальше выбираем команду "Поиск решения" на вкладке "Данные".

Решение поставленной задачи представлено ниже.

Как видно из решения все фиктивные поставки пришлись на потребителя В1, именно у него будет недопоставка в 40 единиц товара.

Если задача будет несбалансированная по потребителям, тогда вводится фиктивный потребитель, чтобы сбалансировать потребности и запасы и задача решается методом аналогичным описанному выше.

Подробнее о решении несбалансированной транспортной задачи на видео.