Covariance Matrix v aplikaci Excel
Kovarianční matice je čtvercová matice, která zobrazuje kovarianci mezi sloupci a rozptyl ve sloupcích. Excel představil vestavěný nástroj „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 pokrytím následujících témat, 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 označuje, ž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). Jeden bod, který si musíte pamatovat o této 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 od sebe liší
- 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
- Principovou složkou 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.
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 vyberte „možnosti“.
Otevře se následující obrazovka.
Krok B: Přejít na Doplňky. V části Možnost správy zkontrolujte, zda je vybrána možnost „Doplňky aplikace Excel“, a vyberte 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ů se na kartu „Data“ přidá sada nástrojů „Analýza dat“.
Krok 3: Klikněte na Analýza dat. Tím se otevře dialogové okno „Analýza dat“. Posunutím nahoru vyberte „Covariance“ 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
Proveďte výpočet kovarianční matice a určete rozptyly mezi výnosy různých akcií portfolia.
Krok 1: V tomto příkladu se berou v úvahu následující data, včetně návratnosti 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“ v existují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“, posunutí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“ v existují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.
- Při změně návratových hodnot se automaticky neaktualizují hodnoty matice.
- Horní polovina matice se zobrazuje jako prázdná, protože je symetrická, a hodnoty zrcadlového obrazu se zobrazují ve spodní úhlopříčce.
