tento tutoriál ukáže, jak vytvořit normální křivku distribučního zvonu ve všech verzích aplikace Excel: 2007, 2010,2013, 2016 a 2019.

ve statistice je zvonová křivka (známá také jako standardní normální distribuce nebo Gaussova křivka)symetrický graf, který ilustruje tendenci dat shlukovat se kolem středové hodnoty nebo střední hodnoty v daném datovém souboru.

osa y představuje relativní pravděpodobnost, že se daná hodnota vyskytne v datovém souboru, zatímco osa x vykresluje hodnoty samotné na grafu, aby vytvořila křivku ve tvaru zvonu, odtud název.

graf nám pomáhá analyzovat, zda je určitá hodnota součástí očekávané odchylky nebo je statisticky významná, a proto je třeba ji podrobněji prozkoumat.

vzhledem k tomu, Excel nemá žádné vestavěné řešení nabídnout, budete muset plot sami. Proto jsme vyvinuli doplněk Chart Creator, nástroj, který vám umožní vytvářet pokročilé grafy aplikace Excel několika kliknutími.

v tomto podrobném tutoriálu se naučíte, jak vytvořit normální křivku distribučního zvonu v aplikaci Excel od základů:

Chcete-li vykreslit Gaussovu křivku, musíte vědět dvě věci:

• průměr (také známý jako standardní měření). To určuje střed křivky-což zase charakterizuje polohu křivky.
• směrodatná odchylka (SD) měření. To definuje šíření vašich dat v normálním rozdělení-nebo v prosté angličtině, jak široká by měla být křivka. Například ve výše uvedené křivce bell představuje jedna směrodatná odchylka průměru rozsah mezi skóre zkoušky 53 a 85.

čím nižší je SD, tím vyšší je křivka a tím méně budou vaše data rozložena a naopak.

za zmínku stojí pravidlo 68-95-99. 7, které lze použít na jakoukoli normální distribuční křivku, což znamená, že zhruba 68% vašich dat bude umístěno do jednoho SD od průměru, 95% do dvou SD a 99.7% do tří SD.

Nyní, když znáte základy, přejděme od teorie k praxi.

Začínáme

pro ilustraci předpokládejme, že máte výsledky testů 200 studentů a chcete je ohodnotit „na křivce“, což znamená, že známky studentů budou založeny na jejich relativním výkonu vůči zbytku třídy:

Krok č. 1: Najděte průměr.

obvykle dostanete střední a SD hodnoty od začátku, ale pokud tomu tak není, můžete tyto hodnoty snadno vypočítat v několika jednoduchých krocích. Pojďme nejprve řešit průměr.

protože průměr udává průměrnou hodnotu vzorku nebo populace dat, můžete najít standardní měření pomocí průměrné funkce.

zadejte následující vzorec do libovolné prázdné buňky (F1 v tomto příkladu) vedle vašich skutečných dat (sloupce a A B) pro výpočet průměru skóre zkoušky v datovém souboru:

rychlá poznámka: častěji než ne, možná budete muset zaokrouhlit výstup vzorce. Chcete-li to provést, jednoduše jej zabalte do kulaté funkce následujícím způsobem:

Krok #2: Najděte směrodatnou odchylku.

jeden dolů, jeden jít. Naštěstí má Excel speciální funkci, která dělá veškerou špinavou práci při hledání směrodatné odchylky pro vás:

Opět platí, že vzorec vybere všechny hodnoty ze zadaného rozsahu buněk (B2: B201)a vypočítá jeho směrodatnou odchylku—nezapomeňte také zaokrouhlit výstup.

Krok #3: nastavte hodnoty osy x pro křivku.

graf v podstatě představuje obrovský počet intervalů (považujte je za kroky) spojených dohromady s čarou a vytvořte hladkou křivku.

v našem případě budou hodnoty osy x použity k ilustraci konkrétního skóre zkoušky, zatímco hodnoty osy y nám řeknou pravděpodobnost, že student získá toto skóre na zkoušce.

technicky můžete zahrnout tolik intervalů, kolik chcete—redundantní data můžete snadno vymazat později úpravou stupnice vodorovné osy. Jen se ujistěte, že vyberete rozsah, který bude zahrnovat tři směrodatné odchylky.

začněme počítat v jednom (protože neexistuje žádný způsob, jak student může získat negativní skóre zkoušky) a jít celou cestu až 150 – nezáleží na tom, zda je to 150 nebo 1500-nastavit další pomocnou tabulku.

1. Vyberte libovolnou prázdnou buňku pod daty grafu (například E4) a zadejte“ 1″, hodnotu, která definuje první interval.
2. přejděte na kartu Domů.
3. v editační skupině zvolte “ vyplnit.“
4. ve sloupci“ řada V „vybrat“.“
5. pro „hodnotu kroku,“ typ “ 1.“Tato hodnota určuje přírůstky, které budou automaticky sčítány, dokud Excel nedosáhne posledního intervalu.
6. pro „Stop value“ zadejte „150“, hodnotu, která znamená poslední interval, a klikněte na “ OK.“

zázračně bylo 149 buněk ve sloupci E (E5:E153) vyplněno hodnotami od 2 do 150.

poznámka: Neskrývejte původní datové buňky, jak je znázorněno na screenshotech. V opačném případě technika nebude fungovat.

Krok č. 4: Vypočítejte normální distribuční hodnoty pro každou hodnotu osy x.

nyní najděte normální distribuční hodnoty-pravděpodobnost, že student získá určité skóre zkoušky představované konkrétní hodnotou osy x—pro každý z intervalů. Naštěstí pro vás má Excel pracovní kůň, který pro vás provede všechny tyto výpočty: norma. DIST funkce.

zadejte následující vzorec do buňky vpravo (F4) vašeho prvního intervalu (E4):

zde je dekódovaná verze, která vám pomůže odpovídajícím způsobem upravit:

uzamknete střední a SD hodnoty, abyste mohli bez námahy provést vzorec pro zbývající intervaly (E5: E153).

nyní poklepejte na popisovač výplně a zkopírujte vzorec do ostatních buněk (F5: F153).

Krok #5: Vytvořte bodový graf s hladkými čarami.

konečně nastal čas na vybudování zvonové křivky:

1. Vyberte libovolnou hodnotu v pomocné tabulce obsahující hodnoty osy x a y (E4: F153).
2. přejděte na kartu Vložit.
3. klikněte na tlačítko“ Vložit bodový (X, Y) nebo bublinový Graf“.
4. zvolte “ Scatter s hladkými liniemi.“

Krok #6: Nastavte tabulku štítků.

technicky máte zvonovou křivku. Ale bylo by to těžké číst, protože postrádá jakékoli údaje, které by to popisovaly.

udělejme normální rozdělení více informativní přidáním štítků zobrazujících všechny hodnoty směrodatné odchylky pod a nad střední hodnotou (můžete je také použít pro zobrazení Z-skóre).

za tímto účelem nastavte další pomocnou tabulku následujícím způsobem:

nejprve zkopírujte střední hodnotu (F1) vedle odpovídající buňky ve sloupci X-hodnota (I5).

dále Vypočítejte hodnoty směrodatné odchylky pod průměrem zadáním tohoto jednoduchého vzorce do buňky I4:

jednoduše řečeno, vzorec odečte součet předchozích hodnot směrodatné odchylky od průměru. Nyní přetáhněte rukojeť výplně nahoru a zkopírujte vzorec do zbývajících dvou buněk (I2:I3).

opakujte stejný postup pro standardní odchylky nad průměrem pomocí zrcadlového vzorce:

stejným způsobem proveďte vzorec pro další dvě buňky (I7:I8).

nakonec vyplňte hodnoty štítků osy y (J2:J8) s nulami, jak chcete datové značky umístěné na vodorovné ose.

Krok #7: vložte data štítku do grafu.

Nyní přidejte všechna data, která jste připravili. Klepněte pravým tlačítkem myši na graf grafu a zvolte „Vybrat Data.“

v dialogovém okně, které se objeví, vyberte “ Přidat.“

zvýrazněte příslušné rozsahy buněk z tabulky pomocníků-I2: I8 pro „hodnoty řady X“ a J2: J8 pro „hodnoty řady Y—-a klikněte na“ OK.“

Krok #8: změňte typ grafu řady štítků.

naším dalším krokem je změna typu grafu nově přidané řady, aby se datové značky zobrazovaly jako tečky. Chcete-li to provést, klepněte pravým tlačítkem myši na graf grafu a vyberte „Změnit typ grafu.“

dále navrhněte kombinovaný graf:

1. přejděte na kartu Combo.
2. pro sérii „Series2,“ změna „typ grafu“ na “ Scatter.“
   • Poznámka: Ujistěte se, že „Series1″ zůstává jako “ rozptyl s hladkými čarami.“Někdy Excel změní, když uděláte Combo také ujistěte se, že „Series1“ není tlačen na sekundární osu-zaškrtávací políčko vedle typu grafu by nemělo být označeno.
3. klikněte na “ OK.“

Krok č. 9: upravte měřítko vodorovné osy.

Vycentrujte graf na křivce zvonu úpravou stupnice vodorovné osy. Klepněte pravým tlačítkem myši na vodorovnou osu a v nabídce vyberte možnost „Formátovat osu“.

jakmile se objeví podokno úloh, proveďte následující kroky:

• přejděte na kartu Možnosti osy.
• nastavte minimální hodnotu mezí na “ 15.“
• Nastavte maximální hodnotu mezí na “ 125.“

rozsah stupnice osy můžete vyladit, jak uznáte za vhodné, ale protože znáte rozsahy směrodatných odchylek, nastavte hodnoty mezí trochu daleko od každé z vašich třetích směrodatných odchylek, abyste ukázali“ ocas “ křivky.

Krok # 10: Vložte a umístěte vlastní datové štítky.

při leštění grafu nezapomeňte přidat vlastní datové štítky. Nejprve klikněte pravým tlačítkem myši na libovolnou tečku představující řadu „Series2″ a vyberte “ Přidat štítky dat.“

poté nahraďte výchozí štítky těmi, které jste dříve nastavili, a umístěte je nad datové značky.

1. klepněte pravým tlačítkem myši na libovolný Datový štítek řady „Series2“.
2. Vyberte “ Formátovat Datové Štítky.“
3. v podokně úloh přepněte na kartu Možnosti štítku.
4. zaškrtněte políčko“ hodnota X“.
5. zrušte zaškrtnutí políčka“ hodnota Y“.
6. v části “ pozice štítku „vyberte“ výše.“

nyní můžete také odstranit mřížky (Klepněte pravým tlačítkem myši na ně > Smazat).

krok # 11: přebarvit datové značky (volitelné).

nakonec přebarvte tečky, abyste jim pomohli zapadnout do vašeho stylu grafu.

1. klepněte pravým tlačítkem myši na libovolný Datový štítek řady „Series2“.
2. klikněte na tlačítko“ Vyplnit“.
3. Vyberte barvu z zobrazené palety.

také odstraňte okraje kolem teček:

1. znovu klikněte pravým tlačítkem na stejnou značku dat a vyberte “ obrys.“
2. Zvolte “ Žádný Obrys.“

krok #12: Přidejte svislé čáry (volitelné).

jako konečnou úpravu můžete do grafu přidat svislé čáry, které pomohou zdůraznit hodnoty SD.

• vyberte graf grafu (tímto způsobem budou řádky vloženy přímo do grafu).
• přejděte na kartu Vložit.
• klikněte na tlačítko“ Tvary“.
• Zvolte “ Řádek.“

podržte klávesu „SHIFT“ při přetahování myši a nakreslete dokonale svislé čáry z každé tečky na místo, kde každá čára splňuje křivku zvonu.

změňte název grafu a vylepšená křivka zvonu je připravena-zobrazuje vaše cenné distribuční údaje.

a tak to uděláte. Nyní si můžete vybrat libovolnou datovou sadu a vytvořit normální křivku distribučního zvonu podle těchto jednoduchých kroků!