Kako stvoriti normalnu distribucijsku zvonastu krivulju u Excelu

Ovaj će vodič pokazati kako stvoriti normalnu krivulju distribucijskog zvona u svim verzijama programa Excel: 2007, 2010, 2013, 2016 i 2022.

U statistikama, zvonasta krivulja (također poznata kao standardna normalna distribucija ili Gaussova krivulja) je simetrični graf koji ilustrira tendenciju podataka da se grupiraju oko središnje vrijednosti ili srednje vrijednosti u danom skupu podataka.

Os y predstavlja relativnu vjerojatnost da se zadana vrijednost pojavi u skupu podataka, dok osi x iscrtavaju same vrijednosti na grafikonu kako bi stvorile krivulju u obliku zvona, otuda i naziv.

Grafikon nam pomaže analizirati je li određena vrijednost dio očekivane varijacije ili je statistički značajna, pa se stoga mora detaljnije ispitati.

Budući da Excel nema ugrađena rješenja za ponuditi, morat ćete to sami iscrtati. Zato smo razvili dodatak Chart Creator-alat koji vam omogućuje da u samo nekoliko klikova izradite napredne Excelove grafikone.

U ovom korak-po-korak vodiču naučit ćete kako stvoriti normalnu krivulju zvona distribucije u Excelu od temelja:

Da biste iscrtali Gaussovu krivulju, morate znati dvije stvari:

• Sredstvo (poznato i kao standardno mjerenje). To određuje središte krivulje-koje, pak, karakterizira položaj krivulje.
• Standardna devijacija (SD) mjerenja. To definira širenje vaših podataka u normalnoj distribuciji-ili na običnom engleskom jeziku, koliko bi široka krivulja trebala biti. Na primjer, u zvonastoj krivulji prikazanoj gore, jedno standardno odstupanje srednje vrijednosti predstavlja raspon između ocjena ispita 53 i 85.

Što je SD manji, krivulja je viša i vaši će se podaci manje raširiti, i obrnuto.

Vrijedno je spomenuti pravilo 68-95-99.7 koje se može primijeniti na bilo koju normalnu krivulju distribucije, što znači da će otprilike 68% vaših podataka biti smješteno unutar jedne SD udaljenosti od srednje vrijednosti, 95% unutar dvije SD, a 99,7% unutar tri SD -a.

Sada kada znate osnovne stvari, prijeđimo s teorije na praksu.

Početak rada

Radi ilustracije, pretpostavimo da imate rezultate testa od 200 učenika i želite ih ocijeniti "na krivulji", što znači da će se ocjene učenika temeljiti na njihovoj relativnoj uspješnosti u odnosu na ostatak razreda:

Korak 1: Pronađite srednju vrijednost.

Obično vam se od početka daju srednje i SD vrijednosti, ali ako to nije slučaj, možete jednostavno izračunati te vrijednosti u samo nekoliko jednostavnih koraka. Prvo se pozabavimo srednjom vrijednosti.

Budući da srednja vrijednost pokazuje prosječnu vrijednost uzorka ili populacije podataka, standardna mjerenja možete pronaći pomoću funkcije PROSJEK.

U bilo koju praznu ćeliju upišite sljedeću formulu (F1 u ovom primjeru) pored vaših stvarnih podataka (stupci A i B) za izračunavanje prosjeka rezultata ispita u skupu podataka:

1	= PROSJEČNO (B2: B201)

Kratka napomena: češće ćete morati zaokružiti izlaz formule. Da biste to učinili, jednostavno ga umotajte u funkciju ROUND na sljedeći način:

1	= OKRUGLO (PROSJEČNO (B2: B201), 0)

Korak 2: Pronađite standardnu ​​devijaciju.

Jedan dolje, jedan do kraja. Srećom, Excel ima posebnu funkciju koja obavlja sav prljavi posao pronalaženja standardne devijacije za vas:

1	= STDEV.P (B2: B201)

Ponovno, formula odabire sve vrijednosti iz navedenog raspona ćelija (B2: B201) i izračunava njegovu standardnu ​​devijaciju-samo ne zaboravite zaokružiti i izlaz.

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

Korak #3: Postavite vrijednosti osi x za krivulju.

U osnovi, grafikon čini ogroman broj intervala (zamislite ih kao korake) spojenih zajedno s linijom kako biste stvorili glatku krivulju.

U našem slučaju, vrijednosti osi x koristit će se za ilustraciju određenog rezultata ispita, dok će nam vrijednosti osi y govoriti o vjerojatnosti da student dobije taj rezultat na ispitu.

Tehnički, možete uključiti onoliko intervala koliko želite-kasnije možete bez napora izbrisati suvišne podatke izmjenom vodoravne osi. Samo pazite da odaberete raspon koji će uključivati ​​tri standardne devijacije.

Počnimo brojati od jedan (jer nema šanse da student dobije negativnu ocjenu ispita) i idemo sve do 150-nije važno je li 150 ili 1500-za postavljanje drugog pomoćnog stola.

1. Odaberite bilo koju praznu ćeliju ispod podataka grafikona (npr E4) i tip “1,” vrijednost koja definira prvi interval.
2. Idite na Dom tab.
3. U Uređivanje grupa, odaberite "Ispunite.”
4. Pod, ispod "Serija u," Odaberi "Stupac.”
5. Za “Vrijednost koraka,”Tip “1.” Ova vrijednost određuje korake koji će se automatski zbrajati sve dok Excel ne dosegne zadnji interval.
6. Za “Stop vrijednost,"Tip"150,” vrijednost koja označava posljednji interval i kliknite “U REDU.”

Za čudo, 149 ćelija u stupcu E (E5: E153) ispunjene su vrijednostima od 2 do 150.

NAPOMENA: Ne skrivajte izvorne podatkovne ćelije kao što je prikazano na snimkama zaslona. U suprotnom, tehnika neće raditi.

Korak #4: Izračunajte normalne vrijednosti raspodjele za svaku vrijednost osi x.

Sada pronađite normalne vrijednosti raspodjele-vjerojatnost da student dobije određeni rezultat ispita predstavljen određenom vrijednošću osi x-za svaki od intervala. Na vašu sreću, Excel ima radnog konja koji za vas može obaviti sve ove izračune: funkciju NORM.DIST.

Upišite sljedeću formulu u ćeliju desno (F4) vašeg prvog intervala (E4):

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

Evo dekodirane verzije koja će vam pomoći da se prilagodite u skladu s tim:

