Как построить матрицу корреляции в excel

Построение корреляционной матрицы

Пример 3.10. Построить корреляционную матрицу по следующим данным:

1. Ввести данные на Лист 1.

После ввода данных, получим таблицу в окне электронной таблицы Excel, изображенную на рисунке 3.3: Рисунок 3.3 – Таблица ввода данных

1. Выбираем опцию Сервис.
2. Выбираем опцию Анализ данных.

В результате появится окно, изображенное на рисунке 3.2.

1. Выбираем опцию Корреляция.

В результате появится окно, изображенное на рисунке 3.4: Рисунок 3.4 – Окно Корреляция

1. Активизируем окно Входной интервал (установить стрелку мыши в окне и нажать левую клавишу)
2. Выбираем ячейку А2 и при нажатой левой клавише мыши передвигаемся к ячейке D8.

Номера ячеек, из которых будут взяты исходные данные, автоматически заносятся в окно Входной интервал. 7. Ставим флажок в окне Новый рабочий лист. 8. Нажимаем кнопку . В результате получим корреляционную матрицу, представленную на рисунке 3.5: Рисунок 3.5 – корреляционная матрица

Построение модели множественной линейной регрессии и ее анализ

Пример 3.11.По данным примера 14 найти коэффициент корреляции, индекс детерминации, уравнения множественной линейной регрессии,F-статистику,t-статистику, доверительные интервалы для параметров уравнения регрессии. Решение. 1. Введем данные на Лист 1. 2. Выбираем опцию Сервис. 3. Выбираем опцию Анализ данных. 4. Выбираем опцию Регрессия. В результате появится окно, изображенное на рисунке 3.6: Рисунок 3.6 – Окно Регрессия

1. Активизируем окно Входной интервал Y.
2. Выбираем ячейку А2 и при нажатой левой клавише мыши передвигаемся к клетке A8.
3. Активизируем окно Входной интервал Х.
4. Выбираем ячейку В2 и при нажатой левой клавише мыши передвигаемся к клетке D8 (если участвуют все переменные).

Номера ячеек, из которых будут взяты исходные данные, автоматически заносятся в окно Входной интервал. 9. Ставим маркер в окне Новый рабочий лист. 10. Ставим маркер в окне Остатки. 11. Нажимаем кнопку . В результате появится таблица, изображенная на рисунке 3.7: Рисунок 3.7 – Вывод итогов Из таблицы, представленной на рис. 3.7 находим:

• множественный коэффициент корреляции – 0,791;
• индекс детерминации – 0,626;
• уравнения множественной линейной регрессии

• F-статистику – 1,674;
• t-статистики:

для коэффициента при переменной : -1,486; для коэффициента при переменной : -1,062; для коэффициента при переменной : -0,008;

• доверительные интервалы для параметров уравнения регрессии.

для коэффициента при переменной : (-49,96;18,14); для коэффициента при переменной : (-31,28;15,62); для коэффициента при переменной : (-2,77; 2,75).

Как рассчитать скользящую корреляцию в Excel

Скользящие корреляции — это корреляции между двумя временными рядами в скользящем окне. Одним из преимуществ этого типа корреляции является то, что вы можете визуализировать корреляцию между двумя временными рядами во времени.

В этом руководстве объясняется, как рассчитать и визуализировать скользящие корреляции в Excel.

Как рассчитать скользящие корреляции в Excel

Предположим, у нас есть следующие два временных ряда в Excel, которые отображают общее количество продуктов, проданных для двух разных продуктов за 20-месячный период:

Чтобы рассчитать 3-месячную скользящую корреляцию между двумя временными рядами, мы можем просто использовать функцию КОРРЕЛ() в Excel. Например, вот как рассчитать скользящую корреляцию между двумя временными рядами за первые 3 месяца:

Затем мы можем перетащить эту формулу в остальные ячейки столбца:

Каждая ячейка в столбце под названием «Скользящая корреляция за 3 месяца» показывает нам корреляцию между продажами двух продуктов за предыдущие 3 месяца.

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

Как визуализировать скользящие корреляции в Excel

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

Шаг 1: Выделите значения скользящей корреляции.

Сначала выделите значения в диапазоне ячеек D7:D21.

Шаг 2: Вставьте линейную диаграмму.

Затем щелкните вкладку « Вставка » на верхней ленте в Excel. В группе « Диаграммы » щелкните первый вариант диаграммы в разделе « Линейная или областная диаграмма ».

Автоматически появится следующая линейная диаграмма:

На оси Y отображается скользящая 6-месячная корреляция между двумя временными рядами, а на оси X отображается последний месяц для скользящей корреляции.

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

Как построить матрицу корреляции в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Как построить матрицу корреляции в excel

