Ежедневно миллионы пользователей Excel анализируют данные с помощью статистических методов. Одним из наиболее распространенных подходов является использование нормального распределения вероятностей. В этой статье мы подробно разберем, как с помощью всего нескольких функций Excel построить график нормального распределения и интерпретировать полученные результаты.
Теоретические основы нормального распределения
Нормальное распределение - это один из наиболее важных законов распределения случайных величин в теории вероятностей и математической статистике. Он позволяет оценить вероятность попадания случайной величины в тот или иной интервал значений.
Свойства нормального распределения
- Симметричность относительно математического ожидания
- Форма колоколообразной кривой с максимумом в точке матожидания
- Асимптотическое приближение к оси абсцисс
На практике нормальным распределением часто аппроксимируют эмпирические распределения, полученные в результате наблюдений и экспериментов. Это связано с центральной предельной теоремой, согласно которой сумма большого числа случайных величин приближается к нормальному распределению.
Параметры нормального распределения
Нормальное распределение задается двумя параметрами:
- Математическим ожиданием (обозначается буквой
M) - Среднеквадратичным отклонением или стандартным отклонением (обозначается буквой
σ)
Первый параметр определяет положение центра распределения, второй - его "размазанность".
Встроенные функции Excel для работы с нормальным распределением
Для работы с нормальным законом распределения в Excel предусмотрено 3 основные функции.
1. НОРМ.РАСП - вычисление плотности вероятности
Функция НОРМ.РАСП вычисляет плотность вероятности для заданных параметров M и σ. Например, для нормального распределения со средним 50 и стандартным отклонением 5 ее можно использовать так:
| NORM.DIST(45; 50; 5; FALSE) | =0.10798 |
Полученное значение 0.10798 и есть плотность вероятности для числа 45.
2. НОРМ.СТ.РАСП - вычисление вероятности для стандартизированных значений
Функция НОРМ.СТ.РАСП используется для вычисления вероятностей, когда исходные данные приведены к стандартному нормальному распределению со средним 0 и стандартным отклонением 1.
Например, с ее помощью можно найти вероятность того, что стандартизированная случайная величина примет значение меньшее 1.96:
| NORM.S.DIST(1.96; TRUE) | =0.975 |
То есть с вероятностью 97.5% стандартизированная нормальная случайная величина будет меньше 1.96.
3. НОРМ.ОБР - вычисление значения случайной величины по заданной вероятности
Если требуется найти само значение нормально распределенной случайной величины при известной вероятности, используют функцию НОРМ.ОБР.
Например, можно определить значение, ниже которого с вероятностью 95% будут находиться значения нормально распределенной случайной величины со средним 170 и стандартным отклонением 12:
| NORM.INV(0.95; 170; 12) | =156.6 |
Таким образом, с вероятностью 95% значения не будут меньше 156.6.
Построение нормального распределения в Excel на практике
Нормальное распределение в Excel используется в различных областях: от проверки статистических гипотез до моделирования и прогнозирования процессов. Рассмотрим последовательность его построения на конкретном примере.
Генерирование выборки случайных чисел
Сначала с помощью функции СЛЧИС сгенерируем выборку из 100 случайных чисел, равномерно распределенных на интервале от 0 до 100:
| СЛЧИС() |
Расчет параметров распределения: СРЗНАЧ, СТАНДОТКЛОН
Для полученной выборки вычислим ее среднее значение и стандартное отклонение при помощи функций СРЗНАЧ и СТАНДОТКЛОН:
| СРЗНАЧ() | =55 |
| СТАНДОТКЛОН() | =20 |
Использование НОРМ.РАСП для расчета плотности вероятности
Теперь при помощи функции НОРМ.РАСП вычислим теоретическую плотность вероятности для разных значений исходя из полученных параметров распределения (M=55, σ=20):
| NORM.DIST(0; 55; 20; FALSE) | =0.004 |
| NORM.DIST(55; 55; 20; FALSE) | =0.0797 |
Видно, что в точке математического ожидания 55 плотность максимальна.
Интерпретация полученных результатов
Итак, мы построили график плотности вероятности для нормального распределения со средним 55 и стандартным отклонением 20. Как можно интерпретировать полученные результаты?
- Максимальная плотность 0.0797 соответствует среднему значению 55. Это логично, поскольку в точке математического ожидания плотность достигает пика.
- Чем дальше от центра, тем плотность быстро убывает. Например, для нуля она составляет всего 0.004.
- Зная плотность, можно вычислить вероятность попадания в тот или иной интервал значений. Чем выше плотность на данном интервале, тем выше вероятность.
Примеры использования для решения практических задач
Нормальное распределение в Excel применяется в самых разных областях:
- При анализе финансовых и экономических показателей
- В задачах управления качеством продукции
- При обработке результатов социологических и маркетинговых исследований
- В инженерных расчетах и моделировании сложных систем
Рассмотрим некоторые конкретные примеры.
Анализ устойчивости финансовых показателей компании
Пусть известно, что месячная прибыль некой компании в среднем составляет 500 000 рублей со стандартным отклонением 100 000 рублей. Требуется оценить вероятность того, что в следующем месяце:
- Прибыль не превысит 400 000 рублей
- Прибыль будет больше 300 000 рублей
Для решения воспользуемся функцией НОРМ.СТ.РАСП. Предварительно стандартизируем значения показателя:
| (400 000 - 500 000) / 100 000 = -1 |
| (300 000 - 500 000) / 100 000 = -2 |
Теперь вычисляем требуемые вероятности:
| НОРМ.СТ.РАСП(-1) | =0.84 |
| НОРМ.СТ.РАСП(-2) | =0.977 |
Итак, вероятность снижения прибыли ниже 400 000 составляет 16%, а 300 000 рублей будет превышена с вероятностью 97.7%.
Определение допусков на размер детали
Рассмотрим задачу определения допустимых отклонений размера некой детали. Пусть технологический процесс настроен на средний размер 12 мм со стандартным отклонением 0.05 мм. Требуется установить допуск, в который будет попадать 95% всех деталей.
Решение:
- Находим граничные стандартизированные значения: ±1.96
- Переводим их в миллиметры: ±1.96 * 0.05 = ±0.1 мм
- Получаем допуск: 12 мм ± 0.1 мм
Таким образом, 95% всех деталей будут иметь размер в интервале от 11.9 мм до 12.1 мм.
