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

Как проводится корреляционный анализ в Excel

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

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

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

Вариант 1: Вызов через Мастер функций

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

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

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

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

Закончив с заполнением нажмите кнопку “Ок”. Подсчет будет произведен автоматически и выведен в указанной ранее ячейке.
Если полученный коэффициент оказался больше +/-0.5, то это значит, что одна величина сильно зависима от другой.

Вариант 2: Применение пакета анализа

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

1. Перейдите во вкладку “Файл”, что расположена в верхней части окна.

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

В открывшемся окне установите галочку у пункта “Пакет анализа” и нажмите “Ок”. После этого у вас должны появится дополнительные инструменты в верхней панели Excel.
Нужные нам инструменты расположена во вклакде “Данные”. Там должен будет появится дополнительный блок инструментов — “Анализ”. Воспользуйтесь в нем единственным инструментом — “Анализом данных”.

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

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

Использование MS EXCEL для расчета ковариации

Ковариация близка по смыслу с дисперсией (также является мерой разброса) с тем отличием, что она определена для 2-х переменных, а дисперсия — для одной. Поэтому, cov(x;x)=VAR(x).

Для вычисления ковариации в MS EXCEL (начиная с версии 2010 года) используются функции КОВАРИАЦИЯ.Г() и КОВАРИАЦИЯ.В() . В первом случае формула для вычисления аналогична вышеуказанной (окончание .Г обозначает Генеральная совокупность), во втором – вместо множителя 1/n используется 1/(n-1), т.е. окончание .В обозначает Выборка.

Примечание: Функция КОВАР() , которая присутствует в MS EXCEL более ранних версий, аналогична функции КОВАРИАЦИЯ.Г() .

Примечание: Функции КОРРЕЛ() и КОВАР() в английской версии представлены как CORREL и COVAR. Функции КОВАРИАЦИЯ.Г() и КОВАРИАЦИЯ.В() как COVARIANCE.P и COVARIANCE.S.

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

Эти формулы используют свойство ковариации:

Если переменные x и y независимые, то их ковариация равна 0. Если переменные не являются независимыми, то дисперсия их суммы равна:

Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel

КОРРЕЛ – функция, применяемая для подсчета коэффициента корреляции между 2-мя массивами. Разберем на четырех примерах все способности этой функции.

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

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

24

Алгоритм расчёта выглядит следующим образом:

25

Отображенный показатель близок к 1. Результат:

26

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

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

27

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

• =КОРРЕЛ(А3:А17;В3:В17).
• =КОРРЕЛ(А3:А17;С3:С17).

Результаты:

28

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

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

Третий пример. Человек для продвижения собственных роликов на видеохостинге Ютуб применяет соцсети для рекламирования канала. Он замечает, что существует некая взаимосвязь между числом репостов в соцсетях и количеством просмотров на канале. Можно ли про помощи инструментов табличного процессора произвести прогноз будущих показателей? Необходимо выявить резонность применения уравнения линейной регрессии для прогнозирования числа просмотров видеозаписей в зависимости от количества репостов. Табличка со значениями:

29

Теперь необходимо провести определение наличия связи между 2-мя показателями по нижеприведенной формуле:

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

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

30

Теперь производим построение графика:

31

Применяем это уравнение, чтобы определить число просматриваний при 200, 500 и 1000 репостов: =9,2937*D4-206,12. Получаем следующие результаты:

32

Функция ПРЕДСКАЗ позволяет определить число просмотров в моменте, если было проведено, к примеру, двести пятьдесят репостов. Применяем: 0,7;ПРЕДСКАЗ(D7;B3:B8;A3:A8);”Величины не взаимосвязаны”)’ class=’formula’>. Получаем следующие результаты:

33

Особенности использования функции КОРРЕЛ в Excel

Данная функция имеет нижеприведенные особенности:

1. Не учитываются ячейки пустого типа.
2. Не учитываются ячейки, в которых находится информация типа Boolean и Text.
3. Двойное отрицание «–» применяется для учёта логических величин в виде чисел.
4. Количество ячеек в исследуемых массивах обязаны совпадать, иначе будет выведено сообщение #Н/Д.

9.1.2. Проверка статистических гипотез о связи переменных

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

Проделаем небольшое упражнение.

Упражнение 9.1.2(1). Возьмите две симметричные монеты достоинством в один рубль и один евро. Проведите серию четырех подбрасываний пары монет и запишите результаты в виде ​\( (x_1, y_1),\dots,(x_4, y_4) \)​ , полагая

​\( x_i=0 \)​, если рубль выпал цифрой;

​\( x_i=1 \), если рубль выпал гербом;

​\( y_i=0 \), если евро выпал цифрой;

​\( y_i=1 \), если евро выпал гербом.

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

В таблице 9.1.2(2) приведены двухсторонние квантили распределения выборочного коэффициента корреляции по Пирсону для ​\( n=10 \)​. Они рассчитаны для выборок, полученных испытаниями двух нормально распределенных случайных величин, теоретическая корреляция между которыми равна нулю. Дихотомический результат подбрасывания монеты не распределен нормально, однако некоторое представление о возможных результатах наших испытаний табличный квантиль все же дает.

Таблица 9.1.2(2) Двусторонние квантили распределения коэффициента Пирсона для n = 10

​\( \alpha \)​	0.05	0.025	0.01	0.005
​\( r_\alpha(10) \)​	0.497	0.576	0.658	0.709

Обычно при исследовании связи переменных статистической гипотезой ​\( H_0 \)​ будет гипотеза об отсутствии связи, т.е. о независимости переменных. Альтернативная гипотеза \( H_1 \)​ (т.е. гипотеза, к которой мы склоняемся, получив большие по модулю значения выборочной корреляции) будет утверждать только наличие связи . Можно оценить значимость относительно данного результата (полученной парной выборки) гипотез о других значениях теоретической корреляции, но это требует некоторых дополнительных усилий (см. подпараграф ). Если истинна гипотеза \( H_0 \)​, то выборочный коэффициент корреляции будет принимать значения, более или менее близкие к нулю. Если выборочная корреляция принимает достаточно большое по модулю значение, которому соответствует значимость, измеряемая маленьким числом, то мы склоняемся к гипотезе \( H_1 \)​ о наличии связи, но без указания точного значения теоретической корреляции.

Можно заметить, что если верна гипотеза  об отсутствии зависимости между случайными величинами, то выборочный коэффициент при \( n=10 \) может принимать тем не менее довольно большие значения, так что уровень значимости 0.05 для принятия гипотезы о зависимости случайных величин требует, чтобы выборочный коэффициент корреляции достигал почти 0.5 (см. ). В связи с этим надо иметь в виду, что даже выборочная корреляция, например 0.6, вполне может согласовываться с истинной корреляцией, равной 0.2 .

Коэффициент корреляции

Рисунок 4. Сравнение автокорреляционной функции (АКФ) и кросскорреляционной функции (ВКФ) для трех разных отведений

Значение корреляционного анализа состоит в нахождении автокорреляционной функции, которая показывает преобладающие ритмы, или взаимно-корреляционной функции, отражающей связь фрагмента обрабатываемого сигнала и эталона сигнала без помех и шумов или же связь между двумя неочищенными сигналами. (рис. 4) Биомедицинские сигналы относятся к квазипериодическим сигналам. ЭЭГ-сигналы содержат эпохи, имеющие схожие друг с другом характеристики. Эпоха ЭЭГ-сигнала – это произвольно выделенный период времени в записи ЭЭГ (чаще всего составляет 10 с.) В качестве эталона какой-либо эпохи ЭЭГ-сигнала используется либо стандартный фрагмент данного сигнала, свободный от помех и шумов, либо искусственно созданная модель стандартного фрагмента ЭЭГ-сигнала, построенная на основе заранее известных данных о форме и амплитудно-временных характеристик “чистого” фрагмента. Сравнение автокорреляционной функции (АКФ) и кросскорреляционной функции (ВКФ) для трех разных отведений представлено на рис. 4.  График под буквой г изображает автокорреляционную функцию фрагмента а. Подобная функция графика свидетельствует о том, что сходство двух эпох данного сигнала сходит на ноль относительно медленно. Следовательно, мы можем сделать вывод о периодичности процессов, протекающих в данном отведении, т.к. отдельные его фрагменты достаточно сильно схожи друг с другом. Аналогичные выводы можно сделать и о сигнале на графике б, опираясь на его функцию, обозначенную буквой д.

Рисунок 5. Применение корреляционного анализа к обработке ЭЭГ-сигнала

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

График ж изображает кросс-корреляционную функцию между сигналами а и б. Ее медленное затухание свидетельствует о наличии взаимовлияния фрагментов а и б. График з и график и отражают кросс-корреляционную функцию между сигналами б–в и а–в соответственно. Поскольку затухание графика происходит достаточно быстро, мы можем сделать вывод о том, что между данными отрезками ЭЭГ-сигнала отсутствует существенная взаимосвязь. 

Рисунок 6. Результат сравнения графиков напряжения при использовании сухих и влажных электродов

Корреляционный анализ применительно к исправлению графика ЭЭГ можно изобразить так: в центре иллюстрации приведено два графика – график воздействия стимула сверху (синий цвет) и фрагмент ЭЭГ снизу (синий цвет).  (рис. 5)

В правой части иллюстрации изображены графики скорости воздействия стимула (синий цвет) и средней формы волны (розовый цвет). Последний был получен путем суммации изначальных ЭЭГ-графиков. Их также подвергли кросскорелляционному анализу, результат которого приведен ниже (розовый цвет). Затем, используя полученные данные о корреляции, был построен исправленный график ЭЭГ-сигнала (розовый цвет).

В левой части изображены графики скорости воздействия стимула (синий цвет) и мощности ЭЭГ (зеленый цвет). Их подвергли кросскорелляционному анализу, результат которого приведен ниже (зеленый цвет). Затем, используя полученные данные о корреляции, был построен исправленный график ЭЭГ-сигнала (зеленый цвет).

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

Интерпретация результата вычисления по Пирсону

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

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

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

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

Предвзятость средств массовой информации

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

Как вы можете рассчитать корреляцию с помощью Excel? — 2019

a:

Корреляция измеряет линейную зависимость двух переменных. Измеряя и связывая дисперсию каждой переменной, корреляция дает представление о силе взаимосвязи. Или, говоря иначе, корреляция отвечает на вопрос: сколько переменная A (независимая переменная) объясняет переменную B (зависимую переменную)?

Формула корреляции

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

Формула:

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

Общие ошибки с корреляцией

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

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

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

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

Для корреляции цен на акции вы, по сути, задаете два вопроса: каково возвращение за определенное количество периодов и как этот доход коррелирует с возвратом другой безопасности за тот же период? Это также связано с тем, что корреляция цен на акции затруднена: две ценные бумаги могут иметь высокую корреляцию, если доход составляет ежедневно процентов за последние 52 недели, но низкая корреляция, если доход ежемесячно > изменения за последние 52 недели. Какая из них лучше»? На самом деле нет идеального ответа, и это зависит от цели теста. ( Улучшите свои навыки excel, пройдя курс обучения Excel в Академии Excel. ) Поиск корреляции в Excel

Существует несколько методов расчета корреляции в Excel

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

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

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

Как только вы нажмете enter, данные будут автоматически сделаны. Вы можете добавить текст и условное форматирование, чтобы очистить результат.

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

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

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

Что представляет собой корреляционный анализ

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

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

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

1. Количество человек, которые стали утопленниками в бассейнах, четко коррелирует с количеством фильмов, в которых Николас Кейдж был актером.
2. Количество съеденной моцареллы и количество человек, которые получили докторскую степень, также коррелирует на протяжении 2000-2009 годов. Наверно, действительно, моцарелла как-то влияет на мозг и стимулирует желание совершать научные открытия.
3. Почти во всех случаях средний возраст женщин, которые получили статус «Мисс Америка» коррелирует с количеством людей, которые погибли от нахождения в горячем паре.
4. Число людей, которое погибло в результате дорожно-транспортного происшествия, четко коррелирует с количеством сметаны, которое съедают люди.
5. Мало кто знает, что чем больше курятины человек ест, тем больше сырой нефти импортируется в мире. Правда, это тоже пример ложной корреляции. Кстати, импорт сырой нефти родом из Норвегии тесно связано с количеством людей, которые погибли в результате столкновения автомобиля с поездом. Причем в этом случае корреляция почти 100 процентов.
6. А еще маргарин негативно влияет на статистику разводов. Чем больше людей, которые проживали в штате Мэн, потребляли маргарина, тем выше была частота разводов. Правда, здесь еще может быть рациональное зерно. Ведь частота потребления маргарина имеет обратную корреляцию с экономическим положением в семье. В свою очередь, плохое экономическое положение в семье имеет непосредственную связь с количеством разводов. И это уже доказано научно. Так что кто знает, может, эта корреляция и не является такой ложной. Правда, никто этого не перепроверял.
7. Количество денег, которое правительство США тратит на развитие науки, космоса и технологий, имеет тесную связь с количеством самоубийств, проведенных в форме повешения или удушения.

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

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

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

Способ 1. Определение корреляции с помощью Мастера Функций

Функция КОРРЕЛ – один из самых простых методов, как можно реализовать поставленную задачу. В своем общем виде этот оператор имеет следующий вид: КОРРЕЛ(массив1;массив2). Как же ее ввести? Для этого нужно осуществлять следующие действия:

1. С помощью левой кнопки мыши выделяем ту ячейку, в которой будет находиться получившийся коэффициент корреляции. После этого находим слева от строки формул кнопку fx, которая откроет инструмент ввода функций. 
2. Далее выбираем категорию «Полный алфавитный перечень», в котором ищем функцию КОРРЕЛ. Как видно из названия категории, все названия функций располагаются в алфавитном порядке. 
3. Далее открывается окно ввода параметров функции. У нас два основных аргумента, каждый из которых являет собой массив данных, которые сравниваются между собой. В поле «Массив 1» указываем координаты первого диапазона, а в поле «Массив 2» – адрес второго диапазона. Для ввода данных массива, используемого для расчета, достаточно выделить нажать левой кнопкой мыши по соответствующему полю и выделить правильный диапазон. 
4. После того, как мы введем данные в аргументы, нажимаем кнопку «ОК», чем подтверждаем совершенные действия.

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

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

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

1. Нажимаем на кнопку «Файл», которая находится в левом верхнем углу сразу возле вкладки «Главная». 
2. После этого открываем раздел с настройками. 
3. В меню слева переходим в предпоследний пункт, озаглавленный, как «Надстройки». Делаем левый клик по соответствующей надписи. 
4. Открывается окно управления надстройками. Нам нужно переключить поле ввода, находящееся внизу, на пункт «Надстройки Excel» и нажать на «Перейти». Если это поле уже находится в таком положении, то не выполняем никаких изменений. 
5. Затем включаем пакет анализа в настройках. Для этого ставим соответствующую галочку и нажимаем на кнопку «ОК». 

Все, теперь наша надстройка включена. Теперь мы во вкладке «Данные» можем увидеть кнопку «Анализ данных». Если она появилась, то мы все сделали правильно. Нажимаем на нее. 

Появляется перечень с выбором разных способов анализа информации. Нам следует выбрать пункт «Корреляция» и нажать на «ОК». 

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

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

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

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

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