Санкт-Петербургский национальный исследовательский университет информационных технологий, механики и оптики Центр авторизованного обучения IT-технологиям

Методические материалы по курсу

Microsoft Excel 2007 для аналитиков

(практика)

Cанкт-Петербург

2011

СОДЕРЖАНИЕ

Лабораторная работа 1: Анализ данных с помощью сценариев “ЧТО-ЕСЛИ” 4

Лабораторная работа 2: Расчет характеристик марковских процессов 10

Лабораторная работа 3: Решение задач линейной оптимизации 14

Лабораторная работа 4: Изучение законов распределения случайных величин 18

Лабораторная работа 5: Генерация случайных чисел и анализ выборки данных 23

Лабораторная работа 6: Анализ временных рядов 29

Лабораторная работа 7: Регрессионный анализ 34

Лабораторная работа 1: Анализ данных с помощью сценариев “ЧТО-ЕСЛИ”

Цель работы

Изучить средства программы MicrosoftExcelдля анализа данных с помощью сценариев и таблиц подстановки.

Теоретические основы

Во многих случаях возникает необходимость просматривать различные варианты значений в ячейках на листе. Для выполнения анализа “что-если” Excelпредоставляет следующие возможности:

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

• Таблицы подстановки.

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

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

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

В Excel2003: выполните команду Сервис (Tools) – Сценарии (Scenarios).

В Excel2007: вкладка Данные, группа Работа с данными, кнопка Анализ “что-если”, команда Диспетчер сценариев…

В результате откроется окно диалога (рис.1.1):

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

В окне Диспетчер сценариев (Scenario Manager)следует нажать кнопкуДобавить (Add). Отобразится окноДобавление сценария (Add Scenario), изображенное на рис. 1.2.

Рис. 1.2 Добавление сценариев

Далее указывается имя сценария, ячейки, в которых записаны изменяемые данные.

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

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

В обоих случаях используется одно диалоговое окно (см. рис. 1.3), для открытия которого следует выполнить следующие действия:

В Excel2003: выполните команду менюДанные (Data) – Таблица подстановки…(Table)

В Excel2007: вкладкаДанные, группаРабота с данными, кнопкаАнализ “что-если”, командаТаблица данных…

Рис.1.3 Окно диалога для применения таблиц подстановки

В случае одного входа данные подставляются либо по столбцам (если данные располагаются в строках), либо по строкам (если данные располагаются в столбце).

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

Содержание работы

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