Normaalin jakelukellokäyrän luominen Excelissä

Tässä opetusohjelmassa kerrotaan, kuinka luodaan normaali jakelukellokäyrä kaikissa Excel-versioissa: 2007, 2010, 2013 , 2016 ja 2019.

Tilastossa kellokäyrä (tunnetaan myös nimellä normaali normaalijakauma tai Gaussin käyrä) on symmetrinen kaavio, joka kuvaa datan taipumusta klusteriin tietyn tietojoukon keskiarvon tai keskiarvon ympärillä.

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

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

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

Tässä vaiheessa- vaiheittainen opetusohjelma oppii luomaan normaalin jakelukellokäyrän Excelissä alusta alkaen:

piirrä Gaussin käyrä, sinun on tiedettävä kaksi asiaa:

• Keskiarvo (tunnetaan myös nimellä vakiomittaus). Tämä määrittää käyrän keskipisteen – mikä puolestaan kuvaa käyrän sijaintia.
• Mittausten keskihajonta (SD). Tämä määrittelee tietojesi leviämisen normaalijakaumassa – tai yksinkertaisella englannilla, kuinka leveän käyrän tulisi olla. Esimerkiksi edellä esitetyssä kellokäyrässä yksi keskiarvon keskihajonta edustaa vaihteluväliä 53 ja 85.

Mitä alhaisempi SD, sitä korkeampi käyrä ja sitä vähemmän tietosi hajautetaan ja päinvastoin.

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

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

Aloittaminen

Oletetaan oletettavasti, että sinulla on 200 opiskelijan testitulokset ja haluat luokitella ne käyrälle , Eli opiskelijoiden arvosanat perustuvat heidän suhteelliseen suoritukseensa muuhun luokkaan:

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 vain muutamalla yksinkertaisella vaiheella. Käsitellään ensin keskiarvoa.

Koska keskiarvo osoittaa otoksen tai tietopopulaation keskimääräisen arvon, löydät vakiomittauksesi AVERAGE-funktiolla.

Kirjoita seuraava kaava mihin tahansa tyhjään soluun (F1 tässä esimerkissä) todellisten tietojesi (sarakkeet A ja B) vieressä laskeaksesi tietojoukon tenttipisteiden keskiarvon:

Pikaohje : Useimmiten joudut ehkä pyöristämään kaavan tuloksen. Tee se yksinkertaisesti kääri se ROUND-funktioon seuraavasti:

Vaihe 2: Etsi keskihajonta.

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

Kaava valitsee jälleen kaikki arvot määritetyltä solualueelta (B2: B201) ja laskee sen keskihajonnan – älä unohda pyöristää ylöspäin myös lähtö.

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

Pohjimmiltaan kaavio muodostaa valtavan määrän aikavälejä ( ajattele niitä vaiheina), jotka on liitetty yhteen viivan kanssa sujuvan käyrän luomiseksi.

Tapauksessamme x-akselin arvoja käytetään kuvaamaan tiettyä tenttipistettä, kun taas y-akselin arvot kertovat meille todennäköisyys, että opiskelija saa kyseisen pisteen tentissä.

Teknisesti voit sisällyttää niin monta jaksoa kuin haluat – voit poistaa tarpeettomat tiedot vaivattomasti myöhemmin muokkaamalla vaaka-akselin asteikkoa. Varmista vain, että valitset alueen, joka sisältää kolme keskihajontaa.

Aloitetaan laskenta yhdellä (koska opiskelija ei voi mitenkään saada negatiivista tenttipistettä) ja mene aina ylöspäin 150 – sillä ei ole väliä onko 150 vai 1500 – uuden avustajataulukon asettaminen.

1. Valitse mikä tahansa tyhjä solu kaavion tietojen (kuten E4) alapuolelta ja kirjoita ”1”. arvo, joka määrittää ensimmäisen aikavälin.
2. Siirry Koti-välilehdelle.
3. Valitse Muokkaus-ryhmässä Täytä.
4. Kohdassa Sarja, ”Valitse” Sarake ”.
5. Kirjoita” Vaihe-arvo ”-kohtaan” 1. ” Tämä arvo määrittää lisäykset, jotka lisätään automaattisesti, kunnes Excel saavuttaa viimeisen aikavälin.
6. Kirjoita ”Stop-arvo” -kohtaan ”150”, viimeisen aikavälin arvo ja napsauta ”OK”. ”

Ihmeellisellä tavalla 149 solua sarakkeessa E (E5: E153) on täytetty arvoilla, jotka vaihtelevat välillä 2-150.

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

Vaihe 4: Laske normaalit jakauma-arvot jokaiselle x-akselin arvolle.

Etsi nyt normaalit jakauma-arvot – todennäköisyys, että opiskelija saa tietyn tenttipisteen, jota edustaa tietty x-akselin arvo —Kullekin aikavälille. Onneksi Excelillä on työhevonen tekemään kaikki nämä laskelmat puolestasi: NORM.DIST-funktio.

Kirjoita seuraava kaava solun oikealle puolelle (F4). ensimmäinen väli (E4):

Tässä on dekoodattu versio, jonka avulla voit säätää vastaavasti:

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

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

Vaihe 5: Luo sirontakaavio tasaisilla viivoilla.

Lopuksi kellokäyrän muodostamisen aika on tullut:

1. Valitse mikä tahansa arvo aputaulukosta, joka sisältää x- ja y-akselin arvot (E4: F153).
2. Siirry Lisää-välilehdelle.
3. Napsauta Lisää sironta (X, Y) tai kuplakaavio -painiketta.
4. Valitse Hajota tasaisilla viivoilla.

