Excel vba нормальное распределение

Обновлено: 06.07.2024

В статье приведены примеры кода Excel-VBA, задающие пользовательские функции для генерирования случайных величин с нужным распределением. Также разобраны встроенные средства для работы с распределениями.

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

В Excel достаточно удобно работать с нормальным распределением с помощью формул НОРМ.РАСП (NORM.DIST) и НОРМ.ОБР (NORM.INV). Первая функция позволяет считать доверительные интервалы, а вторая - генерировать нормальные распределения с произвольным мат. ожиданием и стандартным отклонением.

Треугольное распределение

Как сгенерировать в Excel

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

После добавления данного кода в Excel появится возможность написать формулу =TRDIST(random,min,max,mean)

Первый аргумент - random - случайная величина распределенная равномерно от 0 до 1. (функция СЛЧИС() либо СЛЧИСМЕЖДУ(0,1)).

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

Третий аргумент - mean - мат. ожидание.

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

В каких случаях применяется

При моделировании случайных процессов чаще всего используется нормальное или log-нормальное распределения, однако в некоторых случаях оправдано использование треугольного распределения. Один из примеров - вариативность случайной величины строго ограничена определённым диапазоном. Когда такое бывает? Допустим, что мы строим модель DCF для оценки денежного потока компании и для симуляции монте-карло нам необходимо задать распределение EBIT margin. Очевидно, что в теории данная величина может принимать значения от -1 до 1, но на практике для большинства здоровых компаний она находится в диапазоне от 5% до 50% и здесь-то нам и может помочь треугольное распределение и пошльзовательская функция TRDIST.

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

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

Дополнительные сведения о новой функции см. в Norm_Dist методе.

Синтаксис

выражения. NormDist (Arg1, Arg2, Arg3, Arg4)

выражение Переменная, представляюная объект WorksheetFunction.

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Arg1	Обязательный	Double	X — это значение, для которого нужно распределение.
Arg2	Обязательный	Double	Mean — арифметическое средство распределения.
Arg3	Обязательный	Double	Standard_dev — стандартное отклонение дистрибутива.
Arg4	Обязательный	Логический	Накопительное значение — логическое значение, определя которое определяет форму функции. Если кумулятивная является True, NormDist возвращает накопительную функцию распределения; если false, он возвращает функцию массы вероятности.

Возвращаемое значение

Double

Заметки

Если означает = 0, standard_dev = 1, а накопительный = TRUE, NormDist возвращает стандартное нормальное распределение, NormSDist.

Уравнение для нормальной функции плотности (кумулятивное = FALSE) является:

При кумулятивном = TRUE формула является неотъемлемой частью от отрицательной бесконечности до x данной формулы.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

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

Синтаксис

выражения. Norm_Dist (Arg1, Arg2, Arg3, Arg4)

выражение Переменная, представляюная объект WorksheetFunction.

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Arg1	Обязательный	Double	X . Значение, для которого нужно распространение.
Arg2	Обязательный	Double	Mean — арифметическое означает распределение.
Arg3	Обязательный	Double	Standard_dev — стандартное отклонение дистрибутива.
Arg4	Обязательный	Логический	Накопительное значение — логическое значение, определя которое определяет форму функции. Если накопительный является True, Norm_Dist возвращает накопительную функцию рассылки; если false, он возвращает функцию массы вероятности.

Возвращаемое значение

Double

Заметки

Если означает = 0, standard_dev = 1, а накопительный = TRUE, Norm_Dist возвращает стандартное нормальное распределение, Norm_S_Dist.

Уравнение для нормальной функции плотности (кумулятивное = FALSE) является:

Поддержка и обратная связь

Посоветуйте способ преобразования равномерно распределённой случайной величины (стандартная функция Rnd в VBA) в случайную величину по нормальному распределению на VBA.

Вообще говоря, мне нужно, чтобы возвращаемое значение было в виде double в заданном диапазоне, в котором оно варьируется по нормальному распределению (мат. ожидание - середина отрезка, дисперсию задаём).

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

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Определить, соответствует ли заданная выборка нормальному распределению
Доброго времени суток. Нужна программа которая бы определяла нормально ли(по условиям нормального.

Функция возвращающая абсолютное разницу первого и второго параметров и функция типа int, возвращающая минимальное из трех целых чисел
Разработать заданные согласно условия функции и сделать в главной программе по два вызова этих.

Найти случайную величину X
Случайная величина X задана плотностью распределения.Найти: a)Значение коэффициента A; б)функцию.

Ну вот за урок болванку набросал, но главное посмотреть, как и куда всё это втискивать.

Как добавить случайную величину к сигналу?
Здравствуйте! я хочу добавить случайную величину (нормальное распределение ) к сигналу на.

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

Случайную величину Х умножили на k. Как от этого изменяются ее характеристики
Случайную величину Х умножили на k. Как от этого изменяются ее характеристики: 1) математическое.

Мигалка на 3х группах светодиодах на Atmega8. Как использовать случайную величину?
Рабочий код-мигалка на 3х группах светодиодах на Atmega8.В ПРОТЕУСе он работает. Вопрос,как.

Рассмотрим Нормальное распределение. С помощью функции 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 .

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