1	= NORM.DIST ([prvi interval], [srednja vrijednost (apsolutna referenca)], [standardna devijacija (apsolutna referenca), FALSE)

Zaključavate srednje i SD vrijednosti tako da možete bez napora izvesti formulu za preostale intervale (E5: E153).

Sada dvokliknite ručicu ispune da biste kopirali formulu u ostale ćelije (F5: F153).

Korak #5: Izradite raspršenu plohu s glatkim linijama.

Konačno, došlo je vrijeme za izgradnju zvonaste krivulje:

1. Odaberite bilo koju vrijednost u pomoćnoj tablici koja sadrži vrijednosti osi x i y (E4: F153).
2. Idite na Umetnuti tab.
3. Kliknite na “Umetnite raspršivač (X, Y) ili grafikon mjehurića" dugme.
4. Odaberite “Raspršite s glatkim linijama. ”

Korak #6: Postavite tablicu oznaka.

Tehnički, imate svoju zvonastu krivulju. No, bilo bi ga teško pročitati jer nema podataka koji ga opisuju.

Učinimo normalnu distribuciju informativnijom dodavanjem oznaka koje ilustriraju sve vrijednosti standardne devijacije ispod i iznad srednje vrijednosti (možete ih koristiti i za prikazivanje z-rezultata).

U tu svrhu postavite još jednu pomoćnu tablicu na sljedeći način:

Prvo kopirajte srednju vrijednost (F1) pored odgovarajuće ćelije u stupcu X-vrijednost (I5).

Zatim izračunajte vrijednosti standardnog odstupanja ispod srednje vrijednosti unošenjem ove jednostavne formule ćelija I4:

1	= I5- $ F $ 2

Jednostavno rečeno, formula oduzima prosjek zbroja prethodnih vrijednosti standardnog odstupanja. Sada povucite ručicu za popunjavanje prema gore da kopirate formulu u preostale dvije ćelije (I2: I3).

Ponovite isti postupak za standardna odstupanja iznad srednje vrijednosti pomoću zrcalne formule:

1	= I5+$ F $ 2

Na isti način izvedite formulu za druge dvije ćelije (I7: I8).

Na kraju, popunite vrijednosti oznake osi y (J2: J8) s nulama koliko želite da se oznake podataka postavljaju na vodoravnu os.

Korak #7: Umetnite podatke o oznaci u grafikon.

Sada dodajte sve podatke koje ste pripremili. Desnom tipkom miša kliknite grafikon i odaberite "Odaberite Podaci.”

U dijaloškom okviru koji se pojavi odaberite "Dodati.”

Istaknite odgovarajuće raspone ćelija iz pomoćne tablice-I2: I8 za “Vrijednosti serije X”I J2: J8 za “Vrijednosti serije Y ”-i kliknite "U REDU.”

Korak #8: Promijenite vrstu grafikona serije oznaka.

Naš sljedeći korak je promjena vrste grafikona novo dodane serije kako bi se oznake podataka prikazale kao točke. Da biste to učinili, desnom tipkom miša kliknite grafikon i odaberite "Promijenite vrstu grafikona.”

Zatim dizajnirajte kombinirani grafikon:

1. Idite na Kombinirano tab.
2. Za Serija "Serija2", promijeniti “Vrsta grafikona"Do"Raspršiti.”
   • Napomena: Provjerite "Serija1"Ostaje kao"Raspršivanje s glatkim linijama. ” Ponekad će ga Excel promijeniti kada napravite Kombinirano Također provjerite “Serija1”Se ne gura na sekundarnu os-potvrdni okvir pored vrste grafikona ne bi trebao biti označen.
3. Kliknite “u redu.”

Korak #9: Izmijenite ljestvicu vodoravne osi.

Centrirajte grafikon na krivulji zvona podešavanjem ljestvice vodoravne osi. Desnom tipkom miša kliknite vodoravnu os i odaberite "Osovina formata”S izbornika.

Nakon što se prikaže okno zadataka, učinite sljedeće:

• Idite na Opcije osi tab.
• Postavi Minimalne granice vrijednost za "15.”
• Postavi Maksimalne granice vrijednost za "125.”

Raspon osi možete prilagoditi kako god smatrate prikladnim, ali budući da poznajete raspone standardnih odstupanja, postavite vrijednosti Bounds malo dalje od svakog od svojih trećih standardnih odstupanja kako biste prikazali "rep" krivulje.

Korak #10: Umetnite i postavite prilagođene oznake podataka.

Dok polirate grafikon, svakako dodajte prilagođene oznake podataka. Prvo, desnom tipkom miša kliknite bilo koju točku koja predstavlja Serija "Serija 2" i odaberite “Dodajte podatkovne oznake.”

Zatim zamijenite zadane oznake onima koje ste prethodno postavili i postavite ih iznad oznaka podataka.

1. Desnom tipkom miša kliknite bilo koji Serija "Serija 2" oznaka podataka.
2. Odaberi "Oblikovanje oznaka podataka.”
3. U oknu zadataka prebacite se na Opcije oznaka tab.
4. Provjeri "X vrijednost”Kutija.
5. Poništite oznaku "Y vrijednost”Kutija.
6. Pod, ispod "Položaj naljepnice,"Odaberite"Iznad.”

Također, sada možete ukloniti crte rešetke (desnom tipkom miša kliknite na njih> Izbriši).

Korak #11: Promijenite boju oznaka podataka (izborno).

Na kraju, ponovno obojite točkice kako biste se uklopili u vaš stil grafikona.

1. Desnom tipkom miša kliknite bilo koju Serija "Serija 2" oznaka podataka.
2. Kliknite na “Ispunite" dugme.
3. Odaberite boju s prikazane palete.

Također uklonite obrube oko točkica:

1. Ponovo desnom tipkom miša kliknite isti marker podataka i odaberite “Nacrt.”
2. Odaberite “Nema obrisa.”

Korak #12: Dodajte okomite crte (izborno).

Kao posljednju prilagodbu, grafikonu možete dodati okomite crte kako biste naglasili vrijednosti SD -a.

• Odaberite grafikon grafikona (na taj način linije će biti umetnute izravno u grafikon).
• Idite na Umetnuti tab.
• Kliknite na “Oblici" dugme.
• Odaberite “Crta.”

Držite tipku "SHIFT" tipkom dok povlačite miša za crtanje savršeno okomitih linija od svake točke do mjesta gdje svaka linija zadovoljava zvonastu krivulju.

Promijenite naslov grafikona i vaša poboljšana krivulja zvona spremno prikazuje vaše vrijedne podatke o distribuciji.

I tako to radite. Sada možete odabrati bilo koji skup podataka i stvoriti normalnu krivulju distribucijskog zvona slijedeći ove jednostavne korake!