Настройка сервиса Поиск решения

Откройте окно Поиск решения, выполнив команду Сервис ® ® Поиск решения. Укажите в качестве целевой - ячейку С17, установите переключатель целевой ячейки в положение «минимальное значение». Введите в качестве изменяемых ячеек диапазон С11:G13.

Нажатием кнопки Добавить (условие) в окне Поиск решения последовательно введите ограничения поиска решения (2.5.3) – (2.5.5):

 

Таблица 2.5.4

Ограничения решения задачи для окна Поиск решения

Ссылка на ячейку	Знак	Ограничение
$C$11:$G$13	>=
$C$14:$G$14	=	$C$15:$G$15
$H$11:$H$13	=	$I$11:$I$13

 

Получившееся окно Поиска решения изображено на рис. 2.5.2.

 

Рис. 2.5.2. Настройки окна Поиск решения

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

 

После нажатия кнопки Выполнить в диалоговом окне Поиск решения откроется окно Результаты поиска решения с текстом «Решение найдено». Сохраните найденное решение и нажмите кнопку ОК. Результатом решения задачи оптимизации является таблица перевозок, соответствующая суммарной стоимости перевозок (целевой функции), равной 3330.

 

3. Решение несбалансированной транспортной задачи:

Запросы потребителей превосходят возможности

Отправителей

 

Решаемая в п. 1 – 2 задача является сбалансированной, т.е. потребности получателей равны возможностям отправителей. В этом можно убедиться, введя функции =СУММ(I11:I13) и =СУММ(С15:G15) в ячейки I14 и С16 соответственно. Увеличьте потребность магазина Б до 550. При этом суммарная потребность всех магазинов будет равна 1000, а пропускная способность всех оптовых баз – 800. Естественно, что суммарный объём перевозок в этом случае не превысит 800, так как пропускная способность и потребителей, и поставщиков не может быть превышена исходя из требований здравого смысла. Для решения задачи в третьем ограничении (см. рис. 2.5.2 или последнюю строку табл. 2.5.4) знак равенства замените на знак <=. После окончания процесса решения задачи суммарная стоимость перевозок (целевая функция) принимает значение 2990, т.е. уменьшится по сравнению со сбалансированной задачей. Однако магазины А и Б не обеспечивают свои потребности, так как возможности оптовых баз ограничены. Снижение общей стоимости перевозок при неизменном их объёме (800) стало возможным за счёт преобладания самых дешёвых маршрутов доставки: так, поставки в магазин Б увеличены с 350 до 520 единиц, а в магазин А – снижены с 250 до 80 единиц, так как в магазин Б доставлять товар в целом дешевле, чем в магазин А.

 

Решение несбалансированной транспортной задачи: предложение поставщиков превосходит потребности

Потребителей

Подобное соотношение на практике встречается чаще, чем в случае, рассмотренном в п. 3. Восстановите потребность магазина Б до исходного значения 350, а пропускную способность (мощность) оптовой базы 5 увеличьте с 80 до 180 единиц. При этом суммарная потребность всех магазинов будет равна 800, а пропускная способность всех оптовых баз – 900. Суммарный объём перевозок в этом случае также не превысит 800 исходя из требований здравого смысла. Для решения задачи в третьем ограничении (см. рис. 2.5.2 или последнюю строку табл. 2.5.4) восстановите знак равенства, а во втором ограничении (см. рис. 2.5.2 или последнюю строку табл. 2.5.4) замените равенство на знак <=. После окончания процесса решения задачи суммарная стоимость перевозок (целевая функция) примет значение 3030, т.е. также уменьшится по сравнению со сбалансированной задачей. Однако на оптовой базе 2 образуется резерв пропускной способности 100 единиц, так как все потребности потребителей (магазинов) уже исполнены. Снижение общей стоимости перевозок при неизменном их объёме (800) также стало возможным за счёт выбора самых дешёвых маршрутов доставки.

 

5. Задача для самостоятельного решения

 

Имеется пять морских портов-отправителей грузов и четыре морских порта-получателя, между которыми необходимо оптимизировать перевозки. Таблица стоимостей перевозок, а также пропускные способности портов отправления и назначения по вариантам приведены в табл. 2.5.5 и 2.5.6. Вариант задания выберите в соответствии с номером компьютера: первый вариант – для нечётных номеров, второй – для чётных.

Таблица 2.5.5

Исходные данные для самостоятельного решения

транспортной задачи (вариант 1)

Исходные данные	Порт-отправитель	Пропускная способность получателя
Порт- получатель	А
В
С
D
E
Пропускная способность отправителя

 

Таблица 2.5.6

Исходные данные для самостоятельного решения

транспортной задачи (вариант 2)

Исходные данные	Порт-отправитель	Пропускная способность получателя
Порт- получатель	А
В
С
D
E
Пропускная способность отправителя

 

5.1. Проверьте задачу на сбалансированность и решите её. Результаты решения сохраните на отдельном листе.

5.2. Решите несбалансированную задачу, аналогичную п. 3. Для этого измените пропускную способность двух любых портов отправления на 20 единиц. Результаты решения сохраните на отдельном листе.

5.3. Решите несбалансированную задачу, аналогичную п. 4. Для этого измените пропускную способность трёх любых портов назначения на 30 единиц. Результаты решения сохраните на отдельном листе.

Покажите полученную рабочую книгу MS Excel преподавателю.

 

Приложение А