Jak vytvořit zvonovou křivku v aplikaci Excel (podrobný průvodce)

Zvonková křivka (známá také jako křivka normálního rozdělení) je způsob, jak vykreslit a analyzovat data, která vypadají jako zvon křivka.

Ve zvonové křivce je nejvyšší bod ten, který má nejvyšší pravděpodobnost výskytu, a pravděpodobnost výskytu klesá na obou stranách křivky.

se často používá při hodnocení výkonu zaměstnanců nebo při hodnocení na zkouškách (někdy jste slyšeli – „Budete klasifikováni na křivce?“).

Teď, než skočím na to, jak vytvořit zvonovou křivku v aplikaci Excel, pojďme lépe porozumět konceptu na příkladu.

Porozumění Bell Curve

Předpokládejme, že pracujete v týmu 100 členů a váš manažer vám řekne, že váš výkon bude ve srovnání s ostatními a bude vyhodnocen na zvonové křivce.

To znamená, že i když je váš tým nejlepším týmem všech dob a jste všichni superhrdinové, nejlepší hodnocení by získala jen hrstka z vás z lidé ve vašem týmu by získali průměrné hodnocení a hrstka by získala nejnižší hodnocení.

Zdroj obrázku: EmpxTrack

Ale proč potřebujeme křivku zvonu?

Spravedlivá otázka!

Předpokládejme, že máte třídu 100 studentů, kteří objevit se na zkoušku. Podle vašeho systému známek dostane každý, kdo získá více než 80 ze 100, známku A. Ale protože jste nastavili opravdu snadný papír, každý skóroval nad 80 a dostal známku A.

Nyní se v tomto druhu klasifikačního systému nic neděje. Při jeho použití však nelze rozlišovat mezi někým, kdo dostal 81, a někým, kdo dostal 95 (protože oba by dostali známku A).

Chcete-li zachovat spravedlivé srovnání a udržovat živého soutěžního ducha, zvonek křivka se často používá k hodnocení výkonů (alespoň tak to bylo, když jsem byl na vysoké škole).

Pomocí přístupu zvonové křivky se známky studentů převádějí na percentily, které se pak navzájem porovnávají.

Studenti, kteří získávají vyšší známky, jsou na pravé straně křivky a studenti, kteří získávají nízké známky, jsou na levé straně křivky (přičemž většina studentů je uprostřed kolem průměrného skóre).

Nyní, abyste porozuměli zvonové křivce, potřebujete vědět o dvou metrikách:

• Průměr – průměrná hodnota všech datových bodů
• Standardní odchylka – ukazuje, jak datová sada se výrazně odchyluje od průměru datové sady. Předpokládejme například, že máte skupinu 50 lidí a zaznamenáváte jejich hmotnost (v kg). V tomto datovém souboru je průměrná hmotnost 60 kg a standardní odchylka 4 kg. To znamená, že 68% hmotnosti lidí je v rámci 1 standardní odchylky od průměru – což by bylo 56-64 kg. Podobně 95% lidí je ve 2 standardní odchylce – což by bylo 52–68 kg.

Pokud máte datovou sadu, která je normálně distribuována, bude se vaše křivka zvonku řídit níže uvedenými pravidly :

• Střed zvonové křivky je průměrem datového bodu (také nejvyšší bod ve zvonové křivce).
• 68,2% z celkových datových bodů leží v rozsahu (průměr – standardní odchylka od průměru + standardní odchylka).
• 95,5% z celkových datových bodů leží v rozsahu (průměr – 2 * standardní odchylka od průměru + 2 * standardní odchylka)
• 99,7% z celkových datových bodů leží v rozsahu (průměr – 3 * standardní odchylka od průměru + 3 * standardní odchylka)

Zdroj obrázku: Zprávy MIT

Nyní se podívejme, jak vytvořit zvonovou křivku v aplikaci Excel.

Vytvoření zvonové křivky v aplikaci Excel

Vezměme si příklad třídy studentů, kteří byli hodnoceni zkouškou.

Průměrné skóre třídy je 65 a standardní odchylka je 10. (Průměr můžete vypočítat pomocí funkce PRŮMĚR v aplikaci Excel a standardní odchylka pomocí funkce STDEV.P).

Zde jsou kroky k vytvoření křivky zvonu pro tuto datovou sadu:

• V buňce A1 zadejte 35. Tuto hodnotu lze vypočítat pomocí střední hodnoty – 3 * standardní odchylka (65-3 * 10).
• V buňce pod ní zadejte 36 a vytvořte série od 35 do 95 (kde 95 je střední hodnota + 3 * standardní odchylka). Můžete to udělat rychle pomocí možnosti automatického vyplňování, nebo použijte úchyt výplně a přetažením dolů vyplňte buňky.
• V buňce sousedící s 35 , zadejte vzorec: = NORM.DIST (A1,65,10, FALSE)
  • Všimněte si, že zde mám pevně zakódovanou hodnotu střední a standardní odchylky. Můžete je také mít v buňkách a použít odkazy na buňky ve vzorci.
• Znovu použijte rukojeť výplně k rychlému zkopírujte a vložte vzorec pro všechny buňky.
• Vyberte datovou sadu a přejděte na kartu Vložit.
• Vložit Rozptyl s grafem Smooth Lines.

Získáte tak křivku zvonu v aplikaci Excel.

Nyní můžete změnit název grafu a případně upravit osu.

Všimněte si, že když máte nízkou směrodatnou odchylku, dostanete zabalenou štíhlou křivku zvonu a když máte vysokou směrodatnou odchylku, křivka zvonu je široká a pokrývá více plochy v grafu.

Tento druh křivky zvonu lze použít k identifikaci, kde v grafu leží datový bod. Například v případě, že je tým plný vysoce výkonných hráčů, může někdo při hodnocení na křivce, přestože je vysoce výkonným, získat průměrné hodnocení, protože byl uprostřed křivky.

Poznámka: V tomto příspěvku na blogu jsem diskutoval o konceptu křivky zvonu a o tom, jak ji vytvořit v aplikaci Excel. Statistik by se lépe hodil hovořit o účinnosti zvonové křivky a omezeních s ní spojených. Jsem spíše chlápek z Excelu a moje zapojení do Bellovy křivky bylo omezeno na výpočty, které jsem dělal, když jsem pracoval jako finanční analytik.

Doufám, že vám tento návod připadal užitečný!

Sdělte mi své myšlenky v sekci komentářů.

Mohly by se vám také líbit následující výukové programy pro Excel:

• Jak vytvořit histogram v aplikaci Excel.
• Jak vypočítat složený úrok v aplikaci Excel + kalkulačka ZDARMA.
• Jak vytvořit teplotní mapu v aplikaci Excel.
• Krokový graf v aplikaci Excel.
• Jak vytvořit graf časové osy / milníku v aplikaci Excel.
• Vytvoření Paretova grafu v aplikaci Excel.
• Vytvoření výsečového grafu v aplikaci Excel
• Pokročilé grafy aplikace Excel.