VARIANCE - Excel a Tabuľky Google

Tento návod ukazuje, ako používať Funkcia Excel VARIANCE v Exceli odhadnúť rozptyl na základe danej vzorky.

Prehľad funkcií VARIANCE

Funkcia VARIANCE vypočíta rozptyl odhadu na základe danej vzorky.

Ak chcete použiť funkciu pracovného hárka VARIANCE Excel, vyberte bunku a zadajte:

(Všimnite si, ako sa zobrazujú vstupy vzorcov)

Funkcia VARIANCE Syntax a vstupy:

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

čísla- Hodnoty na získanie odchýlky

Ako vypočítať odchýlku v programe Excel

Rozptyl vám hovorí, ako sú hodnoty v súbore údajov rozložené od priemeru. Matematicky povedané, odchýlka je priemerom štvorcového rozdielu každého skóre od priemeru (k tomu sa však dostaneme čoskoro).

Excel vám ponúka množstvo funkcií na výpočet rozptylu - VAR.S, VAR.P, VARA, VARPA a dve staršie funkcie, VAR a VARP.

Predtým, ako sa ponoríme do týchto funkcií a naučíme sa ich používať, porozprávajme sa o rozptyle a jeho výpočte.

Aký je rozptyl?

Pri analýze údajov je bežným prvým krokom výpočet priemeru. Toto je samozrejme užitočná štatistika na výpočet, ale neposkytuje vám úplný obraz o tom, čo sa deje s vašimi údajmi.

Vykonajte nasledujúci súbor údajov, ktorý môže predstavovať skupinu výsledkov testov dosiahnutých zo 100:

1	48,49,50,51,52

Priemer tohto rozsahu je 50 (súčet čísel a delenie n, kde n je počet hodnôt).

Ďalej vykonajte nasledujúcu sadu výsledkov testov:

1	10,25,50,75,90

Priemer tohto rozsahu je tiež 50 - ale evidentne tu máme dva veľmi odlišné rozsahy údajov.

Priemer vám sám o sebe nemôže povedať nič o tom, ako sú skóre rozložené. Nehovorí vám, či sú všetky hodnoty zoskupené ako v prvých príkladoch, alebo sú od seba vzdialené ako druhý. Odchýlka vám môže pomôcť sa to naučiť.

Rozptyl sa používa aj ako základný bod pre celý rad komplexnejších štatistických postupov.

Ako sa vypočítava odchýlka

Poďme sa pozrieť na základný príklad a vypočítať rozptyl ručne. Tak budete vedieť, čo sa deje v zákulisí, keď skutočne začnete uvádzať do činnosti variačné funkcie Excelu.

Povedzme, že máme súbor údajov, ktorý predstavuje tri hracie karty, 4, 6 a 8.

Na výpočet rozptylu vykonáte tento postup:

1) Vypočítajte priemer

Najprv vypočítame priemer. Vieme, že náš rozsah údajov je 4, 6, 8, takže priemer bude:

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

Nižšie som to potvrdil pomocou funkcie Excel AVERAGE <>:

1	= PRIEMERNÉ (C4: C6)

2) Odpočítajte priemer od každej hodnoty v súbore údajov

Ďalej od každej z našich hodnôt odpočítame priemer.

Urobil som to pomocou nasledujúceho vzorca:

1	= C4-$ H $ 4

Priemer je uložený v H4, takže to len odpočítam od každej hodnoty v tabuľke. Znaky dolára tu iba „uzamknú“ odkaz bunky na H4, takže keď ju skopírujem do stĺpca, zostane rovnaká.

Výsledky:

Máme:

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

Priemer týchto rozdielov musíme získať z priemeru, ale priemer týchto troch hodnôt je nulový! Musíme teda zdôrazniť rozdiely, ktoré robíme tak, že ich zarovnáme.

3) Vyrovnajte rozdiely

Pridajte nový stĺpec a číslice v stĺpci D dajte na štvorec:

1

= D4*D4

