Нормстрасп в excel - IT Справочник
Llscompany.ru

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

Нормстрасп в excel

Функция НОРМСТРАСП(Z)

Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент — Z, вычисляемый функцией НОРМАЛИЗАЦИЯ().

Нетрудно заметить,что эти функции следует использовать вместе. При этом наиболее эффективным и компактным способом их задания является указаниефункции НОРМАЛИЗАЦИЯ() в качестве аргумента функции — НОРМСТРАСП(), т.е.:

=НОРМСТРАСП(НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)).

С целью повышения наглядности, в проектируемом шаблоне функции заданы раздельно (ячейки Е18 и F18).

Сформируйте данный шаблон и сохраните его на магнитном диске под именем SIMUL_1. Приступаем к имитационному эксперименту. Для его проведения необходимо выполнить следующие шаги.

1. Ввести значения постоянных переменных (табл. 1.2) в ячейки В2:В4 и D2:D4 листа «Результаты анализа».

2. Ввести значения диапазонов изменений ключевых переменных (табл. 1.1) в ячейки В3:С5 листа «Имитация».

3. Нажатием клавиши F9 провести расчет.

4. Перейти к листу «Результаты анализа» и проанализировать полученные результаты.

Результатом выполнения этих действий будет заполнение блока А10:Е510случайными значениями ключевых переменных V, Q, P и результатами вычислений величин NCF и NPV. Фрагмент результатов имитации приведен на рис. 1.3. Соответствующие проведенному эксперименту результаты анализа приведены на рисунке 1.4.

Рис. 1.3- Результаты имитации

Рис. 1.4 — Результаты анализа

Сумма всех отрицательных значений NPV в полученной генеральной совокупности (ячейка F14) может быть интерпретирована как чистая стоимость неопределенности для инвестора в случае принятия проекта. Аналогично сумма всех положительных значений NPV (ячейка F15) может трактоваться как чистая стоимость неопределенности для инвестора в случае отклонения проекта. Несмотря на всю условность этих показателей, в целом они представляют собой индикаторы целесообразности проведения дальнейшего анализа.

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

На практике одним из важнейших этапов анализа результатов имитационного эксперимента является исследование зависимостей между ключевыми параметрами. Ограничимся визуальным (графическим) исследованием. На рисунке 1.5 приведен график распределения значений ключевых параметров V, P и Q, построенный на основании 75 имитаций.

Нетрудно заметить, что в целом, вариация значений всех трех параметров носит случайный характер, что подтверждает принятую ранее гипотезу об их независимости. Для сравнения ниже приведен график распределений потока платежей NCF и величины NPV (рисунок 1.6).

Рис. 1.5 Распределение значений параметров V, P и Q

Рис. 1.6 — Зависимость между NCF и NPV

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Формула чистнз в excel

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

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

III. Статистические функции НОРМАЛИЗАЦИЯ() и НОРМСТРАСП() в пакете прикладных программ EXCEL

В рассматриваемом примере мы исходим из предположения о независимости и равномерном распределении ключевых переменных Q, V, P. Однако какое распределение при этом будет иметь результатная величина — показатель NPV, заранее определить нельзя.

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

В прикладном анализе для целей аппроксимации широко применяется частный случай нормального распределения — т.н. стандартное нормальное распределение. Математическое ожидание стандартно распределенной случайной величины Е равно 0: M(E) = 0. График этого распределения симметричен относительно оси ординат и оно характеризуется всего одним параметром — стандартным отклонением , равным 1.

Приведение случайной переменной E к стандартно распределенной величине Z осуществляется с помощью т.н. нормализации — вычитания средней и последующего деления на стандартное отклонение:

(1).

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

В ППП EXCEL подобные вычисления осуществляются с помощью статистических функций НОРМАЛИЗАЦИЯ() и НОРМСТРАСП().

Функция НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)

Эта функция возвращает нормализованное значение Z величины x, на основании которого затем вычисляется искомая вероятность p(E x). Она реализует соотношение (1). Функция требует задания трех аргументов:

