Постановка и решение задачи с помощью надстройки "Поиск решения"

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

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

1. Ячейки переменных

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

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

В результате выполнения получены следующие значения:

Постановка и решение задачи

1. На вкладке " Данные " в группе "Анализ " выберите пункт "Поиск решения".
   

   Примечание

   Если команда "Поиск решения " или группа "Анализ " недоступны, необходимо активировать надстройку "Поиск решения". Дополнительные сведения см. в разделе Как активировать надстройку "Поиск решения".

   

2. В поле "Задать цель" введите ссылку на ячейку или ее имя. Целевая ячейка должна содержать формулу.

3. Выполните одно из следующих действий.

   • Чтобы значение целевой ячейки было как можно большим, выберите значение Max.
   • Чтобы значение целевой ячейки было как можно меньше, выберите "Мин".
   • Если нужно, чтобы в целевой ячейке использовалось определенное значение, нажмите кнопку "Значение" и введите значение в поле.
   • В поле Изменяя ячейки переменных введите имена диапазонов ячеек переменных решения или ссылки на них. Несмежные ссылки разделяйте запятыми. Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 ячеек переменных.

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

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

   2. В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых налагаются ограничения.

   3. Выберите нужное отношение ( <=, =, >=, целое, бин или диф ) между ячейкой, на которую указывает ссылка, и ограничением. Если выбрано целое число, в поле ограничения отображается целое число. Если выбрать bin, в поле ограничения появится двоичный код. Если выбран вариант "дифференц", в поле "Ограничение" отображается "все другое".

   4. Если для отношения в поле ограничения выбрано <значение =, = или >=, введите число, ссылку на ячейку, имя или формулу.

   5. Выполните одно из следующих действий.

      • Чтобы принять ограничение и добавить другое, нажмите кнопку "Добавить".

      • Чтобы принять ограничение и вернуться в диалоговое окно "Параметры решателя", нажмите кнопку ОК.

        Примечание

        Отношения int,b и dif можно применять только в ограничениях для ячеек переменных решения.

   6. Вы можете изменить или удалить существующее ограничение, выполнив следующие действия.

      • В диалоговом окне Параметры поиска решения выберите ограничение, которое требуется изменить или удалить.
      • Нажмите "Изменить" и внесите изменения или выберите "Удалить".

5. Выберите "Решить" и выполните одно из следующих действий.

   • Чтобы сохранить значения решения на листе, в диалоговом окне Результаты поиска решения выберите Сохранить решение средства поиска.
   • Чтобы восстановить исходные значения до нажатия кнопки "Решить", выберите "Восстановить исходные значения".
   • Процесс решения можно прервать, нажав клавишу ESC. Excel пересчитывает лист с учетом последних значений ячеек переменной решения.
   • Чтобы создать отчет на основе вашего решения после того, как надстройка "Поиск решения" найдет решение, выберите тип отчета в окне "Отчеты" и нажмите кнопку "ОК". Отчет будет помещен на новый лист книги. Если решение не найдено, будут доступны только некоторые отчеты или они вообще не будут доступны.
   • Чтобы сохранить значения в ячейках переменных решения для отображения позже, выберите команду Сохранить сценарий в диалоговом окне Результаты поиска решения и введите имя сценария в поле Имя сценария .

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

1. После определения задачи в диалоговом окне "Параметры поиска решения" выберите "Параметры".

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

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

4. В диалоговом окне " Демонстрация пробного решения " выполните одно из следующих действий.

   • Чтобы остановить процесс решения и отобразить диалоговое окно Результаты поиска решения , выберите Остановить.
   • Чтобы продолжить процесс решения и показать следующее пробное решение, выберите "Продолжить".

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

1. В диалоговом окне Параметры поиска решения (Solver Parameters ) выберите Опции (Options).
2. В диалоговом окне на вкладках Все методы, Поиск решения нелинейных задач методом ОПГ и Эволюционный поиск решения выберите или введите значения нужных параметров.

Сохранение или загрузка модели задачи

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

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

   Совет

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

Методы поиска решения

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

• Обобщенный уменьшенный градиент (GRG) Nlinear: Используйте для задач с гладким, нелинейным характером.
• LP Simplex: Используйте для линейных задач.
• Эволюционный: Используйте для проблем, которые не являются гладкими.

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

В приведенном ниже примере количество проданных единиц в каждом квартале зависит от уровня рекламы, что косвенно определяет объем продаж, связанные издержки и прибыль. Надстройка "Поиск решения" может изменять ежеквартальные расходы на рекламу (ячейки переменных решения B5:C5) до ограничения в 20 000 рублей (ячейка D5), пока общая прибыль (целевая ячейка D7) не достигнет максимального значения. Значения в переменных ячейках используются для вычисления прибыли за каждый квартал, поэтому они связаны с целевой ячейкой формулы D7: =СУММ(прибыль за 1 квартал:прибыль за 2 квартал).

Количество переменных ячеек

Ограниченная ячейка

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

В результате выполнения получены следующие значения:

Постановка и решение задачи

1. Выберите "Поиск данных>".

   

2. В поле "Задать цель" введите ссылку на ячейку или имя целевой ячейки.

   Примечание

   Целевая ячейка должна содержать формулу.

3. Выполните одно из следующих действий.

   Действие	Действия
   Сделать так, чтобы значение целевой ячейки было максимальным из возможных	Выберите "Максимальный".
   Сделать так, чтобы значение целевой ячейки было минимальным из возможных	Выберите мин.
   Сделать так, чтобы целевая ячейка имела определенное значение	Выберите "Значение", а затем введите значение в поле.

4. В поле Изменяя ячейки переменных введите имена диапазонов ячеек переменных решения или ссылки на них. Несмежные ссылки разделяйте запятыми.
   Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 ячеек переменных.

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

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

   2. В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых налагаются ограничения.

   3. Во всплывающем меню отношения = выберите нужное отношение между ячейкой, на которую указывает ссылка, и ограничением.< Если в поле ограничения выбрано <=, = или >=, введите число, ссылку на ячейку, имя ячейки или формулу.

      Примечание

      Отношения int,b и dif можно применять только к ограничениям к ячейкам переменных решения.

   4. Выполните одно из следующих действий.

   Действие	Действия
   Принять ограничение и добавить другое	Нажмите кнопку Добавить.
   Принять ограничение и вернуться в диалоговое окно Параметры поиска решения	Нажмите кнопку ОК.

6. Нажмите кнопку "Решить" и выполните одно из следующих действий.

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

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

1. Выберите "Поиск данных>".

   

2. После определения задачи в диалоговом окне "Параметры надстройки " выберите "Параметры".

3. Установите флажок "Показать результаты итерации" проверка, чтобы увидеть значения для каждого пробного решения, а затем нажмите кнопку "ОК".

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

5. В диалоговом окне " Демонстрация пробного решения " выполните одно из следующих действий.

   Действие	Действия
   Остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения	Выберите "Остановить".
   Продолжить поиск и просмотреть следующее предварительное решение	Нажмите кнопку Продолжить.

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

1. Выберите "Поиск данных>".

   

2. Выберите "Параметры", а затем в диалоговом окне "Параметры" или "Параметры решения проблемы" выберите один или несколько из следующих параметров:

   Действие	Действия
   Настроить время решения и число итераций	На вкладке Все методы в разделе Пределы решения в поле Максимальное время (в секундах) введите количество секунд, в течение которых можно будет искать решение. Затем в поле Итерации укажите максимальное количество итераций, которое вы хотите разрешить. Примечание. Если процесс решения достигает максимального времени или количества итераций, прежде чем надстройка «Поиск решения» найдет решение, отобразится диалоговое окно «Показ пробного решения ».
   Задать точность	На вкладке Все методы введите в поле Точность ограничения нужное значение погрешности. Чем меньше число, тем выше точность.
   Задать степень сходимости	На вкладке Поиск решения нелинейных задач методом ОПГ или Эволюционный поиск решения в поле Сходимость укажите, насколько должны отличаться результаты последних пяти итераций, чтобы средство прекратило поиск решения. Чем меньше число, тем меньше должно быть изменение.

3. Нажмите кнопку ОК.

4. В диалоговом окне Параметры поиска решения выберите Решить или Закрыть.

Сохранение или загрузка модели задачи

1. Выберите "Поиск данных>".

   

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

   Совет

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

Методы поиска решения

1. Выберите "Поиск данных>".

   

2. Во всплывающем меню Выберите метод решения выберите одно из следующих значений:

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

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