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

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

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

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

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

Пример таблицы Excel с заголовками Point, X, Y и заполненным столбцом точек от 1 до 61.

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

Схема расчета значений X для колоколообразной кривой. Среднее значение находится в 31-й точке, каждая последующая ниже увеличивается на 1/10 стандартного отклонения, каждая предыдущая выше — уменьшается.

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

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

Расчет значений Y с помощью функции NORM.DIST на основе значений X. Функция возвращает малые числа по краям и максимальное значение в середине кривой. — Расчет значений Y с помощью функции НОРМ.РАСП / NORM.DIST на основе значений X. Функция возвращает малые числа по краям и максимальное значение в середине кривой.

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

Выбор типа диаграммы 'Точечная с гладкими кривыми' на вкладке 'Вставка' в Excel.

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

Готовый график колоколообразной кривой (нормального распределения) в Excel, построенный по описанной методике.

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

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

📖 Похожие материалы