Как да създадете нормална разпределителна крива на звънец в 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 в този пример) до действителните ви данни (колони А и Б) за изчисляване на средната стойност на изпитите в набора от данни:

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

Една бърза бележка: по -често може да се наложи да закръглите изхода на формулата. За да направите това, просто го увийте във функцията ROUND, както следва:

1	= КРЪГЛА (СРЕДНА (B2: B201), 0)

Стъпка 2: Намерете стандартното отклонение.

Един надолу, един за тръгване. За щастие Excel има специална функция да върши цялата мръсна работа по намирането на стандартното отклонение за вас:

1	= STDEV.P (B2: B201)

Отново формулата избира всички стойности от посочения диапазон от клетки (B2: B201) и изчислява стандартното му отклонение-просто не забравяйте да закръглите и изхода.

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 клетки в колона Е (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.

• Изберете графика на диаграмата (по този начин линиите ще бъдат вмъкнати директно в диаграмата).
• Отидете на Вмъкване раздел.
• Щракнете върху „Форми”Бутон.
• Избирам "Линия.”

Задръжте „SHIFT“ клавиша, докато плъзгате мишката, за да нарисувате перфектно вертикални линии от всяка точка до мястото, където всяка линия отговаря на кривата на камбаната.

Променете заглавието на диаграмата и подобрената крива на звънеца ще покаже вашите ценни данни за разпространение.

И по този начин го правите. Вече можете да изберете всеки набор от данни и да създадете нормална крива на звънеца за разпределение, като следвате тези прости стъпки!