Диспетчер сценариев

Таблицы данных — хорошее средство, когда в расчетах принимает  участие не больше двух переменных. Но реальные ситуации часто сложнее, и тогда вам на помощь придет другое эффективное средство Excel — диспетчер сценариев. Этот инструмент позволяет моделировать  задачи,  оперирующие  не  одной  или  двумя  переменными,  а  тридцатью двумя!  Чтобы  запустить  диспетчер  сценариев,  выберите  в  меню   Сервис  команду Сценарии (Scenarios).

Договоримся, что сценарием (scenario) мы будем называть набор значений, заданных для одной или нескольких изменяемых ячеек в модели «что-если». На рис. 18.8 представлен рабочий  лист,  на  котором  реализована  указанная  модель,  и  показано,  как  изменение значений  переменных влияет на конечный результат вычислений. Диспетчер  сценариев записывает,  отслеживает  и  применяет  все  изменения  переменных  величин  в  любых комбинациях.

позволяет:

? создать несколько сценариев для одной модели «что-если», каждый из которых может иметь собственный набор переменных;

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

Рис. 18.8. Здесь продемонстрировано использование диспетчера сценариев в рамках экономической задачи при изменении значений в диапазонах D2:D3, D5 и Е8:Е13

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

? печатать отчеты с подробной информацией обо всех изменяемых и итоговых ячейках;

? защищать сценарии от изменений посредством пароля и даже скрывать их;

? сравнивать между собой сценарии, созданные разными  пользователями, с помощью инструментов   Структура   сценария   (Scenario   Summary)   или   Сводная   таблица (Scenario  PivotTable).  Последний  подробно  рассматривается  в  главе  30  «Анализ данных при помощи сводных таблиц».

Чтобы  понять,  как  работает  диспетчер  сценариев,  представьте,  что  вы  —  директор небольшого магазинчика и для оценки его  коммерческой деятельности создали модель расчетов,  представленную  на  рис.  18.8.  Числа  в  диапазонах  D2:D5 и  Е8:Е13 —  это средние  данные  за  последний  год  работы  магазина.  Требуется  узнать,  как  изменение данных параметров отразится на прибыли, то есть на значении в ячейке Е16.

СОВЕТ

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

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

Чтобы срздать новый сценарий, выполните следующие действия:

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

2.  В открывшемся диалоговом окне (Scenario  Manager), пока-

занном на рис. 18.9, щелкните на кнопке Добавить (Add).

Рис. 18.9. Диалоговое окно

3.   В диалоговом окне Добавление сценария (Add Scenario),  изображенном на рис. 18.10,  укажите  имя  для  нового  сценария.  Обратите  внимание,  что,  как  только  вы начнете  вводить  в   следующее  поле  этого  окна  ссылки  на  изменяемые  ячейки, диалоговое окно станет называться Изменение сценария (Edit Scenario).

СОВЕТ

Имя нового сценария лучше всего составить из названий параметров,  то  есть величин, которые будут фигурировать в этом сценарии в  качестве переменных. Например, можно назвать  сценарий  Начальные  значения  или  Прошедший  год,  как  это  сделано  в  представленном примере. В противном случае велика вероятность, что вы очень скоро забудете, к чему относятся произведенные расчеты, и вряд ли вспомните все предпосылки, на которых была построена модель «что-если».

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

4.   В поле Изменяемые ячейки (Changing Cells) укажите ячейки, содержимое которых планируется  варьировать.  Ссылки  на  эти  ячейки  можно  ввести  вручную  или  же выделив  ячейки  с  помощью  мыши.  Чтобы  выделить  мышью  несмежные  ячейки  и диапазоны, удерживайте при выделении клавишу Ctrl. При вводе с клавиатуры между ссылками помещайте символ точки с запятой, как показано на рис. 18.10.

5.   Нажмите  кнопку  OК,  чтобы  создать  первый  сценарий.  Откроется  диалоговое  окно Значения  ячеек  сценария  (Scenario  Values)  с  полями  для  каждой  изменяемой ячейки. Если этим ячейкам присвоены имена, они будут отображаться рядом с полями, как на рис. 18.11. В противном случае в качестве имен будут выступать ссылки на эти ячейки.

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

6.   Здесь при желании можно изменить все представленные величины. В примере мы оставили все как есть. Щелкните на кнопке ОК.

СОВЕТ —-

В поля диалогового окна Значение ячеек сценария допускается помещать как числа, так и формулы. Например, чтобы на рис. 18.11 увеличить  значение первой переменной, нужно щелкнуть  на  соответствующем  ей  поле  и  ввести  перед  ссылкой  формулу  =1,1*.  Таким образом, текущее значение ячейки будет масштабировано с коэффициентом 1,1. Заметьте, что, хотя ввод формул в поля диалогового окна и разрешен, после нажатия кнопки ОК эти формулы будут заменены их возвращаемыми значениями, о чем Excel предупредит вас.

