Решение с использованием MS EXCEL

Лабораторная работа № ИII-6. Транспортная задача

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

Дано: 3 поставщика обеспечивают сырьем 4 потребителей. Предложения поставщиков равны 160, 140 и 170 т, соответственно. Спрос на сырье составляет 120, 50, 190 и 110 т, соответственно. На каждое предприятие сырье может завозиться от любого поставщика. Тарифы перевозок известны и задаются в таблице (в у.е./т):

	Потребитель 1	Потребитель 2	Потребитель 3	Потребитель 4
Поставщик 1
Поставщик 2
Поставщик 3

Требуется составить план перевозок, при котором общая стоимость перевозок минимальна.

Решение с использованием MS EXCEL

1. Откроем новый рабочий лист и внесем на него исходные данные так, как это показано на рис. 1.

Рис.1

Проверим, сбалансирована ли задача – рассчитаем сумму спроса в ячейке F7 и сумму предложения в ячейке F8. Так как эти суммы равны, задача сбалансирована и спрос равен предложению.

2. Подготовим таблицу для решения задачи, зададим целевую функцию и ограничения (см. рис.2).

· В диапазоне (B10:E12) будет рассчитано количество перевозимого сырья. До начала расчетов в эти ячейки записываем значения 0.

В ячейку С13 записываем формулу для вычисления значения общей стоимости всех перевозок:

=СУММПРОИЗВ(B4:E6; B10:E12).

· В ячейки B15-B17 записываем формулы для вычисления левых частей ограничений. Для вычисления суммы значений в ячейках используем функцию СУММ:

=СУММ(B10:E10) (количество товара, поставленного 1-м поставщиком)

=СУММ(B11:E11) (количество товара, поставленного 2-м поставщиком)

=СУММ(B12:E12) (количество товара, поставленного 3-м поставщиком)

В ячейки С15-С17 записываем знак равенства (=). В ячейках D15-D17 располагаем правые части ограничений – ссылки на ячейки, в которых находятся данные о предложении поставщиков, с F4 по F6.

· В ячейки B18-B21 записываем формулы для вычисления левых частей ограничений. Для вычисления суммы значений в ячейках используем функцию СУММ:

=СУММ(B10:B12) (количество товара, поставленного 1-му потребителю)

=СУММ(C10:C12) (количество товара, поставленного 2-му потребителю)

=СУММ(D10:D12) (количество товара, поставленного 3-му потребителю)

=СУММ(E10:E12) (количество товара, поставленного 4-му потребителю)

В ячейки С18-С21 записываем знак равенства (=). В ячейках D18-D21 располагаем правые части ограничений – ссылки на ячейки, в которых находятся данные о спросе, с B7 по E7.

· Ограничений (2.4) в явном виде задавать не будем.

Рис.2

3. Для выполнения расчета используем подпрограмму Поиск решения. В окне Поиск решения зададим (рис.3):

· В поле Установить целевую ячейку – адрес ячейки, в которой находится формула для вычисления общей стоимости - C13.

· Переключатель - в положение Минимальному значению, поскольку необходимо найти минимум транспортных затрат.

· В поле Изменяя ячейки - адрес диапазона ячеек, в котором находится план перевозок – B10:E12.

· В окно Ограничения по одному добавим 7 ограничений.

· Чтобы указать, что значения переменных не могут быть отрицательными, нажмем кнопку Параметры. В окне Параметры поиска решения установим флажок в поле Неотрицательные значения. Флажок в поле Линейная модель также должен быть установлен.

Рис.3

Вернемся в окно Поиск решения и нажмем кнопку Выполнить.

4. Проанализируем полученное решение (рис.4).

· Минимальная стоимость перевозок составляет 1330 (значение в ячейке С13).

· Оптимальный план перевозок содержится в диапазоне B10:E12. Если в соответствующей ячейке находится 0, значит, везти сырье от данного поставщика к данному потребителю невыгодно. Так, 0 в ячейке В10 показывает, что Поставщик1 не поставляет сырье Потребителю1.