Rozptyl je míra rozptylu, která popisuje srovnávací odchylku mezi hodnotami dat a průměrem. Je to nejpoužívanější míra rozptylu ve statistice, vypočítává se sečtením a umocněním odchylky každé hodnoty dat od průměru. Vzorec pro výpočet rozptylu je uveden níže:
s 2 – výběrový rozptyl;
x av — střední hodnota vzorku;
n — velikost vzorku (počet hodnot dat),
(x i – x avg) je odchylka od průměrné hodnoty pro každou hodnotu souboru dat.
Pro lepší pochopení vzorce se podívejme na příklad. Vaření mě moc nebaví, takže to dělám jen zřídka. Abych však neumřel hlady, musím čas od času zajít ke sporáku, abych realizoval plán nasycení těla bílkovinami, tuky a sacharidy. Níže uvedená data ukazují, kolikrát Renat každý měsíc vaří:
Prvním krokem při výpočtu rozptylu je stanovení výběrového průměru, který je v našem příkladu 7,8krát za měsíc. Zbytek výpočtů si můžete usnadnit pomocí následující tabulky.
Závěrečná fáze výpočtu rozptylu vypadá takto:
Pro ty, kteří rádi provádějí všechny výpočty najednou, by rovnice vypadala takto:
Použití metody surového počtu (příklad vaření)
Je jich víc účinná metoda výpočet rozptylu, známý jako metoda „raw counting“. I když se rovnice může na první pohled zdát značně těžkopádná, ve skutečnosti není tak děsivá. Můžete se o tom ujistit a pak se rozhodnout, která metoda se vám nejvíce líbí.
je součet každé hodnoty dat po umocnění,
je druhá mocnina součtu všech datových hodnot.
Neztrácejte rozum hned teď. Uveďme to vše do tabulky a uvidíte, že zde je méně výpočtů než v předchozím příkladu.
Jak vidíte, výsledek byl stejný jako při použití předchozí metody. Výhody tato metoda se projeví, když se velikost vzorku (n) zvětší.
Výpočet odchylky v Excelu
Jak jste již pravděpodobně uhodli, Excel má vzorec, který vám umožňuje vypočítat rozptyl. Navíc počínaje Excelem 2010 můžete najít 4 typy vzorců rozptylu:
1) VARIANCE.V – Vrací rozptyl vzorku. Booleovské hodnoty a text jsou ignorovány.
2) DISP.G - Vrátí rozptyl populace. Booleovské hodnoty a text jsou ignorovány.
3) VARIANCE - Vrací rozptyl vzorku, přičemž bere v úvahu booleovské a textové hodnoty.
4) VARIANCE - Vrací rozptyl základního souboru s přihlédnutím k logickým a textovým hodnotám.
Nejprve pochopíme rozdíl mezi vzorkem a populací. Účelem popisné statistiky je shrnout nebo zobrazit data, abyste rychle získali celkový obrázek, abych tak řekl, přehled. Statistická inference vám umožňuje dělat závěry o populaci na základě vzorku dat z této populace. Totalita představuje vše možné výsledky nebo měření, která nás zajímají. Vzorek je podmnožinou populace.
Například nás zajímá celá skupina studentů z jednoho z nich ruské univerzity a musíme určit průměrné skóre skupiny. Můžeme vypočítat průměrný výkon studentů a výsledný údaj bude parametrem, protože do našich výpočtů bude zapojena celá populace. Pokud však chceme vypočítat GPA všech studentů u nás, pak tato skupina bude naším vzorkem.
Rozdíl ve vzorci pro výpočet rozptylu mezi vzorkem a souborem je jmenovatel. Kde pro vzorek bude roven (n-1) a pro obecnou populaci pouze n.
Nyní se podíváme na funkce pro výpočet rozptylu s koncovkami A, v jehož popisu je řečeno, že výpočet bere v úvahu text a booleovské hodnoty. V v tomto případě při výpočtu rozptylu určitého pole dat, kde nejsou číselné hodnoty Excel bude interpretovat text a falešné booleovské hodnoty jako rovné 0 a skutečné booleovské hodnoty jako rovné 1.
Pokud tedy máte datové pole, nebude výpočet jeho rozptylu obtížný pomocí jedné z výše uvedených funkcí Excelu.
Dobré odpoledne
V tomto článku jsem se rozhodl podívat na to, jak funguje směrodatná odchylka v Excelu pomocí funkce STANDARDEVAL. Jen jsem to hodně dlouho nepopisoval a nekomentoval a také jednoduše proto, že je to velmi užitečná funkce pro ty, kteří studují vyšší matematiku. A pomáhat studentům je svaté, z vlastní zkušenosti vím, jak těžké je to zvládnout. Ve skutečnosti lze pomocí funkcí směrodatné odchylky určovat stabilitu prodávaných produktů, vytvářet ceny, upravovat nebo tvořit sortiment a tak dále. užitečné analýzy vaše prodeje.
Excel používá několik variant této variační funkce:
Matematická teorie
Nejprve něco málo o teorii jak matematický jazyk můžete popsat funkci standardní odchylka pro použití v Excelu, pro analýzu například dat statistik prodeje, ale o tom později. Hned upozorňuji, napíšu spoustu nesrozumitelných slov...)))), pokud cokoliv níže v textu, hledejte ihned praktické uplatnění v programu.
Co přesně dělá směrodatná odchylka? Vytváří odhad směrodatné odchylky náhodná proměnná X vzhledem k ní matematické očekávání na základě nezkresleného odhadu jeho rozptylu. Souhlas, zní to zmateně, ale myslím, že studenti pochopí, o čem vlastně mluvíme!
Nejprve musíme určit „směrodatnou odchylku“, abychom následně „směrodatnou odchylku“ vypočítali, pomůže nám s tím vzorec: 
Vzorec lze popsat následovně: bude měřen ve stejných jednotkách jako měření náhodné veličiny a používá se při výpočtu standardní aritmetické střední chyby při konstrukci intervaly spolehlivosti, při testování hypotéz pro statistiku nebo při analýze lineárního vztahu mezi nezávislé veličiny. Funkce je definována jako Odmocnina z rozptylu nezávislých proměnných.
Nyní můžeme definovat a standardní odchylka je analýza směrodatné odchylky náhodné veličiny X vzhledem k její matematické perspektivě na základě nezkresleného odhadu její rozptylu. Vzorec je napsán takto: 
Podotýkám, že všechny dva odhady jsou zkreslené. Na obecné případy Není možné vytvořit nezkreslený odhad. Ale odhad založený na odhadu nezaujatého rozptylu bude konzistentní.
Praktická implementace v Excelu
No, nyní se vzdálíme od nudné teorie a podívejme se v praxi, jak funguje funkce STANDARDEVAL. Nebudu uvažovat všechny varianty funkce směrodatné odchylky v Excelu, stačí jedna, ale v příkladech. Podívejme se například, jak se určují statistiky stability prodeje.
Nejprve se podívejte na pravopis funkce, a jak vidíte, je to velmi jednoduché:
STANDARDNÍ ODCHYLKA.Г(_číslo1_;_číslo2_; ….), kde:
Nyní si vytvoříme ukázkový soubor a na jeho základě uvažujme, jak tato funkce funguje. 
Vzhledem k tomu, že pro provádění analytických výpočtů je nutné použít alespoň tři hodnoty, jako v zásadě v jakékoli statistické analýze, vzal jsem podmíněně 3 období, může to být rok, čtvrtletí, měsíc nebo týden. V mém případě - měsíc. Pro maximální spolehlivost doporučuji brát co nejvíce období, ale ne méně než tři. Všechny údaje v tabulce jsou velmi jednoduché pro přehlednost ovládání a funkčnosti vzorce.
Nejprve musíme vypočítat průměrnou hodnotu za měsíc. Použijeme k tomu funkci AVERAGE a dostaneme vzorec: = AVERAGE(C4:E4). 
Nyní vlastně pomocí funkce STANDARDEVAL.G zjistíme směrodatnou odchylku, do jejíž hodnoty potřebujeme zadat tržby produktu za každé období. Výsledkem bude vzorec v následujícím tvaru: =STANDARDNÍ ODCHYLKA.Г(C4;D4;E4). 
No, polovina práce je hotová. Další krok vytvoříme „Variaci“, ta se získá vydělením průměrnou hodnotou, směrodatnou odchylkou a převedením výsledku na procenta. Dostaneme následující tabulku: 
No a základní výpočty jsou hotové, zbývá jen zjistit, zda jsou prodeje stabilní nebo ne. Berme jako podmínku, že odchylky 10 % jsou považovány za stabilní, od 10 do 25 % jsou to malé odchylky, ale cokoliv nad 25 % již stabilní není. Pro získání výsledku podle podmínek použijeme logickou jedničku a pro získání výsledku napíšeme vzorec:
IF(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))
Všechny rozsahy jsou brány pro přehlednost, vaše úkoly mohou mít úplně jiné podmínky. 
Chcete-li zlepšit vizualizaci dat, když má váš stůl tisíce pozic, měli byste využít příležitosti použít určité podmínky, které potřebujete nebo používáte ke zvýraznění určitých možností pomocí barevného schématu, bude to velmi jasné.
Nejprve vyberte ty, u kterých použijete podmíněné formátování. V ovládacím panelu „Domů“ vyberte „Podmíněné formátování“ a v rozevírací nabídce vyberte „Pravidla pro zvýraznění buněk“ a poté klikněte na položku nabídky „Text obsahuje...“. Zobrazí se dialogové okno, ve kterém zadáte své podmínky.
Poté, co si zapíšete podmínky, například „stabilní“ - zelená, „normální“ - žlutá a „nestabilní“ - červená, dostaneme krásnou a srozumitelnou tabulku, ve které můžete vidět, na co si dát pozor jako první.
Použití VBA pro funkci STDEV.Y
Každý, kdo má zájem, může své výpočty automatizovat pomocí maker a použít následující funkci:
Funkce MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Pro každé x In Arr aSum = aSum + x "vypočítejte součet prvků pole aCnt = aCnt + 1 "vypočítejte počet prvků Další x aAver = aSum / aCnt "průměrná hodnota pro každé x In Arr tmp = tmp + (x - aAver) ^ 2 "vypočítejte součet druhých mocnin rozdílu mezi prvky pole a průměrnou hodnotou Další x MyStDevP = Sqr(tmp / aCnt ) "vypočítat STANDARDEV.G() End Function
Funkce MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# Pro každé x In Arr aSum = aSum + x "vypočítejte součet prvků pole |
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 výběrový rozptyl v Excelu vypočítá se jmenovatelem, který neoznačuje celkový počet čísel, ale o jedno méně. To se pro menší chybu provádí 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 zobrazí 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í.
Z mnoha ukazatelů, které se ve statistice používají, je třeba vyzdvihnout výpočet rozptylu. Je třeba poznamenat, že ruční provádění tohoto výpočtu je poměrně zdlouhavý úkol. Naštěstí má Excel funkce, které umožňují automatizovat postup výpočtu. Pojďme zjistit algoritmus pro práci s těmito nástroji.
Rozptyl je indikátor variace, což je průměrná čtverec odchylek od matematického očekávání. Vyjadřuje tedy rozptyl čísel kolem průměrné hodnoty. Výpočet rozptylu lze provést jak pro obecnou populaci, tak pro vzorek.
Metoda 1: výpočet na základě populace
K výpočtu tohoto ukazatele v Excelu pro obecnou populaci použijte funkci DISP.G. Syntaxe tohoto výrazu je následující:
DISP.G(Číslo1;Číslo2;…)
Celkem lze použít 1 až 255 argumentů. Argumenty mohou být buď číselné hodnoty, nebo odkazy na buňky, ve kterých jsou obsaženy.
Podívejme se, jak vypočítat tuto hodnotu pro rozsah s číselnými údaji.
Metoda 2: výpočet podle vzorku
Na rozdíl od výpočtu hodnoty na základě populace, při výpočtu vzorku neudává jmenovatel celkový počet čísel, ale o jedno méně. To se provádí za účelem opravy chyb. Excel bere tuto nuanci v úvahu ve speciální funkci, která je určena pro tento typ výpočtu - DISP.V. Jeho syntaxe je reprezentována následujícím vzorcem:
DISP.B(Číslo1;Číslo2;…)
Počet argumentů, stejně jako v předchozí funkci, může být také v rozsahu od 1 do 255.
Jak vidíte, program Excel může výpočet rozptylu značně usnadnit. Tuto statistiku může aplikace vypočítat buď z populace, nebo ze vzorku. V tomto případě se všechny akce uživatele ve skutečnosti snižují na specifikaci rozsahu čísel, která mají být zpracována, a Excel dělá hlavní práci sám. To samozřejmě ušetří značné množství času uživatele.
Funkce směrodatné odchylky je již z kategorie vyšší matematiky související se statistikou. Existuje několik možností použití funkce standardní odchylka v aplikaci Excel:
- funkce STANDARDEV.
- Funkce STANDARDNÍ ODCHYLKA.
- Funkce STDEV
Tyto funkce budeme potřebovat ve statistice prodeje pro identifikaci stability prodeje (XYZ analýza). Tato data lze využít jak pro cenotvorbu, tak pro tvorbu (úpravu) matice sortimentu a pro další užitečné rozbory prodeje, o kterých budu určitě mluvit v dalších článcích.
Předmluva
Podívejme se na vzorce nejprve v matematickém jazyce a poté (níže v textu) podrobně rozebereme vzorec v Excelu a jak je výsledný výsledek použit při analýze prodejních statistik.
Standardní odchylka je tedy odhad standardní odchylky náhodné veličiny X ohledně jeho matematického očekávání na základě nezkresleného odhadu jeho rozptylu)))) Nebojte se nesrozumitelných slov, buďte trpěliví a všemu porozumíte!
Popis vzorce: Směrodatná odchylka se měří v jednotkách měření samotné náhodné veličiny a používá se při výpočtu směrodatné chyby aritmetického průměru, při konstrukci intervalů spolehlivosti, při statistickém testování hypotéz, při měření lineárního vztahu mezi náhodnými veličinami . Definováno jako druhá odmocnina rozptylu náhodné veličiny
Nyní je standardní odchylka odhadem standardní odchylky náhodné veličiny X vzhledem k jeho matematickému očekávání založenému na nezkresleném odhadu jeho rozptylu:
disperze;
- i prvek výběru;
Velikost vzorku;
Aritmetický průměr vzorku:
Je třeba poznamenat, že oba odhady jsou zkreslené. V obecném případě je nemožné vytvořit nezkreslený odhad. Odhad založený na nestranném odhadu rozptylu je však konzistentní.
Pravidlo tři sigma() - téměř všechny hodnoty normálně distribuované náhodné proměnné leží v intervalu. Přesněji, s pravděpodobností přibližně 0,9973 leží hodnota normálně rozdělené náhodné veličiny ve stanoveném intervalu (za předpokladu, že hodnota je pravdivá a není získána jako výsledek zpracování vzorku). Použijeme zaokrouhlený interval 0,1
Pokud je skutečná hodnota neznámá, měli byste použít ne, ale s. Tím se pravidlo tří sigma transformuje na pravidlo tří s. Právě toto pravidlo nám pomůže určit stabilitu prodejů, ale o tom později...
Nyní funkce standardní odchylky v aplikaci Excel
Doufám, že jsem vás matematikou moc nenudil? Možná někdo bude potřebovat tyto informace pro esej nebo pro jiné účely. Nyní se podívejme, jak tyto vzorce fungují v Excelu...
Abychom zjistili stabilitu prodeje, nemusíme se pouštět do všech možností funkcí směrodatné odchylky. Použijeme pouze jeden:
Funkce STDEV
STDEV(číslo 1;číslo 2;... )
Číslo1,číslo2,..- od 1 do 30 číselných argumentů odpovídajících běžné populaci.
Nyní se podívejme na příklad:
Vytvořme knihu a provizorní stůl. Tento příklad si stáhnete v Excelu na konci článku.
Pokračování příště!!!
Ahoj znovu. Studna!? Měl jsem volnou minutu. Pokračujme?
A tak stabilita prodeje s pomocí Funkce STDEV
Pro přehlednost si vezměme několik improvizovaných věcí:
V analytice, ať už se jedná o předpověď, výzkum nebo cokoli jiného souvisejícího se statistikou, je vždy nutné vzít tři období. Může to být týden, měsíc, čtvrtletí nebo rok. Je možné a dokonce nejlepší vzít si co nejvíce menstruací, ale ne méně než tři.
Konkrétně jsem ukázal přehnané prodeje, kdy pouhým okem vidíte, co se konzistentně prodává a co ne. To usnadní pochopení toho, jak vzorce fungují.
A tak máme tržby, nyní musíme vypočítat průměrné hodnoty prodeje podle období.
Vzorec pro průměrnou hodnotu je AVERAGE (údaje za období), v mém případě vzorec vypadá takto = PRŮMĚR (C6: E6)
Vzorec aplikujeme na všechny produkty. To lze provést uchopením pravého rohu vybrané buňky a jejím přetažením na konec seznamu. Nebo umístěte kurzor na sloupec s produktem a stiskněte následující kombinace kláves:
Ctrl + Dolů přesune kurzor na začátek seznamu.
Ctrl + Right, kurzor se přesune na pravou stranu tabulky. Ještě jednou doprava a dostaneme se ke sloupci se vzorcem.
Nyní upneme
Ctrl + Shift a stiskněte nahoru. Tímto způsobem vybereme oblast, kam se bude vzorec kreslit.
A kombinace kláves Ctrl + D přetáhne funkci tam, kde ji potřebujeme.
Pamatujte si tyto kombinace, skutečně zvýší vaši rychlost v Excelu, zvláště když pracujete s velkými poli.
Další fáze, samotná funkce standardního odchodu, jak jsem již řekl, budeme používat pouze jednu STDEV
Funkci zapíšeme a do hodnot funkce nastavíme prodejní hodnoty každého období. Pokud máte tržby v tabulce za sebou, můžete použít rozsah, jako v mém vzorci =STDEV(C6:E6) nebo uvést požadované buňky oddělené středníky =STDEV(C6;D6;E6)
Nyní jsou všechny výpočty připraveny. Jak ale poznáte, co se trvale prodává a co ne? Položme konvenci XYZ, kde
X je stabilní
Y - s malými odchylkami
Z - není stabilní
K tomu používáme chybové intervaly. pokud dojde k výkyvům do 10 %, budeme předpokládat, že tržby jsou stabilní.
Pokud je mezi 10 a 25 procenty, bude to Y.
A pokud hodnota odchylky přesáhne 25 %, nejde o stabilitu.
Pro správné nastavení písmen pro každý produkt použijeme vzorec KDYŽ. Další informace o. V mé tabulce bude tato funkce vypadat takto:
IF(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))
V souladu s tím rozšíříme všechny vzorce pro všechna jména.
Pokusím se okamžitě odpovědět na otázku Proč intervaly 10% a 25%?
Ve skutečnosti mohou být intervaly různé, vše závisí na konkrétním úkolu. Konkrétně jsem vám ukázal přehnané prodejní hodnoty, kde je rozdíl viditelný okem. Je zřejmé, že produkt 1 se neprodává konzistentně, ale dynamika ukazuje nárůst prodeje. Tento produkt necháme být...
Ale tady je produkt 2, tam už je evidentní destabilizace. A naše výpočty ukazují Z, což nám říká, že prodeje nejsou stabilní. Produkt 3 a produkt 5 vykazují stabilní výkon, upozorňujeme, že odchylka je v rozmezí 10 %.
Tito. Produkt 5 se skóre 45, 46 a 45 vykazuje odchylku 1 %, což je stabilní číselná řada.
Produkt 2 s ukazateli 10, 50 a 5 však vykazuje odchylku 93 %, což NENÍ stabilní číselná řada.
Po všech výpočtech můžete dát filtr a odfiltrovat stabilitu, takže pokud se váš stůl skládá z několika tisíc položek, snadno poznáte, které jsou v prodeji nestálé nebo naopak stabilní.
„Y“ v mé tabulce nefungovalo, myslím, že pro přehlednost číselné řady je třeba jej přidat. Nakreslím produkt 6...
Víte, číselná řada 40, 50 a 30 ukazuje 20% variaci. Nezdá se, že by došlo k nějaké velké chybě, ale spread je stále významný...
A tak to shrnu:
10.50.5 - Z není stabilní. Variace více než 25 %
40,50,30 - Y můžete věnovat pozornost tomuto produktu a zlepšit jeho prodej. Odchylka menší než 25 %, ale více než 10 %
45,46,45 - X je stabilita, s tímto produktem zatím nemusíte nic dělat. Odchylka menší než 10 %
To je vše! Doufám, že jsem vše vysvětlil jasně, pokud ne, zeptejte se, co není jasné. A budu vám vděčný za každý komentář, ať už pochvalu nebo kritiku. Tak budu vědět, že mě čtete a že vás to, což je velmi DŮLEŽITÉ, zajímá. A podle toho se objeví nové lekce.