Dobre, to je lepšie. Teraz, keď sa rozdiely ne priemerujú na nulu, môžeme vypočítať odchýlku.

4) Vypočítajte priemer štvorcových rozdielov

Tu stretávame vidličku na ceste. Existujú dva spôsoby výpočtu rozptylu a ten, ktorý použijete, závisí od typu údajov, ktoré máte.

• Ak používate údaje o populácii, jednoducho vezmete priemer za normálny (súčet hodnôt a delenie n)
• Ak používate ukážkové údaje, spočítate hodnoty a vydelíte n-1

Údaje o populácii znamenajú, že máte k dispozícii všetky potrebné údaje, napríklad ak chcete priemerný vek učiteľov na konkrétnej škole a máte údaje o veku pre každého učiteľa na danej škole, máte údaje o populácii.

Vzorové údaje znamenajú, že nemáte všetky svoje údaje, iba vzorku odobratú z väčšej populácie. Ak teda chcete priemerný vek učiteľov v celej krajine a máte údaje iba o učiteľoch na jednej škole, máte vzorové údaje.

V našom prípade máme údaje o populácii. Máme záujem iba o naše tri karty - to je populácia a my sme z nich neodobrali vzorku. Môžeme teda vziať priemer štvorcových rozdielov normálnym spôsobom:

1	= PRIEMERNÉ (E4: E8)

Rozptyl našej populácie je 2,666.

Ak toto bol ukážkové údaje (možno sme tieto tri karty vytiahli z väčšej sady), priemer by sme vypracovali nasledovne:

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

Alebo:

1	Rozptyl vzorky = 8/2 = 4

Prečo deliť n-1 vzorovými údajmi, nie iba n?

Krátka odpoveď na túto otázku je „Pretože dáva správnu odpoveď“. Ale predstavujem si, že budeš chcieť trochu viac! Toto je komplexná téma, preto tu uvediem len stručný prehľad.

Zamyslite sa nad tým takto: Ak odoberiete vzorku údajov z populácie, tieto hodnoty budú mať tendenciu byť bližšie k priemeru ukážka než sú do priemeru populácia.

To znamená, že ak vydelíte n, budete trochu podceňovať populačný rozptyl. Delenie n-1 to trochu opravuje.

So sadou troch kariet sme na správnom mieste na otestovanie tejto teórie. Pretože sú len tri karty, existuje malý počet vzoriek, ktoré môžeme prípadne odobrať.

Zoberme si vzorky dvoch kariet. Vyberieme jednu kartu, vrátime ju, zamiešame a potom vyberieme ďalšiu kartu. To znamená, že si môžeme vybrať deväť kombinácií dvoch kariet.

S iba deviatimi možnými vzorkami môžeme vypočítať všetky možné odchýlky vzorky pomocou oboch metód (delené n a delené n-1), vziať ich priemer a zistiť, ktorá z nich nám dáva správnu odpoveď.

V nižšie uvedenej tabuľke som rozložil všetko. Každý riadok tabuľky je iná vzorka a stĺpce B a C zobrazujú dve karty, ktoré boli vybraté v každej vzorke. Potom som pridal ďalšie dva stĺpce: jeden, v ktorom som vypočítal rozptyl tejto vzorky dvoch kariet delením n, a druhý, kde som rozdelil n - 1.

Pozri sa:

Vpravo od tabuľky som zobrazil priemer stĺpcov D a E.

Priemer stĺpca D pri delení n nám dáva rozptyl 1,333.

Priemer stĺpca E pri delení n-1 nám dáva rozptyl 2,666.

Z nášho predchádzajúceho príkladu už vieme, že rozptyl populácie je 2,666. Delenie n-1 pri použití vzorových údajov nám teda dáva presnejšie odhady.

Funkcie Excelu na výpočet rozptylu

Teraz, keď ste videli príklad výpočtu odchýlky, prejdeme k funkciám programu Excel.

Tu máte niekoľko možností:

• P vráti rozptyl údajov o populácii (pomocou metódy delenia n)
• S vráti rozptyl pre vzorové údaje (delené n-1)
• VAR je staršia funkcia, ktorá funguje úplne rovnako ako VAR.S
• VARA je rovnaký ako VAR.S, ibaže obsahuje textové bunky a booleovské hodnoty
• VARPA je rovnaký ako VAR.P, ibaže obsahuje textové bunky a booleovské hodnoty

Poďme si ich prejsť po jednom.

Funkcia Excel VAR.P

VAR.P vypočítava rozptyl údajov o populácii (pomocou metódy delenia n). Použite to takto:

1	= VAR.P (C4: C6)

Vo VAR.P definujete iba jeden argument: rozsah údajov, pre ktorý chcete vypočítať odchýlku. V našom prípade sú to hodnoty karty v C4: C6.

Ako vidíte vyššie, VAR.P vracia 2,666 za našu sadu troch kariet. To je rovnaká hodnota, akú sme predtým vypočítali ručne.

Všimnite si toho, že VAR.P úplne ignoruje bunky obsahujúce text alebo booleovské hodnoty (TRUE/FALSE). Ak ich potrebujete zahrnúť, použite namiesto toho VARPA.

Funkcia Excel VAR.S

VAR.S vypočítava rozptyl pre vzorové údaje (delené n-1). Používate to takto:

1	= VAR.S (C4: C6)

Opäť existuje iba jeden argument - rozsah vašich údajov.

V tomto prípade VAR.S vráti 4. Rovnaký údaj sme získali v kroku 4, keď sme vyššie vykonali manuálny výpočet.

VAR.S úplne ignoruje bunky obsahujúce text alebo booleovské hodnoty (TRUE/FALSE). Ak ich potrebujete zahrnúť, použite namiesto toho VARA.

Funkcia Excel VAR

VAR je úplne ekvivalentný s VAR.S: vypočítava odchýlky pre vzorové údaje (pomocou metódy n-1). Tu je návod, ako ho použiť:

1	= VAR (C4: C6)

VAR je „funkcia kompatibility“. To znamená, že spoločnosť Microsoft práve odstraňuje túto funkciu z programu Excel. V súčasnej dobe je stále k dispozícii na použitie, ale namiesto toho by ste mali používať VAR.S, aby vaše tabuľky zostali kompatibilné s budúcimi verziami Excelu.

Funkcia Excel VARA

VARA tiež vracia rozptyl vzorových údajov, ale má niekoľko zásadných rozdielov voči VAR a VAR.S. Do svojho výpočtu zahŕňa najmä booleovské a textové hodnoty:

• SKUTOČNÉ hodnoty sa počítajú ako 1
• FALSE hodnoty sa počítajú ako 0
• Textové reťazce sa počítajú ako 0

Takto ho používate:

1	= VARA (C4: C11)

Do tabuľky sme pridali ďalších päť riadkov: J, Q, K, TRUE a FALSE. Stĺpec D ukazuje, ako VARA interpretuje tieto hodnoty.

Pretože teraz máme v tabuľke novú dávku nízkych hodnôt, rozptyl sa zvýšil na 10,268.

Funkcia Excel VARPA

VARPA vypočítava rozptyl údajov o populácii. Je podobný VAR.P, okrem toho, že do výpočtu zahŕňa aj booleovské hodnoty a textové reťazce:

• SKUTOČNÉ hodnoty sa počítajú ako 1
• FALSE hodnoty sa počítajú ako 0
• Textové reťazce sa počítajú ako 0

Používate to takto:

1	= VARPA (C4: C12)

Do tabuľky sme pridali ďalších päť riadkov: J, Q, K, TRUE a FALSE. Stĺpec D ukazuje, ako VARPA interpretuje tieto hodnoty.

V dôsledku pridania tejto skupiny nižších hodnôt k údajom sa rozptyl zvýšil na 8,984.

Funkcia VARIANCE v Tabuľkách Google

Funkcia CORREL funguje v Tabuľkách Google úplne rovnako ako v programe Excel: