Экспоненциальное распределение в excel

Обновлено: 07.07.2024

Экспоненциальный формат отображает число в экспоненциальном виде, заменяя часть числа на E+n, в котором E (показатель экспоненты) умножает предыдущее число на 10 до n-йточки. Например, в научном формате с 12345678901 12345678901 отображается 1,23E+10, т. е. от 1,23 до 10-й энергии.

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

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

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

На вкладке Главная нажмите маленькую кнопку Дополнительные рядом с кнопкой Число.

В списке Категория выберите научных.

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

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

Кроме того, помните о том, что:

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

Формат не влияет на фактическое значение в ячейке, которое Excel использует при вычислениях. Фактическое значение можно увидеть в строка формул.

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

Чтобы сбросить числовом формате, в поле Числовом формате выберите общий (вкладка Главная, группа Число). Ячейки, отформатированные с использованием формата Общий, не имеют определенного числовых формата. Однако в формате Общий для больших чисел (12 и более цифр) используется экспоненциальное означение. Чтобы удалить экспоненциальное нотацию из больших чисел, можно применить другой числовом формате, например Число.

Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью экспоненциальной функции.

Метод наименьших квадратов (англ. Ordinary Least Squares , OLS ) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным. Основная статья про МНК - МНК: Метод Наименьших Квадратов в MS EXCEL .

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

y=a*m x =a*(e ln(m) ) x = a*e x*ln(m) =a*e bx , где b= ln(m))

В свою очередь экспоненциальную зависимость y=a*EXP(b*x) при a>0 можно свести к случаю линейной зависимости с помощью замены переменных (см. файл примера ).

После замены переменных Y=ln(y) и A=ln(a) вычисления полностью аналогичны линейному случаю Y=b*x+A. Для нахождения коэффициента a необходимо выполнить обратное преобразование a= EXP(A) .

Примечание : Построить линию тренда по методу наименьших квадратов можно также с помощью инструмента диаграммы Линия тренда ( Экспоненциальная линия тренда ). Поставив в диалоговом окне галочку в поле «показывать уравнение на диаграмме» можно убедиться, что найденные выше параметры совпадают со значениями на диаграмме. Подробнее о диаграммах см. статью Основы построения диаграмм в MS EXCEL .

Следствием замены Y=ln(y) и A=ln(a) являются дополнительные ограничения: a>0 и y>0. При уменьшении х (в сторону больш и х по модулю отрицательных чисел) соответствующее значение y асимптотически стремится к 0. Именно такую линию тренда и строит инструмент диаграммы Линия тренда. Если среди значений y есть отрицательные, то с помощью инструмента Линия тренда экспоненциальную линию тренда построить не удастся.

Чтобы обойти это ограничение используем другое уравнение экспоненциальной зависимости y=a*EXP(b*x)+с, где по прежнему a>0, т.е. при росте х значения y также будут увеличиваться. В качестве с можно взять некую заранее известную нижнюю границу для y , ниже которой у не может опускаться, т.е. у>с. Далее заменой переменных Y=ln(y-c) и A=ln(a) опять сведем задачу к линейному случаю (см. файл примера лист Экспонента2 ).

Если при росте х значения y уменьшаются по экспоненциальной кривой, т.е. a файл примера лист Экспонента3 ).

Функция РОСТ()

Еще одним способом построить линию экспоненциального тренда является использование функции РОСТ() , английское название GROWTH.

Синтаксис функции следующий:

РОСТ( известные_значения_y; [известные_значения_x]; [новые_значения_x]; [конст] )

Для работы функции нужно просто ввести ссылки на массив значений переменной Y (аргумент известные_значения_y ) и на массив значений переменной Х (аргумент известные_значения_x ). Функция рассчитает прогнозные значения Y для Х, указанных в аргументе новые_значения_x . Если требуется, чтобы экспоненциальная кривая y=a*EXP(b*x) имела a=1, т.е. проходила бы через точку (0;1), то необязательный аргумент конст должен быть установлен равным ЛОЖЬ (или 0).

Если среди значений y есть отрицательные, то с помощью функции РОСТ() аппроксимирующую кривую построить не удастся.

Безусловно, использование функции РОСТ() часто удобно, т.к. не требуется делать замену переменных и сводить задачу к линейному случаю.

Наконец, покажем как с помощью функции РОСТ() вычислить коэффициенты уравнения y= a *EXP( b *x).

Примечание : В MS EXCEL имеется специальная функция ЛГРФПРИБЛ() , которая позволяет вычислить коэффициенты уравнения y=a*EXP(b*x). Об этой функции см. ниже.

Чтобы вычислить коэффициент a (значение Y в точке Х=0) используйте формулу =РОСТ(C26:C45;B26:B45;0) . В диапазонах C26:C45 и B26:B45 должны находиться массивы значений переменной Y и X соответственно.

Чтобы вычислить коэффициент b используйте формулу:

= LN(РОСТ(C26:C45;B26:B45;МИН(B26:B45))/ РОСТ(C26:C45;B26:B45;МАКС(B26:B45)))/ (МИН(B26:B45)-МАКС(B26:B45))

Функция ЛГРФПРИБЛ()

Функция ЛГРФПРИБЛ() на основе имеющихся значений переменных Х и Y подбирает методом наименьших квадратов коэффициенты а и m уравнения y= a * m ^x.

Используя свойство степеней a mn =(a m ) n приведем уравнение экспоненциального тренда y= a *EXP( b *x)= a *e b *x = a *(e b ) x к виду y= a * m ^x, сделав замену переменной m= e b =EXP( b ).

Чтобы вычислить коэффициенты уравнения y= a *EXP( b *x) используйте следующие формулы:

= LN(ЛГРФПРИБЛ(C26:C45;B26:B45)) - коэффициент b

= ИНДЕКС(ЛГРФПРИБЛ(C26:C45;B26:B45);;2) - коэффициент a

Примечание : Функция ЛГРФПРИБЛ() , английское название LOGEST, является формулой массива, возвращающей несколько значений . Поэтому, например, для вывода коэффициентов уравнения необходимо выделить 2 ячейки в одной строке, в Строке формул ввести = ЛГРФПРИБЛ(C26:C45;B26:B45) , затем для ввода формулы вместо обычного ENTER нажать CTRL + SHIFT + ENTER .

Функция ЛГРФПРИБЛ() имеет линейный аналог – функцию ЛИНЕЙН() , которая рассмотрена в статье про простую линейную регрессию. Если 4-й аргумент этой функции ( статистика ) установлен ИСТИНА, то ЛГРФПРИБЛ() возвращает регрессионную статистику: стандартные ошибки для оценок коэффициентов регрессии, коэффициент детерминации, суммы квадратов: SSR , SSE и др.

Примечание : Особой нужды в функции ЛГРФПРИБЛ() нет, т.к. с помощью логарифмирования и замены переменной показательную функцию y= a * m ^x можно свести к линейной ln(y)=ln(a)+x*ln(m)=> Y=A+bx. То же справедливо и для экспоненциальной функции y= a *EXP( b *x).

Рассмотрим Экспоненциальное распределение, вычислим его математическое ожидание, дисперсию, медиану. С помощью функции 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 .

Экспоненциальное распределение имеет обозначение Exp ( λ ).

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

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

Наш первый вопрос был: почему λ * e^(−λt) — это плотность вероятности времени до следующего события?

Определение экспоненциального распределения — это распределение вероятности времени *между* событиями в процессе Пуассона .

Смотрите: в период ожидания не происходит ни одного события. Другими словами, Пуассон (X=0).

Пуассон (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 в EXCEL была функция ВЕЙБУЛЛ() , которая позволяет вычислить интегральную функцию распределения и плотность вероятности . ВЕЙБУЛЛ() оставлена в MS EXCEL 2010 для совместимости.

Генерация случайных чисел и оценка параметров

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

Теперь имея массив случайных чисел, сгенерированных с заданными параметрами распределения альфа и бета (пусть их будет 200), оценим параметры распределения.

Оценку параметров альфа и бета можно сделать с помощью линейной регрессии. Для этого необходимо привести функцию распределения Вейбулла к виду обычной прямой, задаваемой уравнением Y=aX+b. Для этого сделаем следующие преобразования:

Сравнивая выражение с уравнением прямой Y=ax+b получим, что:

Y соответствует левая часть выражения,
X – соответствует ln(x),
параметр распределения бета соответствует коэффициенту a , отвечающего за наклон прямой к оси абсцисс.
выражение –бета*ln(альфа) соответствует коэффициенту b (ордината точки пересечения с осью Oy).

Заметим, что -LN(1-Ui) – это обратная функция распределения с параметрами альфа=1 и бета=1. Второй логарифм нам потребовался, т.к. по оси абсцисс отложены не сами x, а ln(x).

Примечание : Т.к. форма распределения Вейбулла существенно зависит от его параметров, то вместо альфа=1 и бета=1 для обратной функции лучше использовать точечные оценки этих параметров , полученные на основании выборки . О том как вычислить оценку параметров альфа и бета см. ниже.

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

С помощью функции НАКЛОН() вычислим наклон получившейся кривой (коэффициент прямой а, англ. slope ), который служит оценкой параметра бета .

Функция ОТРЕЗОК() вернет ординату точки пересечения с Оу (коэффициент прямой b ). Выражение =EXP(-b/бета) служит оценкой параметра альфа .

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

Процедура построения модельного распределения следующая:

В итоге получим:

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

Примечание : При построении диаграммы использована гистограмма и график с маркерами . Подробнее о построении диаграмм см. Основы построения диаграмм в MS EXCEL .

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

Как видно из диаграммы выше, совпадение также достаточно хорошее.

СОВЕТ : Т.к. генерирование случайных чисел происходит с помощью функции СЛЧИС() , то нажимая клавишу F9 , можно каждый раз получать новую выборку и, соответственно, новую оценку параметров.

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

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

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

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

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

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

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

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

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

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

Правила использования функции ВЕЙБУЛЛ в Excel

Функция имеет следующий синтаксис:

Описание аргументов (все являются обязательными):

x – принимает числовое значение некоторой величины с распределением Вейбулла, для которой необходимо определить функцию;
альфа – принимает числовое значение, характеризующее α-параметр распределения;
бета – принимает числовое значение, которое характеризует β-параметр распределения;
интегральная – принимает данные логического типа, определяющие форму вычисляемой функции: ИСТИНА – будет возвращена интегральная функция, ЛОЖЬ – будет возвращена функция плотности распределения Вейбулла.

Что нужно иметь в виду об экспоненциальной функции (EXP) в Excel

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

Мы также можем использовать функцию POWER вместо экспоненциальной функции в Excel, но единственным отличием является точность измерения. При использовании функции POWER ее можно указать от e до 2.71 или до десятичных разрядов 3-4, но функция EXP в Excel обычно принимает значение от e до десятичных разрядов 9.

Таким образом, если вы вычисляете экспоненту в серии Excel для работы с нелинейными экспоненциальными функциями, экспоненциальное значение которых у нас есть, лучше использовать функцию EXP в Excel, чем функцию POWER.

В случае сложных показателей, например = EXP (- (2.2 / 9.58) ^ 2), необходимо соблюдать осторожность с помощью скобок, если скобки перепутаны, выходные данные могут отличаться от фактических выходных данных, поэтому это должно быть = EXP (- ((2.2 / 9.58) ^ 2)).

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