Эмпирическая функция распределения в Excel

Обновлено: 21.11.2024

Теперь мы определим понятие распределения вероятностей для дискретных случайных величин, т. е. случайных величин, которые принимают дискретный набор значений. Такие случайные величины обычно принимают конечный набор значений (орел или решка, люди, живущие в Лондоне, результаты теста IQ), но они также могут включать в себя случайные величины, принимающие счетное множество значений (0, 1, 2, 3). , …).

Определение 1. Функция частоты (вероятности), также называемая функцией плотности вероятности (сокращенно pdf), дискретной случайной величины x определяется так, что для любого значения t в домене случайной величины (т. е. в ее выборочном пространстве):

т.е. вероятность того, что x примет значение t.

Соответствующая (кумулятивная) функция распределения F(x) определяется при значении t с помощью

Свойство 1. Для любой дискретной случайной величины, заданной в диапазоне S с функцией частоты f и функцией распределения F

.

Доказательство: это характеристики функции вероятности P(E) в соответствии со свойством 1 основных концепций вероятности.

Наблюдение: если f — функция частоты дискретного случайного x с функцией распределения F, то f(t) — вероятность того, что x принимает значение t и F(t) – это вероятность того, что x принимает значение, меньшее или равное t. Таким образом, вероятность того, что x принимает значение t такое, что t1 Рисунок 1. Таблица функций частоты и распределения

Учитывая частотную функцию, определенную таблицей в диапазоне B4:B11, мы можем определить функцию распределения в диапазоне C4:C11, поместив формулу =B4 в ячейку C4 и формулу =B5+C4 в ячейку C5 и затем скопируйте эту формулу в ячейки с C6 по C11 (например, выделив диапазон C5:C11 и нажав Ctrl-D).

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

Рисунок 2. Диаграммы функций частоты и распределения

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

Где R1 — это диапазон, определяющий дискретные значения случайной величины x (например, A4:A11 на рисунке 1), а R2 — это диапазон, состоящий из значений частоты f(x), соответствующих значениям x в R1 (например, B4:B11 на рис. 1), функция Excel PROB определяется следующим образом:

PROB (R1, R2, c) = значение частоты f(c)
PROB (R1, R2,, c ) = кумулятивное значение распределения F(c)
PROB (R1, R2, a , b ) = вероятность что x принимает значение t между a и b включительно, т.е.

Таким образом, в примере 1 мы можем поместить формулу =PROB(A4:A11,B4:B11,,A8) в ячейку C8 и аналогичным образом для других значений в столбце C. Также для функции частоты в примере 1,

Для примера 1 также следует, что P(3 ≤ x ≤ 5) = f(3) + f (4) + f(5) = F(5) – F(2) = 0,31.

Пример 2. Определите частотную функцию для данных в столбце A на рис. 3.

Рисунок 3. Построение частотной функции

Сначала создайте список уникальных значений данных. Это можно сделать, скопировав необработанные оценки данных в столбце A в новое место на листе (например, в столбец C в приведенном выше примере) и выбрав «Данные» > «Инструменты данных» | «Удалить дубликаты». Затем выделенные данные можно дополнительно отсортировать, выбрав «Данные» > «Сортировка и фильтрация|Сортировка». Результат появится в диапазоне ячеек C4:C8 выше. В качестве альтернативы используйте функции Real Statistics QSORT и NoDupes, как описано в дополнительных функциях.

Затем используйте функцию СЧЁТЕСЛИ (см. Встроенные функции), чтобы подсчитать, сколько раз каждая оценка встречается в выборке данных. Например. ячейка D4 содержит формулу =СЧЁТЕСЛИ($A$3:$A$15,C4), которая имеет значение 2, поскольку элемент данных 12 (значение в ячейке C4) появляется дважды в необработанных данных. Поскольку имеется 12 элементов данных, правильное значение частотной функции для элемента данных 2 составляет 2/12 = 0,167, что можно рассчитать по формуле D4/D$9 в ячейке E4, где D9 содержит формулу СУММ(D4:D8) .

Функция реальной статистики: Ресурсный пакет Real Statistics предоставляет следующую дополнительную функцию массива для создания частотной функции.

FREQTABLE(R1) = массив n × 3, который содержит таблицу частот для данных в диапазоне R1, где n = количество уникальных значений в R1 ( т.е. количество элементов данных в R1 без дубликатов)

Пример 3. Повторите пример 2, используя функцию FREQTABLE.

Рисунок 4. Использование функции FREQTABLE

Вывод =FREQTABLE(A3:A14) (где A3:A14 соответствует рисунку 3) показан в диапазоне M4:O8 на рис. 4 (заголовки в строке 3 были добавлены вручную).

Инструмент анализа данных реальной статистики. Пакет ресурсов также содержит инструмент анализа данных под названием «Гистограмма с наложением нормальной кривой». Это работает так же, как функция FREQTABLE, за исключением того, что вам не нужно указывать размер таблицы частот. Инструмент анализа автоматически определяет размеры выходных данных.

Наблюдение: см. Гистограммы для примеров использования функции FREQTABLE и инструмента анализа данных таблицы частот.

Наблюдение: понятие функции вероятности можно распространить на несколько случайных величин. Теперь дадим определение для двух случайных величин.

Определение 2: f(x, y) – это совместная функция плотности вероятности (PDF) случайных величин x, y, если для любые значения a и b в доменах x и y соответственно

В этом случае кумулятивная функция распределения определяется выражением

Свойство 2: если x — случайная величина с pdf f, а y — случайная величина с pdf g, то x и y независимы тогда и только тогда, когда функция f(x) ∙ g(y) является совместной ФПВ для х, у.

Доказательство: следует из определения 3 основных понятий вероятности.

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

Функция кумулятивного распределения (CDF) описывает вероятность того, что случайная величина примет значение, меньшее или равное некоторому числу.

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

В следующем примере показано, как рассчитать и построить CDF в Excel.

Пример: расчет и построение CDF в Excel

Сначала создадим в Excel следующий набор данных:

Далее укажем среднее значение и стандартное отклонение распределения:

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

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

Теперь CDF завершен. Мы интерпретируем значения следующим образом:

  • Вероятность того, что случайная величина примет значение, равное или меньше 6, составляет 0,00135.
  • Вероятность того, что случайная величина примет значение, равное или меньшее 7, составляет 0,00383.
  • Вероятность того, что случайная величина примет значение, равное или меньшее 8, составляет 0,00982.

Чтобы визуализировать этот CDF, мы можем выделить каждое значение в столбце B. Затем мы можем щелкнуть вкладку «Вставка» на верхней ленте и нажать «Вставить линейную диаграмму», чтобы создать следующую диаграмму:

Значения по оси X показывают значения из набора данных, а значения по оси Y показывают значения CDF.

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

Техника

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

<р>1. DUniform: использует только список наблюдаемых значений

<р>2. CumulA и CumulD или, проще говоря, Ogive: создает кумулятивное распределение и, следовательно, допускает значения между наблюдаемыми и значениями за пределами наблюдаемого диапазона;

<р>3. Гистограмма: когда у вас огромное количество данных

Вариант 1: дискретное равномерное распределение

ModelRisk предлагает дискретное равномерное распределение, которое принимает один параметр: список значений.Затем он случайным образом выбирает любое из этих значений с равной вероятностью (выборка с заменой). Таким образом, например, =VoseDUniform() будет генерировать на каждой итерации одно из пяти значений 1, 4, 5, 7 или 10 (каждое значение имеет на каждой итерации вероятность выбора 20%). На рисунке ниже показано, как выглядит распределение вероятностей.

Представим, что у нас есть данные в массиве ячеек под названием «Наблюдения». Просто написав =VoseDUniform(Observations), мы создадим распределение, которое повторяет шаблон наблюдаемых данных. Вы можете использовать распределение DUniform как для дискретных, так и для непрерывных данных, если у вас достаточно наблюдений.

Вариант 2: кумулятивное распределение

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

Наше лучшее предположение о кумулятивной вероятности точки данных в наборе наблюдений равно r/(n+1), где r — ранг точки данных в наборе данных, а n — количество наблюдений. . Таким образом, при выборе этого варианта необходимо:

  • Расположите наблюдения в порядке возрастания или убывания (в Excel есть значок, упрощающий эту задачу);
  • В соседнем столбце вычислите ранг данных: напишите столбец со значениями 1, 2, . п;
  • В следующем столбце рассчитайте совокупную вероятность F(x) = rank/(n+1);
  • Используйте данные и столбцы F(x) в качестве входных данных для распределения VoseCumulA или VoseCumulD вместе с субъективными оценками возможных минимальных и максимальных значений.

Более простой вариант — использовать распределение Ogive от ModelRisk, которое выполняет эти расчеты автоматически.

Обратите внимание, что минимальное и максимальное значения оказывают какое-либо влияние только на самую первую и последнюю строки интерполяции для создания кумулятивного распределения, поэтому распределение становится все менее и менее чувствительным к выбранным значениям по мере того, как при его построении используется все больше данных. Поскольку минимальное и максимальное значения выбираются субъективно, часто имеет смысл использовать субъективное распределение (например, PERT) для обоих.

Вариант 3. Распределение по гистограмме

Иногда (правда, не так часто, как хотелось бы) у нас есть огромное количество случайных наблюдений, из которых мы хотели бы построить распределение (например, сгенерированные значения из другого моделирования). Приведенные выше параметры DUniform и Cumul начинают работать немного медленнее в этот момент и моделируют переменную с излишне мелкими деталями. Теперь более практичным подходом является создание гистограммы данных и ее использование. Функция массива ЧАСТОТА( ) в Excel анализирует набор данных и сообщает, сколько из них лежит в любом количестве смежных диапазонов ячеек. Распределение ModelRisk VoseHistogram имеет три параметра: минимально возможное значение, максимально возможное значение и массив частот (или вероятностей) бинов, который представляет собой просто массив FREQUENCY().

Создание эмпирического совместного распределения для двух или более переменных

Для данных, собранных наборами (парами, тройками и т. д.), могут существовать закономерности корреляции, присущие наблюдениям, которые мы хотели бы сохранить при подгонке эмпирических распределений к данным. Примером могут служить данные о весе и росте людей, где между ними явно существует какая-то связь. Сочетание использования распределения StepUniform (где min = 1 и max = количество строк) с функцией Excel VLOOKUP() или OFFSET() позволяет нам сделать это легко.

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

Эмпирические и параметрические распределения

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

С другой стороны, эмпирическое распределение не основано на математической функции. Он основан на самих данных.

Когда мы создаем модель для имитации, некоторые входные данные являются случайными величинами (в противном случае мы бы не занимались моделированием). Если для представления переменной используется параметрическое распределение, мы делаем одно из двух. Либо мы приходим к параметрам с помощью «экспертных» оценок, либо путем подгонки распределения к данным и оценки параметров.

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

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

Определение эмпирического распределения

Для значения x вероятность ≤ X равна:

P(x) = (количество точек данных ≤ x) / N

Где N — общее количество точек данных.

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

1
2
2
4
4
5
5
5
6
7
7
8
9
9
10

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

Выборка

Выборка из дистрибутива бывает двух видов: дискретная и непрерывная.

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

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

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

Дискретная выборка

Дискретная выборка относительно проста. Мы просто находим значение случайного числа по вертикальной оси и идем вправо до пересечения с кривой распределения. Затем спуститесь к горизонтальной оси и запишите значение. Значение является образцом.

В приведенном ниже примере мы сгенерировали случайное число 0,44. Пример значения – 5.

Непрерывная выборка

Непрерывная выборка немного сложнее, так как нам нужно выполнять интерполяцию между точками данных. Один из способов сделать это — использовать процентили. Для этого мы вычисляем значение в заданном процентиле. Случайным числом является процентиль, представляющий собой долю от 1. На приведенной ниже диаграмме показано исходное эмпирическое распределение, а также интерполированные значения, рассчитанные как процентили.

Обратите внимание, что значения данных с более чем одним экземпляром будут возвращать это значение точно для некоторого диапазона вероятности (вертикальные линии). Например, если случайное число находится в диапазоне от 0,3752 до 0,5, выборка будет ровно 5.

В этом примере допустим, что мы генерируем случайное число 0,77. Двигаясь вправо до пересечения с зеленой линией, а затем спускаясь к горизонтальной оси, мы получаем выборочное значение 7,78.

Мастер моделирования RVEMPIRICAL Функция

Simulation Master — это надстройка Excel® для моделирования методом Монте-Карло. Это позволяет проводить эмпирическую выборку с помощью функции RVEMPIRICAL. Каждый раз, когда вычисляется электронная таблица, функция выбирает данные и возвращает новое значение.

К модели можно добавить эмпирическое распределение с помощью функции эмпирического распределения. Функция вводится в нужную ячейку, как показано ниже.

=RVEMPIRICAL (диапазон, тип)

    • диапазон – это диапазон ячеек, содержащий данные, от которых зависит распределение.
    • type – это тип данных: "непрерывный" или "дискретный". Тип необходимо указывать в кавычках.

    Диапазон данных должен соответствовать следующему:

    • Все ячейки должны быть числовыми.
    • Диапазон должен состоять из одного столбца.
    • Диапазон должен быть отсортирован по возрастанию (от меньшего к большему) с наименьшим значением в верхней ячейке и наибольшим значением в нижней ячейке.

    Дискретный вернет только значения, включенные в диапазон данных. Continuous использует интерполяцию для возврата значений между точками данных. Функция Excel ПРОЦЕНТИЛЬ.ВКЛ используется для создания непрерывных значений.

    Функция должна возвращать числовое значение, иначе возникнет ошибка.

    Ограничения

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

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

    Excel является зарегистрированным товарным знаком корпорации Microsoft. Используется с разрешения Microsoft.

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