Как рассчитать вероятность в Excel

Обновлено: 01.07.2024

Прежде чем вы начнете, вам может быть полезно узнать, что такое нормальное распределение. См.: Что такое нормальное распределение?
Вы также можете посмотреть, с какими типами текстовых задач вы, скорее всего, столкнетесь. См.:
Задачи Word с нормальным распределением.

Посмотрите видео, чтобы узнать, как это сделать. Обновление от 04.06.2016. Хотя в этом видео я использую более старую версию Excel, действия для Excel 2016 точно такие же:

Как рассчитать вероятность нормального распределения в Excel

Хотя можно найти вероятности нормального распределения с помощью z-таблицы, на самом деле гораздо проще вычислять вероятности в Excel по нескольким причинам. Во-первых, нельзя смотреть на стол; функция НОРМРАСП сделает всю тяжелую работу за вас. Во-вторых, Excel выполняет промежуточные вычисления за вас. Большинство ошибок вычислений происходят на промежуточном этапе (например, при вычислении z-показателя для поиска), а не на самом фактическом z-показателе. Excel может обрабатывать три типа расчетов вероятности: больше, меньше и промежуточное. Эти инструкции подходят для Excel 2007 и Excel 2010.

Пример вопроса. Предположим, что загрязнитель в пробах городского водоснабжения имеет среднее значение 500 частей на миллион и стандартное отклонение 100 частей на миллион. Какова вероятность того, что бактерии в случайно выбранной пробе воды будут: 1/ менее 600 ppm 2/более 600 ppm 3/между 400 и 600 ppm?

Расчет вероятности нормального распределения в Excel: меньше

Шаг 1. Нажмите на пустую ячейку.

Шаг 2. Нажмите «Вставить формулу».

Шаг 3. Введите «Normdist» в поле поиска и нажмите «Перейти».

Шаг 4. Выберите «НОРМРАСП» из списка и нажмите «ОК», чтобы открыть окно «Аргументы функции».

Шаг 5. Введите свои данные в поле. Для этого примера введите «600» в поле «X», «500» в поле «Среднее», «100» в поле «Стандартное отклонение» и «true» в поле «Накопление». .

Шаг 6. Нажмите «ОК». Это возвращает 0,84134474 в ячейке, которую вы щелкнули на шаге 1, что является вероятностью получения менее 600 частей на миллион.

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

Шаг 7. Вычтите ответ из шага 7 (выше) из 1:
1-0,84134474= 0,158653.

Расчет вероятности нормального распределения в Excel: Между

Шаг 8. Повторите шаги с 1 по 8 для второго значения, которое в данном примере равно 400. Вероятность равна 0,15865526.
Шаг 9: Вычтите большую вероятность из меньшей вероятности. В этом примере:
0,84134474 – 0,15865526 = 0,68269

Ссылки

Нужна помощь с домашним заданием или контрольным вопросом? С Chegg Study вы можете получить пошаговые решения ваших вопросов от эксперта в этой области. Ваши первые 30 минут занятий с репетитором Chegg бесплатны!

Комментарии? Нужно опубликовать исправление? Оставьте комментарий на нашей странице Facebook.

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

Рисунок 1. Окончательный результат: расчет вероятности

Синтаксис PROB

=PROB(диапазон, вероятностный_диапазон, [нижний_предел], [верхний_предел])

range — диапазон числовых значений, содержащих наши данные
prob_range — диапазон вероятностей для каждого соответствующего значения в нашем диапазоне
нижний_лимит – необязательный; нижняя граница значений, для которых мы хотим рассчитать вероятность
upper_limit – необязательно; верхний предел значений, для которых мы хотим рассчитать вероятность

Как рассчитать вероятность?

Чтобы получить надежные результаты вероятности, мы должны иметь возможность подготовить данные до расчета.

Подготовить наши данные

Рисунок 2. Пример данных для расчета вероятности

Мы хотим рассчитать вероятность того, что продажи продукта находятся в диапазоне от 50 до 80.

Вычисление вероятности

Для выполнения расчета мы вводим эту формулу в ячейку C11

= ПРОБ (B4:B7, C4:C7, C9, C10)

Где B4:B7 — это диапазон, содержащий значения продаж продукта, C4:C7 — вероятности для каждого объема продаж, C9 — нижний предел 50, а C10 — верхний предел 80.

Рисунок 3. Ввод формулы вероятности

В результате вероятность в ячейке C11 равна 0.68 или 68 % — вероятность того, что продажи продукта находятся в диапазоне от 50 до 80.

Рассчитать вероятность без верхнего предела

Если нет верхнего предела, функция PROB возвращает вероятность того, что она равна только нижнему пределу.

Пример:

Если мы опустили верхний предел в нашей формуле, результат в ячейке C11 будет равен 0,50 или 50 %, что также соответствует вероятности продажи продукта, равной 50.

Рисунок 4. Формула вероятности без верхнего предела

Мгновенное подключение к эксперту Excel

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

В этом справочном листе за январь 2009 г. содержится информация о том, как получить:

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

ВЕРОЯТНОСТИ И ОБРАТНЫЕ ВЕРОЯТНОСТИ

В качестве примера мы рассматриваем стандартное нормальное распределение.

Пусть X — случайная величина, x — значение случайной величины, а p — вероятность. Затем:

Вероятность, такая как Pr(X). Таким образом, команда Excel включает «РАСП»
например, СТЬЮДРАСП для распределения T
например, НОРМРАСП для стандартного нормального распределения
например, НОРМРАСП для нормального распределения< /li>

Значение x такое, что Pr(X Таким образом, команда Excel включает «ОБР»
например, TОБРАБ для распределения T
например, НОРМСТОБР для стандартного нормального распределения
например, НОРМОББР для нормального распределения распространение
Эти функции представлены на вкладке "Формулы" | Группа библиотеки функций | Дополнительные функции | Статистические данные.

ВЕРОЯТНОСТИ T-РАСПРЕДЕЛЕНИЯ И ОБРАТНЫЕ ВЕРОЯТНОСТИ

Это наиболее часто используемые вероятности в статистическом анализе экономических данных.
Они используют функции TDIST и TINV.

Среднее СРСП дает вероятность оказаться в правом хвосте, т. е. Pr(X > x), или оказаться в обоих хвостах, т. е. Pr(|X| > x).
TINV учитывает обратное значение вероятности того, что выпадет обе решки.

Это 1 – Pr(X > 1,9), где функция Excel СТЬЮДРАСП дает Pr(X > 1,9).

Выберите вкладку «Формулы» | Группа библиотеки функций | Дополнительные функции | Статистический | СРЕДНЕЕ РАСП.
Заполните вкладку «Аргументы функции»:

Это дает результат Pr(X > 1,9) = 0,0449.
Поэтому Pr(X

Намного проще ввести в ячейку = 1 - СТЬЮДРАСП(1.9, 9, 2) и нажать .

Это то же значение, для которого Pr(|X| >= x*) = 0,2.
(Поскольку вероятность 0,1 в правом хвосте и вероятность 0,1 в левом хвосте).

Выберите вкладку «Формулы» | Группа библиотеки функций | Дополнительные функции | Статистический | ИННВ.
Заполните вкладку «Аргументы функции»:

Это дает результат Pr(|X| > 1,383) = 0,2.
Поэтому Pr(X

Намного проще ввести в ячейку =TОБР(0,2, 9) и нажать, чтобы получить Pr(|X| > 1,383) = 0,2.

СТАНДАРТНЫЕ НОРМАЛЬНЫЕ ВЕРОЯТНОСТИ И ОБРАТНЫЕ ВЕРОЯТНОСТИ

Они используются реже, чем t-распределение, в статистическом анализе экономических данных.
Они используют функции НОРМРАСП и НОРМРАСП.

ВАЖНО: Формат и результаты этих команд отличаются от обычных.
НОРМРАСП напрямую дает кумулятивную функцию распределения, т.е. Pr(X x) !!
NORMINV считает обратную вероятность оказаться в обоих хвостах, подобно TINV.

Выберите вкладку «Формулы» | Группа библиотеки функций | Дополнительные функции | Статистический | НОРМРАСП.
Заполните вкладку «Аргументы функции» значением Z, равным 1,9.
Это дает результат, что Pr(X

Гораздо проще ввести в ячейку =НОРМРАСП(1.9) и нажать, чтобы получить Pr(X <р>2. Найдите значение x* такое, что Pr(X

Выберите вкладку «Формулы» | Группа библиотеки функций | Дополнительные функции | Статистический | НОРМРАСП.
Заполните вкладку «Аргументы функции» значением вероятности 0,9.
Это дает результат x* = 1,2816, т. е. Pr(X

Намного проще ввести в ячейку = НОРМСТОБР(0,9) и нажать, чтобы получить x* = 1,2816.

НОРМАЛЬНЫЕ ВЕРОЯТНОСТИ И ОБРАТНЫЕ ВЕРОЯТНОСТИ

Стандартная норма устанавливает среднее значение равным 0, а стандартное отклонение равным 1.
Здесь мы рассматриваем нормальное распределение с другими значениями среднего значения µ и стандартного отклонения σ.
Используются функции НОРМРАСП и НОРМРАСП.

<р>1.Найдите Pr(X Здесь стандартное отклонение = σ = sqrt(4.8) = 2,1909.

Выберите вкладку «Формулы» | Группа библиотеки функций | Дополнительные функции | Статистический | НОРМРАСП.
Заполните вкладку «Аргументы функции»

Это дает результат Pr(X > 9) = 0,67596 для X, нормально распределенного со средним значением 8 и дисперсией 4,8.

Намного проще ввести в ячейку = НОРМРАСП(9, 8, 2,1909, 1) и нажать .

Выберите вкладку «Формулы» | Группа библиотеки функций | Дополнительные функции | Статистический | НОРМИНВ.
Заполните вкладку «Аргументы функции»:

вероятность	0,9
среднее	8
standard_dev	2.1909

Намного проще ввести в ячейку = НОРМОБР(0,9, 8, 2,1909) и нажать .

Excel предоставляет вероятности для следующих распределений (вкладка "Формулы" | Группа "Библиотека функций" | Дополнительные функции | Статистические), представленных в приблизительном порядке, наиболее часто используемом при анализе экономических данных:

ГЕНЕРАЦИЯ СЛУЧАЙНЫХ ЧИСЕЛ

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

Использовать вкладку "Данные" | Группа анализа | Анализ данных.
Это позволяет создавать

Обычный
Униформа
Бернулли (0 или 1)
Биномиальное
Пуассон
Дискретный (вы предоставляете значения и вероятности для дискретного распределения с конечным числом возможных значений)
Узорчатый

Сгенерируйте 1000 значений x, где x является нормальным, со средним значением mu = 8 и дисперсией 4,8, поэтому стандартное отклонение = sigma = sqrt(4,8) = 2,1909.

Выберите Данные | Анализ | Анализ данных | Генерация случайных чисел.
Затем в диалоговом окне Генерация случайных чисел введите:

У 1000 случайных выборок среднее значение выборки близко к 8, дисперсия выборки близка к 4,8, а гистограмма близка к колоколообразной кривой.

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

Функция ПРОБ в Excel

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

Синтаксис функции PROB:

PROB (диапазон_x, диапазон_образца, нижний_предел, [верхний_предел])

x_range: диапазон значений x.
prob_range: диапазон x ячеек.
lower_limit: нижний предел значения вероятности
upper_limit: Здесь устанавливается верхний предел значения вероятности. Этот аргумент является необязательным.

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

Простой пример вероятности

<р>1. Введите данные в лист Excel.

<р>2. Чтобы рассчитать вероятность, используйте функцию PROB.

Здесь используется формула =ПРОБ(A2:A10,B2:B10,B13,B14), где

A2:A10 — это диапазон оценок в этом примере.
B2:B10 – шанс получить оценку из столбца A.
B13 — нижний диапазон
B14 означает верхний диапазон.

Вероятность здесь составляет 45%.

Калькулятор вероятности на 2 кости

Проверим, какова вероятность броска двух игральных костей.

Я подготовил данные.

Во-первых, мне нужно рассчитать вероятность броска кубика.

Двумя кубиками можно выбросить от 2 до 12. Формула шанса броска: =СЧЁТЕСЛИ($C$4:$H$9,K4)

Я использовал абсолютную ссылку в формуле.

только один шанс выбросить 2 и 12
два шанса выбросить 3 и 11
три попытки выбросить 4 и 10
четыре шанса выбросить 5 и 9
пять шансов выбросить 6 и 8
шесть шансов выбросить 7

Чтобы рассчитать вероятность выпадения двух кубиков, нужно количество шансов разделить на сумму шансов.

Формула вероятности для двух игральных костей:=$K4/СУММ($K$4:$K$14)

Вероятность двух игральных костей составляет от 2,78% до 16,67%.

На основе приведенных выше вычислений вы можете легко рассчитать вероятность выпадения двойных чисел.

Есть только шесть шансов сделать это, поэтому вероятность выпадения дублей составляет 6/36 = 16,67%. Интересно то, что вероятность выпадения удвоения точно такая же, как и вероятность выпадения 7.

Вероятность подбрасывания монеты

Это 50 %, чтобы перевернуть голову и 50 %, чтобы перевернуть хвост.Вы когда-нибудь задумывались, какова вероятность того, что орёл (или решка) 11 раз подряд подвернётся? Давайте посчитаем в Excel.

Во-первых, давайте посчитаем вероятность того, что вы подбросите решку один раз. Это легко. 50%. голова или флип.

Переведя его на язык Excel, мы можем создать формулу Excel: =POWER(0,5,1)

Кроме того, вероятность равна =POWER(0,5, number_of_tries).

Вероятность выпадения орла (или решки) 11 раз подряд равна 0,04883%, что меньше 0,05%!

Вероятность подбрасывания головы 10 раз составляет 0,09766%.

Вероятность поворота головы (или хвоста) 20 раз подряд составляет 0,000095%.

Мы также можем искать возможность выпадения орла (или решки) несколько раз подряд. Это легко, потому что каждый раз вероятность в 2 раза меньше.

Шанс 1:2048 выпадения орла (или решки) 11 раз подряд. Это означает, что каждые 2048 раз, когда вы подбрасываете монету, 11 раз выпадает 11 орлов (или решек).

Вероятность покера

Было бы интересно проанализировать покерные вероятности.

Во-первых, используйте функцию Excel, чтобы проверить количество покерных комбинаций. Есть 52 карты, из которых вы получаете только 5. С математической точки зрения это простая комбинация для использования. Для этого существует функция Excel COMBIN. Функции принимают всего два аргумента в виде комбинации. Просто используйте формулу Excel =COMBIN(52,5)

Оказывается, в покере существует 2 598 960 комбинаций.

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

Вероятность описывает вероятность того, что какое-то событие произойдет.

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

PROB(x_range, prob_range, нижний_предел, [верхний_предел])

x_range: диапазон числовых значений x.
prob_range: диапазон вероятностей, связанных с каждым значением x.
lower_limit: нижний предел значения, для которого вы хотите получить вероятность.
upper_limit: Верхний предел значения, для которого вы хотите получить вероятность. Необязательно.

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

Пример 1. Вероятность игры в кости

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

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

На следующем изображении показано, как найти вероятность того, что кубик выпадет на число от 3 до 6:

Вероятность оказывается равной 0,5.

Обратите внимание, что аргумент верхнего предела является необязательным. Таким образом, мы могли бы использовать следующий синтаксис, чтобы найти вероятность того, что кости выпадут только на 4:

Вероятность оказывается равной 0,166667.

Пример 2. Вероятность продаж

На следующем рисунке показана вероятность того, что компания продаст определенное количество товаров в предстоящем квартале:

На следующем рисунке показано, как найти вероятность того, что компания совершит 3 или 4 продажи:

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