Kuinka laskea keskihajonta Excelissä. Mikä on keskihajonta - Keskihajontafunktion käyttäminen keskihajonnan laskemiseen Excelissä

Hyvää iltapäivää

Tässä artikkelissa päätin tarkastella, miten keskihajonta toimii Excelissä STANDARDEVAL-funktion avulla. En vain ole kuvaillut tai kommentoinut sitä pitkään aikaan, ja myös yksinkertaisesti siksi, että tämä on erittäin hyödyllinen toiminto niille, jotka opiskelevat korkeampaa matematiikkaa. Ja opiskelijoiden auttaminen on pyhää, tiedän kokemuksesta, kuinka vaikeaa se on hallita. Todellisuudessa keskihajonnan funktioita voidaan käyttää myytyjen tuotteiden vakauden määrittämiseen, hintojen luomiseen, säätöön tai valikoiman muodostamiseen ja muihin yhtä hyödyllisiin myyntianalyyseihin.

Excel käyttää useita tämän varianssifunktion muunnelmia:

Matemaattinen teoria

Ensin vähän teoriasta, miten keskihajontafunktiota voidaan kuvata matemaattisella kielellä käytettäväksi Excelissä, esimerkiksi myyntitilastotietojen analysointiin, mutta siitä lisää myöhemmin. Varoitan heti, kirjoitan paljon käsittämättömiä sanoja...)))), jos jotain alla tekstissä, etsi heti käytännön sovellusta ohjelmaan.

Mitä keskihajonta tarkalleen ottaen tekee? Se arvioi satunnaismuuttujan X keskihajonnan suhteessa sen matemaattiseen odotukseen perustuen sen varianssin puolueettomaan arvioon. Olen samaa mieltä, se kuulostaa hämmentävältä, mutta uskon, että opiskelijat ymmärtävät, mistä oikeastaan ​​puhumme!

Ensinnäkin meidän on määritettävä "keskihajonta", jotta voimme myöhemmin laskea "keskihajonnan", kaava auttaa meitä tässä: Kaava voidaan kuvata seuraavasti: se mitataan samoissa yksiköissä kuin satunnaismuuttujan mittaukset ja sitä käytetään laskettaessa aritmeettista keskivirhettä, muodostettaessa luottamusväliä, testattaessa hypoteeseja tilastoja varten tai analysoitaessa lineaarista riippumattomien muuttujien välinen suhde. Funktio määritellään riippumattomien muuttujien varianssin neliöjuureksi.

Nyt voimme määritellä ja keskihajonta on analyysi satunnaismuuttujan X keskihajonnasta suhteessa sen matemaattiseen perspektiiviin, joka perustuu sen varianssin puolueettomaan arvioon. Kaava on kirjoitettu näin:
Huomaan, että kaikki kaksi arviota ovat puolueellisia. Yleisissä tapauksissa ei ole mahdollista muodostaa puolueetonta arviota. Mutta puolueettoman varianssin arvioon perustuva arvio on johdonmukainen.

Käytännön toteutus Excelissä

No, siirrytään nyt pois tylsästä teoriasta ja katsotaan käytännössä, kuinka STANDARDEVAL-funktio toimii. En käsittele kaikkia standardipoikkeamafunktion muunnelmia Excelissä, vaan yksi riittää. Katsotaanpa esimerkkinä, kuinka myynnin vakaustilastot määritetään.

Katso ensin funktion oikeinkirjoitus, ja kuten näet, se on hyvin yksinkertainen:

STANDARDIPOIKKAAMINEN.G(_numero1_;_numero2_; ....), jossa:

Luodaan nyt esimerkkitiedosto ja pohditaan sen perusteella, miten tämä funktio toimii. Koska analyyttisten laskelmien suorittamiseen on käytettävä vähintään kolmea arvoa, kuten periaatteessa missä tahansa tilastoanalyysissä, otin ehdollisesti 3 jaksoa, tämä voi olla vuosi, neljännes, kuukausi tai viikko. Minun tapauksessani - kuukausi. Parhaan luotettavuuden vuoksi suosittelen ottamaan mahdollisimman monta jaksoa, mutta vähintään kolme. Kaikki taulukon tiedot ovat hyvin yksinkertaisia ​​toiminnan ja kaavan toimivuuden selkeyden vuoksi.

Ensin meidän on laskettava keskimääräinen arvo kuukausittain. Käytämme tähän funktiota AVERAGE ja saamme kaavan: = KESKIARVO(C4:E4).
Nyt itse asiassa voimme löytää keskihajonnan käyttämällä STANDARDEVAL.G-funktiota, jonka arvoon tulee syöttää tuotteen myynti kullekin ajanjaksolle. Tuloksena on seuraavan muotoinen kaava: =STANDARDIPOIKKAAMINEN.Г(C4;D4;E4).
No, puolet työstä on tehty. Seuraava vaihe on "Variation" muodostaminen, joka saadaan jakamalla keskiarvolla, keskihajonnalla ja muuttamalla tulos prosenteiksi. Saamme seuraavan taulukon:
No, peruslaskelmat on tehty, jäljellä on vain selvittää, onko myynti vakaata vai ei. Otetaan ehtona, että 10 %:n poikkeamia pidetään stabiileina, 10:stä 25 %:iin nämä ovat pieniä poikkeamia, mutta kaikki yli 25 % ei ole enää stabiilia. Ehtojen mukaisen tuloksen saamiseksi käytämme loogista ja tuloksen saamiseksi kirjoitamme kaavan:

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

Kaikki alueet on otettu selvyyden vuoksi.
Tietojen visualisoinnin parantamiseksi, kun taulukossasi on tuhansia paikkoja, kannattaa hyödyntää mahdollisuus soveltaa tiettyjä ehtoja, joita tarvitset, tai käyttää tiettyjen vaihtoehtojen korostamiseen värimaailmalla, tämä on erittäin selkeä.

Valitse ensin ne, joissa käytät ehdollista muotoilua. Valitse "Koti"-ohjauspaneelista "Ehdollinen muotoilu" ja valitse avattavasta valikosta "Säännöt solujen korostamiseksi" ja napsauta sitten valikkokohtaa "Teksti sisältää...". Näyttöön tulee valintaikkuna, johon kirjoitat ehdot.

Kun olet kirjoittanut ehdot, esimerkiksi "vakaa" - vihreä, "normaali" - keltainen ja "epävakaa" - punainen, saamme kauniin ja ymmärrettävän taulukon, josta näet, mihin kiinnittää ensin huomiota.

VBA:n käyttö STDEV.Y-funktiolle

Kaikki kiinnostuneet voivat automatisoida laskutoimituksensa makrojen avulla ja käyttää seuraavaa toimintoa:

Funktio MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Jokaiselle x In Arr aSum = aSum + x "laske taulukon elementtien summa aCnt = aCnt + 1 "laske elementtien lukumäärä Seuraava x aAver = aSum / aCnt "keskiarvo jokaiselle x In Arr tmp = tmp + (x - aAver) ^ 2 "laske taulukon elementtien ja keskiarvon välisen eron neliöiden summa Next x MyStDevP = Sqr(tmp / aCnt ) "laske STANDARDEV.G() loppufunktio

Funktio MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# Jokaiselle x In Arr aSum = aSumma + x "laske taulukon elementtien summa

Tilastot käyttävät 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;...). Toimintojen algoritmi:

• Kuten edellisessä menetelmässä, sinun on valittava tulokselle 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ää otoksen keskiarvo, 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, niin näet, että siinä 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 niin, että 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.

Keskimääräisen arvon löytämiseksi Excelistä (riippumatta siitä, onko se numeerinen, teksti, prosenttiarvo tai muu arvo), on monia toimintoja. Ja jokaisella niistä on omat ominaisuutensa ja etunsa. Tässä tehtävässä voidaan todellakin asettaa tiettyjä ehtoja.

Esimerkiksi Excelin numerosarjan keskiarvot lasketaan tilastofunktioilla. Voit myös syöttää oman kaavan manuaalisesti. Mietitään erilaisia ​​vaihtoehtoja.

Kuinka löytää lukujen aritmeettinen keskiarvo?

Aritmeettisen keskiarvon löytämiseksi sinun on laskettava yhteen kaikki joukon luvut ja jaettava summa määrällä. Esimerkiksi tietojenkäsittelytieteen opiskelijan arvosanat: 3, 4, 3, 5, 5. Mitä vuosineljännekselle sisältyy: 4. Löysimme aritmeettisen keskiarvon kaavalla: =(3+4+3+5+5) /5.

Kuinka nopeasti tämä tehdään Excelin funktioilla? Otetaan esimerkiksi sarja satunnaislukuja merkkijonossa:

Tai: tee aktiivinen solu ja kirjoita kaava manuaalisesti: =KESKIARVO(A1:A8).

Katsotaan nyt, mitä muuta AVERAGE-funktio voi tehdä.

Etsitään kahden ensimmäisen ja kolmen viimeisen luvun aritmeettinen keskiarvo. Kaava: =KESKIARVO(A1:B1,F1:H1). Tulos:

Kunto keskinkertainen

Aritmeettisen keskiarvon löytämisen ehto voi olla numeerinen kriteeri tai teksti. Käytämme funktiota: =AVERAGEIF().

Etsi aritmeettinen keskiarvo numeroille, jotka ovat suurempia tai yhtä suuria kuin 10.

Funktio: =KESKIARVOJOS(A1:A8,">=10")

Kolmas argumentti - "Averaging range" - jätetään pois. Ensinnäkin sitä ei vaadita. Toiseksi ohjelman analysoima alue sisältää VAIN numeerisia arvoja. Ensimmäisessä argumentissa määritetyt solut etsitään toisessa argumentissa määritetyn ehdon mukaan.

Huomio! Hakuehto voidaan määrittää solussa. Ja tee linkki siihen kaavaan.

Etsitään lukujen keskiarvo tekstikriteerin avulla. Esimerkiksi "taulukoiden" tuotteiden keskimääräinen myynti.

Funktio näyttää tältä: =AVERAGEIF($A$2:$A$12,A7,$B$2:$B$12). Alue – sarake, jossa on tuotteiden nimiä. Hakuehto on linkki soluun, jossa on sana "taulukot" (voit lisätä sanan "taulukot" linkin A7 sijaan). Keskiarvoalue – solut, joista tiedot otetaan keskiarvon laskemiseksi.

Toiminnon laskemisen tuloksena saamme seuraavan arvon:

Huomio! Tekstikriteerille (ehdolle) on määritettävä keskiarvoalue.

Kuinka laskea painotettu keskihinta Excelissä?

Miten saimme selville painotetun keskihinnan?

Kaava: =SUMMATUOTE(C2:C12,B2:B12)/SUMMA(C2:C12).

SUMPRODUCT-kaavan avulla saamme selville kokonaistulot, kun olemme myyneet koko tavaramäärän. Ja SUM-funktio summaa tavaroiden määrän. Jakamalla tavaroiden myynnistä saadut kokonaistulot tavarayksiköiden kokonaismäärällä saimme painotetun keskihinnan. Tämä indikaattori ottaa huomioon kunkin hinnan "painon". Sen osuus arvojen kokonaismassasta.

Keskihajonta: kaava Excelissä

Yleispopulaatiolle ja otokselle on standardipoikkeamia. Ensimmäisessä tapauksessa tämä on yleisen varianssin juuri. Toisessa otosvarianssista.

Tämän tilastollisen indikaattorin laskemiseksi laaditaan hajontakaava. Juuri uutetaan siitä. Mutta Excelissä on valmis toiminto keskihajonnan löytämiseksi.

Keskihajonta on sidottu lähdetietojen mittakaavaan. Tämä ei riitä kuvaavaan esitykseen analysoidun alueen vaihtelusta. Suhteellisen tietojen sirontatason saamiseksi lasketaan variaatiokerroin:

keskihajonta / aritmeettinen keskiarvo

Excelin kaava näyttää tältä:

STDEV (arvoalue) / AVERAGE (arvoalue).

Variaatiokerroin lasketaan prosentteina. Siksi asetamme soluun prosenttimuodon.

Excel-ohjelmaa arvostavat niin ammattilaiset kuin harrastajatkin, koska sen kanssa voivat työskennellä minkä tahansa taitotason käyttäjät. Esimerkiksi kuka tahansa, jolla on minimaaliset "kommunikaatiotaidot" Excelissä, voi piirtää yksinkertaisen kaavion, tehdä kunnollisen levyn jne.

Samaan aikaan tämän ohjelman avulla voit jopa suorittaa erilaisia ​​laskelmia, esimerkiksi laskelmia, mutta tämä vaatii hieman erilaista koulutusta. Jos olet kuitenkin vasta alkanut tutustua läheisesti tähän ohjelmaan ja olet kiinnostunut kaikesta, mikä auttaa sinua tulemaan edistyneemmäksi käyttäjäksi, tämä artikkeli on sinua varten. Tänään kerron teille, mikä on Excelin keskihajonnan kaava, miksi sitä ylipäätään tarvitaan ja tarkasti ottaen, milloin sitä käytetään. Mennä!

Mikä se on

Aloitetaan teoriasta. Keskihajontaa kutsutaan yleensä neliöjuureksi, joka saadaan kaikkien käytettävissä olevien arvojen neliöerojen aritmeettisesta keskiarvosta sekä niiden aritmeettisesta keskiarvosta. Muuten, tätä arvoa kutsutaan yleensä kreikkalaiseksi kirjaimeksi "sigma". Keskihajonta lasketaan vastaavasti STANDARDEVAL-kaavaa käyttäen, ohjelma tekee tämän itse.

Tämän käsitteen ydin on tunnistaa instrumentin vaihteluaste, eli se on omalla tavallaan kuvaavista tilastoista johdettu indikaattori. Se tunnistaa muutokset instrumentin volatiliteetissa tietyn ajanjakson aikana. STDEV-kaavoja voidaan käyttää näytteen keskihajonnan arvioimiseen jättäen huomioimatta Boolen ja tekstiarvot.

Kaava

Excelissä automaattisesti toimitettava kaava auttaa laskemaan keskihajonnan Excelissä. Löytääksesi sen, sinun on löydettävä kaavaosa Excelistä ja valittava sitten se nimeltä STANDARDEVAL, joten se on hyvin yksinkertaista.

Tämän jälkeen edessäsi tulee ikkuna, johon sinun on syötettävä tiedot laskentaa varten. Erityisesti kaksi numeroa tulisi syöttää erikoiskenttiin, jonka jälkeen ohjelma itse laskee näytteen keskihajonnan.

Matemaattiset kaavat ja laskelmat ovat epäilemättä melko monimutkainen asia, eivätkä kaikki käyttäjät pysty selviytymään siitä heti. Jos kuitenkin kaivaa hieman syvemmälle ja tarkastelet asiaa hieman yksityiskohtaisemmin, käy ilmi, että kaikki ei ole niin surullista. Toivon, että olet vakuuttunut tästä käyttämällä esimerkkiä keskihajonnan laskemisesta.

Video avuksi