✅ Як зробити криву дзвоника в Excel (Покроковий посібник)

Зміст

Розуміння кривої дзвоника
Створення кривої дзвінка в Excel

Крива дзвоника (також відома як крива нормального розподілу) - це спосіб побудови та аналізу даних, які виглядають як крива дзвоника.

На кривій дзвоника найвищою точкою є та, що має найбільшу ймовірність того, що це станеться, а ймовірність настання випадків опускається по обидва боки кривої.

Його часто використовують під час оцінювання результатів працівників або під час оцінювання на іспитах (коли -небудь чув - "Вас оцінять на кривій?").

Тепер, перш ніж я перейду до того, як створити криву дзвоника в Excel, давайте краще зрозуміємо цю концепцію, взявши приклад.

Розуміння кривої дзвоника

Припустимо, ви працюєте в команді з 100 членів, і ваш менеджер скаже вам, що ваші результати будуть відносними до інших і будуть оцінені за кривою дзвоника.

Це означає, що навіть якщо ваша команда є найкращою командою з усіх, і ви всі супергерої, лише кілька з вас отримають найвищий рейтинг, більшість людей у вашій команді отримають середній рейтинг, а жменька - найнижчий рейтинг.

Джерело зображення: EmpxTrack

Але навіщо нам крива дзвоника?

Справедливе питання!

Припустимо, у вас є 100 учнів, які приходять на іспит. Відповідно до вашої системи оцінювання, кожен, хто отримає понад 80 із 100, отримує оцінку А. Але оскільки ви склали дуже легкий папір, усі набрали вище 80 і отримали оцінку А.

Тепер у цій системі оцінювання немає нічого поганого. Однак, використовуючи його, ви не можете розрізняти когось, хто отримав 81, і когось, хто отримав 95 (оскільки обидва отримали б оцінку А).

Щоб зберегти порівняння справедливим і зберегти дух змагання, для оцінки виступів часто використовується крива дзвоника (принаймні так це було, коли я навчався в коледжі).

Використовуючи підхід кривої дзвінка, оцінки учнів перетворюються в процентилі, які потім порівнюються між собою.

Студенти, які отримують вищі оцінки, знаходяться праворуч від кривої, а студенти, які отримують низькі оцінки, - ліворуч від кривої (більшість студентів знаходяться посередині середнього балу).

Тепер, щоб зрозуміти криву дзвоника, вам потрібно знати про дві метрики:

Середнє - середнє значення всіх точок даних
Стандартне відхилення - він показує, наскільки набір даних відхиляється від середнього значення набору даних. Наприклад, припустимо, що у вас є група з 50 осіб, і ви записуєте їх вагу (у кг). У цьому наборі даних середня вага становить 60 кг, а стандартне відхилення - 4 кг. Це означає, що 68% ваги людей знаходиться в межах 1 стандартного відхилення від середнього значення - що склало б 56-64 кг. Так само 95% людей знаходяться в межах 2 стандартних відхилень - це буде 52-68 кг.

Якщо у вас є набір даних, який зазвичай розповсюджується, ваша крива дзвінка буде відповідати таким правилам:

Центр кривої дзвоника - це середнє значення точки даних (також найвища точка кривої дзвоника).
68,2% від загальної кількості даних лежать в діапазоні (середнє - стандартне відхилення до середнього + стандартне відхилення).
95,5% загальних точок даних лежать в діапазоні (середнє значення - 2*стандартне відхилення до середнього значення + 2*стандартне відхилення)
99,7% загальних точок даних лежать в діапазоні (середнє - 3*стандартне відхилення до середнього + 3*стандартне відхилення)

Джерело зображення: Новини MIT

Тепер давайте подивимося, як створити криву дзвоника в Excel.

Створення кривої дзвінка в Excel

Давайте візьмемо приклад класу студентів, які були оцінені на іспиті.

Середній бал класу дорівнює 65 і стандартне відхилення дорівнює 10. (Ви можете обчислити середнє значення, використовуючи функцію AVERAGE в Excel та Standard Deviation за допомогою функції STDEV.P).

Ось кроки для створення кривої дзвінка для цього набору даних:

У клітинку А1 введіть 35. Це значення можна обчислити за допомогою Середнє значення - 3* Стандартне відхилення (65-3*10).
У комірці під нею введіть 36 і створіть ряд від 35 до 95 (де 95 - середнє значення + 3* стандартне відхилення). Ви можете зробити це швидко за допомогою опції автозаповнення або скористатися маркером заповнення та перетягнути його вниз, щоб заповнити клітинки.
У комірку, прилеглу до 35, введіть формулу: = NORM.DIST (A1,65,10, FALSE)
- Зауважте, що тут я жорстко закодував значення середнього значення та стандартного відхилення. Ви також можете мати їх у клітинках та використовувати посилання на клітинки у формулі.
Знову скористайтесь маркером заповнення, щоб швидко скопіювати та вставити формулу для всіх клітинок.
Виберіть набір даних і перейдіть на вкладку Вставка.
Вставте діаграму "Розсіювання з плавними лініями".

Це дасть вам дзвінку криву в Excel.

Тепер ви можете змінити назву діаграми та відрегулювати вісь, якщо вам це потрібно.

Зауважте, що при низькому стандартному відхиленні ви отримуєте щільну криву тонкого дзвінка, а при високому стандартному відхиленні крива дзвінка широка і охоплює більшу площу на діаграмі.

Таку криву дзвоника можна використати для визначення місця розташування точки даних на діаграмі. Наприклад, якщо команда наповнена вищими виконавцями, при оцінці на кривій, незважаючи на те, що вона висока, хтось може отримати середній рейтинг, оскільки він/вона знаходився посередині кривої.

Примітка: У цій публікації в блозі я обговорював концепцію кривої дзвоника та як її створити в Excel. Статистику краще підійде для того, щоб розповісти про ефективність кривої дзвоника та обмеження, пов’язані з нею. Я більше схожий на Excel, і моя участь у кривій Белла обмежувалася розрахунками, які я робив, коли працював фінансовим аналітиком.

Сподіваюся, вам цей підручник був корисним!

Дайте мені знати ваші думки в розділі коментарів.

Вам також можуть сподобатися такі підручники Excel: