Как создать ковариационную матрицу в excel

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

Формула для расчета ковариации между двумя переменными X и Y :

COV( X , Y ) = Σ(x- x )(y- y ) / n

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

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

Как создать ковариационную матрицу в Excel

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

Чтобы создать ковариационную матрицу для этого набора данных, щелкните параметр «Анализ данных» в правом верхнем углу Excel на вкладке «Данные» .

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

Как только вы нажмете на эту опцию, появится новое окно. Нажмите «Ковариация» .

В поле « Диапазон ввода» введите «$A$1:$C$11», поскольку это диапазон ячеек, в которых расположен наш набор данных. Установите флажок «Метки в первой строке» , чтобы сообщить Excel, что метки для наших переменных находятся в первой строке. Затем в поле Выходной диапазон введите любую ячейку, в которой вы хотите, чтобы отображалась ковариационная матрица. Я думаю о ячейке $E$2. Затем нажмите ОК .

Ковариационная матрица генерируется автоматически и появляется в ячейке $E$2:

Как интерпретировать ковариационную матрицу

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

Значения по диагоналям матрицы — это просто дисперсии каждого предмета. Например:

• Разница оценок по математике составляет 64,96.
• Дисперсия оценок по естествознанию составляет 56,4.
• Историческая дисперсия баллов составляет 75,56.

Остальные значения матрицы представляют собой ковариации между разными субъектами. Например:

• Ковариация между оценками по математике и естественным наукам составляет 33,2.
• Ковариация между оценками по математике и истории составляет -24,44.
• Ковариация между оценками по естествознанию и истории составляет -24,1.

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

Отрицательное число ковариации указывает на то, что по мере увеличения одной переменной вторая переменная имеет тенденцию к уменьшению. Например, математика и история имеют отрицательную ковариацию (-24,44), что указывает на то, что учащиеся, набравшие высокие баллы по математике, как правило, получают низкие баллы по истории. Точно так же учащиеся с низкими баллами по математике, как правило, получают высокие баллы по истории.