Ako vypočítať smerodajnú odchýlku v Exceli. Čo je štandardná odchýlka - Použitie funkcie štandardnej odchýlky na výpočet štandardnej odchýlky v programe Excel

Funkcia smerodajnej odchýlky je už z kategórie vyššej matematiky súvisiacej so štatistikou. Existuje niekoľko možností použitia funkcie štandardnej odchýlky v Exceli:

• Funkcia STDEV.
• Funkcia ŠTANDARDNÁ ODCHÝLKA.
• Funkcia STDEV

Tieto funkcie budeme potrebovať v štatistike predaja na identifikáciu stability predaja (XYZ analýza). Tieto údaje je možné použiť ako na cenotvorbu, tak aj na vytváranie (úpravu) matice sortimentu a na ďalšie užitočné analýzy predaja, o ktorých určite budem hovoriť v ďalších článkoch.

Predslov

Pozrime sa na vzorce najprv v matematickom jazyku a potom (nižšie v texte) podrobne rozoberieme vzorec v Exceli a ako sa výsledný výsledok používa pri analýze štatistík predaja.

Štandardná odchýlka je teda odhadom štandardnej odchýlky náhodnej premennej X ohľadom jeho matematického očakávania na základe nezaujatého odhadu jeho rozptylu)))) Nebojte sa nezrozumiteľných slov, buďte trpezliví a všetko pochopíte!

Opis vzorca: Smerodajná odchýlka sa meria v jednotkách merania samotnej náhodnej premennej a používa sa pri výpočte smerodajnej chyby aritmetického priemeru, pri konštrukcii intervalov spoľahlivosti, pri štatistickom testovaní hypotéz, pri meraní lineárneho vzťahu medzi náhodnými premennými. . Definuje sa ako druhá odmocnina rozptylu náhodnej premennej

Štandardná odchýlka je teraz odhadom štandardnej odchýlky náhodnej premennej X v porovnaní s jeho matematickým očakávaním na základe nezaujatého odhadu jeho rozptylu:

disperzia;

- i prvok výberu;

Veľkosť vzorky;

Aritmetický priemer vzorky:

Treba poznamenať, že oba odhady sú skreslené. Vo všeobecnom prípade nie je možné vytvoriť nezaujatý odhad. Odhad založený na nestrannom odhade rozptylu je však konzistentný.

Pravidlo troch sigma() - takmer všetky hodnoty normálne rozloženej náhodnej premennej ležia v intervale. Presnejšie, s pravdepodobnosťou približne 0,9973 leží hodnota normálne rozloženej náhodnej premennej v špecifikovanom intervale (za predpokladu, že hodnota je pravdivá a nie je získaná ako výsledok spracovania vzorky). Použijeme zaokrúhlený interval 0,1

Ak je skutočná hodnota neznáma, mali by ste použiť nie, ale s. Pravidlo troch sigma sa teda transformuje na pravidlo troch s. Práve toto pravidlo nám pomôže určiť stabilitu predaja, ale o tom neskôr...

Teraz funkcia štandardnej odchýlky v Exceli

Dúfam, že som ťa matematikou príliš nenudil? Možno niekto bude potrebovať tieto informácie na esej alebo na iné účely. Teraz sa pozrime, ako tieto vzorce fungujú v Exceli...

Aby sme určili stabilitu predaja, nemusíme sa zaoberať všetkými možnosťami funkcií smerodajnej odchýlky. Použijeme len jeden:

Funkcia STDEV

STDEV(číslo 1;číslo2;... )

Číslo1,číslo2,..- od 1 do 30 číselných argumentov zodpovedajúcich bežnej populácii.

Teraz sa pozrime na príklad:

Vytvorme knihu a provizórny stôl. Tento príklad si stiahnete v Exceli na konci článku.

Pokračovanie nabudúce!!!

Ahoj zas. No!? Mal som voľnú minútu. Pokračujme?

A tak stabilita predaja s pomocou Funkcie STDEV

Pre prehľadnosť si zoberme niekoľko improvizovaných tovarov:

