Расчет вероятности в excel

Как построить график с нормальным распределением в Excel

Так как я часто имею дело с большим количеством данных, у меня время от времени возникает необходимость генерировать массивы значений для проверки моделей в Excel. К примеру, если я хочу увидеть распределение веса продукта с определенным стандартным отклонением, потребуются некоторые усилия, чтобы привести результат работы формулы СЛУЧМЕЖДУ() в нормальный вид. Дело в том, что формула СЛУЧМЕЖДУ() выдает числа с единым распределением, т.е. любое число с одинаковой долей вероятности может оказаться как у нижней, так и у верхней границы запрашиваемого диапазона. Такое положение дел не соответствует действительности, так как вероятность возникновения продукта уменьшается по мере отклонения от целевого значения. Т.е. если я произвожу продукт весом 100 грамм, вероятность, что я произведу 97-ми или 103-граммовый продукт меньше, чем 100 грамм. Вес большей части произведенной продукции будет сосредоточен рядом с целевым значением. Такое распределение называется нормальным. Если построить график, где по оси Y отложить вес продукта, а по оси X – количество произведенного продукта, график будет иметь колоколообразный вид, где наивысшая точка будет соответствовать целевому значению.

Таким образом, чтобы привести массив, выданный формулой СЛУЧМЕЖДУ(), в нормальный вид, мне приходилось ручками исправлять пограничные значения на близкие к целевым. Такое положение дел меня, естественно, не устраивало, поэтому, покопавшись в интернете, открыл интересный способ создания массива данных с нормальным распределением. В сегодняшней статье описан способ генерации массива и построения графика с нормальным распределением.

Нормальное распределение в статистике

История закона насчитывает 300 лет. Первым открывателем стал Абрахам де Муавр, который придумал аппроксимацию биномиального распределения еще 1733 году. Через много лет Карл Фридрих Гаусс (1809 г.) и Пьер-Симон Лаплас (1812 г.) вывели математические функции.

Лаплас также обнаружил замечательную закономерность и сформулировал центральную предельную теорему (ЦПТ), согласно которой сумма большого количества малых и независимых величин имеет нормальное распределение.

Нормальный закон не является фиксированным уравнением зависимости одной переменной от другой. Фиксируется только характер этой зависимости. Конкретная форма распределения задается специальными параметрами. Например, у = аx + b – это уравнение прямой. Однако где конкретно она проходит и под каким наклоном, определяется параметрами а и b. Также и с нормальным распределением. Ясно, что это функция, которая описывает тенденцию высокой концентрации значений около центра, но ее точная форма задается специальными параметрами.

Кривая нормального распределения Гаусса имеет следующий вид.

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

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

Формула нормального распределения (плотности) следующая.

Формула состоит из двух математических констант:

π – число пи 3,142;

е – основание натурального логарифма 2,718;

двух изменяемых параметров, которые задают форму конкретной кривой:

m – математическое ожидание (в различных источниках могут использоваться другие обозначения, например, µ или a);

σ2 – дисперсия;

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

Конкретная форма нормального распределения зависит от 2-х параметров: математического ожидания (m) и дисперсии (σ2). Кратко обозначается N(m, σ2) или N(m, σ). Параметр m (матожидание) определяет центр распределения, которому соответствует максимальная высота графика. Дисперсия σ2 характеризует размах вариации, то есть «размазанность» данных.

Параметр математического ожидания смещает центр распределения вправо или влево, не влияя на саму форму кривой плотности.

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

Плотность распределения не имеет прямого практического применения. Для расчета вероятностей нужно проинтегрировать функцию плотности.

Вероятность того, что случайная величина окажется меньше некоторого значения x, определяется функцией нормального распределения:

P(a ≤ X < b) = Ф(b) – Ф(a)

Лабораторная работа № 16 Распределение Вейбулла при расчёте показателей надёжности

Далее
&nbsp
&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp

Распределение Вейбулла имеет два параметра: δ – параметр формы (не путать
со среднеквадратическим отклонением) и λ –параметр масштаба (не путать с интенсивностью отказов).

В случае распределения Вейбулла интенсивность отказов
λ(t) = λδtδ-1

Три участка лямбда-характеристики на рис. 15.1 соответствуют распределениям
Вейбулла с различными параметрами λ и δ. Так, в период
приработки δ δ = 1 (при этом
распределение Вейбулла соответствует экспоненциальному распределению), в
области износа δ > 1 (при δ = 2 распределение
Вейбулла соответствует распределению Рэлея).

Пример 16.1. По экспериментальным данным найдено, что лямбда-характеристика
выпускаемых предприятием изделий подобна показанной на рис. 15.1, и участки
кривой соответствуют распределению Вейбулла с параметрами, указанными в
табл. 16.1.
Рассчитать интенсивности отказов на участке от 50 до 5000 ч с интервалом 50 ч,
построить лямбда-характеристику.

Таблица 16.1.

Параметр	Приработка	Рабочая область	Область износа
δ	0,45	1	1*10-11
λ	0,007	0,0002	2,9

Фрагмент расчёта для примера 16.1 показан на рис. 16.1.

Рис. 16.1. Фрагмент расчёта для примера 16.1.

Вводим значения параметра масштаба и соответствующие им значения параметра
формы, а также столбец значений времени. Затем рассчитываем столбцы
интенсивностей отказов на участке от 50 до 5000 ч с интервалом 50 ч при
каждой из трёх пар параметров масштаба и формы. Строим графики всех трёх
кривых (рис. 16.2).

Рис.16.2. Графики распределений Вейбулла.

Область приработки на лямбда-характеристике будет выше и левее точки 1, рабочая
область – между точками 1 и 2, область износа – выше и правее точки 2.

Как видно из расчётных данных, рабочая область начинается примерно с 200 ч,
когда интенсивность отказов в ней становится больше интенсивности отказов в
области приработки. Заканчивается рабочая область примерно с 4000 ч, когда
интенсивность отказов в ней становится меньше интенсивности отказов в области
износа. Таким образом, для получения нужных значений интенсивности отказов в
ячейку F8 столбца λ(t) вводим максимальное значение из
диапазона C8:E8, используя функцию МАКС. Затем ячейку F8 копируем на весь
столбец λ(t). По этим значениям строим лямбда-характеристику.

Рис.16.3. Лямбда-характеристика.

Задание.

1. Выполнить пример 16.1.

&nbsp &nbsp &nbsp &nbsp Далее
&nbsp &nbsp

В.В.Заляжных
Ссылка на сайт обязательна

Комментарии на [email protected]

Примеры дискретных распределений

Константа. Да, это тоже случайная величина в теории вероятностей. Случайная величина X называется константой, если она принимает лишь одно значение c ∈ R с вероятностью 1

Распределение Бернулли, p ∈

Самое простое распределение. То самое подбрасывание монетки, где у нас есть всего два исхода (то, что монетка может упасть ребром, мы не рассматриваем). Обозначается Ber(p). Имеет параметр p ∈ , где p — вероятность успеха.

Случайная величина X имеет распределение Бернулли с параметром p ∈ , если X принимает значение 1 с вероятностью p и значение с вероятностью 1 − p. Параметр p называется вероятностью успеха

Таблица распределения:

Пишется как: X ∼ Ber(0.5) — это как раз про монетку, X ∼ Ber(1).

Примеры распределения Бернулли в реальной жизни: пол сотрудника, победа спортивной команды, бумага в общественном туалете (либо она есть, либо её нет).

Равномерное распределение на конечном множестве

Случайная величина X имеет равномерное распределение на множестве {a1, a2, . . . , an}, если X принимает каждое значение a_i с вероятностью 1/n. Т.е. конечное количество исходов, где каждый из них принимается с одинаковой вероятностью. Таблица распределения имеет следующий вид:

Примеры равномерного распределения в реальной жизни: игральная кость, рулетка

Биномиальное распределение Bin(n, p), n ∈ N, p ∈

Здесь у нас уже два параметра. Случайная величина Y имеет биномиальное распределение с параметрами n ∈ N и p ∈ , если Y = X1 + X2 + . . . + Xn, где X1, X2, . . . , Xn ∼ Ber(p) независимые, т.е. сумма независимых случайных величин с равностью успеха p (с одной и той же вероятностью успеха). Фактически, Y — это количество «успехов» в n независимых испытания Бернулли, от нуля до n

Распределение Пуассона Pois(λ), λ > 0

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

Случайная величина X имеет распределение Пуассона с параметром λ > 0, если X принимает значения k = 0, 1, 2, . . . с вероятностями:

k! — факториал числа k (произведение всех натуральных чисел от 1 до k включительно)

Является предельным распределением для Bin(n, p) при p → 0, np → λ. Параметр p зависит от n. При n стремящемся к бесконечности (n -> ∞), р будет стремиться к нулю (p -> 0), но при этом np ->  λ. Например, у нас есть игра «Морской бой»

Где n- количество ходов (снарядов), а m — количество блоков. Вероятность попасть в один блок (p) будет равна 1/m. Получается биноминальное распределение с такими параметрами можно попытаться аппроксимировать с помощью распределения Пуассона

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

Небольшая задачка

На пустынном шоссе вероятность появления автомобиля за30-минутный период составляет 0.95. Какова вероятность его появленияза 10 минут? 

Решить её в лоб не получится, т.к.0.95 нельзя просто поделить на три — вероятность встретить автомобиль даже в случае 30 минут не равна единице, но при этом, мы можем встретить не один автомобиль, а два. Или даже три — есть множество комбинаций вероятностей.

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

Что сюда можно прикрутить? Условия задачи отлично подходят для распределения Бернулли. Мы можем разделить отрезок 30 минут на три равных, по 10 минут. У каждого отрезка есть случайная величина. Каждая X_i — это бернуллиевская случайная величина с вероятностью наступления успеха p

Вспоминаем таблицу распределения:

Задача сводится к том, что нам нужно найти вероятность успеха р. Получается, что нашу вероятность можно записать так:

P (X_i =1 хотя бы для одного i = 1,2,3) = 0.95

Вероятность того, что мы встретим хотя бы один автомобиль равна единице. НО! Если в событие встроено условие «хотя бы один», будет правильным перейти к дополнительному событию. Здесь мы перемножаем вероятность для x_i = 0, которая равняется 1-p.

1 − P(все X_i = 0 для всех i=1,2,3) или же 1- (1-p)3

Таким образом мы получаем:

1- (1-p)3 = 0.95

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

p = 1 − 3√ 1 − 0.95

Кубический корень из 0,05 ≈ 0.37, соответственно, 1 — 0,37 = 0,67

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

Используем обратную функцию распределения (или p — quantile , см. статью про Квантили ), которая для распределения Вейбулла может быть выражена в явном виде с использованием элементарных функций:

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

Функция СЛЧИС() генерирует непрерывное равномерное распределение от 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 .

• Внедренные решения 1с опубликовать

    

• Как установить драйвера на ноутбук самсунг r540

    

• Как соединить два видео в одно в after effects

    

• 1с регистр сведений запрет удаления

    

• Как изменить нумерацию страниц в ворде в колонтитуле в рамке

NEGBINOM.DIST: отрицательное биноминальное распределение

Функция NEGBINOM.DIST находит вероятность того, что указанное число сбоев произойдет до указанного числа успехов, на основе константы вероятности успеха. Функция использует синтаксис

= NEGBINOM.DIST (number_f, число_успехи, вероятность_успех)

где number_f указанное количество сбоев, число_успехов указанное количество успехов, вероятность_успеха это вероятность успеха, а кумулятивным является переключатель, который вы установили на 0 или FALSE, если вы хотите кумулятивное распределение, и на 1 или TRUE, если вы хотите распределение вероятности.

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

= NEGBINOM.DIST (10,2, .05,0)

который возвращает значение 0,016465266, указывающее, что существует менее 2-процентной вероятности того, что вы десять раз потерпите неудачу, прежде чем попасть в гашер.

Как сделать экспоненциальный график в excel

В этой статье описаны синтаксис формулы и использование функции EXP в Microsoft Excel.

Описание

Возвращает число e, возведенное в указанную степень. Число e равно 2,71828182845904 и является основанием натурального логарифма.

Синтаксис

Аргументы функции EXP описаны ниже.

Число — обязательный аргумент. Показатель степени, в которую возводится основание e.

Замечания

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

Функция EXP является обратной по отношению к функции LN, т. е. к натуральному логарифму числа.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

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

=ВЕЙБУЛЛ(x;альфа;бета;интегральная)

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

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

Примечания:

1. Первые три аргумента функции должны принимать числовые значения или данные, которые могут быть преобразованы к числам, иначе результатом выполнения функции ВЕЙБУЛЛ будет код ошибки #ЗНАЧ!
2. Если аргумент x принимает значение 0, функция вернет 0 (нуль) при любых значениях остальных аргументов. Если первый аргумент (x) указан числом из диапазона отрицательных значений, будет возвращен код ошибки #ЧИСЛО! Аналогичная ошибка возникает, если аргументы, характеризующие α-параметр и β-параметр соответственно не взяты из диапазона положительных значений (0 также исключен).
3. Последний аргумент может указан в виде числа, соответствующего логическим значениям: 1 – ИСТИНА, 0 – ЛОЖЬ.

Случайные величины

Случайная величина — это функция, которая каждому возможному исходу в эксперименте ставит в соответствие действительное число. Можно понимать случайную величину как «кодирование» исходов эксперимента. Иными словами, с помощью случайной величины мы моделируем случайный эксперимент, ведь логично, что в теории вероятностей мы изучаем вероятность наступления какого-либо события

Нас будет интересовать множество значений случайной величины и с какими вероятностями она принимает возможные значения, т.е. мы будем говорить о вероятности событий, которые связаны со случайными величинами. Например, если X — случайная величина (будем обозначать большими латинскими буквами), то нас могут интересовать вероятности событий.

Как записывается событие

• {X = a} для некоторого a ∈ R — (в фигурных скобках записано, что случайная величина принимает какое-то значение), a — любое произвольное действительное число, ∈ — принадлежит, R — множество действительных чисел (дробные, иррациональные в т.ч.)
• {X < a} для некоторого a ∈ R
• {a ≤ X < b} для некоторых a, b ∈ R, a < b (из промежутка для каких-то чисел a и b)

В самом общем виде любое событие можно записать так: {X ∈ A} для некоторого подмножества A ⊂ R

Классификация случайных величин

Можно (нужно!) классифицировать случайные величины по мощности их множества значений

Дискретные случайные величины имеют конечное или счетное
множество значений. Примеры: {1, . . . , n}, N, Z. Это в случае, если бесконечность счетная, т.е. мы можем пересчитать элементы этой бесконечности. Это счетное множество. Например, N — все натуральные числа (от 1 до бесконечности). Z — все целые числа и так далее

Непрерывные случайные величины имеют несчетное множество
значений. Примеры: , R. Это бесконечность на стероидах, т.е. еще «большая» бесконечность, чем обычная бесконечность, т.к. мы не можем пересчитать её элементы. Например, любой отрезок, скажем от 0 до 1, т.к. в нём присутствуют иррациональные числа (дроби, корни), которых очень много. И процесса пересчёта не существует — их будет бесконечно много

Комбинаторика и вероятность

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

ЧИСЛКОМБ / COMBIN	Возвращает количество сочетаний без повторений.
ФАКТР / FACT	Вычисляет факториал числа.
СЛЧИС / RAND	Выдает случайное число в интервале от 0 до 1 (равномерно распределенное).
СЛУЧМЕЖДУ / RANDBETVEEN	Выдает случайное число в заданном интервале.
БИНОМРАСП / BINOMDIST	Вычисляет отдельное значение биномиального распределения.
ГИПЕРГЕОМЕТ / HYRGEOMDIST	Определяет гипергеометрическое распределение.
НОРМРАСП / NORMDIST	Вычисляет значение нормальной функции распределения.
НОРМОБР / NORMINV	Выдает обратное нормальное распределение.
НОРМСТРАСП / NORMSDIST	Выдает стандартное нормальное интегральное распределение.
НОРМСТОБР / NORMSINV	Выдает обратное значение стандартного нормального распределения.
ПЕРЕСТ / PERMUT	Находит количество размещений без повторений
ВЕРОЯТНОСТЬ / PROB	Определяет вероятность того, что значение из диапазона находится внутри заданных пределов.

Подробнее: Формулы комбинаторики в Excel.

Подробно решим ваши задачи по теории вероятностей