Standardna devijacija u Excelu. Kako pronaći aritmetičku sredinu u Excelu

U ovom ću članku govoriti o kako pronaći standardnu devijaciju. Ovaj materijal je izuzetno važan za potpuno razumijevanje matematike, tako da nastavnik matematike treba posvetiti zasebnu lekciju ili čak nekoliko za njeno proučavanje. U ovom ćete članku pronaći poveznicu na detaljan i razumljiv video vodič koji objašnjava što je standardna devijacija i kako je pronaći.

Standardna devijacija omogućuje procjenu raspona vrijednosti dobivenih kao rezultat mjerenja određenog parametra. Označava se simbolom (grčko slovo "sigma").

Formula za izračun je prilično jednostavna. Da biste pronašli standardnu devijaciju, trebate uzeti Korijen od disperzije. Sada se morate zapitati: "Što je varijanca?"

Što je varijanca

Definicija varijance ide ovako. Disperzija je aritmetička sredina kvadrata odstupanja vrijednosti od sredine.

Da biste pronašli varijancu, izvršite sljedeće izračune uzastopno:

Odredite prosjek (jednostavni prosjek aritmetički niz vrijednosti).
Zatim oduzmite prosjek od svake vrijednosti i kvadrirajte dobivenu razliku (dobićete kvadrat razlike).
Sljedeći korak je izračunati aritmetičku sredinu dobivenih kvadrata razlike (zašto točno kvadrati možete saznati u nastavku).

Pogledajmo primjer. Recimo da vi i vaši prijatelji odlučite izmjeriti visinu svojih pasa (u milimetrima). Kao rezultat mjerenja dobili ste sljedeće mjere visine (u grebenu): 600 mm, 470 mm, 170 mm, 430 mm i 300 mm.

Izračunajmo srednju vrijednost, varijancu i standardnu devijaciju.

Najprije pronađimo prosječnu vrijednost. Kao što već znate, da biste to učinili, morate zbrojiti sve izmjerene vrijednosti i podijeliti s brojem mjerenja. Napredak izračuna:

Prosječni mm.

Dakle, prosjek (aritmetička sredina) je 394 mm.

Sada moramo odrediti odstupanje visine svakog psa od prosjeka:

Konačno, izračunati varijancu, kvadriramo svaku od dobivenih razlika, a zatim nalazimo aritmetičku sredinu dobivenih rezultata:

Raspršenost mm 2 .

Dakle, disperzija je 21704 mm 2.

Kako pronaći standardnu devijaciju

Dakle, kako sada možemo izračunati standardnu devijaciju, znajući varijancu? Kao što se sjećamo, izvucite kvadratni korijen. Odnosno, standardna devijacija je jednaka:

Mm (zaokruženo na najbliži cijeli broj u mm).

Koristeći ovu metodu, otkrili smo da su neki psi (na primjer, Rottweileri) vrlo veliki psi. Ali postoje i vrlo mali psi (na primjer, jazavčari, ali im to ne biste trebali reći).

Najzanimljivije je to što standardna devijacija nosi sa sobom korisna informacija. Sada možemo pokazati koji su od dobivenih rezultata mjerenja visine unutar intervala koji dobijemo ako ucrtamo standardnu devijaciju od prosjeka (s obje njegove strane).

To jest, koristeći standardnu devijaciju, dobivamo "standardnu" metodu koja nam omogućuje da saznamo koja je od vrijednosti normalna (statistički prosječna), a koja je izuzetno velika ili, obrnuto, mala.

Što je standardna devijacija

Ali... sve će biti malo drugačije ako analiziramo uzorak podaci. U našem primjeru koji smo razmotrili opća populacija. Odnosno, naših 5 pasa bili su jedini psi na svijetu koji su nas zanimali.

Ali ako su podaci uzorak (vrijednosti odabrane iz velikog populacija), onda se izračuni moraju napraviti drugačije.

Ako postoje vrijednosti, tada:

Svi ostali izračuni se provode na sličan način, uključujući određivanje prosjeka.

Na primjer, ako je naših pet pasa samo uzorak populacije pasa (svih pasa na planetu), moramo podijeliti s 4, ne 5, naime:

Varijanca uzorka = mm 2.

U ovom slučaju, standardna devijacija za uzorak jednaka je mm (zaokruženo na najbliži cijeli broj).

Možemo reći da smo napravili neke "ispravke" u slučaju kada su naše vrijednosti samo mali uzorak.

Bilješka. Zašto točno kvadrat razlike?

Ali zašto uzimamo točno kvadrat razlike kada računamo varijancu? Recimo da ste prilikom mjerenja nekog parametra dobili sljedeći skup vrijednosti: 4; 4; -4; -4. Ako jednostavno zbrojimo apsolutna odstupanja od srednje vrijednosti (razlike) među sobom... negativne vrijednosti međusobno će se poništiti s pozitivnima:

Ispostavilo se da je ova opcija beskorisna. Onda možda vrijedi isprobati apsolutne vrijednosti odstupanja (odnosno module ovih vrijednosti)?

Na prvi pogled ispada dobro (rezultirajuća vrijednost se, usput, naziva srednja apsolutna devijacija), ali ne u svim slučajevima. Pokušajmo s drugim primjerom. Neka rezultat mjerenja bude sljedeći skup vrijednosti: 7; 1; -6; -2. Tada je prosječno apsolutno odstupanje:

Wow! Opet smo dobili rezultat 4, iako razlike imaju puno veći raspon.

Pogledajmo sada što se događa ako kvadriramo razlike (a zatim izvadimo kvadratni korijen njihovog zbroja).

Za prvi primjer to će biti:

Za drugi primjer to će biti:

Sada je to sasvim druga stvar! Što je veće širenje razlika, veća je standardna devijacija... što je ono čemu smo težili.

Zapravo, u ovu metodu Koristi se ista ideja kao i kod izračuna udaljenosti između točaka, samo se primjenjuje na drugačiji način.

I sa matematička točka Iz perspektive perspektive, korištenje kvadrata i kvadratnih korijena daje više koristi nego što bismo mogli dobiti apsolutne vrijednosti odstupanja, čineći standardnu devijaciju primjenjivom na druge matematičke probleme.

Sergey Valerievich vam je rekao kako pronaći standardnu devijaciju

Među brojnim pokazateljima koji se koriste u statistici potrebno je istaknuti izračun varijance. Treba napomenuti da je ručno izvođenje ovog izračuna prilično dosadan zadatak. Srećom, Excel ima funkcije koje vam omogućuju automatizaciju postupka izračuna. Otkrijmo algoritam za rad s ovim alatima.

Disperzija je mjera varijacije koja predstavlja srednji trg odstupanja od matematičko očekivanje. Dakle, izražava širenje brojeva oko srednje vrijednosti. Izračun varijance može se provesti i za opću populaciju i za uzorak.

Metoda 1: izračun na temelju populacije

Da biste izračunali ovaj pokazatelj u Excelu za opću populaciju, koristite funkciju DISP.G. Sintaksa ovog izraza je sljedeća:

DISP.G(Broj1;Broj2;…)

Ukupno se može koristiti od 1 do 255 argumenata. Argumenti mogu biti sljedeći: numeričke vrijednosti, kao i reference na ćelije u kojima se nalaze.

Pogledajmo kako izračunati ovu vrijednost za raspon s numeričkim podacima.

Metoda 2: izračun prema uzorku

Za razliku od izračuna vrijednosti na temelju populacije, u izračunavanju uzorka nazivnik ne označava ukupno brojevi, ali jedan manje. Ovo se radi u svrhu ispravljanja grešaka. Excel uzima u obzir ovu nijansu u posebnoj funkciji koja je dizajnirana za ovu vrstu izračuna - DISP.V. Njegova sintaksa predstavljena je sljedećom formulom:

DISP.B(Broj1;Broj2;…)

Broj argumenata, kao i u prethodnoj funkciji, također može biti u rasponu od 1 do 255.

Kao što vidite, program Excel može uvelike olakšati izračun varijance. Ovaj statistička vrijednost može se izračunati aplikacijom, kako za opću populaciju tako i za uzorak. U ovom slučaju, sve radnje korisnika zapravo se svode na određivanje raspona brojeva koji se obrađuju, a Excel sam obavlja glavni posao. Naravno, ovo će uštedjeti značajnu količinu vremena korisnika.

Standardna devijacija je jedna od njih statistički pojmovi u korporativnom svijetu, što vam omogućuje da podignete autoritet ljudima koji su to uspjeli uspješno zeznuti tijekom razgovora ili prezentacije, a ostavlja nejasan nesporazum za one koji ne znaju o čemu se radi, ali im je neugodno pitati. Zapravo, većina menadžera ne razumije koncept standardne devijacije i ako ste jedan od njih, vrijeme je da prestanete živjeti u laži. U današnjem članku ću vam reći kako vam ova nedovoljno cijenjena statistička mjera može pomoći da bolje razumijete podatke s kojima radite.

Što mjeri standardna devijacija?

Zamislite da ste vlasnik dvije trgovine. A kako biste izbjegli gubitke, važno je imati jasnu kontrolu stanja zaliha. U pokušaju da saznate koji upravitelj bolje upravlja zalihama, odlučujete analizirati zadnjih šest tjedana zaliha. Prosječni tjedni trošak zaliha za obje trgovine približno je isti i iznosi oko 32 konvencionalne jedinice. Na prvi pogled, prosječno otjecanje pokazuje da oba menadžera rade slično.

Ali ako bolje pogledate aktivnosti druge trgovine, uvjerit ćete se da iako je prosječna vrijednost točna, varijabilnost dionica je vrlo visoka (od 10 do 58 USD). Stoga možemo zaključiti da prosjek ne ocjenjuje uvijek podatke ispravno. 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 shvatiti kolika je razlika u otjecanju iz tjedna u tjedan.

U našem primjeru koristili smo Excel funkcija STANDARDNO ODSTUPANJE za izračunavanje standardnog odstupanja zajedno sa srednjom vrijednosti.

U slučaju prvog menadžera standardna devijacija je bila 2. To nam govori da svaka vrijednost u uzorku u prosjeku odstupa 2 od prosjeka. Je li to dobro? Pogledajmo pitanje iz drugog kuta - standardna devijacija od 0 govori nam da je svaka vrijednost u uzorku jednaka svojoj sredini (u našem slučaju 32,2). Stoga se standardna devijacija od 2 ne razlikuje puno od 0, što ukazuje da je većina vrijednosti blizu srednje vrijednosti. Što je standardna devijacija bliža 0, to je prosjek pouzdaniji. Štoviše, standardna devijacija blizu 0 ukazuje na malu varijabilnost podataka. Odnosno, vrijednost istjecanja sa standardnom devijacijom od 2 ukazuje na nevjerojatnu dosljednost prvog menadžera.

U slučaju druge trgovine, standardna devijacija bila je 18,9. Odnosno, cijena otjecanja u prosjeku iz tjedna u tjedan odstupa za 18,9 od prosječne vrijednosti. Ludo širenje! Što je standardna devijacija dalje od 0, prosjek je manje točan. U našem slučaju brojka od 18,9 pokazuje da se prosječnoj vrijednosti (32,8 USD tjedno) jednostavno ne može vjerovati. Također nam govori da tjedno otjecanje vrlo varira.

Ovo je ukratko koncept standardne devijacije. Iako ne daje uvid u druge važne statističke mjere (mod, medijan...), zapravo standardna devijacija igra ulogu odlučujuću ulogu u većini statističkih izračuna. Razumijevanje načela standardne devijacije rasvijetlit će mnoge vaše poslovne procese.

Kako izračunati standardnu devijaciju?

Sada znamo što kaže broj standardne devijacije. Hajde da shvatimo kako se izračunava.

Pogledajmo skup podataka od 10 do 70 u koracima od 10. Kao što vidite, već sam izračunao vrijednost standardne devijacije za njih pomoću funkcije STANDARDEV u ćeliji H2 (narančasto).

Ispod su koraci koje Excel poduzima da dođe do 21.6.

Imajte na umu da su svi izračuni vizualizirani, za bolje razumijevanje. Zapravo, u Excelu se izračun događa trenutno, ostavljajući sve korake iza scene.

Prvo Excel pronalazi srednju vrijednost uzorka. U našem slučaju, prosjek je ispao 40, koji se u sljedećem koraku oduzima od svake vrijednosti uzorka. Svaka dobivena razlika se kvadrira i zbraja. Dobili smo zbroj jednak 2800, koji se mora podijeliti s brojem elemenata uzorka minus 1. Budući da imamo 7 elemenata, ispada da trebamo podijeliti 2800 sa 6. Iz dobivenog rezultata nalazimo kvadratni korijen, ovo brojka će biti standardna devijacija.

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

Funkcije za izračunavanje standardne devijacije u Excelu

Excel ima nekoliko vrsta formula standardne devijacije. Sve što trebate učiniti je upisati =STDEV i uvjerit ćete se sami.

Vrijedno je napomenuti da funkcije STDEV.V i STDEV.G (prva i druga funkcija na popisu) dupliciraju funkcije STDEV i STDEV (peta i šesta funkcija na popisu), respektivno, koje su ostavljene radi kompatibilnosti s više ranije verzije Excel.

Općenito, razlika u završecima funkcija .B i .G ukazuje na načelo izračuna standardne devijacije uzorka ili populacije. Već sam objasnio razliku između ova dva niza u prethodnom.

Posebna značajka funkcija STANDARDEV i STANDDREV (treća i četvrta funkcija na popisu) je da se pri izračunavanju standardne devijacije niza uzimaju u obzir logičke i tekstualne vrijednosti. Tekst i stvarne Boolean vrijednosti su 1, a lažne Boolean vrijednosti su 0. Ne mogu zamisliti situaciju u kojoj bi mi trebale ove dvije funkcije, pa mislim da se mogu zanemariti.

Moramo se baviti izračunom takvih vrijednosti kao što su varijanca, prosjek standardna devijacija i, naravno, koeficijent varijacije. Vrijedno je obratiti pozornost na izračun potonjeg Posebna pažnja. Vrlo je važno da svaki početnik koji tek počinje raditi s uređivačem proračunskih tablica može brzo izračunati relativnu granicu širenja vrijednosti.

Što je koeficijent varijacije i zašto je potreban?

Dakle, čini mi se da bi bilo korisno napraviti kratki teorijski izlet i razumjeti prirodu koeficijenta varijacije. Ovaj pokazatelj je neophodan za odražavanje raspona podataka u odnosu na prosječnu vrijednost. Drugim riječima, pokazuje omjer standardne devijacije i srednje vrijednosti. Koeficijent varijacije obično se mjeri u postocima i koristi za prikaz homogenosti vremenske serije.

Koeficijent varijacije postat će nezamjenjiv pomoćnik u slučaju kada trebate napraviti prognozu na temelju podataka iz danog uzorka. Ovaj indikator će istaknuti glavne nizove vrijednosti koje će biti najkorisnije za naknadno predviđanje, a također će očistiti uzorak od nevažnih čimbenika. Dakle, ako vidite da je vrijednost koeficijenta 0%, onda pouzdano izjavite da je niz homogen, što znači da su sve vrijednosti u njemu jednake jedna drugoj. Ako koeficijent varijacije ima vrijednost veću od 33%, to znači da imate posla s heterogenim nizom u kojem pojedinačne vrijednosti značajno razlikuju od prosjeka uzorka.

Kako pronaći standardnu devijaciju?

Budući da za izračunavanje indeksa varijacije u Excelu moramo koristiti standardnu devijaciju, bilo bi sasvim prikladno saznati kako možemo izračunati ovaj parametar.

Iz školskog tečaja algebre znamo da je standardna devijacija kvadratni korijen izvađen iz varijance, odnosno ovaj pokazatelj određuje stupanj odstupanja određenog pokazatelja općeg uzorka od njegove prosječne vrijednosti. Njime možemo mjeriti apsolutna mjera fluktuacije proučavane karakteristike i jasno je interpretirati.

Izračunavanje koeficijenta u Excelu

Nažalost, Excel nema standardnu formulu koja bi vam omogućila automatski izračun indeksa varijacije. Ali to ne znači da morate računati u svojoj glavi. Nepostojanje predloška u "Traci s formulama" ni na koji način ne umanjuje mogućnosti Excela, tako da možete vrlo lako prisiliti program da izvrši izračun koji vam je potreban ručnim unosom odgovarajuće naredbe.

Da biste izračunali indeks varijacije u Excelu, morate zapamtiti školski tečaj matematike i podijelite standardnu devijaciju sa sredinom uzorka. To jest, zapravo, formula izgleda ovako - STANDARDEVAL(navedeni raspon podataka)/AVERAGE(navedeni raspon podataka). Ovu formulu morate unijeti u Excel ćeliju u kojoj želite dobiti izračun koji vam je potreban.

Ne zaboravite da, budući da je koeficijent izražen kao postotak, ćeliju s formulom treba formatirati u skladu s tim. To možete učiniti na sljedeći način:

Otvorite karticu Početna.
U njemu pronađite kategoriju "Format ćelije" i odaberite željenu opciju.

Alternativno, možete postaviti postotni format za ćeliju desnim klikom na aktiviranu ćeliju tablice. U kontekstnom izborniku koji se pojavi, slično gornjem algoritmu, trebate odabrati kategoriju "Format ćelije" i postaviti potrebnu vrijednost.

Odaberite Postotak i po potrebi unesite broj decimalnih mjesta

Možda se gornji algoritam nekima može učiniti kompliciranim. Zapravo, izračunavanje koeficijenta jednostavno je poput zbrajanja dva prirodni brojevi. Nakon što završite ovaj zadatak u Excelu, više se nikada nećete vratiti zamornim, složenim rješenjima u bilježnici.

Još uvijek ne mogu kvalitativna usporedba stupanj raspršenosti podataka? Zbunjeni ste veličinom uzorka? Onda se odmah bacite na posao i savladajte sve u praksi teorijsko gradivošto je gore navedeno! Neka Statistička analiza i razvoj prognoze više ne izazivaju strah i negativnost. Uštedite svoju energiju i vrijeme s

Program Excel visoko cijene i profesionalci i amateri, jer s njim mogu raditi korisnici bilo koje razine vještine. Na primjer, svatko s minimalnim "komunikacijskim" vještinama u Excelu može nacrtati jednostavan grafikon, napraviti pristojnu ploču itd.

U isto vrijeme, ovaj program vam čak omogućuje izvođenje raznih vrsta izračuna, na primjer, izračuna, ali to zahtijeva nešto drugačiju razinu obuke. Međutim, ako ste se tek počeli pobliže upoznavati s ovim programom i zanima vas sve što će vam pomoći da postanete napredniji korisnik, ovaj članak je za vas. Danas ću vam reći što je formula standardne devijacije u Excelu, zašto je uopće potrebna i, strogo govoreći, kada se koristi. Ići!

Što je

Počnimo s teorijom. Standardna devijacija obično se naziva kvadratni korijen dobiven iz aritmetičke sredine svih kvadratnih razlika između dostupnih vrijednosti, kao i njihove aritmetičke sredine. Usput, ova se vrijednost obično naziva grčkim slovom "sigma". Standardna devijacija izračunava se pomoću formule STANDARDEVAL; program to radi sam za korisnika.

Poanta je ovaj koncept je identificirati stupanj varijabilnosti instrumenta, odnosno, on je, na svoj način, pokazatelj koji je izvorno iz opisne statistike. Identificira promjene u volatilnosti instrumenta tijekom određenog vremenskog razdoblja. Formule STDEV mogu se koristiti za procjenu standardne devijacije uzorka, zanemarujući Booleove i tekstualne vrijednosti.

Formula

Pomaže u izračunavanju standardne devijacije u excel formula, koji se automatski daje u Excel program. Da biste ga pronašli, morate pronaći odjeljak formule u Excelu, a zatim odabrati onaj koji se zove STANDARDEVAL, tako da je vrlo jednostavno.

Nakon toga, pred vama će se pojaviti prozor u koji ćete morati unijeti podatke za izračun. Posebno treba unijeti dva broja u posebna polja, nakon čega će program sam izračunati standardnu devijaciju za uzorak.

nedvojbeno matematičke formule i izračuni prilično su složeno pitanje i ne mogu se svi korisnici odmah nositi s njim. Međutim, ako zagrebete malo dublje i pogledate problem malo detaljnije, ispada da nije sve tako tužno. Nadam se, koristeći primjer izračuna standardna devijacija uvjereni ste u ovo.