НОРМСТРАСП функция стандартного нормального распределения в Excel

Требуется построить диаграмму стандартного нормального интегрального распределения (стандартное нормальное распределение имеет М = 0 и = 1), используя функцию НОРМСТРАСП.

1. В ячейку A3 введем символ х, а в ячейку ВЗ — символ функции плотности вероятности f(x).

2. Вычислим нижнюю М — За границу диапазона значений х, для чего установим курсор в ячейку С2 и введем формулу =0-3*1, а также верхнюю границу — в ячейку Е2 введем формулу =0+3*1.

3. Скопируем формулу из ячейки С2 в ячейку А4, полученное в ячейке А4 значение нижней границы будет началом последовательности арифметической прогрессии.

4. Создадим последовательность значений х в требуемом диапазоне, для чего установим курсор в ячейку А4 и выполним команду меню Правка/Заполнить/Прогрессия.

5. В открывшемся окне диалога Прогрессия установим переключатели арифметическая, по столбцам, в поле Шаг введем значение 0,5, а в поле Предельное значение — число, равное верхней границе диапазона.

заполнение диалогового окна Прогрессия в EXCEL

Функция НОРМРАСПР в EXCEL

6. Щелкнем на кнопке ОК. В диапазоне А4:А16 будет сформирована последовательность значений х.

7. Установим курсор в ячейку В4 и выполним команду меню Вставка/Функция. В открывшемся окне Мастер функций выберем категорию Статистические, а в списке функций — НОРМРАСП.

8. Установим значения параметров функции НОРМРАСП: для параметра х установим ссылку на ячейку А4, для параметра Среднее — введем число 0, для параметра Стандартное_откл — число 1, для параметра Интегральное — число 0 (весовая).

аргументы функции НОРМРАСПР в Excel

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

9. Используя маркер буксировки, скопируем полученную формулу в диапазон ячеек В5:В16.

10. Выделим диапазон полученных табличных значений функции f(х) (ВЗ:В16) и выполним команду меню Вставка/Диаграмма. В окне Мастер диаграмм во вкладке Стандартные выберем График, а в поле Вид — вид графика, щелкнем на кнопке Далее.

11. В окне Мастер диаграмм (шаг 2) выберем закладку Ряд. В поле Подписи оси х укажем ссылку на диапазон, содержащий значения х (А4:А16). Щелкнем на кнопке Далее.
В окне Мастер диаграмм (шаг 3) введем подписи: Название диаграммы, Ось х, Ось у. Щелкнем на кнопке Готово. На рабочий лист будет выведена диаграмма плотности вероятности .

Функция НОРМСТРАСП

Функция НОРМСТРАСП — это одна из статистических функций. Возвращает стандартное нормальное интегральное распределение.

Синтаксис функции НОРМСТРАСП:

НОРМСТРАСП(число)

где число — это числовое значение, введенное вручную или находящееся в ячейке, на которую дается ссылка.

Чтобы применить функцию НОРМСТРАСП,

  1. выделите ячейку, в которой требуется отобразить результат,
  2. щелкните по значку Вставить функциюЗначок Вставить функцию, расположенному на верхней панели инструментов,
    или щелкните правой кнопкой мыши по выделенной ячейке и выберите в меню команду Вставить функцию,
    или щелкните по значку Значок Функцияперед строкой формул,
  3. выберите из списка группу функций Статистические,
  4. щелкните по функции НОРМСТРАСП,
  5. введите требуемый аргумент,
  6. нажмите клавишу Enter.

Функция НОРМСТРАСП(Z)

Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент — Z, вычисляемый функцией НОРМАЛИЗАЦИЯ().

Нетрудно заметить,что эти функции следует использовать вместе. При этом наиболее эффективным и компактным способом их задания является указаниефункции НОРМАЛИЗАЦИЯ() в качестве аргумента функции — НОРМСТРАСП(), т.е.:

=НОРМСТРАСП(НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)).

С целью повышения наглядности, в проектируемом шаблоне функции заданы раздельно (ячейки Е18 и F18).

Сформируйте данный шаблон и сохраните его на магнитном диске под именем SIMUL_1. Приступаем к имитационному эксперименту. Для его проведения необходимо выполнить следующие шаги.

1. Ввести значения постоянных переменных (табл. 1.2) в ячейки В2:В4 и D2:D4 листа "Результаты анализа".

2. Ввести значения диапазонов изменений ключевых переменных (табл. 1.1) в ячейки В3:С5 листа "Имитация".

3. Нажатием клавиши F9 провести расчет.

4. Перейти к листу "Результаты анализа" и проанализировать полученные результаты.

Результатом выполнения этих действий будет заполнение блока А10:Е510случайными значениями ключевых переменных V, Q, P и результатами вычислений величин NCF и NPV. Фрагмент результатов имитации приведен на рис. 1.3. Соответствующие проведенному эксперименту результаты анализа приведены на рисунке 1.4.

Рис. 1.3- Результаты имитации

Рис. 1.4 — Результаты анализа

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

В данном случае они наглядно демонстрируют несоизмеримость суммы возможных убытков по отношению к общей сумме доходов (-11691,92 и 1692669,76 соответственно).

На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Ограничимся визуальным (графическим) исследованием. На рисунке 1.5 приведен график распределения значений ключевых параметров V, P и Q, построенный на основании 75 имитаций.

Нетрудно заметить, что в целом, вариация значений всех трех параметров носит случайный характер, что подтверждает принятую ранее гипотезу об их независимости. Для сравнения ниже приведен график распределений потока платежей NCF и величины NPV (рисунок 1.6).

Методология работы в программе MS EXCEL с функциями нормального распределения и распределения Стьюдента

Функция НОРМРАСП(рис. 5.8) определяет плотность f и интегральную функцию F нормального распределения при заданных аргументах:

— «x» — значение X, для которого определяются значения f или F.

— «Среднее» — математическое ожидание (или его оценка), представленное самим значением или ссылкой на ячейку, содержащую результат его расчёта.

— «Стандартное откл» — стандартное отклонение распределения (или оценка стандартного отклонения) или ссылка на ячейку, содержащую результат его расчёта.

— «Интегральная» — направление расчёта: плотность f или интегральная функция F распределения, см. § 2.4.

Рис. 5.8. Аргументы функции НОРМРАСП

Например, поставлена задача определения вероятности попадания некоторой характеристики (размера, какого-либо механического свойства и т.д.) в поле допуска с границами (x1, x2), если известна выборка значений этой характеристики. Тогда, подставляя в аргументы функции НОРМРАСП рассчитанные «Среднее» и «Стандартное откл», в «Интегральная» — значение ИСТИНА, а в «x» — последовательно x1 и x2, определяют интегральную функцию и для нижней и верхней границы поля допуска. Искомая вероятность представляет собой разность —

Функция НОРМСТРАСП определяет интегральное функцию стандартного нормального распределения (здесь не представлена). Эта функция используется вместо справочной таблицы для стандартной нормальной кривой (приложение ….). Поскольку для стандартного нормального распределения =0 и σ=1 известны, в функции НОРМСТРАСП представлен лишь один аргумент — z.

НОРМОБР — функция, обратная функции НОРМРАСП, определяющей по заданному значению x интегральную функцию F. НОРМОБР (рис. 5.9) для указанного среднего и стандартного отклонения по заданному значению вероятности ищет значение x, используя метод итераций. (Такую же операцию выполняет и не представленная здесь функция НОРМСТОБР, но для стандартного нормального распределения.)

Рис. 5.9. Аргументы функции НОРМОБР

Как правило, работа технолога состоит в разработке процесса производства, обеспечивающего попадание с максимальной вероятностью характеристик качества в заданные границы допусков. Но бывают случаи, особенно в мелкосерийном производстве, когда необходимо исходить из возможностей уже существующего процесса: определить какие границы допуска при неизменной настройке можно обеспечить с заданной степенью достоверности (или уровня значимости). Именно тогда функцию НОРМОБР удобно использовать для нахождения границ доверительного интервала, обеспечивающих необходимую вероятность попадания в этот интервал контролируемой характеристики, см. § 6.6. Для этого уровень значимости α, например 0,05, делят на две части, обычно равные. Для нахождения нижней границы интервала в качестве аргумента «Вероятность» (см. рис. 5.9) ставят величину α/2 (= 0,025). Для определения верхней границы в качестве аргумента «Вероятность» ставят величину 1,0 — α/2 (= 0,975).

Но бывают случаи, когда выпады за пределы нижней и верхней границы доверительного интервала неравноценны по своим последствиям. Например, при изготовлении детали выпад в одну сторону от допуска может означать исправимый, а в другую — неисправимый брак, см. § 6.6. В этом случае по сравнению с предыдущим уровень значимости α, например 0,05, делят на две неравные части. Меньшую часть назначают в сторону области неисправимого брака (например, при обработке вала — в сторону меньших размеров). Наоборот, бóльшую часть α назначают в сторону исправимого брака, то есть при обработке вала в сторону бóльших размеров.

Таким образом, будучи родственной функции ДОВЕРИТ (см. ниже § 6.3), устанавливающей только ширину доверительного интервала, функция НОРМОБР имеет более широкие возможности в отношении установления расположения доверительного интервала, см. ниже § 6.6.

Функция СТЬЮДРАСП (рис. 5.10) определяет процентные точки (вероятность) для t-распределения Стьюдента, используемого для проверки гипотез при малом объеме выборки. При увеличении n оно приближается к нормальному распределению. (В аргументах функции СТЬЮДРАСП, как и в аргументах функции НОРМСТРАСПотсутствуют математическое ожидание и дисперсия.)

Рис. 5.10. Аргументы функции СТЬЮДРАСП

В качестве аргументов функции используются:

— «x» — это значение, для которого вычисляются вероятности;

— «Степени_свободы» — целое, указывающее число степеней свободы k;

— «Хвосты» — число, которое может быть равно 1 или 2 и определяет следующим образом характер распределения: если «Хвосты» = 1, то функция СТЬЮДРАСП определяет одностороннее распределение; если «Хвосты» = 2, то функция СТЬЮДРАСП определяет двухстороннее распределение.

Расчет производится только для x ³ 0. Но следует помнить, что для одностороннего распределения: СТЬЮДРАСП(-x,df,1) = 1 – СТЬЮДРАСП(x,df,1) и для двустороннего распределения: СТЬЮДРАСП(-x,df,2) = СТЬЮДРАСП(x df,2). То есть распределение Стьюдента можно «достроить» и для области x < 0.

Данную функцию можно использовать вместо таблицы критических значений t-распределения (Приложение …) для определения значений односторонней или двусторонней доверительной вероятности. Например, для t-распределения с числом степеней свободы k = 9 (выборка объёмом n = 10) для значения t = 2,262 (вставляется в «x») для одностороннего распределения («Хвосты» = 1) будет получен уровень значимости α = 0,025, а для двустороннего распределения («Хвосты» = 2) — уровень значимости α = 0,05 (см. приложение ).

Функция СТЬЮДРАСПОБР (рис. 5.11) в практике статистического анализа используется более часто, чем Функция СТЬЮДРАСП, т.к. определяет критическое значение t-распределения для заданных доверительной вероятности (соответствующей двустороннему распределению Стьюдента) и числа степеней свободы.

Рис. 5.11. Аргументы функции СТЬЮДРАСПОБР

Таким образом, при использовании функции СТЬЮДРАСПОБР для заданного уровня значимости α = 0,05 и числа степеней свободы k = 9 получается заданное ранее (см. выше) двустороннее t-значение 2,262 (см. также приложение ).

При необходимости установления одностороннего t-значения оно может быть получено при замене аргумента «Вероятность» на удвоенное (2*α) значение. В результате подстановки в аргумент «Вероятность» значения 0,1=2*0,05 для того же числа степеней свободы k=9 получается одностороннее t-значение, равное 1,833.

Функция НОРМСТРАСП(Z)

Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент — Z, вычисляемый функцией НОРМАЛИЗАЦИЯ().

Нетрудно заметить,что эти функции следует использовать в тандеме. При этом наиболее эффективным и компактным способом их задания является указаниефункции НОРМАЛИЗАЦИЯ() в качестве аргумента функции — НОРМСТРАСП(), т.е.:

=НОРМСТРАСП(НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)).

С целью повышения наглядности, в проектируемом шаблоне функции заданы раздельно (ячейки Е18 и F18).

Сформируйте данный шаблон и сохраните его на магнитном диске под именем SIMUL_1.XLT. Приступаем к имитационному эксперименту. Для его проведения необходимо выполнить следующие шаги.

  1. Ввести значения постоянных переменных (табл. 3.2) в ячейки В2.В4 и D2.D4 листа "Результаты анализа".
  2. Ввести значения диапазонов изменений ключевых переменных (табл. 3.1) в ячейки В3.С5 листа "Имитация".
  3. Задать в ячейке В7 требуемое число экспериментов.
  4. Установить курсор в ячейку А11 и вставить необходимое число строк в шаблон (номер последней строки будет вычислен в Е7).
  5. Скопировать формулы блока А10.Е10 требуемое количество раз.
  6. Перейти к листу "Результаты анализа" и проанализировать полученные результаты.

Рассмотрим реализацию выделенных шагов более подробно. Выполнение первых трех пунктов не должно вызвать особых затруднений. Введите значения постоянных переменных в ячейки В2.В4 листа "Результаты анализа". Введите значения диапазонов изменений ключевых переменных в ячейки В3.С5 листа "Имитация". Укажите в ячейке В7 число проводимых экспериментов, например — 500. Установите табличный курсор в ячейку А11.

На следующем шаге необходимо вставить в шаблон нужное количество строк (498) (Поскольку первая и последняя строка блока уже определены, число вставляемых строк равно: 500 — 2 = 498). Однако выделение такого количества строк при помощи указателя мыши — достаточно трудоемкая операция. К счастью ППП EXCEL предоставляет более эффективные процедуры для выполнения подобных операций. В частности, в данном случае можно воспользоваться операцией перехода, которую также удобно применять и для выделения больших диапазонов ячеек.

Нажмите функциональную клавишу [F5]. На экране появится окно диалога "Переход" (рис. 3.3).

Рис. 3.3. Окно диалога "Переход"

Для перехода к нужному участку электронной таблицы достаточно указать в поле "Ссылка" адрес или имя соответствующей ячейки (блока). В данном случае, таким адресом будет любая ячейка последней вставляемой строки, номер которой вычислен в ячейке Е7 (508). Например, в качестве адреса перехода может быть указана ячейка А508.

Читайте также:  Решение уравнений в Excel методом итераций Крамера и Гаусса

Введите в поле "Ссылка" адрес: А508 и нажмите комбинацию клавиш [SHIFT] + [ENTER]. Результатом выполнения этих действий будет выделение блока А11.А508. После чего осуществите вставку строк любым из известных вам способов.

Теперь необходимо заполнить вставленные строки формулами блока ячеек А10.Е10. Для этого выполните следующие действия.

    1. Выделите и скопируйте в буфер блок ячеек А10.Е10.
    2. Нажмите комбинацию клавиш [CTRL] + [SHIFT] + [ ].
    3. Нажмите клавишу [ENTER].
    4. Нажмите клавишу [F9] (Этот пункт выполняется в том случае, если был установлен режим ручного пересчета).

    Результатом выполнения этих действий будет заполнение блока А10.Е509случайными значениями ключевых переменных V, Q, P и результатами вычислений величин NCF и NPV. Фрагмент результатов имитации, полученных автором, приведен на рис. 3.4 (Необходимо все время помнить о случайной природе эксперимента. Полученные вами результаты будут отличаться от приведенных). Соответствующие проведенному эксперименту результаты анализа приведены на рис. 3.5.

    Рис. 3.4. Результаты имитации

    Рис. 3.5. Результаты анализа

    Нетрудно заметить, что по результатам имитационного анализа риск проекта значительно ниже. Величина ожидаемой NPV меньше результата предыдущего анализа (3361,96 и 4502,30 соответственно). Однако величина стандартного отклонения также существенно ниже (2271,31 и 4673,62) и не превышает значения NPV. Коэффициент вариации (0,68) меньше 1, таким образом риск данного проекта в целом ниже среднего риска инвестиционного портфеля фирмы. Результаты вероятностного анализа показывают, что шанс получить отрицательную величину NPV не превышает 7%. Еще больший оптимизм внушают результаты анализа распределения чистых поступлений от проекта NCF. Величина стандартного отклонения здесь составляет всего 42% от среднего значения. Таким образом с вероятностью более 90% можно утверждать, что поступления от проекта будут положительными величинами.

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

    В данном случае они наглядно демонстрируют несоизмеримость суммы возможных убытков по отношению к общей сумме доходов (-11691,92 и 1692669,76 соответственно).

    На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Как было показано в предыдущей главе, количественная оценка вариации напрямую зависит от степени корреляции между случайными величинами. Методы оценки степени зависимости, а также технология ее автоматизации путем применения специальных инструментов ППП EXCEL, будут продемонстрированы ниже. Здесь же мы ограничимся визуальным (графическим) исследованием. На рис. 3.6 приведен график распределения значений ключевых параметров V, P и Q, построенный на основании 75 имитаций.

    Нетрудно заметить, что в целом, вариация значений всех трех параметров носит случайный характер, что подтверждает принятую ранее гипотезу о их независимости. Для сравнения ниже приведен график распределений потока платежей NCF и величины NPV (рис. 3.7).

    Рис. 3.6 Распределение значений параметров V, P и Q

    Рис. 3.7. Зависимость между NCF и NPV

    Как и следовало ожидать, направления колебаний здесь в точности совпадают и между этими величинами существует сильная корреляционная связь, близкая к функциональной. Дальнейшие расчеты показали, что величина коэффициента корреляции между полученными распределениями NCF и NPV оказалась равной 1.

    Подводя итоги отметим, что в целом применение рассмотренной технологии проведения имитационных экспериментов в среде EXCEL — достаточно трудоемкий процесс, который к тому же ограничивается случаем равномерного распределения исследуемых переменных.

    Гораздо более удобным и эффективным способом решения таких задач в среде ППП EXCEL является использование специального инструмента анализа — "Генератор случайных чисел".

    НОРМСТОБР (NORMSINV)

    Вычисляет обратное стандартное нормальное распределение для указанного значения.

    Пример использования

    Синтаксис

    • x – значение, для которого требуется вычислить обратную стандартную функцию нормального распределения.

    Примечания

    • «Стандартная» функция нормального распределения – это функция нормального распределения, у которой среднее значение равно 0 , а дисперсия (и, следовательно, стандартное отклонение) равны 1 .

    См. также:

    ВЕЙБУЛЛ : Вычисляет распределение Вейбулла для заданной формы и масштаба.

    ПУАССОН : Вычисляет распределение Пуассона на основании заданной величины и среднего арифметического распределения.

    НОРМСТРАСП : Вычисляет стандартное нормальное распределение для указанного значения.

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

    НОРМРАСП : Вычисляет нормальное распределение. Расчеты включают заданное значение, среднее арифметическое и стандартное отклонение распределения.

    ОТРБИНОМРАСП : Вычисляет вероятность получения определенного количества успешных результатов после проведения определенного числа неудачных испытаний.

    ЛОГНОРМРАСП : Вычисляет логнормальное распределение. Расчеты включают среднее арифметическое и стандартное отклонение распределения.

    ЛОГНОРМОБР : Вычисляет обратное логнормальное распределение. Расчеты включают среднее арифметическое и стандартное отклонение распределения.

    ЭКСПРАСП : Вычисляет экспоненциальное распределение для заданных значений X и λ.

    БИНОМРАСП : Вычисляет вероятность получения указанного (или максимального) числа успешных результатов из определенного числа испытаний. В расчет принимается совокупность испытаний с определенным числом успешных результатов.

    НОРМСТРАСП (NORMSDIST)

    Вычисляет стандартное нормальное распределение для указанного значения.

    Пример использования

    Синтаксис

    • x – значение, для которого требуется вычислить стандартную интегральную функцию нормального распределения.

    Примечания

    • «Стандартная» функция нормального распределения – это функция нормального распределения, у которой среднее значение равно 0 , а дисперсия (и, следовательно, стандартное отклонение) равны 1 .

    См. также:

    ZТЕСТ : Возвращает двустороннее P-значение Z-теста со стандартным распределением.

    ВЕЙБУЛЛ : Вычисляет распределение Вейбулла для заданной формы и масштаба.

    ПУАССОН : Вычисляет распределение Пуассона на основании заданной величины и среднего арифметического распределения.

    НОРМСТОБР : Вычисляет обратное стандартное нормальное распределение для указанного значения.

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

    НОРМРАСП : Вычисляет нормальное распределение. Расчеты включают заданное значение, среднее арифметическое и стандартное отклонение распределения.

    ОТРБИНОМРАСП : Вычисляет вероятность получения определенного количества успешных результатов после проведения определенного числа неудачных испытаний.

    ЛОГНОРМРАСП : Вычисляет логнормальное распределение. Расчеты включают среднее арифметическое и стандартное отклонение распределения.

    ЛОГНОРМОБР : Вычисляет обратное логнормальное распределение. Расчеты включают среднее арифметическое и стандартное отклонение распределения.

    ЭКСПРАСП : Вычисляет экспоненциальное распределение для заданных значений X и λ.

    БИНОМРАСП : Вычисляет вероятность получения указанного (или максимального) числа успешных результатов из определенного числа испытаний. В расчет принимается совокупность испытаний с определенным числом успешных результатов.

    НОРМРАСП (функция НОРМРАСП)

    ​ Обязательный. Среднее арифметическое​ для указанного среднего​ плотность распределения. Поэтому,​ в MS EXCEL​ распределению, приняла отрицательное​того, что непрерывная​ НОРМ.РАСП(x; среднее; стандартное_откл;​ х=0,05 равно 3,894.​

    ​ EXCEL.​​ будет равна бесконечности,​ Сумма вероятностей всех​1​ если процесс настроен​ события X​Поскольку в математической статистике,​ приведенных выше условий​ содержит значение ЛОЖЬ)​ распределения.​ и стандартного отклонения.​ в аргументах функции​ имеются соответствующие функции,​ значение. Согласно определения​ случайная величина примет​интегральная​

    ​ Но, при этом​В литературе Функция плотности​ а не 1.​ возможных значений случайной​

    Синтаксис

    ​ на изготовление деталей​F(x) = P(X​

    ​ любой вывод делается​​0,9087888​ имеет следующий вид:​

    ​Стандартное_откл​​ Эта функция очень​ НОРМ.СТ.ОБР() отсутствует параметр​

    ​ позволяющие вычислить вероятности.​​ Функции распределения, вероятность​ конкретное значение x​

    ​). Если функция MS​​ можно убедиться, что​ распределения непрерывной случайной величины​Выходом из этой​ величины равна 1.​2​ весом 195 г,​Поясним на примере нашего​ только на основании​=НОРМРАСП(A2;A3;A4;ЛОЖЬ)​Если аргумент «интегральная» имеет​

    Замечания

    ​ Обязательный. Стандартное отклонение​ широко применяется в​интегральная​Вспомним задачу из предыдущего​ равна F(0)=0,5.​

    ​ равна 0. Для​ EXCEL должна вернуть​ вероятность на любом​ может называться: Плотность​ ситуации является введение​

    ​Примечание​)-F(x​ то разумно предположить,​ станка. Хотя предполагается,​ характеристики Х (абстрагируясь​Функция плотности распределения для​ значение ИСТИНА, формула​

    ​ распределения.​ статистике, в том​, который подразумевается. Подробнее​ раздела: Найдем вероятность,​

    ​В MS EXCEL для​ непрерывной случайной величины​ Функцию распределения, то​ интервале будет, как​ вероятности, Плотность распределения,​

    Пример

    ​ так называемой функции​: В MS EXCEL имеется​1​ что вероятность выбрать​ что наш станок​ от самих объектов),​ приведенных выше условий​ описывает интеграл с​Интегральная​ числе при проверке​ про функцию НОРМ.СТ.ОБР()​ что случайная величина,​ нахождения этой вероятности​

    ​ Х можно вычислить​

    ​ параметр интегральная, д.б.​

    ​ англ. Probability Density​ плотности распределения p(x).​

    ​ несколько функций, позволяющих​

    ​ деталь легче 195​

    ​ производит только один​

    ​ пределами от минус​

    ​ Обязательный. Логическое значение,​

    ​ гипотез.​ см. статью про​

    ​ распределенная по стандартному​

    ​ используйте формулу =НОРМ.СТ.РАСП(0;ИСТИНА)​

    ​ только вероятность события,​ установлен ИСТИНА. Если​

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

    Диаграмма нормального распределения симметрична относительно точки а (математического ожидания). Ме­диана нормального распределения равна тоже а. При этом в точке а функция f(x) достигает своего максимума, который равен

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

    Функция имеет параметры:

    НОРМРАСП (х; среднее; стандартное_откл; интегральная) , где:

    х — значения выборки, для которых строится распределение;

    среднее — среднее арифметическое выборки;

    стандартное_откл — стандартное отклонение распределения;

    интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интег­ральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.

    Если среднее = 0 и стандартное_откл = 1, то функция НОРМРАСП возвращает стан­дартное нормальное распределение.

    Пример 2 . Построить график нормальной функции распределения f ( x ) при x , меняющемся от 19,8 до 28,8 с шагом 0,5, a =24,3 и

    1. В ячейку А1 вводим символ случайной величины х, а в ячейку B 1 — символ фун­кции плотности вероятности — f ( x ) .

    2. Вводим в диапазон А2:А21 значе­ния х от 19,8 до 28,8 с шагом 0,5. Для этого воспользуемся маркером автозаполнения: в ячейку А2 вводим левую границу диапазона (19,8), в ячейку A3 левую границу плюс шаг (20,3). Выделяем блок А2:А3. Затем за правый нижний угол протягиваем мышью до ячейки А21 (при нажатой левой кнопке мыши).

    3. Устанавливаем табличный курсор в ячейку В2 и для получения значения веро­ятности воспользуемся специальной функцией — нажимаем на панели инстру­ментов кнопку Вставка функции ( fx ) . В появившемся диалоговом окне Мастер функций — шаг 1 из 2 слева в поле Категория указаны виды функций. Выбираем Статистическая. Справа в поле Функция выбираем функцию НОРМРАСП. Нажимаем на кнопку ОК.

    4. Появляется диалоговое окно НОРМРАСП. В рабочее поле X вводим адрес ячейки А2 щелчком мыши на этой ячейке. В рабочее поле Среднее вводим с клавиатуры значение математиче­ского ожидания (24,3). В рабочее поле Стандартное_откл вводим с клавиатуры значение среднеквадратического отклонения (1,5). В ра­бочее поле Интегральная вводим с клавиатуры вид функции распределения (0). Нажимаем на кнопку ОК.

    5. В ячейке В2 появляется вероятность р = 0,002955. Указателем мыши за правый нижний угол табличного курсора протягиванием (при нажатой левой кнопке мыши) из ячейки В2 до В21 копируем функцию НОРМРАСП в диапазон В3:В21.

    6. По полученным данным строим искомую диаграмму нормальной функции рас­пределения. Щелчком указателя мыши на кнопке на панели инструментов вызы­ваем Мастер диаграмм. В появившемся диалоговом окне выбираем тип диаграммы График, вид — левый верхний. После нажатия кнопки Далее указываем диапазон данных — В1:В21 (с помощью мыши). Проверяем, положение переключателя Ряды в: столбцах. Выбираем закладку Ряд и с помощью мыши вводим диапазон подписей оси X: А2:А21. Нажав на кнопку Далее, вводим названия осей Х и У и нажимаем на кнопку Готово.

    Рис. 1 График нормальной функции распределения

    Получен приближенный график нормальной функции плотности распределения (см. рис.1).

    НОРМРАСП (функция НОРМРАСП)

    ​ превышении некого порогового​ для описания такого​ Обязательный. Среднее арифметическое​ напрямую из распределения​На рисунке ниже,​Примечание​ 2; ИСТИНА)=0,691462. Сделав​ распределения и Плотности​

    ​Способ второй. Вернемся к​​ при обращении на​ такую вероятность, с​ примера.​ variance of a​ значения μ.​ степени согласия гипотетического​ =1- ЛОГНОРМ.РАСП(400;5;1;ИСТИНА)​ LOGNORM.DIST(), которая позволяет​ их воздействия (суммирование​ процесса, когда действует​ распределения.​ N(μ(1)+ μ(2); КОРЕНЬ(σ(1)^2+​ выделена область значений​: Для удобства написания​ замену переменной z=(2,5-1,5)/2=0,5,​

    ​ вероятности см. статью Функция​ таблице с исходными​ них функции ГАУСС​ которой элемент стандартной​

    Синтаксис

    ​В случае односторонней гипотезы​

    ​ normal population).​Данные оценки несколько отличаются​

    ​ распределения с наблюдаемыми​​Задача2.​ вычислить плотность вероятности​

    ​ микродефектов). Т.е. в​​ большое число независимых​Стандартное_откл​

    ​ σ(2)^2)).​​ диаметров, которая удовлетворяет​ формул в файле​

    ​ запишем формулу для​​ распределения и плотность​ данными. Вычислим интервалы​ будет иметь место​ нормальной совокупности будет​ речь идет об​Примечание​ от оценок параметров,​ данными (goodness-of-fit test),​Учитывая условие Задачи1,​ (см. формулу выше)​

    Замечания

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

    ​ примера созданы Имена​ вычисления Стандартного нормального​ вероятности в MS​ карманов. Сначала найдем​ ошибка.​

    ​ находиться в интервале​ отклонении дисперсии только​: Изложенный ниже метод​ полученных с помощью​ который рассмотрен в​ вычислить какой срок​ и интегральную функцию​

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

    ​Решение приведено в файле​ для параметров распределения:​ распределения: =НОРМ.СТ.РАСП(0,5; ИСТИНА)=0,691462.​ EXCEL.​ максимальное значение в​

    Пример

    ​В созданный рядом с​ между средними и​ в одну сторону:​ проверки гипотез о​ функций СРЗНАЧ() и​ статье Проверка простых​ жизни будет у​ распределения (вероятность, что​ последующий микродефект воздействует​ мыла, вес каждого​Интегральная​ кривые достаточно близки.​ примера лист Задачи.​

    ​Естественно, обе формулы дают​

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

    ​ диапазоне температур и​ первоначальной таблицей столбец​

    ​ стандартными отклонениями от​

    ​ либо больше либо​

    ​ дисперсии,очень чувствителен к​

    ​ СТАНДОТКЛОН.В(), т.к. они​

    ​ гипотез критерием Пирсона​

    ​ 99% лазеров?​

    ​ случайная величина X,​

    ​ куска немного отличается​ Обязательный. Логическое значение,​

    ​В качестве примера можно​

    ​С помощью надстройки Пакет​ одинаковые результаты (см.​

    ​ двух параметров: μ (мю) —​

    БлогNot. Функция Лапласа и другие табличные статистические функции: считаем в Excel

    Функция Лапласа и другие табличные статистические функции: считаем в Excel

    При решении таких типовых задач математической статистики, как построение доверительных интервалов или проверка гипотез о параметрах случайных величин, широко используются несколько табличных функций, например, функция Лапласа или квантили распределения хи-квадрат.

    В наше время совершенно необязательно обращаться за недостающими в формуле величинами к толстым справочникам со статистическими таблицами, можно всё посчитать непосредственно в Excel:

    Формула =НОРМСТРАСП(x)-0,5 вычисляет значение функции Лапласа от аргумента x (подставьте вместо x соответствующую ячейку). При этом Ф(-x)=-Ф(x) , а при x>3,85 значение Ф(x)=0,5 .

    Вычислить значение обратной функции Лапласа от аргумента x можно формулой =НОРМСТОБР(x) . В Excel функция НОРМСТОБР (1-eps/2) даст требуемое критическое значение, соответствующее уровню значимости критерия, равному eps. Например, для критерия с критическим уровнем 0,05 (5%) формула НОРМСТОБР(1-0,05/2)=1,96

    Критические точки t-критерия можно вычислить с помощью формулы =СТЬЮДРАСПОБР(α;n) , где α – уровень значимости (вероятность γ или надёжность 1-γ ), n – число степеней свободы (например, объём выборки в задачах о построении доверительных интервалов). При числе степеней свободы n≥30 распределение сводится к нормальному с параметрами α=0 , σ=корень(n/(n-2)) .

    Критические точки распределения Пирсона χ 2 можно вычислить с помощью формулы =ХИ2ОБР(a;n) , где a – уровень значимости, n – число степеней свободы.

    Получить значение функции плотности распределения Пуассона можно с помощью формулы =ПУАССОН(n;λ;0) , где n – число степеней свободы (количество событий), λ – среднее число появлений события (ожидаемое численное значение).

    В ряде случаев для расчёта с заданным значением параметра γ функции Excel может понадобиться передать аргумент функции α=1-γ , смотрите внимательно встроенную справку по функциям.

    Математическая статистика — лекции и примеры в Excel

    12.03.2013, 17:18; рейтинг: 43632

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

    Нормальное распределение

    Числовые случайные величины могут быть либо дискретными, либо непрерывными (подробнее см. Типы данных). Дискретные случайные величины (т.е. величины, возникающие в результате подсчета событий) были рассмотрены ранее (см. Биноминальное распределение, Гипергеометрическое распределение, Распределение Пуассона). В этой и нескольких следующих заметках мы изучим непрерывные случайные величины, которые возникают в результате измерений. Непрерывная случайная величина может принимать любое значение, принадлежащее числовой оси или интервалу. [1] Примером такой случайной величины может служить вес какой-нибудь коробки, время загрузки Web-страницы, расходы на рекламу, доходы от продаж, время обслуживания клиента и время между двумя приходами клиентов в банк.

    Математическое выражение, описывающее распределение значений непрерывной случайной величины, называется плотностью непрерывного распределения вероятностей (рис. 1). На панели А представлена плотность нормального распределения. Эта функция является симметричной и колоколообразной. Следовательно, большинство значений такой случайной величины концентрируется вокруг математического ожидания, которое совпадает с медианой. Несмотря на то что нормально распределенная случайная величина может принимать любые числовые значения, вероятность очень больших положительных или отрицательных значений крайне мала. На панели Б изображена плотность равномерного распределения. Значения случайной величины, равномерно распределенной на интервале от а до b, равновероятны. Иногда это распределение называют прямоугольным. Оно является симметричным, и, следовательно, его математическое ожидание равно медиане. На панели В показана плотность экспоненциального распределения. Это распределение имеет ярко выраженную положительную асимметрию, и, следовательно, его математическое ожидание больше медианы. Экспоненциально распределенные случайные величины изменяются от нуля до плюс бесконечности, однако очень большие значения крайне мало вероятны.

    Рис. 1. Три непрерывных распределения

    Скачать заметку в формате Word или pdf, примеры в формате Excel2013

    В этой заметке описывается одно из наиболее важных распределений в статистике — нормальное распределение, которое также называют гауссовым. То, что это одно из наиболее часто используемых распределений, косвенно подтверждается, в частности тем, что ранее я уже писал на эту тему :), см. Нормальное распределение. Построение графика в Excel. Концепция шести сигм. Страница с этой заметкой является третьей по посещаемости из более чем 370 на сайте.

    Плотность нормального распределения изображена на рис. 1а. Можно вычислить вероятность того, что нормально распределенная случайная величина лежит в заданном интервале. Однако вероятность того, что она принимает наперед заданное значение, равна нулю. Это отличает непрерывные случайные величины (измеряемые) от дискретных (подсчитываемых). Например, время измеряется, а не подсчитывается. Следовательно, можно вычислить вероятность того, что Web-страница будет загружаться от 7 до 10 с. Сужая заданный интервал, можно вычислить вероятность того, что она будет загружаться от 8 до 9 с. Кроме того, можно вычислить вероятность того, что она будет загружаться от 8,99 до 9,01 с. Однако вероятность того, что Web-страница будет загружаться ровно 8 с, равна нулю.

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

    Важность нормального распределения в статистике обусловлена тремя причинами:

    1. Оно описывает (точно или приблизительно) распределение многих непрерывных случайных величин.
    2. С помощью нормального распределения можно аппроксимировать разнообразные дискретные распределения.
    3. Нормальное распределение лежит в основе классической теории статистических выводов, поскольку оно тесно связано с центральной предельной теоремой.
    • Имеет колоколообразную (а значит, симметричную) форму.
    • Его математическое ожидание, медиана и мода совпадают друг с другом.
    • Половина нормально распределенных значений лежит в интервале, длина которого равна 4/3 стандартного отклонения. Это значит, что межквартильный размах находится в интервале от 2/3 стандартного отклонения левее среднего значения до 2/3 стандартного отклонения правее среднего значения.
    • Значения нормально распределенной случайной величины лежат на всей числовой оси (–∞ < Х < +∞).

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

    Рис. 2. Толщина 10 000 медных дисков

    Здесь перечислены результаты измерения толщины 10 000 медных дисков. Толщина представляет собой непрерывную случайную величину, распределение которой аппроксимируется нормальным. Основная масса значений этой величины лежит в интервале от 0,0190 до 0,0192 дюймов и распределена симметрично относительно этого интервала, формируя колоколообразную кривую. Как следует из таблицы, разбиение числовой прямой на интервалы образует группы взаимоисключающих и исчерпывающих событий, сумма вероятностей которых равна единице. Таким образом, распределение вероятностей можно интерпретировать как распределение относительных частот (подробнее см. Представление числовых данных в виде таблиц и диаграмм и последний раздел заметки Определение среднего значения, вариации и формы распределения. Описательные статистики), соответствующих средним точкам интервалов.

    На рис. 3 изображена гистограмма относительных частот и полигон распределения толщины 10 000 медных дисков. Как видим, первые три условия нормального распределения выполняются, а четвертое — нет. Толщина диска не может быть отрицательной или равной нулю. Из таблицы (рис. 2) следует, что из 10 000 медных дисков только 48 толще 0,0202 дюйма и такое же количество дисков тоньше 0,0180 дюйма. Таким образом, вероятность случайно выбрать слишком толстый или слишком тонкий диск равна 0,0048+0,0048=0,0096, т.е. меньше 1 из 100.

    Рис. 3. Гистограмма относительных частот и полигон распределения ширины 10 000 медных дисков

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

    где е — основание натурального логарифма, константа равная 2,71828, μ — математическое ожидание генеральной совокупности, σ — стандартное отклонение генеральной совокупности, X — произвольное значение непрерывной случайной величины, –∞ < X < +∞.

    Поскольку величины е и π являются математическими константами, плотность нормального распределения зависит только от двух параметров — математического ожидания μ и стандартного отклонения σ (рис. 4). Разным комбинациям этих параметров соответствуют разные плотности нормального распределения. Распределения А и Б имеют одинаковое математическое ожидание μ, но разные стандартные отклонения. С другой стороны, распределения А и В имеют одинаковое стандартное отклонение σ, но разные математические ожидания. Кроме того, распределения Б и В имеют разные математические ожидания и стандартные отклонения.

    Рис. 4. Три нормальных распределения, соответствующие разным комбинациям параметров μ и σ

    К сожалению, вычислить математическое выражение, заданное формулой (1), довольно сложно. Чтобы упростить задачу, значения плотности нормального распределения, как правило, табулируют. Поскольку количество возможных комбинаций параметров μ и σ бесконечно, для вычислений понадобилось бы бесконечное количество таблиц. Однако, если нормировать данные, все распределения можно свести к одной таблице. Используя формулу преобразования, любую нормально распределенную случайную величину X можно преобразовать в нормированную нормально распределенную случайную величину Z.

    Величина Z равна разности между величиной X и математическим ожиданием генеральной совокупности μ, деленной на стандартное отклонение σ:

    Математическое ожидание стандартизованного нормального распределения равно нулю, а стандартное отклонение — единице. Плотность стандартизованного нормального распределения можно получить, подставив формулу (2) в формулу (1):

    Таким образом, любое множество нормально распределенных величин можно преобразовать в стандартизованную форму. Проиллюстрируем процедуру нормирования. Например, время загрузки Web-страницы распределено нормально, причем его математическое ожидание равно р = 7 с, а стандартное отклонение σ = 2 с. Как показывает рис. 5, каждому значению переменной X соответствует нормированное значение Z, полученное с помощью формулы преобразования (2). Следовательно, время загрузки, равное 9 с, на одну стандартную единицу превышает математическое ожидание: Z = (9 – 7) / 2 = +1, а время загрузки равное 1 с на три стандартные единицы (стандартных отклонения) меньше математического ожидания: Z = (1 – 7) / 2 = –3.

    Рис. 5. Преобразование шкал для загрузки Web-сайта; μ = 7, σ = 2

    Таким образом, стандартное отклонение становится единицей измерения. Иначе говоря, время загрузки, равное 9 с, на 2 с (т.е. на одно стандартное отклонение) превышает математическое ожидание, а время, равное 1 с, на 6 с (т.е. на три стандартных отклонения) меньше математического ожидания. Допустим теперь, что среднее время загрузки другого Web-сайта равно 4 с, а стандартное отклонение 1 с (рис. 6).

    Рис. 6. Преобразование шкал для загрузки Web-сайта; μ = 4, σ = 1

    Сравнивая рисунки 5 и 6, легко обнаружить, что время загрузки, равное 5 с, на одно стандартное отклонение больше среднего времени загрузки: Z = (5 – 4) / 1 = +1, а время загрузки, равное 1 с, на три стандартных отклонения меньше математического ожидания: Z = (1 – 4) / 1 = –3. На рис. 5 и 6 показаны полигоны относительных частот, соответствующие времени загрузки двух Web-сайтов. Поскольку результаты измерений образуют полную генеральную совокупность, сумма вероятностей, т.е. площадь фигуры, лежащей под кривой, должна быть равной единице.

    Предположим, нам необходимо определить вероятность того, что время загрузки Web-сайта (μ = 7, σ = 2, рис. 5) меньше 9 с. Поскольку это время на одно стандартное отклонение превышает математическое ожидание, следует найти вероятность того, что время загрузки не превышает величины, равной математическому ожиданию плюс одно стандартное отклонение. В Excel2013 для работы с нормально распределенными случайными величинами используется довольно много функций. Для решения нашей задачи идеально подходит =НОРМ.СТ.РАСП(z;интегральная) (рис. 7). В Excel до версии 2007 используется функция =НОРМСТРАСП(z). В ней только один параметр, так как второй параметр (интегральная) по умолчанию равен ИСТИНА.

    Рис. 7. Расчет вероятности того, что время загрузки Web-сайта (μ = 7, σ = 2, рис. 5) меньше 9 с

    Параметр z – это координата X на нормированной оси (рис. 8). Мы же с помощью функции =НОРМ.СТ.РАСП() определили вероятность того, что случайная величина будет левее Х.

    Рис. 8. Площадь фигуры, ограниченной интегральной кривой стандартизованного нормального распределения

    С другой стороны, для распределения с параметрами μ = 4, σ = 1 (рис. 6) время загрузки, равное 5 с, на одно стандартное отклонение превышает математическое ожидание, т.е. 4 с. Следовательно, вероятность того, что Web-страница загрузится быстрее, чем за 5 с, также равна 0,8413. На рис. 9 показано, как два отличающихся распределения преобразуются в одно и тоже стандартизованное распределение.

    Рис. 9. Преобразование шкал для площадей фигур, ограниченных интегральными кривыми двух нормальных распределений

    Рассмотрим несколько примеров.

    Пример 1. Определите вероятность того, что время загрузки (μ = 7, σ = 2) превысит 9 с.

    Решение. Вероятность того, что время загрузки не превысит 9 с, равна 0,8413, следовательно, искомая вероятность равна 1 – 0,8413 = 0,1587.

    Пример 2. Определите вероятность того, что время загрузки (μ = 7, σ = 2) лежит в интервале 7–9 с.

    Решение. Р(7 < Х < 9) = P(X < 9) – P(X < 7). Можно было бы, как и выше, сначала привести нормальное распределение к стандартному виду, а потом воспользоваться функцией =НОРМ.СТ.РАСП (рис. 10).

    Рис. 10. Определение вероятности Р(7 < Х < 9)

    Однако в Excel есть функция и для нестандартизированного нормального распределения (рис. 11).

    Рис. 11. Определение вероятности того, что время загрузки (μ = 7, σ = 2) лежит в интервале от 7 до 9 с

    Обратите внимание, что, поскольку математическое ожидание и медиана нормального распределения совпадают между собой, вероятность того, что загрузка продлится меньше 7 с, равна 0,5, то есть, =НОРМ.РАСП(7;7;2;ИСТИНА) = 0,5.

    Пример 3. Определите вероятность того, что время загрузки (μ = 7, σ = 2) лежит в интервале 5–9 с.

    Решение. Р(5 < Х < 9) = P(X < 9) – P(X < 5) =НОРМ.РАСП(9;7;2;ИСТИНА)-НОРМ.РАСП(5;7;2;ИСТИНА) = 0,6826 (рис. 12).

    Рис. 12. Определение вероятности Р(5 < Х < 9)

    Полученный результат довольно важен. Для любого нормального распределения вероятность того, что случайно выбранное число лежит в окрестности математического ожидания на расстоянии, не превышающем одно стандартное отклонение, равно 0,6826. В окрестности математического ожидания на расстоянии, не превышающем двух стандартных отклонений, лежит чуть более 95% нормально распределенных величин (рис. 13).

    Рис. 13. Вероятность случайной величины попасть в пределы σ-окрестности

    В окрестности математического ожидания на расстоянии, не превышающем трех стандартных отклонений, расположено 99,7% всех нормально распределенных величин. Следовательно, 99,73% результатов измерений времени загрузки Web-страницы лежат в интервале от 1 до 13 с. Таким образом, весьма маловероятно (0,0027, или 27 шансов из 10 000), что время загрузки Web-страницы будет меньше 1 с или больше 13 с. Вот почему на практике считают, что интервал длиной 6σ, центром которого является математическое ожидание, содержит практически все значения нормально распределенной случайной величины.

    В примерах 1–3 мы вычислили вероятности, связанные с разными значениями измеренной величины. Примеры 4 и 5 посвящены обратной задаче: как определить значение переменной, соответствующей заданной вероятности?

    Пример 4. Найдите значение переменной Х, соответствующей интегральной вероятности, равной 0,1. Сколько секунд длится загрузка Web-страницы в 10% случаев?

    Решение. Поскольку предполагается, что в 10% случаев Web-страница загружается не более чем за X с, площадь фигуры, ограниченной гауссовой кривой и осью абсцисс, равна 0,1 (рис. 14). Для обратной задачи в Excel до версии 2007 существуют две функции =НОРМСТОБР() – возвращает обратное значение стандартного нормального распределения, и =НОРМОБР() – возвращает обратное нормальное распределение (не стандартизированное). В версии Excel, начиная с 2010, им соответствуют функции: =НОРМ.СТ.ОБР() и =НОРМ.ОБР(). В нашем примере =НОРМ.ОБР(0,1;7;2) = 4,4 с (рис. 15).

    Рис. 14. Интегральная вероятность, равная 0,1

    Рис. 15. Использование функции =НОРМ.ОБР()

    В общем случае формула для определения величины X может быть выведена на основе формулы (2) Z = (Х – μ)/σ:

    Пример 5. Для стандартного нормального распределения определите нижнюю и верхнюю границы интервала с центром в математическом ожидании, который содержит 90% значений случайной величины.

    Решение. Нижняя граница Z соответствует такой интегральной вероятности р(Z), которая меньше (1 – 90%) / 2, то есть меньше 5%. Верхняя граница Z соответствует такой интегральной вероятности р(Z), которая больше (1 – 90%) / 2 + 90%, то есть больше 95%.

    Рис. 16. Определение величин Z, соответствующих значениям ±45%

    Таким образом, с вероятностью 90% случайная величина попадает в окрестность ±1,65σ математического ожидания. 90%-ные интервалы находят широкое применение в оценочных суждениях; см., например, Дуглас Хаббард. Как измерить всё, что угодно. Оценка стоимости нематериального в бизнесе.

    [1] Используются материалы книги Левин и др. Статистика для менеджеров. – М.: Вильямс, 2004. – с. 346–363

    52. Функция нормобр

    Описание:Возвращает обратное нормальное распределение для указанного среднего и стандартного отклонения.

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

    Вероятность— вероятность, соответствующая нормальному распределению.

    Среднее— среднее арифметическое распределения.

    Стандартное_откл— стандартное отклонение распределения.

    — Если какой-либо из аргументов не является числом, функция НОРМОБР возвращает значение ошибки #ЗНАЧ!.

    — Если вероятность < 0 или вероятность > 1, функция НОРМОБР возвращает значение ошибки #ЧИСЛО!.

    — Если стандартное_откл ≤ 0, функция НОРМОБР возвращает значение ошибки #ЧИСЛО!.

    — Если среднее = 0 и стандартное_откл = 1, функция НОРМОБР использует стандартное нормальное распределение (см. НОРМСТОБР).

    Если задано значение вероятности, функция НОРМОБР ищет значение x, для которого функция НОРМРАСП(x, среднее, стандартное_откл, ИСТИНА) = вероятность. Однако точность функции НОРМОБР зависит от точности НОРМРАСП. В функции НОРМОБР для поиска применяется метод итераций. Если поиск не закончился после 100 итераций, функция возвращает значение ошибки #Н/Д.

    42. Функция отрбиномрасп

    Описание:Возвращает отрицательное биномиальное распределение. Функция ОТРБИНОМРАСП возвращает вероятность того, заданному количеству успешных испытаний («число успехов») будет предшествовать определенное количество неудачных испытаний («число неудач») при условии, что вероятность успешного испытания постоянна и равна значению аргумента «вероятность_успеха». Эта функция подобна биномиальному распределению, за тем исключением, что количество успехов — фиксированное, а количество испытаний — переменное. Как и в случае биномиального распределения, испытания считаются независимыми.

    Например, требуется найти 10 человек с блестящими способностями, при этом известно, что вероятность наличия таких способностей у кандидата составляет 0,3. Функция ОТРБИНОМРАСП вычислит вероятность того, что придется провести собеседования с определенным количеством неподходящих кандидатов, прежде чем будут найдены все 10 подходящих кандидатов.

    ОТРБИНОМРАСП(число_неудач;число_успехов;вероятность_успеха)

    Число_неудач— количество неудачных испытаний.

    Число_успехов— пороговое значение числа успешных испытаний.

    Вероятность_успеха— вероятность успеха.

    — Значения «число_неудач» и «число_успехов» усекаются до целых.

    — Если какой-либо из аргументов не является числом, то функция ОТРБИНОМРАСП возвращает значение ошибки #ЗНАЧ!.

    — Если вероятность_успеха < 0 или вероятность_успеха > 1, функция ОТРБИНОМРАСП возвращает значение ошибки #ЧИСЛО!.

    — Если число_неудач; < 0 или число_успехов;< 1, функция ОТРБИНОМРАСП возвращает значение ошибки #ЧИСЛО!.

    — Уравнение для отрицательного биномиального распределения имеет следующий вид:

    x — число_неудач, r — число_успехов, а p — вероятность_успеха.

    Встроенные функции Excel.

    Здесь мы опишем возможности Excel при вычислении статистических функций и дадим пояснения к способу их вызова.

    Нормальное распределение.

    См. стр. 16-17 пособия[4].

    Практически любой справочник по математической статистике содержит таблицы функции и её квантилей. Так как стандартное нормальное распределение симметрично, то эти таблицы составляют для значенийи. Приведем фрагмент таблицы из сборника [1].

    Таблица 1.1. Функция нормального распределения Ф(x)

    Слева в таблице представлено входное значение с точностью до второго знака после запятой. Третий знак указан в самой верхней строке таблицы. С целью представления на одном листе по возможности большей информации, таблица разбита на блоки (выделенные чертой), в которых числа имеют несколько одинаковых первых цифр. Эти совпадающие части приведены только для одного значения (в столбце под верхней первой ячейкой с цифрой 0). Так, например,

    Ф(2,052) = 0,97 9915, Ф(2,058) = 0,98 0205, Ф(2,074) = 0,98 0960.

    Для нахождения квантилей можно использовать таблицу исходной функции распределения, отыскивая значение вероятности внутри таблицы и находя соответствующее входное значение. Например, при верхняяp-квантиль (то есть решение уравнения) будет находиться где-то между 2,053 и 2,054, так как. Простая линейная аппроксимация дает.

    В этом же сборнике [1] имеется таблица значений обратной функции нормального распределения, иными словами – таблица p-квантилей (не верхних).

    Таблица 1.3. Функция, обратная функции нормального распределения

    Таким образом, , что весьма близко к полученному выше приближенному значению.

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

    вызвать «Мастера Функций»

    нажать кнопку панели инструментов или

    перейти в подраздел “Функция” раздела “Вставка” главного меню Excel;

    в категории “Статистические” найти соответствующую функцию;

    заполнить таблицу аргументов функции.

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

    Аргументами функции могут быть либо числа, либо ссылки на ячейки, их хранящие.

    Рассмотрим каждую из этих функций по отдельности.

    НОРМСТРАСП– функция стандартного нормального распределения. Аргумент – значение(любое число).

    НОРМСТОБР– обратная функция стандартного нормального распределения. Аргумент –(число от 0 до 1).

    НОРМРАСП– функция распределенияили функция плотностинормальногозакона. Имеет 4 аргумента:

    не требует пояснений

    среднее значение

    корень из дисперсии

    0 (или FALSE) – вычисляется плотность,

    1 (или TRUE) – функция распределения

    НОРМОБР– функция, обратная функции нормального распределения. Имеет 3 аргумента, аналогичные первым трем аргументам предыдущей функции.

    Приведем несколько примеров применения этих функций.

    Функция Excel

    Характеристика

    распределения

    Ф(2,058) – функция распределения

    t 0, 9 5 – 5%-квантиль

    =НОРМРАСП(-1; 0; 1; 1)

    Ф((2,058-0)/1) – функция распределения

    =НОРМРАСП(-1; 0; 1; 0)

    φ((2,058-0)/1) – функция плотности

    0+1* t 0, 0 5 – верхняя 5%-квантиль

    Задание.Объясните совпадение значений (с точностью до знака) во второй и пятой строках этой таблицы.

    Хи-квадрат распределение.

    См. стр. 18-19 пособия[4].

    Сборник таблиц [1] содержит значения так называемого интеграла вероятностей хи-квадрат– в нашей терминологии это просто функция надежности. Таблица имеет два входа – по числу степеней свободы (верхняя строка) и по аргументу функции (левый столбец).

    Таблица 2.1а. Интеграл вероятностей

    Здесь, кроме значения функции распределения (столбец P), приведены также первые разности этой функции (столбец-Δ), точнее, только 5 значащих цифр после запятой без первых нулей. Таким образом,(после запятой поставлен один ноль, чтобы получилось пять цифр). Еслии– два рядом стоящие значения аргумента, то для нахождения значения функции в промежуточной точкеможно применить аппроксимацию. В приведенном нами фрагменте. Поэтому.

    Значения верхних p-квантилейраспределения хи-ква­д­рат содержатся в следующей таблице на стр.166 сборника [1].

    Таблица 2.2а. Процентные точки распределения

    Вход в таблицу осуществляется по числу степеней свободы (mв левом столбце) и по вероятности, выраженной в процентах (Qв верхней строке). Таким образом,.

    Пакет Excelпредоставляет возможность вычисления как значений функции надежности, так и значенийp-квантилей хи-квадрат распределения. Эти функции называютсяХИ2РАСПиХИ2ОБР. Рассмотрим несколько примеров применения этих функций.

    Функция Excel

    Значение в ячейке

    Характеристика распределения

    ­– ­функция надежности

    –­функция надежности

    –верхняя 5%-квантиль

    –верхняя 2,5%-квантиль

    Распределение Стьюдента.

    См. стр. 19-20 пособия[4].

    Таблицы распределения Стьюдента также имеются в любом справочнике по математической статистике. Приведем здесь фрагмент со­от­ве­т­ст­ву­ющей таблицы из сборника [1].

    Таблица 3.1а. Функция распределения Стьюдента

    Эта таблица имеет два входа – число степеней свободы (верхняя строка) и аргумент функции(левый столбец). Из этой таблицы находим, что. При степенях свободы больше 20 можно воспользоваться нормальным приближением:.

    Следующая таблица указанного сборника [1] содержит значения верхних -квантилей. Эта таблица также имеет два входа – число степеней свободы (левый столбец) и вероятность в процентах(верхняя строка). Для наглядности целые части вместе с запятой приведены только для верхних чисел в блоке из пяти чисел.

    Таблица 3.2. Процентные точки распределения Стьюдента

    Таким образом, .

    В пакете Excelимеются встроенные функции

    СТЬЮДРАСП, вычисляющая функцию надежности, и

    СТЬЮДРАСПОБР, вычисляющая верхние квантили.

    Функция СТЬЮДРАСПимеет три аргумента. Кроме двух естественных (аргументаи числа степеней свободы), при обращении к этой функции требуется указать количество хвостов распределения, которые нужно учитывать (1 или 2). Под “хвостом” распределения понимается любой интервал с одним конечным и одним бесконечным концом. Например, при вычислении функции надежности ищется вероятность попадания в область. Поэтому— это функцияСТЬЮДРАСПсодним“хвостом”. Очень часто в статистической практике требуется найти вероятность попадания в областьпри, то есть в область сдвумя“хвостами”. Легко видеть, что функция, вычисляющая вероятности таких симметричных интервалов, представляет собой не что иное, как функцию надежности распределения модуля.

    Обращение к функции СТЬЮДРАСПОБРвполне тривиально и полностью аналогично обращению к функцииХИ2ОБР(см. выше).

    Показательное (экспоненциальное) распределение.

    См. стр. 21 пособия[4].

    Как функция плотности, так и функция распределения показательного закона могут быть вычислены посредством калькулятора. В Excelобе эти функции можно вычислить, воспользовавшись функциейEXP(…).

    Биномиальное распределение.

    См. стр. 22пособия [4].

    В пакете Excelимеется возможность вычисления функции

    ,

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

Ссылка на основную публикацию