Лабораторная работа № 23 Решение задач линейного программирования
Такие задачи, как оптимизация распределения ресурсов, минимизация транспортных затрат доставки товаров относятся к задачам линейного программирования. Задачи линейного программирования описываются системами линейных уравнений и линейными целевыми функциями, для решения которых в Excel предусмотрен инструмент Поиск решения из меню Сервис.
Задание 1
Требуется расфасовать 1400 кг сыпучего материала по контейнерам (каждый вмещает по 270 кг), бочкам (каждая вмещает по 130 кг) и канистрам (90 кг). Требуется определить, сколько контейнеров, бочек и канистр потребуется для расфасовки всего сыпучего материала.
1. Составьте таблицу, заполните её исходными данными (рис.1)
2. В ячейку B4 запишите выражение для расчета объема расфасованного материала по тарам:
=$B$2*$B$3+$C$2*$C$3+$D$2*$D$3. (1)
Выражение представляет сумму произведений вместительности данного типа ёмкости на количество емкостей данного типа.
Рис. 1
3. В ячейку B6 записываем целевую функцию, выраженную как разность между исходным количеством сыпучего материала (ячейка B5) и реально расфасованным количеством (ячейка B4):
=$B$5–$B$4. (2)
В соответствии с поставленной задачей эту разность необходимо свести к минимуму.
4. В меню Сервис выберите Поиск решения. Если указанный инструмент отсутствует, то в меню Сервис выберите пункт Надстройки и в окне установите флажок Поиск решения.
5. В окне Поиск решения выполните следующие действия:
– в поле Установить целевую ячейку запишите B6;
– в группе переключателей Равной: установите переключатель минимальному значению;
– в поле Изменяя ячейки укажите диапазон $B$3:$D$3;
– в поле Ограничения задайте искомые значения как целые $B$3:$D$3=целое и неотрицательное $B$3:$D$3>=0 (так как количество контейнеров и т. д. не может выражаться отрицательным числом) и $B$4<=$B$5 (так как исходное количество сыпучего материала не может быть меньше расфасованного).
6.
Щелкните по кнопке Выполнить.
Если материал расфасован без остатка,
то значение целевой функции будет равно
0. То есть найден-ное решение будет
оптималь-ным (рис. 2).
Рис. 2
Задание 2
Перед вами стоит задача расселения постояльцев в гостинице. Допустим, прибыло 125 человек. В гостинице имеются одно-, двух-, и трёхместные номера. Мест в гостинице достаточно для всех прибывших. Определить, какое количество одно-, двух-, и трёхместных номеров потребуется для полного расселения постояльцев.
1. Составьте таблицу, заполните её исходными данными (рис. 3)
2. В ячейку B5 запишите выражение для расчета количества расселённых постояльцев:
=$B$2*$B$3+$C$2*$C$3+$D$2*$D$3. (3)
Выражение представляет сумму произведений вместительности данного типа ёмкости на количество емкостей данного типа.
3. В ячейку B6 записываем целевую функцию, выраженную как разность между количеством прибывших (ячейка B4) и расселенным по номерам (ячейка B5), взятую по модулю (функция ABS):
=ABS($B$4–$B$5). (4)
В соответствии с задачей эту разность необходимо минимизировать.
4. В меню Сервис выберите Поиск решения. Заполните следующие поля:
установить целевую функцию – запишите B6;
переключатель Равной – установите в положение минимальному значению;
изменяя ячейки – запишите диапазон $B$3:$D$3;
ограничения – количество комнат должно быть целым ($B$3:$D$3 целое) и не отрицательное число ($B$3:$D$3>=0).
5. Щелкните по кнопке Выполнить.
Подобную задачу менеджер гостиницы может решить и без целевой функции, записав требования в поле окна ограничения следующим образом:
$B$3:$D$3=целое, $B$3:$D$3>=0, $B$4=$B$5.
Рис. 3
Последнее ограничение требует строгого соответствия количества при-бывших и размещённых людей, что отличается от предыдущего варианта, ког-да разность между указанными количествами только минимизировалась. Полученные результаты будут отличаться от предыдущих. Более жёсткое условие равенства вместо минимизации целевой функции изменяет результаты поиска решения и приводит к излишней точности.