Методические указания по выполнению домашней контрольной работы для студентов дистанционной формы обучения на базе среднего (полного) общего образования (стр. 6 из 7)

Рисунок 2.6 - Окно параметров Excel

Рисунок 2.7 - Диалоговое окно установки надстроек

При успешной загрузке надстройки «Поиск решения» в группе Анализ на вкладки Данные становится доступна команда Поиск решения.

Рассмотрим процесс ее использования на примере простейшей задачи линейной оптимизации.

2.2.2 Линейная оптимизация

Пример 2.2.

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

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

Решение.

1. Составим математическую модель.

Обозначим: х — количество изделий модели А, выпускаемых в течение недели, у — количество изделий модели В.

Прибыль от этих изделий равна 2х+4у доллара. Эту прибыль нужно максимизировать. Функция, для которой ищется экстремум (максимум или минимум), носит название целевой функции.

Беспредельному увеличению количества изделий препятствуют ограничения.

- Ограничено количество материала для полок, отсюда неравенство

Зх + 4 у < £1700 .

- Ограничено машинное время на изготовление полок. На изделие А уходит 0.2 часа, на изделие В — 0.5 часа, а всего не более 160 ч, поэтому 0.2х + 0.5у £ 160.

- Количество изделий — неотрицательное число, поэтому х ³ 0, у ³ 0.

- Кроме того, нельзя выпустить дробное число полок, поэтому х и у должны быть целыми числами.

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

2. Решим эту задачу в Excel.

Для решения надо выполнить следующие действия:

- Ввести в ячейки D3:D4 начальные приближения для количества выпускаемых изделий (рис. 1.8).

- В ячейку D7 ввести формулу =2*D3+4*D4 для вычисления целевой функции.

- В ячейки D10 и D11 ввести формулы =3*D3+4*D4 и =0,2*D3+0,5*D4 соответственно для вычисления ограничений.

- Выделить ячейку D7, в которой вычисляется целевая функция.

- Выполнить команду «Данные/ Анализ/ Поиск решения…». При этом появляется диалоговое окно «Поиск решения» (рис.1.9).

- В диалоговом окне в поле «Установить целевую» уже содержится адрес ячейки с целевой функцией $D$7.

- Установить переключатель «Равной:» в положение «максимальному значению».

- В поле «Изменяя ячейки:» задать диапазон $D$3:$D$4. Это можно сделать несколькими способами. Во-первых, набрать с клавиатуры. Во-вторых, воспользоваться кнопками навигатора. В данной задаче достаточно щелкнуть кнопку "Предположить" и в поле ввода появится адрес блока $D$3:$D$4.

- Перейти к вводу ограничений. Щелкнуть по кнопке "Добавить". При этом появится диалоговое окно "Добавление ограничения" (рис. 1.10).

- В поле ввода "Ссылка на ячейку:" указать $D$10.

- В выпадающем списке с условными операторами выбрать условие <=.

- В поле «Ограничение:» ввести число 1700.

- Не выходя из этого диалогового окна, щелкнуть кнопку "Добавить" и ввести ограничение $D$11<=160 (рис.1.10).

- Не выходя из этого диалогового окна, щелкнуть кнопку "Добавить" и ввести ограничение $D$3:$D$4=целое.

Рисунок 2.8 - Заполнение шаблона задачи линейной регрессии

Рисунок 2.9 - Диалоговое окно «Поиск решения»

Рисунок 2.10 - Диалоговое окно «Добавление ограничения»

Рисунок 2.11 - Диалоговое окно «Параметры поиска решения»

- Нажать кнопку ОК. При этом Вы возвращаетесь в диалоговое окно "Поиск решения" (рис.2.9). В нем отображаются введенные ограничения. Справа имеются кнопки "Изменить" и "Удалить". С их помощью можно изменить какое-либо ограничение или стереть его.

- Щелкнуть кнопку "Параметры". При этом откроется диалоговое окно "Параметры поиска решения" (рис. 2.11).

- Установить флажки «Линейная модель» (так как ограничения и целевая функция являются линейными по переменным х и у) и «Неотрицательные значения» (для переменных х и у, что оговаривалось при построении математической модели).

- Щелкнуть кнопку ОК. Происходит возврат в исходное окно. Таким образом, задача оптимизации полностью подготовлена.

- Нажимаем кнопку «Выполнить». Появляется диалоговое окно «Результаты поиска решения» (рис.2.12).

- Выбрать вариант: «Сохранить найденное решение».

- Нажать клавишу ОК. Вид таблицы меняется: в ячейках D3 и D4 появляются оптимальные значения.

Рисунок 2.12 - Диалоговое окно «Результаты поиска решения»

Рисунок 2.13 - Результат решения задачи линейной оптимизации

Решение задачи показывает, что для получения максимальной прибыли изделие А нужно выпускать в количестве 300 штук в неделю, а изделие В — 200 штук. Соответственно пересчитываются все формулы рабочего листа. Целевая функция достигает значения 1400. Полный вариант результатов решения приведен на рисунке 2.13.

2.2.3 Нелинейная оптимизация

Во многих ситуациях уравнения математической модели для решения задачи оптимизации имеют нелинейный характер. Оптимизационные задачи такого вида называются задачами нелинейной оптимизации.

В случае нелинейной оптимизации необходимо воспользоваться тем же алгоритмом, что и для решения задач линейной оптимизации (п. 2.2).

Существенным является только одно отличие в решениях. На этапе выбора параметров поиска решения (рис. 2.11) необходимо снять флажок «Линейная модель».

2.2.4 Сценарии

Сценарий — это набор значений для изменяемых ячеек, которому дано имя. Благодаря этому средству пользователь таблицы может хранить в ней несколько вариантов расчетов и обращаться к ним при необходимости.

Очень часто использование сценариев оправдано при решении оптимизационных задач, в которых при одинаковых исходных значениях используются разные условия оптимизации.

Рассмотрим процесс создания и использования сценариев на примере решения конкретной оптимизационной задачи.

Пример 2.3.

Имеются три сплава. Первый сплав содержит 70% олова и 30% свинца, второй — 80% олова и 20% цинка, третий — 50% олова, 10% свинца и 40% цинка. Из них необходимо изготовить новый сплав, содержащий 15% свинца. Какое наибольшее и наименьшее процентное содержание олова может быть в этом сплаве?

Решение.

1. Математическая модель задачи.

Пусть и — количество первого сплава, v — количество второго сплава, w — количество третьего сплава, взятые для изготовления нового сплава.

Так как в сплаве должно быть 15% свинца, получаем уравнение:

Количество олова в новом сплаве:

Для этой функции трех неотрицательных переменных нужно найти наибольшее и наименьшее значения.

Уместно перейти к новым переменным:

Тогда мы получаем ограничения: 0.3x+0.1z=0.15 и х+y+z=1, причем переменные х, у, z неотрицательные. Целевая функция примет вид: 0.7x+O.8y+0.5z

2. Подготовка решения задачи в Microsoft Excel.

Для этого заполним рабочий лист так, как показано на рисунке 1.14. На рисунке в столбце С приведены формулы из столбца В. На ячейку В8 наложен процентный формат.

Рисунок 2.14 - Подготовка рабочего листа для решения задачи оптимизации

Выделим ячейку В8 и выполним команду «Данные/ Анализ/ Поиск решения». Зададим ячейку с целевой функцией (В8), изменяемые переменные (В3:В5) и ограничения (В11=0,15 и В12=1). Щелкнув кнопку «Параметры», установим флажки «Линейная модель» и «Неотрицательные значения».

Дальнейшее решение ведется с использованием сценариев, поскольку вопрос задачи предполагает два варианта решения: для максимального и минимального процентного содержания олова в сплаве.

3. Создание сценариев в Microsoft Excel.

Для создания сценариев нужно выполнить следующие действия:

- В диалоговом окне «Поиск решения» (рис. 1.9) установить переключатель «Равной:» в положение «максимальному значению». Нажать кнопку ОК.

- В диалоговом окне "Результаты поиска решения" (рис.1.12) щелкнуть кнопку "Сохранить сценарий". Появится диалоговое окно "Сохранение сценария" (рис. 1.15).