acest tutorial va demonstra cum se creează o curbă normală a clopotului de distribuție în toate versiunile Excel: 2007, 2010, 2013, 2016 și 2019.

în statistici, o curbă clopot (cunoscută și sub numele de distribuție normală standard sau curbă Gaussiană) este un grafic simetric care ilustrează tendința datelor de a se grupa în jurul unei valori centrale sau medii într-un set de date dat.

axa y reprezintă probabilitatea relativă a unei valori date care apare î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 îndeaproape.

deoarece Excel nu are soluții încorporate de oferit, va trebui să-l complotați singur. De aceea am dezvoltat programul de completare Chart Creator, un instrument care vă permite să construiți diagrame Excel avansate în doar câteva clicuri.

în acest tutorial pas cu pas, veți învăța cum să creați o curbă normală a clopotului de distribuție în Excel de la bază:

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.
• deviația 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 examenului de 53 și 85.

cu cât SD-ul este mai mic, 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 normale de distribuție, ceea ce înseamnă că aproximativ 68% din datele dvs. vor fi plasate într-un SD departe de medie, 95% în două SD și 99,7% în trei SD.

acum, că știți elementele esențiale, să trecem de la teorie la practică.

Noțiuni de bază

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

Pasul # 1: Găsiți media.

de obicei, vi se dau valorile medii și 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:

o notă rapidă: de cele mai multe ori, poate fi necesar să rotunjiți ieșirea formulei. Pentru a face acest lucru, pur și simplu înfășurați – l în funcția rotundă după cum urmează:

Pasul #2: Găsiți abaterea standard.

unul jos, unul pentru a merge. Din fericire, Excel are o funcție specială pentru a face toată munca murdară 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—nu uitați să rotunjiți și ieșirea.

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 pași) Unite împreună cu o linie pentru a crea o curbă lină.

în cazul nostru, valorile axei x vor fi folosite 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 câte intervale doriți—puteți șterge fără efort datele redundante ulterior modificând scala axei orizontale. Doar asigurați-vă că alegeți o gamă care va încorpora cele trei abateri standard.

să începem un număr la unul (deoarece nu există nicio modalitate prin 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ă masă de ajutor.

1. Alegeți orice celulă goală de sub datele diagramei (cum ar fi E4) și tastați „1”, valoarea care definește primul interval.
2. navigați la fila Acasă.
3. în grupul de editare, alegeți „completați.”
4. sub „serie în,” selectați „coloană.”
5. pentru „valoarea pasului”, tastați ” 1.”Această valoare determină incrementele care vor fi adăugate automat până când Excel atinge ultimul interval.
6. pentru „Stop value”, 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 umplute cu valorile care merg de la 2 la 150.

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

Pasul #4: calculați valorile normale de distribuție pentru fiecare valoare a axei 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 pentru dvs., Excel are calul de lucru pentru a face toate aceste calcule pentru dvs.: norma.Funcția DIST.

introduceți următoarea formulă în celula din dreapta (F4) a primului interval (E4):

aici este versiunea decodat pentru a vă ajuta să se adapteze în consecință:

blocați valorile medii și 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 complot scatter cu linii netede.

în cele din urmă, a venit timpul să construim curba clopotului:

1. Selectați orice valoare din tabelul de ajutor care conține valorile axei x și y (E4: F153).
2. accesați fila Inserare.
3. Faceți clic pe butonul „Insert Scatter (X, Y) sau Bubble Chart”.
4. alegeți „împrăștiați cu linii netede.”

Pasul #6: configurați tabelul de etichete.

tehnic, ai curba clopotului. Dar ar fi greu de citit, deoarece îi lipsesc datele care o descriu.

să facem distribuția normală mai informativă prin adăugarea etichetelor 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ă un tabel de ajutor după cum urmează:

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

apoi, calculați valorile deviației standard sub medie introducând această formulă simplă î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 markerii 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 graficul graficului și alegeți „Selectați date.”

în caseta de dialog care apare, selectați „Adăugare.”

evidențiați intervalele de celule respective din tabelul de ajutor—I2:I8 pentru „valorile seriei X” și J2:J8 pentru „valorile seriei Y”—și faceți clic pe „OK.”

Pasul #8: schimbați tipul de diagramă al seriei de etichete.

următorul nostru pas este să schimbăm tipul de diagramă al seriei nou adăugate pentru a face markerii de date să apară ca puncte. Pentru a face acest lucru, faceți clic dreapta pe graficul graficului și selectați „Modificați tipul graficului.”

apoi, proiectați o diagramă combinată:

1. navigați la fila Combo.
2. pentru seria „Series2,” Schimbare „tip diagramă” la „Scatter.”
   • notă: Asigurați-vă că” Series1 „rămâne ca” Scatter cu linii netede.”Uneori Excel îl va schimba atunci când faceți o combinație, 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 reglând scala axei orizontale. Faceți clic dreapta pe axa orizontală și alegeți „Format Axis” din meniu.

după ce apare panoul de activități, procedați în felul următor:

• accesați fila Opțiuni axe.
• setați valoarea limitelor minime la „15.”
• setați valoarea limitelor maxime la ” 125.”

puteți modifica intervalul de scală a axei oricum doriți, dar din moment ce cunoașteți intervalele de deviație standard, setați valorile limitelor un pic distanță de fiecare dintre cele trei 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 etichetele de date personalizate. Mai întâi, faceți clic dreapta pe orice punct reprezentând seria „Serie2” ș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 marcatorilor de date.

1. faceți clic dreapta pe orice etichetă de date „Series2” din serie.
2. Selectați „Formatați Etichetele De Date.”
3. în panoul de activități, comutați la fila Opțiuni etichetă.
4. bifați caseta „X Value”.
5. debifați caseta „Y Value”.
6. sub „poziția etichetei”, alegeți „de mai sus.”

de asemenea, acum Puteți elimina liniile de grilă (Faceți clic dreapta pe ele > șterge).

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 orice etichetă de date „Series2” din serie.
2. Faceți clic pe butonul „completați”.
3. alegeți culoarea din paleta care apare.

de asemenea, eliminați marginile din jurul punctelor:

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

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

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

• selectați graficul graficului (astfel, liniile vor fi inserate direct în diagramă).
• accesați fila Inserare.
• Faceți clic pe butonul „Forme”.
• Alege „Linie.”

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

schimbați titlul graficului și curba îmbunătățită a clopotului este gata—afișând datele dvs. valoroase de distribuție.

și așa o faceți. Puteți alege acum orice set de date și de a crea o curbă normală clopot de distribuție urmând acești pași simpli!