✅ Как построить кривую колокола в Excel (пошаговое руководство)

Содержание

Понимание колоколообразной кривой
Создание кривой колокола в Excel

Колоколообразная кривая (также известная как кривая нормального распределения) - это способ построения и анализа данных, который выглядит как колоколообразная кривая.

На кривой колокола наивысшая точка - это точка с наибольшей вероятностью возникновения, и вероятность возникновения снижается по обе стороны кривой.

Он часто используется во время аттестации сотрудников или во время аттестации на экзаменах (когда-нибудь слышал - "Вас будут оценивать по кривой?").

Теперь, прежде чем я перейду к тому, как создать кривую колокола в Excel, давайте лучше разберемся в концепции на примере.

Понимание колоколообразной кривой

Предположим, вы работаете в команде из 100 человек, и ваш менеджер говорит вам, что ваша производительность будет относительно других и будет оцениваться по кривой нормального распределения.

Это означает, что даже если ваша команда - лучшая команда в истории, а все вы супергерои, только небольшая часть из вас получит высший рейтинг, большинство людей в вашей команде получит средний рейтинг, а горстка получит самый низкий. рейтинг.

Источник изображения: EmpxTrack

Но зачем нам колоколообразная кривая?

Честный вопрос!

Предположим, у вас есть класс из 100 студентов, которые приходят на экзамен. Согласно вашей системе оценок, любой, кто набирает более 80 баллов из 100, получает оценку «отлично». Но так как вы поставили действительно простой лист, каждый набрал больше 80 и получил пятерку.

В такой системе оценок нет ничего плохого. Однако, используя его, вы не сможете отличить человека, получившего 81 балл, от человека, получившего 95 баллов (поскольку оба получат оценку «А»).

Чтобы сравнение было справедливым и сохранялся дух соревнования, для оценки результатов часто используется кривая колокола (по крайней мере, так было, когда я учился в колледже).

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

Учащиеся, получающие более высокие оценки, находятся на правой стороне кривой, а учащиеся, получающие низкие оценки, - в левой части кривой (при этом большинство учащихся находятся в середине около среднего балла).

Теперь, чтобы понять кривую колокола, вам нужно знать о двух показателях:

Иметь в виду - среднее значение всех точек данных
Среднеквадратичное отклонение - показывает, насколько набор данных отклоняется от среднего значения набора данных. Например, предположим, что у вас есть группа из 50 человек, и вы записываете их вес (в килограммах). В этом наборе данных средний вес составляет 60 кг, а стандартное отклонение - 4 кг. Это означает, что 68% веса человека находится в пределах 1 стандартного отклонения от среднего значения, что составляет 56-64 кг. Точно так же 95% людей находятся в пределах 2 стандартных отклонений, что составляет 52-68 кг.

Когда у вас есть набор данных, который обычно распределяется, ваша кривая колокола будет соответствовать следующим правилам:

Центр колоколообразной кривой - это среднее значение точки данных (также самая высокая точка колоколообразной кривой).
68,2% от общего числа точек данных лежат в диапазоне (среднее - стандартное отклонение к среднему + стандартное отклонение).
95,5% от общего числа точек данных лежат в диапазоне (Среднее - 2 * стандартное отклонение к среднему + 2 * стандартное отклонение)
99,7% от общего числа точек данных лежат в диапазоне (Среднее - 3 * стандартное отклонение к среднему + 3 * стандартное отклонение)

Источник изображения: MIT News

Теперь давайте посмотрим, как создать кривую колокола в Excel.

Создание кривой колокола в Excel

Возьмем пример класса учащихся, получивших баллы на экзамене.

Средний балл класса 65 и стандартное отклонение 10. (Среднее значение можно вычислить с помощью функции СРЕДНЕЕ в Excel, а стандартное отклонение - с помощью функции СТАНДОТКЛОН.P).

Вот шаги, чтобы создать колоколообразную кривую для этого набора данных:

В ячейке A1 введите 35. Это значение можно вычислить с помощью Среднее - 3 * стандартное отклонение (65-3*10).
В ячейке ниже введите 36 и создайте ряд от 35 до 95 (где 95 - среднее + 3 * стандартное отклонение). Вы можете сделать это быстро, используя опцию автозаполнения или используя маркер заполнения и перетащив его вниз, чтобы заполнить ячейки.
В ячейке рядом с 35 введите формулу: = НОРМ.РАСП (A1,65,10; ЛОЖЬ)
- Обратите внимание, что здесь я жестко запрограммировал значение среднего и стандартного отклонения. Вы также можете разместить их в ячейках и использовать ссылки на ячейки в формуле.
Снова используйте маркер заполнения, чтобы быстро скопировать и вставить формулу для всех ячеек.
Выберите набор данных и перейдите на вкладку Вставка.
Вставьте диаграмму «Точечная диаграмма с плавными линиями».

Это даст вам кривую колокола в Excel.

Теперь вы можете изменить заголовок диаграммы и при необходимости отрегулировать ось.

Обратите внимание, что когда у вас низкое стандартное отклонение, вы получаете сжатую тонкую колоколообразную кривую, а когда у вас высокое стандартное отклонение, колоколообразная кривая широкая и покрывает большую площадь на диаграмме.

Этот вид колоколообразной кривой можно использовать для определения того, где находится точка данных на диаграмме. Например, если в команде много высокопроизводительных сотрудников, при оценке по кривой, несмотря на то, что он является высокопроизводительным исполнителем, кто-то может получить средний рейтинг, поскольку он / она находился в середине кривой.

Примечание. В этом сообщении блога я обсудил концепцию кривой колокола и то, как ее создать в Excel. Статистику лучше было бы говорить об эффективности кривой колокола и связанных с ней ограничениях. Я больше увлекаюсь Excel, и мое участие в работе с кривой Белла ограничивалось расчетами, которые я делал, когда работал финансовым аналитиком.

Надеюсь, вы нашли этот урок полезным!

Сообщите мне свои мысли в разделе комментариев.

Вам также могут понравиться следующие руководства по Excel: