Jak vytvořit normální distribuční Bell křivku v aplikaci Excel

Tento tutoriál předvede, jak vytvořit normální distribuční křivku distribuce ve všech verzích aplikace Excel: 2007, 2010, 2013, 2016 a 2022.

Ve statistikách je zvonová křivka (také známá jako standardní normální distribuce nebo Gaussova křivka) symetrický graf, který ilustruje tendenci dat shlukovat se kolem středové hodnoty nebo průměru v daném souboru dat.

Osa y představuje relativní pravděpodobnost výskytu dané hodnoty v datové sadě, zatímco osa x vykresluje samotné hodnoty do grafu a vytváří křivku ve tvaru zvonu, odtud název.

Graf nám pomáhá analyzovat, zda je určitá hodnota součástí očekávané variace nebo je statisticky významná, a proto je třeba ji blíže prozkoumat.

Protože aplikace Excel nenabízí žádná integrovaná řešení, budete ji muset vykreslit sami. Proto jsme vyvinuli doplněk Chart Creator, nástroj, který vám umožňuje vytvářet pokročilé grafy aplikace Excel několika kliknutími.

V tomto podrobném kurzu se naučíte, jak vytvořit normální distribuční zvonovou křivku v Excelu od základů:

K vykreslení Gaussovy křivky potřebujete 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.
• Standardní odchylka (SD) měření. To definuje šíření vašich dat v normální distribuci-nebo v jednoduché angličtině, jak široká by křivka měla být. Například ve výše uvedené zvonové křivce jedna standardní odchylka průměru představuje rozsah mezi skóre vyšetření 53 a 85.

Čím nižší je SD, tím vyšší je křivka a tím méně se vaše data rozloží 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 v rámci jednoho SD od průměru, 95% ve dvou SD a 99,7% v rámci tři SD.

Nyní, když víte, co je podstatné, přejděme od teorie k praxi.

Začínáme

Pro ilustraci předpokládejme, že máte testovací skóre 200 studentů a chcete je ohodnotit „na křivce“, což znamená, že hodnocení studentů bude založeno na jejich relativním výkonu vůči zbytku třídy:

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

Obvykle jsou od začátku uvedeny průměrné hodnoty a hodnoty SD, ale pokud tomu tak není, můžete tyto hodnoty snadno vypočítat v několika jednoduchých krocích. Nejprve se zaměřme na průměr.

Protože průměr udává průměrnou hodnotu vzorku nebo populaci dat, můžete své standardní měření najít pomocí funkce PRŮMĚR.

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

1	= PRŮMĚR (B2: B201)

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

1	= KOLO (PRŮMĚR (B2: B201), 0)

Krok č. 2: Najděte standardní odchylku.

Jeden dolů, jeden pryč. Naštěstí má Excel speciální funkci, která za vás provede veškerou špinavou práci při hledání standardní odchylky:

1	= STDEV.P (B2: B201)

Vzorec opět vybere všechny hodnoty ze zadaného rozsahu buněk (B2: B201) a vypočítá jeho standardní odchylku-nezapomeňte také zaokrouhlit výstup.

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

Krok č. 3: Nastavte hodnoty osy x křivky.

Graf v zásadě představuje obrovský počet intervalů (uvažujte o nich jako o krocích) spojených dohromady čarou, která vytváří 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 libovolný počet intervalů-nadbytečná data můžete později snadno vymazat úpravou měřítka horizontální osy. Jen si vyberte rozsah, který bude zahrnovat tři standardní odchylky.

Začněme počítat od jednoho (protože neexistuje způsob, jak by student mohl získat záporné skóre) a pojďme až na 150-nezáleží na tom, zda je to 150 nebo 1 500-abychom vytvořili další pomocný stůl.

1. Vyberte libovolnou prázdnou buňku pod daty grafu (například E4) a napište “1,” hodnota, která definuje první interval.
2. Přejděte na Domov tab.
3. V Úpravy skupina, vyberte „Vyplnit.”
4. Pod „Série v,"Vyberte"Sloupec.”
5. Pro "Hodnota kroku,”Typ “1.” Tato hodnota určuje přírůstky, které se automaticky sčítají, dokud Excel nedosáhne posledního intervalu.
6. Pro "Stop hodnota,"Typ"150,” hodnota, která představuje poslední interval, a klikněte na „OK.”

Jako zázrakem 149 buněk ve sloupci E (E5: E153) byly vyplněny hodnotami od 2 do 150.

POZNÁMKA: Nezakrývejte původní datové buňky, jak je znázorněno na snímcích obrazovky. Jinak tato technika nebude fungovat.

Krok č. 4: Vypočítejte hodnoty normálního rozdělení 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 reprezentované konkrétní hodnotou osy x-pro každý z intervalů. Naštěstí pro vás má Excel tahoun, který všechny tyto výpočty provede za vás: funkce NORM.DIST.

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

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

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