V analytike, či už ide o predpoveď, výskum alebo čokoľvek iné, čo súvisí so štatistikou, je vždy potrebné vziať tri obdobia. Môže to byť týždeň, mesiac, štvrťrok alebo rok. Je možné a dokonca najlepšie brať čo najviac menštruácií, ale nie menej ako tri.

Konkrétne som ukázal prehnané predaje, kde voľným okom vidno, čo sa konzistentne predáva a čo nie. Uľahčí to pochopenie fungovania vzorcov.

A tak máme tržby, teraz musíme vypočítať priemerné hodnoty predaja podľa obdobia.

Vzorec pre priemernú hodnotu je AVERAGE (údaje z obdobia), v mojom prípade vzorec vyzerá takto = AVERAGE (C6: E6)

Vzorec aplikujeme na všetky produkty. Môžete to urobiť tak, že uchopíte pravý roh vybranej bunky a pretiahnete ju na koniec zoznamu. Alebo umiestnite kurzor na stĺpec s produktom a stlačte nasledujúce kombinácie klávesov:

Ctrl + Down presunie kurzor na začiatok zoznamu.

Ctrl + Right, kurzor sa presunie na pravú stranu tabuľky. Ešte raz doprava a dostaneme sa do stĺpca so vzorcom.

Teraz upneme

Ctrl + Shift a stlačte nahor. Takto vyberieme oblasť, kde sa bude vzorec kresliť.

A kombinácia kláves Ctrl + D pretiahne funkciu tam, kde ju potrebujeme.

Pamätajte na tieto kombinácie, skutočne zvyšujú rýchlosť v Exceli, najmä keď pracujete s veľkými poľami.

Ďalšia fáza, samotná funkcia štandardného odchodu, ako som už povedal, budeme používať iba jednu STDEV

Napíšeme funkciu a nastavíme hodnoty predaja každého obdobia v hodnotách funkcie. Ak máte v tabuľke tržby za sebou, môžete použiť rozsah, ako v mojom vzorci =STDEV(C6:E6) alebo uviesť požadované bunky oddelené bodkočiarkami =STDEV(C6;D6;E6)

Teraz sú všetky výpočty pripravené. Ale ako viete, čo sa neustále predáva a čo nie? Dajme konvenciu XYZ, kde

X je stabilný

Y - s malými odchýlkami

Z - nie je stabilný

Na tento účel používame intervaly chýb. ak dôjde k výkyvom do 10 %, budeme predpokladať, že tržby sú stabilné.

Ak je medzi 10 a 25 percentami, bude to Y.

A ak hodnota variácie presiahne 25 %, nejde o stabilitu.

Na správne nastavenie písmen pre každý produkt použijeme vzorec IF. V mojej tabuľke bude táto funkcia vyzerať takto:

IF(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

V súlade s tým rozširujeme všetky vzorce pre všetky mená.

Pokúsim sa okamžite odpovedať na otázku Prečo intervaly 10% a 25%?

V skutočnosti môžu byť intervaly odlišné, všetko závisí od konkrétnej úlohy. Konkrétne som vám ukázal prehnané predajné hodnoty, kde je rozdiel viditeľný aj okom. Je zrejmé, že produkt 1 sa nepredáva konzistentne, ale dynamika ukazuje nárast predaja. Tento produkt necháme na pokoji...

Ale tu je produkt 2, už je tu zjavná destabilizácia. A naše výpočty ukazujú Z, čo nám hovorí, že tržby nie sú stabilné. Produkt 3 a produkt 5 vykazujú stabilný výkon, upozorňujeme, že odchýlka je v rámci 10 %.

Tie. Produkt 5 so skóre 45, 46 a 45 vykazuje odchýlku 1 %, čo je stabilný číselný rad.

Produkt 2 s ukazovateľmi 10, 50 a 5 však vykazuje odchýlku 93 %, čo NIE JE stabilný číselný rad.

Po všetkých výpočtoch si môžete dať filter a odfiltrovať stabilitu, takže ak sa vaša tabuľka skladá z niekoľkých tisíc položiek, ľahko zistíte, ktoré z nich nie sú stabilné v predaji alebo naopak, ktoré sú stabilné.

„Y“ v mojej tabuľke nefungovalo, myslím, že kvôli prehľadnosti číselného radu je potrebné ho doplniť. Nakreslím produkt 6...

Vidíte, číselný rad 40, 50 a 30 ukazuje 20% variáciu. Nezdá sa, že by došlo k veľkej chybe, ale šírenie je stále značné...

A tak, aby som to zhrnul:

10.50.5 - Z nie je stabilný. Variácia viac ako 25 %

40,50,30 - Y môžete venovať pozornosť tomuto produktu a zlepšiť jeho predaj. Odchýlka menšia ako 25 %, ale väčšia ako 10 %

45,46,45 - X je stabilita, s týmto produktom zatiaľ nemusíte nič robiť. Variácia menšia ako 10 %

To je všetko! Dúfam, že som všetko vysvetlil jasne, ak nie, opýtajte sa, či to nie je jasné. A budem vám vďačný za každý komentár, či už pochvalu alebo kritiku. Takto budem vedieť, že ma čítate a že vás to, čo je veľmi DÔLEŽITÉ, zaujíma. A podľa toho sa objavia nové lekcie.

Štatistika používa obrovské množstvo ukazovateľov a jedným z nich je výpočet rozptylu v Exceli. Ak to urobíte sami ručne, zaberie vám to veľa času a môžete urobiť veľa chýb. Dnes sa pozrieme na to, ako rozložiť matematické vzorce na jednoduché funkcie. Pozrime sa na niektoré z najjednoduchších, najrýchlejších a najpohodlnejších metód výpočtu, ktoré vám umožnia urobiť všetko v priebehu niekoľkých minút.

Vypočítajte rozptyl

Rozptyl náhodnej premennej je matematické očakávanie druhej mocniny odchýlky náhodnej premennej od jej matematického očakávania.

Počítame na základe bežnej populácie

Na výpočet mat. Čaká sa, kým program použije funkciu DISP.G a jej syntax vyzerá takto: „=DISP.G(Číslo1;Číslo2;…)“.

Je možné použiť maximálne 255 argumentov, nie viac. Argumenty môžu byť prvočísla alebo odkazy na bunky, v ktorých sú špecifikované. Pozrime sa, ako vypočítať rozptyl v programe Microsoft Excel:

1. Prvým krokom je vybrať bunku, kde sa zobrazí výsledok výpočtu, a potom kliknúť na tlačidlo „Vložiť funkciu“.

2. Otvorí sa shell správy funkcií. Tam musíte hľadať funkciu „DISP.G“, ktorá môže byť v kategórii „Štatistické“ alebo „Úplný abecedný zoznam“. Keď sa nájde, mali by ste ho vybrať a kliknúť na „OK“.

3. Otvorí sa okno s argumentmi funkcie. V ňom musíte vybrať riadok „Číslo 1“ a na hárku vybrať rozsah buniek s číselným radom.

4. Potom sa výsledky výpočtu zobrazia v bunke, kde bola funkcia zadaná.

Takto môžete ľahko nájsť odchýlky v Exceli.

Výpočty robíme na základe vzorky

V tomto prípade sa vzorový rozptyl v Exceli vypočíta tak, že menovateľ neudáva celkový počet čísel, ale o jedno menej. Pre menšiu chybu sa to robí pomocou špeciálnej funkcie DISP.V, ktorej syntax je =DISP.V(Číslo1;Číslo2;...). Algoritmus akcií:

• Rovnako ako v predchádzajúcej metóde musíte vybrať bunku pre výsledok.
• V Sprievodcovi funkciami by ste mali nájsť „DISP.B“ v kategórii „Úplný abecedný zoznam“ alebo „Štatistické“.

• Ďalej sa zobrazí okno a mali by ste postupovať rovnakým spôsobom ako v predchádzajúcej metóde.

Video: Výpočet rozptylu v Exceli

Záver

Odchýlka v Exceli sa počíta veľmi jednoducho, oveľa rýchlejšie a pohodlnejšie ako manuálne, pretože funkcia matematického očakávania je pomerne zložitá a jej výpočet môže zabrať veľa času a úsilia.

Vykonávanie akejkoľvek štatistickej analýzy je nemysliteľné bez výpočtov. V tomto článku sa pozrieme na to, ako vypočítať rozptyl, smerodajnú odchýlku, variačný koeficient a ďalšie štatistické ukazovatele v Exceli.

Maximálna a minimálna hodnota

Priemerná lineárna odchýlka

Priemerná lineárna odchýlka je priemer absolútnych (modulo) odchýlok od v analyzovanom súbore údajov. Matematický vzorec je:

a- priemerná lineárna odchýlka,

X- analyzovaný ukazovateľ,

X– priemerná hodnota ukazovateľa,

n

V Exceli sa táto funkcia volá SROTCL.

Po zvolení funkcie SROTCL označíme rozsah údajov, v ktorom má prebehnúť výpočet. Kliknite na „OK“.

Disperzia

(modul 111)

Možno nie každý vie, čo, takže vysvetlím, že je to miera, ktorá charakterizuje šírenie údajov okolo matematického očakávania. Zvyčajne je však k dispozícii iba vzorka, takže sa používa nasledujúci vzorec rozptylu:

s 2– výberový rozptyl vypočítaný z pozorovaných údajov,

X- individuálne hodnoty,

X– aritmetický priemer vzorky,

n– počet hodnôt v analyzovanom súbore údajov.

Zodpovedajúca funkcia Excel je DISP.G. Pri analýze relatívne malých vzoriek (do približne 30 pozorovaní) by ste mali použiť hodnotu , ktorá sa vypočíta podľa nasledujúceho vzorca.

Rozdiel, ako vidíte, je len v menovateľovi. Excel má funkciu na výpočet vzorového nezaujatého rozptylu DISP.B.

Vyberte požadovanú možnosť (všeobecnú alebo selektívnu), označte rozsah a kliknite na tlačidlo „OK“. Výsledná hodnota môže byť veľmi veľká v dôsledku predbežnej kvadratúry odchýlok. Rozptyl v štatistike je veľmi dôležitým ukazovateľom, ale zvyčajne sa nepoužíva v čistej forme, ale na ďalšie výpočty.

Smerodajná odchýlka

Štandardná odchýlka (RMS) je koreňom rozptylu. Tento ukazovateľ sa tiež nazýva štandardná odchýlka a vypočíta sa pomocou vzorca:

všeobecnou populáciou

podľa vzorky

Môžete jednoducho vziať koreň rozptylu, ale Excel má pripravené funkcie pre štandardnú odchýlku: STDEV.G A STDEV.V(pre všeobecnú a vzorovú populáciu).

Štandardná a štandardná odchýlka, opakujem, sú synonymá.

Ďalej, ako obvykle, uveďte požadovaný rozsah a kliknite na „OK“. Smerodajná odchýlka má rovnaké jednotky merania ako analyzovaný ukazovateľ, a preto je porovnateľná s pôvodnými údajmi. Viac o tom nižšie.

Variačný koeficient

Všetky vyššie uvedené ukazovatele sú viazané na rozsah zdrojových údajov a neumožňujú získať obraznú predstavu o variácii analyzovanej populácie. Ak chcete získať relatívnu mieru rozptylu údajov, použite variačný koeficient, ktorý sa vypočíta delením smerodajná odchýlka na priemer. Vzorec pre variačný koeficient je jednoduchý:

V Exceli chýba hotová funkcia na výpočet variačného koeficientu, čo nie je veľký problém. Výpočet možno vykonať jednoduchým vydelením štandardnej odchýlky strednou hodnotou. Ak to chcete urobiť, napíšte do riadka vzorcov:

STANDARDEV.G()/AVERAGE()

Rozsah údajov je uvedený v zátvorkách. V prípade potreby použite vzorovú smerodajnú odchýlku (STDEV.V).

Variačný koeficient sa zvyčajne vyjadruje v percentách, takže bunku môžete orámovať vzorcom v percentuálnom formáte. Požadované tlačidlo sa nachádza na páse s nástrojmi na karte „Domov“:

Formát môžete zmeniť aj výberom z kontextovej ponuky po zvýraznení požadovanej bunky a kliknutím pravým tlačidlom myši.

Variačný koeficient sa na rozdiel od iných ukazovateľov rozptylu hodnôt používa ako nezávislý a veľmi informatívny ukazovateľ variácie údajov. V štatistike sa všeobecne uznáva, že ak je variačný koeficient menší ako 33 %, potom je súbor údajov homogénny, ak je viac ako 33 %, potom je heterogénny. Tieto informácie môžu byť užitočné pri predbežnej charakterizácii údajov a pri identifikácii príležitostí na ďalšiu analýzu. Okrem toho variačný koeficient, meraný v percentách, umožňuje porovnať stupeň rozptylu rôznych údajov bez ohľadu na ich rozsah a jednotky merania. Užitočný majetok.

Oscilačný koeficient

Ďalším ukazovateľom rozptylu dát je dnes koeficient oscilácie. Ide o pomer rozsahu variácie (rozdiel medzi maximálnymi a minimálnymi hodnotami) k priemeru. Neexistuje žiadny hotový vzorec Excel, takže budete musieť skombinovať tri funkcie: MAX, MIN, AVERAGE.

Koeficient oscilácie ukazuje rozsah variácie v porovnaní s priemerom, ktorý možno použiť aj na porovnanie rôznych súborov údajov.

Vo všeobecnosti sa pomocou Excelu veľa štatistických ukazovateľov počíta veľmi jednoducho. Ak niečo nie je jasné, vždy môžete použiť vyhľadávacie pole vo funkcii vložiť. Google je tu, aby vám pomohol.

Teraz vám odporúčam pozrieť si video tutoriál.

Dobrý deň

V tomto článku som sa rozhodol pozrieť na to, ako funguje štandardná odchýlka v Exceli pomocou funkcie STANDARDEVAL. Len som to veľmi dlho neopisoval a nekomentoval a tiež jednoducho preto, že je to veľmi užitočná funkcia pre tých, ktorí študujú vyššiu matematiku. A pomáhať študentom je posvätné, z vlastnej skúsenosti viem, aké ťažké je to zvládnuť. V skutočnosti možno pomocou funkcií smerodajnej odchýlky určiť stabilitu predávaných produktov, vytvárať ceny, upravovať alebo vytvárať sortiment a iné rovnako užitočné analýzy vášho predaja.

Excel používa niekoľko variácií tejto funkcie rozptylu:

Matematická teória

Najprv trochu o teórii, ako môžete opísať funkciu smerodajnej odchýlky v matematickom jazyku na jej použitie v Exceli, na analýzu napríklad údajov štatistiky predaja, ale o tom neskôr. Hneď vás varujem, napíšem veľa nezrozumiteľných slov...)))), ak čokoľvek nižšie v texte, ihneď hľadajte praktické uplatnenie v programe.

Čo presne robí štandardná odchýlka? Odhaduje štandardnú odchýlku náhodnej premennej X vo vzťahu k jej matematickému očakávaniu na základe nezaujatého odhadu jej rozptylu. Súhlasím, znie to mätúco, ale myslím si, že študenti pochopia, o čom vlastne hovoríme!

Najprv musíme určiť „štandardnú odchýlku“, aby sme následne mohli vypočítať „štandardnú odchýlku“, vzorec nám s tým pomôže: Vzorec možno opísať takto: bude sa merať v rovnakých jednotkách ako merania náhodnej premennej a používa sa pri výpočte štandardnej aritmetickej strednej chyby, pri konštrukcii intervalov spoľahlivosti, pri testovaní hypotéz pre štatistiku alebo pri analýze lineárnej vzťah medzi nezávislými premennými. Funkcia je definovaná ako druhá odmocnina rozptylu nezávislých premenných.

Teraz môžeme definovať a smerodajná odchýlka je analýza štandardnej odchýlky náhodnej premennej X vo vzťahu k jej matematickej perspektíve na základe nezaujatého odhadu jej rozptylu. Vzorec je napísaný takto:
Podotýkam, že všetky dva odhady sú skreslené. Vo všeobecných prípadoch nie je možné vytvoriť nezaujatý odhad. Ale odhad založený na odhade nezaujatého rozptylu bude konzistentný.

Praktická implementácia v Exceli

No, poďme teraz preč od nudnej teórie a pozrime sa v praxi, ako funguje funkcia STANDARDEVAL. Nebudem uvažovať o všetkých variáciách funkcie štandardnej odchýlky v Exceli stačí jedna, ale v príkladoch. Ako príklad sa pozrime na to, ako sa určujú štatistiky stability predaja.

Najprv sa pozrite na pravopis funkcie a ako vidíte, je to veľmi jednoduché:

ŠTANDARDNÁ ODCHÝLKA.G(_číslo1_;_číslo2_; ....), kde:

Teraz si vytvoríme vzorový súbor a na základe neho pouvažujme, ako táto funkcia funguje. Pretože na vykonanie analytických výpočtov je potrebné použiť aspoň tri hodnoty, ako v zásade pri akejkoľvek štatistickej analýze, vzal som podmienečne 3 obdobia, môže to byť rok, štvrťrok, mesiac alebo týždeň. V mojom prípade - mesiac. Pre maximálnu spoľahlivosť odporúčam absolvovať čo najviac období, nie však menej ako tri. Všetky údaje v tabuľke sú veľmi jednoduché pre prehľadnosť obsluhy a funkčnosti vzorca.

Najprv musíme vypočítať priemernú hodnotu za mesiac. Použijeme na to funkciu AVERAGE a dostaneme vzorec: = AVERAGE(C4:E4).
Teraz v skutočnosti vieme pomocou funkcie STANDARDEVAL.G nájsť smerodajnú odchýlku, do ktorej hodnoty potrebujeme zadať tržby produktu za každé obdobie. Výsledkom je vzorec v nasledujúcom tvare: =ŠTANDARDNÁ ODCHÝLKA.Г(C4;D4;E4).
No a polovica práce je hotová. Ďalším krokom je vytvorenie „Variácie“, ktorá sa získa vydelením priemernou hodnotou, štandardnou odchýlkou ​​a prevedením výsledku na percentá. Dostaneme nasledujúcu tabuľku:
No a základné výpočty sú hotové, ostáva už len zistiť, či sú tržby stabilné alebo nie. Zoberme si ako podmienku, že odchýlky 10% sa považujú za stabilné, od 10 do 25% sú to malé odchýlky, ale čokoľvek nad 25% už stabilné nie je. Na získanie výsledku podľa podmienok použijeme logickú jednotku a na získanie výsledku napíšeme vzorec:

IF(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

Všetky rozsahy sa berú kvôli prehľadnosti; vaše úlohy môžu mať úplne iné podmienky.
Ak chcete zlepšiť vizualizáciu údajov, keď má váš stôl tisíce pozícií, mali by ste využiť možnosť použiť určité podmienky, ktoré potrebujete alebo použijete na zvýraznenie určitých možností pomocou farebnej schémy, bude to veľmi jasné.

Najprv vyberte tie, na ktoré použijete podmienené formátovanie. Na ovládacom paneli „Domov“ vyberte „Podmienené formátovanie“ a v rozbaľovacej ponuke vyberte „Pravidlá pre zvýraznenie buniek“ a potom kliknite na položku ponuky „Text obsahuje...“. Zobrazí sa dialógové okno, v ktorom zadáte svoje podmienky.

Po zapísaní podmienok, napríklad „stabilná“ - zelená, „normálna“ - žltá a „nestabilná“ - červená, dostaneme krásnu a zrozumiteľnú tabuľku, v ktorej môžete vidieť, na čo treba venovať pozornosť ako prvému.

Použitie VBA pre funkciu STDEV.Y

Každý, kto má záujem, môže automatizovať svoje výpočty pomocou makier a použiť nasledujúcu funkciu:

Funkcia MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Pre každé x In Arr aSum = aSum + x "vypočítajte súčet prvkov poľa aCnt = aCnt + 1 "vypočítajte počet prvkov Ďalej x aAver = aSum / aCnt "priemerná hodnota pre každé x In Arr tmp = tmp + (x - aAver) ^ 2 "vypočítajte súčet druhých mocnín rozdielu medzi prvkami poľa a priemernou hodnotou Next x MyStDevP = Sqr(tmp / aCnt ) "vypočítať STANDARDEV.G() End Function

Funkcia MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# Pre každé x In Arr aSum = aSum + x "vypočítajte súčet prvkov poľa

Funkcia STDEV.B vráti smerodajnú odchýlku vypočítanú pre zadaný rozsah číselných hodnôt.

Funkcia STDEV.G sa používa na určenie štandardnej odchýlky populácie číselných hodnôt a vracia hodnotu štandardnej odchýlky za predpokladu, že odovzdané hodnoty predstavujú celú populáciu a nie vzorku.

Funkcia STANDARDEV vracia hodnotu smerodajnej odchýlky pre určitý rozsah čísel, čo je vzorka a nie celá populácia.

Funkcia STD vracia štandardnú odchýlku celej populácie odovzdanej ako svoje argumenty.

Príklady použitia STDEV.V, STDEV.G, STDEV a STDEV

Príklad 1. Podnik zamestnáva dvoch manažérov získavania zákazníkov. Údaje o počte klientov obsluhovaných za deň každým manažérom sú zaznamenané v excelovej tabuľke. Zistite, ktorý z dvoch zamestnancov pracuje efektívnejšie.

Tabuľka zdrojových údajov:

Najprv si vypočítajme priemerný počet klientov, s ktorými manažéri denne pracovali:

AVERAGE(B2:B11)

Táto funkcia vypočíta aritmetický priemer pre rozsah B2:B11, ktorý obsahuje údaje o počte klientov denne prijatých prvým manažérom. Podobne vypočítame priemerný počet klientov za deň pre druhého manažéra. Dostaneme:

Na základe získaných hodnôt sa zdá, že obaja manažéri pracujú približne rovnako efektívne. Vizuálne je však viditeľný výrazný rozptyl v počte klientov pre prvého manažéra. Vypočítajme štandardnú odchýlku pomocou vzorca:

STDEV.B(B2:B11)

B2:B11 – rozsah študovaných hodnôt. Podobne určíme smerodajnú odchýlku pre druhého manažéra a získame nasledujúce výsledky:

Ako vidíte, ukazovatele výkonnosti prvého manažéra sa vyznačujú vysokou variabilitou (rozptyl) hodnôt, a preto aritmetický priemer absolútne neodráža skutočný obraz výkonnosti. Odchýlka 1,2 znamená stabilnejšiu, a teda efektívnejšiu prácu druhého manažéra.



Príklad použitia funkcie STANDARDEV v Exceli

Príklad 2. Dve rôzne skupiny vysokoškolákov dostali skúšku z tej istej disciplíny. Hodnotiť výkon študentov.

Tabuľka zdrojových údajov:

Určme štandardnú odchýlku hodnôt pre prvú skupinu pomocou vzorca:

STDEV(A2:A11)

Podobný výpočet urobíme aj pre druhú skupinu. V dôsledku toho dostaneme:

Získané hodnoty naznačujú, že študenti druhej skupiny boli oveľa lepšie pripravení na skúšku, keďže rozptyl známok je relatívne malý. Všimnite si, že funkcia STANDARDEV konvertuje textovú hodnotu „nevyhovel“ na číselnú hodnotu 0 (nula) a zohľadní ju pri výpočte.

Príklad funkcie STANDARDEV.G v Exceli

Príklad 3. Určte efektívnosť prípravy študentov na skúšku pre všetky skupiny univerzity.

Poznámka: na rozdiel od predchádzajúceho príkladu nebude analyzovaná vzorka (niekoľko skupín), ale celý počet študentov - všeobecná populácia. Na študentov, ktorí neabsolvujú skúšku, sa neprihliada.

Vyplňte tabuľku údajov:

Pri hodnotení efektívnosti budeme pracovať s dvoma ukazovateľmi: priemerným skóre a rozptylom hodnôt. Na určenie aritmetického priemeru použijeme funkciu:

AVERAGE(B2:B21)

Na určenie odchýlky zavedieme vzorec:

STDEV.G(B2:B21)

V dôsledku toho dostaneme:

Získané údaje naznačujú akademickú výkonnosť mierne pod priemerom (<4), величина разброса характеризует довольно большое количество студентов, получивших 5 и 3 соответственно (учитывая, что анализировались только данные из диапазона от 3 до 5).

Príklad funkcie štandardnej odchýlky v Exceli

Príklad 4. Analyzujte výkon študentov na základe výsledkov skúšky s prihliadnutím na tých študentov, ktorí túto skúšku nezložili.

Tabuľka údajov:

V tomto príklade tiež analyzujeme populáciu, ale niektoré dátové polia obsahujú textové hodnoty. Na určenie smerodajnej odchýlky použijeme funkciu:

STDEV(B2:B21)

V dôsledku toho dostaneme:

Vysoké rozpätie hodnôt v poradí naznačuje veľký počet študentov, ktorí na skúške neuspeli.

Vlastnosti používania STDEV.V, STDEV.G, STDEV a STDEV

Funkcie STDEV a STDEV majú rovnakú syntax ako:

FUNKCIA (hodnota1; [hodnota2];…)

Popis:

• FUNKCIA – jedna z dvoch vyššie uvedených funkcií;
• hodnota1 – požadovaný argument charakterizujúci jednu z hodnôt vzorky (alebo všeobecnú populáciu);
• [hodnota2] – voliteľný argument charakterizujúci druhú hodnotu skúmaného rozsahu.

Poznámky:

1. Argumenty funkcií môžu zahŕňať názvy, číselné hodnoty, polia, odkazy na rozsahy číselných údajov, boolovské hodnoty a odkazy na ne.
2. Obe funkcie ignorujú prázdne hodnoty a textové údaje obsiahnuté v odovzdanom rozsahu údajov.
3. Funkcie vrátia kód chyby #HODNOTA, ak boli ako argumenty odovzdané chybové hodnoty alebo textové údaje, ktoré nemožno previesť na číselné hodnoty.

Funkcie STDEV.V a STDEV.G majú nasledujúcu syntax:

FUNCTION(číslo1,[číslo2],…)

Popis:

• FUNKCIA – niektorá z funkcií STANDARDDEVIATION.V alebo STANDARDDEVIATION.G;
• číslo1 – požadovaný argument charakterizujúci číselnú hodnotu vybranú zo vzorky alebo celej populácie;
• číslo2 – voliteľný argument charakterizujúci druhú číselnú hodnotu skúmaného rozsahu.

Poznámka: Obe funkcie nezahŕňajú do procesu výpočtu čísla reprezentované ako textové údaje, ani boolovské hodnoty TRUE a FALSE.

Poznámky:

1. Smerodajná odchýlka sa široko používa v štatistických výpočtoch, keď nájdenie priemeru rozsahu hodnôt neposkytuje pravdivé vyjadrenie distribúcie údajov. Ukazuje princíp rozdelenia hodnôt vo vzťahu k priemernej hodnote v konkrétnej vzorke alebo celej sekvencii. Príklad 1 jasne preskúma praktické využitie tohto štatistického parametra.
2. Funkcie STANDARDEV.G a STANDARDEVAL by sa mali použiť na analýzu iba časti populácie a výpočet pomocou prvého vzorca, zatiaľ čo funkcie STANDARDEV.G a STANDARDEVAL by mali vziať vstupné údaje o celej populácii a vypočítať pomocou druhého vzorca.
3. Excel obsahuje vstavané funkcie STDEV a STDEV, ktoré sú zachované kvôli kompatibilite so staršími verziami balíka Microsoft Office. V neskorších verziách programu nemusia byť zahrnuté, preto sa ich používanie neodporúča.
4. Na nájdenie štandardnej odchýlky sa používajú dva bežné vzorce: S=√((∑_(i=1)^n▒(x_i-x_av)^2)/(n-1)) a S=√((∑_ (i= 1)^n▒(x_i-x_ср)^2)/n), kde:

• S – požadovaná hodnota smerodajnej odchýlky;
• n – uvažovaný rozsah hodnôt (vzorka);
• x_i – individuálna hodnota zo vzorky;
• x_avg – aritmetický priemer pre uvažovaný rozsah.