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

Обновлено: 05.07.2024

Объяснение

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

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

Чтобы создать квадратную ковариационную матрицу 3 × 3, нам нужны трехмерные данные. Диагональные значения матрицы представляют собой дисперсии переменных X, Y и Z (т. Е. COV (X, X), COV (Y, Y) и COV (Z, Z)). Ковариационная матрица симметрична относительно диагонали. Это означает, что COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) и COV (Y, Z) = COV (Z, Y). Об этой матрице следует помнить, что она является результатом ковариационной матрицы NXN для данных n-мерного размера.

Как использовать матрицу ковариации в Excel?

Ковариационная матрица используется в различных приложениях, в том числе

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

Ниже приведены некоторые примеры использования ковариационной матрицы в Excel.

Выполнение ковариационного анализа оценок, полученных студентами по разным предметам.

Шаг 1: Следующие данные, включая оценки учащихся по математике, английскому языку и естествознанию, считаются такими, как показано на рисунке.

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

Если пакет инструментов «Анализ данных» недоступен, выполните следующие действия.

Шаг А: Перейдите на вкладку «Файл» и выберите «Параметры».

Откроется следующий экран.

Шаг B: Зайдите в Надстройки. В разделе «Параметры управления» убедитесь, что выбран «Надстройки Excel», и нажмите кнопку «Перейти», как показано на рисунке.

Шаг C: Выберите «Analysis-Tool Pak» и «Analysis-ToolPak VBA», как показано на снимке экрана.

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

Шаг 3: Щелкните Анализ данных. Откроется диалоговое окно «Анализ данных». Выберите «Ковариацию», прокрутив вверх, и нажмите «ОК».

Он отображает диалоговое окно «Ковариация».

Шаг 5: Выберите диапазон ввода, включая имена субъектов, отметьте «метки в первой строке» и укажите «диапазон вывода» на существующем листе. И нажмите «ОК».

Верхняя часть диагонали пуста, так как ковариационная матрица Excel симметрична относительно диагонали.

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

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

Шаг 2: Открывает диалоговое окно «Анализ данных» и выбирает «Ковариацию», прокручивая вверх и нажимая «ОК».

Он отображает диалоговое окно «Ковариация».

Шаг 3: Выберите диапазон ввода, включая заголовки, отметьте «метки в первой строке» и укажите «диапазон вывода» на существующем листе. И нажмите «ОК».

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

Расчет ковариационной матрицы котировок акций корпоративных компаний

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

Шаг 2: Открывает диалоговое окно «Анализ данных», выбирает «Ковариацию», прокручивая вверх, и нажимает «ОК».

Он отображает диалоговое окно «Ковариация».

Шаг 3: Выберите диапазон ввода, включая заголовки, отметьте «метки в первой строке» и укажите «диапазон вывода» на существующем листе и нажмите «ОК».

Корреляция в Microsoft Excel

Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.

Суть корреляционного анализа

Предназначение корреляционного анализа сводится к выявлению наличия зависимости между различными факторами. То есть, определяется, влияет ли уменьшение или увеличение одного показателя на изменение другого.

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

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

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

Способ 1: определение корреляции через Мастер функций

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

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

Переход в мастер функций для корреляции в Microsoft Excel

Функция КОРРЕЛ в Мастере функций в Microsoft Excel

В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.

Аргументы функции КОРРЕЛ в Microsoft Excel

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

Результат функции КОРРЕЛ в Microsoft Excel

Способ 2: вычисление корреляции с помощью пакета анализа

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

    Переходим во вкладку «Файл».

Переход во вкладку Файл в Microsoft Excel

Переход в раздел Параметры в Microsoft Excel

Переход в надстройки в Microsoft Excel

Переход в надстройки Excel в Microsoft Excel

Включение пакета анализа в Microsoft Excel

Переход в анализ данных в Microsoft Excel

Переход в Корреляцию в Microsoft Excel

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

В параметрах вывода по умолчанию установлен пункт «Новый рабочий лист», то есть, данные будут выводиться на другом листе. Можно изменить место, переставив переключатель. Это может быть текущий лист (тогда вы должны будете указать координаты ячеек вывода информации) или новая рабочая книга (файл).

Параметры для рассчета корреляции в Microsoft Excel

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

Расчет корреляции в Microsoft Excel

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

Закрыть

Мы рады, что смогли помочь Вам в решении проблемы.

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

Закрыть

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Объяснение

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

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

Теперь давайте посмотрим, как найти корреляционную матрицу в Excel с помощью пакета Analysis Toolpak в Excel.

Если этот параметр недоступен, добавьте его из списка надстроек. Добавить,

  • В разделе «Параметры» выберите вкладку «Надстройки», затем нажмите кнопку «Перейти» рядом с раскрывающимся списком в поле «Управление».
  • Установите флажок для пакета инструментов анализа и нажмите ОК.

Набор инструментов будет добавлен на вкладку «Данные» в разделе «Анализ» как «Анализ данных».

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

Появится всплывающее окно с запросом диапазона ввода.

  • Выберите диапазон данных переменных в поле диапазона ввода.
  • Установите флажок для меток в первой строке (если у вас есть метки переменных в первой строке)
  • Выберите параметр «Диапазон вывода» и выберите / введите номер ячейки, в которой вы хотите получить таблицу результатов. Щелкните ОК.
  • Это таблица результатов корреляции для переменных A и B.

Давайте посмотрим на пример корреляционной матрицы в Excel для нескольких переменных.

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

Появится всплывающее окно с запросом диапазона ввода.

  • Выберите диапазон данных переменных в поле диапазона ввода.
  • Установите флажок для меток в первой строке (если у вас есть метки переменных в первой строке)
  • Выберите опцию Output Range и выберите / введите номер ячейки, в которой вы хотите получить таблицу результатов.
  • Щелкните ОК.

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

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

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

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

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

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

Показатели x и y.

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

Формула коэффициента корреляции.

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

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

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

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

КОРРЕЛ.

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

График.

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

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

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

Переменные.

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

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

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

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

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

Читайте также: