Решение транспортной задачи в 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), поскольку контракты на обязательную поставку снизили общую эффективность перевозки.