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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Унитаз am pm bliss l отзывы

Рис. 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 шаблон Excel, гистограмма, кривая распределения, критерий согласия Пирсона

Похожие статьи

Решение задач анализа и синтеза на имитационных моделях.

Значения случайной величины могут быть получены в результате статистических

Изучение работы новых машин и станков должно базироваться на статистических данных, собранных в

Рис. 2. Гистограммы для длительностей интервалов времени распиловки бревен на.

Вычисление статистических показателей с использованием.

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

Обработка результатов имитационного моделирования.

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

Критерий появления грубых ошибок.

По результатам моделирование построены гистограммы относительных отклонений оценок.

Прецизионный генератор псевдослучайных чисел

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

Аналитическая модель префиксного дерева на основе.

Первоначальный вид распределения значения индекса представлен гистограммой на рисунке 1

В силу аппроксимации значения индекса нормальным законом распределения, а также

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

Проверка статистических гипотез в психолого-педагогических.

Задачей анализа полученных данных является изучение частоты встречаемости тех или иных значения признака в эксперименте. Эти данные дают предварительную информацию о виде распределения признака: о том, какие значения встречаются реже, а какие чаще, насколько.

Анализ процедур генерации ключей криптографических алгоритмов.

Мы получили значение критерия «хи-квадрат» для экспериментальных данных.

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

Рассмотрим графический тест «Гистограмма распределения элементов».

Анализ методов распознавания образов | Статья в журнале.

Распределение – апостериорное распределение значений w, при условии получения

Пример распределения Гаусса для различных параметров µ и σ показан ниже.

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

Непараметрические робастные алгоритмы обработки данных

Проблема обработки данных, содержащих резко выделяющиеся значения, давно известна. Даже одно такое незамеченное значение может значительно снизить точность анализа данных, а иногда и совсем его обесценить. Представление о том, какие значения считать резко.

Идентификация многосвязных объектов в условиях частичной.

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

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

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

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

История закона насчитывает 300 лет. Первым открывателем стал Абрахам де Муавр, который придумал аппроксимацию биномиального распределения еще 1733 году. Через много лет Карл Фридрих Гаусс (1809 г.) и Пьер-Симон Лаплас (1812 г.) вывели математические функции.

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

Нормальный закон не является фиксированным уравнением зависимости одной переменной от другой. Фиксируется только характер этой зависимости. Конкретная форма распределения задается специальными параметрами. Например, у = аx + b – это уравнение прямой. Однако где конкретно она проходит и под каким наклоном, определяется параметрами а и b. Также и с нормальным распределением. Ясно, что это функция, которая описывает тенденцию высокой концентрации значений около центра, но ее точная форма задается специальными параметрами.

Читайте также:  Вывод долларов с вебмани

Кривая нормального распределения Гаусса имеет следующий вид.

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

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

Формула нормального распределения (плотности) следующая.

Формула состоит из двух математических констант:

π – число пи 3,142;

е – основание натурального логарифма 2,718;

двух изменяемых параметров, которые задают форму конкретной кривой:

m – математическое ожидание (в различных источниках могут использоваться другие обозначения, например, µ или a);

ну и сама переменная x, для которой высчитывается плотность вероятности.

Конкретная форма нормального распределения зависит от 2-х параметров: математического ожидания (m) и дисперсии ( σ 2 ). Кратко обозначается N(m, σ 2 ) или N(m, σ). Параметр m (матожидание) определяет центр распределения, которому соответствует максимальная высота графика. Дисперсия σ 2 характеризует размах вариации, то есть «размазанность» данных.

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

А вот дисперсия определяет остроконечность кривой. Когда данные имеют малый разброс, то вся их масса концентрируется у центра. Если же у данных большой разброс, то они «размазываются» по широкому диапазону.

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

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


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

P(a ≤ X 0 =1 и остается рассчитать только соотношение 1 на корень из 2 пи.

Таким образом, по графику хорошо видно, что значения, имеющие маленькие отклонения от средней, выпадают чаще других, а те, которые сильно отдалены от центра, встречаются значительно реже. Шкала оси абсцисс измеряется в стандартных отклонениях, что позволяет отвязаться от единиц измерения и получить универсальную структуру нормального распределения. Кривая Гаусса для нормированных данных отлично демонстрирует и другие свойства нормального распределения. Например, что оно является симметричным относительно оси ординат. В пределах ±1σ от средней арифметической сконцентрирована большая часть всех значений (прикидываем пока на глазок). В пределах ±2σ находятся большинство данных. В пределах ±3σ находятся почти все данные. Последнее свойство широко известно под названием правило трех сигм для нормального распределения.

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

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

Таблица нормального распределения

Таблицы нормального распределения встречаются двух типов:

— таблица плотности;

— таблица функции (интеграла от плотности).

Таблица плотности используется редко. Тем не менее, посмотрим, как она выглядит. Допустим, нужно получить плотность для z = 1, т.е. плотность значения, отстоящего от матожидания на 1 сигму. Ниже показан кусок таблицы.

В зависимости от организации данных ищем нужное значение по названию столбца и строки. В нашем примере берем строку 1,0 и столбец , т.к. сотых долей нет. Искомое значение равно 0,2420 (0 перед 2420 опущен).

Функция Гаусса симметрична относительно оси ординат. Поэтому φ(z)= φ(-z), т.е. плотность для 1 тождественна плотности для -1, что отчетливо видно на рисунке.

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

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

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

Функция Ф(z) симметрична относительно своего значения 0,5 (а не оси ординат, как плотность). Отсюда справедливо равенство:

Это факт показан на картинке:

Значения функции Ф(-z) и Ф(z) делят график на 3 части. Причем верхняя и нижняя части равны (обозначены галочками). Для того, чтобы дополнить вероятность Ф(z) до 1, достаточно добавить недостающую величину Ф(-z). Получится равенство, указанное чуть выше.

Если нужно отыскать вероятность попадания в интервал (0; z), то есть вероятность отклонения от нуля в положительную сторону до некоторого количества стандартных отклонений, достаточно от значения функции стандартного нормального распределения отнять 0,5:

Для наглядности можно взглянуть на рисунок.

На кривой Гаусса, эта же ситуация выглядит как площадь от центра вправо до z.

Довольно часто аналитика интересует вероятность отклонения в обе стороны от нуля. А так как функция симметрична относительно центра, предыдущую формулу нужно умножить на 2:

Читайте также:  Error may have been discarded

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

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

Для облегчения задачи в учебниках обычно публикуют таблицы для функции вида:

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

Теперь посмотрим на конкретные примеры. Ниже показана таблица стандартного нормального распределения. Найдем табличные значения для трех z: 1,64, 1,96 и 3.

Как понять смысл этих чисел? Начнем с z=1,64, для которого табличное значение составляет 0,4495. Проще всего пояснить смысл на рисунке.

То есть вероятность того, что стандартизованная нормально распределенная случайная величина попадет в интервал от до 1,64, равна 0,4495. При решении задач обычно нужно рассчитать вероятность отклонения в обе стороны, поэтому умножим величину 0,4495 на 2 и получим примерно 0,9. Занимаемая площадь под кривой Гаусса показана ниже.

Таким образом, 90% всех нормально распределенных значений попадает в интервал ±1,64σ от средней арифметической. Я не случайно выбрал значение z=1,64, т.к. окрестность вокруг средней арифметической, занимающая 90% всей площади, иногда используется для проверки статистических гипотез и расчета доверительных интервалов. Если проверяемое значение не попадает в обозначенную область, то его наступление маловероятно (всего 10%).

Для проверки гипотез, однако, чаще используется интервал, накрывающий 95% всех значений. Половина вероятности от 0,95 – это 0,4750 (см. второе выделенное в таблице значение).

Для этой вероятности z=1,96. Т.е. в пределах почти ±2σ от средней находится 95% значений. Только 5% выпадают за эти пределы.

Еще одно интересное и часто используемое табличное значение соответствует z=3, оно равно по нашей таблице 0,4986. Умножим на 2 и получим 0,997. Значит, в рамках ±3σ от средней арифметической заключены почти все значения.

Так выглядит правило 3 сигм для нормального распределения на диаграмме.

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

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

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

Функция НОРМ.СТ.РАСП

Функция НОРМ.СТ.РАСП предназначена для расчета плотности ϕ( z ) или вероятности Φ(z) по нормированным данным (z).

z – значение стандартизованной переменной

интегральная – если 0, то рассчитывается плотность ϕ( z ) , если 1 – значение функции Ф(z), т.е. вероятность P(Z

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

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

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

xi mi n∙pi
карманы частота теоретическая частота статистика U
x1 m1 n∙p1
x2 m2 n∙p2
xk mk n∙pk

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

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

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

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

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

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

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

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

Заполните диапазон ячеек Е33:Е40 результатами вычисления этой формулы, используя маркер заполнения, чтобы получить в ячейках , , … , теоретическое (ожидаемое) число случайных величин, попавших в каждый частный интервал.

Рис. 9. Столбец n∙pi(E31;E41) содержит результаты вычисления теоретических значений числа случайных величин попавших в каждый частичный интервал (карман) n∙pi

3. В ячейку E41 поместите формулу для вычисления теоретического (гипотетического) числа случайных величин, попавших в промежуток (x10; ∞):

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Для студентов недели бывают четные, нечетные и зачетные. 9909 — | 7692 — или читать все.

Ссылка на основную публикацию
Форге оф импаерс великие строения
Другое название: Кузница Империй Ниже мы приводим подробный гайд по игре Forge of Empires с советами как вам быстрее отстроить...
Троттлинг процессора что это
Простой компьютерный блог для души) Всем привет. Сегодня мы затронем тему процессоров, а если быть точнее, то такое явление как...
Троянские программы и хакерские утилиты
В данную категорию входят программы, осуществляющие различные несанкционированные пользователем действия: сбор информации и ее передачу злоумышленнику, ее разрушение или злонамеренную...
Форза хорайзен 3 список машин
Серия игр Forza всегда поражала количеством доступных автомобилей. На момент выхода игры доступно уже более 150 автомобилей, а разработчики обещают...
Adblock detector