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

Как создать и интерпретировать корреляционную матрицу в Excel

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

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

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

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

Как создать корреляционную матрицу в Excel

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

Чтобы создать матрицу корреляции для этого набора данных, перейдите на вкладку « Данные » на верхней ленте Excel и нажмите « Анализ данных» .

В новом появившемся окне выберите « Корреляция » и нажмите « ОК ».

Для Input Range выберите ячейки, в которых находятся данные (включая первую строку с метками). Установите флажок рядом с Метки в первой строке.Для выходного диапазона выберите ячейку, в которой вы хотите разместить корреляционную матрицу. Затем нажмите ОК .

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

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

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

Корреляция между очками и подборами: -0,04639. Очки и подборы имеют небольшую отрицательную корреляцию, но это значение настолько близко к нулю, что нет убедительных доказательств значимой связи между этими двумя переменными.

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

Корреляция между подборами и передачами: 0,713713. Подборы и передачи имеют сильную положительную корреляцию. То есть игроки, у которых больше подборов, как правило, и чаще делают передачи.

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

Бонус: визуализация коэффициентов корреляции

Один из простых способов визуализировать значение коэффициентов корреляции в таблице — применить условное форматирование к таблице.

На верхней ленте в Excel перейдите на вкладку « Главная », затем в группу « Стили ».

Нажмите « Таблица условного форматирования » , затем нажмите « Цветовые шкалы » , затем нажмите « Цветовая шкала зелено-желто-красный» .

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

Это помогает нам легко визуализировать силу корреляции между переменными.

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в R:

Матрица корреляции в Excel (пример) — Как создать матрицу корреляции?

Прежде чем перейти к актуальной теме Матрица корреляции в Excel, я хотел бы объяснить, что такое корреляция и где ее можно использовать.

• Согласно английской литературе, слово «корреляция» означает взаимоотношения или связь между двумя или более вещами. С точки зрения статистики, мы сталкиваемся с ситуациями, когда мы стремимся проверить (или выяснить), существует ли какая-либо связь / связь между двумя (или более чем двумя) переменными. Это интересные вещи при проведении статистического анализа. В двух словах, можно сказать, что корреляция представляет собой статистический аналитический процесс, который позволяет нам выяснить связь между двумя или более переменными.
• Корреляция может быть измерена с помощью коэффициента корреляции (обозначается как «r»), который представляет собой числовую меру для определения связи / взаимосвязи между двумя переменными одновременно. Корреляция считается идеальной, если числовое значение коэффициента корреляции равно -1 или +1. Однако почти невозможно определить идеальную корреляцию, если значение коэффициента корреляции равно нулю (0), тогда говорят, что корреляции между двумя исследуемыми переменными нет. Если значение корреляции изменяется от -1 до +1, говорят, что корреляция слабая, умеренная и сильная на основе числового значения коэффициента корреляции.
• Матрица корреляции: Матрица корреляции — это таблица, в которой представлены значения коэффициентов корреляции для различных переменных. Он показывает числовое значение коэффициента корреляции для всех возможных комбинаций переменных. Это в основном используется, когда мы должны выяснить связь между более чем двумя переменными. Он состоит из строк и столбцов, содержащих переменные, а каждая ячейка содержит значения коэффициентов, которые дают информацию о степени ассоциации / линейной взаимосвязи между двумя переменными. Более того, это также может быть использовано в некоторых статистических анализах. Ex. Множественная линейная регрессия, где у нас есть несколько независимых переменных, и корреляционная матрица помогает выяснить степень связи между ними.

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

Как включить Анализ ToolPak в Excel?

В Excel имеется мощный инструмент для проведения всего статистического анализа, с помощью которого мы можем выполнить практически любой важный анализ всего несколькими щелчками мыши. Он называется анализом ToolPak . Если вы не можете увидеть его на вкладке анализа данных, вам, вероятно, нужно добавить его туда. Просто выполните следующие простые шаги.

• В книге Excel выберите Файл.

• Перейти к настройкам.

• В разделе «Параметры» перейдите в « Надстройки»,

• Появится экран, перейдите к опции «Управление» и выберите « Надстройки Excel», а затем нажмите кнопку « Перейти» .

• Под всплывающими надстройками проверьте Пакет инструментов анализа и нажмите OK.

• Вау, вы успешно включили Analysis ToolPak в Excel.

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

Как создать корреляционную матрицу в Excel?

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

Вы можете скачать этот шаблон Матрица корреляции Excel здесь — Шаблон Матрица корреляции Excel

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

Предположим, у нас есть набор данных, содержащий годовые оценки по физике, математике и статистике соответственно. И мы стремимся выяснить, есть ли какая-либо связь / отношения между этими тремя.

Давайте выясним это через матрицу корреляции Excel. Следуй этим шагам.

Шаг 1 — Перейдите на вкладку « Данные » в рабочей книге Excel и нажмите « Анализ данных».

Шаг 2 — появится окно анализа данных. Выберите там Корреляцию и нажмите ОК .

• Откроется окно корреляции.

Шаг 3 — В появившемся окне Корреляция выберите Диапазон ввода в качестве исходных данных, включая заголовки столбцов (в данном случае A1: C10).

Шаг 4 — Поскольку ваши данные сгруппированы по столбцам, нажмите переключатель « Столбцы ».

Шаг 5 — В разделе «Сгруппированы по» следует проверить метки в первой строке, так как заголовки столбцов хранятся в первой строке для справки.

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

В этом примере я дал ссылку на тот же лист, где мои исходные данные (A12).

Шаг 6 — Нажмите OK, когда закончите с настройками.

• Таким образом, результат будет таким, как указано ниже.

Давайте разберем наши выводы через корреляционную матрицу в Excel:

• Ячейки, имеющие значение 1, являются ячейками, связанными с переменными, что имеет корреляцию с самими собой.
• 2267… показывает, что существует положительная корреляция между Марками в физике и Марками в математике. Однако это можно считать слабой положительной корреляцией. Идеально показывает, что увеличение оценок, полученных в физике, приводит к увеличению оценок, полученных в математике на 22, 67%.
• -0.6016… показывает, что существует сильная отрицательная корреляция между Марками в физике и Марками в статистике. Идеально показывает, что увеличение оценок, полученных по физике, приводит к снижению на 60, 14% оценок, полученных по математике.
• 4506… показывает, что существует положительная корреляция между Марками в математике и Марками в статистике. Это идеально показывает, что увеличение оценок, полученных в математике, приводит к увеличению на 45, 06% оценок, полученных в математике.

Что нужно помнить о матрице корреляции в Excel

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

Рекомендуемые статьи

Это руководство по Матрице корреляции в Excel. Здесь мы обсудим, как создать корреляционную матрицу в Excel, а также пример и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

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

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

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

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

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

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

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

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

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

1. Переходим во вкладку «Файл». В левом вертикальном меню окна, которое откроется после этого, щелкаем по пункту «Параметры».
2. После запуска окна параметров посредством его левого вертикального меню переходим в раздел «Надстройки». Там в самом низу правой части окна располагается поле «Управление». Переставляем переключатель в нём в позицию «Надстройки Excel», если отображен другой параметр. После этого клацаем по кнопке «Перейти…», находящейся справа от указанного поля.
3. Происходит запуск небольшого окошка «Надстройки». Устанавливаем флажок около параметра «Пакет анализа». Затем в правой части окна кликаем по кнопке «OK».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Расчет коэффициента корреляции в Excel

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

Значения показателей x и y:

Y – независимая переменная, x – зависимая. Необходимо найти силу (сильная / слабая) и направление (прямая / обратная) связи между ними. Формула коэффициента корреляции выглядит так:

Чтобы упростить ее понимание, разобьем на несколько несложных элементов.

1. Найдем средние значения переменных, используя функцию СРЗНАЧ:
2. Посчитаем разницу каждого y и yсредн., каждого х и хсредн. Используем математический оператор «-».
3. Теперь перемножим найденные разности:
4. Найдем сумму значений в данной колонке. Это и будет числитель.
5. Для расчета знаменателя разницы y и y-средн., х и х-средн. Нужно возвести в квадрат.
6. Находим суммы значений в полученных колонках (с помощью функции АВТОСУММА). Перемножаем их. Результат возводим в квадрат (функция КОРЕНЬ).
7. Осталось посчитать частное (числитель и знаменатель уже известны).

Между переменными определяется сильная прямая связь.

Встроенная функция КОРРЕЛ позволяет избежать сложных расчетов. Рассчитаем коэффициент парной корреляции в Excel с ее помощью. Вызываем мастер функций. Находим нужную. Аргументы функции – массив значений y и массив значений х:

Покажем значения переменных на графике:

Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.

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

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

Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».

1. На вкладке «Данные» в группе «Анализ» открываем пакет «Анализ данных» (для версии 2007). Если кнопка недоступна, нужно ее добавить («Параметры Excel» — «Надстройки»). В списке инструментов анализа выбираем «Корреляция».
2. Нажимаем ОК. Задаем параметры для анализа данных. Входной интервал – диапазон ячеек со значениями. Группирование – по столбцам (анализируемые данные сгруппированы в столбцы). Выходной интервал – ссылка на ячейку, с которой начнется построение матрицы. Размер диапазона определится автоматически.
3. После нажатия ОК в выходном диапазоне появляется корреляционная матрица. На пересечении строк и столбцов – коэффициенты корреляции. Если координаты совпадают, то выводится значение 1.

Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.

Изобразим наглядно корреляционные отношения с помощью графиков.

1. Сильная прямая связь между y и х1.
2. Сильная обратная связь между y и х2. Изменения значений происходят параллельно друг другу. Но если y растет, х падает. Значения y увеличиваются – значения х уменьшаются.
3. Отсутствие взаимосвязи между значениями y и х3. Изменения х3 происходят хаотично и никак не соотносятся с изменениями y.

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