O curbă de clopot, cunoscută și sub numele de distribuție normală, este cel mai comun tip de distribuție pentru o variabilă. Este acceptat ca o distribuție care apare în mod natural în multe situații. Într-o curbă de clopot, cel mai înalt punct al arcului definește media, care este cel mai mare număr de apariții ale unui element. Probabilitatea de apariție scade către ambele părți ale curbei. Puteți utiliza o curbă de clopot pentru a compara performanțele excludând extremele sau puteți defini așteptările dvs. prin posibilitatea ca un rezultat să se afle într-un interval la stânga sau la dreapta centrului. În acest ghid, vă vom arăta cum să creați o curbă de clopot în Excel, cu un scenariu de utilizare a cazului real, ca exemplu.
Descărcați registrul de lucru
Noțiuni de bază
Tot ce aveți nevoie este media (medie) și valorile deviației standard ale setului de date. Ambele valori pot fi calculate în Excel folosind formulele de mai jos.
Având în vedere aceste două valori, valorile distribuite în mod normal urmează aceste reguli:
- Suprafața totală sub curba este egală cu 1 (100%)
- Centrul curbei clopotului este media punctului de date
- (1-σ) Aproximativ 68,2% din aria de sub curbă se încadrează într-o abatere standard (medie ± abatere standard)
- (2-σ) Aproximativ 95,5% din aria de sub curbă se încadrează în două abateri standard (medie ± 2 * abatere standard)
- (3-σ) Aproximativ 99,7% din aria de sub curbă se încadrează în trei deviații standard (medie ± 3 * deviație standard)
Imagine de la Universitatea din Virginia
Crearea unei curbe de clopot în Excel
Să luăm un exemplu comun și să spunem că analizăm rezultatele examenului Este pentru o clasă de studenți. Vom folosi o curbă de clopot pentru a măsura rezultatele examenului pentru o mai bună comparație.
Începem prin calcularea valorilor pentru a genera o distribuție normală de date care va genera curba noastră. Trebuie să calculăm:
- Media (medie) a valorilor.
- Abaterea standard a valorilor.
- 3-limite abaterii standard pentru înainte și după medie.
- Valoarea intervalului pentru punctele de date distribuite în mod normal. Acest lucru necesită, de asemenea, o determinare a punctelor de interval. Puteți selecta orice număr, dar rețineți că mai multe intervale înseamnă mai multă precizie.
Valori
Începeți prin calcularea mediei și a deviației standard a datelor. Puteți utiliza funcțiile AVEREAGE și STDEV.P pentru a calcula valorile medii și respectiv a deviației standard.
Pasul următor este calcularea cele 3 valori ale deviației standard pentru a seta valorile minime și maxime pentru 99,7% din date.
Maxim = 83,23 – 3 * 5,54 = 99,86
După setarea valorilor minime și maxime pentru curba noastră, trebuie să generăm intervalele . Valorile intervalului vor fi baza pentru valorile distribuite în mod normal. Pentru a calcula intervalele, tot ce trebuie să faceți este să împărțiți aria între valorile minime și maxime la numărul de intervale. În acest exemplu, l-am setat la 20, dar puteți utiliza un număr mai mare pentru a crește numărul de puncte de date.
Odată calculată valoarea intervalului, puteți genera punctele de date. Pentru a face acest lucru, introduceți valoarea minimă într-o celulă. Apoi, chiar sub valoarea minimă, introduceți formula pentru a adăuga valoarea intervalului la minim. Aici, am folosit referințe de celule (cum ar fi J4), care ajută la popularea cu ușurință a punctelor de date până la valoarea maximă.
Următorul pas este calcularea valorilor distribuite în mod normal din punctele de date generate. Puteți utiliza funcția Excel NORM.DIST pentru a genera aceste valori.
Utilizați punctele de date populate ca prim argument al funcției. Valoarea medie și deviația standard sunt următoarele argumente. Finalizați formula cu o valoare booleană FALSĂ pentru a utiliza tipul necumulativ al acestei funcții.
Grafic
Aproape am terminat! Selectați punctele de date și valorile normale de distribuție, apoi introduceți o diagramă X-Y Scatter. Utilizați versiunea Scattered with Smooth Lines pentru a crea o curbă de clopot în Excel.
Diagrama poate părea mai puțin dezactivată mai întâi.Să vedem cum o puteți face să arate mai bine.
Pentru a schimba titlul diagramei, faceți dublu clic pe titlu și actualizați numele.
Apoi, faceți dublu clic pe Axa X și definiți valorile minime și maxime din panoul Opțiuni axă pentru a elimina spațiul alb de ambele părți. Acest lucru va oferi graficului dvs. o formă mai bună de clopot. Stabilim valori care sunt puțin în afara setului nostru de date. De exemplu, 66 – 100 pentru valorile 66,30 – 99,86.
Vă puteți îmbunătăți graficul adăugând valorile deviației standard .
