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

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

Пример поиска решения

В приведенном ниже примере объем продаж в каждом квартале зависит от уровня рекламы, что косвенно определяет сумму доходов, издержки, а также прибыль. Чтобы найти максимальную возможную сумму общего дохода, процедура поиска решения может повышать ежеквартальные расходы на рекламу (ячейки B5:C5), пока общие расходы не превысят ограничения в 20 тысяч (ячейка F5). Значения во влияющих ячейках служат для вычисления дохода за каждый квартал, поэтому они связаны с формулой в целевой ячейке F7, =СУММА(Q1 Прибыль:Q2 Прибыль).

1 --- Изменяемые ячейки

2 --- Ячейка с ограничениями

3 --- Целевая ячейка

После выполнения процедуры получены следующие значения:

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

В составе Microsoft Excel в папке Office\Samples находится книга с примерами (Solvsamp.xls) использования процедуры поиска решения (Solver.xls).

Примеры, содержащиеся в книге Solvsamp.xls, помогут разрешить ваши вопросы. Чтобы применить любой из шести примеров: «Структура производства», «Транспортная задача», «График занятости», «Управление капиталом», «Портфель ценных бумаг» и «Проектирование цепи», — откройте книгу, перейдите к нужному листу и выберите команду Поиск решения в меню Сервис. В примерах уже подобраны целевая и влияющие ячейки, а также ограничения.

Алгоритм и методы поиска решения

Средство поиска решения Microsoft Excel использует алгоритм нелинейной оптимизации Generalized Reduced Gradient (GRG2), разработанный Леоном Ласдоном (Leon Lasdon, University of Texas at Austin) и Аланом Уореном (Allan Waren, Cleveland State University).

Алгоритмы симплексного метода и метода «branch-and-bound» для решения линейных и целочисленных задач с ограничениями разработаны Джоном Уотсоном (John Watson) и Деном Филстра (Dan Fylstra) из Frontline Systems, Inc. Чтобы получить более подробные сведения об используемых алгоритмах оптимального поиска, обратитесь по адресу:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
Адрес в Интернете: http://www.frontsys.com
Электронная почта: @

Авторские права на исполняемый код надстройки Microsoft Excel поиска решения версий 1990, 1991 и 1992 годов принадлежат Frontline Systems, Inc. Авторские права на версию 1989 года принадлежат Optimal Methods, Inc.

Добавление, изменение и удаление ограничения на поиск решения

В меню Сервис выберите команду Поиск решения.

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

Инструкции
Добавление ограничения
1. В разделе Ограничения диалогового окна Поиск решения нажмите кнопку Параметры.
2. В поле Ссылка на ячейку введите адрес или имя ячейки, на значение которой накладываются ограничения.
3. Выберите из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич ), который должен располагаться между ссылкой и ограничение. Если выбрано цел, в поле Ограничение появится «целое». Если выбрано двоич, в поле Ограничение появится «двоичное».
4. В поле Ограничение введите число, ссылку на ячейку или ее имя либо формулу.
5. Выполните одно из следующих действий.
  
  Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить.
  
  Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажмите кнопку OK.
Примечания
- Условные операторы типа цел и двоич можно применять только при наложении ограничений на изменяемые ячейки.
- Флажок Линейная модель в диалоговом окне Параметры поиска решения позволяет задать любое количество ограничений. При решении нелинейных задач на значения изменяемых ячеек можно наложить более 100 ограничений, в дополнение к целочисленным ограничениям на переменные.
Изменение и удаление ограничений
1. В списке Ограничения диалогового окна Поиск решения укажите ограничение, которое требуется изменить или удалить.
2. Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить.

Изменение способа поиска решения

В меню Сервис выберите команду Поиск решения.

Если команда Поиск решения отсутствует в меню Сервис, установите надстройку «Поиск решения».

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

Время поиска и количество итераций
1. В поле Максимальное время введите интервал в секундах, чтобы ограничить время поиска решения задачи.
2. В поле Предельное число итераций введите максимальное количество количество итераций, отводимое на достижение конечного результата.
Примечание. При достижении границы отведенного временного интервала или при выполнении отведенного числа итераций на экране появляется диалоговое окно Текущее состояние поиска решения.
Относительная погрешность
- В поле Относительная погрешность введите необходимую погрешность — чем меньше введенное число, тем выше точность результатов.
Допустимое отклонение
- В поле Допустимое отклонение введите необходимый допуск.
Сходимость
- В поле Сходимость введите значение относительного изменения, при достижении которого в последних пяти итерациях поиск решения прекращается — чем меньше это значение, тем выше точность результатов.
Примечание. Для получения дополнительных сведений об этих параметрах нажмите кнопку Справка в данном диалоговом окне.
Нажмите кнопку OK.
В диалоговом окне Поиск решения нажмите кнопку Выполнить или Закрыть.

Создание отчета о поиске решения

Сформулируйте задачу и найдите решение.

Инструкции
1. В меню Сервис выберите команду Поиск решения.
2. Если команда Поиск решения отсутствует в меню Сервис, установите надстройку «Поиск решения».
  
  Инструкции
  В меню Сервис выберите команду Надстройки.
  
  Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.
  
  Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.
  
  Следуйте инструкциям программы установки, если они имеются.
3. В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.
4. Выполните одно из следующих действий:
  - чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;
  - чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;
  - чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
5. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.
6. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
7. В поле Ограничения введите все ограничения, накладываемые на поиск решения.
  
  Инструкции
  Добавление ограничения
  
  В разделе Ограничения диалогового окна Поиск решения нажмите кнопку Параметры.
  
  В поле Ссылка на ячейку введите адрес или имя ячейки, на значение которой накладываются ограничения.
  
  Выберите из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич ), который должен располагаться между ссылкой и ограничение. Если выбрано цел, в поле Ограничение появится «целое». Если выбрано двоич, в поле Ограничение появится «двоичное».
  
  В поле Ограничение введите число, ссылку на ячейку или ее имя либо формулу.
  
  Выполните одно из следующих действий.
  
  Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить.
  
  Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажмите кнопку OK.
  
  Примечания
  
  Условные операторы типа цел и двоич можно применять только при наложении ограничений на изменяемые ячейки.
  
  Флажок Линейная модель в диалоговом окне Параметры поиска решения позволяет задать любое количество ограничений. При решении нелинейных задач на значения изменяемых ячеек можно наложить более 100 ограничений, в дополнение к целочисленным ограничениям на переменные.
  
  Изменение и удаление ограничений
  
  В списке Ограничения диалогового окна Поиск решения укажите ограничение, которое требуется изменить или удалить.
  
  Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить.
8. Нажмите кнопку Выполнить и выполните одно из следующих действий:
  - чтобы сохранить найденное решение на листе, выберите в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение;
  - чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения.
  Совет
  
  Чтобы прервать поиск решения, нажмите клавишу ESC. Лист Microsoft Excel будет пересчитан с учетом найденных значений влияющих ячеек.
Если решение будет найдено, выберите тип отчета в списке Отчеты и нажмите кнопку ОК.

Отчет будет помещен на новый лист книги.

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

В меню Сервис выберите команду Поиск решения.
Если команда Поиск решения отсутствует в меню Сервис, установите надстройку «Поиск решения».

Инструкции
1. В меню Сервис выберите команду Надстройки.
2. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.
3. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.
4. Следуйте инструкциям программы установки, если они имеются.
В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.
Выполните одно из следующих действий:
- чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;
- чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;
- чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.
Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
В поле Ограничения введите все ограничения, накладываемые на поиск решения.

Инструкции
Добавление ограничения
1. В разделе Ограничения диалогового окна Поиск решения нажмите кнопку Параметры.
2. В поле Ссылка на ячейку введите адрес или имя ячейки, на значение которой накладываются ограничения.
3. Выберите из раскрывающегося списка условный оператор ( <=, =, >=, цел или двоич ), который должен располагаться между ссылкой и ограничение. Если выбрано цел, в поле Ограничение появится «целое». Если выбрано двоич, в поле Ограничение появится «двоичное».
4. В поле Ограничение введите число, ссылку на ячейку или ее имя либо формулу.
5. Выполните одно из следующих действий.
  
  Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить.
  
  Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, нажмите кнопку OK.
Примечания
- Условные операторы типа цел и двоич можно применять только при наложении ограничений на изменяемые ячейки.
- Флажок Линейная модель в диалоговом окне Параметры поиска решения позволяет задать любое количество ограничений. При решении нелинейных задач на значения изменяемых ячеек можно наложить более 100 ограничений, в дополнение к целочисленным ограничениям на переменные.
Изменение и удаление ограничений
1. В списке Ограничения диалогового окна Поиск решения укажите ограничение, которое требуется изменить или удалить.
2. Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить.
Нажмите кнопку Выполнить и выполните одно из следующих действий:
- чтобы сохранить найденное решение на листе, выберите в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение;
- чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения.
Совет

Чтобы прервать поиск решения, нажмите клавишу ESC. Лист Microsoft Excel будет пересчитан с учетом найденных значений влияющих ячеек.

Загрузка модели оптимизации

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

В меню Сервис выберите команду Поиск решения.
В диалоговом окне Поиск решения нажмите кнопку Параметры.
В диалоговом окне Параметры поиска решения нажмите кнопку Загрузить модель.
Введите ссылку на весь диапазон ячеек с областью модели.

Применение стандартных значений параметров поиска решений

В меню Сервис выберите команду Поиск решения.
Нажмите кнопку Восстановить.

Сохранение модели оптимизации

Сформулируйте задачу и найдите решение.

Инструкции
1. В меню Сервис выберите команду Поиск решения.
2. Если команда Поиск решения отсутствует в меню Сервис, установите надстройку «Поиск решения».
  
  Инструкции
  В меню Сервис выберите команду Надстройки.
  
  Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.
  
  Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.
  
  Следуйте инструкциям программы установки, если они имеются.
3. В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.
4. Выполните одно из следующих действий:
  - чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;
  - чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;
  - чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
5. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.
6. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
7. В поле Ограничения введите все ограничения, накладываемые на поиск решения.
8. Нажмите кнопку Выполнить и выполните одно из следующих действий:
  - чтобы сохранить найденное решение на листе, выберите в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение;

чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения.

Совет

Чтобы прервать поиск решения, нажмите клавишу ESC. Лист Microsoft Excel будет пересчитан с учетом найденных значений влияющих ячеек.

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

Совет

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

Просмотр промежуточных результатов поиска решения

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