Një nga mjetet kryesore të analizës statistikore është llogaritja e devijimit standard. Ky tregues ju lejon të vlerësoni devijimin standard për një mostër ose për një popullsi. Le të mësojmë se si të përdorim formulën e devijimit standard në Excel.

Le të përcaktojmë menjëherë se çfarë është devijimi standard dhe si duket formula e tij. Kjo sasi është rrënja katrore e mesatares aritmetike të katrorëve të diferencës midis të gjitha sasive në seri dhe mesatares aritmetike të tyre. Ekziston një emër identik për këtë tregues - devijimi standard. Të dy emrat janë plotësisht të barabartë.

Por, natyrisht, në Excel përdoruesi nuk duhet ta llogarisë këtë, pasi programi bën gjithçka për të. Le të mësojmë se si të llogarisim devijimin standard në Excel.

Llogaritja në Excel

Ju mund të llogarisni vlerën e specifikuar në Excel duke përdorur dy funksione speciale STDEV.V(bazuar në popullatën e mostrës) dhe STDEV.G(bazuar në popullatën e përgjithshme). Parimi i funksionimit të tyre është absolutisht i njëjtë, por ato mund të quhen në tre mënyra, të cilat do t'i diskutojmë më poshtë.

Metoda 1: Funksioni Wizard

Metoda 2: Tab

Metoda 3: Futja manuale e formulës

Ekziston gjithashtu një mënyrë në të cilën nuk do të keni nevojë të thërrisni fare dritaren e argumenteve. Për ta bërë këtë, duhet të futni formulën me dorë.

Siç mund ta shihni, mekanizmi për llogaritjen e devijimit standard në Excel është shumë i thjeshtë. Përdoruesi duhet vetëm të fusë numra nga popullata ose referenca për qelizat që i përmbajnë ato. Të gjitha llogaritjet kryhen nga vetë programi. Është shumë më e vështirë të kuptohet se cili është treguesi i llogaritur dhe si mund të zbatohen rezultatet e llogaritjes në praktikë. Por të kuptuarit e kësaj tashmë lidhet më shumë me fushën e statistikave sesa me të mësuarit për të punuar me softuer.

Koeficienti i variacionit është një krahasim i shpërndarjes së dy vlerave të rastit. Sasitë kanë njësi matëse, gjë që çon në një rezultat të krahasueshëm. Ky koeficient është i nevojshëm për përgatitjen e analizave statistikore.

Me të, investitorët munden llogarit treguesit e rrezikut përpara se të bëni investime në asete të zgjedhura. Është e dobishme kur aktivet e përzgjedhura kanë kthime dhe shkallë të ndryshme rreziku. Për shembull, një aktiv mund të ketë të ardhura të larta dhe shkallë të lartë rreziku, ndërsa një tjetër, përkundrazi, mund të ketë të ardhura të ulëta dhe një shkallë rreziku përkatësisht më të ulët.

Llogaritja e devijimit standard

Devijimi standard është një vlerë statistikore. Duke llogaritur këtë vlerë, përdoruesi do të marrë informacion se sa të dhënat devijojnë në një drejtim ose në një tjetër në lidhje me vlerën mesatare. Devijimi standard në Excel llogaritet në disa hapa.

Përgatitni të dhënat: hapni faqen ku do të bëhen llogaritjet. Në rastin tonë, kjo është një fotografi, por mund të jetë çdo skedar tjetër. Gjëja kryesore është të mbledhni informacionin që do të përdorni në tabelë për llogaritjen.

Futni të dhënat në çdo redaktues të fletëllogaritjes (në rastin tonë Excel), duke plotësuar qelizat nga e majta në të djathtë. Duhet të fillojë nga kolona "A". Futni titujt në rreshtin në krye dhe emrat në të njëjtat kolona që lidhen me titujt, pak më poshtë. Më pas data dhe të dhënat që do të llogariten në të djathtë të datës.

Ruaje këtë dokument.

Tani le të kalojmë në vetë llogaritjen. Zgjidhni një qelizë me kursorin pas vlerës së fundit të futur më poshtë.

