Как построить многоугольник распределения в excel

Обновлено: 02.07.2024

Очень давно не писал блог. Расслабился совсем. Ну ничего, исправляюсь.

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

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

Статистический анализ в Excel можно осуществлять двумя способами:
• С помощью функций
• С помощью средств надстройки «Пакет анализа». Ее, как правило, еще необходимо установить.

Чтобы установить пакет анализа в Excel, выберите вкладку «Файл» (а в Excel 2007 это круглая цветная кнопка слева сверху), далее — «Параметры», затем выберите раздел «Надстройки». Нажмите «Перейти» и поставьте галочку напротив «Пакет анализа».

А теперь — к построению гистограмм распределения по частоте и их анализу.

Речь пойдет именно о частотных гистограммах, где каждый столбец соответствует частоте появления* значения в пределах границ интервалов. Например, мы хотим посмотреть, как у нас выглядит распределение значения предела текучести стали S355J2 в прокате толщиной 20 мм за несколько месяцев. В общем, хотим посмотреть, похоже ли наше распределение на нормальное (а оно должно быть таким).

*Примечание: для металловедческих целей типа оценки размера зерна или оценки объемной доли частиц этот вид гистограмм не пойдет, т.к. там высота столбика соответствует не частоте появления частиц определенного размера, а доле объема (а в плоскости шлифа — площади), которую эти частицы занимают.

График нормального распределения выглядит следующим образом:

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

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

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

А теперь — построение гистограмм!

Способ 1-ый. Халявный.

Идем во вкладку «Анализ данных» и выбираем «Гистограмма».
Выбираем входной интервал.
Здесь же предлагается задать интервал карманов, т.е. те диапазоны, в пределах которых будут лежать наши значения. Чем больше значений в интервале — тем выше столбик гистограммы. Если мы оставим поле «Интервалы карманов» пустым, то программа вычислит границы интервалов за нас.
Если хотим сразу же вывести график,то ставим галочку напротив «Вывод графика».
Нажимаем «ОК».
Вот, вроде бы, и все: гистограмма готова. Теперь нужно сделать так, чтобы по вертикальной оси отображалась не абсолютная частота, а относительная.
Под появившейся таблицей со столбцами «Карман» и «Частота» под столбцом «Частота» введем формулу «=СУММ» и сложим все абсолютные частоты.
К появившейся таблице со столбцами «Карман» и «Частота» добавим еще один столбец и назовем его «Относительная частота».
Во всех ячейках нового столбца введем формулу, которая будет рассчитывать относительную частоту: 100 умножить на абсолютную частоту (ячейка из столбца «частота») и разделить на сумму, которую мы вычислил в п. 7.

Способ 2-ой. Трудный, но интересный.

Будет полезен тому, кто по каким-либо причинам не смог установить Пакет анализа.

Перво-наперво нужно задать интервалы тех самых карманов, которые мы не стали вычислять в способе, описанном выше.
Интервал карманов вычисляют так: разность максимального значения и минимального значений массива, деленная на количество интервалов: (Xmax-Xmin)/n.
Для оценки оптимального для нашего массива данных количества интервалов можно воспользоваться формулой Стерджесса: n

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

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

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

Характеристики нормального распределения

Непрерывная случайная переменная, которая подчиняется нормальному распределению вероятностей, обладает некоторыми особыми свойствами. Предположим, что вся производимая продукция подчиняется нормальному распределению со средним значением 100 грамм и стандартным отклонением 3 грамма. Распределение вероятностей для такой случайной переменной представлено на рисунке.

Из этого рисунка мы можем сделать следующие наблюдения относительно нормального распределения — оно имеет форму колокола и симметрично относительно среднего значения.

Стандартное отклонение имеет немаловажную роль в форме изгиба. Если посмотреть на предыдущий рисунок, то можно заметить, что практически все измерения веса продукта попадают в интервал от 95 до 105 граммов. Давайте рассмотрим следующий рисунок, на котором представлено нормальное распределение с той же средней – 100 грамм, но со стандартным отклонением всего 1,5 грамма

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

