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

Фирма производит 2 модели, А и В сборных книжных полок. Их производство ограничено наличием сырья и временем машинной обработки. Для каждого изделия модели, А требуется 3 м. кв. досок, а для изделия В — 4 м. кв. Фирма может получать от своих поставщиков до 1700 м. кв. досок в неделю. Для каждого изделия модели, А требуется 12 мин. машинного времени, а для изделия модели В — 30 мин.

В неделю можно использовать 160 ч. машинного времени.

Сколько изделий каждой модели следует выпускать фирме в неделю, если каждое изделие модели, А приносит 2 $ прибыли, а каждое изделие модели В4 $ прибыли?

Составим математическую модель. Обозначим: x — количество изделий модели А, выпускаемых в течение недели, y — количество изделий модели В. Прибыль от этих изделий равна 24хy. Эту прибыль нужно максимизировать. Фирма, для которой ищется экстремум (максимум или минимум) носит название целевой функции. Беспредельному увеличению количества изделий препятствуют ограничения. Ограничено количество материала для полок, отсюда неравенство:

3x + 4y? 1700.

Ограничено машинное время на изготовление полок. На изделие, А уходит 0,2 часа, на изделие В — 0,5 часа, всего не более 160 ч., поэтому:

0,2x + 0,5y? 160.

Кроме того, количество изделий — неотрицательное число, поэтому:

Формально наша задача оптимизации записывается так:

Теперь решим задачу в Excel.

Таблица 2. — Введите в ячейки рабочего листа информацию:

Ячейкам В2 и В3 присвойте имена x и y. В ячейках B6, B9 и B10 представлены соответствующие формулы.

Выделим ячейку, в которой вычисляется целевая функция (B6), и вызовем Решатель («Сервис/Поиск решения»).

В диалоговом окне в поле ввода «Установить целевую ячейку» уже содержится адрес ячейки с целевой функцией $B$ 6. Установим переключатель: «Равной максимальному значению».

Перейдем к полю ввода «Изменяя ячейки:». В нашем случае достаточно щелкнуть кнопку «Предположить» и в поле ввода появится адрес блока B2: b3.

Перейдем к вводу ограничений. Щелкнем кнопку «Добавить». Появится диалоговое окно «Добавление ограничения». В поле ввода «Ссылка на ячейку», укажите B9.

Правее расположен выпадающий список с условными операторами (раскройте его и посмотрите). Выберем условие.

В поле ввода «Ограничение» введите число 1700. У нас есть еще одно ограничение, поэтому, не выходя из этого диалогового окна, щелкните кнопку «Добавить» и введите ограничение проектирование информация excel.

Ввод ограничений закончен, поэтому нажмите «ОК». Вы вновь окажитесь в диалоговом окне «Поиск решения». Вы увидите введенные ограничения:

Справа имеются кнопки «Изменить» и «Удалить». С их помощью вы можете изменить ограничение или стереть его. Щелкните кнопку «Параметры». Вы окажитесь в диалоговом окне «Параметры поиска решения». Чтобы узнать назначение полей ввода этого окна, щелкните кнопку «Справка». Менять ничего не будем, только установим 2 флажка: «Линейная модель» (т. к., наши ограничения и целевая функция являются линейными по переменным x и y) и «Неотрицательные значения» (для переменных x и y). Нажимаем кнопку «Выполнить». Появляется диалоговое окно «Результаты поиска решения». В нем мы читаем сообщение «Решение найдено. Все ограничения и условия оптимальности выполнены». На выбор предлагаются варианты: «Сохранить найденное решение» или «Восстановить исходные значения». Выбираем первое.

После нажатия «ОК» вид таблицы меняется: в ячейках x и y появляются оптимальные значения.

Таблица 3: