Построить матрицу парных коэффициентов корреляции в excel

Построить матрицу парных коэффициентов корреляции в excel

1. Составим матрицу парных коэффициентов корреляции исходных переменных, используя инструмент «Корреляция» (анализ данных EXCEL).

Рис. 1. Диалоговые окна

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

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

У Х1 Х2 Х3
У
Х1 0,662068116
Х2 0,855940592 0,442460376
Х3 0,948257445 0,738989302 0,796991531

Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная у, т.е. цена реализации квартиры на вторичном рынке, наиболее сильно связана с Х2 ( ) и с Х3 ( ). Проверим наличие мультиколлинеарности. Так как Х1иХ3связаны между собой сильно , аналогично, Х2иХ3 ( ), то фактор Х3 исключаем из анализа связи с наличием мультиколлинеарности и оставляем Х1 и Х2.

Построим линейное уравнение множественной регрессии с полным набором немультиколлинеарных факторов (Х1, Х2) — .

Используем инструмент Регрессия (анализ данных EXCEL).

Рис. 4. Диалоговые окна

В итоге получаем следующие данные анализа:

Таблица 3. Регрессионная статистика

Множественный R 0,912394887
R-квадрат 0,83246443
Нормированный R-квадрат 0,802003417
Стандартная ошибка 3,146048427
Наблюдения

Таблица 4. Дисперсионный анализ

df SS MS F
Регрессия 540,9811723 270,4905861 27,32884945
Остаток 108,8738277 9,897620703
Итого 649,855

Таблица 5. Данные по точности и значимости модели

Коэффициенты Стандартная ошибка t-статистика
Y-пересечение -6,512138886 9,023202439 -0,721710383
х1 0,765866218 0,299143285 2,5601986
х2 5,811862737 1,142490613 5,087011368

Таблица 6. Вывод остатка

Наблюдение Предсказанное y Остатки
27,8731306 -0,673130599
37,9661855 3,533814503
52,92142902 2,678570979
30,96723012 -1,167230121
38,21892135 -7,018921349
29,18276183 1,217238168
34,71891273 -2,118912731
31,89392824 -0,293928245
30,47707574 2,022924259
30,91255763 1,787442375
28,61602083 0,88397917
30,03287333 1,167126666
36,41913574 -4,219135735
32,29983734 2,200162659

Таким образом, получили следующее уравнение регрессии (табл. 5, второй столбец): , т.е. при неизменном количестве комнат увеличение цены новой квартиры на 1 тыс. у.е. влечет за собой рост цены реализации на вторичном рынке на 0,766 тыс. у.е.; при неизменной цене новых квартир, увеличение количества комнат на 1 ед. влечет за собой рост цены реализации на вторичном рынке на 5,811 тыс. у.е.

Рассчитаем коэффициенты эластичности:

%, т.е. с ростом цены новых квартир на 1% цена реализации на вторичном рынке возрастет в среднем на 0,750% при неизменном числе комнат.

%, т.е. с увеличением количества комнат в квартире на 1% цена реализации на вторичном рынке возрастет в среднем на 0,443% при неизменной цене новых квартир.

Значение коэффициента детерминации, приведенное в табл. 3, равно: . Он показывает долю вариации результативного признака у под воздействием изучаемых факторов. Следовательно, до 83,2% вариации зависимой переменной учтено в модели и обусловлено влиянием факторов.

Его также можно рассчитать по следующей формуле:

.

Задача 5. Известны данные по уровню жизни населения г. Тулы на 199Х г. (табл. 1).

Таблица 1. Исходные данные задачи

Месяцы Среднедушевой доход, руб. на чел. Расходы на бытовые услуги, руб. на чел.

Исследуя спрос на бытовые услуги, построены следующие модели: ; ; .

а) оценить точность построенных моделей;

б) выбрать наилучшую модель и оценить величину расходов на 2 ближайших месяца;

в) отобразить на графике фактические данные, результаты моделирования и прогнозирования (доверительные интервалы прогнозов рассчитать при уровне значимости ).

1. Оценим точность построенных моделей на основе средней ошибки аппроксимации: %, сведя все вычисления в табл. 2.

Таблица 2. Промежуточные данные расчета моделей

