Функция НОРМ.ОБР и обратное нормальное распределение в Excel

Функция НОРМ.ОБР в Excel предназначена для расчета значения нормальной распределенной переменной при известных значениях вероятности, средней величины и стандартного отклонения, и возвращает соответствующее число. Данная функция является обратной для НОРМ.РАСП.

Как рассчитать обратное стандартное нормальное распределение в Excel

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

Центральная предельная теорема теории вероятности.

Нормальное распределение имеет два характеризующих его параметра: математическое ожидание (), определяющее положение центра плотности вероятности, и стандартное отклонение (), характеризующее разброс относительно центральной части.

Пример 1. Длина грифа гитары, производимого на заводе, составляет в среднем 650 мм, стандартное отклонение в партии составляет 12 мм. Определить максимальную длину грифа по техническим условиям, чтобы 95% всех грифов соответствовали ей.

Вид таблицы данных:

Пример 1.

Для решения введем в ячейку B5 функцию:

Полученное значение:

НОРМОБР.

В результате вычислений функции получаем наиболее оптимальные параметры.



Генератор случайных чисел с нормальным распределением в Excel

Пример 2. В таблицу Excel введены некоторые данные из эксперимента. Необходимо сгенерировать на основе имеющихся данных массив случайных числовых значений с нормальным распределением.

Вид таблицы данных:

Пример 2.

Для решения выделим 11 ячеек соседнего столбца B и запишем следующую формулу массива CTRL+SHIFT+Enter:

В качестве первого аргумента используется функция СЛЧИС, возвращающая случайные числа из диапазона от 0 до 1 (оба включительно), что соответствует диапазону допустимых значений вероятности. Для определения второго аргумента используется функция СРЗНАЧ, возвращающая среднее арифметическое для исследуемого распределения. Третий аргумент – результат выполнения функции СТАНДОТКЛОН.Г.

Полученный результат:

Генератор случайных чисел.

Правила использования функции НОРМ.ОБР в Excel

Функция имеет следующую синтаксическую запись:

=НОРМ.ОБР(вероятность;среднее;стандартное_откл;)

Описание аргументов:

  • вероятность – обязательный, принимает числовое значение из диапазона [0;1], которое соответствует вероятности для нормального распределения.
  • среднее – обязательный, принимает число, соответствующее значению среднего арифметического для исследуемого распределения величин.
  • стандартное_откл – обязательный, принимает число, соответствующее стандартному отклонению, определенному для исследуемого распределения величин. Может быть рассчитано с использованием функций СТАНДОТКЛОН.В (выборка) и СТАНДОТКЛОН.Г (генеральная совокупность).

Примечания:

  1. Любой аргумент рассматриваемой функции должен быть указан числом или данными, которые могут быть преобразованы к числовым значения. Иначе НОРМ.ОБР вернет код ошибки #ЗНАЧ!
  2. Если аргумент среднее рассматриваемой функции принимает значение 0, а третий – 1, результат ее выполнения эквивалентен результату, возвращаемого функцией НОРМ.СТ.ОБР (то есть, рассматриваемое распределение является стандартным нормальным).
  3. Между функциями НОРМ.РАСП и НОРМ.ОБР существует следующая взаимосвязь: =НОРМ.РАСП(НОРМ.ОБР(вероятность;среднее;стандартное_откл);среднее;стандартное_откл;ИСТИНА).
  4. Ошибка #ЧИСЛО! генерируется в следующих ситуациях:
  • первый аргумент указан числом, находящимся вне диапазона допустимых значений, то есть >1 или <0.
  • второй аргумент указан в виде числового значения, взятого из диапазона отрицательных чисел или введен 0 (нуль).

en ru