Как создать кривую колокола в Excel

Колоколообразная кривая представляет собой распределение переменной на графике. Вот как создать колоколообразную кривую в Excel

Кривые распределения Гаусса, широко известные как колоколообразные кривые, представляют собой графики нормального распределения, которые помогают в анализе дисперсии в наборах данных. В колоколообразной кривой наивысшая точка (которая также является средним значением) представляет собой событие, вероятность которого наиболее высока, в то время как остальные события распределены симметрично относительно среднего значения

Колоколообразные кривые имеют широкий спектр применения – от выставления относительных оценок студентам и создания систем конкурсного оценивания до прогнозирования доходности. Здесь мы проведем вас через процесс создания колоколообразной кривой в Excel

Основы создания колоколообразной кривой в Excel

Чтобы понять, как создать колоколообразную кривую в Excel, предположим, что вы преподаватель истории, которому нужно выставить оценки студентам на основе их успеваемости по тесту. Предположим, что в классе 15 студентов со следующими оценками:

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

Среднее значение — среднее значение набора данных (дает центр кривой).

Стандартное отклонение — измеряет, насколько сильно точки данных разбросаны относительно среднего значения (дает разброс кривой).

Нахождение среднего значения

Вы можете использовать встроенные функции в Excel для вычисления основных статистических данных, таких как среднее значение, стандартное отклонение, процент и т.д. Чтобы найти среднее значение, используйте функцию AVERAGE в Excel:

Введите =AVERAGE(B2:B16), чтобы найти среднее значение для приведенного выше листа отметок. Вы заметите, что оно дает значение 53,93

Если вам нужно получить значение целого числа, что обычно и происходит, вы можете использовать функцию ROUND. Для этого введите:

=ROUND(AVERAGE(B2:B16),0)

Теперь среднее значение становится равным 54

Нахождение стандартного отклонения

Excel показывает две формулы для стандартного отклонения:

STDEV.P используется, когда вы знаете, что ваши данные полные, т.е.это популяция.

STDEV.S используется, когда ваши данные неполные, т.е.у вас есть выборка из популяции.

В статистике люди часто отбирают выборки из популяции, поэтому обычно используется STEV.S. Поскольку у вас есть полные данные, т.е.т.е. оценки всех учеников класса, мы будем использовать STDEV.P. Чтобы получить стандартное отклонение заданного листа отметок, введите:

=STDEV.P(B2:B16)

Вы получите 27,755. Если вы хотите получить значение в целых числах, просто округлите его, набрав:

=ROUND(STDEV.P(B2:B16),0)

Вы получите 28

Сортировка данных в порядке возрастания

Чтобы создать форму колокола для диаграммы нормального распределения, данные должны быть расположены в порядке возрастания. Если ваши данные не расположены в порядке возрастания (как в нашем примере), просто выберите все значения (тестовые метки) в вашем наборе данных, перейдите в меню Sort & Filter на верхней панели и выберите Sort Ascending

Как построить кривую колокола в Excel

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

1. Нахождение нормального распределения

Пришло время вычислить нормальное распределение точек данных. В Excel нормальное распределение можно найти с помощью функции NORM.DIST, для которой требуются следующие переменные:

X – точка данных, для которой вы хотите рассчитать нормальное распределение.

Mean – среднее значение данных (уже вычислено).

Standard Deviation – стандартное отклонение данных (уже рассчитано).

Cumulative – логическое значение, используемое для указания необходимого типа распределения.

Вычислить нормальное распределение наших тестовых баллов:

1. Введите =NORM.DIST( в новую ячейку (в нашем случае ячейка C2.).
2. Введите необходимые значения с запятыми между ними, как показано в синтаксисе.
3. Для x введите B2, что даст первую точку данных, т.е. 12.
4. Для среднего значения введите D2, что дает среднее значение наших данных, и нажмите F4.F4 фиксирует значение среднего, обозначенное знаком доллара ($D$2), так что при копировании этой формулы для различных значений наших данных значение среднего останется неизменным.
5. Для стандартного отклонения введите E2, чтобы получить стандартное отклонение, и зафиксируйте его значение, нажав F4.
6. Наконец, введите FALSE вместо кумулятивного логического значения и закройте круглые скобки. Набрав FALSE, вы получите нормальную функцию плотности вероятности (PDF). Примечание: Чтобы получить кумулятивную функцию нормального распределения (CDF), нужно набрать TRUE.
7. Получив нормальное распределение для первого значения, просто перетащите его, чтобы получить нормальное распределение для остальных значений вашего набора данных.

2. Создание диаграммы рассеяния

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

1. Выберите набор данных (оценки студентов) и их нормальное распределение.
2. Перейдите в раздел Вставка > Диаграмма рассеяния.
3. Выберите Диаграмма рассеяния с плавными линиями.
4. И у вас есть колоколообразная кривая.

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

3. Настройка кривой Белла

У нас уже есть наша колоколообразная кривая, но мы можем сделать ее немного лучше, настроив ее. Прежде всего, давайте обновим название нашей кривой, дважды щелкнув по названию и введя желаемое название.(Вы можете изменить тип шрифта, размер шрифта и расположение заголовка, среди прочего)

Вы можете убрать надпись Series1 в нижней части колоколообразной кривой, выключив переключатель Legend. Чтобы придать колоколообразной кривой лучшую форму, вы можете определить максимальное и минимальное значения колоколообразной диаграммы. Для этого дважды щелкните по оси x, и у вас откроется вкладка Параметры оси, где вы можете внести необходимые изменения

Использование кривых Белла в Excel

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

В то время как колоколообразная кривая показывает вероятность конкретной точки данных в вашем наборе данных, существует несколько других графиков, которые вы можете создать в Excel, чтобы найти другие интересные сведения о вашем наборе данных