Standardní odchylka funkce Excel. Video na pomoc
Počítáme vSLEČNAVYNIKATrozptyl a směrodatná odchylka vzorku. Vypočítáme také rozptyl náhodné veličiny, pokud je známo její rozdělení.
Nejprve zvažte rozptyl, 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 to vidět rozptyl vzorku je součet čtverců odchylek každé hodnoty v poli z průměru děleno velikostí vzorku mínus 1.
rozptyl vzorkování je použita funkce DISP (). Název VAR, tzn. VARiance. Od verze MS EXCEL 2010 je doporučeno používat jeho analog DISP.B (), eng. název VARS, tzn. Ukázka VARiance. Navíc od verze MS EXCEL 2010 existuje funkce DISP.G (), angličtina. název VARP, tzn. Populační VARiance, která počítá rozptyl pro obecná populace... Celý rozdíl spočívá ve jmenovateli: místo n-1 jako v DISP.B () má DISP.G () ve jmenovateli právě n. Před MS EXCEL 2010 byla pro výpočet rozptylu obecné populace použita funkce VARP ().
Ukázkový rozptyl
= ČTVEREC (vzorek) / (POČET (vzorek) -1)
= (SOUČET (vzorek) -POČET (vzorek) * PRŮMĚR (vzorek) ^ 2) / (POČET (vzorek) -1)- obvyklý vzorec
= SUM ((Vzorek -HODNOTA (Vzor)) ^ 2) / (POČET (Vzorek) -1) –
Ukázkový rozptyl se rovná 0, pouze pokud jsou si všechny hodnoty navzájem rovny, a jsou tedy stejné průměrný... Obvykle tím větší hodnota rozptyl, tím větší je rozptyl hodnot v poli.
Ukázkový rozptyl je bodový odhad rozptyl rozdělení náhodné veličiny, ze které vzorek... O stavění intervaly spolehlivosti při hodnocení rozptyl si můžete přečíst v článku.
Rozptyl náhodné veličiny
Vypočítat rozptyl náhodná veličina, musíte ji znát.
Pro rozptyl náhodná proměnná X se často používá označení 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 náhodná proměnná nabývat, 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 rozptyl odpovídá druhé mocnině měrné jednotky původních hodnot. Pokud jsou například hodnoty ve vzorku měření hmotnosti součásti (v kg), pak bude rozměr rozptylu kg 2. To může být obtížné interpretovat, a proto charakterizovat šíření hodnot, hodnotu rovnající se druhé odmocnině rozptyl – standardní odchylka.
Některé vlastnosti rozptyl:
Var (X + a) = Var (X), kde X je náhodná proměnná a a je konstanta.
Var (aX) = a 2 Var (X)
Var (X) = E [(XE (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 (X 2) - (E (X)) 2
Tato vlastnost rozptylu se použí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é veličiny nezávislé, pak jejich kovariance se rovná 0, a proto Var (X + Y) = Var (X) + Var (Y). Tato vlastnost rozptylu se používá ve výstupu.
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 rozptylu se používá pro vykreslování.
Vzorová směrodatná odchylka
Vzorová směrodatná odchylka je mírou toho, jak široce jsou hodnoty ve vzorku rozptýleny vzhledem k jejich.
Podle definice, standardní odchylka rovná se druhé odmocnině z rozptyl:
Standardní odchylka nebere v úvahu velikost hodnot v vzorek, ale pouze stupeň rozptylu hodnot kolem nich střední... Zde je příklad, který to ilustruje.
Vypočítejme směrodatnou odchylku pro 2 vzorky: (1; 5; 9) a (1001; 1005; 1009). V obou případech je s = 4. Je zřejmé, že poměr směrodatné odchylky k hodnotám pole je u vzorků výrazně odlišný. Pro takové případy použijte Variační koeficient(Variační koeficient, CV) - poměr Standardní odchylka do středu aritmetický vyjádřeno v procentech.
V MS EXCEL 2007 a dřívějších pro výpočet Vzorová směrodatná odchylka funkce se používá = STDEV (), eng. název STDEV, tzn. Standardní odchylka. Od verze MS EXCEL 2010 se doporučuje používat jeho analog = STDEV.V (), eng. název STDEV.S, tzn. Ukázka standardní odchylky.
Od verze MS EXCEL 2010 je navíc k dispozici funkce STDEV.G (), eng. název STDEV.P, tzn. Standardní odchylka populace, která počítá standardní odchylka pro obecná populace... Veškerý rozdíl spočívá ve jmenovateli: namísto n-1 jako STDEV.V () má STDEV.G () ve jmenovateli pouze n.
Standardní odchylka lze také vypočítat přímo podle následujících vzorců (viz soubor s příkladem)
= KOŘEN (čtverec (vzorek) / (POČET (vzorek) -1))
= KOŘEN ((SOUČET (Ukázka) -POČET (Ukázka) * PRŮMĚR (Ukázka) ^ 2) / (POČET (Vzorek) -1))
Další opatření šíření
Funkce SQUARE () počítá s umma kvadrát odchylky hodnot od jejich střední... 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 SQUARE () se provádějí podle vzorce:
Funkce AVEDEV () je také mírou šíření množiny dat. Funkce AVEDV () vypočítá průměr absolutních hodnot odchylek hodnot od střední... Tato funkce vrátí stejný výsledek jako vzorec = SUMPRODUKT (ABS (vzorek-průměr (vzorek))) / POČET (vzorek), kde Vzorek- odkaz na rozsah obsahující pole vzorových hodnot.
Výpočty ve funkci AVEDV () se provádějí podle vzorce:
Jedním z hlavních nástrojů statistické analýzy je výpočet směrodatné odchylky. Tento indikátor umožňuje provést odhad směrodatné odchylky pro vzorek nebo pro obecnou populaci. Pojďme se naučit, jak používat vzorec směrodatné odchylky v Excelu.
Okamžitě určíme, co je směrodatná odchylka a jak vypadá její vzorec. Tato hodnota je druhou odmocninou aritmetického průměru druhých mocnin rozdílu všech hodnot řady a jejich aritmetického průměru. Tento ukazatel má shodný název – směrodatná odchylka. Obě jména jsou zcela ekvivalentní.
Ale v Excelu to uživatel samozřejmě nemusí počítat, protože program dělá vše za něj. Pojďme zjistit, jak vypočítat směrodatnou odchylku v Excelu.
Výpočet v Excelu
Zadanou hodnotu můžete v Excelu vypočítat pomocí dvou speciálních funkcí STDEV.B.(pro vzorek) a STDEV.G(pro běžnou populaci). Princip jejich fungování je naprosto stejný, ale mohou být způsobeny třemi způsoby, o kterých si povíme níže.
Metoda 1: Průvodce funkcí
Metoda 2: karta "Vzorce".
Metoda 3: ruční zadání vzorce
Existuje také způsob, že okno s argumenty nemusíte vůbec vyvolávat. Chcete-li to provést, musíte vzorec zadat ručně.
Jak vidíte, mechanismus výpočtu směrodatné odchylky v Excelu je velmi jednoduchý. Uživatel potřebuje pouze zadat čísla z populace nebo odkazy na buňky, které je obsahují. Veškeré výpočty provádí program sám. Mnohem obtížnější je pochopit, co je vypočítaný ukazatel a jak lze výsledky výpočtu uplatnit v praxi. Ale pochopení toho už patří spíše do oblasti statistiky než k učení se pracovat se softwarem.
Střední kvadratická odchylka (nebo směrodatná odchylka) je druhá největší konstanta ve variační řadě. Je to míra rozmanitosti objektů zařazených do skupiny a ukazuje, jak moc průměrný možnosti se odchylují od aritmetického průměru studované populace. Čím více jsou možnosti rozptýleny kolem průměru, čím častěji jsou extrémní nebo jiné vzdálené třídy odchylek od průměru variační řady, tím větší je standardní odchylka. Směrodatná odchylka je mírou variability znaků v důsledku vlivu náhodných faktorů na ně. Směrodatná odchylka na druhou ( S²) se nazývá rozptyl .
Co je „náhodné“ při podrobném zkoumání? Ve vzorci modelu varianta, náhodná složka vystupuje jako jakýsi „přírůstek“ podílu variant, vzniklý pod vlivem systematických faktorů, ± x případ... ... To se zase skládá z účinků vlivu neurčitě velkého počtu faktorů: x případ . = Σ x případ k.
Každý z těchto faktorů může odhalit svůj silný účinek (významně přispět), nebo se jen stěží může podílet na utváření konkrétní varianty (slabý účinek, nevýznamný příspěvek). Navíc podíl náhodného "zvýšení" pro každou možnost je jiný! Když vezmeme v úvahu například velikost dafnie, je vidět, že jeden jedinec je větší, druhý menší, protože jeden se narodil o pár hodin dříve, druhý později, nebo jeden geneticky není úplně totožný s ostatními, třetina rostla v teplejší zóně akvária atd.
Pokud tyto konkrétní faktory nejsou zahrnuty do kontrolovaných při sběru varianty pak oni, individuálně se projevující v různé míře, poskytují náhodné varianta variace. Čím více náhodných faktorů, tím silnější jsou, tím více budou možnosti rozptýleny kolem průměru a čím větší je variační charakteristika, standardní odchylka. V kontextu naší knihy je termín „náhodný“ synonymem slova „neznámý“, „nekontrolovatelný“. Dokud nějak nevyjádříme intenzitu faktoru (seskupením, gradací, číslem), do té doby zůstane faktorem způsobujícím náhodnou variabilitu.
Význam směrodatné odchylky (varianty od průměru) vyjadřuje vzorec:
kde X - hodnotu atributu pro každý objekt ve skupině,
M - aritmetický průměr prvku,
P - počet vzorových možností.
Je pohodlnější provádět výpočty pomocí pracovní vzorec:
,
kde Σ X² - součet druhých mocnin charakteristických hodnot pro všechny varianty,
Σ X - součet charakteristických hodnot,
n je objem vzorku.
Pro příklad s tělesnou hmotností rejska bude směrodatná odchylka: S= 0,897216496 a po požadovaném zaokrouhlení S= 0,897 g.
V některých případech je nutné určit vážená směrodatná odchylka pro kumulativní rozdělení složené z několika vzorků, pro které jsou již známé standardní odchylky. Tento úkol je vyřešen pomocí vzorce:
,
kde SΣ je průměrná hodnota střední kvadratické odchylky pro celkové rozdělení,
S--- průměrné hodnoty směrodatné odchylky,
P - objemy jednotlivých vzorků,
k- počet zprůměrovaných směrodatných odchylek.
Podívejme se na příklad. Čtyři nezávislá stanovení hmotnosti jater (mg) u rejsků rejsků v červnu, červenci, srpnu a září poskytla následující standardní odchylky: 93, 83, 50, 71 (při n = 17, 115, 132, 140). Dosazením požadovaných hodnot do výše uvedeného vzorce získáme směrodatné odchylky pro celkový vzorek (za celé období bez sněhu):
V případě, že je vyžadováno primární statistické zpracování velkého počtu vzorků, ale ne nutně s vysokou přesností, můžete použít expresní metodou na základě znalosti zákona normálního rozdělení. Jak již bylo uvedeno, extrémní hodnoty pro vzorek (s pravděpodobností P= 95 %) lze považovat za hranice vzdálené od průměru ve vzdálenosti 2 S: X min = M - 2S, X max = M+ 2S... To znamená, že limit (Lim) v rozsahu od maximální do minimální vzorkované hodnoty odpovídá čtyřem standardním odchylkám:
Lim = (M+ 2S) − (M - 2S) = 4S.
Tento závěr však platí pouze pro velké vzorky, zatímco u malých vzorků je třeba provést úpravy. Pro přibližný výpočet směrodatné odchylky se doporučuje následující vzorec (Ashmarin et al., 1975):
,
kde je hodnota d převzato z tabulky 3 (oproti odpovídající velikosti vzorku, n).
Tabulka 3
Vzorová směrodatná odchylka tělesné hmotnosti rejsků ( n= 63), vypočtený podle výše uvedeného vzorce, je:
S= (11,9 - 7,3) / 4 = 1,15 g,
která se dostatečně blíží přesné hodnotě, S= 0,89 g.
Použití expresních odhadů směrodatné odchylky výrazně zkracuje dobu výpočtu, aniž by to významně ovlivnilo jejich přesnost. Existuje pouze mírná tendence nadhodnocovat hodnoty směrodatné odchylky získané touto metodou pro malé velikosti vzorků.
Směrodatná odchylka je pojmenovaná hodnota, proto ji lze použít k porovnání charakteru variace pouze stejných znamének. Takzvaný variační koeficient (CV), bezrozměrná veličina, podíl výběrového odhadu S na svůj průměr M:
.
V našem příkladu s tělesnou hmotností rejska:
9.6%.
Individuální variabilita (variace) znaků je jednou z nejobsáhlejších charakteristik biologické populace, jakéhokoli biologického procesu nebo jevu. Variační koeficient lze považovat za zcela adekvátní a objektivní ukazatel dobře reflektující skutečnou diverzitu populace bez ohledu na absolutní hodnotu znaku. Index byl vytvořen za účelem sjednocení ukazatelů variability různých nebo různě velkých znaků jejich přiblížením na stejnou stupnici.
Praxe ukazuje, že u mnoha biologických znaků dochází ke zvýšení variability (směrodatné odchylky) s nárůstem jejich hodnoty (aritmetický průměr). Variační koeficient přitom zůstává přibližně na stejné úrovni – 8–15 %. Růst variačního koeficientu je zpravidla odpovědný za rostoucí rozdíly v distribuci znaku od normálního zákona.
Pro nalezení průměrné hodnoty v Excelu (nezáleží na tom, zda se jedná o číselnou, textovou, procentuální nebo jinou hodnotu) existuje mnoho funkcí. A každý z nich má své vlastní vlastnosti a výhody. V tomto úkolu lze skutečně nastavit určité podmínky.
Například průměrné hodnoty řady čísel v Excelu se počítají pomocí statistických funkcí. Můžete také ručně zadat svůj vlastní vzorec. Zvažme různé možnosti.
Jak zjistit aritmetický průměr čísel?
Chcete-li zjistit aritmetický průměr, sečtěte všechna čísla v sadě a vydělte součet číslem. Například známky studenta z informatiky: 3, 4, 3, 5, 5. Co přesahuje čtvrtinu: 4. Aritmetický průměr jsme našli podle vzorce: = (3 + 4 + 3 + 5 + 5) / 5.
Jak to udělat rychle pomocí funkcí Excelu? Vezměme si například řadu náhodných čísel v řetězci:
Nebo: aktivujte buňku a jednoduše ručně zadejte vzorec: = PRŮMĚR (A1: A8).
Nyní se podívejme, co dalšího funkce PRŮMĚR umí.
Najděte aritmetický průměr prvních dvou a posledních tří čísel. Vzorec: = PRŮMĚR (A1: B1; F1: H1). Výsledek:
Průměr podle stavu
Podmínkou pro zjištění aritmetického průměru může být kritérium číselné nebo textové. Použijeme funkci: = AVERAGEIF ().
Najděte aritmetický průměr čísel větších nebo rovných 10.
Funkce: = AVERAGEIF (A1: A8, "> = 10")
Výsledek použití funkce AVERAGEIF s podmínkou "> = 10": Třetí argument - "Averaging range" - je vynechán. Za prvé, je to nepovinné. Za druhé, rozsah analyzovaný programem obsahuje POUZE číselné hodnoty. Buňky zadané v prvním argumentu budou prohledány podle podmínky zadané ve druhém argumentu.
Pozornost! V buňce lze zadat kritérium vyhledávání. A ve vzorci na něj udělejte odkaz.
Najděte průměrnou hodnotu čísel podle textového kritéria. Například průměrné prodeje produktu „stoly“.
Funkce bude vypadat takto: = AVERAGEIF ($ A $ 2: $ A $ 12; A7; $ B $ 2: $ B $ 12). Rozsah - sloupec s názvy produktů. Kritériem vyhledávání je odkaz na buňku se slovem „tabulky“ (místo odkazu A7 můžete vložit samotné slovo „tabulky“). Rozsah průměrování - ty buňky, ze kterých se budou brát data pro výpočet průměru.
V důsledku výpočtu funkce dostaneme následující hodnotu:
Pozornost! Pro textové kritérium (podmínku) musí být specifikován rozsah průměrování.
Jak vypočítat vážený průměr ceny v Excelu?
Jak jsme znali váženou průměrnou cenu?
Vzorec: = SOUČETNÍ SOUČIN (C2: C12; B2: B12) / SOUČET (C2: C12).
Pomocí vzorce SUMPRODUCT zjistíme celkovou tržbu po prodeji celého množství zboží. A funkce SUM sečte množství zboží. Vydělením celkových příjmů z prodeje produktu celkovým počtem jednotek produktu jsme zjistili váženou průměrnou cenu. Tento ukazatel zohledňuje „váhu“ každé ceny. Jeho podíl na celkovém množství hodnot.
Směrodatná odchylka: vzorec v Excelu
Rozlišujte mezi standardní odchylkou pro obecnou populaci a pro vzorek. V prvním případě jde o kořen obecného rozptylu. Ve druhém z výběrového rozptylu.
Pro výpočet této statistiky je sestaven vzorec rozptylu. Z ní se extrahuje kořen. Excel má ale připravenou funkci pro zjištění směrodatné odchylky.
Směrodatná odchylka je vázána na měřítko původních dat. To nestačí pro obrazové znázornění variace analyzovaného rozsahu. Variační koeficient se vypočítá, aby se získala relativní úroveň rozptylu dat:
směrodatná odchylka / aritmetický průměr
Vzorec v Excelu vypadá takto:
STDEVP (rozsah hodnot) / AVERAGE (rozsah hodnot).
Variační koeficient se vypočítá v procentech. V buňce tedy nastavíme procentuální formát.
K identifikaci příčin odchylek je zapotřebí zásah managementu.
K sestavení regulačního diagramu používám nezpracovaná data, průměr (μ) a směrodatnou odchylku (σ). V Excelu: μ = PRŮMĚR ($ F $ 3: $ F $ 15), σ = STDEV ($ F $ 3: $ F $ 15)
Samotný kontrolní diagram obsahuje: základní údaje, střední hodnotu (μ), spodní kontrolní mez (μ - 2σ) a horní kontrolní mez (μ + 2σ):
Stáhněte si poznámku ve formátu, příklady ve formátu
Při pohledu na poskytnutou mapu jsem si všiml, že původní data vykazovala zřetelný lineární trend směrem k poklesu podílu režijních nákladů:
Chcete-li přidat spojnici trendu, vyberte řádek s daty v grafu (v našem příkladu zelené tečky), klikněte pravým tlačítkem a vyberte možnost „Přidat spojnici trendu“. V okně „Formát trendu“, které se otevře, experimentujte s možnostmi. Rozhodl jsem se pro lineární trend.
Pokud nejsou výchozí data rozptýlena kolem průměru, pak není zcela správné je popisovat parametry μ a σ. Lineární trendová linie a kontrolní hranice ve stejné vzdálenosti od této trendové linie jsou vhodnější pro popis namísto průměrné hodnoty.
Trendovou linii Excelu lze vykreslit pomocí funkce FORECAST. Potřebujeme další řádek A3: A15 až známé hodnoty X byly souvislou řadou (čísla bloků netvoří tak souvislou řadu). Místo průměrné hodnoty ve sloupci H zadáme funkci FORECAST:
Směrodatná odchylka σ (funkce STDEV v Excelu) se vypočítá podle vzorce:
Bohužel jsem v Excelu nenašel funkci pro stanovení směrodatné odchylky (ve vztahu k trendu) tímto způsobem. Problém lze vyřešit pomocí maticového vzorce. Pokud neznáte maticové vzorce, doporučuji nejprve přečíst.
Maticový vzorec může vrátit jednu hodnotu nebo pole. V našem případě maticový vzorec vrátí jednu hodnotu:
Podívejme se blíže na to, jak maticový vzorec funguje v buňce G3.
SUM (($ F $ 3: $ F $ 15- $ H $ 3: $ H $ 15) ^ 2) určuje součet druhých mocnin rozdílů; ve skutečnosti vzorec vypočítá následující součet = (F3 - H3) 2 + (F4 - H4) 2 + ... + (F15 - H15) 2
COUNT ($ F $ 3: $ F $ 15) - počet hodnot v rozsahu F3: F15
KOŘEN (SUM (($ F $ 3: $ F $ 15- $ H $ 3: $ H $ 15) ^ 2) / (POČET ($ F $ 3: $ F $ 15) -1)) = σ
Hodnota 6,2 % je bodem spodní kontrolní hranice = 8,3 % - 2 σ
Složené uvozovky na obou stranách vzorce znamenají, že se jedná o maticový vzorec. Chcete-li vytvořit maticový vzorec, po zadání vzorce do buňky G3:
H4 – 2 * KOŘEN (SOUČET (($ F $ 3: $ F $ 15- $ H $ 3: $ H $ 15) ^ 2) / (COUNT ($ F $ 3: $ F $ 15) -1))
musíte stisknout ne Enter, ale Ctrl + Shift + Enter. Nepokoušejte se zadávat složené závorky z klávesnice – maticový vzorec nebude fungovat. Pokud potřebujete upravit maticový vzorec, proveďte to stejným způsobem jako u běžného vzorce, ale znovu po dokončení úprav stiskněte Ctrl + Shift + Enter místo Enter.
Maticový vzorec, který vrací jednu hodnotu, lze přetáhnout stejně jako běžný vzorec.
V důsledku toho jsme získali regulační diagram vytvořený pro data, která mají tendenci klesat.
P.S. Po napsání poznámky jsem byl schopen upřesnit vzorce používané k výpočtu směrodatné odchylky pro trendová data. Můžete se s nimi seznámit v souboru Excel.
