Matrice de covariance dans Excel

La matrice de covariance est une matrice carrée pour montrer la covariance entre les colonnes et la variance dans les colonnes. Excel présenté avec un outil `` d'analyse de données '' intégré pour déterminer la covariance entre les différents ensembles de données. Le présent article expliquera le calcul de la matrice de covariance dans Excel en couvrant les sujets suivants, notamment

Explication

La covariance est l'une des mesures utilisées pour comprendre comment une variable est associée à une autre variable. La formule suivante est utilisée pour la détermination de la covariance.

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

La matrice de covariance est une matrice carrée permettant de comprendre les relations présentées entre les différentes variables d'un ensemble de données. Il est facile et utile de montrer la covariance entre deux ou plusieurs variables.

La covariance aura des valeurs positives et négatives. Une valeur positive indique que deux variables diminueront ou augmenteront dans le même sens. Une valeur négative indique que si une variable diminue, une autre variable augmente et une relation inverse existe entre elles. La matrice de covariance est représentée dans le format suivant. La matrice de covariance tridimensionnelle est représentée par

Pour créer la matrice de covariance carrée 3 × 3, nous avons besoin de données tridimensionnelles. Les valeurs diagonales de la matrice représentent les variances des variables X, Y et Z (c.-à-d. COV (X, X), COV (Y, Y) et COV (Z, Z)). La matrice de covariance est symétrique par rapport à la diagonale. Cela indique que COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) et COV (Y, Z) = COV (Z, Y). Un point à retenir à propos de cette matrice est le résultat de la matrice de covariance NXN pour les données de n dimensions.

Comment utiliser une matrice de covariance dans Excel?

La matrice de covariance est utilisée dans diverses applications, notamment

Analyser comment deux vecteurs se différencient
Utilisé en machine learning pour déterminer les modèles de dépendance entre les deux vecteurs
La matrice de covariance est utilisée pour indiquer la relation entre les différentes dimensions des variables aléatoires
Utilisé dans la modélisation stochastique en ingénierie financière pour corréler les variables aléatoires
Principe Component est une autre application de la matrice de covariance aux variables originales aux variables linéaires indépendantes
Dans l'analyse des données, la matrice de covariance a un rôle essentiel.
La matrice de covariance est utilisée dans la théorie moderne du portefeuille dans l'estimation des risques.
Les mesures de la matrice de covariance sont utilisées pour anticiper les rendements des actifs financiers

Exemples de matrice de covariance dans Excel

Voici quelques exemples d'utilisation de la matrice de covariance dans Excel.

Vous pouvez télécharger ce modèle Excel de matrice de covariance ici - Modèle Excel de matrice de covariance

Exemple 1

Effectuer une analyse de covariance sur les notes obtenues par les élèves dans différentes matières.

Étape 1: Les données suivantes, y compris les notes des élèves en mathématiques, en anglais et en sciences, sont considérées comme indiqué dans la figure.

Étape 2: Allez dans l'onglet «Données» sur le ruban et trouvez le ToolPak «Analyse des données» dans le coin droit.

Si ToolPak «Analyse des données» n'est pas disponible, procédez comme suit

Étape A: Allez dans l'onglet «Fichier», puis sélectionnez les «options».

L'écran suivant s'ouvrira.

Étape B: accédez aux compléments. Sous l'option Gérer, assurez-vous que "Compléments Excel" est sélectionné et sélectionnez le bouton "Aller" comme indiqué sur la figure.

Étape C: Sélectionnez «Analysis-Tool Pak» et «Analysis-ToolPak VBA» comme indiqué sur la capture d'écran.

Une fois ces étapes terminées, le pak de l'outil «Analyse des données» est ajouté à l'onglet «Données».

Étape 3: Cliquez sur Analyse des données. Cela ouvre la boîte de dialogue «Analyse des données». Sélectionnez «Covariance» en faisant défiler vers le haut et cliquez sur «OK».

Il affiche la boîte de dialogue «Covariance».

Étape 5: Sélectionnez la plage d'entrée, y compris les noms des sujets, vérifiez les «étiquettes dans la première ligne» et indiquez la «plage de sortie» dans la feuille de calcul existante. Et cliquez sur «OK».

Étape 6: Nous obtiendrons la sortie comme suit -

La partie supérieure de la diagonale est vide car la matrice de covariance Excel est symétrique vers la diagonale.

Exemple # 2

Calcul de la matrice de covariance pour déterminer les écarts entre les rendements des différentes actions du portefeuille.

Étape 1: Pour cet exemple, les données suivantes, y compris les rendements boursiers, sont prises en compte.

Étape 2: Ouvre la boîte de dialogue «Analyse des données» et sélectionnez «Covariance» en faisant défiler vers le haut et cliquez sur «OK».

Il affiche la boîte de dialogue «Covariance».

Étape 3: Sélectionnez la plage d'entrée, y compris les en-têtes, vérifiez les «étiquettes dans la première ligne» et indiquez la «plage de sortie» dans la feuille de calcul existante. Et cliquez sur «OK».

Étape 4: Nous obtiendrons la sortie comme suit -

La partie supérieure de la diagonale est vide car la matrice de covariance est symétrique vers la diagonale.

Exemple # 3

Calcul de la matrice de covariance pour les cours des actions des entreprises

Étape 1: Pour cet exemple, les données suivantes, y compris les cours des actions de différentes sociétés, sont prises en compte.

Étape 2: Ouvre la boîte de dialogue «Analyse des données», sélectionnez «Covariance» en faisant défiler vers le haut et cliquez sur «OK».

Il affiche la boîte de dialogue «Covariance».

Étape 3: Sélectionnez la plage d'entrée, y compris les en-têtes, vérifiez les «étiquettes dans la première ligne» et donnez la «plage de sortie» dans la feuille de calcul existante et cliquez sur «OK».

Étape 4: Nous obtiendrons la sortie comme suit -

Choses à retenir

L'outil de covariance présenté par Excel présente certaines limites, notamment la détermination de la formule des variances de population uniquement, la création d'une matrice avec uniquement des valeurs diagonales inférieures et la prise en compte des formules uniquement pour une variance.
Lorsque les valeurs de retour sont modifiées, il ne met pas à jour automatiquement les valeurs de la matrice.
La moitié supérieure de la matrice est affichée comme vide car elle est symétrique et les valeurs de l'image miroir sont affichées dans la diagonale inférieure.