Построение нормального распределения в Excel с помощью функций

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

Теоретические основы нормального распределения

Нормальное распределение - это один из наиболее важных законов распределения случайных величин в теории вероятностей и математической статистике. Он позволяет оценить вероятность попадания случайной величины в тот или иной интервал значений.

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

• Симметричность относительно математического ожидания
• Форма колоколообразной кривой с максимумом в точке матожидания
• Асимптотическое приближение к оси абсцисс

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

Параметры нормального распределения

Нормальное распределение задается двумя параметрами:

1. Математическим ожиданием (обозначается буквой M)
2. Среднеквадратичным отклонением или стандартным отклонением (обозначается буквой σ)

Первый параметр определяет положение центра распределения, второй - его "размазанность".

Встроенные функции Excel для работы с нормальным распределением

Для работы с нормальным законом распределения в Excel предусмотрено 3 основные функции.

1. НОРМ.РАСП - вычисление плотности вероятности

Функция НОРМ.РАСП вычисляет плотность вероятности для заданных параметров M и σ. Например, для нормального распределения со средним 50 и стандартным отклонением 5 ее можно использовать так:

Полученное значение 0.10798 и есть плотность вероятности для числа 45.

2. НОРМ.СТ.РАСП - вычисление вероятности для стандартизированных значений

Функция НОРМ.СТ.РАСП используется для вычисления вероятностей, когда исходные данные приведены к стандартному нормальному распределению со средним 0 и стандартным отклонением 1.

Например, с ее помощью можно найти вероятность того, что стандартизированная случайная величина примет значение меньшее 1.96:

То есть с вероятностью 97.5% стандартизированная нормальная случайная величина будет меньше 1.96.

3. НОРМ.ОБР - вычисление значения случайной величины по заданной вероятности

Если требуется найти само значение нормально распределенной случайной величины при известной вероятности, используют функцию НОРМ.ОБР.

Например, можно определить значение, ниже которого с вероятностью 95% будут находиться значения нормально распределенной случайной величины со средним 170 и стандартным отклонением 12:

Таким образом, с вероятностью 95% значения не будут меньше 156.6.

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

Нормальное распределение в Excel используется в различных областях: от проверки статистических гипотез до моделирования и прогнозирования процессов. Рассмотрим последовательность его построения на конкретном примере.

Генерирование выборки случайных чисел

Сначала с помощью функции СЛЧИС сгенерируем выборку из 100 случайных чисел, равномерно распределенных на интервале от 0 до 100:

Расчет параметров распределения: СРЗНАЧ, СТАНДОТКЛОН

Для полученной выборки вычислим ее среднее значение и стандартное отклонение при помощи функций СРЗНАЧ и СТАНДОТКЛОН:

Использование НОРМ.РАСП для расчета плотности вероятности

Теперь при помощи функции НОРМ.РАСП вычислим теоретическую плотность вероятности для разных значений исходя из полученных параметров распределения (M=55, σ=20):

Видно, что в точке математического ожидания 55 плотность максимальна.

Интерпретация полученных результатов

Итак, мы построили график плотности вероятности для нормального распределения со средним 55 и стандартным отклонением 20. Как можно интерпретировать полученные результаты?

• Максимальная плотность 0.0797 соответствует среднему значению 55. Это логично, поскольку в точке математического ожидания плотность достигает пика.
• Чем дальше от центра, тем плотность быстро убывает. Например, для нуля она составляет всего 0.004.
• Зная плотность, можно вычислить вероятность попадания в тот или иной интервал значений. Чем выше плотность на данном интервале, тем выше вероятность.

Примеры использования для решения практических задач

Нормальное распределение в Excel применяется в самых разных областях:

• При анализе финансовых и экономических показателей
• В задачах управления качеством продукции
• При обработке результатов социологических и маркетинговых исследований
• В инженерных расчетах и моделировании сложных систем

Рассмотрим некоторые конкретные примеры.

Анализ устойчивости финансовых показателей компании

Пусть известно, что месячная прибыль некой компании в среднем составляет 500 000 рублей со стандартным отклонением 100 000 рублей. Требуется оценить вероятность того, что в следующем месяце:

1. Прибыль не превысит 400 000 рублей
2. Прибыль будет больше 300 000 рублей

Для решения воспользуемся функцией НОРМ.СТ.РАСП. Предварительно стандартизируем значения показателя:

Теперь вычисляем требуемые вероятности:

Итак, вероятность снижения прибыли ниже 400 000 составляет 16%, а 300 000 рублей будет превышена с вероятностью 97.7%.

Определение допусков на размер детали

Рассмотрим задачу определения допустимых отклонений размера некой детали. Пусть технологический процесс настроен на средний размер 12 мм со стандартным отклонением 0.05 мм. Требуется установить допуск, в который будет попадать 95% всех деталей.

Решение:

1. Находим граничные стандартизированные значения: ±1.96
2. Переводим их в миллиметры: ±1.96 * 0.05 = ±0.1 мм
3. Получаем допуск: 12 мм ± 0.1 мм

Таким образом, 95% всех деталей будут иметь размер в интервале от 11.9 мм до 12.1 мм.