Normaalin jakelukellon luominen Excelissä

Tämä opetusohjelma osoittaa, kuinka luoda normaalin jakautumiskellon käyrä kaikissa Excel -versioissa: 2007, 2010, 2013, 2016 ja 2022.

Tilastossa kellokäyrä (tunnetaan myös nimellä tavallinen normaalijakauma tai Gaussin käyrä) on symmetrinen kaavio, joka kuvaa tietojen taipumusta ryhmittyä tietyn tietojoukon keskiarvon tai keskiarvon ympärille.

Y-akseli edustaa tietyn arvon suhteellista todennäköisyyttä tietojoukossa, kun taas x-akseli piirtää arvot itse kaavioon luodakseen kellon muotoisen käyrän, josta nimi.

Kaavio auttaa meitä analysoimaan, onko tietty arvo osa odotettua vaihtelua vai onko se tilastollisesti merkitsevä ja siksi sitä on tarkasteltava tarkemmin.

Koska Excelillä ei ole sisäänrakennettuja ratkaisuja, sinun on piirrettävä se itse. Siksi kehitimme Chart Creator -apuohjelman, työkalun, jonka avulla voit luoda edistyneitä Excel-kaavioita muutamalla napsautuksella.

Tässä vaiheittaisessa opetusohjelmassa opit luomaan normaalijakelukellon käyrän Excelissä alusta alkaen:

Jotta voit piirtää Gaussin käyrän, sinun on tiedettävä kaksi asiaa:

• Ilkeät (tunnetaan myös nimellä vakiomittaus). Tämä määrittää käyrän keskipisteen, joka puolestaan ​​kuvaa käyrän sijaintia.
• Keskihajonta (SD) mittauksista. Tämä määrittää tietojesi leviämisen normaalijakaumassa-tai yksinkertaisena englanniksi, kuinka leveän käyrän tulisi olla. Esimerkiksi edellä esitetyssä kellokäyrässä yksi keskiarvon keskihajonta edustaa koketulosten 53 ja 85 välistä aluetta.

Mitä matalampi SD, sitä korkeampi käyrä ja sitä vähemmän tietoja levitetään ja päinvastoin.

On syytä mainita 68-95-99,7-sääntö, jota voidaan soveltaa mihin tahansa normaalijakaumakäyrään, mikä tarkoittaa, että noin 68% tiedoistasi sijoitetaan yhden SD: n päähän keskiarvosta, 95% kahden SD: n sisällä ja 99,7% sisällä kolme SD.

Nyt kun tiedät olennaiset asiat, siirrymme teoriasta käytäntöön.

Päästä alkuun

Oletetaan esimerkkinä, että sinulla on 200 oppilaan testitulokset ja haluat luokitella ne "käyrälle", mikä tarkoittaa, että oppilaiden arvosanat perustuvat heidän suhteelliseen suoritukseensa muulla luokalla:

Vaihe 1: Etsi keskiarvo.

Yleensä sinulle annetaan keskiarvo ja SD -arvot alusta alkaen, mutta jos näin ei ole, voit helposti laskea nämä arvot muutamalla yksinkertaisella vaiheella. Käsitellään ensin keskiarvo.

Koska keskiarvo osoittaa näytteen tai tietopopulaation keskiarvon, voit löytää vakiomittauksesi KESKI -funktion avulla.

Kirjoita seuraava kaava mihin tahansa tyhjään soluun (F1 tässä esimerkissä) todellisten tietojen vieressä (sarakkeet A ja B) laskemaan tietojoukon koetulosten keskiarvo:

1	= KESKIARVO (B2: B201)

Nopea huomautus: useimmiten sinun on ehkä pyöristettävä kaavan tulos. Voit tehdä tämän yksinkertaisesti kääntämällä sen ROUND -toimintoon seuraavasti:

1	= Kierros (KESKI (B2: B201), 0)

Vaihe 2: Etsi keskihajonta.

Yksi alas, yksi menemään. Onneksi Excelillä on erityinen toiminto, joka tekee kaiken likaisen työn löytääksesi keskihajonnan puolestasi:

1	= STDEV.P (B2: B201)

Jälleen kaava poimii kaikki arvot määritetystä solualueesta (B2: B201) ja laskee sen keskihajonnan-älä unohda pyöristää myös tuotosta.

1	= PYÖREÄ (KATSAUS.P (B2: B201), 0)

Vaihe 3: Määritä käyrän x-akselin arvot.

Pohjimmiltaan kaaviossa on valtava määrä aikavälejä (ajattele niitä askeleina), jotka on yhdistetty viivaan tasaisen käyrän luomiseksi.

Meidän tapauksessamme x-akselin arvoja käytetään havainnollistamaan tiettyä tenttipistettä, kun taas y-akselin arvot kertovat meille todennäköisyyden siitä, että opiskelija saa pisteet tentissä.

