Hvordan beregne standardavvik i excel. Hvordan finne standardavvik

Standardavviksfunksjonen er allerede fra kategorien høyere matematikk knyttet til statistikk. Det er flere alternativer for å bruke standardavviksfunksjonen i Excel:

• STDEV-funksjon.
• STANDARDAVVIK funksjon.
• STDEV-funksjon

Vi vil trenge disse funksjonene i salgsstatistikk for å identifisere stabiliteten i salget (XYZ-analyse). Disse dataene kan brukes både til prissetting og til å lage (justere) sortimentsmatrisen og til andre nyttige salgsanalyser, som jeg definitivt vil snakke om i fremtidige artikler.

Forord

La oss først se på formlene i matematisk språk, og deretter (nedenfor i teksten) vil vi analysere i detalj formelen i Excel og hvordan det resulterende resultatet brukes i analysen av salgsstatistikk.

Så, standardavvik er et estimat av standardavviket til en tilfeldig variabel x angående dens matematiske forventning basert på et objektivt estimat av variansen)))) Ikke vær redd for uforståelige ord, vær tålmodig og du vil forstå alt!

Beskrivelse av formelen: Standardavviket måles i måleenheter for selve stokastisk variabel og brukes ved beregning av standardfeilen til det aritmetiske gjennomsnittet, ved konstruksjon av konfidensintervaller, ved statistisk testing av hypoteser, ved måling av den lineære sammenhengen mellom tilfeldige variabler. . Definert som kvadratroten av variansen til den tilfeldige variabelen

Nå er standardavvik et estimat av standardavviket til en tilfeldig variabel x i forhold til dens matematiske forventning basert på et objektivt estimat av variansen:

Spredning;

- Jeg elementet i utvalget;

Prøvestørrelse;

Aritmetisk gjennomsnitt av prøven:

Det skal bemerkes at begge estimatene er partiske. I det generelle tilfellet er det umulig å konstruere et objektivt estimat. Imidlertid er estimatet basert på det objektive variansestimatet konsistent.

Tre sigma regel() - nesten alle verdier av en normalfordelt tilfeldig variabel ligger i intervallet. Mer strengt, med omtrent 0,9973 sannsynlighet, ligger verdien av en normalfordelt tilfeldig variabel i det angitte intervallet (forutsatt at verdien er sann og ikke oppnådd som et resultat av prøvebehandling). Vi vil bruke et avrundet intervall på 0,1

Hvis den sanne verdien er ukjent, bør du bruke ikke, men s. Dermed blir regelen om tre sigma forvandlet til regelen om tre s. Det er denne regelen som vil hjelpe oss med å bestemme stabiliteten i salget, men mer om det senere...

Nå standardavviksfunksjon i Excel

Jeg håper jeg ikke kjedet deg for mye med matematikk? Kanskje noen vil trenge denne informasjonen til et essay eller et annet formål. La oss nå se på hvordan disse formlene fungerer i Excel...

For å bestemme stabiliteten i salget, trenger vi ikke å fordype oss i alle alternativene for standardavviksfunksjonene. Vi bruker bare én:

STDEV-funksjon

STDEV(nummer1;Nummer 2;... )

Nummer1, nummer2,..- fra 1 til 30 numeriske argumenter som tilsvarer den generelle befolkningen.

La oss nå se på et eksempel:

La oss lage en bok og et provisorisk bord. Du vil laste ned dette eksemplet i Excel på slutten av artikkelen.

Fortsettelse følger!!!

Hallo igjen. Vi vil!? Jeg hadde et friminutt. La oss fortsette?

Og så stabiliteten i salget med hjelp STDEV-funksjoner

For klarhetens skyld, la oss ta noen få improviserte varer:

I analyser, det være seg en prognose, forskning eller annet relatert til statistikk, er det alltid nødvendig å ta tre perioder. Dette kan være en uke, en måned, et kvartal eller et år. Det er mulig og til og med best å ta så mange perioder som mulig, men ikke mindre enn tre.

Jeg viste spesifikt overdrevne salg, der det blotte øye kan se hva som selger konsekvent og hva som ikke selger det. Dette vil gjøre det lettere å forstå hvordan formlene fungerer.

Og så vi har salg, nå må vi beregne de gjennomsnittlige salgsverdiene etter periode.

Formelen for gjennomsnittsverdien er AVERAGE (periodedata), i mitt tilfelle ser formelen slik ut = AVERAGE (C6: E6)

Vi bruker formelen på alle produkter. Dette kan gjøres ved å ta tak i høyre hjørne av den valgte cellen og dra den til slutten av listen. Eller plasser markøren på kolonnen med produktet og trykk på følgende tastekombinasjoner:

Ctrl + Ned flytter markøren til toppen av listen.

Ctrl + Høyre, markøren flyttes til høyre side av tabellen. Nok en gang til høyre og vi kommer til kolonnen med formelen.

Nå klemmer vi

Ctrl + Shift og trykk opp. På denne måten vil vi velge området der formelen skal tegnes.

Og tastekombinasjonen Ctrl + D vil dra funksjonen dit vi trenger den.

Husk disse kombinasjonene, de øker virkelig hastigheten din i Excel, spesielt når du jobber med store matriser.

Det neste trinnet, selve standardavgangsfunksjonen, som jeg allerede har sagt, vil vi bare bruke en STDEV

Vi skriver funksjonen og setter salgsverdiene for hver periode i funksjonsverdiene. Hvis du har salg i tabellen etter hverandre, kan du bruke et område, som i min formel =STDEV(C6:E6) eller liste de nødvendige cellene atskilt med semikolon =STDEV(C6;D6;E6)

Nå er alle beregningene klare. Men hvordan vet du hva som selger konsekvent og hva som ikke gjør det? La oss bare sette konvensjonen XYZ der,

X er stabil

Y - med små avvik

Z - ikke stabil

For å gjøre dette bruker vi feilintervaller. hvis det oppstår svingninger innenfor 10 %, vil vi anta at salget er stabilt.

Hvis mellom 10 og 25 prosent, vil det være Y.

Og hvis variasjonsverdien overstiger 25 %, er ikke dette stabilitet.

For å sette bokstavene riktig for hvert produkt, vil vi bruke IF-formelen Lær mer om. I tabellen min vil denne funksjonen se slik ut:

HVIS(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))

Følgelig utvider vi alle formlene for alle navn.

Jeg vil prøve å umiddelbart svare på spørsmålet, Hvorfor intervallene på 10% og 25%?

Faktisk kan intervallene være forskjellige, alt avhenger av den spesifikke oppgaven. Jeg viste deg spesifikt overdrevne salgsverdier, hvor forskjellen er synlig for øyet. Det er klart at produkt 1 ikke selges konsekvent, men dynamikken viser en økning i salget. Vi lar dette produktet være i fred...

Men her er produkt 2, det er allerede åpenbar destabilisering. Og våre beregninger viser Z, som forteller oss at salget ikke er stabilt. Produkt 3 og produkt 5 viser stabil ytelse, vær oppmerksom på at variasjonen er innenfor 10 %.

De. Produkt 5 med skårene 45, 46 og 45 viser en variasjon på 1 %, som er en stabil tallserie.

Men produkt 2 med indikatorene 10, 50 og 5 viser en variasjon på 93 %, som IKKE er en stabil tallserie.

Etter alle beregningene kan du sette et filter og filtrere ut stabilitet, så hvis tabellen din består av flere tusen varer, kan du enkelt identifisere hvilke som ikke er stabile i salg eller omvendt hvilke som er stabile.

"Y" fungerte ikke i tabellen min, jeg tror at det må legges til for å få oversikt over tallserien. Jeg skal tegne produkt 6...

Du skjønner, tallseriene 40, 50 og 30 viser 20 % variasjon. Det ser ikke ut til å være en stor feil, men spredningen er fortsatt betydelig...

Og så for å oppsummere:

10.50.5 - Z er ikke stabil. Variasjon mer enn 25 %

40,50,30 - Y du kan ta hensyn til dette produktet og forbedre salget. Variasjon mindre enn 25 % men mer enn 10 %

45,46,45 - X er stabilitet, du trenger ikke å gjøre noe med dette produktet ennå. Variasjon mindre enn 10 %

Det er alt! Jeg håper jeg forklarte alt tydelig, hvis ikke, spør hva som ikke er klart. Og jeg vil være deg takknemlig for hver kommentar, det være seg ros eller kritikk. På denne måten vil jeg vite at du leser meg og at du, som er veldig VIKTIG, er interessert. Og følgelig vil nye leksjoner dukke opp.

