Sådan oprettes en normal distributionsklokkekurve i Excel

Denne vejledning viser, hvordan du opretter en normal distributionsklokkekurve i alle versioner af Excel: 2007, 2010, 2013 , 2016 og 2019.

I statistik er en klokkekurve (også kendt som en standard normalfordeling eller Gaussisk kurve) en symmetrisk graf, der illustrerer datatendens tendens til klynger 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 tegner værdierne selv i diagrammet for at skabe en klokkeformet kurven, 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 har vi udviklet tilføjelsesprogrammet Chart Creator, et værktøj, der giver dig mulighed for at oprette avancerede Excel-diagrammer med blot et par klik.

I dette trin- trinvis vejledning lærer du, hvordan du opretter en normal distributionsklokkekurve i Excel fra bunden:

Til plotte en Gaussisk kurve, skal du vide to ting:

• Middelværdien (også kendt som standardmåling). Dette bestemmer centrum for kurven – hvilket igen karakteriserer kurvens position.
• Målingenes standardafvigelse (SD). Dette definerer spredningen af dine data i normalfordelingen – eller på almindelig engelsk, hvor bred kurven skal være. F.eks. Repræsenterer en standardafvigelse af gennemsnittet i klokkekurven vist ovenfor intervallet mellem eksamensscore på 53 og 85.

Jo lavere SD, jo højere kurve og jo mindre dine data vil blive spredt ud og omvendt.

Det er værd at nævne 68-95-99.7-reglen, der kan anvendes på enhver normal distributionskurve, hvilket betyder at ca. 68% af dine data vil blive placeret inden for en SD væk fra gennemsnittet, 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

Som illustration skal vi antage, at du har testresultaterne på 200 studerende og vil bedømme dem “på en kurve , ”Hvilket betyder, at elevernes karakterer vil være baseret på deres relative præstationer i forhold til resten af klassen:

Trin # 1: Find middelværdien.

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

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

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

En hurtig note : oftere end ikke, skal du muligvis afrunde formeloutput. For at gøre det skal du blot indpakke det i RUND-funktionen som følger:

Trin # 2: Find standardafvigelsen.

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

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

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

Dybest set 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 værdierne på x-aksen blive brugt til at illustrere en bestemt eksamensscore, mens y-aksens værdier vil fortælle os sandsynligheden for, at en studerende får den score på eksamen.

Teknisk set kan du medtage så mange intervaller, som du vil – du kan nemt slette de overflødige data senere ved at ændre den vandrette akseskala. Bare sørg for at vælge et interval, der indeholder de tre standardafvigelser.

Lad os starte en optælling på én (da der ikke er nogen måde, at en studerende kan få en negativ eksamensscore) og gå helt op til 150 — det betyder ikke rigtig noget, om det er 150 eller 1500 — for at oprette en anden hjælpetabel.

1. Vælg en tom celle under diagramdataene (f.eks. E4) og skriv “1” den værdi, der definerer det første interval.
2. Naviger til fanen Hjem.
3. I gruppen Redigering skal du vælge “Udfyld”.
4. Under “Serie i, ”Vælg” Kolonne. “
5. For” Trinværdi “skal du skrive” 1. ” Denne værdi bestemmer de trin, der automatisk tilføjes, indtil Excel når det sidste interval.
6. For “Stop-værdi” skal du skrive “150”, den værdi, der står for det sidste interval, og klikke på “OK. ”

Mirakuløst set 149 celler i kolonne E (E5: E153) er udfyldt 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 studerende 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: NORM.DIST-funktionen.

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

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

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

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

Trin # 5: Opret et spredningsdiagram med glatte linjer.

Endelig er tiden til at oprette klokkekurven kommet:

1. Vælg en hvilken som helst værdi i hjælpetabellen, der indeholder x- og y-akseværdierne (E4: F153).
2. Gå til fanen Indsæt.
3. Klik på knappen “Indsæt spredning (X, Y) eller boblediagram”.
4. Vælg “Spred med glatte linjer.”

Trin # 6: Opret etikettabellen.

Teknisk set er du har 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 etiketter, der illustrerer alle standardafvigelsesværdierne under og over gennemsnittet ( du kan også bruge dem til at vise z-scores i stedet).

Til det skal du oprette endnu en hjælpetabel som følger:

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

Beregn derefter standardafvigelsesværdierne under gennemsnittet ved at indtaste denne enkle formel i celle I4:

Simpelthen fratrækker formlen 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 gennemsnittet ved hjælp af spejlformlen:

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

Til sidst skal du udfylde y-aksens etiketværdier (J2: J8) med nuller, som du vil have datamarkører placeret på den vandrette akse.

Trin # 7: Indsæt etiketdataene i diagrammet.

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

I dialogboksen, der vises, skal du vælge ” Tilføj. ”

Fremhæv de respektive celler varierer fra hjælperetabellen — I2: I8 for” Serie X-værdier ”og J2 : J8 for “Serie Y-værdier” – og klik på “OK.”

Trin # 8: Skift diagramtypen for etiketserien.

Vores næste trin er at ændre diagrammetypen for den nytilføjede serie for at få datamarkørerne til at vises som prikker. For at gøre det skal du højreklikke på diagrammet og vælge “Skift Diagramtype. ”

Dernæst design et kombinationsdiagram:

1. Naviger til Kombinationsfane.
2. For serie “Series2” skal du ændre “Chart Type” til “Scatter.”
   • Bemærk: Sørg for, at “Series1” forbliver som “Scatter med glatte linjer.” Nogle gange ændrer Excel det, når du opretter en kombinationsboks. Sørg også for, at “Series1” ikke skubbes til den sekundære akse – afkrydsningsfeltet ud for diagrammet skal ikke markeres.
3. Klik på “OK.”

Trin # 9: Rediger den vandrette akseskala.

Centrer kortet på klokkekurven ved at justere den vandrette akseskala. Højreklik på den vandrette akse og vælg “Formater akse” fra menuen.

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

• Gå til fanen Axis Options.
• Indstil minimumsgrænseværdien til “15.”
• Indstil den maksimale grænseværdi til “125.”

Du kan tilpasse akseskalaområdet, uanset hvad du finder passende, men da du kender standardafvigelsesområdet, skal du indstille grænseværdierne en smule væk fra hver af dine tredje standardafvigelser for at vise kurvens “hale” .

Trin # 10: Indsæt og placer de brugerdefinerede dataetiketter.

Når du polerer dit diagram, skal du sørge for for at tilføje de brugerdefinerede dataetiketter. Højreklik først på et punkt, der repræsenterer serie “Series2”, og vælg “Tilføj dataetiketter.”

Næste, udskift standardetiketterne med dem, du tidligere har konfigureret, og placer dem over datamarkørerne.

1. Højreklik på en hvilken som helst serie “Series2” -datamærkat.
2. Vælg “Format Datamærkater. ”
3. I opgaveruden skal du skifte til fanen Etiketindstillinger.
4. Marker afkrydsningsfeltet” X-værdi “.
5. Fjern markeringen af” Y-værdi ” ”-Feltet.
6. Under” Etiketposition “skal du vælge” Over “.

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

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

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

1. Højreklik på en hvilken som helst serie “Series2” -datamærkat.
2. Klik knappen “Fyld”.
3. Vælg din farve fra den palet, der vises.

Også r flyt grænserne omkring prikkerne:

1. Højreklik på den samme datamarkør igen, og vælg “Outline”.
2. Vælg “No Outline.”

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

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

• Vælg diagrammet (på den måde indsættes linjerne direkte i diagrammet).
• Gå til fanen Indsæt.
• Klik på knappen “Figurer”.
• Vælg “Linje”.

Hold “SKIFT” -tasten nede, mens træk med musen for at tegne perfekt lodrette linjer fra hver prik til hvor hver linje møder klokkekurven.

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

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