Матрица коэффициентов парной корреляции excel

Матрица парных корреляций. Мультиколлинеарность

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

В общем виде формула для подсчета коэффициента корреляции такова:

где — значения, принимаемые переменной X,

— значения, принимаемые переменой Y,

— средняя по X,

— средняя по Y.

Расчет коэффициента корреляции Пирсона предполагает, что переменные и распределены нормально.

Матрица парных коэффициентов корреляции представляет собой матрицу, элементами которой являются парные коэффициенты корреляции всех факторов модели.

Для нахождения в excel: КОРРЕЛ(массив1;массив2) Массив1 — это ячейка интервала значений.
Массив2 — это второй интервал ячеек со значениями.

Обращение к средствам анализа данных. Они доступны через команду Анализ данных меню Сервис.
Для вычисления матрицы коэффициентов парной корреляции R следует воспользоваться инструментом Корреляция.Инструмент Корреляцияприменяется, если имеется более двух переменных измерений для каждого объекта. В результате выдается таблица, корреляционная матрица, показывающая значение функции КОРРЕЛ для каждой возможной пары переменных измерений. Любое значение коэффициента корреляции должно находиться в диапазоне от -1 до +1 включительно.

По величине парных коэффициентов корреляции обнаруживается лишь явная коллинеарность факторов. Наибольшие трудности при использовании множественной регрессии возникают при наличии мультиколлинеарности факторов, когда более чем два фактора связаны между собой линейной зависимостью, т.е. имеет место совокупное воздействие факторов друг на друга. Наличие мультиколлинеарности факторов может означать, что некоторые факторы будут всегда действовать в унисон. В результате вариация в исходных данных перестаёт быть полностью независимой и нельзя оценить воздействие каждого фактора в отдельности. Чем сильнее мультиколлинеарность факторов, тем менее надёжна оценка распределения суммы объяснённой вариации по отдельным факторам с помощью МНК.

Если рассматривается регрессия y = a+b×x+c×z+d×v+e, то как одно из следствий системы нормальных уравнений получается равенство

. (5.11)

.

В свою очередь при независимости факторов друг от друга выполнимо равенство

,

где S_x, S_z, S_v – суммы квадратов отклонений, обусловленные влиянием соответствующих факторов.

Если факторы интеркоррелированы, последнее равенство нарушается.

Включение в модель мультиколлинеарных факторов нежелательно в силу следующих последствий:

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

оценки параметров ненадёжны, обнаруживают большие стандартные ошибки и меняются с изменением объёма наблюдений (не только по величине, но и по знаку), что делает модель непригодной для анализа и прогнозирования.

Для оценки мультиколлинеарности факторов может использоваться определитель матрицы парных коэффициентов корреляции между векторами (МОПРЕД в EXCEL).

Если бы факторы не коррелировали между собой, то матрица парных коэффициентов корреляции между факторами была бы единичной матрицей, поскольку все недиагональные элементы г_xi,xj (x_i ¹ x_j) были бы равны нулю. Так, для включающего три объясняющих переменных уравнения

матрица коэффициентов корреляции между факторами имела бы определитель, равный 1

det(R)= .

Если же, наоборот, между факторами существует полная линейная зависимость и все коэффициенты корреляции = 1, то определитель такой матрицы равен нулю:

Det(R) = .

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

Оценка значимости мультиколлинеарности факторов может быть проведена методом испытания гипотезы о независимости переменных H₀: det(R)=1. Доказано, что величина имеет приближённое c 2 –распределение с n×(n-1)/2 степенями свободы. Если фактическое значение c 2 превосходит табличное (критическое)

то гипотеза Н₀ отклоняется. Это означает, что det(R) ¹ 1, недиагональные ненулевые коэффициенты корреляции указывают на коллинеарность факторов. Мультиколлинеарность считается доказанной.

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

и т.д.,

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

Используя обозначение матрицы плана (для m = 3)

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

— столбцы матрицы X линейно зависимы между собой;

Определение множественного коэффициента корреляции в MS Excel

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

Вычисление множественного коэффициента корреляции

Принято следующим образом определять уровень взаимосвязи между различными показателями, в зависимости от коэффициента корреляции:

• 0 – 0,3 – связь отсутствует;
• 0,3 – 0,5 – связь слабая;
• 0,5 – 0,7 – средняя связь;
• 0,7 – 0,9 – высокая;
• 0,9 – 1 – очень сильная.

Если корреляционный коэффициент отрицательный, то это значит, что связь параметров обратная.

Для того, чтобы составить корреляционную матрицу в Экселе, используется один инструмент, входящий в пакет «Анализ данных». Он так и называется – «Корреляция». Давайте узнаем, как с помощью него можно вычислить показатели множественной корреляции.

