Математически ковариация (англ. 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) коэффициента ковариации наблюдается в том случае, когда колебания доходности ценной бумаги и доходности портфеля носят случайный характер.

Функция КОВАРИАЦИЯ.В в Excel предназначена для расчета коэффициента ковариации двух наборов данных (массивов или диапазонов ячеек, хранящих числовые значения), являющихся выборками соответствующих диапазонов данных, и возвращает соответствующее числовое значение.

Функция КОВАРИАЦИЯ.Г в Excel используется для расчета коэффициента ковариации всей совокупности двух диапазонов данных (генеральной совокупности) и возвращает соответствующее значение.

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

Использование функций КОВАР, КОВАРИАЦИЯ.В и КОВАРИАЦИЯ.Г в Excel

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

Вид исходной таблицы:

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

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

• B3:B14 – диапазон ячеек, содержащих данные о количестве прочитанных книг;
• C3:C14 – диапазон ячеек с итоговыми оценками по предмету.

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

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



Расчет ковариации роста и падения цен двух видов акций в Excel

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

Вид исходной таблицы:

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

В результате получим:

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

Статистический анализ ковариации показателей в Excel

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

Вид исходной таблицы данных:

Вначале рассчитаем ковариацию между спросом и индексом цен по формуле:

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

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

B12/КОРЕНЬ(ДИСП.Г(B3:B10)*ДИСП.Г(C3:C10))

Функция ДИСП.Г используется для расчета дисперсии генеральной совокупности. Приведенная выше формула наглядно демонстрирует взаимосвязь между коэффициентами ковариации и корреляции.

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

Как видно, между ценами и спросом существует довольно сильная обратная связь. Однако для определения степени влияния спроса определим коэффициент детерминации r2 по формуле:

СТЕПЕНЬ(B13;2)

Полученное значение, выраженное в процентах:

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

КОРРЕЛ(B3:B10;D3:D10)

Результат:

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

КОРРЕЛ(C3:C10;D3:D10)

Результат:

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

=(B13-B15*B16)/КОРЕНЬ((1-СТЕПЕНЬ(B15;2))*(1-СТЕПЕНЬ(B16;2)))

Результат:

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

Особенности использования функций КОВАР, КОВАРИАЦИЯ.В и КОВАРИАЦИЯ.Г в Excel

Функция КОВАР имеет следующий синтаксис:

КОВАР(массив1;массив2 )

Функция КОВАРИАЦИЯ.В имеет следующую синтаксическую запись:

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

Синтаксис функции КОВАРИАЦИЯ.Г:

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

Все рассматриваемые функции принимают на вход следующие аргументы:

• массив1 – обязательный аргумент, характеризующий первый массив или диапазон ячеек, содержащих данные числового типа, которые являются всей генеральной совокупностью данных (для функций КОВАРИАЦИЯ.Г и КОВАР) или выборкой (для функции КОВАРИАЦИЯ.В);
• массив2 – обязательный аргумент, характеризующий второй массив или диапазон ячеек с числовыми значениями (генеральная совокупность либо выборка, чем обусловлен выбор функции для расчета).

Примечания 1:

1. Все рассматриваемые функции принимают в качестве аргументов массивы или ссылки на диапазоны ячеек, содержащие текстовые, логические, числовые и данные других типов.
2. Число элементов в диапазонах или массивах, переданных в качестве аргументов массив1 и массив2 должны совпадать. В противном случае все рассматриваемые функции вернут код ошибки #Н/Д.
3. При расчете не учитываются значения типа Текст, Имя, логические значения (ИСТИНА, ЛОЖЬ), ссылки на пустые ячейки. Однако ячейки, содержащие числовое значения 0 (нуль), будут учтены.
4. Если рассматриваемые функции в качестве аргументов принимают:

• Диапазоны пустых ячеек, результатом их выполнения будет код ошибки #ЗНАЧ! (принимают по одной пустой ячейке в качестве каждого аргумента) или #ДЕЛ/0! (принимают по несколько пустых ячеек в качестве аргументов);
• Массивы, состоящие из одного элемента или по одной ячейке в качестве каждого аргумента, функции КОВАРИАЦИЯ.Г и КОВАР вернут числовое значение 0, а функция КОВАРИАЦИЯ.В – код ошибки #ДЕЛ/0!.

Примечания 2:

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

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

Примечания 3:

1. Функция КОВАР являлась стандартной функцией для расчета ковариации в ранних версиях Excel (2007 и более старых) и оставлена для обеспечения совместимости. В последующих версиях Excel она может отсутствовать, поэтому рекомендуется использовать функции КОВАРИАЦИЯ.В и КОВАРИАЦИЯ.Г.
2. Выборка – это подмножество величин одного множества, называемого генеральной совокупностью. Другими словами, выборкой считается результат ограниченного ряда наблюдений какого-либо одно или нескольких признаков. Например, при изучении банковской системы государства генеральной совокупностью являются все банковские организации страны, а выборкой – банки города Санкт-Петербург.
3. В отличие от коэффициента корреляции, значение коэффициента ковариации не ограничено диапазоном чисел от -1 до 1.
4. При определении коэффициента ковариации одних и тех же двух диапазонов чисел функции КОВАР и КОВАРИАЦИЯ.Г вернут одинаковый результат, отличающийся от числового значения, которое вернет функция КОВАРИАЦИЯ.В, поскольку они используют разные алгоритмы расчетов.

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

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

где

Ковариационная матрица имеет вид

где по диагонали стоят дисперсии координат случайного вектора o n =D Xi , o 22 =D X2 , о кк = D Xk , а остальные элементы представляют собой ковариации между координатами

°12 = M"x i x 2 j а 1* = M-jc,** >

Ковариационная матрица является симметрической матрицей, т.е.

Для примера рассмотрим ковариационную матрицу двумерного вектора

Аналогично получается ковариационная матрица для любого /^-мерного вектора.

Дисперсии координат можно представить в виде

где Gi,C2,...,0? - средние квадратичные отклонения координат случайного вектора.

Коэффициентом корреляции называется, как известно, отношение ковариации к произведению средних квадратичных отклонений:

После нормирования по последнему соотношению членов ковариационной матрицы получают корреляционную матрицу

которая является симметрической и неотрицательно определенной.

Многомерным аналогом дисперсии случайной величины является обобщенная дисперсия, под которой понимается величина определителя ковариационной матрицы

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

где Скк - диагональные элементы ковариационной матрицы.

Часто в многомерном статистическом анализе используется нормальное распределение.

Обобщением нормальной плотности вероятности на случай ^-мерного случайного вектора является функция

где ц = (pj, ц 2 , М^) т - вектор-столбец математических ожиданий;

|Х| - определитель ковариационной матрицы X;

1 - обратная ковариационная матрица.

Матрица X -1 , обратная к матрице X размерности пх п, может быть получена различными способами. Одним из них является метод Жордана-Гаусса. В этом случае составляется матричное уравнение

где х - вектор-столбец переменных, число которых равно я; b - я-мерный вектор-столбец правых частей.

Умножим слева уравнение (6.21) на обратную матрицу ХГ 1:

Так как произведение обратной матрицы на данную дает единичную матрицу Е, то

Если вместо b взять единичный вектор

то произведение X -1 -е х дает первый столбец обратной матрицы. Если же взять второй единичный вектор

то произведение Е 1 е 2 дает первый столбец обратной матрицы и т.д. Таким образом, последовательно решая уравнения

методом Жордана-Гаусса, получаем все столбцы обратной матрицы.

Другой метод получения матрицы, обратной к матрице Е, связан с вычислением алгебраических дополнений A tJ .= (/= 1, 2,..., п; j = 1, 2, ..., п) к элементам данной матрицы Е, подстановкой их вместо элементов матрицы Е и транспортированием такой матрицы:

Обратная матрица получается после деления элементов В на определитель матрицы Е:

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

Пример. Написать выражение плотности вероятности для нормально распределенной двумерной случайной величины {X v Х 2)

при условии, что математические ожидания, дисперсии и ковариации этих величин имеют следующие значения:

Решение. Обратную ковариационную матрицу для матрицы (6.19) можно получить, используя следующее выражение обратной матрицы к матрице X:

где А - определитель матрицы X.

А и, Л 12 , А 21 , А 22 - алгебраические дополнения к соответствующим элементам матрицы X.

Тогда для матрицы ]г- ! получаем выражение

Так как а 12 = 01О2Р и °2i =a 2 a iP> а a i2 a 2i = cyfст|р, то Значит,

Найдем произведение

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

Подставив исходные данные, получим следующее выражение для функции плотности вероятности

Рассмотрим технику расчета ковариации и корреляции доходностей бумаг на примере.

Доходность бумаги X за пять лет составила соответственно 20%, 25%, 22%, 28%, 24%. Доходность бумаги F: 24%, 28%, 25%, 27%, 23%. Определить ковариацию доходностей бумаг.

Приведем решение задачи двумя способами.

а) Печатаем в хронологическом порядке в ячейках с Al no A5 значения доходности бумаги X, а в ячейках с В1 по В5 - доходности бумаги F. Решение получим в ячейке С1, поэтому наводим на нее курсор и щелкаем мышью. Печатаем в ячейке С1 формулу:

и нажимаем клавишу Enter. В ячейке С1 появилось решение задачи - цифра 3,08, т.е. выборочная ковариация для нашего примера.

б) Ковариацию можно рассчитать с помощью программы "Мастер функций". Для этого наводим курсор на значок А на панели инструментов и щелкаем мышью. Появилось окно "Мастер функций". В левом поле ("Категория") наводим курсор на строку "Статистические" и щелкаем мышью. Строка высветилась синим цветом, а в правом поле окна ("Функция") появился перечень статистических функций. Наводим курсор на строку "КОВАР" и щелкаем левой клавишей мыши. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "КОВАР". В окне две строки, которые называются "Массив 1" и "Массив 2". В первую строку заносим номера ячеек с А1 по А5. Для этого наводим курсор на знак 3, расположенный с правой стороны первой строки и щелкаем мышью. Окно "КОВАР" превратилось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, доводим курсор вниз до ячейки А5 и отпускаем клавишу. В поле строки появилась запись А1:А5. Вновь наводим курсор на знак??? и щелкаем мышью. Появилось развернутое окно "КОВАР". Заносим номера ячеек с Bl no B5 во вторую строку. Для этого наводим курсор на знак 5J во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, доводим курсор вниз до ячейки В5, отпускаем клавишу. В поле строки появилась запись В1:В5. Наводим курсор на кнопку 3| и щелкаем мышью. Появилось развернутое окно "КОВАР". Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке С1 появилась цифра 3,08.

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

а) Печатаем в хронологическом порядке в ячейках с Al no A5 значения доходности бумаги X, а в ячейках с В1 по В5 - доходности бумаги F. Решение получим в ячейке С1, поэтому наводим на нее курсор и щелкаем мышью. Печатаем в ячейке С1 формулу:

и нажимаем клавишу Enter. В ячейке С1 появилось решение задачи - цифра 0,612114.

б) Корреляцию можно рассчитать с помощью программы "Мастер функций". Для этого выбираем курсором на панели инструментов значок л» и щелкаем мышью. Появилось окно "Мастер функций". В левом поле ("Категория") выбираем курсором строку "Статистические" и щелкаем мышью. В правом поле окна ("Функция") появился перечень статистических функций. Выбираем курсором строку "КОРРЕЛ" и щелкаем мышью. Строка высветилась синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно "КОРРЕЛ". В окне две строки, которые называются "Массив 1" и "Массив 2". В первую строку заносим номера ячеек с Al no A5. Для этого наводим курсор на знак ЗР справа от первой строки и щелкаем мышью. Окно "КОРРЕЛ" превратилось в поле первой строки. Наводим курсор на ячейку А1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим курсор вниз до ячейки А5 и отпускаем клавишу. В поле строки появилась запись А1:А5. Вновь наводим курсор на знак Щ и щелкаем мышью. Появилось развернутое окно "КОРРЕЛ". Заносим номера ячеек с Bl no B5 во вторую строку. Для этого наводим курсор на знак Ш во второй строке и щелкаем мышью. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, доводим курсор вниз до ячейки В5, отпускаем клавишу. В поле строки появилась запись В1:В5. Наводим курсор на кнопку Щ и щелкаем мы шью. Появилось развернутое окно "КОРРЕЛ". Наводим курсор на кнопку ОК и щелкаем мышью. В ячейке С1 появилась цифра 0,612114.

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

Знаете ли Вы, что: Форекс-брокер «NPBFX» выводит абсолютно все сделки своих клиентов на поставщиков ликвидности (на межбанковский рынок), работая по технологии STP/NDD (Straight-through processing – сквозная обработка транзакций / Non Dealing Desk).

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

Пример 3. Расчет ковариации

Имеется выборка данных по доходностям бумаг В, С и D за десять периодов. Печатаем значения доходности для бумаги В в ячейки от В1 до В10, бумаги С от С1 до СЮ и бумаги D от D1 до D10, как показано на рис. 1.8. Наводим курсор на меню "Сервис" и щелкаем левой клавишей мыши. Появилось выпадающее меню. Наводим курсор на строку "Анализ данных" и щелкаем левой клавишей мыши. Появилось окно" Анализ данных". Наводим курсор на строку "Ковариация" и щелкаем левой клавишей мыши. Строка высвечивается синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно Ковариация". (см. рис. 1.10).

Наводим курсор на знак 3 справа от поля строки "Входной интервал" и щелкаем мышью. Окно "Ковариация" свернулось в поле строки. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, проводим до ячейки D10. В строке появилась запись $B$1:$D$10. Вновь наводим курсор на знак и щелкаем мышью. Появилось развернутое окно "Ковариация". Группировку данных проводим по столбцам. Поэтому, если в круглом окне слева от надписи "по столбцам" не стоит точка, то наводим на нее курсор и щелкаем левой клавишей мыши. В окне появится точка. Ниже расположена строчка "Выходной интервал". В круглом окне слева от надписи должна стоять точка. Если ее нет, то наводим курсор на данную строчку и щелкаем левой клавишей мыши. В окне появится точка. Наводим курсор на знак 3 справа от поля строки "Выходной интервал" и щелкаем мышью. Окно "Ковариация" превратилось в поле строки. В качестве начала выходного интервала возьмем ячейку А12. Поэтому наводим на нее курсор и нажимаем левую клавишу мыши. В поле строки появилась запись $А$12. Вновь наводим курсор на знак 3 и щелкаем мышью. Окно "Ковариация" развернулось. Наводим курсор на кнопку ОК и щелкаем мышью. На листе появилось решение задачи как показано на рис. 1.11. В блоке от В13 до D15 представлена ковариационная матрица. По ее диагонали, т.е. в ячейках В13, С14 и В15 расположены дисперсии соответственно бумаг В, С и D, в остальных ячейках - ковариации доходностей бумаг: в ячейке В14 ковариация доходностей бумаг В и С, в В15 – бумаг B и D,в С15 - бумаг C и D.

Пример 4. Расчет корреляций

Имеется выборка данных по доходностям трех бумаг - В, С и D - за десять периодов. Как и в задаче 3, печатаем значения доходности для бумаги В в ячейки от В1 до В10, бумаги С от С1 до С10 и бумаги D от D1 до D10 (рис. 1.9). Наводим курсор на меню "Сервис" и щелкаем левой клавишей мыши. Появилось выпадающее меню. Наводим курсор на строку "Анализ данных" и щелкаем левой клавишей мыши. Появилось окно" Анализ данных". Наводим курсор на строку "Корреляция" и щелкаем левой клавишей мыши. Строка высвечивается синим цветом. Наводим курсор на кнопку ОК и щелкаем мышью. Появилось окно корреляция (по структуре оно аналогично окну "ковариация)". Наводим курсор на знак 3 справа от поля строки "Входной интервал" и щелкаем мышью. Окно "Корреляция" свернулось в поле строки. Наводим курсор на ячейку В1, нажимаем левую клавишу мыши и, удерживая ее в нажатом положении, доводим курсор до ячейки D10. В строке появилась запись $B$1:$D$10. Вновь наводим курсор на знак и щелкаем мышью. Появилось развернутое окно "Корреляция". Группировку данных проводим по столбцам. Поэтому, если в круглом окне слева от надписи "по столбцам" не стоит точка, то наводим на нее курсор и щелкаем левой клавишей мыши. В окне появится точка. Ниже расположена строчка "Выходной интервал". В круглом окне слева от надписи должна стоять точка. Если ее нет, то наводим курсор на данную строчку и щелкаем левой клавишей мыши. В окне появится точка. Наводим курсор на знак 3 справа от поля строки "Выходной интервал" и щелкаем мышью. Окно "Корреляция" превратилось в поле строки. В качестве начала выходного интервала возьмем ячейку А12. Поэтому наводим на нее курсор и нажимаем левую клавишу мыши. В поле строки появилась запись $А$12. Вновь наводим курсор на знак 3 и щелкаем мышью. Окно "Корреляция" развернулось. Наводим курсор на кнопку ОК и щелкаем мышью. На листе появилось решение задачи как показано на рис 1.12. В блоке от В13 до D15 представлена корреляционная матрица. По ее диагонали, т.е. в ячейках В13, С14 и D15 расположены единицы, в остальных ячейках - корреляции доходностей бумаг: в ячейке В14 корреляция доходностей бумаг В и С, в В15 - бумаг B и D, в С15 - бумаг C и D.

Содержание

Ранее была рассмотрена диаграмма разброса, иллюстрирующая распределение двумерных числовых данные (см. последний раздел Изображение двумерных числовых данных заметки ). В настоящей заметке мы изучим два количественных показателя, характеризующих силу зависимости между двумя переменными - ковариацию и коэффициент корреляции. Ковариация оценивает силу линейной зависимости между двумя числовыми переменными 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