Futni shenjën "=" dhe shkruani formulën më poshtë. Kërkohet shenja e barazimit. Përndryshe, programi nuk do të llogarisë të dhënat e propozuara. Formula futet pa hapësira.

Programi do të shfaqë emrat e disa formulave. zgjidhni " DEVIJA STANDARD" Kjo është formula për llogaritjen e devijimit standard. Ekzistojnë dy lloje të llogaritjes:

• me llogaritjen e mostrës;
• me llogaritje në bazë të popullsisë së përgjithshme.

Duke zgjedhur njërën prej tyre, specifikoni gamën e të dhënave. E gjithë formula e futur do të duket si kjo: "=STDEV (B2: B5)".

Më pas klikoni në butonin “ Hyni" Të dhënat e marra do të shfaqen në artikullin e shënuar.

Llogaritja e mesatares aritmetike

Llogaritur kur përdoruesi duhet të krijojë një raport, për shembull, mbi pagat në kompaninë e tij. Kjo bëhet si më poshtë:

• do të ketë vetëm zgjidhni gamën dhe klikoni në butonin "Enter". Dhe qeliza tani do të shfaqë rezultatin nga të dhënat e marra më sipër.

Llogaritja e koeficientit të variacionit

Formula për llogaritjen e koeficientit të variacionit:

V= S/X, ku S është devijimi standard dhe X është mesatarja.

Për të llogaritur koeficientin e variacionit në Excel, duhet të gjeni devijimin standard dhe mesataren aritmetike. Kjo do të thotë, pasi të keni përfunduar dy llogaritjet e para, të cilat u treguan më lart, mund të vazhdoni të punoni në koeficientin e ndryshimit.

Për ta bërë këtë, hapni Excel, plotësoni dy fusha ku duhet të futni numrat rezultues të devijimit standard dhe vlerës mesatare.

Tani zgjidhni qelizën që është caktuar për numrin për të llogaritur variacionin. Hapni skedën " në shtëpi“nëse nuk është e hapur. Klikoni në mjet " Numri" Zgjidhni formatin e përqindjes.

Shkoni te qeliza e shënuar dhe klikoni dy herë mbi të. Pastaj vendosni shenjën e barazimit dhe nënvizoni artikullin ku është futur devijimi total standard. Pastaj klikoni në butonin "slash" ose "split" në tastierën tuaj (duket kështu: "/"). Zgjidhni artikullin, ku futet mesatarja aritmetike dhe klikoni në butonin “Enter”. Duhet të duket kështu:

Dhe këtu është rezultati pasi shtypni "Enter":

Ju gjithashtu mund të përdorni kalkulatorë në internet për të llogaritur koeficientin e variacionit, për shembull planetcalc.ru dhe allcalc.ru. Mjafton të futni numrat e nevojshëm dhe të filloni llogaritjen, pas së cilës do të merrni informacionin e nevojshëm.

Devijimi standard

Devijimi standard në Excel zgjidhet duke përdorur dy formula:

Me fjalë të thjeshta, nxirret rrënja e variancës. Mënyra e llogaritjes së variancës diskutohet më poshtë.

Devijimi standard është sinonim i devijimit standard dhe llogaritet saktësisht gjithashtu. Theksohet qeliza për rezultatin nën numrat që duhet të llogariten. Një nga funksionet e paraqitura në figurën e mësipërme është futur. Klikohet butoni “ Hyni" Rezultati është marrë.

Koeficienti i lëkundjes

Raporti i diapazonit të variacionit ndaj mesatares quhet koeficienti i lëkundjes. Nuk ka formula të gatshme në Excel, kështu që duhet të montohet disa funksione në një.

Funksionet që duhen bashkuar janë formulat mesatare, maksimale dhe minimale. Ky koeficient përdoret për të krahasuar një grup të dhënash.

Dispersion

Varianca është një funksion me të cilin karakterizojnë përhapjen e të dhënave rreth pritshmërisë matematikore. Llogaritur duke përdorur ekuacionin e mëposhtëm:

Variablat marrin vlerat e mëposhtme:

Excel ka dy funksione që përcaktojnë variancën:

Për të bërë një llogaritje, një qelizë theksohet nën numrat që duhet të llogariten. Shkoni te skeda e funksionit të futjes. Zgjidhni kategorinë " Statistikore" Zgjidhni një nga funksionet nga lista rënëse dhe klikoni në butonin "Enter".

Maksimumi dhe minimumi

Maksimumi dhe minimumi nevojiten në mënyrë që të mos kërkohet manualisht midis një numri të madh numrash për numrin minimal ose maksimal.

Për të llogaritur maksimumin, zgjidhni të gjithë gamën numrat e kërkuar në tabelë dhe një qelizë të veçantë, pastaj klikoni në "Σ" ose " Autosum" Në dritaren që shfaqet, zgjidhni "Maximum" dhe duke shtypur butonin "Enter" merrni vlerën e dëshiruar.

Ju bëni të njëjtën gjë për të marrë minimumin. Thjesht zgjidhni funksionin "Minimum".

Devijimi standard është një nga ato terma statistikorë në botën e korporatave që u jep besueshmëri njerëzve që arrijnë ta nxjerrin atë mirë në një bisedë ose prezantim, ndërsa lë një ndjenjë të paqartë konfuzioni midis atyre që nuk e dinë se çfarë është, por që janë shumë. me siklet të pyes. Në fakt, shumica e menaxherëve nuk e kuptojnë konceptin e devijimit standard dhe nëse jeni një prej tyre, është koha që ju të ndaloni së jetuari një gënjeshtër. Në artikullin e sotëm, do t'ju tregoj se si kjo masë statistikore e nënvlerësuar mund t'ju ndihmojë të kuptoni më mirë të dhënat me të cilat po punoni.

Çfarë mat devijimi standard?

Imagjinoni që jeni pronar i dy dyqaneve. Dhe për të shmangur humbjet, është e rëndësishme të keni kontroll të qartë të bilanceve të stoqeve. Në përpjekje për të gjetur se cili menaxher e menaxhon më mirë inventarin, ju vendosni të analizoni gjashtë javët e fundit të inventarit. Kostoja mesatare javore e stokut për të dy dyqanet është afërsisht e njëjtë dhe arrin në rreth 32 njësi konvencionale. Në pamje të parë, balotazhi mesatar tregon se të dy menaxherët performojnë njësoj.

Por nëse i shikoni më nga afër aktivitetet e dyqanit të dytë, do të bindeni se megjithëse vlera mesatare është e saktë, ndryshueshmëria e stokut është shumë e lartë (nga 10 në 58 USD). Kështu, mund të konkludojmë se mesatarja jo gjithmonë i vlerëson saktë të dhënat. Këtu hyn devijimi standard.

Devijimi standard tregon se si shpërndahen vlerat në raport me mesataren në tonë. Me fjalë të tjera, ju mund të kuptoni se sa e madhe është përhapja në balotazh nga java në javë.

Në shembullin tonë, kemi përdorur funksionin STDEV të Excel për të llogaritur devijimin standard së bashku me mesataren.

Në rastin e menaxherit të parë, devijimi standard ishte 2. Kjo na tregon se çdo vlerë në kampion, mesatarisht, devijon me 2 nga mesatarja. A është mirë? Le ta shohim pyetjen nga një kënd tjetër - një devijim standard prej 0 na tregon se çdo vlerë në mostër është e barabartë me mesataren e saj (në rastin tonë, 32.2). Kështu, një devijim standard prej 2 nuk është shumë i ndryshëm nga 0, duke treguar se shumica e vlerave janë afër mesatares. Sa më afër të jetë devijimi standard me 0, aq më i besueshëm është mesatarja. Për më tepër, një devijim standard afër 0 tregon pak ndryshueshmëri në të dhëna. Kjo do të thotë, një vlerë e balotazhit me një devijim standard prej 2 tregon një qëndrueshmëri të jashtëzakonshme të menaxherit të parë.

Në rastin e dyqanit të dytë, devijimi standard ishte 18.9. Kjo do të thotë, kostoja e balotazhit mesatarisht devijon me 18.9 nga vlera mesatare nga java në javë. Përhapje e çmendur! Sa më shumë të jetë devijimi standard nga 0, aq më pak i saktë është mesatarja. Në rastin tonë, shifra 18.9 tregon se vlera mesatare (32.8 USD në javë) thjesht nuk mund t'i besohet. Gjithashtu na tregon se balotazhi javor është shumë i ndryshueshëm.

Ky është koncepti i devijimit standard me pak fjalë. Edhe pse nuk ofron njohuri për matje të tjera të rëndësishme statistikore (Mode, Median...), në fakt, devijimi standard luan një rol vendimtar në shumicën e llogaritjeve statistikore. Kuptimi i parimeve të devijimit standard do të hedhë dritë mbi shumë nga proceset e biznesit tuaj.

Si të llogarisni devijimin standard?

Pra, tani ne e dimë se çfarë thotë numri i devijimit standard. Le të kuptojmë se si llogaritet.

Le të shohim grupin e të dhënave nga 10 në 70 në rritje prej 10. Siç mund ta shihni, unë kam llogaritur tashmë vlerën e devijimit standard për to duke përdorur funksionin STANDARDEV në qelizën H2 (në portokalli).

Më poshtë janë hapat që ndërmerr Excel për të arritur në 21.6.

Ju lutemi vini re se të gjitha llogaritjet janë vizualizuar për një kuptim më të mirë. Në fakt, në Excel, llogaritja ndodh në çast, duke lënë të gjitha hapat prapa skenave.

Së pari, Excel gjen mesataren e mostrës. Në rastin tonë, mesatarja doli të jetë 40, e cila në hapin tjetër zbritet nga çdo vlerë mostre. Çdo ndryshim i përftuar është në katror dhe përmbledhur. Ne morëm një shumë të barabartë me 2800, e cila duhet të pjesëtohet me numrin e elementeve të mostrës minus 1. Meqenëse kemi 7 elementë, rezulton se duhet të pjesëtojmë 2800 me 6. Nga rezultati i marrë gjejmë rrënjën katrore, kjo figura do të jetë devijimi standard.

Për ata që nuk janë plotësisht të qartë për parimin e llogaritjes së devijimit standard duke përdorur vizualizimin, unë jap një interpretim matematikor të gjetjes së kësaj vlere.

Funksionet për llogaritjen e devijimit standard në Excel

Excel ka disa lloje formulash të devijimit standard. Gjithçka që duhet të bëni është të shkruani =STDEV dhe do ta shihni vetë.

Vlen të përmendet se funksionet STDEV.V dhe STDEV.G (funksioni i parë dhe i dytë në listë) kopjojnë funksionet STDEV dhe STDEV (funksionet e pesta dhe të gjashta në listë), respektivisht, të cilat u ruajtën për pajtueshmëri me ato të mëparshme. versionet e Excel.

Në përgjithësi, ndryshimi në mbaresat e funksioneve .B dhe .G tregon parimin e llogaritjes së devijimit standard të një kampioni ose popullate. Unë tashmë shpjegova ndryshimin midis këtyre dy grupeve në atë të mëparshme.

Një veçori e veçantë e funksioneve STANDARDEV dhe STANDDREV (funksioni i tretë dhe i katërt në listë) është se kur llogaritet devijimi standard i një grupi, merren parasysh vlerat logjike dhe tekstuale. Teksti dhe vlerat e vërteta boolean janë 1, dhe vlerat false boolean janë 0. Nuk mund ta imagjinoj një situatë ku do të më duheshin këto dy funksione, kështu që mendoj se ato mund të shpërfillen.

Statistikat përdorin një numër të madh treguesish, dhe një prej tyre është llogaritja e variancës në Excel. Nëse e bëni këtë vetë me dorë, do t'ju duhet shumë kohë dhe mund të bëni shumë gabime. Sot do të shohim se si të zbërthejmë formulat matematikore në funksione të thjeshta. Le të shohim disa nga metodat më të thjeshta, më të shpejta dhe më të përshtatshme të llogaritjes që do t'ju lejojnë të bëni gjithçka në pak minuta.

Llogaritni variancën

Varianca e një ndryshoreje të rastësishme është pritshmëria matematikore e devijimit në katror të një ndryshoreje të rastësishme nga pritshmëria e saj matematikore.

Ne llogarisim në bazë të popullsisë së përgjithshme

Për të llogaritur mat. Duke pritur që programi të përdorë funksionin DISP.G dhe sintaksa e tij duket kështu: “=DISP.G(Number1;Number2;…)”.

Mund të përdoren maksimumi 255 argumente, jo më shumë. Argumentet mund të jenë numra të thjeshtë ose referenca për qelizat në të cilat ato janë specifikuar. Le të shohim se si të llogarisim variancën në Microsoft Excel:

1. Hapi i parë është të zgjidhni qelizën ku do të shfaqet rezultati i llogaritjes dhe më pas klikoni në butonin "Fut Funksionin".

2. Do të hapet guaska e menaxhimit të funksionit. Aty duhet të kërkoni funksionin "DISP.G", i cili mund të jetë në kategorinë "Statistikore" ose "Lista e plotë alfabetike". Kur të gjendet, duhet ta zgjidhni dhe të klikoni "OK".

3. Do të hapet një dritare me argumentet e funksionit. Në të duhet të zgjidhni rreshtin "Numri 1" dhe në fletë zgjidhni gamën e qelizave me seritë e numrave.

4. Pas kësaj, rezultatet e llogaritjes do të shfaqen në qelizën ku është futur funksioni.

Kështu mund ta gjeni lehtësisht variancën në Excel.

Ne bëjmë llogaritjet në bazë të mostrës

Në këtë rast, varianca e mostrës në Excel llogaritet me emëruesin që tregon jo numrin total të numrave, por një më pak. Kjo bëhet për një gabim më të vogël duke përdorur funksionin special DISP.V, sintaksa e të cilit është =DISP.V(Number1;Number2;...). Algoritmi i veprimeve:

• Ashtu si në metodën e mëparshme, duhet të zgjidhni qelizën për rezultatin.
• Në magjistarin e funksionit, duhet të gjeni "DISP.B" nën kategorinë "Lista e plotë alfabetike" ose "statistikore".

• Më pas, do të shfaqet një dritare dhe duhet të vazhdoni në të njëjtën mënyrë si në metodën e mëparshme.

Video: Llogaritja e variancës në Excel

konkluzioni

Varianca në Excel llogaritet shumë thjesht, shumë më shpejt dhe më i përshtatshëm sesa ta bësh atë me dorë, sepse funksioni i pritjes matematikore është mjaft kompleks dhe llogaritja e tij mund të marrë shumë kohë dhe përpjekje.

Varianca është një masë e shpërndarjes që përshkruan devijimin krahasues midis vlerave të të dhënave dhe mesatares. Është matja më e përdorur e shpërndarjes në statistika, e llogaritur duke mbledhur dhe katrorë devijimin e secilës vlerë të të dhënave nga mesatarja. Formula për llogaritjen e variancës është dhënë më poshtë:

s 2 – varianca e mostrës;

x av-mesatarja e mostrës;

n — madhësia e mostrës (numri i vlerave të të dhënave),

(x i – x mesatar) është devijimi nga vlera mesatare për secilën vlerë të grupit të të dhënave.

Për të kuptuar më mirë formulën, le të shohim një shembull. Nuk më pëlqen shumë të gatuaj, kështu që e bëj rrallë. Megjithatë, për të mos ngordhur nga uria, herë pas here më duhet të shkoj në sobë për të zbatuar planin e ngopjes së trupit me proteina, yndyrna dhe karbohidrate. Seti i të dhënave më poshtë tregon se sa herë gatuan Renat në muaj:

Hapi i parë në llogaritjen e variancës është përcaktimi i mesatares së mostrës, e cila në shembullin tonë është 7.8 herë në muaj. Pjesa tjetër e llogaritjeve mund të bëhet më e lehtë duke përdorur tabelën e mëposhtme.

Faza përfundimtare e llogaritjes së variancës duket si kjo:

Për ata që duan të bëjnë të gjitha llogaritjet me një lëvizje, ekuacioni do të duket si ky:

Përdorimi i metodës së numërimit të papërpunuar (shembull gatimi)

Ekziston një mënyrë më efikase për të llogaritur variancën, e njohur si metoda e numërimit të papërpunuar. Megjithëse ekuacioni mund të duket mjaft i rëndë në shikim të parë, në fakt nuk është aq i frikshëm. Ju mund të siguroheni për këtë dhe më pas të vendosni se cila metodë ju pëlqen më shumë.

është shuma e secilës vlerë të të dhënave pas katrorit,

është katrori i shumës së të gjitha vlerave të të dhënave.

Mos e humb mendjen tani. Le t'i vendosim të gjitha këto në një tabelë dhe do të shihni se ka më pak llogaritje të përfshira sesa në shembullin e mëparshëm.

Siç mund ta shihni, rezultati ishte i njëjtë si kur përdorni metodën e mëparshme. Përparësitë e kësaj metode bëhen të dukshme me rritjen e madhësisë së kampionit (n).

Llogaritja e variancës në Excel

Siç e keni menduar tashmë, Excel ka një formulë që ju lejon të llogaritni variancën. Për më tepër, duke filluar me Excel 2010, mund të gjeni 4 lloje të formulës së variancës:

1) VARIANCE.V – Rikthen variancën e kampionit. Vlerat Boolean dhe teksti nuk merren parasysh.

2) DISP.G - Rikthen variancën e popullatës. Vlerat Boolean dhe teksti nuk merren parasysh.

3) VARIANCE - Rikthen variancën e mostrës, duke marrë parasysh vlerat Boolean dhe tekstin.

4) VARIANCE - Rikthen variancën e popullatës, duke marrë parasysh vlerat logjike dhe të tekstit.

Së pari, le të kuptojmë ndryshimin midis një kampioni dhe një popullate. Qëllimi i statistikave përshkruese është të përmbledhë ose shfaqë të dhënat në mënyrë që të merrni shpejt pamjen e madhe, si të thuash një përmbledhje. Konkluzioni statistikor ju lejon të bëni konkluzione rreth një popullate bazuar në një mostër të të dhënave nga ajo popullatë. Popullsia përfaqëson të gjitha rezultatet ose matjet e mundshme që janë me interes për ne. Një mostër është një nëngrup i një popullate.

Për shembull, ne jemi të interesuar për një grup studentësh nga një prej universiteteve ruse dhe duhet të përcaktojmë rezultatin mesatar të grupit. Ne mund të llogarisim performancën mesatare të studentëve, dhe më pas shifra që rezulton do të jetë një parametër, pasi e gjithë popullata do të përfshihet në llogaritjet tona. Megjithatë, nëse duam të llogarisim notën mesatare të të gjithë studentëve në vendin tonë, atëherë ky grup do të jetë kampioni ynë.

Dallimi në formulën për llogaritjen e variancës midis një kampioni dhe një popullate është emëruesi. Ku për mostrën do të jetë e barabartë me (n-1), dhe për popullatën e përgjithshme vetëm n.

Tani le të shohim funksionet për llogaritjen e variancës me mbaresat A, përshkrimi i të cilit thotë se teksti dhe vlerat logjike janë marrë parasysh në llogaritje. Në këtë rast, kur llogaritet varianca e një grupi të caktuar të dhënash ku ndodhin vlera jonumerike, Excel do të interpretojë tekstin dhe vlerat e rreme Boolean si të barabarta me 0 dhe vlerat e vërteta Boolean si të barabarta me 1.

Pra, nëse keni një grup të dhënash, llogaritja e variancës së tij nuk do të jetë e vështirë duke përdorur një nga funksionet e Excel të listuara më sipër.