Abaterea standard - Excel și Foi de calcul Google

Acest tutorial arată cum să utilizați fișierul Funcția de deviere standard Excel în Excel pentru a calcula abaterea standard pentru o întreagă populație.

DEVIARE STANDARD Prezentare generală a funcției

Funcția STANDARD DEVIATION Calculează calcularea deviației standard pentru o întreagă populație.

Pentru a utiliza funcția Foaie de lucru Excel STANDARD DEVIATION, selectați o celulă și tastați:

(Observați cum apar datele introduse de formulă)

Funcția STANDARD DEVIATION Sintaxă și intrări:

1	= STDEV (număr1, [număr2], …)

numere- Valori pentru a obține varianța standard

Cum se calculează abaterea standard în Excel

Ori de câte ori aveți de-a face cu date, veți dori să efectuați câteva teste de bază pentru a vă ajuta să le înțelegeți. De obicei, veți începe prin calcularea mediei, utilizând funcția Excel MEDIE <>.

Acest lucru vă oferă o idee despre locul în care se află „mijlocul” datelor. Și de acolo, veți dori să vedeți cât de răspândite sunt datele în jurul acestui punct de mijloc. Aici intervine abaterea standard.

Excel vă oferă o serie de funcții pentru a calcula abaterea standard - STDEV, STDEV.P, STDEV.S și DSTDEV. Vom ajunge la toate, dar mai întâi, să aflăm care este abaterea standard este, exact.

Ce este abaterea standard?

Abaterea standard vă oferă o idee despre cât de departe sunt punctele dvs. de date față de medie. Luați următorul set de date de scoruri din 100:

1	48,49,50,51,52

Media acestui set de date este 50 (adunați toate numerele și împărțiți la n, unde n este numărul de valori din interval).

Uită-te acum la următorul set de date:

1	10,25,50,75,90

Media acestui set de date este de asemenea 50 - dar cele două game spun o poveste foarte diferită. Dacă tocmai ați folosit media, ați putea crede că cele două grupuri erau aproximativ egale în aptitudini - și, în medie, sunt.

Dar în primul grup, avem 5 persoane care au obținut scoruri foarte asemănătoare, foarte mediocre. Și în cel de-al doilea grup, am câștigat câțiva zburători mari echilibrați de câțiva marcatori slabi, cu o persoană în mijloc. The răspândire dintre scoruri este foarte diferit, ceea ce face ca interpretarea datelor dvs. să fie foarte diferită.

Abaterea standard este o măsură a acestei răspândiri.

Cum se calculează deviația standard

Pentru a înțelege care este abaterea standard și cum funcționează, vă poate ajuta să treceți manual un exemplu. În acest fel, veți ști ce se întâmplă „sub capotă” odată ce ajungem la funcțiile Excel pe care le puteți utiliza.

Pentru a calcula abaterea standard, lucrați prin acest proces:

1) Calculați media

Să luăm primul nostru set de date de mai sus: 48,49,50,51,52

Știm deja media (50), pe care am confirmat-o aici cu funcția Excel MEDIE <>:

1	= MEDIE (C4: C8)

2) Scădeți media din fiecare valoare din setul de date

Am făcut acest lucru cu următoarea formulă:

1	= C4- $ H $ 4

Media noastră este în H4 și am „blocat” referința celulei punând semnele dolarului înainte de coloană și rând (prin apăsarea F4). Acest lucru înseamnă că pot copia formula în coloană fără actualizarea referinței celulei.

Rezultatul:

Acum, să facem o pauză aici pentru o secundă. Dacă aruncați o privire la noua coloană - veți vedea că numerele de aici se adaugă la zero. Media acestor numere este, de asemenea, zero.

Desigur, răspândirea datelor noastre nu poate fi zero - știm că există unele variații acolo. Avem nevoie de o modalitate de a reprezenta această variație, fără ca media să se dovedească a fi zero.

3) Păstrați diferențele

Putem realiza acest lucru prin echilibrarea diferențelor. Deci, să adăugăm o nouă coloană și să pătratem numerele din coloana D:

1

= D4 * D4

Arată mai bine. Acum avem unele variații, iar cantitatea de variație este legată de cât de departe este fiecare scor de medie.

4) Calculați varianța - media diferențelor pătrate

Următorul pas este de a obține media acestor diferențe pătrate. Există de fapt două modalități de a face acest lucru atunci când calculați abaterea standard.

• Dacă utilizați date despre populație, pur și simplu luați media (însumați valorile și împărțiți la n)
• Dacă utilizați date eșantion, luați suma valorilor și împărțiți la n-1

Datele despre populație înseamnă că aveți „setul complet” de date, de exemplu, aveți date despre fiecare elev dintr-o clasă dată.

Exemple de date înseamnă că nu aveți toate datele, ci doar un eșantion preluat de la o populație mai mare. De obicei, obiectivul dvs. cu date eșantion este să faceți o estimare a valorii în populația mai mare.

