Cum se calculează abaterea standard în Excel. Aplicație statistică. Determinarea mediei populației

Acasă

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 de volume de vânzări este evidentă a doua zi s-au vândut încă 30 de tablete. 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. Fiţi atenți!

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 cel mai mult formulaîntr-un mod rapid

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 Excel are prioritate implicită pentru operația de împărțire față de operația de scădere, nu este nevoie să folosiți paranteze în această formulă.

Pentru a găsi valoarea medie în Excel (indiferent dacă este o valoare numerică, text, procentuală sau altă valoare), există multe funcții. Și fiecare dintre ele are propriile sale caracteristici și avantaje. Într-adevăr, în această sarcină pot fi stabilite anumite condiții.

De exemplu, valorile medii ale unei serii de numere în Excel sunt calculate folosind funcții statistice. De asemenea, puteți introduce manual propria formulă. Să luăm în considerare diferite opțiuni.

Cum se găsește media aritmetică a numerelor?

Pentru a găsi media aritmetică, trebuie să adunați toate numerele din mulțime și să împărțiți suma la cantitate. De exemplu, notele unui student la informatică: 3, 4, 3, 5, 5. Ce este inclus în trimestru: 4. Am găsit media aritmetică folosind formula: =(3+4+3+5+5) /5.

Cum să faci asta rapid folosind funcțiile Excel? Să luăm de exemplu o serie de numere aleatorii dintr-un șir:

Sau: faceți celula activă și introduceți pur și simplu formula manual: =AVERAGE(A1:A8).

Acum să vedem ce mai poate face funcția AVERAGE.

Să găsim media aritmetică a primelor două și a ultimelor trei numere. Formula: =MEDIA(A1:B1;F1:H1). Rezultat:

Stare medie

Condiția pentru aflarea mediei aritmetice poate fi un criteriu numeric sau unul text. Vom folosi funcția: =AVERAGEIF().

Aflați media aritmetică a numerelor care sunt mai mari sau egale cu 10.

Funcție: =AVERAGEIF(A1:A8,">=10")

Al treilea argument – ​​„Intervalul mediu” – este omis. În primul rând, nu este necesar. În al doilea rând, intervalul analizat de program conține NUMAI valori numerice. Celulele specificate în primul argument vor fi căutate conform condiției specificate în al doilea argument.

Atenţie! Criteriul de căutare poate fi specificat în celulă. Și faceți un link către el în formulă.

Să găsim valoarea medie a numerelor folosind criteriul text. De exemplu, vânzările medii ale produsului „tabele”.

Funcția va arăta astfel: =AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). Gamă – o coloană cu nume de produse. Criteriul de căutare este o legătură către o celulă cu cuvântul „tabele” (puteți introduce cuvântul „tabele” în loc de linkul A7). Interval de mediere – acele celule din care vor fi luate date pentru a calcula valoarea medie.

Ca rezultat al calculului funcției, obținem următoarea valoare:

Atenţie! Pentru un criteriu text (condiție), trebuie specificat intervalul de mediere.

Cum se calculează prețul mediu ponderat în Excel?

Cum am aflat prețul mediu ponderat?

Formula: =SUMAPRODUS(C2:C12;B2:B12)/SUMA(C2:C12).

Folosind formula SUMPRODUCT, aflăm venitul total după vânzarea întregii cantități de mărfuri. Și funcția SUM însumează cantitatea de bunuri. Împărțind venitul total din vânzarea de bunuri la numărul total de unități de mărfuri, am găsit prețul mediu ponderat. Acest indicator ia în considerare „greutatea” fiecărui preț. Ponderea sa în masa totală a valorilor.

Abaterea standard: formula în Excel

Distinge între medie abaterea standard pentru populația generală și pentru eșantion. În primul caz, aceasta este rădăcina varianței generale. În al doilea, din varianța eșantionului.

Pentru a calcula acest indicator statistic, este compilată o formulă de dispersie. Din ea se extrage rădăcina. Dar în Excel există o funcție gata făcută pentru găsirea abaterii standard.

Abaterea standard este legată de amploarea datelor sursă. Acest lucru nu este suficient pentru o reprezentare figurativă a variației intervalului analizat. Pentru a obține nivelul relativ de împrăștiere a datelor, se calculează coeficientul de variație:

abatere standard / medie aritmetică

Formula în Excel arată astfel:

STDEV (interval de valori) / AVERAGE (interval de valori).

Coeficientul de variație se calculează procentual. Prin urmare, setăm formatul procentual în celulă.

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 media abaterea standard formula în Excel, de ce este nevoie și, strict vorbind, când este folosită. Să mergem!

Ce este

Să începem cu teoria. Abaterea standard este de obicei numită rădăcină pătrată, obținută din media aritmetică a tuturor diferențelor pătrate dintre mărimile disponibile, precum și din media aritmetică a acestora.

Apropo, această valoare este de obicei numită litera greacă „sigma”. Abaterea standard este calculată folosind formula STANDARDEVAL în consecință, programul face acest lucru pentru utilizator. Ideea este acest concept

este de a identifica gradul de variabilitate al instrumentului, adică este, în felul său, un indicator originar din statistica descriptivă. Identifică modificările volatilității unui instrument în orice perioadă de timp. Formulele STDEV pot fi utilizate pentru a estima abaterea standard a unui eșantion, ignorând valorile booleene și text.

Formula 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 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.

Funcția de abatere standard este deja din categoria matematicii superioare legate de statistică. Există mai multe opțiuni pentru utilizarea Funcțiilor în Excel. abaterea standard Acest:

• Funcția STANDARDEV.
• Funcția DEVIARE STANDARD.
• Funcția STDEV

Vom avea nevoie de aceste funcții în statisticile vânzărilor pentru a identifica stabilitatea vânzărilor (analiza XYZ). Aceste date pot fi folosite atât pentru stabilirea prețurilor, cât și pentru crearea (ajustarea) matricei de sortiment și pentru alte analize utile de vânzări, despre care cu siguranță voi vorbi în articolele viitoare.

Prefaţă

Să ne uităm mai întâi la formulele în limbaj matematic, iar apoi (mai jos în text) vom analiza în detaliu formula în Excel și modul în care rezultatul rezultat este utilizat în analiza statisticilor vânzărilor.

Deci, Abaterea standard este o estimare a abaterii standard a unei variabile aleatorii xîn ceea ce privește așteptările sale matematice bazate pe o estimare imparțială a varianței sale)))) Nu vă fie teamă de cuvinte de neînțeles, aveți răbdare și veți înțelege totul!

Descrierea formulei: Abaterea standard se măsoară în unități de măsură ale variabilei aleatoare în sine și este utilizată la calcularea erorii standard a mediei aritmetice, la construirea intervalelor de încredere, la testarea statistică a ipotezelor, la măsurarea relației liniare dintre variabilele aleatoare. . Definit ca rădăcina pătrată a varianței variabilei aleatoare

Acum, abaterea standard este o estimare a abaterii standard a unei variabile aleatoare x raportat la așteptările sale matematice bazate pe o estimare imparțială a varianței sale:

Dispersie;

- i al-lea element al selecției;

Dimensiunea probei;

Media aritmetică a eșantionului:

Trebuie remarcat faptul că ambele estimări sunt părtinitoare. În cazul general, este imposibil să se construiască o estimare imparțială. Cu toate acestea, estimarea bazată pe estimarea variației imparțiale este consecventă.

Regula trei sigma() - aproape toate valorile unei variabile aleatoare distribuite normal se află în interval. Mai strict, cu o probabilitate de aproximativ 0,9973, valoarea unei variabile aleatoare distribuite normal se află în intervalul specificat (cu condiția ca valoarea să fie adevărată și să nu fie obținută ca urmare a procesării eșantionului). Vom folosi un interval rotunjit de 0,1

Dacă valoarea adevărată este necunoscută, atunci ar trebui să utilizați nu, dar s. Astfel, regula de trei sigma este transformată în regula de trei s. Această regulă este cea care ne va ajuta să determinăm stabilitatea vânzărilor, dar mai multe despre asta mai târziu...

Acum Funcția de abatere standard în Excel

Sper că nu te-am plictisit prea mult cu matematica? Poate cuiva aceste informații va fi necesar pentru un eseu sau pentru alte scopuri. Acum să vedem cum funcționează aceste formule în Excel...

Pentru a determina stabilitatea vânzărilor, nu trebuie să analizăm toate opțiunile pentru funcțiile de abatere standard. Vom folosi doar unul:

Funcția STDEV

STDEV(numarul 1;numărul 2;... )

Numărul 1, numărul 2,..- de la 1 la 30 de argumente numerice corespunzătoare populaţiei generale.

Acum să ne uităm la un exemplu:

Să creăm o carte și o masă improvizată. Veți descărca acest exemplu în Excel la sfârșitul articolului.

De continuat!!!

Bună din nou. Bine!? Am avut un minut liber. Să continuăm?

Și astfel stabilitatea vânzărilor cu ajutorul Funcții STDEV

Pentru claritate, să luăm câteva produse improvizate:

În analiză, fie că este vorba despre o prognoză, cercetare sau orice altceva legat de statistică, este întotdeauna necesar să se ia trei perioade. Aceasta poate fi o săptămână, o lună, un sfert sau un an. Este posibil și chiar cel mai bine să luați cât mai multe menstruații, dar nu mai puțin de trei.

Am arătat în mod special vânzări exagerate, unde ochiul liber poate vedea ce se vinde în mod constant și ce nu. Acest lucru va face mai ușor de înțeles cum funcționează formulele.

Și astfel avem vânzări, acum trebuie să calculăm valorile medii ale vânzărilor pe perioadă.

Formula pentru valoarea medie este MEDIE (date de perioadă), în cazul meu formula arată astfel = MEDIE (C6: E6)

Aplicam formula tuturor produselor. Acest lucru se poate face prin apucarea colțului din dreapta al celulei selectate și trăgând-o până la sfârșitul listei. Sau plasați cursorul pe coloana cu produsul și apăsați următoarele combinații de taste:

Ctrl + Jos mută cursorul în partea de sus a listei.

Ctrl + Dreapta, cursorul se deplasează în partea dreaptă a tabelului. Încă o dată la dreapta și vom ajunge la coloana cu formula.

Acum prindem

Ctrl + Shift și apăsați în sus. Astfel vom selecta zona în care va fi desenată formula.

Iar combinația de taste Ctrl + D va trage funcția acolo unde avem nevoie.

Amintiți-vă de aceste combinații, vă cresc cu adevărat viteza în Excel, mai ales când lucrați cu matrice mari.

Următoarea etapă, funcția standard de plecare în sine, așa cum am spus deja, vom folosi doar una STDEV

Scriem funcția și punem valorile vânzărilor din fiecare perioadă în valorile funcției. Dacă aveți vânzări în tabel una după alta, puteți utiliza un interval, ca în formula mea =STDEV(C6:E6) sau enumerați celulele necesare separate prin punct și virgulă =STDEV(C6;D6;E6)

Acum toate calculele sunt gata. Dar de unde știi ce se vinde în mod constant și ce nu? Să punem convenția XYZ unde,

X este stabil

Y - cu mici abateri

Z - nu este stabil

Pentru a face acest lucru, folosim intervale de eroare. dacă apar fluctuații în limita a 10%, vom presupune că vânzările sunt stabile.

Dacă între 10 și 25 la sută, va fi Y.

Și dacă valoarea variației depășește 25%, aceasta nu este stabilitate.

Pentru a seta corect literele pentru fiecare produs, vom folosi formula IF. Aflați mai multe despre. În tabelul meu, această funcție va arăta astfel:

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

În consecință, extindem toate formulele pentru toate numele.

Voi încerca să răspund imediat la întrebarea, De ce intervalele de 10% și 25%?

De fapt, intervalele pot fi diferite, totul depinde de sarcina specifică. Ți-am arătat în mod special valori de vânzări exagerate, unde diferența este vizibilă cu ochii. Evident, produsul 1 nu este vândut constant, dar dinamica arată o creștere a vânzărilor. Lăsăm acest produs în pace...

Dar aici este produsul 2, există deja o destabilizare evidentă. Iar calculele noastre arată Z, care ne spune că vânzările nu sunt stabile. Produsul 3 și Produsul 5 prezintă performanțe stabile, vă rugăm să rețineți că variația este de 10%.

Aceste. Produsul 5 cu scoruri de 45, 46 și 45 prezintă o variație de 1%, care este o serie numerică stabilă.

Dar Produsul 2 cu indicatorii 10, 50 și 5 arată o variație de 93%, ceea ce NU este o serie numerică stabilă.

După toate calculele, poți pune un filtru și filtra stabilitatea, așa că dacă tabelul tău este format din câteva mii de articole, poți evidenția cu ușurință care dintre ele nu sunt stabile în vânzări sau, dimpotrivă, care sunt stabile.

„Y” nu a funcționat în tabelul meu, cred că pentru claritate a seriei de numere, trebuie adăugat. Voi desena produsul 6...

Vedeți, seriile de numere 40, 50 și 30 arată o variație de 20%. Nu pare să existe o eroare mare, dar răspândirea este încă semnificativă...

Și așa, pentru a rezuma:

10.50.5 - Z nu este stabil. Variație mai mare de 25%

40,50,30 - Și puteți acorda atenție acestui produs și puteți îmbunătăți vânzările acestuia. Variație mai mică de 25% dar mai mare de 10%

45,46,45 - X este stabilitate, încă nu trebuie să faceți nimic cu acest produs. Variație mai mică de 10%

Asta e tot! Sper că am explicat totul clar, dacă nu, întrebați dacă nu este clar. Și vă voi fi recunoscător pentru fiecare comentariu, fie el laude sau critici. Așa voi ști că mă citești și că, ceea ce este foarte IMPORTANT, ești interesat. Și, în consecință, vor apărea noi lecții.

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 calculare a 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.