Vaihe 6: Määritä tarrataulukko.

Teknisesti sinä sinulla on kellokäyräsi. Mutta sitä olisi vaikea lukea, koska siltä puuttuu sitä kuvaavat tiedot.

Tehdään normaalijakauma informatiivisemmaksi lisäämällä tarrat, jotka kuvaavat kaikkia keskihajontan arvoja keskiarvon alapuolella ja yläpuolella ( Voit myös käyttää niitä z-pisteiden näyttämiseen sen sijaan).

Luo sitä varten toinen aputaulukko seuraavasti:

Kopioi ensin keskiarvo (F1) vastaavan solun viereen sarakkeessa X-Value (I5).

Laske seuraavaksi keskiarvon alapuolella olevat keskihajonta-arvot syöttämällä tämä yksinkertainen kaava soluun I4:

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

Toista sama prosessi keskiarvoa suuremmille keskihajonnoille peilikaavan avulla:

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

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

Vaihe 7: Lisää tarratiedot kaavioon.

Lisää nyt kaikki valmistamasi tiedot. Napsauta hiiren kakkospainikkeella kaavion kuvaajaa ja valitse ”Valitse tiedot”.

Valitse avautuvassa valintaikkunassa ” Lisää. ”

Korosta vastaavat taulukoiden alueet – I2: I8 sarjassa X-arvot ja J2 : J8 sarjalle Y-arvot – ja napsauta OK.

Vaihe # 8: Vaihda kaavion tyyppi tarrasarja.

Seuraava askel on muuttaa äskettäin lisätyn sarjan kaaviotyyppi, jotta tietomerkit näkyvät pisteinä. Voit tehdä tämän napsauttamalla hiiren kakkospainikkeella kaaviokuvaa ja valitsemalla ”Muuta Kaavion tyyppi. ”

Suunnittele seuraavaksi yhdistelmikaavio:

1. siirry Yhdistelmä-välilehti.
2. Muuta sarjassa ”Sarja2” ”Kaavion tyyppi” -vaihtoehdoksi ”Hajonta”.
   • Huomaa: Varmista, että ”Sarja1” pysyy ”Hajottaa tasaisilla viivoilla”. Joskus Excel muuttaa sitä, kun teet yhdistelmän. Varmista myös, että ”Series1” ei ole työnnetty toissijaiseen akseliin – kaaviotyypin vieressä olevaa valintaruutua ei tule merkitä.
3. Napsauta ”OK”.

Vaihe # 9: Muuta vaaka-akselin asteikkoa.

Keskitä kaavio kellokäyrälle säätämällä vaaka-akselin asteikkoa. Napsauta hiiren kakkospainikkeella vaaka-akselia ja valitse ”Alusta akseli” valikosta.

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

• Siirry Akseliasetukset-välilehdelle.
• Aseta vähimmäisraja-arvoksi ”15.”
• aseta enimmäisraja-arvoksi ”125”.

Voit säätää akselivälin aluetta haluamallasi tavalla, mutta koska tiedät standardipoikkeama-alueet, aseta Bounds-arvot hieman pois jokaisesta kolmannesta standardipoikkeamastasi käyrän ”pyrstön” näyttämiseksi. .

Vaihe # 10: Lisää ja aseta mukautetut tietotarrat.

Varmista, että kiillotat kaaviota lisätäksesi mukautetut tietotarrat. Napsauta ensin hiiren kakkospainikkeella mitä tahansa sarjaa ”Series2” edustavaa pistettä ja valitse ”Lisää tietotarrat”.

Korvaa seuraavaksi oletustarrat aiemmin asettamiesi tunnisteiden kanssa ja sijoita ne tietomerkkien yläpuolelle.

1. Napsauta hiiren kakkospainikkeella mitä tahansa sarjan ”Series2” -tietotunnistetta.
2. Valitse ”Muotoile” Data Labels. ”
3. Vaihda tehtäväpaneelissa Label Options -välilehdelle.
4. Valitse X-arvo-ruutu.
5. Poista valinta Y-arvosta ”-Ruutu.
6. Valitse Tunnisteen sijainti -kohdasta Yllä.

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

Vaihe # 11: Väritä tietomerkit (valinnainen).

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

1. Napsauta hiiren kakkospainikkeella mitä tahansa Sarjan ”Series2” -tunnistetta.
2. Napsauta ”Täytä” -painike.
3. Valitse väri näkyviin tulevasta paletista.

Myös r poistaa reunat pisteiden ympärillä:

1. Napsauta hiiren kakkospainikkeella samaa tietomerkintää uudelleen ja valitse ”Outline”.
2. Valitse ”No Outline”.

Vaihe # 12: Lisää pystysuoria viivoja (valinnainen).

Viimeisenä säätönä voit lisää pystysuoria viivoja kaavioon SD-arvojen korostamiseksi.

• Valitse kaavio (siten linjat lisätään suoraan kaavioon).
• Siirry Lisää-välilehteen.
• Napsauta Muodot-painiketta.
• Valitse Viiva.

Pidä SHIFT-näppäintä painettuna vetämällä hiirtä piirtämään täydellisesti pystysuorat viivat kustakin pisteestä kohtaan, jossa kukin viiva kohtaa kellokäyrän.

Muuta kaavion otsikkoa , ja parannettu soittokäyräsi on valmis – näytät arvokkaat jakelutiedot.

Ja niin teetkin. Voit nyt valita minkä tahansa tietojoukon ja luoda normaalin jakelukellokäyrän näiden yksinkertaisten ohjeiden mukaisesti!