Решение транспортной задачи в Excel с примером и описанием

Виды транспортных задач

Условия и ограничения транспортной задачи достаточно обширны и разнообразны. Поэтому для ее решения разработаны специальные методы. С помощью некоторых из них можно найти эталонное решение. А затем улучшить его и получить лучший вариант.

Условия транспортной задачи можно представить двумя способами:

• в виде схемы;
• в виде матрицы.

В процессе решения могут быть ограничения (или задача решается без них).

В зависимости от характера условий различают следующие виды транспортных задач:

• открытые открытые транспортные задачи (инвентаризация товаров у поставщика не соответствует потребности в товарах у потребителя);
• закрытые (суммарный запас продукции у поставщиков и потребителей одинаков).

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

Транспортная задача: описание

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

Транспортные задачи бывают двух типов:

• Закрытый — общее предложение продавца равно общему спросу.
• открытый — спрос и предложение не равны. Чтобы решить такую ​​задачу, нужно сначала привести ее к закрытому типу. В этом случае добавляется условный покупатель или продавец с отсутствующим количеством спроса или предложения. Также в таблице себестоимости должна быть сделана соответствующая запись (с нулевыми значениями).

Инструменты для решения транспортной задачи в Эксель

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

1. Перейдите на вкладку «Файл».
2. Нажмите на подраздел «Настройки».
3. В новом окне переходим к надписи «Дополнения».
4. В блоке «Управление», который находится внизу открывшегося окна, в выпадающем списке остановите выбор на пункте «Надстройки Excel». Нажимаем на кнопку «Перейти к…».
5. Откроется окно активации надстройки. Установите флажок рядом с «Поиск решения». Нажмите на кнопку «ОК».
6. В результате этих действий во вкладке «Данные» блока настроек «Анализ» на ленте появится кнопка «Поиск решения». Он нам понадобится, когда мы будем искать решение транспортной проблемы.

Читайте также: Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий — statanaliz

Пример решения транспортной задачи в Excel

Компании А1, А2, А3 и А4 производят однородную продукцию А1, А2, А3 и А4 соответственно. В условных единицах – 246, 186, 196 и 197. Далее товары поступают по пяти адресам: Б1, Б2, Б3, Б4 и Б5. Это потребители продукта. Они готовы принять ежедневно 136, 171, 71, 261 и 186 единиц товара.

Стоимость перевозки единицы продукции с учетом удаленности от места назначения:

Задача: минимизировать транспортные затраты на перевозку продукции.

1. Проверим сбалансированность модели транспортной задачи. Для этого весь объем произведенной продукции сравнивается с общим объемом спроса на продукцию: 246 + 186 + 196 + 197 = 136 + 171 + 71 + 261 + 186. Вывод – модель сбалансирована.
2. Сформулируем ограничения: Объем перевозимой продукции не может быть отрицательным и все товары должны быть доставлены по назначению (поскольку модель сбалансирована).
3. Введем стоимость перевозки единицы продукции в рабочие ячейки Excel.
4. Введем формулы для расчета совокупного спроса на товары. Это будет первое ограничение.
5. Введем формулы для расчета общего объема производства. Это будет вторым ограничением.
6. Введем известные значения спроса на товары и объема производства.
7. Введем формулу целевой функции СУММПРОИЗВ(B3:F6; B9:F12), где первая матрица (B3:F6) – стоимость транспортной единицы товара. Вторые (B9:F12) — желаемые значения транспортных расходов.
8. Вызываем команду «Поиск решения» на вкладке «Данные» (если этого инструмента нет, то его необходимо подключить в настройках Excel, а как это сделать описано в статье: расширенные возможности финансового анализа). Заполните диалоговое окно. В графе «Введите целевую ячейку» — ссылка на целевую функцию. Установите флажок «Равно минимальному значению». В поле «Изменить ячейки» — ряд искомых критериев. В поле «Ограничения»: искомый массив >=0, целое число; «ограничение 1» = объем спроса; «ограничение 2» = объем производства.
9. Нажмите «Выполнить». Команда выберет оптимальные переменные при заданных ограничениях.

Вот так выглядит «сырая» версия инструмента. Экспериментируя с полученными данными, мы находим подходящие значения.

Условия задачи

У нас есть 5 поставщиков и 6 покупателей. Объемы производства данных поставщиков составляют 48, 65, 51, 61, 53 единицы. Потребность покупателей: 43, 47, 42, 46, 41, 59 ед. Таким образом, общее предложение равно спросу, т е имеем замкнутую транспортную задачу.

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

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

Перед нами стоит задача в указанных выше условиях свести транспортные расходы к минимуму.

1. Чтобы решить проблему, мы строим таблицу с точно таким же количеством ячеек, что и матрица затрат, описанная выше.
2. Выберите любую пустую ячейку на листе. Щелкните значок «Вставить функцию» слева от строки формул.
3. Откроется мастер функций. В списке, который он предлагает, нам следует искать функцию СУММПРОИЗВ. Выберите его и нажмите на кнопку «ОК».
4. Откроется окно для ввода аргументов функции СУММПРОИЗВ. В качестве первого аргумента введем диапазон ячеек матрицы стоимости. Для этого достаточно выделить данные ячейки курсором. Вторым аргументом будет диапазон ячеек в таблице, подготовленной для расчетов. Затем нажмите на кнопку «ОК».
5. Нажимаем на ячейку, которая находится левее верхней левой ячейки в таблице для расчетов. Как и в прошлый раз вызываем мастер функций, открываем в нем аргументы функции СУММ. Нажав на поле первого аргумента, мы выделяем для расчетов всю верхнюю строку ячеек таблицы. После того, как их координаты будут введены в соответствующее поле, нажмите кнопку «ОК».
6. Остаемся в правом нижнем углу ячейки с функцией СУММ. Появится маркер заполнения. Щелкните левой кнопкой мыши и перетащите маркер заполнения вниз в конец таблицы для расчета. Итак, мы скопировали формулу.
7. Нажмите на ячейку над ячейкой в ​​левом верхнем углу таблицы для расчетов. Как и прежде, мы вызываем функцию СУММ, но на этот раз в качестве аргумента используем первый столбец таблицы расчета. Нажмите на кнопку «ОК».
8. Копируем формулу на всю строку с маркером заполнения.
9. Перейдите на вкладку «Данные». Там в блоке инструментов «Анализ» нажмите кнопку «Поиск решения».
10. Откроются параметры поиска решения. В поле «Оптимизировать целевую функцию» введите ячейку, содержащую функцию СУММПРОИЗВ. В блоке «Кому» установите значение «Минимум». В поле «Изменить ячейки с переменными» укажите весь диапазон таблицы для расчета. В блоке настроек «Согласно ограничениям» нажмите кнопку «Добавить», чтобы добавить несколько важных ограничений.
11. Откроется окно Добавить ограничение. В первую очередь нужно добавить условие, что сумма данных в строках таблицы для расчетов должна быть равна сумме данных в строках таблицы с условием. В поле «Ссылка на ячейки» введите диапазон суммы в строках таблицы расчета. Затем ставим знак равенства (=). В поле «Ограничение» укажите диапазон сумм в строках таблицы с условием. Затем нажмите на кнопку «ОК».
12. Таким же образом добавляем условие, что столбцы в двух таблицах должны быть равны между собой. Добавляем ограничение, что сумма площадей всех ячеек таблицы для расчета должна быть больше или равна 0, а также условие, что она должна быть целым числом. Общий обзор ограничений должен быть таким, как показано на рисунке ниже. Обязательно убедитесь, что рядом с пунктом «Сделать неотрицательными переменные без ограничений» стоит галочка и в качестве метода решения выбран «Поиск решения нелинейных задач методом ОПГ». После того, как все настройки указаны, нажмите кнопку «Найти решение».
13. После этого происходит расчет. Данные отображаются в ячейках таблицы для расчета. Откроется окно результатов поиска решения. Если вас устраивают результаты, нажмите кнопку «ОК».

Как видите, решение транспортной задачи в Excel сводится к правильному формированию входных данных. Сами расчеты выполняются программой вместо пользователя.

Решение открытой транспортной задачи в Excel

При этом типе возможны два сценария:

• общий объем производства превышает общий спрос на продукт;
• общий спрос превышает количество запасов.

Открытая транспортная задача приведена к закрытому типу. В первом случае вводится фиктивный потребитель. Его потребности равны разнице между общим объемом производства и суммой существующих потребностей.

Во втором случае вводится фиктивный поставщик. Объем производства равен разнице между общим спросом и общими запасами.

Единицей отгрузки для фиктивного участника является 0.

После завершения всех преобразований транспортная задача закрывается и решается обычным образом.

Шаг 1

Дублируем нашу таблицу в Excel.

Шаг 2

Рисуем еще одну таблицу.

Диапазон ячеек D12 — F15 заполняется единицами. Затем мы изменим эти значения, чтобы найти самый дешевый вариант транспорта. В области H12 — H15 должна быть сумма трех единиц таблицы в строке D12 — F12, а в области D17 — F17 — сумма четырех единиц в столбце. Затем напротив каждой строки и каждого столбца

Шаг 3

Рисуем третью таблицу, которая будет умножать соответствующие ячейки в первых двух таблицах.

Для этого выделите диапазон 3 х 4 ячейки, нажмите кнопку «=», выберите диапазон D3-F6, нажмите «*» на клавиатуре, выберите D12 — F15 и зажмите комбинацию клавиш Ctrl+Shift+Enter . Вот и все, вы умножили значения.

Шаг 4

Теперь суммируем все значения последней таблицы. Для этого просто выберите произвольную свободную ячейку в MS Excel. Введите в нем «=SUM(» и выберите третью таблицу. Нажмите Enter.

Шаг 5

Перейдите на вкладку «Данные» и найдите там «Поиск решения».

Нажимаем эту кнопку. Далее делаем все как показано на рисунке.

Я описываю все окно сверху вниз. Выберите целевую ячейку, которую мы создали на четвертом шаге нашего решения. Затем выберите минимум. В поле «Изменить ячейки переменных» выбираем область, где мы размещаем блоки. Мы устанавливаем границы. Значения, которые будут на месте единиц, должны быть больше нуля и целых чисел, а требования не должны превышать доли. Нажмите «Найти решение».

Получаем следующий результат.

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