Як створити звичайну криву дзвінка розподілу в Excel

Цей підручник продемонструє, як створити звичайну криву дзвінка розподілу у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022 роки.

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

Вісь y представляє відносну ймовірність того, що задане значення трапиться в наборі даних, тоді як вісь x наносить самі значення на діаграму, щоб створити криву у формі дзвіночка, звідси і назва.

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

Оскільки в Excel немає жодних вбудованих рішень, вам доведеться планувати це самостійно. Ось чому ми розробили надбудову Chart Creator-інструмент, який дозволяє створювати розширені діаграми Excel всього за кілька кліків.

У цьому покроковому посібнику ви дізнаєтесь, як створити нормальну криву дзвіночка розподілу в Excel з нуля:

Щоб побудувати криву Гаусса, вам потрібно знати дві речі:

• Значення (також відомий як стандартне вимірювання). Це визначає центр кривої, що, у свою чергу, характеризує положення кривої.
• Стандартне відхилення (SD) вимірювань. Це визначає розповсюдження ваших даних у звичайному розповсюдженні-або простою англійською, наскільки широкою має бути крива. Наприклад, на кривій дзвінка, показаній вище, одне стандартне відхилення середнього значення становить діапазон між балами іспитів 53 і 85.

Чим нижче SD, тим вища крива і тим менше ваші дані будуть розкидані, і навпаки.

Варто згадати правило 68-95-99,7, яке можна застосувати до будь-якої нормальної кривої розподілу, тобто приблизно 68% ваших даних буде розміщено в межах однієї SD від середньої, 95% у двох SD і 99,7% у межах три SD.

Тепер, коли ви знаєте основи, переходимо від теорії до практики.

Починаємо

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

Крок №1: Знайдіть середнє значення.

Як правило, вам від початку даються середні значення та значення SD, але якщо це не так, ви можете легко обчислити ці значення всього за кілька простих кроків. Давайте спочатку розберемося із середнім.

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

Введіть таку формулу у будь -яку порожню клітинку (F1 у цьому прикладі) поруч із вашими фактичними даними (стовпці А та B) для обчислення середніх балів іспитів у наборі даних:

1	= СЕРЕДНЯ (B2: B201)

Коротка примітка: найчастіше вам може знадобитися округлити вихід формули. Для цього просто оберніть його функцією ROUND наступним чином:

1	= КРУГЛИЙ (СЕРЕДНЯ (B2: B201), 0)

Крок 2: Знайдіть стандартне відхилення.

Один вниз, один - піти. На щастя, у Excel є спеціальна функція, яка виконує всю брудну роботу з пошуку стандартного відхилення для вас:

1	= STDEV.P (B2: B201)

Знову ж таки, формула вибирає всі значення із зазначеного діапазону комірок (В2: В201) і обчислює його стандартне відхилення-просто не забудьте також округлити результат.

1	= КРУГЛИЙ (STDEV.P (B2: B201), 0)

Крок #3: Встановіть значення осі x для кривої.

По суті, діаграма складається з величезної кількості інтервалів (подумайте про них як про кроки), з'єднаних разом з лінією, щоб створити гладку криву.

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

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

Давайте почнемо підрахунок на одиницю (оскільки студент не може отримати негативний результат іспиту) і підійдемо до 150-насправді не важливо, чи це 150 чи 1500-щоб створити іншу таблицю помічників.

1. Виберіть будь -яку порожню клітинку під даними діаграми (наприклад, E4) і тип “1,” значення, що визначає перший інтервал.
2. Перейдіть до Додому вкладка.
3. В Редагування групи, виберіть "Заповнити.”
4. Під "Серія в,"Виберіть"Колонка.”
5. За “Крок значення,”Типу “1.” Це значення визначає кроки, які будуть автоматично додаватися, поки Excel не досягне останнього інтервалу.
6. За “Стоп -значення,"Тип"150,” значення, яке позначає останній інтервал, і натисніть «В ПОРЯДКУ.”

Дивом, 149 клітин у стовпці E (E5: E153) були заповнені значеннями від 2 до 150.

ПРИМІТКА. Не приховуйте вихідні клітинки даних, як показано на скріншотах. В іншому випадку техніка не підійде.

Крок #4: Обчисліть нормальні значення розподілу для кожного значення осі x.

Тепер знайдіть нормальні значення розподілу-ймовірність того, що студент отримає певний бал іспиту, представлений певним значенням осі x-для кожного з інтервалів. На щастя для вас, Excel має робочого коня, який може виконати всі ці розрахунки за вас: функція NORM.DIST.

Введіть таку формулу у клітинку праворуч (F4) вашого першого інтервалу (E4):

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

Ось декодована версія, яка допоможе вам відповідно налаштуватись:

1	= NORM.DIST ([перший інтервал], [середнє значення (абсолютний відлік)], [стандартне відхилення (абсолютний відлік), FALSE)

Ви блокуєте середнє значення та значення SD, щоб без зусиль виконати формулу для інших інтервалів (E5: E153).

Тепер двічі клацніть на маркері заповнення, щоб скопіювати формулу в інші клітинки (F5: F153).

Крок №5: Створіть діаграму розсіювання з плавними лініями.

Нарешті, настав час побудови кривої дзвоника:

1. Виберіть будь-яке значення в допоміжній таблиці, що містить значення осі x та y (E4: F153).
2. Перейдіть до Вставити вкладка.
3. Натисніть кнопку «Вставте розсіяну (X, Y) або бульбашкову діаграму”.
4. Виберіть "Розсіювання з плавними лініями ».

Крок 6: Налаштуйте таблицю міток.

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

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

Для цього створіть ще одну допоміжну таблицю таким чином:

Спочатку скопіюйте середнє значення (F1) біля відповідної клітинки у стовпці X-значення (I5).

Далі обчисліть значення стандартного відхилення нижче середнього, ввівши цю просту формулу комірка I4:

1	= I5- $ F $ 2

Простіше кажучи, формула віднімає суму попередніх значень стандартного відхилення від середнього значення. Тепер перетягніть маркер заповнення вгору, щоб скопіювати формулу в дві інші клітинки (I2: I3).

Повторіть той самий процес для стандартних відхилень вище середнього за допомогою дзеркальної формули:

1	= I5+$ F $ 2

Точно так само виконайте формулу для двох інших клітинок (I7: I8).

Нарешті, заповніть значення мітки осі Y (J2: J8) з нулями, як ви хочете, щоб маркери даних розміщувалися на горизонтальній осі.

Крок №7: Вставте дані мітки в діаграму.

Тепер додайте всі дані, які ви підготували. Клацніть правою кнопкою миші на діаграмі діаграми та виберіть "Виберіть Дані.”

У діалоговому вікні, що з'явиться, виберіть "Додати.”

Виділіть відповідні діапазони клітинок із допоміжної таблиці-I2: I8 за “Значення серії X”Та J2: J8 за “Значення серії Y »-і натисніть "В ПОРЯДКУ.”

Крок #8: Змініть тип діаграми серії міток.

Наступний наш крок-змінити тип діаграми щойно доданої серії, щоб маркери даних виглядали як крапки. Для цього клацніть правою кнопкою миші на графіку діаграми та виберіть "Змінити тип діаграми.”

Далі створіть комбіновану діаграму:

1. Перейдіть до Комбо вкладка.
2. За Серія "Серія 2", змінити "Тип діаграми"До"Розкидати.”
   • Примітка: Переконайтеся, що "Серія 1"Залишається як"Скатер з гладкими лініями. ” Іноді Excel змінює його, коли ви робите Комбо Також переконайтесь, що «Серія 1”Не натискається на вторинну вісь-не слід позначати прапорець біля типу діаграми.
3. Натисніть «в порядку.”

Крок 9: Змініть масштаб горизонтальної осі.

Відцентруйте діаграму на кривій дзвіночка, відрегулювавши шкалу горизонтальної осі. Клацніть правою кнопкою миші по горизонтальній осі і виберіть "Формат осі»З меню.

Після появи панелі завдань виконайте наведені нижче дії.

• Перейдіть до Параметри осі вкладка.
• Встановіть Мінімальні межі значення "15.”
• Встановіть Максимальні межі значення "125.”

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

Крок 10: Вставте та розташуйте спеціальні мітки даних.

Під час полірування діаграми обов’язково додайте спеціальні мітки даних. По-перше, клацніть правою кнопкою миші будь-яку точку, що представляє Серія "Серія 2" і виберіть "Додати мітки даних.”

Потім замініть мітки за замовчуванням на ті, які ви раніше встановили, і розмістіть їх над маркерами даних.

1. Клацніть правою кнопкою миші на будь-якому Серія "Серія 2" мітка даних.
2. Виберіть "Форматувати мітки даних.”
3. На панелі завдань перейдіть до Параметри міток вкладка.
4. Перевірте "Значення X”Коробка.
5. Зніміть прапорець "Y Значення”Коробка.
6. Під "Позиція етикетки,"Вибрати"Вище.”

Також тепер можна видалити лінії сітки (клацніть по них правою кнопкою миші> Видалити).

Крок #11: Змініть колір маркерів даних (необов’язково).

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

1. Клацніть правою кнопкою миші на будь-якому Серія "Серія 2" мітка даних.
2. Натисніть кнопку «Заповнити”.
3. Виберіть свій колір з палітри, що з’явиться.

Також видаліть кордони навколо крапок:

1. Знову клацніть правою кнопкою миші той самий маркер даних і виберіть «Контур.”
2. Виберіть "Без контуру.”

Крок #12: Додайте вертикальні лінії (необов’язково).

Як остаточне коригування, ви можете додати вертикальні лінії до діаграми, щоб допомогти підкреслити значення SD.

• Виберіть графік діаграми (таким чином лінії будуть вставлені безпосередньо в діаграму).
• Перейдіть до Вставити вкладка.
• Натисніть кнопку «Фігури”.
• Виберіть "Лінія.”

Утримуйте "ЗМІНИТИ" перетягуючи мишу, малюйте ідеально вертикальні лінії від кожної точки до місця, де кожна лінія відповідає кривій дзвоника.

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

І ось як ти це робиш. Тепер ви можете вибрати будь -який набір даних і створити звичайну криву дзвіночка розподілу, виконавши ці прості кроки!