Вычислить ковариационную матрицу с помощью 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

надеюсь, это поможет

Давайте будем гением компьютера.