Teknisesti voit sisällyttää niin monta aikaväliä kuin haluat-voit poistaa tarpeettomat tiedot myöhemmin vaivattomasti muuttamalla vaaka-akseliasteikkoa. Varmista vain, että valitset alueen, joka sisältää kolme keskihajontaa.

Aloitetaan lasku yhdestä (koska opiskelija ei voi mitenkään saada negatiivista tenttipistettä) ja jatka aina 150: een asti-sillä ei ole väliä, onko 150 tai 1500-perustaa toinen avustajapöytä.

1. Valitse tyhjä solu kaavion tietojen alapuolelta (kuten E4) ja tyyppi “1,” arvo, joka määrittää ensimmäisen välin.
2. Siirry kohtaan Koti välilehti.
3. Kohteessa Muokkaus ryhmä, valitse "Täyttää.”
4. Alla "Sarja sisään,"Valitse"Sarake.”
5. "Askelarvo,”Tyyppiä “1.” Tämä arvo määrittää lisäykset, jotka lisätään automaattisesti, kunnes Excel saavuttaa viimeisen välin.
6. "Pysäytä arvo,"Tyyppi"150,” arvo, joka edustaa viimeistä aikaväliä, ja napsauta "OK.”

Ihmeellisesti 149 solua sarakkeessa E (E5: E153) on täytetty arvoilla 2-150.

HUOMAUTUS: Älä piilota alkuperäisiä tietosoluja kuvakaappausten mukaisesti. Muuten tekniikka ei toimi.

Vaihe 4: Laske normaalijakauman arvot jokaiselle x-akselin arvolle.

Etsi nyt normaalijakauman arvot-todennäköisyys, että opiskelija saa tietyn tenttipisteen, jota edustaa tietty x-akselin arvo-kullekin aikavälille. Onneksi Excelillä on työhevonen, joka suorittaa kaikki nämä laskelmat puolestasi: NORM.DIST -funktio.

Kirjoita seuraava kaava oikealla olevaan soluun (F4) ensimmäisestä välistäsi (E4):

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

Tässä on dekoodattu versio, joka auttaa sinua säätämään vastaavasti:

1	= NORM.JAKAUMA ([ensimmäinen väli], [keskiarvo (absoluuttinen viite)], [keskihajonta (absoluuttinen viite), EPÄTOSI)

Lukitset keskiarvon ja SD -arvot, jotta voit suorittaa vaivatta kaavan jäljellä oleville aikaväleille (E5: E153).

Kaksoisnapsauta nyt täyttökahvaa kopioidaksesi kaavan muihin soluihin (F5: F153).

Vaihe 5: Luo sirontakaavio tasaisilla viivoilla.

Lopuksi on tullut aika rakentaa kellokäyrä:

1. Valitse mikä tahansa arvo aputaulukosta, joka sisältää x- ja y-akselin arvot (E4: F153).
2. Siirry kohtaan Lisää välilehti.
3. Klikkaa "Lisää piste- (X, Y) tai kuplakaavio”-Painiketta.
4. Valitse "Hajottaa sileillä viivoilla. ”

Vaihe 6: Määritä tarrataulukko.

Teknisesti sinulla on kellokäyräsi. Mutta sitä olisi vaikea lukea, koska siitä puuttuvat tiedot siitä.

Tehdään normaalijakaumasta informatiivisempi lisäämällä tarroja, jotka kuvaavat kaikkia keskihajonta-arvoja keskiarvon alapuolella ja yläpuolella (voit käyttää niitä myös z-pisteiden näyttämiseen).

Aseta sitä varten toinen avustajapöytä seuraavasti:

Kopioi ensin keskiarvo (F1) vastaavan solun vieressä sarakkeessa X-arvo (I5).

Laske seuraavaksi keskihajonta -arvot keskiarvon alapuolelle syöttämällä tämä yksinkertainen kaava solu I4:

1	= I5- $ 2 $

Yksinkertaisesti sanottuna kaava vähentää edellisten keskihajonta -arvojen summan keskiarvosta. Vedä täyttökahvaa ylöspäin kopioidaksesi kaavan jäljellä oleviin kahteen soluun (I2: I3).

Toista sama prosessi keskihajontojen yläpuolella oleville keskihajonnoille peilikaavan avulla:

1	= I5+$ 2 $

Suorita samalla tavalla kaava kahdelle muulle solulle (I7: I8).

Täytä lopuksi y-akselin tarran arvot (J2: J8) nollilla, kuten haluat, että tietomerkit sijoitetaan vaaka -akselille.

Vaihe 7: Lisää tarratiedot kaavioon.

Lisää nyt kaikki valmistamasi tiedot. Napsauta kaaviota hiiren kakkospainikkeella ja valitse "Valitse Data.”

Valitse avautuvasta valintaikkunasta "Lisätä.”

Korosta vastaavat solualueet apupöydästä-I2: I8 varten "Sarja X -arvot”Ja J2: J8 varten "Y -sarjan arvot ”-ja napsauta "OK.”

Vaihe 8: Vaihda tarrasarjan kaaviotyyppiä.

Seuraava askeleemme on muuttaa äskettäin lisätyn sarjan kaaviotyyppi siten, että tietomerkit näkyvät pisteinä. Voit tehdä sen napsauttamalla kaaviota hiiren kakkospainikkeella ja valitsemalla "Muuta kaavion tyyppiä.”

Suunnittele seuraavaksi yhdistelmäkaavio:

1. Siirry kohtaan Yhdistelmä välilehti.
2. Varten Sarja "Sarja2" muuta "Kaavion tyyppi"Kohtaan"Hajaantua.”
   • Huomaa: Varmista, että "Sarja 1"Pysyy"Hajottaa sileillä viivoilla. ” Joskus Excel muuttaa sitä, kun teet Yhdistelmä Varmista myös "Sarja 1”Ei työnnetä toissijaiselle akselille-kaaviotyypin vieressä olevaa valintaruutua ei tule merkitä.
3. Klikkaus "OK.”

Vaihe 9: Muokkaa vaaka -akselin asteikkoa.

Keskitä kaavio kellokäyrään säätämällä vaaka -akseliasteikkoa. Napsauta hiiren kakkospainikkeella vaaka-akselia ja valitse "Muotoile akseli”Valikosta.

Kun tehtäväruutu tulee näkyviin, toimi seuraavasti:

• Siirry kohtaan Akselivaihtoehdot välilehti.
• Aseta Vähimmäisrajat arvo "15.”
• Aseta Enimmäisrajat arvo "125.”

Voit säätää akseliasteikkoa haluamallasi tavalla, mutta koska tiedät keskihajonta -alueet, aseta Bounds -arvot hieman kauemmaksi kolmannesta keskihajonnasta käyrän "hännän" näyttämiseksi.

Vaihe 10: Lisää ja aseta mukautetut datatarrat.

Kun viimeistelet kaaviotasi, muista lisätä mukautetut datatarrat. Napsauta ensin mitä tahansa edustavaa pistettä hiiren kakkospainikkeella Sarja "Sarja 2" ja valitse "Lisää datatarrat.”

Korvaa seuraavaksi oletustarrat aiemmin määrittämilläsi ja aseta ne datamerkkien yläpuolelle.

1. Napsauta mitä tahansa hiiren kakkospainikkeella Sarja "Sarja 2" tietolappu.
2. Valitse "Muotoile datatarrat.”
3. Vaihda tehtäväruudussa kohtaan Tarra -asetukset välilehti.
4. Tarkista "X Arvo”Laatikko.
5. Poista valinta "Y Arvo”Laatikko.
6. Alla "Tarran sijainti,"Valitse"Edellä.”

Voit myös nyt poistaa ruudukon (napsauta niitä hiiren kakkospainikkeella> Poista).

Vaihe 11: Väritä datamerkit uudelleen (valinnainen).

Väritä lopuksi pisteet uudelleen, jotta ne sopivat kaavion tyyliin.

1. Napsauta mitä tahansa hiiren kakkospainikkeella Sarja "Sarja 2" tietolappu.
2. Klikkaa "Täyttää”-Painiketta.
3. Valitse väri näkyviin tulevasta paletista.

Poista myös pisteiden reunat:

1. Napsauta hiiren kakkospainikkeella samaa tietomerkkiä uudelleen ja valitse "Ääriviivat.”
2. Valitse "Ei ääriviivoja.”

Vaihe 12: Lisää pystysuorat viivat (valinnainen).

Lopuksi voit lisätä kaavioon pystysuoria viivoja korostaaksesi SD -arvoja.

• Valitse kaaviokaavio (tällä tavalla viivat lisätään suoraan kaavioon).
• Siirry kohtaan Lisää välilehti.
• Klikkaa "Muodot”-Painiketta.
• Valitse "Linja.”

Pidä "SIIRTÄÄ" vetämällä hiirtä vetääksesi täysin pystysuorat viivat jokaisesta pisteestä kohtaan, jossa jokainen viiva kohtaa kellokäyrän.

Muuta kaavion otsikkoa, ja parannettu kellokäyräsi näyttää arvokkaat jakelutietosi.

Ja näin teet sen. Voit nyt valita minkä tahansa tietojoukon ja luoda normaalijakaumakellon näiden yksinkertaisten ohjeiden mukaisesti!