Sådan oprettes en normal distributionsklokkekurve i Excel

Denne vejledning viser, hvordan du opretter en normal distributionsklokke -kurve i alle versioner af Excel: 2007, 2010, 2013, 2016 og 2022.

I statistik er en klokkekurve (også kendt som en standard normalfordeling eller gaussisk kurve) en symmetrisk graf, der illustrerer datatendens til at klynge sig omkring en centerværdi eller middelværdi i et givet datasæt.

Y-aksen repræsenterer den relative sandsynlighed for, at en given værdi forekommer i datasættet, mens x-aksen plotter selve værdierne på diagrammet for at oprette en klokkeformet kurve, deraf navnet.

Grafen hjælper os med at analysere, om en bestemt værdi er en del af den forventede variation eller er statistisk signifikant og derfor skal undersøges nærmere.

Da Excel ikke har nogen indbyggede løsninger at tilbyde, skal du selv plotte det. Derfor udviklede vi Chart Creator-tilføjelsen, et værktøj, der giver dig mulighed for at opbygge avancerede Excel-diagrammer med få klik.

I denne trin-for-trin vejledning lærer du, hvordan du opretter en normal distributionsklokke i Excel fra bunden:

For at tegne en gaussisk kurve skal du vide to ting:

• Middelværdien (også kendt som standardmåling). Dette bestemmer kurvens centrum-hvilket igen karakteriserer kurvens position.
• Standardafvigelsen (SD) af målingerne. Dette definerer spredningen af ​​dine data i normalfordelingen-eller på almindeligt engelsk, hvor bred kurven skal være. For eksempel i klokkekurven vist ovenfor repræsenterer en standardafvigelse af middelværdien intervallet mellem eksamensresultater på 53 og 85.

Jo lavere SD, jo højere kurve og jo mindre spredes dine data og omvendt.

Det er værd at nævne 68-95-99.7-reglen, der kan anvendes på enhver normal distributionskurve, hvilket betyder, at omtrent 68% af dine data vil blive placeret inden for en SD væk fra middelværdien, 95% inden for to SD og 99,7% inden for tre SD.

Nu hvor du kender det væsentlige, lad os gå fra teori til praksis.

Kom godt i gang

For at illustrere det, antager vi, at du har 200 elevers testresultater og ønsker at bedømme dem "på en kurve", hvilket betyder, at elevernes karakterer vil være baseret på deres relative præstationer til resten af ​​klassen:

Trin 1: Find middelværdien.

Typisk får du middelværdien og SD -værdierne fra starten, men hvis det ikke er tilfældet, kan du nemt beregne disse værdier i blot et par enkle trin. Lad os først tackle middelværdien.

Da middelværdien angiver gennemsnitsværdien af ​​en prøve eller en population af data, kan du finde din standardmåling ved hjælp af GENNEMSNIT -funktionen.

Indtast følgende formel i en hvilken som helst tom celle (F1 i dette eksempel) ved siden af ​​dine faktiske data (kolonner A og B) for at beregne gennemsnittet af eksamensresultaterne i datasættet:

1	= Gennemsnit (B2: B201)

En hurtig bemærkning: oftere end ikke skal du muligvis afrunde formeloutput. For at gøre det skal du blot pakke det ind i RUND -funktionen som følger:

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

Trin 2: Find standardafvigelsen.

En ned, en til at gå. Heldigvis har Excel en særlig funktion til at gøre alt det beskidte arbejde med at finde standardafvigelsen for dig:

1	= STDEV.P (B2: B201)

Igen vælger formlen alle værdierne fra det angivne celleområde (B2: B201) og beregner sin standardafvigelse-bare glem ikke også at afrunde output.

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

Trin 3: Konfigurer x-aksens værdier for kurven.

Grundlæggende udgør diagrammet et massivt antal intervaller (tænk på dem som trin) sammenføjet med en linje for at skabe en jævn kurve.

I vores tilfælde vil x-akse-værdierne blive brugt til at illustrere en bestemt eksamensscore, mens y-aksens værdier fortæller os sandsynligheden for, at en elev får denne score på eksamen.

Teknisk set kan du inkludere så mange intervaller, som du vil-du kan ubesværet slette de overflødige data senere ved at ændre den vandrette akseskala. Bare sørg for at vælge et område, der vil inkorporere de tre standardafvigelser.

Lad os starte en optælling med én (da der ikke er nogen måde, hvorpå en elev kan få en negativ eksamens score) og gå helt op til 150-det er ikke ligegyldigt, om det er 150 eller 1500-for at dække et andet hjælperbord.

1. Vælg en hvilken som helst tom celle under diagramdataene (f.eks E4) og type “1,” værdien, der definerer det første interval.
2. Naviger til Hjem fane.
3. I Redigering gruppe, vælg "Fylde.”
4. Under "Serie i," Vælg "Kolonne.”
5. Til "Trinværdi,”Type “1.” Denne værdi bestemmer de trin, der automatisk tilføjes, indtil Excel når det sidste interval.
6. Til "Stopværdi,"Type"150,” værdien, der står for det sidste interval, og klik på "OKAY.”

Mirakuløst nok 149 celler i kolonne E (E5: E153) er blevet fyldt med værdierne fra 2 til 150.

BEMÆRK: Skjul ikke de originale dataceller som vist på skærmbillederne. Ellers fungerer teknikken ikke.

Trin 4: Beregn de normale fordelingsværdier for hver x-akse værdi.

Find nu de normale fordelingsværdier-sandsynligheden for, at en elev får en bestemt eksamensscore repræsenteret af en bestemt x-akse-værdi-for hvert af intervallerne. Heldigvis for dig har Excel arbejdshesten til at udføre alle disse beregninger for dig: funktionen NORM.DIST.

Indtast følgende formel i cellen til højre (F4) af dit første interval (E4):

1	= NORM.FORDELING (E4, $ F $ 1, $ F $ 2, FALSK)

Her er den afkodede version, der hjælper dig med at justere i overensstemmelse hermed:

1	= NORM.DIST ([det første interval], [middelværdien (absolut reference)], [standardafvigelsen (absolut reference), FALSK)

Du låser middelværdien og SD -værdierne, så du ubesværet kan udføre formlen for de resterende intervaller (E5: E153).

Dobbeltklik nu på fyldhåndtaget for at kopiere formlen til resten af ​​cellerne (F5: F153).

Trin #5: Opret et spredningsdiagram med glatte linjer.

Endelig er tiden kommet til at bygge klokkekurven:

1. Vælg en hvilken som helst værdi i hjælper-tabellen, der indeholder x- og y-aksens værdier (E4: F153).
2. Gå til Indsæt fane.
3. Klik på "Indsæt Scatter (X, Y) eller Bubble Chart”Knap.
4. Vælg "Spred med glatte linjer. ”

Trin #6: Konfigurer etiketbordet.

Teknisk set har du din klokkekurve. Men det ville være svært at læse, da det mangler data, der beskriver det.

Lad os gøre normalfordelingen mere informativ ved at tilføje etiketterne, der illustrerer alle standardafvigelsesværdierne under og over middelværdien (du kan også bruge dem til at vise z-score i stedet).

Til det skal du dække endnu et hjælperbord som følger:

Kopier først middelværdien (F1) ved siden af ​​den tilsvarende celle i kolonne X-værdi (I5).

Derefter beregnes standardafvigelsesværdierne under middelværdien ved at indtaste denne enkle formel i celle I4:

1	= I5- $ F $ 2

Kort sagt, formlen trækker summen af ​​de foregående standardafvigelsesværdier fra middelværdien. Træk nu fyldhåndtaget opad for at kopiere formlen til de resterende to celler (I2: I3).

Gentag den samme proces for standardafvigelser over middelværdien ved hjælp af spejlformlen:

1	= I5+$ F $ 2

På samme måde skal du udføre formlen for de to andre celler (I7: I8).

Endelig skal du udfylde y-aksens etiketværdier (J2: J8) med nuller, som du vil have datamærkerne placeret på den vandrette akse.

Trin #7: Indsæt etiketdata i diagrammet.

Tilføj nu alle de data, du har forberedt. Højreklik på diagramplottet og vælg "Vælg Data.”

I dialogboksen, der dukker op, skal du vælge "Tilføje.”

Fremhæv de respektive celler varierer fra hjælperbordet-I2: I8 til "Serie X værdier”Og J2: J8 til "Serie Y -værdier ”-og klik "OKAY.”

Trin #8: Skift diagramtype for etiketserien.

Vores næste trin er at ændre diagramtypen for den nyligt tilføjede serie for at få datamarkørerne til at se ud som prikker. For at gøre det skal du højreklikke på diagramplottet og vælge "Skift diagramtype.”

Design derefter et kombinationsdiagram:

1. Naviger til Kombination fane.
2. Til Serie "Serie 2" lave om "Diagramtype" til "Sprede.”
   • Bemærk: Sørg for "Serie 1"Forbliver som"Spred med glatte linjer. ” Nogle gange vil Excel ændre det, når du laver en Kombination Sørg også for "Serie 1”Ikke skubbes til den sekundære akse-afkrydsningsfeltet ud for diagramtypen bør ikke markeres.
3. Klik på “Okay.”

Trin #9: Rediger den vandrette akseskala.

Centrer diagrammet på klokkekurven ved at justere den vandrette akseskala. Højreklik på den vandrette akse og vælg "Format akse”Fra menuen.

Når opgaveruden vises, skal du gøre følgende:

• Gå til Akseindstillinger fane.
• Indstil Minimumsgrænser værdi til "15.”
• Indstil Maksimale grænser værdi til "125.”

Du kan justere akseskalaområdet, som du finder det passende, men da du kender standardafvigelsesområderne, skal du indstille grænser -værdierne lidt væk fra hver af dine tredje standardafvigelser for at vise kurvens "hale".

Trin #10: Indsæt og placer de tilpassede datatiketter.

Når du polerer dit diagram, skal du tilføje de tilpassede datatiketter. Højreklik først på en vilkårlig prik, der repræsenterer Serie "Serie 2" og vælg "Tilføj datatiketter.”

Derefter skal du udskifte standardetiketterne med dem, du tidligere har konfigureret, og placere dem over datamærkerne.

1. Højreklik på en vilkårlig Serie "Serie 2" datamærke.
2. Vælg "Formater datatiketter.”
3. I procesruden skal du skifte til Etiketindstillinger fane.
4. Tjek "X Værdi”Kasse.
5. Fjern markeringen i "Y Værdi”Kasse.
6. Under "Etiketposition,"Vælg"Over.”

Du kan også nu fjerne gitterlinjerne (højreklik på dem> Slet).

Trin #11: Farv datamarkørerne (valgfrit).

Endelig farves prikkerne igen for at hjælpe dem med at passe ind i din diagramstil.

1. Højreklik på en vilkårlig Serie "Serie 2" datamærke.
2. Klik på "Fylde”Knap.
3. Vælg din farve fra paletten, der vises.

Fjern også grænserne omkring prikkerne:

1. Højreklik på den samme datamarkør igen og vælg "Omrids.”
2. Vælg "Ingen kontur.”

Trin #12: Tilføj lodrette linjer (valgfrit).

Som en sidste justering kan du tilføje lodrette linjer til diagrammet for at hjælpe med at understrege SD -værdierne.

• Vælg diagramplottet (på den måde indsættes linjerne direkte i diagrammet).
• Gå til Indsæt fane.
• Klik på "Former”Knap.
• Vælg "Line.”

Hold nede "FLYTTE" nøgle, mens du trækker musen for at tegne perfekt lodrette linjer fra hver prik til hvor hver linje møder klokkekurven.

Skift diagramtitel, og din forbedrede klokkekurve viser dine værdifulde distributionsdata klar.

Og sådan gør du det. Du kan nu vælge ethvert datasæt og oprette en normal distributionsklokke -kurve ved at følge disse enkle trin!