Лабораторная работа № 2

 

Инструменты статистического анализа данных

Цель работы: изучить возможности Пакета анализа и его некоторые инструменты: Генерация случайных чисел, Гистограмма, описательная статистика.

Порядок выполнения работы:

 

1. Загрузка Пакета анализа
2. Параметры инструментов анализа

- Входные данные

- Выходные данные

1. Инструменты статистического анализа

- Генерация случайных чисел (равномерное распределение)(задание 1)

- Описательная статистика (нормальное распределение)(задание 2)

1. Самостоятельная работа (задание 3)

 

1. Пакет анализа

 

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

 

Загрузка Пакета анализа

Пакет анализа без дополнительных установок автоматически не загружается при запуске Excel. Он входит в так называемую Надстройку – набор дополнительных подпрограмм, к которым относятся, например, уже известные вам Мастер диаграмм и Мастер функций. Для загрузки Пакет анализа необходимо:

1) в Основном меню выбрать пункт Сервис;

2) выбрать пункт Надстройки;

3) в появившемся списке Надстроек активизировать переключатель AnalysisToolPak-VBA и нажать ОК.

После этого в меню Сервис добавится пункт Анализ данных. К этому пункту следует обращаться для вызова Пакета анализа.

 

2. Инструменты статистического анализа

Параметры инструментов анализа во многих инструментах анализа есть одинаковые параметры:

Входные данные

Входной интервал. Нужно ввести ссылку на интервал данных рабочего листа, подлежащих анализу. Excel также прелагает группирование входных данных по строкам или столбцам. Если во входной интервал включаются метки (заголовки строк или столбцов данных), необходимо установит флажок Метки,в противном случае Excel выдаст предупреждающее сообщение.

Метки. Если входной интервал не включает меток, снимите флажок Метки. Excel генерирует соответствующие метки данных для выходной таблицы (Строка 1, Строка 2, или Столбец 1, Столбец 2 и т.д.)

Выходные данные

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

Новый рабочий лист. Этот параметр вставляет новый лист в рабочую книгу, где располагается текущий рабочий лист, и вставляет результаты в ячейку А1 нового листа. Используйте поле ввода рядом с параметром для задания имени нового листа.

Новая рабочая книга. Этот параметр создает новую рабочую книгу, добавляет новый рабочий лист и вставляет результаты в ячейку А1 нового листа.

Генерация случайных чисел

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

Для генерации случайных величин необходимо иметь возможность получать последовательность равномерно распределенных случайных чисел, т.е. чисел, которые ведут себя как независимые реализации или выборки случайной величины R, равномерно распределенной на единичном интервале [0,1]. Такие числа получают с помощью генераторов случайных чисел.

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

 

Задание 1

 

В Excel имеется:

a) встроенная функция СЛЧИСЛ (или RAND) для генерации равномерно распределенных случайных чисел в интервале [0,1].

b) Пакет анализа позволяет генерировать случайные числа с различными типами распределений: равномерное, нормальное, Бернулли, биномиальное, Пуассона и дискретное (определенное пользователем).

Окно инструмента Генерация случайных чисел содержит одинаковые параметры:

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

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

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

Параметры. Вводят параметры для выбранного закона распределения.

Случайное рассеивание. В это поле вводится произвольное значение, для которого необходимо генерировать случайные числа. Excel «запоминает» соответствие сгенерированной совокупности случайных чисел введенному произвольному числу. Впоследствии можно снова использовать это значение для получения той же самой совокупности случайных чисел. Если никакое число не будет введено, то то каждая совокупность случайных чисел будет индивидуальна.

1. Ввести заголовок «Равномерное распределение R: a,b» в ячейку А1

2. Ввести название столбца «параметры между 0 и 1» в ячейку А2

3. Сгенерировать совокупность равномерно распределенных случайных чисел объемом 20 для одной переменной в интервале от 0 до 1. Выходной интервал А3.

4. Ввести название столбца «параметры между 1 и 2» в ячейку В2

5. Сгенерировать совокупность равномерно распределенных случайных чисел объемом 20 для одной переменной в интервале от 1 до 2. Выходной интервал В3.

6. По результатам генерирования равномерных случайных чисел (например, таких

Равномерное распределение R: a,b
Параметры между 0 и 1	Параметры между 1 и 2
0,619495224	1,878536332
0,283242286	1,524338511
0,069063387	1,997039705
0,803704947	1,449690237
0,553697317	1,67629017
0,252327036	1,15253151
0,380260628	1,026642659
0,615375225	1,68263802
0,681661428	1,818659017
0,782799768	1,781792657
0,235328227	1,229499191
0,142460402	1,022797327
0,528427992	1,289620655
0,843012787	1,880581072
0,598834193	1,439039277
0,382549516	1,753715629
0,927976318	1,387676626
0,951109348	1,606738487
0,356303598	1,521866512
0,859065523	1,754356517

построить диаграммы рассеяния случайных чисел.

Описательная статистика.

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

- Среднее (статистическую оценку математического ожидания)

- стандартная ошибка (среднего)

- дисперсия выборки

- стандартное отклонение (среднее квадратическое отклонение)

- мода – значение изучаемого признака, повторяющегося с наибольшей частотой

- медиана – значение признака, приходящееся на середину ранжированной (упорядоченной) совокупности

- интервал (размах выборки)

- максимальное значение выборки

- минимальное значение выборки

- асимметрия

- эксцесс

- объем выборки

- сумму всех значений выборки

- уровень надежности (доверительный интервал) (предельную ошибку выборки)

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

Задание 2

1. Ввести заголовок «Нормальное распределение» в ячейку А1

2. Сгенерировать совокупность нормально распределенных случайных чисел объемом 200 для одной переменной с параметрами:

- Среднее (математическое ожидание) M_x=0

- Стандартное отклонение (среднее квадратическое отклонение)

- Выходной интервал А2

3. Сделайте копию листа с числами генеральной совокупности. Разделите всю совокупность сгенерированных чисел на 10 равных частей объемом по 20 чисел и переместите для наглядности в ячейки A2:J21

Нормальное распределение N: m,
-0,03623	0,684004	1,048029	-1,07001	0,965124	1,80713	-0,32844	-0,99631	0,640377	1,488106
0,070552	-0,76959	1,018286	-0,31314	-0,18896	-0,17426	1,295889	-1,29183	-1,52842	-0,18655
1,355756	-1,27302	2,260276	-2,43203	-1,2744	0,696332	1,441372	0,242367	-0,49255	-0,24536
0,580555	-1,31146	0,129529	-0,80929	-0,18234	0,882035	0,206201	2,027573	0,990792	-2,1378
-0,1686	-0,69098	-0,45497	0,084517	0,644797	-0,17193	-0,78293	-0,65747	-1,07804	-0,62004
-0,38808	0,824241	0,877753	0,37475	-0,37147	-1,4132	0,416362	-0,021	2,023407	0,172864
0,119506	-0,06672	1,061235	1,459107	-1,52133	0,085054	-0,56272	-1,60764	0,111113	-2,10584
-0,6533	1,777398	1,829094	-1,29909	-1,07572	0,552889	-2,54702	0,368275	-0,87888	0,159058
-0,57694	1,230494	-1,12232	-0,16394	-0,80463	1,119308	-1,04248	-0,59392	2,058396	1,295889
-1,70334	0,05369	1,466915	0,581822	1,109488	1,299086	-1,4649	2,001389	0,416946	-0,01174
1,057347	-1,42134	0,906452	1,79245	0,026509	-1,56798	2,773013	-0,97431	-0,65216	0,735877
0,836335	-0,06587	-0,43899	-0,10857	0,329009	1,583867	-0,29871	1,849439	0,401973	0,353579
-0,41561	-0,35252	0,450735	-0,68943	1,777025	-0,38593	0,005393	-0,67879	-1,35672	1,393134
0,281398	-1,02254	-0,7069	0,661366	1,143244	-1,26566	0,067255	-0,05453	1,052676	-1,74248
0,237957	1,034086	0,067944	-1,11702	2,012275	1,745984	-0,94856	0,391958	-1,7527	-0,2228
-0,4872	0,63148	1,204173	-0,37828	1,510466	-0,14211	1,975968	0,848443	0,075	0,533119
-0,12074	-1,27475	-1,16242	1,44571	-0,24938	-1,15628	-0,97308	-1,60764	1,307671	-0,66203
0,645456	0,19808	-1,63207	1,191165	-1,62889	-0,51469	0,974796	0,119737	0,762923	1,359795
-0,05369	2,176994	-1,20417	-0,4316	-0,63803	0,683617	-0,06933	0,718239	-1,42535	0,654811
0,813011	-1,40044	0,192076	-0,10334	0,548171	2,323686	-1,78075	-0,90058	-0,84047	-1,29678

 

4. С помощью инструмента Описательная статистика вычислить числовые параметры распределения генеральной совокупности по случайной выборке из нее (использовать лист с генеральной совокупностью в столбце А).

5. входной интервал A22:A121, выходной интервал В1, итоговая статистика, уровень надежности 95%

 

 

Задание 3

1. Вычислить статистические показатели с помощью инструмента Описательная статистика для данных об уровнях безработицы (лаб. Работа №1 задание 2)

- Скопировать лист с данными об уровнях безработицы из книги Лабораторная_работа_1.xls

- Ввести заголовок «Описательная статистика» в ячейку Н2

- Активизировать инструмент Описательная статистика

- Входной интервал – исходные данные для BEL_i и ZVET_i с заголовками столбцов

- Установить флажок Метки в первой строке

- Выходной интервал – Н3

- Отформатировать полученные данные

 

Описательная статистика
BEL(%)		ZVET(%)
Среднее	3,117647	Среднее	6,576471
Стандартная ошибка	0,027433	Стандартная ошибка	0,100926
Медиана	3,1	Медиана	6,5
Мода	3,2	Мода	6,5
Стандартное отклонение	0,113111	Стандартное отклонение	0,416127
Дисперсия выборки	0,012794	Дисперсия выборки	0,173162
Эксцесс	-0,62375	Эксцесс	0,045445
Асимметричность	-0,09451	Асимметричность	-0,38358
Интервал	0,4	Интервал	1,6
Минимум	2,9	Минимум	5,7
Максимум	3,3	Максимум	7,3
Сумма		Сумма	111,8
Счет		Счет

 

2. Сравнить результаты вычисленных показателей. Выделить одинаковым фоном совпадающие значения.

3. Построить диаграмму рассеяния

- Выполнить настройку формата оси Х и оси Y в соответствии с образцом диаграммы.

- Выполнить разбиение диаграммы рассеяния на 4 прямоугольника секущими, проводимыми через точки средних значений уровней безработицы для BEL и ZVET

4. Вычислить значения выборочной ковариации Cov(BEL ), Cov(ZVET), Cov(BEL , ZVET)

- Ввести заголовок «Выборочная ковариация» в ячейку М2

- Активизировать функцию КОВАР

- Ввести диапазон ячеек массиваBEL и ZVET

5. Вычислить значение выборочного коэффициента корреляции R _BEL,ZVET

- Ввести заголовок «Коэффициент корреляции» в ячейку О2

- Активизировать функцию КОРРЕЛ

- Ввести диапазон ячеек массиваBEL и ZVET

 

Выборочная ковариация	Коэффициент корреляции
0,020415225	0,460841876