Использование команды Поиск решения

 

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

Представим себе, что мы распоряжаемся делами кафетерия, в котором продается кофе трех разновидностей: обычный кофе, особый кофе со сливками и особый кофе с шоколадом. В настоящий момент цены на них равны 3 руб., 4 руб. 45 коп. и 5 руб. 40 коп. соответственно. У нас нет полной информации относительно размера потенциальной прибыли и того, на какой напиток следует делать основную ставку (хотя особые виды кофе приносят больше прибыли, их компоненты стоят дороже, к тому же приготовление порции занимает больше времени). Некоторые подсчеты можно произвести и от руки, но все же хотелось бы представить данные в виде листа Excel, чтобы периодически обновлять информацию и анализировать ее с помощью команды Поиск решения.

ПРИМЕЧАНИЕ. Команда Поиск решения является надстройкой, поэтому перед началом работы с ней следует убедиться, что она установлена. Если команда Поиск решения отсутствует в меню Данные, выполните команду Надстройки из меню Сервис и установите флажок Пакет анализа в диалоговом окне Надстройки. Если данный флажок отсутствует, необходимо снова запустить программу установки Office.

 

 

Постановка задачи.

Первым шагом при работе с командой Поиск решения является создание специализированного листа. Для этого необходимо создать целевую ячейку, в которой определяется суть задачи (например, формула определения общего дохода, который необходимо максимизировать), а также одну или несколько переменных ячеек, значения которых могут изменяться для достижения поставленной цели. Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Для успешного поиска решения необходимо, чтобы каждая из переменных ячеек влияла на целевую ячейку (другими словами, формула в целевой ячейке должна опираться в вычислениях на другие значения переменных ячеек). В противном случае при выполнении команды Поиск решения появляется сообщение об ошибке: Результаты целевой ячейки не сходятся

СОВЕТ. Задавайте имена для ключевых ячеек. Если решаемая задача содержит несколько переменных и ограничений, для облегчений ввода данных следует дать имена ячейкам и диапазонам листа командой «Присвоить Имя» меню «Формулы».

 

 

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

После того как задача оптимизации будет подготовлена на листе, можно приступать к работе.

Необходимо выполнить следующие действия:

1. Выделите на листе целевую ячейку (с формулой, основанной на искомых значениях переменных ячеек).

2. Выполните команду Поиск решения из меню Данные (раздел Анализ данных). Открывается диалоговое окно Поиск решения. Поскольку на этапе 1 была выделена ячейка, в текстовом поле «Установить целевую ячейку» уже содержится правильная ссылка. В группе Равной установлен переключатель Максимальному значению (устанавливается по умолчанию).

3. Перейдите к полю «Изменяя ячейки». Перетащите диалоговое окно чтобы видеть переменные ячейки листа, и выделите их. Если переменные ячейки являются смежными, достаточно выделить весь диапазон перетягивания указателя. В противном случае следует щелкать на ячейках при нажатой клавише Ctrl - при этом ссылки в поле разделяются запятыми.

СОВЕТ. Используйте кнопку Предположить для просмотра результата. При щелчке на кнопке Предположить Excel пытается самостоятельно угадать состав переменных ячеек, входящих в задачу. Для этого он просматривает формулу и выбирает ячейки, на которые имеются ссылки. Не стоит полагаться на это предположение - довольно часто оно оказывается неверным.

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

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

ПРИМЕЧАНИЕ. Имеется несколько способов задать значение в поле Ограничение - ввод с клавиатуры, выделение ячейки на листе или указание ее имени.

6. Щелкните на кнопке Добавить, чтобы ввести первое ограничение и заняться вводом следующего ограничения.

7. После описания последнего ограничения щелкните на кнопке ОК.

СОВЕТ. Чтобы изменить ограничения, приведенные в диалоговом окне Поиск решения, выделите ограничение в списке и щелкните на кнопке Изменить. Чтобы настроить итерационный цикл и задать параметры вычислений, щелкните на кнопке Параметры и внесите требуемые изменения.

8. Оптимизационная задача готова к выполнению. Можно щелкнуть на кнопке выполнить для получения отпета. Через несколько секунд появляется диалоговое окно с описанием результатов процесса оптимизации. Если при поиске решения возникли проблемы, появляется сообщение об ошибке; кнопка Справка поможет получить дополнительные сведения. Если решение найдено, открывается диалоговое окно следующего вида.

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