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

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

• Указывать несколько изменяемых ячеек.

• Указывать ограничения на значения изменяемых ячеек.

• Находить решение, при котором значение в определенной ячейке рабочего листа достигает максимума или минимума.

• Получить несколько решений.

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

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

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

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

• Решение должно находиться в определенных пределах или удовлетворять определенным ограничениям.

Простой пример использования процедуры поиска решения

Пусть компания выпускает три вида товаров (Товар А, Товар В и Товар С). Известна прибыль от производства единицы товара каждого вида. Общая прибыль от производства каждого вида товара равна произведению количества товара на прибыль от единицы. Конкретные данные приведены в нижеследующей таблице.

Продукция	Товар A	Товар B	Товар C
Прибыль	13 руб.	18 руб.	22 руб.

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

• Общий объем производства — всего 300 изделий в день.

• Компании нужно произвести 50 изделий А для выполнения существующего заказа.

• Компании нужно произвести 40 изделий В для выполнения планового заказа.

• Поскольку сбыт изделий С относительно небольшой, то должно быть изготовлено не более 40 единиц этого изделия.

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

Ограничения	Сокращенная запись	Ограничения	Сокращенная запись
Объем выпуска 300 единиц	В6=300	Не меньше 40 изделий В	В4>=40
Не меньше 50 изделий А	В3>=50	Не больше 40 изделий С	B5<=40

Введем исходные данные и расчетные формулы в рабочий лист. Чтобы долго не выдумывать, в графе Количество введем 100 единиц для всех вид продукции. Общий вид этого рабочего листа представлен на рисунке 16.4.

Рис. 16.4. Вид рабочего листа с исходными и вычисленными данными.

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

1. Ввести в рабочий лист исходные данные и формулы.

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

3. Указать целевую ячейку.

4. Указать изменяемые ячейки.

5. Задать ограничения.

Рис.16.5. Окно Поиск решения.

6. При необходимости изменить опции процедуры поиска решения.

7. Позволить процедуре поиска решения выполнить поставленную задачу.

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

В поле Установить целевую ячейку указать D6. Поскольку наша цель – максимизировать значение в этой ячейке, установим переключатель Равной максимальному значению. Затем определим изменяемые ячейки, которые находятся в диапазоне В3:В5. Далее щелкнем на кнопке Добавить.

Появится показанное ниже диалоговое окно Добавление ограничения, показанное на рисунке 16.6.

Рис.16.5. Окно для добавления ограничений.

В этом окне для каждого ограничения нужно указать ссылку на ячейку, оператор ограничения, значение ограничения и щелкнуть на кнопке Добавить. После ввода всех ограничений нужно щелкнуть на кнопке ОК.

После этого мы снова окажемся в окне Поиск решения. Теперь можно щелкнуть на кнопке Выполнить.

Через некоторое время появится диалоговое окно Результаты поиска решения, показанное на рисунке 16.7.

Рис. 16.7. Окно результатов поиска решения.

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

• Заменить исходные значения в изменяемых ячейках на найденные в результате решения задачи.

• Восстановить исходные значения в изменяемых ячейках.

• Создать несколько отчетов о процедуре поиска решения (Для выбора нескольких отчетов нужно воспользоваться клавишей <Shift>).

• Щелкнуть на кнопке Сохранить сценарий Для сохранения решения в виде сценария, который можно использовать в средстве Диспетчер сценариев.

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

Microsoft Excel 9.0 Отчет по результатам
Рабочий лист: [ПримерМакросов.xls]Модель продаж
Отчет создан: 04.05.02 8:15:48
Целевая ячейка (Максимум)
	Ячейка	Имя	Исходно	Результат
	$D$6	Всего Доход	5 300,00р.	5 310,00р.
Изменяемые ячейки
	Ячейка	Имя	Исходно	Результат
	$B$3	Товар А Кол-во	100	50
	$B$4	Товар В Кол-во	100	210,000001
	$B$5	Товар С Кол-во	100	40
Ограничения
	Ячейка	Имя	Значение	формула	Статус	Разница
	$B$6	Всего Кол-во	300,000001	$B$6=300	связанное	0
	$B$3	Товар А Кол-во	50	$B$3>=50	связанное	0
	$B$4	Товар В Кол-во	210,000001	$B$4>=40	не связан.	170
	$B$5	Товар С Кол-во	40	$B$5<=40	связанное	0