Вычислить ковариационную матрицу с помощью Excel
В Excel у меня есть 10 столбцов данных из столбца A в столбец J, и каждый столбец имеет 1000 строк из строки 1 в строку 1000. Интересно, как вычислить матрицу 10 x 10 ковариации из 10 столбцов данных в Excel?
Мое частичное решение основано на знаках доллара и копировальных формулах:
Сначала я =covar($A1:$A1000,A1:A1000) в ячейку формулу =covar($A1:$A1000,A1:A1000) .
Затем я =covar($A1:$A1000,B1:B1000) формулу в ячейки справа от первой ячейки, которая дает мне =covar($A1:$A1000,B1:B1000) … =covar($A1:$A1000,J1:J1000) ,
Теперь я не знаю, как я могу получить =covar(B1:B1000,A1:A1000) … =covar(J1:J1000,A1:A1000) , потому что, если я =covar(J1:J1000,A1:A1000) формулу в ячейки ниже первого я получаю =covar($A2:$A1001,A2:A1001) , …, =covar($A1000:$A2001,A1000:A2001) .
Благодаря!
Чтобы сделать формулу «copy-proof», вы можете использовать функцию =OFFSET() в сочетании с индексами строк и столбцов. Пример:
- в L1 … U1 введите числа 1, 2, 3, … 10
- в K2 … K11 введите числа 1, 2, 3, … 10
- теперь копии доказательств ссылки на один из 10 столбцов A … J. Это можно получить:
-
=OFFSET($A$1:$A$1000,0,L$1-1)чтобы следовать горизонтальному индексу -
=OFFSET($A$1:$A$1000,0,$K2-1)чтобы следовать вертикальному индексу
-
-
и, наконец, вы объедините 2 выше в
=COVAR(OFFSET($A$1:$A$1000,0,L$1-1),OFFSET($A$1:$A$1000,0,$K2-1)) -
эту формулу, которую вы вводите в L2 , скопируйте в L2..U11, чтобы получить свою матрицу 10×10
надеюсь, это поможет