seychellesartprojects.org

Covariance Matrix v aplikaci Excel

Kovarianční matice je čtvercová matice, která zobrazuje kovarianci mezi sloupci a rozptyl ve sloupcích. Excel prezentován s vestavěným nástrojem „Analýza dat“ k určení kovariance mezi různými soubory dat. Tento článek vysvětlí výpočet kovarianční matice v aplikaci Excel tím, že pokryje následující témata včetně

Vysvětlení

Kovariance je jedním z opatření používaných k pochopení toho, jak je proměnná spojena s jinou proměnnou. Následující vzorec se používá pro stanovení kovariance.

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

Kovarianční matice je čtvercová matice, která slouží k pochopení vztahů prezentovaných mezi různými proměnnými v datové sadě. Je snadné a užitečné ukázat kovarianci mezi dvěma nebo více proměnnými.

Kovariance bude mít kladné i záporné hodnoty. Kladná hodnota znamená, že dvě proměnné se budou snižovat nebo zvyšovat stejným směrem. Záporná hodnota znamená, že pokud jedna proměnná klesá, zvyšuje se další proměnná a existuje mezi nimi inverzní vztah. Kovarianční matice je znázorněna v následujícím formátu. Trojrozměrná kovarianční matice je zobrazena jako

Abychom vytvořili čtvercovou kovarianční matici 3 × 3, potřebujeme mít trojrozměrná data. Diagonální hodnoty matice představují odchylky proměnných X, Y a Z (tj. COV (X, X), COV (Y, Y) a COV (Z, Z)). Kovarianční matice je symetrická vzhledem k úhlopříčce. To naznačuje, že COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) a COV (Y, Z) = COV (Z, Y). Jedním bodem, který je třeba pamatovat na tuto matici, je výsledek z kovarianční matice NXN pro data n-dimenzionální.

Jak používat kovarianční matici v aplikaci Excel?

Kovarianční matice se používá v různých aplikacích včetně

• Analýza toho, jak se dva vektory navzájem odlišují
• Používá se ve strojovém učení k určení vzorců závislostí mezi dvěma vektory
• Kovarianční matice se používá při určování vztahu mezi různými dimenzemi náhodných proměnných
• Používá se při stochastickém modelování ve finančním inženýrství ke korelaci náhodných proměnných
• Principle Component je další aplikace kovarianční matice na původní proměnné na lineární nezávislé proměnné
• Při analýze dat má kovarianční matice zásadní roli.
• Kovarianční matice se využívá v moderní teorii portfolia při odhadu rizik.
• Míry kovarianční matice se používají při předvídání výnosů z finančních aktiv

Příklady matice kovariance v aplikaci Excel

Níže uvádíme několik příkladů použití kovarianční matice v aplikaci Excel.

Tuto šablonu Covariance Matrix Excel si můžete stáhnout zde - Šablona Covariance Matrix Excel

Příklad č. 1

Provádění kovarianční analýzy známek získaných studenty v různých předmětech.

Krok 1: Následující data včetně známek studentů z matematiky, angličtiny a přírodních věd jsou považována za znázorněná na obrázku.

Krok 2: Přejděte na kartu „Data“ na pásu karet a v pravém bočním rohu najděte balíček „Analýza dat“.

Pokud ToolPak „Analýza dat“ není k dispozici, postupujte podle těchto kroků

Krok A: Přejděte na kartu „Soubor“ a poté vyberte „možnosti“.

Otevře se následující obrazovka.

Krok B: Přejít na Doplňky. V části Možnost správy se ujistěte, že jsou vybrány doplňky aplikace Excel, a klikněte na tlačítko Přejít, jak je znázorněno na obrázku.

Krok C: Vyberte „Analysis-Tool Pak“ a „Analysis-ToolPak VBA“, jak je znázorněno na snímku obrazovky.

Po dokončení těchto kroků je na kartu „Data“ přidán nástroj „Analýza dat“.

Krok 3: Klikněte na Analýza dat. Tím se otevře dialogové okno „Analýza dat“. Vyberte „Kovariance“ posouváním nahoru a klikněte na „OK“.

Zobrazí se dialogové okno „Covariance“.

Krok 5:  Vyberte rozsah vstupu včetně názvů předmětů, zkontrolujte „štítky v prvním řádku“ a uveďte „výstupní rozsah“ v existujícím listu. A klikněte na „OK“.

Krok 6:  Získáme výstup následujícím způsobem -

Horní část úhlopříčky je prázdná, protože kovarianční matice aplikace Excel je symetrická k úhlopříčce.

Příklad č. 2

Výpočet kovarianční matice k určení odchylek mezi výnosy různých akcií portfolia.

Krok 1: V tomto příkladu se berou v úvahu následující data včetně výnosů akcií.

Krok 2: Otevře dialogové okno „Analýza dat“ a posouváním nahoru vyberte „Covariance“ a klikněte na „OK“.

Zobrazí se dialogové okno „Covariance“.

Krok 3: Vyberte rozsah vstupu včetně záhlaví, zkontrolujte „štítky v první řadě“ a uveďte „výstupní rozsah“ ve stávajícím listu. A klikněte na „OK“.

Krok 4: Získáme výstup následujícím způsobem -

Horní část úhlopříčky je prázdná, protože kovarianční matice je vůči úhlopříčce symetrická.

Příklad č. 3

Výpočet kovarianční matice pro ceny akcií firemních společností

Krok 1: V tomto příkladu jsou brány v úvahu následující údaje včetně cen akcií různých společností.

Krok 2: Otevře se dialogové okno „Analýza dat“, posouváním nahoru vyberte „Covariance“ a klikněte na „OK“.

Zobrazí se dialogové okno „Covariance“.

Krok 3: Vyberte rozsah vstupu včetně záhlaví, zkontrolujte „štítky v prvním řádku“ a uveďte „výstupní rozsah“ ve stávajícím listu a klikněte na „OK“.

Krok 4: Získáme výstup následujícím způsobem -

Věci k zapamatování

• Nástroj kovariance prezentovaný aplikací Excel má některá omezení, včetně stanovení vzorce pouze odchylek populace, vytvoření matice pouze s nižšími úhlopříčnými hodnotami a zvážení vzorců pouze pro rozptyl.
• Když se změní návratové hodnoty, neaktualizuje automaticky hodnoty matice.
• Horní polovina matice je zobrazena jako prázdná, protože je symetrická a hodnoty zrcadlového obrazu jsou zobrazeny ve spodní úhlopříčce.