Kako standardna devijacija funkcionira u Excelu. Funkcija standardne devijacije

Dobar dan

U ovom članku odlučio sam pogledati kako standardna devijacija funkcionira u Excelu pomoću funkcije STANDARDEVAL. Samo to nisam dugo opisivao niti komentirao, a i jednostavno zato što je vrlo korisna funkcija za one koji studiraju višu matematiku. A pomaganje studentima je svetinja; iz iskustva znam koliko je teško savladati. U stvarnosti, funkcije standardne devijacije mogu se koristiti za određivanje stabilnosti prodanih proizvoda, kreiranje cijena, prilagođavanje ili formiranje asortimana i druge jednako korisne analize vaše prodaje.

Excel koristi nekoliko varijacija ove funkcije varijanse:

Matematička teorija

Prvo, malo o teoriji, kako možete opisati funkciju standardne devijacije u matematičkom jeziku za korištenje u Excelu, za analizu, na primjer, podataka statistike prodaje, ali više o tome kasnije. Odmah upozoravam, napisaću dosta nerazumljivih reči...)))), ako išta ispod teksta, odmah potražite praktičnu primenu u programu.

Šta tačno radi standardna devijacija? On procjenjuje standardnu ​​devijaciju slučajne varijable X u odnosu na njeno matematičko očekivanje na osnovu nepristrasne procjene njene varijanse. Slažem se, zvuči zbunjujuće, ali mislim da će studenti razumjeti o čemu zapravo govorimo!

Prvo, moramo odrediti “standardnu ​​devijaciju”, da bismo naknadno izračunali “standardnu ​​devijaciju”, formula će nam pomoći u tome: Formula se može opisati na sljedeći način: mjerit će se u istim jedinicama kao mjerenja slučajne varijable i koristi se pri izračunavanju standardne aritmetičke srednje greške, pri konstruiranju intervala povjerenja, pri testiranju hipoteza za statistiku ili pri analizi linearne odnos između nezavisnih varijabli. Funkcija je definirana kao kvadratni korijen varijanse nezavisnih varijabli.

Sada možemo definisati i standardna devijacija je analiza standardne devijacije slučajne varijable X u odnosu na njenu matematičku perspektivu zasnovanu na nepristrasnoj procjeni njene varijanse. Formula je napisana ovako:
Napominjem da su sve dvije procjene pristrasne. U općim slučajevima, nije moguće napraviti nepristrasnu procjenu. Ali procjena zasnovana na procjeni nepristrasne varijanse bit će konzistentna.

Praktična implementacija u Excel-u

Pa, hajde da se sada odmaknemo od dosadne teorije i da vidimo u praksi kako funkcioniše funkcija STANDARDEVAL. Neću razmatrati sve varijacije funkcije standardne devijacije u Excelu; jedna je dovoljna, ali u primjerima. Kao primjer, pogledajmo kako se utvrđuje statistika stabilnosti prodaje.

Prvo, pogledajte pravopis funkcije, i kao što vidite, vrlo je jednostavno:

STANDARDNO ODSTUPANJE.G(_broj1_;_broj2_; ….), gdje je:

Sada napravimo primjer datoteke i, na osnovu nje, razmotrimo kako ova funkcija funkcionira. Pošto je za izvođenje analitičkih proračuna potrebno koristiti najmanje tri vrijednosti, kao u principu u svakoj statističkoj analizi, uzeo sam uslovno 3 perioda, to može biti godina, kvartal, mjesec ili sedmica. U mom slučaju - mesec dana. Za maksimalnu pouzdanost, preporučujem da uzmete što više perioda, ali ne manje od tri. Svi podaci u tabeli su vrlo jednostavni radi jasnoće rada i funkcionalnosti formule.

Prvo, trebamo izračunati prosječnu vrijednost po mjesecima. Za ovo ćemo koristiti funkciju AVERAGE i dobiti formulu: = AVERAGE(C4:E4).
Sada, zapravo, standardnu ​​devijaciju možemo pronaći pomoću funkcije STANDARDEVAL.G, u čiju vrijednost trebamo unijeti prodaju proizvoda za svaki period. Rezultat će biti formula sljedećeg oblika: =STANDARDNO ODSTUPANJE.G(C4;D4;E4).
Pa, pola posla je obavljeno. Sljedeći korak je formiranje „Varijacije“, ovo se dobija dijeljenjem sa prosječnom vrijednošću, standardnom devijacijom i pretvaranjem rezultata u procente. Dobijamo sledeću tabelu:
Pa, osnovne kalkulacije su završene, ostaje samo da se utvrdi da li je prodaja stabilna ili ne. Uzmimo kao uslov da se odstupanja od 10% smatraju stabilnim, od 10 do 25% to su mala odstupanja, ali sve iznad 25% više nije stabilno. Da bismo dobili rezultat u skladu sa uslovima, koristićemo logički i da bismo dobili rezultat zapisaćemo formulu:

IF(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

Svi rasponi su uzeti radi jasnoće; vaši zadaci mogu imati potpuno različite uvjete.
Da biste poboljšali vizualizaciju podataka, kada vaša tabela ima hiljade pozicija, trebali biste iskoristiti priliku da primijenite određene uslove koji su vam potrebni ili koristite da istaknete određene opcije shemom boja, to će biti vrlo jasno.

Prvo odaberite one za koje ćete primijeniti uvjetno oblikovanje. Na kontrolnoj tabli “Početna” odaberite “Uvjetno oblikovanje” i u padajućem izborniku odaberite “Pravila za isticanje ćelija” i zatim kliknite na stavku menija “Tekst sadrži...”. Pojavljuje se okvir za dijalog u koji unosite svoje uslove.

Nakon što zapišete uslove, na primjer, "stabilno" - zeleno, "normalno" - žuto i "nestabilno" - crveno, dobijamo lijepu i razumljivu tabelu u kojoj možete vidjeti na šta prvo treba obratiti pažnju.

Korištenje VBA za funkciju STDEV.Y

Svi zainteresovani mogu automatizirati svoje proračune pomoću makronaredbi i koristiti sljedeću funkciju:

Funkcija MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Za svaki x In Arr aSum = aSum + x "izračunajte zbir elemenata niza aCnt = aCnt + 1" izračunajte broj elemenata Next x aAver = aSum / aCnt "prosječna vrijednost za svaki x In Arr tmp = tmp + (x - aAver) ^ 2 "izračunajte zbir kvadrata razlike između elemenata niza i prosječne vrijednosti Next x MyStDevP = Sqr(tmp / aCnt ) "izračunaj STANDARDEV.G() Krajnju funkciju

Funkcija MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# Za svaki x U Arr aZbroj = aZbroj + x "izračunajte zbir elemenata niza

U ovom članku ću govoriti o kako pronaći standardnu ​​devijaciju. Ovaj materijal je izuzetno važan za potpuno razumijevanje matematike, tako da bi nastavnik matematike trebao posvetiti posebnu lekciju ili čak nekoliko lekcija tome. U ovom članku ćete pronaći vezu na detaljan i razumljiv video vodič koji objašnjava šta je standardna devijacija i kako je pronaći.

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

Formula za izračun je prilično jednostavna. Da biste pronašli standardnu ​​devijaciju, morate uzeti kvadratni korijen varijanse. Dakle, sada morate da se zapitate: "Šta je varijansa?"

Šta je varijansa

Definicija varijanse ide ovako. Disperzija je aritmetička sredina kvadrata odstupanja vrijednosti od srednje vrijednosti.

Da biste pronašli varijansu, izvršite sljedeće proračune uzastopno:

• Odredite prosek (jednostavnu aritmetičku sredinu niza vrednosti).
• Zatim oduzmite prosjek od svake vrijednosti i kvadrirate rezultujuću razliku (dobijete razlika na kvadrat).
• Sljedeći korak je izračunavanje aritmetičke sredine rezultujućih kvadrata razlika (u nastavku možete saznati zašto upravo kvadrati).

Pogledajmo primjer. Recimo da ste vi i vaši prijatelji odlučili izmjeriti visinu vaših 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, varijansu i standardnu ​​devijaciju.

Prvo pronađimo prosječnu vrijednost. Kao što već znate, da biste to učinili, morate sabrati sve izmjerene vrijednosti i podijeliti ih sa brojem mjerenja. Napredak proračuna:

Prosjek mm.

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

Sada treba da odredimo odstupanje visine svakog psa od prosjeka:

konačno, za izračunavanje varijanse, kvadriramo svaku od rezultirajućih razlika, a zatim pronađemo aritmetičku sredinu dobivenih rezultata:

Disperzija mm 2 .

Dakle, disperzija je 21704 mm 2.

Kako pronaći standardnu ​​devijaciju

Dakle, kako sada možemo izračunati standardnu ​​devijaciju, znajući varijansu? Kao što se sjećamo, uzmite 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, rotvajleri) vrlo veliki psi. Ali postoje i vrlo mali psi (na primjer, jazavčari, ali to im ne biste trebali reći).

Najzanimljivije je da standardna devijacija nosi korisne informacije. Sada možemo pokazati koji se od dobijenih rezultata mjerenja visine nalaze unutar intervala koji dobijemo ako iscrtamo standardnu ​​devijaciju od prosjeka (na obje njegove strane).

Odnosno, koristeći standardnu ​​devijaciju, dobijamo "standardnu" metodu koja nam omogućava da saznamo koja je od vrijednosti normalna (statistički prosjek), a koja je izuzetno velika ili, obrnuto, mala.

Šta je standardna devijacija

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

Ali ako su podaci uzorak (vrijednosti odabrane iz velike populacije), onda je potrebno izračunati drugačije.

Ako postoje vrijednosti, onda:

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

Na primjer, ako je naših pet pasa samo uzorak populacije pasa (svi psi na planeti), moramo podijeliti sa 4, ne 5, naime:

Varijanca uzorka = mm 2.

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

Možemo reći da smo napravili neku „ispravku“ u slučaju kada su naše vrijednosti samo mali uzorak.

Bilješka. Zašto tačno kvadratne razlike?

Ali zašto uzimamo upravo kvadratne razlike kada računamo varijansu? 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) zajedno... negativne vrijednosti se poništavaju sa pozitivnim:

.

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

Na prvi pogled ispada dobro (rezultirajuća vrijednost, inače, naziva se srednja apsolutna devijacija), ali ne u svim slučajevima. Pokušajmo sa drugim primjerom. Neka mjerenje rezultira sljedećim skupom vrijednosti: 7; 1; -6; -2. Tada je prosječno apsolutno odstupanje:

Vau! Opet smo dobili rezultat 4, iako su razlike znatno veće.

Sada da vidimo šta će se dogoditi ako kvadriramo razlike (a zatim uzmemo kvadratni korijen njihovog zbira).

Za prvi primjer to će biti:

.

Za drugi primjer bit će:

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

Zapravo, ova metoda koristi istu ideju kao kod izračunavanja udaljenosti između tačaka, samo primijenjena na drugačiji način.

A sa matematičke tačke gledišta, korištenje kvadrata i kvadratnog korijena daje više koristi nego što bismo mogli dobiti od apsolutnih vrijednosti odstupanja, čineći standardnu ​​devijaciju primjenjivom na druge matematičke probleme.

Sergej Valerievič vam je rekao kako pronaći standardnu ​​devijaciju

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

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, trebate sabrati sve brojeve u skupu i podijeliti zbir s količinom. Na primjer, ocjene učenika iz informatike: 3, 4, 3, 5, 5. Šta je uključeno u tromjesečje: 4. Pronašli smo aritmetičku sredinu koristeći formulu: =(3+4+3+5+5) /5.

Kako to brzo učiniti koristeći Excel funkcije? Uzmimo za primjer niz nasumičnih brojeva u nizu:

Ili: napravite aktivnu ćeliju i jednostavno unesite formulu ručno: =PROSJEČNO(A1:A8).

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

Nađimo aritmetičku sredinu prva dva i zadnja tri broja. Formula: =PROSJEK(A1:B1,F1:H1). rezultat:

Stanje prosečno

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

Pronađite aritmetičku sredinu brojeva koji su veći ili jednaki 10.

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

Treći argument - "Raspon usrednjavanja" - je izostavljen. Prije svega, nije potrebno. Drugo, opseg analiziran programom sadrži SAMO numeričke vrijednosti. Ćelije navedene u prvom argumentu će se pretraživati ​​u skladu sa uvjetom navedenim u drugom argumentu.

Pažnja! U ćeliji se može odrediti kriterij pretraživanja. I napravite vezu do njega u formuli.

Nađimo prosječnu vrijednost brojeva koristeći tekstualni kriterij. 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 rečju „tabele“ (možete umetnuti reč „tabele“ umesto veze A7). Raspon prosjeka – ć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 smo saznali 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 Excelu

Postoje standardne devijacije za opštu populaciju i za uzorak. U prvom slučaju, ovo je korijen opšte varijanse. U drugom, iz varijanse uzorka.

Za izračunavanje ovog statističkog pokazatelja sastavlja se formula disperzije. Iz njega se vadi korijen. Ali u Excelu postoji gotova funkcija za pronalaženje standardne devijacije.

Standardna devijacija je vezana za skalu izvornih podataka. Ovo nije dovoljno za figurativni prikaz varijacije analiziranog raspona. Da bi se dobio relativni nivo rasipanja podataka, izračunava se koeficijent varijacije:

standardna devijacija / aritmetička sredina

Formula u Excelu izgleda ovako:

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

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

Instrukcije

Neka postoji nekoliko brojeva koji karakterišu homogene veličine. Na primjer, rezultati mjerenja, vaganja, statistička posmatranja itd. Sve prikazane količine moraju biti izmjerene pomoću istog mjerenja. Da biste pronašli standardnu ​​devijaciju, učinite sljedeće:

Odredite aritmetičku sredinu svih brojeva: saberite sve brojeve i podijelite zbir ukupnim brojem brojeva.

Odredite disperziju (raspršenost) brojeva: dodajte kvadrate prethodno pronađenih odstupanja i rezultujući zbroj podijelite sa brojem brojeva.

Na odjeljenju je sedam pacijenata sa temperaturom od 34, 35, 36, 37, 38, 39 i 40 stepeni Celzijusa.

Potrebno je odrediti prosječno odstupanje od srednje vrijednosti.
Rješenje:
“na odjelu”: (34+35+36+37+38+39+40)/7=37 ºS;

Odstupanja temperature od prosjeka (u ovom slučaju normalne vrijednosti): 34-37, 35-37, 36-37, 37-37, 38-37, 39-37, 40-37, što rezultira: -3, - 2, -1 , 0, 1, 2, 3 (ºS);

Podijelite zbir prethodno dobijenih brojeva njihovim brojem. Za tačne proračune, bolje je koristiti kalkulator. Rezultat dijeljenja je aritmetička sredina dodanih brojeva.

Obratite pažnju na sve faze proračuna, jer će greška čak i u jednom od proračuna dovesti do pogrešnog konačnog indikatora. Provjerite svoje proračune u svakoj fazi. Aritmetički prosjek ima isti metar kao i zbrojeni brojevi, odnosno, ako odredite prosječnu posjećenost, tada će svi vaši pokazatelji biti "osoba".

Ova metoda proračuna se koristi samo u matematičkim i statističkim proračunima. Na primjer, aritmetička sredina u informatici ima drugačiji algoritam proračuna. Aritmetička sredina je vrlo relativan pokazatelj. Pokazuje vjerovatnoću događaja, pod uslovom da ima samo jedan faktor ili indikator. Za najdublju analizu potrebno je uzeti u obzir mnoge faktore. U tu svrhu koristi se proračun opštijih veličina.

Aritmetička sredina je jedna od mjera centralne tendencije, koja se široko koristi u matematici i statističkim proračunima. Pronalaženje aritmetičkog prosjeka za nekoliko vrijednosti je vrlo jednostavno, ali svaki zadatak ima svoje nijanse, koje je jednostavno potrebno znati kako biste izvršili ispravne proračune.

Kvantitativni rezultati sličnih eksperimenata.

Kako pronaći aritmetičku sredinu

Značajke rada s negativnim brojevima

1. Pronalaženje opšte aritmetičke sredine standardnom metodom;
2. Pronalaženje aritmetičke sredine negativnih brojeva.
3. Izračunavanje aritmetičke sredine pozitivnih brojeva.

Odgovori za svaku radnju su napisani odvojeni zarezima.

Prirodni i decimalni razlomci

Kada radite s prirodnim razlomcima, treba ih svesti na zajednički nazivnik, koji se množi brojem brojeva u nizu. Brojač odgovora će biti zbir datih brojnika originalnih razlomaka.

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 varijanse je data u nastavku:

s 2 – varijansa 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š da kuvam, pa to retko radim. Međutim, da ne bih gladovao, s vremena na vrijeme moram otići do štednjaka da provedem plan zasićenja tijela proteinima, mastima i ugljikohidratima. Donji skup podataka pokazuje koliko puta Renat kuha svakog mjeseca:

Prvi korak u izračunavanju varijanse je određivanje srednje vrijednosti uzorka, koja je u našem primjeru 7,8 puta mjesečno. Ostatak proračuna može se olakšati korištenjem sljedeće tabele.

Završna faza izračunavanja varijanse izgleda ovako:

Za one koji vole da sve proračune rade u jednom potezu, jednadžba bi izgledala ovako:

Korištenje metode sirovog brojanja (primjer kuhanja)

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

je zbir svake vrijednosti podataka nakon kvadriranja,

je kvadrat zbira svih vrijednosti podataka.

Ne gubi razum sada. Hajde da sve ovo stavimo u tabelu i videćete da ovde ima manje proračuna nego u prethodnom primeru.

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

Kalkulacija varijanse u Excel-u

Kao što ste verovatno već pretpostavili, Excel ima formulu koja vam omogućava da izračunate varijansu. Štaviše, počevši od Excel 2010, možete pronaći 4 vrste formule varijanse:

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

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

3) VARIANCE - Vraća varijansu uzorka, uzimajući u obzir Boolean i tekstualne vrijednosti.

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

Prvo, shvatimo razliku između uzorka i populacije. Svrha deskriptivne statistike je da sumira ili prikaže podatke tako da brzo dobijete širu sliku, pregled da tako kažemo. Statističko zaključivanje vam omogućava da napravite zaključke o populaciji na osnovu uzorka podataka iz te populacije. Populacija predstavlja sve moguće ishode ili mjerenja koja nas zanimaju. Uzorak je podskup populacije.

Na primjer, zainteresovani smo za grupu studenata sa jednog od ruskih univerziteta i treba da odredimo prosječan rezultat grupe. Možemo izračunati prosječan uspjeh učenika, a onda će dobijena cifra biti parametar, budući da će cijela populacija biti uključena u naše proračune. Međutim, ako želimo da izračunamo GPA svih učenika u našoj zemlji, onda će ova grupa biti naš uzorak.

Razlika u formuli za izračunavanje varijanse između uzorka i populacije je imenilac. Pri čemu će za uzorak to biti jednako (n-1), a za opštu populaciju samo n.

Pogledajmo sada funkcije za izračunavanje varijanse sa završecima A,čiji opis navodi da se u proračunu uzimaju u obzir tekstualne i logičke vrijednosti. U ovom slučaju, prilikom izračunavanja varijanse određenog skupa podataka u kojem se pojavljuju nenumeričke vrijednosti, Excel će tumačiti tekst i lažne Booleove vrijednosti kao jednake 0, a prave Booleove vrijednosti kao jednake 1.

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