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

Корреляционный анализ в Excel

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

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

Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

Задача:
Определить, есть ли взаимосвязь между временем работы токарного станка и стоимостью его обслуживания.

Ставим курсор в любую ячейку и нажимаем кнопку fx.

1. В категории «Статистические» выбираем функцию КОРРЕЛ.
2. Аргумент «Массив 1» — первый диапазон значений – время работы станка: А2:А14.
3. Аргумент «Массив 2» — второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.

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

Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

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

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

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

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

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

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

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

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

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

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

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

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

   Жмем на кнопку «OK».

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

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

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

1. Переходим во вкладку «Файл».
2. В открывшемся окне перемещаемся в раздел «Параметры».
3. Далее переходим в пункт «Надстройки».
4. В нижней части следующего окна в разделе «Управление» переставляем переключатель в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «OK».
5. В окне надстроек устанавливаем галочку около пункта «Пакет анализа». Жмем на кнопку «OK».
6. После этого пакет анализа активирован. Переходим во вкладку «Данные». Как видим, тут на ленте появляется новый блок инструментов – «Анализ». Жмем на кнопку «Анализ данных», которая расположена в нем.
7. Открывается список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Кликаем по кнопке «OK».
8. Открывается окно с параметрами корреляционного анализа. В отличие от предыдущего способа, в поле «Входной интервал» мы вводим интервал не каждого столбца отдельно, а всех столбцов, которые участвуют в анализе. В нашем случае это данные в столбцах «Затраты на рекламу» и «Величина продаж».

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

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

   Когда все настройки установлены, жмем на кнопку «OK».

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

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

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

Помогла ли вам эта статья?

Да Нет

Регрессионный и корреляционный анализ – статистические методы исследования. Это наиболее распространенные способы показать зависимость какого-либо параметра от одной или нескольких независимых переменных.

Ниже на конкретных практических примерах рассмотрим эти два очень популярные в среде экономистов анализа. А также приведем пример получения результатов при их объединении.

Зачем использовать Excel таблицу парных коэффициентов корреляции?

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

1. Планирование бизнеса: анализировать связь между различными показателями, такими как объем продаж, рекламные расходы и прибыль, может помочь принимать обоснованные решения по развитию бизнеса.
2. Научные исследования: определение связи между независимыми и зависимыми переменными может помочь ученым изучать и понимать природу явлений.
3. Финансовый анализ: анализ связей между финансовыми показателями, например, доходностью акций и процентной ставкой, может помочь инвесторам принимать решения о портфеле инвестиций.

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

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

Использование Excel таблицы парных коэффициентов корреляции:	Преимущества:
Оценка связи между переменными.	Получение точных числовых значений.
Анализ показателей бизнеса.	Визуализация результатов с помощью графиков.
Научные исследования.	Статистическая проверка значимости связей.
Финансовый анализ.	Обеспечение достоверности результатов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример . По данным 154 сельскохозяйственных предприятий Кемеровской области 2003 г. изучить эффективность производства зерновых (табл. 13).

Задание

1. Определите факторы, формирующие рентабельность зерновых в сельскохозяйственных предприятий в 2003 г.
2. Постройте матрицу парных коэффициентов корреляции. Установите, какие факторы мультиколлинеарны.
3. Постройте уравнение регрессии, характеризующее зависимость рентабельности зерновых от всех факторов.
4. Оцените значимость полученного уравнения регрессии. Какие факторы значимо воздействуют на формирование рентабельности зерновых в этой модели?
5. Оцените значение рентабельности производства зерновых в сельскохозяйственном предприятии № 3.

1. Оценка уравнения регрессии. Определим вектор оценок коэффициентов регрессии. Согласно методу наименьших квадратов, вектор получается из выражения: s = (X T X) -1 X T Y Матрица X

Матрица X T Умножаем матрицы, (X T X)

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

Найдем парные коэффициенты корреляции. Для y и x₁Уравнение имеет вид y = ax + b Средние значения

Для y и x₂Уравнение имеет вид y = ax + b Средние значения

Для y и x₃Уравнение имеет вид y = ax + b Средние значения

Для x₁ и x₂Уравнение имеет вид y = ax + b Средние значения

Для x₁ и x₃Уравнение имеет вид y = ax + b Средние значения

Для x₂ и x₃Уравнение имеет вид y = ax + b Средние значения

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

3. Анализ параметров уравнения регрессии. Перейдем к статистическому анализу полученного уравнения регрессии: проверке значимости уравнения и его коэффициентов, исследованию абсолютных и относительных ошибок аппроксимации Для несмещенной оценки дисперсии проделаем следующие вычисления: Несмещенная ошибка e = Y — X*s (абсолютная ошибка аппроксимации)

s_e 2 = (Y — X*s) T (Y — X*s) Несмещенная оценка дисперсии равна

Оценка среднеквадратичного отклонения равна

Найдем оценку ковариационной матрицы вектора k = a*(X T X) -1

Дисперсии параметров модели определяются соотношением S 2 _i= K_ii, т.е. это элементы, лежащие на главной диагонали С целью расширения возможностей содержательного анализа модели регрессии используются частные коэффициенты эластичности, которые определяются по формуле:

Частные коэффициент эластичности E₁ 2 = 0.62 2 = 0.38, т.е. в 38.0855 % случаев изменения х приводят к изменению y. Другими словами — точность подбора уравнения регрессии — средняя

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

