Hvordan lage en normal distribusjonsklokkekurve i Excel

Denne opplæringen vil demonstrere hvordan du lager en normal distribusjonsklokkekurve i alle versjoner av Excel: 2007, 2010, 2013 , 2016 og 2019.

I statistikk er en bjellekurve (også kjent som en standard normalfordeling eller Gaussisk kurve) en symmetrisk graf som illustrerer datatendensen til klynging rundt en senterverdi, eller middelverdi, i et gitt datasett.

Y-aksen representerer den relative sannsynligheten for at en gitt verdi oppstår i datasettet mens x-aksen plotter verdiene selv i diagrammet for å lage en klokkeformet kurven, derav navnet.

Grafen hjelper oss med å analysere om en bestemt verdi er en del av den forventede variasjonen eller er statistisk signifikant og derfor må undersøkes nærmere.

Siden Excel ikke har noen innebygde løsninger å tilby, må du plotte det selv. Derfor utviklet vi Chart Creator-tillegget, et verktøy som lar deg bygge avanserte Excel-diagrammer med bare noen få klikk.

I dette trinnet- trinnvis opplæring, vil du lære hvordan du lager en normal distribusjonsklokkurve i Excel fra grunnen av:

Til plotte en Gaussisk kurve, du trenger å vite to ting:

• Gjennomsnittet (også kjent som standardmåling). Dette bestemmer sentrum for kurven — som igjen karakteriserer kurvens posisjon.
• Målingenes standardavvik (SD). Dette definerer spredningen av dataene dine i normalfordelingen – eller på engelsk, hvor bred kurven skal være. For eksempel, i bellkurven vist ovenfor representerer ett standardavvik av gjennomsnittet området mellom eksamenspoengene 53 og 85.

Jo lavere SD, jo høyere kurve og jo mindre dataene dine blir spredt, og omvendt.

Det er verdt å nevne 68-95-99.7-regelen som kan brukes på en hvilken som helst normal distribusjonskurve, noe som betyr at omtrent 68% av dataene dine blir plassert innenfor en SD unna gjennomsnittet, 95% innen to SD og 99,7% innen tre SD.

Nå som du kjenn det essensielle, la oss gå fra teori til praksis.

Komme i gang

For illustrasjonsformål, la oss anta at du har testresultatene på 200 studenter og ønsker å rangere dem «på en kurve , ”Som betyr at studentenes karakterer vil være basert på deres relative ytelse til resten av klassen:

Trinn 1: Finn gjennomsnittet.

Vanligvis får du gjennomsnittet og SD-verdier fra starten, men hvis det ikke er tilfelle, kan du enkelt beregne disse verdiene i bare noen få enkle trinn. La oss takle gjennomsnittet først.

Siden gjennomsnittet indikerer gjennomsnittsverdien til et utvalg eller en populasjon av data, kan du finne din standardmåling ved hjelp av AVERAGE-funksjonen.

Skriv inn følgende formel inn i en hvilken som helst tom celle (F1 i dette eksemplet) ved siden av dine faktiske data (kolonne A og B) for å beregne gjennomsnittet av eksamenspoengene i datasettet:

Et raskt notat : oftere enn ikke, må du kanskje avrunde formelutgangen. For å gjøre det, pakk det bare inn i RUNDE-funksjonen som følger:

Trinn 2: Finn standardavviket.

En ned, en å gå. Heldigvis har Excel en spesiell funksjon for å gjøre alt det skitne arbeidet med å finne standardavviket for deg:

Igjen plukker formelen alle verdiene fra det angitte celleområdet (B2: B201) og beregner standardavviket – bare ikke glem å avrunde utgangen også.

Trinn 3: Sett opp x-akseverdiene for kurven.

I utgangspunktet utgjør diagrammet et stort antall intervaller ( tenk på dem som trinn) sammenføyd med en linje for å skape en jevn kurve.

I vårt tilfelle vil x-akseverdiene brukes til å illustrere en bestemt eksamenspoeng mens y-akseverdiene vil fortelle oss sannsynligheten for at en student får den poengsummen på eksamen.

Teknisk sett kan du inkludere så mange intervaller du vil – du kan enkelt slette de overflødige dataene senere ved å endre den horisontale akseskalaen. Bare vær sikker på at du velger et område som vil omfatte de tre standardavvikene.

La oss starte en telling på en (ettersom det ikke er noen måte en student kan få en negativ eksamenspoeng) og gå helt opp til 150 — det spiller ingen rolle om det er 150 eller 1500 — for å sette opp en annen hjelpetabell.

1. Velg hvilken som helst tom celle under kartdataene (for eksempel E4) og skriv «1» verdien som definerer det første intervallet.
2. Naviger til Hjem-fanen.
3. I Redigeringsgruppen velger du «Fyll.»
4. Under «Serie i, «Velg» Kolonne. «
5. For» Trinnverdi «skriver du inn» 1. » Denne verdien bestemmer trinnene som automatisk blir lagt opp til Excel når det siste intervallet.
6. For «Stoppverdi» skriver du inn «150», verdien som står for det siste intervallet, og klikker «OK. ”

Mirakuløst sett, 149 celler i kolonne E (E5: E153) er fylt med verdiene som går fra 2 til 150.

MERK: Ikke skjul de originale datacellene som vist på skjermbildene. Ellers fungerer ikke teknikken.

Trinn 4: Beregn normalfordelingsverdiene for hver x-akseverdi.

Finn nå normalfordelingsverdiene – sannsynligheten for at en student får en viss eksamenspoeng representert av en bestemt x-akseverdi —For hvert av intervallene. Heldigvis for deg har Excel arbeidshesten til å gjøre alle disse beregningene for deg: NORM.FORDELING.

Skriv inn følgende formel i cellen til høyre (F4) for ditt første intervall (E4):

Her er den dekodede versjonen som hjelper deg med å justere deretter:

Du låser middel- og SD-verdiene slik at du enkelt kan utføre formelen for de gjenværende intervallene (E5: E153).

Nå dobbeltklikker du på fyllhåndtaket for å kopiere formelen til resten av cellene (F5: F153).

Trinn 5: Lag et spredningsdiagram med glatte linjer.

Endelig er tiden for å bygge bjellekurven kommet:

1. Velg hvilken som helst verdi i hjelpetabellen som inneholder x- og y-akseverdiene (E4: F153).
2. Gå til kategorien Sett inn.
3. Klikk på «Sett inn spredning (X, Y) eller boblediagram» -knappen.
4. Velg «Spred med glatte linjer.»

Trinn 6: Sett opp etikettabellen.

Teknisk sett ha klokkekurven din. Men det ville være vanskelig å lese da den mangler data som beskriver den.

La oss gjøre normalfordelingen mer informativ ved å legge til etikettene som illustrerer alle standardavviksverdiene under og over gjennomsnittet ( Du kan også bruke dem til å vise z-score i stedet).

For det, sett opp enda en hjelpetabell som følger:

Kopier først middelverdien (F1) ved siden av den tilsvarende cellen i kolonne X-verdi (I5).

Beregn deretter standardavviksverdiene under gjennomsnittet ved å skrive inn denne enkle formelen i celle I4:

Enkelt sagt trekker formelen summen av de foregående standardavviksverdiene fra gjennomsnittet. Dra nå fyllhåndtaket oppover for å kopiere formelen til de to gjenværende cellene (I2: I3).

Gjenta samme prosess for standardavvikene over gjennomsnittet ved hjelp av speilformelen:

På samme måte utfører du formelen for de to andre cellene (I7 : I8).

Til slutt fyller du y-aksens etikettverdier (J2: J8) med nuller som du vil ha datamarkører plassert på den horisontale aksen.

Trinn 7: Sett inn etikettdataene i diagrammet.

Nå, legg til alle dataene du har utarbeidet. Høyreklikk på kartplottet og velg «Velg data.»

I dialogboksen som dukker opp, velger du » Legg til. ”

Merk de respektive cellene fra hjelperbordet – I2: I8 for» Serie X-verdier «og J2 : J8 for «Serie Y-verdier» – og klikk «OK».

Trinn 8: Endre diagramtypen for etikettserien.

Vårt neste trinn er å endre diagramtypen for den nylig tilførte serien for å få datamarkørene til å vises som prikker. For å gjøre det, høyreklikk på kartplottet og velg «Endre Karttype. ”

Deretter designer du et kombinasjonskart:

1. Naviger til Kombinasjonsfane.
2. For serie «Series2» endrer du «Chart Type» til «Scatter.»
   • Merk: Forsikre deg om at «Series1» forblir som «Scatter with Smooth Lines.» Noen ganger endrer Excel det når du lager en kombinasjon. Pass også på at «Series1» ikke skyves til den sekundære aksen – avkrysningsruten ved siden av diagramtypen skal ikke merkes.
3. Klikk «OK.»

Trinn 9: Endre skalaen for den horisontale aksen.

Sentrer diagrammet på bjellekurven ved å justere den horisontale akseskalaen. Høyreklikk på den horisontale aksen og velg «Format Axis» fra menyen.

Når oppgaveruten vises, gjør du følgende:

• Gå til fanen Aksealternativer.
• Sett minimumsverdien til «15.»
• Sett maksimumsverdien til «125.»

Du kan tilpasse akseskalaområdet, men du vil, men siden du kjenner til standardavviksområdene, kan du sette grenseverdiene litt borte fra hver av dine tredje standardavvik for å vise kurvenes «hale» .

Trinn 10: Sett inn og plasser de egendefinerte dataetikettene.

Når du pusser opp diagrammet ditt, må du være sikker for å legge til de egendefinerte dataetikettene. Høyreklikk først på et punkt som representerer serie «Series2» og velg «Legg til dataetiketter.»

Deretter erstatter du standardetikettene med de du tidligere har satt opp, og plasser dem over datamarkørene.

1. Høyreklikk på en hvilken som helst serie «Series2» -dataetikett.
2. Velg «Format Dataetiketter. ”
3. I oppgaveruten bytter du til kategorien Etikettalternativer.
4. Merk av i boksen» X-verdi «.
5. Fjern merket for» Y-verdi » ”-Boksen.
6. Under» Etikettposisjon «velger du» Over. «

Du kan nå også fjerne rutenettene (høyreklikk på dem > Slett).

Trinn # 11: Omfarg datamarkørene (valgfritt).

Endelig farger du prikkene på nytt for å hjelpe dem med å passe inn i diagramstilen din.

1. Høyreklikk på en hvilken som helst Series «Series2» -dataetikett.
2. Klikk «Fyll» -knappen.
3. Velg fargen din fra paletten som vises.

Også r skyv kantene rundt prikkene:

1. Høyreklikk på den samme datamarkøren igjen og velg «Outline».
2. Velg «No Outline.»

Trinn # 12: Legg til vertikale linjer (valgfritt).

Som en endelig justering kan du legg til vertikale linjer i diagrammet for å understreke SD-verdiene.

• Velg kartplottet (på den måten vil linjene settes direkte inn i diagrammet).
• Gå til kategorien Sett inn.
• Klikk på «Figurer» -knappen.
• Velg «Linje».

Hold nede «SKIFT» -tasten mens dra musen for å tegne perfekt vertikale linjer fra hver prikk til der hver linje møter bjelkekurven.

Endre diagramtittelen , og den forbedrede bjellekurven er klar – viser verdifulle distribusjonsdata.

Og det er slik du gjør det. Du kan nå velge et hvilket som helst datasett og opprette en normal distribusjonsklokkekurve ved å følge disse enkle trinnene.