Statistikk bruker et stort antall indikatorer, og en av dem er å beregne varians i Excel. Gjør du dette selv manuelt vil det ta mye tid og du kan gjøre mange feil. I dag skal vi se på hvordan du bryter ned matematiske formler til enkle funksjoner. La oss se på noen av de enkleste, raskeste og mest praktiske beregningsmetodene som lar deg gjøre alt i løpet av få minutter.

Beregn varians

Variansen til en tilfeldig variabel er den matematiske forventningen til det kvadrerte avviket til en tilfeldig variabel fra dens matematiske forventning.

Vi regner ut fra befolkningen generelt

For å beregne matten. Venter på at programmet skal bruke DISP.G-funksjonen, og syntaksen ser slik ut: "=DISP.G(Number1;Number2;...)".

Maksimalt 255 argumenter kan brukes, ikke flere. Argumenter kan være primtall eller referanser til cellene de er spesifisert i. La oss se på hvordan du beregner varians i Microsoft Excel:

1. Det første trinnet er å velge cellen der beregningsresultatet skal vises, og deretter klikke på "Sett inn funksjon"-knappen.

2. Funksjonsadministrasjonsskallet åpnes. Der må du se etter "DISP.G"-funksjonen, som kan være i kategorien "Statistisk" eller "Full alfabetisk liste". Når den er funnet, velg den og klikk "OK".

3. Et vindu med funksjonsargumentene åpnes. I den må du velge linjen "Nummer 1" og på arket velge celleområdet med nummerserien.

4. Etter dette vil beregningsresultatene vises i cellen der funksjonen ble lagt inn.

Slik kan du enkelt finne varians i Excel.

Vi gjør beregninger basert på utvalget

I dette tilfellet beregnes prøvevariansen i Excel med nevneren som ikke indikerer det totale antallet tall, men ett mindre. Dette gjøres for en mindre feil ved å bruke spesialfunksjonen DISP.V, hvis syntaks er =DISP.V(Tall1;Tall2;...). Algoritme for handlinger:

• Som i forrige metode, må du velge en celle for resultatet.
• I funksjonsveiviseren bør du finne "DISP.B" under kategorien "Full Alphabetical List" eller "Statistical".

• Deretter vises et vindu, og du bør fortsette på samme måte som i forrige metode.

Video: Beregning av varians i Excel

Konklusjon

Varians i Excel beregnes veldig enkelt, mye raskere og mer praktisk enn å gjøre det manuelt, fordi den matematiske forventningsfunksjonen er ganske kompleks og å beregne den kan ta mye tid og krefter.

Varians er et mål på spredning som beskriver det komparative avviket mellom dataverdier og gjennomsnittet. Det er det mest brukte målet for spredning i statistikk, beregnet ved å summere og kvadrere avviket til hver dataverdi fra gjennomsnittet. Formelen for beregning av varians er gitt nedenfor:

s 2 – prøvevarians;

x av—sample mean;

n — prøvestørrelse (antall dataverdier),

(x i – x avg) er avviket fra gjennomsnittsverdien for hver verdi av datasettet.

For bedre å forstå formelen, la oss se på et eksempel. Jeg liker egentlig ikke å lage mat, så jeg gjør det sjelden. Men for ikke å sulte, må jeg fra tid til annen gå til komfyren for å gjennomføre planen om å mette kroppen min med proteiner, fett og karbohydrater. Datasettet nedenfor viser hvor mange ganger Renat lager mat hver måned:

Det første trinnet i å beregne varians er å bestemme prøvegjennomsnittet, som i vårt eksempel er 7,8 ganger per måned. Resten av beregningene kan gjøres enklere ved å bruke følgende tabell.

Den siste fasen av beregning av varians ser slik ut:

For de som liker å gjøre alle beregningene på én gang, vil ligningen se slik ut:

Bruke råtellemetoden (matlagingseksempel)

Det er en mer effektiv måte å beregne varians på, kjent som råtellingsmetoden. Selv om ligningen kan virke ganske tungvint ved første øyekast, er den faktisk ikke så skummel. Du kan forsikre deg om dette, og deretter bestemme hvilken metode du liker best.

er summen av hver dataverdi etter kvadrering,

er kvadratet av summen av alle dataverdier.

