Funkcija standardne devijacije u excelu. Što je standardna devijacija - korištenje funkcije standardne devijacije za izračunavanje standardne devijacije u excelu

Jedan od glavnih alata statističke analize je izračun standardne devijacije. Ovaj vam pokazatelj omogućuje procjenu standardne devijacije za uzorak ili populaciju. Naučimo kako koristiti formulu standardne devijacije u Excelu.

Odmah odredimo što je standardna devijacija i kako izgleda njegova formula. Ova je količina kvadratni korijen aritmetičke sredine kvadrata razlike između svih veličina u nizu i njihove aritmetičke sredine. Za ovaj pokazatelj postoji identičan naziv - standardna devijacija. Oba imena su potpuno jednaka.

Ali, naravno, u Excelu korisnik to ne mora izračunati, jer program radi sve za njega. Naučimo kako izračunati standardnu ​​devijaciju u Excelu.

Izračun u Excelu

Navedenu vrijednost možete izračunati u Excelu pomoću dvije posebne funkcije STDEV.V(na temelju uzorka populacije) i STDEV.G(na temelju opće populacije). Načelo njihovog rada je apsolutno isto, ali se mogu nazvati na tri načina, o čemu ćemo raspravljati u nastavku.

Metoda 1: Čarobnjak za funkcije

Metoda 2: Kartica Formule

Metoda 3: Ručni unos formule

Također postoji način na koji uopće nećete morati pozivati ​​prozor argumenata. Da biste to učinili, morate ručno unijeti formulu.

Kao što vidite, mehanizam za izračunavanje standardne devijacije u Excelu je vrlo jednostavan. Korisnik samo treba unijeti brojeve iz populacije ili reference na ćelije koje ih sadrže. Sve izračune izvodi sam program. Mnogo je teže razumjeti što je izračunati pokazatelj i kako se rezultati izračuna mogu primijeniti u praksi. Ali razumijevanje ovoga već se više odnosi na područje statistike nego na učenje rada sa softverom.

Varijanca je mjera disperzije koja opisuje komparativno odstupanje između vrijednosti podataka i srednje vrijednosti. To je najčešće korištena mjera disperzije u statistici, izračunata zbrajanjem i kvadriranjem odstupanja svake vrijednosti podataka od srednje vrijednosti. Formula za izračunavanje varijance je navedena u nastavku:

s 2 – varijanca uzorka;

x av—srednja vrijednost uzorka;

n — veličina uzorka (broj vrijednosti podataka),

(x i – x avg) je odstupanje od prosječne vrijednosti za svaku vrijednost skupa podataka.

Da bismo bolje razumjeli formulu, pogledajmo primjer. Ne volim baš kuhati, pa to rijetko radim. Međutim, kako ne bih gladovao, s vremena na vrijeme moram otići do štednjaka kako bih proveo plan zasićenja tijela bjelančevinama, mastima i ugljikohidratima. Skup podataka u nastavku pokazuje koliko puta Renat kuha svaki mjesec:

Prvi korak u izračunavanju varijance je određivanje srednje vrijednosti uzorka, koja u našem primjeru iznosi 7,8 puta mjesečno. Ostatak izračuna možete olakšati pomoću sljedeće tablice.

Završna faza izračuna varijance izgleda ovako:

Za one koji vole raditi sve izračune odjednom, jednadžba bi izgledala ovako:

Korištenje metode sirovog brojanja (primjer kuhanja)

Postoji učinkovitiji način za izračunavanje varijance, poznat kao metoda sirovog brojanja. Iako se jednadžba na prvi pogled može činiti prilično glomaznom, zapravo i nije tako strašna. Možete se u to uvjeriti, a zatim odlučiti koja vam se metoda najviše sviđa.

je zbroj svake vrijednosti podataka nakon kvadriranja,

je kvadrat zbroja svih vrijednosti podataka.

Nemoj sada izgubiti razum. Stavimo sve ovo u tablicu i vidjet ćete da ovdje ima manje izračuna nego u prethodnom primjeru.

Kao što vidite, rezultat je bio isti kao i kod prethodne metode. Prednosti ove metode postaju očite kako se veličina uzorka (n) povećava.

Izračun varijance u Excelu

Kao što ste vjerojatno već pogodili, Excel ima formulu koja vam omogućuje izračunavanje varijance. Štoviše, počevši od Excela 2010, možete pronaći 4 vrste formule varijance:

1) VARIANCE.V – Vraća varijancu uzorka. Booleove vrijednosti i tekst se zanemaruju.

2) DISP.G - Vraća varijancu populacije. Booleove vrijednosti i tekst se zanemaruju.

3) VARIJANCIJA - Vraća varijancu uzorka, uzimajući u obzir Booleove i tekstualne vrijednosti.

4) VARIJANCIJA - Vraća varijancu populacije, uzimajući u obzir logičke i tekstualne vrijednosti.

Prvo, shvatimo razliku između uzorka i populacije. Svrha deskriptivne statistike je sažeti ili prikazati podatke kako biste brzo dobili širu sliku, da tako kažem pregled. Statističko zaključivanje omogućuje vam donošenje zaključaka o populaciji na temelju uzorka podataka iz te populacije. Populacija predstavlja sve moguće ishode ili mjerenja koja su nam od interesa. Uzorak je podskup populacije.

Na primjer, zanima nas grupa studenata jednog od ruskih sveučilišta i trebamo odrediti prosječnu ocjenu grupe. Možemo izračunati prosječni uspjeh učenika, a tada će dobivena brojka biti parametar, jer će cijela populacija biti uključena u naše izračune. Međutim, ako želimo izračunati GPA svih učenika u našoj zemlji, onda će ova grupa biti naš uzorak.

Razlika u formuli za izračun varijance između uzorka i populacije je nazivnik. Pri čemu će za uzorak biti jednak (n-1), a za opću populaciju samo n.

Sada pogledajmo funkcije za izračunavanje varijance sa završecima A, u čijem opisu stoji da se u izračunu uzimaju u obzir tekstualne i logičke vrijednosti. U ovom slučaju, prilikom izračunavanja varijance određenog skupa podataka gdje se pojavljuju nenumeričke vrijednosti, Excel će interpretirati tekstualne i lažne Booleove vrijednosti kao jednake 0, a prave Booleove vrijednosti kao jednake 1.

Dakle, ako imate niz podataka, izračunavanje njegove varijance neće biti teško pomoću jedne od gore navedenih Excel funkcija.

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 pokazatelj varijacije, a to je prosječni kvadrat odstupanja od matematičkog očekivanja. Dakle, izražava širenje brojeva oko prosječne 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 ili numeričke vrijednosti ili 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, kod izračuna uzorka nazivnik ne označava ukupan broj brojeva, već 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. Ovu statistiku može izračunati aplikacija, bilo iz populacije ili iz uzorka. 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.

Koncept postotnog odstupanja odnosi se na razliku između dvije numeričke vrijednosti kao postotak. Navedimo konkretan primjer: recimo da je jedan dan iz veleprodajnog skladišta prodano 120 tableta, a sljedeći dan - 150 komada. Razlika u količini prodaje je očigledna, sljedećeg dana prodano je još 30 tableta. Pri oduzimanju broja 120 od 150 dobivamo odstupanje koje je jednako broju +30. Postavlja se pitanje: što je postotak odstupanja?

Kako izračunati postotak odstupanja u Excelu

Postotak odstupanja izračunava se oduzimanjem stare vrijednosti od nove vrijednosti, a zatim dijeljenjem rezultata sa starom vrijednošću. Rezultat ovog izračuna formule u Excelu trebao bi biti prikazan u formatu postotka ćelije. U ovom primjeru formula za izračun je sljedeća (150-120)/120=25%. Formulu je lako provjeriti: 120+25%=150.

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

Slika u nastavku prikazuje primjer kako gornji izračun predstaviti kao Excel formulu. Formula u ćeliji D2 izračunava postotak odstupanja između prodajnih vrijednosti za tekuću i prošlu godinu: =(C2-B2)/B2

Važno je obratiti pozornost na prisutnost zagrada u ovoj formuli. Prema zadanim postavkama, u Excelu operacija dijeljenja uvijek ima prednost nad operacijom oduzimanja. Dakle, ako ne stavimo zagrade, tada će se vrijednost prvo podijeliti, a zatim će se od nje oduzeti druga vrijednost. Takav izračun (bez prisutnosti zagrada) bit će pogrešan. Zatvaranje prvog dijela izračuna u formuli sa zagradama automatski podiže prioritet operacije oduzimanja nad operacijom dijeljenja.

Unesite formulu ispravno sa zagradama u ćeliju D2, a zatim je jednostavno kopirajte u preostale prazne ćelije raspona D2:D5. Kako biste najbrže kopirali formulu, samo pomaknite kursor miša na oznaku kursora tipkovnice (u donji desni kut) tako da se kursor miša promijeni iz strelice u crni križ. Zatim samo dvaput kliknite lijevom tipkom miša i Excel će automatski popuniti prazne ćelije formulom i odrediti raspon D2:D5 koji treba popuniti do ćelije D5 i ne više. Ovo je vrlo zgodan Excel life hack.



Alternativna formula za izračun postotka odstupanja u Excelu

U alternativnoj formuli koja izračunava relativno odstupanje prodajnih vrijednosti od tekuće godine, odmah se podijeli s prodajnim vrijednostima prethodne godine, a tek onda se od rezultata oduzme jedan: =C2/B2-1.

Kao što vidite na slici, rezultat izračuna alternativne formule je isti kao u prethodnoj, a samim tim i točan. Ali alternativnu formulu lakše je napisati, iako je nekima možda teže čitati kako bi razumjeli princip njezina rada. Ili je teže razumjeti koju vrijednost određena formula daje kao rezultat izračuna ako nije potpisana.

Jedini nedostatak ove alternativne formule je nemogućnost izračunavanja postotka odstupanja za negativne brojeve u brojniku ili u zamjeni. Čak i ako u formuli koristimo funkciju ABS, formula će vratiti pogrešan rezultat ako je broj u zamjeni negativan.

Budući da je u Excelu prema zadanim postavkama prioritet operacije dijeljenja veći od operacije oduzimanja, u ovoj formuli nema potrebe koristiti zagrade.

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, prema tome program to radi za samog korisnika.

Bit ovog koncepta je identificirati stupanj varijabilnosti instrumenta, odnosno on je, na svoj način, pokazatelj izveden iz deskriptivne 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

Formula koja se automatski daje u Excelu pomaže izračunati standardnu ​​devijaciju u Excelu. 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.

Bez sumnje, 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 da ste se u to uvjerili na primjeru izračuna standardne devijacije.

Video za pomoć