Этап 1: активация пакета анализа

Сразу нужно сказать, что по умолчанию пакет «Анализ данных» отключен. Поэтому, прежде чем приступить к процедуре непосредственного вычисления коэффициентов корреляции, нужно его активировать. К сожалению, далеко не каждый пользователь знает, как это делать. Поэтому мы остановимся на данном вопросе.

1. Переходим во вкладку «Файл». В левом вертикальном меню окна, которое откроется после этого, щелкаем по пункту «Параметры».

После запуска окна параметров посредством его левого вертикального меню переходим в раздел «Надстройки». Там в самом низу правой части окна располагается поле «Управление». Переставляем переключатель в нём в позицию «Надстройки Excel», если отображен другой параметр. После этого клацаем по кнопке «Перейти…», находящейся справа от указанного поля.

Происходит запуск небольшого окошка «Надстройки». Устанавливаем флажок около параметра «Пакет анализа». Затем в правой части окна кликаем по кнопке «OK».

После указанного действия пакет инструментов «Анализ данных» будет активирован.

Этап 2: расчет коэффициента

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

Перемещаемся во вкладку «Данные». Как видим, на ленте появился новый блок инструментов «Анализ». Клацаем по кнопке «Анализ данных», которая располагается в нём.

Открывается окошко, которое носит наименование «Анализ данных». Выделяем в списке инструментов, расположенных в нём, наименование «Корреляция». После этого щелкаем по кнопке «OK» в правой части интерфейса окна.

Открывается окно инструмента «Корреляция». В поле «Входной интервал» следует внести адрес диапазона таблицы, в котором расположены данные по трем изучаемым факторам: энерговооруженность, фондовооруженность и производительность. Можно произвести ручное внесение координат, но легче просто установить курсор в поле и, зажав левую кнопку мыши, выделить соответствующую область таблицы. После этого адрес диапазона будет отображен в поле окна «Корреляция».

Так как у нас факторы разбиты по столбцам, а не по строкам, то в параметре «Группирование» выставляем переключатель в позицию «По столбцам». Впрочем, он там уже и так установлен по умолчанию. Поэтому остается только проверить правильность его расположения.

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

В блоке настроек «Параметр вывода» следует указать, где именно будет располагаться наша корреляционная матрица, в которую выводится результат расчета. Доступны три варианта:

• Новая книга (другой файл);
• Новый лист (при желании в специальном поле можно дать ему наименование);
• Диапазон на текущем листе.

Давайте выберем последний вариант. Переставляем переключатель в положение «Выходной интервал». В этом случае в соответствующем поле нужно указать адрес диапазона матрицы или хотя бы её верхнюю левую ячейку. Устанавливаем курсор в поле и клацаем по ячейке на листе, которую планируем сделать верхним левым элементом диапазона вывода данных.

После выполнения всех указанных манипуляций остается только щелкнуть по кнопке «OK» в правой части окошка «Корреляция».

После выполнения последнего действия Excel строит матрицу корреляции, заполняя её данными, в указанном пользователем диапазоне.

Этап 3: анализ полученного результата

Теперь давайте разберемся, как понимать тот результат, который мы получили в процессе обработки данных инструментом «Корреляция» в программе Excel.

Как видим из таблицы, коэффициент корреляции фондовооруженности (Столбец 2) и энерговооруженности (Столбец 1) составляет 0,92, что соответствует очень сильной взаимосвязи. Между производительностью труда (Столбец 3) и энерговооруженностью (Столбец 1) данный показатель равен 0,72, что является высокой степенью зависимости. Коэффициент корреляции между производительностью труда (Столбец 3) и фондовооруженностью (Столбец 2) равен 0,88, что тоже соответствует высокой степени зависимости. Таким образом, можно сказать, что зависимость между всеми изучаемыми факторами прослеживается довольно сильная.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Матрица парных коэффициентов корреляции

Пример . По данным 154 сельскохозяйственных предприятий Кемеровской области 2003 г. изучить эффективность производства зерновых (табл. 13).

Задание

1. Определите факторы, формирующие рентабельность зерновых в сельскохозяйственных предприятий в 2003 г.
2. Постройте матрицу парных коэффициентов корреляции. Установите, какие факторы мультиколлинеарны.
3. Постройте уравнение регрессии, характеризующее зависимость рентабельности зерновых от всех факторов.
4. Оцените значимость полученного уравнения регрессии. Какие факторы значимо воздействуют на формирование рентабельности зерновых в этой модели?
5. Оцените значение рентабельности производства зерновых в сельскохозяйственном предприятии № 3.

