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

Определение множественного коэффициента корреляции в 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, что тоже соответствует высокой степени зависимости. Таким образом, можно сказать, что зависимость между всеми изучаемыми факторами прослеживается довольно сильная.

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

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

Инвестиционный анализ. Расчет корреляционной матрицы в Excel

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

Задача любого инвестора – снизить риски вложения, для этого используется различные виды диверсификации (распределения) средств по различным направлениям. Диверсифицированный портфель содержит некоррелированные активы, другими словами, активы которые имеют различную динамику изменения стоимости (цены). Например, если цена одного актива растет, то другого изменяется независимо от первого. Для описания силы связи между активами инвесторы и аналитики используют коэффициент корреляция, который также называет коэффициентом корреляции Пирсона. Он изменяется в диапазоне от -1 до 1.

Если коэффициент корреляции между двумя активами равен 1, то их стоимость изменяется в тандеме (параллельно).
Если коэффициент корреляции равен 0, то цены активов изменяются независимо друг от друга.
Если коэффициент корреляции равен -1, то цены активов сильно зависимы друг от друга и изменяются в противоположных направлениях. Например, если один из активов растет, то другой с таким же темпом обесценивается.

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

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

Сложности использования коэффициента корреляции в инвестиционном анализе
Финансовые аналитики часто не соглашаются, что корреляция вообще может быть оценена и рассчитана. Выделяют основные недостатки:

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

Расчет корреляционной матрицы в Excel
Заполняет колонки с изменением стоимости актива. Данные по котировкам можно получить на сайте finam.ru в разделе экспорт котировок. Данные котировки были получены с сайта finance.yahoo.com по иностранным акциям.

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

Далее необходимо в Excel выбрать входной интервал котировок (зеленая область + названия активов). Отметить галочку метки в первой строке. Выбрать выходной интервал как ячейку и нажать Oк.

Итоговый результат расчета корреляционной матрицы представлен на рисунке ниже. Можно выделить низкий коэффициент корреляции между активами TBILL и всеми остальными (стремится к 0), также между USX и GMC составляет 0.2. Это показывает высокую степень диверсификации данных активов в портфеле. Но активы ATT и GMC имеют достаточно высокую зависимость 0.5, это требует пересмотра данных активов в инвестиционном портфеле.

Записки магистра бизнес-информатики

Вычисление ковариационной, корреляционной матриц, матрицы вторых моментов в MS Excel

Основная цель — научиться вычислять матрицы ковариации и корреляции.

Задание: для набора курсов валют вычислить ковариационную матрицу, корреляционную матрицу, матрицу вторых моментов, нормированную матрицу вторых моментов.

1. Подготовка исходных данных в MS Excel

2. Центрирование данных

Создали новый лист, задали ему имя «Центр». Установили курсор на ячейку В3 и набрали команду =Лист1!B3-Лист1!B$12

Обратите, что адрес ячейки, содержащей среднее значение, закреплен только по столбцам. Далее растянули формулу вправо до ячейки G3 и вниз до ячейки G10.

3. Вычисление ковариационной матрицы

Создали лист и дали ему имя «Ковариация». Ковариационная матрица вычисляется по следующей формуле

Для транспонирования матрицы используем команду ТРАНСП(). Команда перемножения матриц имеет вид

Полученная ковариационная матрица показана на рисунке ниже.

Диагональные коэффициенты ковариационной матрицы представляют дисперсии показателей.

4. Вычисление корреляционной матрицы

Для вычисления корреляционной матрицы воспользуемся формулой

Здесь E — диагональная матрица, i-ый диагональный коэффициент которой равен обратной величине среднеквадратического отклонения i-го показателя.

По формуле =1/КОРЕНЬ(C3) и так далее для диагонали предыдущей матрицы построили матрицу нормализации.
Пустые ячейки (по обе стороны диагонали заполнили нулями).

Создали лист и задали ему имя «Корреляция». Установите курсор на ячейке С3 и выполнили умножение трех матриц командой =МУМНОЖ(Ковариация!L3:Q8; МУМНОЖ(Ковариация!C3:H8; Ковариация!L3:Q8)).

Корреляционная матрица представлена на рисунке ниже.

5. Вычисление матрицы вторых моментов.

При вычислении матрицы вторых моментов используются не центрированные данные.

Матрица вторых моментов показана на рисунке ниже

Вычисление линейного парного коэффициента корреляции в 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 , получаем результат:

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

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

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

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

Коэффициент корреляция в excel — примеры как применять

Microsoft Office Excel часто используется для анализа данных и применения различных функций, которые встроены в программу. Для выявления зависимости одной величины от другой ведутся корреляционные исследования, которые достаточно популярны в статистике. Сегодня разберемся, как осуществляется корреляция в excel.

Введение

Чтобы рассчитать коэффициент корреляции, необходимо воспользоваться специальной функцией КОРРЕЛ. Формула содержит аргументы для двух массивов данных, между которыми нужно найти зависимость. Полученный коэффициент корреляции в excel можно расшифровать следующим образом:

1. Если значение близко к 1 или -1, то существует сильная прямая или обратная связь между величинами.
2. Коэффициент около 0,5 или -0,5 говорит о том, что между массивами слабая взаимосвязь.
3. Если получается число близкое к нулю, то величины не связаны между собой.

При этом есть ряд особенностей использования функции КОРРЕЛ:

1. Программа не учитывает в расчете пустые ячейки, элементы массива с текстовым форматом и ячейки с логическими операторами. При этом числа в виде текста будут учтены.
2. Размеры двух массивов должны быть одинаковыми, в противном случае редактор выдаст ошибку типа Н/Д.
3. При корреляционном анализе нельзя использовать пустые столбцы или диапазон с нулевыми значениями.

Примеры использования

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

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

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

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

Пример 2.

Есть показатели продаж мебели за квартал, а также изменение цены на товар за тот же период времени.

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

Пример 3.

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

Полученный результат говорит о слабой связи этих категорий.

Прочие возможности

Также при помощи функции КОРРЕЛ можно провести более сложные исследования. Примером является парная и множественная корреляция. Отличие их заключается в том, что при множественной корреляции независимых переменных, влияющих на величину, может быть две и более, а при парной – только одна. Эти инструменты используют специалисты при анализе большого количества данных для проведения статистических исследований и выявления сложных зависимостей одной величины от множества других или их отсутствие.

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

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

Как видите, редактор Excel от Microsoft позволяет проводить статистические исследования и выявлять взаимосвязи между массивами данных при помощи встроенных функций. Корреляция дает общее представление о взаимосвязи данных, но более точные результаты можно получить только с использованием нескольких статистических инструментов.

Жми «Нравится» и получай только лучшие посты в Facebook ↓