Abaterea standard a funcției în excel. Ce este abaterea standard - folosind funcția de abatere standard pentru a calcula abaterea standard în Excel

Unul dintre principalele instrumente de analiză statistică este calculul abaterii standard. Acest indicator vă permite să faceți o estimare a abaterii standard pentru un eșantion sau pentru populația generală. Să învățăm cum să folosim formula abaterii standard în Excel.

Să definim imediat care este abaterea standard și cum arată formula ei. Această valoare este rădăcina pătrată a mediei aritmetice a pătratelor diferenței dintre toate valorile seriei și media lor aritmetică. Există un nume identic pentru acest indicator - abatere standard. Ambele nume sunt complet echivalente.

Dar, desigur, în Excel, utilizatorul nu trebuie să calculeze acest lucru, deoarece programul face totul pentru el. Să învățăm cum să calculăm abaterea standard în Excel.

Calculul in Excel

Puteți calcula valoarea specificată în Excel folosind două funcții speciale STDEV.V(conform eșantionului) și STDEV.G(după populația generală). Principiul funcționării lor este absolut același, dar ele pot fi numite în trei moduri, pe care le vom discuta mai jos.

Metoda 1: Expertul funcției

Metoda 2: fila Formule

Metoda 3: Introducerea manuală a formulei

Există, de asemenea, o modalitate prin care nu trebuie să apelați deloc fereastra argumentului. Pentru a face acest lucru, introduceți manual formula.

După cum puteți vedea, mecanismul de calcul al abaterii standard în Excel este foarte simplu. Utilizatorul trebuie doar să introducă numere din populație sau link-uri către celulele care le conțin. Toate calculele sunt efectuate de programul însuși. Este mult mai dificil de înțeles care este indicatorul calculat și cum rezultatele calculului pot fi aplicate în practică. Dar înțelegerea acestui lucru aparține deja mai mult domeniului statisticilor decât învățării cum să lucrezi cu software.

Dispersia este o măsură a dispersiei care descrie abaterea relativă dintre valorile datelor și medie. Este cea mai folosită măsură a dispersiei în statistică, calculată prin însumarea, la pătrat, a abaterii fiecărei valori de date de la medie. Formula de calcul a varianței este prezentată mai jos:

s 2 - varianța eșantionului;

x cf este valoarea medie a probei;

n — dimensiunea eșantionului (număr de valori ale datelor),

(x i – x cf) este abaterea de la valoarea medie pentru fiecare valoare a setului de date.

Pentru a înțelege mai bine formula, să ne uităm la un exemplu. Nu prea îmi place să gătesc, așa că o fac rar. Totuși, pentru a nu muri de foame, din când în când trebuie să merg la aragaz să pun în aplicare planul de a-mi satura corpul cu proteine, grăsimi și carbohidrați. Setul de date de mai jos arată de câte ori Renat gătește mâncare în fiecare lună:

Primul pas în calcularea varianței este determinarea mediei eșantionului, care în exemplul nostru este de 7,8 ori pe lună. Calculele rămase pot fi facilitate cu ajutorul următorului tabel.

Faza finală a calculării varianței arată astfel:

Pentru cei cărora le place să facă toate calculele dintr-o singură mișcare, ecuația va arăta astfel:

Folosind metoda numărării crude (exemplu de gătit)

Există o modalitate mai eficientă de a calcula varianța, cunoscută sub numele de metoda „numărării brute”. Deși la prima vedere ecuația poate părea destul de greoaie, de fapt nu este atât de înfricoșătoare. Puteți verifica acest lucru și apoi decideți ce metodă vă place cel mai mult.

este suma fiecărei valori de date după pătrat,

este pătratul sumei tuturor valorilor datelor.

Nu-ți pierde mințile chiar acum. Să punem totul sub formă de tabel și apoi veți vedea că aici sunt mai puține calcule decât în ​​exemplul anterior.

După cum puteți vedea, rezultatul este același ca atunci când utilizați metoda anterioară. Avantajele acestei metode devin evidente pe măsură ce dimensiunea eșantionului (n) crește.

Calcularea variației în Excel

După cum probabil ați ghicit deja, Excel are o formulă care vă permite să calculați varianța. Mai mult, începând cu Excel 2010, puteți găsi 4 varietăți ale formulei de dispersie:

1) VAR.V - Returnează varianța eșantionului. Valorile booleene și textul sunt ignorate.

2) VAR.G - Returnează varianța populației. Valorile booleene și textul sunt ignorate.

3) VASP - Returnează varianța eșantionului, luând în considerare valorile booleene și text.

4) VARP - Returnează varianța populației, ținând cont de valorile logice și de text.

În primul rând, să ne uităm la diferența dintre un eșantion și o populație. Scopul statisticilor descriptive este de a rezuma sau de a afișa datele în așa fel încât să obțineți rapid o imagine de ansamblu, ca să spunem așa, o imagine de ansamblu. Inferența statistică vă permite să faceți inferențe despre o populație pe baza unui eșantion de date din această populație. Populația reprezintă toate rezultatele sau măsurătorile posibile care ne interesează. Un eșantion este un subset al unei populații.

De exemplu, suntem interesați de totalitatea unui grup de studenți de la una dintre universitățile ruse și trebuie să stabilim scorul mediu al grupului. Putem calcula performanța medie a elevilor, iar apoi cifra rezultată va fi un parametru, deoarece întreaga populație va fi implicată în calculele noastre. Totuși, dacă dorim să calculăm GPA-ul tuturor studenților din țara noastră, atunci acest grup va fi eșantionul nostru.

Diferența în formula de calcul a variației dintre eșantion și populație este la numitor. Unde pentru eșantion va fi egal cu (n-1), iar pentru populația generală doar n.

Acum să ne ocupăm de funcțiile de calcul a varianței cu terminații DAR,în descrierea căreia se spune că calculul ia în considerare valorile de text și logice. În acest caz, atunci când se calculează varianța unui anumit set de date în care apar valori non-numerice, Excel va interpreta textul și valorile booleene false ca 0, iar valorile booleene adevărate ca 1.

Deci, dacă aveți o serie de date, nu va fi dificil să calculați varianța acesteia folosind una dintre funcțiile Excel enumerate mai sus.

Dintre numeroșii indicatori care sunt utilizați în statistică, este necesar să se evidențieze calculul varianței. Trebuie remarcat faptul că efectuarea manuală a acestui calcul este o sarcină destul de obositoare. Din fericire, în Excel există funcții care vă permit să automatizați procedura de calcul. Să aflăm algoritmul pentru lucrul cu aceste instrumente.

Dispersia este un indicator al variației, care este pătratul mediu al abaterilor de la așteptarea matematică. Astfel, exprimă răspândirea numerelor despre medie. Calculul dispersiei poate fi efectuat atât pentru populația generală, cât și pentru eșantion.

Metoda 1: calcul pe populația generală

Pentru a calcula acest indicator în Excel pentru populația generală, se utilizează funcția DISP.G. Sintaxa acestei expresii este următoarea:

DISP.G(Număr1;Număr2;…)

În total, pot fi aplicate de la 1 la 255 de argumente. Argumentele pot fi atât valori numerice, cât și referințe la celulele în care sunt conținute.

Să vedem cum să calculăm această valoare pentru un interval de date numerice.

Metoda 2: calculul eșantionului

Spre deosebire de calculul valorii pentru populația generală, în calculul pentru eșantion, numitorul nu este numărul total de numere, ci unul mai puțin. Acest lucru se face pentru a corecta eroarea. Excel ține cont de această nuanță într-o funcție specială care este concepută pentru acest tip de calcul - DISP.V. Sintaxa sa este reprezentată de următoarea formulă:

VAR.B(Număr1;Număr2;…)

Numărul de argumente, ca și în funcția anterioară, poate varia de la 1 la 255.

După cum puteți vedea, programul Excel este capabil să faciliteze foarte mult calculul varianței. Această statistică poate fi calculată prin aplicație atât pentru populație, cât și pentru eșantion. În acest caz, toate acțiunile utilizatorului sunt de fapt reduse doar la specificarea intervalului de numere care trebuie procesate, iar Excel își face singur treaba principală. Desigur, acest lucru va economisi o cantitate semnificativă de timp pentru utilizatori.

Conceptul de procent de abatere implică diferența dintre două valori numerice în procente. Să dăm un exemplu concret: să presupunem că într-o zi s-au vândut 120 de bucăți de tablete din depozitul angro, iar a doua zi - 150 de bucăți. Diferența de volume de vânzări este evidentă, cu încă 30 de tablete vândute a doua zi. Când scădem numărul 120 din 150, obținem abaterea, care este egală cu numărul +30. Apare întrebarea: care este abaterea procentuală?

Cum se calculează abaterea procentuală în Excel

Procentul de abatere se calculează scăzând valoarea veche din noua valoare și apoi împărțind rezultatul la valoarea veche. Rezultatul calculării acestei formule în Excel ar trebui să fie afișat în formatul procentual al celulei. În acest exemplu, formula de calcul arată astfel (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ă variația procentuală dintre vânzările curente și cele de anul trecut: =(C2-B2)/B2

Este important să acordați atenție prezenței parantezelor în această formulă. În mod implicit, în Excel, împărțirea are întotdeauna prioritate față de scădere. Prin urmare, dacă nu punem paranteze, atunci valoarea va fi împărțită mai întâi, iar apoi o altă valoare va fi scăzută din ea. Un astfel de calcul (fără paranteze) va fi eronat. Închiderea primei părți a calculului într-o formulă cu paranteze crește automat precedența operației de scădere mai mare față de operația de împărțire.

Corect cu paranteze, introduceți formula în celula D2, apoi copiați-o pur și simplu în restul celulelor goale 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ă. După aceea, faceți dublu clic pe butonul stâng al mouse-ului și Excel va completa automat celulele goale cu formula, în timp ce va determina intervalul D2:D5, care trebuie completat până la celula D5 și nu mai mult. Acesta este un truc de viață foarte util în Excel.



Formula alternativă pentru a calcula procentul de abatere în Excel

Într-o formulă alternativă care calculează abaterea relativă a valorilor vânzărilor de la anul curent, împărțiți imediat la valorile vânzărilor din anul precedent și numai atunci se scade una din rezultat: \u003d C2 / B2-1.

După cum puteți vedea în figură, rezultatul calculării formulei alternative este același cu cel precedent, ceea ce înseamnă că este corect. Dar formula alternativă este mai ușor de scris, deși poate fi mai greu pentru cineva să o citească pentru a înțelege cum funcționează. Sau este mai greu de înțeles ce valoare produce formula dată ca rezultat al calculului dacă nu este semnată.

Singurul dezavantaj al acestei formule alternative este incapacitatea de a calcula abaterea procentuală pentru numerele negative din numărător sau înlocuitor. Chiar dacă folosim funcția ABS în formulă, formula va returna un rezultat eronat cu un număr negativ în substituent.

Deoarece în Excel, în mod implicit, operatorul de împărțire are prioritate față de operatorul de scădere, nu este nevoie să folosiți paranteze în această formulă.

Programul Excel este foarte apreciat atât de profesioniști, cât și de amatori, deoarece un utilizator de orice nivel de pregătire poate lucra cu el. De exemplu, oricine are abilități minime de „comunicare” cu Excel poate să deseneze un grafic simplu, să facă un semn decent etc.

În același timp, acest program vă permite chiar să efectuați diferite tipuri de calcule, de exemplu, calcule, dar acest lucru necesită deja un nivel ușor diferit de pregătire. Cu toate acestea, dacă tocmai ați început o cunoaștere apropiată a acestui program și sunteți interesat de tot ceea ce vă va ajuta să deveniți un utilizator mai avansat, acest articol este pentru dvs. Astăzi vă voi spune care este formula deviației standard în excel, de ce este necesară deloc și, de fapt, când este aplicată. Merge!

Ce este

Să începem cu teorie. Deviația standard se numește de obicei rădăcină pătrată, obținută din media aritmetică a tuturor diferențelor pătrate dintre valorile disponibile, precum și din media lor aritmetică. Apropo, această valoare este de obicei numită litera greacă „sigma”. Abaterea standard este calculată folosind formula STDEV, respectiv, programul o face pentru utilizator.

Esența acestui concept este identificarea gradului de variabilitate a instrumentului, adică este, în felul său, un indicator din statistica descriptivă. Dezvăluie modificări ale volatilității instrumentului în orice perioadă de timp. Folosind formulele STDEV, puteți estima abaterea standard a unui eșantion, în timp ce valorile booleene și text sunt ignorate.

Formulă

Ajută la calcularea abaterii standard în formula Excel, care este furnizată automat în Excel. Pentru a-l găsi, trebuie să găsiți secțiunea de formule în Excel și deja acolo selectați-o pe cea care are numele STDEV, așa că este foarte simplu.

După aceea, în fața dvs. va apărea o fereastră în care va trebui să introduceți date pentru calcul. În special, două numere trebuie introduse în câmpuri speciale, după care programul va calcula automat abaterea standard pentru eșantion.

Fără îndoială, formulele și calculele matematice sunt o problemă destul de complicată și nu toți utilizatorii o pot rezolva imediat. Cu toate acestea, dacă săpați puțin mai adânc și înțelegeți problema puțin mai în detaliu, se dovedește că nu totul este atât de trist. Sper că sunteți convins de acest lucru prin exemplul de calcul a abaterii standard.

Video pentru a ajuta