Небольшое значение стандартного отклонения выражается в более «тощей и высокой кривой, плотно прижимающейся к среднему значению. Чем больше стандартное, тем «толще», ниже и растянутее получается кривая.

Создание массива с нормальным распределением

Итак, чтобы сгенерировать массив данных с нормальным распределением, нам понадобится функция НОРМ.ОБР() – это обратная функция от НОРМ.РАСП(), которая возвращает нормально распределенную переменную для заданной вероятности для определенного среднего значения и стандартного отклонения. Синтаксис формулы выглядит следующим образом:

=НОРМ.ОБР(вероятность; среднее_значение; стандартное_отклонение)

Другими словами, я прошу Excel посчитать, какая переменная будет находится в вероятностном промежутке от 0 до 1. И так как вероятность возникновения продукта с весом в 100 грамм максимальная и будет уменьшаться по мере отдаления от этого значения, то формула будет выдавать значения близких к 100 чаще, чем остальных.

Давайте попробуем разобрать на примере. Выстроим график распределения вероятностей от 0 до 1 с шагом 0,01 для среднего значения равным 100 и стандартным отклонением 1,5.

Как видим из графика точки максимально сконцентрированы у переменной 100 и вероятности 0,5.

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

=НОРМ.ОБР(СЛЧИС(); среднее_значение; стандартное_отклонение)

Создадим массив данных для нашего примера со средним значением 100 грамм и стандартным отклонением 1,5 грамма и протянем нашу формулу вниз.

Теперь, когда массив данных готов, мы можем выстроить график с нормальным распределением.

Построение графика нормального распределения

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

Далее строим таблицу с категориями. Нижняя граница (B11) равняется округленному вниз ближайшему кратному числу. Остальные категории увеличиваются на значение шага. Формула в ячейке B12 и последующих будет выглядеть:

В столбце X будет производится подсчет количества переменных в заданном промежутке. Для этого воспользуемся формулой ЧАСТОТА(), которая имеет два аргумента: массив данных и массив интервалов. Выглядеть формула будет следующим образом =ЧАСТОТА(Data!A1:A175;B11:B20). Также стоит отметить, что в таком варианте данная функция будет работать как формула массива, поэтому по окончании ввода необходимо нажать сочетание клавиш Ctrl+Shift+Enter.

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

Осталось отформатировать диаграмму и наш график с нормальным распределением готов.

Итак, мы познакомились с вами с нормальным распределением, узнали, что Excel позволяет генерировать массив данных с помощью формулы НОРМ.ОБР() для определенного среднего значения и стандартного отклонения и научились приводить данный массив в графический вид.

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

Построим диаграмму распределения в Excel. А также рассмотрим подробнее функции круговых диаграмм, их создание.

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

График нормального распределения имеет форму колокола и симметричен относительно среднего значения. Получить такое графическое изображение можно только при огромном количестве измерений. В Excel для конечного числа измерений принято строить гистограмму.

Внешне столбчатая диаграмма похожа на график нормального распределения. Построим столбчатую диаграмму распределения осадков в Excel и рассмотрим 2 способа ее построения.

Имеются следующие данные о количестве выпавших осадков:

Первый способ. Открываем меню инструмента «Анализ данных» на вкладке «Данные» (если у Вас не подключен данный аналитический инструмент, тогда читайте как его подключить в настройках Excel):

Задаем входной интервал (столбец с числовыми значениями). Поле «Интервалы карманов» оставляем пустым: Excel сгенерирует автоматически. Ставим птичку около записи «Вывод графика»:

После нажатия ОК получаем такой график с таблицей:

В интервалах не очень много значений, поэтому столбики гистограммы получились низкими.

Теперь необходимо сделать так, чтобы по вертикальной оси отображались относительные частоты.

Найдем сумму всех абсолютных частот (с помощью функции СУММ). Сделаем дополнительный столбец «Относительная частота». В первую ячейку введем формулу:

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

Чтобы найти интервал карманов, нужно разность максимального и минимального значений массива разделить на количество интервалов. Получим «ширину кармана».

Представим интервалы карманов в виде столбца значений. Сначала ширину кармана прибавляем к минимальному значению массива данных. В следующей ячейке – к полученной сумме. И так далее, пока не дойдем до максимального значения.

Для определения частоты делаем столбец рядом с интервалами карманов. Вводим функцию массива:

Вычислим относительные частоты (как в предыдущем способе).

Построим столбчатую диаграмму распределения осадков в Excel с помощью стандартного инструмента «Диаграммы».

Частота распределения заданных значений:

Круговые диаграммы для иллюстрации распределения

С помощью круговой диаграммы можно иллюстрировать данные, которые находятся в одном столбце или одной строке. Сегмент круга – это доля каждого элемента массива в сумме всех элементов.

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

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

На основании имеющихся данных о количестве осадков построим круговую диаграмму.

Доля «каждого месяца» в общем количестве осадков за год:

Круговая диаграмма распределения осадков по сезонам года лучше смотрится, если данных меньше. Найдем среднее количество осадков в каждом сезоне, используя функцию СРЗНАЧ. На основании полученных данных построим диаграмму:

Получили количество выпавших осадков в процентном выражении по сезонам.

В двух словах: Добавляем полосу прокрутки к гистограмме или к графику распределения частот, чтобы сделать её динамической или интерактивной.

Уровень сложности: продвинутый.

На следующем рисунке показано, как выглядит готовая динамическая гистограмма:

Что такое гистограмма или график распределения частот?

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

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

На какие вопросы отвечает гистограмма распределения?

Гистограмма – это один из моих самых любимых типов диаграмм, поскольку она дает огромное количество информации о данных.

В данном случае мы хотим знать, как много участников окажется в возрастных группах 20-ти, 30-ти, 40-ка лет и так далее. Гистограмма наглядно покажет это, поэтому определить закономерности и отклонения будет довольно легко.

«Неужели наше мероприятие не интересно гражданам в возрасте от 20 до 29 лет?»

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

Динамическая гистограмма

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

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

Как это работает?

Краткий ответ: Формулы, динамические именованные диапазоны, элемент управления «Полоса прокрутки» в сочетании с гистограммой.

Формулы

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

Чтобы вычислить размер группы, разделим общее количество (80-10) на количество групп. Количество групп устанавливается настройками полосы прокрутки. Чуть позже разъясним это подробнее.

Далее при помощи функции ЧАСТОТА (FREQUENCY) я рассчитываю количество элементов в каждой группе в заданном столбце. В данном случае мы возвращаем частоту из столбца Age таблицы с именем tblData.

Функция ЧАСТОТА (FREQUENCY) вводится, как формула массива, нажатием Ctrl+Shift+Enter.

Динамический именованный диапазон

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

Когда пользователь перемещает ползунок полосы прокрутки, число строк в динамическом диапазоне изменяется так, чтобы отобразить на графике только нужные данные. В нашем примере задано два динамических именованных диапазона: один для данных — rngGroups (столбец Frequency) и второй для подписей горизонтальной оси — rngCount (столбец Bin Name).

Элемент управления «Полоса прокрутки»

Элемент управления Полоса прокрутки (Scroll Bar) может быть вставлен с вкладки Разработчик (Developer).

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

Гистограмма

График – это самая простая часть задачи. Создаём простую гистограмму и в качестве источника данных устанавливаем динамические именованные диапазоны.

Есть вопросы?

Что ж, это был лишь краткий обзор того, как работает динамическая гистограмма.

Да, это не самая простая диаграмма, но, полагаю, пользователям понравится с ней работать. Определённо, такой интерактивной диаграммой можно украсить любой отчёт.

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

Закон распределения дискретной случайной величины можно изобразить графически, для чего в прямоугольной системе координат строят точки М1 (Х1; Р1), M2(X2; Р2). • • • Mn(Хn; Рn) (Xi—-возможные значения X, Pi—соответствующие вероятности) и соединяют их отрезками прямых. Полученную фигуру называют многоугольником распределения.

Задача 164. Дискретная случайная величина X задана законом распределения:

X 1 3 6 8
р 0,2 0,1 0,4 0,3

Построить многоугольник распределения.

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

В Excel имеются средства для создания графиков и диаграмм, с помощью которых вы сможете в наглядной форме представить зависимости и тенденции, отраженные в числовых данных. Кнопки построения графиков и диаграмм находятся в группе Диаграммы на вкладке Вставка (Рис.1).

Для того чтобы построить многоугольник распределения сначала нужно сформировать таблицу , например так (Рис.2).

Далее просто выделяем нужные нам ячейки и выбираем тип графика который надо построить (Рис.3).

В результате мы получи многоугольник распределения с которым сможем производить дальнейшие действия Рис.4.

Для тех кому облом учить Excel, даем готовый шаблон (калькулятор) для построения многоугольника распределения , скачать шаблон бесплатно.

Задания на тему: "Многоугольник распределения" для самостоятельного решения.

165. Дискретная случайная величина X задана законом распределения:
а) X 2 4 5 6 б) X 10 15 20
р 0,3 0,1 0,2 0,4 р 0,1 0,7 0,2
Построить многоугольник распределения

Рассмотрим Биномиальное распределение, вычислим его математическое ожидание, дисперсию, моду. С помощью функции MS EXCEL БИНОМ.РАСП() построим графики функции распределения и плотности вероятности. Произведем оценку параметра распределения p, математического ожидания распределения и стандартного отклонения. Также рассмотрим распределение Бернулли.

Определение . Пусть проводятся n испытаний, в каждом из которых может произойти только 2 события: событие «успех» с вероятностью p или событие «неудача» с вероятностью q =1-p (так называемая Схема Бернулли, Bernoulli trials ).

Вероятность получения ровно x успехов в этих n испытаниях равна:

Примечание : Порядок получения успехов значения не имеет. Если важен порядок, то см. статью Отрицательное Биномиальное распределение .

Количество успехов в выборке x является случайной величиной, которая имеет Биномиальное распределение (англ. Binomial distribution ) p и n – являются параметрами этого распределения.

Примечание : Запись означает количество сочетаний из n элементов по x . Для сочетаний также используется запись . Подробнее о сочетаниях см. статью Сочетания без повторений: Комбинаторика в MS EXCEL .

Напомним, что для применения схемы Бернулли и соответственно Биномиального распределения, должны быть выполнены следующие условия:

каждое испытание должно иметь ровно два исхода, условно называемых «успехом» и «неудачей».
результат каждого испытания не должен зависеть от результатов предыдущих испытаний (независимость испытаний).
вероятность успеха p должна быть постоянной для всех испытаний.

Биномиальное распределение в MS EXCEL

В MS EXCEL, начиная с версии 2010, для Биномиального распределения имеется функция БИНОМ.РАСП() , английское название - BINOM.DIST(), которая позволяет вычислить вероятность того, что в выборке будет ровно х «успехов» (т.е. функцию плотности вероятности p(x), см. формулу выше), и интегральную функцию распределения (вероятность того, что в выборке будет x или меньше «успехов», включая 0).

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

До MS EXCEL 2010 в EXCEL была функция БИНОМРАСП() , которая также позволяет вычислить функцию распределения и плотность вероятности p(x). БИНОМРАСП() оставлена в MS EXCEL 2010 для совместимости.

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

Примечание : Для построения интегральной функции распределения идеально подходит диаграмма типа График , для плотности распределения – Гистограмма с группировкой . Подробнее о построении диаграмм читайте статью Основные типы диаграмм.

Примечание : Для удобства написания формул в файле примера созданы Имена для параметров Биномиального распределения : n и p.

В файле примера приведены различные расчеты вероятности с помощью функций MS EXCEL:

Как видно на картинке выше, предполагается, что:

В бесконечной совокупности, из которой делается выборка, содержится 10% (или 0,1) годных элементов (параметр p , третий аргумент функции = БИНОМ.РАСП() )
Чтобы вычислить вероятность, того что в выборке из 10 элементов (параметр n , второй аргумент функции) будет ровно 5 годных элементов (первый аргумент), нужно записать формулу: =БИНОМ.РАСП(5; 10; 0,1; ЛОЖЬ)
Последний, четвертый элемент, установлен =ЛОЖЬ, т.е. возвращается значение функции плотности распределения .

Если значение четвертого аргумента =ИСТИНА, то функция БИНОМ.РАСП() возвращает значение интегральной функции распределения или просто Функцию распределения . В этом случае можно рассчитать вероятность того, что в выборке количество годных элементов будет из определенного диапазона, например, 2 или меньше (включая 0).

Для этого нужно записать формулу: = БИНОМ.РАСП(2; 10; 0,1; ИСТИНА)

Примечание : При нецелом значении х, дробная часть отбрасывается . Например, следующие формулы вернут одно и тоже значение: =БИНОМ.РАСП( 2 ; 10; 0,1; ИСТИНА) =БИНОМ.РАСП( 2,9 ; 10; 0,1; ИСТИНА)

Примечание : В файле примера плотность вероятности и функция распределения также вычислены с использованием определения и функции ЧИСЛКОМБ() .

Показатели распределения

В файле примера на листе Пример имеются формулы для расчета некоторых показателей распределения:

математического ожидания =n*p;
дисперсии (квадрата стандартного отклонения) = n*p*(1-p);
моды = (n+1)*p;
коэффициента асимметрии =(1-2*p)*КОРЕНЬ(n*p*(1-p)).

Выведем формулу математического ожидания Биномиального распределения , используя Схему Бернулли .

По определению случайная величина Х в схеме Бернулли (Bernoulli random variable) имеет функцию распределения :

Это распределение называется распределение Бернулли .

Примечание : распределение Бернулли – частный случай Биномиального распределения с параметром n=1.

Найдем математическое ожидание ( среднее, mean ) распределения Бернулли ( x принимает только 2 значения).

Предположим, что мы провели n последовательных испытаний Бернулли и у нас сформировалась выборка , состоящая из n элементов: x1, x2, …, xn (каждое из которых равно 0 или 1). Сумма этих случайных величин Y=X1+X2+…+Xn, в свою очередь, также является случайной величиной и, как мы помним, будет иметь Биномиальное распределение с параметрами n и p .

Учитывая, что математическое ожидание для каждого x _i равно p , то для соответствующего Биномиального распределения μ=p*n.

Аналогичным образом, можно вычислить дисперсию Биномиального распределения.

Для этого сначала найдем дисперсию ( второй момент, variance ) распределения Бернулли :

Соответственно, дисперсия для Биномиального распределения равна σ 2 =n*p*(1-p)= n*p*q.

Генерация случайных чисел. Распределение Бернулли

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

СОВЕТ : О надстройке Пакет анализа можно прочитать в статье Надстройка Пакет анализа MS EXCEL .

Сгенерируем 3 массива по 100 чисел с различными вероятностями успеха: 0,1; 0,5 и 0,9. Для этого в окне Генерация случайных чисел установим следующие параметры для каждой вероятности p:

Примечание : Если установить опцию Случайное рассеивание ( Random Seed ), то можно выбрать определенный случайный набор сгенерированных чисел. Например, установив эту опцию =25 можно сгенерировать на разных компьютерах одни и те же наборы случайных чисел (если, конечно, другие параметры распределения совпадают). Значение опции может принимать целые значения от 1 до 32 767. Название опции Случайное рассеивание может запутать. Лучше было бы ее перевести как Номер набора со случайными числами .

В итоге будем иметь 3 столбца по 100 чисел, на основании которых можно, например, оценить вероятность успеха p по формуле: Число успехов/100 (см. файл примера лист ГенерацияБернулли ).

Примечание : Для распределения Бернулли с p=0,5 можно использовать формулу =СЛУЧМЕЖДУ(0;1) , которая соответствует дискретному равномерному распределению .

Генерация случайных чисел. Биномиальное распределение

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

Сгенерируем 3 массива по 100 чисел с различными вероятностями успеха: 0,1; 0,5; 0,9. Количество испытаний n установим 20. Для этого в окне Генерация случайных чисел установим следующие параметры для каждой вероятности p:

В итоге будем иметь 3 столбца чисел, на основании которых можно, например, оценить вероятность успеха p по формуле: Среднее значение успехов/ n (см. файл примера лист ГенерацияБином ).

