Распределение Пуассона в Excel

Обновлено: 21.11.2024

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

Продолжая симуляционную стимуляцию, давайте рассмотрим другой способ моделирования сценариев.

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

Давайте рассмотрим ограничивающие факторы при моделировании:

  • Иногда поймать рыбу может и не получиться, но я никогда не получу -2 рыбы в любой день
  • Я также не получу порции рыбы — все или ничего
  • Может быть поймано большое количество рыбы, но с небольшой вероятностью — теоретически я мог бы собрать 5000 рыб в день, хотя это крайне маловероятно.
  • События не могут происходить одновременно, на линии только одна рыба.
  • Ожидаемое среднее число известно за определенный период времени.

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

Давайте использовать λ для обозначения среднего количества событий. Возвращаясь к n , p биномиального распределения, это будет более точно моделировать бесконечное количество испытаний, т.е. n → ∞ и вероятность уменьшается p → 0 таким образом, что np → λ . Это известно как распределение Пуассона, и математическое доказательство можно посмотреть здесь.

Таким образом, вероятность x событий, происходящих с распределением Пуассона, может быть рассчитана по формуле:

В Excel есть две функции, которые могут его вычислить:

  • ПУАССОН(x, среднее, кумулятивный). Когда последний аргумент (кумулятивный) имеет значение TRUE, POISSON возвращает кумулятивную вероятность того, что наблюдаемое значение случайной величины Пуассона с указанным средним значением будет меньше или равно x . Если для кумулятивного параметра установлено значение ЛОЖЬ (или 0, что интерпретируется как ЛОЖЬ), функция ПУАССОН возвращает рассчитанную вероятность по приведенной выше формуле
  • ПУАССОНА.РАСП(x, среднее, кумулятивное). Как и выше, но обратите внимание, что, начиная с Excel 2007, Microsoft обновила многие свои статистические функции. Эта более точная функция заменяет функцию ПУАССОНА, которая остается для обратной совместимости — по крайней мере, на данный момент. Если вы не знакомы с обеими этими функциями, я бы посоветовал использовать этот вариант, поскольку Microsoft сообщила, что может не поддерживать предыдущую функцию в будущих версиях Excel.

В прикрепленном файле Excel приведен пример функции Excel, которую можно использовать на практике:

Анализ моделирования в Excel

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

Давайте рассмотрим логику функции Пуассона и создадим собственную определяемую пользователем функцию в VBA (которую мы назвали PoissonInverse):

  • Проверить, что вероятность, которую мы хотим проверить, находится в допустимых пределах, и вернуть 0, если вероятность равна 0
  • Из-за взаимосвязи между биномиальным распределением и распределением Пуассона для достаточно большого числа испытаний, n p → λ, мы можем использовать обратное биномиальное распределение для оценки x. Мы можем сгенерировать «количество испытаний», используя n = λ / p . Таким образом, мы можем использовать следующую функцию

БИНОМ.ОБР(количество испытаний, среднее / количество испытаний, вероятность)

  • С нашей оценкой для x, используя POISSON.DIST для получения совокупного значения, а затем выполните цикл вперед или назад соответственно, чтобы определить, что это правильное значение, что минимальное совокупное значение больше, чем предоставлена ​​вероятность.

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

Мы можем заставить Excel выбирать случайное число от 0 до 1 с помощью функции. Нет ситуации, когда допустимо значение 1, но, к счастью, RAND() вычисляет только интервал [0,1), т.е. число должно быть больше или равно нулю, но строго меньше единицы .Для заданного среднего это приведет к получению конкретного результата, соответствующего указанному распределению вероятностей, который затем можно будет использовать в модели, как показано на следующем рисунке:

Мы хотим смоделировать, сколько рыбы мы выловим за период в 1000 дней, в среднем четыре рыбы в день. Согласно вышеизложенным пунктам, мы считаем, что это распределение Пуассона. Используя функцию RAND() для генерации вероятностей, используя нашу недавно созданную функцию PoissonInverse, мы можем моделировать, сколько рыбы поймано в данный день, а затем отображать частоту.

При этом мы видим, что наше среднее вычисляется очень близко к среднему значению модели, и частота выглядит довольно неплохо:

Функция POISSON.DIST относится к категории Excel Статистические функции Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel. Он вычислит функцию массы вероятности Пуассона.

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

Функция ПУАССОН.РАСП появилась в MS Excel 2010 и, следовательно, недоступна в более ранних версиях. Для более старых версий MS Excel мы можем использовать функцию ПУАССОН.

Формула

=POISSON.DIST(x,среднее,кумулятивный)

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

  1. X (обязательный аргумент) — это количество событий, для которых мы хотим рассчитать вероятность. Значение должно быть больше или равно 0.
  2. Среднее значение (обязательный аргумент) — это ожидаемое количество событий. Аргумент должен быть больше или равен нулю.
  3. Накопление (обязательный аргумент) — это логический аргумент, указывающий тип рассчитываемого распределения. Это может быть:
    • TRUE — возвращает кумулятивную пуассоновскую вероятность того, что количество случайных событий будет находиться в диапазоне от нуля до x включительно.
    • FALSE — возвращает функцию массы вероятности Пуассона, согласно которой число происходящих событий будет равно x.

Функция массы вероятности Пуассона вычисляет вероятность того, что будет ровно x вхождений, и определяется по формуле:

Где λ – ожидаемое количество событий за указанный период времени.

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

Как использовать функцию ПУАССОН.РАСП в Excel?

Чтобы понять, как использовать функцию POISSON.DIST, давайте рассмотрим пример:

Пример

Предположим, что нам даны следующие данные:

Чтобы узнать кумулятивную вероятность Пуассона, мы будем использовать следующую формулу:

Мы получаем следующий результат:

Чтобы найти функцию массы вероятности Пуассона, мы будем использовать следующую формулу:

Мы получаем следующий результат:

Несколько замечаний о функции POISSON.DIST

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

  • Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
  • Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
  • Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

Распределение Пуассона описывает вероятность достижения k успехов в течение заданного интервала времени.

Если случайная величина X следует распределению Пуассона, то вероятность того, что X = k успехов, можно найти по следующей формуле:

P(X=k) = λ k * e – λ / k!

  • λ: среднее количество успехов за определенный интервал.
  • k: количество успехов
  • e: константа, приблизительно равная 2,71828

В следующем примере показано, как создать график распределения Пуассона в Excel.

Пример: график распределения Пуассона в Excel

Чтобы создать график распределения Пуассона, нам нужно сначала выбрать значение для λ (среднее число успехов):

Далее нам нужно создать столбец для каждого возможного количества успехов:

Обратите внимание, что мы выбрали k = 10 возможных успехов. Мы могли бы выбрать больше, но вероятности становятся очень малыми для значений больше 10, как мы увидим позже в этом посте.

Далее мы можем использовать функцию POISSON.DIST() для вычисления вероятности Пуассона для первого числа успехов:

Затем мы можем скопировать и вставить эту формулу в оставшиеся ячейки столбца B:

Наконец, мы можем выделить каждую из вероятностей Пуассона, затем щелкнуть вкладку «Вставка» на верхней ленте, а затем щелкнуть значок «Вставить столбец или гистограмму» в группе «Диаграммы»:

Ось X графика показывает количество успехов, а ось Y показывает соответствующую вероятность этого количества успехов.

Обратите внимание, что если вы измените значение λ (среднее число успехов), график автоматически изменится, чтобы отразить новые вероятности.

Например, если мы изменим значение λ на 4, вероятности Пуассона и график автоматически обновятся:

Распределение Пуассона — это тип распределения, который используется для расчета частоты событий, которые будут происходить в любой фиксированный момент времени, но эти события независимы. В Excel 2007 или более ранних версиях у нас была встроенная функция для расчета Распределение Пуассона, для версий выше 2007 функция заменена на функцию Poisson.DIst.

Синтаксис

X: это количество событий. Должно быть >=0.

Среднее: ожидаемое количество событий. Это также должно быть >=0.

Совокупный. Это определяет тип рассчитываемого распределения. Здесь у нас есть два варианта: ИСТИНА или ЛОЖЬ.

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

Примеры

Как владелец компании по аренде автомобилей, в среднем у вас 500 клиентов по аренде автомобилей на выходных. Вы ожидаете 520 клиентов в ближайшие выходные.

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

    Здесь x равно 520, а среднее значение равно 500. Введите эти данные в Excel.

Итак, вероятность увеличения количества клиентов по аренде автомобилей с 500 до 520 в ближайшую неделю составляет около 17,93%.

При производстве 1000 единиц автомобильной продукции средний процент брака составляет около 6%. Аналогичным образом, какова вероятность того, что в выборке из 5000 товаров окажется 55 дефектных?

Сначала подсчитайте количество бракованных товаров в 1000 единиц. т. е. λ = np. λ = 1000 * 0,06.

Итак, общее количество бракованных изделий в 1000 единицах составляет 60 единиц. Теперь мы получили общее количество дефектов (x). Итак, х = 60.

Теперь, чтобы уменьшить количество дефектных продуктов с 60 до 55, нам нужно найти процент распределения Пуассона в Excel.

Итак, СРЕДНЕЕ = 55, x = 60.

Приведенная выше формула даст нам значение распределения Пуассона. В ячейке ниже примените формулу 1 – распределение Пуассона в Excel.

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