Excel standardafvigelse. Hvad er standardafvigelse - brug af standardafvigelsesfunktionen til at beregne standardafvigelse i excel

I denne artikel vil jeg tale om hvordan man finder standardafvigelse. Dette materiale er ekstremt vigtigt for en fuld forståelse af matematik, så en matematikvejleder bør afsætte en separat lektion eller endda flere til at studere det. I denne artikel finder du et link til en detaljeret og forståelig videotutorial, der forklarer, hvad standardafvigelse er, og hvordan du finder den.

Standardafvigelse gør det muligt at evaluere spredningen af ​​værdier opnået som et resultat af måling af en bestemt parameter. Angivet med symbolet (græsk bogstav "sigma").

Formlen til beregning er ret enkel. For at finde standardafvigelsen skal du tage kvadratroden af ​​variansen. Så nu skal du spørge: "Hvad er varians?"

Hvad er varians

Definitionen af ​​varians lyder sådan her. Dispersion er det aritmetiske gennemsnit af de kvadrerede afvigelser af værdier fra middelværdien.

For at finde variansen skal du udføre følgende beregninger sekventielt:

• Bestem gennemsnittet (simpelt aritmetisk gennemsnit af en række værdier).
• Træk derefter gennemsnittet fra hver værdi og kvadreret den resulterende forskel (du får kvadratisk forskel).
• Det næste trin er at beregne det aritmetiske middelværdi af de resulterende kvadratforskelle (Du kan finde ud af hvorfor præcist kvadraterne nedenfor).

Lad os se på et eksempel. Lad os sige, at du og dine venner beslutter at måle højden på dine hunde (i millimeter). Som resultat af målingerne fik du følgende højdemål (på manken): 600 mm, 470 mm, 170 mm, 430 mm og 300 mm.

Lad os beregne middelværdien, variansen og standardafvigelsen.

Lad os først finde gennemsnitsværdien. Som du allerede ved, for at gøre dette skal du tilføje alle de målte værdier og dividere med antallet af målinger. Beregningsforløb:

Gennemsnitlig mm.

Så gennemsnittet (aritmetisk gennemsnit) er 394 mm.

Nu skal vi bestemme afvigelse af hver hunds højde fra gennemsnittet:

Endelig, at beregne varians, kvadrerer vi hver af de resulterende forskelle og finder derefter det aritmetiske middelværdi af de opnåede resultater:

Dispersion mm 2 .

Således er spredningen 21704 mm 2.

Sådan finder du standardafvigelse

Så hvordan kan vi nu beregne standardafvigelsen ved at kende variansen? Som vi husker, tag kvadratroden af ​​det. Det vil sige, at standardafvigelsen er lig med:

Mm (afrundet til nærmeste hele tal i mm).

Ved at bruge denne metode fandt vi ud af, at nogle hunde (for eksempel rottweilere) er meget store hunde. Men der er også meget små hunde (for eksempel gravhunde, men det skal du ikke fortælle dem).

Det mest interessante er, at standardafvigelsen indeholder nyttige oplysninger. Nu kan vi vise, hvilke af de opnåede højdemålingsresultater, der ligger inden for det interval, vi får, hvis vi plotter standardafvigelsen fra gennemsnittet (til begge sider af det).

Det vil sige, at vi ved hjælp af standardafvigelsen får en "standard" metode, der giver os mulighed for at finde ud af, hvilken af ​​værdierne der er normal (statistisk gennemsnit), og hvilken der er ekstraordinært stor eller omvendt lille.

Hvad er standardafvigelse

Men... alt bliver lidt anderledes, hvis vi analyserer prøve data. I vores eksempel overvejede vi almindelig befolkning. Det vil sige, at vores 5 hunde var de eneste hunde i verden, der interesserede os.

Men hvis dataene er en stikprøve (værdier udvalgt fra en stor population), så skal beregningerne gøres anderledes.

Hvis der er værdier, så:

Alle andre beregninger udføres tilsvarende, herunder bestemmelse af gennemsnittet.

For eksempel, hvis vores fem hunde blot er et udsnit af hundepopulationen (alle hunde på planeten), skal vi dividere med 4, ikke 5, nemlig:

Prøvevarians = mm 2.

I dette tilfælde er standardafvigelsen for prøven lig med mm (afrundet til nærmeste hele tal).

Vi kan sige, at vi har lavet en "korrektion" i det tilfælde, hvor vores værdier kun er et lille udsnit.

Bemærk. Hvorfor præcis kvadratiske forskelle?

Men hvorfor tager vi præcis de kvadratiske forskelle, når vi beregner variansen? Lad os sige, at når du målte en eller anden parameter, modtog du følgende sæt værdier: 4; 4; -4; -4. Hvis vi blot lægger de absolutte afvigelser fra middelværdien (forskelle) sammen... udligner de negative værdier med de positive:

.

Det viser sig, at denne mulighed er ubrugelig. Så er det måske værd at prøve de absolutte værdier af afvigelserne (det vil sige modulerne af disse værdier)?

Ved første øjekast viser det sig godt (den resulterende værdi kaldes i øvrigt den gennemsnitlige absolutte afvigelse), men ikke i alle tilfælde. Lad os prøve et andet eksempel. Lad målingen resultere i følgende sæt værdier: 7; 1; -6; -2. Så er den gennemsnitlige absolutte afvigelse:

Wow! Igen fik vi et resultat på 4, selvom forskellene har en meget større spredning.

Lad os nu se, hvad der sker, hvis vi kvadrerer forskellene (og derefter tager kvadratroden af ​​deres sum).

For det første eksempel vil det være:

.

For det andet eksempel vil det være:

Nu er det en helt anden sag! Jo større spredningen af ​​forskellene er, jo større er standardafvigelsen... hvilket vi sigtede efter.

Faktisk bruger denne metode den samme idé som ved beregning af afstanden mellem punkter, kun anvendt på en anden måde.

Og fra et matematisk synspunkt giver det at bruge kvadrater og kvadratrødder flere fordele, end vi kunne få ved absolutte afvigelsesværdier, hvilket gør standardafvigelsen gældende for andre matematiske problemer.

Sergey Valerievich fortalte dig, hvordan du finder standardafvigelsen

Andrey Lipov

Enkelt sagt viser standardafvigelsen, hvor meget prisen på et instrument svinger over tid. Det vil sige, jo højere denne indikator er, jo større er volatiliteten eller variabiliteten af ​​en række værdier.

Standardafvigelse kan og bør bruges til at analysere værdisæt, da to sæt med tilsyneladende samme gennemsnit kan vise sig at være helt forskellige i spredningen af ​​værdier.

Eksempel

Lad os tage to rækker med tal.

a) 1,2,3,4,5,6,7,8,9. Gennemsnit - 5. St. afvigelse = 2,7386

b) 20,1,7,1,15,-1,-20,4,18,5. Gennemsnit - 5. St. afvigelse = 12,2066

Hvis du ikke holder hele rækken af ​​tal foran dine øjne, viser standardafvigelsesindikatoren, at i tilfælde af "b" er værdierne meget mere spredt omkring deres gennemsnitlige værdi.

Groft sagt, i serie "b" er værdien 5 plus eller minus 12 (i gennemsnit) - ikke nøjagtig, men det afslører betydningen.

Sådan beregnes standardafvigelsen

For at beregne standardafvigelsen kan du bruge en formel, der er lånt fra beregning af standardafvigelsen for investeringsforeningers afkast:

Her er N antallet af mængder,
DOHaverage - gennemsnittet af alle værdier,
DOH-periode - værdi N.

I Excel hedder den tilsvarende funktion STANDARDEVAL (eller STDEV i den engelske version af programmet).

Trin-for-trin instruktionerne er som følger:

1. Beregn gennemsnittet for en række tal.
2. Bestem forskellen mellem middelværdien og denne værdi for hver værdi.
3. Beregn summen af ​​kvadraterne af disse forskelle.
4. Divider den resulterende sum med antallet af tal i serien.
5. Tag kvadratroden af ​​det tal, du fik i det sidste trin.

Dine venner vil finde denne information nyttig. Del med dem!

Vi er nødt til at beskæftige os med beregningen af ​​sådanne værdier som spredning, standardafvigelse og selvfølgelig variationskoefficient. Det er beregningen af ​​sidstnævnte, der fortjener særlig opmærksomhed. Det er meget vigtigt, at enhver nybegynder, der lige er begyndt at arbejde med en regnearkseditor, hurtigt kan beregne den relative grænse for spredningen af ​​værdier.

Hvad er variationskoefficienten, og hvorfor er det nødvendigt?

Så det forekommer mig, at det ville være nyttigt at tage en kort teoretisk udflugt og forstå arten af ​​variationskoefficienten. Denne indikator er nødvendig for at afspejle rækkevidden af ​​data i forhold til gennemsnitsværdien. Med andre ord viser den forholdet mellem standardafvigelsen og middelværdien. Variationskoefficienten måles normalt i procent og bruges til at vise homogeniteten af ​​en tidsserie.

Variationskoefficienten bliver en uundværlig assistent, når du skal lave en prognose baseret på data fra en given prøve. Denne indikator vil fremhæve hovedserien af ​​værdier, der vil være mest nyttige til efterfølgende prognoser, og vil også rydde stikprøven af ​​uvæsentlige faktorer. Så hvis du ser, at koefficientværdien er 0%, så erklær dig trygt, at serien er homogen, hvilket betyder, at alle værdierne i den er lig med hinanden. Hvis variationskoefficienten tager en værdi, der overstiger 33 %, indikerer dette, at du har at gøre med en heterogen serie, hvor individuelle værdier adskiller sig væsentligt fra stikprøvegennemsnittet.

Hvordan finder man standardafvigelsen?

Da vi for at beregne variationsindekset i Excel skal bruge standardafvigelsen, ville det være ret passende at finde ud af, hvordan vi kan beregne denne parameter.

Fra skolealgebrakurset ved vi, at standardafvigelsen er kvadratroden udtrukket fra variansen, det vil sige, at denne indikator bestemmer graden af ​​afvigelse af en bestemt indikator for den samlede prøve fra dens gennemsnitlige værdi. Med dens hjælp kan vi måle det absolutte mål for fluktuation af karakteristikken, der studeres, og klart fortolke den.

Beregning af koefficienten i Excel

Desværre har Excel ikke en standardformel, der giver dig mulighed for automatisk at beregne variationsindekset. Men det betyder ikke, at du skal lave beregningerne i dit hoved. Fraværet af en skabelon i "Formellinjen" forringer på ingen måde Excels evner, så du kan ganske nemt tvinge programmet til at udføre den beregning, du har brug for, ved at indtaste den relevante kommando manuelt.

For at beregne variationsindekset i Excel skal du huske dit matematikkursus på gymnasiet og dividere standardafvigelsen med stikprøvegennemsnittet. Det vil sige, at formlen faktisk ser sådan ud - STANDARDEVAL(angivet dataområde)/MIDDEL(angivet dataområde). Du skal indtaste denne formel i Excel-cellen, hvor du ønsker at få den udregning, du skal bruge.

Glem ikke, at da koefficienten er udtrykt i procent, skal cellen med formlen formateres i overensstemmelse hermed. Du kan gøre dette på følgende måde:

1. Åbn fanen Hjem.
2. Find kategorien "Celleformat" i den, og vælg den ønskede mulighed.

Alternativt kan du indstille procentformatet for cellen ved at højreklikke på den aktiverede tabelcelle. I kontekstmenuen, der vises, i lighed med ovenstående algoritme, skal du vælge kategorien "Cell Format" og indstille den nødvendige værdi.

Vælg Procent, og indtast om nødvendigt antallet af decimaler

Måske kan ovenstående algoritme virke kompliceret for nogle. Faktisk er det så simpelt at beregne koefficienten som at tilføje to naturlige tal. Når du har fuldført denne opgave i Excel, vil du aldrig vende tilbage til kedelige, komplekse løsninger i en notesbog.

Kan du stadig ikke lave en kvalitativ sammenligning af graden af ​​dataspredning? Forvirret over størrelsen af ​​stikprøven? Så kom i gang med det samme og behersk i praksis alt det teoretiske materiale, der blev præsenteret ovenfor! Lad statistisk analyse og prognoseudvikling ikke længere få dig til at føle dig bange og negativ. Spar din energi og tid med

God eftermiddag

I denne artikel besluttede jeg at se på, hvordan standardafvigelse fungerer i Excel ved hjælp af STANDARDEVAL-funktionen. Jeg har bare ikke beskrevet eller kommenteret det i meget lang tid, og også simpelthen fordi det er en meget nyttig funktion for dem, der læser højere matematik. Og at hjælpe elever er helligt, jeg ved af erfaring, hvor svært det er at mestre. I virkeligheden kan standardafvigelsesfunktioner bruges til at bestemme stabiliteten af ​​solgte produkter, skabe priser, justere eller danne et sortiment og andre lige så nyttige analyser af dit salg.

Excel bruger flere variationer af denne variansfunktion:

Matematisk teori

Først lidt om teorien, hvordan man i matematisk sprog kan beskrive standardafvigelsesfunktionen for at bruge den i Excel, til at analysere fx salgsstatistikdata, men mere om det senere. Jeg advarer dig med det samme, jeg vil skrive en masse uforståelige ord...)))), hvis der er noget nedenfor i teksten, så søg straks efter praktisk anvendelse i programmet.

Hvad gør standardafvigelse helt præcist? Den estimerer standardafvigelsen af ​​en tilfældig variabel X i forhold til dens matematiske forventning baseret på et upartisk estimat af dens varians. Enig, det lyder forvirrende, men jeg tror, ​​eleverne vil forstå, hvad vi egentlig taler om!

Først skal vi bestemme "standardafvigelsen", for efterfølgende at beregne "standardafvigelsen", vil formlen hjælpe os med dette: Formlen kan beskrives som følger: den vil blive målt i de samme enheder som målingerne af en stokastisk variabel og bruges ved beregning af den aritmetiske standardmiddelfejl, ved konstruktion af konfidensintervaller, ved test af hypoteser til statistik eller ved analyse af en lineær sammenhæng mellem uafhængige variable. Funktionen er defineret som kvadratroden af ​​de uafhængige variables varians.

Nu kan vi definere og standardafvigelse er en analyse af standardafvigelsen af ​​en tilfældig variabel X i forhold til dens matematiske perspektiv baseret på et upartisk estimat af dens varians. Formlen er skrevet sådan:
Jeg bemærker, at alle to estimater er partiske. I generelle tilfælde er det ikke muligt at konstruere et objektivt skøn. Men et estimat baseret på et estimat af den upartiske varians vil være konsistent.

Praktisk implementering i Excel

Nå, lad os nu gå væk fra den kedelige teori og se i praksis, hvordan STANDARDEVAL-funktionen fungerer. Jeg vil ikke overveje alle variationer af standardafvigelsesfunktionen i Excel; en er nok, men i eksempler. Lad os som et eksempel se på, hvordan salgsstabilitetsstatistikker bestemmes.

Først skal du se på stavningen af ​​funktionen, og som du kan se, er den meget enkel:

STANDARDAFVIKLING.Г(_tal1_;_tal2_; ….), hvor:

Lad os nu oprette en eksempelfil og, baseret på den, overveje, hvordan denne funktion fungerer. Da det for at udføre analytiske beregninger er nødvendigt at bruge mindst tre værdier, som i princippet i enhver statistisk analyse, tog jeg betinget 3 perioder, dette kunne være et år, et kvartal, en måned eller en uge. I mit tilfælde - en måned. For maksimal pålidelighed anbefaler jeg at tage så mange perioder som muligt, men ikke mindre end tre. Alle data i tabellen er meget enkle for klarhed i drift og funktionalitet af formlen.

Først skal vi beregne den gennemsnitlige værdi efter måned. Vi vil bruge AVERAGE-funktionen til dette og få formlen: = AVERAGE(C4:E4).
Nu kan vi faktisk finde standardafvigelsen ved hjælp af funktionen STANDARDEVAL.G, i hvilken værdi vi skal indtaste salget af produktet for hver periode. Resultatet vil være en formel med følgende form: =STANDARDAFVIKLING.Г(C4;D4;E4).
Nå, halvdelen af ​​arbejdet er udført. Næste trin er at danne "Variation", dette fås ved at dividere med gennemsnitsværdien, standardafvigelsen og omregne resultatet til procenter. Vi får følgende tabel:
Nå, de grundlæggende beregninger er afsluttet, det eneste, der er tilbage, er at finde ud af, om salget er stabilt eller ej. Lad os tage som betingelse, at afvigelser på 10 % betragtes som stabile, fra 10 til 25 % er disse små afvigelser, men alt over 25 % er ikke længere stabilt. For at opnå resultatet i henhold til betingelserne bruger vi en logisk, og for at opnå resultatet skriver vi formlen:

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

Alle intervaller er taget for overskuelighed; dine opgaver kan have helt andre betingelser.
For at forbedre datavisualiseringen, når din tabel har tusindvis af positioner, bør du benytte lejligheden til at anvende visse betingelser, som du har brug for eller bruge til at fremhæve visse muligheder med et farveskema, dette vil være meget tydeligt.

Først skal du vælge dem, som du vil anvende betinget formatering for. I kontrolpanelet "Hjem" skal du vælge "Betinget formatering" og i rullemenuen vælge "Regler for at fremhæve celler" og derefter klikke på menupunktet "Tekst indeholder...". Der vises en dialogboks, hvor du indtaster dine betingelser.

Efter du har skrevet forholdene ned, for eksempel "stabil" - grøn, "normal" - gul og "ustabil" - rød, får vi en smuk og forståelig tabel, hvor du kan se, hvad du skal være opmærksom på først.

Brug af VBA til STDEV.Y-funktionen

Alle interesserede kan automatisere deres beregninger ved hjælp af makroer og bruge følgende funktion:

Funktion MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# For hvert x I Arr aSum = aSum + x "beregn summen af ​​array-elementerne aCnt = aCnt + 1 "beregn antallet af elementer Næste x aAver = aSum / aCnt "gennemsnitsværdi for hver x In Arr tmp = tmp + (x - aAver) ^ 2 "beregn summen af ​​kvadraterne af forskellen mellem array-elementerne og gennemsnitsværdien Next x MyStDevP = Sqr(tmp / aCnt ) "beregn STANDARDEV.G() Slutfunktion

Funktion MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# For hvert x i Arr aSum = aSum + x "beregn summen af ​​array-elementerne

At udføre nogen statistisk analyse er utænkeligt uden beregninger. I denne artikel vil vi se på, hvordan man beregner varians, standardafvigelse, variationskoefficient og andre statistiske indikatorer i Excel.

Maksimum og minimum værdi

Gennemsnitlig lineær afvigelse

Den gennemsnitlige lineære afvigelse er gennemsnittet af de absolutte (modulo) afvigelser fra i det analyserede datasæt. Den matematiske formel er:

-en- gennemsnitlig lineær afvigelse,

x– analyseret indikator,

X– gennemsnitsværdien af ​​indikatoren

n

I Excel kaldes denne funktion SROTCL.

Efter at have valgt SROTCL-funktionen angiver vi det dataområde, som beregningen skal foregå over. Klik på "OK".

Spredning

(modul 111)

Måske ikke alle ved hvad, så jeg vil forklare, det er et mål, der karakteriserer spredningen af ​​data omkring den matematiske forventning. Normalt er der dog kun en prøve tilgængelig, så følgende variansformel bruges:

s 2– prøvevarians beregnet ud fra observationsdata

x– individuelle værdier,

X– aritmetisk middelværdi for prøven,

n– antallet af værdier i det analyserede datasæt.

Den tilsvarende Excel-funktion er DISP.G. Når du analyserer relativt små prøver (op til ca. 30 observationer), skal du bruge , som beregnes ved hjælp af følgende formel.

Forskellen, som du kan se, er kun i nævneren. Excel har en funktion til at beregne upartisk prøvevarians DISP.B.

Vælg den ønskede mulighed (generel eller selektiv), angiv området, og klik på knappen "OK". Den resulterende værdi kan være meget stor på grund af den foreløbige kvadrering af afvigelserne. Spredning i statistik er en meget vigtig indikator, men den bruges normalt ikke i sin rene form, men til yderligere beregninger.

Standardafvigelse

Standardafvigelsen (RMS) er roden til variansen. Denne indikator kaldes også standardafvigelse og beregnes ved hjælp af formlen:

af den almindelige befolkning

efter prøve

Du kan blot tage roden af ​​variansen, men Excel har færdige funktioner til standardafvigelse: STDEV.G Og STDEV.V(for henholdsvis den generelle og stikprøvepopulationen).

Standard og standardafvigelse, jeg gentager, er synonymer.

Angiv derefter som sædvanligt det ønskede område og klik på "OK". Standardafvigelsen har samme måleenheder som den analyserede indikator og er derfor sammenlignelig med de originale data. Mere om dette nedenfor.

Variationskoefficienten

Alle indikatorer diskuteret ovenfor er bundet til skalaen af ​​kildedata og tillader ikke at få en billedlig idé om variationen i den analyserede befolkning. For at opnå et relativt mål for dataspredning, brug variationskoefficienten, som beregnes ved at dividere standardafvigelse på gennemsnit. Formlen for variationskoefficienten er enkel:

Der er ingen færdiglavet funktion til at beregne variationskoefficienten i Excel, hvilket ikke er et stort problem. Beregningen kan foretages ved blot at dividere standardafvigelsen med middelværdien. For at gøre dette skal du skrive i formellinjen:

STANDARDAFVIKLING.G()/AVERAGE()

Dataområdet er angivet i parentes. Brug om nødvendigt prøvestandardafvigelsen (STDEV.B).

Variationskoefficienten udtrykkes normalt som en procentdel, så du kan indramme en celle med en formel i et procentformat. Den nødvendige knap er placeret på båndet på fanen "Hjem":

Du kan også ændre formatet ved at vælge fra kontekstmenuen efter at have fremhævet den ønskede celle og højreklikke.

Variationskoefficienten bruges i modsætning til andre indikatorer for spredning af værdier som en uafhængig og meget informativ indikator for datavariation. I statistik er det generelt accepteret, at hvis variationskoefficienten er mindre end 33%, så er datasættet homogent, hvis mere end 33%, så er det heterogent. Disse oplysninger kan være nyttige til foreløbig karakterisering af dataene og til at identificere muligheder for yderligere analyse. Derudover giver variationskoefficienten, målt i procent, dig mulighed for at sammenligne graden af ​​spredning af forskellige data, uanset deres skala og måleenheder. Nyttig ejendom.

Oscillationskoefficient

En anden indikator for dataspredning i dag er oscillationskoefficienten. Dette er forholdet mellem variationsområdet (forskellen mellem maksimum- og minimumværdierne) og gennemsnittet. Der er ingen færdiggjort Excel-formel, så du bliver nødt til at kombinere tre funktioner: MAX, MIN, AVERAGE.

Oscillationskoefficienten viser omfanget af variationen i forhold til gennemsnittet, som også kan bruges til at sammenligne forskellige datasæt.

Generelt, ved hjælp af Excel, beregnes mange statistiske indikatorer meget enkelt. Hvis noget ikke er klart, kan du altid bruge søgefeltet i funktionsindsættet. Nå, Google er her for at hjælpe.

Nu foreslår jeg, at du ser videotutorialen.