4. Ход работы

1. В книге лабораторных работ по математической статистике Листу1 присвойте оригинальное имя, например, «Первичный анализ данных».

2. Получите выборку из 25 случайных чисел, распределенных по нормальному закону, с математическим ожиданием равным 5, и стандартным отклонением равным 1, используя набор статистических функций Excel.

• Так как Excel не имеет функции генерации случайного числа, распределенного по нормальному закону, воспользуемся функцией СЛЧИС(), которая возвращает случайное число, имеющее равномерное распределение из интервала (0; 1). Запишем ее в ячейки столбца А (А1:А25).

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

• Полученные числа будут являться значениями интегральной функции стандартного нормального распределения для чисел, которые можно получить следующим образом: выбрать статистическую функцию НОРМСТОБР(А1). Эта функция возвращает обратное значение стандартного нормального распределения по известным значениям интегральной функции стандартного нормального распределения. НОРМСТОБР использует метод итераций для вычисления функции. Если задано значение интегральной функции стандартного нормального распределения, то функция НОРМСТОБР производит итерации, пока не получит результат с точностью ± 3x10^-7. Если НОРМСТОБР не сходится после 100 итераций, то функция возвращает значение ошибки #Н/Д.

• Чтобы получить нормально распределенные случайные числа, имеющие математическое ожидание равное 5, в столбце В (диапазон В1:В25) в качестве аргумента функции НОРМСТОБР укажем значение соответствующих ячеек столбца А: НОРМСТОБР(А1)+5.

Получите выборку с указанными параметрами математического ожидания и стандартного отклонения с помощью надстройки Excel–Анализ данных… из меню Сервис. Результат поместите в отдельные ячейки рабочего листа.

• Вызовитедиалоговое окно Анализ данных. ИзИнструментов анализа выберите пункт Генерация случайных чисел (рис. 1.1).

• Чтобыполучить 1 столбец случайных чисел, укажите в поле Числопеременных: 1. Если это число не введено, то все столбцы в выходном диапазоне будут заполнены (рис. 1.2).

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

• Укажите из выпадающего списка поля Распределение: Нормальное. Задайте параметры распределения: Среднее = 5, Стандартное отклонение = 1.

• В разделе Параметры вывода введите ссылку на левую верхнюю ячейку выходного диапазона, ниже на текущем листе. Размер выходного диапазона будет определен автоматически, и на экран будет выведено сообщение в случае возможного наложения выходного диапазона на исходные данные (рис. 1.2).

Рис. 1.1. Диалоговое окно Анализ данных	Рис. 1.2. Генерация случайных чисел

4. Далее найдите точечные выборочные оценки математического ожидания и среднеквадратического отклонения, используя встроенные статистические функции Excel.

• Вычислите среднее арифметическое для полученных чисел – функция СРЗНАЧ(B1:B25) и медиану – функция МЕДИАНА(B1:B25), где диапазон B1:B25 содержит значения выборки, полученной по п.2. Значения этих функций будут близки к заданному нами математическому ожиданию.

• Найдите оценку дисперсии по выборке – ДИСП(B1:B25), и оценку стандартного отклонения равную квадратному корню из оценки дисперсии – СТАНДОТКЛОН(B1:B25).

• При расчете оценок дисперсии функцией ДИСП и стандартного отклонения функцией СТАНДОТКЛОН логические значения, такие, как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Сравните полученное значение оценки СКО с заданным.

Найдите размах выборки – интервал значений выборки, равный выражению ABS(МАКС(В1:В25)-МИН(В1:В25)), гдедиапазон B1:B25 содержит значения выборки, полученной по п.2.

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

• Выполните команду Сервис-Анализ данных…В появившемся диалоговом окне средиИнструментов анализа выберите пунктОписательная статистика(рис. 1.3).

• В поле Входной интервал: укажите диапазон значений выборки, например диапазон B1:B25. В разделе Группирование установите переключатель в положение По столбцам, так как в нашем случае значения выборки расположены в столбце.

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

