Andrei Lipov
În termeni simpli, abaterea standard arată cât de mult fluctuează prețul unui instrument în timp. Adică, cu cât este mai mare acest indicator, cu atât este mai mare volatilitatea sau variabilitatea unui număr de valori.
Abaterea standard poate și ar trebui utilizată pentru a analiza seturi de valori, deoarece două seturi cu aceeași medie aparent se pot dovedi a fi complet diferite în răspândirea valorilor.
Exemplu
Să luăm două rânduri de numere.
a) 1,2,3,4,5,6,7,8,9. Medie - 5. St. abatere = 2,7386
b) 20,1,7,1,15,-1,-20,4,18,5. Medie - 5. St. abatere = 12,2066
Dacă nu țineți întreaga serie de numere în fața ochilor, atunci indicatorul de abatere standard arată că în cazul „b” valorile sunt mult mai împrăștiate în jurul valorii lor medii.
În linii mari, în seria „b” valoarea este 5 plus sau minus 12 (în medie) - nu este exactă, dar dezvăluie semnificația.
Cum se calculează abaterea standard
Pentru a calcula abaterea standard, puteți utiliza o formulă împrumutată din calcularea abaterii standard a randamentelor fondurilor mutuale:
Aici N este numărul de cantități,
DOHaverage - media tuturor valorilor,
Perioada DOH - valoarea N.
În Excel, funcția corespunzătoare se numește STANDARDEVAL (sau STDEV în versiunea în limba engleză a programului).
Instrucțiunile pas cu pas sunt următoarele:
- Calculați media pentru o serie de numere.
- Pentru fiecare valoare, determinați diferența dintre medie și acea valoare.
- Calculați suma pătratelor acestor diferențe.
- Împărțiți suma rezultată la numărul de numere din serie.
- Luați rădăcina pătrată a numărului pe care l-ați obținut la ultimul pas.
Prietenii tăi vor găsi aceste informații utile. Distribuie cu ei!
Să calculăm înDOMNIȘOARĂEXCELAvarianța eșantionului și abaterea standard. Vom calcula, de asemenea, varianța unei variabile aleatoare dacă distribuția ei este cunoscută.
Să luăm în considerare mai întâi dispersie, apoi deviație standard.
Varianta eșantionului
Varianta eșantionului (varianța eșantionului,probăvarianţă) caracterizează răspândirea valorilor în matrice relativ la .
Toate cele 3 formule sunt echivalente din punct de vedere matematic.
Din prima formulă este clar că varianța eșantionului este suma abaterilor pătrate ale fiecărei valori din matrice de la medie, împărțit la dimensiunea eșantionului minus 1.
variaţiile mostre se folosește funcția DISP(), engleză. numele VAR, adică Varianta. Din versiunea MS EXCEL 2010, se recomandă utilizarea analogului său DISP.V(), engleză. numele VARS, adică Varianta eșantionului. În plus, începând cu versiunea de MS EXCEL 2010, există o funcție DISP.Г(), engleză. numele VARP, adică Varianta populației, care calculează dispersie Pentru populatie. Întreaga diferență se reduce la numitor: în loc de n-1 ca DISP.V(), DISP.G() are doar n în numitor. Înainte de MS EXCEL 2010, funcția VAR() a fost utilizată pentru a calcula varianța populației.
Varianta eșantionului
=QUADROTCL(Eșantion)/(COUNT(Eșantion)-1)
=(SUMA(Eșantion)-COUNT(Eșantion)*MEDIE(Eșantion)^2)/ (NUMĂR(Eșantion)-1)– formula uzuală
=SUMA((Eșantion -MEDIE(Eșantion))^2)/ (NUMĂR (Eșantion)-1) –
Varianta eșantionului este egal cu 0, numai dacă toate valorile sunt egale între ele și, în consecință, egale valoarea medie. De obicei, cu cât valoarea este mai mare variaţiile, cu atât este mai mare răspândirea valorilor în matrice.
Varianta eșantionului este o estimare punctuala variaţiile distribuţia variabilei aleatoare din care a fost făcută probă. Despre construcție intervale de încredere la evaluare variaţiile poate fi citit in articol.
Varianta unei variabile aleatoare
A calcula dispersie variabilă aleatoare, trebuie să o știți.
Pentru variaţiile variabila aleatoare X este adesea desemnată Var(X). Dispersia egal cu pătratul abaterii de la medie E(X): Var(X)=E[(X-E(X)) 2 ]
dispersie calculat prin formula:
unde x i este valoarea pe care o poate lua o variabilă aleatoare și μ este valoarea medie (), p(x) este probabilitatea ca variabila aleatoare să ia valoarea x.
Dacă o variabilă aleatoare are , atunci dispersie calculat prin formula:
Dimensiune variaţiile corespunde pătratului unității de măsură a valorilor inițiale. De exemplu, dacă valorile din eșantion reprezintă măsurători ale greutății părții (în kg), atunci dimensiunea varianței ar fi kg 2 . Acest lucru poate fi dificil de interpretat, deci pentru a caracteriza răspândirea valorilor, o valoare egală cu rădăcina pătrată a variaţiile – deviație standard.
Unele proprietăți variaţiile:
Var(X+a)=Var(X), unde X este o variabilă aleatoare și a este o constantă.
Var(aХ)=a 2 Var(X)
Var(X)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)- 2*E(X)*E(X)+(E(X)) 2 =E(X 2)-(E(X)) 2
Această proprietate de dispersie este utilizată în articol despre regresia liniară.
Var(X+Y)=Var(X) + Var(Y) + 2*Cov(X;Y), unde X și Y sunt variabile aleatoare, Cov(X;Y) este covarianța acestor variabile aleatoare.
Dacă variabilele aleatoare sunt independente, atunci ele covarianta este egal cu 0 și, prin urmare, Var(X+Y)=Var(X)+Var(Y). Această proprietate de dispersie este utilizată în derivare.
Să arătăm că pentru mărimi independente Var(X-Y)=Var(X+Y). Într-adevăr, Var(X-Y)= Var(X-Y)= Var(X+(-Y))= Var(X)+Var(-Y)= Var(X)+Var(-Y)= Var(X)+(- 1) 2 Var(Y)= Var(X)+Var(Y)= Var(X+Y). Această proprietate de dispersie este utilizată pentru a construi .
Deviația standard a eșantionului
Deviația standard a eșantionului este o măsură a cât de larg sunt împrăștiate valorile dintr-un eșantion în raport cu .
A-priorie, deviație standard egal cu rădăcina pătrată a variaţiile:
Deviație standard nu ține cont de mărimea valorilor în probă, ci doar gradul de dispersie a valorilor din jurul lor in medie. Pentru a ilustra acest lucru, să dăm un exemplu.
Să calculăm abaterea standard pentru 2 eșantioane: (1; 5; 9) și (1001; 1005; 1009). În ambele cazuri, s=4. Este evident că raportul dintre abaterea standard și valorile matricei diferă semnificativ între eșantioane. Pentru astfel de cazuri este folosit Coeficientul de variație(Coeficient de variație, CV) - raport Deviație standard la medie aritmetic, exprimat ca procent.
În MS EXCEL 2007 și versiuni anterioare pentru calcul Deviația standard a eșantionului se folosește funcția =STDEVAL(), engleză. numele STDEV, adică Deviație standard. Din versiunea MS EXCEL 2010, se recomandă utilizarea analogului său =STDEV.B() , engleză. numele STDEV.S, adică Exemplu de deviare standard.
În plus, începând cu versiunea de MS EXCEL 2010, există o funcție STANDARDEV.G(), engleză. numele STDEV.P, adică Deviația standard a populației, care calculează deviație standard Pentru populatie. Toată diferența se reduce la numitor: în loc de n-1 ca în STANDARDEV.V(), STANDARDEVAL.G() are doar n în numitor.
Deviație standard poate fi calculat și direct folosind formulele de mai jos (vezi fișierul exemplu)
=ROOT(QUADROTCL(Eșantion)/(COUNT(Eșantion)-1))
=ROOT((SUMA(Eșantion)-NUMĂR (Eșantion)*MEDIA(Eșantion)^2)/(NUMĂR (Eșantion)-1))
Alte măsuri de împrăștiere
Funcția SQUADROTCL() calculează cu o sumă a abaterilor pătrate ale valorilor de la acestea in medie. Această funcție va returna același rezultat ca și formula =DISP.G( Probă)*VERIFICA( Probă) , Unde Probă- o referință la un interval care conține o matrice de valori ale eșantionului (). Calculele în funcția QUADROCL() se fac după formula:
Funcția SROTCL() este, de asemenea, o măsură a răspândirii unui set de date. Funcția SROTCL() calculează media valorilor absolute a abaterilor valorilor de la in medie. Această funcție va returna același rezultat ca și formula =SUMPRODUS(ABS(Eșantion-MEDIE(Eșantion)))/COUNT(Eșantion), Unde Probă- o legătură către un interval care conține o serie de valori eșantion.
Calculele în funcția SROTCL () se fac după formula:
Bună ziua
În acest articol, am decis să mă uit la modul în care funcționează abaterea standard în Excel folosind funcția STANDARDEVAL. Pur și simplu nu l-am descris sau comentat de foarte mult timp și, de asemenea, pur și simplu pentru că este o funcție foarte utilă pentru cei care studiază matematica superioară. Și a ajuta studenții este sfânt; știu din experiență cât de dificil este să stăpânești. În realitate, funcțiile de abatere standard pot fi folosite pentru a determina stabilitatea produselor vândute, a crea prețuri, a ajusta sau a forma un sortiment și alte analize la fel de utile ale vânzărilor tale.
Excel utilizează mai multe variante ale acestei funcții de variație:
Teoria matematică
În primul rând, puțin despre teorie, cum puteți descrie funcția de abatere standard în limbaj matematic pentru a o utiliza în Excel, pentru a analiza, de exemplu, datele statisticilor de vânzări, dar mai multe despre asta mai târziu. Vă avertizez imediat, voi scrie o mulțime de cuvinte de neînțeles...)))), dacă ceva mai jos în text, căutați imediat aplicarea practică în program.
Ce face mai exact abaterea standard? Estimează abaterea standard a unei variabile aleatoare X în raport cu așteptările sale matematice pe baza unei estimări imparțiale a varianței sale. De acord, sună confuz, dar cred că elevii vor înțelege despre ce vorbim de fapt!
În primul rând, trebuie să determinăm „abaterea standard”, pentru a calcula ulterior „abaterea standard”, formula ne va ajuta cu aceasta: 
Formula poate fi descrisă după cum urmează: se va măsura în aceleași unități ca și măsurătorile unei variabile aleatoare și este utilizată la calcularea erorii medii aritmetice standard, la construirea intervalelor de încredere, la testarea ipotezelor pentru statistici sau la analizarea unui liniar. relația dintre variabile independente. Funcția este definită ca rădăcina pătrată a varianței variabilelor independente.
Acum putem defini și deviație standard este o analiză a abaterii standard a unei variabile aleatoare X în raport cu perspectiva sa matematică bazată pe o estimare imparțială a varianței acesteia. Formula se scrie astfel: 
Observ că toate cele două estimări sunt părtinitoare. În cazuri generale, nu este posibil să se construiască o estimare imparțială. Dar o estimare bazată pe o estimare a varianței nepărtinitoare va fi consecventă.
Implementare practică în Excel
Ei bine, acum să ne îndepărtăm de teoria plictisitoare și să vedem în practică cum funcționează funcția STANDARDEVAL. Nu voi lua în considerare toate variațiile funcției de abatere standard în Excel; una este suficientă, dar în exemple. De exemplu, să ne uităm la modul în care sunt determinate statisticile privind stabilitatea vânzărilor.
Mai întâi, uitați-vă la ortografia funcției și, după cum puteți vedea, este foarte simplu:
DEVIARE STANDARD.Г(_număr1_;_număr2_; ….), unde:
Acum să creăm un exemplu de fișier și, pe baza acestuia, să analizăm cum funcționează această funcție. 
Întrucât pentru a efectua calcule analitice este necesar să se folosească cel puțin trei valori, ca în principiu în orice analiză statistică, am luat condiționat 3 perioade, acesta ar putea fi un an, un sfert, o lună sau o săptămână. În cazul meu - o lună. Pentru fiabilitate maximă, recomand să luați cât mai multe menstruații, dar nu mai puțin de trei. Toate datele din tabel sunt foarte simple pentru claritatea funcționării și funcționalitatea formulei.
În primul rând, trebuie să calculăm valoarea medie pe lună. Vom folosi funcția MEDIE pentru aceasta și vom obține formula: = MEDIE(C4:E4). 
Acum, de fapt, putem găsi abaterea standard folosind funcția STANDARDEVAL.G, în valoarea căreia trebuie să introducem vânzările produsului pentru fiecare perioadă. Rezultatul va fi o formulă de următoarea formă: =DEVIAȚIA STANDARD.Г(C4;D4;E4). 
Ei bine, jumătate din muncă este făcută. Următorul pas este formarea „Variație”, aceasta se obține prin împărțirea la valoarea medie, abaterea standard și conversia rezultatului în procente. Obținem următorul tabel: 
Ei bine, calculele de bază sunt finalizate, rămâne doar să ne dăm seama dacă vânzările sunt stabile sau nu. Să luăm drept condiție ca abaterile de 10% să fie considerate stabile, de la 10 la 25% acestea sunt abateri mici, dar orice peste 25% nu mai este stabil. Pentru a obține rezultatul conform condițiilor, vom folosi unul logic și pentru a obține rezultatul vom scrie formula:
IF(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))
Toate intervalele sunt luate pentru claritate; sarcinile dvs. pot avea condiții complet diferite. 
Pentru a îmbunătăți vizualizarea datelor, atunci când tabelul dvs. are mii de poziții, ar trebui să profitați de ocazie pentru a aplica anumite condiții de care aveți nevoie sau pentru a evidenția anumite opțiuni cu o schemă de culori, acest lucru va fi foarte clar.
Mai întâi, selectați-le pe cele pentru care veți aplica formatarea condiționată. În panoul de control „Acasă”, selectați „Formatare condiționată”, iar în meniul derulant, selectați „Reguli pentru evidențierea celulelor” și apoi faceți clic pe elementul de meniu „Textul conține...”. Apare o casetă de dialog în care introduceți condițiile dvs.
După ce ați notat condițiile, de exemplu, „stabil” - verde, „normal” - galben și „instabil” - roșu, obținem un tabel frumos și ușor de înțeles în care puteți vedea la ce să acordați atenție mai întâi.
Folosind VBA pentru funcția STDEV.Y
Oricine este interesat își poate automatiza calculele folosind macrocomenzi și poate folosi următoarea funcție:
Funcția MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Pentru fiecare x În Arr aSum = aSum + x "calculați suma elementelor matricei aCnt = aCnt + 1 "calculați numărul de elemente Următorul x aAver = aSum / aCnt "valoare medie pentru fiecare x In Arr tmp = tmp + (x - aAver) ^ 2 "calculați suma pătratelor diferenței dintre elementele matricei și valoarea medie Next x MyStDevP = Sqr(tmp / aCnt ) "calculați STANDARDEV.G() Funcția de sfârșit
Funcția MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# Pentru fiecare x în Arr aSuma = aSuma + x „calculați suma elementelor matricei |
Conceptul de abatere procentuală se referă la diferența dintre două valori numerice ca procent. Să dăm un exemplu concret: să presupunem că într-o zi s-au vândut 120 de tablete dintr-un depozit angro, iar a doua zi - 150 de bucăți. Diferența în volumele vânzărilor este evidentă, încă 30 de tablete au fost vândute a doua zi. Când scădem numărul 120 din 150, obținem o abatere care este egală cu numărul +30. Apare întrebarea: ce este abaterea procentuală?
Cum se calculează abaterea procentuală în Excel
Abaterea procentuală se calculează scăzând valoarea veche din noua valoare și apoi împărțind rezultatul la valoarea veche. Rezultatul calculului acestei formule în Excel ar trebui să fie afișat în format de procent de celule. În acest exemplu, formula de calcul este următoarea (150-120)/120=25%. Formula este ușor de verificat: 120+25%=150.
Notă! Dacă schimbăm numerele vechi și cele noi, atunci vom avea o formulă pentru calcularea markupului.
Figura de mai jos arată un exemplu de prezentare a calculului de mai sus ca o formulă Excel. Formula din celula D2 calculează abaterea procentuală dintre valorile vânzărilor pentru anul curent și anul trecut: =(C2-B2)/B2
Este important să acordați atenție prezenței parantezelor în această formulă. În mod implicit, în Excel, operația de împărțire are întotdeauna prioritate față de operația de scădere. Prin urmare, dacă nu punem paranteze, atunci valoarea va fi mai întâi împărțită, iar apoi o altă valoare va fi scăzută din ea. Un astfel de calcul (fără prezența parantezelor) va fi eronat. Închiderea primei părți a unui calcul într-o formulă cu paranteze crește automat prioritatea operației de scădere deasupra operației de împărțire.
Introduceți formula corect cu paranteze în celula D2, apoi copiați-o pur și simplu în celulele goale rămase din intervalul D2:D5. Pentru a copia formula în cel mai rapid mod, trebuie doar să mutați cursorul mouse-ului la marcatorul cursorului de la tastatură (în colțul din dreapta jos), astfel încât cursorul mouse-ului să se schimbe de la o săgeată la o cruce neagră. Apoi faceți dublu clic cu butonul stâng al mouse-ului și Excel va completa automat celulele goale cu formula și va determina intervalul D2:D5, care trebuie completat până la celula D5 și nu mai mult. Acesta este un hack Excel foarte util.
Formula alternativă pentru calcularea abaterii procentuale în Excel
Într-o formulă alternativă care calculează abaterea relativă a valorilor vânzărilor de la anul curent, se împarte imediat la valorile vânzărilor din anul precedent și numai atunci se scade una din rezultat: =C2/B2-1.
După cum puteți vedea în figură, rezultatul calculării formulei alternative este același cu cel precedent și, prin urmare, corect. Dar formula alternativă este mai ușor de scris, deși pentru unii poate fi mai greu de citit pentru a înțelege principiul funcționării ei. Sau este mai greu de înțeles ce valoare produce o anumită formulă ca urmare a unui calcul dacă nu este semnată.
Singurul dezavantaj al acestei formule alternative este incapacitatea de a calcula abaterea procentuală pentru numerele negative în numărător sau în substitut. Chiar dacă folosim funcția ABS în formulă, formula va returna un rezultat eronat dacă numărul din substitut este negativ.
Deoarece în Excel, în mod implicit, prioritatea operației de împărțire este mai mare decât operația de scădere, nu este nevoie să folosiți paranteze în această formulă.
Abaterea standard este unul dintre acei termeni statistici din lumea corporativă care conferă credibilitate oamenilor care reușesc să o desfășoare bine într-o conversație sau o prezentare, lăsând în același timp o vagă confuzie pentru cei care nu știu ce este, dar sunt prea jenați să o facă. cere. De fapt, majoritatea managerilor nu înțeleg conceptul de abatere standard și dacă ești unul dintre ei, este timpul să nu mai trăiești o minciună. În articolul de astăzi, vă voi spune cum această măsură statistică subapreciată vă poate ajuta să înțelegeți mai bine datele cu care lucrați.
Ce măsoară abaterea standard?
Imaginează-ți că ești proprietarul a două magazine. Și pentru a evita pierderile, este important să aveți un control clar asupra soldurilor stocurilor. În încercarea de a afla care manager gestionează mai bine inventarul, decideți să analizați ultimele șase săptămâni de inventar. Costul mediu săptămânal al stocului pentru ambele magazine este aproximativ același și se ridică la aproximativ 32 de unități convenționale. La prima vedere, scorul mediu arată că ambii manageri au rezultate similare.
Dar dacă te uiți mai atent la activitățile celui de-al doilea magazin, te vei convinge că, deși valoarea medie este corectă, variabilitatea stocului este foarte mare (de la 10 la 58 USD). Astfel, putem concluziona că media nu evaluează întotdeauna corect datele. Aici intervine abaterea standard.
Abaterea standard arată cum sunt distribuite valorile în raport cu media din . Cu alte cuvinte, puteți înțelege cât de mare este răspândirea scurgerii de la o săptămână la alta.
În exemplul nostru, am folosit funcția STDEV din Excel pentru a calcula abaterea standard împreună cu media.
În cazul primului manager, abaterea standard a fost 2. Aceasta ne spune că fiecare valoare din eșantion, în medie, se abate cu 2 de la medie. Este bine? Să privim întrebarea dintr-un unghi diferit - o abatere standard de 0 ne spune că fiecare valoare din eșantion este egală cu media sa (în cazul nostru, 32,2). Astfel, o abatere standard de 2 nu este mult diferită de 0, ceea ce indică faptul că majoritatea valorilor sunt apropiate de medie. Cu cât abaterea standard este mai aproape de 0, cu atât media este mai fiabilă. Mai mult, o abatere standard apropiată de 0 indică o variabilitate redusă a datelor. Adică, o valoare de scurgere cu o abatere standard de 2 indică o consistență incredibilă a primului manager.
În cazul celui de-al doilea magazin, abaterea standard a fost de 18,9. Adică, costul scurgerii se abate în medie cu 18,9 de la valoarea medie de la o săptămână la alta. Răspândire nebună! Cu cât abaterea standard este mai mare de la 0, cu atât media este mai puțin precisă. În cazul nostru, cifra de 18,9 indică faptul că valoarea medie (32,8 USD pe săptămână) pur și simplu nu poate fi de încredere. De asemenea, ne spune că scurgerea săptămânală este foarte variabilă.
Acesta este conceptul de abatere standard pe scurt. Deși nu oferă o perspectivă asupra altor măsurători statistice importante (Mod, Median...), de fapt, abaterea standard joacă un rol crucial în majoritatea calculelor statistice. Înțelegerea principiilor abaterii standard va pune în lumină multe dintre procesele dvs. de afaceri.
Cum se calculează abaterea standard?
Deci acum știm ce spune numărul deviației standard. Să ne dăm seama cum se calculează.
Să ne uităm la setul de date de la 10 la 70 în trepte de 10. După cum puteți vedea, am calculat deja valoarea abaterii standard pentru ele folosind funcția STANDARDEV din celula H2 (în portocaliu).
Mai jos sunt pașii pe care îi face Excel pentru a ajunge la 21.6.
Vă rugăm să rețineți că toate calculele sunt vizualizate pentru o mai bună înțelegere. De fapt, în Excel, calculul are loc instantaneu, lăsând toți pașii în spatele scenei.
În primul rând, Excel găsește media eșantionului. În cazul nostru, media s-a dovedit a fi 40, care în pasul următor este scăzut din valoarea fiecărei probe. Fiecare diferență obținută se pune la pătrat și se însumează. Am obținut o sumă egală cu 2800, care trebuie împărțită la numărul de elemente eșantion minus 1. Deoarece avem 7 elemente, rezultă că trebuie să împărțim 2800 la 6. Din rezultatul obținut găsim rădăcina pătrată, aceasta cifra va fi abaterea standard.
Pentru cei care nu sunt complet clari cu privire la principiul calculării abaterii standard folosind vizualizare, dau o interpretare matematică a găsirii acestei valori.
Funcții pentru calcularea abaterii standard în Excel
Excel are mai multe tipuri de formule de abatere standard. Tot ce trebuie să faci este să tastați =STDEV și veți vedea singur.
Este de remarcat faptul că funcțiile STDEV.V și STDEV.G (prima și a doua funcție din listă) dublează funcțiile STDEV și STDEV (a cincea și, respectiv, a șasea funcție din listă), care au fost reținute pentru compatibilitate cu cele anterioare. versiuni de Excel.
În general, diferența dintre terminațiile funcțiilor .B și .G indică principiul calculării abaterii standard a unui eșantion sau populație. Am explicat deja diferența dintre aceste două matrice în cel precedent.
O caracteristică specială a funcțiilor STANDARDEV și STANDDREV (a treia și a patra funcție din listă) este că atunci când se calculează abaterea standard a unei matrice, se iau în considerare valorile logice și ale textului. Textul și valorile booleene adevărate sunt 1, iar valorile booleene false sunt 0. Nu îmi pot imagina o situație în care aș avea nevoie de aceste două funcții, așa că cred că pot fi ignorate.