Решение оптимизационных задач

С помощью команды Подбор параметра решаются задачи, в которых требуется найти значение одного параметра.

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

Если команды Поиск решения нет в меню Сервис, то надо ее установить, т.к. она является надстройкой: выберите команду Сервис®Надстройки… и в списке Доступные надстройки выполните установку команды Поиск решения (флажок).

Задачи, решаемые с помощью инструмента Поиск решения, можно сформулировать следующим образом:

дана функция (целевая функция);

найти такие, что целевая функция достигает минимального значения, максимального значения или некоторого фиксированного значения;

на искомые переменные могут накладываться ограничения (в виде равенств или неравенств).

Рис.9.4. Диалоговое окно команды Поиск решения
При выборе команды Поиск решения открывается диалоговое окно, которое содержит следующие элементы:

Ø в поле Установить целевую ячейку указывается адрес ячейки, значение которой необходимо максимизировать, минимизировать или установить равной заданному значению;

Ø переключатель Равной служит для выбора варианта оптимизации значения целевой ячейки;

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

Ø кнопка Предположить используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую указана в поле Установить целевую ячейку;

Ø поле Ограничения служит для отображения списка граничных условий поставленной задачи (задаются ограничения с помощью кнопок Добавить, Изменить, Удалить);

Ø кнопка Параметры используется для вывода диалогового окна Параметры поиска решения, позволяющего сохранить параметры поиска (Сохранить модель…) или использовать уже сохраненные параметры (Загрузить модель…);

Ø кнопка Выполнить используется для запуска поиска решения поставленной задачи;

Ø кнопка Закрыть позволяет выйти из диалогового окна без запуска поиска решения поставленной задачи;

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

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

С помощью инструмента Поиск решения можно решить не всякую задачу оптимизации. Если оптимальное решение не будет найдено, то в диалоговом окне Результаты поиска решения появится сообщение о неудачном завершении поиска.

6.Пусть в колхозе требуется распределить площадь пашни между двумя культурами в соответствии со следующими данными:

Культура Площадь, га Урожай, ц/га Затраты, руб./га Цена за 1 ц, руб. Затраты, человеко-дней на 1 га
x
y

Кроме того, заданы ресурсы производства:

земли —1800 га, человеко-дней —8000.

Величины x и y являются неизвестными и подлежат определению.

Построение математической модели задачи включает в себя:

задание целевой функции (ее надо максимизировать или минимизировать);

задание системы ограничений в форме линейных уравнений и неравенств;

требование неотрицательности переменных.

Ограничения задачи имеют вид:

ограничение по площади: ;

ограничение по человеко-дням: , или .

Кроме того ясно, что , .

Для прибыли получаем: .

Учитывая все условия задачи, приходим к ее математической модели: найти решение системы неравенств

,

которое соответствует максимуму функции .

Щелкните на кнопке Параметры этого диалогового окна Поиск решения и установите флажки Линейная модель и Неотрицательные значения, <ОК>, <Выполнить>.

Оптимальное решение найдено: в ячейке С2 значение x=1250 (га), в ячейке С3 значение y=550 (га).

7.Создайте таблицу по форме, вставьте необходимые формулы в колонку “Сумма выручки” и ячейки “Объем выпуска-итого”.

Используя инструмент Поиск решения, выполните расчет объема выпуска каждого товара, обеспечивающего максимальную сумму общей выручки, считая, что объем выпуска Изделия1<=10. Изделия2<=15, Изделия3<=20, Изделие4<=5, а общее количество выпускаемых изделий <=35.

Наименование товара Объем выпуска Расчет реализации
Цена Сумма выручки
Изделие1  
Изделие2  
Изделие3  
Изделие4  
Итого      

8.Предполагается, что рацион коров составляется из двух видов кормов —сена и концентратов. Суточная потребность кормов на 1 корову равна 20 кормовым единицам. В таблице приведены числовые данные о себестоимости кормов в данном хозяйстве.

Виды кормов Содержание кормовых единиц в 1 кг кормов Себестоимость кормов в коп.
Сено 0,5 1,5
Концентраты 1,0 2,5

Найти самый дешевый рацион, если ежедневный рацион кормления сельскохозяйственных животных должен включать не менее 16 кг сена.

Указание: ограничения , , целевая функция .

9.Мебельная фабрика выпускает кресла двух видов. На изготовление кресла первого типа расходуется 2 м досок стандартного сечения, 0,8 м2 обивочной ткани и затрачивается 2 человеко-часа, а на изготовление кресла второго типа — соответственно 4 м, 1,25 м2 и 1,75 человеко-часа. Известно, что цена одного кресла первого типа равна 15 рублей, второго типа — 20 рублей. Сколько кресел каждого типа надо выпускать, чтобы стоимость выпускаемой продукции была максимальной, если фабрика имеет в наличии 4400 м досок, 1500 м2 обивочной ткани и может затратить 3200 человеко-часов рабочего времени на изготовление этой продукции?

10.Хозрасчетной бригаде выделено для возделывания кормовых культур 100 га пашни. Эту пашню предполагается занять кукурузой и свеклой, причем свеклой решено занять не менее 40 га. Как должна быть распределена площадь пашни по культурам, чтобы получилось наибольшее число кормовых единиц? При этом должно быть учтено следующее: 1 ц кукурузного силоса содержит 0,2 кормовой единицы, 1 ц свеклы — 0,26 ц кормовой единицы, на возделывание 1 га кукурузного поля необходимо затратить 38 человеко-часов труда механизаторов и 15 человеко-часов ручного труда, а на 1 га поля, занятого свеклой, соответственно 43 и 185 человеко-часов, ожидаемый урожай кукурузы — 500 ц с 1 га, а свеклы — 200 ц с 1 га, наконец, всего на возделывание кормовых культур можно затратить 4000 человеко-часов труда механизаторов и 15000 человеко-часов ручного труда.