Примечание : Для генерирования массива чисел, распределенных по Биномиальному закону , можно использовать формулу =БИНОМ.ОБР(20; p; СЛЧИС()) , где p – вероятность успеха. Функция СЛЧИС() генерирует непрерывное равномерное распределение от 0 до 1, что как раз соответствует диапазону изменения вероятности (см. файл примера лист ГенерацияБином ).

Оценка параметра p

В схеме Бернулли оценить параметр распределения p можно по формуле =СУММ(B14:B113)/СЧЁТ(B14:B113) . В формуле предполагается, что массив случайных чисел находится в диапазоне B14:B113 .

Оценить параметр Биномиального распределения p можно по формуле = СРЗНАЧ(B13:B112)/n (предполагается, что случайные числа сгенерированы формулой =БИНОМ.ОБР(n; p; СЛЧИС() ). Также в формуле предполагается, что массив случайных чисел находится в диапазоне B13:B112 .

Обратная функция БИНОМ.ОБР()

Вспомним график функции Биномиального распределения :

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

Задана величина выборки из партии ( n =20) и р= 0,2 - доля дефектных изделий, которая обычно наблюдается в данном производственном процессе. Также пусть задана вероятность допустить ошибку 1-го рода (см. статью про уровень доверия ) равная 90%. Пороговый приемочный критерий можно вычислить по формуле =БИНОМ.ОБР(20; 0,2; 90%) . Формула вернет значение 6 - наибольшее количество дефектных изделий, допустимое в выборке .

Примечание : Третий аргумент функции БИНОМ.ОБР() называется Альфа ( α error, type I error, риск производителя, альфа-риск ) и представляет собой вероятность допустить ошибку 1-го рода при проверке статистической гипотезы (см. статью Проверка статистических гипотез в MS EXCEL о равенстве среднего значения распределения (дисперсия известна) ).

Предположим, что в выборке обнаружилось 7 дефектных изделий. Это означает, что «очень вероятна» ситуация, что изменилась доля дефектных изделий p , которая является характеристикой нашего производственного процесса. Хотя такая ситуация «очень вероятна», но существует вероятность (альфа-риск, ошибка 1-го рода, «ложная тревога»), что все же p осталась без изменений, а увеличенное количество дефектных изделий обусловлено случайностью выборки.

Как видно на рисунке ниже, 7 – количество дефектных изделий, которое допустимо для процесса с p=0,21 при том же значении Альфа . Это служит иллюстрацией, что при превышении порогового значения дефектных изделий в выборке, p «скорее всего» увеличилось. Фраза «скорее всего» означает, что существует всего лишь 10% вероятность (100%-90%) того, что отклонение доли дефектных изделий выше порогового вызвано только сучайными причинами.

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

Примечание : До MS EXCEL 2010 в EXCEL была функция КРИТБИНОМ() , которая эквивалентна БИНОМ.ОБР() . КРИТБИНОМ() оставлена в MS EXCEL 2010 и выше для совместимости.

Связь Биномиального распределения с другими распределениями

Если параметр n Биномиального распределения стремится к бесконечности, а p стремится к 0, то в этом случае Биномиальное распределение может быть аппроксимировано Распределением Пуассона . Можно сформулировать условия, когда приближение распределением Пуассона работает хорошо:

p0,9 (учитывая, что q=1-p , вычисления в этом случае необходимо производить через q (а х нужно заменить на n-x ). Следовательно, чем меньше q и больше n , тем приближение точнее).

При 0,1 10 Биномиальное распределение можно аппроксимировать Нормальным распределением .

В свою очередь, Биномиальное распределение может служить хорошим приближением Гипергеометрического распределения , когда размер совокупности N Гипергеометрического распределения гораздо больше размера выборки n (т.е., N>>n или n/N СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

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

Нормальное распределение (также называется распределением Гаусса) является самым важным как в теории, так в приложениях системы контроля качества. Важность значения Нормального распределения (англ. Normal distribution ) во многих областях науки вытекает из Центральной предельной теоремы теории вероятностей.

Определение : Случайная величина x распределена по нормальному закону , если она имеет плотность распределения :

Нормальное распределение зависит от двух параметров: μ (мю) — является математическим ожиданием (средним значением случайной величины) , и σ ( сигма) — является стандартным отклонением (среднеквадратичным отклонением). Параметр μ определяет положение центра плотности вероятности нормального распределения , а σ — разброс относительно центра (среднего).

Примечание : О влиянии параметров μ и σ на форму распределения изложено в статье про Гауссову кривую , а в файле примера на листе Влияние параметров можно с помощью элементов управления Счетчик понаблюдать за изменением формы кривой.

Нормальное распределение в 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 .

Геометрическое распределение (англ. Geometric distribution ) является частным случаем Отрицательного Биномиального распределения (при r=1).

Пусть проводятся испытания, в каждом из которых может произойти только событие «успех» с вероятностью p или событие «неудача» с вероятностью q =1-p ( Схема Бернулли ).

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

Геометрическое распределение в MS EXCEL

В MS EXCEL, начиная с версии 2010, для Отрицательного Биномиального распределения имеется функция ОТРБИНОМ.РАСП() , английское название NEGBINOM.DIST(), которая позволяет вычислить вероятность возникновения количества неудач до получения заданного числа успеха при заданной вероятности успеха.

Для Геометрического распределения второй аргумент этой функции должен быть 1, т.к. нас интересует только первый успех.

Это определение несколько отличается от формулировки приведенной выше, где вычисляется вероятность, что первый успех произойдет после x испытаний . Различие сводится к диапазону изменения диапазона x : если вероятность определена через количество испытаний, то х может принимать значения начиная с 1, а если через количество неудач, то – начиная с 0. Поэтому справедлива формула: p(x_ неудач )= p(x_ испытаний -1). См. файл примера лист Пример , где приведено 2 способа расчета.

Ниже используется подход, принятый в функции MS EXCEL: через количество неудач.

Чтобы вычислить функцию плотности вероятности p(x), см. формулу выше, необходимо установить четвертый аргумент в функции ОТРБИНОМ.РАСП() равным ЛОЖЬ. Для вычисления интегральной функции распределения , необходимо установить четвертый аргумент равным ИСТИНА.

Примечание : До MS EXCEL 2010 в EXCEL была функция ОТРБИНОМРАСП() , которая позволяет вычислить только плотность вероятности . В файле примера приведена формула на основе функции ОТРБИНОМРАСП() для вычисления интегральной функции распределения . Там же приведена формула для вычисления вероятности через определение.

Примечание : Для построения интегральной функции распределения идеально подходит диаграмма типа График , для плотности распределения – Гистограмма с группировкой . Подробнее о построении читайте статью Основные типы диаграмм .

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

Примечание : В функции ОТРБИНОМ.РАСП( ) при нецелом значении х , дробная часть отбрасывается . Например, следующие формулы вернут одно и тоже значение: ОТРБИНОМ.РАСП( 2 ; 1; 0,4; ИСТИНА)= ОТРБИНОМ.РАСП( 2,9 ; 1; 0,4; ИСТИНА)

Задачи

Решения задач приведены в файле примера на листе Пример .

Задача1 . Нефтяная компания бурит скважины для добычи нефти. Вероятность обнаружить нефть в скважине равна 20%. Какова вероятность, что первая нефть будет получена именно в третью попытку? Какова вероятность, что для обнаружения первой нефти потребуется три попытки? Решение1 : =ОТРБИНОМ.РАСП(3-1; 1; 0,2; ЛОЖЬ) =ОТРБИНОМ.РАСП(3-1; 1; 0,2; ИСТИНА)

Задача2 . Рейтинговое агентство делает опрос случайных прохожих в городе о любимой марке автомобиля. Пусть известно, что у 1% горожан любимым автомобилем является Lada Granta . Какова вероятность, что встретить первого почитателя этой марки автомобиля после опроса 10 человек? Решение2 : =ОТРБИНОМ.РАСП(10-1; 1; 0,01; ИСТИНА )=9,56%

Параметры распределения

В файле примера на листе Пример имеются формулы для расчета математического ожидания = (1-p)/p и дисперсии (квадрата стандартного отклонения) =(1-p)/p^2.

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

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