Месяцы
147,85 144,83 141,72
148,09 145,05 142,03
149,13 146,00 143,34
153,53 150,06 148,76
168,65 164,37 166,18
181,21 176,68 179,34
218,17 215,05 212,52
220,49 217,56 214,37
221,53 218,69 215,19
227,21 224,91 219,58
Итого: 1835,86 1803,21 1783,02
Месяцы
1,4333 3,4467 5,5170
1,2733 3,3003 5,3149
6,5214 4,2874 2,3825
18,1000 15,4332 14,4320
2,2121 0,3788 0,7123
4,6263 7,0102 5,6102
1,4744 0,0218 1,1532
11,3586 9,8792 8,2665
0,6955 0,5946 2,1874
0,9822 0,0389 2,4082
Итого: 48,6772 44,3912 47,9843

Тогда имеем следующие значения для средней относительной ошибки:

— для линейной модели , т.е. в среднем расчетные значения для этой модели отличаются от фактических значений на 4,87%, что говорит о высокой точности уравнения регрессии (ошибка аппроксимации в 5-7% свидетельствует о хорошем подборе модели к исходным данным).

· , т.е. в среднем расчетные значения для степенной модели отличаются от фактических значений на 4,44%, что говорит о хорошем качестве уравнения регрессии;

Читайте также:  Dying light the following как начать играть

· , т.е. в среднем расчетные значения для гиперболической модели отличаются от фактических значений на 4,80%, что говорит о хорошем качестве уравнения регрессии. Так как меньшей ошибкой аппроксимации обладает степенная модель, по этому показателю она наилучшая.

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

Рис. 1. График фактических и модельных значений

Таким образом, найдем прогнозные значения переменной х:

;

Прогнозные значения переменной у:

; .

Найдем верхнюю и нижнюю границу прогноза:

где .

Получаем: ; и , тогда

. .

Точечный прогноз у Интервальные оценки прогноза
Нижняя граница Верхняя граница
231,30 204,418 258,184
238,65 207,937 269,357

Построим графики фактических и прогнозных модельных значений:

Рис. Графики прогноза

Дата публикования: 2015-09-18 ; Прочитано: 1730 | Нарушение авторского права страницы

studopedia.org — Студопедия.Орг — 2014-2020 год. Студопедия не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования (0.005 с) .

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

y x1 x2 x3
y 1 ryx1 ryx2 ryx3
x1 rx1y 1 rx1x2 rx1x3
x2 rx2y rx2x1 1 rx2x3
x3 rx3y rx3x1 rx3x2 1

Вставьте в поле матрицу парных коэффициентов.

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

Задание

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

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

1 0.43 2.02 0.29
1 0.87 1.29 0.55
1 1.01 1.09 0.7
1 0.63 1.68 0.41
1 0.52 0.3 0.37
1 0.44 1.98 0.3
1 1.52 0.87 1.03
1 2.19 0.8 1.3
1 1.8 0.81 1.17
1 1.57 0.84 1.06
1 0.94 1.16 0.64
1 0.72 1.52 0.44
1 0.73 1.47 0.46
1 0.77 1.41 0.49
1 1.21 0.97 0.88
1 1.25 0.93 0.91
1 1.31 0.91 0.94
1 0.38 2.08 0.27
1 0.41 2.05 0.28
1 0.48 1.9 0.32
1 0.58 1.73 0.38
1

Матрица Y

0.22
0.67
0.79
0.42
0.32
0.24
0.95
1.05
0.99
0.96
0.73
0.52
2.1
0.58
0.87
0.89
0.91
0.14
0.18
0.27
0.37

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

22 19.76 27.81 13.19
19.76 23.78 22.45 15.73
27.81 22.45 42.09 14.96
13.19 15.73 14.96 10.45

В матрице, (X T X) число 22, лежащее на пересечении 1-й строки и 1-го столбца, получено как сумма произведений элементов 1-й строки матрицы X T и 1-го столбца матрицы X
Умножаем матрицы, (X T Y)

14.17
15.91
16.58
10.56

Находим определитель det(X T X) T = 34.35
Находим обратную матрицу (X T X) -1

0.6821 0.3795 -0.2934 -1.0118
0.3795 9.4402 -0.133 -14.4949
-0.2934 -0.133 0.1746 0.3204
-1.0118 -14.4949 0.3204 22.7272

Вектор оценок коэффициентов регрессии равен
s = (X T X) -1 X T Y =

0.1565
0.3375
0.0043
0.2986

Уравнение регрессии (оценка уравнения регрессии): Y = 0.1565 + 0.3375X 1+ 0.0043X 2+ 0.2986X 3

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

Число наблюдений n = 22. Число независимых переменных в модели ровно 3, а число регрессоров с учетом единичного вектора равно числу неизвестных коэффициентов. С учетом признака Y, размерность матрицы становится равным 5. Матрица, независимых переменных Х имеет размерность (22 х 5). Матрица Х T Х определяется непосредственным умножением или по следующим предварительно вычисленным суммам.
Матрица составленная из Y и X

1 0.22 0.43 2.02 0.29
1 0.67 0.87 1.29 0.55
1 0.79 1.01 1.09 0.7
1 0.42 0.63 1.68 0.41
1 0.32 0.52 0.3 0.37
1 0.24 0.44 1.98 0.3
1 0.95 1.52 0.87 1.03
1 1.05 2.19 0.8 1.3
1 0.99 1.8 0.81 1.17
1 0.96 1.57 0.84 1.06
1 0.73 0.94 1.16 0.64
1 0.52 0.72 1.52 0.44
1 2.1 0.73 1.47 0.46
1 0.58 0.77 1.41 0.49
1 0.87 1.21 0.97 0.88
1 0.89 1.25 0.93 0.91
1 0.91 1.31 0.91 0.94
1 0.14 0.38 2.08 0.27
1 0.18 0.41 2.05 0.28
1 0.27 0.48 1.9 0.32
1 0.37 0.58 1.73 0.38
1
Читайте также:  Как удалить видео с айтюнса на компьютере

Транспонированная матрица
Матрица A T A.

22 14.17 19.76 27.81 13.19
14.17 13.55 15.91 16.58 10.56
19.76 15.91 23.78 22.45 15.73
27.81 16.58 22.45 42.09 14.96
13.19 10.56 15.73 14.96 10.45

Полученная матрица имеет следующее соответствие:

∑n ∑y ∑x1 ∑x2 ∑x3
∑y ∑y² ∑x1y ∑x2y ∑x3
∑x1 ∑x1y ∑x²1 ∑x2x1 ∑x3x1
∑x2 ∑x2y ∑x2x1 ∑x²2 ∑x3x2
∑x3 ∑x3y ∑x3x1 ∑x3x2 ∑x²3

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

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

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

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

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

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

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

y x 1 x 2 x 3
y 1 0.62 -0.24 0.61
x 1 0.62 1 -0.39 0.99
x 2 -0.24 -0.39 1 -0.41
x 3 0.61 0.99 -0.41 1

Анализ первой строки этой матрицы позволяет произвести отбор факторных признаков, которые могут быть включены в модель множественной корреляционной зависимости. Факторные признаки, у которых ryxi r(xkxj) ; r(xky) > r(xkxj).
Если одно из неравенств не соблюдается, то исключается тот параметр xkили xj, связь которого с результативным показателем Y оказывается наименее тесной.

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

-0.18
0.05
0.08
-0.08
-0.12
-0.16
-0.03
-0.24
-0.13
-0.05
0.06
-0.02
1.55
0.01
0.04
0.04
0.03
-0.23
-0.21
-0.15
-0.1
-0.16

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

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

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

0.26 0.15 -0.11 -0.39
0.15 3.66 -0.05 -5.61
-0.11 -0.05 0.07 0.12
-0.39 -5.61 0.12 8.8

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

Частные коэффициент эластичности E1 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не подтверждается

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

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

Статистическая значимость коэффициента регрессии b3не подтверждается
Доверительный интервал для коэффициентов уравнения регрессии
Определим доверительные интервалы коэффициентов регрессии, которые с надежность 95% будут следующими:
(bi— t iS i; bi+ t iS i)
b : (-0.7348;1.0478)
b 1: (-2.9781;3.6531)
b 2: (-0.4466;0.4553)
b 3: (-4.8459;5.4431)

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

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

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

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

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

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

Читайте также:  Расшифровка модели духового шкафа bosch

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Ссылка на основную публикацию
    Порвалась губа на сабвуфере
    Появление дребезжания или хрипа в колонке может свидетельствовать о повреждении подвеса, диффузора, центрирующей шайбы или выводов. Сегодня можно найти как...
    Погрешность аппроксимации и погрешность метода
    Для вычисления погрешности аппроксимации следует найти величину среднеквадратичного отклонения по формуле (3.11). где Уi- значение некоторой физической величины f(x) в...
    Подключение наушников к телевизору philips
    Телевизор – это одно из тех устройств, которые, как правило, требуют наиболее качественной акустической системы. К хорошему телевизору непременно стоит...
    После установки драйверов на видеокарту пропал звук
    Обновление программного обеспечения это весьма ответственная операция со своими нюансами и «подводными камнями». Нередко после ее проведения возникают различные проблемы...
    Adblock detector