ЛАБОРАТОРНАЯ РАБОТА № 5 Статистическая обработка исходных данных
В данной лабораторной работе используется статистический пакет Анализ данных. Доступ к пакету осуществляется командой: Сервис→Анализ данных...
Если команда Анализ данных отсутствует в меню Сервис, то пакет надо установить. Для этого выберите команду: Сервис→Надстройки... А затем в списке Доступные надстройки установите флажок Анализ данных и щелкните на кнопке ОК. Генерация случайных чисел
Пакет анализа данных позволяет генерировать случайные числа с различными распределениями: Равномерное, Нормальное, Пуассона и др.
1. Получите выборку, содержащую 1000 случайных нормально распределенных чисел. Для этого с помощью команды: Сервис→Анализ данных... откройте пакет анализа данных и в диалоговом окне из списка Инструменты анализа выберите инструмент Генерация случайных чисел. Откроется диалоговое окно.
В диалоговом окне в поле Распределение задайте распределение Нормальное. Диалоговое окно примет вид, показанный на рис. 5.1.
Задайте параметры для генерации 1000 нормально распределенных случайных чисел, как показано на рис. 5.1:
а) число переменных – 1;
б) число случайных чисел – 1000;
в) среднее – 100;
г) стандартное отклонение – 2;
д) выходной интервал (абсолютный адрес ячейки, начиная с которой будут размещаться случайные числа) – $А$1.
Рис. 5.1 Щелкните на кнопке ОК. Случайные числа разместятся в диапазоне ячеек А1:А1000.
4. Просмотрите основные характеристики распределения. Для этого с помощью команды: Сервис→Анализ данных... откройте пакет анализа данных и в диалоговом окне из списка Инструменты анализа выберите инструмент Описательная статистика. Откроется диалоговое окно, как показано на рис. 5.2.
Рис. 5.2 5. В диалоговом окне задайте следующие параметры:
а) входной интервал – $А$1:$А$1000;
б) выходной интервал (абсолютный адрес ячейки, начиная с которой будут размещаться случайные числа) – $D$1.
Установите флажок Итоговая статистика. Щелкните на кнопке ОК. Статистические данные разместились на рабочем листе (рис. 5.3).
Обратите внимание. Итоговая статистика с достаточной степенью точности подтвердила заданные характеристики нормального распределения (а = 100; σ = 2).
Рис. 5.3
Определение объема выборки и построение гистограммы
Из полученного массива данных (1000 случайных чисел) определим выборку с минимальным объемом n, которая будет удовлетворять условиям дальнейшего исследования.
6. Выделите ячейку В1 и введите формулу, используя мастер функций fх:
=ЕСЛИ(А1>$E$5;1;-1).
Замечание. В ячейке с абсолютным адресом $E$5 находится значение медианы.
7. Выделите ячейку В1 и нажмите клавишу F5. В диалоговом окне Переход в текстовом поле Ссылка: введите диапазон ячеек В1:В150. Щелкните на кнопке ОК. Диапазон ячеек В1:В150 будет выделен.
8. Скопируйте формулу из ячейки В1 в другие ячейки выделенного диапазона. Для этого выберите команду: Правка → Заполнить → Вниз.
9. В столбце В серии со знаком «-1» выделите рамкой, как показано на рис. 5.4. Для этого используйте инструмент Границы на Панели форматирования.
Рис. 5.4 10. Создайте таблицу в диапазоне ячеек G2:K4 (рис. 5.4). Ячейки в четвертой строке G4:K4 заполняются следующим образом:
а) ячейке G4 присваивается имя n с помощью команды: Вставка → Имя → Присвоить….
Затем в ячейку с именем n вводится небольшой объем выборки (например, n = 30). Это значение понадобится в расчетных формулах;
б) в ячейку I4 введите формулу:
= ЦЕЛОЕ((n+1-1,96*КОРЕНЬ(n-1))/2);
в) в ячейку К4 введите формулу:
=ЦЕЛОЕ (3,3*LOG10(n+1));
г) в ячейку J4 введите из видимой части исходных данных фактическую длину самой длинной серии τ. В нашем примере эта серия состоит из знаков «-1» и находится в диапазоне В8:В13(τ = 6).
Теперь проверяем условие получения независимых данных: если значение в ячейке К4, рассчитанное по формуле, окажется меньше τ, то необходимо увеличить выборку n (например, взять n = 40) и опять повторить проверку условия. Эти действия повторяются до тех пор, пока не окажется, что значение в ячейке К4 больше τ. В нашем примере это условие выполнилось при n = 70. В этом случае значение в ячейке К4 оказалось равным 6 (больше τ).
Обратите внимание. Данные в ячейках с формулами (I4, K4) пересчитываются автоматически;
д) в столбце В1 подсчитайте число серий ν для полученной выборки n = 70. Для этого надо определить число серий, выделенных рамкой, и результат увеличить в 2 раза, чтобы учесть серии со знаком «1». Полученное значение занесите в ячейку Н4. В нашем примере число серий ν = 72.
Проверяем второе условие получения независимых данных: значение в ячейке Н4 должно быть больше значения в ячейке I4, рассчитанное по формуле. Если условие не выполняется, то надо увеличить объем выборки n. В нашем примере при n = 70 это условие выполняется (ν = 72 > 27).
Таким образом, полученную выборку из 70 независимых наблюдений можно использовать для дальнейших исследований.
11. Выделите диапазон выборки А1:А70 и скопируйте его на Лист 2 также в диапазон А1:А70.
12. Постройте гистограмму для выборки. Для этого с помощью команды: Сервис → Анализ данных… откройте пакет анализа данных и в диалоговом окне из списка Инструменты анализа выберите инструмент Гистограмма. Откроется диалоговое окно (рис. 5.5).
Рис. 5.5 13. В диалоговом окне задайте следующие параметры:
а) входной интервал – $А$1:$А$70;
б) выходной интервал – $D$1.
Установите флажок Вывод графика. Щелкните на кнопке ОК. Exсel создает диаграмму и записывает анализ в столбцы D и Е, как показано на рис. 5.6.
Замечание. Поле Интервал карманов можно оставить пустым. Программа EXEL сама создаст равные интервалы разбиения, используя минимальное и максимальное значение во входном диапазоне в качестве начальной и конечной точек. Число интервалов принимается равным √n.
14. Расположите и отформатируйте гистограмму, как показано на рис. 5.6.
Рис. 5.6 Замечание. При форматировании отдельных элементов диаграммы удобно вызывать диалоговые окна форматов, используя контекстное меню.
15. Добавьте линию тренда, вид которой с достаточной степенью точности отображает кривую плотности вероятности нормального распределения. Для этого щелкните правой кнопкой на элементе диаграммы Ряд «Частота» и выберите команду Добавить линию тренда….
В появившемся диалоговом окне выделите тип линии тренда Линейная фильтрация и установите с помощью счетчика Точки: интервал усреднения равным 3. Щелкните на кнопке ОК. Метод скользящего среднего
16. Выделите диапазон выборки А1:А70 и скопируйте его на Лист 3 также в диапазон А1:А70.
17. Внесите следующие изменения («выбросы») в исходные данные: в ячейку А10 занесите число 150, в ячейку А20 – число 60.
18. Выберите команду: Сервис → Анализ данных….
19. В диалоговом окне пакета из списка Инструменты анализа выберите инструмент Скользящее среднее. Откроется диалоговое окно (рис. 5.7).
Рис. 5.7 20. В диалоговом окне задайте следующие параметры:
а) входной интервал – $А$1:$А$70;
б) интервал – 6 (интервал усреднения);
в) выходной интервал – $В$1.
Установите флажок Вывод графика. Щелкните на кнопке ОК. Exсel возвращает выходные данные в столбце В и создает диаграмму.
21. Расположите и отформатируйте диаграмму, как показано на рис. 5.8.
В построенной диаграмме хорошо видно, как линия тренда (Прогноз) сглаживает линию исходных данных (Фактический).
Обратите внимание. В выходном диапазоне (столбец В) первые пять ячеек содержат ошибочное значение #Н/Д, так как при построении сглаживающей кривой с интервалом усреднения, равным 6, первые пять значений отсутствуют.
Рис. 5.8 Таким образом, окончательно для исследования принимается выборка (n = 65) с данными, полученными после сглаживания (столбец В).
|