Сценарный подход к анализу средствами Excel 2010

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

Черняков Михаил Константинович прислал целую серию статей, первой из которых я и делюсь сегодня.

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

1. Активировать Диспетчер сценариев можно с помощью команды Данные - Работа с данными - Анализ «Что если» - Сценарии. После выполнения этой команды раскроется диалоговое окно Диспетчер сценариев.

image

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

image

3. В поле Название сценария можно изменить имя сценария. В поле Изменяемые ячейки можно изменить адреса либо имена ячеек, в которых находятся исходные данные для сценария (эти ячейки не обя­зательно должны быть смежными; если ячейки не смежные, их адреса или имена нужно вводить через точку с запятой). Для одного сценария допускается задавать не более 32 изменяемых ячеек. В нашем примере в качестве изменяемых заданы ячейки В2:В6.

4. В поле Примечание автоматически появляются сведения об авторе и дате создания сценария. При необходимости сюда можно ввести дополнительную информацию.

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

image

6. Заполните эти поля и щелкните на кнопке ОК, чтобы вернуться к диалоговому ок­ну Диспетчер сценариев (рис.1). В нем отобразится имя созданного сценария.

7. Если вы хотите отобразить на экране результаты рас­четов для какого-либо одного сценария, выберите его название в диалоговом окне Диспетчер сценариев и щелкните на кнопке Вывести.

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

9. Если требуется создать итоговый отчет по сценариям, щелкните на кнопке Отчет и в раскрывшемся диалого­вом окне Отчет по сценарию укажите тип отчета (структурированный список либо сводная таблица), а также задайте ячейки результата. Ячейки результата - это ячейки, в которых находятся формулы, результаты которых вам нужно проанализировать. Для нашего примера зададим ячейку В7.

image

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

image

В этом отчете будут представлены изменяемые значе­ния для разных сценариев (область Изменяемые), а также значения формул, вычисленные на основе изменяемых значений (область Результат).

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

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

image

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