2015-05-10
380
Необходимо составить математическую модель для задач лабораторной работы, ввести исходные данные в MS EXCEL и с помощью ПОИСКА РЕШЕНИЯ найти оптимальные значения:
· Установить целевую функцию – создать ссылку на ячейку, в которой записана формула целевой функции, в соответствующих кнопках установить значение, к которому должна стремиться целевая функция – максимальное, минимальное или равное 0:
· Изменяя ячейки – указать те ячейки, в которых находятся изменяемые переменные (что надо определить)
· Ограничения – указать ячейки, в которых записаны ограничения
· Параметры – установить все параметры по умолчанию и добавить:
Линейная модель;
Неотрицательные значения;
Показывать результаты итерации.
· Выполнить – просмотреть по шагам результаты решения
· Вывести отчет по результатам
Рассмотрим пример решения задачи с помощью процедуры Поиск решения.
Пример:
Страховая компания заключает 3 вида страховых договоров по страхованию имущества граждан: страхование автотранспорта, страхование бытовой электроники и страхование предметов искусства и антиквариата. Затраты по каждому из видов страхования представлены в табл. 3. Общая сумма затрат на страхование по всем договорам не должна превышать 20 000 руб.
|
|
|
Таблица 3 – Данные по затратам на 1 договор страхования
Данные о прибыли по каждому договору страхования представлены в табл. 4. Компания намерена развивать только те виды страхования имущества, которые приносили бы максимальную прибыль.
Таблица 4 – Данные по прибыли на 1 договор страхования
Определить, какое количество договоров и по какому виду страхования необходимо заключить компании, чтобы максимизировать свою прибыль.
Решение:
1-й шаг: принимаем за Х переменные, от которых зависит значение целевой функции. Это будут изменяемые ячейки (см. рис. 3).
Рисунок 3 – Создание исходной таблицы для решения задачи
2-й шаг: вводим ограничения, связанные с размером страхового фонда для выплат по договорам страхования.
Рисунок 4 – Ввод ограничений задачи в исходные ячейки
3-й шаг: введем в отдельную ячейку функцию ограничений (см. рис.5)
Рисунок 5 – Ввод функции ограничений задачи в отдельную ячейку
4-й шаг: записываем в отдельную ячейку целевую функцию (см. рис. 6)
9,21*Х1+9,51*Х2+10,5*Х3 → max
Рисунок 6 – Ввод целевой функции
Щелчком мыши необходимо сделать ячейку, содержащую формулу, активной и выбрать в меню Сервис команду Поиск решения.
5-й шаг: заполним диалоговое окно надстройки Поиск решения (см. рис.7)
Рисунок 7 – Заполнение надстройки Поиск решения для решения задачи
Поскольку активна ячейка, содержащая целевую функцию, то в поле Установить целевую ячейку будет находится ссылка на нее. Если активной была бы не ячейка с формулой, а какая-то другая ячейка, необходимо было бы в этом поле задать ссылку на целевую ячейку.
|
|
|
Щелчком на одном из трех переключателей задать цель оптимизации (в данном случае – максимальному значению). Далее щелчком мыши или нажатием клавиши Tab перейти в поле Изменяя ячейки и задать там ссылку на ячейки, содержащие переменные решаемой задачи. Проще всего задать эти ссылки, выделив нужные ячейки.
Щелчком на кнопке Добавить открыть окно Добавление ограничения. В открывшееся поле необходимо ввести ссылки на ячейки, содержащие ограничения и в раскрывающемся списке выбрать знак операции сравнения или условие целочисленности.
Щелчком мыши на кнопке Параметры необходимо задать параметры решения задачи. Тип задачи - линейная, значения – неотрицательные. Затем щелкнуть в окне Поиск решения по кнопке Выполнить.
6-й шаг: результаты, полученные после выполнения процедурыПоиск решения (см. рис. 8)
Рисунок 8 – Результаты решения задачи
Одновременно с перечетом таблицы раскроется окно Результаты поиска решения. Если необходимо сохранить исходные данные (начальный сценарий) и результаты на отдельном рабочем листе, следует в списке Тип отчета выбрать Результаты, затем щелкнуть на кнопке Сохранить сценарий и задать в открывшемся окне имя этого сценария. При этом в рабочую книгу добавится лист Отчет по результатам 1. Этот отчет содержит полную информацию об исходных данных, ограничениях и итогах вычислений. Так можно сохранить информацию о решении задачи при разных условиях, а затем сравнить эти варианты.
По умолчанию в диалоговом окне Результаты поиска решения включен переключатель Сохранить найденное решение. Если ничего не меня, щелкнуть на кнопке ОК, полученные результаты вычислений будут сохранены. А если включить переключатель Восстановить исходные значения и щелкнуть на кнопке ОК, восстановится исходная расчетная таблица.
Таким образом, страховой компании для получения максимальной прибыли в сумме 400043,37 руб. следует заключить 4322 договора по страхованию автотранспорта, 25 договор страхования бытовой электроники и не заключать договоров страхования предметов искусства и антиквариата.
Задачи для решения:
