Kuidas luua Excelis normaalse jaotuskella kõver

See õpetus näitab, kuidas luua normaaljaotuskella kõver kõigis Exceli versioonides: 2007, 2010, 2013, 2016 ja 2022.

Statistikas on kellukõver (tuntud ka kui tavaline normaaljaotus või Gaussi kõver) sümmeetriline graafik, mis illustreerib andmete kalduvust koonduda antud andmestiku keskväärtuse või keskmise ümber.

Y-telg tähistab antud väärtuse suhtelist tõenäosust andmestikus, samas kui x-telg joonistab diagrammil väärtused ise, et luua kellukujuline kõver, sellest ka nimi.

Graafik aitab meil analüüsida, kas konkreetne väärtus on osa oodatavast variatsioonist või on see statistiliselt oluline ja seetõttu tuleb seda lähemalt uurida.

Kuna Excelil pole ühtegi sisseehitatud lahendust pakkuda, peate selle ise joonistama. Seetõttu töötasime välja diagrammilooja lisandmooduli-tööriista, mis võimaldab teil mõne hiireklõpsuga koostada täiustatud Exceli diagramme.

Selles samm-sammulises õpetuses saate teada, kuidas luua Excelis normaaljaotuskella kõver algusest peale:

Gaussi kõvera joonistamiseks peate teadma kahte asja:

• Keskmine (tuntud ka kui standardmõõtmine). See määrab kõvera keskpunkti, mis omakorda iseloomustab kõvera asukohta.
• Standardhälve (SD) mõõtmistest. See määrab teie andmete leviku normaaljaotuses või lihtsas inglise keeles, kui lai peaks kõver olema. Näiteks ülaltoodud kellukõveral tähistab üks keskmise standardhälve vahemikku eksamitulemuste 53 ja 85 vahel.

Mida madalam on SD, seda kõrgem on kõver ja seda vähem levitatakse teie andmeid ning vastupidi.

Tasub mainida reeglit 68-95-99,7, mida saab rakendada mis tahes normaaljaotuskõverale, mis tähendab, et ligikaudu 68% teie andmetest paigutatakse ühe SD-sse keskmisest kaugemale, 95% kahe SD-sse ja 99,7% vahemikku kolm SD -d.

Nüüd, kui teate olulisi asju, liigume teooriast praktikasse.

Alustamine

Oletame näitlikustamiseks, et teie testi tulemused on 200 õpilast ja soovite neid hinnata "kõveral", mis tähendab, et õpilaste hinded põhinevad nende suhtelisel sooritusel ülejäänud klassiga:

Samm: leidke keskmine.

Tavaliselt antakse teile algusest peale keskmised ja SD väärtused, kuid kui see pole nii, saate need väärtused lihtsalt mõne lihtsa sammuga välja arvutada. Alustuseks käsitleme keskmist.

Kuna keskmine näitab valimi või andmete kogumi keskmist väärtust, leiate oma standardmõõtmise funktsiooni KESKMINE abil.

Sisestage järgmine valem suvalisse tühja lahtrisse (F1 selles näites) oma tegelike andmete kõrval (veerud A ja B) andmestiku eksamitulemuste keskmise arvutamiseks:

1	= KESKMINE (B2: B201)

Kiire märkus: sagedamini peate valemi väljundi ümardama. Selleks mähkige see lihtsalt funktsiooni ROUND järgmiselt:

1	= ÜMBER (KESKMINE (B2: B201), 0)

Samm 2: leidke standardhälve.

Üks alla, teine ​​minema. Õnneks on Excelil spetsiaalne funktsioon, mis teeb kogu musta töö teie jaoks standardhälbe leidmiseks:

1	= STDEV.P (B2: B201)

Jällegi valib valem kõik väärtused määratud lahtrivahemikust (B2: B201) ja arvutab selle standardhälbe-ärge unustage ka väljundit ümardada.

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

Samm #3: seadistage kõvera x-telje väärtused.

Põhimõtteliselt koosneb diagramm tohutult paljudest intervallidest (pidage neid sammudeks), mis on ühendatud joonega, et luua sujuv kõver.

Meie puhul kasutatakse x-telje väärtusi konkreetse eksamitulemuse illustreerimiseks, samal ajal kui y-telje väärtused näitavad meile tõenäosust, et õpilane saab eksamil selle tulemuse.

Tehniliselt saate lisada nii palju intervalle kui soovite-saate üleliigsed andmed hiljem vaevata kustutada, muutes horisontaaltelje skaalat. Lihtsalt veenduge, et valite vahemiku, mis sisaldab kolme standardhälvet.

Alustame loendamist ühest (kuna õpilane ei saa mingil juhul negatiivset eksamiskoori) ja läheme kuni 150-ni-pole vahet, kas 150 või 1500 on teise abistaja laud.

1. Valige diagrammi andmete alt tühjad lahtrid (nt E4) ja tüüp “1,” väärtus, mis määratleb esimese intervalli.
2. Liikuge lehele Kodu vahekaart.
3. Aastal Toimetamine rühm, valige "Täida.”
4. All "Sari sisse,"Vali"Veerg.”
5. Sest “Sammu väärtus,”Tüüpi “1.” See väärtus määrab sammud, mis liidetakse automaatselt, kuni Excel jõuab viimasele intervallile.
6. Sest “Peata väärtus,"Tüüp"150,” väärtus, mis tähistab viimast intervalli, ja klõpsake nuppu „OKEI.”

Imekombel 149 rakku veerus E (E5: E153) on täidetud väärtustega 2–150.

MÄRKUS. Ärge peitke andmebaase, nagu on näidatud ekraanipiltidel. Vastasel juhul tehnika ei tööta.

Samm #4: arvutage normaaljaotuse väärtused iga x-telje väärtuse jaoks.

Nüüd leidke normaaljaotuse väärtused-tõenäosus, et õpilane saab iga intervalli kohta teatud eksamitulemuse, mida esindab konkreetne x-telje väärtus. Teie õnneks on Excelil kõigi nende arvutuste eest tööhobune: funktsioon NORM.DIST.

Sisestage paremal asuvasse lahtrisse järgmine valem (F4) oma esimesest intervallist (E4):

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

Siin on dekodeeritud versioon, mis aitab teil vastavalt kohandada:

1	= NORM.DIST ([esimene intervall], [keskmine (absoluutne võrdlusväärtus)], [standardhälve (absoluutne viide), VÄÄR)

Lukustate keskmised ja SD väärtused, et saaksite vaevata ülejäänud intervallide valemit täita (E5: E153).

Nüüd topeltklõpsake täitekäepidemel, et kopeerida valem ülejäänud lahtritesse (F5: F153).

Samm #5: looge ühtlaste joontega hajumisjoon.

Lõpuks on saabunud kellukõvera ehitamise aeg:

1. Valige abitabelis mis tahes väärtus, mis sisaldab x- ja y-telje väärtusi (E4: F153).
2. Minge Sisesta vahekaart.
3. Klõpsake "Sisestage hajumine (X, Y) või mullitabel”Nuppu.
4. Valige "Hajutage sujuvate joontega. ”

6. samm: seadistage siltide tabel.

Tehniliselt on teil kellukõver. Kuid seda oleks raske lugeda, kuna sellel puuduvad selle kirjeldamiseks vajalikud andmed.

Muudame normaaljaotuse informatiivsemaks, lisades sildid, mis illustreerivad kõiki standardhälbe väärtusi alla ja üle keskmise (saate neid kasutada ka z-skooride näitamiseks).

Selleks seadistage veel üks abilaud järgmiselt:

Kõigepealt kopeerige keskmine väärtus (F1) vastava lahtri kõrval veerus X-Value (I5).

Järgmisena arvutage standardhälbe väärtused alla keskmise, sisestades selle lihtsa valemi lahter I4:

1	= I5- $ 2 $

Lihtsamalt öeldes lahutab valem keskmisest keskmise standardhälbe väärtuste summa. Nüüd lohistage täitekäepidet ülespoole, et kopeerida valem ülejäänud kahte lahtrisse (I2: I3).

Korrake sama protsessi keskmisest kõrgemate standardhälvete puhul, kasutades peeglivalemit:

1	= I5+2 dollarit

Samamoodi täitke ülejäänud kahe lahtri valem (I7: I8).

Lõpuks täitke y-telje sildi väärtused (J2: J8) nullidega, nagu soovite, et andmemarkerid asetataks horisontaalteljele.

Samm #7: sisestage sildi andmed diagrammi.

Nüüd lisage kõik ettevalmistatud andmed. Paremklõpsake diagrammi graafikut ja valige "Valige Andmed.”

Avanevas dialoogiboksis valige "Lisama.”

Tõstke abilauast esile vastavad lahtrid-I2: I8 jaoks “X -seeria väärtused”Ja J2: J8 jaoks “Seeria Y väärtused ”-ja klõpsake "OKEI.”

8. samm: muutke sildisarja diagrammitüüpi.

Meie järgmine samm on värskelt lisatud seeria diagrammitüübi muutmine, et andmemärgid ilmuksid punktidena. Selleks paremklõpsake diagrammi graafikut ja valige „Muuda diagrammi tüüpi.”

Seejärel kujundage kombineeritud diagramm:

1. Liikuge lehele Kombinatsioon vahekaart.
2. Sest Sari "Sari 2" muuda "Diagrammi tüüp"Kuni"Hajutada.”
   • Märkus: veenduge, et "Seeria1"Jääb nagu"Hajutage sujuvate joontega. ” Mõnikord muudab Excel seda, kui teete Kombinatsioon Veenduge ka, et "Seeria1”Ei lükata teisese telje juurde-diagrammi tüübi kõrval olevat märkeruutu ei tohiks märkida.
3. Klõpsake „Okei.”

9. samm: muutke horisontaaltelje skaalat.

Keskendage diagramm kellukõverale, reguleerides horisontaaltelje skaalat. Paremklõpsake horisontaalteljel ja valige "Vormindage telg”Menüüst.

Kui ülesannete paan ilmub, tehke järgmist.

• Minge Teljevalikud vahekaart.
• Määrake Miinimumpiirid väärtuseks "15.”
• Määrake Maksimaalsed piirid väärtus "125.”

Te saate telje skaala vahemikku vastavalt oma äranägemisele muuta, kuid kuna teate standardhälbe vahemikke, seadke piiride väärtused igast kolmandast standardhälbest natuke eemale, et näidata kõvera saba.

Samm 10: sisestage ja paigutage kohandatud andmesildid.

Diagrammi lihvides lisage kindlasti kohandatud andmesildid. Esiteks paremklõpsake mis tahes punkti, mis seda esindab Sari “Sari 2” ja valige "Andmesiltide lisamine.”

Seejärel asendage vaikesildid varem seadistatud siltidega ja asetage need andmemärkide kohale.

1. Paremklõpsake ükskõik millisel Sari “Sari 2” andmesilt.
2. Valige "Andmesiltide vormindamine.”
3. Lülituge tegumipaanil valikule Sildi valikud vahekaart.
4. Kontrolli "X väärtus”Kast.
5. Tühjendage märkeruut "Y väärtus”Kast.
6. All "Sildi asukoht,"Vali"Eespool.”

Samuti saate nüüd ruudustikjooned eemaldada (paremklõpsake neid> Kustuta).

Samm 11: värvige andmemarkerid uuesti (valikuline).

Lõpuks värvige punktid ümber, et need sobiksid teie diagrammi stiiliga.

1. Paremklõpsake ükskõik millisel Sari “Sari 2” andmesilt.
2. Klõpsake "Täida”Nuppu.
3. Valige ilmuvast paletist oma värv.

Samuti eemaldage punktide ümber olevad äärised:

1. Paremklõpsake uuesti sama andmemarkerit ja valige „Kontuur.”
2. Valige "Kontuuri pole.”

Samm: lisage vertikaalsed jooned (valikuline).

Viimase korrigeerimisena saate diagrammile lisada vertikaalseid jooni, mis aitavad rõhutada SD väärtusi.

• Valige diagrammi graafik (sel viisil sisestatakse read otse diagrammi).
• Minge Sisesta vahekaart.
• Klõpsake "Kujundid”Nuppu.
• Valige "Rida.”

Hoidke all “SHIFT” klahvi ja lohistage hiirt, et joonistada igast punktist täiesti vertikaalsed jooned sinna, kus iga rida vastab kella kõverale.

Muutke diagrammi pealkirja ja teie täiustatud kellukõver näitab teie väärtuslikke levitamisandmeid.

Ja nii teete seda. Nüüd saate neid lihtsaid samme järgides valida mis tahes andmekogumi ja luua normaaljaotuskella kõvera!