Биномиальное распределение в Excel
Обновлено: 06.07.2024
В Excel биномиальные распределения позволяют вычислять вероятности в двух ситуациях. Кроме того, вы должны быть знакомы с единственной функцией гипергеометрического распределения, поскольку она связана с биномиальными функциями. Вы должны использовать биномиальные распределения в следующих ситуациях:
Когда у вас есть ограниченное количество независимых испытаний или тестов, которые могут быть успешными или неудачными
Когда успех или неудача одного испытания не зависит от других испытаний
БИНОМ.РАСП: Биномиальное распределение вероятностей
Функция БИНОМ.РАСП находит вероятность биномиального распределения. Функция использует синтаксис
где number_s – это желаемое количество успешных попыток, испытаний – количество испытаний, которые вы будете просматривать, вероятность_s – вероятность успеха в испытании. , а кумулятивный — это переключатель, установленный либо в логическое значение ИСТИНА (если вы хотите вычислить кумулятивную вероятность), либо в логическое значение ЛОЖЬ (если вы хотите вычислить точную вероятность).
Например, если издатель хочет узнать вероятность публикации трех книг-бестселлеров из набора из десяти книг, когда вероятность публикации книги-бестселлера составляет десять процентов, формула выглядит так:
который возвращает значение . Это означает, что существует примерно 6-процентная вероятность того, что в наборе из десяти книг издатель опубликует ровно три книги-бестселлера.
Чтобы рассчитать вероятность того, что издатель опубликует один, два или три бестселлера в наборе из десяти книг, используется следующая формула
который возвращает значение , указывающее, что с вероятностью 99 % издатель опубликует от одного до трех бестселлеров в наборе из десяти книг.
BINOM.INV: Биномиальное распределение вероятностей
Функции БИНОМ.ОБР находят наименьшее значение, для которого кумулятивное биномиальное распределение равно или превышает указанный критерий или альфа-значение. Функция использует синтаксис
где испытания — количество испытаний Бернулли, которые вы будете рассматривать, вероятность_s — вероятность успеха в испытании, а альфа равно значению критерия, которому вы хотите соответствовать или превзойти его.
Если вы установите количество испытаний равным 10, вероятностью 0,5 и значением критерия 0,75, например, формула будет
который возвращает значение 6 .
BINOM.DIST.RANGE: Биномиальная вероятность результата испытания
Функция БИНОМ.РАСП.ДИАПАЗОН находит вероятность результата испытания или диапазон результатов испытания для биномиального распределения. Функция использует синтаксис
где испытаний – количество испытаний, которые вы будете просматривать, вероятность_s – вероятность успеха в испытании, number_s – устанавливает количество успешных попыток, а number_s2 (необязательный аргумент) задает максимальное количество успешных попыток.
Если вы установите число испытаний равным 10, вероятностью 0,5 и количеством успешных испытаний равным 3, например, формула будет выглядеть следующим образом:
который возвращает значение 0,11718 , что означает, что вероятность наличия ровно трех успешных попыток равна примерно 12%.
Если вы установите количество попыток равным 10, вероятностью 0,5 и числом успешных попыток, например, от 3 до 10, формула будет выглядеть так:
который возвращает значение , означающее, что вероятность числа успешных попыток в диапазоне от 3 до 10 равна примерно 95%.
NEGBINOM.DIST: Отрицательное биноминальное распределение
Функция ОТРБИНОМ.РАСП определяет вероятность того, что заданное количество отказов произойдет до указанного количества успехов, на основе константы вероятности успеха. Функция использует синтаксис
где number_f – указанное количество неудач, number_s – указанное количество успешных попыток, probability_s – вероятность успеха, а также кумулятивный — это переключатель, который вы устанавливаете на 0 или FALSE, если хотите получить кумулятивное распределение, и на 1 или TRUE, если вам нужно распределение вероятностей.
Например, предположим, что вы занимаетесь разведкой нефти и хотите узнать вероятность того, что вам не удастся найти нефть ровно в десяти скважинах, прежде чем вы найдете нефть ровно в одной скважине. Если шанс на успех составляет 5 процентов, вы можете найти вероятность того, что вы потерпите неудачу десять раз, прежде чем пробурить и найти нефть, используя формулу
который возвращает значение 0,016465266 , что указывает на то, что вероятность того, что вы потерпите неудачу десять раз, прежде чем попасть в фонтан, составляет менее 2%.
CRITBINOM: Кумулятивное биномиальное распределение
Функция CRITBINOM, которая на самом деле является старой функцией Excel и доступна в последних версиях Excel из соображений обратной совместимости, находит наименьшее значение, для которого кумулятивное биномиальное распределение равно или превышает значение критерия. Функция использует синтаксис
где испытаний – количество испытаний Бернулли, вероятность_s – вероятность успеха для каждого испытания, а альфа – значение вашего критерия. Оба аргумента вероятность_s и альфа должны находиться в диапазоне от 0 до 1.
HYPGEOM.DIST: Гипергеометрическое распределение
Функция ГИПЕРГЕОМЕТРИЯ возвращает вероятность определенного количества успешных выборок. Гипергеометрическое распределение напоминает биномиальное распределение, за исключением тонкой разницы. В гипергеометрическом распределении успех в одном испытании влияет на успех в другом испытании. Как правило, вы используете функцию ГИПГЕОМ.РАСП, когда берете образцы из конечной совокупности и не заменяете образцы для последующих испытаний. Функция использует синтаксис
где sample_s — указанное количество успешных выборок, number_sample — размер выборки, population_s — количество успешных попыток в генеральной совокупности, number_pop< /i> дает размер генеральной совокупности, а кумулятивный — это переключатель, который указывает Excel, что нужно вернуть либо кумулятивное распределение (обозначается аргументом 1 или TRUE), либо плотность вероятности (обозначается 0). или значение аргумента FALSE).
В качестве примера гипергеометрического распределения предположим, что вы хотите рассчитать вероятность того, что из 30 элементов 5 будут успешными. Далее предположим, что вы знаете, что среди 4000 элементов 1000 являются успешными. Для расчета используется следующая формула:
который возвращает значение 0,0104596 , указывающее, что шансы на то, что ровно 5 элементов будут успешными в наборе из 30 элементов с учетом характеристик генеральной совокупности, составляют примерно 10 %.
Определение 1. Предположим, что эксперимент имеет следующие характеристики:
- Эксперимент состоит из n независимых испытаний, каждое из которых имеет два взаимоисключающих возможных результата (которые мы будем называть успехом и неудачей)
- для каждого испытания вероятность успеха равна p (поэтому вероятность неудачи равна 1 – p)
Каждое такое испытание называется испытанием Бернулли. Пусть x — дискретная случайная величина, значение которой равно количеству успешных попыток в n испытаниях. Тогда функция распределения вероятностей для x называется биномиальным распределением, B(n, p), частотная функция которого (также известная как функция плотности вероятности) это
C(n, x) можно рассчитать с помощью функции Excel COMBIN(n,x). Дополнительные сведения об этой функции см. на рис. 2 встроенных функций Excel.
Наблюдение . На рис. 1 показан график функции плотности вероятности для B(20, .25).
Рис. 1. Биномиальное распределение
То, что график очень похож на нормальное распределение, не является совпадением (см. Связь между биномиальным и нормальным распределениями)
Нажмите здесь, чтобы получить подтверждение свойства 1.
Функция Excel: Excel предоставляет следующие функции для биномиального распределения:
BINOM.DIST(x, n, p, cum) = значение функции плотности вероятности f(x) для биномиального распределения (т. е. вероятность того, что x успехов в n испытаниях, где вероятность успеха в любом испытании составляет B (n, p), когда cum = FALSE и соответствующее кумулятивное значение распределения вероятностей F(x) (т.е. вероятность того, что в n испытаниях будет не более x успешных испытаний, где вероятность успеха в любом испытании равна p), когда cum< /em> = ИСТИНА.
БИНОМ.ОБР(n, p, 1 – α) = критическое значение; то есть наименьшее значение x такое, что F(x) ≥ 1 – α, где F — кумулятивная функция биномиального распределения для B(n, p)
Эти функции не поддерживаются в версиях Excel до Excel 2010; вместо этого используются следующие функции: БИНОМРАСП, эквивалентная БИНОМ.РАСП, и КРИТБИНОМ, эквивалентная БИНОМ.ОБР.
В Excel 2013 представлена следующая новая функция (где x ≤ y ≤ n):
BINOM.DIST.RANGE(n, p, x, y) = вероятность между x и y успехами (включительно) в n< /em> испытания, в которых вероятность успеха в любом испытании составляет p
Таким образом, БИНОМ.РАСП(n, p, x, y) = БИНОМ.РАСП(y, n, p, ИСТИНА) – БИНОМ.РАСП (x–1, n, p, TRUE), если x > 0 и BINOM.DIST.RANGE( n, p, 0, y) = БИНОМ.РАСП(y, n, p, ИСТИНА). Параметр y можно не указывать, и в этом случае БИНОМ.РАСП.ДИАПАЗОН(n, p, x) = БИНОМ.РАСП(x, n, p, ЛОЖЬ).
Пример 1. Какова вероятность того, что если вы бросите кубик 10 раз, он выпадет 6 раз – 4?
Мы можем смоделировать эту проблему, используя биномиальное распределение B(10, 1/6) следующим образом
В качестве альтернативы проблему можно решить с помощью формулы Excel:
БИНОМ.РАСП(4, 10, 1/6, ЛОЖЬ) = 0,054266
Пример 2. Какова вероятность того, что орел выпадет чаще, чем решка при 20 подбрасываниях правильной монеты?
Наблюдение: поскольку биномиальное распределение является дискретным, значения, рассчитанные методом БИНОМ.ОБР (или КРИТБИНОМ), не всегда могут представлять критические значения. Например. предположим, что вы проводите двусторонний тест с n = 100, p = 0,4 и α = 0,05, тогда BINOM.INV( 100, .4, .025) = 31. Но учтите, что
Поскольку 0,02478 ≤ 0,025 = α/2, нижнее критическое значение равно 30, а не 31. Фактически, нижнее критическое значение всегда на 1 меньше, чем значение, рассчитанное с помощью БИНОМ.ОБР. , за исключением случая, когда значение, вычисленное с помощью =BINOM.DIST(x, n, p, TRUE), точно равно α/2, и в этом случае x = БИНОМ.ОБР(n, p, α/2); и поэтому в этом случае вы не должны вычитать 1.
Оказалось, что верхнее критическое значение действительно вычислено БИНОМ.ОБР. В приведенном выше примере БИНОМ.ОБР(100, .4, .975) = 50 и
Поскольку 0,9832 ≥ 0,975 = 1 – α/2, но 0,9729
Функция реальной статистики. Ресурсный пакет Real Statistics предоставляет следующую функцию, которая реализует описанный выше процесс для определения подходящего критического значения.
Если вы используете двусторонний критерий, вы должны заменить α/2 на α, чтобы получить левое критическое значение, и заменить 1–α /2 для α, чтобы получить правильное критическое значение.
Для предыдущего примера, приведенного выше, мы получаем BINOM_CRIT(100,.4,.025) = 30 и BINOM_CRIT(100,.4,.975) = 50.
Ссылки
153 мысли о «Биномиальном распределении»
1.Вы делаете виджеты. Вы хотите продавать свои виджеты в ближайшем магазине виджетов, так как это потенциально увеличит ваши продажи. Однако вам придется каждый день оплачивать транспортные расходы, чтобы отправить вам виджеты в магазин. Вы решаете провести некоторые расчеты, чтобы увидеть, не рискуете ли вы потерять деньги из-за транспортных расходов.
Вы знаете, что 5 других компаний, производящих виджеты, продают виджеты в этом магазине, поэтому вы будете шестым. Если предположить, что покупатель с равной вероятностью выберет любой из виджетов, какова вероятность того, что он выберет и купит ваш виджет? Запишите свой ответ в виде вероятности (не процента), округленной до 4 знаков после запятой.
2. Владелец магазина виджетов сообщает вам, что 200 покупателей приходят и покупают виджет в магазине каждый день. Предполагая, что вы должны продать 30 ваших вещей, чтобы покрыть транспортные расходы, и учитывая вероятность, которую вы рассчитали в вопросе 1, используйте биномиальное распределение, чтобы оценить вероятность по крайней мере покрытия транспортных расходов (то есть вероятность продажи не менее 30 штук). виджеты). Запишите свой ответ в виде вероятности (не процента), округленной до 4 знаков после запятой.
3.Сколько минимальное количество людей должно посетить магазин, чтобы вы с вероятностью 0,95 покрыли транспортные расходы? СОВЕТ. Используйте функцию БИНОМ.РАСП, пробуя различные значения «n» — количества попыток.
4.Управляющий магазином виджетов отмечает, что не все бренды виджетов имеют одинаковые показатели покупки. Вероятность того, что бренд на полке премиум-класса будет выбран каждым покупателем, составляет 0,28. Он готов предоставить вам место на полках премиум-класса в передней части магазина за небольшую плату. Дополнительная плата плюс первоначальные транспортные расходы увеличат минимальное количество виджетов, которые вам придется продать, до 40 (чтобы покрыть транспортные расходы и дополнительную плату).
Предполагая, что в магазин заходят 200 покупателей, используйте биномиальное распределение, чтобы оценить вероятность покрытия как минимум транспортных расходов и дополнительных сборов. Запишите свой ответ в виде вероятности (не процента), округленной до 4 знаков после запятой.
5.Управляющий магазином виджетов напоминает вам, что, хотя среднее количество людей, которые приходят каждый день, составляет 200 человек, фактическое число может варьироваться. Он говорит вам, что клиенты, которые появляются каждый день, могут быть смоделированы с помощью распределения Пуассона, где лямбда = 200. Какова вероятность того, что придет не менее 200 клиентов (т. е. либо 200, либо более 200 клиентов)? Запишите свой ответ в виде вероятности (не процента), округленной до 4 знаков после запятой.
<р>6.Какое минимальное количество людей должно посетить магазин, чтобы вы с вероятностью 0,95 покрыли транспортные расходы и дополнительную плату? Используйте как 0,28 вероятность выбора виджета человеком. ПОДСКАЗКА: Вам нужно продать не менее 40 виджетов, чтобы покрыть транспортные расходы и дополнительную плату. Итак, количество «успехов» должно быть больше 40. Вероятность «успеха» в каждом испытании равна 0,28. Теперь используйте функцию БИНОМ.РАСП, пробуя различные значения для «n», количества попыток.7.Вас интересует точность оценок, предоставленных вам владельцем магазина виджетов. Если вы хотите взять случайную выборку ежедневных прибытий клиентов, какую из следующих групп населения вы должны выбрать?
Количество поступлений каждый день в этот магазин виджетов и в конкурирующий магазин виджетов вниз по улице.
Количество поступлений каждый день за все дни, когда этот конкретный магазин виджетов был открыт.
Количество поступлений каждый день для этого конкретного виджета за последний месяц.
Случайная репрезентативная выборка количества поступлений каждый день в этот конкретный магазин виджетов.
8. Владелец магазина предоставляет вам данные о прибытии клиентов за последние 3 года. Вы случайным образом выбираете выборку ежедневных прибытий клиентов, а затем берете среднее значение этой выборки. Если бы вы повторили этот процесс несколько раз, вы бы ожидали, что распределение выборочных средних будет:
Нормальное распределение
Биномиальное распределение
Распределение Пуассона
То же самое распределение как интересующая нас популяция
9.
Вопрос 9
Предполагая, что первоначальные оценки владельца магазина виджетов (данные в вопросе 5) точны, каким, по вашему мнению, должно быть среднее значение распределения, приведенного выше?
Биномиальное распределение является одним из наиболее часто используемых распределений в статистике. В этом руководстве объясняется, как использовать следующие функции в Excel для решения вопросов о биномиальных вероятностях:
БИНОМ.РАСП
Функция БИНОМ.РАСП находит вероятность получения определенного количества успешных результатов в определенном количестве испытаний, где вероятность успеха в каждом испытании фиксирована.
Синтаксис БИНОМ.РАСП следующий:
БИНОМ.РАСП(число_с, испытаний, вероятность_с_кумулятивная)
- number_s: количество успехов
- испытания: общее количество испытаний.
- probability_s: вероятность успеха в каждом испытании.
- probability_s_cumulative: TRUE возвращает кумулятивную вероятность; FALSE возвращает точную вероятность
Следующие примеры иллюстрируют, как решать вопросы о биномиальной вероятности с помощью БИНОМ.РАСП:
Пример 1
Натан выполняет 60% штрафных бросков. Если он выполнит 12 штрафных бросков, какова вероятность того, что он выполнит ровно 10?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: БИНОМ.РАСП(10, 12, 0,6, ЛОЖЬ)
Вероятность того, что Натан сделает ровно 10 штрафных бросков из 12, равна 0,063852.
Пример 2
Марти подбрасывает правильную монету 5 раз. Какова вероятность того, что монета выпадет орлом 2 раза или меньше?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: БИНОМ.РАСП(2, 5, 0,5, ИСТИНА)
Вероятность того, что монета выпадет орлом 2 раза или меньше, равна 0,5.
Пример 3
Майк подбрасывает правильную монету 5 раз. Какова вероятность того, что монета выпадет орлом более 3 раз?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: 1 – БИНОМ.РАСП(3, 5, 0,5, ИСТИНА)
Вероятность того, что монета выпадет орлом более 3 раз, составляет 0,1875.
Примечание. В этом примере функция БИНОМ.РАСП(3, 5, 0,5, ИСТИНА) возвращает вероятность того, что монета выпадет орлом 3 раза или меньше. Итак, чтобы найти вероятность того, что монета выпадет орлом более 3 раз, мы просто используем 1 – БИНОМ.РАСП(3, 5, 0,5, ИСТИНА).
БИНОМ.РАСП.ДИАПАЗОН
Функция БИНОМ.РАСП.ДИАПАЗОН определяет вероятность получения определенного количества успешных результатов в определенном диапазоне на основе определенного количества попыток, где вероятность успеха в каждой попытке является фиксированной.
Синтаксис для БИНОМ.РАСП.ДИАПАЗОН следующий:
BINOM.DIST.RANGE(испытания, вероятность_s, число_s, число_s2)
- испытания: общее количество испытаний.
- probability_s: вероятность успеха в каждом испытании.
- number_s: минимальное количество успехов
- number_s2: максимальное количество успехов
Следующие примеры иллюстрируют, как решать вопросы о биномиальной вероятности, используя БИНОМ.РАСП.ДИАПАЗОН:
ПРИМЕР 1
Дебра подбрасывает правильную монету 5 раз. Какова вероятность того, что монета выпадет орлом от 2 до 4 раз?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: БИНОМ.РАСП.ДИАПАЗОН(5, 0,5, 2, 4)
Вероятность того, что монета выпадет орлом от 2 до 4 раз, составляет 0,78125.
ПРИМЕР 2
Известно, что 70% мужчин поддерживают тот или иной закон. Если случайным образом выбрать 10 мужчин, какова вероятность того, что от 4 до 6 из них поддержат закон?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: БИНОМ.РАСП.ДИАПАЗОН(10, 0,7, 4, 6)
Вероятность того, что от 4 до 6 случайно выбранных мужчин поддерживают закон, составляет 0,339797.
ПРИМЕР 3
Тери выполняет 90 % штрафных бросков. Если она выполнит 30 штрафных бросков, какова вероятность того, что она выполнит от 15 до 25?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: БИНОМ.РАСП.ДИАПАЗОН(30, .9, 15, 25)
Вероятность того, что она выполнит от 15 до 25 штрафных бросков, равна 0,175495.
БИНОМ.ОБР
Функция БИНОМ.ОБР находит наименьшее значение, для которого кумулятивное биномиальное распределение больше или равно значению критерия.
Синтаксис для БИНОМ.ОБР следующий:
БИНОМ.ОБР(испытания, вероятность_s, альфа)
- испытания: общее количество испытаний.
- probability_s: вероятность успеха в каждом испытании.
- альфа: значение критерия от 0 до 1
Следующие примеры иллюстрируют, как решать вопросы о биномиальной вероятности с помощью БИНОМ.ОБР:
ПРИМЕР 1
Дуэйн подбрасывает правильную монету 10 раз. Какое наименьшее количество раз монета может упасть орлом, чтобы кумулятивное биномиальное распределение было больше или равно 0,4?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: БИНОМ.ОБР(10, 0,5, 0,4)
Наименьшее количество раз, когда монета может упасть орлом, чтобы кумулятивное биномиальное распределение было больше или равно 0,4, равно 5.
ПРИМЕР 2
Дуэйн подбрасывает правильную монету 20 раз. Какое наименьшее количество раз монета может упасть орлом, чтобы кумулятивное биномиальное распределение было больше или равно 0,4?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: БИНОМ.ОБР(20, 0,5, 0,4)
Наименьшее количество раз, когда монета может упасть орлом, чтобы кумулятивное биномиальное распределение было больше или равно 0,4, равно 9.
ПРИМЕР 3
Дуэйн подбрасывает правильную монету 30 раз. Какое наименьшее количество раз монета может выпасть решкой, чтобы кумулятивное биномиальное распределение было больше или равно 0,7?
Чтобы ответить на этот вопрос, мы можем использовать следующую формулу в Excel: БИНОМ.ОБР(20, 0,5, 0,4)
Наименьшее количество раз, когда монета может выпасть решкой, чтобы кумулятивное биномиальное распределение было больше или равно 0,7, равно 16.
Функция Excel БИНОМ.РАСП возвращает вероятность биномиального распределения отдельного термина. Вы можете использовать БИНОМ.РАСП для расчета вероятности того, что событие произойдет определенное количество раз в заданном количестве испытаний.
- number_s — количество успехов.
- испытания – количество независимых испытаний.
- probability_s – вероятность успеха в каждом испытании.
- кумулятивный – TRUE = кумулятивная функция распределения, FALSE = функция массы вероятности.
Функция БИНОМ.РАСП возвращает вероятность биномиального распределения отдельного термина. Вы можете использовать БИНОМ.РАСП для расчета вероятности того, что событие произойдет определенное количество раз в заданном количестве испытаний. БИНОМ.РАСП возвращает вероятность в виде десятичного числа от 0 до 1.
Двоичные данные имеют место, когда наблюдение можно отнести только к двум категориям. Например, при подбрасывании монеты может выпасть только орел или решка. Или при броске кубика результат может быть либо 6, либо не 6.
Пример
В показанном примере функция БИНОМ.РАСП используется для расчета вероятности выпадения 6 при бросании игральной кости. Поскольку у игральной кости шесть граней, вероятность выпадения 6 равна 1/6, или 0,1667. Столбец B содержит количество испытаний, а формула в C5, скопированная вниз, выглядит следующим образом:
который возвращает вероятность выпадения нуля 6 в 10 попытках, около 16 %. Вероятность выпадения одной 6 из 10 попыток составляет около 32%.
Формула в D5 такая же, за исключением того, что для аргумента совокупный задано значение TRUE. Это приводит к тому, что БИНОМ.РАСП вычисляет вероятность того, что в заданном количестве испытаний будет «не более» X успешных попыток. Формула в D5, скопированная вниз, выглядит так:
В ячейке D5 результат такой же, как и в ячейке C5, потому что вероятность выпадения не более нуля шестерок равна вероятности выпадения нуля шестерок. В ячейке D8 результат равен 0,9302, что означает, что вероятность выпадения не более трех шестерок за 10 бросков составляет около 93%.
Читайте также: