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)
Ima ih još efikasan metod izračunavanje varijanse, poznato 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 ovu metodu postaju očigledni 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 od stanovništva. 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. Totalnost predstavlja sve mogući ishodi ili mjerenja koja nas zanimaju. Uzorak je podskup populacije.
Na primjer, zanima nas ukupnost grupe učenika iz jedne od ruski univerziteti i moramo odrediti 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, u čijem opisu se kaže da se u proračunu uzima u obzir tekst i boolean vrijednosti. IN u ovom slučaju prilikom izračunavanja varijanse određenog niza podataka, gdje ih nema numerič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.
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 tako dalje, ni manje ni više. korisne analize vašu prodaju.
Excel koristi nekoliko varijacija ove funkcije varijanse:
Matematička teorija
Prvo, malo o teoriji kako matematički jezik možete opisati funkciju standardna devijacija za korištenje u Excelu, za analizu, na primjer, podataka statistike prodaje, ali 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 proizvodi procjenu standardne devijacije slučajna varijabla X u odnosu na nju matematičko očekivanje na osnovu nepristrasne procjene njegove 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: ona će se mjeriti u istim jedinicama kao mjerenja slučajne varijable i koristi se pri izračunavanju standardne aritmetičke srednje greške kada se izrađuju konstrukcije intervali poverenja, kada se testiraju hipoteze za statistiku ili kada se analizira linearni odnos između nezavisne količine. Funkcija je definirana kao Kvadratni korijen iz 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. At opšti slučajevi 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 formiramo „Varijaciju“, 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 za isticanje određenih opcija shemom boja, to će biti vrlo jasno.
Prvo odaberite one za koje ćete primijeniti uvjetno oblikovanje. U kontrolnom panelu “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 |
Statistika koristi ogroman broj indikatora, a jedan od njih je izračunavanje varijanse u Excel-u. Ako to uradite sami ručno, to će potrajati dosta vremena i možete napraviti mnogo grešaka. Danas ćemo pogledati kako rastaviti matematičke formule na jednostavne funkcije. Pogledajmo neke od najjednostavnijih, najbržih i najprikladnijih metoda izračuna koje će vam omogućiti da sve obavite za nekoliko minuta.
Izračunajte varijansu
Varijanca slučajne varijable je matematičko očekivanje kvadratnog odstupanja slučajne varijable od njenog matematičkog očekivanja.
Računamo na osnovu opšte populacije
Za izračunavanje mat. Čeka se da program koristi funkciju DISP.G, a njegova sintaksa izgleda ovako: “=DISP.G(Broj1;Broj2;…)”.
Može se koristiti najviše 255 argumenata, ne više. Argumenti mogu biti prosti brojevi ili reference na ćelije u kojima su specificirani. Pogledajmo kako izračunati varijansu u programu Microsoft Excel:
1. Prvi korak je da odaberete ćeliju u kojoj će biti prikazan rezultat izračuna, a zatim kliknete na dugme „Ubaci funkciju“.
2. Otvorit će se ljuska za upravljanje funkcijama. Tamo trebate potražiti funkciju “DISP.G”, koja može biti u kategoriji “Statistički” ili “Puna abecedna lista”. Kada se pronađe, odaberite ga i kliknite na “OK”.
3. Otvoriće se prozor sa argumentima funkcije. U njemu trebate odabrati redak "Broj 1" i na listu odabrati raspon ćelija sa nizom brojeva.
4. Nakon toga, rezultati proračuna će biti prikazani u ćeliji u koju je funkcija unesena.
Ovako možete lako pronaći varijansu u Excelu.
Računamo na osnovu uzorka
U ovom slučaju, varijansa uzorka u Excelu se izračunava tako da imenilac ne pokazuje ukupan broj brojeva, već jedan manje. Ovo se radi za manju grešku pomoću posebne funkcije DISP.V, čija je sintaksa =DISP.V(Broj1;Broj2;...). Algoritam akcija:
- Kao iu prethodnoj metodi, potrebno je odabrati ćeliju za rezultat.
- U čarobnjaku za funkcije, trebali biste pronaći “DISP.B” u kategoriji “Puna abecedna lista” ili “Statistički”.
- Zatim će se pojaviti prozor i treba da postupite na isti način kao u prethodnoj metodi.
Video: Izračunavanje varijanse u Excelu
Zaključak
Varijanca se u Excelu izračunava vrlo jednostavno, mnogo brže i praktičnije nego da se to radi ručno, jer je funkcija matematičkog očekivanja prilično složena i njeno izračunavanje može oduzeti mnogo vremena i truda.
Među brojnim indikatorima koji se koriste u statistici, potrebno je izdvojiti obračun varijanse. Treba napomenuti da je ručno izvođenje ovog proračuna prilično zamoran zadatak. Srećom, Excel ima funkcije koje vam omogućavaju automatizaciju postupka izračunavanja. Hajde da saznamo algoritam za rad sa ovim alatima.
Disperzija je indikator varijacije, što je prosječni kvadrat odstupanja od matematičkog očekivanja. Dakle, izražava širenje brojeva oko prosječne vrijednosti. Izračunavanje varijanse može se izvršiti i za opštu populaciju i za uzorak.
Metoda 1: izračunavanje na osnovu 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 sa numeričkim podacima.
Metoda 2: proračun po uzorku
Za razliku od izračunavanja vrijednosti na osnovu populacije, pri izračunavanju uzorka imenilac 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 proračuna - DISP.V. Njegova sintaksa je predstavljena sljedećom formulom:
DISP.B(Broj1;Broj2;…)
Broj argumenata, kao iu prethodnoj funkciji, također može biti u rasponu od 1 do 255.
Kao što vidite, Excel program može uvelike olakšati izračunavanje varijanse. Ova statistika može biti izračunata aplikacijom, bilo iz populacije ili iz uzorka. U ovom slučaju, sve radnje korisnika zapravo se svode na specificiranje opsega brojeva koji će se obraditi, a Excel sam obavlja glavni posao. Naravno, ovo će uštedjeti značajnu količinu vremena korisnika.
Funkcija standardne devijacije je već iz kategorije više matematike koja se odnosi na statistiku. Postoji nekoliko opcija za korištenje funkcije standardne devijacije u Excelu:
- STANDARDEV funkcija.
- Funkcija STANDARDNO ODSTUPANJE.
- 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 kreiranje (prilagođavanje) 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 koristi u analizi statistike prodaje.
Dakle, standardna devijacija je procjena standardne devijacije slučajne varijable x u pogledu njegovog matematičkog očekivanja zasnovanog na nepristrasnoj procjeni njegove varijanse)))) Ne bojte se nerazumljivih riječi, budite strpljivi i sve ćete razumjeti!
Opis formule: Standardna devijacija se mjeri u mjernim 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 linearnog odnosa 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 u odnosu na njegovo matematičko očekivanje zasnovano na nepristrasnoj procjeni njegove varijanse:
disperzija;
- i th element selekcije;
Veličina uzorka;
Aritmetička sredina uzorka:
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 i nije 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. Tako se pravilo tri sigma 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 previše zamarao matematikom? Možda će nekome ove informacije trebati za esej ili neku drugu svrhu. Pogledajmo sada 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:
Hajde da napravimo knjigu i improvizovani sto. Ovaj primjer ćete preuzeti u Excelu na kraju članka.
Nastavlja se!!!
Zdravo opet. Pa!? Imao sam slobodan minut. Da nastavimo?
I tako stabilnost prodaje uz pomoć STDEV funkcije
Radi jasnoće, uzmimo nekoliko improviziranih proizvoda:
U analitici, bilo da se radi o prognozi, istraživanju ili bilo čemu drugom u vezi sa statistikom, 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 može vidjeti š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 za prosječnu vrijednost je PROSEK (podaci perioda), u mom slučaju formula izgleda ovako = PROSEK (C6: E6)
Formulu primjenjujemo na sve proizvode. To se može učiniti tako što ćete uhvatiti desni ugao odabrane ćelije i povući je na kraj liste. Ili postavite kursor na kolonu s proizvodom i pritisnite sljedeće kombinacije tipki:
Ctrl + Dolje pomiče kursor na vrh liste.
Ctrl + Desno, kursor se pomiče na desnu stranu tabele. Još jednom desno i doći ćemo do stupca sa formulom.
Sada stezamo
Ctrl + Shift i pritisnite gore. Na ovaj način ćemo odabrati područje gdje će se formula nacrtati.
A kombinacija tipki Ctrl + D će povući 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ća faza, sama funkcija standardnog odlaska, kao što sam već rekao, koristit ćemo samo jednu STDEV
Zapisujemo funkciju i postavljamo vrijednosti prodaje za svaki period u vrijednostima funkcije. Ako imate prodaje u tabeli jednu za drugom, možete koristiti raspon, kao u mojoj formuli =STDEV(C6:E6) ili navesti potrebne ćelije odvojene tačkom i zarezom =STDEV(C6;D6;E6)
Sada su svi proračuni spremni. Ali kako znati šta se stalno prodaje, a šta ne? Hajde samo da stavimo 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 vrijednost varijacije prelazi 25%, to nije stabilnost.
Da bismo ispravno postavili slova za svaki proizvod, koristit ćemo formulu IF. Saznajte više o tome. U mojoj tabeli ova funkcija će izgledati ovako:
IF(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))
Shodno tome, proširujemo 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 oku. Očigledno, proizvod 1 se ne prodaje konstantno, ali dinamika pokazuje rast prodaje. Ostavljamo ovaj proizvod na miru...
Ali evo proizvoda 2, tu je već očigledna destabilizacija. A naši proračuni pokazuju Z, što nam govori da prodaja nije stabilna. Proizvod 3 i Proizvod 5 pokazuju stabilne performanse, imajte na umu da je varijacija unutar 10%.
One. Proizvod 5 sa ocjenama 45, 46 i 45 pokazuje varijaciju od 1%, što je stabilan broj brojeva.
Ali proizvod 2 sa indikatorima 10, 50 i 5 pokazuje varijaciju od 93%, što NIJE stabilan niz brojeva.
Nakon svih kalkulacija, možete staviti filter i filtrirati stabilnost, tako da ako se vaša tabela sastoji od nekoliko hiljada artikala, lako možete prepoznati koji nisu stabilni u prodaji ili, obrnuto, koji su stabilni.
"Y" nije funkcionirao u mojoj tabeli, mislim da radi jasnoće niza brojeva, treba ga dodati. Nacrtaću proizvod 6...
Vidite, serije brojeva 40, 50 i 30 pokazuju 20% varijacije. Čini se da nema velike greške, ali širenje je i dalje značajno...
I tako da rezimiramo:
10.50.5 - Z nije stabilan. Varijacija veća od 25%
40,50,30 - Y možete obratiti pažnju na ovaj proizvod i poboljšati njegovu prodaju. Varijacija manja od 25%, ali više od 10%
45,46,45 - X je stabilnost, još ne morate ništa 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 je to pohvala ili kritika. Tako ću znati da me čitate i da ste, što je veoma VAŽNO, zainteresovani. I shodno tome će se pojaviti nove lekcije.
