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

В предыдущем примере команда Поиск решения определила, что для получения выручки в 2000 р. необходимо продавать 115 чашек кофе с шоколадом, 221 чашку со сливками и 135 чашек обычного кофе. Но уровня в 2000 р. можно добиться и при другом составе -- например, продавать примерно 122 чашки кофе с шоколадом, 225 чашек со сливками и 112 чашек обычного кофе. Как же команда Поиск решения определяет оптимальное соотношение переменных? По­скольку мы не накладывали ограничений на выбор, команда просто начала работу с чисел в переменных ячейках и увеличивала их до тех пор, пока не было найдено приемлемое решение. По этой причине в нелинейных задачах при различных наборах начальных условий можно получить различные решения.

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

Анализ «что-если» с помощью Диспетчера сценариев

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

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

Сценарием называется именованная модель «что-если», в которую входят пере­менные ячейки, связанные одной или несколькими формулами. Перед созданием сценария необходимо спроектировать лист так, чтобы на нем была ем сценария необходимо спроектировать лист так, чтобы на нем была хотя бы одна формула, зависящая от ячеек, которые могут принимать различные значения. Например, может возникнуть потребность в сравнении лучшего и худшего сценариев для недельных продаж кофе в кафетерии. На рис. 23.6 изображен лист с тремя переменными ячейками и несколькими формулами, который может послужить основой для создания нескольких сценариев (если вы внимательно работали с примером для команды Поиск решения, то тема с кафетерием окажется вам знакомой). В следующем примере этот лист будет использоваться для создания сценариев продажи Лучший вариант и Худший вариант.

Рис.6.

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

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

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

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

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

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