х — нормализуемое значение;

среднее — математическое ожидание случайной величины Е;

станд_откл — стандартное отклонение.

Полученное значение Z является аргументом для следующей функции — НОРМСТРАСП().

Эта функция возвращает стандартное нормальное распределение, т.е. вероятность того, что случайная нормализованная величина Е будет меньше или равна х. Она имеет всего один аргумент — Z, вычисляемый функцией НОРМАЛИЗАЦИЯ().

Нетрудно заметить, что эти функции следует использовать в тандеме. При этом наиболее эффективным и компактным способом их задания является указание функции НОРМАЛИЗАЦИЯ() в качестве аргумента функции — НОРМСТРАСП(), т.е.:

=НОРМСТРАСП(НОРМАЛИЗАЦИЯ(x; среднее; станд_откл)).

Шаблон Excel для проверки законов распределения данных наблюдений по критерию согласия Пирсона

Рубрика: Экономика и управление

Дата публикации: 30.03.2019 2019-03-30

Статья просмотрена: 3980 раз

Библиографическое описание:

Фаюстов, А. А. Шаблон Excel для проверки законов распределения данных наблюдений по критерию согласия Пирсона / А. А. Фаюстов. — Текст : непосредственный, электронный // Молодой ученый. — 2019. — № 13 (251). — С. 142-147. — URL: https://moluch.ru/archive/251/57618/ (дата обращения: 06.04.2020).

Читать еще:  Копирование ячеек в excel vba

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

Ключевые слова: шаблон Excel, гистограмма, кривая распределения, критерий согласия Пирсона

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

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

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

Эта задача представляет собой частный случай более общей проблемы, заключающейся в подборе теоретической функции распределения, в некотором смысле наилучшим образом согласующейся с опытными данными. Сама процедура проверки нормальности распределения относится к распространенной стандартной и довольно тривиальной задаче обработки данных и достаточно подробно и широко описана в различной литературе по метрологии и статистической обработке данных измерений [2- 4].

Данные, получаемые в результате измерений при контроле технологических процессов, оценке характеристик различных объектов и др. для дальнейшей обработки желательно представлять в виде теоретического распределения, максимально соответствующего экспериментальному распределению. Проверку гипотезы о виде функции распределения в настоящее время проводят по различным критериям согласия — Пирсона, Колмогорова, Смирнова и другим в соответствии с новыми разработанными нормативными документами — рекомендациями по стандартизации [5, 6].

Наиболее часто используется критерий Пирсона  2 . Однако применение критериев согласия требует обычно довольно значительного объёма данных. Так, критерий Пирсона обычно рекомендуется использовать при объёме выборки не менее 50…100. Поэтому при небольшом объёме выборки проверку гипотезы о виде функции распределения проводят приближёнными методами — графическим методом или по асимметрии и эксцессу. Применение критерия Пирсона для ручной обработки данных очень подробно было изложено в известной работе [2]. Как свидетельствует опыт проверок согласия экспериментальных данных с теоретическими по различным критериям, эта процедура является очень трудоемкой, требует некоторой усидчивости и особого внимания при обработке от исследователя, как правило, не исключает ошибок в работе и не вызывает особого энтузиазма у выполняющего эту работу.

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

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

Рассмотрим порядок действий при работе с критерием Пирсона в среде Excel.

1. Полученные в результате измерений значения 100 случайных результатов измерений внести в ячейки A1:A100 шаблона Excel и приступить к построению гистограммы на основе данных, назначая длину интервала (карман) и выбирая необходимое число интервалов.

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

Читать еще:  Функция пусто в excel

Рис. 1. Фрагмент таблицы с исходными данными

В ячейку D2 вносится формула =СРЗНАЧ(A1:A100), D3: =СТАНДОТКЛОН(A1:A100), D4: =МИН(A1:A100), D5: =МАКС(A1:A100), D6: =D5-D4, D7: =D6/D8. В ячейку D8 вводится число интервалов, которое для числа измерений, равным 100, может быть принято от 7 до 12.

Для оценки оптимального для нашего массива данных количества интервалов можно воспользоваться формулой Стерджесса: k

1+3,322lgN, где N— количество всех значений величины. Например, для N = 100, n = 7,6, которое должно быль округлено до целого числа, округляем до n = 8.

3. Интервал карманов вычисляют так: разность максимального и минимального значений массива, деленная на количество интервалов: .

4. Теперь в каждой ячейке шаг за шагом прибавляем полученное значение ширины кармана: сначала к минимальному значению нашего массива (ячейка D4), затем в следующей ячейке ниже — к полученной сумме и т. д. Так постепенно доходим до максимального значения. Таким образом, мы и построили интервалы карманов в виде столбца значений.

Интервалом считается следующий диапазон: (i-1; i] или i Научный журнал “Молодой Ученый” в социальных сетях:

Bootstrapping 2 — анализ нормального распределения в Excel

Ранее мы рассмотрели, как рассчитать прогноз редких продаж в Excel по методу Bootstrap подробности вы можете прочитать в статье по этой ссылке. П рогноз мы рассчитали с учетом того, что распределение нормально.

В данной статье мы выделим фактическое распределение и за счет этого повысим точность прогноза.

Сегодня вы узнаете, как в Excel быстро проанализировать распределение и уточнить прогноз.

Итак, в предыдущей статье мы:

  • рассчитали прогноз, вытащив случайным образом из ряда с редкими продажами по месяцам 12 чисел и
  • сделали это 10 000 раз,
  • рассчитали среднее по каждому из 10 000 рядов и
  • среднее по средним, получили прогноз на месяц,
  • Дальше среднее по средним умножили на 3 и получили прогноз на 3 месяца.

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

  • Выделим среднее значение по 10 000 рядам выборки;
  • Построим сводную таблицу по выделенным средним;
  • Сгруппируем ряд средних;
  • Выведем в сводную таблицу по группам следующие показатели: количество, среднее, сумму, максимум, минимум.
  • Рассчитаем прогноз на 6 месяцев.
  1. Выделим среднее значение по 10 000 рядам выборки. Для этого устанавливаете курсор в заголовок «Среднее», и нажимаете Ctrl+Shift+стрелочку вниз:

Далее ставим галочку «на существующий лист» и нажимаем «ОК»

3. Сгруппируем ряд средних.

Для этого перетянем средние в область «Строк» сводной таблицы:

Нажимаем правой кнопкой мыши на сводной таблице в столбце со средними и выберем пункт «Группировать»

В появившемся окне мы можем задать параметры группировки – с какого по какое значение группировать с каким шагом. Берем значения по умолчанию и нажимаем «ОК»:

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

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

Перетаскиваем среднее 5 раз в область значений сводной таблицы:

Задаем показатели (количество, среднее, сумму, максимум, минимум) в «параметрах полей значений»

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

Получаем следующую таблицу:

Видим, что 5 375 рядов в диапазоне от 1-2:

Сделаем еще раз группировку, только шаг поставим 2

Видим, что 7 379 рядов из 10 000 в диапазоне от 0-2 – 73% наблюдений. И так мы от нормального распределения перешли к фактическому и опираясь на него сделаем прогноз.

Теперь мы получили 96% наблюдений в диапазоне от 0 до 3.

5. Рассчитаем прогноз на 6 месяцев.

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

Среднее значение ряда по группе с 96% наблюдений у нас получился 1,49, умножим на 6 месяцев, получаем уточненный прогноз на 6 месяцев 8,96, т.е. можем планировать, что нам понадобится 9 шт. данного товара:

Столбцы «Максимум» и «Минимум» — границы прогноза.

Точных вам прогнозов!

Программа Forecast4AC PRO (начиная с версии 4.7) умеет автоматически использовать модель Bootstrap при расчете прогноза на больших массивах данных! Если один из рядов с нерегулярными продажами, программа это понимает и строит прогноз, используя метод Bootstrap!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector