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ă abatere relativă valorile vânzărilor din anul curent se împart 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, ceea ce înseamnă că este corect. Dar formula alternativă este mai ușor de scris, deși poate fi mai greu de citit pentru cineva 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ă.

Pentru a găsi valoarea medie în Excel (fie că este o valoare numerică, textuală, procentuală sau altă valoare), există multe funcții. Și fiecare dintre ele are propriile sale caracteristici și avantaje. La urma urmei, anumite condiții pot fi stabilite în această sarcină.

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 diverse opțiuni.

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

Pentru a găsi media aritmetică, adăugați toate numerele din mulțime și împărțiți suma la număr. De exemplu, notele unui student la informatică: 3, 4, 3, 5, 5. Ce este valabil pentru un sfert: 4. Am găsit media aritmetică folosind formula: \u003d (3 + 4 + 3 + 5 + 5) / 5.

Cum se face rapid folosind funcțiile Excel? Luați, de exemplu, serialul numere aleatoriiîn linie:

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

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

Aflați media aritmetică a primelor două și a ultimelor trei numere. Formula: =MEDIE(A1:B1;F1:H1). Rezultat:



Medie după stare

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

Găsiți media numere aritmetice care sunt mai mari sau egale cu 10.

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

Rezultatul utilizării funcției AVERAGEIF cu condiția „>=10”:

Al treilea argument - „Intervalul de mediere” - este omis. În primul rând, nu este necesar. În al doilea rând, intervalul analizat de program conține NUMAI valori numerice. În celulele specificate în primul argument, căutarea se va efectua conform condiției specificate în al doilea argument.

Atenţie! Criteriul de căutare poate fi specificat într-o celulă. Și în formula pentru a face o referire la ea.

Să găsim valoarea medie a numerelor după criteriul textului. 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). Interval - o coloană cu nume de produse. Criteriul de căutare este o legătură către o celulă cu cuvântul „tables” (puteți introduce cuvântul „tables” î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 știm prețul mediu ponderat?

Formula: =SUMPRODUS(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 mărfuri. Î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 ține cont de „greutatea” fiecărui preț. Ponderea sa în masa totală a valorilor.

Abaterea standard: formula în Excel

Distingeți 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 asta indicator statistic formula de dispersie. Rădăcina este luată din ea. 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 în date, 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ă.

Abaterea standard este unul dintre acei termeni statistici din lumea corporativă care ridică profilul oamenilor care reușesc să o strice cu succes într-o conversație sau o prezentare și lasă o vagă neînțelegere pentru cei care nu știu ce este, dar sunt 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 minciuna. În articolul de astăzi, vă voi arăta cum această statistică subestimată 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ă existe un control clar al soldurilor stocurilor. În încercarea de a afla cine este cel mai bun administrator de stoc, decideți să analizați stocurile din ultimele șase săptămâni. Costul mediu săptămânal al stocului ambelor magazine este aproximativ același și este de aproximativ 32 de unități convenționale. La prima vedere, valoarea medie a stocului arată că ambii manageri lucrează în același mod.

Dar dacă te uiți mai atent la activitățile celui de-al doilea magazin, poți observa că, deși valoarea medie este corectă, variabilitatea stocului este foarte mare (de la 10 la 58 USD). Astfel, se poate concluziona că media nu întotdeauna estimează 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 scurgerea de la o săptămână la alta.

În exemplul nostru, am folosit Funcția Excel STDEV pentru a calcula scorul abaterii standard împreună cu media.

În cazul primului manager, abaterea standard a fost 2. Aceasta ne spune că fiecare valoare din eșantion se abate în medie 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 valoarea sa medie (în cazul nostru, 32,2). De exemplu, 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 sink cu o abatere standard de 2 indică consistența 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 importante măsurători statistice(Mod, Median...), de fapt, abaterea standard joacă un rol crucial în majoritatea calculelor statistice. Înțelegerea principiilor abaterii standard va pune în lumină esența multor procese din activitatea dumneavoastră.

Cum se calculează abaterea standard?

Deci, acum știm ce spune cifra abaterii standard. Să vedem cum contează.

Luați în considerare un set de date de la 10 la 70 în trepte de 10. După cum puteți vedea, am calculat deja abaterea standard pentru ele folosind funcția STDEV din celula H2 (portocalie).

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 este instantaneu, lăsând toți pașii în culise.

Excel găsește mai întâi media eșantionului. În cazul nostru, media s-a dovedit a fi 40, care este scăzută din valoarea fiecărei probe în pasul următor. Fiecare diferență rezultată este pătrată și însumată. Am obținut suma egală cu 2800, care trebuie împărțită la numărul de elemente eșantion minus 1. Deoarece avem 7 elemente, se dovedește că trebuie să împărțim 2800 la 6. Din rezultat găsim rădăcina pătrată, această cifră. 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 de calcul a abaterii standard în Excel

Există mai multe varietăți de formule de abatere standard în Excel. Trebuie doar 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ții. În și. Funcțiile 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ă a funcțiilor STDEV și STDEVPA (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 booleenii adevărați sunt 1, iar booleanii falși sunt 0. Îmi este greu să-mi imaginez o situație în care aș avea nevoie de aceste două funcții, așa că cred că pot fi ignorate.

Funcția de abatere standard este deja în afara categoriei matematica superioara referitoare la statistici. În Excel, există mai multe opțiuni pentru utilizarea funcției de abatere standard:

• Funcția STDEV.
• Funcția STDEV.
• 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 formarea (corecția) matricei sortimentale și pentru alte analize utile de vânzări, despre care cu siguranță voi vorbi în articolele viitoare.

cuvânt înainte

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

Deci, Abaterea standard este o estimare a abaterii standard variabilă aleatorie X cu privire la ea așteptări matematice bazat pe o estimare imparțială a variației sale)))) Nu vă fie frică 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 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 unei relații liniare între variabile aleatoare. Definit ca rădăcina pătrată a varianței unei variabile aleatoare

Acum 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:

Dispersie;

- i-al-lea element de probă;

Marime de mostra;

Eșantion de medie aritmetică:

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, o estimare bazată pe o estimare imparțială a varianței este consecventă.

regula trei sigma() - aproape toate valorile unei variabile aleatoare distribuite normal se află în intervalul . 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. În acest fel, regula de trei sigma este convertită la 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 copleșit cu matematică? Poate cineva aceasta informatie va fi necesar pentru un rezumat sau alt scop. 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(Numărul 1;numarul 2;... )

Numărul1, Numărul2,..- 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 foaie de calcul improvizată. Puteți descărca acest exemplu în Excel la sfârșitul articolului.

Va urma!!!

Buna din nou. Bine!? Am un minut liber. Hai sa 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 de o prognoză, de cercetare sau de altceva legat de statistică, este întotdeauna necesar să se ia trei perioade. Poate fi o săptămână, lună, trimestru sau 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 poți vedea cu ochiul liber 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 valorii medii MEDIE (date de perioadă) în cazul meu, formula arată astfel = MEDIU (C6:E6)

Întindem formula pentru toate produsele. Acest lucru se poate face ținând apăsat colțul 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 mutați cursorul în partea de jos a listei.

Ctrl + Dreapta, cursorul se va muta î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. Deci selectăm zona de întindere a formulei.

Iar combinația de taste Ctrl + D va întinde 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ătorul pas, funcția de abatere standard în sine, așa cum am spus, vom folosi doar una STDEV

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

Aici sunt toate calculele și gata. Dar de unde știi ce se vinde în mod constant și ce nu? Să punem jos 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ă valorile variației depășesc 25% - aceasta nu este stabilitate.

Pentru a seta corect literele pentru fiecare produs, vom folosi formula IF mai detaliat despre. în masa mea funcţie dată va arata asa:

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

În consecință, întindem 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ă „ochiului”. Este evident că produsul 1 nu se vinde în mod constant, dar dinamica arată o creștere a vânzărilor. Lăsați acest articol în pace...

Dar produsul 2, există deja destabilizare pe față. Iar calculele noastre arată Z, care ne spune despre instabilitatea vânzărilor. Elementele 3 și 5 prezintă performanță stabilă, vă rugăm să rețineți că variația este de 10%.

Acestea. Itemul 5 cu scoruri de 45, 46 și 45 arată o variație de 1%, care este o serie de numere stabilă.

Dar Produsul 2 cu scoruri de 10, 50 și 5 arată o variație de 93%, care NU este o serie de numere stabilă.

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

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

Vedeți, seriile de numere 40, 50 și 30 arată o variație de 20%. Se pare că nu există nicio eroare mare, dar totuși răspândirea este semnificativă...

Și așa pentru a rezuma:

10,50,5 - Z nu este stabil. Variație peste 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 făcut nimic cu acest produs. Variație mai mică de 10%

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

Andrei Lipov

În termeni simpli, abaterea standard arată cât de mult fluctuează prețul unui instrument în timp. Adică, cu cât acest indicator este mai mare, cu atât volatilitatea sau variabilitatea unui număr de valori este mai puternică.

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 ceea ce privește 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. Art. abatere = 2,7386

b) 20.1.7.1.15, -1, -20.4,18.5. Medie - 5. Art. abatere = 12,2066

Dacă nu păstrați întreaga serie de numere în fața ochilor, atunci abaterea standard arată că, în cazul lui „b”, valorile sunt împrăștiate mult mai mult în jurul valorii lor medii.

Aproximativ, în rândul „b” valoarea este 5 plus sau minus 12 (în medie) - nu exact, dar dezvăluie semnificația.

Cum se calculează abaterea standard

Pentru a calcula abaterea standard, puteți utiliza formula împrumutată din calculul abaterii standard a randamentelor fondurilor mutuale:

Aici N este numărul de valori,
DOHaverage - media tuturor valorilor,
Perioada DOH - valoarea lui N.

În Excel, funcția corespunzătoare se numește STDEV (sau STDEV în versiunea în limba engleză a programului).

Instrucțiunile pas cu pas sunt:

1. Calculați media pentru o serie de numere.
2. Pentru fiecare valoare, determinați diferența dintre medie și această valoare.
3. Calculați suma pătratelor acestor diferențe.
4. Împărțiți suma rezultată la numărul de numere din serie.
5. Luați rădăcina pătrată a numărului obținut în ultimul paragraf.

Prietenii tăi vor beneficia de aceste informații. Distribuie cu ei!