Сценарный подход к анализу средствами Excel 2010
Всвязи с праздниками, публикация конкурсных статей задержалась – но никто не забыт и ничто не забыто. Поприветствуем следующего участника конкурса.
Черняков Михаил Константинович прислал целую серию статей, первой из которых я и делюсь сегодня.
Многие задачи, решаемые с помощью табличного процесса, требуют анализа данных для прогнозирования и принятия решений, в частности бюджетирование. Автоматизировать процесс выполнения анализа позволяет средство Диспетчер сценариев. Сценарий — это набор значений, которые сохраняются в приложении Excel и могут автоматически подставляться в ячейки листа. Можно создать и сохранить различные группы значений на листе и затем переключаться на любые их этих новых сценариев для просмотра различных результатов.
1. Активировать Диспетчер сценариев можно с помощью команды Данные - Работа с данными - Анализ «Что если» - Сценарии. После выполнения этой команды раскроется диалоговое окно Диспетчер сценариев.
2. В диалоговом окне Диспетчер сценариев нажмите кнопку Изменить. После выполнения этой команды раскроется диалоговое окно Изменение сценария.
3. В поле Название сценария можно изменить имя сценария. В поле Изменяемые ячейки можно изменить адреса либо имена ячеек, в которых находятся исходные данные для сценария (эти ячейки не обязательно должны быть смежными; если ячейки не смежные, их адреса или имена нужно вводить через точку с запятой). Для одного сценария допускается задавать не более 32 изменяемых ячеек. В нашем примере в качестве изменяемых заданы ячейки В2:В6.
4. В поле Примечание автоматически появляются сведения об авторе и дате создания сценария. При необходимости сюда можно ввести дополнительную информацию.
5. Выбрав нужные параметры в диалоговом окне Изменение сценария, щелкните на кнопке ОК. В результате раскроется диалоговое окно Значения ячеек сценария, где отобразятся поля для всех изменяемых ячеек, заданных в предыдущем окне.
6. Заполните эти поля и щелкните на кнопке ОК, чтобы вернуться к диалоговому окну Диспетчер сценариев (рис.1). В нем отобразится имя созданного сценария.
7. Если вы хотите отобразить на экране результаты расчетов для какого-либо одного сценария, выберите его название в диалоговом окне Диспетчер сценариев и щелкните на кнопке Вывести.
8. Для того чтобы внести изменения в сценарий, щелкните на кнопке Изменить и отредактируйте значения изменяемых ячеек.
9. Если требуется создать итоговый отчет по сценариям, щелкните на кнопке Отчет и в раскрывшемся диалоговом окне Отчет по сценарию укажите тип отчета (структурированный список либо сводная таблица), а также задайте ячейки результата. Ячейки результата - это ячейки, в которых находятся формулы, результаты которых вам нужно проанализировать. Для нашего примера зададим ячейку В7.
10. После щелчка на кнопке Отчет в диалоговом окне Диспетчер сценариев программа вставит в рабочую книгу новый лист и отобразит на нем итоговый отчет для созданных сценариев.
В этом отчете будут представлены изменяемые значения для разных сценариев (область Изменяемые), а также значения формул, вычисленные на основе изменяемых значений (область Результат).
Так как предварительно были присвоены имена изменяемым ячейкам и ячейкам результата, то при создании сценариев вводятся не адреса этих ячеек, а их имена, и итоговый отчет выглядит более наглядным и понятным, так как вместо адресов ячеек отображены имена.
Сценарный подход позволяет анализировать любое количество вариантов. Если несколько пользователей хранят определенные сведения в отдельных книгах, которые необходимо использовать в сценариях, можно собрать эти книги и объединить их сценарии. После создания или сбора всех необходимых сценариев можно создать итоговый отчет по сценариям, включающий сведения из них. В отчете по сценариям все сведения сценариев отображаются в одной сводной таблице на новом листе.
Сценарный подход позволяет принимать управленческие решения путем просмотра различных результатов и выбора наиболее эффективного.