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

Что такое корреляция простыми словами

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

Значение коэффициента может меняться от -1 до +1:

Если значение близко к единице или минус единице — значит два явления так или иначе сильно взаимосвязаны. Впрочем, причины этого не всегда очевидны — явление А может влиять на явление B, может быть наоборот. Нередко бывает, что существует явление C, которое приводит в движение А и В одновременно. В общем, природа корреляции — это уже второй вопрос, которым должны заниматься исследователи.

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

При высокой положительной корреляции вслед за графиком А растёт и график B, и чем выше значение, тем слаженнее оба движутся. Для наглядности, вот как выглядит корреляция +1:

Движения графиков полностью повторяют друг друга, причем это как в случае простого добавления, так и с множителем.

При сильной отрицательной корреляции рост графика А приводит к падению графика B и наоборот. Вот так выглядит корреляция -1:

Движения графиков похожи на зеркальные отражения.

Коэффициент корреляции — удобный инструмент для анализа во многих сферах науки и жизни. Его легко рассчитать в Excel и применить, поэтому самая большая сложность в работе с ним — грамотно подобрать данные для расчёта. Основное правило — чем больше данных, тем лучше. Многие взаимосвязи проявляют себя лишь на длинной дистанции.

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

Как выполнить корреляцию в Excel?

Самым трудоемким этапом определения корреляции является набор массива данных. Сравниваемые данные располагаются обычно в двух колонках или строчках. Таблицу следует делать без пропусков в ячейках. Современные версии Excel (с 2007 и младше) не требуют установок дополнительных настроек для статистических расчетов; необходимые манипуляции можно сделать в разделе формул:

  1. Выбрать пустую ячейку, в которую будет выведен результат расчетов.
  2. Нажать в главном меню Excel пункт «Формулы».
  3. Среди кнопок, сгруппированных в «Библиотеку функций», выбрать «Другие функции».
  4. В выпадающих списках выбрать функцию расчета корреляции (Статистические — КОРРЕЛ).
  5. В Excel откроется панель «Аргументы функции». «Массив 1» и «Массив 2» — это диапазоны сравниваемых данных. Для автоматического заполнения этих полей можно просто выделить нужные ячейки таблицы.
  6. Нажать «ОК», закрыв окно аргументов функции. В ячейке появится подсчитанный коэффициент корреляции.

Корреляция может быть прямая (если коэффициент больше нуля) и обратная (от -1 до 0).

Первая означает, что при росте одного параметра растет и другой. Обратная (отрицательная) корреляция отражает факт, что при росте одной переменной другая уменьшается.

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

Если коэффициент показывает среднюю или сильную взаимосвязь (от ±0,5 до ±0,99), следует помнить, что это лишь статистическая взаимосвязь, которая вовсе не гарантирует влияние одного параметра на другой. Также нельзя исключать ситуации, что оба параметра независимы друг от друга, но на них воздействует какой-нибудь третий неучтенный фактор. Excel помогает моментально вычислить коэффициент корреляции, но обычно только количественных методов недостаточно для установления причинно-следственных связей в соотносимых выборках.

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

​ полностью.​ к 0,5 или​=КОРРЕЛ(массив1;массив2)​ В связи с​ зарплаты.​ данные сгруппированы в​ х и хсредн.​ вместе.​ Что справедливо.​

​ «Перейти». Жмем.​ приоритеты. И основываясь​ в процессе обработки​ поле окна​ нём в позицию​ и столбцов располагаются​ –​ одной величины от​Теперь давайте попробуем посчитать​ -0,5, два свойства​Описание аргументов:​ этим полагаться только​Результат расчетов:​ столбцы). Выходной интервал​ Используем математический оператор​Пример:​​Открывается список доступных надстроек.​ на главных факторах,​ данных инструментом​«Корреляция»​

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

​«Надстройки Excel»​ соответствующие коэффициенты корреляции.​«По столбцам»​ другой.​ коэффициент корреляции на​

​ слабо прямо или​массив1 – обязательный аргумент,​

​ на значение коэффициента​Полученный результат близок к​ – ссылка на​ «-».​Строим корреляционное поле: «Вставка»​Корреляционный анализ помогает установить,​ Выбираем «Пакет анализа»​ прогнозировать, планировать развитие​«Корреляция»​

​.​, если отображен другой​ Давайте выясним, как​

  1. ​, так как у​Кроме того, корреляцию можно​
  2. ​ конкретном примере. Имеем​ обратно взаимосвязаны друг​ содержащий диапазон ячеек​ корреляции в данном​ 1 и свидетельствует​
  3. ​ ячейку, с которой​
  4. ​Теперь перемножим найденные разности:​ — «Диаграмма» -​ есть ли между​
  5. ​ и нажимаем ОК.​ приоритетных направлений, принимать​в программе Excel.​Так как у нас​ параметр. После этого​
  6. ​ можно провести подобный​ нас группы данных​ вычислить с помощью​ таблицу, в которой​ с другом соответственно.​ или массив данных,​
  7. ​ случае нельзя. То​ о сильной прямой​ начнется построение матрицы.​

​Найдем сумму значений в​ «Точечная диаграмма» (дает​

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

​Если коэффициент корреляции близок​ которые характеризуют изменения​

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

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

​«Перейти…»​ инструментов Excel.​ два столбца. Если​ который представлен в​ отдельных колонках затрата​ к 0 (нулю),​ свойства какого-либо объекта.​ не характеризует причинно-наследственную​

​ величинами. Однако прямо​ автоматически.​ и будет числитель.​ значений – все​ связь. Например, между​

  1. ​ «Данные».​линейной (у = а​(Столбец 2​ по строкам, то​, находящейся справа от​Скачать последнюю версию​ бы они были​ пакете анализа. Но​ на рекламу и​ между двумя исследуемыми​
  2. ​массив2 – обязательный аргумент​ связь.​ пропорциональной зависимости между​После нажатия ОК в​Для расчета знаменателя разницы​ числовые данные таблицы.​ временем работы станка​Теперь займемся непосредственно регрессионным​ + bx);​) и энерговооруженности (​ в параметре​ указанного поля.​ Excel​
  3. ​ разбиты построчно, то​ прежде нам нужно​ величина продаж. Нам​ свойствами отсутствует прямая​ (диапазон ячеек либо​Пример 3. Владелец канала​ ними нет, то​ выходном диапазоне появляется​

​ y и y-средн.,​Щелкаем левой кнопкой мыши​ и стоимостью ремонта,​ анализом.​параболической (y = a​Столбец 1​«Группирование»​Происходит запуск небольшого окошка​

  1. ​ этот инструмент активировать.​ предстоит выяснить степень​
  2. ​ либо обратная взаимосвязи.​ массив), элементы которого​ YouTube использует социальную​ есть на увеличение​ корреляционная матрица. На​ х и х-средн.​ по любой точке​ ценой техники и​Открываем меню инструмента «Анализ​
  3. ​ + bx +​) составляет 0,92, что​выставляем переключатель в​«Надстройки»​ в Экселе​ переставить переключатель в​

​Переходим во вкладку​ зависимости количества продаж​

​Примечание 3: Для понимания​ характеризуют изменение свойств​ сеть для рекламы​ средней зарплаты оказывали​ пересечении строк и​

exceltable.com>

Задачи корреляционного анализа

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

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

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

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

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

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

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

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

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

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

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

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

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

Анализ популярности контента по корреляции просмотров и репостов видео

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

Определим наличие взаимосвязи между двумя параметрами по формуле:

0,7;ЕСЛИ(КОРРЕЛ(A3:A8;B3:B8)>0,7;”Сильная прямая зависимость”;”Сильная обратная зависимость”);”Слабая зависимость или ее отсутствие”)’ >

Если модуль коэффициента корреляции больше 0,7, считается рациональным использование функции линейной регрессии (y=ax+b) для описания связи между двумя величинами. В данном случае:

Построим график зависимости числа просмотров от количества репостов, отобразим линию тренда и ее уравнение:

Используем данное уравнение для определения количества просмотров при 200, 500 и 1000 репостов:

Аналогичное уравнение использует функция ПРЕДСКАЗ. То есть, чтобы найти количество просмотров в случае, если было сделано, например, 250 репостов, можно использовать формулу:

0,7;ПРЕДСКАЗ(D7;B3:B8;A3:A8);”Величины не взаимосвязаны”)’ >

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

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

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

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

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

Где x·y , x , y — средние значения выборок; σ(x), σ(y) — среднеквадратические отклонения.
Кроме того, коэффициент линейной парной корреляции может быть определен через коэффициент регрессии b: , где σ(x)=S(x), σ(y)=S(y) — среднеквадратические отклонения, b — коэффициент перед x в уравнении регрессии y=a+bx .

Другие варианты формул:
или К xy — корреляционный момент (коэффициент ковариации)

Линейный коэффициент корреляции принимает значения от –1 до +1 (см. шкалу Чеддока). Например, при анализе тесноты линейной корреляционной связи между двумя переменными получен коэффициент парной линейной корреляции, равный –1 . Это означает, что между переменными существует точная обратная линейная зависимость.

Геометрический смысл коэффициента корреляции

Свойства коэффициента корреляции

  1. |r xy | ≤ 1;
  2. если X и Y независимы, то r xy =0, обратное не всегда верно;
  3. если |r xy |=1, то Y=aX+b, |r xy (X,aX+b)|=1, где a и b постоянные, а ≠ 0;
  4. |r xy (X,Y)|=|r xy (a 1 X+b 1 , a 2 X+b 2)|, где a 1 , a 2 , b 1 , b 2 – постоянные.

Инструкция
. Укажите количество исходных данных. Полученное решение сохраняется в файле Word
(см. Пример нахождения уравнения регрессии). Также автоматически создается шаблон решения в Excel
. .

1.Открыть программу Excel

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

1 столбик — № испытуемого

2 столбик — агрессивность
в баллах

3 столбик — неуверенность в себе
в баллах

3.Затем необходимо выбрать пустую ячейку рядом с таблицей и нажать на значок f(x)
в панели Excel

4.Откроется меню функций, среди категорий необходимо выбрать Статистические

, а затем среди списка функций по алфавиту найти КОРРЕЛ
и нажать ОК

5.Затем откроется меню аргументов функции, которое позволит выбрать нужные нам столбики с данными. Для выбора первого столбика Агрессивность
нужно нажать на синюю кнопочку у строки Массив1

6.Выберем данные для Массива1
из столбика Агрессивность
и нажмем на синюю кнопочку в диалоговом окне

7. Затем аналогично Массиву 1 нажмём на синюю кнопочку у строки Массив2

8.Выберем данные для Массива2
— столбик Неуверенность в себе
и опять нажмем синюю кнопку, затем ОК

9.Вот, коэффициент корреляции r-Пирсона посчитан и записан в выбранной ячейке.В нашем случае он положительный и приблизительно равен 0,225
. Это говорит об умеренной положительной
связи между агрессивностью и неуверенностью в себе у детей-первоклассников

Таким образом, статистическим выводом
эксперимента будет: r = 0,225, выявлена умеренная положительная взаимосвязь между переменными агрессивность
и неуверенность в себе.

В некоторых исследованиях требуется указывать р-уровень значимости коэффициента корреляции, однако программа Excel, в отличие от SPSS, не предоставляет такой возможности. Ничего страшного, есть (А.Д. Наследов).

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

Использование корреляции

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

Рассчитать корреляцию можно вручную, выполняя несложные арифметические действия. Однако процесс вычисления оказывается очень трудоемким, если набор данных велик. Особенность метода в том, что он требует сбора большого количества исходных данных, чтобы наиболее точно отобразить, есть ли связь между признаками. Поэтому серьезное использование корреляционного анализа невозможно без применения вычислительной техники. Одной из наиболее популярных и доступных программ для решения этой задачи является Microsoft Office Excel.

Ложные корреляции

Дело в том, что с помощью коэффициента корреляции можно проверить на взаимосвязь любые явления, которые можно выразить в числовом выражении. То есть, реально любые — например количество свадеб в Нью-Йорке и объем импорта нефти в США из Норвегии:


tylervigen.com — если знаете английский, сможете отыскать на сайте еще больше странных корреляций

Корреляция составила 86%! Действительно ли свадьбы влияют на экспорт нефти? Разумеется, нет — подобная зависимость совершенно случайна. Именно так выглядит ловушка ложной корреляции — она может показать взаимосвязь там, где её на самом деле нет.

Не хочу сильно заострять внимание на этой проблеме, так что если интересно поразбираться — нашел для вас видео, в котором найдете еще несколько примеров странных взаимосвязей и причины их появления:

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

Диаграмма рассеяния в Excel и сферы ее применения

Использование корреляции

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

Рассчитать корреляцию можно вручную, выполняя несложные арифметические действия. Однако процесс вычисления оказывается очень трудоемким, если набор данных велик. Особенность метода в том, что он требует сбора большого количества исходных данных, чтобы наиболее точно отобразить, есть ли связь между признаками. Поэтому серьезное использование корреляционного анализа невозможно без применения вычислительной техники. Одной из наиболее популярных и доступных программ для решения этой задачи является Microsoft Office Excel.

Виды коэффициента корреляции

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

В зависимости от анализируемых значений рассчитывается коэффициент:

  • Пирсона;
  • Спирмена;
  • Кендала;
  • знаков Фехнера;
  • конкорддации или множественной ранговой корреляции.

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

  • точного установления корреляционной силы;
  • сравнения количественных признаков.

Недостатков использования линейного корреляционного коэффициента Пирсона немного:

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

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

Метод Спирмена рекомендуется применять в ситуациях:

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

Метод Спирмена относится к методам непараметрического анализа, поэтому нет необходимости проверять нормальность распределения признака. К тому же он позволяет сравнивать показатели, выраженные в разных шкалах. Например, сравнение значений количества эритроцитов в определенном объеме крови (непрерывная шкала) и экспертной оценки, выражаемой в баллах (порядковая шкала).

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

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

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

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

Примеры использования функции КОРРЕЛ в Excel

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

Таблица данных:

Формула для расчета:

Описание аргументов:

  • B3:B13 – диапазон ячеек, в которых хранятся данные о среднем курсе доллара;
  • C3:C13 – диапазон ячеек со значениями средней зарплаты.

Результат расчетов:

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

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

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

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

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

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

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

Замечания

Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения игнорируются; Тем не менее, ячейки, содержащие нулевые значения, учитываются.

Если аргумент “массив1” и “массив2” имеют различное количество точек данных, КОРРЕЛ возвращает ошибку #N/A.

Если аргумент массив1 или массив2 пуст или если ( стандартное отклонение) их значений равны нулю, КОРРЕЛ возвращает значение #DIV/0! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

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

Уравнение для коэффициента корреляции имеет следующий вид:

являются средними значениями выборок СРЗНАЧ(массив1) и СРЗНАЧ(массив2).

Определение коэффициента корреляции влияния действий на результат

Пример 2. Два сильных кандидата на руководящий пост воспользовались услугами двух различных пиар-агентств для запуска предвыборной компании, которая длилась 15 дней. Ежедневно проводился соцопрос независимыми исследователями, которые определяли процент поддержки одного и второго кандидата. Респонденты могли отдавать предпочтение первому, второму кандидату или выступать против обоих. Определить, насколько влияла каждая предвыборная кампания на степень поддержки кандидатов, какая из них оказалась более эффективной?

Исходные данные:

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

=КОРРЕЛ(A3:A17;B3:B17)

=КОРРЕЛ(A3:A17;C3:C17)

Описание аргументов:

  • A3:A17 – массив ячеек, содержащий номера дней предвыборной кампании;
  • B3:B17 и C3:C17 – диапазон ячеек, содержащие данные о проценте поддержки первого и второго кандидатов соответственно.

Полученные результаты:

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

Как выполнить корреляцию в Excel?

Самым трудоемким этапом определения корреляции является набор массива данных. Сравниваемые данные располагаются обычно в двух колонках или строчках. Таблицу следует делать без пропусков в ячейках. Современные версии Excel (с 2007 и младше) не требуют установок дополнительных настроек для статистических расчетов; необходимые манипуляции можно сделать в разделе формул:

  1. Выбрать пустую ячейку, в которую будет выведен результат расчетов.
  2. Нажать в главном меню Excel пункт «Формулы».
  3. Среди кнопок, сгруппированных в «Библиотеку функций», выбрать «Другие функции».
  4. В выпадающих списках выбрать функцию расчета корреляции (Статистические — КОРРЕЛ).
  5. В Excel откроется панель «Аргументы функции». «Массив 1» и «Массив 2» — это диапазоны сравниваемых данных. Для автоматического заполнения этих полей можно просто выделить нужные ячейки таблицы.
  6. Нажать «ОК», закрыв окно аргументов функции. В ячейке появится подсчитанный коэффициент корреляции.

Корреляция может быть прямая (если коэффициент больше нуля) и обратная (от -1 до 0).

Первая означает, что при росте одного параметра растет и другой. Обратная (отрицательная) корреляция отражает факт, что при росте одной переменной другая уменьшается.

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

Если коэффициент показывает среднюю или сильную взаимосвязь (от ±0,5 до ±0,99), следует помнить, что это лишь статистическая взаимосвязь, которая вовсе не гарантирует влияние одного параметра на другой. Также нельзя исключать ситуации, что оба параметра независимы друг от друга, но на них воздействует какой-нибудь третий неучтенный фактор. Excel помогает моментально вычислить коэффициент корреляции, но обычно только количественных методов недостаточно для установления причинно-следственных связей в соотносимых выборках.

Вычисление коэффициента посредством мастера функций

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

Порядок действий:

  1. Кликнуть по ячейке, в которой должен появиться результат.
  2. Нажать кнопку «Вставить формулу».
  3. В появившемся окне выбрать категорию «Полный алфавитный перечень».
  4. Найти и активировать функцию «КОРРЕЛ».
  5. Кликнуть «ОК».
  6. В открывшемся окне аргументов поставить курсор в поле «Массив 1», выделить первый столбец с данными.
  7. Поставить курсор в поле «Массив 2», выделить второй столбец из таблицы.
  8. Кликнуть «ОК».

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

Расчёт с помощью пакета анализа

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

  1. Выполнить действия «Файл» — «Сведения» — «Параметры».
  2. В появившемся окне перейти в раздел «Надстройки». В нижней части окна в выпадающем списке выбрать «Надстройки Excel». Нажать кнопку «Перейти».
  3. В открывшемся окне «Надстройки» следует отметить пункт «Пакет анализа» и нажать «ОК»

Чтобы воспользоваться пакетом, следует:

  1. На панели задач активировать вкладку «Данные».
  2. Нажать кнопку «Анализ данных».
  3. В новом окне выделить строку «Корреляция» и нажать «ОК». Появится окно с параметрами.
  4. Для выбора входного интервала необходимо установить курсор в соответствующее поле и выделить сразу оба столбца.
  5. Параметр группировки следует отметить «по столбцам». Вывод результатов возможен в указанное место, на новый лист или в новую книгу.
  6. Следует отметить соответствующее поле.

  Работа со сводными таблицами в MS Excel

После указание всех параметров следует нажать «ОК».

Значение получилось тем же, что и в первом случае.

Поле корреляции (диаграмма рассеяния)

Корреляционное поле — это графическое отображение исходных данных.  По расположению точек можно определить наличие зависимости и ее характер.

В редакторе Excel построение выполняется с помощью инструмента «Диаграмма»:

  1. Выделить столбцы с данными.
  2. Кликнуть «Вставка» — «Точечная» — «Точечная с маркерами».
  • Результат построения корреляционной матрицы.
  • По расположению точек на диаграмме можно сделать вывод о том, что прослеживается сильная положительная корреляционная зависимость между величиной затрат на маркетинг и объемом продаж.
  • Для того, чтобы использовать диаграмму в практических целях, можно добавить линию тренда и уравнение.  Для этого нужно выполнить следующие действия:
  1. Кликнуть правой кнопкой мыши на любой точке диаграммы.
  2. В контекстном меню выбрать «добавить линию тренда».
  3. Настроить параметры линии тренда (можно оставить по умолчанию).
  4. Нажать кнопку «закрыть».

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

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

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

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

  Что делать, если лист или книга в Excel защищены паролем – как снять защиту

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

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Оцените статью
Рейтинг автора
5
Материал подготовил
Андрей Измаилов
Наш эксперт
Написано статей
116
Добавить комментарий