По таблице Стьюдента находим Tтабл T_табл(n-m-1;a) = (18;0.05) = 1.734 Поскольку Tнабл > Tтабл , то отклоняем гипотезу о равенстве 0 коэффициента корреляции. Другими словами, коэффициента корреляции статистически — значим Интервальная оценка для коэффициента корреляции (доверительный интервал)

Доверительный интервал для коэффициента корреляции r(0.3882;0.846)

5. Проверка гипотез относительно коэффициентов уравнения регрессии (проверка значимости параметров множественного уравнения регрессии). 1) t-статистика

Статистическая значимость коэффициента регрессии bне подтверждается

Статистическая значимость коэффициента регрессии b₁не подтверждается

Статистическая значимость коэффициента регрессии b₂не подтверждается

Статистическая значимость коэффициента регрессии b₃не подтверждается Доверительный интервал для коэффициентов уравнения регрессии Определим доверительные интервалы коэффициентов регрессии, которые с надежность 95% будут следующими: (b_i— t _iS _i; b_i+ t _iS _i) b : (-0.7348;1.0478) b ₁: (-2.9781;3.6531) b ₂: (-0.4466;0.4553) b ₃: (-4.8459;5.4431)

Линейный коэффициент корреляции Пирсона

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

Взаимосвязь двух переменных проявляется в совместной вариации: при изменении одного показателя имеет место тенденция изменения другого. Такая взаимосвязь называется корреляцией, а раздел статистики, который занимается взаимосвязями – корреляционный анализ.

Корреляция – это, простыми словами, взаимосвязанное изменение показателей. Она характеризуется направлением, формой и теснотой. Ниже представлены примеры корреляционной связи.

Далее будет рассматриваться только линейная корреляция. На диаграмме рассеяния (график корреляции) изображена взаимосвязь двух переменных X и Y. Пунктиром показаны средние.

При положительном отклонении X от своей средней, Y также в большинстве случаев отклоняется в положительную сторону от своей средней. Для X меньше среднего, Y, как правило, тоже ниже среднего. Это прямая или положительная корреляция. Бывает обратная или отрицательная корреляция, когда положительное отклонение от средней X ассоциируется с отрицательным отклонением от средней Y или наоборот.

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

Крайне важная характеристика корреляции – теснота. Чем теснее взаимосвязь, тем ближе к прямой точки на диаграмме. Как же ее измерить?

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

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

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

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

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

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

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

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

Линейная функция является моделью взаимосвязи между X иY и показывает ожидаемое значение Y при заданном X. Коэффициент детерминации – это соотношение дисперсии ожидаемых Y (точек на прямой линии) к общей дисперсии Y, или доля объясненной вариации Y. При r = 0,1 r 2 = 0,01 или 1%, при r = 0,5 r 2 = 0,25 или 25%.

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

Ковариация

близка по смыслу с (также является мерой разброса) с тем отличием, что она определена для 2-х переменных, а

дисперсия — для одной. Поэтому, cov(x;x)=VAR(x).

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

обозначает

Генеральная совокупность ), во втором – вместо множителя 1/n используется 1/(n-1), т.е. окончание

.В обозначает

Выборка .

Примечание

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

Примечание

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

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

=СУММПРОИЗВ(B28:B88-СРЗНАЧ(B28:B88);(D28:D88-СРЗНАЧ(D28:D88)))/СЧЁТ(D28:D88)

=СУММПРОИЗВ(B28:B88-СРЗНАЧ(B28:B88);(D28:D88))/СЧЁТ(D28:D88)

=СУММПРОИЗВ(B28:B88;D28:D88)/СЧЁТ(D28:D88)-СРЗНАЧ(B28:B88)*СРЗНАЧ(D28:D88)

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

Если переменные x

и

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

VAR(x+y)= VAR(x)+ VAR(y)+2COV(x;y)

А дисперсия

их разности равна

VAR(x-y)= VAR(x)+ VAR(y)-2COV(x;y)

Инвестиционный анализ. Расчет корреляционной матрицы в Excel

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

Задача любого инвестора – снизить риски вложения, для этого используется различные виды диверсификации (распределения) средств по различным направлениям. Диверсифицированный портфель содержит некоррелированные активы, другими словами, активы которые имеют различную динамику изменения стоимости (цены). Например, если цена одного актива растет, то другого изменяется независимо от первого. Для описания силы связи между активами инвесторы и аналитики используют коэффициент корреляция, который также называет коэффициентом корреляции Пирсона. Он изменяется в диапазоне от -1 до 1.

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

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

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

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

Расчет корреляционной матрицы в Excel Заполняет колонки с изменением стоимости актива. Данные по котировкам можно получить на сайте finam.ru в разделе экспорт котировок. Данные котировки были получены с сайта finance.yahoo.com по иностранным акциям.

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

Далее необходимо в Excel выбрать входной интервал котировок (зеленая область + названия активов). Отметить галочку метки в первой строке. Выбрать выходной интервал как ячейку и нажать Oк.

Итоговый результат расчета корреляционной матрицы представлен на рисунке ниже. Можно выделить низкий коэффициент корреляции между активами TBILL и всеми остальными (стремится к 0), также между USX и GMC составляет 0.2. Это показывает высокую степень диверсификации данных активов в портфеле. Но активы ATT и GMC имеют достаточно высокую зависимость 0.5, это требует пересмотра данных активов в инвестиционном портфеле.

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

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

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

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

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

   Нажмите на кнопку «ОК».

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

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

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

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

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

   Когда все настройки введены, нажмите кнопку «ОК».

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

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