Calculer la matrice de covariance à l'aide d'Excel

Dans Excel, j'ai 10 colonnes de données de la colonne A à la colonne J, et chaque colonne a 1000 lignes à partir de la ligne 1 à la ligne 1000. Je me demande comment faire pour calculer le 10 x 10 matrice de covariance de l'10 colonnes de données dans Excel?

Ma solution partielle est basé sur le signe du dollar et de la copie de formules:

J'ai d'abord entrée dans une cellule une formule =covar($A1:$A1000,A1:A1000).

Puis-je copier et coller de la formule dans les cellules sur la droite de la première cellule, ce qui me donne =covar($A1:$A1000,B1:B1000) ... =covar($A1:$A1000,J1:J1000).

Maintenant, je ne sais pas comment je peux obtenir =covar(B1:B1000,A1:A1000) ... =covar(J1:J1000,A1:A1000), parce que si je fais un copier-coller de la formule dans les cellules en dessous de la première cellule, je vais obtenir =covar($A2:$A1001,A2:A1001), ..., =covar($A1000:$A2001,A1000:A2001) à la place.

Merci!

  • quelque chose n'est pas tout à fait le sens de votre question... que voulez-vous précisément la deuxième et les suivantes des lignes? (en passant, si vous souhaitez ancrer les numéros de ligne utiliser A$1, ou d'ancrage des lignes et des colonnes $A$1)
InformationsquelleAutor StackExchange for All | 2011-09-27
  1. 4

    Pour faire de la formule "la copie de la preuve", vous pouvez faire usage de la =OFFSET() fonction en combinaison avec des lignes et des colonnes d'indices. Exemple:

    • dans L1...U1 entrer des nombres 1, 2, 3, ... 10
    • dans K2...K11 entrer des nombres 1, 2, 3, ... 10
    • maintenant, copiez-la preuve des références à l'un des 10 colonnes Un...J. Ceci peut être obtenu par:
      • =OFFSET($A$1:$A$1000,0,L$1-1) de suivre l'horizontale indice
      • =OFFSET($A$1:$A$1000,0,$K2-1) de suivre la verticale de l'indice de

    • et enfin, vous combinez les 2 ci-dessus dans 

      =COVAR(OFFSET($A$1:$A$1000,0,L$1-1),OFFSET($A$1:$A$1000,0,$K2-1))

    • cette formule que vous entrez dans L2, copier dans L2..U11 pour obtenir votre 10x10 matrice

    Espère que ça aide

    • Merci! (1) je me demande ce que les deuxième et troisième arguments de DÉCALAGE signifie? (2) Si il y a 1000 colonnes au lieu de 10, comment pouvez-vous accomplir la première des deux étapes de manière efficace, ou pensez-vous passer à une autre méthode différente de ce qui est décrit dans votre réponse?
    • OFFSET(de référence,les lignes,les colonnes,hauteur,largeur) ... pour plus de détails, consultez l'Aide d'Excel <F1> .... (2) je ne voudrais pas utiliser Excel qui (max 256 colonnes), alors max de ca 125 du col pourrait théoriquement être fait avec la matrice dans le même onglet, sinon 255 cols avec la matrice dans autre onglet ... c'est une question de temps et de patience; pour > 20 du col ou alors j'aurait plus de chances de faire une fonction VBA calcul de n covariances d'un seul coup
    InformationsquelleAutor MikeD