Oc-windows.ru

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

Проверить равномерность распределения в excel

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

Построение графика проверки распределения на нормальность ( Normal Probability Plot ) является графическим методом определения соответствия значений выборки нормальному распределению.

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

Рассмотренный ниже графический метод основан на субъективной визуальной оценке данных. Объективным же подходом является, например, анализ степени согласия гипотетического распределения с наблюдаемыми данными (goodness-of-fit test), который рассмотрен в статье Проверка простых гипотез критерием Пирсона ХИ-квадрат .

Из-за наличия неустранимой статистической ошибки выборки, присущей случайной величине, невозможно однозначно ответить на вопрос «Взята ли данная выборка из нормального распределения или нет». Поэтому, рассмотренный графический метод, скорее, дает ответ на вопрос «Разумно ли предположение, что оцениваемая выборка взята из нормального распределения »?

Рассмотрим алгоритм построения графика проверки распределения на нормальность ( Normal Probability Plot ) :

  • Отсортируйте значения выборки по возрастанию (значения выборки x j будут отложены по горизонтальной оси Х);
  • Каждому значению x jвыборки поставьте в соответствие значения (j-0,5)/n, где n – количество значений в выборке , j – порядковый номер значения от 1 до n. Этот массив будет содержать значения от 0,5/n до (n-0,5)/n. Таким образом, диапазон от 0 до 1 будет разбит на равномерные отрезки. Этот диапазон соответствует вероятности наблюдения значений случайной величины Z НОРМ.СТ.ОБР() и отложим их по вертикальной оси Y.

Если значения выборки , откладываемые по оси Х, взяты из стандартного нормального распределения , то на графике мы получим приблизительно прямую линию, проходящую примерно через 0 и под углом 45 градусов к оси х (если масштабы осей совпадают).

Расчеты и графики приведены в файле примера на листе Нормальное . О построении диаграмм см. статью Основные типы диаграмм в MS EXCEL .

Примечание : Значения выборки в файле примера сгенерированы с помощью формулы =НОРМ.СТ.ОБР(СЛЧИС()) . При перерасчете листа или нажатии клавиши F9 происходит обновление данных в выборке . О генерации чисел, распределенных по нормальному закону см. статью Нормальное распределение. Непрерывные распределения в MS EXCEL . Таже значения выборки могут быть сгенерированы с помощью надстройки Пакет анализа .

Если значения выборки взяты из нормального распределения (μ не обязательно равно 0, σ не обязательно равно 1), то угол наклона кривой даст оценку стандартного отклонения σ, а ордината точки пересечения оси Y – оценку среднего значения μ.

Данные оценки несколько отличаются от оценок параметров, полученных с помощью функций СРЗНАЧ() и СТАНДОТКЛОН.В() , т.к. они получены методом наименьших квадратов , рассмотренного в статье про регрессионный анализ.

Примечание : Рассмотренный выше метод в отечественной литературе имеет название Метод номограмм . Номограмма – это листы бумаги, разлинованные определенным образом. Номограмма используется в различных областях знаний. В математической статистике номограмма называется вероятностной бумагой. Такую «вероятностную бумагу» мы практически построили самостоятельно, когда нелинейно изменили масштаб шкалы ординат: =НОРМ.СТ.ОБР((j-0,5)/n)

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

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

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

Проверка гипотезы о нормальном распределении

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

  1. Сравнение характеристик набора данных со свойствами нормального распределения.
  2. Построение специального графика на основе набора данных.

Оценка свойств

Напомним, что нормальное распределение является симметричным и колоколообразным, так что все характеристики его среднего значения — математическое ожидание, мода и медиана — совпадают друг с другом. Межквартильный размах нормального распределения равен 1,33 стандартного отклонения. Нормальное распределение является непрерывным, причем нормально распределенная случайная величина принимает произвольные значения, лежащие на всей числовой оси.

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

На практике характеристики набора данных могут немного отличаться от теоретических, либо потому, что случайная величина является лишь приближенно нормальной, либо потому, что ее реальные свойства отличаются от предполагаемых. В таких ситуациях кривая распределения оказывается не совсем симметричной и колоколообразной. Оценки математического ожидания могут слегка отличаться от теоретических, а межквартильный размах может не быть равным 1,33 стандартного отклонения. Кроме того, на практике диапазон изменения данных не может быть бесконечным — как правило, он ограничен шестью стандартными отклонениями. Такие распределения являются приближенно нормальными.

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

Читать еще:  Видеоредактор для монтажа видео онлайн

Рис. 1. Оценки, полученные студентами при сдаче четырех тестов; мода зачеркнута, так как не имеет смысла

Приблизительно нормальным является распределение оценок только по первому тесту: математическое ожидание равно медиане, доля наблюдений в пределах окрестности ±1σ от математического ожидания составляет 68% (в точности, как и для нормального распределения), асимметричность = 0.

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

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

Для вычисления квантилей используется следующее правило (рис. 2): i-ый квантиль стандартизованного нормального распределения Qi представляет собой стандартизованную нормально распределенную величину Z, которой соответствует площадь фигуры, лежащей под кривой плотности вероятностей, равная i/(n+1).

Рис. 2. Расчет квантилей в Excel

График нормального распределения строится в Excel на основе точечного графика, на вертикальной оси которого отложены значения наблюдаемых данных, а на горизонтальной оси — соответствующие квантили стандартизованного нормального распределения (рис. 3). В отличие от описательных статистик, для построения графиков данные должны быть упорядочены по возрастанию. Если точки, соответствующие наблюдаемым данным, образуют прямую, проведенную из левого нижнего угла в правый верхний угол, значит, данные распределены приближенно нормально. С другой стороны, если эти точки отклоняются от прямой линии, распределение данных отличается от нормального.

Рис. 3. Графики распределений для четырех тестов

График «Тест 1» свидетельствует, что наблюдаемые точки лежат очень близко к прямой линии, поэтому можно считать, что оценки, полученные студентами при сдаче первого теста, распределены практически нормально. Обратите внимание на полигон (кривую плотности распределения) и блочную диаграмму, изображенные на рис. 4, панель А.

Рис. 4. Четыре распределения, исследованные с помощью блочных диаграмм

«Тест 2» (рис. 3): точки значительно отклоняются от прямой линии. Значения случайной переменной сначала возрастают довольно резко, а затем их рост становится умеренным. Этот рисунок соответствует распределению с отрицательной асимметрией, о чем свидетельствует более длинный левый хвост распределения. Обратите внимание на соответствующие полигон и блочную диаграмму, изображенные на рис. 4, панель Б. «Тест 3»: наблюдается противоположная картина. Значения случайной переменной сначала возрастают довольно медленно, а затем их рост становится более заметным. Этот рисунок соответствует распределению с положительной асимметрией, о чем свидетельствует более длинный правый хвост распределения. Обратите внимание на соответствующие полигон и блочную диаграмму, изображенные на рис. 4, панель В. «Тест 4»: изображен симметричный график, средняя часть которого почти линейна. Значения случайной переменной сначала довольно медленно возрастают, затем их рост прекращается, а в третьей части — ускоряется. Этот рисунок не совпадает ни с панелью Б, ни с панелью В. Это распределение не имеет хвостов. Следовательно, оно является равномерным (или прямоугольным). Обратите внимание на соответствующие полигон и блочную диаграмму, изображенные на рис. 4, панель Г.

[1] Используются материалы книги Левин и др. Статистика для менеджеров. – М.: Вильямс, 2004. – с. 368–375

НОРМСТРАСП функция стандартного нормального распределения в Excel

Функция НОРМСТРАСП в Excel используется для нахождения значения статистической функции стандартного нормального распределения. Рассмотрим примеры использования данной функции и самостоятельно составим таблицу нормального закона.

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

В новых версиях Microsoft Office была введена более универсальная функция =НОРМ.СТ.РАСП(), содержащая дополнительный аргумент, который принимает два возможных значения:

  • ИСТИНА – для получения интегральной функции распределения;
  • ЛОЖЬ – для получения весовой функции распределения.

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

Главные особенности функции:

  1. Площадь участка, ограниченного кривой и осью абсцисс принята за 1.
  2. Стандартное отклонение считается равным 1.
  3. Среднее арифметическое значение принято равным 0.
  4. В функцию f(x) общего теоретического нормального распределения введена переменная z (стандартная нормальная).

Переменная z рассчитывается по формуле:

  • X – значение некоторой случайной величины;
  • µ — среднее значение;
  • ó — значение стандартного отклонения.

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

Функция НОРМСТРАСП возвращает результат, рассчитанный на основе следующей формулы:

Именно так и выглядит алгоритм вычисления функции НОРМСТРАСП в Excel

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

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

Вид таблицы данных:

Для расчетов используем следующую формулу:

  • A2:A11 – диапазон ячеек, содержащих значения переменной z.

С принципом действия функции мы ознакомились. Теперь ничто нам не мешает составить свою таблицу стандартного распределения в Excel. Для этого построим шаблон таблицы нормального закона и заполним ее ячейки формулой со смешанными ссылками:

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

Читать еще:  Легендой диаграммы ms excel является

Расчет вероятности стандартным нормальным распределением в Excel

Пример 2. На заводе изготавливают лампочки. Средний период бесперебойной работы каждой лампы составляет 1000 ч. Стандартное отклонение от срока службы составляет 50 ч. Определить вероятность для каждого из указанных случаев:

  1. Купленная лампа будет работать не более 1200 ч.
  2. Срок службы составит менее 800 ч.
  3. Количество ламп в партии из 500 шт., которые проработают от 900 до 1100 часов.

Вид таблицы данных:

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

(1200-B2)/B3 – выражение для расчета переменной z.

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

Аналогично рассчитаем вероятность того, что срок службы составит менее 800 часов:

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

Нормальное распределение является симметричным относительно оси ординат, поэтому функция НОРМСТРАСП может вычислить значение даже для отрицательного z.

Для определения числа ламп, которые проработают 900-1100 часов, используем формулу:

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

Описание параметров функции НОРМСТРАСП в Excel

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

  • z – единственный аргумент, обязательный для заполнения, принимающий числовое значение стандартной нормальной переменной.
  1. В качестве аргумента z может быть передано числовое значение, преобразуемый в число текст, логическое значение (например, результат выполнения функции =НОРМСТРАСП(ИСТИНА) будет число 0,841, поскольку данная функция выполняет промежуточное преобразование логического ИСТИНА в число 1), ссылка на ячейку с числовыми данными.
  2. Если функция НОРМСТРАСП получила в качестве аргумента текст, не преобразуемый в числовые данные, она вернет код ошибки #ЗНАЧ!.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как запаролить отдельные ячейки в excel

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

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

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

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

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

Рис. 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, при условии получения

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

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

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

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

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

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

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

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