Matrice di covarianza in Excel

Matrice di covarianza in Excel

La matrice di covarianza è una matrice quadrata per mostrare la covarianza tra le colonne e la varianza nelle colonne. Excel ha presentato lo strumento "Analisi dei dati" integrato per determinare la covarianza tra i diversi set di dati. Il presente articolo spiegherà il calcolo della matrice di covarianza in Excel coprendo i seguenti argomenti, tra cui

Spiegazione

La covarianza è una delle misure utilizzate per capire come una variabile è associata a un'altra variabile. La seguente formula viene utilizzata per la determinazione della covarianza.

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

La matrice di covarianza è una matrice quadrata per comprendere le relazioni presentate tra le diverse variabili in un set di dati. È facile e utile mostrare la covarianza tra due o più variabili.

La covarianza avrà valori sia positivi che negativi. Un valore positivo indica che due variabili diminuiranno o aumenteranno nella stessa direzione. Un valore negativo indica che se una variabile diminuisce, un'altra variabile aumenta e tra di loro esiste una relazione inversa. La matrice di covarianza è rappresentata nel seguente formato. La matrice di covarianza tridimensionale è mostrata come

Per creare la matrice di covarianza quadrata 3 × 3, abbiamo bisogno di dati tridimensionali. I valori diagonali della matrice rappresentano le varianze delle variabili X, Y e Z (cioè COV (X, X), COV (Y, Y) e COV (Z, Z)). La matrice di covarianza è simmetrica rispetto alla diagonale. Ciò indica che COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) e COV (Y, Z) = COV (Z, Y). Un punto da ricordare su questa matrice è il risultato della matrice di covarianza NXN per i dati di n-dimensione.

Come utilizzare una matrice di covarianza in Excel?

La matrice di covarianza viene utilizzata in varie applicazioni tra cui

  • Analizzando come due vettori si differenziano tra loro
  • Utilizzato nell'apprendimento automatico per determinare i modelli di dipendenza tra i due vettori
  • La matrice di covarianza viene utilizzata per raccontare la relazione tra le diverse dimensioni delle variabili casuali
  • Utilizzato nella modellazione stocastica in ingegneria finanziaria per correlare le variabili casuali
  • Il componente principale è un'altra applicazione della matrice di covarianza alle variabili originali alle variabili indipendenti lineari
  • Nell'analisi dei dati, la matrice di covarianza ha un ruolo fondamentale.
  • La matrice di covarianza è utilizzata nella moderna teoria del portafoglio nella stima dei rischi.
  • Le misure della matrice di covarianza vengono utilizzate per anticipare i rendimenti delle attività finanziarie

Esempi di matrice di covarianza in Excel

Di seguito sono riportati alcuni esempi per utilizzare la matrice di covarianza in Excel.

Puoi scaricare questo modello Excel per matrice di covarianza qui - Modello Excel per matrice di covarianza

Esempio 1

Esecuzione di analisi di covarianza sui voti ottenuti dagli studenti in diverse materie.

Passaggio 1: i seguenti dati, inclusi i voti degli studenti in matematica, inglese e scienze, sono considerati come mostrato nella figura.

Passaggio 2: vai alla scheda "Dati" sulla barra multifunzione e trova il ToolPak "Analisi dati" nell'angolo destro.

Se ToolPak "Analisi dati" non è disponibile, seguire questi passaggi

Passaggio A: vai alla scheda "File" e quindi seleziona le "opzioni".

Si aprirà la seguente schermata.

Passaggio B: vai a Componenti aggiuntivi. Sotto l'opzione Gestisci assicurati che sia selezionato "Componenti aggiuntivi di Excel" e seleziona il pulsante "Vai" come mostrato nella figura.

Passaggio C: selezionare "Analysis-Tool Pak" e "Analysis-ToolPak VBA" come mostrato nello screenshot.

Dopo aver completato questi passaggi, il pacchetto dello strumento "Analisi dei dati" viene aggiunto alla scheda "Dati".

Passaggio 3: fare clic su Analisi dei dati. Si apre la finestra di dialogo "Analisi dei dati". Selezionare la "Covarianza" scorrendo verso l'alto e fare clic su "OK".

Visualizza la finestra di dialogo "Covarianza".

Passaggio 5:  selezionare l'intervallo di input inclusi i nomi dei soggetti, controllare le "etichette nella prima riga" e fornire l '"intervallo di output" nel foglio di lavoro esistente. E fare clic su "OK".

Passaggio 6:  otterremo l'output come segue:

La parte superiore della diagonale è vuota poiché la matrice di covarianza Excel è simmetrica rispetto alla diagonale.

Esempio n. 2

Calcolo della matrice di covarianza per determinare le varianze tra i rendimenti di diversi titoli in portafoglio.

Passaggio 1: per questo esempio, vengono considerati i seguenti dati, inclusi i rendimenti delle azioni.

Passaggio 2: apre la finestra di dialogo "Analisi dati" e seleziona "Covarianza" scorrendo verso l'alto e facendo clic su "OK".

Visualizza la finestra di dialogo "Covarianza".

Passaggio 3: selezionare l'intervallo di input comprese le intestazioni, controllare le "etichette nella prima riga" e fornire l '"intervallo di output" nel foglio di lavoro esistente. E fare clic su "OK".

Passaggio 4: otterremo l'output come segue:

La parte superiore della diagonale è vuota poiché la matrice di covarianza è simmetrica rispetto alla diagonale.

Esempio n. 3

Calcolo della matrice di covarianza per i prezzi delle azioni delle società corporate

Passaggio 1: per questo esempio, vengono considerati i seguenti dati, inclusi i prezzi delle azioni di diverse società.

Passaggio 2: apre la finestra di dialogo "Analisi dati", selezionare "Covarianza" scorrendo verso l'alto e fare clic su "OK".

Visualizza la finestra di dialogo "Covarianza".

Passaggio 3: selezionare l'intervallo di input comprese le intestazioni, controllare le "etichette nella prima riga" e fornire l '"intervallo di output" nel foglio di lavoro esistente e fare clic su "OK".

Passaggio 4: otterremo l'output come segue:

Cose da ricordare

  • Lo strumento di covarianza presentato da Excel presenta alcune limitazioni, tra cui la determinazione della sola formula delle varianze della popolazione, la creazione di una matrice con solo valori diagonali inferiori e la considerazione delle formule solo per una varianza.
  • Quando i valori di ritorno vengono modificati, non aggiorna automaticamente i valori della matrice.
  • La metà superiore della matrice viene visualizzata come vuota poiché è simmetrica e i valori dell'immagine speculare vengono visualizzati nella diagonale inferiore.