Ковариация

Содержание
  1. Статистика - это грамматика науки о данных. Часть 4
  2. Введение
  3. Ковариация
  4. Формула
  5. Ограниченность
  6. Корреляция
  7. Ключевые свойства
  8. Корреляция и Ковариация
  9. Линейная корреляционная связь
  10. Ковариация
  11. Коэффициент линейной корреляции
  12. Примеры
  13. Разница между ковариацией и корреляцией
  14. Сравнительная таблица
  15. Определение ковариации
  16. Определение корреляции
  17. Ключевые различия между ковариацией и корреляцией
  18. сходства
  19. Заключение
  20. Корреляция, ковариация и девиация (часть 3)
  21. 7. Векторизация и нормирование одномерных координат
  22. 8. Векторизация и ортонормирование многомерных координат
  23. 9. Матрица Грина — это матрица корреляции векторов
  24. Примеры расчетов функций КОВАРИАЦИЯ.В и КОВАРИАЦИЯ.Г в Excel
  25. Статистический анализ ковариации показателей в Excel
  26. Особенности использования функций КОВАР, КОВАРИАЦИЯ.В и КОВАРИАЦИЯ.Г в Excel
  27. Корреляция и ковариация в EXCEL
  28. Теоретическое отступление
  29. Использование MS EXCEL для расчета корреляции
  30. Использование MS EXCEL для расчета ковариации
  31. Оценка статистической значимости коэффициента корреляции
  32. Надстройка Пакет анализа

Статистика - это грамматика науки о данных. Часть 4

Ковариация

Повторение статистики для начала путешествия по науке о данных

Часть 1, Часть 2, Часть 3, Часть 4, Часть 5

Введение

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

Диаграмма рассеяния «профессиональный опыт vs доход»

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

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

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

N.B: в приведенном примере представлен двумерный вид данных (с двумя переменными). На практике статистики используют многомерные данные (со множеством переменных).

Ковариация

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

С английского «covariance» = «co» (совместность/соединение) + «variance» (дисперсия).

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

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

Ковариация измеряет дисперсию между двумя переменными.

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

Формула

Формулу сложно объяснить, но важно понять, что она означает:

Ковариация между переменными X и Y

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

  • #1. Преобразуйте данные два вектора в векторы отклонений от среднего значения.
  • #2. Скалярное произведение двух векторов (которое равно косинусу угла между ними) поделите на размер выборки (n или n — 1, в зависимости от того, дана ли полная совокупность или нет).

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

Ограниченность

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

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

В этом случае на помощь приходит корреляция!

Радужная диаграмма рассеяния. Источник: Scipy

Корреляция

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

Ключевые свойства

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

Напоследок необходимо запомнить, что корреляция не является причинно-следственной связью.

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

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

Перевод статьи Semi Koen: Statistics is the Grammar of Data Science — Part 4/5

Источник: https://nuancesprog.ru/p/3332/

Корреляция и Ковариация

Ковариация

Существует два вида зависимости между переменными величинами:

  • Функциональная зависимость. Каждому значению одной переменной x соответствует СТРОГО только одно значение другой переменной y: y=f(x). (Бывают и многозначные функциональные связи, но они всегда строгие.) Такая функциональная связь имеет место только в случае, когда переменная y зависит только от переменной x и больше ни от чего. На практике, при анализе биржевых цен, таких функциональных связей не существует. Функциональная связь, это сильно упрощенная математическая модель.
  • Статистическая зависимость. Каждому значению одной переменной x соответствует некоторое распределение вероятности переменной y. Например, пусть переменная y, это биржевая цена, а x, это какой-то параметр, от которого в нашей математической модели зависит цена. В этом случае на цену влияет явным образом не только значение параметра x, но и множество других факторов, которые существуют в реальности, но не учитываются упрощенной математической моделью. Многие из этих факторов являются случайными величинами.

Линейная корреляционная связь

Таких статистических связей может быть очень много самых разных. Для трейдера самым важным видом статистической связи является корреляционная связь.

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

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

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

Ковариация

Пусть математическое ожидание и дисперсия случайной величины X равны, соответственно, μx и σx2. А математическое ожидание и дисперсия случайной величины Y равны, соответственно, μy и σy2.

Для независимых случайных величин X и Y всегда матожидание произведения случайных величин равно произведению их матожиданий по отдельности:

M(xy) = μxμy

А для зависимых случайных величин это равенство не выполняется.

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

cov(x,y) ≡ σxy = σyx = M(xy) — μxμy = M[(x-μx)(y-μy)]

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

Матрица ковариаций для нескольких случайных величин X, Y, …, Z всегда симметрична, причем на главной диагонали этой матрицы всегда стоят положительные числа, равные дисперсиям случайных величин X, Y, …, Z.

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

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

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

Коэффициент корреляции, это ковариация, нормированная на среднеквадратичные отклонения двух случайных величин.

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

  1. Коэффициент корреляции может принимать значения от -1 до +1. Значения -1 и +1 этот коэффициент принимает только при линейной функциональной зависимости между X и Y.

    Обычно, говорят, что если коэффициент корреляции равен +1, то это абсолютно коррелирующие величины (или коррелированные на все 100%). А если коэффициент корреляции равен -1, то говорят, что это абсолютно антикоррелирующие величины (или антикоррелированные на все 100%).

  2. Коэффициент корреляции между независимыми случайными величинами равен нулю. Но обратное неверно! Если коэффициент корреляции двух случайных величин равен нулю, то это ещё не означает, что эти случайные величины независимые. Они просто некоррелированные.
  3. Линейные преобразования случайных величин X и Y не изменяют их коэффициента корреляции: ρ(x,y)=ρ(a+bx,c+dy)

Матрица коэффициентов корреляций для нескольких случайных величин X, Y, …, Z всегда симметрична, причем на главной диагонали этой матрицы всегда стоят единицы.

Примеры

Допустим, в каком-то эксперименте в равные промежутки времени измеряют две величины, X и Y. Если их значения меняются, как на этом графике, то это полностью коррелированные величины с коэффициентом корреляции, равным +1.

Этот факт говорит о том, что между величинами X и Y имеется строгая функциональная зависимость: Y=f(X).

Допустим, в каком-то эксперименте в равные промежутки времени измеряют две величины, X и Y. Если их значения меняются, как на следующем графике, то это полностью антикоррелированные величины с коэффициентом корреляции, равным -1.

Этот факт также говорит о том, что между величинами X и Y имеется какая-то строгая функциональная зависимость: Y=g(X).

Теперь рассмотрим реальные цены. Для примера рассмотрим коэффициенты корреляции между ценами валютной пары EURUSD и ценами валютных пар GBPUSD, USDCHF и USDJPY. Для расчета возьмем дневные графики за первую половину 2017 года.

EURUSD

GBPUSD

USDCHF

USDJPY

Расчеты, сделанные по ценам закрытия тайм-фреймов дают следующие коэффициенты корреляции за полгода:

  • ρ(eurusd,gbpusd)=0.8030
  • ρ(eurusd,usdchf)=-0.9598
  • ρ(eurusd,usdjpy)=-0.4802

Эти коэффициенты корреляции достаточно ожидаемые.

Достаточно сильная корреляция между EURUSD и GBPUSD объясняется достаточно сильными связями экономики ЕвроЗоны и экономики Британии.

Очень сильная антикорреляция между EURUSD и USDCHF объясняется еще более сильной связью между экономиками ЕвроЗоны и Швейцарии.

А знак минус получился потому что в валютной паре USDCHF швейцарский франк стоит в знаменателе, в то время как в валютной паре EURUSD евро стоит в числителе.

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

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

А вот в первую половину 2017 года швейцарский франк оказался привязанным к евро очень сильно. Коэффициент корреляции менялся в пределах от -0.96 до -0.78. Это и понятно, ведь Швейцария со всех сторон окружена ЕвроЗоной. Поэтому её экономика должна быть сильно связана с экономикой ЕвроЗоны. Гораздо сильнее, чем британская экономика с экономикой ЕвроЗоны.

А вот что касается евро и йены, то тут ситуация самая интересная. В начале первого полугодия 2017 года была антикорреляция выше средней, примерно -0.71. Потом эта антикорреляция исчезла до нуля. Но на этом изменения коэффициента корреляции не остановились.

Коэффициент корреляции вырос до +0.2564.

Так как евро в валютной паре EURUSD находится в числителе, а йена в валютной паре USDJPY находится в знаменателе, то получается, что в начале года евро и йена сильно коррелировали, а к середине года стали слегка антикоррелировать.

Источник: http://chance.nanoquant.ru/correlation.htm

Разница между ковариацией и корреляцией

Ковариация

Ковариация и корреляция — это две математические концепции, которые довольно часто используются в бизнес-статистике.

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

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

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

Сравнительная таблица

Основа для сравненияковариациикорреляция
Имея в видуКовариация — это мера, показывающая степень изменения двух случайных величин в тандеме.Корреляция — это статистическая мера, которая показывает, насколько сильно связаны две переменные.
Что это?Мера корреляцииМасштабная версия ковариации
ЦенностиЛежат между -∞ и + ∞Лежат между -1 и +1
Изменение масштабаВлияет на ковариациюНе влияет на корреляцию
Единица измерениянетда

Определение ковариации

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

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

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

В дополнение к этому, когда все наблюдения одной и той же переменной совпадают, ковариация будет равна нулю.

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

Определение корреляции

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

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

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

  • Корреляционная диаграмма
  • Коэффициент продукт-момент корреляции
  • Коэффициент ранговой корреляции
  • Коэффициент одновременных отклонений

Ключевые различия между ковариацией и корреляцией

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

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

сходства

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

Заключение

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

Источник: https://ru.gadget-info.com/difference-between-covariance

Корреляция, ковариация и девиация (часть 3)

Ковариация

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

В данной статье покажем, что матрица Грина и матрица корреляции — суть одно и то же.

7. Векторизация и нормирование одномерных координат

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

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

Для выполнения данной операции нам нужно вычислить эту сумму (точнее среднее):

Теперь можно построить ССК исходного набора как совокупность собственного числа S и нормированных координат вектора:

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

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

Упражнение для тех, кто любит «щупать руками». Построить ССК для набора {1, 2, 3, 4}. Ответ. Собственное число (дисперсия): 1.25. Собственный вектор: {-1.342, -0.447, 0.447, 1.342}.

8. Векторизация и ортонормирование многомерных координат

Что, если вместо набора чисел нам задан набор векторов — пар, троек и прочих размерностей чисел. То есть точка (узел) задается не одной координатой, а несколькими. Как в этом случае построить ССК? Стандартный путь следующий.

Введем обозначение характеристик (компонент) набора. Нам заданы точки (элементы) и каждой точке соответствует числовое значение характеристики .

Обращаем внимание, что второй индекс — это номер характеристики (столбцы матрицы), а первый индекс — номер точки (элемента) набора (строки матрицы).

Далее векторизуем характеристики. То есть для каждой находим центроид (среднее значение) и вычитаем его из значения характеристики:

Получили матрицу координат векторов (МКВ) .

Следующим шагом как будто бы надо вычислить дисперсию для каждой характеристики и их нормировать. Но хотя таким образом мы действительно получим нормированные векторы, нам-то нужно, чтобы эти векторы были независимыми, то есть ортонормированными. Операция нормирования не поворачивает вектора (а лишь меняет их длину), а нам нужно развернуть векторы перпендикулярно друг другу. Как это сделать? Правильный (но пока бесполезный) ответ — рассчитать собственные вектора и числа (спектр). Бесполезный потому, что мы не построили матрицу, для которой можно считать спектр. Наша матрица координат векторов (МКВ) не является квадратной — для нее собственные числа не рассчитаешь. Соответственно, надо на основе МКВ построить некую квадратную матрицу. Это можно сделать умножением МКВ на саму себя (возвести в квадрат).

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

Умножая МКВ на транспонированную, мы получаем матрицу корреляции: Из данного определения (есть и другие) следует, что элементы матрицы корреляции являются скалярными произведениями векторов (грамиан на векторах). Значения главной диагонали отражают квадрат длины данных векторов. Значения матрицы не нормированы (обычно их нормируют, но для наших целей этого не нужно). Размерность матрицы корреляции совпадает с количеством исходных точек (векторов).

Теперь переставим перемножаемые в (8.1) матрицы местами и получим матрицу ковариации (опять же опускаем множитель 1/(1-n), которым обычно нормируют значения ковариации):

Здесь результат выражен в характеристиках. Соответственно, размерность матрицы ковариации равна количеству исходных характеристик (компонент). Для двух характеристик матрица ковариации имеет размерность 2×2, для трех — 3×3 и т.д.

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

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

Отметим, что метод главных компонент как раз и состоит в расчете спектра матрицы ковариации/корреляции для заданного набора векторных данных.

Найденные компоненты спектра располагаются вдоль главных осей эллипсоида данных.

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

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

9. Матрица Грина — это матрица корреляции векторов

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

Достаточно ли данной информации для определения ССК (собственной системы координат) набора?

Ответ дан в первой части — да, вполне. Здесь же мы покажем, что построенная по формуле (1.

3') матрица Грина и определенная выше матрица корреляции векторов (8.1) — это одна и та же матрица.

Как такое получилось? Сами в шоке. Чтобы в этом убедиться, надо подставить выражение для элемента матрицы квадратов расстояний в формулу преобразования девиации: Отметим, что среднее значение матрицы квадратов расстояний отражает дисперсию исходного набора (при условии, что расстояния в наборе — это сумма квадратов компонент): Подставляя (9.1) и (9.3) в (9.2), после несложных сокращений приходим к выражению для матрицы корреляции (8.1): Итак, матрица Грина и матрица корреляции векторов — суть одно и то же. Ранг матрицы корреляции совпадает с рангом матрицы ковариации (количеством характеристик — размерностью пространства). Это обстоятельство позволяет строить спектр и собственную систему координат для исходных точек на основе матрицы расстояний. Для произвольной матрицы расстояний потенциальный ранг (количество измерений) на единицу меньше количества исходных векторов. Расчет спектра (собственной системы координат) позволяет определить основные (главные) компоненты, влияющие на расстояния между точками (векторами). Таким образом можно строить собственные координаты элементов либо на основании их характеристик, либо на основании расстояний между ними. Например, можно определить собственные координаты городов по матрице расстояний между ними.

  • дисперсия
  • корреляция
  • ковариация
  • девиация
  • метод главных компонент

Хабы:

Источник: https://habr.com/ru/post/263907/

Примеры расчетов функций КОВАРИАЦИЯ.В и КОВАРИАЦИЯ.Г в Excel

Ковариация

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: https://exceltable.com/funkcii-excel/primery-raschetov-kovariacii

Корреляция и ковариация в EXCEL

Ковариация

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

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

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

Источник: https://excel2.ru/articles/korrelyaciya-i-kovariaciya-v-ms-excel

Все термины
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: