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

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

În statistici, o curbă de clopot (cunoscută și sub denumirea de distribuție normală standard sau curbă gaussiană) este un grafic simetric care ilustrează tendința datelor de a se grupa î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 curbă în formă de clopot, 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 avansate Excel în doar câteva clicuri.

În acest 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:

Pentru a trasa o curbă gaussiană, trebuie să știț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 limba 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 scăzut, cu atât curba este mai înaltă și cu atât datele dvs. vor fi mai puțin răspândite și invers.

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

Acum, că știi esențialul, să trecem de la teorie la practică.

Noțiuni de bază

Î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 utilizând 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:

1	= MEDIE (B2: B201)

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ă:

1	= RUNDĂ (MEDIE (B2: B201), 0)

Pasul 2: găsiți abaterea standard.

Un jos, unul de plecat. Din fericire, Excel are o funcție specială pentru a face toată munca murdară de a găsi abaterea standard pentru dvs.:

1	= STDEV.P (B2: B201)

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

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

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

Practic, graficul constituie un număr masiv de intervale (gândiți-vă la ele ca la pași) unite î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 ne vor spune 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 la unul (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 înființa o altă masă 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 Acasă filă.
3. În Editarea grup, alegeți „Completati.”
4. Sub "Seria în," Selectați "Coloană.”
5. Pentru "Valoarea pasului," tip “1.” Această valoare determină creșterile care vor fi adăugate automat până când Excel atinge ultimul interval.
6. Pentru "Opriți valoarea," tip "150,” valoarea care reprezintă ultimul interval și faceți clic pe „BINE.”

În mod miraculos, 149 de celule din coloana E (E5: E153) au fost completate cu valori de la 2 la 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 axa x - pentru fiecare dintre intervale. Din fericire pentru dvs., 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) din primul dvs. interval (E4):

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

Iată versiunea decodificată pentru a vă ajuta să vă adaptați corespunzător:

1	= NORM.DIST ([primul interval], [media (referință absolută)], [deviația standard (referință absolută), FALS)

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 scatter cu linii netede.

În cele din urmă, 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. Du-te la Introduce filă.
3. Apasă pe "Introduceți Scatter (X, Y) sau diagramă cu bule”Buton.
4. Alegeți „Împrăștiați cu linii netede. ”

Pasul 6: configurați tabelul de etichete.

Din punct de vedere tehnic, aveți curba clopotului. Dar ar fi greu de citit, deoarece nu au date care să-l 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 folosi și pentru afișarea scorurilor z).

Pentru aceasta, configurați încă o masă 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:

1	= I5- $ F $ 2

Pur și simplu, formula scade suma valorilor deviației standard precedente din medie. Acum, glisaț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ă:

1	= I5 + $ F $ 2

Î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 așa cum doriți marcatorii de date așezați 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 graficul și alegeți „Selectați Date.”

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

Evidențiați intervalele de celule respective din tabelul de ajutorI2: I8 pentru "Valorile seriei X" și J2: J8 pentru "Valorile seriei Y ”-și faceți clic pe „BINE.”

Pasul 8: Schimbați tipul graficului pentru seria de etichete.

Următorul nostru pas este să schimbăm tipul de grafic al seriei nou adăugate pentru a face ca marcajele de date să apară ca puncte. Pentru a face acest lucru, faceți clic dreapta pe diagrama graficului și selectați „Schimbați tipul diagramei.”

Apoi, proiectați o diagramă combinată:

1. Navigați la Combo filă.
2. Pentru Seria „Seria2” Schimbare "Tipul graficului" la "Împrăștia.”
   • Notă: asigurați-vă că „Seria1"Rămâne la fel"Împrăștiați cu linii netede. ” Uneori, Excel îl va schimba atunci când creați un Combo De asemenea, asigurați-vă că „Seria1”Nu este apăsat pe axa secundară - caseta de selectare de lângă tipul graficului nu trebuie marcată.
3. Faceți clic pe „Bine.”

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 „Formatează axa”Din meniu.

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

• Du-te la Opțiuni Axis filă.
• Seteaza Limite minime valoare la „15.”
• Seteaza Limite maxime valoare 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ă adăugați etichete de date personalizate. Mai întâi, faceți clic dreapta pe orice punct reprezentând 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 oricare Seria „Seria2” eticheta de date.
2. Selectați "Formatați etichete de date.”
3. În panoul de activități, comutați la Opțiuni pentru etichete filă.
4. Verifică "Valoarea X" cutie.
5. Debifați „Valoarea Y" cutie.
6. Sub "Poziția etichetei,”Alege”De mai sus.”

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

Pasul 11: Recolorați markerii 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 oricare Seria „Seria2” eticheta de date.
2. Apasă pe "Completati”Buton.
3. Alegeți culoarea din paleta care apare.

De asemenea, eliminați marginile din jurul punctelor:

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

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

Ca o ajustare finală, puteți adăuga 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).
• Du-te la Introduce filă.
• Apasă pe "Forme”Buton.
• Alegeți „Linia.”

Țineți apăsat butonul "SCHIMB" tasta în timp ce trageți 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ă vă arată datele de distribuție valoroase.

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