Информационные технологии

Поиск решения

В Excel имеется надстройка "Поиск решения", которая позволяет решать оптимизационные задачи, а также решать уравнения. Меню Данные/Анализ/Поиск решения. Если надстройки нет, её нужно установить. Бывает так, что надстройка установлена, но не отображается. Надо выполнить команду кнопка Office/Параметры Excel/Надстройки/ и включить надстройку.

Оптимизация состоит в отыскании минимума или максимума целевой функции модели при заданных ограничениях. На рабочем листе нужно создать целевую ячейку и изменяемые ячейки.

1. В целевой ячейке строится выражение Excel, которое вычисляет значение целевой функции. 

2. Целевая ячейка зависит от группы ячеек, которые называются изменяемыми ячейками. Их значения надо подобрать так, чтобы значение в целевой ячейке было максимальным, минимальным или заранее определенным.

3. Решение (значения изменяемых ячеек) должно удовлетворять определенным ограничениям.

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

Пример

Фирма производит две модели А и В полок. Их производство ограничено наличием досок и временем изготовления. Для изделия А требуется 3 м² досок, а для изделия В — 4 м². Фирма может получать до 1700 м² досок в неделю. Для изделия А требуется 0,2 час, а для изделия В — 0,5 час. В неделю можно использовать 160 час. Изделие А приносит 2 долл., а изделие В — 4 долл. прибыли. Сколько изделий каждой модели следует выпускать фирме в неделю, чтобы максимизировать прибыль?

Решение

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

1. Обозначим: х — количество изделий А, выпускаемых в течение недели, у — количество изделий В.
2. Прибыль от этих изделий равна 2х+4у долл. Эту прибыль нужно максимизировать.
3. Ограничено количество досок для полок: Зх+4у<=1700.
4. Ограничено время на изготовление полок: 0,2х+0,5у<=160 .
5. Количество изделий — неотрицательное число: х>= 0, у>= 0.

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

Заметим, что данная модель является линейной, т.к. целевая функция и ограничения линейно зависят от переменных.

Теперь решим эту задачу в Excel:

	А	В
1	Переменные	Исходные данные и формулы
2	Количество изделий А (x)	0
3	Количество изделий В (y)	0
4
5	Целевая функция
6	Прибыль	=2*х+4*у
7
8	Ограничения
9	Доски	=3*х+4*у
10	Время	=0,2*х+0,5*у

Дайте ячейке В2 имя х, ячейке В3 имя y, ячейке В6 имя Прибыль, ячейке В9 имя Доски, ячейке В9 имя Время. Выделим ячейку В6, в которой вычисляется целевая функция, и вызовем Поиск решения.

Появится диалоговое окно "Поиск решения".

1. В поле "Установить целевую ячейку" уже содержится адрес ячейки с целевой функцией $В$6.
2. Установим переключатель "Равной максимальному значению".
3. Перейдем к полю "Изменяя ячейки". В нашем случае достаточно щелкнуть кнопку "Предположить" и в поле появится адрес блока $В$2:$В$3.
4. Перейдем к вводу ограничений. Щелкнем кнопку "Добавить".

Появится диалоговое окно "Добавление ограничения". Ограничения добавляются по одному.

1. В поле "Ссылка на ячейку" укажите $В$9.
2. Правее расположен выпадающий список с условными операторами. Выберем условие <=.
3. В поле "Ограничение" введите число 1700.
4. У нас есть еще одно ограничение, поэтому, щелкните кнопку "Добавить" и
5. Введите ограничение $В$10<=160.
6. Ввод ограничений закончен, поэтому нажмите ОК.

Вы вновь окажетесь в диалоговом окне "Поиск решения".

1. Вы увидите введенные ограничения $В$10<=160 и $В$9<=1700.
2. Справа имеются кнопки "Изменить" и "Удалить". С их помощью Вы можете изменить ограничение или стереть его.
3. Щелкните кнопку "Параметры".

Вы окажетесь в диалоговом окне "Параметры поиска решения".

1. Установим флажок "Линейная модель" (так как наши ограничения и целевая функция являются линейными по переменным х и у).
2. Установим флажок "Неотрицательные значения" (для переменных х и у).
3. Щелкнем ОК и окажемся в исходном окне "Поиск решения".

Мы полностью подготовили задачу оптимизации. Нажимаем кнопку "Выполнить".

Появляется диалоговое окно "Результаты поиска решения".

1. В нем мы читаем сообщение "Решение найдено. Все ограничения и условия оптимальности выполнены."
2. На выбор предлагаются варианты: "Сохранить найденное решение" или "Восстановить исходные значения". Выбираем первое.
3. Можно также вывести отчеты: по результатам, по устойчивости, по пределам. Выделите их все, чтобы иметь представление о том, какая информация в них размещена.
4. Нажмите ОК.

После нажатия ОК вид таблицы меняется.

	А	В
1	Переменные	Исходные данные и формулы
2	Количество изделий А (x)	300
3	Количество изделий В (y)	200
4
5	Целевая функция
6	Прибыль	1400
7
8	Ограничения
9	Доски	1700
10	Время	160

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

Добавьте ограничение чтобы х было целочисленным. Аналогично сделайте ограничение для y.

Совет. Ограничения можно было ввести быстрее. Нужно было ввести в В9:В10 формулы =3*х+4*у-1700 и =0.2*х+0.5*у-160. Тогда ограничения можно было задать блоком $В$9:$В$10<=0. В случае большого количества ограничений это существенно ускорит подготовку задачи.