7.   Чтобы создать другой сценарий, вновь нажмите кнопку Добавить и повторите шаги 36.

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

Выделите имя сценария в списке диалогового окна Диспетчер  сценариев и нажмите кнопку Вывести (Show). Программа заменит  исходные значения переменных рабочего листа  их  значениями,  заданными  при  создании  выбранного  сценария.  На  рис.  18.12 показано,  как  выглядит  рабочий  лист  со  сценарием,  по  которому  среднее  количество посетителей магазина увеличится на 5%, но доходы от каждого покупателя станут меньше на те же 5%.

Рис. 18.12. При просмотре выбранного сценария диспетчер заменяет текущие значения рабочего листа значениями, заданными при создании выбранного сценария

При  просмотре  сценариев  с  помощью  кнопки  Вывести  диалоговое  окно  диспетчера остается  на  экране,  поэтому  сравнить  результаты  с  таковыми  для  других  сценариев можно, не возвращаясь на рабочий лист. Если нажать кнопку Закрыть или клавишу Esc, чтобы закрыть  диалоговое  окно, на рабочем листе останутся величины, подставленные для последнего просматриваемого сценария. (Именно по этой причине  стоит сохранять исходный вариант, чтобы всегда оставалась возможность к нему вернуться.)

Добавление, редактирование и удаление сценариев

В  момент  сохранения  книги  сценарии  записываются  вместе  со   всеми   остальными данными рабочего листа, при этом каждый лист  книги может содержать собственный набор  сценариев.  Чтобы   добавить   в  книгу  новый  сценарий,  как  уже  говорилось, достаточно  щелкнуть на кнопке Добавить в диалоговом окне и повторить  описанную  выше  процедуру  создания  сценария.  А  когда  требуется  внести изменения  в  существующий  вариант,  выберите  в  этом  же  диалоговом  окне  команду Изменить  (Edit) — на экране появится диалоговое окно Изменение сценария  (Edit Scenario),   идентичное   диалоговому   окну   Добавление   сценария.   Здесь   можно отредактировать  название  выбранного   сценария,  добавить  или  удалить  изменяемые ячейки. Чтобы удалить  сценарий, выберите его имя в списке сохраненных сценариев в диалоговом окне и щелкните на кнопке Удалить (Delete).

Отслеживание авторства изменений

Когда  вы  модифицируете  сценарий,  в  поле  Примечание  (Comment)  диалогового  окна Диспетчер  сценариев  Excel  после  строки  Автор  добавляет  строку  Автор  изменений.

Благодаря этому легко в любой момент не только узнать имя создателя  сценария, но и проследить, кто и когда вносил в него правку. Данная  информация особенно полезна, если модели расчетов передаются другим пользователям с тем, чтобы после совместной работы над проектом объединить все сценарии в одну общую модель «что-если».

ПРИМЕЧАНИЕ

При  редактировании  сценариев  поле  Примечание  также  доступно  для  изменения;  эти модификации отражаются на всех диалоговых окнах, а  также на датах создания и редактирования  сценария.  Такое   воздействие  может  быть  нежелательным,  особенно  когда требуется  отслеживать  все  изменения  или  необходимо  предотвратить  несанкционированные вмешательства. В этом случае нужно принять  меры по защите элементов документа, которые подробно описаны в разделе «Защита данных» главы 5.

Объединение сценариев

Если часть вашей работы состоит в разработке моделей «что-если» или в планировании деятельности организации, то вам, несомненно,  приходится тратить много времени на сбор воедино полезной информации от своих коллег, а также об изменениях и тенденциях на  рынке, которые в будущем способны оказать влияние на деятельность  и развитие фирмы.   Чтобы   составить   более   полную   картину,    необходимо   опросить   немало сотрудников,   представляющих    различные   стороны   деятельности   компании.   Для облегчения   такого   рода   работы   Excel   предлагает   две   возможности   —   рассылку документов и объединение сценариев.

Если компьютер подключен к сети с совместимой системой электронной почты, значит, вы   можете   организовать   рассылку   текущей   книги    своим   коллегам.   Для   этого присоедините ее к электронному письму,  выбрав в меню Файл команду Отправить ? По маршруту (Send To ? Routing Recipient). К совместимым системам электронной почты  относятся Microsoft Outlook, Microsoft Mail, Lotus cc:Mail и другие  приложения, работающие по стандарту MAPI (Messaging Application  Programming Interface) или VIM (Vendor   Independent   Messaging).    Также    для   рассылки   подходит   сеть   Интернет, позволяющая пересылать документы как вебстраницы или через FTP-сайты.

СМОТРИ ТАКЖЕ

Подробнее об организации рассылки по маршруту будет сказано в главе 19, использование Интернета при работе в Excel описывается в главе 20.

Если компьютер не подключен к сети, можно просто создать электронные копии книги и распространить их среди коллег старым надежным способом — на гибких дисках. Предположим,   вы   хотите   распространить   модель   «что-если»   между   несколькими

сослуживцами: Ивановой, отличным специалистом по работе с  клиентами, Крыловой,

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

именованных  копий  для  каждого  сотрудника.  После  того  как  все   стороны  внесли изменения в модель или добавили свои собственные  таблицы «что-если», необходимо слить все сценарии в один. Для этого откройте все книги, содержащие нужные сценарии, активизируйте лист,  на  котором будут храниться результаты объединения, и нажмите кнопку  Объединить (Merge )  в диалоговом  окне  Диспетчер  сценариев.  На  экране появится диалоговое окно Объединение сценариев (Merge Scenarios), представленное на рис. 18.13.

Рис. 18.13. Диалоговое окно Объединение сценариев позволяет импортировать сценарии из любого листа в любую открытую книгу

СОВЕТ

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

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

Обратите внимание, что в поле Примечание (Comment) в нижней  части диалогового окна отображается имя создателя (и автора изменений) выделенного сценария. Заметьте также, что на рис. 18.14  список Сценарии (Scenarios) содержит сценарии со схожими именами.  Это произошло потому, что в данном примере все сотрудники назвали  свои модели одинаково. В такой ситуации, чтобы избежать путаницы, к  каждому названию программно добавляется дата создания сценария и  порядковый номер на случай, если сценарии созданы в один день.  Любой из них всегда доступен для переименования с помощью кнопки Изменить (Edit).

В  каждой  группе  сценариев,  предоставленной  сотрудниками,  установлены  различные изменяемые ячейки. На листе Ивановой — это ячейки D2, D3 и D5, Крыловой — Е8, а Андреевой  —  Е11.  Теперь,  объединив  сценарии,  можно  оценить  влияние  различных комбинаций переменных величин на перспективы компании.

Рис. 18.14. Объединенные сценарии появляются на текущем листе

СОВЕТ

Проще и быстрее всего получить доступ к сценариям — воспользоваться кнопкой Сценарии, которую предварительно нужно добавить на панель  инструментов. Для этого в меню Вид выберите  команду  Панели  инструментов  ?  Настройка.  Перейдите  на  вкладку  Команды (Commands)  и  выберите  в  списке  категорию  Сервис  (Tools).  Найдите  в  правой  части диалогового окна Настройка (Customize) команду Сценарии (Scenario) и перетащите ее на любую  видимую  панель   инструментов.  В  раскрывающемся  списке  отображаются  все сценарии  текущего рабочего листа, и чтобы обратиться к какому-либо из них,  достаточно выбрать в списке его название.

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

После  объединения  сценариев  изначально  простая  модель  в  нашем   примере  стала достаточно  сложной.  Однако  вы  можете  создавать  и   еще  более  сложные  модели, включающие в себя любое количество сценариев (конечно, все зависит от возможностей компьютера), имеющих до 32 переменных в каждом! предлагает два типа  отчетов:  отчет  Структура  (Scenario  Summary),  в  котором  отображаются  все возможные пути развития модельной ситуации, и  отчет Сводная таблица (PivotTable Report)  с  набором  дополнительных  инструментов  для  работы  с  элементами  самого отчета.

Если щелкнуть на кнопке Отчет (Summary) в диалоговом окне , на  экране  появится  диалоговое  окно  Отчет по  сценарию (Scenario Summary),  показанное на рис. 18.15. С его  помощью создаются отчеты, содержащие информацию о значениях,  заносимых по каждому из сценариев в изменяемые ячейки. В поле  Ячейки результата  (Result  Cells)  диалогового   окна   через   запятую   указываются   ячейки,

в   которых  будут  отображаться  результаты  при  определенных  значениях  переменных сценария. В нашем случае в качестве места для результата выбраны ячейки Е1б (Чистая прибыль) и Е6 (Общая прибыль).

Рис. 18.18. Вместе со сводной таблицей по отчету на экран выводится и панель инструментов Сводные таблицы

СОВЕТ

Отчет Сводная таблица лучше всего подходит для работы со сложными моделями с множеством параметров. Создание такого отчета занимает больше времени и требует больше памяти, чем создание отчета Структура. Если вы формируете все сценарии самостоятельно и в каждом из них  сохраняете одно и то же множество изменяемых ячеек, то удобнее воспользоваться вариантом Структура, так как_в этом случае у вас все равно не получится оценить все достоинства отчета Сводная таблица.

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий