Různé statistické ukazatele, včetně rozptylu, lze vypočítat pomocí robustního nástroje Excel. Rozptyl nebo rozptýlení skupiny datových bodů kolem průměru se měří pomocí rozptylu. Nabízí užitečné informace o variabilitě dat. V tomto tutoriálu probereme, jak v aplikaci Excel zjistit rozptyl a směrodatnou odchylku a jak vypočítat hodnotu v riziku (VaR).
V závislosti na tom, zda pracujete s výběrovými daty nebo s daty populace, lze k určení rozptylu použít vestavěné funkce VAR.P nebo VAR.S aplikace Excel. Při výpočtu rozptylu používá funkce VAR.P celou populaci, zatímco funkce VAR.S používá vzorek populace. Rozsah dat je argument, který obě funkce přijímají.
1. Vyberte prázdnou buňku, ve které se má zobrazit výsledek. 2. Nahraďte „rozsah“ skutečným rozsahem dat, pro který chcete v buňce vypočítat rozptyl, zadáním vzorce „=VAR.P(rozsah)“. 3. Chcete-li získat výsledek, stiskněte klávesu Enter.
Protože směrodatná odchylka je druhou odmocninou rozptylu, jsou zjištění rozptylu a směrodatné odchylky v aplikaci Excel úzce propojené pojmy. Směrodatnou odchylku lze rychle určit odmocninou z rozptylu poté, co jste rozptyl určili pomocí funkce VAR.P nebo VAR.S.
Níže uvedené pokyny lze použít k výpočtu směrodatné odchylky v aplikaci Excel. 1. Rozhodněte, ve které volné buňce chcete výsledek zobrazit. 2. Před zadáním vzorce „=SQRT(variance)“ do buňky nahraďte slovo „variance“ odkazem na buňku obsahující výpočet odchylky. 3. Chcete-li získat výsledek, stiskněte klávesu Enter.
Hodnota v riziku (VaR) je statistická metrika používaná k předpovědi možné ztráty z investice v určitém časovém rámci. K výpočtu VaR lze použít funkci NORM.INV v aplikaci Excel, protože určuje inverzní hodnotu kumulativní distribuční funkce pro danou pravděpodobnost.
V aplikaci Excel proveďte následující kroky pro výpočet VaR: 1. Vyberte požadovanou úroveň spolehlivosti pro odhad VaR. Například 95% úroveň spolehlivosti znamená 5% pravděpodobnost, že ztráta překročí VaR.
2. Pomocí dříve popsaných technik určete směrodatnou odchylku vašich investičních výsledků.
3. Pro výpočet VaR použijte funkci NORM.INV spolu s požadovanou úrovní spolehlivosti a také střední hodnotou a směrodatnou odchylkou vašich výsledků. „Úroveň spolehlivosti“ bude zadána jako desetinné číslo mezi 0 a 1 a vzorec bude „=NORM.INV(1-úroveň spolehlivosti, průměr, směrodatná odchylka)“.
Použití aplikace Excel k vytvoření tabulky VaR vám může poskytnout důkladnou představu o potenciálních ztrátách při různých úrovních spolehlivosti. Funkci datové tabulky aplikace Excel lze použít k automatickému výpočtu VaR pro různé úrovně spolehlivosti. Excel automaticky vypočítá VaR pro každou úroveň spolehlivosti, pokud do sloupce zadáte různé stupně jistoty a do vedlejšího sloupce uvedete odkaz na algoritmus VaR.
Závěrem lze říci, že Excel poskytuje silné nástroje a funkce pro výpočet rozptylu, směrodatné odchylky a hodnoty v riziku. Rozptyl můžete rychle zjistit pomocí funkcí VAR.P nebo VAR.S. Kromě toho lze směrodatnou odchylku vypočítat výpočtem odmocniny z rozptylu. Zadanou úroveň spolehlivosti, průměr a směrodatnou odchylku lze využít pomocí funkce NORM.INV ke stanovení VaR. Tabulku VaR s různými úrovněmi spolehlivosti lze také vytvořit pomocí funkce Data Table aplikace Excel. Excel nabízí rychlý a efektivní přístup ke zkoumání a pochopení variability a rizika souvisejícího s daty nebo aktivy pomocí těchto nástrojů.
Ke stanovení směrodatné odchylky lze použít funkci STDEV aplikace Excel. Zde je uveden postup, jak na to: Za předpokladu, že jsou vaše data uspořádána do sloupce nebo řádku, vyberte prázdnou buňku, do které umístíte směrodatnou odchylku.
2. Bez použití uvozovek napište „=STDEV(„. 3. Vyberte sadu buněk, ve kterých jsou vaše data. 4. Ukončete závorky a stiskněte klávesu Enter. Excel zjistí a zobrazí směrodatnou odchylku vybraných dat.
V závislosti na tom, zda chcete vypočítat rozptyl pro populaci nebo vzorek, je vzorec pro rozptyl v aplikaci Excel „=VAR.P(rozsah)“ nebo „=VAR.S(rozsah)“.
Vzorec „=AVERAGE(range)“ aplikace Excel vypočítá průměrnou hodnotu v zadaném rozsahu dat.
V aplikaci Excel můžete použít následující vzorec pro výpočet rozptylu ze směrodatné odchylky: =STDEV.S(číslo)2
Použití tohoto vzorce je následující:
1. V aplikaci Excel zadejte hodnoty dat do sloupce. 2. Vyberte rozsah buněk obsahujících hodnoty dat pomocí funkce STDEV.S v prázdné buňce. Poté se určí směrodatná odchylka. Například =STDEV.S(A1:A10) 3. Pomocí exponenciálního operátoru () a čísla 2 odmocněte hodnotu směrodatné odchylky, abyste získali rozptyl. Příkladem je =STDEV.S(A1:A10)2.
Tento vzorec můžete použít k určení rozptylu souboru dat na základě stanovené směrodatné odchylky.
Pomocí aplikace Excel můžete určit variační koeficient (CV) provedením následujících kroků: V aplikaci Excel začněte nastavením dat do sloupce nebo řádku. Vypočítejte směrodatnou odchylku vydělenou průměrem pomocí rovnice „=STDEV.P(rozsah)/PRŮMĚR(rozsah)“. Nahraďte „rozsah“ sadou buněk, ve kterých jsou vaše data obsažena. Například výpočet by byl „=STDEV.P(A1:A10)/AVERAGE(A1:A10)“, pokud by se vaše data nacházela v buňkách A1 až A10. 3. Stisknutím klávesy Enter zadejte výsledek, kterým je variační koeficient pro vaše data.
Pokud použijete tento vzorec, Excel vypočítá CV pro váš rozsah dat automaticky.