Co je to odchylka?
Rozptyl dat používáme k odhadování rozsahu budoucí hodnoty v sérii. Rozptyl udává, kolik dat se může lišit od znamenat souboru dat. Variance je často označována jako chybová hodnota. Není to nejdůvěryhodnější statistika a nepoužíváme ji pouze k předpovídání budoucí hodnoty.
Pokud mluvíme matematicky, je odchylka průměrem čtvercového rozdílu datových bodů od průměru dat. Rozptyl je druhá mocnina standardní směrodatné odchylky. Níže jsou uvedeny dva vzorce rozptylu.
Tyto vzorce nebudeme muset použít k výpočtu rozptylu v aplikaci Excel. K tomu má Excel dva vzorce VAR.P a VAR.S. Pokud chcete vědět, jak vypočítat rozptyl v aplikaci Excel, použijte vzorce popsané níže. Pokud chcete vědět, co je to odchylka a kdy použít vzorec rozptylu, přečtěte si celý článek.
Jak najít odchylku v Excelu?
Vezměme si příklad.
Svůj web jsem spustil v lednu 2019. Zde mám každý měsíc na svém webu připojená data nových uživatelů. Chci znát rozptyl těchto dat.
Toto jsou kompletní údaje. Když zachytíme kompletní data (celá populace), vypočítáme rozptyl populace (proč? Vysvětlím to dále v článku). Funkce Excelu pro výpočet rozptylu populace je VAR.P. Syntaxe VAR.P je
= VAR.P (číslo1, [číslo2], …)
Číslo1, číslo2,… : toto jsou čísla, ze kterých chcete vypočítat rozptyl.
První číslo je povinné.
Použijme tento vzorec k výpočtu rozptylu našich dat. Máme data v buňce C2: C15. Vzorec tedy bude:
| = VAR.P (C2: C15) |
Tím se vrátí hodnota 186.4285714, což je vzhledem k našim datům poměrně velká odchylka.
Od spuštění mého webu od ledna 2019 mám všechna data. Za předpokladu, že jsem svůj web spustil již dávno, ale data, která mám, jsou pouze od ledna 2019 do února 2020. Nemám úplná data. Jedná se tedy pouze o ukázková data. V takovém případě nebudu používat VAR.P, místo toho použiji funkci VAR.S pro výpočet rozptylu.
| = VAR.S (C2: C15) |
Funkce VAR.S vrací větší rozptyl než VAR.P. Vrací 200,7692308.
Jak se rozptyl vypočítává ručně v aplikaci Excel?
Takže ano, takto se vypočítá odchylka v aplikaci Excel. Jak ale tyto variační funkce vypočítají tato čísla? Pokud to víte, můžete těmto číslům více porozumět a používat je moudře. Jinak jsou tato čísla jen náhodná čísla. Abychom to pochopili, musíme rozptyl vypočítat ručně.
Ručně vypočítat rozptyl populace v aplikaci Excel
Použijeme stejná data, která jsme použili ve výše uvedeném příkladu. Matematický vzorec pro odchylku populace je:
Pro výpočet rozptylu potřebujeme vypočítat průměr (PRŮMĚR) dat, rozdíl každé hodnoty od průměru, sečíst je a nakonec tento součet rozdělit celkovým počtem pozorování.
Krok 1. Vypočítejte průměr dat
Pro výpočet průměru dat v Excelu používáme funkci PRŮMĚR.
Tento vzorec použijte v buňce C17 (nebo kdekoli chcete).
| = PRŮMĚR (C2: C15) |
Tím se vrátí 233,0.
Krok 2: Najděte rozdíl každého datového bodu od průměru
Nyní přejděte do buňky D2 a odečtěte průměr (C17) od C2 (x). Použijte tento vzorec v D2 a přetáhněte jej dolů na D15.
| = C2-$ C $ 17 |
Krok 3: Squire každý rozdíl.
Nyní musíme umocnit každý rozdíl, který zde máme. Do buňky E2 napište vzorec níže a přetáhněte dolů na E15:
| = VÝKON (D2,2) |
Krok 4: Sečtěte čtverce
Nyní musíme tyto hranaté rozdíly shrnout. V buňce C18 tedy použijte tento vzorec:
| = SUM (E2: E13) |
Poslední krok: Rozdělte součet čtverců s počtem pozorování.
Mám 14 pozorování. Pokud chcete, můžete vypočítat pomocí funkce COUNT.
Pomocí tohoto vzorce v buňce C19 vypočítejte rozptyl populace.
| = C18/POČET (C2: C15) |
Tím se vrátí hodnota 186,429…, která je přesně stejná jako rozptyl vrácený vzorcem rozptylu Excel VAR.P výše.
Nyní, když víte, jak se rozptyl populace vypočítává ručně v aplikaci Excel, budete vědět, jak jej použít ve skutečné analýze.
Ručně vypočítat rozptyl vzorku v aplikaci Excel
Ve většině případů není možné zachytit všechna data pro analýzu. Obvykle z dat vybereme náhodný vzorek a analyzujeme jej, abychom interpretovali povahu dat. V takovém případě, pokud použijeme rozptyl populace, může to být destruktivní analýza. Pro jistotu používáme vzorec Rozptyl vzorku. Vzorec pro rozptyl vzorku je:
Jediným rozdílem ve variaci vzorku a populace je jmenovatel. Ve variaci vzorku odečteme jedno od počtu pozorování (n-1). Tomu se říká nezaujatá analýza. Tím je zajištěno, že data nebudou podceňována a možný rozsah chyb je o něco širší.
K ručnímu výpočtu rozptylu vzorku v Excelu musíme zopakovat kroky 1 až 4 rozptylu populace. V posledním kroku použijte následující vzorec:
| = C18/(POČET (C2: C15) -1) |
To vrátí 200,769. To je přesně stejné jako rozptyl vrácený funkcí VAR.S. Trvá větší rozsah chyb než VAR.P, aby byla zajištěna menší šance na chyby v predikci.
Rozptyl dat není pro předpověď tak důvěryhodný. Vypočítáváme standardní odchylku, která je druhou odmocninou rozptylu, a mnoho dalších statistik, abychom minimalizovali šance na chybu v predikci.
Takže ano, lidi, takto můžete vypočítat rozptyl v aplikaci Excel. Doufám, že to bylo vysvětlující a užitečné. Pokud máte nějaké pochybnosti o této odchylce v Excelu nebo jiné statistice, sekce komentářů je vaše.
Jak vypočítat standardní odchylku v aplikaci Excel : Pro výpočet směrodatné odchylky máme několik vzorců. Standardní odchylka je jednoduše druhá odmocnina rozptylu. Vypovídá více o datech než o odchylkách.
Jak používat funkci VAR.P v aplikaci Excel : Vypočítejte rozptyl čísel dat populace v aplikaci Excel pomocí funkce VAR.P
Jak používat funkci STDEV.P v aplikaci Excel : Vypočítejte standardní odchylku pro čísla populačních dat v aplikaci Excel pomocí funkce VAR.P
Jak používat funkci DSTDEVP v aplikaci Excel : Vypočítejte směrodatnou odchylku pro čísla ukázkových dat s více kritérii v aplikaci Excel pomocí funkce DSTDEVP
Jak používat funkci VAR v Excelu : Vypočítejte rozptyl čísel vzorových dat v aplikaci Excel pomocí funkce VAR.
Regresní analýza v Excelu: Regrese je analytický nástroj, který používáme k analýze velkého množství dat a vytváření předpovědí a předpovědí v aplikaci Microsoft Excel.
Jak vytvořit graf standardní odchylky : Standardní odchylka udává, jak moc jsou data seskupena kolem průměru dat.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Funkce VLOOKUP v Excelu | Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
COUNTIF v aplikaci Excel 2016 | Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.