ЛАБОРАТОРНАЯ РАБОТА
КОРРЕЛЯЦИОННЫЙ АНАЛИЗ В 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. Результаты исследования
Испытуемые п/п |
тактичность |
требовательность |
критичность |
1.Открыть программу Excel
2.Создать столбцы с данными. В нашем примере мы будем считать взаимосвязь, или корреляцию, между агрессивностью и неуверенностью в себе у детей-первоклассников. В эксперименте участвовали 30 детей, данные представлены в таблице эксель:
1 столбик — № испытуемого
2 столбик — агрессивность в баллах
3 столбик — неуверенность в себе в баллах
3.Затем необходимо выбрать пустую ячейку рядом с таблицей и нажать на значок f(x) в панели Excel
4.Откроется меню функций, среди категорий необходимо выбрать Статистические , а затем среди списка функций по алфавиту найти КОРРЕЛ и нажать ОК
5.Затем откроется меню аргументов функции, которое позволит выбрать нужные нам столбики с данными. Для выбора первого столбика Агрессивность нужно нажать на синюю кнопочку у строки Массив1
6.Выберем данные для Массива1 из столбика Агрессивность и нажмем на синюю кнопочку в диалоговом окне
7. Затем аналогично Массиву 1 нажмём на синюю кнопочку у строки Массив2
8.Выберем данные для Массива2 — столбик Неуверенность в себе и опять нажмем синюю кнопку, затем ОК
9.Вот, коэффициент корреляции r-Пирсона посчитан и записан в выбранной ячейке.В нашем случае он положительный и приблизительно равен 0,225 . Это говорит об умеренной положительной связи между агрессивностью и неуверенностью в себе у детей-первоклассников
Таким образом, статистическим выводом эксперимента будет: r = 0,225, выявлена умеренная положительная взаимосвязь между переменными агрессивность и неуверенность в себе.
В некоторых исследованиях требуется указывать р-уровень значимости коэффициента корреляции, однако программа Excel, в отличие от SPSS, не предоставляет такой возможности. Ничего страшного, есть (А.Д. Наследов).
Также Вы можете и приложить её к результатам исследования.
Критерий χ 2 Пирсона – это непараметрический метод, который позволяет оценить значимость различий между фактическим (выявленным в результате исследования) количеством исходов или качественных характеристик выборки, попадающих в каждую категорию, и теоретическим количеством, которое можно ожидать в изучаемых группах при справедливости нулевой гипотезы. Выражаясь проще, метод позволяет оценить статистическую значимость различий двух или нескольких относительных показателей (частот, долей).
1. История разработки критерия χ 2
Критерий хи-квадрат для анализа таблиц сопряженности был разработан и предложен в 1900 году английским математиком, статистиком, биологом и философом, основателем математической статистики и одним из основоположников биометрики Карлом Пирсоном (1857-1936).
2. Для чего используется критерий χ 2 Пирсона?
Критерий хи-квадрат может применяться при анализе таблиц сопряженности , содержащих сведения о частоте исходов в зависимости от наличия фактора риска. Например, четырехпольная таблица сопряженности выглядит следующим образом:
Исход есть (1) | Исхода нет (0) | Всего | |
Фактор риска есть (1) | A | B | A + B |
Фактор риска отсутствует (0) | C | D | C + D |
Всего | A + C | B + D | A + B + C + D |
Как заполнить такую таблицу сопряженности? Рассмотрим небольшой пример.
Проводится исследование влияния курения на риск развития артериальной гипертонии. Для этого были отобраны две группы исследуемых - в первую вошли 70 человек, ежедневно выкуривающих не менее 1 пачки сигарет, во вторую - 80 некурящих такого же возраста. В первой группе у 40 человек отмечалось повышенное артериальное давление. Во второй - артериальная гипертония наблюдалась у 32 человек. Соответственно, нормальное артериальное давление в группе курильщиков было у 30 человек (70 - 40 = 30) а в группе некурящих - у 48 (80 - 32 = 48).
Заполняем исходными данными четырехпольную таблицу сопряженности:
В полученной таблице сопряженности каждая строчка соответствует определенной группе исследуемых. Столбцы - показывают число лиц с артериальной гипертонией или с нормальным артериальным давлением.
Задача, которая ставится перед исследователем: имеются ли статистически значимые различия между частотой лиц с артериальным давлением среди курящих и некурящих? Ответить на этот вопрос можно, рассчитав критерий хи-квадрат Пирсона и сравнив получившееся значение с критическим.
3. Условия и ограничения применения критерия хи-квадрат Пирсона
- Сопоставляемые показатели должны быть измерены в номинальной шкале (например, пол пациента - мужской или женский) или в порядковой (например, степень артериальной гипертензии, принимающая значения от 0 до 3).
- Данный метод позволяет проводить анализ не только четырехпольных таблиц, когда и фактор, и исход являются бинарными переменными, то есть имеют только два возможных значения (например, мужской или женский пол, наличие или отсутствие определенного заболевания в анамнезе...). Критерий хи-квадрат Пирсона может применяться и в случае анализа многопольных таблиц, когда фактор и (или) исход принимают три и более значений.
- Сопоставляемые группы должны быть независимыми, то есть критерий хи-квадрат не должен применяться при сравнении наблюдений "до-"после". В этих случаях проводится тест Мак-Немара (при сравнении двух связанных совокупностей) или рассчитывается Q-критерий Кохрена (в случае сравнения трех и более групп).
- При анализе четырехпольных таблиц ожидаемые значения в каждой из ячеек должны быть не менее 10. В том случае, если хотя бы в одной ячейке ожидаемое явление принимает значение от 5 до 9, критерий хи-квадрат должен рассчитываться с поправкой Йейтса . Если хотя бы в одной ячейке ожидаемое явление меньше 5, то для анализа должен использоваться точный критерий Фишера .
- В случае анализа многопольных таблиц ожидаемое число наблюдений не должно принимать значения менее 5 более чем в 20% ячеек.
4. Как рассчитать критерий хи-квадрат Пирсона?
Для расчета критерия хи-квадрат необходимо:
Данный алгоритм применим как для четырехпольных, так и для многопольных таблиц.
5. Как интерпретировать значение критерия хи-квадрат Пирсона?
В том случае, если полученное значение критерия χ 2 больше критического, делаем вывод о наличии статистической взаимосвязи между изучаемым фактором риска и исходом при соответствующем уровне значимости.
6. Пример расчета критерия хи-квадрат Пирсона
Определим статистическую значимость влияния фактора курения на частоту случаев артериальной гипертонии по рассмотренной выше таблице:
- Рассчитываем ожидаемые значения для каждой ячейки:
- Находим значение критерия хи-квадрат Пирсона:
χ 2 = (40-33.6) 2 /33.6 + (30-36.4) 2 /36.4 + (32-38.4) 2 /38.4 + (48-41.6) 2 /41.6 = 4.396.
- Число степеней свободы f = (2-1)*(2-1) = 1. Находим по таблице критическое значение критерия хи-квадрат Пирсона, которое при уровне значимости p=0.05 и числе степеней свободы 1 составляет 3.841.
- Сравниваем полученное значение критерия хи-квадрат с критическим: 4.396 > 3.841, следовательно зависимость частоты случаев артериальной гипертонии от наличия курения - статистически значима. Уровень значимости данной взаимосвязи соответствует p<0.05.
Ширина интервала составит:
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 набл, тем сильнее довод против основной гипотезы.
Поэтому критическая область для этой статистики всегда правосторонняя: }
Обзор Samsung Galaxy A7 (2017): не боится воды и экономии Стоит ли покупать samsung a7
Делаем бэкап прошивки на андроиде
Как настроить файл подкачки?
Установка режима совместимости в Windows
Резервное копирование и восстановление драйверов Windows