Джими в Хънтсвил иска да начертае крива на камбана, показваща средните резултати на няколко души. Когато Джими зададе въпроса по време на моя семинар за Power Excel, се сетих за един от най-популярните си видеоклипове в YouTube.
В Podcast 1665 - Създаване на крива на звънец в Excel, обяснявам, че за да създадете крива на звънец, трябва да изчислите средното и стандартното отклонение. След това генерирам 30 точки по оста x, които обхващат хипотетична популация от хора. В това видео генерирах, което обхваща от -3 стандартни отклонения до + 3 стандартни отклонения около средна стойност.
Например, ако имате средна стойност 50 и стандартно отклонение 10, бих създал ос x, която се движи от 70 до 130. Височината на всяка точка се изчислява с помощта =NORM.DIST(x,mean,standard deviation,False).
На изображението по-горе числата в A10: A40 са по същество „фалшиви точки с данни“. Генерирам 31 числа, за да създам хубава гладка крива. Ако щях да използвам само 7 точки с данни, кривата би изглеждала така:
За набора от данни на Джими действителните средни резултати на неговите служители са по същество точки по оста x. За да ги поставите на камбанна крива, трябва да разберете височината или Y-стойността за всеки служител.
Следвай тези стъпки:
-
Сортирайте данните, така че резултатите да изглеждат най-ниски към най-високи.
Сортирайте данните - Изчислете средно с помощта на функцията AVERAGE.
- Изчислете стандартно отклонение с помощта на функцията STDEV.
-
Изчислете стойността Y вдясно от резултатите, като използвате
=NORM.DIST(L2,$H$2,$H$3,FALSE). Стойността Y ще генерира височина на точката на всеки човек по кривата на камбаната. Функцията NORM.DIST ще се погрижи за начертаване на хора в близост до средната стойност на по-високо място от хората в горната или долната част.
Генерирайте поредица от Y стойности. - Изберете вашите данни в L1: M15
-
Странна грешка наскоро започна да се появява в Excel, за да осигурите успех, изберете Всички диаграми в раздела Вмъкване.
Диалоговият стартер ще ви отведе до всички типове диаграми В диалоговия прозорец Вмъкване на диаграма щракнете върху раздела Всички диаграми. Щракнете върху XY (Scatter) вляво. Изберете втората икона в горната част. Изберете визуализацията отдясно.
Четири щраквания, за да изберете диаграмата Вашата първоначална крива на камбана ще изглежда така:
Кривата на камбаната
За да изчистите кривата на камбаната, изпълнете следните стъпки:
- Кликнете върху заглавието и натиснете клавиша Delete.
- Щракнете двукратно върху произволно число по оста Y в долната част на диаграмата. Ще се появи панелът Форматиране на ос.
-
Въведете нови стойности за минимум и максимум. Диапазонът тук трябва да е достатъчно широк, за да покаже всички на графиката. Използвах 50 до 90.
Променете минимума и максимума - Направете диаграмата по-широка, като плъзнете ръба на диаграмата.
- Щракнете върху иконата + вдясно от диаграмата и изберете Етикети с данни. Не се притеснявайте, че етикетите все още нямат смисъл.
- Щракнете двукратно върху един етикет, за да отворите панела Форматиране на етикети.
- В горната част на панела има четири икони. Изберете иконата, която показва колонна диаграма.
- Щракнете върху стрелката до Опции на етикета, за да разгънете тази част от панела.
- Изберете Стойност от клетки. Ще се появи диалогов прозорец с искане за местоположението на етикетите. Изберете имената в K2: K15.
-
Все още в панела Форматиране на етикета на данни премахнете отметката от Y стойности. Важно е да завършите стъпка 15, преди да направите стъпка 16, или по невнимание ще премахнете етикетите.
Вземете етикетите от клетките, съдържащи имена.
Забележка
Възможността за получаване на етикети от клетки е добавена в Excel 2013. Ако използвате Excel 2010 или по-стара версия, изтеглете добавката XY Chart Labeler от Rob Bovey. (Google да го намери).
В този момент вижте дали имате етикети на диаграми, които се сриват един към друг. За да ги поправите, следвайте внимателно тези стъпки.
- Кликнете еднократно върху един етикет на диаграмата. Това избира всички етикети.
- Щракнете с едно щракване върху един от етикетите, който е отгоре на друг етикет, за да изберете точно този етикет.
- Задръжте курсора на мишката върху различни части на етикета, докато видите четириглава стрелка. Щракнете и плъзнете етикета на нова позиция.
-
След като сте избрали само един етикет, можете да щракнете с едно щракване върху всеки друг етикет, за да изберете този етикет. Повторете за всички други етикети, които трябва да бъдат преместени.
Финалната диаграма
Гледам видео
Видео транскрипция
Научете Excel от Подкаст, епизод 2217: Поставете хората на крива на камбана.
Хей, добре дошъл в мрежата, аз съм Бил Джелен. Днешният въпрос от Джими в семинара ми в Хънтсвил, Алабама. Джими има данни, той иска да обобщи тези данни и след това да нанесе резултатите на камбанна крива.
Добре? Сега един от най-популярните ми видеоклипове в YouTube е този: номер 1663, Създайте крива на звънец в Excel. И като се има предвид средно и стандартно отклонение, разбрах ниското, което е 3 пъти стандартното отклонение по-малко от средното и високото - 3 пъти стандартното отклонение повече от средното - където разликата е - и серия от стойности X тук и за да разберете височината, използвайте тази функция: = NORM.DIST на стойността X, средната стойност и стандартното отклонение, запетая false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSE)).
И ако се замислите, това видео всъщност просто използва поредица от фалшиви X стойности тук, за да получи хубаво изглеждаща крива. И тук ще използваме същата концепция, но вместо фалшиви стойности X, всъщност ще имаме хората тук и тогава височината ще бъде точно същата формула. Добре.
И така, Джими искаше да създаде обобщена таблица. Така че ще вмъкнем, PivotTable, ще го поставим тук на този лист, щракнете върху OK. Хората надолу отляво и след това техния среден резултат. Добре, така че започва със Sum of Score, ще щракна два пъти там и ще променя това на средно. Страхотен. Сега, най-отдолу, не искам общ сбор - щракнете с десния бутон на мишката и Премахване на общ сбор - и ние искаме да подредим тези Хора от високо до ниско и това е лесно да се направи в обобщена таблица. Данни, от А до Я - отлични. Добре. Сега ще направим точно същото нещо, което направихме още в Podcast 1663, и това изчислява средната стойност и стандартното отклонение. Така че средната стойност е средна стойност на тези резултати и след това се равнява на стандартно отклонение на тези оценки. Добре. Сега, когато знам това, мога да създам своята y-стойност.
Добре, така че ще направим няколко неща тук. Първо, не можете да създадете обобщена таблица - разпръсната диаграма - от обобщена таблица. Така че ще копирам всички тези данни и просто ще направя това с = D2. Забележете, че внимавам да не използвам мишката или клавишите със стрелки, за да соча към тях. И така, тук имаме своите ценности. Те ще станат X стойности, Y стойността ще стане = NORM.DIST, тук е x стойността, запетая, за средното, това число, ще натисна F4, за да го заключа; за стандартното отклонение това е числото, отново натиснете F4, за да го заключите, и кумулативно FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) И ще щракнем двукратно, за да копираме това. Добре. И тогава, не избирайте етикетите,просто изберете XY и ние ще вмъкнем разпръсната диаграма с линии - можете да изберете тази с извити линии или малко прави линии. Ето, ще отида с криви линии като тази. И сега всички наши Хора са поставени на камбанарна крива.
Добре. Сега някои неща - някои неща от форматирането - ще направим тук: Първо, щракнете двукратно тук по скалата и изглежда, че най-ниското ни число е някъде около 50 - задайте минимум 50 - и най-голямото ни число - най-голямото ни число - е 88 - така че ще задам максимум 90. Добре. И сега, ние трябва да обозначим тези точки. Ако сте в Excel 2013 или по-нова версия, това е лесно да се направи; но ако сте в по-стара версия на Excel, ще трябва да се върнете назад и да използвате добавката на Rob Bovey's Chart Labeler, за да може тези етикети на точки да идват от място, което не е в диаграмата. Добре, така че започваме тук. Ще добавим етикети с данни, а той добавя цифри и те изглеждат ужасно. Ще дойда тук и ще кажа, че искам още опции, опции за етикети,и искам да получа стойност от клетки - стойност от клетки. Добре? Така че диапазонът от клетки е точно там, щракнете върху OK. Много важно е да използвам Value от клетки, преди да премахна отметката от Y стойността. Започва да изглежда добре. Ще се отърва от това. Сега целият ключ тук - защото имате някои хора, които се презаписват един друг - е да се опитате да направите диаграмата възможно най-голяма. Нямаме нужда от насочване там. Защо? Просто изтрийте това. И все още виждам, че Кели и Лу и Анди и Фло са почти на едно и също място; Джаред и … Добре. Така че сега това ще бъде разочароващо - тези, които се припокриват. Но когато щракнем върху етикет, ние избрахме всички етикети и след това щракнахме върху етикет отново и избираме само един етикет. Добре? Така че сега. много внимателно. опитайте и кликнете върху Andy и просто плъзнете Andy нагоре вляво.Изглежда, че Джаред и Айк са заедно, така че сега, когато съм в режим за избор на един етикет, е по-лесно. А след това Кели и Лу ги плъзнете така. Може би има по-добро място, което не прекалява с Лу, или дори, като тук, мога да го влача от двете страни. Добре, така, какво имаме? Започнахме с куп данни, създадохме обобщена таблица, разбрахме средното и стандартното отклонение, което просто ни позволява да разберем височината - позицията Y за всеки от тези резултати и височината на тези, надяваме се, ще вкараме хората в хубава парабола с форма на камбана, така.Добре, така, какво имаме? Започнахме с куп данни, създадохме обобщена таблица, разбрахме средното и стандартното отклонение, което просто ни позволява да разберем височината - позицията Y за всеки от тези резултати и височината на тези, надяваме се, ще вкараме хората в хубава парабола с форма на камбана, така.Добре, така, какво имаме? Започнахме с куп данни, създадохме обобщена таблица, разбрахме средното и стандартното отклонение, което просто ни позволява да разберем височината - позицията Y за всеки от тези резултати и височината на тези, надяваме се, ще вкараме хората в хубава парабола с форма на камбана, така.
Обичам този въпрос от Джими, този въпрос не е в тази книга, но ще бъде при следващия път, когато напиша тази книга. Ще трябва да добавя това - това е страхотна заявка и страхотен малък трик. Звънчевите криви са много популярни в Excel.
Но вижте моята книга, LIVe, 54-те най-добри съвета на Excel за всички времена.
Добре, заключение от този епизод: Джими от Хънтсвил иска да подреди хората на камбанарна крива. Така че използваме обобщена таблица, за да разберем средния резултат, сортираме обобщените таблици по резултатите - подредени високо към ниско - отървем се от общия сбор в долната част - това по същество ще бъдат стойностите X - и след това настрани, изчислете средното и стандартното отклонение на тези резултати и използвайте формули, за да копирате данните от обобщената таблица в нов диапазон, защото не можете да имате XY диаграма, която да се пресича с обобщена таблица. Изчислете y-стойност за всеки човек с = NORM.DIST на тяхната x-стойност, средната стойност, стандартното отклонение, запетая FALSE; създайте XY разпръсната диаграма с гладки линии - ако сте Excel 2010 или по-нова версия, ще използвате добавката на Ron Bovey's Chart Labeler. Ще ви помоля да потърсите в Google, защото,в случай, че Роб промени URL адреса си, не искам грешен URL адрес тук. В Excel 2013 имаше етикети с данни, От клетки, посочете имената и след това някои корекции - променете мащаба по дъното, аз ги променям в и Макс и след това преместете етикетите, които се задават взаимно.
За да изтеглите работната книга от днешното видео, използвайте URL адреса в описанието на YouTube. Искам да благодаря на Джими за този страхотен въпрос в Хънтсвил и искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Изтеглете Excel файла
За да изтеглите файла на Excel: place-people-on-bell-curve.xlsx
Благодаря на Джими в Хънтсвил за днешния въпрос!
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Ако сте поставили Excel в режим на ръчно преизчисляване през последния месец, време е за въртене на захранването (никога повече няма да имате нужда от ръчен режим)“
Роб Коли
