denna handledning visar hur man skapar en normal distributionsklockkurva i alla versioner av Excel: 2007, 2010, 2013, 2016 och 2019.

i statistik är en klockkurva (även känd som en standard normalfördelning eller Gaussisk kurva) ett symmetriskt diagram som illustrerar dataens tendens att klustera runt ett mittvärde eller medelvärde i en given dataset.

y-axeln representerar den relativa sannolikheten för ett givet värde som förekommer i datauppsättningen medan x-axeln plottar värdena själva på diagrammet för att skapa en klockformad kurva, därav namnet.

grafen hjälper oss att analysera om ett visst värde är en del av den förväntade variationen eller är statistiskt signifikant och måste därför undersökas närmare.

eftersom Excel inte har några inbyggda lösningar att erbjuda, måste du plotta det själv. Därför utvecklade vi Chart Creator-tillägget, ett verktyg som låter dig bygga avancerade Excel-diagram med bara några klick.

i denna steg-för-steg-handledning lär du dig hur du skapar en normal distributionsklockkurva i Excel från grunden:

för att plotta en Gaussisk kurva måste du veta två saker:

• medelvärdet (även känt som standardmätningen). Detta bestämmer mitten av kurvan—som i sin tur karakteriserar kurvens position.
• standardavvikelsen (SD) för mätningarna. Detta definierar spridningen av dina data i normalfördelningen—eller på vanlig engelska, hur bred kurvan ska vara. Till exempel, i klockkurvan som visas ovan, representerar en standardavvikelse för medelvärdet intervallet mellan provresultat på 53 och 85.

ju lägre SD, desto högre kurva och desto mindre kommer dina data att spridas och vice versa.

det är värt att nämna 68-95-99.7-regeln som kan tillämpas på någon normalfördelningskurva, vilket innebär att ungefär 68% av dina data kommer att placeras inom en SD bort från medelvärdet, 95% inom två SD och 99.7% inom tre SD.

nu när du vet det väsentliga, låt oss gå från teori till praktik.

komma igång

för illustrationsändamål, låt oss anta att du har testresultaten på 200 studenter och vill betygsätta dem ”på en kurva”, vilket betyder att elevernas betyg kommer att baseras på deras relativa prestanda till resten av klassen:

Steg # 1: Hitta medelvärdet.

vanligtvis får du medelvärdet och SD-värdena från början, men om så inte är fallet kan du enkelt beräkna dessa värden i bara några enkla steg. Låt oss ta itu med medelvärdet först.

eftersom medelvärdet anger medelvärdet för ett prov eller en population av data, kan du hitta din standardmätning med hjälp av MEDELFUNKTIONEN.

Skriv följande formel i en tom cell (F1 i det här exemplet) bredvid dina faktiska data (kolumnerna A och B) för att beräkna genomsnittet av tentamen i datauppsättningen:

en snabb anteckning: oftare kan du behöva runda upp formelutmatningen. För att göra det, helt enkelt linda in den i den runda funktionen enligt följande:

steg #2: Hitta standardavvikelsen.

en ner, en att gå. Lyckligtvis har Excel en speciell funktion för att göra allt det smutsiga arbetet med att hitta standardavvikelsen för dig:

återigen väljer formeln alla värden från det angivna cellområdet (B2:B201) och beräknar dess standardavvikelse—glöm inte att runda upp utgången också.

steg #3: Ställ in x-axelvärdena för kurvan.

i grund och botten utgör diagrammet ett stort antal intervaller (tänk på dem som steg) sammanfogade med en linje för att skapa en jämn kurva.

i vårt fall kommer X-axelvärdena att användas för att illustrera en viss tentamen medan y-axelvärdena kommer att berätta sannolikheten för att en student får poängen på tentamen.

Tekniskt kan du inkludera så många intervaller som du vill—du kan enkelt radera de överflödiga data senare genom att ändra den horisontella axelskalan. Se bara till att du väljer ett intervall som innehåller de tre standardavvikelserna.

låt oss börja räkna på en (eftersom det inte finns något sätt att en student kan få en negativ tentamen) och gå hela vägen upp till 150—det spelar ingen roll om det är 150 eller 1500—för att ställa in ett annat hjälparbord.

1. välj en tom cell under diagramdata (t.ex. E4) och skriv ”1”, det värde som definierar det första intervallet.
2. navigera till fliken Hem.
3. i redigeringsgruppen väljer du ”Fyll.”
4. under” serie i, ”Välj” kolumn.”
5. för ”stegvärde,” typ ”1.”Detta värde bestämmer de steg som automatiskt läggs till tills Excel når det sista intervallet.
6. för ”stoppvärde” skriver du ”150”, värdet som står för det sista intervallet och klickar på ”OK.”

mirakulöst har 149 celler i kolumn E (E5:E153) fyllts med värdena från 2 till 150.

notera: Dölj inte de ursprungliga datacellerna som visas på skärmdumparna. Annars fungerar inte tekniken.

steg # 4: Beräkna normalfördelningsvärdena för varje x-axelvärde.

hitta nu normalfördelningsvärdena-sannolikheten för att en student får en viss tentamen som representeras av ett visst x-axelvärde—för var och en av intervallen. Lyckligtvis för dig har Excel arbetshästen att göra alla dessa beräkningar åt dig: normen.DIST funktion.

Skriv följande formel i cellen till höger (F4) i ditt första intervall (E4):

här är den avkodade versionen som hjälper dig att justera därefter:

du låser medelvärdet och SD-värdena så att du enkelt kan utföra formeln för de återstående intervallen (E5:E153).

dubbelklicka nu på fyllningshandtaget för att kopiera formeln till resten av cellerna (F5:F153).

Steg # 5: skapa en scatter plot med släta linjer.

Slutligen har tiden att bygga klockkurvan kommit:

1. Välj valfritt värde i hjälptabellen som innehåller X – och y-axelvärdena (E4:F153).
2. gå till fliken Infoga.
3. klicka på knappen ”Infoga Scatter (X, Y) eller Bubble Chart”.
4. Välj ”Scatter med släta linjer.”

steg #6: Ställ in etiketttabellen.

tekniskt sett har du din klockkurva. Men det skulle vara svårt att läsa eftersom det saknar data som beskriver det.

Låt oss göra normalfördelningen mer informativ genom att lägga till etiketterna som illustrerar alla standardavvikelsevärden under och över medelvärdet (du kan också använda dem för att visa z-poängen istället).

för det, sätt upp ännu ett hjälparbord enligt följande:

kopiera först medelvärdet (F1) bredvid motsvarande cell i kolumn X-värde (i5).

beräkna sedan standardavvikelsevärdena under medelvärdet genom att ange denna enkla formel i cell I4:

enkelt uttryckt subtraherar formeln Summan av föregående standardavvikelsevärden från medelvärdet. Dra nu fyllningshandtaget uppåt för att kopiera formeln till de återstående två cellerna (I2:I3).

upprepa samma process för standardavvikelserna ovanför medelvärdet med hjälp av spegelformeln:

på samma sätt utför du formeln för de andra två cellerna (i7:I8).

slutligen fyller du Y-axelns etikettvärden (J2:J8) med nollor som du vill att datamarkörerna placeras på den horisontella axeln.

Steg # 7: Sätt in etikettdata i diagrammet.

lägg nu till alla data du har förberett. Högerklicka på diagrammet och välj ”Välj Data.”

i dialogrutan som dyker upp väljer du ” Lägg till.”

markera respektive cellintervall från hjälptabellen—I2:i8 för ”serie X—värden” och J2:J8 för ”serie Y-värden” – och klicka på ”OK.”

steg #8: ändra diagramtypen för etikettserien.

vårt nästa steg är att ändra diagramtypen för den nyligen tillagda serien så att datamarkörerna visas som prickar. För att göra det, högerklicka på diagrammet och välj ”Ändra diagramtyp.”

nästa, designa ett kombinationsschema:

1. navigera till fliken Combo.
2. för serien ”Serie2”, ändra ”diagramtyp” till ”Scatter.”
   • notera: Se till att ” Serie1 ”förblir som” Scatter med släta linjer.”Ibland kommer Excel att ändra det när du gör en kombinationsruta, se också till att” Serie1 ” inte trycks till sekundäraxeln—kryssrutan bredvid diagramtypen ska inte markeras.
3. klicka på ” OK.”

steg # 9: ändra den horisontella axelskalan.

Centrera diagrammet på klockkurvan genom att justera den horisontella axelskalan. Högerklicka på den horisontella axeln och välj ”Formatera axel” från menyn.

när åtgärdsfönstret visas gör du följande:

• gå till fliken Axelalternativ.
• Ställ in minsta Gränsvärde till ”15.”
• Ställ in det maximala gränsvärdet till ” 125.”

du kan justera axelskalans intervall men du passar, men eftersom du känner till standardavvikelseområdena ställer du in gränsvärdena lite bort från var och en av dina tredje standardavvikelser för att visa kurvans ”svans”.

steg #10: Infoga och placera de anpassade dataetiketterna.

när du polerar upp ditt diagram, se till att lägga till anpassade dataetiketter. Högerklicka först på någon punkt som representerar serien ” Serie2 ”och välj” Lägg till dataetiketter.”

byt sedan ut standardetiketterna med de som du tidigare ställt in och placera dem ovanför datamarkörerna.

1. högerklicka på någon serie ”Serie2” dataetikett.
2. Välj ”Formatera Dataetiketter.”
3. i åtgärdsfönstret växlar du till fliken Etikettalternativ.
4. markera rutan ”X-värde”.
5. avmarkera rutan ”Y-värde”.
6. under ”Etikettposition,” välj ”ovan.”

Dessutom kan du nu ta bort stödlinjer (högerklicka på dem > ta bort).

steg #11: ändra färg på datamarkörerna (valfritt).

slutligen, ändra färg på prickarna för att hjälpa dem att passa in i din diagramstil.

1. högerklicka på någon serie ”Serie2” dataetikett.
2. klicka på ”Fyll” – knappen.
3. Välj din färg från paletten som visas.

ta också bort gränserna runt prickarna:

1. högerklicka på samma datamarkör igen och välj ”kontur.”
2. Välj ” Ingen Kontur.”

steg #12: Lägg till vertikala linjer (valfritt).

som en slutlig justering kan du lägga till vertikala linjer i diagrammet för att betona SD-värdena.

• Välj diagrammet (på så sätt kommer linjerna att infogas direkt i diagrammet).
• gå till fliken Infoga.
• klicka på knappen ”former”.
• Välj ”Linje.”

håll ner” SHIFT ” – tangenten medan du drar musen för att rita perfekt vertikala linjer från varje punkt till var varje linje möter klockkurvan.

ändra diagrammets titel och din förbättrade klockkurva är klar—visar dina värdefulla distributionsdata.

och så gör du det. Du kan nu välja vilken dataset som helst och skapa en normal distributionsklockkurva enligt dessa enkla steg!