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

Практическая работа: «Решение транспортной задачи в Excel»

Цель: Научиться решать транспортную задачу с использованием метода «Поиск решения в MS Excel».

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

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

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

Имеются следующие исходные данные.

Наличие минеральных удобрений на складах.
 

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

Расстояния между складами и пунктами доставки.
 

На пересечении столбца конкретного пункта доставки со строкой склада находится информация о стоимости доставки от пункта до склада (они на примере выделены желтыми ячейками).

Решение:

Заполняем таблицу в Excel состоящую из 4 строк и 7 колонок

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

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

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

Ниже создаем такую же таблицу, но с пустыми значениями

Теперь, после того, как мы ознакомились с таблицами с исходными данными, можем воспользоваться следующим алгоритмом, чтобы решить эту задачу:

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

У нас появляется окно, в котором нам нужно выбрать категорию «Математические». А какая функция нас интересует? Та, которая выделена на этом скриншоте. Функция СУММИПРОИЗВ умножает диапазоны или массивы между собой и суммирует их. Как раз то, что нам нужно. После этого нажимаем клавишу ОК.

Далее на экране появится окно, в котором необходимо указать параметры функции. Они следующие:

Массив 1. Это первый аргумент, в котором записываем тот диапазон, который выделен жёлтым цветом. Задать параметры функции можно как используя клавиатуру, так и выделив соответствующую область с помощью левой кнопки мыши.

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

Подтверждаем свое действие нажатием клавиши ОК.

Считаем сумму значений по строкам в новосозданной таблице.

То же самое сделаем по столбцам

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

Результатом будет ноль.

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

Теперь перед нашим взором появилось окошко, через которое можно настроить параметры нашего дополнения. Давайте каждый из этих параметров разберем:

Оптимизировать целевую функцию. Здесь нам нужно выбрать ячейку, содержащую функцию СУММПРОИЗВ. Видим, что эта опция дает возможность выбрать функцию, для которой будет осуществляться поиск решения.

До. Здесь выставляем опцию «Минимум».

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

В соответствии с ограничениями. Здесь нам нужно добавить ограничения, нажав кнопку «Добавить».

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

Задача ограничений осуществляется следующим образом:

Ссылка на ячейки. Здесь заносим диапазон таблицы для расчётов.

Условия. Это математическая операция на предмет соответствия которой проверяется диапазон, заданный в первом поле ввода.

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

После того, как все действия будут выполнены, нажимаем кнопку ОК, тем самым подтверждая наши действия.

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

Следующий этап – задание условий. Нам необходимо установить следующие критерии для суммы ячеек в таблице – больше или равно нулю, целое число. В результате у нас появляется такой перечень условий, при которых задача решается. Здесь нужно убедиться, что поставлен флажок возле опции «Сделать переменные без ограничений неотрицательными». Также в нашей ситуации требуется, чтобы был выбран метод решения задачи – «Поиск решения нлинейных задач симплекс-методом». Теперь можно смело говорить, что настройка осуществлена. Поэтому осталось только выполнить расчеты. Для этого нажимаем на кнопку «Найти решение».

После этого все данные будут рассчитаны автоматически, а потом Эксель покажет окно с результатами. Оно необходимо для того, чтобы перепроверить работу компьютера, поскольку возможны ошибки, если условия ранее были заданы неверно. Если все правильно, то нажимаем кнопку «ОК» и видим готовую таблицу.

Решить самостоятельно:

1.                  Для строительства четырех объектов используется кирпич, изготавливаемый на трех заводах. Транспортные издержки задаются матрицей C:

Ежедневно каждый из заводов может изготовить 100, 400 и 200 условных единиц кирпича (так называемые мощности поставщиков). Потребности в кирпиче на каждом из строящихся объектов ежедневно составляют 250, 300, 100 и 200 условных единиц (спрос потребителей).

2.                  Необходимо составить план транспортирования строительных материалов, минимизирующей затраты на перевозку, используя EXCEL.  После получения и обработки  результатов сформулировать выводы.

Исходные данные для расчета:

Таблица 16

3.       

4.