Ako vytvoriť normálnu distribučnú Bell krivku v programe Excel

Tento tutoriál vám ukáže, ako vytvoriť normálnu distribučnú krivku vo všetkých verziách programu Excel: 2007, 2010, 2013, 2016 a 2022.

V štatistike je zvonová krivka (známa tiež ako štandardná normálna distribúcia alebo Gaussova krivka) symetrický graf, ktorý ilustruje tendenciu zoskupovania údajov okolo stredovej hodnoty alebo priemeru v danom súbore údajov.

Os y predstavuje relatívnu pravdepodobnosť, že sa daná hodnota vyskytne v súbore údajov, zatiaľ čo os x vykreslí samotné hodnoty do grafu a vytvorí zvoncovú krivku, odtiaľ pochádza názov.

Graf nám pomáha analyzovať, či je konkrétna hodnota súčasťou očakávanej variácie alebo je štatisticky významná, a preto sa musí podrobnejšie preskúmať.

Pretože program Excel nemá žiadne vstavané riešenia, budete ho musieť vykresliť sami. Preto sme vyvinuli doplnok Chart Creator, nástroj, ktorý vám umožňuje vytvárať pokročilé grafy programu Excel niekoľkými kliknutiami.

V tomto podrobnom návode sa naučíte, ako vytvoriť normálnu krivku distribúcie zvonenia v programe Excel od základov:

Na vykreslenie Gaussovej krivky potrebujete vedieť dve veci:

• Význam (tiež známe ako štandardné meranie). Toto určuje stred krivky-ten zase charakterizuje polohu krivky.
• Štandardná odchýlka (SD) meraní. Toto definuje šírenie vašich údajov v normálnej distribúcii-alebo v jednoduchej angličtine-aká široká by mala byť krivka. Napríklad vo vyššie uvedenej zvonovej krivke jedna štandardná odchýlka priemeru predstavuje rozsah medzi skóre vyšetrenia 53 a 85.

Čím nižší je SD, tým vyššia je krivka a tým menej údajov sa rozložia a naopak.

Za zmienku stojí pravidlo 68-95-99,7, ktoré je možné použiť na akúkoľvek normálnu distribučnú krivku, čo znamená, že zhruba 68% vašich údajov bude umiestnených v rámci jedného SD mimo priemeru, 95% v rámci dvoch SD a 99,7% v rámci tri SD.

Teraz, keď viete podstatné, prejdeme od teórie k praxi.

Začíname

Na ilustračné účely predpokladajme, že máte testovacie skóre 200 študentov a chcete ich ohodnotiť „na krivke“, čo znamená, že známky študentov budú založené na ich relatívnom výkone voči zvyšku triedy:

Krok č. 1: Nájdite priemer.

Priemerné hodnoty a hodnoty SD sú zvyčajne uvedené od začiatku, ale ak tomu tak nie je, tieto hodnoty môžete ľahko vypočítať v niekoľkých jednoduchých krokoch. Najprv sa zamerajme na priemer.

Pretože priemer označuje priemernú hodnotu vzorky alebo súboru dát, môžete svoje štandardné meranie nájsť pomocou funkcie PRIEMER.

Do ľubovoľnej prázdnej bunky zadajte nasledujúci vzorec (F1 v tomto prípade) vedľa vašich skutočných údajov (stĺpce A a B) na výpočet priemeru skóre skúšky v súbore údajov:

1	= PRIEMERNÉ (B2: B201)

Rýchla poznámka: častejšie ako často budete musieť zaokrúhliť výstup vzorca. Ak to chcete urobiť, jednoducho ho zabaľte do funkcie ROUND takto:

1	= KOLO (PRIEMERNE (B2: B201), 0)

Krok č. 2: Nájdite štandardnú odchýlku.

Jeden dolu, jeden do odchodu. Našťastie má Excel špeciálnu funkciu, ktorá za vás urobí všetku špinavú prácu pri hľadaní štandardnej odchýlky:

1	= STDEV.P (B2: B201)

Vzorec opäť vyberie všetky hodnoty zo zadaného rozsahu buniek (B2: B201) a vypočíta svoju štandardnú odchýlku-nezabudnite tiež zaokrúhliť výstup.

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

Krok č. 3: Nastavte hodnoty osi x krivky.

Graf v zásade predstavuje obrovský počet intervalov (uvažujte o nich ako o krokoch) spojených čiarou na vytvorenie hladkej krivky.

V našom prípade budú hodnoty osi x použité na ilustráciu konkrétneho skóre skúšky, zatiaľ čo hodnoty osi y nám povedia pravdepodobnosť, že študent získa toto skóre na skúške.

Technicky môžete zahrnúť ľubovoľný počet intervalov-nadbytočné údaje môžete neskôr bez námahy vymazať úpravou mierky horizontálnej osi. Uistite sa, že vyberiete rozsah, ktorý bude zahŕňať tri štandardné odchýlky.

Začnime počítať od jedného (pretože neexistuje spôsob, akým môže študent získať záporné skóre z testu) a prejdeme až na 150-nezáleží na tom, či je to 150 alebo 1 500-na zriadenie ďalšej pomocnej tabuľky.

1. Vyberte ľubovoľnú prázdnu bunku pod údajmi grafu (napríklad E4) a napíšte “1,” hodnota, ktorá definuje prvý interval.
2. Prejdite na Domov tab.
3. V Úpravy skupina, zvoľte „Vyplniť.”
4. V časti „Séria v,“Vyberte„Stĺpec.”
5. Pre “Kroková hodnota,”Typ “1.” Táto hodnota určuje prírastky, ktoré sa budú automaticky sčítavať, kým Excel nedosiahne posledný interval.
6. Pre “Stop hodnota,"Typ"150,” hodnota, ktorá predstavuje posledný interval, a kliknite na „OK.”

Zázračne 149 buniek v stĺpci E (E5: E153) boli naplnené hodnotami od 2 do 150.

POZNÁMKA: Neskrývajte pôvodné bunky údajov, ako je to znázornené na snímkach obrazovky. V opačnom prípade technika nebude fungovať.

Krok č. 4: Vypočítajte hodnoty normálneho rozdelenia pre každú hodnotu osi x.

Teraz nájdite normálne distribučné hodnoty-pravdepodobnosť, že študent získa určité skóre z testu reprezentované konkrétnou hodnotou osi x-pre každý z intervalov. Našťastie pre vás má Excel pracovného koňa, ktorý všetky tieto výpočty vykoná za vás: funkcia NORM.DIST.

Do bunky napravo zadajte nasledujúci vzorec (F4) vášho prvého intervalu (E4):

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

Tu je dekódovaná verzia, ktorá vám pomôže zodpovedajúcim spôsobom sa prispôsobiť:

1	= NORM.DIST ([prvý interval], [priemer (absolútna referencia)], [štandardná odchýlka (absolútna referencia), FALSE)

Priemerné hodnoty a hodnoty SD uzamknete, aby ste mohli bez problémov vykonať vzorec pre zostávajúce intervaly (E5: E153).

Teraz dvakrát kliknite na rukoväť výplne a skopírujte vzorec do zvyšných buniek (F5: F153).

Krok č. 5: Vytvorte bodový graf s hladkými čiarami.

Nakoniec nastal čas na vybudovanie zvonovej krivky:

1. Vyberte ľubovoľnú hodnotu v pomocnej tabuľke obsahujúcej hodnoty osí x a y (E4: F153).
2. Choďte do Vložiť tab.
3. Kliknite na „Vložte bodový (X, Y) alebo bublinový graf”Tlačidlo.
4. Vyberte „Rozptyl hladkými čiarami. “

Krok č. 6: Nastavte tabuľku štítkov.

Technicky máte svoju zvonovú krivku. Bolo by však ťažké ho prečítať, pretože chýbajú údaje, ktoré by ho popisovali.

Urobme normálnu distribúciu informatívnejšou pridaním štítkov ilustrujúcich všetky hodnoty štandardnej odchýlky pod a nad priemerom (môžete ich použiť aj na zobrazenie z-skóre).

Za týmto účelom nastavte ďalšiu pomocnú tabuľku nasledovne:

Najprv skopírujte priemernú hodnotu (F1) vedľa zodpovedajúcej bunky v stĺpci X-hodnota (I5).

Potom vypočítajte hodnoty štandardnej odchýlky pod priemerom zadaním tohto jednoduchého vzorca do bunka I4:

1	= I5-$ F $ 2

Jednoducho povedané, vzorec odpočíta súčet predchádzajúcich hodnôt štandardnej odchýlky od priemeru. Teraz potiahnutím rukoväte výplne skopírujte vzorec do zvyšných dvoch buniek (I2: I3).

Opakujte ten istý postup pre štandardné odchýlky nad priemerom pomocou zrkadlového vzorca:

1	= I5+$ F $ 2

Rovnakým spôsobom spustite vzorec pre ďalšie dve bunky (I7: I8).

Nakoniec vyplňte hodnoty menovky osi y (J2: J8) s nulami, ako chcete, aby dátové značky boli umiestnené na vodorovnej osi.

Krok č. 7: Vložte údaje štítka do grafu.

Teraz pridajte všetky údaje, ktoré ste pripravili. Pravým tlačidlom myši kliknite na graf a zvoľte „Vyberte položku Údaje.”

V dialógovom okne, ktoré sa otvorí, zvoľte „Pridať.”

Zvýraznite príslušné rozsahy buniek z pomocnej tabuľky-I2: I8 pre “Hodnoty radu X“A J2: J8 pre “Hodnoty radu Y “-a kliknite na „OK.”

Krok č. 8: Zmeňte typ grafu série štítkov.

Našim ďalším krokom je zmeniť typ grafu novo pridanej série tak, aby sa značky údajov zobrazovali ako bodky. Ak to chcete urobiť, kliknite pravým tlačidlom myši na graf a zvoľte „Zmeňte typ grafu.”

Ďalej navrhnite kombinovaný graf:

1. Prejdite na Combo tab.
2. Pre Séria „Séria 2“ zmeniť “Typ grafu“Až„Rozptyl.”
   • Poznámka: Uistite sa, že „Séria 1“Zostáva ako„Rozptyl hladkými čiarami. ” Niekedy to Excel zmení, keď urobíte súbor Combo Tiež sa uistite, že „Séria 1”Nie je posunuté do sekundárnej osi-začiarkavacie políčko vedľa typu grafu by nemalo byť označené.
3. Kliknite na „OK.”

Krok č. 9: Upravte mierku horizontálnej osi.

Vycentrujte graf na zvonovú krivku úpravou mierky horizontálnej osi. Kliknite pravým tlačidlom myši na vodorovnú os a zvoľte „Formát osi“Z ponuky.

Hneď ako sa zobrazí panel úloh, postupujte takto:

• Choďte do Možnosti osi tab.
• Nastaviť Minimálne hranice hodnota pre “15.”
• Nastaviť Maximálne hranice hodnota pre “125.”

Rozsah mierky osi môžete vyladiť podľa vlastného uváženia, ale keďže poznáte rozsahy štandardných odchýlok, nastavte hodnoty Bounds trochu ďalej od každej zo svojich tretích štandardných odchýlok, aby sa ukázal „chvost“ krivky.

Krok č. 10: Vložte a umiestnite štítky vlastných údajov.

Pri vylepšovaní grafu nezabudnite pridať vlastné štítky s údajmi. Najprv kliknite pravým tlačidlom myši na ľubovoľnú bodku, ktorá predstavuje Séria „Séria 2“ a zvoľte „Pridajte štítky s údajmi.”

Potom nahraďte predvolené štítky tými, ktoré ste predtým nastavili, a umiestnite ich nad značky údajov.

1. Kliknite pravým tlačidlom myši na ľubovoľný Séria „Séria 2“ dátový štítok.
2. Vyberte „Formátovanie dátových štítkov.”
3. Na paneli úloh prepnite na Možnosti štítku tab.
4. Skontrolovať "X hodnota”Box.
5. Zrušte začiarknutie políčka „Hodnota Y”Box.
6. V časti „Poloha štítku,"Vybrať"Hore.”

Teraz môžete tiež odstrániť mriežky (kliknite na ne pravým tlačidlom myši> Odstrániť).

Krok č. 11: Prefarbite značky údajov (voliteľné).

Nakoniec bodky znova zafarbite, aby im to zodpovedalo štýlu grafu.

1. Kliknite pravým tlačidlom myši na ľubovoľný Séria „Séria 2“ dátový štítok.
2. Kliknite na „Vyplniť”Tlačidlo.
3. Vyberte farbu zo zobrazenej palety.

Odstráňte tiež okraje okolo bodiek:

1. Opäť kliknite pravým tlačidlom myši na rovnakú značku údajov a zvoľte „Obrys.”
2. Vyberte „Žiadny obrys.”

Krok č. 12: Pridajte zvislé čiary (voliteľné).

Ako poslednú úpravu môžete do grafu pridať zvislé čiary, ktoré pomôžu zdôrazniť hodnoty SD.

• Vyberte diagram grafu (týmto spôsobom sa riadky vložia priamo do grafu).
• Choďte do Vložiť tab.
• Kliknite na „Tvary”Tlačidlo.
• Vyberte „Riadok.”

Podržte stlačené „SHIFT“ pri ťahaní myši nakreslite perfektne zvislé čiary z každého bodu na miesto, kde sa každý riadok stretáva so zvonovou krivkou.

Zmeňte názov grafu a vylepšená zvonová krivka bude zobrazovať vaše cenné údaje o distribúcii.

A takto to robíte. Teraz môžete vybrať ľubovoľný súbor údajov a vytvoriť normálnu distribučnú krivku podľa týchto jednoduchých krokov!