Hur man skapar en klockkurva i Excel (Steg-för-steg-guide)

En klockkurva (även känd som normal distributionskurva) är ett sätt att plotta och analysera data som ser ut som en klocka kurva.

I klockkurvan är den högsta punkten den som har högst sannolikhet att inträffa, och sannolikheten för förekomster går ner på vardera sidan om kurvan.

Det används ofta under anställdas prestationsbedömningar eller under utvärdering i tentor (någonsin hört – ”Du kommer att få betyg på kurvan?”).

Nu innan jag hoppar in på hur man skapar en klockkurva i Excel, låt oss få en bättre förståelse för konceptet genom att ta ett exempel.

Förstå Bell Curve

Antag att du arbetar i ett team med 100 medlemmar och din chef säger att din prestation kommer att bli i förhållande till andra och kommer att utvärderas utifrån klockan.

Det betyder att även om ditt lag är det bästa laget någonsin och ni alla är superhjältar, så skulle bara en handfull av er få högsta betyg, mest av personer i ditt team skulle få ett genomsnittligt betyg och en handfull får lägst betyg.

Bildkälla: EmpxTrack

Men varför behöver vi klockan?

Rättvis fråga!

Antag att du har en klass på 100 elever som visas för en tentamen. Enligt ditt betygssystem får alla som får över 80 av 100 betyg A. Men eftersom du satte upp ett riktigt enkelt papper fick alla poäng över 80 och fick A-betyget.

Nu är det inget fel i denna typ av betygssystem. Med hjälp av det kan du inte skilja mellan någon som fick 81 och någon som fick 95 (eftersom båda skulle få A-betyg).

För att hålla jämförelsen rättvis och hålla tävlingsandan vid liv, en klocka kurvan används ofta för att utvärdera prestationer (åtminstone så var det när jag var på college).

Med hjälp av klockkurvans tillvägagångssätt omvandlas elevernas betyg till percentiler som sedan jämförs med varandra.

Studenter som får högre poäng är på höger sida av kurvan och elever som får låga poäng är till vänster om kurvan (de flesta av eleverna ligger i mitten runt medelvärdet).

För att förstå klockkurvan måste du veta om två mätvärden:

• Medelvärde – medelvärdet för alla datapunkter
• Standardavvikelse – det visar hur mycket datauppsättningen avviker från medelvärdet för datasetet. Antag till exempel att du har en grupp på 50 personer och att du registrerar deras vikt (i kg). I den här datasetet är genomsnittsvikten 60 kg och standardavvikelsen 4 kg. Det betyder att 68% av folkets vikt ligger inom 1 standardavvikelse från medelvärdet – vilket skulle vara 56-64 kg. På samma sätt är 95% av människorna inom två standardavvikelser – vilket skulle vara 52-68 kg.

När du har en datauppsättning som normalt distribueras följer din klockkurva nedanstående regler :

• Mitten av klockkurvan är medelvärdet för datapunkten (även den högsta punkten i klockkurvan).
• 68,2% av de totala datapunkterna ligger i intervallet (medelvärde – standardavvikelse till medelvärde + standardavvikelse).
• 95,5% av de totala datapunkterna ligger i intervallet (medelvärde – 2 * standardavvikelse till medelvärde + 2 * standardavvikelse)
• 99,7% av de totala datapunkterna ligger i området (medelvärde – 3 * standardavvikelse till medelvärde + 3 * standardavvikelse)

Bildkälla: MIT News

Låt oss nu se hur man skapar en klockkurva i Excel.

Skapa en klockkurva i Excel

Låt oss ta ett exempel på en klass elever som fått poäng i en tentamen.

Medelpoängen för klassen är 65 och standarden avvikelse är 10. (Du kan beräkna medelvärdet med AVERAGE-funktionen i Excel och standardavvikelse med STDEV.P-funktionen).

Här är stegen för att skapa en klockkurva för denna dataset:

• Ange 35 i cell A1. Detta värde kan beräknas med medelvärde – 3 * standardavvikelse (65-3 * 10).
• I cellen nedan anger du 36 och skapar en serier från 35 till 95 (där 95 är medelvärde + 3 * standardavvikelse). Du kan göra detta snabbt genom att använda autofyllalternativet eller använda fyllningshandtaget och dra ner det för att fylla cellerna.
• I cellen intill 35 , ange formeln: = NORM.DIST (A1,65,10, FALSE)
  • Observera att här har jag hårdkodat värdet på medelvärdet och standardavvikelsen. Du kan också ha dessa i celler och använda cellreferenser i formeln.
• Återanvänd fyllningshandtaget för att snabbt kopiera och klistra in formeln för alla celler.
• Välj datamängden och gå till fliken Infoga.
• Sätt in Scatter med Smooth Lines-diagram.

Detta ger dig en klockkurva i Excel.

Nu kan du ändra diagramtiteln och justera axeln om du behöver.

Observera att när du har en låg standardavvikelse får du en packad tunn klockkurva, och när du har en hög standardavvikelse är klockkurvan bred och täcker mer område i diagrammet.

Denna typ av klockkurva kan användas för att identifiera var en datapunkt ligger i diagrammet. Om ett lag till exempel är fullt av högpresterande kan någon få ett genomsnittligt betyg när han / hon var mitt i kurvan, trots att han / hon var högpresterande.

Obs: I det här blogginlägget har jag diskuterat konceptet med en klockkurva och hur man skapar den i Excel. En statistiker skulle vara bättre lämpad för att prata om effekten av klockkurvan och begränsningarna i samband med den. Jag är mer en Excel-kille och mitt engagemang i Bell-kurvan har begränsats till de beräkningar jag gjorde när jag arbetade som finansanalytiker.

Hoppas du tyckte att den här handledningen var användbar!

Låt mig veta dina tankar i kommentarfältet.

Du kanske också gillar följande Excel-självstudier:

• Hur man gör ett histogram i Excel.
• Hur man beräknar sammansatt intresse i Excel + GRATIS kalkylator.
• Hur man skapar en värmekarta i Excel.
• Stegdiagram i Excel.
• Hur man skapar ett tidslinje- / milstolpediagram i Excel.
• Skapa ett Pareto-diagram i Excel.
• Skapa ett cirkeldiagram i Excel
• Avancerade Excel-diagram.