Ikke mist forstanden akkurat nå. La oss sette alt dette inn i en tabell, og du vil se at det er færre beregninger involvert enn i forrige eksempel.

Som du kan se, var resultatet det samme som ved bruk av forrige metode. Fordelene med denne metoden blir tydelige ettersom prøvestørrelsen (n) øker.

Avviksberegning i Excel

Som du sikkert allerede har gjettet, har Excel en formel som lar deg beregne varians. Fra og med Excel 2010 kan du dessuten finne 4 typer variansformler:

1) VARIANCE.V – Returnerer variansen til utvalget. Boolske verdier og tekst ignoreres.

2) DISP.G - Returnerer variansen til populasjonen. Boolske verdier og tekst ignoreres.

3) VARIANSE – Returnerer variansen til utvalget, tar hensyn til boolske verdier og tekstverdier.

4) VARIANS - Returnerer variansen til populasjonen, tar hensyn til logiske verdier og tekstverdier.

La oss først forstå forskjellen mellom et utvalg og en populasjon. Hensikten med beskrivende statistikk er å oppsummere eller vise data slik at du raskt får det store bildet, en oversikt for å si det sånn. Statistisk slutning lar deg gjøre slutninger om en populasjon basert på et utvalg data fra den populasjonen. Populasjonen representerer alle mulige utfall eller målinger som er av interesse for oss. Et utvalg er en delmengde av en populasjon.

For eksempel er vi interessert i en gruppe studenter fra et av de russiske universitetene, og vi må bestemme den gjennomsnittlige poengsummen til gruppen. Vi kan beregne den gjennomsnittlige ytelsen til elevene, og da vil det resulterende tallet være en parameter, siden hele befolkningen vil være involvert i våre beregninger. Men hvis vi ønsker å beregne GPA for alle studenter i landet vårt, vil denne gruppen være vårt utvalg.

Forskjellen i formelen for å beregne varians mellom et utvalg og en populasjon er nevneren. Hvor for utvalget vil det være lik (n-1), og for den generelle populasjonen bare n.

La oss nå se på funksjonene for å beregne varians med endinger EN, beskrivelsen av hvilke sier at tekst og logiske verdier er tatt i betraktning i beregningen. I dette tilfellet, når du beregner variansen til et bestemt datasett der ikke-numeriske verdier forekommer, vil Excel tolke tekst og falske boolske verdier som lik 0, og sanne boolske verdier som lik 1.

Så hvis du har en datamatrise, vil det ikke være vanskelig å beregne variansen ved å bruke en av Excel-funksjonene som er oppført ovenfor.

Vi må forholde oss til beregningen av slike verdier som spredning, standardavvik og, selvfølgelig, variasjonskoeffisient. Det er beregningen av sistnevnte som fortjener spesiell oppmerksomhet. Det er veldig viktig at hver nybegynner som akkurat har begynt å jobbe med et regnearkredigeringsprogram raskt kan beregne den relative grensen for spredningen av verdier.

Hva er variasjonskoeffisienten og hvorfor er det nødvendig?

Så for meg ser det ut til at det ville være nyttig å ta en kort teoretisk ekskursjon og forstå arten av variasjonskoeffisienten. Denne indikatoren er nødvendig for å gjenspeile rekkevidden av data i forhold til gjennomsnittsverdien. Med andre ord viser den forholdet mellom standardavviket og gjennomsnittet. Variasjonskoeffisienten måles vanligvis i prosent og brukes til å vise homogeniteten til en tidsserie.

Variasjonskoeffisienten vil bli en uunnværlig assistent når du skal lage en prognose basert på data fra en gitt prøve. Denne indikatoren vil fremheve hovedserien med verdier som vil være mest nyttige for påfølgende prognoser, og vil også fjerne utvalget av uviktige faktorer. Så hvis du ser at koeffisientverdien er 0%, erklærer du trygt at serien er homogen, noe som betyr at alle verdiene i den er like med hverandre. Hvis variasjonskoeffisienten har en verdi som overstiger 33 %, indikerer dette at du har å gjøre med en heterogen serie der individuelle verdier skiller seg betydelig fra prøvegjennomsnittet.

Hvordan finner man standardavviket?

Siden for å beregne variasjonsindeksen i Excel må vi bruke standardavviket, ville det være ganske riktig å finne ut hvordan vi kan beregne denne parameteren.

Fra skolealgebrakurset vet vi at standardavviket er kvadratroten hentet fra variansen, det vil si at denne indikatoren bestemmer graden av avvik for en bestemt indikator for det samlede utvalget fra dens gjennomsnittsverdi. Med dens hjelp kan vi måle det absolutte målet for fluktuasjon av karakteristikken som studeres og tydelig tolke den.

Beregning av koeffisienten i Excel

Dessverre har ikke Excel en standardformel som lar deg beregne variasjonsindeksen automatisk. Men dette betyr ikke at du må gjøre beregningene i hodet. Fraværet av en mal i "Formellinjen" forringer på ingen måte Excels evner, så du kan ganske enkelt tvinge programmet til å utføre beregningen du trenger ved å skrive inn riktig kommando manuelt.

For å beregne variasjonsindeksen i Excel, må du huske matematikkkurset ditt på videregående skole og dele standardavviket på prøvegjennomsnittet. Det vil si at formelen ser slik ut - STANDARDEVAL(angitt dataområde)/GJENNOMSNITT(angitt dataområde). Du må legge inn denne formelen i Excel-cellen der du ønsker å få utregningen du trenger.

Ikke glem at siden koeffisienten er uttrykt i prosent, må cellen med formelen formateres tilsvarende. Du kan gjøre dette på følgende måte:

1. Åpne fanen "Hjem".
2. Finn kategorien "Celleformat" i den og velg ønsket alternativ.

Alternativt kan du angi prosentformatet for cellen ved å høyreklikke på den aktiverte tabellcellen. I kontekstmenyen som vises, i likhet med algoritmen ovenfor, må du velge kategorien "Celleformat" og angi den nødvendige verdien.

Velg Prosent og skriv om nødvendig inn antall desimaler

Kanskje algoritmen ovenfor kan virke komplisert for noen. Faktisk er det like enkelt å beregne koeffisienten som å legge til to naturlige tall. Når du har fullført denne oppgaven i Excel, vil du aldri gå tilbake til kjedelige, komplekse løsninger i en notatbok.

Kan du fortsatt ikke gjøre en kvalitativ sammenligning av graden av dataspredning? Forvirret av størrelsen på utvalget? Sett deg i gang med det samme og behersk i praksis alt det teoretiske materialet som ble presentert ovenfor! La statistisk analyse og prognoseutvikling ikke lenger få deg til å føle deg redd og negativ. Spar energi og tid med

Et av hovedverktøyene for statistisk analyse er beregning av standardavvik. Denne indikatoren lar deg estimere standardavviket for et utvalg eller for en populasjon. La oss lære hvordan du bruker standardavviksformelen i Excel.

La oss umiddelbart finne ut hva standardavviket er og hvordan formelen ser ut. Denne mengden er kvadratroten av det aritmetiske gjennomsnittet av kvadratene av forskjellen mellom alle mengder i serien og deres aritmetiske gjennomsnitt. Det er et identisk navn for denne indikatoren - standardavvik. Begge navnene er helt like.

Men, naturlig nok, i Excel trenger ikke brukeren å beregne dette, siden programmet gjør alt for ham. La oss lære hvordan du beregner standardavvik i Excel.

Beregning i Excel

Du kan beregne den angitte verdien i Excel ved å bruke to spesialfunksjoner STDEV.V(basert på utvalgspopulasjonen) og STDEV.G(basert på den generelle befolkningen). Prinsippet for deres operasjon er helt det samme, men de kan kalles på tre måter, som vi vil diskutere nedenfor.

Metode 1: Funksjonsveiviser

Metode 2: Formler Tab

Metode 3: Angi formelen manuelt

Det er også en måte å unngå å kalle argumentvinduet i det hele tatt. For å gjøre dette må du angi formelen manuelt.

Som du kan se, er mekanismen for å beregne standardavviket i Excel veldig enkel. Brukeren trenger kun å legge inn tall fra populasjonen eller referanser til cellene som inneholder dem. Alle beregninger utføres av programmet selv. Det er mye vanskeligere å forstå hva den beregnede indikatoren er og hvordan beregningsresultatene kan brukes i praksis. Men å forstå dette er allerede mer knyttet til statistikkfeltet enn å lære å jobbe med programvare.