Standardna devijacija funkcije u excelu. Što je standardna devijacija - korištenje funkcije standardne devijacije za izračun 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 za opću populaciju. Naučimo kako koristiti formulu standardne devijacije u Excelu.

Definirajmo odmah što je standardna devijacija i kako izgleda njezina formula. Ova vrijednost je kvadratni korijen aritmetičke sredine kvadrata razlike između svih vrijednosti niza 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.B(prema uzorku) i STDEV.G(prema općoj populaciji). 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 morate pozivati ​​prozor argumenata. Da biste to učinili, ručno unesite formulu.

Kao što možete vidjeti, mehanizam za izračunavanje standardne devijacije u Excelu je vrlo jednostavan. Korisnik samo treba unijeti brojeve iz populacije ili poveznice 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ć pripada više području statistike nego učenju rada sa softverom.

Disperzija je mjera disperzije koja opisuje relativno odstupanje između vrijednosti podataka i srednje vrijednosti. To je najčešće korištena mjera disperzije u statistici, izračunata zbrajanjem, na kvadrat, odstupanja svake vrijednosti podataka od srednje vrijednosti. Formula za izračun varijance je prikazana u nastavku:

s 2 - varijanca uzorka;

x cf srednja vrijednost uzorka;

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

(x i – x cf) je odstupanje od srednje vrijednosti za svaku vrijednost skupa podataka.

Da bismo bolje razumjeli formulu, pogledajmo primjer. Ne volim baš kuhati pa se time rijetko bavim. Međutim, kako ne bih umro od gladi, 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 hranu 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. Preostali izračuni mogu se olakšati uz pomoć sljedeće tablice.

Konačna faza izračuna varijance izgleda ovako:

Za one koji vole sve izračune raditi odjednom, jednadžba će izgledati ovako:

Korištenje metode sirovog brojanja (primjer kuhanja)

Postoji učinkovitiji način za izračunavanje varijance, poznat kao metoda "sirovog brojanja". Iako se na prvi pogled jednadžba može činiti prilično glomaznom, zapravo i nije tako strašna. To možete provjeriti, 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 to u obliku tablice, pa ćete vidjeti da ovdje ima manje izračuna nego u prethodnom primjeru.

Kao što vidite, rezultat je isti kao kod prethodne metode. Prednosti ove metode postaju očite kako raste veličina uzorka (n).

Izračunavanje 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 varijante disperzijske formule:

1) VAR.V - Vraća varijancu uzorka. Booleove vrijednosti i tekst se zanemaruju.

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

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

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

Prvo, pogledajmo razliku između uzorka i populacije. Svrha deskriptivne statistike je sažeti ili prikazati podatke na takav način da se brzo dobije velika slika, da tako kažem, pregled. Statističko zaključivanje omogućuje vam da donosite zaključke 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 ukupna 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 u nazivniku. Pri čemu će za uzorak biti jednak (n-1), a za opću populaciju samo n.

Sada se pozabavimo funkcijama izračuna varijance sa završecima A, u čijem opisu je rečeno da izračun uzima 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 tekst i lažne Booleove vrijednosti kao 0, a prave Booleove vrijednosti kao 1.

Dakle, ako imate niz podataka, neće biti teško izračunati njegovu varijancu 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, u Excelu postoje 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 srednje vrijednosti. Izračun disperzije može se provesti i za opću populaciju i za uzorak.

Metoda 1: izračun na općoj populaciji

Za izračun ovog pokazatelja u Excelu za opću populaciju koristi se funkcija DISP.G. Sintaksa za ovaj izraz je sljedeća:

DISP.G(Broj1;Broj2;…)

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

Pogledajmo kako izračunati ovu vrijednost za niz numeričkih podataka.

Metoda 2: izračun uzorka

Za razliku od izračuna vrijednosti za opću populaciju, kod izračuna za uzorak nazivnik nije ukupan broj brojeva, već jedan manji. Ovo se radi kako bi se ispravila greška. 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:

VAR.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 aplikacija može izračunati i za populaciju i za uzorak. U ovom slučaju, sve korisničke radnje zapravo se svode samo na određivanje raspona brojeva koji se obrađuju, a Excel sam obavlja glavni posao. Naravno, ovo će značajno uštedjeti vrijeme korisnicima.

Koncept postotka odstupanja podrazumijeva razliku između dvije numeričke vrijednosti u postocima. Navedimo konkretan primjer: recimo da je jedan dan iz veleprodajnog skladišta prodano 120 komada tableta, a drugi dan - 150 komada. Razlika u prodajnim količinama je očita, sljedeći dan prodano je još 30 tableta. Pri oduzimanju broja 120 od 150 dobivamo odstupanje koje je jednako broju +30. Postavlja se pitanje: koliki je postotak odstupanja?

Kako izračunati postotak odstupanja u Excelu

Postotak odstupanja izračunava se oduzimanjem stare vrijednosti od nove vrijednosti i potom dijeljenjem rezultata sa starom vrijednošću. Rezultat izračuna ove formule u Excelu trebao bi biti prikazan u postotnom obliku ćelije. U ovom primjeru formula za izračun izgleda ovako (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 varijance između trenutne i prošlogodišnje prodaje: =(C2-B2)/B2

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

Ispravno pomoću zagrada unesite formulu u ćeliju D2, a zatim je jednostavno kopirajte u ostale prazne ćelije u rasponu 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ž. Nakon toga samo dvaput kliknete lijevom tipkom miša i Excel će automatski popuniti prazne ćelije formulom, pri čemu će odrediti raspon D2:D5 koji mora biti popunjen 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 prodajnih vrijednosti od tekuće godine, odmah se podijeli s prodajnim vrijednostima prethodne godine, a tek onda se od rezultata oduzme jedan: \u003d C2 / B2-1.

Kao što vidite na slici, rezultat izračuna alternativne formule je isti kao u prethodnoj, što znači da je točan. Ali alternativnu formulu lakše je zapisati, iako je nekome možda teže pročitati kako bi razumio kako funkcionira. Ili je teže razumjeti koju vrijednost dana 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 zamjeni. Čak i ako u formuli koristimo funkciju ABS, 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, u ovoj formuli nema potrebe koristiti zagrade.

Program Excel visoko cijene i profesionalci i amateri, jer s njim može raditi korisnik bilo koje razine obuke. Na primjer, svatko s minimalnim vještinama "komunikacije" s Excelom može nacrtati jednostavan grafikon, napraviti pristojan znak itd.

U isto vrijeme, ovaj program vam čak omogućuje izvođenje raznih vrsta izračuna, na primjer, izračun, ali to već zahtijeva nešto drugačiju razinu obuke. Međutim, ako ste tek započeli blisko upoznavanje s ovim programom i zanima vas sve što će vam pomoći da postanete napredniji korisnik, ovaj je članak za vas. Danas ću vam reći što je formula standardne devijacije u Excelu, zašto je uopće potrebna i, zapravo, kada se primjenjuje. 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čko slovo "sigma". Standardna devijacija izračunava se pomoću formule STDEV, odnosno program to radi za samog korisnika.

Bit ovog koncepta je identificirati stupanj varijabilnosti instrumenta, odnosno on je na svoj način pokazatelj iz deskriptivne statistike. Otkriva promjene u volatilnosti instrumenta u bilo kojem vremenskom razdoblju. Koristeći STDEV formule, možete procijeniti standardnu ​​devijaciju uzorka, dok se booleove i tekstualne vrijednosti zanemaruju.

Formula

Pomaže izračunati standardnu ​​devijaciju u excel formuli, koja se automatski daje u Excelu. Da biste ga pronašli, morate pronaći odjeljak formule u Excelu i već tamo odabrati onu koja ima naziv STDEV, tako da je vrlo jednostavno.

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

Bez sumnje, matematičke formule i izračuni prilično su komplicirano pitanje i ne mogu se svi korisnici nositi s njim odmah. Međutim, ako kopate malo dublje i malo detaljnije shvatite problem, ispada da nije sve tako tužno. Nadam se da ste se u to uvjerili na primjeru izračuna standardne devijacije.

Video za pomoć