Kovariansmatris i Excel

Kovariansmatrisen är en kvadratmatris som visar kovariansen mellan kolumnerna och variansen i kolumnerna. Excel presenterades med det inbyggda verktyget 'Dataanalys' för att bestämma kovariansen mellan de olika datamängderna. Denna artikel kommer att förklara beräkningen av kovariansmatris i Excel genom att täcka följande ämnen inklusive

Förklaring

Kovarians är en av de åtgärder som används för att förstå hur en variabel associeras med en annan variabel. Följande formel används för bestämning av kovarians.

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

Kovariansmatrisen är en kvadratmatris för att förstå förhållandena som presenteras mellan de olika variablerna i en dataset. Det är enkelt och användbart att visa kovariansen mellan två eller flera variabler.

Kovariansen kommer att ha både positiva och negativa värden. Ett positivt värde indikerar att två variabler minskar eller ökar i samma riktning. Ett negativt värde indikerar att om en variabel minskar ökar andra variabler och det finns ett omvänt förhållande mellan dem. Kovariansmatrisen representeras i följande format. Den tredimensionella kovariansmatrisen visas som

För att skapa den 3 × 3 kvadratiska kovariansmatrisen måste vi ha tredimensionell data. De diagonala värdena för matrisen representerar varianserna för X-, Y- och Z-variablerna (dvs COV (X, X), COV (Y, Y) och COV (Z, Z)). Kovariansmatrisen är symmetrisk med avseende på diagonal. Detta indikerar att COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) och COV (Y, Z) = COV (Z, Y). En punkt att komma ihåg om denna matris är resultatet från NXN-kovariansmatrisen för data av n-dimensionell.

Hur använder jag en kovariansmatris i Excel?

Kovariansmatrisen används i olika applikationer inklusive

Analyserar hur två vektorer skiljer sig från varandra
Används i maskininlärning för att bestämma beroendemönstret mellan de två vektorerna
Kovariansmatrisen används för att berätta förhållandet mellan de olika dimensionerna av slumpmässiga variabler
Används i stokastisk modellering inom ekonomiteknik för att korrelera slumpmässiga variabler
Princip Component är en annan tillämpning av kovariansmatris på originalvariabler på linjära oberoende variabler
I dataanalys har kovariansmatrisen en viktig roll.
Kovariansmatrisen används i modern portföljteori för att uppskatta risker.
Måtten i kovariansmatrisen används för att förutse avkastningen på de finansiella tillgångarna

Exempel på kovariansmatris i Excel

Nedan följer några av exemplen för att använda kovariansmatrisen i excel.

Du kan ladda ner denna Excel-mall för Covariance Matrix här - Covariance Matrix Excel-mall

Exempel nr 1

Utföra kovariansanalys på de poäng som eleverna fått i olika ämnen.

Steg 1: Följande data inklusive elevernas betyg i matematik, engelska och naturvetenskap betraktas som visas i figuren.

Steg 2: Gå till fliken "Data" på menyfliksområdet och hitta "Dataanalys" ToolPak till höger.

Om ”Dataanalys” ToolPak inte är tillgängligt följer du dessa steg

Steg A: Gå till fliken "File" och välj sedan "alternativ".

Följande skärm öppnas.

Steg B: Gå till tillägg. Se till att hantera alternativet "Excel-tillägg" är markerat och välj "Gå" -knappen som visas i figuren.

Steg C: Välj "Analysis-Tool Pak" och "Analysis-ToolPak VBA" som visas på skärmdumpen.

Efter att ha slutfört dessa steg läggs verktyget "Dataanalys" till på fliken "Data".

Steg 3: Klicka på dataanalys. Detta öppnar dialogrutan "Dataanalys". Välj "Kovarians" genom att bläddra uppåt och klicka på "OK".

Den visar dialogrutan "Kovarians".

Steg 5: Välj inmatningsområdet inklusive ämnesnamnen, kontrollera "etiketterna i första raden" och ange "utmatningsområdet" i det befintliga kalkylbladet. Och klicka på “OK”.

Steg 6: Vi får utdata enligt följande -

Den övre delen av diagonalen är tom eftersom excel-kovariansmatrisen är symmetrisk mot diagonalen.

Exempel 2

Beräkning av kovariansmatris för att bestämma avvikelser mellan olika portföljaktiers avkastning.

Steg 1: I det här exemplet beaktas följande data inklusive aktiereturer.

Steg 2: Öppnar dialogrutan "Dataanalys" och välj "Kovarians" genom att bläddra uppåt och klicka på "OK".

Den visar dialogrutan "Kovarians".

Steg 3: Välj inmatningsområdet inklusive rubriker, kontrollera "etiketterna i första raden" och ange "utmatningsområdet" i det befintliga kalkylbladet. Och klicka på “OK”.

Steg 4: Vi får utdata enligt följande -

Den övre delen av diagonalen är tom eftersom kovariansmatrisen är symmetrisk mot diagonalen.

Exempel # 3

Beräkning av kovariansmatris för aktiekurser i företagsföretag

Steg 1: För detta exempel beaktas följande data inklusive aktiekurser för olika företag.

Steg 2: Öppnar dialogrutan "Dataanalys", välj "Kovarians" genom att bläddra uppåt och klicka på "OK".

Den visar dialogrutan "Kovarians".

Steg 3: Välj inmatningsområdet inklusive rubriker, kontrollera "etiketterna i första raden" och ange "utmatningsområdet" i det befintliga kalkylbladet och klicka på "OK".

Steg 4: Vi får utdata enligt följande -

Saker att komma ihåg

Kovariansverktyget som presenteras av Excel har vissa begränsningar, inklusive bestämning av endast populationsvariansformel, skapande av matris med endast lägre diagonala värden och beaktande av formler endast för en varians.
När returvärden ändras uppdateras inte matrisens värden automatiskt.
Den övre halvan av matrisen visas som tom eftersom den är symmetrisk och spegelvärdet visas i den nedre diagonalen.