Равномерное распределение в excel

Обновлено: 03.07.2024

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

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

Дополнительные сведения о новом варианте этой функции см. в статье Функция БИНОМ.РАСП.

Синтаксис

Аргументы функции БИНОМРАСП описаны ниже.

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

Число_испытаний — обязательный аргумент. Количество независимых испытаний.

Вероятность_успеха — обязательный аргумент. Вероятность успеха каждого испытания.

Интегральная — обязательный аргумент. Логическое значение, определяющее форму функции. Если "накопительный" имеет number_s, функция БИНОМРАСП возвращает накопительную функцию распределения. Если имеется ложь, возвращается функция вероятностной массы, которая является вероятностью number_s успеха.

Замечания

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

Если x = число_успехов, n = число_испытаний и p = вероятность_успеха, то весовая функция биномиального распределения выглядит следующим образом:

Если x = число_успехов, n = число_испытаний и p = вероятность_успеха, то интегральное биномиальное распределение выглядит следующим образом:

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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


Равномерно распределенная на отрезке [a; b] случайная величина имеет плотность распределения (вероятности) :

Функция распределения определяется следующим образом:


Равномерное непрерывное распределение (англ. Continuous uniform d istribution или Rectangular distribution ) часто встречается на практике.

Пример1. Например, известно, что гейзер извергается каждые 50 минут. Найти вероятность, того что турист увидит извержение, если будет ждать у гейзера 20 минут. В соответствии с вышеуказанными формулами вероятность увидеть извержение в течение времени наблюдения равна 20/50=0,4, т.е. 40%.

Пример2. Симметричный волчок после раскручивания падает набок. Вертикальная ось волчка после падения указывает на определенный угол от 0 до 360 градусов. Найти вероятность, того что ось волчка укажет на сектор от 90 до 180 градусов. Вероятность равна (180-90)/(360-0)=0,25.


В файле примера приведены графики плотности распределения вероятности и интегральной функции распределения .

Математическое ожидание и дисперсия

Математическое ожидание для равномерного непрерывного распределения вычисляется по формуле =(a+b)/2.

Генерация случайных чисел

Случайные числа, имеющие равномерное непрерывное распределение на отрезке [0; 1), можно сгенерировать с помощью функции MS EXCEL СЛЧИС() . В функции нельзя задать нижнюю и верхнюю границу интервала, но записав формулу =СЛЧИС()*(b-a)+a можно сгенерировать равномерно распределенные числа на любом интервале [a; b).

Примечание : Чтобы сгенерировать случайные числа, имеющие равномерное дискретное распределение , воспользуйтесь функцией СЛУЧМЕЖДУ() .

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

Сгенерируем массив из 50 чисел из диапазона [3,3; 7,5). Для этого в окне Генерация случайных чисел установим следующие параметры (см. файл примера лист Генерация ):


Как видно из рисунка выше, в поле Случайное рассеивание установлен необязательный параметр равный 2. Параметр Случайное рассеивание может принимать значение от 1 до 32767. Если установить этот параметр, то MS EXCEL будет каждый раз генерировать один и тот же массив чисел, соответствующий этот значению. Этот подход удобен для генерации одинаковых массивов, например, на различных компьютерах.

Оценка среднего и стандартного отклонения

Нижнюю и верхнюю границу интервала возьмем [3,3; 7,5) и разместим их в ячейках B4:B5 . Сгенерируем 50 чисел ( выборку ) и поместим их в диапазоне С14:С63 .

Математическое ожидание этого распределения =(B4+B5)/2 и равно 5,4. Стандартное отклонение распределения равно =КОРЕНЬ(((B5-B4)^2)/12)=1,21

Чтобы оценить математическое ожидание воспользуемся значениями выборки =СУММ(C14:C63)/СЧЁТ(C14:C63) .

Оценить стандартное отклонение можно с помощью формулы =СТАНДОТКЛОН.В(C14:C63) в MS EXCEL 2010 или =СТАНДОТКЛОН(C14:C63) для более ранних версий.

Чтобы оценить дисперсию используйте формулу =ДИСП.В(C14:C63) в MS EXCEL 2010 или =ДИСП(C14:C63) для более ранних версий. Также можно использовать формулу =СТАНДОТКЛОН.В(C14:C63)^2 .

СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

Рассмотрим Равномерное дискретное распределение, построим график функции распределения, вычислим среднее значение и дисперсию. Сгенерируем случайные значения (выборку) с помощью функции MS EXCEL СЛУЧМЕЖДУ() . На основании выборки оценим среднее и стандартное отклонение распределения.

Равномерное дискретное распределение (англ. Discrete uniform distribution) имеет место, например, при подбрасывании симметричной монеты. Пусть если выпал «орёл», то случайная величина принимает значение 1, если выпала «решка» - то 0. Т.к. вероятность наступления событий одинакова и всего 2 возможных исхода, то вероятность случайной величины принять значение 1 (или 0) равна 1/2=0,5.

Распределение называется равномерным, т.к. вероятность любого исхода одинакова.

Примечание : В данном случае, когда возможно всего 2 исхода, равномерное распределение является частным случаем Распределения Бернулли с параметром p = q =1- p =0,5.

Другой пример. Результат бросания симметричной игральной кости является равномерной дискретной случайной величиной , т.к. количество точек на грани кубика принимает одно из 6 равновероятных значений. Вероятность выпадения каждой из шести граней равна 1/6.

Для этого примера функция распределения будет выглядеть следующим образом.


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

СОВЕТ : Подробнее о Функции распределения см. статью Функция распределения и плотность вероятности в MS EXCEL .

Математическое ожидание и дисперсия

В файле примера на листе График приведен расчет математического ожидания по формуле =(a+b)/2.

Дисперсия (квадрат стандартного отклонения) для равномерного дискретного распределения может быть вычислена по формуле =((b-a+1)^2-1)/12.

Генерация случайных значений

Случайные числа, имеющие равномерное дискретное распределение , можно сгенерировать с помощью функции MS EXCEL СЛУЧМЕЖДУ() . В функции можно задать нижнюю и верхнюю границу интервала [a; b]. Функцией будут сгенерированы целые случайные числа из указанного интервала (см. файл примера лист Генерация ).


Обратите внимание, что массив случайных чисел, сгенерированных с помощью функции СЛУЧМЕЖДУ() , автоматически обновится при пересчете листа. Пересчет листа в MS EXCEL производится при вводе нового значения в ячейку или при нажатии клавиши F9 .

Примечание : Подробнее про функцию СЛУЧМЕЖДУ() см. статью Функция СЛУЧМЕЖДУ() - Случайное число из заданного интервала в MS EXCEL .

Чтобы сгенерировать нецелые случайные числа, например из интервала [1,1; 2,5], необходимо записать формулу = СЛУЧМЕЖДУ(1,1*10;2,5*10)/10 .

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

Как видно из формулы - границы интервала также могут быть нецелыми числами. Хотя, конечно, можно сгенерировать числа, например, с точностью до сотых с помощью формулы = СЛУЧМЕЖДУ(10*100;20*100)/100 . В этом случае случайные числа будут принадлежать интервалу [10;20] и иметь вид 10,37; 16,08; 15,43 и т.д.

Оценка среднего и стандартного отклонения

Сгенерируем 50 чисел (выборку) и разместим их в диапазоне B17:B66 . Нижнюю и верхнюю границу интервала возьмем [1; 6] и разместим их в диапазоне B5:B6 .

Математическое ожидание этого распределения =(B5+B6)/2 и равно (6+1)/2=3,5. Стандартное отклонение распределения равно = КОРЕНЬ(((B6-B5+1)^2-1)/12) =1,71

Чтобы оценить математическое ожидание воспользуемся значениями выборки =СУММ(B17:B66)/СЧЁТ(B17:B66) .

Оценить стандартное отклонение можно с помощью формулы =СТАНДОТКЛОН.В(B17:B66) в MS EXCEL 2010 или = СТАНДОТКЛОН(B17:B66) для более ранних версий.

Чтобы оценить дисперсию используйте формулу =ДИСП.В(B17:B66) в MS EXCEL 2010 или =ДИСП(B17:B66) для более ранних версий. Также можно использовать формулу =СТАНДОТКЛОН.В(B17:B66)^2 .

СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

Распределение вероятностей – одно из центральных понятий теории вероятности и математической статистики. Определение распределения вероятности равносильно заданию вероятностей всех СВ, описывающих некоторое случайное событие. Распределение вероятностей некоторой СВ, возможные значения которой x 1, x 2, … xn образуют выборку, задается указанием этих значений и соответствующих им вероятностей p 1, p 2,… pn . ( pn должны быть положительны и в сумме давать единицу).

В данной лабораторной работе будут рассмотрены и построены с помощью MS Excel наиболее распространенные распределения вероятности: биномиальное и нормальное.

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

Представляет собой распределение вероятностей числа наступлений некоторого события («удачи») в n повторных независимых испытаниях, если при каждом испытании вероятность наступления этого события равна p . При этом распределении разброс вариант (есть или нет события) является следствием влияния ряда независимых и случайных факторов.

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

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

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

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

число_успехов — это количество успешных испытаний;

число_испытаний — это число независимых испытаний (число успехов и число испытаний должны быть целыми числами);

вероятность_ успеха — это вероятность успеха каждого испытания;

интегральный — это логическое значение, определяющее форму функции.

Если данный параметр имеет значение ИСТИНА (=1), то считается интегральная функция распределения (вероятность того, что число успешных испытаний не менее значения число_ успехов);

если этот параметр имеет значение ЛОЖЬ (=0), то вычисляется значение функ­ции плотности распределения (вероятность того, что число успешных испытаний в точности равно значению аргумента число_ успехов).

Пример 1. Какова вероятность того, что трое из четырех новорож­денных будут мальчиками?

1. Устанавливаем табличный курсор в свободную ячейку, например в А1. Здесь должно оказаться значение искомой вероятности.

2. Для получения значения вероятности воспользуемся специальной функцией: нажимаем на панели инструментов кнопку Вставка функции ( fx ) .

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

Появляется диалоговое окно функции. В поле Число_ s вводим с клавиатуры количество успешных испытаний (3). В поле Испытания вво­дим с клавиатуры общее количество испытаний (4). В рабочее поле Вероятность_ s вводим с клавиатуры вероятность успеха в отдельном испытании (0,5). В поле Интегральный вводим с клавиатуры вид функции распределения — интегральная или весовая (0). Нажимаем на кнопку ОК.

В ячейке А1 появляется искомое значение вероятности р = 0,25. Ровно 3 мальчика из 4 новорожденных могут появиться с вероят­ностью 0,25.

Если изменить формулировку условия задачи и выяснить вероятность того, что появится не более трех мальчиков, то в этом случае в рабочее поле Интегральный вводим 1 (вид функции распределения интегральный). Вероятность этого события будет равна 0,9375.

Задания для самостоятельной работы

1. Какова вероятность того, что восемь из десяти студентов, сдающих зачет, получат «незачет». (0,04)

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

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

Диаграмма нормального распределения симметрична относительно точки а (математического ожидания). Ме­диана нормального распределения равна тоже а. При этом в точке а функция 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).

Задания для самостоятельной работы

1. Построить график нормальной функции плотности распределения f ( x ) при x , меняющемся от 20 до 40 с шагом 1 при

3. Генерация случайных величин

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

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

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

СЛЧИС () – выводит на экран равномерно распределенные случайные числа больше или равные 0 и меньшие 1;

СЛУЧМЕЖДУ (ниж_граница; верх_граница) – выводит на экран случайное число, лежащее между про­ извольными заданными значениями.

В случае использования процедуры Генерация случайных чисел из пакета Анализа необходимо запол­нить следующие поля:

- число переменных вводится число столбцов значений, которые необходимо разместить в выходном диапазоне. Если это число не введено, то все столбцы в выходном диапазоне будут заполнены;

- число случайных чисел вводится число случайных значений, которое необ­ ходимо вывести для каждой переменной, если число случайных чисел не будет введе­ но, то все строки выходного диапазона будут заполнены;

- в поле распределение необходимо выбрать тип распределения, которое следует использовать для генерации случайных переменных:

1. равномерное - характеризуется вер x ней и нижней границами. Переменные из­ влекаются с одной и той же вероятностью для всех значений интервала.

2. нормальное — характеризуется средним значением и стандартным отклонени­ ем. Обычно для этого распределения используют среднее значе­ ние 0 и стандартное отклонение 1.

3. биномиальное — характеризуется вероятностью успеха (величина р) для неко­ торого числа попыток. Например, можно сгенерировать случайные двухальтер нативные переменные по числу попыток, сумма которых будет биномиальной случайной переменной;

4. дискретное — характеризуется значением СВ и соответствующим ему интервалом вероятности, диапазон должен состоять из двух столбцов: левого, содержаще­ го значения, и правого, содержащего вероятности, связанные со значением в дан­ ной строке. Сумма вероятностей должна быть равна 1;

5. распределения Бернулли, Пуассона и Модельное.

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

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

1. Пронумеруем первые блюда по порядку: 1 — уха, 2 — щи, 3 — борщ, 4 — грибной суп. Введем числа 1-4 в диапазон А2:А5 рабочей таблицы.

2. Укажем желаемую вероятность появления каждого первого блюда. Пусть все блюда будут равновероятны (р=1/4). Вводим число 0,25 в диапазон В2:В5.

4. Указываем выходной диапазон и нажимаем ОК. В столбце С появляются случайные числа: 1, 2, 3, 4.

Задание для самостоятельной работы

1. Сформировать выборку из 10 случайных чисел, лежащих в диапазоне от 0 до 1.

2. Сформировать выборку из 20 случайных чисел, лежащих в диапазоне от 5 до 20.

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

4. Составить расписание внеклассных мероприятий на неделю для случайного проведения: семинаров, интеллектуальных игр, КВН и спец. курса.

5. Составить расписание на месяц для случайной демонстрации на телевидении одного из четырех рекламных роликов турфирмы. Причем вероятность появления рекламного ролика №1 должна быть в два раза выше, чем остальных рекламных роликов.

Читайте также: