Использование MS Excel для решения транспортной задачи

ФГБОУ ВО Башкирский государственный педагогический университет им. М.Акмуллы

Отчет по лабораторной работе №3-4

по дисциплине

“Исследование операций”

на тему

“Использование MS Excel для решения транспортной задачи”

Вариант 9

Выполнили:

студенты гр. ЗПИС 21-18

Гумеров И. И.

Колесниченко А. Р.

Проверил:

доцент кафедры ПИ

Дяминова Э.И.

Уфа 2020

[pic 1]

Решение:

1. Занесем исходные данные в MS Excel (Рис. 1).

[pic 2]

Рис. 1 - Исходные данные транспортной задачи в MS Excel

В данном случае модель закрытая (170 + 150 + 100 = 150 + 70 + 200 = 420).

1. Обозначим Xij – величина поставки i-го поставщика j-му потребителю (i = 1, 2, 3; j = 1, 2, 3). Выделим под переменные Xij ячейки B11:D13 (Рис. 2).

[pic 3]

Рис. 2 -  Подготовка к решению транспортной задачи

1. В ячейках B14:D14 будем рассчитывать сумму поставок каждому потребителю. Для этого воспользуемся функцией СУММ. Аналогично в ячейках E11:E13 рассчитаем суммарные поставки каждого поставщика.

1. В ячейку E14 запишем целевую функцию – суммарные затраты на осуществление всех поставок. Эта функция вычисляется как сумма произведения величин поставок Xij на соответствующую им стоимость. Для этого воспользуемся функцией СУММПРОИЗВ.

1. Выделяем ячейку с формулой целевой функции. Запускаем надстройку «Поиск решения». В диалоговом окне в поле ввода «Установить целевую» ячейку уже содержится $E$14. Установим переключатель «Равной минимальному значению». В поле ввода «Изменяя ячейки» нужно указать величины поставок Xij, т.е. выбрать ячейки B11:D13 (Рис. 3).

[pic 4]

Рис. 3 - Окно “Поиск решения”

1. Щелкнем кнопку «Добавить».  Появится диалоговое окно «Добавление ограничения» (Рис. 4). В поле ввода «Ссылка на ячейку» укажем суммарные поставки каждому потребителю (диапазон B14:D14). Правее в выпадающем списке с условными операторами выберем « = ». В поле ввода «Ограничение» укажем суммарный спрос каждого потребителя, который был задан по условию задачи (диапазон B7:D7). Щелкнем кнопку «Добавить». Аналогично введем ограничения-равенства на суммарные поставки каждого поставщика (они должны быть равны заданной по условию задачи мощности). После нажатия кнопки ОК в поле «Ограничения» можно увидеть введенные вами условия (Рис. 3).

 [pic 5]

Рис. 4 - Ввод ограничений

1. Щелкнем кнопку «Параметры». (Рис. 5).
2. Нажимаем кнопку «Выполнить».  В диалоговом окне «Результаты поиска решения», выберем переключатель «Сохранить найденное решение» (Рис. 6).

[pic 6]

Рис. 5 - Параметры поиска решений

[pic 7]

[pic 8]

Рис. 6 - Результаты решения задачи

В ячейке E14 указано 1320. Это минимальные затраты на перевозку. В ячейках В11:D13 указаны значения оптимального плана поставок:

1-й поставщик должен доставить 170 единиц груза 3-му потребителю.

2-й поставщик должен доставить 120 единиц груза 1-му потребителю и 30 единиц груза 3-му потребителю.

3-й поставщик должен доставить 30 единиц груза 1-му потребителю, 70 единиц груза 2-му потребителю.