Тест, это не просто множество, а система тестовых заданий. Требование системности означает, что между заданиями существуют связи, которые можно обнаружить в результатах тестирования. Определение корреляции, как между заданиями, так и заданий с тестом в целом, позволит оценить системные качества теста. Благодаря такому анализу можно будет выполнить «чистку» — избавить тест от заданий, нарушающих его системные свойства.

Если две величины связаны между собой, то между ними есть корреляция. Виды корреляционной связи показаны в таблице 3.9.

Для выяснения вопроса о наличии связи между двумя величинами X и Y необходимо определить, существует ли соответствие между большими и малыми значениями X и соответствующими значениями Y или такой связи не обнаруживается. Значение каждого элемента X_i и Y_i определяется величиной и знаком отклонения от среднего арифметического 11 :

Если большие значения X_i соответствуют большим значениям Y_i, то это произведение будет большим и положительным, так как

То же самое будет наблюдаться и, когда малые значения X_i будут соответствовать малым Y_i, поскольку произведение отрицательных чисел будет положительным.

Если же большие значения X_i соответствуют малым значениям Y_i, то это произведение будет большим и отрицательным, что будет свидетельствовать об обратной зависимости между этими величинами.

В тех случаях, когда нет систематического соответствия больших значений X_i большим или малым Y_i, то знак произведения будет положительным или отрицательным для разных пар X_i и Y_i. Тогда сумма

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

Для того, чтобы эта сумма не зависела от количества значений X и Y, ее следует поделить ее на N-1. Полученная величина s_XY называется ковариацией X и Y и является мерой их связи:

Для исключения влияния стандартных отклонений на величину связи, следует поделить ковариацию s_XY на стандартные отклонения s_X и s_Y:

Полученная мера связи между X и Y называется коэффициентом корреляции Пирсона. Обозначение r происходит от слова регрессия. Подставив соответствующие выражения, получим формулу для коэффициента корреляции Пирсона r_XY 11

Для вычислений более удобна следующая формула

Коэффициент корреляции Пирсона r_XY изменяется в пределах от -1 до +1. В таблице 3.7.1 приведены различные виды линейной зависимости и соответствующие значения r_XY.

Следует отметить, что в случае нелинейной связи между X и Y коэффициент корреляции может оказаться близким к нулю, даже если связь очень сильная.

Таблица 3.7.1. Типы корреляционной связи

(Гласс Дж., Стэнли Дж., 1976).

Для решения вопроса о наличии связи между заданиями теста, надо, используя данные по столбцам из бинарной матрицы, рассчитать коэффициенты корреляции Пирсона для каждой пары заданий. Для расчетов используются различные статистические программы (SPSS, STATISTICA и др.). В простейшем случае можно использовать табличный процессор Excel с вызовом функции «ПИРСОН».

В случае дихотомического оценивания (1 — верно, 0 –неверно) выражение для коэффициента корреляции упрощается. Введем следующие обозначения:

p_m – доля верных ответов для задания с номером m;
q_m – доля неверных ответов для задания с номером m;
p_k – доля верных ответов для задания k;
q_k – доля неверных ответов для задания с номером k;
p_mk – доля верных ответов для задания с номером m и k.

Коэффициент корреляции Пирсона, для дихотомических данных называется коэффициентом «фи». Коэффициент φ_mk, описывающий связь между заданиями с номерами m и k записывается следующим образом 11

Отметим, что коэффициент «фи» и коэффициент корреляции Пирсона дают в результате одно и то же значение, поскольку обе формулы эквивалентны. Рассмотрим пример вычисления коэффициента корреляции между 2-м и 5-м заданиями. Из таблицы 3.2.5 имеем: p₂=0.7, q₂=0.3, p₅=0.5, q₅=0.5. Для определения p₂₅ надо подсчитать количество верных ответов на оба задания одновременно. Видно, что испытуемые с номерами 1-5 успешно справились с обоими заданиями (5 верных ответов). Испытуемые 6 и 7 правильно ответили на 2-е задание, но неправильно на 5-е (нет одновременно верных ответов). Испытуемые 8 и 9 не справились и со 2-м и с 5-м заданиями. Таким образом, p₂₅ =5/10 = 0,5.

Результаты расчетов для всех заданий приведены в корреляционной матрице (таблица 3.7.2). Корреляционная матрица представляет собой квадратную матрицу размерности MxM, где M – количество заданий, симметричную относительно главной диагонали. В нашем примере матрица имеет 8 строк и столько же столбцов. Коэффициент корреляции Пирсона, скажем, между 2-м и 5-м заданиями находится на пересечении 2-й строки и 5-го столбца (0,655).

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

ТАБЛИЦА 3.7.2. Корреляционная матрица тестовых заданий.