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

Кривая нормального распределения или кривая (bell curve)нормального распределения(normal distribution chart) — это график анализатора производительности. Многие компании используют колокольную диаграмму для отслеживания эффективности своих сотрудников. Точно так же график можно использовать в школах для отслеживания успеваемости учащихся. Вы можете использовать эту диаграмму для анализа производительности конкретного человека на разных этапах или команды на определенном этапе. В этой статье мы увидим, как создать кривую нормального распределения в Excel .

как сделать кривую колокола в экселе

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

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

Сделать образец данных Excel кривой Белла

1] Во- первых(First) , вы должны расположить все данные в порядке возрастания. Для этого выделите весь столбец оценок, нажмите на кнопку « Сортировать фильтр(Sort Filter) » на панели инструментов и выберите опцию « Сортировка от меньшего к большему(Sort Smallest to Largest) ». После выбора этой опции на экране появится всплывающее окно с двумя опциями: « Расширить выделение(Expand the Selection) » и « Продолжить с текущим выделением(Continue with the Current Selection) ». Вы должны выбрать первый вариант, так как он будет располагать оценки учащихся в порядке возрастания вместе с их именами.

Сделать кривую колокола для сортировки данных Excel

2] Чтобы создать кривую колокола в Excel, нам нужны три значения: среднее, стандартное отклонение и нормальное распределение. Давайте сначала рассчитаем среднее значение данных. Для этого введите следующую формулу и нажмите кнопку « Ввод(Enter) »:

=Average(B2:B10)

Вы можете рассчитать среднее значение в любой ячейке, я вычислил его в ячейке B13 . Обратите внимание, что вы должны ввести правильный адрес ячейки в средней формуле. Здесь ячейка B2 содержит первое число, а ячейка B11 ​​содержит последнее число для расчета среднего.

Сделать кривую Белла в среднем в Excel

Среднее значение наших данных составляет 82.

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

=STDEV.P(B2:B11)

Как и среднее значение, вы можете рассчитать стандартное отклонение в любой ячейке, я рассчитал его в ячейке B14 . Опять же(Again) , в этой формуле B2 представляет данные в ячейке B2, а B11 ​​представляет данные в ячейке B11 . Пожалуйста(Please) , введите адрес ячейки правильно.

 

Сделать кривую Белла стандартного отклонения Excel

Стандартное отклонение наших данных составляет 6,09918. Это означает, что большинство студентов будут лежать в диапазоне 82 – 6,09918 или 82 + 6,09918.

4] Теперь давайте рассчитаем нормальное распределение данных. Нормальное распределение рассчитывается для каждого учащегося отдельно. Поэтому(Therefore) я записал его в столбце C. Выберите(C. Select) ячейку C2 (ячейка чуть ниже ячейки нормального распределения), введите следующую формулу и нажмите кнопку « Ввод(Enter) »:

=NORM.DIST(B2,$B$13,$B$14,FALSE)

В приведенной выше формуле знак $ указывает на то, что мы заморозили соответствующую ячейку, а ЛОЖЬ(FALSE) указывает на « Распределение вероятностей по массе(Probability mass distribution) ». Поскольку колоколообразная кривая не является непрерывно возрастающей кривой, мы выбрали функцию FALSE . Функция TRUE указывает на « кумулятивную функцию распределения(Cumulative distribution function) », которая используется для построения графиков с возрастающими значениями.

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

5] Теперь поместите курсор в правый нижний угол выбранной ячейки (C2) и перетащите его в последнюю ячейку ( C11 ). Это скопирует и вставит всю формулу во все ячейки.

Создайте нормальное распределение кривой нормального распределения Excel

6] Наши данные готовы. Теперь нам нужно вставить кривую нормального распределения. Для этого сначала выберите столбцы меток и нормального распределения и перейдите в « Insert > Recommended Charts ».

Сделать диаграммы Bell Curve рекомендованными в Excel

7] Теперь нажмите на вкладку « Все графики(All Charts) » и перейдите к « XY Scatter > Scatter with Smooth Lines ». Здесь выберите график « Нормальное (Normal) распределение » и нажмите кнопку « (Distribution)ОК(OK) ».

Сделать кривую колокола Excel Вставить диаграмму

8] Колоколообразная кривая готова.

Создайте график Bell Curve Excel 1

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

  1. Выберите ось X.
  2. Выберите меню « Параметры (Options)оси » на правой панели и установите минимальное и максимальное значения в разделе « (Axis) Границы(Bounds) ».

Создайте график Bell Curve Excel 2

Вы также можете отображать и скрывать различные элементы диаграммы. Для этого выберите график и нажмите на значок « Плюс(Plus) ». Там вы получите несколько вариантов. Вы можете выбрать и отменить выбор любого или всех из них.

Создайте свойства диаграммы Bell Curve Excel

Выше мы видели, что форма колоколообразной кривой несовершенна. Это связано с тем, что разница между значениями по оси X(X-axis) (отметки учащихся) неодинакова. Теперь мы создаем еще один график колокола с равными интервалами значений по оси(X-axis) X. Вы должны выполнить те же шаги, указанные выше в статье. См. скриншот ниже.

создание колоколообразной кривой в excel

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

Теперь вы можете создать кривую нормального распределения в MS Excel.

Вам также может быть интересно(You may also like) : Как сделать диаграмму Ганта в Excel .