Джимми из Хантсвилля хочет построить кривую колокола, показывающую средние оценки нескольких человек. Когда Джимми задал этот вопрос во время моего семинара по Power Excel, я вспомнил одно из моих самых популярных видео на YouTube.
В «Подкасте 1665 - Создание колоколообразной кривой в Excel» я объясняю, что для создания колоколообразной кривой необходимо вычислить среднее значение и стандартное отклонение. Затем я генерирую 30 точек по оси x, которые охватывают гипотетическую популяцию людей. В этом видео я создал диапазон от -3 стандартных отклонений до +3 стандартных отклонений вокруг среднего значения.
Например, если у вас есть среднее значение 50 и стандартное отклонение 10, я бы создал ось абсцисс от 70 до 130. Высота каждой точки рассчитывается с использованием =NORM.DIST(x,mean,standard deviation,False).
На изображении выше числа в A10: A40 по сути являются «поддельными точками данных». Я генерирую 31 число, чтобы создать красивую плавную кривую. Если бы я использовал только 7 точек данных, кривая выглядела бы так:
Для набора данных Джимми фактические средние баллы его сотрудников по существу представляют собой точки по оси абсцисс. Чтобы уместить их на колоколообразной кривой, вам нужно вычислить рост или значение Y для каждого сотрудника.
Следуй этим шагам:
-
Отсортируйте данные таким образом, чтобы оценки были от наименьшего к наибольшему.
Сортировать данные - Вычислите среднее значение, используя функцию СРЕДНЕЕ.
- Рассчитайте стандартное отклонение с помощью функции СТАНДОТКЛОН.
-
Вычислите значение Y справа от оценок, используя
=NORM.DIST(L2,$H$2,$H$3,FALSE). Значение Y будет генерировать высоту точки каждого человека вдоль кривой колокола. Функция НОРМ.РАСП позаботится о том, чтобы люди рядом со средним значением находились выше, чем люди рядом с верхним или нижним.
Создайте серию значений 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, а чтобы вычислить высоту, используйте эту функцию: = НОРМ.РАСП значения X, среднего и стандартного отклонения, запятая ложная (= НОРМ.РАСП (A10, $ B $ 2, $ B $ 3, ЛОЖЬ)).
И если задуматься, в этом видео на самом деле просто используется серия поддельных значений X, чтобы получить красивую кривую. И мы собираемся использовать здесь ту же концепцию, но вместо фальшивых значений X, мы собираемся разместить людей здесь, и тогда высота будет точно такой же формулой. Хорошо.
Итак, теперь Джимми хотел создать сводную таблицу. Итак, мы вставим сводную таблицу, поместим ее сюда, на этот лист, нажмите ОК. Люди внизу слева, а затем их средний балл. Хорошо, так что все начинается с суммы баллов, я дважды щелкаю по ней и изменяю ее на среднее значение. Отлично. Теперь, в самом низу, мне не нужна общая сумма - щелкните правой кнопкой мыши и удалите общую сумму - и мы хотим расположить этих людей по убыванию, и это легко сделать в сводной таблице. Данные, от А до Я - отлично. Хорошо. Теперь мы собираемся сделать то же самое, что и в Podcast 1663, а именно вычислить среднее значение и стандартное отклонение. Таким образом, среднее значение - это среднее значение этих оценок, а затем оно равно стандартному отклонению этих оценок. Хорошо. Теперь, когда я это знаю, я могу создать свое значение y.
Хорошо, вот пара вещей, которые мы здесь сделаем. Во-первых, вы не можете создать сводную таблицу - диаграмму рассеяния - из сводной таблицы. Итак, я скопирую все эти данные и сделаю это с помощью = D2. Обратите внимание, я стараюсь не использовать мышь или клавиши со стрелками, чтобы указывать на них. Итак, у нас есть свои ценности. Они станут значениями X, значение Y станет = НОРМ.РАСП, вот значение x, запятая, для среднего, это число, я нажму F4, чтобы заблокировать его; для стандартного отклонения это число, снова нажмите F4, чтобы заблокировать его, и кумулятивное значение FALSE. (= НОРМ.РАСП (K2, $ H $ 2, $ H $ 3, FALSE)) И мы дважды щелкните, чтобы скопировать это вниз. Хорошо. И тогда не выбирайте ярлыки,просто выберите XY, и мы вставим диаграмму рассеяния с линиями - вы можете выбрать диаграмму с изогнутыми линиями или небольшими прямыми линиями. Здесь я буду использовать такие изогнутые линии. И теперь у нас есть все наши люди, расположенные на кривой нормального распределения.
Хорошо. Теперь кое-что - некоторые вещи, связанные с форматированием, - мы собираемся сделать здесь: во-первых, дважды щелкните здесь по шкале, и похоже, что наше наименьшее число, вероятно, где-то около 50 - так что я установите минимум 50 - и наше самое большое число - самое большое число - это 88 - так что я установлю максимум 90. Хорошо. А теперь мы должны обозначить эти точки. Если вы используете Excel 2013 или новее, это легко сделать; но если вы используете более старую версию Excel, вам придется вернуться и использовать надстройку Rob Bovey's Chart Labeler, чтобы эти метки точек поступали из места, которого нет на диаграмме. Хорошо, мы начнем здесь. Мы собираемся добавить метки данных, они складывают числа, и они выглядят ужасно. Я приду сюда и скажу, что мне нужны дополнительные параметры, параметры ярлыков,и я хочу получить значение из ячеек - значение из ячеек. Хорошо? Итак, Диапазон ячеек прямо там, нажмите ОК. Очень важно использовать значение из ячеек, прежде чем я сниму флажок со значения Y. Это начинает хорошо выглядеть. Я избавлюсь от этого. Итак, весь ключ здесь - потому что у вас есть люди, которые вроде как перезаписывают друг друга, - это попытаться сделать диаграмму как можно больше. Нам не нужен туда заголовок. Зачем? Просто удалите это. И я все еще вижу, что Келли, Лу, Энди и Фло почти в одном месте; Джаред и … Хорошо. Так что теперь это будет неприятно - те, которые пересекаются. Но когда мы нажимаем на ярлык, мы выбираем все ярлыки, а затем снова щелкаем ярлык и выбираем только один ярлык. Хорошо? А сейчас. очень осторожно. попробуйте и нажмите на Энди, и просто перетащите Энди вверх влево.Похоже, Джаред и Айк вместе, так что теперь, когда я нахожусь в режиме выбора одной метки, это проще. А потом Келли и Лу, вытащите их вот так. Может быть, есть место получше, где Лу не перегружает, или даже здесь я могу перетащить его в обе стороны. Хорошо, так что у нас есть? Мы начали с набора данных, создали сводную таблицу, вычислили среднее значение и стандартное отклонение, которое просто позволяет нам вычислить высоту - положение Y для каждого из этих баллов и их высоту, надеюсь, мы поместим людей в красивую колоколообразную кривую в форме параболы, вот так.Хорошо, так что у нас есть? Мы начали с набора данных, создали сводную таблицу, вычислили среднее значение и стандартное отклонение, которое просто позволяет нам вычислить высоту - положение Y для каждого из этих баллов и их высоту, надеюсь, мы поместим людей в красивую колоколообразную кривую в форме параболы, вот так.Хорошо, так что у нас есть? Мы начали с набора данных, создали сводную таблицу, вычислили среднее значение и стандартное отклонение, которое просто позволяет нам вычислить высоту - положение Y для каждого из этих баллов и их высоту, надеюсь, мы поместим людей в красивую колоколообразную кривую в форме параболы, вот так.
Мне нравится этот вопрос Джимми, этого вопроса нет в этой книге, но он будет в следующий раз, когда я буду писать эту книгу. Придется добавить - это крутая просьба и небольшая крутая уловка. Кривые колокола очень популярны в Excel.
Но посмотрите мою книгу LIVe, 54 величайших совета по работе с Excel всех времен.
Хорошо, подведем итоги этой серии: Джимми из Хантсвилла хочет расположить людей по кривой колокола. Итак, мы используем сводную таблицу, чтобы вычислить средний балл, отсортировать сводные таблицы по баллам - упорядочить по убыванию - избавиться от общей суммы внизу - по сути, это будут значения X - а затем в сторону вычислите среднее и стандартное отклонение этих оценок и используйте формулы для копирования данных из сводной таблицы в новый диапазон, потому что у вас не может быть XY-диаграммы, которая пересекается со сводной таблицей. Вычислите значение y для каждого человека с помощью = НОРМ.РАСП их значения x, среднего, стандартного отклонения, запятой ЛОЖЬ; создайте точечную диаграмму XY с плавными линиями - если вы используете Excel 2010 или более раннюю версию, вы собираетесь использовать надстройку Ron Bovey's Chart Labeler. Я попрошу вас погуглить, потому чтона случай, если Роб изменит свой URL, мне не нужен неправильный URL. В Excel 2013 были метки данных, из ячеек, укажите имена, а затем некоторые настройки - измените масштаб внизу, я меняю их на максимальное значение, а затем перемещаю метки, которые перекрывают друг друга.
Чтобы загрузить книгу из сегодняшнего видео, используйте URL-адрес в описании YouTube. Я хочу поблагодарить Джимми за этот замечательный вопрос в Хантсвилле, и я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз для другого сетевого вещания от.
Скачать файл Excel
Чтобы загрузить файл Excel: place-people-on-bell-curve.xlsx
Спасибо Джимми из Хантсвилла за сегодняшний вопрос!
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Если в прошлом месяце вы перевели Excel в режим ручного пересчета, пришло время для Power Pivot (вам больше никогда не понадобится ручной режим)»
Роб Колли
