Cum se creează o curbă de clopot de distribuție normală în Excel

Acest tutorial va arăta cum să creați o curbă de clopot de distribuție normală în toate versiunile de Excel: 2007, 2010, 2013 , 2016 și 2019.

În statistică, o curbă a clopotului (cunoscută și ca distribuție normală standard sau curbă gaussiană) este un grafic simetric care ilustrează tendința datelor la grup în jurul valorii centrale, sau a mediei, într-un set de date dat.

Axa y reprezintă probabilitatea relativă ca o anumită valoare să apară în setul de date, în timp ce axa x trasează valorile în sine pe diagramă pentru a crea o formă de clopot curba, de unde și numele.

Graficul ne ajută să analizăm dacă o anumită valoare face parte din variația așteptată sau este semnificativă statistic și, prin urmare, trebuie examinată mai atent.

Deoarece Excel nu are nicio soluție încorporată de oferit, va trebui să o complotați singur. De aceea, am dezvoltat programul de completare Chart Creator, un instrument care vă permite să creați diagrame Excel avansate în doar câteva clicuri.

În acest pas- tutorial pas cu pas, veți învăța cum să creați o curbă normală de clopot de distribuție în Excel de la capăt:

trasați o curbă gaussiană, trebuie să cunoașteți două lucruri:

• Media (cunoscută și sub denumirea de măsurare standard). Aceasta determină centrul curbei – care, la rândul său, caracterizează poziția curbei.
• Abaterea standard (SD) a măsurătorilor. Aceasta definește răspândirea datelor dvs. în distribuția normală – sau în engleză simplă, cât de largă ar trebui să fie curba. De exemplu, în curba clopotului prezentată mai sus, o abatere standard a mediei reprezintă intervalul dintre scorurile examenelor de 53 și 85.

Cu cât SD este mai mică, cu atât curba este mai înaltă și cu atât este mai mică datele dvs. vor fi distribuite și invers.

Merită menționată regula 68-95-99.7 care poate fi aplicată oricărei curbe normale de distribuție, ceea ce înseamnă că aproximativ 68% din datele dvs. vor fi plasate într-un SD distanță de media, 95% în două SD și 99,7% în trei SD.

Acum că cunoașteți elementele esențiale, să trecem de la teorie la practică.

Noțiuni introductive

În scop ilustrativ, să presupunem că aveți scorurile la 200 de elevi și doriți să le notați „pe o curbă ”, Adică notele elevilor se vor baza pe performanța lor relativă față de restul clasei:

Pasul 1: Găsiți media.

De obicei, vi se oferă media și valorile SD de la început, dar dacă nu este cazul, puteți calcula cu ușurință aceste valori în doar câțiva pași simpli. Să abordăm mai întâi media.

Deoarece media indică valoarea medie a unui eșantion sau a unei populații de date, puteți găsi măsurarea standard folosind funcția MEDIE.

Tastați următoarea formulă în orice celulă goală (F1 în acest exemplu) lângă datele dvs. reale (coloanele A și B) pentru a calcula media scorurilor examenului din setul de date:

O notă rapidă : cel mai adesea, poate fi necesar să rotunjiți rezultatul formulei. Pentru a face acest lucru, pur și simplu înfășurați-l în funcția ROUND după cum urmează:

Pasul 2: găsiți abaterea standard.

Una jos, unul de plecat. Din fericire, Excel are o funcție specială pentru a face toate lucrurile murdare de a găsi abaterea standard pentru dvs.:

Din nou, formula alege toate valorile din intervalul de celule specificat (B2: B201) și calculează abaterea standard – doar nu uitați să rotunjiți ieșirea, de asemenea.

Pasul 3: configurați valorile axei x pentru curbă.

Practic, graficul constituie un număr masiv de intervale ( gândiți-vă la ei ca la pași) uniți împreună cu o linie pentru a crea o curbă lină.

În cazul nostru, valorile axei x vor fi utilizate pentru a ilustra un anumit scor de examen, în timp ce valorile axei y vor spune ne probabilitatea ca un student să obțină acel scor la examen.

Din punct de vedere tehnic, puteți include oricâte intervale doriți – puteți șterge fără efort datele redundante ulterior modificând scala axei orizontale. Asigurați-vă că alegeți o gamă care va încorpora cele trei abateri standard.

Să începem să numărăm una (deoarece nu există nicio modalitate în care un student poate obține un scor negativ la examen) și să mergem până la 150 – nu contează cu adevărat dacă este 150 sau 1500 – pentru a configura o altă tabelă de ajutor.

1. Alegeți orice celulă goală sub datele graficului (cum ar fi E4) și tastați „1” valoarea care definește primul interval.
2. Navigați la fila Acasă.
3. În grupul Editare, alegeți „Umplere”.
4. Sub „Seria în, ”Selectați„ Coloană ”.
5. Pentru„ Valoare pas ”, tastați„ 1. ” Această valoare determină creșterile care vor fi adăugate automat până când Excel atinge ultimul interval.
6. Pentru „Valoare oprire”, tastați „150”, valoarea care reprezintă ultimul interval și faceți clic pe „OK”. ”

În mod miraculos, 149 de celule din coloana E (E5: E153) au fost completate cu valori cuprinse între 2 și 150.

NOTĂ: Nu ascundeți celulele de date originale așa cum se arată în capturile de ecran. În caz contrar, tehnica nu va funcționa.

Pasul # 4: Calculați valorile normale de distribuție pentru fiecare valoare axa x.

Acum, găsiți valorile normale de distribuție – probabilitatea ca un student să obțină un anumit scor de examen reprezentat de o anumită valoare a axei x —Pentru fiecare dintre intervale. Din fericire, Excel are calul de lucru pentru a face toate aceste calcule pentru dvs.: funcția NORM.DIST.

Tastați următoarea formulă în celula din dreapta (F4) a primul dvs. interval (E4):

Iată versiunea decodificată pentru a vă ajuta să vă ajustați în mod corespunzător:

Blocați media și valorile SD, astfel încât să puteți executa fără efort formula pentru intervalele rămase (E5: E153).

Acum, faceți dublu clic pe mânerul de umplere pentru a copia formula în restul celulelor (F5: F153).

Pasul 5: creați un grafic de împrăștiere cu linii netede.

În sfârșit, a sosit timpul pentru a construi curba clopotului:

1. Selectați orice valoare din tabelul de asistență care conține valorile axelor x și y (E4: F153).
2. Accesați fila Insert.
3. Faceți clic pe butonul „Insert Scatter (X, Y) sau Bubble Chart”.
4. Alegeți „Scatter with Smooth Lines”.

Pasul 6: configurați tabelul de etichete.

Tehnic, dvs. aveți curba clopotului. Dar ar fi greu de citit, deoarece nu au date care să o descrie.

Să facem distribuția normală mai informativă adăugând etichete care ilustrează toate valorile deviației standard sub și peste medie ( le puteți utiliza, de asemenea, pentru afișarea scorurilor z).

Pentru aceasta, configurați încă un tabel de ajutor, după cum urmează:

Mai întâi, copiați valoarea medie (F1) lângă celula corespunzătoare din coloana X-Value (I5).

Apoi, calculați valorile deviației standard sub medie prin introducerea acestei formule simple în celula I4:

Pur și simplu, formula scade suma valorilor deviației standard precedente din medie. Acum, trageți mânerul de umplere în sus pentru a copia formula în celelalte două celule (I2: I3).

Repetați același proces pentru abaterile standard peste medie folosind formula oglindă:

În același mod, executați formula pentru celelalte două celule (I7 : I8).

În cele din urmă, completați valorile etichetei axei y (J2: J8) cu zerouri, după cum doriți markere de date plasate pe axa orizontală.

Pasul 7: introduceți datele etichetei în diagramă.

Acum, adăugați toate datele pe care le-ați pregătit. Faceți clic dreapta pe diagrama graficului și alegeți „Selectare date”.

În caseta de dialog care apare, selectați ” Adăugați. ”

