✅ Jak vytvořit zvonovou křivku v aplikaci Excel (průvodce krok za krokem)

Obsah

Pochopení Bellovy křivky
Vytvoření Bellovy křivky v Excelu

Zvonková křivka (také známá jako normální distribuční křivka) je způsob, jak vykreslit a analyzovat data, která vypadají jako zvonová křivka.

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

Často se používá při hodnocení výkonu zaměstnanců nebo při hodnocení u zkoušek (už jste někdy slyšeli - „Budete na křivce známkovaní?“).

Nyní, než se vrhneme na to, jak vytvořit zvonovou křivku v Excelu, pojďme si lépe porozumět konceptu na příkladu.

Pochopení Bellovy křivky

Předpokládejme, že pracujete v týmu se 100 členy a váš manažer vám řekne, že váš výkon bude relativní vůči ostatním a bude hodnocen na zvonové křivce.

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

Zdroj obrázku: EmpxTrack

Ale proč potřebujeme zvonovou křivku?

Spravedlivá otázka!

Předpokládejme, že máte třídu 100 studentů, kteří se dostaví ke zkoušce. Podle vašeho systému hodnocení každý, kdo se dostane nad 80 ze 100, dostane známku A. Ale protože jste nastavili opravdu snadný papír, každý dosáhl skóre nad 80 a získal známku A.

Na tomto druhu systému hodnocení není nic špatného. Při jeho použití však nemůžete rozlišovat mezi někým, kdo získal 81, a někým, kdo získal 95 (protože oba by získali známku A).

Aby bylo srovnání férové a udržel naživu soutěžního ducha, často se k hodnocení výkonů používá zvonová křivka (alespoň tak to bylo, když jsem byl na vysoké škole).

Pomocí přístupu se zvonovou křivkou se známky studentů převedou na percentily, které se poté navzájem porovnají.

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

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

Znamenat - průměrná hodnota všech datových bodů
Standardní odchylka - ukazuje, jak moc se datová sada 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 této datové sadě je průměrná hmotnost 60 kg a standardní odchylka je 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í má 2 standardní odchylky - což by bylo 52 - 68 kg.

Když máte datovou sadu, která je normálně distribuována, bude se vaše křivka zvonění řídit následujícími pravidly:

Střed zvonové křivky je průměr datového bodu (také nejvyšší bod zvonové křivky).
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: MIT News

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

Vytvoření Bellovy křivky v Excelu

Ukažme si příklad na třídě studentů, kteří byli u zkoušky ohodnoceni.

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í zvonové křivky pro tuto datovou sadu:

Do buňky A1 zadejte 35. Tuto hodnotu lze vypočítat pomocí Průměr - 3* standardní odchylka (65-3*10).
Do buňky níže zadejte 36 a vytvořte řadu od 35 do 95 (kde 95 je průměr + 3* standardní odchylka). Můžete to udělat rychle pomocí možnosti automatického vyplňování nebo použít úchyt výplně a přetažením dolů vyplnit buňky.
Do buňky sousedící s 35 zadejte vzorec: = NORM.DIST (A1,65,10, FALSE)
- Všimněte si, že zde jsem pevně zakódoval hodnotu průměru a standardní odchylky. Můžete je mít také v buňkách a použít odkazy na buňky ve vzorci.
Opět použijte úchyt pro rychlé zkopírování a vložení vzorce pro všechny buňky.
Vyberte sadu dat a přejděte na kartu Vložit.
Vložte graf „Scatter with Smooth Lines“.

To vám v aplikaci Excel poskytne zvonovou křivku.

Nyní můžete změnit název grafu a v případě potřeby upravit osu.

Všimněte si toho, že když máte nízkou standardní odchylku, získáte zabalenou tenkou zvonovou křivku, a když máte vysokou standardní odchylku, zvonková křivka je široká a pokrývá větší plochu v grafu.

Tento druh zvonové křivky 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 týmů, může být při hodnocení na křivce někdo průměrně hodnocen, protože byl uprostřed křivky.

Poznámka: V tomto příspěvku na blogu jsem diskutoval o konceptu zvonové křivky a o tom, jak ji vytvořit v aplikaci Excel. Statistik by byl vhodnější mluvit o účinnosti zvonové křivky a omezení s ní spojených. Jsem spíše excelista a moje zapojení do Bellovy křivky se omezilo na výpočty, které jsem dělal, když jsem pracoval jako finanční analytik.

Doufám, že jste našli tento návod užitečný!

Dejte mi vědět své myšlenky v sekci komentáře.

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