Standardní odchylka - Tabulky Excelu a Google

Tento výukový program ukazuje, jak používat Funkce standardní odchylky aplikace Excel v Excelu pro výpočet standardní odchylky pro celou populaci.

STANDARDNÍ DEVIACE Přehled funkcí

Funkce STANDARD DEVIATION vypočítá výpočet standardní odchylky pro celou populaci.

Chcete -li použít funkci listu STANDARD DEVIATION Excel, vyberte buňku a zadejte:

(Všimněte si, jak se zobrazují vstupy vzorců)

Funkce STANDARD DEVIATION Syntaxe a vstupy:

1	= STDEV (číslo1, [číslo2], …)

čísla- Hodnoty pro získání standardní odchylky

Jak vypočítat standardní odchylku v aplikaci Excel

Kdykoli pracujete s daty, budete chtít provést několik základních testů, které vám pomohou je pochopit. Začnete obvykle výpočtem průměru pomocí funkce Excel AVERAGE <>.

Získáte tak představu, kde je „střed“ dat. A odtud se budete chtít podívat, jak jsou data rozložena kolem tohoto středního bodu. Zde přichází na řadu standardní odchylka.

Excel vám poskytuje řadu funkcí pro výpočet standardní odchylky - STDEV, STDEV.P, STDEV.S a DSTDEV. Ke všem se dostaneme, ale nejprve se podívejme, jaká je standardní odchylka je, přesně.

Co je standardní odchylka?

Standardní odchylka vám dává představu o tom, jak daleko jsou vaše datové body od průměru. Vezměte si následující datovou sadu skóre testů ze 100:

1	48,49,50,51,52

Průměr této sady dat je 50 (sečtěte všechna čísla a vydělte n, kde n je počet hodnot v rozsahu).

Nyní se podívejte na tuto další sadu dat:

1	10,25,50,75,90

Průměr této sady dat je taky 50 - ale tyto dva rozsahy vyprávějí velmi odlišný příběh. Pokud jste právě použili průměr, mohli byste si myslet, že tyto dvě skupiny byly ve své schopnosti zhruba stejné - a v průměru jsou.

Ale v první skupině máme 5 lidí, kteří získali velmi podobná, velmi průměrná skóre. A ve druhé skupině jsme my, pár letců, vyvážených několika špatnými střelci, s jednou osobou uprostřed. The rozpětí skóre se velmi liší, takže vaše interpretace dat je také velmi odlišná.

Standardní odchylka je měřítkem tohoto rozpětí.

Jak se vypočítává standardní odchylka

Abychom pochopili, co je to standardní odchylka a jak to funguje, může pomoci ruční zpracování příkladu. Tak budete vědět, co se děje „pod kapotou“, jakmile se dostaneme k funkcím Excelu, které můžete použít.

Chcete -li vypočítat standardní odchylku, proveďte tento proces:

1) Vypočítejte průměr

Vezměme si naši první sadu dat výše: 48,49,50,51,52

Průměr již známe (50), což jsem zde potvrdil pomocí funkce Excel AVERAGE <>:

1	= PRŮMĚR (C4: C8)

2) Odečtěte průměr od každé hodnoty v sadě dat

Udělal jsem to pomocí následujícího vzorce:

1	= C4-$ H $ 4

Náš průměr je v H4 a odkaz na buňku jsem „zamkl“ vložením znaků dolaru před sloupec a řádek (stisknutím klávesy F4). To znamená, že mohu zkopírovat vzorec dolů do sloupce bez aktualizace odkazu na buňku.

Výsledek:

Zde se na chvíli zastavme. Když se podíváte do nového sloupce - uvidíte, že čísla se zde sčítají až k nule. Průměr těchto čísel je také nulový.

Šíření našich dat samozřejmě nemůže být nulové - víme, že existují určité variace. Potřebujeme způsob, jak tuto variantu reprezentovat, aniž by se průměr ukázal být nulový.

3) Vyrovnejte rozdíly

Toho docílíme tím, že rozdíly rozdvojíme. Pojďme tedy přidat nový sloupec a vydělit čísla ve sloupci D:

1

= D4*D4

To vypadá lépe. Nyní máme nějaké variace a množství variací souvisí s tím, jak daleko je každé skóre od průměru.

4) Vypočítejte rozptyl - průměr čtvercových rozdílů

Dalším krokem je zjistit průměr těchto čtvercových rozdílů. Ve skutečnosti existují dva způsoby, jak to provést při výpočtu směrodatné odchylky.

• Pokud používáte údaje o populaci, jednoduše vezmete průměr (sečtete hodnoty a vydělíte n)
• Pokud používáte vzorek dat, vezmete součet hodnot a vydělíte n-1

Populační data znamenají, že máte „úplnou sadu“ svých dat, například máte data o každém studentovi v dané třídě.

Ukázková data znamenají, že nemáte všechna svá data, pouze vzorek odebraný z větší populace. Obvykle je vaším cílem s ukázkovými daty odhadnout, jaká je hodnota ve větší populaci.

Průzkum politických názorů je dobrým příkladem ukázkových dat - výzkumníci řekněme řekněme 1 000 lidí, aby získali představu o tom, co si myslí celá země nebo stát.

Zde nemáme ukázku. Máme jen pět statisticky smýšlejících členů rodiny, kteří chtějí vypočítat směrodatnou odchylku testu, který všichni absolvovali. Máme všechny datové body a neděláme odhad pro větší skupinu lidí. Toto jsou údaje o populaci - takže zde můžeme vzít průměr:

1	= PRŮMĚR (E4: E8)

Dobře, takže máme 2. Toto skóre je známé jako „rozptyl“ a je základním bodem pro mnoho statistických testů, včetně standardní odchylky. Více o rozptylu si můžete přečíst na jeho hlavní stránce: jak vypočítat rozptyl v Excelu <>.

5) Získejte odmocninu odchylky

Srovnali jsme naše čísla dříve, což zjevně hodnoty trochu nafoukne. Abychom uvedli číslo zpět do souladu se skutečnými rozdíly skóre od průměru, musíme odmocnit výsledek kroku 4:

1	= SQRT (H4)

A máme svůj výsledek: standardní odchylka je 1,414

Protože jsme odmocnili naše dříve čtvercová čísla, standardní odchylka je uvedena ve stejných jednotkách jako původní data. Takže standardní směrodatná odchylka je 1,414 testovacích bodů.

Standardní odchylka, když jsou data více rozložena

Dříve jsme měli druhý příklad datového rozsahu: 10,25,50,75,90

Jen pro zajímavost, podívejme se, co se stane, když na těchto datech vypočítáme směrodatnou odchylku:

Všechny vzorce jsou přesně stejné jako dříve (celkový průměr je stále 50).

Jediná věc, která se změnila, bylo rozšíření skóre ve sloupci C. Nyní je však naše standardní odchylka mnohem vyšší, a to 29 832 testovacích bodů.

Samozřejmě, protože máme pouze 5 datových bodů, je velmi snadné vidět, že rozpětí skóre se mezi těmito dvěma sadami liší. Ale když máte 100 nebo 1 000 datových bodů, nemůžete to poznat pouhým rychlým naskenováním dat. A právě proto používáme standardní odchylku.

Funkce Excelu pro výpočet standardní odchylky

Nyní, když víte, jak funguje standardní odchylka, nemusíte projít celým procesem, abyste dospěli ke standardní odchylce. Můžete použít jednu z integrovaných funkcí aplikace Excel.

Excel má k tomuto účelu několik funkcí:

• P vypočítá standardní odchylku pro populační data (pomocí přesné metody, kterou jsme použili ve výše uvedeném příkladu)
• S vypočítá směrodatnou odchylku pro ukázková data (pomocí metody n-1, které jsme se dotkli dříve)
• STDEV je úplně stejný jako STDEV.S. Toto je starší funkce, která byla nahrazena STDEV.S a STDEV.P.
• STDEVA je velmi podobný STDEV.S, kromě toho, že při výpočtu obsahuje textové buňky a booleovské (TRUE/FALSE) buňky.
• STDEVPA je velmi podobný STDEV.P, kromě toho, že při výpočtu obsahuje textové buňky a booleovské (TRUE/FALSE) buňky.

Páni, tady je spousta možností! Nenechte se zastrašit - v naprosté většině případů budete používat buď STDEV.P nebo STDEV.S.

Pojďme si postupně projít každou z nich, počínaje STDEV.P, protože to je metoda, kterou jsme právě prošli.

Funkce Excel STDEV.P

STDEV.P vypočítá standardní odchylku pro data populace. Používáte to takto:

1	= STDEV.P (C4: C8)

Ve STDEV.P definujete jeden argument: rozsah dat, pro který chcete vypočítat směrodatnou odchylku.

Jedná se o stejný příklad, kterým jsme krok za krokem prošli výše, když jsme ručně vypočítali standardní odchylku. A jak vidíte výše, získáme přesně stejný výsledek - 1,414.

Poznámka: STDEV.P ignoruje všechny buňky obsahující text nebo booleovské (TRUE/FALSE) hodnoty. Pokud je potřebujete zahrnout, použijte STDEVPA.

Funkce Excel STDEV.S

STDEV.S vypočítá standardní odchylku pro ukázková data. Použijte to takto:

1	= STDEV.S (C4: C8)

Opět to chce jeden argument - rozsah dat, u kterých chcete znát směrodatnou odchylku.

Než se dostaneme k příkladu, probereme rozdíl mezi STDEV.S a STDEV.P.

Jak jsme již diskutovali, STDEV.S by měl být použit na ukázkových datech - pokud jsou vaše data součástí větší sady. Předpokládejme nyní, že v našem příkladu výše test absolvovalo více lidí. Chceme odhadnout směrodatnou odchylku každého, kdo test absolvoval, pomocí těchto pěti skóre. Nyní používáme ukázková data.

Výpočet se nyní liší od kroku (4) výše, když počítáme rozptyl - průměr čtvercového rozdílu každého skóre od celkového průměru.

Místo použití normální metody - sečteme všechny hodnoty a vydělíme n, sečteme všechny hodnoty a vydělíme n-1:

1	= SUM (E4: E8) / (COUNT (E4: E8) -1)

V tomto vzorci:

• SUM získá součet čtvercových rozdílů
• COUNT vrátí naše n, od kterého odečteme 1
• Pak jednoduše vydělíme náš součet naším n-1

Tentokrát je průměr čtvercových rozdílů 2,5 (můžete si vzpomenout, že dříve to byly 2, takže je to o něco vyšší).

Proč tedy při nakládání se vzorovými daty dělíme místo n-1 místo n?

Odpověď je poměrně složitá, a pokud se jen pokoušíte spustit svá čísla, abyste porozuměli svým datům, není to něco, o co byste se opravdu měli starat. Jen se ujistěte, že používáte STDEV.S pro ukázková data a STDEV.P pro populační data, a budete v pořádku.

Pokud vás opravdu zajímá proč, podívejte se na hlavní stránku, jak vypočítat rozptyl v aplikaci Excel <>.

Dobře, takže nyní máme rozptyl pro vzorek, takže abychom získali standardní odchylku pro vzorek, dostali bychom odmocninu rozptylu:

1	= SQRT (H4)

Dostaneme 1,581.

STDEV.S za nás provede všechny výše uvedené výpočty a vrátí standardní směrodatnou odchylku vzorku pouze v jedné buňce. Pojďme se tedy podívat, s čím přichází…

1	= STDEV.S (C4: C8)

Ano, znovu 1,581.

Funkce Excel STDEV

Funkce STDEV aplikace Excel funguje přesně stejným způsobem jako STDEV.S - to znamená, že vypočítá standardní odchylku pro vzorek dat.

Používáte to stejným způsobem:

1	= STDEV (C4: C8)

Opět získáme stejný výsledek.

Důležitá poznámka: STDEV je „funkce kompatibility“, což v zásadě znamená, že se ho Microsoft zbavuje. Zatím to stále funguje, takže všechny starší tabulky budou i nadále fungovat jako obvykle. Ale v budoucích verzích Excelu to může Microsoft úplně zrušit, takže byste měli všude, kde je to možné, místo STDEV používat STDEV.S.

Funkce Excel STDEVA

STDEVA se také používá k výpočtu standardní odchylky pro vzorek, ale má několik důležitých rozdílů, o kterých potřebujete vědět:

• SKUTEČNÉ hodnoty se počítají jako 1
• FALSE hodnoty se počítají jako 0
• Textové řetězce se počítají jako 0

Použijte jej následovně:

1	= STDEVA (C4: C8)

Čtyři další přátelé a rodinní příslušníci uvedli výsledky svých testů. Ty jsou uvedeny ve sloupci C a sloupec D udává, jak STDEVA interpretuje tato data.

Protože jsou tyto buňky interpretovány jako tak nízké hodnoty, vytváří to mnohem širší rozpětí mezi našimi daty, než jsme viděli dříve, což výrazně zvýšilo standardní odchylku, nyní na 26,246.

Funkce Excel STDEVPA

STDEVPA vypočítá standardní odchylku pro populaci stejným způsobem jako STDEV.P. Do výpočtu však zahrnuje také booleovské hodnoty a textové řetězce, které jsou interpretovány následovně:

• SKUTEČNÉ hodnoty se počítají jako 1
• FALSE hodnoty se počítají jako 0
• Textové řetězce se počítají jako 0

Používáte to takto:

1	= STDEVPA (C4: C12)

Filtrování dat před výpočtem standardní odchylky

V reálném světě nebudete mít vždy pěkná uklizená tabulka přesná data, která potřebujete. Často budete mít velkou tabulku plnou dat, kterou budete muset před výpočtem standardní odchylky filtrovat.

Můžete to udělat velmi snadno s databázovými funkcemi aplikace Excel: DSTDEV (pro vzorky) a DSTDEVP (pro populace).

Tyto funkce vám umožňují vytvořit tabulku kritérií, ve které můžete definovat všechny potřebné filtry. Funkce použijí tyto filtry v zákulisí, než vrátí standardní odchylku. Tímto způsobem se nemusíte dotýkat automatického filtru nebo stahovat data do samostatného listu - DSTDEV a SDTDEVP to vše zvládnou za vás.

Další informace najdete na hlavní stránce funkcí Excel DSTDEV a DSTDEVP <>.

STANDARD DEVIATION Funkce v Tabulkách Google

Funkce STANDARD DEVIATION funguje v Tabulkách Google úplně stejně jako v Excelu: