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

Колоколообразная кривая, или кривая нормального распределения, — это фундаментальный инструмент статистики, визуализирующий распределение данных вокруг среднего значения. В этом руководстве мы подробно разберем, как построить колоколообразную кривую в Excel с использованием встроенной функции НОРМ.РАСП (NORM.DIST) и создать наглядный график.

Для построения кривой нам понадобятся два ключевых параметра: среднее значение (mean) и стандартное отклонение (standard deviation). Статистическое правило гласит, что примерно 68% данных лежат в пределах одного стандартного отклонения от среднего, 95% — в пределах двух, а 99.73% — в пределах трех.

1. Задайте параметры распределения. В ячейке B1 укажите среднее значение (например, 150). В ячейке B2 рассчитайте стандартное отклонение. Например, для диапазона от 100 до 200, где большинство значений укладывается в 3 стандартных отклонения, используйте формулу =50/3 (=50/3) (результат ≈16.667).
2. Создайте структуру данных. В ячейках A4:C4 введите заголовки: Point, X, Y. В столбце A (ячейки A5:A65) заполните последовательность чисел от 1 до 61 — это будут точки для построения плавной кривой.

3. Рассчитайте значения X.
   • В средней точке данных (ряд 31, ячейка B35) введите формулу =Б$1 (=B$1), чтобы поместить туда среднее значение.
   • В ячейке B36 введите формулу для увеличения X: =Б35+($Б$2/10) (=B35+($B$2/10)). Скопируйте эту формулу вниз до строки 65.
   • В ячейке B34 введите формулу для уменьшения X: =Б35-($Б$2/10) (=B35-($B$2/10)). Скопируйте эту формулу вверх до строки 5.

Ключевая функция для построения кривой — НОРМ.РАСП (NORM.DIST) (Нормальное распределение). Она вычисляет высоту (значение Y) кривой для каждого заданного X. Формула имеет четыре аргумента: =НОРМ.РАСП(x; среднее; стандартное_отклонение; интегральная) (=NORM.DIST(x; среднее; стандартное_отклонение; интегральная)). Для построения колоколообразной кривой последний аргумент должен быть ЛОЖЬ (ЛОЖЬ (FALSE)).

4. Рассчитайте значения Y. В ячейке C5 введите формулу =НОРМ.РАСП(Б5;$Б$1;$Б$2;ЛОЖЬ) (=NORM.DIST(B5;$B$1;$B$2;ЛОЖЬ)) и скопируйте ее вниз до строки 65.

5. Постройте диаграмму. Выделите диапазон данных B4:C65. На вкладке «Вставка» (Insert) выберите тип диаграммы «Точечная с гладкими кривыми» (XY-Scatter with Smooth Lines).

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

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