Проверка простых гипотез критерием хи-квадрат Пирсона в MS EXCEL. Коэффициент парной корреляции в Excel
Лабораторная работа №6. Проверка гипотезы о нормальном распределении выборки по критерию Пирсона.
Лабораторная работа выполняется в Excel 2007.
Цель работы – дать навыки первичной обработки данных, построении гистограмм, подборе подходящего закона распределения и вычислении его параметров, проверка согласия между эмпирическим и гипотетическим законом распределения по критерию хи-квадрат Пирсона средствами Excel.
1. Формирование выборки нормально распределенных случайных чисел с заданными значениями математического ожидания и среднего квадратического отклонения.
Данные → Анализ данных → Генерация случайных чисел → ОК .
Рис. 1. Диалоговое окно Анализ данных
В появившемся окне Генерация случайных чисел ввести:
Число переменных: 1 ;
Число случайных чисел: 100 ;
Распределение: Нормальное .
Параметры:
Среднее = 15 (математическое ожидание);
Стандартное отклонение = 2 (среднее квадратическое отклонение);
Случайное рассеивание: не заполнять (или заполнить по указанию преподавателя );
Выходной интервал: адрес первой ячейки столбца массива случайных чисел - $ A $1 . ОК .
Рис. 2. Диалоговое окно Генерация случайных чисел с заполненными полями ввода
В результате выполнения операции Генерация случайных чисел появится столбец $ A $1: $A$100 , содержащий 100 случайных чисел.
Рис. 3. Фрагмент листа Excel первых нескольких случайных чисел $A$1: $A$100.
2. Определение параметров выборки, описательные статистики
В главном меню Excel выбрать: Данные → Анализ данных → Описательная статистика → ОК .
В появившемся окне Описательная статистика ввести:
Входной интервал – 100 случайных чисел в ячейках $ A $1: $ A $100 ;
Группирование - по столбцам;
Выходной интервал – адрес ячейки, с которой начинается таблица Описательная статистика - $ C $1 ;
Итоговая статистика – поставить галочку. ОК.
Рис. 4. Диалоговое окно Описательная статистика с заполненными полями ввода.
На листе Excel появится таблица – Столбец 1
Рис. 5. Таблица Столбец 1 с данными процедуры Описательная статистика .
Таблица содержит описательные статистики, в частности:
Среднее – оценка математического ожидания;
Стандартное отклонение – оценка среднего квадратического отклонения;
Эксцесс и Асимметричность – оценки эксцесса и асимметрии.
Приблизительное равенство нулю оценок эксцесса и асимметрии, и приблизительное равенство оценки среднего оценке медианы дает предварительное основание выбрать в качестве основной гипотезы H 0 распределения элементов генеральной совокупности - нормальный закон.
Интервал – размах выборки;
Минимум – минимальное значение случайной величины в выборке;
Максимум – максимальное значение случайной величины в выборке.
В ячейке F 15 - длина частичного интервала h , вычисленная следующим образом:
Число интервалов группировки k в Excel вычисляется автоматически по формуле
где, скобки означают – округление до целой части числа в меньшую сторону.
В рассматриваемом варианте n = 100 , следовательно, k = 11 . Действительно:
Эта формула занесена в ячейку F 15: =($D$13-$D$12)/10
Результаты процедуры Описательная статистика потребуются в дальнейшем при построении теоретического закона распределения.
Оценка соответствия нормальному распределению
Этот метод используется для проверки согласия опытного и теоретического распределения, если число испытаний больше 100.
Суть метода заключается в определении критерия Пирсона (c 2 ) с последующим сравнением полученного значения с теоретическим.
Порядок определения критерия Пирсона:
Определяют среднее значение и среднее квадратическое отклонение. Для расчета критерия Пирсона составляют таблицу (таблице 11).
2. Определяют отношение
3. С помощью специальной таблицы (таблица 12) определяют частоту распределения Y 0 .
Таблица 11
Таблица 12
t | 0,00 | 0,01 | 0,02 | 0,03 | 0,04 | 0,05 | 0,06 | 0,07 | 0,08 | 0,09 |
0,0 0,1 0,2 0,3 0,4 0,5 0,6 0,7 0,8 0,9 1,0 1,1 1,2 1,3 1,4 1,5 1,6 1,7 1,8 1,9 2,0 2,1 2,2 2,3 2,4 2,5 2,6 2,7 2,8 2,9 3,0 3,1 3,2 3,3 3,4 3,5 3,6 3,7 3,8 3,9 | 0,3989 0,2420 0,0544 0,0044 |
4. Рассчитывают теоретическое значение частот
(40)
где n - общее число испытаний;
k - классовый интервал;
S - среднее квадратическое отклонение.
5. Определяют разность между фактической и теоретической частотой распределения
y i – U т (41)
рассчитывают
6. Находят критерий Пирсона
(43)
7. Определяют число степеней свободы
С = m-3 (44)
где C - число степеней свободы;
m - число классов или строк.
8. Задаваясь доверительной вероятностью q , определяют теоретическое значение критерия Пирсона.
9. Сравнивают c ф 2 с c т 2. Если c 2 ф < c 2 т , то для принятой доверительной вероятности гипотеза о согласии опытного и теоретического распределения принимается, в противном случае отвергается.
В программе Excel проверка осуществляется с помощью функции ХИ2ТЕСТ (рис. 22). ХИ2ТЕСТ возвращает значение для распределения χ 2 Критерий используется для определения того, подтверждается ли гипотеза экспериментом.
Рис. 22. Функция ХИ2ТЕСТ
ХИ2ТЕСТ (фактический_интервал ;ожидаемый_интервал )
Фактический_интервал - это интервал данных, которые содержат наблюдения, подлежащие сравнению с ожидаемыми значениями.
Ожидаемый_интервал - это интервал данных, который содержит отношение произведений итогов по строкам и столбцам к общему итогу.
Если фактический_интервал и ожидаемый_интервал имеют различное количество точек данных, то функция ХИ2ТЕСТ возвращает значение ошибки #Н/Д.
Критерий χ 2 сначала вычисляет χ 2 статистику, используя формулу:
(45)
где A ij - фактическая частота в i -ой строке, j -ом столбце
E ij - ожидаемая частота в i-ой строке, j-ом столбце
r - число строк
c - число столбцов
Значение критерия χ 2 является индикатором независимости. Как видно из формулы, критерий χ 2 всегда положительный или равен 0, а последнее возможно только, если A ij = E ij при любых значениях i,j .
ХИ2ТЕСТ возвращает вероятность того, что при условии независимости может быть получено значение χ 2 статистики по крайней мере такое же высокое, как полученное из приведенной выше формулы. Чтобы вычислить эту вероятность, ХИ2ТЕСТ использует распределение χ 2 с соответствующим числом степеней свободы (df ). Если r > 1, а c > 1, то df = (r - 1)(c - 1). Если r = 1, а c > 1, то df = c - 1 или если r > 1, а c = 1, то df = r - 1. Равенство, где r = c= 1, не позволительно, поэтому появится сообщение об ошибке #Н/Д.
Функцию ХИ2ТЕСТ можно использовать в тех случаях, когда гипотетическое распределение задано полностью, то есть заданы не только вид гипотетического закона распределения, но и все параметры этого закона. Только в этом случае функция правильно выдает число степеней свободы.
ХИ2РАСП (x;степени_свободы) (рис. 23) возвращает одностороннюю вероятность распределения хи-квадрат. Распределение χ 2 связано с критерием χ 2 . Критерий χ 2 используется для сравнения предполагаемых и наблюдаемых значений. Например, в генетическом эксперименте выдвигается гипотеза, что следующее поколение растений будет обладать определенной окраской. Сравнивая наблюдаемые результаты с предполагаемыми, можно определить, была ли верна исходная гипотеза.
х – значение, для которого требуется вычислить распределение.
Степени_свободы – число степеней свободы.
Рис. 23. Функция ХИ2РАСП
Если какой-либо из аргументов не является числом, функция ХИ2РАСП возвращает значение ошибки #ЗНАЧ!.
Если x отрицательное значение, функция ХИ2РАСП
Если степени_свободы < 1 или степени_свободы > 10^10, функция ХИ2РАСП возвращает значение ошибки #ЧИСЛО!.
ХИ2РАСП вычисляется как ХИ2РАСП = P(X> x), где x - χ 2 случайная величина.
ХИ2ОБР (вероятность;степени_свободы) (рис. 24) возвращает значение, обратное односторонней вероятности распределения хи-квадрат. Если вероятность = ХИ2РАСП (x;...), то ХИ2ОБР (вероятность;...) = x. Данная функция позволяет сравнить наблюдаемые результаты с ожидаемыми, чтобы определить, была ли верна исходная гипотеза.
Вероятность - вероятность, связанная с распределением c2 (хи-квадрат).
Степени_свободы - число степеней свободы.
Если какой-либо из аргументов не является числом, функция ХИ2ОБР возвращает значение ошибки #ЗНАЧ!
Рис. 24. Функция ХИ2ОБР
Если вероятность < 0 или вероятность > 1, функция ХИ2ОБР возвращает значение ошибки #ЧИСЛО!
Если значение аргумента «степени_свободы» не является целым числом, оно усекается.
Если степени_свободы < 1 или степени_свободы ≥ 10^10, ХИ2ОБР возвращает значение ошибки #ЧИСЛО!
Если задано значение вероятности, то функция ХИ2ОБР ищет значение x, для которого функция ХИ2РАСП (x; степень_свободы) = вероятность. Однако точность функции ХИ2ОБР зависит от точности ХИ2РАСП . В функции ХИ2ОБР для поиска применяется метод итераций. Если поиск не закончился после 100 итераций, функция возвращает сообщение об ошибке #Н/Д.
ЛАБОРАТОРНАЯ РАБОТА
КОРРЕЛЯЦИОННЫЙ АНАЛИЗ В EXCEL
1.1 Корреляционный анализ в MS Excel
Корреляционный анализ состоит в определении степени связи между двумя случайными величинами X и Y. В качестве меры такой связи используется коэффициент корреляции. Коэффициент корреляции оценивается по выборке объема п связанных пар наблюдений (x i , y i) из совместной генеральной совокупности X и Y. Для оценки степени взаимосвязи величин X и Y, измеренных в количественных шкалах, используетсякоэффициент линейной корреляции (коэффициент Пирсона), предполагающий, что выборки X и Y распределены по нормальному закону.
Коэффициент корреляции изменяется от -1 (строгая обратная линейная зависимость) до 1 (строгая прямая пропорциональная зависимость). При значении 0 линейной зависимости между двумя выборками нет.
Общая классификация корреляционных связей (по Ивантер Э.В., Коросову А.В., 1992):
Существует несколько типов коэффициентов корреляции, что зависит от переменных Х иY, которые могут быть измерены в разных шкалах. Именно этот факт и определяет выбор соответствующего коэффициента корреляции (см. табл. 13):
В MS Excel для вычисления парных коэффициентов линейной корреляции используется специальная функция КОРРЕЛ (массив1; массив2),
№ испытуемых | ||
Пример 1: 10 школьникам были даны тесты на наглядно-образное и вербальное мышление. Измерялось среднее время решения заданий теста в секундах. Исследователя интересует вопрос: существует ли взаимосвязь между временем решения этих задач? Переменная X - обозначает среднее время решения наглядно-образных, а переменная Y- среднее время решения вербальных заданий тестов.
Решение: Для выявления степени взаимосвязи, прежде всего, необходимо ввести данные в таблицу MS Excel (см. табл., рис. 1). Затем вычисляется значение коэффициента корреляции. Для этого курсор установите в ячейку C1. На панели инструментов нажмите кнопку Вставка функции (fx).
В появившемся диалоговом окне Мастер функций выберите категорию Статистические и функциюКОРРЕЛ , после чего нажмите кнопку ОК. Указателем мыши введите диапазон данных выборки Х в поле массив1 (А1:А10). В поле массив2 введите диапазон данных выборки У (В1:В10). Нажмите кнопку ОК. В ячейке С1 появится значение коэффициента корреляции - 0,54119. Далее необходимо посмотреть на абсолютное число коэффициента корреляции и определить тип связи (тесная, слабая, средняя и т.д.)
Рис. 1. Результаты вычисления коэффициента корреляции
Таким образом, связь между временем решения наглядно-образных и вербальных заданий теста не доказана.
Задание 1. Имеются данные по 20 сельскохозяйственным хозяйствам. Найтикоэффициент корреляции между величинами урожайности зерновых культур и качеством земли и оценить его значимость. Данные приведены в таблице.
Таблица 2. Зависимость урожайности зерновых культур от качества земли
Номер хозяйства |
Качество земли, балл |
Урожайность, ц/га |
Задание 2. Определите, имеется ли связь между временем работы спортивного тренажера для фитнеса (тыс. часов) и стоимость его ремонта (тыс. руб.):
Время работа тренажера (тыс. часов) |
Стоимость ремонта (тыс. руб.) |
1.2 Множественная корреляция в MS Excel
При большом числе наблюдений, когда коэффициенты корреляции необходимо последовательно вычислять для нескольких выборок, для удобства получаемые коэффициенты сводят в таблицы, называемые корреляционными матрицами .
Корреляционная матрица - это квадратная таблица, в которой на пересечении соответствующих строк и столбцов находятся коэффициент корреляции между соответствующими параметрами.
В MS Excel для вычисления корреляционных матриц используется процедура Корреляция из пакета Анализ данных. Процедура позволяет получить корреляционную матрицу, содержащую коэффициенты корреляции между различными параметрами.
Для реализации процедуры необходимо:
1. выполнить команду Сервис - Анализ данных ;
2. в появившемся списке Инструменты анализа выбрать строку Корреляция и нажать кнопку ОК ;
3. в появившемся диалоговом окне указать Входной интервал , то есть ввести ссылку на ячейки, содержащие анализируемые данные. Входной интервал должен содержать не менее двух столбцов.
4. в разделе Группировка переключатель установить в соответствии с введенными данными (по столбцам или по строкам);
5. указать выходной интервал , то есть ввести ссылку на ячейку, начиная с которой будут показаны результаты анализа. Размер выходного диапазона будет определен автоматически, и на экран будет выведено сообщение в случае возможного наложения выходного диапазона на исходные данные. Нажать кнопку ОК .
В выходной диапазон будет выведена корреляционная матрица, в которой на пересечении каждых строки и столбца находится коэффициент корреляции между соответствующими параметрами. Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждый столбец во входном диапазоне полностью коррелирует сам с собой
Пример 2. Имеются ежемесячные данные наблюдений за состоянием погоды и посещаемостью музеев и парков (см. табл. 3). Необходимо определить, существует ли взаимосвязь между состоянием погоды и посещаемостью музеев и парков.
Таблица 3. Результаты наблюдений
Число ясных дней |
Количество посетителей музея |
Количество посетителей парка |
Решение . Для выполнения корреляционного анализа введите в диапазон A1:G3 исходные данные (рис. 2). Затем в меню Сервис выберите пункт Анализ данных и далее укажите строку Корреляция . В появившемся диалоговом окне укажите Входной интервал (А2:С7). Укажите, что данные рассматриваются по столбцам. Укажите выходной диапазон (Е1) и нажмите кнопку ОК .
На рис. 33 видно, что корреляция между состоянием погоды и посещаемостью музея равна -0,92, а между состоянием погоды и посещаемостью парка - 0,97, между посещаемостью парка и музея - 0,92.
Таким образом, в результате анализа выявлены зависимости: сильная степень обратной линейной взаимосвязи между посещаемостью музея и количеством солнечных дней и практически линейная (очень сильная прямая) связь между посещаемостью парка и состоянием погоды. Между посещаемостью музея и парка имеется сильная обратная взаимосвязь.
Рис. 2. Результаты вычисления корреляционной матрицы из примера 2
Задание 3 . 10 менеджеров оценивались по методике экспертных оценок психологических характеристик личности руководителя. 15 экспертов производили оценку каждой психологической характеристики по пятибальной системе (см. табл. 4). Психолога интересует вопрос, в какой взаимосвязи находятся эти характеристики руководителя между собой.
Таблица 4. Результаты исследования
Испытуемые п/п |
тактичность |
требовательность |
критичность |
Ширина интервала составит:
Xmax - максимальное значение группировочного признака в совокупности.
Xmin - минимальное значение группировочного признака.
Определим границы группы.
Номер группы | Нижняя граница | Верхняя граница |
1 | 43 | 45.83 |
2 | 45.83 | 48.66 |
3 | 48.66 | 51.49 |
4 | 51.49 | 54.32 |
5 | 54.32 | 57.15 |
6 | 57.15 | 60 |
Одно и тоже значение признака служит верхней и нижней границами двух смежных (предыдущей и последующей) групп.
Для каждого значения ряда подсчитаем, какое количество раз оно попадает в тот или иной интервал. Для этого сортируем ряд по возрастанию.
43 | 43 - 45.83 | 1 |
48.5 | 45.83 - 48.66 | 1 |
49 | 48.66 - 51.49 | 1 |
49 | 48.66 - 51.49 | 2 |
49.5 | 48.66 - 51.49 | 3 |
50 | 48.66 - 51.49 | 4 |
50 | 48.66 - 51.49 | 5 |
50.5 | 48.66 - 51.49 | 6 |
51.5 | 51.49 - 54.32 | 1 |
51.5 | 51.49 - 54.32 | 2 |
52 | 51.49 - 54.32 | 3 |
52 | 51.49 - 54.32 | 4 |
52 | 51.49 - 54.32 | 5 |
52 | 51.49 - 54.32 | 6 |
52 | 51.49 - 54.32 | 7 |
52 | 51.49 - 54.32 | 8 |
52 | 51.49 - 54.32 | 9 |
52.5 | 51.49 - 54.32 | 10 |
52.5 | 51.49 - 54.32 | 11 |
53 | 51.49 - 54.32 | 12 |
53 | 51.49 - 54.32 | 13 |
53 | 51.49 - 54.32 | 14 |
53.5 | 51.49 - 54.32 | 15 |
54 | 51.49 - 54.32 | 16 |
54 | 51.49 - 54.32 | 17 |
54 | 51.49 - 54.32 | 18 |
54.5 | 54.32 - 57.15 | 1 |
54.5 | 54.32 - 57.15 | 2 |
55.5 | 54.32 - 57.15 | 3 |
57 | 54.32 - 57.15 | 4 |
57.5 | 57.15 - 59.98 | 1 |
57.5 | 57.15 - 59.98 | 2 |
58 | 57.15 - 59.98 | 3 |
58 | 57.15 - 59.98 | 4 |
58.5 | 57.15 - 59.98 | 5 |
60 | 57.15 - 59.98 | 6 |
Результаты группировки оформим в виде таблицы:
Группы | № совокупности | Частота fi |
43 - 45.83 | 1 | 1 |
45.83 - 48.66 | 2 | 1 |
48.66 - 51.49 | 3,4,5,6,7,8 | 6 |
51.49 - 54.32 | 9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26 | 18 |
54.32 - 57.15 | 27,28,29,30 | 4 |
57.15 - 59.98 | 31,32,33,34,35,36 | 6 |
Таблица для расчета показателей.
Группы | x i | Кол-во, f i | x i * f i | Накопленная частота, S | |x - x ср |*f | (x - x ср) 2 *f | Частота, f i /n |
43 - 45.83 | 44.42 | 1 | 44.42 | 1 | 8.88 | 78.91 | 0.0278 |
45.83 - 48.66 | 47.25 | 1 | 47.25 | 2 | 6.05 | 36.64 | 0.0278 |
48.66 - 51.49 | 50.08 | 6 | 300.45 | 8 | 19.34 | 62.33 | 0.17 |
51.49 - 54.32 | 52.91 | 18 | 952.29 | 26 | 7.07 | 2.78 | 0.5 |
54.32 - 57.15 | 55.74 | 4 | 222.94 | 30 | 9.75 | 23.75 | 0.11 |
57.15 - 59.98 | 58.57 | 6 | 351.39 | 36 | 31.6 | 166.44 | 0.17 |
36 | 1918.73 | 82.7 | 370.86 | 1 |
Для оценки ряда распределения найдем следующие показатели:
Показатели центра распределения .
Средняя взвешенная
Мода
Мода - наиболее часто встречающееся значение признака у единиц данной совокупности.
где x 0 – начало модального интервала; h – величина интервала; f 2 –частота, соответствующая модальному интервалу; f 1 – предмодальная частота; f 3 – послемодальная частота.
Выбираем в качестве начала интервала 51.49, так как именно на этот интервал приходится наибольшее количество.
Наиболее часто встречающееся значение ряда – 52.8
Медиана
Медиана делит выборку на две части: половина вариант меньше медианы, половина - больше.
В интервальном ряду распределения сразу можно указать только интервал, в котором будут находиться мода или медиана. Медиана соответствует варианту, стоящему в середине ранжированного ряда. Медианным является интервал 51.49 - 54.32, т.к. в этом интервале накопленная частота S, больше медианного номера (медианным называется первый интервал, накопленная частота S которого превышает половину общей суммы частот).
Таким образом, 50% единиц совокупности будут меньше по величине 53.06
Показатели вариации .
Абсолютные показатели вариации .
Размах вариации - разность между максимальным и минимальным значениями признака первичного ряда.
R = X max - X min
R = 60 - 43 = 17
Среднее линейное отклонение - вычисляют для того, чтобы учесть различия всех единиц исследуемой совокупности.
Каждое значение ряда отличается от другого не более, чем на 2.3
Дисперсия - характеризует меру разброса около ее среднего значения (мера рассеивания, т.е. отклонения от среднего).
Несмещенная оценка дисперсии - состоятельная оценка дисперсии.
Среднее квадратическое отклонение .
Каждое значение ряда отличается от среднего значения 53.3 не более, чем на 3.21
Оценка среднеквадратического отклонения .
Относительные показатели вариации .
К относительным показателям вариации относят: коэффициент осцилляции, линейный коэффициент вариации, относительное линейное отклонение.
Коэффициент вариации - мера относительного разброса значений совокупности: показывает, какую долю среднего значения этой величины составляет ее средний разброс.
Поскольку v ≤ 30%, то совокупность однородна, а вариация слабая. Полученным результатам можно доверять.
Линейный коэффициент вариации или Относительное линейное отклонение - характеризует долю усредненного значения признака абсолютных отклонений от средней величины.
Проверка гипотез о виде распределения .
1. Проверим гипотезу о том, что Х распределено по нормальному закону с помощью критерия согласия Пирсона.
где p i - вероятность попадания в i-й интервал случайной величины, распределенной по гипотетическому закону
Для вычисления вероятностей p i применим формулу и таблицу функции Лапласа
где
s = 3.21, x ср = 53.3
Теоретическая (ожидаемая) частота равна n i = np i , где n = 36
Интервалы группировки | Наблюдаемая частота n i | x 1 = (x i - x ср)/s | x 2 = (x i+1 - x ср)/s | Ф(x 1) | Ф(x 2) | Вероятность попадания в i-й интервал, p i = Ф(x 2) - Ф(x 1) | Ожидаемая частота, 36p i | Слагаемые статистики Пирсона, K i |
43 - 45.83 | 1 | -3.16 | -2.29 | -0.5 | -0.49 | 0.01 | 0.36 | 1.14 |
45.83 - 48.66 | 1 | -2.29 | -1.42 | -0.49 | -0.42 | 0.0657 | 2.37 | 0.79 |
48.66 - 51.49 | 6 | -1.42 | -0.56 | -0.42 | -0.21 | 0.21 | 7.61 | 0.34 |
51.49 - 54.32 | 18 | -0.56 | 0.31 | -0.21 | 0.13 | 0.34 | 12.16 | 2.8 |
54.32 - 57.15 | 4 | 0.31 | 1.18 | 0.13 | 0.38 | 0.26 | 9.27 | 3 |
57.15 - 59.98 | 6 | 1.18 | 2.06 | 0.38 | 0.48 | 0.0973 | 3.5 | 1.78 |
36 | 9.84 |
Определим границу критической области. Так как статистика Пирсона измеряет разницу между эмпирическим и теоретическим распределениями, то чем больше ее наблюдаемое значение K набл, тем сильнее довод против основной гипотезы.
Поэтому критическая область для этой статистики всегда правосторонняя: }