Распределение коши в excel
График нормального распределения в Excel используется для представления явления нормального распределения заданных данных, этот график создается после вычисления среднего и стандартного отклонения для данных, а затем вычисления нормального отклонения по ним, из версий Excel 2013 было легко Постройте график нормального распределения, поскольку он имеет встроенную функцию для расчета нормального распределения и стандартного отклонения, график очень похож на колоколообразную кривую.
График нормального распределения Excel (кривая колокола)
График нормального распределения — это непрерывная функция вероятности. Все мы знаем, что такое вероятность; это метод расчета возникновения явления или переменной. Распределение вероятностей — это функция, которая используется для вычисления появления переменной. Существует два типа вероятностных распределений: дискретное и непрерывное.
Основная идея того, что такое нормальное распределение, объясняется в обзоре выше. По определению, нормальное распределение означает, насколько равномерно распределяются данные. Непрерывное распределение вероятностей используется для расчета появления любого явления в реальном времени. В математике уравнение распределения вероятностей выглядит следующим образом:
Кажется таким сложным, правда? Но Excel упростил нам вычисление нормального распределения, поскольку он имеет встроенную функцию в excel нормального распределения. В ячейках любого типа следующая формула,
Для расчета нормального распределения в Excel используются три основных фактора:
- ИКС: X — указанное значение, для которого мы хотим рассчитать нормальное распределение.
- Значить: Среднее — это среднее значение данных.
- Standard_Dev: Стандартное отклонение — это функция для поиска отклонения данных. (Это должно быть положительное число)
График, который мы строим на основе этих данных, называется графиком нормального распределения. Это также известно как кривая колокола. Что такое колоколообразная кривая? Колоколообразная кривая — это обычное распределение для переменной, т. Е. Насколько равномерно распределяются данные. Есть некоторые. График, который мы строим, может быть линейным или точечной диаграммой со сглаженными линиями.
Как составить нормальный график распределения в Excel?
Ниже приведены примеры графиков нормального распределения в Excel (кривая Белла)
Вы можете скачать этот шаблон Excel для графика нормального распределения здесь — Шаблон Excel для графика нормального распределения
Сначала мы возьмем случайные данные. Давайте возьмем значения от -3 до 3 в столбце A. Теперь нам нужно вычислить среднее значение и стандартное отклонение в Excel перед вычислением нормального распределения, а затем мы можем построить график нормального распределения в Excel.
Итак, взгляните на данные ниже.
- Сначала вычислите среднее значение данных, т. Е. Среднее значение данных; в ячейке D1 запишите следующую формулу.
Нажмите Enter, чтобы получить результат.
- Теперь мы рассчитаем стандартное отклонение для заданных данных, поэтому в ячейке D2 напишите следующую формулу.
Нажмите Enter, чтобы получить результат.
- Теперь в ячейке B2 мы вычислим нормальное распределение по встроенной формуле для Excel. Запишите следующую формулу в ячейку B2.
- Формула возвращает результат, как показано ниже:
- В ячейке B2 у нас есть нормальное распределение для данных, которые мы выбрали. Чтобы построить график нормального распределения, перейдите на вкладку «Вставка» и в разделе «Диаграммы» выберите диаграмму рассеяния со сглаженными линиями и маркерами.
- Когда мы вставляем диаграмму, мы видим, что наша кривая колокола или график нормального распределения создан.
Приведенная выше диаграмма представляет собой график нормального распределения для взятых нами случайных данных. Теперь нам нужно сначала кое-что понять, прежде чем мы перейдем к реальному примеру данных. Стандартное отклонение S означает выборку стандартного отклонения, потому что в реальном анализе данных у нас есть огромный кусок данных, и мы выбираем из него образец данных для анализа.
Переходим к примеру из жизни. Чем больше данных у нас есть, тем более гладкую линию мы получим для нашей колоколообразной кривой или графика нормального распределения Excel. Чтобы доказать это, я возьму пример сотрудников и их стимулы, достигнутые в текущем месяце. Возьмем пример для 25 сотрудников.
Обратите внимание на данные ниже.
- Теперь первый шаг — вычислить среднее значение, которое является средним для данных в Excel. Введите следующую формулу для среднего.
Среднее значение данных составляет 13000.
- Теперь давайте найдем стандартное отклонение для данных. Введите следующую формулу.
Стандартное отклонение данных составляет 7359.801.
- Поскольку мы рассчитали как среднее значение, так и стандартное отклонение, теперь мы можем продолжить и вычислить нормальное распределение для данных. Введите следующую формулу.
- Функция нормального распределения возвращает результат, как показано ниже:
- Теперь, когда мы рассчитали наше нормальное распределение, мы можем продолжить и построить кривую колокола графа нормального распределения данных. На вкладке «Вставка» под диаграммами в разделе щелкните точечную диаграмму со сглаженными линиями и маркерами.
- Когда мы нажимаем ОК, мы видим, что создается следующая диаграмма:
В качестве выборки данных мы взяли 25 сотрудников; мы можем видеть, что на горизонтальной оси кривая заканчивается на 25.
Приведенная выше диаграмма представляет собой график нормального распределения или кривую колокола для данных для сотрудников и стимулов, которых они достигли за текущий месяц.
Нормальное распределение Excel — это в основном процесс анализа данных, для которого требуется несколько функций, таких как среднее и стандартное отклонение данных. График полученного нормального распределения известен как график нормального распределения или колоколообразная кривая.
Распределения вероятностей в MS EXCEL. Нормальное распределение, Биномиальное распределение, распределение Стьюдента, Вейбулла, Фишера и др. Оценка параметров распределения, вычисление математического ожидания и дисперсии. Функции MS EXCEL: НОРМ.РАСП(), СТЬЮДЕНТ.РАСП(), ХИ2.РАСП() и др. Рассмотрены ВСЕ распределения, имеющиеся в MS EXCEL 2010.
Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL
Задана произвольная функция распределения дискретной случайной величины. Сгенерируем случайное число из этой генеральной совокупности. Также рассмотрим функцию ВЕРОЯТНОСТЬ() .
Нормальное распределение. Непрерывные распределения в MS EXCEL
Рассмотрим Нормальное распределение. С помощью функции MS EXCEL НОРМ.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел, распределенных по нормальному закону, произведем оценку параметров распределения, среднего значения …
Логнормальное распределение. Непрерывные распределения в MS EXCEL
Рассмотрим Логнормальное распределение. С помощью функции MS EXCEL ЛОГНОРМ .РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел, распределенных по логнормальному закону, произведем оценку параметров распределения, среднего …
Равномерное дискретное распределение в MS EXCEL
Рассмотрим Равномерное дискретное распределение, построим график функции распределения, вычислим среднее значение и дисперсию. Сгенерируем случайные значения (выборку) с помощью функции MS EXCEL СЛУЧМЕЖДУ() . На основании выборки оценим среднее и …
Взаимосвязь некоторых распределений в MS EXCEL
Рассмотрим взаимосвязь Биномиального распределения, распределения Пуассона, Нормального распределения и Гипергеометрического распределения. Определим условия, когда возможна аппроксимация одного распределения другим, приведем примеры и графики.
Распределение Пуассона. Дискретные распределения в MS EXCEL
Рассмотрим распределение Пуассона, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ПУАССОН.РАСП() построим графики функции распределения и плотности вероятности. Произведем оценку параметра распределения, его математического ожидания и …
Равномерное непрерывное распределение в MS EXCEL
Рассмотрим равномерное непрерывное распределение. Вычислим математическое ожидание и дисперсию. Сгенерируем случайные значения с помощью функции MS EXCEL СЛЧИС() и надстройки Пакет Анализа, произведем оценку среднего значения и стандартного отклонения.
Экспоненциальное распределение. Непрерывные распределения в MS EXCEL
Рассмотрим Экспоненциальное распределение, вычислим его математическое ожидание, дисперсию, медиану. С помощью функции MS EXCEL ЭКСП.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметра …
Гамма распределение. Непрерывные распределения в MS EXCEL
Рассмотрим Гамма распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ГАММА.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров …
Бета распределение. Непрерывные распределения в MS EXCEL
Рассмотрим Бета-распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL БЕТА.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров распределения.
Распределение ХИ-квадрат. Распределения математической статистики в MS EXCEL
Рассмотрим Распределение ХИ-квадрат. С помощью функции MS EXCEL ХИ2.РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.
Распределение Фишера (F-распределение). Распределения математической статистики в MS EXCEL
Рассмотрим распределение Фишера (F-распределение). С помощью функции MS EXCEL F .РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.
Функция распределения и плотность вероятности в MS EXCEL
Гипергеометрическое распределение. Дискретные распределения в MS EXCEL
Рассмотрим Гипергеометрическое распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL ГИПЕРГЕОМ.РАСП() построим графики функции распределения и плотности вероятности. Приведем пример аппроксимации гипергеометрического распределения биномиальным.
Биномиальное распределение. Дискретные распределения в MS EXCEL
Рассмотрим Биномиальное распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL БИНОМ.РАСП() построим графики функции распределения и плотности вероятности. Произведем оценку параметра распределения p, математического ожидания распределения …
Отрицательное Биномиальное распределение. Дискретные распределения в MS EXCEL
Рассмотрим Отрицательное Биномиальное распределение, вычислим его математическое ожидание и дисперсию. С помощью функции MS EXCEL ОТРБИНОМ.РАСП() построим графики функции распределения и плотности вероятности.
Геометрическое распределение. Дискретные распределения в MS EXCEL
Рассмотрим Геометрическое распределение, вычислим его математическое ожидание и дисперсию. С помощью функции MS EXCEL ОТРБИНОМ.РАСП() построим графики функции распределения и плотности вероятности.
Распределение Вейбулла. Непрерывные распределения в MS EXCEL
Рассмотрим распределение Вейбулла, вычислим его математическое ожидание, дисперсию, медиану. С помощью функции MS EXCEL ВЕЙБУЛЛ.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел и произведем оценку параметров …
Распределение Стьюдента (t-распределение). Распределения математической статистики в MS EXCEL
Рассмотрим Распределение Стьюдента (t-распределение). С помощью функции MS EXCEL СТЬЮДЕНТ.РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.
Случайная выборка из генеральной совокупности в MS EXCEL
Инструмент Пакета анализа MS EXCEL «Выборка» извлекает случайную выборку из входного диапазона, рассматривая его как генеральную совокупность. Также случайную выборку можно извлечь с помощью формул.
Введем базовые понятия статистики, без которых невозможно объяснить более сложные понятия.
Генеральная совокупность и случайная величина
Пусть у нас имеется генеральная совокупность (population) из N объектов, каждому из которых присуще определенное значение некоторой числовой характеристики Х.
Примером генеральной совокупности (ГС) может служить совокупность весов однотипных деталей, которые производятся станком.
Поскольку в математической статистике, любой вывод делается только на основании характеристики Х (абстрагируясь от самих объектов), то с этой точки зрения генеральная совокупность представляет собой N чисел, среди которых, в общем случае, могут быть и одинаковые.
В нашем примере, ГС - это просто числовой массив значений весов деталей. Х – вес одной из деталей.
Если из заданной ГС мы выбираем случайным образом один объект, имеющей характеристику Х, то величина Х является случайной величиной . По определению, любая случайная величина имеет функцию распределения , которая обычно обозначается F(x).
Функция распределения
Функцией распределения вероятностей случайной величины Х называют функцию F(x), значение которой в точке х равно вероятности события X файл примера ):
В справке MS EXCEL Функцию распределения называют Интегральной функцией распределения ( Cumulative Distribution Function , CDF ).
Приведем некоторые свойства Функции распределения:
- Функция распределения F(x) изменяется в интервале [0;1], т.к. ее значения равны вероятностям соответствующих событий (по определению вероятность может быть в пределах от 0 до 1);
- Функция распределения – неубывающая функция;
- Вероятность того, что случайная величина приняла значение из некоторого диапазона [x1;x2): P(x 1 =0. Следовательно, плотность вероятности для непрерывной величины может быть, в отличие от Функции распределения, больше 1. Например, для непрерывной равномерной величины , распределенной на интервале [0; 0,5] плотность вероятности равна 1/(0,5-0)=2. А для экспоненциального распределения с параметром лямбда =5, значение плотности вероятности в точке х=0,05 равно 3,894. Но, при этом можно убедиться, что вероятность на любом интервале будет, как обычно, от 0 до 1.
Напомним, что плотность распределения является производной от функции распределения , т.е. «скоростью» ее изменения: p(x)=(F(x2)-F(x1))/Dx при Dx стремящемся к 0, где Dx=x2-x1. Т.е. тот факт, что плотность распределения >1 означает лишь, что функция распределения растет достаточно быстро (это очевидно на примере экспоненциального распределения ).
Примечание : Площадь, целиком заключенная под всей кривой, изображающей плотность распределения , равна 1.
Примечание : Напомним, что функцию распределения F(x) называют в функциях MS EXCEL интегральной функцией распределения . Этот термин присутствует в параметрах функций, например в НОРМ.РАСП (x; среднее; стандартное_откл; интегральная ). Если функция MS EXCEL должна вернуть Функцию распределения, то параметр интегральная , д.б. установлен ИСТИНА. Если требуется вычислить плотность вероятности , то параметр интегральная , д.б. ЛОЖЬ.
Примечание : Для дискретного распределения вероятность случайной величине принять некое значение также часто называется плотностью вероятности (англ. probability mass function (pmf)). В справке MS EXCEL плотность вероятности может называть даже "функция вероятностной меры" (см. функцию БИНОМ.РАСП() ).
Вычисление плотности вероятности с использованием функций MS EXCEL
Понятно, что чтобы вычислить плотность вероятности для определенного значения случайной величины, нужно знать ее распределение.
Найдем плотность вероятности для стандартного нормального распределения N(0;1) при x=2. Для этого необходимо записать формулу =НОРМ.СТ.РАСП(2;ЛОЖЬ) =0,054 или =НОРМ.РАСП(2;0;1;ЛОЖЬ) .
Напомним, что вероятность того, что непрерывная случайная величина примет конкретное значение x равна 0. Для непрерывной случайной величины Х можно вычислить только вероятность события, что Х примет значение, заключенное в интервале (а; b).
Вычисление вероятностей с использованием функций MS EXCEL
1) Найдем вероятность, что случайная величина, распределенная по стандартному нормальному распределению (см. картинку выше), приняла положительное значение. Согласно свойству Функции распределения вероятность равна F(+∞)-F(0)=1-0,5=0,5.
В MS EXCEL для нахождения этой вероятности используйте формулу =НОРМ.СТ.РАСП(9,999E+307;ИСТИНА) -НОРМ.СТ.РАСП(0;ИСТИНА) =1-0,5. Вместо +∞ в формулу введено значение 9,999E+307= 9,999*10^307, которое является максимальным числом, которое можно ввести в ячейку MS EXCEL (так сказать, наиболее близкое к +∞).
2) Найдем вероятность, что случайная величина, распределенная по стандартному нормальному распределению , приняла отрицательное значение. Согласно определения Функции распределения, вероятность равна F(0)=0,5.
В MS EXCEL для нахождения этой вероятности используйте формулу =НОРМ.СТ.РАСП(0;ИСТИНА) =0,5.
3) Найдем вероятность того, что случайная величина, распределенная по стандартному нормальному распределению , примет значение, заключенное в интервале (0; 1). Вероятность равна F(1)-F(0), т.е. из вероятности выбрать Х из интервала (-∞;1) нужно вычесть вероятность выбрать Х из интервала (-∞;0). В MS EXCEL используйте формулу =НОРМ.СТ.РАСП(1;ИСТИНА) - НОРМ.СТ.РАСП(0;ИСТИНА) .
Все расчеты, приведенные выше, относятся к случайной величине, распределенной по стандартному нормальному закону N(0;1). Понятно, что значения вероятностей зависят от конкретного распределения. В статье Распределения случайной величины в MS EXCEL приведены распределения, для которых в MS EXCEL имеются соответствующие функции, позволяющие вычислить вероятности.
Обратная функция распределения (Inverse Distribution Function)
Вспомним задачу из предыдущего раздела: Найдем вероятность, что случайная величина, распределенная по стандартному нормальному распределению, приняла отрицательное значение.
Вероятность этого события равна 0,5.
Теперь решим обратную задачу: определим х, для которого вероятность, того что случайная величина Х примет значение =НОРМ.СТ.ОБР(0,5) =0.
Однозначно вычислить значение случайной величины позволяет свойство монотонности функции распределения.
Обратите внимание, что для вычисления обратной функции мы использовали именно функцию распределения , а не плотность распределения . Поэтому, в аргументах функции НОРМ.СТ.ОБР() отсутствует параметр интегральная , который подразумевается. Подробнее про функцию НОРМ.СТ.ОБР() см. статью про нормальное распределение .
В англоязычной литературе обратная функция распределения часто называется как Percent Point Function (PPF).
Примечание : При вычислении квантилей в MS EXCEL используются функции: НОРМ.СТ.ОБР() , ЛОГНОРМ.ОБР() , ХИ2.ОБР(), ГАММА.ОБР() и т.д. Подробнее о распределениях, представленных в MS EXCEL, можно прочитать в статье Распределения случайной величины в MS EXCEL .
Рассмотрим Нормальное распределение. С помощью функции MS EXCEL НОРМ.РАСП() построим графики функции распределения и плотности вероятности. Сгенерируем массив случайных чисел, распределенных по нормальному закону, произведем оценку параметров распределения, среднего значения и стандартного отклонения .
Нормальное распределение (также называется распределением Гаусса) является самым важным как в теории, так в приложениях системы контроля качества. Важность значения Нормального распределения (англ. Normal distribution ) во многих областях науки вытекает из Центральной предельной теоремы теории вероятностей.
Определение : Случайная величина x распределена по нормальному закону , если она имеет плотность распределения :
СОВЕТ : Подробнее о Функции распределения и Плотности вероятности см. статью Функция распределения и плотность вероятности в MS EXCEL .
Нормальное распределение зависит от двух параметров: μ (мю) — является математическим ожиданием (средним значением случайной величины) , и σ ( сигма) — является стандартным отклонением (среднеквадратичным отклонением). Параметр μ определяет положение центра плотности вероятности нормального распределения , а σ — разброс относительно центра (среднего).
Примечание : О влиянии параметров μ и σ на форму распределения изложено в статье про Гауссову кривую , а в файле примера на листе Влияние параметров можно с помощью элементов управления Счетчик понаблюдать за изменением формы кривой.
Нормальное распределение в MS EXCEL
В MS EXCEL, начиная с версии 2010, для Нормального распределения имеется функция НОРМ.РАСП() , английское название - NORM.DIST(), которая позволяет вычислить плотность вероятности (см. формулу выше) и интегральную функцию распределения (вероятность, что случайная величина X, распределенная по нормальному закону , примет значение меньше или равное x). Вычисления в последнем случае производятся по следующей формуле:
Вышеуказанное распределение имеет обозначение N (μ; σ). Так же часто используют обозначение через дисперсию N (μ; σ 2 ).
Примечание : До MS EXCEL 2010 в EXCEL была только функция НОРМРАСП() , которая также позволяет вычислить функцию распределения и плотность вероятности. НОРМРАСП() оставлена в MS EXCEL 2010 для совместимости.
Стандартное нормальное распределение
Стандартным нормальным распределением называется нормальное распределение с математическим ожиданием μ=0 и дисперсией σ=1. Вышеуказанное распределение имеет обозначение N (0;1).
Примечание : В литературе для случайной величины, распределенной по стандартному нормальному закону, закреплено специальное обозначение z.
Любое нормальное распределение можно преобразовать в стандартное через замену переменной z =( x -μ)/σ . Этот процесс преобразования называется стандартизацией .
Примечание : В MS EXCEL имеется функция НОРМАЛИЗАЦИЯ() , которая выполняет вышеуказанное преобразование. Хотя в MS EXCEL это преобразование называется почему-то нормализацией . Формулы =(x-μ)/σ и =НОРМАЛИЗАЦИЯ(х;μ;σ) вернут одинаковый результат.
В MS EXCEL 2010 для стандартного нормального распределения имеется специальная функция НОРМ.СТ.РАСП() и ее устаревший вариант НОРМСТРАСП() , выполняющий аналогичные вычисления.
Продемонстрируем, как в MS EXCEL осуществляется процесс стандартизации нормального распределения N (1,5; 2).
Для этого вычислим вероятность, что случайная величина, распределенная по нормальному закону N(1,5; 2) , меньше или равна 2,5. Формула выглядит так: =НОРМ.РАСП(2,5; 1,5; 2; ИСТИНА) =0,691462. Сделав замену переменной z =(2,5-1,5)/2=0,5 , запишем формулу для вычисления Стандартного нормального распределения: =НОРМ.СТ.РАСП(0,5; ИСТИНА) =0,691462.
Естественно, обе формулы дают одинаковые результаты (см. файл примера лист Пример ).
Обратите внимание, что стандартизация относится только к интегральной функции распределения (аргумент интегральная равен ИСТИНА), а не к плотности вероятности .
Примечание : В литературе для функции, вычисляющей вероятности случайной величины, распределенной по стандартному нормальному закону, закреплено специальное обозначение Ф(z). В MS EXCEL эта функция вычисляется по формуле =НОРМ.СТ.РАСП(z;ИСТИНА) . Вычисления производятся по формуле
В силу четности функции плотности стандартного нормального распределения f(x), а именно f(x)=f(-х), функция стандартного нормального распределения обладает свойством Ф(-x)=1-Ф(x).
Обратные функции
Функция НОРМ.СТ.РАСП(x;ИСТИНА) вычисляет вероятность P, что случайная величина Х примет значение меньше или равное х. Но часто требуется провести обратное вычисление: зная вероятность P, требуется вычислить значение х. Вычисленное значение х называется квантилем стандартного нормального распределения .
В MS EXCEL для вычисления квантилей используют функцию НОРМ.СТ.ОБР() и НОРМ.ОБР() .
Графики функций
В файле примера приведены графики плотности распределения вероятности и интегральной функции распределения .
Как известно, около 68% значений, выбранных из совокупности, имеющей нормальное распределение , находятся в пределах 1 стандартного отклонения (σ) от μ(среднего или математического ожидания); около 95% - в пределах 2-х σ, а в пределах 3-х σ находятся уже 99% значений. Убедиться в этом для стандартного нормального распределения можно записав формулу:
которая вернет значение 68,2689% - именно такой процент значений находятся в пределах +/-1 стандартного отклонения от среднего (см. лист График в файле примера ).
В силу четности функции плотности стандартного нормального распределения: f ( x )= f (-х) , функция стандартного нормального распределения обладает свойством F(-x)=1-F(x). Поэтому, вышеуказанную формулу можно упростить:
Для произвольной функции нормального распределения N(μ; σ) аналогичные вычисления нужно производить по формуле:
Вышеуказанные расчеты вероятности требуются для построения доверительных интервалов .
Примечание : Для построения функции распределения и плотности вероятности можно использовать диаграмму типа График или Точечная (со сглаженными линиями и без точек). Подробнее о построении диаграмм читайте статью Основные типы диаграмм .
Примечание : Для удобства написания формул в файле примера созданы Имена для параметров распределения: μ и σ.
Генерация случайных чисел
С помощью надстройки Пакет анализа можно сгенерировать случайные числа, распределенные по нормальному закону .
СОВЕТ : О надстройке Пакет анализа можно прочитать в статье Надстройка Пакет анализа MS EXCEL .
Сгенерируем 3 массива по 100 чисел с различными μ и σ. Для этого в окне Генерация случайных чисел установим следующие значения для каждой пары параметров:
Примечание : Если установить опцию Случайное рассеивание ( Random Seed ), то можно выбрать определенный случайный набор сгенерированных чисел. Например, установив эту опцию равной 25, можно сгенерировать на разных компьютерах одни и те же наборы случайных чисел (если, конечно, другие параметры распределения совпадают). Значение опции может принимать целые значения от 1 до 32 767. Название опции Случайное рассеивание может запутать. Лучше было бы ее перевести как Номер набора со случайными числами .
В итоге будем иметь 3 столбца чисел, на основании которых можно, оценить параметры распределения, из которого была произведена выборка: μ и σ . Оценку для μ можно сделать с использованием функции СРЗНАЧ() , а для σ – с использованием функции СТАНДОТКЛОН.В() , см. файл примера лист Генерация .
Примечание : Для генерирования массива чисел, распределенных по нормальному закону , можно использовать формулу =НОРМ.ОБР(СЛЧИС();μ;σ) . Функция СЛЧИС() генерирует непрерывное равномерное распределение от 0 до 1, что как раз соответствует диапазону изменения вероятности (см. файл примера лист Генерация ).
Задачи
Задача1 . Компания изготавливает нейлоновые нити со средней прочностью 41 МПа и стандартным отклонением 2 МПа. Потребитель хочет приобрести нити с прочностью не менее 36 МПа. Рассчитайте вероятность, что партии нити, изготовленные компанией для потребителя, будут соответствовать требованиям или превышать их. Решение1 : = 1-НОРМ.РАСП(36;41;2;ИСТИНА)
Задача2 . Предприятие изготавливает трубы, средний внешний диаметр которых равен 20,20 мм, а стандартное отклонение равно 0,25мм. Согласно техническим условиям, трубы признаются годными, если диаметр находится в пределах 20,00+/- 0,40 мм. Какая доля изготовленных труб соответствует ТУ? Решение2 : = НОРМ.РАСП(20,00+0,40;20,20;0,25;ИСТИНА)- НОРМ.РАСП(20,00-0,40;20,20;0,25) На рисунке ниже, выделена область значений диаметров, которая удовлетворяет требованиям спецификации.
Решение приведено в файле примера лист Задачи .
Задача3 . Предприятие изготавливает трубы, средний внешний диаметр которых равен 20,20 мм, а стандартное отклонение равно 0,25мм. Внешний диаметр не должен превышать определенное значение (предполагается, что нижняя граница не важна). Какую верхнюю границу в технических условиях необходимо установить, чтобы ей соответствовало 97,5% всех изготавливаемых изделий? Решение3 : = НОРМ.ОБР(0,975; 20,20; 0,25) =20,6899 или = НОРМ.СТ.ОБР(0,975)*0,25+20,2 (произведена «дестандартизация», см. выше)
Задача 4 . Нахождение параметров нормального распределения по значениям 2-х квантилей (или процентилей ). Предположим, известно, что случайная величина имеет нормальное распределение, но не известны его параметры, а только 2-я процентиля (например, 0,5- процентиль , т.е. медиана и 0,95-я процентиль ). Т.к. известна медиана , то мы знаем среднее , т.е. μ. Чтобы найти стандартное отклонение нужно использовать Поиск решения . Решение приведено в файле примера лист Задачи .
Примечание : До MS EXCEL 2010 в EXCEL были функции НОРМОБР() и НОРМСТОБР() , которые эквивалентны НОРМ.ОБР() и НОРМ.СТ.ОБР() . НОРМОБР() и НОРМСТОБР() оставлены в MS EXCEL 2010 и выше только для совместимости.
Линейные комбинации нормально распределенных случайных величин
Известно, что линейная комбинация нормально распределённых случайных величин x ( i ) с параметрами μ ( i ) и σ ( i ) также распределена нормально. Например, если случайная величина Y=x(1)+x(2), то Y будет иметь распределение с параметрами μ (1)+ μ(2) и КОРЕНЬ(σ(1)^2+ σ(2)^2). Убедимся в этом с помощью MS EXCEL.
С помощью надстройки Пакет анализа сгенерируем 2 массива по 100 чисел с различными μ и σ.
Теперь сформируем массив, каждый элемент которого является суммой 2-х значений, взятых из каждого массива.
С помощью функций СРЗНАЧ() и СТАНДОТКЛОН.В() вычислим среднее и дисперсию получившейся выборки и сравним их с расчетными.
Кроме того, построим График проверки распределения на нормальность ( Normal Probability Plot ), чтобы убедиться, что наш массив соответствует выборке из нормального распределения .
Прямая линия, аппроксимирующая полученный график, имеет уравнение y=ax+b. Наклон кривой (параметр а) может служить оценкой стандартного отклонения , а пересечение с осью y (параметр b) – среднего значения.
Для сравнения сгенерируем массив напрямую из распределения N (μ(1)+ μ(2); КОРЕНЬ(σ(1)^2+ σ(2)^2) ).
Как видно на рисунке ниже, обе аппроксимирующие кривые достаточно близки.
В качестве примера можно провести следующую задачу.
Задача . Завод изготавливает болты и гайки, которые упаковываются в ящики парами. Пусть известно, что вес каждого из изделий является нормальной случайной величиной. Для болтов средний вес составляет 50г, стандартное отклонение 1,5г, а для гаек 20г и 1,2г. В ящик фасуется 100 пар болтов и гаек. Вычислить какой процент ящиков будет тяжелее 7,2 кг. Решение . Сначала переформулируем вопрос задачи: Вычислить какой процент пар болт-гайка будет тяжелее 7,2кг/100=72г. Учитывая, что вес пары представляет собой случайную величину = Вес(болта) + Вес(гайки) со средним весом (50+20)г, и стандартным отклонением =КОРЕНЬ(СУММКВ(1,5;1,2)) , запишем решение = 1-НОРМ.РАСП(72; 50+20; КОРЕНЬ(СУММКВ(1,5;1,2));ИСТИНА) Ответ : 15% (см. файл примера лист Линейн.комбинация )
Аппроксимация Биномиального распределения Нормальным распределением
Если параметры Биномиального распределения B(n;p) находятся в пределах 0,1 10, то Биномиальное распределение можно аппроксимировать Нормальным распределением .
При значениях λ >15 , Распределение Пуассона хорошо аппроксимируется Нормальным распределением с параметрами: μ =λ , σ 2 = λ .
Подробнее о связи этих распределений, можно прочитать в статье Взаимосвязь некоторых распределений друг с другом в MS EXCEL . Там же приведены примеры аппроксимации, и пояснены условия, когда она возможна и с какой точностью.
СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .
Распределение вероятностей – одно из центральных понятий теории вероятности и математической статистики. Определение распределения вероятности равносильно заданию вероятностей всех СВ, описывающих некоторое случайное событие. Распределение вероятностей некоторой СВ, возможные значения которой x 1, x 2, … xn образуют выборку, задается указанием этих значений и соответствующих им вероятностей p 1, p 2,… pn . ( pn должны быть положительны и в сумме давать единицу).
В данной лабораторной работе будут рассмотрены и построены с помощью MS Excel наиболее распространенные распределения вероятности: биномиальное и нормальное.
1 Биномиальное распределение
Представляет собой распределение вероятностей числа наступлений некоторого события («удачи») в n повторных независимых испытаниях, если при каждом испытании вероятность наступления этого события равна p . При этом распределении разброс вариант (есть или нет события) является следствием влияния ряда независимых и случайных факторов.
П римером практического использования биномиального распределения может являться контроль качества партии фармакологического препарата. Здесь требуется подсчитать число изделий (упаковок), не соответствующих требованиям. Все причины, влияющие на качество препарата, принимаются одинаково вероятными и не зависящими друг от друга. Сплошная проверка качества в этой ситуации не возможна, поскольку изделие, прошедшее испытание, не подлежит дальнейшему использованию. Поэтому для контроля из партии наудачу выбирают определенное количество образцов изделий ( n ). Эти образцы всестороннее проверяют и регистрируют число бракованных изделий ( k ). Теоретически число бракованных изделий может быть любым, от 0 до n .
В Excel функция БИНОМРАСП применяется для вычисления вероятности в задачах с фиксированным числом тестов или испытаний, когда результатом любого испытания может быть только успех или неудача.
Функция использует следующие параметры:
БИНОМРАСП (число_успехов; число_испытаний; вероятностъ_успеха; интегральная) , где
число_успехов — это количество успешных испытаний;
число_испытаний — это число независимых испытаний (число успехов и число испытаний должны быть целыми числами);
вероятность_ успеха — это вероятность успеха каждого испытания;
интегральный — это логическое значение, определяющее форму функции.
Если данный параметр имеет значение ИСТИНА (=1), то считается интегральная функция распределения (вероятность того, что число успешных испытаний не менее значения число_ успехов);
если этот параметр имеет значение ЛОЖЬ (=0), то вычисляется значение функции плотности распределения (вероятность того, что число успешных испытаний в точности равно значению аргумента число_ успехов).
Пример 1. Какова вероятность того, что трое из четырех новорожденных будут мальчиками?
1. Устанавливаем табличный курсор в свободную ячейку, например в А1. Здесь должно оказаться значение искомой вероятности.
2. Для получения значения вероятности воспользуемся специальной функцией: нажимаем на панели инструментов кнопку Вставка функции ( fx ) .
3. В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория указаны виды функций. Выбираем Статистическая. Справа в поле Функция выбираем функцию БИНОМРАСП и нажимаем на кнопку ОК.
Появляется диалоговое окно функции. В поле Число_ s вводим с клавиатуры количество успешных испытаний (3). В поле Испытания вводим с клавиатуры общее количество испытаний (4). В рабочее поле Вероятность_ s вводим с клавиатуры вероятность успеха в отдельном испытании (0,5). В поле Интегральный вводим с клавиатуры вид функции распределения — интегральная или весовая (0). Нажимаем на кнопку ОК.
В ячейке А1 появляется искомое значение вероятности р = 0,25. Ровно 3 мальчика из 4 новорожденных могут появиться с вероятностью 0,25.
Если изменить формулировку условия задачи и выяснить вероятность того, что появится не более трех мальчиков, то в этом случае в рабочее поле Интегральный вводим 1 (вид функции распределения интегральный). Вероятность этого события будет равна 0,9375.
Задания для самостоятельной работы
1. Какова вероятность того, что восемь из десяти студентов, сдающих зачет, получат «незачет». (0,04)
2 . Нормальное распределение
Нормальное распределение - это совокупность объектов, в кото рой крайние значения некоторого признака — наименьшее и наибольшее — появ ляются редко; чем ближе значение признака к математическому ожиданию, тем чаще оно встречается. Например, распределение студентов по их весу приближается к нормальному распределению. Это распределение имеет очень широкий круг приложений в статистике, включая проверку гипотез.
Диаграмма нормального распределения симметрична относительно точки а (математического ожидания). Медиана нормального распределения равна тоже а. При этом в точке а функция f(x) достигает своего максимума, который равен
В Excel для вычисления значений нормального распределения используются функция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения.
Функция имеет параметры:
НОРМРАСП (х; среднее; стандартное_откл; интегральная) , где:
х — значения выборки, для которых строится распределение;
среднее — среднее арифметическое выборки;
стандартное_откл — стандартное отклонение распределения;
интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.
Если среднее = 0 и стандартное_откл = 1, то функция НОРМРАСП возвращает стандартное нормальное распределение.
Пример 2 . Построить график нормальной функции распределения f ( x ) при x , меняющемся от 19,8 до 28,8 с шагом 0,5, a =24,3 и
1. В ячейку А1 вводим символ случайной величины х, а в ячейку B 1 — символ функции плотности вероятности — f ( x ) .
2. Вводим в диапазон А2:А21 значения х от 19,8 до 28,8 с шагом 0,5. Для этого воспользуемся маркером автозаполнения: в ячейку А2 вводим левую границу диапазона (19,8), в ячейку A3 левую границу плюс шаг (20,3). Выделяем блок А2:А3. Затем за правый нижний угол протягиваем мышью до ячейки А21 (при нажатой левой кнопке мыши).
3. Устанавливаем табличный курсор в ячейку В2 и для получения значения вероятности воспользуемся специальной функцией — нажимаем на панели инструментов кнопку Вставка функции ( fx ) . В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория указаны виды функций. Выбираем Статистическая. Справа в поле Функция выбираем функцию НОРМРАСП. Нажимаем на кнопку ОК.
4. Появляется диалоговое окно НОРМРАСП. В рабочее поле X вводим адрес ячейки А2 щелчком мыши на этой ячейке. В рабочее поле Среднее вводим с клавиатуры значение математического ожидания (24,3). В рабочее поле Стандартное_откл вводим с клавиатуры значение среднеквадратического отклонения (1,5). В рабочее поле Интегральная вводим с клавиатуры вид функции распределения (0). Нажимаем на кнопку ОК.
5. В ячейке В2 появляется вероятность р = 0,002955. Указателем мыши за правый нижний угол табличного курсора протягиванием (при нажатой левой кнопке мыши) из ячейки В2 до В21 копируем функцию НОРМРАСП в диапазон В3:В21.
6. По полученным данным строим искомую диаграмму нормальной функции распределения. Щелчком указателя мыши на кнопке на панели инструментов вызываем Мастер диаграмм. В появившемся диалоговом окне выбираем тип диаграммы График, вид — левый верхний. После нажатия кнопки Далее указываем диапазон данных — В1:В21 (с помощью мыши). Проверяем, положение переключателя Ряды в: столбцах. Выбираем закладку Ряд и с помощью мыши вводим диапазон подписей оси X: А2:А21. Нажав на кнопку Далее, вводим названия осей Х и У и нажимаем на кнопку Готово.
Рис. 1 График нормальной функции распределения
Получен приближенный график нормальной функции плотности распределения (см. рис.1).
Задания для самостоятельной работы
1. Построить график нормальной функции плотности распределения f ( x ) при x , меняющемся от 20 до 40 с шагом 1 при
3. Генерация случайных величин
Еще одним аспектом использования законов распределения вероятностей являет ся генерация случайных величин. Бывают ситуации, когда необходимо получить последовательность случайных чисел. Это, в частности, требуется для моделирования объектов, имеющих случайную природу, по известному распределению вероятно стей.
Процедура генерации случайных величин используется для заполнения диапазона ячеек случайными числами, извлеченными из одного или не скольких распределений.
В MS Excel для генерации СВ используются функции из категории Математические :
СЛЧИС () – выводит на экран равномерно распределенные случайные числа больше или равные 0 и меньшие 1;
СЛУЧМЕЖДУ (ниж_граница; верх_граница) – выводит на экран случайное число, лежащее между про извольными заданными значениями.
В случае использования процедуры Генерация случайных чисел из пакета Анализа необходимо заполнить следующие поля:
- число переменных вводится число столбцов значений, которые необходимо разместить в выходном диапазоне. Если это число не введено, то все столбцы в выходном диапазоне будут заполнены;
- число случайных чисел вводится число случайных значений, которое необ ходимо вывести для каждой переменной, если число случайных чисел не будет введе но, то все строки выходного диапазона будут заполнены;
- в поле распределение необходимо выбрать тип распределения, которое следует использовать для генерации случайных переменных:
1. равномерное - характеризуется вер x ней и нижней границами. Переменные из влекаются с одной и той же вероятностью для всех значений интервала.
2. нормальное — характеризуется средним значением и стандартным отклонени ем. Обычно для этого распределения используют среднее значе ние 0 и стандартное отклонение 1.
3. биномиальное — характеризуется вероятностью успеха (величина р) для неко торого числа попыток. Например, можно сгенерировать случайные двухальтер нативные переменные по числу попыток, сумма которых будет биномиальной случайной переменной;
4. дискретное — характеризуется значением СВ и соответствующим ему интервалом вероятности, диапазон должен состоять из двух столбцов: левого, содержаще го значения, и правого, содержащего вероятности, связанные со значением в дан ной строке. Сумма вероятностей должна быть равна 1;
5. распределения Бернулли, Пуассона и Модельное.
- в поле случайное рассеивание вводится произвольное значение, для которого необ ходимо генерировать случайные числа. Впоследствии можно снова использовать это значение для получения тех же самых случайных чисел.
Пример 3. Повар столовой может готовить 4 различных первых блюда (уха, щи, борщ, грибной суп). Необходимо составить меню на месяц, так чтобы первые блюда чередовались в случайном порядке.
1. Пронумеруем первые блюда по порядку: 1 — уха, 2 — щи, 3 — борщ, 4 — грибной суп. Введем числа 1-4 в диапазон А2:А5 рабочей таблицы.
2. Укажем желаемую вероятность появления каждого первого блюда. Пусть все блюда будут равновероятны (р=1/4). Вводим число 0,25 в диапазон В2:В5.
4. Указываем выходной диапазон и нажимаем ОК. В столбце С появляются случайные числа: 1, 2, 3, 4.
Задание для самостоятельной работы
1. Сформировать выборку из 10 случайных чисел, лежащих в диапазоне от 0 до 1.
2. Сформировать выборку из 20 случайных чисел, лежащих в диапазоне от 5 до 20.
3. Пусть спортсмену необходимо составить график тренировок на 10 дней, так чтобы дистанция, пробегаемая каждый день, случайным образом менялась от 5 до 10 км.
4. Составить расписание внеклассных мероприятий на неделю для случайного проведения: семинаров, интеллектуальных игр, КВН и спец. курса.
5. Составить расписание на месяц для случайной демонстрации на телевидении одного из четырех рекламных роликов турфирмы. Причем вероятность появления рекламного ролика №1 должна быть в два раза выше, чем остальных рекламных роликов.
Читайте также: