En bjellekurve, også kjent som normalfordeling, er den vanligste distribusjonstypen for en variabel. Det aksepteres som en fordeling som forekommer naturlig i mange situasjoner. I en bjellekurve definerer det høyeste punktet i buen gjennomsnittet, som er det høyeste antallet forekomster av et element. Sannsynligheten for hendelser reduseres mot hver side av kurven. Du kan bruke en kurve for å sammenligne ytelser ved å ekskludere ekstremer, eller definere forventningene dine ved muligheten for at et resultat vil ligge innenfor et område til venstre eller høyre for sentrum. I denne veiledningen skal vi vise deg hvordan du lager en bjelkekurve i Excel med et bruksscenario fra den virkelige verden som et eksempel.
Last ned arbeidsbok
Grunnleggende
Alt du trenger er gjennomsnittet (gjennomsnittet) og standardavviksverdiene til datasettet ditt. Begge disse beregningene kan beregnes i Excel ved hjelp av formlene nedenfor.
Med tanke på disse to verdiene følger normalt fordelte verdier disse reglene:
- Det totale arealet under kurven er lik 1 (100%)
- Sentrum av bjelkekurven er gjennomsnittet av datapunktet
- (1-σ) Omtrent 68,2% av arealet under kurven faller innenfor ett standardavvik (gjennomsnitt ± standardavvik)
- (2-σ) Omtrent 95,5% av arealet under kurven faller innenfor to standardavvik (gjennomsnitt ± 2 * standardavvik)
- (3-σ) Omtrent 99,7% av arealet under kurven faller innenfor tre standardavvik (gjennomsnitt ± 3 * standardavvik)
Bilde fra University of Virginia
Lage en bjellekurve i Excel
La oss ta et vanlig eksempel, og si at vi analyserer eksamensresu Det er for en klasse studenter. Vi bruker en bjelkekurve for å måle eksamensresultater for bedre sammenligning.
Vi begynner med å beregne beregningene for å generere normalfordelte data som vil generere kurven vår. Vi må beregne:
- Gjennomsnitt (gjennomsnitt) av verdiene.
- Standardavvik for verdiene.
- 3-standardavviksgrenser for før og etter gjennomsnitt.
- Intervallverdi for normalt distribuerte datapunkter. Dette krever også en bestemmelse av intervallpoengene. Du kan velge hvilket som helst tall, men husk det, flere intervaller betyr mer presisjon.
Metrics
Begynn med å beregne gjennomsnittet og standardavviket til dataene. Du kan bruke AVEREAGE- og STDEV.P-funksjonene til å beregne henholdsvis middel- og standardavviksverdiene.
Neste trinn er beregning de 3 standardavviksverdiene for å angi minimums- og maksimumsverdiene for 99,7% av dataene.
Maksimum = 83.23 – 3 * 5.54 = 99.86
Etter å ha satt minimums- og maksimumsverdiene for kurven vår, må vi generere intervallene . Intervallverdiene vil være basen for de normalt distribuerte verdiene. For å beregne intervallene er alt du trenger å gjøre å dele området mellom minimums- og maksimumsverdiene etter intervalltelling. I dette eksemplet setter vi dette til 20, men du kan bruke et større tall for å øke antall datapunkter.
Når intervallverdien er beregnet, kan du generere datapunktene. For å gjøre dette, skriv inn minimumsverdien i en celle. Deretter, rett under minimumsverdien, skriver du inn formelen for å legge til intervallverdien til minimumet. Her brukte vi cellereferanser (som J4) som hjelper å fylle ut datapunktene enkelt opp til maksimumsverdien.
Det neste trinnet er å beregne de normalt distribuerte verdiene fra de genererte datapunktene. Du kan bruke Excels NORM.DIST-funksjon for å generere disse verdiene.
Bruk de befolkede datapunktene som det første argumentet for funksjonen. Gjennomsnitt og standardavvik er neste argument. Fullfør formelen med en FALSE boolsk verdi for å bruke ikke-kumulativ type av denne funksjonen.
Diagram
Vi er nesten ferdige! Velg datapunktene og normalfordelingsverdiene, og sett deretter inn et X-Y scatter-diagram. Bruk Scattered with Smooth Lines-versjonen til å lage en bjelkekurve i Excel.
Diagrammet kan virke litt utenfor først.La oss se hvordan du kan få det til å se bedre ut.
Hvis du vil endre tittelen på diagrammet, dobbeltklikker du på tittelen og oppdaterer navnet.
Deretter dobbeltklikker du på X-akse og definer minimums- og maksimumsverdier fra Axis Options-panelet for å eliminere det hvite rommet på begge sider. Dette vil gi diagrammet bedre klokkeform. Vi setter verdier som ligger litt utenfor datasettet vårt. For eksempel 66 – 100 for verdier 66.30 – 99.86.
Du kan forbedre diagrammet ditt ytterligere ved å legge til standardavviksverdiene .
