Så här skapar du en normal distributionsklocka i Excel

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

I statistik är en klockkurva (även känd som en normal normalfördelning eller Gaussisk kurva) en symmetrisk graf som illustrerar datans tendens att klustera runt ett mittvärde, eller medelvärde, i en given dataset.

Y-axeln representerar den relativa sannolikheten för att ett visst värde inträffar i datasetet medan x-axeln plottar själva värdena i diagrammet för att skapa en klockformad kurvan, 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 därför måste 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 tillägget Chart Creator, ett verktyg som låter dig bygga avancerade Excel-diagram med bara några få klick.

I detta steg- steg-för-steg-självstudie lär du dig hur du skapar en normal distributionsklocka i Excel från grunden:

Till plotta en Gaussisk kurva, du måste veta två saker:

• Medeltalet (även känt som standardmätningen). Detta bestämmer centrum för kurvan – vilket i sin tur karakteriserar kurvens position.
• Mätningens standardavvikelse (SD). 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 av medelvärdet intervallet mellan examenspoäng på 53 och 85.

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

Det är värt att nämna 68-95-99.7-regeln som kan tillämpas på vilken normal distributionskurva som helst, 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 känna till det väsentliga, låt oss gå från teori till praktik.

Komma igång

För att illustrera, låt oss anta att du har testresultaten på 200 elever och vill betygsätta dem ”på en kurva , ”Vilket innebär att elevernas betyg kommer att baseras på deras relativa prestationer i förhållande till resten av klassen:

Steg # 1: Hitta medelvärdet.

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

Eftersom medelvärdet anger medelvärdet för ett urval eller en datapopulation kan du hitta din standardmätning med AVERAGE-funktionen.

Skriv följande formel i valfri tom cell (F1 i det här exemplet) bredvid dina faktiska data (kolumner A och B) för att beräkna genomsnittet av examensresultaten i datasetet:

En snabb anteckning : oftare än inte kan du behöva runda upp formelutmatningen. För att göra det, lägg bara in det i RUND-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 åt dig:

Återigen plockar formeln alla värden från det angivna cellområdet (B2: B201) och beräknar dess standardavvikelse – glöm inte att runda upp utdata också.

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

I grund och botten utgör diagrammet ett stort antal intervall ( 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 examenspoäng medan y-axelvärdena visar oss sannolikheten för att en student får poängen på provet.

Tekniskt sett kan du inkludera så många intervall du vill – du kan enkelt radera de överflödiga uppgifterna 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å ett (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 – att ställa in en annan hjälptabell.

1. Välj vilken tom cell som helst under diagramdata (som E4) och skriv ”1” värdet som definierar det första intervallet.
2. Navigera till fliken Start.
3. I gruppen Redigering väljer du ”Fyll”.
4. Under ”Serie i, ”Välj” Kolumn. ”
5. För” Stegvärde ”, skriv” 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”, det värde som står för det sista intervallet och klickar på ”OK. ”

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

OBS: 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 examenspoäng representerad av ett visst x-axelvärde – för vart och ett av intervallen. Lyckligtvis för dig har Excel arbetshästen för att göra alla dessa beräkningar åt dig: NORM.FÖRDELNING.

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

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

Du låser medel- 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 ett spridningsdiagram med släta linjer.

Slutligen har tiden för 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 bubbeldiagram”.
4. Välj ”Scatter with Smooth Lines.”

Steg # 6: Ställ in etikettabellen.

Tekniskt sett är du ha 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ärdena under och över medelvärdet ( du kan också använda dem för att visa z-poängen istället).

För det, ställa in ännu en hjälptabell 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 över medelvärdet med hjälp av spegelformeln:

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

Fyll slutligen y-axelns etikettvärden (J2: J8) med nollor som du vill ha datamarkörer placerade på den horisontella axeln.

Steg 7: Sätt in etikettdata i diagrammet.

Lägg nu till all information du har förberett. Högerklicka på diagrammet och välj ”Välj data.”

I dialogrutan som dyker upp, välj ” 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 punkter. För att göra det högerklickar du på diagrammet och väljer ”Ändra Diagramtyp. ”

Därefter designar du ett kombinationsdiagram:

1. Navigera till Kombinationsflik.
2. För serie ”Series2” ändrar du ”Diagramtyp” till ”Scatter.”
   • Obs! Se till att ”Series1” förblir som ”Scatter med släta linjer.” Ibland ändrar Excel det när du skapar en kombinationsruta. Se också till att ”Series1” inte trycks till den sekundära axeln – kryssrutan bredvid diagramtypen bör inte markeras.
3. Klicka på ”OK.”

Steg 9: Ändra den horisontella axelskalan.

Centrera diagrammet på klockan genom att justera den horisontella axelskalan. Högerklicka på den horisontella axeln och välj ”Format Axis” från menyn.

När uppgiftsfönstret visas gör du följande:

• Gå till fliken Axelalternativ.
• Ställ in minimivärden på ”15.”
• Ställ in maximivärden på ”125”.

Du kan justera axelns skalningsområde, men du tycker det är lämpligt, men eftersom du känner till standardavvikelsesområdena, ställer du in gränsvärdena lite bort från var och en av dina tredje standardavvikelser för att visa kurvan ”svans” .

Steg # 10: Infoga och placera de anpassade datatiketterna.

När du polerar upp ditt diagram, var noga med för att lägga till anpassade datatiketter. Högerklicka först på en punkt som representerar serie ”Series2” och välj ”Lägg till dataetiketter.”

Nästa, ersätt standardetiketterna med de du tidigare har ställt in och placera dem ovanför datamarkörerna.

1. Högerklicka på valfri serie ”Series2” -datamärke.
2. Välj ”Format Datatiketter. ”
3. I aktivitetsfönstret växlar du till fliken Etikettalternativ.
4. Markera rutan” X-värde ”.
5. Avmarkera” Y-värde ” Rutan.
6. Under ”Etikettposition” väljer du ”Ovan.”

Du kan också ta bort rutnätet (högerklicka på dem > Radera).

Steg # 11: Färgdatamarkörerna (valfritt).

Slutligen färgar du prickarna så att de passar in i din diagramstil.

1. Högerklicka på valfri serie ”Series2” -datamärke.
2. Klicka knappen ”Fyll”.
3. Välj din färg från paletten som visas.

Också r flytta ut kanterna runt punkterna:

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ägg till vertikala linjer i diagrammet för att understryka SD-värdena.

• Välj diagramdiagrammet (på det sättet infogas raderna direkt i diagrammet).
• Gå till fliken Infoga.
• Klicka på knappen ”Former”.
• Välj ”Rad”.

Håll ned ”SKIFT” -tangenten medan dra musen för att rita perfekt vertikala linjer från varje punkt till där varje rad möter klockan.

Ändra diagrammets titel och din förbättrade klockkurva är redo – visar din värdefulla distributionsdata.

Och så gör du det. Du kan nu välja vilken datauppsättning som helst och skapa en normal distributionsklocka genom att följa dessa enkla steg!