Jak vytvořit zvonovou křivku normální distribuce v aplikaci Excel

Tento výukový program předvede, jak vytvořit křivku normální distribuční křivky ve všech verzích aplikace Excel: 2007, 2010, 2013 , 2016 a 2019.

Ve statistikách je zvonová křivka (známá také jako standardní normální rozdělení nebo Gaussova křivka) symetrický graf, který ilustruje tendenci dat ke shlukování kolem středové hodnoty nebo průměru v dané datové sadě.

Osa y představuje relativní pravděpodobnost výskytu dané hodnoty v datové sadě, zatímco osa x vykreslí samotné hodnoty v grafu a vytvoří zvonovitý tvar křivka, 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 zkoumat podrobněji.

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

V tomto kroku – podrobný výukový program se naučíte, jak vytvořit normální distribuční křivku v aplikaci Excel od základu:

nakreslete Gaussovu křivku, potřebujete vědět dvě věci:

• Průměr (známý také 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í distribuci – nebo v prosté angličtině, jak široká by měla být křivka. Například ve zvonové křivce zobrazené výše představuje jedna směrodatná odchylka průměru rozmezí mezi skóre zkoušky 53 a 85.

Čím nižší je SD, tím vyšší je křivka a menší vaše data budou 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í, znáte to podstatné, přejdeme od teorie k praxi.

Začínáme

Pro ilustraci předpokládejme, že máte testovací skóre 200 studentů a chcete je hodnotit „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 se vám dává průměr a hodnoty SD od začátku, ale pokud tomu tak není, můžete tyto hodnoty snadno spočítat v několika jednoduchých krocích. Pojďme se nejprve zabývat průměrem.

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

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

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

Krok 2: Najděte standardní odchylku.

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

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

Krok 3: Nastavení hodnot osy x pro křivku.

Graf v zásadě představuje obrovský počet intervalů ( pomyslete na ně jako na kroky) spojené řádkem k vytvoření hladké křivky.

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 řeknou je pravděpodobnost, že student získá toto skóre na zkoušce.

Technicky můžete zahrnout tolik intervalů, kolik chcete – nadbytečná data můžete později snadno vymazat úpravou měřítka vodorovné osy. Nezapomeňte vybrat rozsah, který bude zahrnovat tři standardní odchylky.

Začněme počítat po jedné (protože student nemůže nijak získat negativní skóre zkoušky) a přejdeme až k 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“ hodnota, která definuje první interval.
2. Přejděte na kartu Domů.
3. Ve skupině Úpravy vyberte možnost Vyplnit.
4. V části Série v ”Vyberte„ Sloupec. “
5. Do pole„ Hodnota kroku “zadejte„ 1. “ Tato hodnota určuje přírůstky, které se automaticky sčítají, dokud Excel nedosáhne posledního intervalu.
6. U hodnoty „Stop value“ zadejte „150“, což je hodnota posledního intervalu, a klikněte na „OK“. ”

Zázračně, 149 buněk ve sloupci E (E5: E153) byly vyplněny 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ě nebude tato technika fungovat.

Krok č. 4: Vypočítejte hodnoty normálního rozdělení pro každou hodnotu osy x.

Nyní najděte hodnoty normálního rozdělení – 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 tu práci, aby za vás provedl všechny tyto výpočty: funkce NORM.DIST.

Do buňky vpravo (F4) od váš první interval (E4):

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

Zamknete střední 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ě nastal čas sestavit křivku zvonu:

1. Vyberte libovolnou hodnotu v pomocné tabulce obsahující hodnoty osy x a y (E4: F153).
2. Přejít na kartu Vložit.
3. Klikněte na tlačítko „Vložit bodový (X, Y) nebo bublinový graf“.
4. Vyberte možnost „Bodový s hladkými čarami“.

Krok 6: Nastavení tabulky štítků.

Technicky vzato mít svou křivku zvonu. Ale bylo by těžké ji přečíst, protože by postrádala veškerá data, která by ji popisovala.

Udělejme normální distribuci informativní přidáním štítků ilustrujících všechny hodnoty standardní odchylky pod a nad průměr ( můžete je také použít k zobrazení z-skóre).

K tomu vytvořte ještě další pomocnou tabulku takto:

Nejprve zkopírujte Střední hodnotu (F1) vedle příslušné buňky ve sloupci X-Hodnota (I5).

Dále spočítejte hodnoty standardní odchylky pod průměr zadáním tohoto jednoduchého vzorce do buňky I4:

Jednoduše řečeno, vzorec odečte součet předchozích hodnot standardní odchylky 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 střední hodnotou pomocí zrcadlového vzorce:

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

Nakonec vyplňte hodnoty popisku osy y (J2: J8) 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. Klikněte pravým tlačítkem na graf grafu a vyberte možnost „Vybrat data“.

V dialogovém okně, které se zobrazí, vyberte možnost Přidat. “

Zvýrazněte příslušné rozsahy buněk z pomocné tabulky – I2: I8 pro„ Series X values “a J2 : J8 pro „Hodnoty řady Y“ – a klikněte na „OK“.

Krok 8: Změňte typ grafu řada štítků.

Dalším krokem je změna typu grafu nově přidané řady tak, aby se datové značky zobrazily jako tečky. Chcete-li to provést, klikněte pravým tlačítkem na graf grafu a vyberte možnost „Změnit Typ grafu. “

Dále vytvořte kombinovaný graf:

1. Přejděte na Kombinovaná karta.
2. U řady „Series2“ změňte „Typ grafu“ na „Scatter“.
   • Poznámka: Ujistěte se, že „Series1“ zůstává jako „Scatter with Smooth Lines“. Někdy to Excel změní, když vytvoříte kombinaci. Také se ujistěte, že „Series1“ není tlačen do sekundární osy – 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.

Upravte měřítko vodorovné osy na střed grafu na zvonové křivce. 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í:

• Přejděte na kartu Možnosti osy.
• Nastavte hodnotu Minimální hranice na „15.“
• Nastavte hodnotu Maximální hranice na „125“.

Rozsah měřítka osy můžete vyladit, jakkoli uznáte za vhodné, ale protože znáte rozsahy směrodatných odchylek, nastavte hodnoty Bounds trochu dále od každé ze svých třetích směrodatných odchylek, abyste zobrazili „ocas“ křivky .

Krok č. 10: Vložte a umístěte štítky vlastních dat.

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

Dále nahraďte výchozí štítky s 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ý datový štítek Series „Series2“.
2. Vyberte možnost „Format“ Datové štítky. “
3. V podokně úloh přejděte na kartu Možnosti štítku.
4. Zaškrtněte políčko„ X Value “.
5. Zrušte zaškrtnutí„ Y Value “ ”.
6. V části„ Poloha štítku “vyberte„ Nad “.

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

Krok č. 11: Přebarvení datových značek (volitelně).

Nakonec zbarvte tečky tak, aby se vešly do vašeho stylu grafu.

1. Klikněte pravým tlačítkem na libovolný datový štítek řady „Series2“.
2. Klikněte tlačítko „Vyplnit“.
3. Vyberte barvu ze zobrazené palety.

Také, r přesuňte hranice kolem teček:

1. Klikněte znovu pravým tlačítkem na stejnou datovou značku a vyberte možnost „Obrys“.
2. Vyberte možnost „Žádný obrys“.

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

Jako poslední úpravu můžete přidejte do grafu svislé čáry, abyste zdůraznili hodnoty SD.

• Vyberte graf grafu (tímto způsobem se čáry vloží přímo do grafu).
• Přejít na kartu Vložit.
• Klikněte na tlačítko „Tvary“.
• Vyberte „Řádek“.

Podržte klávesu „SHIFT“, zatímco tažením myši nakreslete dokonale svislé čáry od každé tečky k místu, 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 – zobrazuje vaše cenná distribuční data.

A tak to děláte. Nyní můžete vybrat libovolnou datovou sadu a vytvořit zvonovou křivku normální distribuce podle těchto jednoduchých kroků!