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

В этом руководстве будет показано, как создать кривую нормального распределения во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

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

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

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

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

В этом пошаговом руководстве вы узнаете, как с нуля создать кривую нормального распределения в Excel:

Чтобы построить кривую Гаусса, вам нужно знать две вещи:

• Значение (также известное как стандартное измерение). Это определяет центр кривой, который, в свою очередь, характеризует положение кривой.
• Стандартное отклонение (SD) измерений. Это определяет разброс ваших данных в нормальном распределении - или, говоря простым языком, насколько широкой должна быть кривая. Например, на приведенной выше колоколообразной кривой одно стандартное отклонение среднего представляет собой диапазон между оценками экзамена от 53 до 85.

Чем ниже SD, тем выше кривая и меньше будут разбросаны ваши данные, и наоборот.

Стоит упомянуть правило 68-95-99,7, которое можно применить к любой кривой нормального распределения, что означает, что примерно 68% ваших данных будет размещено в пределах одного стандартного отклонения от среднего, 95% - в пределах двух стандартных отклонений и 99,7% - в пределах. три SD.

Теперь, когда вы знаете основы, давайте перейдем от теории к практике.

Начиная

В целях иллюстрации предположим, что у вас есть результаты теста 200 учеников и вы хотите выставить им оценки «по кривой», то есть оценки учеников будут основаны на их относительной успеваемости по отношению к остальной части класса:

Шаг № 1: Найдите среднее значение.

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

Поскольку среднее значение указывает среднее значение выборки или совокупности данных, вы можете найти стандартное измерение, используя функцию СРЕДНЕЕ.

Введите следующую формулу в любую пустую ячейку (F1 в этом примере) рядом с вашими фактическими данными (столбцы A а также B), чтобы вычислить среднее значение экзаменационных баллов в наборе данных:

1	= СРЕДНИЙ (B2: B201)

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

1	= ОКРУГЛ (СРЕДНИЙ (B2: B201); 0)

Шаг № 2: Найдите стандартное отклонение.

Один упал, один остался. К счастью, в Excel есть специальная функция, которая сделает за вас всю грязную работу по поиску стандартного отклонения:

1	= СТАНДОТКЛОН.P (B2: B201)

Опять же, формула выбирает все значения из указанного диапазона ячеек (B2: B201) и вычисляет его стандартное отклонение - не забудьте также округлить результат.

1	= ОКРУГЛ (СТАНДОТКЛОН.P (B2: B201); 0)

Шаг № 3: Установите значения оси X для кривой.

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

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

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

Давайте начнем подсчет с одного (так как студент не может получить отрицательный результат на экзамене) и дойдем до 150 - неважно, 150 это или 1500 - чтобы создать еще одну вспомогательную таблицу.

1. Выберите любую пустую ячейку под данными диаграммы (например, E4) и введите “1,” значение, определяющее первый интервал.
2. Перейдите к Дом таб.
3. в Редактирование группа, выберите «Наполнять.”
4. Под "Серия в," Выбрать "Столбец.”
5. Для "Значение шага," тип “1.” Это значение определяет приращения, которые будут автоматически добавляться, пока Excel не достигнет последнего интервала.
6. Для "Стоп-значение," тип "150,” значение, которое соответствует последнему интервалу, и нажмите «OK.”

Чудом 149 ячеек в столбце E (E5: E153) были заполнены значениями от 2 до 150.

ПРИМЕЧАНИЕ. Не скрывайте исходные ячейки данных, как показано на снимках экрана.. В противном случае методика не сработает.

Шаг №4: Вычислите значения нормального распределения для каждого значения оси x.

Теперь найдите значения нормального распределения - вероятность того, что студент получит определенный балл за экзамен, представленный определенным значением оси X - для каждого из интервалов. К счастью для вас, в Excel есть рабочая лошадка для выполнения всех этих вычислений: функция НОРМ.РАСП.

Введите следующую формулу в ячейку справа (F4) вашего первого интервала (E4):

1	= НОРМ.РАСП (E4; $ F $ 1; $ F $ 2; ЛОЖЬ)

Вот декодированная версия, которая поможет вам соответствующим образом настроить:

1	= НОРМ.РАСП ([первый интервал], [среднее (абсолютное значение)], [стандартное отклонение (абсолютное значение), ЛОЖЬ)

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

Теперь дважды щелкните маркер заполнения, чтобы скопировать формулу в остальные ячейки (F5: F153).

Шаг № 5: Создайте диаграмму рассеяния с плавными линиями.

Наконец, пришло время строить колоколообразную кривую:

1. Выберите любое значение в вспомогательной таблице, содержащей значения осей x и y (E4: F153).
2. Перейти к Вставлять таб.
3. Щелкните значок «Вставить точечную (X, Y) или пузырьковую диаграмму" кнопка.
4. Выбирать "Разброс с плавными линиями ».

Шаг № 6: Настройте таблицу меток.

Технически у вас есть кривая колокола. Но его будет трудно прочитать, поскольку в нем отсутствуют какие-либо данные, описывающие это.

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

Для этого создайте еще одну вспомогательную таблицу следующим образом:

Сначала скопируйте среднее значение (F1) рядом с соответствующей ячейкой в ​​столбце X-Value (I5).

Затем вычислите значения стандартного отклонения ниже среднего, введя эту простую формулу в ячейка I4:

1	= I5- $ F $ 2

Проще говоря, формула вычитает сумму предыдущих значений стандартного отклонения из среднего. Теперь перетащите маркер заполнения вверх, чтобы скопировать формулу в оставшиеся две ячейки (I2: I3).

Повторите тот же процесс для стандартных отклонений выше среднего, используя зеркальную формулу:

1	= I5 + $ F $ 2

Таким же образом выполните формулу для двух других ячеек (I7: I8).

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

Шаг № 7: Вставьте данные метки в диаграмму.

Теперь добавьте все данные, которые вы подготовили. Щелкните правой кнопкой мыши график и выберите «Выберите данные.”

В появившемся диалоговом окне выберите «Добавлять.”

Выделите соответствующие диапазоны ячеек из вспомогательной таблицы -I2: I8 для "Значения серии X" а также J2: J8 для "Значения серии Y »-и нажмите «OK.”

Шаг № 8: Измените тип диаграммы для серии этикеток.

Наш следующий шаг - изменить тип диаграммы недавно добавленной серии, чтобы маркеры данных отображались в виде точек. Для этого щелкните правой кнопкой мыши график диаграммы и выберите «Изменить тип диаграммы.”

Затем создайте комбинированную диаграмму:

1. Перейдите к Комбо таб.
2. Для Серия «Series2», изменение "Тип диаграммы" к "Разброс.”
   • Примечание. Убедитесь, что «Серия1»Остается как«Скаттер с плавными линиями. » Иногда Excel изменяет его, когда вы делаете Комбо Также убедитесь, что «Серия1”Не перемещается на вторичную ось - флажок рядом с типом диаграммы не должен быть отмечен.
3. Нажмите «Ok.”

Шаг № 9: Измените масштаб горизонтальной оси.

Отцентрируйте диаграмму на колоколообразной кривой, отрегулировав масштаб горизонтальной оси. Щелкните правой кнопкой мыши горизонтальную ось и выберите «Ось формата»Из меню.

Когда появится панель задач, сделайте следующее:

• Перейти к Параметры оси таб.
• Установить Минимальные границы значение "15.”
• Установить Максимальные границы значение "125.”

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

Шаг № 10: Вставьте и разместите метки пользовательских данных.

По мере того, как вы совершенствуете свою диаграмму, не забудьте добавить пользовательские метки данных. Сначала щелкните правой кнопкой мыши любую точку, представляющую Серия «Series2» и выберите «Добавьте метки данных.”

Затем замените метки по умолчанию на те, которые вы установили ранее, и поместите их над маркерами данных.

1. Щелкните правой кнопкой мыши на любом Серия «Series2» метка данных.
2. Выбирать "Отформатируйте метки данных.”
3. На панели задач переключитесь на Параметры метки таб.
4. Проверить "Значение X" коробка.
5. Снимите флажок "Значение Y" коробка.
6. Под "Положение ярлыка," выбирать "Выше.”

Кроме того, теперь вы можете удалить линии сетки (щелкните их правой кнопкой мыши> Удалить).

Шаг № 11: Перекрасить маркеры данных (необязательно).

Наконец, перекрасьте точки, чтобы они соответствовали вашему стилю диаграммы.

1. Щелкните правой кнопкой мыши любой Серия «Series2» метка данных.
2. Щелкните значок «Наполнять" кнопка.
3. Выберите свой цвет из появившейся палитры.

Также удалите границы вокруг точек:

1. Снова щелкните правой кнопкой мыши тот же маркер данных и выберите «Контур.”
2. Выбирать "Без контура.”

Шаг № 12: Добавьте вертикальные линии (необязательно).

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

• Выберите график диаграммы (таким образом линии будут вставлены прямо в диаграмму).
• Перейти к Вставлять таб.
• Щелкните значок «Формы" кнопка.
• Выбирать "Линия.”

Удерживайте "СДВИГ" при перетаскивании мыши, чтобы нарисовать идеально вертикальные линии от каждой точки до того места, где каждая линия пересекается с колоколообразной кривой.

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

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