Cum se creează o curbă clopot în Excel

o curbă clopot, cunoscută și sub numele de distribuție normală, este cel mai frecvent 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ă clopot, cel mai înalt punct al arcului definește media, care este cel mai mare număr de apariții ale unui element. Probabilitatea apariției scade spre ambele părți ale curbei. Puteți utiliza o curbă de clopot pentru a compara performanțele excluzând extremele sau pentru a vă defini așteptările 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 caz de utilizare din lumea reală ca exemplu.

descărcați Registrul de lucru

Noțiuni de bază

tot ce aveți nevoie este media (media) și valorile deviației standard ale setului dvs. de date. Ambele valori pot fi calculate în Excel folosind formulele de mai jos.

pentru a calcula media (medie) =medie (date)
pentru a calcula deviația standard =STDEV.P (date)

luând în considerare aceste două valori, valorile distribuite în mod normal respectă aceste reguli:

  1. suprafața totală de sub curbă este egală cu 1 (100%)
  2. centrul curbei clopotului este media punctului de date
  3. (1-hectar) aproximativ 68,2% din suprafața de sub curbă se încadrează într-o abatere standard (deviația standard medie de la hectar)
  4. (2-aproximativ 95.5% din suprafața de sub curbă se încadrează în două deviații standard (valoarea medie a deviației standard 2 * )
  5. (3-aproximativ 99,7% din suprafața de sub curbă se încadrează în trei deviații standard (valoarea medie a deviației standard de la 3 * )

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 examenelor pentru o clasă de studenți. Vom folosi o curbă clopot pentru a măsura rezultatele examenului pentru o comparație mai bună.

începem prin calcularea valorilor pentru a genera date distribuite normale care vor genera curba noastră. Trebuie să calculăm:

  • medie (medie) a valorilor.
  • deviația Standard a valorilor.
  • 3-limite de deviație standard pentru media înainte și după.
  • 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 acest lucru, mai multe intervale înseamnă mai multă precizie.
o notă rapidă înainte de a vă scufunda în formule. Am folosit intervale numite în loc de referințe de celule pentru a facilita citirea formulelor. Puteți găsi mai multe detalii despre intervalele numite aici: Excel intervale numite

valori

începeți prin calcularea mediei și deviației standard a datelor. Puteți utiliza AVEREAGE și STDEV.P funcționează pentru a calcula valorile medii și, respectiv, deviația standard.

curba clopotului în excel

următorul pas este calcularea celor 3 valori de abatere standard pentru a seta valorile minime și maxime pentru 99,7% din date.

cele 3 valori ale deviației standard pot depăși setul real de date. Acesta este un comportament normal care poate apărea frecvent cu seturi de date mai mici.

curba clopotului în excel

minim = 83.23 – 3 * 5.54 = 66.61

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 zona între valorile minime și maxime după numărul de intervale. În acest exemplu, setăm acest lucru la 20, dar puteți utiliza un număr mai mare pentru a crește numărul de puncte de date.

valoarea intervalului = (99.85 – 66.60) / 20 = 1.66

odată ce valoarea intervalului este calculată, puteți genera punctele de date. Pentru aceasta, 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ă popula punctele de date cu ușurință până la valoarea maximă.

următorul pas este de a calcula valorile distribuite în mod normal din punctele de date generate. Puteți utiliza norma Excel.Funcția DIST pentru a genera aceste valori.

Folosiți punctele de date populate ca prim argument al funcției. Valorile medii și deviația standard sunt următoarele argumente. Finalizați formula cu o valoare booleană falsă pentru a utiliza tipul non-Cumulativ al acestei funcții.

Grafic

suntem aproape gata! Selectați punctele de date și valorile normale de distribuție, apoi introduceți o diagramă de dispersie X-Y. Utilizați versiunea împrăștiată cu linii netede pentru a crea o curbă de clopot în Excel.

graficul poate părea un pic mai întâi. Să vedem cum poți face să arate mai bine.

pentru a schimba titlul graficului, 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 axe pentru a elimina spațiul alb de pe ambele părți. Acest lucru va oferi graficului dvs. o formă de clopot mai bună. Setăm valori care sunt puțin în afara setului nostru de date. De exemplu, 66 – 100 pentru valorile 66.30 – 99.86.

puteți îmbunătăți și mai mult graficul adăugând valorile deviației standard.

pentru mai multe informații despre diagramele scatter, consultați diagrama Scatter în Excel

curba bell în excel

Leave a Reply

Lasă un răspuns

Adresa ta de email nu va fi publicată.