Hur man skapar en normal distributionsklocka i Excel

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

I statistiken är en klockkurva (även känd som en normal normalfördelning eller Gaussisk kurva) en symmetrisk graf som illustrerar datatendens att klustra runt ett centrumvärde eller medelvärde i en given datamängd.

Y-axeln representerar den relativa sannolikheten för att ett visst värde förekommer i datamängden medan x-axeln plottar själva värdena på diagrammet för att skapa en klockformad kurva, därav namnet.

Diagrammet hjälper oss att analysera om ett visst värde är en del av den förväntade variationen eller om det ä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. Det är därför vi utvecklade tillägget Chart Creator, 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 distributionsklocka i Excel från grunden:

För att rita en gaussisk kurva måste du veta två saker:

• Den elaka (även känd som standardmätning). Detta bestämmer kurvens centrum-vilket i sin tur karaktäriserar kurvens position.
• Standardavvikelsen (SD) av 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 tentamen på 53 och 85.

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

Det är värt att nämna 68-95-99.7-regeln som kan tillämpas på alla normala fördelningskurvor, vilket innebär att ungefär 68% av dina data kommer att placeras inom ett SD 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

Låt oss anta att du har 200 poäng och vill betygsätta dem "på en kurva", vilket betyder att elevernas betyg kommer att baseras på deras relativa prestation gentemot resten av klassen:

Steg 1: Hitta medelvärdet.

Normalt ges 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 indikerar medelvärdet för ett urval eller en population av data kan du hitta din standardmätning med hjälp av AVERAGE -funktionen.

Skriv följande formel i en tom cell (F1 i det här exemplet) bredvid din faktiska data (kolumner A och B) för att beräkna genomsnittet av tentamen i datamängden:

1	= MEDEL (B2: B201)

En snabb notering: oftare än inte kan du behöva avrunda formelutmatningen. För att göra det, linda den in i ROUND -funktionen enligt följande:

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

Steg 2: Hitta standardavvikelsen.

En ner, en att gå. Lyckligtvis har Excel en speciell funktion för att göra allt smutsigt arbete med att hitta standardavvikelsen för dig:

1	= STDEV.P (B2: B201)

Återigen väljer formeln alla värden från det angivna cellintervallet (B2: B201) och beräknar sin standardavvikelse-glöm bara inte att avrunda utgången också.

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

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

I grund och botten utgör diagrammet ett massivt 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 ett visst provresultat medan y-axelvärdena kommer att berätta sannolikheten för att en elev får det poänget på tentamen.

Tekniskt sett kan du inkludera så många intervall som du vill-du kan enkelt radera ö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 med en (eftersom det inte finns något sätt att en elev kan få ett negativt betyg) och gå hela vägen upp till 150-det spelar egentligen ingen roll om det är 150 eller 1500-för att ställa upp ett annat hjälpbord.

1. Välj en tom cell under diagramdata (t.ex. E4) och typ “1,” värdet som definierar det första intervallet.
2. Navigera till Hem flik.
3. I Redigerar grupp, välj "Fylla.”
4. Under "Serien i," Välj "Kolumn.”
5. För "Stegvärde,”Typ “1.” Detta värde bestämmer de steg som ska läggas till automatiskt tills Excel når det sista intervallet.
6. För "Stoppvärde,"Typ"150,” värdet som står för det sista intervallet och klicka på "OK.”

Mirakulöst nog, 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 elev får ett visst betyg som representeras av ett visst x-axelvärde-för vart och ett av intervallerna. Lyckligtvis för dig har Excel arbetshästen för att göra alla dessa beräkningar åt dig: funktionen NORM.DIST.

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

1	= NORM.DIST (E4, $ F $ 1, $ F $ 2, FALSE)

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

1	= NORM.DIST ([det första intervallet], [medelvärdet (absolut referens)], [standardavvikelsen (absolut referens), FALSKT)

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

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

Steg #5: Skapa en spridningsdiagram med släta linjer.

Slutligen är det dags att bygga klockkurvan:

1. Välj ett värde i hjälptabellen som innehåller x- och y-axelvärdena (E4: F153).
2. Gå till Föra in flik.
3. Klicka på "Sätt i Scatter (X, Y) eller Bubble Chart" knapp.
4. Välj "Sprida med släta linjer. ”

Steg #6: Sätt upp etikettbordet.

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 visar alla standardavvikelsens värden under och över medelvärdet (du kan också använda dem för att visa z-poängen istället).

För det, ställ in ännu ett hjälpbord så här:

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

Beräkna sedan standardavvikelsens värden under medelvärdet genom att ange denna enkla formel cell I4:

1	= I5- $ F $ 2

Enkelt uttryckt drar formeln summan av de föregående standardavvikelsen 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:

1	= I5+$ F $ 2

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

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

Steg #7: Infoga etikettdata i diagrammet.

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

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

Markera respektive cellintervall från hjälpbordet-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 för att få datamarkörerna att visas som prickar. För att göra det, högerklicka på diagramdiagrammet och välj "Ändra diagramtyp.”

Designa sedan ett kombinationsschema:

1. Navigera till Kombination flik.
2. För Serien "Series2" förändra "Diagramtyp”Till”Sprida ut.”
   • Obs: Se till att "Serie 1"Förblir som"Sprida med släta linjer. ” Ibland ändras Excel när du gör en Kombination Se också till ”Serie 1”Trycks inte till den sekundära axeln-kryssrutan bredvid diagramtypen bör inte markeras.
3. Klick "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 "Format Axis”Från menyn.

När uppgiftsrutan visas gör du följande:

• Gå till Axialternativ flik.
• Ställ in Minsta gränser värde till ”15.”
• Ställ in Maximala gränser värde till ”125.”

Du kan justera axelskalningsintervallet som du tycker är lämpligt, men eftersom du känner till standardavvikelsintervallen ställer du in gränsvärdena lite från var och en av dina tredje standardavvikelser för att visa kurvens "svans".

Steg #10: Infoga och placera de anpassade datatiketterna.

När du polerar upp ditt diagram, se till att lägga till anpassade datatiketter. Högerklicka först på valfri prick Serie "Serie 2" och välj "Lägg till datatiketter.”

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

1. Högerklicka på valfri Serie "Serie 2" datatikett.
2. Välj "Formatera datatiketter.”
3. I aktivitetsfönstret växlar du till Etikettalternativ flik.
4. Kolla "X -värde" låda.
5. Avmarkera "Y -värde" låda.
6. Under "Etikettposition,"Välj"Ovan.”

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

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

Slutligen färga om prickarna så att de passar in i din diagramstil.

1. Högerklicka på valfri Serie "Serie 2" datatikett.
2. Klicka på "Fylla" knapp.
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 "Översikt.”
2. Välj "Ingen kontur.”

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

Som en sista justering kan du lägga till vertikala linjer i diagrammet för att understryka SD -värdena.

• Välj diagramdiagrammet (på så sätt infogas raderna direkt i diagrammet).
• Gå till Föra in flik.
• Klicka på "Former" knapp.
• Välj "Linje.”

Håll ned "FLYTTA" medan du drar musen för att dra perfekta vertikala linjer från varje prick till där varje linje möter klockkurvan.

Ändra diagramtiteln så visar din förbättrade klockkurva dina värdefulla distributionsdata.

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