Koncept procenta odstupanja podrazumijeva razliku između dvije numeričke vrijednosti u procentima. Navedimo konkretan primjer: recimo, jednog dana je prodato 120 komada tableta iz veleprodajnog skladišta, a sutradan - 150 komada. Razlika u obimu prodaje je očigledna, s još 30 tableta prodatih sljedeći dan. Kada od 150 oduzmemo broj 120, dobijamo odstupanje koje je jednako broju +30. Postavlja se pitanje: koliki je postotak odstupanja?

Kako izračunati procentualno odstupanje u Excelu

Procenat odstupanja se izračunava oduzimanjem stare vrijednosti od nove vrijednosti, a zatim dijeljenjem rezultata sa starom vrijednošću. Rezultat izračunavanja ove formule u Excelu trebao bi biti prikazan u procentualnom formatu ćelije. U ovom primjeru formula za proračun izgleda ovako (150-120)/120=25%. Formulu je lako provjeriti 120+25%=150.

Bilješka! Ako zamijenimo stare i nove brojeve, onda ćemo imati formulu za izračunavanje marže.

Na slici ispod prikazan je primjer kako prikazati gornji izračun kao Excel formulu. Formula u ćeliji D2 izračunava postotak varijacije između tekuće i prošlogodišnje prodaje: =(C2-B2)/B2

Važno je obratiti pažnju na prisustvo zagrada u ovoj formuli. Podrazumevano, u Excelu, deljenje uvek ima prednost nad oduzimanjem. Stoga, ako ne stavimo zagrade, tada će se vrijednost prvo podijeliti, a zatim će se od nje oduzeti druga vrijednost. Takav proračun (bez zagrada) će biti pogrešan. Zatvaranje prvog dijela proračuna u formuli sa zagradama automatski povećava prioritet operacije oduzimanja u odnosu na operaciju dijeljenja.

Ispravno sa zagradama, unesite formulu u ćeliju D2, a zatim je jednostavno kopirajte u ostale prazne ćelije u rasponu D2:D5. Da biste na najbrži način kopirali formulu, samo pomaknite kursor miša na marker kursora tastature (u donji desni ugao) tako da se kursor miša promijeni iz strelice u crni krst. Nakon toga samo dvaput kliknite lijevu tipku miša i Excel će automatski popuniti prazne ćelije formulom, dok će odrediti raspon D2:D5, koji se mora popuniti do ćelije D5 i ne više. Ovo je vrlo zgodan life hack u Excelu.



Alternativna formula za izračunavanje postotka odstupanja u Excelu

U alternativnoj formuli koja izračunava relativno odstupanje vrijednosti prodaje iz tekuće godine se odmah dijele sa vrijednostima prodaje iz prethodne godine, a tek onda se od rezultata oduzima jedna: =C2/B2-1.

Kao što možete vidjeti na slici, rezultat izračunavanja alternativne formule je isti kao u prethodnoj, što znači da je ispravan. Ali alternativnu formulu je lakše zapisati, iako je nekome možda teže pročitati da bi razumio kako ona funkcionira. Ili je teže razumjeti koju vrijednost data formula proizvodi kao rezultat izračuna ako nije potpisana.

Jedini nedostatak ove alternativne formule je nemogućnost izračunavanja procentualnog odstupanja za negativne brojeve u brojiocu ili zamjeni. Čak i ako koristimo ABS funkciju u formuli, formula će vratiti pogrešan rezultat s negativnim brojem u rezerviranom mjestu.

Budući da u Excelu, prema zadanim postavkama, operator dijeljenja ima prednost nad operatorom oduzimanja, nema potrebe za korištenjem zagrada u ovoj formuli.

Da biste pronašli prosječnu vrijednost u Excel-u (bilo da je brojčana, tekstualna, procentualna ili druga vrijednost), postoji mnogo funkcija. I svaki od njih ima svoje karakteristike i prednosti. Uostalom, u ovom zadatku se mogu postaviti određeni uvjeti.

Na primjer, prosječne vrijednosti niza brojeva u Excelu izračunavaju se pomoću statističkih funkcija. Također možete ručno unijeti vlastitu formulu. Razmotrimo razne opcije.

Kako pronaći aritmetičku sredinu brojeva?

Da biste pronašli aritmetičku sredinu, saberete sve brojeve u skupu i podijelite zbir brojem. Na primjer, ocjene učenika iz informatike: 3, 4, 3, 5, 5. Šta vrijedi za četvrtinu: 4. Pronašli smo aritmetičku sredinu koristeći formulu: \u003d (3 + 4 + 3 + 5 + 5) / 5.

Kako to učiniti brzo koristeći Excel funkcije? Uzmimo, na primjer, seriju slučajni brojevi U redu:

Ili: učinite ćeliju aktivnom i jednostavno ručno unesite formulu: =PROSJEK(A1:A8).

Sada da vidimo šta još funkcija AVERAGE može učiniti.

Pronađite aritmetičku sredinu prva dva i posljednja tri broja. Formula: =PROSJEK(A1:B1;F1:H1). rezultat:



Prosjek po stanju

Uslov za pronalaženje aritmetičke sredine može biti numerički ili tekstualni kriterijum. Koristićemo funkciju: =AVERAGEIF().

Nađi sredinu aritmetički brojevi koji su veći ili jednaki 10.

Funkcija: =AVERAGEIF(A1:A8,">=10")

Rezultat korištenja funkcije AVERAGEIF pod uvjetom ">=10":

Treći argument - "Raspon usrednjavanja" - je izostavljen. Prvo, nije potrebno. Drugo, opseg koji je analizirao program sadrži SAMO numeričke vrijednosti. U ćelijama navedenim u prvom argumentu, pretraga će se izvršiti prema uvjetu navedenom u drugom argumentu.

Pažnja! Kriterijum pretrage se može odrediti u ćeliji. I u formuli da se referencira na to.

Nađimo prosječnu vrijednost brojeva po tekstualnom kriteriju. Na primjer, prosječna prodaja proizvoda "stolovi".

Funkcija će izgledati ovako: =AVERAGEIF($A$2:$A$12;A7;$B$2:$B$12). Raspon - kolona s nazivima proizvoda. Kriterijum za pretragu je veza do ćelije sa riječju "tabele" (možete umetnuti riječ "tabele" umjesto veze A7). Raspon prosjeka - one ćelije iz kojih će se uzeti podaci za izračunavanje prosječne vrijednosti.

Kao rezultat izračunavanja funkcije dobijamo sljedeću vrijednost:

Pažnja! Za tekstualni kriterij (uvjet) mora se specificirati raspon prosjeka.

Kako izračunati ponderisanu prosječnu cijenu u Excelu?

Kako znamo ponderisanu prosječnu cijenu?

Formula: =SUMPROIZVOD(C2:C12,B2:B12)/SUM(C2:C12).

Koristeći formulu SUMPRODUCT, saznajemo ukupan prihod nakon prodaje cjelokupne količine robe. A funkcija SUM - sumira količinu robe. Dijeljenjem ukupnog prihoda od prodaje robe sa ukupnim brojem jedinica robe, dobija se prosječna ponderirana cijena. Ovaj indikator uzima u obzir "težinu" svake cijene. Njegov udio u ukupnoj masi vrijednosti.

Standardna devijacija: formula u Excel-u

Razlikovati standardnu ​​devijaciju za opću populaciju i za uzorak. U prvom slučaju, ovo je korijen opće varijanse. U drugom, iz varijanse uzorka.

Da izračunam ovo statistički indikator formula za disperziju. Iz nje se uzima korijen. Ali u Excelu postoji gotova funkcija za pronalaženje standardne devijacije.

Standardna devijacija je povezana sa skalom izvornih podataka. Ovo nije dovoljno za figurativni prikaz varijacije analiziranog raspona. Da bi se dobio relativni nivo raspršenosti u podacima, izračunava se koeficijent varijacije:

standardna devijacija / aritmetička sredina

Formula u Excelu izgleda ovako:

STDEV (opseg vrijednosti) / AVERAGE (opseg vrijednosti).

Koeficijent varijacije se izračunava kao procenat. Stoga postavljamo format postotka u ćeliji.

Standardna devijacija je jedan od onih statističkih pojmova u korporativnom svijetu koji podiže profil ljudi koji uspijevaju da je zeznu u razgovoru ili prezentaciji, i ostavlja nejasan nesporazum za one koji ne znaju šta je, ali im je neugodno. pitaj. U stvari, većina menadžera ne razumije koncept standardne devijacije, a ako ste jedan od njih, vrijeme je da prestanete živjeti u laži. U današnjem članku ću vam pokazati kako vam ova podcijenjena statistika može pomoći da bolje razumijete podatke s kojima radite.

Šta mjeri standardna devijacija?

Zamislite da ste vlasnik dvije radnje. A kako bi se izbjegli gubici, važno je da postoji jasna kontrola stanja zaliha. U pokušaju da saznate ko je najbolji menadžer akcija, odlučujete da analizirate akcije iz proteklih šest nedelja. Prosječna sedmična cijena zaliha obje trgovine je približno ista i iznosi oko 32 konvencionalne jedinice. Na prvi pogled, prosječna vrijednost dionica pokazuje da oba menadžera rade na isti način.

Ali ako bolje pogledate aktivnost druge trgovine, možete vidjeti da iako je prosječna vrijednost tačna, varijabilnost zaliha je vrlo velika (od 10 do 58 USD). Dakle, može se zaključiti da srednja vrijednost ne procjenjuje uvijek ispravno podatke. Ovdje dolazi standardna devijacija.

Standardna devijacija pokazuje kako su vrijednosti raspoređene u odnosu na srednju vrijednost u našem . Drugim riječima, možete razumjeti koliki je otjecanje iz sedmice u sedmicu.

U našem primjeru koristili smo Excel funkcija STDEV za izračunavanje standardne devijacije zajedno sa srednjom sredinom.

U slučaju prvog menadžera, standardna devijacija je bila 2. To nam govori da svaka vrijednost u uzorku odstupa u prosjeku za 2 od srednje vrijednosti. je li dobro? Pogledajmo pitanje iz drugog ugla - standardna devijacija od 0 nam govori da je svaka vrijednost u uzorku jednaka njenoj srednjoj vrijednosti (u našem slučaju 32,2). Na primjer, standardna devijacija od 2 nije mnogo drugačija od 0, što ukazuje da je većina vrijednosti blizu srednje vrijednosti. Što je standardna devijacija bliža 0, to je srednja vrijednost pouzdanija. Štaviše, standardna devijacija blizu 0 ukazuje na malu varijabilnost u podacima. To jest, vrijednost ponora sa standardnom devijacijom od 2 ukazuje na nevjerovatnu dosljednost prvog menadžera.

U slučaju druge trgovine, standardna devijacija je bila 18,9. Odnosno, trošak oticanja u prosjeku odstupa za 18,9 od prosječne vrijednosti iz sedmice u sedmicu. Crazy spread! Što je standardna devijacija dalje od 0, to je srednja vrijednost manje tačna. U našem slučaju, brojka 18,9 ukazuje da se prosječnoj vrijednosti (32,8 USD sedmično) jednostavno ne može vjerovati. To nam također govori da je sedmični protok veoma varijabilan.

Ovo je koncept standardne devijacije ukratko. Iako ne daje uvid u druge važne statistička mjerenja(Mode, Median...), u stvari, standardna devijacija igra ključnu ulogu u većini statističkih proračuna. Razumijevanje principa standardne devijacije će baciti svjetlo na suštinu mnogih procesa u vašoj aktivnosti.

Kako izračunati standardnu ​​devijaciju?

Dakle, sada znamo šta govori broj standardne devijacije. Da vidimo kako se računa.

Razmotrite skup podataka od 10 do 70 u koracima od 10. Kao što vidite, već sam izračunao standardnu ​​devijaciju za njih koristeći STDEV funkciju u ćeliji H2 (narandžasta).

Ispod su koraci koje Excel preduzima da bi stigao do 21.6.

Imajte na umu da su svi proračuni vizualizirani radi boljeg razumijevanja. U stvari, u Excelu je proračun trenutan, ostavljajući sve korake iza scene.

Excel prvo pronalazi srednju vrijednost uzorka. U našem slučaju se pokazalo da je prosjek 40, koji se oduzima od svake vrijednosti uzorka u sljedećem koraku. Svaka rezultirajuća razlika se kvadrira i zbraja. Dobili smo zbir jednak 2800, koji se mora podijeliti sa brojem elemenata uzorka minus 1. Pošto imamo 7 elemenata, ispada da trebamo podijeliti 2800 sa 6. Iz rezultata nalazimo kvadratni korijen, ovaj broj će biti standardna devijacija.

Za one kojima nije sasvim jasan princip izračunavanja standardne devijacije pomoću vizualizacije, dajem matematičku interpretaciju pronalaženja ove vrijednosti.

Funkcije proračuna standardne devijacije u Excelu

Postoji nekoliko varijanti formula standardne devijacije u Excelu. Samo trebate ukucati =STDEV i uvjerit ćete se sami.

Vrijedi napomenuti da funkcije STDEV.V i STDEV.G (prva i druga funkcija na listi) dupliciraju funkcije STDEV i STDEV (peta i šesta funkcija na listi), respektivno, koje su zadržane radi kompatibilnosti s ranijim verzije Excel-a.

Općenito, razlika u završetcima In i G funkcije ukazuju na princip izračunavanja standardne devijacije uzorka ili populacije. Već sam objasnio razliku između ova dva niza u prethodnom.

Značajka STDEV i STDEVPA funkcija (treća i četvrta funkcija na listi) je da se prilikom izračunavanja standardne devijacije niza uzimaju u obzir logičke i tekstualne vrijednosti. Tekst i prave logičke vrednosti su 1, a lažne logičke vrednosti su 0. Teško mi je da zamislim situaciju u kojoj bi mi bile potrebne ove dve funkcije, pa mislim da se mogu zanemariti.

Funkcija standardne devijacije je već izvan kategorije višu matematiku koji se odnose na statistiku. U Excelu postoji nekoliko opcija za korištenje funkcije standardne devijacije:

• STDEV funkcija.
• STDEV funkcija.
• STDEV funkcija

Ove funkcije će nam trebati u statistici prodaje da bismo identificirali stabilnost prodaje (XYZ analiza). Ovi podaci se mogu koristiti i za određivanje cijena i za formiranje (korekciju) matrice asortimana i za druge korisne analize prodaje, o čemu ću svakako govoriti u budućim člancima.

Predgovor

Pogledajmo formule prvo na matematičkom jeziku, a zatim ćemo (u nastavku teksta) detaljno analizirati formulu u Excelu i kako se dobijeni rezultat primjenjuje u analizi statistike prodaje.

Dakle, standardna devijacija je procjena standardne devijacije slučajna varijabla x u vezi s njom matematičko očekivanje na osnovu nepristrasne procjene njegove varijanse)))) Ne bojte se nerazumljivih riječi, budite strpljivi i sve ćete razumjeti!

Opis formule: Standardna devijacija se mjeri u jedinicama same slučajne varijable i koristi se pri izračunavanju standardne greške aritmetičke sredine, pri konstruiranju intervala povjerenja, pri statističkom testiranju hipoteza, pri mjerenju linearne veze između slučajnih varijabli. Definira se kao kvadratni korijen varijanse slučajne varijable

Sada je standardna devijacija procjena standardne devijacije slučajne varijable x s obzirom na njegovo matematičko očekivanje zasnovano na nepristrasnoj procjeni njegove varijanse:

disperzija;

- i-ti element uzorka;

Veličina uzorka;

Primjer aritmetičke sredine:

Treba napomenuti da su obje procjene pristrasne. U opštem slučaju, nemoguće je konstruisati nepristrasnu procenu. Međutim, procjena zasnovana na nepristrasnoj procjeni varijanse je konzistentna.

tri sigma pravilo() - gotovo sve vrijednosti normalno raspoređene slučajne varijable leže u intervalu. Strogo rečeno, sa vjerovatnoćom od približno 0,9973, vrijednost normalno raspoređene slučajne varijable leži u navedenom intervalu (pod uvjetom da je vrijednost istinita, a ne dobijena kao rezultat obrade uzorka). Koristićemo zaokruženi interval od 0,1

Ako je prava vrijednost nepoznata, onda biste trebali koristiti not, ali s. Na ovaj način, pravilo troje sigma se pretvara u pravilo tri s. Upravo ovo pravilo će nam pomoći da utvrdimo stabilnost prodaje, ali o tome kasnije...

Sada funkcija standardne devijacije u Excelu

Nadam se da te nisam zatrpao matematikom? Možda neko ove informacije biće potrebno za apstraktnu ili neku drugu svrhu. Hajde sada da prožvakamo kako ove formule rade u Excelu...

Da bismo utvrdili stabilnost prodaje, ne moramo ulaziti u sve opcije za funkcije standardne devijacije. Koristićemo samo jednu:

STDEV funkcija

STDEV(broj 1;broj2;... )

Broj 1, Broj 2,...- od 1 do 30 numeričkih argumenata koji odgovaraju općoj populaciji.

Pogledajmo sada primjer:

Kreirajmo knjigu i improviziranu tabelu. Ovaj primjer možete preuzeti u Excelu na kraju članka.

Nastavlja se!!!

Zdravo opet. Pa!? Dobio sam slobodan minut. Hajde da nastavimo?

I tako stabilnost prodaje uz pomoć STDEV funkcije

Radi jasnoće, uzmimo nekoliko improviziranih proizvoda:

U analitici, bilo da je u pitanju prognoza, istraživanje ili nešto drugo vezano za statistiku, uvijek je potrebno uzeti tri perioda. To može biti sedmica, mjesec, kvartal ili godina. Moguće je i čak najbolje uzeti što više menstruacija, ali ne manje od tri.

Posebno sam pokazao pretjeranu prodaju, gdje se golim okom vidi šta se stalno prodaje, a šta ne. Ovo će olakšati razumijevanje kako formule rade.

I tako imamo prodaju, sada moramo izračunati prosječne vrijednosti prodaje po periodu.

Formula prosječne vrijednosti PROSJEK (podaci perioda) u mom slučaju, formula izgleda ovako =PROSJEČNO(C6:E6)

Proširujemo formulu za sve proizvode. To se može učiniti tako što ćete držati desni ugao odabrane ćelije i povući je na kraj liste. Ili stavite kursor na kolonu s proizvodom i pritisnite sljedeće kombinacije tipki:

Ctrl + Dolje pomjerite kursor na dno liste.

Ctrl + Desno, kursor će se pomeriti na desnu stranu tabele. Još jednom udesno i doći ćemo do stupca sa formulom.

Sada stezamo

Ctrl + Shift i pritisnite gore. Dakle, biramo područje rastezanja formule.

A kombinacija tipki Ctrl + D će proširiti funkciju tamo gdje nam je potrebna.

Zapamtite ove kombinacije, one zaista povećavaju vašu brzinu u Excelu, posebno kada radite s velikim nizovima.

Sljedeći korak, sama funkcija standardne devijacije, kao što sam rekao, koristit ćemo samo jednu STDEV

Propisujemo funkciju i u vrijednosti funkcije stavljamo prodajne vrijednosti svakog perioda. Ako imate prodaje u tabeli jednu za drugom, možete koristiti raspon, kao u mojoj formuli =SDV(C6:E6) ili navesti potrebne ćelije sa tačkom i zarezom =SDV(C6;D6;E6)

Ovdje su svi proračuni i spremni. Ali kako znati šta se stalno prodaje, a šta ne? Hajdemo samo da zapišemo konvenciju XYZ gdje,

X je stabilan

Y - sa malim odstupanjima

Z - nije stabilan

Da bismo to učinili, koristimo intervale grešaka. ako dođe do fluktuacija unutar 10%, pretpostavit ćemo da je prodaja stabilna.

Ako između 10 i 25 posto, to će biti Y.

A ako vrijednosti varijacije ​​pređu 25% - to nije stabilnost.

Da bismo ispravno postavili slova za svaki proizvod, detaljnije ćemo koristiti formulu IF. u mom stolu datu funkcijuće izgledati ovako:

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

U skladu s tim, rastežemo sve formule za sva imena.

Pokušaću odmah da odgovorim na pitanje zašto intervali od 10% i 25%?

Zapravo, intervali mogu biti različiti, sve ovisi o konkretnom zadatku. Posebno sam vam pokazao preuveličane prodajne vrijednosti, gdje je razlika vidljiva na "oko". Očigledno je da se proizvod 1 ne prodaje konstantno, ali dinamika pokazuje rast prodaje. Ostavite ovu stavku na miru...

Ali proizvod 2, već postoji destabilizacija na licu. A naše kalkulacije pokazuju Z, što nam govori o nestabilnosti prodaje. Stavka 3 i stavka 5 pokazuju stabilne performanse, imajte na umu da je varijacija unutar 10%.

One. Stavka 5 sa ocjenom 45, 46 i 45 pokazuje varijaciju od 1%, što je stabilan niz brojeva.

Ali proizvod 2 sa rezultatom 10, 50 i 5 pokazuje varijaciju od 93%, što NIJE stabilan niz brojeva.

Nakon svih kalkulacija, možete staviti filter i filtrirati stabilnost, pa ako se vaša tabela sastoji od nekoliko hiljada artikala, lako možete odabrati koji nisu stabilni u prodaji ili, naprotiv, koji su stabilni.

"Y" nije funkcionirao u mojoj tabeli, mislim da radi jasnoće niza brojeva, treba ga dodati. Nacrtaću Robu 6...

Vidite, serije brojeva 40, 50 i 30 pokazuju 20% varijacije. Čini se da nema velike greške, ali ipak je širenje značajno...

I tako da sumiramo:

10,50,5 - Z nije stabilan. Varijacija preko 25%

40,50,30 - Y možete obratiti pažnju na ovaj proizvod i poboljšati njegovu prodaju. Varijacija manja od 25%, ali veća od 10%

45,46,45 - X je stabilnost, još ništa ne treba raditi s ovim proizvodom. Varijacija manja od 10%

To je sve! Nadam se da sam sve jasno objasnio, ako nisam, pitajte šta nije jasno. I bit ću vam zahvalan na svakom komentaru, bilo da se radi o pohvali ili kritici. Tako da ću znati da čitate mene i vas, što je vrlo VAŽNO, zanimljivo. I shodno tome će se pojaviti nove lekcije.

Andrey Lipov

Jednostavno rečeno, standardna devijacija pokazuje koliko cijena instrumenta varira tokom vremena. Odnosno, što je ovaj indikator veći, to je jača volatilnost ili varijabilnost određenog broja vrijednosti.

Standardna devijacija se može i treba koristiti za analizu skupova vrijednosti, budući da se dva skupa sa naizgled istom srednjom sredinom mogu pokazati potpuno različitim u smislu širenja vrijednosti.

Primjer

Uzmimo dva reda brojeva.

a) 1,2,3,4,5,6,7,8,9. Prosjek - 5. Art. odstupanje = 2,7386

b) 20.1.7.1.15, -1, -20.4,18.5. Prosjek - 5. Art. odstupanje = 12,2066

Ako ne držite cijeli niz brojeva pred očima, tada standardna devijacija pokazuje da su u slučaju "b" vrijednosti mnogo više rasute oko svoje prosječne vrijednosti.

Grubo govoreći, u redu "b" vrijednost je 5 plus ili minus 12 (u prosjeku) - ne baš, ali otkriva značenje.

Kako izračunati standardnu ​​devijaciju

Da biste izračunali standardnu ​​devijaciju, možete koristiti formulu posuđenu iz izračuna standardne devijacije prinosa uzajamnih fondova:

Ovdje je N broj vrijednosti,
DOHaverage - prosjek svih vrijednosti,
DOH period - vrijednost N.

U Excelu se odgovarajuća funkcija naziva STDEV (ili STDEV u engleskoj verziji programa).

Upute korak po korak su:

1. Izračunajte prosjek za niz brojeva.
2. Za svaku vrijednost odredite razliku između srednje vrijednosti i ove vrijednosti.
3. Izračunajte zbir kvadrata ovih razlika.
4. Dobiveni zbir podijelite sa brojem brojeva u nizu.
5. Uzmite kvadratni korijen broja dobivenog u posljednjem pasusu.

Vaši prijatelji će imati koristi od ovih informacija. Podijelite s njima!