Un sondaj de opinie politic este un bun exemplu de date eșantion - cercetătorii cercetează, să zicem, 1.000 de persoane pentru a-și face o idee despre ceea ce gândește o țară sau un stat întreg.

Aici nu avem un eșantion. Avem doar cinci membri ai familiei, cu gânduri statistice, care doresc să calculeze abaterea standard a unui test pe care l-au făcut cu toții. Avem toate punctele de date și nu facem o estimare a unui grup mai mare de oameni. Acestea sunt date despre populație - deci putem lua doar media aici:

1	= MEDIE (E4: E8)

OK, deci avem 2. Acest scor este cunoscut sub numele de „varianță” și este punctul de bază pentru o mulțime de teste statistice, inclusiv abaterea standard. Puteți citi mai multe despre varianță pe pagina sa principală: cum se calculează varianța în Excel <>.

5) Obțineți rădăcina pătrată a varianței

Ne-am pătrat numerele mai devreme, ceea ce evident umflă puțin valorile. Deci, pentru a readuce cifra în concordanță cu diferențele reale ale scorurilor față de medie, trebuie să înrădăcinăm rezultatul pasului 4:

1	= SQRT (H4)

Și avem rezultatul nostru: abaterea standard este 1.414

Deoarece ne-am înrădăcinat pătratele numerele pătrate anterior, abaterea standard dată în aceleași unități ca și datele originale. Deci, abaterea standard este de 1.414 puncte de testare.

Abaterea standard atunci când datele sunt mai răspândite

Anterior, am avut un al doilea exemplu de date: 10,25,50,75,90

Doar pentru distracție, să vedem ce se întâmplă atunci când calculăm abaterea standard pe aceste date:

Toate formulele sunt exact la fel ca înainte (rețineți că media generală este încă 50).

Singurul lucru care s-a schimbat a fost răspândirea scorurilor în coloana C. Dar acum, abaterea noastră standard este mult mai mare, la 29.832 puncte de test.

Desigur, din moment ce avem doar 5 puncte de date, este foarte ușor să vedem că răspândirea scorurilor este diferită între cele două seturi. Dar când aveți 100 sau 1000 de puncte de date, nu vă puteți da seama doar scanând rapid datele. Și tocmai de aceea folosim abaterea standard.

Funcțiile Excel pentru a calcula abaterea standard

Acum, că știți cum funcționează abaterea standard, nu este nevoie să parcurgeți întregul proces pentru a ajunge la abaterea standard. Puteți utiliza una dintre funcțiile încorporate Excel.

Excel are mai multe funcții în acest scop:

• P calculează abaterea standard pentru datele populației (folosind metoda exactă pe care am folosit-o în exemplul de mai sus)
• S calculează abaterea standard pentru datele eșantionului (folosind metoda n-1 pe care am atins-o mai devreme)
• STDEV este exact la fel ca STDEV.S. Aceasta este o funcție mai veche care a fost înlocuită de STDEV.S și STDEV.P.
• STDEVA este foarte asemănător cu STDEV.S, cu excepția faptului că include celule text și celule booleene (TRUE / FALSE) la efectuarea calculului.
• STDEVPA este foarte asemănător cu STDEV.P, cu excepția faptului că include celule text și celule booleene (TRUE / FALSE) la efectuarea calculului.

Uau, multe opțiuni aici! Nu vă lăsați intimidați - în marea majoritate a cazurilor, veți utiliza fie STDEV.P, fie STDEV.S.

Să parcurgem fiecare dintre acestea la rândul nostru, începând cu STDEV.P, deoarece aceasta este metoda pe care tocmai am lucrat-o.

Funcția Excel STDEV.P

STDEV.P calculează abaterea standard pentru datele populației. Îl folosești astfel:

1	= STDEV.P (C4: C8)

Definiți un argument în STDEV.P: intervalul de date pentru care doriți să calculați abaterea standard.

Acesta este același exemplu pe care l-am parcurs pas cu pas mai sus când am calculat abaterea standard cu mâna. Și, după cum puteți vedea mai sus, obținem exact același rezultat - 1.414.

Notă STDEV.P ignoră orice celule care conțin text sau valori booleene (TRUE / FALSE). Dacă trebuie să le includeți, utilizați STDEVPA.

Funcția Excel STDEV.S

STDEV.S calculează abaterea standard pentru datele eșantionului. Folosiți-l astfel:

1	= STDEV.S (C4: C8)

Din nou, este nevoie de un argument - gama de date pentru care doriți să cunoașteți abaterea standard.

Înainte de a intra într-un exemplu, să discutăm diferența dintre STDEV.S și STDEV.P.

Așa cum am discutat deja, STDEV.S ar trebui utilizat pe eșantion de date - atunci când datele dvs. fac parte dintr-un set mai mare. Deci, să presupunem acum că, în exemplul nostru de mai sus, mai mulți oameni au susținut testul. Vrem să estimăm abaterea standard a tuturor celor care au susținut testul, folosind doar aceste cinci scoruri. Acum folosim eșantion de date.

Acum, calculul diferă de pasul (4) de mai sus, atunci când calculăm varianța - media diferenței pătrate a fiecărui scor din media generală.

În loc să folosim metoda normală - însumăm toate valorile și împărțim la n, am însuma toate valorile și le împărțim la n-1:

1	= SUM (E4: E8) / (COUNT (E4: E8) -1)

În această formulă:

• SUM obține suma diferențelor pătrate
• COUNT returnează n-ul nostru, din care scădem 1
• Apoi ne împărțim suma prin n-1

De data aceasta, media diferențelor pătrate este de 2,5 (vă amintiți că a fost 2 anterior, deci este puțin mai mare).

Deci, de ce împărțim la n-1 în loc de n atunci când avem de-a face cu eșantionul de date?

Răspunsul este destul de complex și, dacă încercați doar să vă rulați numerele pentru a vă înțelege datele, nu este ceva de care trebuie să vă preocupați cu adevărat. Asigurați-vă că folosiți STDEV.S pentru date eșantion și STDEV.P pentru date despre populație și veți fi bine.

Dacă sunteți curioși să aflați de ce, consultați pagina principală despre cum să calculați varianța în Excel <>.

OK, deci avem varianța pentru eșantion acum, deci, pentru a obține abaterea standard pentru eșantion, vom obține doar rădăcina pătrată a varianței:

1	= SQRT (H4)

Obținem 1.581.

STDEV.S face toate calculele de mai sus pentru noi și returnează abaterea standard eșantion într-o singură celulă. Deci, să vedem cu ce vine …

1	= STDEV.S (C4: C8)

Da, din nou 1.581.

Funcția Excel STDEV

Funcția STDEV a Excel funcționează exact în același mod ca STDEV.S - adică calculează abaterea standard pentru un eșantion de date.

Îl folosiți în același mod:

1	= STDEV (C4: C8)

Din nou, obținem același rezultat.

Notă importantă: STDEV este o „Funcție de compatibilitate”, ceea ce înseamnă practic că Microsoft scapă de ea. Încă funcționează deocamdată, așa că orice foi de calcul mai vechi vor continua să funcționeze normal. Dar în versiunile viitoare de Excel, Microsoft ar putea să-l renunțe complet, deci ar trebui să utilizați STDEV.S în loc de STDEV ori de câte ori este posibil.

Funcția Excel STDEVA

STDEVA este, de asemenea, utilizat pentru a calcula abaterea standard pentru un eșantion, dar are câteva diferențe importante despre care trebuie să știți:

• Valorile TRUE sunt numărate ca 1
• Valorile FALSE sunt numărate ca 0
• Șirurile de text sunt numărate ca 0

Folosiți-l după cum urmează:

1	= STDEVA (C4: C8)

Încă patru prieteni și membri ai familiei au dat rezultatele testelor. Acestea sunt prezentate în coloana C, iar coloana D indică modul în care STDEVA interpretează aceste date.

Deoarece aceste celule sunt interpretate ca valori atât de scăzute, acest lucru creează o răspândire mult mai largă între datele noastre decât am văzut înainte, ceea ce a crescut foarte mult abaterea standard, acum la 26.246.

Funcția Excel STDEVPA

STDEVPA calculează abaterea standard pentru o populație în același mod ca STDEV.P. Cu toate acestea, include și valori booleene și șiruri de text în calcul, care sunt interpretate după cum urmează:

• Valorile TRUE sunt numărate ca 1
• Valorile FALSE sunt numărate ca 0
• Șirurile de text sunt numărate ca 0

Îl folosești astfel:

1	= STDEVPA (C4: C12)

Filtrarea datelor înainte de calcularea abaterii standard

În lumea reală, nu veți avea întotdeauna datele exacte de care aveți nevoie într-un tabel frumos ordonat. Adesea, veți avea o foaie de calcul mare, plină de date, pe care va trebui să o filtrați înainte de a calcula abaterea standard.

Puteți face acest lucru foarte ușor cu funcțiile bazei de date Excel: DSTDEV (pentru eșantioane) și DSTDEVP (pentru populații).

Aceste funcții vă permit să creați un tabel de criterii, în care puteți defini toate filtrele de care aveți nevoie. Funcțiile aplică aceste filtre în culise înainte de a returna abaterea standard. În acest fel, nu este nevoie să atingeți un autofiltru sau să extrageți datele într-o foaie separată - DSTDEV și SDTDEVP pot face toate acestea pentru dvs.

Aflați mai multe pe pagina principală pentru funcțiile Excel DSTDEV și DSTDEVP <>.

FUNCȚIONARE STANDARD Funcție în Foi de calcul Google

Funcția STANDARD DEVIATION funcționează exact la fel în Foi de calcul Google ca în Excel: