Джиммі в Хантсвіллі хоче побудувати криву дзвоника, яка б показувала середні бали кількох людей. Коли Джиммі задав питання під час мого семінару Power Excel, я згадав одне з моїх найпопулярніших відео на YouTube.
У підкасті 1665 - Створіть криву дзвоника в Excel, я пояснюю, що для створення кривої дзвоника потрібно розрахувати середнє та стандартне відхилення. Потім я генерую 30 точок вздовж осі х, які охоплюють гіпотетичну популяцію людей. У цьому відео я створив такий показник, який варіюється від -3 стандартних відхилень до + 3 стандартних відхилень навколо середнього значення.
Наприклад, якщо у вас середнє значення 50 і стандартне відхилення 10, я створив би вісь х, яка проходила від 70 до 130. Висота кожної точки обчислюється за допомогою =NORM.DIST(x,mean,standard deviation,False).
На зображенні вище цифри в A10: A40 по суті є "фальшивими точками даних". Я створюю 31 число, щоб створити приємну плавну криву. Якби я використовував лише 7 точок даних, крива мала б такий вигляд:
Для набору даних Джиммі фактичні середні бали його співробітників є по суті точками вздовж осі х. Щоб розмістити їх на дзвіночній кривій, потрібно з’ясувати зріст або значення Y для кожного працівника.
Виконайте такі дії:
-
Відсортуйте дані, щоб бали здавались від найнижчого до найвищого.
Сортувати дані - Обчисліть середнє значення за допомогою функції AVERAGE.
- Обчисліть стандартне відхилення за допомогою функції STDEV.
-
Обчисліть значення Y праворуч від балів, використовуючи
=NORM.DIST(L2,$H$2,$H$3,FALSE). Значення Y генерує висоту точки кожної людини вздовж кривої дзвона. Функція NORM.DIST подбає про побудову людей поблизу середнього значення у вищому місці, ніж про людей поблизу зверху або знизу.
Сформувати ряд значень Y. - Виберіть свої дані в L1: M15
-
Нещодавно в Excel почала з’являтися дивна помилка, тому для забезпечення успіху виберіть Усі діаграми на вкладці Вставка.
Запуск діалогового вікна призведе до всіх типів діаграм У діалоговому вікні Вставити діаграму перейдіть на вкладку Усі діаграми. Клацніть XY (Розсіяно) ліворуч. Виберіть другу піктограму вгорі. Виберіть попередній перегляд праворуч.
Чотири кліки, щоб вибрати діаграму Ваша початкова крива дзвоника буде виглядати так:
Крива дзвона
Щоб очистити криву дзвоника, виконайте такі дії:
- Клацніть на заголовок і натисніть клавішу Delete.
- Двічі клацніть будь-яке число вздовж осі Y внизу діаграми. З'явиться панель Формат осі.
-
Введіть нові значення для мінімуму та максимуму. Діапазон тут повинен бути просто достатньо широким, щоб показати всіх на графіку. Я використовував від 50 до 90.
Змініть мінімум і максимум - Зробіть діаграму ширшою, перетягнувши край діаграми.
- Клацніть піктограму + праворуч від діаграми та виберіть Мітки даних. Не хвилюйтеся, що ярлики ще не мають сенсу.
- Двічі клацніть на одній мітці, щоб відкрити панель Форматування міток.
- У верхній частині панелі є чотири піктограми. Виберіть піктограму, яка відображає стовпчасту діаграму.
- Клацніть стрілку поруч із Параметри мітки, щоб розгорнути цю частину панелі.
- Виберіть значення з комірок. З'явиться діалогове вікно із запитом розташування міток. Виберіть імена в K2: K15.
-
На панелі Форматування мітки даних скасуйте вибір значень Y. Важливо закінчити крок 15 перед виконанням кроку 16, інакше ви ненавмисно видалите ярлики.
Отримайте мітки з комірок, що містять імена.
Примітка
Можливість отримувати мітки з комірок була додана в Excel 2013. Якщо ви використовуєте Excel 2010 або старішу версію, завантажте надбудову XY Chart Labeler від Роб Бові. (Google знаходить).
На цьому етапі перевірте, чи є у вас мітки діаграм, які збігаються одна з одною. Щоб їх виправити, уважно виконайте такі дії.
- Клацніть один раз на одній мітці діаграми. Це вибирає всі мітки.
- Клацніть одним клацанням на одній з міток, яка знаходиться над іншою міткою, щоб вибрати саме цю мітку.
- Наведіть курсор на різні частини етикетки, доки не побачите чотириголову стрілку. Клацніть і перетягніть мітку на нове місце.
-
Коли ви вибрали лише одну мітку, ви можете натиснути будь-яку іншу мітку, щоб вибрати цю мітку. Повторіть для будь-яких інших міток, які потрібно перемістити.
Підсумкова діаграма
Переглянути відео
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2217: Помістіть людей на криву дзвоника.
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Сьогоднішнє запитання від Джиммі на моєму семінарі в Хантсвіллі, штат Алабама. У Джиммі є дані, він хоче узагальнити ці дані, а потім побудувати результати на кривій дзвоника.
Добре? Зараз одне з моїх найпопулярніших відео на YouTube - це таке: номер 1663, Створіть криву дзвоника в Excel. І враховуючи середнє та стандартне відхилення, я з’ясував мінімальне, яке в 3 рази перевищує стандартне відхилення менше середнього, а велике - у 3 рази більше стандартного відхилення більше середнього - де розрив - і серія значень X тут, і щоб визначити висоту, використовуйте цю функцію: = NORM.DIST значення X, середнє значення та стандартне відхилення, кома false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSE)).
І якщо задуматися, це відео насправді просто використовує тут ряд підроблених значень X, щоб отримати гарну вигляд кривої. І ми будемо використовувати ту саму концепцію тут, але замість підроблених значень X, насправді ми будемо мати людей тут, і тоді висота буде точно такою ж формулою. Гаразд
Отже, Джиммі хотів створити зведену таблицю. Отже, ми вставимо зведену таблицю, розмістимо її тут, на цьому аркуші, натисніть кнопку ОК. Люди вниз зліва, а потім їх середній бал. Добре, так що це починається з Суми Оцінок, я двічі клацну там і зміню це на середнє. Чудово. Тепер, в самому низу, я не хочу загального підсумку - клацніть правою кнопкою миші та Видалити загальний підсумок - і ми хочемо розташувати цих людей від низького до нижчого, і це легко зробити у зведеній таблиці. Дані, від А до Я - відмінні. Гаразд Тепер ми будемо робити те саме, що було в 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, вам доведеться повернутися назад і використовувати надбудову Графа Бове Графіка, щоб ці мітки точок надходили з місця, якого немає на графіку. Гаразд, так ми починаємо тут. Ми збираємося додати ярлики даних, а це додає цифри, і вони виглядають жахливо. Я прийду сюди і скажу, що хочу більше варіантів, параметри етикетки,і я хочу отримати значення з клітин - значення з клітин. Добре? Отже, Діапазон комірок знаходиться тут, натисніть кнопку ОК. Дуже важливо використовувати значення з клітинок, перш ніж я знімаю позначку Y. Це починає виглядати добре. Я позбудусь цього. Тепер головне тут - адже у вас є люди, які начебто перезаписують одне одного, - спробувати зробити діаграму якомога більшою. Нам не потрібен напрямок туди. Чому? Просто видаліть це. І я все ще бачу, що Келлі, Лу, Енді та Фло майже на одному місці; Джаред і-- Добре. Тож зараз це буде розчаровувати - ці, що перекриваються. Але коли ми натискаємо на ярлик, ми виділяємо всі мітки, а потім клацаємо на ярлику ще раз, і ми вибираємо лише одну мітку. Добре? Так що тепер. дуже обережно. спробуйте і натисніть на Енді, і просто перетягніть Енді вліво.Схоже, Джаред і Айк разом, тому тепер, коли я перебуваю в режимі вибору однієї етикетки, це простіше. А потім Келлі і Лу, перетягуйте їх так. Можливо, є краще місце, де не надмірно працює Лу, або навіть, наприклад, тут я можу перетягнути його з будь-якої сторони. Добре, так, що ми маємо? Ми почали з маси даних, створили зведену таблицю, з’ясували середнє та стандартне відхилення, що просто дозволяє нам з’ясувати висоту - позицію Y для кожного з цих балів та висоту цих, сподіваємось, ми заведемо людей у гарну криву дзвоника у формі параболи, ось так.Добре, так, що ми маємо? Ми почали з маси даних, створили зведену таблицю, з’ясували середнє та стандартне відхилення, що просто дозволяє нам з’ясувати висоту - позицію Y для кожного з цих балів та висоту тих, сподіваємось ми заведемо людей у гарну криву дзвоника у формі параболи, ось так.Добре, так, що ми маємо? Ми почали з маси даних, створили зведену таблицю, з’ясували середнє та стандартне відхилення, що просто дозволяє нам з’ясувати висоту - позицію Y для кожного з цих балів та висоту цих, сподіваємось, ми заведемо людей у гарну криву дзвоника у формі параболи, ось так.
Мені подобається це запитання від Джиммі, цього питання немає в цій книзі, але воно буде наступного разу, коли я напишу цю книгу. Мені доведеться додати це - це класний запит і крутий маленький фокус. Криві дзвону дуже популярні в Excel.
Але перегляньте мою книгу LIVe, 54 найбільші підказки Excel усіх часів.
Гаразд, підсумок цього епізоду: Джиммі з Хантсвіля хоче розмістити людей на кривій дзвону. Отже, ми використовуємо зведену таблицю, щоб визначити середній бал, відсортувати зведені таблиці за оцінками - розташованими від високих до низьких - позбутися загальної суми внизу - це, по суті, будуть значення X - а потім убік, обчисліть середнє та стандартне відхилення цих балів і використовуйте формули для копіювання даних зі зведеної таблиці в новий діапазон, оскільки ви не можете мати діаграму XY, яка перетинається зі зведеною таблицею. Обчисліть значення y для кожної людини з = NORM.DIST їх значення x, середнє значення, стандартне відхилення, кома FALSE; створити діаграму розсіювання XY з плавними лініями - якщо ви Excel 2010 або старішої версії, ви збираєтеся використовувати надбудову Chart Labeler Рона Бові. Я попрошу вас погуглити це, тому що,у випадку, якщо Роб змінить свою URL-адресу, я не хочу тут неправильну URL-адресу. В Excel 2013 мітки даних «З клітинок» вказують імена, а потім деякі коригування - змінюйте масштаб внизу, я змінюю їх і Макс, а потім переміщую мітки, які надмірно встановлюють одна одну.
Щоб завантажити книгу із сьогоднішнього відео, використовуйте URL-адресу в описі YouTube. Я хочу подякувати Джиммі за це дивовижне запитання в Хантсвіллі, і я хочу подякувати вам за заїзд. Побачимось наступного разу для чергової трансляції від.
Завантажте файл Excel
Щоб завантажити файл Excel: place-people-on-bell-curve.xlsx
Дякую Джиммі з Хантсвіля за сьогоднішнє запитання!
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Якщо за останній місяць ви перевели Excel у ручний режим перерахунку, настав час для розвороту живлення (вам більше ніколи не знадобиться ручний режим)
Роб Коллі