1. Оценка уравнения регрессии.
Определим вектор оценок коэффициентов регрессии. Согласно методу наименьших квадратов, вектор получается из выражения:
s = (X T X) -1 X T Y
Матрица X

Матрица X T
Умножаем матрицы, (X T X)

Матрица парных коэффициентов корреляции

Найдем парные коэффициенты корреляции.
Для y и x₁
Уравнение имеет вид y = ax + b
Средние значения

Для y и x₂
Уравнение имеет вид y = ax + b
Средние значения

Для y и x₃
Уравнение имеет вид y = ax + b
Средние значения

Для x₁ и x₂
Уравнение имеет вид y = ax + b
Средние значения

Для x₁ и x₃
Уравнение имеет вид y = ax + b
Средние значения

Для x₂ и x₃
Уравнение имеет вид y = ax + b
Средние значения

Матрица парных коэффициентов корреляции.

3. Анализ параметров уравнения регрессии.
Перейдем к статистическому анализу полученного уравнения регрессии: проверке значимости уравнения и его коэффициентов, исследованию абсолютных и относительных ошибок аппроксимации
Для несмещенной оценки дисперсии проделаем следующие вычисления:
Несмещенная ошибка e = Y — X*s (абсолютная ошибка аппроксимации)

s_e 2 = (Y — X*s) T (Y — X*s)
Несмещенная оценка дисперсии равна

Оценка среднеквадратичного отклонения равна

Найдем оценку ковариационной матрицы вектора k = a*(X T X) -1

Дисперсии параметров модели определяются соотношением S 2 _i= K_ii, т.е. это элементы, лежащие на главной диагонали
С целью расширения возможностей содержательного анализа модели регрессии используются частные коэффициенты эластичности, которые определяются по формуле:

Частные коэффициент эластичности E₁ 2 = 0.62 2 = 0.38, т.е. в 38.0855 % случаев изменения х приводят к изменению y. Другими словами — точность подбора уравнения регрессии — средняя

Значимость коэффициента корреляции

По таблице Стьюдента находим Tтабл
T_табл(n-m-1;a) = (18;0.05) = 1.734
Поскольку Tнабл > Tтабл , то отклоняем гипотезу о равенстве 0 коэффициента корреляции. Другими словами, коэффициента корреляции статистически — значим
Интервальная оценка для коэффициента корреляции (доверительный интервал)

Доверительный интервал для коэффициента корреляции
r(0.3882;0.846)

5. Проверка гипотез относительно коэффициентов уравнения регрессии (проверка значимости параметров множественного уравнения регрессии).
1) t-статистика

Статистическая значимость коэффициента регрессии b₀не подтверждается

Статистическая значимость коэффициента регрессии b₁не подтверждается

Статистическая значимость коэффициента регрессии b₂не подтверждается

Статистическая значимость коэффициента регрессии b₃не подтверждается
Доверительный интервал для коэффициентов уравнения регрессии
Определим доверительные интервалы коэффициентов регрессии, которые с надежность 95% будут следующими:
(b_i— t _iS _i; b_i+ t _iS _i)
b ₀: (-0.7348;1.0478)
b ₁: (-2.9781;3.6531)
b ₂: (-0.4466;0.4553)
b ₃: (-4.8459;5.4431)

ПОСТРОИМ МАТРИЦУ КОЭФФИЦИЕНТОВ ПАРНОЙ КОРРЕЛЯЦИИ

1. ПОСТРОИМ МАТРИЦУ КОЭФФИЦИЕНТОВ ПАРНОЙ КОРРЕЛЯЦИИ.

Для этого рассчитаем коэффициенты парной корреляции по формуле:

Необходимые расчеты представлены в таблице 9.

—

связь между выручкой предприятия Y и объемом капиталовложений Х₁ слабая и прямая;

—

связи между выручкой предприятия Y и основными производственными фондами Х₂ практически нет;

—

связь между объемом капиталовложений Х₁ и основными производственными фондами Х₂ тесная и прямая;

Вспомогательная таблица для расчета коэффициентов парных корреляций

Также матрицу коэффициентов парных корреляций можно найти в среде Excel с помощью надстройки АНАЛИЗ ДАННЫХ, инструмента КОРРЕЛЯЦИЯ.

Матрица коэффициентов парной корреляции имеет вид:

Матрица парных коэффициентов корреляции показывает, что результативный признак у (выручка) имеет слабую связь с объемом капиталовложений х₁, а с Размером ОПФ связи практически нет. Связь между факторами в модели оценивается как тесная, что говорит о их линейной зависимости, мультиколлинеарности.

2. ПОСТРОИТЬ ЛИНЕЙНУЮ МОДЕЛЬ МНОЖЕСТВЕННОЙ РЕГРЕССИИ

Параметры модели найдем с помощью МНК. Для этого составим систему нормальных уравнений.

Расчеты представлены в таблице 10.

Решим систему уравнений, используя метод Крамера:

Вспомогательные вычисления для нахождения параметров линейной модели множественной регрессии

Линейная модель множественной регрессии имеет вид:

Если объем капиталовложений увеличить на 1 млн. руб., то выручка предприятия увеличиться в среднем на 2,317 млн. руб. при неизменных размерах основных производственных фондов.

Если основные производственные фонды увеличить на 1 млн. руб., то выручка предприятия уменьшиться в среднем на 1,171 млн. руб. при неизменном объеме капиталовложений.

коэффициент множественной корреляции:

Связь выручки предприятия с объемом капиталовложений и основными производственными фондами тесная.

67,82% изменения выручки предприятия обусловлено изменением объема капиталовложений и основных производственных фондов, на 32,18% — влиянием факторов, не включенных в модель.

F – критерий Фишера

Проверим значимость уравнения

Табличное значение F – критерия при уровне значимости α = 0,05 и числе степеней свободы d.f.₁ = k = 2 (количество факторов), числе степеней свободы d.f.₂ = (n – k – 1) = (10 – 2 – 1) = 7 составит 4,74.

Так как F_расч. = 7,375 > F_табл. = 4.74, то уравнение регрессии в целом можно считать статистически значимым.

Рассчитанные показатели можно найти в среде Excel с помощью надстройки АНАЛИЗА ДАННЫХ, инструмента РЕГРЕССИЯ.

Вспомогательные вычисления для нахождения средней относительной ошибки аппроксимации

среднюю относительную ошибку аппроксимации

В среднем расчетные значения отличаются от фактических на 3,53 %. Ошибка небольшая, модель можно считать точной.

4. Построить степенную модель множественной регрессии

Для построения данной модели прологарифмируем обе части равенства

Сделаем замену Y = lg y, A = lg a, X₁ = lg x₁, X₂ = lg x₂.

Тогда Y = A + β₁ ∙ X₁ + β₂ ∙ X₂ – линейная двухфакторная модель регрессии. Можно применить МНК.

Расчеты представлены в таблице 12.

Вспомогательные вычисления для нахождения параметров степенной модели множественной регрессии

Решаем систему уравнений применяя метод Крамера.

Степенная модель множественной регрессии имеет вид:

В степенной функции коэффициенты при факторах являются коэффициентами эластичности. Коэффициент эластичности показывает на сколько процентов измениться в среднем значение результативного признака у, если один из факторов увеличить на 1 % при неизменном значении других факторов.

Если объем капиталовложений увеличить на 1%, то выручка предприятия увеличиться в среднем на 0,897% при неизменных размерах основных производственных фондов.

Если основные производственные фонды увеличить на 1%, то выручка предприятия уменьшиться на 0,226% при неизменных капиталовложениях.

коэффициент множественной корреляции:

Связь выручки предприятия с объемом капиталовложений и основными производственными фондами тесная.

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

Вычисление линейного парного коэффициента корреляции в MS Excel

Корреляция характеризует степень взаимосвязи между случайными величинами. Приведем пример вычисления линейного парного коэффициента корреляции в MS Excel.

Линейный парный коэффициент корреляции обозначают .
В среде электронных таблиц для его вычисления используют функцию КОРРЕЛ(массив1;массив2),
массив1 — это диапазон, в котором расположены значения первой переменной, а массив2 — это диапазон, в котором расположены значения второй переменной.

Свойства линейного парного коэффициента корреляции:
1)
2) если 0″ title=»Rendered by QuickLaTeX.com» height=»18″ width=»56″ style=»vertical-align: -6px;»/>, то связь между переменными — прямая
если , то связь между переменными — обратная
3) если , то связь между переменными — отсутствует
если , то связь между переменными — слабая
если , то связь между переменными — умеренная
если 0,7″ title=»Rendered by QuickLaTeX.com» height=»20″ width=»81″ style=»vertical-align: -6px;»/>, то связь между переменными — сильная.

Пример. Измерим корреляцию между переменными:
— цена на наушники JBL T100, руб.
— спрос на эти наушники, шт.

Исходная информация представлена в среде электронных таблиц:

Для вычисления линейного парного коэффициента корреляции воспользуемся функцией электронных таблиц, в ячейку D2 вводим выражение =КОРРЕЛ(А2:А16;В2:В16) и нажимаем Enter , получаем результат:

Сделаем вывод: линейная связь между ценой на наушники и спросом на них обратная и очень сильная.

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

Получаем корреляционное поле:

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