VARIANCE - Excel a Tabulky Google

Tento výukový program ukazuje, jak používat Funkce Excel VARIANCE v Excelu odhadnout rozptyl na základě daného vzorku.

Přehled funkcí VARIANCE

Funkce VARIANCE vypočítá rozptyl odhadu na základě daného vzorku.

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

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

Funkce VARIANCE Syntaxe a vstupy:

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

čísla- Hodnoty pro získání Variance

Jak vypočítat rozptyl v aplikaci Excel

Rozptyl vám řekne, jak jsou hodnoty v datové sadě rozloženy od průměru. Matematicky řečeno, rozptyl je průměrem čtvercového rozdílu každého skóre od průměru (ale k tomu se brzy dostaneme).

Excel vám poskytuje řadu funkcí pro výpočet rozptylu - VAR.S, VAR.P, VARA, VARPA a dvě starší funkce, VAR a VARP.

Než se do těchto funkcí ponoříme a naučíme se je používat, promluvme si o rozptylu a jeho výpočtu.

Co je to odchylka?

Při analýze dat je běžným prvním krokem výpočet průměru. To je samozřejmě užitečná statistika pro výpočet, ale neposkytuje vám úplný obraz o tom, co se děje s vašimi daty.

Vezměte si následující sadu dat, což může být skupina výsledků testů zaznamenaných ze 100:

1	48,49,50,51,52

Průměr tohoto rozsahu je 50 (součet čísel a dělení n, kde n je počet hodnot).

Dále proveďte následující sadu výsledků testů:

1	10,25,50,75,90

Průměr tohoto rozsahu je taky 50 - ale evidentně tu máme dva velmi odlišné rozsahy dat.

Průměr vám sám o sobě nemůže říci nic o tom, jak jsou skóre rozložena. Neříká vám, zda jsou všechny hodnoty seskupeny jako v prvních příkladech, nebo široce od sebe jako ve druhém. Rozptyl vám může pomoci se to naučit.

Rozptyl se také používá jako základní bod pro řadu složitějších statistických postupů.

Jak se vypočítá rozptyl

Pojďme si projít základní příklad a vypočítat rozptyl ručně. Tímto způsobem budete vědět, co se děje v zákulisí, když ve skutečnosti začnete uvádět do chodu funkce odchylek Excelu.

Řekněme, že máme datovou sadu představující tři hrací karty, 4, 6 a 8.

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

1) Vypočítejte průměr

Nejprve vypočítáme průměr. Víme, že náš datový rozsah je 4, 6, 8, takže průměr bude:

1	(6 + 4 + 8) / 3 = 6

Níže jsem to potvrdil pomocí funkce Excel AVERAGE <>:

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

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

Dále od každé z našich hodnot odečteme průměr.

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

1	= C4-$ H $ 4

Průměr je uložen v H4, takže to od každé hodnoty v tabulce jen odečtu. Podpisy dolaru zde pouze „uzamknou“ odkaz buňky na H4, takže když ji zkopíruji do sloupce, zůstane stejná.

Výsledky:

Máme:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Potřebujeme získat průměr těchto rozdílů z průměru, ale průměr těchto tří hodnot je nulový! Musíme tedy zdůraznit rozdíly, které děláme tak, že je umocníme na druhou.

3) Vyrovnejte rozdíly

Pojďme přidat nový sloupec a vydělit čísla ve sloupci D:

1

= D4*D4

Dobře, to je lepší. Nyní, když se rozdíly neprůměrují na nulu, můžeme vypočítat rozptyl.

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

Zde potkáváme vidličku na silnici. Existují dva způsoby výpočtu rozptylu a ten, který použijete, závisí na typu dat, která máte.

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

Údaje o populaci znamenají, že máte k dispozici všechny potřebné údaje, například pokud chcete průměrný věk učitelů na konkrétní škole a máte údaje o věku pro každého jednotlivého učitele na této škole, máte údaje o populaci.

Ukázková data znamenají, že nemáte všechna svá data, pouze vzorek odebraný z větší populace. Pokud tedy chcete průměrný věk učitelů v celé zemi a máte údaje pouze o učitelích na jedné škole, máte vzorová data.

V našem příkladu máme údaje o populacích. Zajímají nás pouze naše tři karty - to je populace, a neodebrali jsme z nich vzorek. Můžeme tedy vzít průměr čtvercových rozdílů normálním způsobem:

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

Rozptyl naší populace je tedy 2 666.

Pokud tohle byl ukázková data (možná jsme tyto tři karty vytáhli z větší sady), průměr bychom vypracovali následovně:

1	Rozptyl vzorku = (4 + 0 + 4) / (3 - 1)

Nebo:

1	Rozptyl vzorku = 8/2 = 4

Proč dělit n-1 vzorovými daty místo pouhého n?

Krátká odpověď na tuto otázku je „Protože dává správnou odpověď“. Ale myslím si, že budeš chtít trochu víc! Toto je složité téma, proto zde uvedu jen stručný přehled.

Přemýšlejte o tom takto: pokud odeberete vzorek dat z populace, budou se tyto hodnoty obvykle blížit průměru vzorek než jsou do průměru počet obyvatel.

To znamená, že pokud dělíte pouze n, trochu podceníte populační rozptyl. Dělení n-1 to trochu koriguje.

S naší sadou tří karet jsme na správném místě, abychom tuto teorii otestovali. Protože existují pouze tři karty, existuje malý počet vzorků, které můžeme případně odebrat.

Vezměme si vzorky dvou karet. Vybereme jednu kartu, vrátíme ji, zamícháme a pak vybereme další kartu. To znamená, že můžeme vybrat devět kombinací dvou karet.

Pouze s devíti možnými vzorky můžeme vypočítat každou možnou odchylku vzorku pomocí obou metod (dělit n a dělit n-1), vzít jejich průměr a zjistit, který z nich nám dává správnou odpověď.

V níže uvedené tabulce jsem vše rozložil. Každý řádek tabulky je jiný vzorek a sloupce B a C ukazují dvě karty, které byly vybrány v každém vzorku. Pak jsem přidal další dva sloupce: jeden, kde jsem vypočítal rozptyl tohoto vzorku dvou karet dělením n, a druhý, kde jsem dělil n - 1.

Podívej se:

Vpravo od tabulky jsem ukázal průměry sloupců D a E.

Průměr sloupce D při dělení n nám dává rozptyl 1,333.

Průměr sloupce E při dělení n-1 nám dává rozptyl 2,666.

Z našeho předchozího příkladu již víme, že rozptyl populace je 2,666. Dělení n-1 při použití ukázkových dat nám tedy poskytuje přesnější odhady.

Funkce Excelu pro výpočet rozptylu

Nyní, když jste viděli příklad výpočtu rozptylu, přejdeme k funkcím aplikace Excel.

Zde máte několik možností:

• P vrací rozptyl pro data populace (pomocí metody dělení n)
• S vrací rozptyl pro ukázková data (děleno n-1)
• VAR je starší funkce, která funguje úplně stejně jako VAR.S
• VARA je stejný jako VAR.S, kromě toho, že obsahuje textové buňky a logické hodnoty
• VARPA je stejný jako VAR.P, kromě toho, že obsahuje textové buňky a booleovské hodnoty

Pojďme si je projít jeden po druhém.

Funkce Excel VAR.P

VAR.P vypočítá rozptyl populačních dat (pomocí metody dělení n). Použijte to takto:

1	= VAR.P (C4: C6)

Ve VAR.P definujete pouze jeden argument: rozsah dat, pro který chcete vypočítat rozptyl. V našem případě jsou to hodnoty karty v C4: C6.

Jak vidíte výše, VAR.P vrací 2,666 za naši sadu tří karet. To je stejná hodnota, kterou jsme dříve vypočítali ručně.

Všimněte si toho, že VAR.P zcela ignoruje buňky obsahující text nebo booleovské (TRUE/FALSE) hodnoty. Pokud je potřebujete zahrnout, použijte místo toho VARPA.

Funkce Excel VAR.S

VAR.S vypočítá rozptyl pro ukázková data (děleno n-1). Používáte to takto:

1	= VAR.S (C4: C6)

Opět existuje pouze jeden argument - rozsah vašich dat.

V tomto případě VAR.S vrátí 4. Stejný údaj jsme získali v kroku 4, když jsme provedli výše uvedený ruční výpočet.

VAR.S zcela ignoruje buňky obsahující text nebo booleovské hodnoty (TRUE/FALSE). Pokud je potřebujete zahrnout, použijte místo toho VARA.

Funkce Excel VAR

VAR je zcela ekvivalentní VAR.S: vypočítává rozptyly pro ukázková data (pomocí metody n-1). Zde je návod, jak jej použít:

1	= VAR (C4: C6)

VAR je „funkce kompatibility“. To znamená, že společnost Microsoft právě odebírá tuto funkci z aplikace Excel. V tuto chvíli je stále k dispozici k použití, ale místo toho byste měli použít VAR.S, aby vaše tabulky zůstaly kompatibilní s budoucími verzemi Excelu.

Funkce Excel VARA

VARA také vrací rozptyl ukázkových dat, ale má několik klíčových rozdílů oproti VAR a VAR.S. Jmenovitě zahrnuje do výpočtu booleovské a textové hodnoty:

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

Zde je návod, jak jej používáte:

1	= VARA (C4: C11)

Do tabulky jsme přidali dalších pět řádků: J, Q, K, TRUE a FALSE. Sloupec D ukazuje, jak VARA interpretuje tyto hodnoty.

Protože nyní máme v tabulce novou dávku nízkých hodnot, rozptyl se zvýšil na 10,268.

Funkce Excel VARPA

VARPA vypočítá rozptyl údajů o populaci. Je podobný VAR.P, kromě toho, že do výpočtu zahrnuje také booleovské hodnoty a textové řetězce:

• 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	= VARPA (C4: C12)

Do tabulky jsme přidali dalších pět řádků: J, Q, K, TRUE a FALSE. Sloupec D ukazuje, jak VARPA interpretuje tyto hodnoty.

V důsledku přidání této skupiny nižších hodnot do dat se rozptyl zvýšil na 8,984.

Funkce VARIANCE v Tabulkách Google

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