Вычислить линейный коэффициент парной корреляции excel
Обновлено: 07.07.2024
Коэффициент корреляции отражает степень взаимосвязи между двумя показателями. Всегда принимает значение от -1 до 1. Если коэффициент расположился около 0, то говорят об отсутствии связи между переменными.
Если значение близко к единице (от 0,9, например), то между наблюдаемыми объектами существует сильная прямая взаимосвязь. Если коэффициент близок к другой крайней точке диапазона (-1), то между переменными имеется сильная обратная взаимосвязь. Когда значение находится где-то посередине от 0 до 1 или от 0 до -1, то речь идет о слабой связи (прямой или обратной). Такую взаимосвязь обычно не учитывают: считается, что ее нет.
Расчет коэффициента корреляции в Excel
Рассмотрим на примере способы расчета коэффициента корреляции, особенности прямой и обратной взаимосвязи между переменными.
Значения показателей x и y:
Y – независимая переменная, x – зависимая. Необходимо найти силу (сильная / слабая) и направление (прямая / обратная) связи между ними. Формула коэффициента корреляции выглядит так:
Чтобы упростить ее понимание, разобьем на несколько несложных элементов.
- Найдем средние значения переменных, используя функцию СРЗНАЧ:
- Посчитаем разницу каждого y и yсредн., каждого х и хсредн. Используем математический оператор «-».
- Теперь перемножим найденные разности:
- Найдем сумму значений в данной колонке. Это и будет числитель.
- Для расчета знаменателя разницы y и y-средн., х и х-средн. Нужно возвести в квадрат.
- Находим суммы значений в полученных колонках (с помощью функции АВТОСУММА). Перемножаем их. Результат возводим в квадрат (функция КОРЕНЬ).
- Осталось посчитать частное (числитель и знаменатель уже известны).
Между переменными определяется сильная прямая связь.
Встроенная функция КОРРЕЛ позволяет избежать сложных расчетов. Рассчитаем коэффициент парной корреляции в Excel с ее помощью. Вызываем мастер функций. Находим нужную. Аргументы функции – массив значений y и массив значений х:
Покажем значения переменных на графике:
Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.
Матрица парных коэффициентов корреляции в Excel
Корреляционная матрица представляет собой таблицу, на пересечении строк и столбцов которой находятся коэффициенты корреляции между соответствующими значениями. Имеет смысл ее строить для нескольких переменных.
Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».
Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.
Изобразим наглядно корреляционные отношения с помощью графиков.
- Сильная прямая связь между y и х1.
- Сильная обратная связь между y и х2. Изменения значений происходят параллельно друг другу. Но если y растет, х падает. Значения y увеличиваются – значения х уменьшаются.
- Отсутствие взаимосвязи между значениями y и х3. Изменения х3 происходят хаотично и никак не соотносятся с изменениями y.
Для чего нужен такой коэффициент? Для определения взаимосвязи между наблюдаемыми явлениями и составления прогнозов.
Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.
Суть корреляционного анализа
Предназначение корреляционного анализа сводится к выявлению наличия зависимости между различными факторами. То есть, определяется, влияет ли уменьшение или увеличение одного показателя на изменение другого.
Если зависимость установлена, то определяется коэффициент корреляции. В отличие от регрессионного анализа, это единственный показатель, который рассчитывает данный метод статистического исследования. Коэффициент корреляции варьируется в диапазоне от +1 до -1. При наличии положительной корреляции увеличение одного показателя способствует увеличению второго. При отрицательной корреляции увеличение одного показателя влечет за собой уменьшение другого. Чем больше модуль коэффициента корреляции, тем заметнее изменение одного показателя отражается на изменении второго. При коэффициенте равном 0 зависимость между ними отсутствует полностью.
Расчет коэффициента корреляции
Теперь давайте попробуем посчитать коэффициент корреляции на конкретном примере. Имеем таблицу, в которой помесячно расписана в отдельных колонках затрата на рекламу и величина продаж. Нам предстоит выяснить степень зависимости количества продаж от суммы денежных средств, которая была потрачена на рекламу.
Способ 1: определение корреляции через Мастер функций
Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).
-
Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.
В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.
Как видим, коэффициент корреляции в виде числа появляется в заранее выбранной нами ячейке. В данном случае он равен 0,97, что является очень высоким признаком зависимости одной величины от другой.
Способ 2: вычисление корреляции с помощью пакета анализа
Кроме того, корреляцию можно вычислить с помощью одного из инструментов, который представлен в пакете анализа. Но прежде нам нужно этот инструмент активировать.
-
Переходим во вкладку «Файл».
Параметр «Группирование» оставляем без изменений – «По столбцам», так как у нас группы данных разбиты именно на два столбца. Если бы они были разбиты построчно, то тогда следовало бы переставить переключатель в позицию «По строкам».
В параметрах вывода по умолчанию установлен пункт «Новый рабочий лист», то есть, данные будут выводиться на другом листе. Можно изменить место, переставив переключатель. Это может быть текущий лист (тогда вы должны будете указать координаты ячеек вывода информации) или новая рабочая книга (файл).
Так как место вывода результатов анализа было оставлено по умолчанию, мы перемещаемся на новый лист. Как видим, тут указан коэффициент корреляции. Естественно, он тот же, что и при использовании первого способа – 0,97. Это объясняется тем, что оба варианта выполняют одни и те же вычисления, просто произвести их можно разными способами.
Как видим, приложение Эксель предлагает сразу два способа корреляционного анализа. Результат вычислений, если вы все сделаете правильно, будет полностью идентичным. Но, каждый пользователь может выбрать более удобный для него вариант осуществления расчета.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Функция КОРРЕЛ в Excel используется для расчета коэффициента корреляции между для двух исследуемых массивов данных и возвращает соответствующее числовое значение.
Примеры использования функции КОРРЕЛ в Excel
Пример 1. В таблице Excel содержатся данные о курсе доллара и средней зарплате сотрудников фирмы на протяжении нескольких лет. Определить взаимосвязь между курсом валюты и средней зарплатой.
Формула для расчета:
- B3:B13 – диапазон ячеек, в которых хранятся данные о среднем курсе доллара;
- C3:C13 – диапазон ячеек со значениями средней зарплаты.
Полученный результат близок к 1 и свидетельствует о сильной прямой взаимосвязи между исследуемыми величинами. Однако прямо пропорциональной зависимости между ними нет, то есть на увеличение средней зарплаты оказывали влияние и прочие факторы.
Определение коэффициента корреляции влияния действий на результат
Пример 2. Два сильных кандидата на руководящий пост воспользовались услугами двух различных пиар-агентств для запуска предвыборной компании, которая длилась 15 дней. Ежедневно проводился соцопрос независимыми исследователями, которые определяли процент поддержки одного и второго кандидата. Респонденты могли отдавать предпочтение первому, второму кандидату или выступать против обоих. Определить, насколько влияла каждая предвыборная кампания на степень поддержки кандидатов, какая из них оказалась более эффективной?
Произведем расчет коэффициентов корреляции с помощью формул:
- A3:A17 – массив ячеек, содержащий номера дней предвыборной кампании;
- B3:B17 и C3:C17 – диапазон ячеек, содержащие данные о проценте поддержки первого и второго кандидатов соответственно.
Как видно, уровень поддержки первого кандидата увеличивался с каждым днем кампании, поэтому коэффициент корреляции в первом случае стремится к единице. На старте кампании второй кандидат имел больший процент поддержки, и это значение на протяжении первых пяти дней демонстрировало положительную динамику изменений. Однако затем уровень поддержки стал снижаться, и к 15-му дню упал ниже начального значения. Отрицательное значение коэффициента корреляции свидетельствует о негативном эффекте кампании. Однако на события могли оказывать влияние различные факторы, например, опубликованные компрометирующие материалы. В связи с этим полагаться только на значение коэффициента корреляции в данном случае нельзя. То есть, коэффициент корреляции не характеризует причинно-наследственную связь.
Определим наличие взаимосвязи между двумя параметрами по формуле:
0,7;ЕСЛИ(КОРРЕЛ(A3:A8;B3:B8)>0,7;"Сильная прямая зависимость";"Сильная обратная зависимость");"Слабая зависимость или ее отсутствие")' >
Если модуль коэффициента корреляции больше 0,7, считается рациональным использование функции линейной регрессии (y=ax+b) для описания связи между двумя величинами. В данном случае:
0,7;ПРЕДСКАЗ(D7;B3:B8;A3:A8);"Величины не взаимосвязаны")' >
Коэффициент корреляции – один из множества статистических критериев определения наличия взаимосвязи между двумя рядами значений. Для построения точных статистических моделей рекомендуется использовать дополнительные параметры, такие как коэффициент детерминации, стандартная ошибка и другие.
Особенности использования функции КОРРЕЛ в Excel
Функция КОРРЕЛ имеет следующий синтаксис:
- массив1 – обязательный аргумент, содержащий диапазон ячеек или массив данных, которые характеризуют изменения свойства какого-либо объекта.
- массив2 – обязательный аргумент (диапазон ячеек либо массив), элементы которого характеризуют изменение свойств второго объекта.
Примечание 2: Коэффициент корреляции представляет собой количественную характеристику степени взаимосвязи между двумя свойствами объектов. Этот коэффициент может принимать значения из диапазона от -1 до 1, при этом:
- Если значение коэффициента приближается к 1 или -1, между двумя исследуемыми свойствами существует сильная прямая или обратная взаимосвязи соответственно.
- Если значение коэффициента стремится к 0,5 или -0,5, два свойства слабо прямо или обратно взаимосвязаны друг с другом соответственно.
- Если коэффициент корреляции близок к 0 (нулю), между двумя исследуемыми свойствами отсутствует прямая либо обратная взаимосвязи.
Примечание 3: Для понимания смысла коэффициента корреляции можно привести два простых примера:
Для определения степени зависимости между несколькими показателями применяется множественные коэффициенты корреляции. Их затем сводят в отдельную таблицу, которая имеет название корреляционной матрицы. Наименованиями строк и столбцов такой матрицы являются названия параметров, зависимость которых друг от друга устанавливается. На пересечении строк и столбцов располагаются соответствующие коэффициенты корреляции. Давайте выясним, как можно провести подобный расчет с помощью инструментов Excel.
Вычисление множественного коэффициента корреляции
Принято следующим образом определять уровень взаимосвязи между различными показателями, в зависимости от коэффициента корреляции:
- 0 – 0,3 – связь отсутствует;
- 0,3 – 0,5 – связь слабая;
- 0,5 – 0,7 – средняя связь;
- 0,7 – 0,9 – высокая;
- 0,9 – 1 – очень сильная.
Если корреляционный коэффициент отрицательный, то это значит, что связь параметров обратная.
Для того, чтобы составить корреляционную матрицу в Экселе, используется один инструмент, входящий в пакет «Анализ данных». Он так и называется – «Корреляция». Давайте узнаем, как с помощью него можно вычислить показатели множественной корреляции.
Этап 1: активация пакета анализа
Сразу нужно сказать, что по умолчанию пакет «Анализ данных» отключен. Поэтому, прежде чем приступить к процедуре непосредственного вычисления коэффициентов корреляции, нужно его активировать. К сожалению, далеко не каждый пользователь знает, как это делать. Поэтому мы остановимся на данном вопросе.
- Переходим во вкладку «Файл». В левом вертикальном меню окна, которое откроется после этого, щелкаем по пункту «Параметры».
После указанного действия пакет инструментов «Анализ данных» будет активирован.
Этап 2: расчет коэффициента
Теперь можно переходить непосредственно к расчету множественного коэффициента корреляции. Давайте на примере представленной ниже таблицы показателей производительности труда, фондовооруженности и энерговооруженности на различных предприятиях рассчитаем множественный коэффициент корреляции указанных факторов.
-
Перемещаемся во вкладку «Данные». Как видим, на ленте появился новый блок инструментов «Анализ». Клацаем по кнопке «Анализ данных», которая располагается в нём.
Так как у нас факторы разбиты по столбцам, а не по строкам, то в параметре «Группирование» выставляем переключатель в позицию «По столбцам». Впрочем, он там уже и так установлен по умолчанию. Поэтому остается только проверить правильность его расположения.
Около пункта «Метки в первой строке» галочку ставить не обязательно. Поэтому мы пропустим данный параметр, так как он не повлияет на общий характер расчета.
В блоке настроек «Параметр вывода» следует указать, где именно будет располагаться наша корреляционная матрица, в которую выводится результат расчета. Доступны три варианта:
- Новая книга (другой файл);
- Новый лист (при желании в специальном поле можно дать ему наименование);
- Диапазон на текущем листе.
Давайте выберем последний вариант. Переставляем переключатель в положение «Выходной интервал». В этом случае в соответствующем поле нужно указать адрес диапазона матрицы или хотя бы её верхнюю левую ячейку. Устанавливаем курсор в поле и клацаем по ячейке на листе, которую планируем сделать верхним левым элементом диапазона вывода данных.
Этап 3: анализ полученного результата
Теперь давайте разберемся, как понимать тот результат, который мы получили в процессе обработки данных инструментом «Корреляция» в программе Excel.
Как видим из таблицы, коэффициент корреляции фондовооруженности (Столбец 2) и энерговооруженности (Столбец 1) составляет 0,92, что соответствует очень сильной взаимосвязи. Между производительностью труда (Столбец 3) и энерговооруженностью (Столбец 1) данный показатель равен 0,72, что является высокой степенью зависимости. Коэффициент корреляции между производительностью труда (Столбец 3) и фондовооруженностью (Столбец 2) равен 0,88, что тоже соответствует высокой степени зависимости. Таким образом, можно сказать, что зависимость между всеми изучаемыми факторами прослеживается довольно сильная.
Как видим, пакет «Анализ данных» в Экселе представляет собой очень удобный и довольно легкий в обращении инструмент для определения множественного коэффициента корреляции. С его же помощью можно производить расчет и обычной корреляции между двумя факторами.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: