Yksi tilastollisen analyysin tärkeimmistä työkaluista on keskihajonnan laskenta. Tämän indikaattorin avulla voit arvioida otoksen tai populaation keskihajonnan. Opitaan käyttämään keskihajonnan kaavaa Excelissä.

Selvitetään heti, mikä keskihajonna on ja miltä sen kaava näyttää. Tämä suure on sarjan kaikkien suureiden ja niiden aritmeettisen keskiarvon välisen eron neliöiden aritmeettisen keskiarvon neliöjuuri. Tälle indikaattorille on identtinen nimi - keskihajonta. Molemmat nimet ovat täysin samanarvoisia.

Mutta luonnollisesti Excelissä käyttäjän ei tarvitse laskea tätä, koska ohjelma tekee kaiken hänen puolestaan. Opitaan laskemaan keskihajonta Excelissä.

Laskenta Excelissä

Voit laskea määritetyn arvon Excelissä käyttämällä kahta erikoisfunktiota STDEV.V(otosjoukon perusteella) ja STDEV.G(perustuen yleiseen väestöön). Niiden toimintaperiaate on täysin sama, mutta niitä voidaan kutsua kolmella tavalla, joista keskustelemme alla.

Tapa 1: Ohjattu toimintotoiminto

Menetelmä 2: Kaavat-välilehti

Tapa 3: Syötä kaava manuaalisesti

On myös tapa, jolla sinun ei tarvitse kutsua argumenttiikkunaa ollenkaan. Tätä varten sinun on syötettävä kaava manuaalisesti.

Kuten näet, mekanismi keskihajonnan laskemiseksi Excelissä on hyvin yksinkertainen. Käyttäjän tarvitsee vain syöttää numeroita populaatiosta tai viittauksia niitä sisältäviin soluihin. Kaikki laskelmat suorittaa ohjelma itse. On paljon vaikeampaa ymmärtää, mikä on laskettu indikaattori ja miten laskentatuloksia voidaan soveltaa käytännössä. Mutta tämän ymmärtäminen liittyy jo enemmän tilastoalaan kuin ohjelmiston kanssa työskentelyn oppimiseen.

Variaatiokerroin on kahden satunnaisen arvon hajaantumisen vertailu. Summilla on mittayksiköt, mikä johtaa vertailukelpoiseen tulokseen. Tämä kerroin tarvitaan tilastollisen analyysin laatimiseen.

Sen avulla sijoittajat voivat laskea riskiindikaattoreita ennen sijoitusten tekemistä valittuihin omaisuuseriin. Siitä on hyötyä, kun valituilla omaisuuserillä on eri tuotto ja riskiaste. Esimerkiksi yhdellä omaisuuserällä voi olla korkea tuotto ja korkea riskiaste, kun taas toisella voi päinvastoin olla alhaiset tulot ja vastaavasti pienempi riski.

Keskihajonnan laskenta

Keskihajonta on tilastollinen arvo. Laskemalla tämän arvon käyttäjä saa tiedon siitä, kuinka paljon data poikkeaa yhteen tai toiseen suuntaan suhteessa keskiarvoon. Keskihajonta Excelissä lasketaan useassa vaiheessa.

Valmistele tiedot: avaa sivu, jolla laskelmat suoritetaan. Meidän tapauksessamme tämä on kuva, mutta se voi olla mikä tahansa muu tiedosto. Tärkeintä on kerätä tiedot, joita käytät taulukossa laskennassa.

Syötä tiedot mihin tahansa laskentataulukkoeditoriin (tapauksessamme Exceliin) täyttämällä solut vasemmalta oikealle. Pitäisi alkaa sarakkeesta "A". Kirjoita otsikot yläreunassa olevalle riville ja nimet samoihin sarakkeisiin, jotka liittyvät otsikoihin, vain alla. Sitten päivämäärä ja laskettavat tiedot päivämäärän oikealla puolella.

Tallenna tämä asiakirja.

Siirrytään nyt itse laskemiseen. Valitse solu kursorilla viimeisen alle syötetyn arvon jälkeen.

Kirjoita "="-merkki ja kirjoita alla oleva kaava. Tasa-arvomerkki vaaditaan. Muuten ohjelma ei laske ehdotettuja tietoja. Kaava syötetään ilman välilyöntejä.

Apuohjelma näyttää useiden kaavojen nimet. Valitse " STANDARDIPOIKKAAMINEN" Tämä on standardipoikkeaman laskentakaava. Laskentatyyppejä on kaksi:

• näytelaskennan kanssa;
• yleiseen väestöön perustuvalla laskennalla.

Valitse tietoalue valitsemalla yksi niistä. Koko syötetty kaava näyttää tältä: "=STDEV (B2: B5)".

Napsauta sitten painiketta " Tulla sisään" Vastaanotetut tiedot näkyvät merkittyyn kohtaan.

Aritmeettisen keskiarvon laskeminen

Lasketaan, kun käyttäjän on laadittava raportti esimerkiksi yrityksensä palkoista. Tämä tehdään seuraavasti:

• tulee vain olemaan valitse alue ja napsauta "Enter" -painiketta. Ja solu näyttää nyt tuloksen yllä olevista tiedoista.

Variaatiokertoimen laskeminen

Kaava variaatiokertoimen laskemiseksi:

V = S/X, jossa S on keskihajonnan ja X on keskiarvo.

Jotta voit laskea variaatiokertoimen Excelissä, sinun on löydettävä keskihajonta ja aritmeettinen keskiarvo. Toisin sanoen, kun olet suorittanut kaksi ensimmäistä laskelmaa, jotka näytettiin yllä, voit siirtyä variaatiokertoimen käsittelyyn.

Voit tehdä tämän avaamalla Excelin, täyttämällä kaksi kenttää, joihin sinun tulee syöttää tuloksena saadut keskihajonnan ja keskiarvon luvut.

Valitse nyt numerolle varattu solu muunnelman laskemiseksi. Avaa välilehti " Koti"jos se ei ole auki. Napsauta työkalua " Määrä" Valitse prosenttimuoto.

Siirry merkittyyn soluun ja kaksoisnapsauta sitä. Syötä sitten yhtäläisyysmerkki ja korosta kohta, johon kokonaiskeskihajonta syötetään. Napsauta sitten näppäimistön vinoviiva- tai split-painiketta (näyttää tältä: "/"). Valitse kohde, johon syötetään aritmeettinen keskiarvo, ja napsauta Enter-painiketta. Sen pitäisi näyttää tältä:

Ja tässä on tulos "Enter" painamisen jälkeen:

Voit myös käyttää online-laskimia variaatiokertoimen laskemiseen, esimerkiksi planetcalc.ru ja allcalc.ru. Riittää, kun syötät tarvittavat numerot ja aloitat laskennan, jonka jälkeen saat tarvittavat tiedot.

Standardipoikkeama

Keskihajonta Excelissä ratkaistaan ​​kahdella kaavalla:

Yksinkertaisesti sanottuna varianssin juuri erotetaan. Varianssin laskemista käsitellään alla.

Keskihajonta on synonyymi keskihajonnan kanssa, ja se lasketaan myös tarkasti. Laskettavien numeroiden alla oleva tuloksen solu on korostettu. Yksi yllä olevassa kuvassa esitetyistä toiminnoista on lisätty. Painiketta “ napsautetaan Tulla sisään" Tulos on vastaanotettu.

Värähtelykerroin

Vaihtelualueen suhdetta keskiarvoon kutsutaan värähtelykertoimeksi. Excelissä ei siis ole valmiita kaavoja täytyy koota useita toimintoja yhdeksi.

Yhdistettävät funktiot ovat keskiarvo-, maksimi- ja minimikaavat. Tätä kerrointa käytetään tietojoukon vertaamiseen.

Dispersio

Varianssi on funktio, jolla kuvaamaan tiedon leviämistä matemaattisen odotuksen ympärillä. Laskettu seuraavan yhtälön avulla:

Muuttujat saavat seuraavat arvot:

Excelissä on kaksi funktiota, jotka määrittävät varianssin:

Laskemista varten solu on korostettu laskettavien numeroiden alla. Siirry lisäystoimintovälilehdelle. Valitse kategoria " Tilastollinen" Valitse yksi toiminnoista avattavasta luettelosta ja napsauta "Enter"-painiketta.

Maksimi ja minimi

Enimmäis- ja minimiarvot tarvitaan, jotta ei manuaalisesti haettaisi useista numeroista pienintä tai maksimilukua.

Maksimiarvon laskemiseksi, valitse koko valikoima tarvittavat numerot taulukossa ja erillisessä solussa, napsauta sitten "Σ" tai " Autosum" Valitse näkyviin tulevasta ikkunasta "Maksimi" ja paina "Enter" -painiketta saat haluamasi arvon.

Teet saman saadaksesi minimin. Valitse vain "Minimi"-toiminto.

Keskihajonta on yksi niistä yritysmaailman tilastollisista termeistä, jotka antavat uskottavuutta ihmisille, jotka onnistuvat saamaan sen päätökseen hyvin keskustelussa tai esityksessä, samalla kun se jättää epämääräisen hämmennyksen niille, jotka eivät tiedä mitä se on, mutta ovat liian nolostuneita kysyä. Itse asiassa useimmat johtajat eivät ymmärrä keskihajonnan käsitettä, ja jos olet yksi heistä, sinun on aika lopettaa valheen eläminen. Tämän päivän artikkelissa kerron sinulle, kuinka tämä aliarvostettu tilastollinen mitta voi auttaa sinua ymmärtämään paremmin käsittelemääsi dataa.

Mitä keskihajonta mittaa?

Kuvittele, että olet kahden liikkeen omistaja. Ja tappioiden välttämiseksi on tärkeää hallita varastosaldoa selkeästi. Yrittääksesi selvittää, kumpi johtaja hallitsee varastoa paremmin, päätät analysoida varaston viimeiset kuusi viikkoa. Keskimääräinen viikoittainen varastohinta molemmissa myymälöissä on suunnilleen sama ja on noin 32 tavanomaista yksikköä. Ensi silmäyksellä keskimääräinen vuoto osoittaa, että molemmat johtajat toimivat samalla tavalla.

Mutta jos tarkastelet tarkemmin toisen myymälän toimintaa, olet vakuuttunut siitä, että vaikka keskiarvo on oikea, osakkeen vaihtelu on erittäin korkea (10 - 58 USD). Siten voimme päätellä, että keskiarvo ei aina arvioi tietoja oikein. Tässä tulee standardipoikkeama.

Keskihajonta osoittaa, kuinka arvot jakautuvat suhteessa meidän keskiarvoon. Toisin sanoen voit ymmärtää, kuinka suuri valumahajotus on viikoittain.

Esimerkissämme käytimme Excelin STDEV-funktiota keskihajonnan laskemiseen keskiarvon kanssa.

Ensimmäisen esimiehen tapauksessa keskihajonta oli 2. Tämä kertoo meille, että jokainen otoksen arvo poikkeaa keskimäärin 2:lla keskiarvosta. Onko se hyvää? Tarkastellaan kysymystä eri näkökulmasta - keskihajonna 0 kertoo meille, että jokainen näytteen arvo on yhtä suuri kuin sen keskiarvo (tässä tapauksessa 32,2). Siten 2:n standardipoikkeama ei eroa paljon nollasta, mikä osoittaa, että useimmat arvot ovat lähellä keskiarvoa. Mitä lähempänä keskihajonta on nollaa, sitä luotettavampi on keskiarvo. Lisäksi standardipoikkeama lähellä nollaa osoittaa vain vähän vaihtelua tiedoissa. Toisin sanoen valuma-arvo, jonka keskihajonna on 2, osoittaa ensimmäisen johtajan uskomattoman johdonmukaisuuden.

Toisen myymälän tapauksessa keskihajonna oli 18,9. Eli valumakustannukset poikkeavat keskimäärin 18,9 viikoittain keskiarvosta. Hullu leviäminen! Mitä kauempana keskihajonta on 0:sta, sitä epätarkempi keskiarvo on. Meidän tapauksessamme luku 18,9 osoittaa, että keskiarvoon (32,8 USD viikossa) ei yksinkertaisesti voi luottaa. Se kertoo myös, että viikoittainen valuma vaihtelee suuresti.

Tämä on keskihajonnan käsite pähkinänkuoressa. Vaikka se ei anna käsitystä muista tärkeistä tilastollisista mittauksista (moodi, mediaani...), itse asiassa keskihajonnalla on ratkaiseva rooli useimmissa tilastollisissa laskelmissa. Keskihajonnan periaatteiden ymmärtäminen valaisee monia liiketoimintaprosessejasi.

Kuinka laskea keskihajonta?

Joten nyt tiedämme, mitä keskihajonnan luku sanoo. Selvitetään, miten se lasketaan.

Katsotaanpa tietojoukkoa 10:stä 70:een 10:n välein. Kuten näette, olen jo laskenut niiden keskihajonnan arvon käyttämällä STANDARDEV-funktiota solussa H2 (oranssi).

Alla on vaiheet, joilla Excel saapuu 21.6.

Huomaa, että kaikki laskelmat on visualisoitu paremman ymmärtämisen vuoksi. Itse asiassa Excelissä laskenta tapahtuu välittömästi, jolloin kaikki vaiheet jäävät kulissien taakse.

Ensin Excel löytää näytteen keskiarvon. Meidän tapauksessamme keskiarvoksi muodostui 40, joka seuraavassa vaiheessa vähennetään kustakin näytearvosta. Jokainen saatu erotus neliötetään ja lasketaan yhteen. Saimme summan, joka on 2800, joka on jaettava näytealkioiden määrällä miinus 1. Koska meillä on 7 alkiota, niin käy ilmi, että meidän on jaettava 2800 kuudella. Saadusta tuloksesta saadaan neliöjuuri, tämä luku on keskihajonta.

Niille, jotka eivät ole täysin selvillä keskihajonnan laskentaperiaatteesta visualisoinnin avulla, annan matemaattisen tulkinnan tämän arvon löytämisestä.

Toiminnot keskihajonnan laskemiseen Excelissä

Excelissä on useita keskihajontakaavoja. Sinun tarvitsee vain kirjoittaa =STDEV ja näet itse.

On syytä huomata, että STDEV.V- ja STDEV.G-funktiot (luettelon ensimmäinen ja toinen funktio) kopioivat STDEV- ja STDEV-funktiot (luettelon viides ja kuudes funktio), jotka säilytettiin yhteensopivuuden vuoksi aikaisempien kanssa. Excelin versiot.

Yleensä ero .B- ja .G-funktioiden päätteissä ilmaisee otoksen tai perusjoukon keskihajonnan laskentaperiaatteen. Selitin jo näiden kahden taulukon eron edellisessä.

STANDARDEV- ja STANDDREV-toimintojen (luettelon kolmas ja neljäs funktio) erityispiirre on, että taulukon keskihajonnan laskennassa otetaan huomioon loogiset ja tekstiarvot. Teksti ja todelliset loogiset arvot ovat 1 ja väärät loogiset arvot ovat 0. En voi kuvitella tilannetta, jossa tarvitsisin näitä kahta funktiota, joten mielestäni ne voidaan jättää huomiotta.

Tilastot käyttää valtavaa määrää indikaattoreita, ja yksi niistä on varianssin laskeminen Excelissä. Jos teet tämän itse manuaalisesti, se vie paljon aikaa ja voit tehdä paljon virheitä. Tänään tarkastellaan, kuinka matemaattiset kaavat jaetaan yksinkertaisiksi funktioiksi. Katsotaanpa joitain yksinkertaisimmista, nopeimmista ja kätevimmistä laskentamenetelmistä, joiden avulla voit tehdä kaiken muutamassa minuutissa.

Laske varianssi

Satunnaismuuttujan varianssi on matemaattinen odotus satunnaismuuttujan neliöidylle poikkeamalle sen matemaattisesta odotuksesta.

Laskemme yleisen väestön perusteella

Maton laskemiseen. Odotetaan, että ohjelma käyttää DISP.G-funktiota, ja sen syntaksi näyttää tältä: "=DISP.G(Number1;Number2;...)".

Enintään 255 argumenttia voidaan käyttää, ei enempää. Argumentit voivat olla alkulukuja tai viittauksia soluihin, joissa ne on määritetty. Katsotaanpa, kuinka varianssi lasketaan Microsoft Excelissä:

1. Ensimmäinen vaihe on valita solu, jossa laskentatulos näytetään, ja napsauta sitten "Lisää funktio" -painiketta.

2. Toimintojen hallintakuori avautuu. Siellä sinun on etsittävä "DISP.G"-toimintoa, joka voi olla "Tilastollinen" tai "Täysi aakkosellinen luettelo" -luokasta. Kun se löytyy, valitse se ja napsauta "OK".

3. Ikkuna funktion argumenteilla avautuu. Siinä sinun on valittava rivi "Numero 1" ja arkilta valittava solualue numerosarjalla.

4. Tämän jälkeen laskennan tulokset näkyvät solussa, johon funktio syötettiin.

Näin voit helposti löytää varianssin Excelissä.

Teemme laskelmia näytteen perusteella

Tässä tapauksessa Excelin otosvarianssi lasketaan niin, että nimittäjä ei ilmoita lukujen kokonaismäärää, vaan yksi vähemmän. Tämä tehdään pienemmälle virheelle käyttämällä erikoisfunktiota DISP.V, jonka syntaksi on =DISP.V(Number1;Number2;...). Toiminnan algoritmi:

• Kuten edellisessä menetelmässä, sinun on valittava tuloksen solu.
• Toimintovelhossa sinun pitäisi löytää "DISP.B" "Full Alphabetical List"- tai "Statistical"-luokasta.

• Seuraavaksi ikkuna tulee näkyviin, ja sinun tulee jatkaa samalla tavalla kuin edellisessä menetelmässä.

Video: Varianssin laskeminen Excelissä

Johtopäätös

Excelin varianssi lasketaan hyvin yksinkertaisesti, paljon nopeammin ja kätevämmin kuin manuaalisesti, koska matemaattinen odotusfunktio on melko monimutkainen ja sen laskeminen voi viedä paljon aikaa ja vaivaa.

Varianssi on dispersion mitta, joka kuvaa data-arvojen ja keskiarvon välistä vertailevaa poikkeamaa. Se on tilastoissa eniten käytetty hajontamitta, joka lasketaan summaamalla ja neliöimällä kunkin data-arvon poikkeama keskiarvosta. Varianssin laskentakaava on annettu alla:

s 2 – otosvarianssi;

x av — otoskeskiarvo;

n — näytekoko (tietoarvojen lukumäärä),

(x i – x avg) on ​​poikkeama tietojoukon kunkin arvon keskiarvosta.

Kaavan ymmärtämiseksi paremmin katsotaanpa esimerkkiä. En pidä ruoanlaitosta, joten teen sitä harvoin. Kuitenkin, jotta en kuolisi nälkään, minun on aika ajoin mennä liesille toteuttamaan suunnitelmaa kyllästää kehoni proteiineilla, rasvoilla ja hiilihydraatteilla. Alla oleva tietojoukko näyttää kuinka monta kertaa Renat valmistaa ruokaa kuukaudessa:

Ensimmäinen vaihe varianssin laskennassa on määrittää otoskeskiarvo, joka esimerkissämme on 7,8 kertaa kuukaudessa. Loput laskelmat voidaan helpottaa seuraavan taulukon avulla.

Varianssin laskennan viimeinen vaihe näyttää tältä:

Niille, jotka haluavat tehdä kaikki laskelmat yhdellä kertaa, yhtälö näyttää tältä:

Raakalaskentamenetelmän käyttäminen (esimerkki ruoanlaitosta)

On olemassa tehokkaampi tapa laskea varianssi, joka tunnetaan nimellä raakalaskentamenetelmä. Vaikka yhtälö saattaa ensi silmäyksellä tuntua melko hankalalta, se ei itse asiassa ole niin pelottava. Voit varmistaa tämän ja päättää sitten, mistä menetelmästä pidät eniten.

on kunkin data-arvon summa neliöinnin jälkeen,

on kaikkien data-arvojen summan neliö.

Älä menetä mieltäsi nyt. Laitetaan tämä kaikki taulukkoon ja näet, että tässä on vähemmän laskelmia kuin edellisessä esimerkissä.

Kuten näet, tulos oli sama kuin edellisellä menetelmällä. Tämän menetelmän edut tulevat ilmeisiksi otoksen koon (n) kasvaessa.

Varianssilaskenta Excelissä

Kuten luultavasti jo arvasit, Excelissä on kaava, jonka avulla voit laskea varianssia. Lisäksi Excel 2010:stä alkaen löydät neljä erilaista varianssikaavaa:

1) VARIANCE.V – Palauttaa otoksen varianssin. Boolen arvot ja teksti ohitetaan.

2) DISP.G - Palauttaa populaation varianssin. Boolen arvot ja teksti ohitetaan.

3) VARIANCE - Palauttaa otoksen varianssin ottaen huomioon Boolen ja tekstin arvot.

4) VARIANCE - Palauttaa perusjoukon varianssin, ottaen huomioon loogiset ja tekstiarvot.

Ymmärretään ensin otoksen ja populaation välinen ero. Kuvaavien tilastojen tarkoitus on tiivistää tai näyttää tietoja, jotta saat nopeasti kokonaiskuvan, niin sanotusti yleiskatsauksen. Tilastollisen päättelyn avulla voit tehdä johtopäätöksiä populaatiosta kyseisen populaation dataotoksen perusteella. Yleisö edustaa kaikkia mahdollisia tuloksia tai mittauksia, jotka kiinnostavat meitä. Otos on populaation osajoukko.

Olemme esimerkiksi kiinnostuneita opiskelijoiden ryhmästä yhdestä Venäjän yliopistosta ja meidän on määritettävä ryhmän keskimääräinen pistemäärä. Voimme laskea opiskelijoiden keskimääräisen suorituksen, ja sitten tuloksena oleva luku on parametri, koska koko väestö on mukana laskelmissamme. Jos kuitenkin haluamme laskea kaikkien maamme opiskelijoiden GPA, tämä ryhmä on otoksemme.

Ero otoksen ja populaation välisen varianssin laskentakaavassa on nimittäjä. Jos otokselle se on yhtä suuri kuin (n-1), ja yleiselle perusjoukolle vain n.

Katsotaan nyt funktioita varianssin laskemiseen päätteillä A, jonka kuvauksessa todetaan, että teksti ja loogiset arvot otetaan huomioon laskennassa. Tässä tapauksessa, kun lasketaan tietyn tietojoukon varianssia, jossa esiintyy ei-numeerisia arvoja, Excel tulkitsee tekstin ja väärät Boolen arvot 0:ksi ja todelliset Boolen arvot 1:ksi.

Joten jos sinulla on tietotaulukko, sen varianssin laskeminen ei ole vaikeaa käyttämällä yhtä yllä luetelluista Excel-funktioista.