1	= NORM.DIST ([první interval], [průměr (absolutní reference)], [standardní odchylka (absolutní reference), FALSE)

Uzamknete průměrné hodnoty a hodnoty SD, abyste mohli bez námahy provést vzorec pro zbývající intervaly (E5: E153).

Nyní poklepejte na úchyt 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ě nadešel čas na vybudování zvonové křivky:

1. Vyberte libovolnou hodnotu v pomocné tabulce obsahující hodnoty osy x a y (E4: F153).
2. Jděte na Vložit tab.
3. Klikněte na „Vložte bodový (X, Y) nebo bublinový graf" knoflík.
4. Vybrat "Scatter with Smooth Lines. “

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

Technicky máte svou křivku. Těžko by se to ale četlo, protože chybí jakákoli data, která by to popisovala.

Udělejme normální rozdělení informativnějším přidáním štítků znázorňujících všechny hodnoty směrodatné odchylky pod a nad průměrem (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 standardní odchylky pod průměrem zadáním tohoto jednoduchého vzorce do buňka I4:

1	= I5-$ F $ 2

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

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

1	= I5+$ F $ 2

Stejným způsobem spusťte vzorec pro další dvě buňky (I7: I8).

Nakonec vyplňte hodnoty štítku osy y (J2: J8) s nulami, jak chcete, aby datové značky byly umístěny na vodorovné ose.

Krok č. 7: Vložte data štítku do grafu.

Nyní přidejte všechna data, která jste připravili. Klikněte pravým tlačítkem na graf a vyberte „Vyberte Data.”

V dialogovém okně, které se objeví, vyberte „Přidat.”

Zvýrazněte příslušné rozsahy buněk z pomocné tabulky-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ěnit typ grafu nově přidané řady, aby se datové značky zobrazovaly jako tečky. Chcete-li to provést, klikněte pravým tlačítkem na graf a vyberte „Změnit typ grafu.”

Dále navrhněte kombinovaný graf:

1. Přejděte na Combo tab.
2. Pro Řada „Série 2“ změna "Typ grafu“Až„Rozptyl.”
   • Poznámka: Ujistěte se, že „Řada 1“Zůstává jako„Scatter with Smooth Lines. ” Někdy to Excel změní, když vytvoříte soubor Combo Také se ujistěte, “Řada 1”Není posunuto 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 zvonovou křivku úpravou měřítka horizontální osy. Klikněte pravým tlačítkem na vodorovnou osu a vyberte „Formátovat osu“Z nabídky.

Jakmile se zobrazí podokno úloh, proveďte následující:

• Jděte na Možnosti osy tab.
• Nastav Minimální hranice hodnota na “15.”
• Nastav Maximální hranice hodnota na “125.”

Rozsah stupnice osy můžete upravit, jak uznáte za vhodné, ale protože znáte rozsahy standardních odchylek, nastavte hodnoty Bounds trochu stranou od každé ze svých třetích standardních odchylek, aby se zobrazil „ocas“ křivky.

Krok č. 10: Vložte a umístěte vlastní popisky dat.

Při vylepšování grafu nezapomeňte přidat vlastní popisky dat. Nejprve klikněte pravým tlačítkem na libovolnou tečku představující Řada „Série 2“ a vyberte „Přidejte datové štítky.”

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

1. Klikněte pravým tlačítkem na libovolný Řada „Série 2“ datový štítek.
2. Vyberte „Formátovat datové štítky.”
3. V podokně úloh přepněte na Možnosti štítku tab.
4. Zkontrolovat "Hodnota X" krabice.
5. Zrušte zaškrtnutí políčka „Hodnota Y" krabice.
6. Pod „Pozice etikety," Vybrat "Výše.”

Nyní můžete mřížky také odebrat (klikněte na ně pravým tlačítkem> Odstranit).

Krok č. 11: Změňte barvu datových značek (volitelně).

Nakonec tečky přebarvěte, aby se vešly do vašeho stylu grafu.

1. Klikněte pravým tlačítkem na libovolný Řada „Série 2“ datový štítek.
2. Klikněte na „Vyplnit" knoflík.
3. Vyberte barvu ze zobrazené palety.

Také odstraňte ohraničení kolem teček:

1. Klikněte znovu pravým tlačítkem na stejnou značku dat a vyberte „Obrys.”
2. Vybrat "Žádný obrys.”

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

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

• Vyberte graf grafu (tímto způsobem budou čáry vloženy přímo do grafu).
• Jděte na Vložit tab.
• Klikněte na „Tvary" knoflík.
• Vybrat "Čára.”

Podržte "POSUN" klávesu a tažením myši nakreslete dokonale svislé čáry z každé tečky na místo, kde se každá čára setkává se zvonovou křivkou.

Změňte název grafu a vaše vylepšená křivka zvonku je připravena a zobrazí vaše cenná distribuční data.

A takhle to děláš. Nyní můžete vybrat libovolnou datovou sadu a podle těchto jednoduchých kroků vytvořit normální distribuční křivku!