Как часто Вам приходилось слышать высказывания, в которых говорилось о том, что одно явление коррелируется с другим?

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

«Цена на нефть коррелируется с курсами валют.»

«Боль в мышцах после тренировки не коррелируется с гипертрофией мышечных волокон.»

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

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

Ковариация

Ковариацией $cov\left(X,\ Y\right)$ случайных величин $X$ и $Y$ называется математическое ожидание произведения случайных величин $X-M\left(X\right)$ и $Y-M\left(Y\right)$, то есть:

$$cov\left(X,\ Y\right)=M\left(\left(X-M\left(X\right)\right)\left(Y-M\left(Y\right)\right)\right).$$

Бывает удобно вычислять ковариацию случайных величин $X$ и $Y$ по следующей формуле:

$$cov\left(X,\ Y\right)=M\left(XY\right)-M\left(X\right)M\left(Y\right),$$

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

1 . Ковариация случайной величины с самой собой есть ее дисперсия.

$$cov\left(X,\ X\right)=D\left(X\right).$$

2 . Ковариация симметрична.

$$cov\left(X,\ Y\right)=cov\left(Y,\ X\right).$$

3 . Если случайные величины $X$ и $Y$ независимы, то:

$$cov\left(X,\ Y\right)=0.$$

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

$$cov\left(cX,\ Y\right)=cov\left(X,\ cY\right)=c\cdot cov\left(X,\ Y\right).$$

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

$$cov\left(X+c,\ Y\right)=cov\left(X,\ Y+c\right)=cov\left(X+x,\ Y+c\right)=cov\left(X,\ Y\right).$$

6 . $cov\left(aX+b,\ cY+d\right)=ac\cdot cov\left(X,\ Y\right)$.

7 . $\left|cov\left(X,\ Y\right)\right|\le \sqrt{D\left(X\right)D\left(Y\right)}$.

8 . $\left|cov\left(X,\ Y\right)\right|=\sqrt{D\left(X\right)D\left(Y\right)}\Leftrightarrow Y=aX+b$.

9 . Дисперсия суммы (разности) случайных величин равна сумме их дисперсий плюс (минус) удвоенная ковариация этих случайных величин:

$$D\left(X\pm Y\right)=D\left(X\right)+D\left(Y\right)\pm 2cov\left(X,\ Y\right).$$

Пример 1 . Дана корреляционная таблица случайного вектора $\left(X,\ Y\right)$. Вычислить ковариацию $cov\left(X,\ Y\right)$.

$\begin{array}{|c|c|}
\hline

\hline
-2 & 0,1 & 0 & 0,2 \\
\hline
0 & 0,05 & p_{22} & 0 \\
\hline
1 & 0 & 0,2 & 0,05 \\
\hline
7 & 0,1 & 0 & 0,1 \\
\hline
\end{array}$

События $\left(X=x_i,\ Y=y_j\right)$ образуют полную группу событий, поэтому сумма всех вероятностей $p_{ij}$, указанных в таблице, должна быть равна 1. Тогда $0,1+0+0,2+0,05+p_{22}+0+0+0,2+0,05+0,1+0+0,1=1$, отсюда $p_{22}=0,2$.

$\begin{array}{|c|c|}
\hline
X\backslash Y & -6 & 0 & 3 \\
\hline
-2 & 0,1 & 0 & 0,2 \\
\hline
0 & 0,05 & 0,2 & 0 \\
\hline
1 & 0 & 0,2 & 0,05 \\
\hline
7 & 0,1 & 0 & 0,1 \\
\hline
\end{array}$

Пользуясь формулой $p_{i} =\sum _{j}p_{ij} $, находим ряд распределения случайной величины $X$.

$\begin{array}{|c|c|}
\hline
X & -2 & 0 & 1 & 7 \\
\hline
p_i & 0,3 & 0,25 & 0,25 & 0,2 \\
\hline
\end{array}$

$$M\left(X\right)=\sum^n_{i=1}{x_ip_i}=-2\cdot 0,3+0\cdot 0,25+1\cdot 0,25+7\cdot 0,2=1,05.$$

$$D\left(X\right)=\sum^n_{i=1}{p_i{\left(x_i-M\left(X\right)\right)}^2}=0,3\cdot {\left(-2-1,05\right)}^2+0,25\cdot {\left(0-1,05\right)}^2+0,25\cdot {\left(1-1,05\right)}^2+$$

$$+\ 0,2\cdot {\left(7-1,05\right)}^2=10,1475.$$

$$\sigma \left(X\right)=\sqrt{D\left(X\right)}=\sqrt{10,1475}\approx 3,186.$$

Пользуясь формулой $q_{j} =\sum _{i}p_{ij} $, находим ряд распределения случайной величины $Y$.

$\begin{array}{|c|c|}
\hline
Y & -6 & 0 & 3 \\
\hline
p_i & 0,25 & 0,4 & 0,35 \\
\hline
\end{array}$

$$M\left(Y\right)=\sum^n_{i=1}{y_ip_i}=-6\cdot 0,25+0\cdot 0,4+3\cdot 0,35=-0,45.$$

$$D\left(Y\right)=\sum^n_{i=1}{p_i{\left(y_i-M\left(Y\right)\right)}^2}=0,25\cdot {\left(-6+0,45\right)}^2+0,4\cdot {\left(0+0,45\right)}^2+0,35\cdot {\left(3+0,45\right)}^2=11,9475.$$

$$\sigma \left(Y\right)=\sqrt{D\left(Y\right)}=\sqrt{11,9475}\approx 3,457.$$

Поскольку $P\left(X=-2,\ Y=-6\right)=0,1\ne 0,3\cdot 0,25$, то случайные величины $X,\ Y$ являются зависимыми.

Определим ковариацию $cov\ \left(X,\ Y\right)$ случайных величин $X,\ Y$ по формуле $cov\left(X,\ Y\right)=M\left(XY\right)-M\left(X\right)M\left(Y\right)$. Математическое ожидание произведения случайных величин $X,\ Y$ равно:

$$M\left(XY\right)=\sum_{i,\ j}{p_{ij}x_iy_j}=0,1\cdot \left(-2\right)\cdot \left(-6\right)+0,2\cdot \left(-2\right)\cdot 3+0,05\cdot 1\cdot 3+0,1\cdot 7\cdot \left(-6\right)+0,1\cdot 7\cdot 3=-1,95.$$

Тогда $cov\left(X,\ Y\right)=M\left(XY\right)-M\left(X\right)M\left(Y\right)=-1,95-1,05\cdot \left(-0,45\right)=-1,4775.$ Если случайные величины независимы, то их ковариации равна нулю. В нашем случае $cov(X,Y)\ne 0$.

Корреляция

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

$$\rho \left(X,\ Y\right)={{cov\left(X,\ Y\right)}\over {\sqrt{D\left(X\right)D\left(Y\right)}}}.$$

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

1 . $\rho \left(X,\ X\right)=1$.

2 . $\rho \left(X,\ Y\right)=\rho \left(Y,\ X\right)$.

3 . $\rho \left(X,\ Y\right)=0$ для независимых случайных величин $X$ и $Y$.

4 . $\rho \left(aX+b,\ cY+d\right)={sgn \left(ac\right)\rho \left(X,\ Y\right)\ }$, где ${sgn \left(ac\right)\ }$ - знак произведения $ac$.

5 . $\left|\rho \left(X,\ Y\right)\right|\le 1$.

6 . $\left|\rho \left(X,\ Y\right)\right|=1\Leftrightarrow Y=aX+b$.

Ранее было сказано, что коэффициент корреляции $\rho \left(X,\ Y\right)$ отражает степень линейной зависимости между двумя случайными величинами $X$ и $Y$.

При $\rho \left(X,\ Y\right)>0$ можно сделать вывод о том, что с ростом случайной величины $X$ случайная величина $Y$ имеет тенденцию к увеличению. Это называется положительной корреляционной зависимостью. Например, рост и вес человека связаны положительной корреляционной зависимостью.

При $\rho \left(X,\ Y\right)<0$ можно сделать вывод о том, что с ростом случайной величины $X$ случайная величина $Y$ имеет тенденцию к уменьшению. Это называется отрицательной корреляционной зависимостью. Например, температура и время сохранности продуктов питания связаны между собой отрицательной корреляционной зависимостью.

При $\rho \left(X,\ Y\right)=0$ случайные величины $X$ и $Y$ называются некоррелированными. Стоит отметить, что некоррелированность случайных величин $X$ и $Y$ не означает их статистическую независимость, это говорит лишь о том, что между ними нет линейной зависимости.

Пример 2 . Определим коэффициент корреляции $\rho \left(X,\ Y\right)$ для двумерной случайной величины $\left(X,\ Y\right)$ из примера 1.

Коэффициент корреляции случайных величин $X,\ Y$ равен $r_{XY} ={cov(X,Y)\over \sigma (X)\sigma (Y)} ={-1,4775\over 3,186\cdot 3,457} =-0,134.$ Поскольку $r_{XY}<0$, то с ростом $X$ случайная величина $Y$ имеет тенденцию к уменьшению (отрицательная корреляционная зависимость).

Ранее была рассмотрена диаграмма разброса, иллюстрирующая распределение двумерных числовых данные (см. последний раздел Изображение двумерных числовых данных заметки ). В настоящей заметке мы изучим два количественных показателя, характеризующих силу зависимости между двумя переменными - ковариацию и коэффициент корреляции. Ковариация оценивает силу линейной зависимости между двумя числовыми переменными X и Y. Выборочная ковариация:

Скачать заметку в формате или , примеры в формате

Рассмотрим пятилетнюю среднегодовую доходность и долю затрат в фондах с очень низким уровнем риска (рис. 1). Для расчета ковариации двух выборок в Excel до 2007 года используется функция =КОВАР(), начиная с версии 2010 – функция КОВАРИВЦИЯ.В().

Рис. 1. Пятилетняя среднегодовая доходность и доля затрат взаимных фондов с очень низким уровнем риска

Любопытно, что ковариация случайной величины с собой равна дисперсии:

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

Относительная сила зависимости, или связи, между двумя переменными, образующими двумерную выборку, измеряется коэффициентом корреляции, изменяющимся от –1 для идеальной обратной зависимости до +1 для идеальной прямой зависимости. Коэффициент корреляции обозначается греческой буквой ρ . Линейность корреляции означает, что все точки, изображенные на диаграмме разброса, лежат на прямой (рис 2). На панели А изображена обратная линейная зависимость между переменными X и Y. Таким образом, коэффициент корреляции ρ равен –1, т.е., когда переменная X возрастает, переменная Y убывает. На панели Б показана ситуация, в которой между переменными X и Y нет корреляции. В этом случае коэффициент корреляции ρ равен 0, и, когда переменная X возрастает, переменная Y не проявляет никакой определенной тенденции: она ни убывает, ни возрастает. На панели В изображена линейная прямая зависимость между переменными X и Y. Таким образом, коэффициент корреляции ρ равен +1, и, когда переменная X возрастает, переменная Y также возрастает.

Рис. 2. Три вида зависимости между двумя переменными

При анализе выборок, содержащих двумерные данные, вычисляется выборочный коэффициент корреляции, который обозначается буквой r . В реальных ситуациях коэффициент корреляции редко принимает точные значения -1, 0 и +1. На рис. 3 приведены шесть диаграмм разброса и соответствующие коэффициенты корреляции r между 100 значениями переменных X и Y.

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

На панели А показана ситуация, в которой выборочный коэффициент корреляции r равен –0,9. Прослеживается четко выраженная тенденция: небольшим значениям переменной X соответствуют очень большие значения переменной Y, и, наоборот, большим значениям переменной X соответствуют малые значения переменной Y. Однако данные не лежат на одной прямой, поэтому зависимость между ними нельзя назвать линейной. На панели Б приведены данные, выборочный коэффициент корреляции между которыми равен –0,6. Небольшим значениям переменной X соответствуют большие значения переменной Y. Обратите внимание на то, что зависимость между переменными X и Y нельзя назвать линейной, как на панели А, и корреляция между ними уже не так велика. Коэффициент корреляции между переменными X и Y, изображенными на панели В, равен –0,3. Прослеживается слабая тенденция, согласно которой большим значениям переменной X, в основном, соответствуют малые значения переменной Y. Панели Г–Е иллюстрируют положительную корреляцию между данными - малым значениям переменной X соответствуют большие значения переменной Y.

Обсуждая рис. 3, мы употребляли термин тенденция, поскольку между переменными X и Y нет причинно-следственных связей. Наличие корреляции не означает наличия причинно-следственных связей между переменными X и Y, т.е. изменение значения одной из переменных не обязательно приводит к изменению значения другой. Сильная корреляция может быть случайной и объясняться третьей переменной, оставшейся за рамками анализа. В таких ситуациях необходимо проводить дополнительное исследование. Таким образом, можно утверждать, что причинно-следственные связи порождают корреляцию, но корреляция не означает наличия причинно-следственных связей.

Выборочный коэффициент корреляции:

В Excel для вычисления коэффициента корреляции используется функция =КОРРЕЛ() (рис. 4).

Рис. 4. Функция КОРРЕЛ в Excel

Итак, коэффициент корреляции свидетельствует о линейной зависимости, или связи, между двумя переменными. Чем ближе коэффициент корреляции к –1 или +1, тем сильнее линейная зависимость между двумя переменными. Знак коэффициента корреляции определяет характер зависимости: прямая (+) и обратная (–). Сильная корреляция не является причинно-следственной зависимостью. Она лишь свидетельствует о наличии тенденции, характерной для данной выборки.

Используются материалы книги Левин и др. Статистика для менеджеров. – М.: Вильямс, 2004. – с. 221–227

В этой статье описаны синтаксис формулы и использование функции КОВАРИАЦИЯ.Г в Microsoft Excel.

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

Синтаксис

КОВАРИАЦИЯ.Г(массив1;массив2)

Аргументы функции КОВАРИАЦИЯ.Г описаны ниже.

Массив1 - обязательный аргумент. Первый диапазон ячеек с целыми числами.

Массив2 - обязательный аргумент. Второй диапазон ячеек с целыми числами.

Замечания

Пример

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

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

Коэффициент корреляции (критерий корреляции Пирсона, англ. Pearson Product Moment correlation coefficient) определяет степень линейной взаимосвязи между случайными величинами.

Как следует из определения, для вычисления коэффициента корреляции требуется знать распределение случайных величин Х и Y. Если распределения неизвестны, то для оценки коэффициента корреляции используется выборочный коэффициент корреляции r (еще он обозначается как R xy или r xy ) :

где S x – стандартное отклонение выборки случайной величины х, вычисляемое по формуле:

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

Рассчитать коэффициент корреляции и ковариацию выборки в MS EXCEL не представляет труда, так как для этого имеются специальные функции КОРРЕЛ() и КОВАР() . Гораздо сложнее разобраться, как интерпретировать полученные значения, большая часть статьи посвящена именно этому.

Теоретическое отступление

Напомним, что корреляционной связью называют статистическую связь, состоящую в том, что различным значениям одной переменной соответствуют различные средние значения другой (с изменением значения Х среднее значение Y изменяется закономерным образом). Предполагается, что обе переменные Х и Y являются случайными величинами и имеют некий случайный разброс относительно их среднего значения .

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

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

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

1. Наличие причинной зависимости между переменными. Например, количество инвестиций в научные исследования (переменная Х) и количество полученных патентов (Y). Первая переменная выступает как независимая переменная (фактор) , вторая - зависимая переменная (результат) . Необходимо помнить, что зависимость величин обуславливает наличие корреляционной связи между ними, но не наоборот.
2. Наличие сопряженности (общей причины). Например, с ростом организации растет фонд оплаты труда (ФОТ) и затраты на аренду помещений. Очевидно, что неправильно предполагать, что аренда помещений зависит от ФОТ. Обе этих переменных во многих случаях линейно зависят от количества персонала.
3. Взаимовлияние переменных (при изменении одной, вторая переменная изменяется, и наоборот). При таком подходе допустимы две постановки задачи; любая переменная может выступать как в роли независимой переменной и в роли зависимой.

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

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

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

Для вычисления коэффициента корреляции требуется, чтобы сопоставляемые переменные удовлетворяли следующим условиям:

• количество переменных должно быть равно двум;
• переменные должны быть количественными (например, частота, вес, цена). Вычисленное среднее значение этих переменных имеет понятный смысл: средняя цена или средний вес пациента. В отличие от количественных, качественные (номинальные) переменные принимают значения лишь из конечного набора категорий (например, пол или группа крови). Этим значениям условно сопоставлены числовые значения (например, женский пол – 1, а мужской – 2). Понятно, что в этом случае вычисление среднего значения , которое требуется для нахождения корреляции , некорректно, а значит некорректно и вычисление самой корреляции ;
• переменные должны быть случайными величинами и иметь .

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

• Для данных с нелинейной связью корреляцию нужно использовать с осторожностью. Для некоторых задач бывает полезно преобразовать одну или обе переменных так, чтобы получить линейную взаимосвязь (для этого требуется сделать предположение о виде нелинейной связи, чтобы предложить нужный тип преобразования).
• С помощью диаграммы рассеяния у некоторых данных можно наблюдать неравную вариацию (разброс). Проблема неодинаковой вариации состоит в том, что места с высокой вариацией не только предоставляют наименее точную информацию, но и оказывают наибольшее влияние при расчете статистических показателей. Эту проблему также часто решают с помощью преобразования данных, например, с помощью логарифмирования.
• У некоторых данных можно наблюдать разделение на группы (clustering), что может свидетельствовать о необходимости разделения совокупности на части.
• Выброс (резко отклоняющееся значение) может исказить вычисленное значение коэффициента корреляции. Выброс может быть причиной случайности, ошибки при сборе данных или могут действительно отражать некую особенность взаимосвязи. Так как выброс сильно отклоняется от среднего значения, то он вносит большой вклад при расчете показателя. Часто расчет статистических показателей производят с и без учета выбросов.

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

В качестве примера возьмем 2 переменные Х и Y и, соответственно, выборку состоящую из нескольких пар значений (Х i ; Y i). Для наглядности построим .

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

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

Примечание : В файле примера можно задать параметры линейного тренда (наклон, пересечение с осью Y) и степень разброса относительно этой линии тренда. Также можно настроить параметры квадратичной зависимости.

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

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

Как было сказано выше, для расчета коэффициента корреляции в MS EXCEL существует функций КОРРЕЛ() . Также можно воспользоваться аналогичной функцией PEARSON() , которая возвращает тот же результат.

Для того, чтобы удостовериться, что вычисления корреляции производятся функцией КОРРЕЛ() по вышеуказанным формулам, в файле примера приведено вычисление корреляции с помощью более подробных формул:

=КОВАРИАЦИЯ.Г(B28:B88;D28:D88)/СТАНДОТКЛОН.Г(B28:B88)/СТАНДОТКЛОН.Г(D28:D88)

=КОВАРИАЦИЯ.В(B28:B88;D28:D88)/СТАНДОТКЛОН.В(B28:B88)/СТАНДОТКЛОН.В(D28:D88)

Примечание : Квадрат коэффициента корреляции r равен коэффициенту детерминации R2, который вычисляется при построении линии регрессии с помощью функции КВПИРСОН() . Значение R2 также можно вывести на диаграмме рассеяния , построив линейный тренд с помощью стандартного функционала MS 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.

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

=СУММПРОИЗВ(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)

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

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

которая имеет с n-2 степенями свободы.

Если вычисленное значение случайной величины |t r | больше, чем критическое значение t α,n-2 (α- заданный ), то нулевую гипотезу отклоняют (взаимосвязь величин является статистически значимой).

Надстройка Пакет анализа

В для вычисления ковариации и корреляции имеются одноименные инструменты анализа .

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

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

Надстройка возвращает вычисленные значения корреляции и ковариации (для ковариации также вычисляются дисперсии обоих случайных величин).

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

где k i – доходность ценной бумаги в i-ом периоде;

Ожидаемая (средняя) доходность ценной бумаги;

p i – доходность портфеля в i-ом периоде;

Ожидаемая (средняя) доходность портфеля;

n – количество наблюдений.

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

Пример . В таблице представлена динамика доходность акций Компании А и Компании Б, а также динамика доходности портфеля ценных бумаг.

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

• для акций Компании А 4,986%;
• для акций Компании Б 5,031%;
• для портфеля 3,201%.

Таким образом, ковариация акций Компании А с портфелем составит -0,313, а акций Компании Б 0,242.

Cov (k A , k p) = ((5,93-4,986)(2,27-3,201) + (5,85-4,986)(2,39-3,201) + (5,21-4,986)(3,47-3,201) + (5,37-4,986)(3,21-3,201) + (4,99-4,986)(2,95-3,201) + (4,87-4,986)(2,97-3,201) + (4,70-4,986)(3,32-3,201) + (4,75-4,986)(3,65-3,201) + (4,33-4,986)(3,97-3,201) + (3,86-4,986)(3,81-3,201))/(10-1) = -0,313

Cov (k Б, k p) = ((4,25-5,031)(2,27-3,201) + (4,47-5,031)(2,39-3,201) + (4,68-5,031)(3,47-3,201) + (4,71-5,031)(3,21-3,201) + (4,77-5,031)(2,95-3,201) + (5,25-5,031)(2,97-3,201) + (5,45-5,031)(3,32-3,201) + (5,33-5,031)(3,65-3,201) + (5,55-5,031)(3,97-3,201) + (5,85-5,031)(3,81-3,201))/(10-1) = 0,242

Аналогичные расчеты можно произвести в Microsoft Excel при помощи функции «КОВАРИАЦИЯ.В» для выборки из генеральной совокупности или функции «КОВАРИАЦИЯ.Г» для всей генеральной совокупности.

Интерпретация ковариации

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

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