Průměr a směrodatná odchylka v excelu. Výpočet směrodatné odchylky v aplikaci Microsoft Excel

30.03.2024

Andrej Lipov

Zjednodušeně řečeno, směrodatná odchylka ukazuje, jak moc se cena nástroje v čase mění. To znamená, že čím vyšší je tento ukazatel, tím větší je volatilita nebo variabilita řady hodnot.

Směrodatná odchylka může a měla by být použita k analýze souborů hodnot, protože dva soubory se zdánlivě stejným průměrem se mohou ukázat jako zcela odlišné v rozložení hodnot.

Příklad

Vezměme si dvě řady čísel.

a) 1,2,3,4,5,6,7,8,9. Průměr - 5. Čl. odchylka = 2,7386

b) 20,1,7,1,15,-1,-20,4,18,5. Průměr - 5. Čl. odchylka = 12,2066

Pokud nemáte celou řadu čísel před očima, pak indikátor směrodatné odchylky ukazuje, že v případě „b“ jsou hodnoty mnohem více rozptýleny kolem jejich průměrné hodnoty.

Zhruba řečeno, v řadě „b“ je hodnota 5 plus minus 12 (v průměru) - není to přesné, ale prozrazuje význam.

Jak vypočítat směrodatnou odchylku

Pro výpočet směrodatné odchylky můžete použít vzorec vypůjčený z výpočtu směrodatné odchylky výnosů podílových fondů:

Zde N je počet množství,
DOHaverage - průměr všech hodnot,
Období DOH - hodnota N.

V Excelu se odpovídající funkce nazývá STANDARDEVAL (nebo STDEV v anglické verzi programu).

Pokyny krok za krokem jsou následující:

Vypočítejte průměr pro řadu čísel.
Pro každou hodnotu určete rozdíl mezi průměrem a touto hodnotou.
Vypočítejte součet druhých mocnin těchto rozdílů.
Výsledný součet vydělte počtem čísel v řadě.
Vezměte druhou odmocninu z čísla, které jste získali v posledním kroku.

Vaši přátelé budou tyto informace považovat za užitečné. Sdílejte s nimi!

Variační koeficient je porovnáním rozptylu dvou náhodných hodnot. Veličiny mají jednotky měření, což vede ke srovnatelnému výsledku. Tento koeficient je potřebný pro přípravu statistické analýzy.

S ním mohou investoři vypočítat rizikové ukazatele před investicí do vybraných aktiv. Je to užitečné, když vybraná aktiva mají různé výnosy a stupně rizika. Například jedno aktivum může mít vysoký příjem a vysokou míru rizika, zatímco jiné může mít naopak nízký příjem a odpovídající nižší míru rizika.

Výpočet směrodatné odchylky

Směrodatná odchylka je statistická hodnota. Výpočtem této hodnoty získá uživatel informaci o tom, jak moc se data odchylují tím či oním směrem vzhledem k průměrné hodnotě. Směrodatná odchylka v Excelu se počítá v několika krocích.

Připravte data: otevření stránky, kde budou probíhat výpočty. V našem případě se jedná o obrázek, ale může to být jakýkoli jiný soubor. Hlavní je shromáždit informace, které použijete v tabulce pro výpočet.

Zadejte data do libovolného tabulkového editoru (v našem případě Excel) a vyplňte buňky zleva doprava. Mělo by začít ze sloupce "A". Zadejte nadpisy do řádku nahoře a názvy do stejných sloupců, které se vztahují k nadpisům, pouze níže. Potom datum a data, která se mají vypočítat, vpravo od data.

Uložte tento dokument.

Nyní přejdeme k samotnému výpočtu. Vyberte buňku pomocí kurzoru po poslední níže zadané hodnotě.

Zadejte znak „=“ a zadejte níže uvedený vzorec. Rovnítko je povinné. Jinak program navrhovaná data nevypočítá. Vzorec se zadává bez mezer.

Nástroj zobrazí názvy několika vzorců. Vyberte " STANDARDNÍ ODCHYLKA" Toto je vzorec pro výpočet směrodatné odchylky. Existují dva typy výpočtu:

se vzorovým výpočtem;
s výpočtem na základě běžné populace.

Výběrem jednoho z nich určete rozsah dat. Celý zadaný vzorec bude vypadat takto: „=STDEV (B2: B5)“.

Poté klikněte na tlačítko „ Vstupte" Přijatá data se objeví v označené položce.

Výpočet aritmetického průměru

Vypočítá se, když uživatel potřebuje vytvořit sestavu, například o mzdách ve své firmě. To se provádí následovně:

bude jen vyberte rozsah a klikněte na tlačítko „Enter“. A buňka nyní zobrazí výsledek z výše pořízených dat.

Výpočet variačního koeficientu

Vzorec pro výpočet variačního koeficientu:

V= S/X, kde S je standardní odchylka a X je průměr.

Abyste mohli vypočítat variační koeficient v Excelu, musíte najít směrodatnou odchylku a aritmetický průměr. To znamená, že po dokončení prvních dvou výpočtů, které byly uvedeny výše, můžete přejít k práci na variačním koeficientu.

Chcete-li to provést, otevřete Excel, vyplňte dvě pole, kam byste měli zadat výsledná čísla směrodatné odchylky a průměrné hodnoty.

Nyní vyberte buňku, která je přidělena číslu pro výpočet variace. Otevřete kartu " Domov„pokud není otevřeno. Klikněte na nástroj " Číslo" Vyberte formát procent.

Přejděte do označené buňky a dvakrát na ni klikněte. Poté zadejte rovnítko a zvýrazněte položku, kde se zadává celková směrodatná odchylka. Poté klikněte na tlačítko „lomítko“ nebo „rozdělit“ na klávesnici (vypadá takto: „/“). Vyberte položku, kde se zadá aritmetický průměr, a klikněte na tlačítko „Enter“. Mělo by to vypadat takto:

A zde je výsledek po stisknutí „Enter“:

Pro výpočet variačního koeficientu můžete také použít online kalkulačky, například planetcalc.ru a allcalc.ru. Stačí zadat potřebná čísla a spustit výpočet, po kterém obdržíte potřebné informace.

Standardní odchylka

Směrodatná odchylka v Excelu se řeší pomocí dvou vzorců:

Jednoduše řečeno, je extrahován kořen rozptylu. Jak vypočítat rozptyl je diskutováno níže.

Směrodatná odchylka je synonymem směrodatné odchylky a počítá se také přesně. Buňka s výsledkem pod čísly, která je třeba vypočítat, je zvýrazněna. Je vložena jedna z funkcí znázorněných na obrázku výše. Klikne se na tlačítko “ Vstupte" Výsledek byl přijat.

Oscilační koeficient

Poměr variačního rozsahu k průměru se nazývá oscilační koeficient. V Excelu nejsou žádné hotové vzorce, takže je třeba sestavit několik funkcí do jedné.

Funkce, které je třeba dát dohromady, jsou vzorce průměru, maxima a minima. Tento koeficient se používá k porovnání souboru dat.

Disperze

Rozptyl je funkce, pomocí které charakterizovat šíření dat kolem matematického očekávání. Vypočítá se pomocí následující rovnice:

Proměnné nabývají následujících hodnot:

Excel má dvě funkce, které určují rozptyl:

Chcete-li provést výpočet, zvýrazní se buňka pod čísly, která je třeba vypočítat. Přejděte na kartu funkce vložení. Vyberte kategorii " Statistický" Vyberte jednu z funkcí z rozevíracího seznamu a klikněte na tlačítko „Enter“.

Maximum a minimum

Maximum a minimum jsou potřeba, aby se mezi velkým počtem čísel ručně nehledalo minimální nebo maximální číslo.

Chcete-li vypočítat maximum, vyberte celý rozsah požadovaná čísla v tabulce a samostatnou buňku, poté klikněte na „Σ“ nebo „ Autosum" V okně, které se objeví, vyberte „Maximum“ a stisknutím tlačítka „Enter“ získáte požadovanou hodnotu.

Uděláte totéž, abyste získali minimum. Stačí vybrat funkci „Minimum“.

Program Excel je vysoce ceněn profesionály i amatéry, protože s ním mohou pracovat uživatelé jakékoli úrovně dovedností. Například kdokoli s minimálními „komunikačními“ dovednostmi v Excelu může nakreslit jednoduchý graf, vytvořit slušnou desku atd.

Zároveň tento program dokonce umožňuje provádět různé typy výpočtů, například výpočty, ale to vyžaduje trochu jinou úroveň školení. Pokud jste se však s tímto programem teprve začali podrobně seznamovat a zajímá vás vše, co vám pomůže stát se pokročilejším uživatelem, je tento článek určen právě vám. Dnes vám řeknu, jaký je vzorec směrodatné odchylky v Excelu, proč je vůbec potřeba a přesně řečeno, kdy se používá. Jít!

co to je

Začněme teorií. Směrodatná odchylka se obvykle nazývá druhá odmocnina získaná z aritmetického průměru všech umocněných rozdílů mezi dostupnými veličinami a také jejich aritmetický průměr. Mimochodem, tato hodnota se obvykle nazývá řecké písmeno „sigma“. Směrodatná odchylka se vypočítá pomocí vzorce STANDARDEVAL, program to provede sám za uživatele.

Podstatou tohoto konceptu je identifikace míry variability nástroje, tedy je svým způsobem ukazatelem odvozeným z deskriptivní statistiky. Identifikuje změny ve volatilitě nástroje za určité časové období. Vzorce STDEV lze použít k odhadu směrodatné odchylky vzorku, přičemž se ignorují logické a textové hodnoty.

Vzorec

Vzorec, který je automaticky poskytnut v Excelu, pomáhá vypočítat směrodatnou odchylku v Excelu. Chcete-li jej najít, musíte v Excelu najít oddíl vzorce a poté vybrat oddíl s názvem STANDARDEVAL, takže je to velmi jednoduché.

Poté se před vámi objeví okno, ve kterém budete muset zadat data pro výpočet. Zejména je třeba do speciálních polí zadat dvě čísla, po kterých program sám vypočítá směrodatnou odchylku pro vzorek.

Matematické vzorce a výpočty jsou bezesporu poměrně složitou záležitostí a ne všichni uživatelé si s ní hned poradí. Pokud však zabrousíte trochu hlouběji a podíváte se na problematiku trochu podrobněji, ukáže se, že ne všechno je tak smutné. Doufám, že jste se o tom přesvědčili na příkladu výpočtu směrodatné odchylky.

Video na pomoc

Statistika používá obrovské množství ukazatelů a jedním z nich je výpočet rozptylu v Excelu. Pokud to uděláte sami ručně, zabere to spoustu času a můžete udělat spoustu chyb. Dnes se podíváme na to, jak rozložit matematické vzorce na jednoduché funkce. Podívejme se na některé z nejjednodušších, nejrychlejších a nejpohodlnějších metod výpočtu, které vám umožní udělat vše během několika minut.

Vypočítejte rozptyl

Rozptyl náhodné veličiny je matematické očekávání druhé mocniny odchylky náhodné veličiny od jejího matematického očekávání.

Počítáme na základě běžné populace

Pro výpočet mat. Čekání, až program použije funkci DISP.G a její syntaxe vypadá takto: „=DISP.G(Číslo1;Číslo2;…)“.

Lze použít maximálně 255 argumentů, ne více. Argumenty mohou být prvočísla nebo odkazy na buňky, ve kterých jsou specifikovány. Podívejme se, jak vypočítat rozptyl v aplikaci Microsoft Excel:

1. Prvním krokem je vybrat buňku, kde se zobrazí výsledek výpočtu, a poté kliknout na tlačítko „Vložit funkci“.

2. Otevře se shell pro správu funkcí. Zde musíte hledat funkci „DISP.G“, která může být v kategorii „Statistické“ nebo „Úplný abecední seznam“. Když je nalezen, vyberte jej a klikněte na „OK“.

3. Otevře se okno s argumenty funkce. V něm musíte vybrat řádek „Číslo 1“ a na listu vybrat rozsah buněk s číselnou řadou.

4. Poté se výsledky výpočtu zobrazí v buňce, kde byla funkce zadána.

Takto můžete snadno najít odchylku v Excelu.

Provádíme výpočty na základě vzorku

V tomto případě se vzorový rozptyl v Excelu vypočítá se jmenovatelem, který neoznačuje celkový počet čísel, ale o jedno méně. To se provádí pro menší chybu pomocí speciální funkce DISP.V, jejíž syntaxe je =DISP.V(Číslo1;Číslo2;...). Algoritmus akcí:

Stejně jako v předchozí metodě je třeba vybrat buňku pro výsledek.
V Průvodci funkcí byste měli najít „DISP.B“ v kategorii „Úplný abecední seznam“ nebo „Statistické“.

Dále se objeví okno a měli byste postupovat stejným způsobem jako v předchozí metodě.

Video: Výpočet rozptylu v Excelu

Závěr

Rozptyl v Excelu se počítá velmi jednoduše, mnohem rychleji a pohodlněji než ručně, protože funkce matematického očekávání je poměrně složitá a její výpočet může zabrat spoustu času a úsilí.

Pojďme počítat vSLEČNAVYNIKATvýběrový rozptyl a směrodatná odchylka. Vypočítáme také rozptyl náhodné veličiny, pokud je známo její rozdělení.

Nejprve uvažujme disperze, pak standardní odchylka.

Ukázkový rozptyl

Ukázkový rozptyl (vzorový rozptyl,vzorekrozptyl) charakterizuje rozložení hodnot v poli vzhledem k .

Všechny 3 vzorce jsou matematicky ekvivalentní.

Z prvního vzorce je jasné, že rozptyl vzorku je součet čtverců odchylek každé hodnoty v poli z průměru, děleno velikostí vzorku mínus 1.

odchylky Vzorky je použita funkce DISP(), angl. název VAR, tzn. VARiance. Od verze MS EXCEL 2010 se doporučuje používat jeho analog DISP.V(), anglicky. název VARS, tzn. Ukázka VARiance. Od verze MS EXCEL 2010 je navíc k dispozici funkce DISP.Г(), anglicky. název VARP, tzn. Populační VARiance, která počítá disperze Pro populace. Celý rozdíl spočívá ve jmenovateli: místo n-1 jako DISP.V() má DISP.G() ve jmenovateli právě n. Před MS EXCEL 2010 byla k výpočtu rozptylu základního souboru použita funkce VAR().

Ukázkový rozptyl
=QUADROTCL(vzorek)/(POCET(vzorek)-1)
=(SOUČET(Ukázka)-POČET(Ukázka)*PRŮMĚR (Ukázka)^2)/ (POČET (Vzorek)-1)– obvyklý vzorec
=SUM((Vzorek -PRŮMĚR(Ukázka))^2)/ (POČET(Vzor)-1) –

Ukázkový rozptyl se rovná 0, pouze pokud jsou všechny hodnoty navzájem stejné, a tedy rovny průměrná hodnota. Obvykle tím větší hodnota odchylky, tím větší je rozptyl hodnot v poli.

Ukázkový rozptyl je bodový odhad odchylky rozdělení náhodné veličiny, ze které byla vytvořena vzorek. O stavbě intervaly spolehlivosti při posuzování odchylky si můžete přečíst v článku.

Rozptyl náhodné veličiny

Vypočítat disperze náhodná veličina, musíte ji znát.

Pro odchylky náhodná proměnná X se často označuje Var(X). Disperze rovná se druhé mocnině odchylky od průměru E(X): Var(X)=E[(X-E(X)) 2 ]

disperze vypočítá se podle vzorce:

kde x i je hodnota, kterou může mít náhodná proměnná, a μ je průměrná hodnota (), p(x) je pravděpodobnost, že náhodná proměnná nabude hodnoty x.

Pokud má náhodná proměnná , pak disperze vypočítá se podle vzorce:

Dimenze odchylky odpovídá druhé mocnině měrné jednotky původních hodnot. Pokud například hodnoty ve vzorku představují měření hmotnosti dílu (v kg), pak by rozměr rozptylu byl kg 2 . To může být obtížné interpretovat, takže pro charakterizaci šíření hodnot je to hodnota rovna odmocnině z odchylky – standardní odchylka.

Některé vlastnosti odchylky:

Var(X+a)=Var(X), kde X je náhodná proměnná a a je konstanta.

Var(aХ)=a 2 Var(X)

Var(X)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)- 2*E(X)*E(X)+(E(X))2 =E(X2)-(E(X))2

Tato disperzní vlastnost se využívá v článek o lineární regresi.

Var(X+Y)=Var(X) + Var(Y) + 2*Cov(X;Y), kde X a Y jsou náhodné proměnné, Cov(X;Y) je kovariance těchto náhodných proměnných.

Pokud jsou náhodné proměnné nezávislé, pak jsou kovariance se rovná 0, a proto Var(X+Y)=Var(X)+Var(Y). Tato vlastnost disperze se využívá při derivaci.

Ukažme, že pro nezávislé veličiny Var(X-Y)=Var(X+Y). Opravdu, Var(X-Y)= Var(X-Y)= Var(X+(-Y))= Var(X)+Var(-Y)= Var(X)+Var(-Y)= Var( X)+(- 1) 2 Var(Y)= Var(X)+Var(Y)= Var(X+Y). Tato vlastnost disperze se používá ke konstrukci .

Vzorová směrodatná odchylka

Vzorová směrodatná odchylka je mírou toho, jak široce rozptýlené jsou hodnoty ve vzorku vzhledem k jejich .

A-priory, standardní odchylka rovná se druhé odmocnině z odchylky:

Standardní odchylka nebere v úvahu velikost hodnot v vzorek, ale pouze stupeň rozptylu hodnot kolem nich průměrný. Abychom to ilustrovali, uveďme příklad.

Vypočítejme směrodatnou odchylku pro 2 vzorky: (1; 5; 9) a (1001; 1005; 1009). V obou případech s=4. Je zřejmé, že poměr směrodatné odchylky k hodnotám pole se mezi vzorky výrazně liší. Pro takové případy se používá Variační koeficient(Variační koeficient, CV) - poměr Standardní odchylka k průměru aritmetický, vyjádřeno v procentech.

V MS EXCEL 2007 a starších verzích pro výpočet Vzorová směrodatná odchylka je použita funkce =STDEVAL(), angl. název STDEV, tzn. Standardní odchylka. Od verze MS EXCEL 2010 se doporučuje používat jeho analog =STANDDEV.B() , angličtina. název STDEV.S, tzn. Ukázka standardní odchylky.

Navíc od verze MS EXCEL 2010 existuje funkce STANDARDEV.G(), angličtina. název STDEV.P, tzn. Standardní odchylka populace, která počítá standardní odchylka Pro populace. Celý rozdíl spočívá ve jmenovateli: místo n-1 jako v STANDARDEV.V() má STANDARDEVAL.G() ve jmenovateli právě n.

Standardní odchylka lze také vypočítat přímo pomocí níže uvedených vzorců (viz soubor s příkladem)
=ROOT(QUADROTCL(Ukázka)/(POČET(Ukázka)-1))
=KOŘEN((SOUČET(Ukázka)-POČET(Ukázka)*PRŮMĚR (Ukázka)^2)/(POČET (Vzorek)-1))

Další míry rozptylu

Funkce SQUADROTCL() počítá s součet čtverců odchylek hodnot od jejich průměrný. Tato funkce vrátí stejný výsledek jako vzorec =DISP.G( Vzorek)*ŠEK( Vzorek), kde Vzorek- odkaz na rozsah obsahující pole vzorových hodnot (). Výpočty ve funkci QUADROCL() se provádějí podle vzorce:

Funkce SROTCL() je také mírou šíření datové sady. Funkce SROTCL() vypočítá průměr absolutních hodnot odchylek hodnot od průměrný. Tato funkce vrátí stejný výsledek jako vzorec =SOUČETNÍ PRODUKT(ABS(Vzorek-PRŮMĚR (Ukázka)))/POČET (Vzorek), Kde Vzorek- odkaz na rozsah obsahující pole vzorových hodnot.

Výpočty ve funkci SROTCL () se provádějí podle vzorce: