- Экспоненциальное распределение в MS EXCEL
- Графики функций
- Вывод плотности вероятности
- Отсутствие последействия
- Применение в реальной жизни
- Распределение Вейбулла в MS EXCEL
- Генерация случайных чисел и оценка параметров
- Коэффициенты и параметры функции распределения по закону Вейбулла
- Правила использования функции ВЕЙБУЛЛ в Excel
- Что нужно иметь в виду об экспоненциальной функции (EXP) в Excel
Экспоненциальное распределение в MS EXCEL
В MS EXCEL, начиная с версии 2010, для Экспоненциального распределения имеется функция ЭКСП.РАСП() , английское название – EXPON.DIST(), которая позволяет вычислить плотность вероятности (см. формулу в начале статьи) и интегральную функцию распределения (вероятность, что случайная величина X, распределенная по экспоненциальному закону , примет значение меньше или равное x). Вычисления в последнем случае производятся по следующей формуле:
Экспоненциальное распределение имеет обозначение Exp ( λ ).
Примечание : До MS EXCEL 2010 в EXCEL была функция ЭКСПРАСП() , которая позволяет вычислить кумулятивную (интегральную) функцию распределения и плотность вероятности . ЭКСПРАСП() оставлена в MS EXCEL 2010 для совместимости.
В файле примера на листе Пример приведены несколько альтернативных формул для вычисления плотности вероятности и интегральной функции экспоненциального распределения :
- =1-EXP(- λ *x)
- =ГАММА.РАСП(x;1;1/ λ , т.к. экспоненциальное распределение является частным случаем Гамма распределения
- =ВЕЙБУЛЛ.РАСП(x;1;1/ λ , т.к. экспоненциальное распределение является частным случаем распределения Вейбулла
Примечание : Для удобства написания формул в файле примера создано Имя для параметра распределения – λ .
Графики функций
В файле примера приведены графики плотности распределения вероятности и интегральной функции распределения .
Примечание : Для построения функции распределения и плотности вероятности можно использовать диаграмму типа График или Точечная (со сглаженными линиями и без точек). Подробнее о построении диаграмм читайте статью Основные типы диаграмм .
Вывод плотности вероятности
Наш первый вопрос был: почему λ * e^(−λt) — это плотность вероятности времени до следующего события?
Определение экспоненциального распределения — это распределение вероятности времени *между* событиями в процессе Пуассона.
Смотрите: в период ожидания не происходит ни одного события. Другими словами, Пуассон (X=0).
Есть важная вещь, которую стоит помнить о пуассоновской плотности вероятности: период времени, в течение которого возникают пуассоновские события (X=k), составляет только одну (1) единицу времени.
Как смоделировать распределение вероятности не просто в течение одной единицы времени, а “ничего не произошло в период времени t”?
P(ничего не произошло в течение t единиц времени)
= P(X=0 в первую единицу времени)
* P(X=0 во вторую единицу времени)
* … * P (X=0 в t-ую единицу времени)
= e^−λ * e^−λ * … * e^−λ = e^(-λt)
Распределение Пуассона предполагает, что события возникают независимо друг от друга. Следовательно, можно посчитать вероятность нулевого успеха в течение t единиц времени, умножив P(X=0 в единицу времени) на t раз.
P(T > t) = P(X=0 в t единиц времени) = e^−λt
* T : случайная переменная времени до первого успешного события
* X : количество событий
* P(T > t) : вероятность того, что время ожидания события больше,чем t единиц времени
* P(X=0 в t единиц времени): вероятность нулевого успеха в t единиц времени
Плотность вероятности — это производная от кумулятивной функции распределения вероятности.
Поскольку у нас уже есть кумулятивная функция распределения вероятности экспоненциального распределения, 1 — P(T > t), мы можем получить плотность вероятности, продифференцировав ее.
Отсутствие последействия
Определение:
P(X > a + b | X > a) = P(X > b)
Это означает:
Доказательство:
Отсутствие последействия — полезный параметр?
Рационально ли моделировать долговечность механического устройства, используя экспоненциальное распределение?
Например, если устройство уже проработало девять лет, отсутствие последействия означает, что вероятность его бесперебойной работы в следующие три года (то есть в сумме 12 лет) точно такая же, как для совершенно нового механизма.
P(X > 12|X > 9) = P(X > 3)
Это уравнение кажется вам разумным?
Мне нет. Как показывает мой опыт, чем старше устройство, тем вероятнее поломка. Смоделировать этот параметр — возрастающую интенсивность отказов — можно с помощью распределения Вейбулла.
Так когда же стоит применять экспоненциальное распределение (постоянную интенсивность отказов)?
Автомобильные происшествия. Если никто не врезался в вас за последние пять часов, это не снижает и не повышает шансы попадания в аварию.
Где еще есть отсутствие последействия?
Экспоненциальное распределение — это единственное непрерывное распределение с отсутствием последействия (или распределение с постоянной интенсивностью отказов). Геометрическое распределение, его дискретный аналог, является единственным дискретным распределением с отсутствием последействия.
Применение в реальной жизни
a) Моделирование времени ожидания
У значений экспоненциальной случайной величины есть много маленьких значений и немного крупных значений. Автобус, который вы ждете, скорее всего приедет в течение 10 минут нежели в течение 60 минут.
Используя экспоненциальное распределение, можно ответить на следующие вопросы:
1. Автобус в среднем приезжает каждые 15 минут (предположим, что время между прибытием автобусов имеет экспоненциальное распределение, значит, количество автобусов, прибывающих в течение часа имеет распределение Пуассона). И я только что пропустила автобус! Водитель был злой. Как только я пришла, он закрыл двери и уехал. Если следующий автобус не приедет в течение десяти мнут, я вызову Uber, иначе опоздаю. Какова вероятность того, что ожидание следующего автобуса займет меньше 10 минут?
2. Девяносто процентов автобусов прибывают через сколько минут после предыдущего?
3. В течение какого времени в среднем прибывают два автобуса?
b) Моделирование отказа
Раз мы можем смоделировать успешное событие (прибытие автобуса), почему бы не смоделировать отказ — время поломки продукта.
Количество часов, которое AWS оборудование способно проработать без перезагрузки, соответствует экспоненциальному распределению со средним значением 8 000 часов в год.
1. У вас нет резервного сервера, а вам нужна бесперебойная работа в течение 10 000 часов. Какова вероятность того, что вы сможете выполнить эту задачу без перезагрузки сервера?
2. Какова вероятность того, что сервер не потребует перезагрузки между 12 и 18 месяцами?
Заметьте, что иногда экспоненциальное распределение не подходит — когда интенсивность отказов изменяется в течение срока службы. Тем не менее это единственное распределение, обладающее уникальным параметром — постоянной интенсивностью отказов.
c) Моделирование времени обслуживания (Теория очередей)
Время обслуживания (например, как долго в кафе готовят мне буррито) тоже можно смоделировать как экспоненциально распределенные переменные.
Общая длина процесса — последовательность нескольких независимых задач — соответствует распределению Эрланга: распределению суммы нескольких независимых экспоненциально распределенных переменных.
Распределение Вейбулла в MS EXCEL
В MS EXCEL, начиная с версии 2010, для распределения Вейбулла имеется функция ВЕЙБУЛЛ.РАСП() , английское название – WEIBULL.DIST(), которая позволяет вычислить плотность вероятности и интегральную функцию распределения (вероятность, что случайная величина X, имеющая распределение Вейбулла , примет значение меньше или равное x).
Примечание : До MS EXCEL 2010 в EXCEL была функция ВЕЙБУЛЛ() , которая позволяет вычислить интегральную функцию распределения и плотность вероятности . ВЕЙБУЛЛ() оставлена в MS EXCEL 2010 для совместимости.
Генерация случайных чисел и оценка параметров
Используем обратную функцию распределения (или p – quantile , см. статью про Квантили ), которая для распределения Вейбулла может быть выражена в явном виде с использованием элементарных функций:
С помощью этой функции можно сгенерировать значения случайной величины, имеющей распределение Вейбулла . Для этого нужно использовать формулу MS EXCEL:
=бета*(-LN(СЛЧИС()))^(1/альфа)
Функция СЛЧИС() генерирует непрерывное равномерное распределение от 0 до 1, что как раз соответствует диапазону изменения вероятности (см. файл примера лист Генерация ).
Теперь имея массив случайных чисел, сгенерированных с заданными параметрами распределения альфа и бета (пусть их будет 200), оценим параметры распределения.
Оценку параметров альфа и бета можно сделать с помощью линейной регрессии. Для этого необходимо привести функцию распределения Вейбулла к виду обычной прямой, задаваемой уравнением Y=aX+b. Для этого сделаем следующие преобразования:
Сравнивая выражение с уравнением прямой Y=ax+b получим, что:
- Y соответствует левая часть выражения,
- X – соответствует ln(x),
- параметр распределения бета соответствует коэффициенту a , отвечающего за наклон прямой к оси абсцисс.
- выражение –бета*ln(альфа) соответствует коэффициенту b (ордината точки пересечения с осью Oy).
По сути, мы практически построили Вероятностный график (probability plot) для распределения Вейбулла : если отсортированные значения ln(x), отложенные по оси Ох, лягут приблизительно вдоль прямой, то это будет означать, что значения выборки взяты из распределения Вейбулла. Осталось модифицировать ось Оу с помощью формулы =LN(-LN(1-Ui)), где Ui=(i-0,5)/200, а i=1; 2; …; 200.
Заметим, что -LN(1-Ui) – это обратная функция распределения с параметрами альфа=1 и бета=1. Второй логарифм нам потребовался, т.к. по оси абсцисс отложены не сами x, а ln(x).
Примечание : Т.к. форма распределения Вейбулла существенно зависит от его параметров, то вместо альфа=1 и бета=1 для обратной функции лучше использовать точечные оценки этих параметров , полученные на основании выборки . О том как вычислить оценку параметров альфа и бета см. ниже.
В файле примера на листе Генерация построен соответствующий Вероятностный график .
С помощью функции НАКЛОН() вычислим наклон получившейся кривой (коэффициент прямой а, англ. slope ), который служит оценкой параметра бета .
Функция ОТРЕЗОК() вернет ординату точки пересечения с Оу (коэффициент прямой b ). Выражение =EXP(-b/бета) служит оценкой параметра альфа .
Построив частотную гистограмму по данным из выборки , сравним ее с плотностью вероятности модельного распределения, т.е. распределения, с помощью которого были сгенерированы сами значения выборки . Из-за наличия случайной ошибки выборки (sampling error) значения могут расходиться.
Процедура построения модельного распределения следующая:
- Значения плотности вероятности модельного распределения вычислены как P i – P i-1 , где P – значения интегральной функции распределения на границах интервалов гистограммы, а dx =1. (Обычно, плотность вероятности непрерывного распределения вычисляется как производная функции распределения dP/dx).
- Вследствие такого преобразования, мы перешли от непрерывного распределения к дискретному . Необходимо убедиться, что сумма плотностей вероятностей равна 1.
- Пронормировав модифицированные плотности вероятностей на количество значений в выборке (200), вычислим для каждого интервала частоты модельного распределения (можно обойтись без нормирования, использовав вспомогательную ось диаграммы).
В итоге получим:
Как видно из диаграммы выше, совпадение модельного распределения с гистограммой выборки достаточно хорошее.
Примечание : При построении диаграммы использована гистограмма и график с маркерами . Подробнее о построении диаграмм см. Основы построения диаграмм в MS EXCEL .
Также можно сравнить плотности вероятностей модельного распределения и распределения с параметрами, полученными в результате оценки.
Как видно из диаграммы выше, совпадение также достаточно хорошее.
СОВЕТ : Т.к. генерирование случайных чисел происходит с помощью функции СЛЧИС() , то нажимая клавишу F9 , можно каждый раз получать новую выборку и, соответственно, новую оценку параметров.
СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .
Коэффициенты и параметры функции распределения по закону Вейбулла
Интегральная функция распределения соответствует значению вероятности события, при котором некоторая величина X, распределенная по закону Вейбулла, будет принимать значение, которое <=x.
Формула функции ВЕЙБУЛЛ:
Формула плотности вероятности для данного распределения:
Данное распределение характеризуется двумя основными параметрами:
- α – характеризует форму распределения.
- β – характеризует масштаб.
Оба параметра указываются значениями из диапазона от 0 (не включительно) до бесконечности со знаком плюс (при этом для практического применения распределения рационально в качестве параметра β (бетта) указывать значение >=1).
Распределение Вейбулла может быть преобразовано к обычному экспоненциальному распределению, если параметр α (альфа) принимает значение 1.
Целесообразность применения:
- Определение времени наработки без отказа до момента выхода из строя самого уязвимого элемента системы.
- Определение времени работы до момента разрушения вследствие внутренних причин (физический износ материала). Если причина разрушения материала обусловлена внешними факторами, применяют экспоненциальное распределение (то есть, принимают α=1).
Примечание:
Рассматриваемая функция использовалась до выхода MS Office версии 2010 года. В последующих версиях она заменена аналогичной функцией ВЕЙБУЛЛ.РАСП, однако оставлена для обеспечения совместимости.
Правила использования функции ВЕЙБУЛЛ в Excel
Функция имеет следующий синтаксис:
=ВЕЙБУЛЛ()
Описание аргументов (все являются обязательными):
- x – принимает числовое значение некоторой величины с распределением Вейбулла, для которой необходимо определить функцию;
- альфа – принимает числовое значение, характеризующее α-параметр распределения;
- бета – принимает числовое значение, которое характеризует β-параметр распределения;
- интегральная – принимает данные логического типа, определяющие форму вычисляемой функции: ИСТИНА – будет возвращена интегральная функция, ЛОЖЬ – будет возвращена функция плотности распределения Вейбулла.
Примечания:
- Первые три аргумента функции должны принимать числовые значения или данные, которые могут быть преобразованы к числам, иначе результатом выполнения функции ВЕЙБУЛЛ будет код ошибки #ЗНАЧ!
- Если аргумент x принимает значение 0, функция вернет 0 (нуль) при любых значениях остальных аргументов. Если первый аргумент (x) указан числом из диапазона отрицательных значений, будет возвращен код ошибки #ЧИСЛО! Аналогичная ошибка возникает, если аргументы, характеризующие α-параметр и β-параметр соответственно не взяты из диапазона положительных значений (0 также исключен).
- Последний аргумент может указан в виде числа, соответствующего логическим значениям: 1 – ИСТИНА, 0 – ЛОЖЬ.
Что нужно иметь в виду об экспоненциальной функции (EXP) в Excel
Экспоненциальная функция в Excel часто используется с функцией журнала, например, если мы хотим найти скорость роста или уменьшения, мы будем использовать функции EXP и LOG вместе.
Мы также можем использовать функцию POWER вместо экспоненциальной функции в Excel, но единственным отличием является точность измерения. При использовании функции POWER ее можно указать от e до 2.71 или до десятичных разрядов 3-4, но функция EXP в Excel обычно принимает значение от e до десятичных разрядов 9.
Таким образом, если вы вычисляете экспоненту в серии Excel для работы с нелинейными экспоненциальными функциями, экспоненциальное значение которых у нас есть, лучше использовать функцию EXP в Excel, чем функцию POWER.
Функция EXP в Excel всегда принимает числовое значение в качестве входных данных, когда мы предоставляем ввод, отличный от числового значения, которое она генерирует #NAME? Ошибка.
В случае сложных показателей, например = EXP (- (2.2 / 9.58) ^ 2), необходимо соблюдать осторожность с помощью скобок, если скобки перепутаны, выходные данные могут отличаться от фактических выходных данных, поэтому это должно быть = EXP (- ((2.2 / 9.58) ^ 2)).
- https://excel2.ru/articles/eksponencialnoe-raspredelenie-nepreryvnye-raspredeleniya-v-ms-excel
- https://zen.yandex.ru/media/nuancesprog/eksponencialnoe-raspredelenie-5eba41abd329951511c36c2d
- https://excel2.ru/articles/raspredelenie-veybulla-nepreryvnye-raspredeleniya-v-ms-excel
- https://exceltable.com/funkcii-excel/zakon-raspredeleniya-veybulla
- http://windowsbulletin.com/ru/%D0%BA%D0%B0%D0%BA-%D1%81%D0%B4%D0%B5%D0%BB%D0%B0%D1%82%D1%8C-%D1%8D%D0%BA%D1%81%D0%BF%D0%BE%D0%BD%D0%B5%D0%BD%D1%86%D0%B8%D0%B0%D0%BB%D1%8C%D0%BD%D1%8B%D0%B9-%D1%80%D0%B0%D1%81%D1%87%D0%B5%D1%82-%D1%87%D0%B8%D1%81%D0%B5%D0%BB-%D0%B2-%D0%B4%D0%B8%D0%B0%D0%BF%D0%B0%D0%B7%D0%BE%D0%BD%D0%B5-%D1%8F%D1%87%D0%B5%D0%B5%D0%BA-%D0%B2-Excel/