Распределение вейбулла в excel

Обновлено: 06.07.2024

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

Необходимо отметить особую роль распределения Вейбулла в попытках понять природу lead time (времени-в-процессе) профессиональных сервисов и умственного труда. Большое количество выборок lead time из реальной практики достаточно хорошо соответствует распределению Вейбулла. И, конечно, существуют выборки, которые не соответствуют такому распределению. Упрощающее допущение о том, что данные о lead time по каждому новому сервису предположительно будут соответствовать распределению Вейбулла, приводило нас к определенным открытиям и выводам. Впоследствии оказалось, что мы можем отказаться от “допущения Вейбулла”, но инсайты и практические советы по его результатам останутся в силе.

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

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

Приведу несколько возможных сценариев:

Из всех распределений в семействе Вейбулла те, у которых значение параметра формы соответствует 1<k<2, находятся в счастливом среднем домене хорошо управляемой умственной работы. Распределение с k=1,5 представляет пример распределения из такого домена. Распределения Вейбулла с k<1 находятся в Крайнестане и домене непредсказуемых сервисов “бросай-все-и-чини”. Распределения Вейбулла с k>2 относятся к Центральному Среднестану, в домене “Вы не делаете умственной работы, на грани развала”. Распределения с k=1 и k=2 обозначают границы.

Допущение (но не всегда корректное, насколько вы успели узнать) о том, что распределение lead time является распределением Вейбулла, позволяло нам думать в простых категориях формы и масштаба. Форма (в случае с распределением Вейбулла это равнозначно параметру формы) выражала всю информацию о паттернах рисков и задержек в сервисе. Параметр масштаба говорит нам о том, какие единицы времени отложены по горизонтальной оси.

Теперь уберем допущение Вейбулла. Паттерны рисков и источников задержек продолжают определять форму, только теперь не в численном виде, а в виде линии графика распределения lead time, который менеджер приносит на встречу по обзору сервиса. И Вам все еще важно знать временную шкалу по оси Х: насколько быстро вы поставляете результат при типичном, умеренно-оптимистичном сценарии? Измеряется ли это время в часах? в днях? неделях? месяцах? Если первичным источником для ответа на это вопрос являются ваши “эмпирические” данные, то правильной привычкой будет наблюдение за 60-ым и 70-ым процентилями. Математики могут определить верхнюю границу доверительного интервала для оценки медианы значений lead time. При типичном размере выборок lead time, собираемых и используемых менеджерами в профессиональных сервисах, верхняя (правая) граница ожидаемо находится правее (“восточнее”) 60-ого процентиля. Что полностью совпадает с точкой формоинвариантного 63-его процентиля кривой распределения Вейбулла. И это справедливо для любого распределения, как Вейбулла, так и другого.

Рассмотрим Экспоненциальное распределение, вычислим его математическое ожидание, дисперсию, медиану. С помощью функции MS EXCEL ЭКСП.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметра распределения.

Экспоненциальное распределение (англ. Exponential distribution ) часто используется для расчета времени ожидания между случайными событиями. Ниже описаны ситуации, когда возможно применение Экспоненциального распределения :

Промежутки времени между появлением посетителей в кафе;
Промежутки времени нормальной работы оборудования между появлением неисправностей (неисправности возникают из-за случайных внешних влияний, а не по причине износа, см. Распределение Вейбулла );
Затраты времени на обслуживание одного покупателя.

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

График плотности распределения вероятности и интегральной функции Экспоненциального распределения выглядит следующим образом (см. ниже).

СОВЕТ : Подробнее о Функции распределения и Плотности вероятности см. статью Функция распределения и плотность вероятности в MS EXCEL .

Экспоненциальное распределение тесно связано с дискретным распределением Пуассона . Если Распределение Пуассона описывает число случайных событий, произошедших за определенный интервал времени, то Экспоненциальное распределение должноописывать длину интервала времени между двумя последовательными событиями.

Приведем пример. Предположим, что число машин, прибывающих на парковку днем, описывается распределением Пуассона со средним значением равным 15 машин в час (параметр распределения λ =15). Вероятность того, что на стоянку в течение часа приедет k машин равно:

Т.к. в среднем в час на стоянку приезжает 15 машин, то среднее время между 2-мя приезжающими машинами равно 1час/15машин=0,067. Т.к. среднее время между 2-мя событиями равно обратному значению параметра экспоненциального распределения , то параметр λ =15 , а плотность соответствующего экспоненциального распределения равна:

Экспоненциальное распределение в 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/ λ ;ИСТИНА) , т.к. экспоненциальное распределение является частным случаем распределения Вейбулла ;

Примечание : Для удобства написания формул в файле примера создано Имя для параметра распределения - λ .

Графики функций

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

Примечание : Для построения функции распределения и плотности вероятности можно использовать диаграмму типа График или Точечная (со сглаженными линиями и без точек). Подробнее о построении диаграмм читайте статью Основные типы диаграмм .

Генерация случайных чисел

Для генерирования массива чисел, распределенных по экспоненциальному закону , можно использовать формулу =-LN(СЛЧИС())/ λ

Функция СЛЧИС() генерирует непрерывное равномерное распределение от 0 до 1, что как раз соответствует диапазону изменения вероятности (см. файл примера лист Генерация ).

Если случайные числа содержатся в диапазоне B14:B213 , то оценку параметра экспоненциального распределения λ можно сделать с использованием формулы =1/СРЗНАЧ(B14:B213) .

Задачи

Экспоненциальное распределение широко используется в такой дисциплине как Техника обеспечения надежности (Reliability Engineering). Параметр λ называется интенсивность отказов , а 1/ λ – среднее время до отказа .

Предположим, что электронный компонент некой системы имеет срок полезного использования, описываемый Экспоненциальным распределением с интенсивностью отказа равной 10^(-3) в час, таким образом, λ = 10^(-3). Среднее время до отказа равно 1000 часов. Для того чтобы подсчитать вероятность, что компонент выйдет из строя за Среднее время до отказа, то нужно записать формулу:

Т.е. результат не зависит от параметра λ .

В MS EXCEL решение выглядит так: =ЭКСП.РАСП(10^3; 10^(-3); ИСТИНА)

Задача . Среднее время до отказа некого компонента равно 40 часов. Найти вероятность, что компонент откажет между 20 и 30 часами работы. =ЭКСП.РАСП(30; 1/40; ИСТИНА)- ЭКСП.РАСП(20; 1/40; ИСТИНА)

СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

Функция ВЕЙБУЛЛ в Excel предназначена для определения интегральной функции распределения Вейбулла, а также плотность вероятности (в зависимости от значения, переданного в качестве последнего аргумента), и возвращает соответствующее числовое значение.

Коэффициенты и параметры функции распределения по закону Вейбулла

Интегральная функция распределения соответствует значению вероятности события, при котором некоторая величина X, распределенная по закону Вейбулла, будет принимать значение, которое <=x.

Формула функции ВЕЙБУЛЛ:

Формула плотности вероятности для данного распределения:

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

α - характеризует форму распределения.
β - характеризует масштаб.

Оба параметра указываются значениями из диапазона от 0 (не включительно) до бесконечности со знаком плюс (при этом для практического применения распределения рационально в качестве параметра β (бетта) указывать значение >=1).

Распределение Вейбулла может быть преобразовано к обычному экспоненциальному распределению, если параметр α (альфа) принимает значение 1.

Определение времени наработки без отказа до момента выхода из строя самого уязвимого элемента системы.
Определение времени работы до момента разрушения вследствие внутренних причин (физический износ материала). Если причина разрушения материала обусловлена внешними факторами, применяют экспоненциальное распределение (то есть, принимают α=1).

Рассматриваемая функция использовалась до выхода MS Office версии 2010 года. В последующих версиях она заменена аналогичной функцией ВЕЙБУЛЛ.РАСП, однако оставлена для обеспечения совместимости.

Плотность распределения Вейбулла в Excel

Пример 1. Определить интегральную функцию распределения Вейбулла и функцию плотности вероятности для некоторого значения x=85, если данная величина подчиняется закону распределения Вейбулла с α-параметром равным 17 и β-параметром равным 90.

Вид таблицы данных:

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

Четвертый аргумент принимает значение ИСТИНА для расчета интегральной функции. Полученный результат:

Аналогичность определим функцию плотности:

В результате с помощью одной функции мы вычислили 2 коэффициента в Excel.

Распределение Вейбулла случайной величины в Excel

Пример 2. Сгенерировать 10 случайных величин, имеющих распределение Вейбулла. Определить интегральные значения при известных альфа- и бета-параметрах (8 и 35 соответственно).

Вид таблицы данных:

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

Вместо значения (1-P) будем вводить результат выполнения функции СЛЧИС, которая возвращает числа из диапазона (0;1), соответствующих допустимым значениям вероятности.

Для заполнения столбца «значение» используем формулу:

Ячейки в формуле «закреплены» с помощью “$” для получения корректных результатов при растягивании формулы:

Поскольку при выполнении любого действия на листе функция СЛЧИС будет выполнять пересчет, выделим полученные числа и вставим в те же ячейки как значения (с помощью инструмента «Специальная вставка»).

Для определения искомых значений выделим ячейки C5:C14 и запишем следующую формулу:

ОБНОВЛЕНИЕ: Данный пост все еще актуален, но есть новый: Как сверить выборку с распределением Вейбулла без Excel.

Предупреждение: это очень технический и практический пост.

Совпадают ли ваши лид-таймы с распределением Вейбулла?

Соответствует ли данный набор значений распределению Вейбулла?
Если соответствует, то чему равен параметр формы?

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

Шаг 1. Скопируйте ваш набор значений в колонку А электронной таблицы. Я предпочитаю оставлять первый ряд для заголовков столбцов, так чтобы значения начинались с ячейки А2.

Шаг 2. Отсортируйте значения в колонке А в порядке возрастания.

Шаг 3. Разделите интервал [0; 1] на равные интервалы, согласно количеству значений в вашей выборке. Внесите средние значения этих интервалов в колонку В. Если у вас N=100 значений, то формула Excel для этого будет =(2*ROW(B2)-3)/200 (200 == 2*N). Введите эту формулу в ячейку В2 и растяните на все ячейки колонки В.

Шаг 4. Заполните колонку С натуральными логарифмами значений колонки А. Введите формулу =LN(A2) в ячейку С2 и растяните ее на всю колонку.

Шаг 5. Заполните колонку D значениями, полученными из колонки В как описано дальше. Введите формулу =LN(-LN(1-B2)) в ячейку D2 и растяните ее до конца колонки. По сути, мы линеаризуем функцию накопительного распределения, чтобы линейная регрессия показала нам параметр формы.

Шаг 7. Посчитайте формулу =INTERCEPT(D2:101,C2:C101). (Ячейка G3)

Шаг 8. Посчитайте параметр масштаба для предыдущего шага =EXP(-G3/G2). (Ячейка G4)

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

В Excel нет встроенной Гамма-функции, но есть функция, возвращающая ее логарифм. Таким образом, мы можем рассчитать прогнозируемое среднее по формуле =G4*EXP(GAMMALN(1+1/G2)). Теперь можно сравнить прогнозируемое среднее и общее среднее, которое получим формулой =AVERAGE(A2:A101).

Есть и другой способ сделать линейную регрессию. Шаги выше сократят вертикальные расстояния (по направлению шкалы y) между лучшим совпадением кривой и точками выборки. Это возможно сделать и через сокращение горизонтальных расстояний. Второй метод последовательно переоценивает параметр формы, что нежелательно для практических применений аналитики лид-таймов. Это может приводить к неточностям, когда одно и то же значение встречается в выборке несколько раз, особенно на левой стороне распределения. По этим причинам, я рекомендую использовать метод, изначально описанный в оригинальном посте (линейная регрессия, сокращение вертикальных расстояний).

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