Elettracompany.com

Компьютерный справочник
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Нормрасп в excel

Нормальное распределение. Построение графика в Excel. Концепция шести сигм

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

Функция НОРМРАСП имеет следующий синтаксис:

НОРМРАСП (Х; среднее; стандартное_откл; интегральная)

Х — аргумент функции; фактически НОРМРАСП можно трактовать как y=f(x); при этом функция возвращает вероятность реализации события Х

Среднее (µ) — среднее арифметическое распределения; чем дальше Х от среднего, тем ниже вероятность реализации такого события

Стандартное_откл (σ) — стандартное отклонение распределения; мера кучности; чем меньше σ, тем выше вероятность у тех Х, которые расположены ближе к среднему

Интегральная — логическое значение, определяющее форму функции. Если «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения, тот есть суммарную вероятность всех событий для аргументов от -∞ до Х; если «интегральная» имеет значение ЛОЖЬ, возвращается вероятность реализации события Х, точнее говоря, вероятность событий находящихся в некотором диапазоне вокруг Х

Например, для µ=0 имеем:

Скачать заметку в формате Word, пример в формате Excel

Здесь по оси абсцисс единица измерения – σ, или (что то же самое), можно сказать, что график построен для σ = 1. То есть, «-2» на графике означает -2σ. По оси ординат шкала убрана умышленно, так как она лишена смысла. Точнее говоря, высота кривой зависит от плотности точек на оси абсцисс, по которым мы строим график. Например, если на интервал от 0 до 1σ приходится 10 точек, то высота в максимуме составит 4%, а если 20 точек – 2%. Здесь проценты означают вероятность попадания случайной величины в узкий диапазон окрестности точки на оси абсцисс. Зато имеет смысл площадь под кривой на определенном интервале. И эта площадь не зависит от плотности точек. Так, например, площадь под кривой на интервале от 0 до 1σ составляет 34,13%. Это значение можно интерпретировать следующим образом: с вероятностью 68,26% случайная величина Х попадет в диапазон µ ± σ.

Теперь, наверное, вам будет лучше понятен смысл выражения «качество шести сигм». Оно означает, что производство налажено таким образом, что случайная величина Х (например, диаметр вала) находясь в диапазон µ ± 6σ, всё еще удовлетворяет техническим условиям (допускам). Это достигается за счет значительного уменьшения сигмы, то есть случайная величина Х очень близка к нормативному значению µ. На графике ниже представлено три ситуации, когда границы допуска остаются неизменными, а благодаря повышению качества (уменьшению вариабельности, сужению сигма) доля брака сокращается:

На первом рисунке только 1,5σ попадают в границы допуска, то есть только 86,6% деталей являются годными. На втором рисунке уже 3σ попадают в границы допуска, то есть 99,75% являются годными. Но всё еще 25 деталей из каждых 10 000 произведенных являются браком. На третьем рисунке целых 6σ попадают в границы допуска, то есть в брак попадут только две детали на миллиард изготовленных!

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

Опыт показывает, что показатели процессов имеют тенденцию изменяться с течением времени. В результате со временем в промежуток между границами поля допуска будет входить меньше, чем было установлено первоначально. Опытным путём было установлено, что изменение параметров во времени можно учесть с помощью смещения в 1,5 сигма. Другими словами, с течением времени длина промежутка между границами поля допуска под кривой нормального распределения уменьшается до 4,5 сигма вследствие того, что среднее процесса с течением времени смещается и/или среднеквадратическое отклонение увеличивается.

Широко распространённое представление о «процессе шесть сигма» заключается в том, что такой процесс позволяет получить уровень качества 3,4 дефектных единиц на миллион готовых изделий при условии, что длина под кривой слева или справа от среднего будет соответствовать 4,5 сигма (без учёта левого или правого конца кривой за границей поля допуска). Таким образом, уровень качества 3,4 дефектных единиц на миллион готовых изделий соответствует длине промежутка 4,5 сигма, получаемых разницей между 6 сигма и сдвигом в 1,5 сигма, которое было введено, чтобы учесть изменение показателей с течением времени. Такая поправка создана для того, чтобы предупредить неправильною оценку уровня дефектности, встречающееся в реальных условиях.

С моей точки зрения, не вполне внятное объяснение. Тем не менее, во всем мире принята следующая таблица соответствия числа дефектов и уровня качества в сигмах:

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

Читать еще:  Сортировка строк в excel по значению

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

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

Непрерывная случайная переменная, которая подчиняется нормальному распределению вероятностей, обладает некоторыми особыми свойствами. Предположим, что вся производимая продукция подчиняется нормальному распределению со средним значением 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 позволяет генерировать массив данных с помощью формулы НОРМ.ОБР() для определенного среднего значения и стандартного отклонения и научились приводить данный массив в графический вид.

Вам также могут быть интересны следующие статьи

12 комментариев

Ренат, добрый день.
Все несколько проще:
Данные->Анализ данных->Генерация случайных чисел (Распределение=Нормальное)
+
Данные->Анализ данных->Гистограмма->Галка на «вывод графика» («Карманы» можно даже не задавать)

График нормального распределения в Excel

Наверное, не все знают, что в Excel есть встроенная функция для построения нормального распределения. Графики нормального распределения

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

Функция НОРМРАСП имеет следующий синтаксис:

НОРМРАСП (Х; среднее; стандартное_откл; интегральная)

Х — аргумент функции; фактически НОРМРАСП можно трактовать как y=f(x); при этом функция возвращает вероятность реализации события Х

Среднее (µ) — среднее арифметическое распределения; чем дальше Х от среднего, тем ниже вероятность реализации такого события

Стандартное_откл (σ) — стандартное отклонение распределения; мера кучности; чем меньше σ, тем выше вероятность у тех Х, которые расположены ближе к среднему

Интегральная — логическое значение, определяющее форму функции. Если «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения, тот есть суммарную вероятность всех событий для аргументов от -∞ до Х; если «интегральная» имеет значение ЛОЖЬ, возвращается вероятность реализации события Х, точнее говоря, вероятность событий находящихся в некотором диапазоне вокруг Х

Например, для µ=0 имеем:

Читать еще:  Условное форматирование в excel это

Скачать заметку в формате Word, пример в формате Excel

Здесь по оси абсцисс единица измерения — σ, или (что то же самое), можно сказать, что график построен для σ = 1. То есть, «-2» на графике означает −2σ. По оси ординат шкала убрана умышленно, так как она лишена смысла. Точнее говоря, высота кривой зависит от плотности точек на оси абсцисс, по которым мы строим график. Например, если на интервал от 0 до 1σ приходится 10 точек, то высота в максимуме составит 4%, а если 20 точек — 2%. Здесь проценты означают вероятность попадания случайной величины в узкий диапазон окрестности точки на оси абсцисс. Зато имеет смысл площадь под кривой на определенном интервале. И эта площадь не зависит от плотности точек. Так, например, площадь под кривой на интервале от 0 до 1σ составляет 34,13%. Это значение можно интерпретировать следующим образом: с вероятностью 68,26% случайная величина Х попадет в диапазон µ ± σ.

Теперь, наверное, вам будет лучше понятен смысл выражения «качество шести сигм». Оно означает, что производство налажено таким образом, что случайная величина Х (например, диаметр вала) находясь в диапазон µ ± 6σ, всё еще удовлетворяет техническим условиям (допускам). Это достигается за счет значительного уменьшения сигмы, то есть случайная величина Х очень близка к нормативному значению µ. На графике ниже представлено три ситуации, когда границы допуска остаются неизменными, а благодаря повышению качества (уменьшению вариабельности, сужению сигма) доля брака сокращается:

На первом рисунке только 1,5σ попадают в границы допуска, то есть только 86,6% деталей являются годными. На втором рисунке уже 3σ попадают в границы допуска, то есть 99,75% являются годными. Но всё еще 25 деталей из каждых 10 000 произведенных являются браком. На третьем рисунке целых 6σ попадают в границы допуска, то есть в брак попадут только две детали на миллиард изготовленных!

Вообще-то говоря, измерение качества в терминах сигм использует не совсем нормальное распределение. ? Вот что пишет на эту тему Википедия:

Опыт показывает, что показатели процессов имеют тенденцию изменяться с течением времени. В результате со временем в промежуток между границами поля допуска будет входить меньше, чем было установлено первоначально. Опытным путём было установлено, что изменение параметров во времени можно учесть с помощью смещения в 1,5 сигма. Другими словами, с течением времени длина промежутка между границами поля допуска под кривой нормального распределения уменьшается до 4,5 сигма вследствие того, что среднее процесса с течением времени смещается и/или среднеквадратическое отклонение увеличивается.

Широко распространённое представление о «процессе шесть сигма» заключается в том, что такой процесс позволяет получить уровень качества 3,4 дефектных единиц на миллион готовых изделий при условии, что длина под кривой слева или справа от среднего будет соответствовать 4,5 сигма (без учёта левого или правого конца кривой за границей поля допуска). Таким образом, уровень качества 3,4 дефектных единиц на миллион готовых изделий соответствует длине промежутка 4,5 сигма, получаемых разницей между 6 сигма и сдвигом в 1,5 сигма, которое было введено, чтобы учесть изменение показателей с течением времени. Такая поправка создана для того, чтобы предупредить неправильною оценку уровня дефектности, встречающееся в реальных условиях.

С моей точки зрения, не вполне внятное объяснение. Тем не менее, во всем мире принята следующая таблица соответствия числа дефектов и уровня качества в сигмах:

Для сравнения приведу таблицу для нормального распределения:

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

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

xi – границы интервалов группировки (карманы – получены как результат выполнения процедуры Гистограмма);

mi – количество элементов выборки, попавших в i – ый интервал (частота – получена в результате процедуры Гистограмма);

Для построения этой таблицы в Excel к столбцам Карман и Частота процедуры Гистограмманадо добавить столбцы n∙pi и , где в ячейках столбца будет находиться статистика (как показано на рис. 6).

pi – теоретическая вероятность попадания элементов выборки в i – ый интервал группировки для принятой гипотезы о нормальном распределении генеральной совокупности равна

Замечание. Функцию НОРМРАСП вызывается следующим образом. В главном меню Excel выбирается закладка Формулы → Вставить функцию(или щелкните в строке формул значок fx)в диалоговом окнеМастер функций – шаг 1 из 2в категорииСтатистические → НОРМРАСП. ОК.

Рис. 7. Окно Мастер функций для выбора функции НОРМРАСП из категории Статистические.

В раскрывшемся окне Аргументы функции НОРМРАСП заполните поля ввода как показано далее на рис. 10.

Рис. 8. Окно ввода параметров для получения функции нормального распределения

В поле X вводится адрес ячейки, в которой находится граница интервала группировки.

Читать еще:  Снимание видео с экрана

В поле Среднее вводится адрес ячейки, в которой находится среднее значение выборки, полученное при выполнении процедуры Описательная статистика – E$10$.

В поле Стандартное_откл вводится адрес ячейки, в которой находится значение стандартного отклонения выборки, полученное при выполнении процедуры Описательная статистика – E$14$.

В поле Интегральная введите единица 1. Единица в поле Интегральная означает вычисление функции распределения F(x). ОК.

1. В ячейку E31внесите формулу

для вычисления значения функции нормального распределения F(x1 = 2,93) = P(– ∞

Инструкция по подбору параметров нормального распределения в MS Excel

На лист MS Excel в таблицу результатов контроля заносятся следующие данные (рисунок Н.1):

— столбец 1 – истинные значения контролируемого параметра образцов;

— столбец 2 – количество попыток, в которых образец был признан соответствующим;

— столбец 3 – заранее рассчитанная вероятность признания образца соответствующим.

Рисунок Н.1 – Пример заполнения таблицы результатов контроля

Отдельно от таблицы результатов контроля на лист MS Excel заносятся приближенные значения параметров m и s нормального распределения
(см. рисунок Н.1), аппроксимирующего распределение вероятности признания образцов соответствующими.

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

— m определяется как истинное значение параметра образца , для которого вероятность признания образца соответствующим равна 0,5. Если образец, имеющий такое значение параметра, отсутствует, то m рассчитывается по формуле:

, (Н.1)

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

— s определяется по формуле:

. (Н.2)

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

;

.

В столбец 4 таблицы результатов контроля заносятся значения аппроксимирующего нормального распределения (рисунок Н.2), рассчитанные по формуле:

=НОРМРАСП( ;m;s;ИСТИНА) (Н.3)

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

m, s – номера ячеек, содержащих значения соответствующих параметров.

«ИСТИНА» – параметр, определяющий применение интегральной функции нормального закона распределения.

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

(Н.3)

где – номер ячейки, содержащей находящейся в той же строке, в столбце 4 таблицы результатов контроля;

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

В ячейку, соответствующую следующей после окончания таблицы строке и столбцу 5 таблицы результатов контроля заносится значение суммы столбца 5 с помощью кнопки и указания диапазона ячеек столбца 5 с помощью курсора мыши (рисунок Н.3).

Рисунок Н.2 – Схема заполнения столбца 4 таблицы результатов контроля

Рисунок Н.3 – Схема вычисления суммы столбца 5

Для поиска аппроксимирующего нормального распределения методом наименьших квадратов необходимо выбрать пункт «Сервис» главного меню, затем подпункт «Поиск решения» (рисунок Н.4). При том на экране отобразится диалоговое окно «Поиск решения» (рисунок Н.5).

Рисунок Н.4 – Выбор подпункта «Поиск решения»

Рисунок Н.5 – Диалоговое окно «Поиск решения»

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

Переключатель «Равной» следует установить на поле «минимальному значению» (см. рисунок Н.5).

В поле ввода «Изменяя ячейки» указываются номера ячеек, содержащих значения m и s.

В поле ввода «Ограничения:» вводится дополнительное ограничение. Для этого необходимо нажать кнопку «Добавить» справа от поля ввода «Ограничения:», после чего на экране отобразится диалоговое окно «Добавление ограничения» (рисунок Н.6).

В поле ввода «Ссылка на ячейку:» указывается номер ячейки, содержащей значение s.

В поле выбора знака с помощью раскрывающегося меню выбрать знак «>=« (рисунок Н.7).

Рисунок Н.6 – Диалоговое окно «Добавление ограничения»

Рисунок Н.7 – Выбор знака в окне «Добавление ограничения»

В поле ввода «Ограничение:» следует с клавиатуры набрать «0».

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

В диалоговом окне «Поиск решения» после заполнения всех поле ввода для подбора параметров нормального распределения следует нажать кнопку в верхнем правом углу диалогового окна. После завершения расчетов на экране отобразится окно «Результаты поиска решения» (рисунок Н.8).

Рисунок Н.8 – Диалоговое окно «Результаты поиска решения»

В диалоговом окне «Результаты поиска решения» следует выбрать опцию «Сохранить найденное решение» и нажать на кнопку в левом нижнем углу диалогового окна.

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

Функции распределения вероятности признания образцов соответствующими и аппроксимирующего нормального распределения можно отобразить графически с помощью диаграммы MS Excel.

Для этого следует, удерживая нажатой клавишу , мышью выделить столбцы 1, 3 и 4 таблицы результатов контроля.

Для построения диаграммы по выбранным столбцам необходимо выбрать пункт «Вставка» главного меню, затем подпункт «Диаграмма» (рисунок Н.9). При том на экране отобразится диалоговое окно «Мастер диаграмм» (рисунок Н.10).

Рисунок Н.9 – Выбор подпункта «Диаграмма»

Рисунок Н.10 – Диалоговое окно «Мастер диаграмм»

В диалоговом окне «Мастер диаграмм» следует выбрать тип диаграммы «Точечная» и вид «Точечная диаграмма со значениями, соединенными сглаживающими линиями» (см. рисунок Н.10).

После выбора нужного типа диаграммы рекомендуется нажать кнопку «Готово», после чего диаграмма будет отображена на листе MS Excel.

Приложение П
(обязательное)

Протокол анализа смещения и сходимости средства контроля № ______


Приложение Р
(рекомендуемое)

Таблица значений распределения Стьюдента

В таблице Р.1 приведены значения t-распределения (распределения Стьюдента).

Ссылка на основную публикацию
Adblock
detector