Процедура поиска решения

О надстройке «Поиск решения»

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

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

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

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

1

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

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

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

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

2

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

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

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

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

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

Инструкции

1. В меню Сервис выберите команду Надстройки.

2. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.

3. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.

4. Следуйте инструкциям программы установки, если они имеются.

  1. Добавьте или измените ограничения.

Инструкции

Добавление ограничения

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

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

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

4. В поле Ограничение введите число, ссылку на ячейку или ее имя либо формулу.

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

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

Примечания

o Условные операторы типа цел и двоич можно применять только при наложении ограничений на изменяемые ячейки.

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

Изменение и удаление ограничений

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

2. Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить.

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

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

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

Инструкции

1. В меню Сервис выберите команду Надстройки.

2. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.

3. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.

4. Следуйте инструкциям программы установки, если они имеются.

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

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

Время поиска и количество итераций

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

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

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

Относительная погрешность

В поле Относительная погрешность введите необходимую погрешность — чем меньше введенное число, тем выше точность результатов.

Допустимое отклонение

В поле Допустимое отклонение введите необходимый допуск.

Сходимость

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

Примечание. Чтобы получить дополнительные сведения о других параметрах, нажмите в диалоговом окне кнопку Справка .

1. Нажмите кнопку OK.

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

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

Инструкции

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

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

3. В меню Сервис выберите команду Надстройки.

4. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.

5. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.

6. Следуйте инструкциям программы установки, если они имеются.

7. В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.

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

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

9. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.

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

11. В поле Ограничения введите все ограничения , накладываемые на поиск решения.

Добавление ограничения

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

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

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

4. В поле Ограничение введите число, ссылку на ячейку или ее имя либо формулу.

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

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

Изменение и удаление ограничений

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

2. Выберите команду Изменить и внесите изменения либо нажмите кнопку Удалить.

3. Нажмите кнопку Выполнить и выполните одно из следующих действий:

§ чтобы сохранить найденное решение на листе, выберите в диалоговом окне Результаты поиска решения вариант Сохранить найденное решение;

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

4. Если решение будет найдено, выберите тип отчета в списке Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги.

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

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

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

Инструкции

1. В меню Сервис выберите команду Надстройки.

2. Нажмите кнопку Обзор, чтобы найти надстройку, которой нет в окне Список надстроек.

3. Установите в окне Список надстроек флажок той надстройки, которую необходимо загрузить, а затем нажмите кнопку OK.

4. Следуйте инструкциям программы установки, если они имеются.

3. В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.

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

1. чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;

2. чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;

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

5. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.

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

7. В поле Ограничения введите все ограничения, накладываемые на поиск решения.

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

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

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

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

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

4. Введите ссылку на весь диапазон ячеек с областью модели.

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

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

Инструкции

    1. В меню Сервис выберите команду Поиск решения.
    2. В поле Установить целевую ячейку введите ссылку на ячейку или имя конечной ячейки. Конечная ячейка должна содержать формулу.
    3. Выполните одно из следующих действий:
      • чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению;
      • чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению;
      • чтобы установить значение в конечной ячейке равным некоторому числу, установите переключатель в положение значению и введите в соответствующее поле требуемое число.

        4. В поле Изменяя ячейки введите имена или ссылки на изменяемые ячейки, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с конечной ячейкой. Допускается задание до 200 изменяемых ячеек.

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

        6. В поле Ограничения введите все ограничения , накладываемые на поиск решения.

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

        8. Нажмите кнопку Параметры.

        9. Нажмите кнопку Сохранить модель.

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

        Совет

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

        Сохранение значений влияющих ячеек в качестве сценария

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

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

        3. В поле Ограничения введите все ограничения, накладываемые на поиск решения.

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

        5. Введите в поле Название сценария название сценария.

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

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

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

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

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

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

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

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

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

Работа со сценариями

Сценарии

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

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

3

В приведенном примере можно назвать сценарий «Худший случай», установить в ячейке B1 значение 50 000р., а в ячейке B2 значение 13 200р.

4

Второй сценарий можно назвать «Лучший случай» и изменить значения в ячейке B1 на 150 000р., а в ячейке B2 на 26 000р.

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

Создание сценария

        1. Выберите команду Сценарии в меню Сервис.

        2. Нажмите кнопку Добавить.

        3. Введите в поле Название сценария название сценария .

        4. В поле Изменяемые ячейки введите ссылки на ячейки, которые требуется изменить.

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

        5. Установите необходимые флажки в наборе флажков Защита .

        6. Нажмите кнопку OK.

        7. Введите необходимые значения в диалоговом окне Значения ячеек сценария.

        8. Чтобы создать сценарий, нажмите кнопку OK.

        9. Если требуется создать дополнительные сценарии, снова нажмите кнопку Добавить, а затем повторите эту процедуру. После завершения создания сценариев нажмите кнопку OK, а затем — кнопку Закрыть в диалоговом окне Диспетчер сценариев.

Создание итогового отчета по сценариям

        1. Выберите команду Сценарии в меню Сервис.

        2. Нажмите кнопку Отчет.

        3. Установите переключатель в положение Структура или Сводная таблица .

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

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

Удаление сценария

        1. Выберите команду Сценарии в меню Сервис.

        2. Выберите название сценария, который требуется удалить, а затем нажмите кнопку Удалить.

        Просмотр сценария

        При просмотре сценария изменяются ячейки, сохраненные как часть этого сценария.

        1. Выберите команду Сценарии в меню Сервис.

        2. Выберите название сценария, который необходимо просмотреть.

        3. Нажмите кнопку Показать.

Правка сценария

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

        1. Выберите команду Сценарии в меню Сервис.

        2. Выберите название изменяемого сценария, а затем нажмите кнопку Изменить.

        3. Внесите требуемые изменения.

        4. Введите необходимые значения в диалоговом окне Значения ячеек сценария.

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

        o чтобы сохранить изменения, нажмите кнопку OK;

        o чтобы вернуться в диалоговое окно Диспетчер сценариев без изменения текущего сценария, нажмите кнопку Отмена .

Объединение сценариев с других листов

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

        1. Откройте все книги, содержащие сценарии, которые требуется объединить.

        2. Перейдите на лист, на котором требуется объединить сценарии.

        3. Выберите команду Сценарии в меню Сервис.

        4. Нажмите кнопку Объединить.

        5. Выберите название книги из списка Книга .

        6. В поле Лист щелкните имя листа, содержащего сценарии, которые требуется объединить, и нажмите кнопку OK.

        7. Если требуется объединить сценарии с нескольких листов, повторите эту процедуру.

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

Последнее изменение: Четверг, 11 августа 2011, 12:32