Hvordan lage en normal distribusjonsklokkekurve i Excel

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

I statistikk er en klokkekurve (også kjent som en standard normalfordeling eller Gaussisk kurve) en symmetrisk graf som illustrerer tendensen til data til å samle seg rundt en senterverdi eller gjennomsnitt i et gitt datasett.

Y-aksen representerer den relative sannsynligheten for at en gitt verdi forekommer i datasettet, mens x-aksen plotter selve verdiene på diagrammet for å lage en klokkeformet kurve, 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 denne trinnvise opplæringen lærer du hvordan du lager en normal distribusjonsklokkekurve i Excel fra grunnen:

For å plotte en gaussisk kurve, må du vite to ting:

• Gjennomsnittet (også kjent som standardmåling). Dette bestemmer midten av kurven-som igjen karakteriserer kurvens posisjon.
• Standardavviket (SD) av målingene. Dette definerer spredningen av dataene dine i normalfordelingen-eller på vanlig engelsk, hvor bred kurven skal være. For eksempel, i bellkurven vist ovenfor, representerer ett standardavvik for gjennomsnittet intervallet mellom eksamenspoeng på 53 og 85.

Jo lavere SD, jo høyere kurve og desto mindre data 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 vil bli plassert innenfor ett SD fra gjennomsnittet, 95% innen to SD og 99,7% innenfor tre SD.

Nå som du vet det vesentlige, la oss gå fra teori til praksis.

Starter

For illustrasjonsformål, la oss anta at du har testresultatene på 200 elever og ønsker å rangere dem "på en kurve", noe som betyr at elevenes karakterer vil være basert på deres relative prestasjoner til resten av klassen:

Trinn 1: Finn gjennomsnittet.

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

Siden gjennomsnittet angir gjennomsnittsverdien av en prøve eller populasjon av data, kan du finne din standardmåling ved hjelp av GJENNOMSNITT -funksjonen.

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

1	= GJENNOMSNITT (B2: B201)

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

1	= RUND (GJENNOMSNITT (B2: B201), 0)

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:

1	= STDEV.P (B2: B201)

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

1	= RUND (STDEV.P (B2: B201), 0)

Trinn 3: Sett opp x-akseverdiene for kurven.

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

I vårt tilfelle vil x-akseverdiene bli brukt til å illustrere en bestemt eksamenskarakter, mens y-aksens verdier 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 sørg for at du velger et område som vil inneholde de tre standardavvikene.

La oss starte en telling på én (ettersom det ikke er noen måte en student kan få en negativ eksamenspoengsum) og gå helt opp til 150-det spiller ingen rolle om det er 150 eller 1500-for å sette opp et nytt hjelperbord.

1. Velg en tom celle under diagramdataene (for eksempel E4) og type “1,” verdien som definerer det første intervallet.
2. Naviger til Hjem kategorien.
3. I Redigerer gruppe, velg "Fylle.”
4. Under "Serien i," plukke ut "Kolonne.”
5. For "Trinnverdi,”Type “1.” Denne verdien bestemmer trinnene som blir lagt til automatisk til Excel når det siste intervallet.
6. For "Stopp verdi,"Type"150,” verdien som står for det siste intervallet, og klikk "OK.”

Mirakuløst nok, 149 celler i kolonne E (E5: E153) har blitt fylt med verdiene fra 2 til 150.

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

Trinn 4: Beregn normalfordelingsverdiene for hver x-akseverdi.

Finn nå normalfordelingsverdiene-sannsynligheten for at en student får en bestemt eksamenskarakter representert med en bestemt x-akseverdi-for hvert av intervallene. Heldigvis for deg har Excel arbeidshesten til å gjøre alle disse beregningene for deg: NORM.DIST -funksjonen.

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

1	= NORM.DIST (E4, $ F $ 1, $ F $ 2, FALSE)

Her er den dekodede versjonen for å hjelpe deg med å justere deretter:

1	= NORM.DIST ([det første intervallet], [gjennomsnittet (absolutt referanse)], [standardavviket (absolutt referanse), FALSKT)

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

Dobbeltklikk nå på fyllhåndtaket for å kopiere formelen til resten av cellene (F5: F153).

Trinn 5: Lag et spredningsdiagram med glatte linjer.

Endelig er det på tide å bygge bjellekurven:

1. Velg en hvilken som helst verdi i hjelpetabellen som inneholder verdiene for x- og y-aksen (E4: F153).
2. Gå til Sett inn kategorien.
3. Klikk på "Sett inn Scatter (X, Y) eller Bubble Chart”-Knappen.
4. Velg "Spred med glatte linjer. ”

Trinn 6: Sett opp etikettbordet.

Teknisk sett har du klokkekurven. Men det ville være vanskelig å lese ettersom det mangler data som beskriver det.

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 et hjelperbord som følger:

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

Deretter beregner du standardavviksverdiene under gjennomsnittet ved å skrive inn denne enkle formelen celle I4:

1	= I5- $ F $ 2

Enkelt sagt trekker formelen summen av de foregående standardavviksverdiene fra gjennomsnittet. Dra dra fyllingshåndtaket oppover for å kopiere formelen til de to resterende cellene (I2: I3).

Gjenta den samme prosessen for standardavvikene over gjennomsnittet ved å bruke speilformelen:

1	= I5+$ F $ 2

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

Til slutt fyller du ut etikettverdiene for y-aksen (J2: J8) med nuller som du vil at datamarkørene skal plasseres på den horisontale aksen.

Trinn 7: Sett inn etikettdataene i diagrammet.

Legg til alle dataene du har forberedt. Høyreklikk på diagramplottet og velg "Velg Data.”

Velg "I dialogboksen som dukker opp"Legge til.”

Marker de respektive cellene fra hjelpertabellen-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øyde serien for å få datamarkørene til å vises som prikker. For å gjøre det, høyreklikk på diagramplottet og velg "Endre diagramtype.”

Deretter designer du et kombinasjonsdiagram:

1. Naviger til Kombinasjon kategorien.
2. Til Serien "Series2" endring "Karttype" til "Spre.”
   • Merk: Sørg for "Serie 1"Forblir som"Spred med glatte linjer. ” Noen ganger vil Excel endre det når du lager en Kombinasjon Sørg også for "Serie 1”Ikke skyves til den sekundære aksen-avmerkingsboksen ved siden av diagramtypen bør ikke merkes.
3. Klikk "OK.”

Trinn 9: Endre den horisontale akseskalaen.

Sentrer diagrammet på bjellekurven ved å justere den horisontale akseskalaen. Høyreklikk på den horisontale aksen og velg "Format akse"Fra menyen.

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

• Gå til Aksealternativer kategorien.
• Sett Minimumsgrenser verdi til "15.”
• Sett Maksimale grenser verdi til "125.”

Du kan justere akseskalaområdet uansett hvor du finner det passende, men siden du kjenner standardavviksområdene, setter du grenseverdiene litt borte fra hvert av dine tredje standardavvik for å vise “halen” på kurven.

Trinn 10: Sett inn og plasser de egendefinerte datatikettene.

Når du polerer opp diagrammet, må du legge til de egendefinerte datatikettene. Høyreklikk først på en hvilken som helst prikk som representerer Serien "Series2" og velg "Legg til datatiketter.”

Deretter erstatter du standardetikettene med de du tidligere konfigurerte, og plasserer dem over datamerkene.

1. Høyreklikk på hvilken som helst Serien "Series2" datatikett.
2. Plukke ut "Formater datatiketter.”
3. I oppgaveruten, bytt til Etikettalternativer kategorien.
4. Undersøk "X Verdi"Boks.
5. Fjern merket for "Y Verdi"Boks.
6. Under "Etikettposisjon,"Velg"Ovenfor.”

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

Trinn 11: Farg datamarkørene på nytt (valgfritt).

Til slutt, farg prikkene på nytt for å hjelpe dem med å passe inn i diagramstilen din.

1. Høyreklikk på hvilken som helst Serien "Series2" datatikett.
2. Klikk på "Fylle”-Knappen.
3. Velg fargen din fra paletten som vises.

Fjern også kantene rundt prikkene:

1. Høyreklikk på den samme datamarkøren igjen og velg "Oversikt.”
2. Velg "Ingen disposisjon.”

Trinn 12: Legg til vertikale linjer (valgfritt).

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

• Velg diagramplottet (på den måten blir linjene satt inn direkte i diagrammet).
• Gå til Sett inn kategorien.
• Klikk på "Former”-Knappen.
• Velg "Linje.”

Hold nede "SKIFTE" mens du drar musen for å tegne perfekt vertikale linjer fra hver prikk til der hver linje møter bjellekurven.

Endre diagramtittelen, og den forbedrede bellkurven viser dine verdifulle distribusjonsdata.

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