Formula pentru abaterea standard în excel. Calculul dispersiei, abaterea medie pătratică (standard), coeficientul de variație în Excel

Varianta este o măsură a dispersiei care descrie abaterea comparativă între valorile datelor și medie. Este cea mai utilizată măsură a dispersiei în statistică, calculată prin însumarea și pătrarea 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 av — medie eșantionului;

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

(x i – x avg) 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 gătește Renat î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ă. Restul calculelor pot fi simplificate folosind următorul tabel.

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

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

Utilizarea metodei de numărare a crudității (exemplu de gătit)

Mai sunt metoda eficienta calculul varianței, cunoscut sub numele de metoda „numărării brute”. Deși ecuația poate părea destul de greoaie la prima vedere, de fapt nu este chiar atât de înfricoșătoare. Puteți să vă asigurați de acest lucru și apoi să 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 toate acestea într-un tabel și veți vedea că aici sunt mai puține calcule decât în ​​exemplul anterior.

După cum puteți vedea, rezultatul a fost același ca atunci când ați folosit metoda anterioară. Avantaje aceasta metoda devin evidente pe măsură ce dimensiunea eșantionului (n) crește.

Calculul 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 tipuri de formule de variație:

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

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

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

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

În primul rând, să înțelegem diferența dintre un eșantion și o populație. Scopul statisticilor descriptive este de a rezuma sau de a afișa date astfel încât să obțineți rapid imaginea de ansamblu, o privire de ansamblu, ca să spunem așa. Inferența statistică vă permite să faceți inferențe despre o populație pe baza unui eșantion de date din acea 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 un 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 varianței dintre un eșantion și o populație este numitorul. Unde pentru eșantion va fi egal cu (n-1), iar pentru populația generală doar n.

Acum să ne uităm la funcțiile pentru calcularea varianței cu terminații A, a cărui descriere afirmă că textul și valorile logice sunt luate în considerare în calcul. Î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 fiind egale cu 0, iar valorile booleene adevărate ca fiind egale cu 1.

Deci, dacă aveți o matrice de date, calcularea varianței acesteia nu va fi dificilă folosind una dintre funcțiile Excel enumerate mai sus.

Statisticile utilizează un număr mare de indicatori, iar unul dintre ei este calcularea variației în Excel. Dacă faci asta manual, va dura mult timp și poți face multe greșeli. Astăzi vom vedea cum să descompunem formulele matematice în funcții simple. Să ne uităm la unele dintre cele mai simple, mai rapide și mai convenabile metode de calcul care vă vor permite să faceți totul în câteva minute.

Calculați varianța

Varianta unei variabile aleatoare este așteptarea matematică a abaterii pătrate a unei variabile aleatoare de la așteptarea ei matematică.

Calculăm pe baza populației generale

Pentru a calcula covorașul. Se așteaptă ca programul să folosească funcția DISP.G, iar sintaxa acesteia arată astfel: „=DISP.G(Number1;Number2;…)”.

Pot fi folosite maxim 255 de argumente, nu mai mult. Argumentele pot fi numere prime sau referințe la celulele în care sunt specificate. Să ne uităm la cum se calculează varianța în Microsoft Excel:

1. Primul pas este să selectați celula în care va fi afișat rezultatul calculului, apoi să faceți clic pe butonul „Inserare funcție”.

2. Se va deschide shell-ul de gestionare a funcției. Acolo trebuie să căutați funcția „DISP.G”, care poate fi în categoria „Statistică” sau „Lista alfabetică completă”. Când este găsit, selectați-l și faceți clic pe „OK”.

3. Se va deschide o fereastră cu argumentele funcției. În ea trebuie să selectați linia „Numărul 1” și pe foaie selectați intervalul de celule cu seria de numere.

4. După aceasta, rezultatele calculului vor fi afișate în celula în care a fost introdusă funcția.

Acesta este modul în care puteți găsi cu ușurință variația în Excel.

Facem calcule pe baza eșantionului

În acest caz, varianța eșantionului în Excel este calculată cu numitorul indicând nu numărul total de numere, ci cu unul mai puțin. Acest lucru se face pentru o eroare mai mică utilizând funcția specială DISP.V, a cărei sintaxă este =DISP.V(Number1;Number2;...). Algoritmul acțiunilor:

• Ca și în metoda anterioară, trebuie să selectați o celulă pentru rezultat.
• În Expertul de funcții, ar trebui să găsiți „DISP.B” în categoria „Lista alfabetică completă” sau „Statistică”.

• În continuare, va apărea o fereastră și ar trebui să procedați în același mod ca în metoda anterioară.

Video: Calcularea variației în Excel

Concluzie

Varianta în Excel este calculată foarte simplu, mult mai rapid și mai convenabil decât să o faci manual, deoarece funcția de așteptare matematică este destul de complexă, iar calcularea acesteia poate dura mult timp și efort.

Programul Excel este foarte apreciat atât de profesioniști, cât și de amatori, deoarece utilizatorii de orice nivel de calificare pot lucra cu el. De exemplu, oricine are abilități minime de „comunicare” în Excel poate să deseneze un grafic simplu, să facă o placă 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ă un nivel ușor diferit de pregătire. Cu toate acestea, dacă tocmai ați început să cunoașteți îndeaproape acest 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 abaterii standard în Excel, de ce este necesară deloc și, strict vorbind, când este folosită. Merge!

Ce este

Să începem cu teoria. Abaterea standard se numește de obicei rădăcină pătrată obținută din media aritmetică a tuturor diferențelor pătrate dintre cantitățile disponibile, precum și media lor aritmetică. Apropo, această cantitate este de obicei numită Literă greacă„sigma”. Abaterea standard este calculată folosind formula STANDARDEVAL în consecință, programul face acest lucru pentru utilizator.

Esența acestui concept este identificarea gradului de variabilitate al unui instrument, adică este, în felul său, un indicator derivat din statistica descriptivă. Identifică modificările volatilității unui instrument în orice perioadă de timp. Folosind formulele DEVIARE STANDARD, puteți estima abaterea standard a eșantionului, în timp ce este logic și valorile textului sunt ignorate.

Formulă

Formula care este furnizată automat în Excel ajută la calcularea abaterii standard în Excel. Pentru a-l găsi, trebuie să găsiți secțiunea de formule în Excel și apoi să o selectați pe cea numită STANDARDEVAL, așa că este foarte simplu.

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

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

Video pentru a ajuta

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

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

Dar, firește, î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(pe baza populației eșantionului) și STDEV.G(pe baza populației generale). 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: Tabul Formule

Metoda 3: Introducerea manuală a formulei

Există, de asemenea, o modalitate prin care nu va trebui să apelați deloc fereastra de argumente. Pentru a face acest lucru, trebuie să 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 referințe la 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 se referă deja mai mult la domeniul statisticii decât la învățarea să lucreze cu software.

Coeficientul de variație este o comparație a dispersiei a două valori aleatorii. Cantitatile au unitati de masura, ceea ce duce la un rezultat comparabil. Acest coeficient este necesar pentru pregătirea analizei statistice.

Cu el, investitorii pot calcula indicatorii de riscînainte de a face investiții în activele selectate. Este util atunci când activele selectate au randamente și grade de risc diferite. De exemplu, un activ poate avea un venit mare și un grad ridicat de risc, în timp ce altul, dimpotrivă, poate avea un venit scăzut și un grad corespunzător de risc mai mic.

Calculul abaterii standard

Abaterea standard este o valoare statistică. Prin calcularea acestei valori, utilizatorul va primi informații despre cât de mult se abate datele într-o direcție sau alta față de valoarea medie. Abaterea standard în Excel este calculată în mai mulți pași.

Pregătiți datele: deschide pagina unde vor avea loc calculele. În cazul nostru, aceasta este o imagine, dar ar putea fi orice alt fișier. Principalul lucru este să colectați informațiile pe care le veți folosi în tabel pentru calcul.

Introduceți datele în orice editor de foi de calcul (în cazul nostru Excel), completând celulele de la stânga la dreapta. Ar trebui să înceapă din coloana „A”. Introduceți titluri în rândul din partea de sus și numele în aceleași coloane care se referă la titluri, doar mai jos. Apoi data și datele să fie calculate în dreapta datei.

Salveaza acest document.

Acum să trecem la calculul în sine. Selectați o celulă cu cursorul după ultima valoare introdusă mai jos.

Introdu semnul „=” și introduceți formula de mai jos. Este necesar semnul egal. În caz contrar, programul nu va calcula datele propuse. Formula este introdusă fără spații.

Utilitarul va afișa numele mai multor formule. Selectați " DEVIAȚIE STANDARD" Aceasta este formula de calcul a abaterii standard. Există două tipuri de calcule:

• cu calcul eșantion;
• cu calcul pe baza populatiei generale.

Selectând unul dintre ele, specificați intervalul de date. Întreaga formulă introdusă va arăta astfel: „=STDEV (B2: B5).”

Apoi faceți clic pe butonul „ introduce" Datele primite vor apărea în elementul marcat.

Calculul mediei aritmetice

Calculat atunci când utilizatorul trebuie să creeze un raport, de exemplu, de salariileîn compania lui. Acest lucru se face după cum urmează:

• va fi doar selectați intervalulși faceți clic pe butonul „Enter”. Și celula va afișa acum rezultatul din datele preluate mai sus.

Calculul coeficientului de variație

Formula pentru calcularea coeficientului de variație:

V= S/X, unde S este abaterea standard și X este media.

Pentru a calcula coeficientul de variație în Excel, trebuie să găsiți abaterea standard și media aritmetică. Adică, după finalizarea primelor două calcule, care au fost arătate mai sus, puteți trece la lucrul la coeficientul de variație.

Pentru a face acest lucru, deschideți Excel, completați două câmpuri în care ar trebui să introduceți numerele rezultate ale abaterii standard și valoarea medie.

Acum selectați celula care este alocată numărului pentru a calcula variația. Deschide fila " Acasă„dacă nu este deschis. Faceți clic pe instrumentul " Număr" Selectați formatul procentual.

Accesați celula marcată și faceți dublu clic pe ea. Apoi introduceți semnul egal și evidențiați elementul în care este introdusă abaterea standard totală. Apoi faceți clic pe butonul „slash” sau „split” de pe tastatură (arată astfel: „/”). Selectați elementul, unde este introdusă media aritmetică și faceți clic pe butonul „Enter”. Ar trebui să arate așa:

Și iată rezultatul după apăsarea „Enter”:

De asemenea, puteți utiliza calculatoare online pentru a calcula coeficientul de variație, de exemplu planetcalc.ru și allcalc.ru. Este suficient să introduceți numerele necesare și să începeți calculul, după care veți primi informațiile necesare.

Deviație standard

Abaterea standard în Excel este rezolvată folosind două formule:

Cu cuvinte simple, se extrage rădăcina varianței. Cum se calculează varianța este discutat mai jos.

Abaterea standard este sinonimă cu abaterea standard și se calculează exact. Celula pentru rezultat sub numerele care trebuie calculate este evidențiată. Este inserată una dintre funcțiile prezentate în figura de mai sus. Se face clic pe butonul „”. introduce" Rezultatul a fost primit.

Coeficient de oscilație

Raportul dintre intervalul de variație și medie se numește coeficient de oscilație. Nu există formule gata făcute în Excel, așa că trebuie asamblate mai multe funcții într-una singură.

Funcțiile care trebuie puse împreună sunt formulele medie, maximă și minimă. Acest coeficient este utilizat pentru a compara un set de date.

Dispersia

Varianta este o functie prin care caracterizează răspândirea datelorîn jurul așteptărilor matematice. Calculat folosind următoarea ecuație:

Variabilele iau următoarele valori:

Excel are două funcții care determină varianța:

Pentru a face un calcul, o celulă este evidențiată sub numerele care trebuie calculate. Accesați fila funcție de inserare. Selecteaza categoria " Statistic" Selectați una dintre funcții din lista derulantă și faceți clic pe butonul „Enter”.

Maxim și minim

Sunt necesare maxime și minime pentru a nu căuta manual într-un număr mare de numere numărul minim sau maxim.

Pentru a calcula maximul, selectați întreaga gamă numerele necesare în tabel și o celulă separată, apoi faceți clic pe „Σ” sau „ Autosumă" În fereastra care apare, selectați „Maximum” și apăsând butonul „Enter” obțineți valoarea dorită.

Faceți același lucru pentru a obține minimul. Doar selectați funcția „Minim”.