Изменение условий оптимизационных задач

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

Изменение условий задачи для получения определенного целевого значения происходит следующим образом:

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

2. Установите в группе Равной переключатель Значению и введите нужную величину. Данный переключатель.для целевой ячейки конкретное числовое значение, а поиск решения состоит в определении набора переменных, при которых это значение будет, достигнуто.

3. Щелкните на кнопке Выполнить, чтобы начать поиск ответа. После завершения работы щелкните на кнопке ОК, чтобы занести найденное решение в ячейки листа.

 

Что делать, если задача имеет несколько решений?

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

Это свойство можно обратить себе на пользу, если хочется увидеть в решении определенное сочетание переменных. Просто перед выполнением команды необходимо задать в переменных ячейках те значения, которые бы вас устроили, и Excel при поиске решения начнет именно с них. Если же необходимо найти настоящее оптимальное решение, следует перед поиском указать в диалоговом окне Поиск решения дополнительные ограничения. Например, можно задать по каждой категории определенное минимальное значение. Для каждой переменной ячейки можно задать по два ограничения (верхняя и нижняя границы значения) и таким образом повлиять на выбор решения.

 

3. АНАЛИЗ “ЧТО-ЕСЛИ” С ПОМОЩЬЮ ДИСПЕТЧЕРА СЦЕНАРИЕВ

 

Команды Подбор параметра и Поиск решения очень полезны, однако после решения нескольких задач легко забыть, какими же были исходные значения. Еще важнее то обстоятельство, что при работе с этими командами не существует удобного способа сравнения результатов - при каждом изменении данных предыдущее решение пропадает. Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями “что-если”, Командой Сценарии из меню Сервис можно создавать новые и просматривать существующие сценарии для решения задач, работать с общими командами управления и отображать консолидированные отчеты.

 

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

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

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

 

Создание сценария происходят следующим образом:

1. Выполните команду Диспетчер сценариев из меню Данные (раздел Работа с данными). Открывается диалоговое окно Диспетчер сценариев.

2. Щелкните нa кнопке Добавить, чтобы создать первый сценарий. Открывается диалоговое окно Добавление сценария.

3. Введите Лучший вариант (или любое подходящее имя) в поле Название сценария и нажмите клавишу Tab.

4. В поле Изменяемые ячейки укажите те переменные ячейки, которые изменяются в вашем сценарии. Это можно сделать несколькими способами - ввести ссылки с клавиатуры, выделить диапазон на листе или, удерживая нажатой клавишу Ctrl, щелкать мышью на отдельных ячейках (в последнем случае Excel автоматически разделяет ссылки на ячейки запятыми).

СОВЕТ. Пои задании переменных ячеек можно пользоваться именами. Это помогает распознавать их при создании сценария и при последующем вводе аргументов.

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

6. Щелкните по кнопке Добавить, чтобы добавить второй сценарий и т.д.

7. После добавления последнего сценария нажмите ОК. Открывается диалоговое окно Диспетчер сценариев, в котором присутствуют сценарии Лучший вариант и Худший вариант. Теперь мы готовы к просмотру результатов.

8. Закройте диалоговое окно Диспетчер сценариев, щелкнув на кнопке Закрыть.

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

 

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

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

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

1. Выполните команду Сценарии из меню Сервис. Открывается диалоговое Диспетчер сценариев.

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

3. Щелкните на кнопке Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе,

4. Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей “что-если”. После завершения щелкните на кнопке Закрыть. Значения последнего активною сценария остаются в ячейках листа.