• В разделе Параметры вывода укажите Выходной интервал: введите ссылку на левую верхнюю ячейку выходного диапазона. Этот инструмент анализа выводит два столбца сведений для каждого набора данных. Левый столбец содержит метки статистических данных; правый столбец содержит статистические данные. Состоящий их двух столбцов диапазон статистических данных будет выведен для каждого столбца или для каждой строки входного диапазона в зависимости от положения переключателя Группирование (рис. 1.3).

• Установите флажок в поле Итоговая статистика.

• Установите флажок в поле Уровень надежности, если в выходную таблицу необходимо включить строку для уровня надежности. В поле введите требуемое значение. Например, значение 95 % вычисляет уровень надежности среднего со значимостью = 0,05 (оставить значение по умолчанию).

• Результат работы режима Описательная статистика представлен на рис. 1.4.

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

Дополнительные пояснения:

Используемая в пакете MicrosoftExcelтерминология не вполне корректна с точки зрения математической статистики. Укажем соответствие некоторых терминов пакета и терминов математической статистики.

• Среднее(ячейкаG11) – это среднее арифметическое (выборочное среднее (1.5);

• Стандартная ошибка(ячейкаG12) – оценка СКО среднего арифметического (1.10). Характеризует стандартное отклонение вариантов выборочного среднего от генерального среднего. Стандартная ошибка выборкииспользуется для расчета предельной ошибки выборки(показательУровень надежностина рис. 1.4), которая дает возможность выяснить, в каких пределах находится величина генерального среднего [1].

Предельная ошибка выборки связана со средней ошибкой выборкисоотношением:

,

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

При расчете коэффициента доверия используется функцияСТЬЮДРАСПОБР(см. лаб. работу «Построение гистограммы по результатам прямых многократных измерений»), в которой задается уровень значимости.

• Медиана(ячейкаG13) – выборочная медиана (1.5) или (1.6);

• Значение Моды(ячейкаG14) оказалось неопределенным в связи с тем, что множество наших данных не содержит одинаковых значений. ВExcelимеется функцияМОДА, которая отображает наиболее часто встречающееся значение в интервале данных.

• Стандартное отклонение(ячейкаG15) – оценка СКО;

• Дисперсия выборки(ячейкаG16) – выборочная оценка дисперсии генеральной совокупности;

• Эксцесс(ячейкаG17) – выборочная оценка эксцесса. Эксцесс характеризует так называемую «крутость», т.е. островершинность или плосковершинность распределения. Он может быть рассчитан для любых распределений, но в большинстве случаев вычисляется только для симметричных. Это объясняется тем, что за исходную принята кривая нормального распределения, относительно вершины которой и определяется выпад вверх или вниз вершины эмпирического распределения.

Точное определение эксцесса основано на расчете центрального момента 4-го порядка:

.

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

,

где – объем выборки.

Если , то распределение островершинное, если– плосковершинное.

Следует учесть, что корректное и имеющее смысл значение эксцесса можно получить только для выборок очень большого объема (>5000). Для малых выборок (30) следует предварительно выяснить закон распределения и уже после этого при необходимости рассчитывать эксцесс.

• Асимметричность(ячейкаG18) – выборочная оценка коэффициента асимметрии. Для симметричных распределений математическое ожидание, мода и медиана равны между собой. Чем больше разница между, тем больше асимметрия выборки.

Показатель асимметрии в генеральной совокупности основан на определение центрального момента 3-го порядка:

.

Функция СКОСопределяет величину асимметрии по выборке следующей формулой:

,

где – объем выборки.

• Если , то асимметрия правосторонняя, если– асимметрия левосторонняя.

• Интервал(ячейкаG19) – это размах варьирования;

• Уровень надежности(95 %) – величина, связанная с уровнем значимости.

Сравните значения статистических параметров выборки, полученные обоими способами.