Evidențiați intervalele de celule respective din tabelul de asistență – I2: I8 pentru„ valorile Seriei X ”și J2 : J8 pentru „Valorile seriei Y” – și faceți clic pe „OK”.

Pasul 8: modificați tipul graficului de seria de etichete.

Următorul nostru pas este să schimbăm tipul de grafic al seriei nou adăugate pentru a face marcatorii de date să apară ca puncte. Pentru aceasta, faceți clic dreapta pe graficul graficului și selectați „Modificare Tipul diagramei. ”

Apoi, proiectați o diagramă combinată:

1. Navigați la Fila Combo.
2. Pentru seria „Series2”, schimbați „Tip diagramă” la „Scatter”.
   • Notă: asigurați-vă că „Series1” rămâne ca „Scatter with Smooth Lines”. Uneori, Excel îl va schimba atunci când creați o combinație. De asemenea, asigurați-vă că „Series1” nu este împins pe axa secundară – caseta de selectare de lângă tipul graficului nu trebuie marcată.
3. Faceți clic pe „OK”.

Pasul # 9: Modificați scala axei orizontale.

Centrați diagrama pe curba clopotului ajustând scala axei orizontale. Faceți clic dreapta pe axa orizontală și alegeți „Format Axis” din meniu.

Odată ce panoul de activități apare, faceți următoarele:

• Accesați fila Opțiuni axă.
• Setați valoarea Minim Bounds la „15.”
• Setați valoarea Maximum Bounds la „125”.

Puteți modifica gama scalei axelor oricum doriți, dar, din moment ce cunoașteți intervalele de deviație standard, setați valorile Bounds puțin departe de fiecare dintre cele trei a treia abateri standard pentru a arăta „coada” curbei .

Pasul 10: introduceți și poziționați etichetele de date personalizate.

Pe măsură ce vă lustruiți graficul, asigurați-vă că pentru a adăuga etichete de date personalizate. Mai întâi, faceți clic dreapta pe orice punct care reprezintă Seria „Seria2” și selectați „Adăugați etichete de date”.

Apoi, înlocuiți etichetele implicite cu cele pe care le-ați configurat anterior și plasați-le deasupra markerelor de date.

1. Faceți clic dreapta pe orice etichetă de date din seria „Serie2”.
2. Selectați „Format” Etichete de date. ”
3. În panoul de activități, comutați la fila Opțiuni etichetă.
4. Bifați caseta„ Valoare X ”.
5. Debifați„ Valoarea Y ” ”Casetă.
6. Sub„ Poziția etichetei ”, alegeți„ Mai sus ”.

De asemenea, puteți elimina acum liniile de grilă (faceți clic dreapta pe ele > Ștergeți).

Pasul 11: Recolorați marcatorii de date (opțional).

În cele din urmă, recolorați punctele pentru a le ajuta să se încadreze în stilul dvs. de diagramă.

1. Faceți clic dreapta pe orice etichetă de date din seria „Serie2”.
2. Faceți clic pe butonul „Completare”.
3. Alegeți culoarea din paleta care apare.

De asemenea, r mutați marginile din jurul punctelor:

1. Faceți din nou clic dreapta pe același marker de date și selectați „Schiță”.
2. Alegeți „Fără schiță”.

Pasul 12: Adăugați linii verticale (opțional).

Ca ajustare finală, puteți adăugați linii verticale în grafic pentru a ajuta la evidențierea valorilor SD.

• Selectați graficul graficului (în acest fel, liniile vor fi inserate direct în grafic).
• Accesați la fila Inserare.
• Faceți clic pe butonul „Forme”.
• Alegeți „Linie”.

Țineți apăsată tasta „SHIFT” în timp ce trăgând mouse-ul pentru a trasa linii perfect verticale de la fiecare punct la locul în care fiecare linie întâlnește curba clopotului.

Schimbați titlul graficului , iar curba clopotului îmbunătățită este gata – afișând datele dvs. de distribuție valoroase.

Și așa o faceți. Acum puteți alege orice set de date și puteți crea o curbă normală a soneriei de distribuție urmând acești pași simpli!