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

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

 

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

 

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

Для отображения формул необходимо на вкладке "Формулы" в группе "Зависимости формул" выбрать "Показать формулы" либо горячее сочетание клавиш "Ctrl+` (тильда)".

 

 

 

Дальше выбираем команду "Поиск решения" на вкладке "Данные" (Файл – Параметры – Надстройки – Управление – Поиск решений).

 

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

 

 

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

 

Постановка задачи

 

Есть запасы однотипной продукции у поставщиков A1, A2, A3, A4.

Существует потребность в этой продукции B1, B2, B3

Стоимость доставки единицы продукции от поставщиков к потребителям представлена в таблице.

Поставщики	Потребители	Запасы
Потребность

Необходимо составить такой план перевозок, который бы удовлетворил все потребности и имел минимальную стоимость.

 

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

 

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

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

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

 

Поставщики	Потребители	Запасы
(ф)
Потребность

 

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

 

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

 

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

=СУММПРОИЗВ(B3:D7;B13:D17)-СУММПРОИЗВ(B7:D7;B17:D17)

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

 

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

 

 

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

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

 

 

 

 

 

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

 

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

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

Постановка задачи

 

Есть запасы однотипной продукции у поставщиков A1, A2, A3, A4.

Существует потребность в этой продукции B1, B2, B3

Стоимость доставки единицы продукции от поставщиков к потребителям представлена в таблице.

Поставщики	Потребители	Запасы
Потребность

 

Необходимо составить такой план перевозок, который бы удовлетворил все потребности и имел минимальную стоимость.

 

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

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

Допустим, у первого поставщика есть обязательство перед потребителем B3 в обязательной поставке 200 единиц товара, а у второго – перед потребителем B2, в обязательной поставке 80 единиц товара.

Записанное условие в таблицах MS Excel выглядит следующим образом:

 

 

 

 

Как видим, минимальная стоимость перевозки несколько больше предыдущей задачи (1690 против 1280), поскольку контракты на обязательную поставку снизили общую эффективность перевозки.