Лабораторная работа №3-4
Схема повторных независимых испытаний
Распределение Бернулли, Пуассона, Лапласа
Цель данной работы – изучить задачу теории вероятностей о повторении однородных независимых испытаний. Для небольшого числа испытаний n < 30 эта задача была разрешена Бернулли, при большем числе испытаний используются предельные формулы Пуассона и Лапласа. При использовании компьютера область применения исходной формулы Бернулли может быть расширена до n = 200, поэтому представляется возможность оценить точность предельных формул Пуассона – Лапласа. На практическом занятия осваиваются также приемы работы с электронной таблицей Excel – как организовать вычисления по формулам Бернулли, Пуассона, Лапласа; как строятся графики зависимостей и как эти графики форматируются к стандартному виду; как работать с большими таблицами и по заданному аргументу находить в таблице значение функции; и многое другое.
Изучение распределения Бернулли средствами Excel
Распределение Бернулли 
зависит от двух параметров n и p (q = 1 – p). На рабочем листе Exсel предлагается построить графики распределения при различных значениях параметра p (0 < p < 1) и при различных значениях другого параметра n (n = 10, 20, 30, 50). Эти графики позволят заметить характерные особенности распределения Бернулли. Кроме этого, полезно убедиться, что характеристики распределения правильно воспроизводятся известными формулами: M(m) = np, D(m) = npq. Далее полезно также убедиться в справедливости правила «3-х сигм»: M(m) – 3× sm < m < M(m) + 3× sm, где 
; значения m, выходящие за пределы указанного интервала, маловероятны (их вероятность меньше 0,01).
Ниже приведен фрагмент рабочего листа таблицы Excel.
| A | B | C | D | E | F | G | H | I | J | |
| Распределение Бернулли | ||||||||||
| Pn(m)=n!/m!/(n-m)!*p^m*q^(n-m) | ||||||||||
| Pn(m)=Pn(m-1)*(n-m+1)/m*p/q | Pn(0)=q^n | |||||||||
| n = | n = | n = | n = | n = | ||||||
| p = | 0,1 | p = | 0,3 | p = | 0,5 | p = | 0,7 | p = | 0,9 | |
| q = | 0,9 | q = | 0,7 | q = | 0,5 | q = | 0,3 | q = | 0,1 | |
| M = | M = | M = | M = | M = | ||||||
| D = | 0,9 | D = | 2,1 | D = | 2,5 | D = | 2,1 | D = | 0,9 | |
| M-3Sm= | -1,84605 | M-3Sm= | -1,34741 | M-3Sm= | 0,256584 | M-3Sm= | 2,652587 | M-3Sm= | 6,15395 | |
| M+3Sm= | 3,84605 | M+3Sm= | 7,347413 | M+3Sm= | 9,743416 | M+3Sm= | 11,34741 | M+3Sm= | 11,84605 | |
| m | р=0,1 | m | р=0,3 | m | р=0,5 | m | р=0,7 | m | р=0,9 | |
| 0,34868 | 0,02825 | 0,00098 | 5,9E-06 | 1E-10 | ||||||
| 0,38742 | 0,12106 | 0,00977 | 0,00014 | 9E-09 | ||||||
| 0,19371 | 0,23347 | 0,04395 | 0,00145 | 3,64E-07 | ||||||
| 0,05740 | 0,26683 | 0,11719 | 0,00900 | 8,75E-06 | ||||||
| 0,01116 | 0,20012 | 0,20508 | 0,03676 | 0,00014 | ||||||
| 0,00149 | 0,10292 | 0,24609 | 0,10292 | 0,00149 | ||||||
| 0,00014 | 0,03676 | 0,20508 | 0,20012 | 0,01116 | ||||||
| 8,75E-06 | 0,00900 | 0,11719 | 0,26683 | 0,05740 | ||||||
| 3,65E-07 | 0,00145 | 0,04395 | 0,23347 | 0,19371 | ||||||
| 9E-09 | 0,00014 | 0,00977 | 0,12106 | 0,38742 | ||||||
| 1E-10 | 5,9E-06 | 0,00098 | 0,02825 | 0,34868 |
Рассмотрим внимательно первый блок (столбцы A, B таблицы Excel).
В строках 5 и 6 задаем значения параметров n = 10, p = 0,1. В следующих строках вычисляем q = 1 – p, M = S m Pn (m), D = S m2 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) = qn вычисляем в ячейке В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 – распределение скошено влево (положительная асимметрия), а при 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 задаем по условию:
Обратите внимание, что в ссылке на ячейку 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 распределение уже практически нормальное. Этот вопрос еще будет обсуждаться ниже при изучении распределения Лапласа. Там же рассмотрим применение кумуляты.