en klockkurva, även känd som normalfördelning, är den vanligaste typen av distribution för en variabel. Det accepteras som en distribution som förekommer naturligt i många situationer. I en klockkurva definierar bågens högsta punkt medelvärdet, vilket är det högsta antalet förekomster av ett element. Sannolikheten för händelser minskar mot vardera sidan av kurvan. Du kan använda en klockkurva för att jämföra prestanda genom att utesluta ytterligheter, eller definiera dina förväntningar genom möjligheten att ett resultat kommer att ligga inom ett intervall till vänster eller höger om mitten. I den här guiden kommer vi att visa dig hur du skapar en klockkurva i Excel med ett verkligt användningsfallsscenario som ett exempel.
ladda ner arbetsbok
grunderna
allt du behöver är medelvärdet (genomsnittet) och standardavvikelsevärdena för din dataset. Båda dessa mätvärden kan beräknas i Excel med hjälp av formlerna nedan.
med hänsyn till dessa två värden följer normalt fördelade värden dessa regler:
- den totala ytan under kurvan är lika med 1 (100%)
- mitten av klockkurvan är medelvärdet av datapunkten
- (1-kcal) ca 68,2% av området under kurvan faller inom en standardavvikelse (Genomsnittlig standardavvikelse för Kg)
- (2-95.5% av arean under kurvan faller inom två standardavvikelser (medelvärde 2 * 2)
- (3-99,7% av arean under kurvan faller inom tre standardavvikelser (Genomsnittlig 3 * standardavvikelse)
bild från University of Virginia
skapa en klockkurva i Excel
Låt oss ta ett vanligt exempel och säga att vi analyserar examensresultat för en klass studenter. Vi kommer att använda en klockkurva för att mäta examensresultat för bättre jämförelse.
vi börjar med att beräkna mätvärdena för att generera en normal distribuerad data som genererar vår kurva. Vi måste beräkna:
- medelvärde (medelvärde) av värdena.
- standardavvikelse för värdena.
- 3-standardavvikelse gränser för före och efter medelvärdet.
- intervallvärde för normalt distribuerade datapunkter. Detta kräver också en bestämning av intervallpunkterna. Du kan välja vilket nummer som helst, men kom ihåg det, fler intervaller betyder mer precision.
Metrics
börja med att beräkna medelvärdet och standardavvikelsen för data. Du kan använda AVEREAGE och STDEV.P fungerar för att beräkna medelvärdet respektive standardavvikelsens värden.
nästa steg är att beräkna de 3 standardavvikelsevärdena för att ställa in minsta och maximala värden för 99,7% av data.
maximal = 83.23 – 3 * 5.54 = 99.86
efter att ha ställt in minsta och maximala värden för vår kurva måste vi generera intervallen. Intervallvärdena kommer att vara basen för de normalt fördelade värdena. För att beräkna intervallen är allt du behöver göra att dela området mellan minsta och maximala värden med intervallräkning. I det här exemplet ställer vi in detta till 20, men du kan använda ett större antal för att öka antalet datapunkter.
när intervallvärdet har beräknats kan du generera datapunkterna. För att göra detta anger du minimivärdet i en cell. Sedan, precis under minimivärdet, ange formeln för att lägga till intervallvärdet till minimum. Här använde vi cellreferenser (som J4) som hjälper till att fylla datapunkterna enkelt upp till det maximala värdet.
nästa steg är att beräkna de normalt fördelade värdena från de genererade datapunkterna. Du kan använda Excels NORM.DIST funktion för att generera dessa värden.
använd de befolkade datapunkterna som det första argumentet för funktionen. Medelvärdet och standardavvikelsevärdena är nästa argument. Avsluta formeln med ett falskt booleskt värde för att använda icke-kumulativ typ av denna funktion.
Diagram
vi är nästan färdiga! Välj datapunkter och normalfördelningsvärden och sätt sedan in ett XY-Spridningsschema. Använd Scattered with Smooth Lines-versionen för att skapa en klockkurva i Excel.
diagrammet kan verka lite av först. Låt oss se hur du kan få det att se bättre ut.
för att ändra titeln på diagrammet, dubbelklicka på titeln och uppdatera namnet.
dubbelklicka sedan på X-axeln och definiera minimi-och maximivärden från axel alternativpanelen för att eliminera det vita utrymmet på båda sidor. Detta ger ditt diagram en bättre klockform. Vi sätter värden som ligger lite utanför vår datamängd. Till exempel 66 – 100 för värden 66.30 – 99.86.
du kan ytterligare förbättra ditt diagram genom att lägga till standardavvikelsevärden.
