Формула бернулли в excel

Формула бернулли в excel

Лабораторная работа №3-4

Схема повторных независимых испытаний

Распределение Бернулли, Пуассона, Лапласа

Цель данной работы – изучить задачу теории вероятностей о повторении однородных независимых испытаний. Для небольшого числа испытаний n

В строках 5 и 6 задаем значения параметров n = 10, p = 0,1. В следующих строках вычисляем q = 1 – p, M = S m Pn(m), D = S m 2 Pn(m) – M^2, M-3Sm = M – 3*КОРЕНЬ(D), M+3Sm = M + 3*КОРЕНЬ(D). Последние 4 формулы можно набрать позже, когда будет заполнен диапазон B14:B24, содержащий значения Pn(m). Отметим полезный прием: в столбце А записываем текст и сдвигаем его вправо, а в столбце В – вычисляем числовое значение и сдвигаем его влево. Получается понятный комментарий к выполненным действиям. Лист Excel, помимо всего прочего, является отчетным документом, поэтому не стоит экономить на комментариях и заголовках. Из информации в строках 8 – 11 первого блока, видно, что, действительно, M = np = 10´0,1 = 1; D = npq = 10´0,1´0,9 = 0,9; и что все вероятные значения m не превзойдут 4.

Значения Pn(m) удобно вычислять по реккурентной формуле (эта формула приведена в строке 3 рабочего листа). Начальное значение Pn(0) = q n вычисляем в ячейке В14. При наборе реккурентной формулы в ячейке В15 следует зафиксировать (знаками $) неизменяемые значения n, p, q. Далее формула копируется ниже до ячейки В24.

Заполнив первый блок, копируем его несколько раз вправо и в новых блоках заменяем значение параметра p на p = 0,3; p = 0,5; p = 0,7; p = 0,9. Все автоматически пересчитывается. В блоках серым фоном выделены значения Pn(m), которые признаны значимыми по правилу «3-х сигм».

Теперь строим графики. Выделяем значения m вместе с заголовком в ячейке А13, далее при нажатой клавише Ctrl выделяем мышкой значения Pn(m) для p = 0,1; 0,3; 0,5; 0,7; 0,9. Выделять диапазоны надо вместе с заголовками в строке 13, тогда эти заголовки автоматически будут отображены в легенде (пояснениях к каждой линии на графике). Вызываем Мастер диаграмм, выбираем тип диаграммы – точечная, легенда – внизу, линии сетки – основные, заголовок: “Распределение Бернулли при разных p (n=10)”. В результате получаем следующий график, который почти не требует дополнительного форматирования:

Из этого графика видно, как меняется асимметрия распределения при увеличении параметра p: при p = 0,5 распределение симметричное, при p 0,5 – скошено вправо (отрицательная асимметрия).

Как уже указывалось выше, заголовки из строки 13 автоматически переносятся в легенду диаграммы. Но тогда хотелось бы, чтобы они автоматически корректировались при изменении параметра p. Поэтому в качестве заголовка в ячейке В13 набрана формула ="р="&ТЕКСТ(B9;"0,0"). Функция ТЕКСТ(Число;Формат) переводит в символьную форму значение p из ячейки В9; в тексте заголовка это число будет округлено до одного знака после запятой. Остальные заголовки в строке 13 корректируются автоматически при копировании.

Теперь переходим к изучению зависимости распределения Бернулли от второго параметра n. Скопируем все 5 готовых блоков вправо, начиная со столбца K, и заменим в новых блоках значения параметров: n = 10, 20, 30, 40, 50 и p = 0,1 (для всех новых блоков). Естественно, новые таблицы надо продлить вниз до строки 64 (они теперь будут иметь разную длину). Ненужную информацию можно скрыть с помощью условного форматирования. Так, таблица для n = 10 фактически обрывается на строке 24, поэтому можно сделать так, чтобы дальнейшие значения m и нулевые значения Pn(m) выводились серым цветом на белом фоне (тогда они почти не будут видны). Условный формат для колонки m задаем по условию:

Читайте также:  Loadlibrary failed with error 1114 viber

Обратите внимание, что в ссылке на ячейку L8 зафиксирован только номер строки. Для колонки Pn(m) с заголовком n=10 условие будет более простое: значение равно 0 . При копировании отформатированного блока, копируются также все условные форматы.

Наконец, надо заменить заголовки в строке 13 на формулы ="n="&ТЕКСТ(L8;"0").

K L M N O P Q R S T
n = n = n = n = n =
p = 0,1 p = 0,1 p = 0,1 p = 0,1 p = 0,1
q = 0,9 q = 0,9 q = 0,9 q = 0,9 q = 0,9
M = M = M = M = M =
D = 0,9 D = 1,8 D = 2,7 D = 3,6 D = 4,5
M-3Sm= -1,84605 M-3Sm= -2,02492 M-3Sm= -1,9295 M-3Sm= -1,6921 M-3Sm= -1,36396
M+3Sm= 3,84605 M+3Sm= 6,024922 M+3Sm= 7,929503 M+3Sm= 9,6921 M+3Sm= 11,36396
m n=10 m n=20 m n=30 m n=40 m n=50
0,348678 0,121577 0,042391 0,014781 0,005154
0,387420 0,270170 0,141304 0,065693 0,028632
0,193710 0,285180 0,227656 0,142334 0,077943
0,057396 0,190120 0,236088 0,200323 0,138565
0,01116 0,089779 0,177066 0,205887 0,180905
0,001488 0,031921 0,102305 0,164710 0,184925
0,000138 0,008867 0,047363 0,106756 0,154104
8,75E-06 0,00197 0,018043 0,057614 0,107628
3,65E-07 0,000356 0,005764 0,026407 0,064278
9E-09 5,27E-05 0,001565 0,010432 0,033329
1E-10 6,44E-06 0,000365 0,003593 0,015183
6,51E-07 7,38E-05 0,001089 0,006135
5,42E-08 1,3E-05 0,000292 0,002215
3,71E-09 2E-06 7E-05 0,000719
2,06E-10 2,69E-07 1,5E-05 0,000211
9,15E-12 3,19E-08 2,89E-06 5,63E-05
3,18E-13 3,33E-09 5,01E-07 1,37E-05
8,31E-15 3,04E-10 7,86E-08 3,04E-06
1,54E-16 2,44E-11 1,12E-08 6,2E-07
1,8E-18 1,71E-12 1,44E-09 1,16E-07
1E-20 1,05E-13 1,68E-10 2E-08

Интересно, что хотя таблицы продолжаются до строки 64, фактически (согласно правилу "3-х сигм") их можно было оборвать на строке 25 (это отразится только на значениях M и D в строках 8, 9). Все готово для построения нового графика, из которого будет видно, как с увеличением n распределение Бернулли приближается к некой стандартной форме – к распределению Лапласа, или к, так называемому, нормальному закону распределения Гаусса.

Считается, что при n ³ 30 распределение уже практически нормальное. Этот вопрос еще будет обсуждаться ниже при изучении распределения Лапласа. Там же рассмотрим применение кумуляты.

Пример 4.В партии 20 изделий, из них 5 бракованных. Найти вероятность того, что в выборке из 4 изделий ровно одно бракованное.

Решение. В данной задаче, прежде всего, определим значения параметров: число_успехов_ в_ выборке = 1; размер_ выборки = 4; число_ успехов_ в_ совокупности = 5; размер_ совокупности = 20.

Искомую вероятность можно рассчитать с помощью функции =ГИПЕРГЕОМЕТ(1; 4; 5; 20), которая дает значение 0,4696.

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

Пусть производится n независимых испытаний, в каждом из которых событие А может появиться либо не появиться. Вероятность события А в каждом испытании одна и та же, а именно равна р. Следовательно, вероятность ненаступления события А в каждом испытании также постоянна и равна q = 1 – р.

Читайте также:  Asus rog не работает подсветка клавиатуры

Вероятность того, что при n повторных независимых испытаниях событие А осуществится ровно k раз вычисляется по формуле Бернулли: .

Для нахождения наиболее вероятного числа успехов k по заданным n и р можно воспользоваться неравенствами np – q £ k£ np + p или правилом: если число np + p не целое, то k равно целой части этого числа.

В случае, если n велико, р мало, а , используют асимптотическую формулу Пуассона вычисления вероятности наступления события А ровно k раз при n повторных независимых испытаниях: .

Пример 5. Вероятность того, что расход электроэнергии на протяжении одних суток не превысит установленной нормы, равна р = 0,75. Найти вероятность того, что в ближайшие 6 суток расход электроэнергии в течение 4 суток не превысит нормы.

Решение. Вероятность нормального расхода элек­троэнергии на протяжении каждых из 6 суток постоянна и равна p = 0,75. Следовательно, вероятность перерасхода электроэнергии в каждые сутки также постоянна и равна q = 1— р = 1 — 0,75 = 0,25. Искомая вероятность по формуле Бернулли равна = 0,297. Для вычисления в Excel используем формулу =БИНОМРАСП(4; 6; 0,75; 0), которая дает значение 0,297. При этом определены следующие значения параметров: число_ успехов = 4; число_ испытаний = 6; вероятность_ успеха = 0,75; интегральная = 0. Подробно с синтаксисом функции БИНОМРАСП можно ознакомиться с помощью справки.

Пример 6. Телефонная станция обслуживает 400 абонентов. Для каждого абонента вероятность того, что в течение часа он позвонит на станцию, равна 0,01. Найти вероятность, что в течение часа ровно 5 абонентов позвонят на станцию.

Решение.Так как р = 0,01 мало и n = 400 велико, то будем пользоваться приближенной формулой Пуассона при l = 400 × 0,01 = 4. Тогда Р400(5) » » 0,156293. Для вычисления в Excel используем формулу =ПУАССОН(5; 4; 0), которая дает значение 0,156293. При этом определены следующие значения параметров: количество_ событий = 5; среднее(λ) = 4; интегральная = 0. Подробно с синтаксисом функции ПУАССОН можно ознакомиться в справке.

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

Локальная теорема Лапласа. Если вероятность р появления события А в каждом испытании постоянна и отлична от нуля и единицы, то вероятность того, что событие А появится в n испытаниях ровно k раз, приближенно равна (тем точнее, чем больше n) значению функции , где .

Имеются таблицы, в которых помещены значения функции .

Интегральная теорема Лапласа. Если вероятность р наступления события А в каждом испытании постоянна и отлична от нуля и еди­ницы, то вероятность того, что событие А появится в n испытаниях от k1 до k2 раз, приближенно равна определенному интегралу:

, где .

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

Пример 7. Найти вероятность того, что событие А на­ступит ровно 80 раз в 400 испытаниях, если вероятность появления этого события в каждом испытании равна 0,2.

Читайте также:  Где в ворде сделать альбомный лист

Решение. По условию n = 400; k = 80; р = 0,2; q = 0,8. Воспользуемся асимптотической формулой Лап­ласа: , , . Для вычисления в Excel используем формулу =НОРМРАСП(80; 80; 8; 0), которая дает значение 0,04986. При этом определены следующие значения параметров: k = 80; среднее= np = 80; стандартное_откл = = = 8, интегральная = 0. Подробно с синтаксисом функции НОРМРАСП можно ознакомиться с помощью справки.

Пример 8. Вероятность того, что деталь не прошла проверку ОТК, равна 0,2. Найти вероятность того, что среди 400 случайно отобранных деталей окажется непроверенных от 70 до 100 деталей.

Решение.Воспользуемся интегральной формулой Лапласа: n = 400; k1= 70; k2=100; р = 0,2; q = 0,8; . Так как функция является нечетной, то P400(70; 100) = Ф(2,5)+ + Ф(1,25) = 0,4938 + 0,3944 = 0,8882.

Для вычисления в Excel используем формулу нормального распределения =НОРМРАСП(100; 80; 8; 1) — НОРМРАСП(70; 80; 8; 1), которая дает значение 0,8882. При этом параметр интегральная = 1, остальные значения параметров определяются аналогично примеру, рассмотренному выше.

Очень часто при работе в Excel необходимо использовать вычисления вероятности появления некоторого события. Для этого используется статистическая функция ВЕРОЯТНОСТЬ.

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

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

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

Вычисление процента вероятности события в Excel

Пример 1. Дана таблица диапазона числовых значений, а также вероятностей, которые им соответствуют:

Необходимо при использовании данной статистической функции вычислить вероятность события, что значение с указанного интервала входит в интервал [1;4].

Для этого введем функцию со следующими аргументами:

  • х_интервал – это начальные данные (0, …, 4);
  • интервал вероятностей является множеством вероятностей для начальных данных (0,15; 0,1; 0,15; 0,2; 0,4);
  • нижний предел равен значению 1;
  • верхний предел равен 4.

В результате выполненных вычислений получим:

Пример 2. В условии предыдущего примера нужно вычислить вероятность события «значение х равно 4».

Введем в ячейку С3 введем функцию с такими аргументами:

  • х_интервал – начальные параметры (0, …, 4);
  • интервал вероятностей – совокупность вероятностей для параметров (0,1; 0,15; 0,2; 0,15; 0,4);
  • нижний предел – 4;

В данном примере верхний предел не указан, поскольку необходимо конкретное значение вероятности, а именно для значения 4.

Функция ВЕРОЯТНОСТЬ при нескольких условиях интервалов

Пример 3. В условии примера 1 нужно вычислить вероятность того, что значения интервала [0; 4] будут находится находятся внутри интервалов [0;1] и [3;4].

Описание формул аналогичные предыдущим примерам.

В результате выполненных вычислений получим:

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

Ссылка на основную публикацию
Уравнение окружности в полярных координатах
Определение: замкнутая плоская кривая, все точки которой одинаково удалены от данной точки (центра О), лежащей в той же плоскости, что...
Тело массой м брошено
Тело массой m = 5 кг брошено под углом α = 30° к горизонту с начальной скоростью v 0 =...
Телефоны с ик портом 2018
В большинстве домов можно обнаружить несколько устройств, которые управляются пультом дистанционного управления: телевизор, музыкальный центр, система климат-контроля, камера наблюдения и...
Уравнение пучка прямых проходящих через точку
Совокупность прямых, проходящих через некоторую точку, называется пучком прямых с центром в этой точке. Если и - уравнения двух пересекающихся...
Adblock detector