Как выполнить тест на нормальность в Excel (шаг за шагом)
Многие статистические тесты предполагают, что значения в наборе данных имеют нормальное распределение .
Один из самых простых способов проверить это предположение — выполнить тест Харке-Бера , который представляет собой тест согласия, который определяет, имеют ли выборочные данные асимметрию и эксцесс, соответствующие нормальному распределению.
В этом тесте используются следующие гипотезы:
H 0 : Данные нормально распределены.
H A : Данные не распределены нормально.
Тестовая статистика JB определяется как:
JB = (n/6) * (S 2 + (C 2 /4))
При нулевой гипотезе нормальности JB ~ X 2 (2).
Если значение p , соответствующее тестовой статистике, меньше некоторого уровня значимости (например, α = 0,05), то мы можем отклонить нулевую гипотезу и сделать вывод, что данные не распределены нормально.
В этом руководстве представлен пошаговый пример того, как выполнить тест Харке-Бера для заданного набора данных в Excel.
Шаг 1: Создайте данные
Во-первых, давайте создадим поддельный набор данных с 15 значениями:
Шаг 2: Рассчитайте тестовую статистику
Затем рассчитайте статистику теста JB. В столбце E показаны используемые формулы:
Тестовая статистика оказывается 1,0175 .
Шаг 3: Рассчитайте P-значение
При нулевой гипотезе нормальности тестовая статистика JB следует распределению хи-квадрат с 2 степенями свободы.
Итак, чтобы найти p-значение для теста, мы будем использовать следующую функцию в Excel: =CHISQ.DIST.RT(статистика теста JB, 2)
Значение p теста составляет 0,601244.Поскольку это p-значение не меньше 0,05, мы не можем отвергнуть нулевую гипотезу. У нас нет достаточных доказательств того, что набор данных не имеет нормального распределения.
Другими словами, мы можем предположить, что данные распределены нормально.
Проверка распределения на нормальность в EXCEL
Предположим, что имеется некий набор данных. Требуется оценить, соответствует ли данная выборка нормальному распределению .
Рассмотренный ниже графический метод основан на субъективной визуальной оценке данных. Объективным же подходом является, например, анализ степени согласия гипотетического распределения с наблюдаемыми данными (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 j ;
- Преобразуем значения массива, полученные на предыдущем шаге, с помощью обратной функциистандартного нормального распределения НОРМ.СТ.ОБР() и отложим их по вертикальной оси Y.
Если значения выборки , откладываемые по оси Х, взяты из стандартного нормального распределения , то на графике мы получим приблизительно прямую линию, проходящую примерно через 0 и под углом 45 градусов к оси х (если масштабы осей совпадают).
Расчеты и графики приведены в файле примера на листе Нормальное . О построении диаграмм см. статью Основные типы диаграмм в MS EXCEL .
Примечание : Значения выборки в файле примера сгенерированы с помощью формулы =НОРМ.СТ.ОБР(СЛЧИС()) . При перерасчете листа или нажатии клавиши F9 происходит обновление данных в выборке . О генерации чисел, распределенных по нормальному закону см. статью Нормальное распределение. Непрерывные распределения в MS EXCEL . Таже значения выборки могут быть сгенерированы с помощью надстройки Пакет анализа .
Если значения выборки взяты из нормального распределения (μ не обязательно равно 0, σ не обязательно равно 1), то угол наклона кривой даст оценку стандартного отклонения σ, а ордината точки пересечения оси Y – оценку среднего значения μ.
Данные оценки несколько отличаются от оценок параметров, полученных с помощью функций СРЗНАЧ() и СТАНДОТКЛОН.В() , т.к. они получены методом наименьших квадратов , рассмотренного в статье про регрессионный анализ.
Примечание : Рассмотренный выше метод в отечественной литературе имеет название Метод номограмм . Номограмма – это листы бумаги, разлинованные определенным образом. Номограмма используется в различных областях знаний. В математической статистике номограмма называется вероятностной бумагой. Такую «вероятностную бумагу» мы практически построили самостоятельно, когда нелинейно изменили масштаб шкалы ординат: =НОРМ.СТ.ОБР((j-0,5)/n)
Интересно посмотреть, как будут выглядеть на диаграмме данные, полученные из выборок из других распределений (не из нормального ). В файле примера на листе Равномерное приведен график, построенный на основе выборки из непрерывного равномерного распределения.
Очевидно, что значения выборки совсем не ложатся на прямую линию и предположение о нормальности выборки должно быть отвергнуто.
Подобная визуальная проверка выборки на соответствие другим распределениям может быть сделана при наличии соответствующих обратных функций . В статье Статистики, их выборочные распределения и точечные оценки параметров распределений в MS EXCEL приведены графики для следующих распределений: Стьюдента , ХИ-квадрат распределения , F-распределения . Подобный график также приведен в статье про распределение Вейбулла .
Проверка выборки на нормальность и расчет корреляционного отношения в среде MS Excel и VBA Текст научной статьи по специальности «Компьютерные и информационные науки»
НОРМАЛЬНОСТЬ ВЫБОРКИ / КОРРЕЛЯЦИОННОЕ ОТНОШЕНИЕ / МАКРОС / ФУНКЦИЯ ПОЛЬЗОВАТЕЛЯ / МЕДИАНА / ЭКСЦЕСС / КОЭФФИЦИЕНТ ДЕТЕРМИНАЦИИ / NORMALITY / CORRELATION RELATIONSHIP / MACROS / CUSTOM FUNCTION / MEDIAN / KURTOSIS / THE COEFFICIENT OF DETERMINATION
Аннотация научной статьи по компьютерным и информационным наукам, автор научной работы — Зинюк Ольга Викторовна
В статье рассматривается методика проверки выборки на нормальность и расчета корреляционного отношения с использованием средств автоматизации обработки данных MS Excel и VBA, таких как создание макросов и пользовательских функций, с целью ее универсального использования в экономико-статистических исследованиях.
i Надоели баннеры? Вы всегда можете отключить рекламу.
Похожие темы научных работ по компьютерным и информационным наукам , автор научной работы — Зинюк Ольга Викторовна
Методика расчета ошибки и однородности выборки средствами MS Excel и VBA
Формирование ассортимента обуви на базе экономико-статистического анализа выборок
Оценка достоверности экспериментальных данных для биологической очистки сточных вод с использованием системы Statistica
Статистический анализ данных в среде wxMaxima
Описательная статистика с использованием пакетов статистических программ Statistica и SPSS
i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.
i Надоели баннеры? Вы всегда можете отключить рекламу.
CHECK SAMPLE FOR THE NORMALITY AND CALCULATION CORRELATION RELATIONSHIPS IN MS EXCEL AND VBA
The article discusses method of test sample for the normality and calculate the correlation relationship with the use of automated data processing of MS Excel and VBA, such as creating macros and custom functions, with a view to its universal use in economy-statistical studies.
Текст научной работы на тему «Проверка выборки на нормальность и расчет корреляционного отношения в среде MS Excel и VBA»
ПРОВЕРКА ВЫБОРКИ НА НОРМАЛЬНОСТЬ И РАСЧЕТ КОРРЕЛЯЦИОННОГО ОТНОШЕНИЯ В СРЕДЕ MS EXCEL И VBA
Ольга Викторовна Зинюк
к. т.н., доцент кафедры дизайна и режиссуры в рекламе Московского гуманитарного университета Тел. 8-916-130-86-94, Эл. почта: ol gazi nyu k@rambl er. ru
В статье рассматривается методика проверки выборки на нормальность и расчета корреляционного отношения с использованием средств автоматизации обработки данных MS Excel и VBA, таких как создание макросов и пользовательских функций, с целью ее универсального использования в экономико-статистических исследованиях.
Ключевые слова: нормальность выборки, корреляционное отношение, макрос, функция пользователя, медиана, эксцесс, коэффициент детерминации.
PtD, Associate Professor of Chair of design and directions in advertisement in Moscow University for the Humanities Tel. 8-916-130-86-94, E-mail: olgazinyuk@rambler.ru
CHECK SAMPLE FOR THE NORMALITY AND CALCULATION CORRELATION RELATIONSHIPS IN MS EXCEL AND VBA
The article discusses method of test sample for the normality and calculate the correlation relationship with the use of automated data processing of MS Excel and VBA, such as creating macros and custom functions, with a view to its universal use in economy-statistical studies.
Keywords: normality, correlation relationship, macros, custom function, median, kurtosis, the coeffici ent of determination.
Совместный анализ выборок, полученных в результате экономико-статистических исследований, требует решения вопроса о выборе параметрических или непараметрических критериев статистики для оценки их взаимосвязи [1].
Выбор параметрических критериев методов математической статистики основывается на предположении о том, что распределение выборок подчиняется нормальному (гауссовому) закону распределения, в связи с чем одной из задач статистического анализа является проверка вида распределения выборок [2].
В работе представлена методика проверки выборки на нормальность и определения корреляционного отношения с использованием средств автоматизации расчетов MS Excel и VBA. Представленная методика дает возможность максимально автоматизировать операции расчета данных и может быть использована для выборок любого объема в статистических исследованиях, проводимых в любой области экономики.
2. Формирование исходной выборки
В качестве исходных данных рассматриваются выборки, полученных в результате сбора информации на сайте по количеству заказов обуви в течение 20 контрольных дней. Потребительским качествам и факторам обуви присвоены квалификационные коды для их использования в создании аналитических баз данных: КГГ — повышенная гигиеничность; КНП — снижение нагрузки на позвоночник; КОВ — обувь для водителей; КПВ — повышенная влагонепроницаемость; КПГ -повышенная гибкость; КПП — противоскользящая подошва; КСЭ — защита от статического электричества; КФС — форма подошвы, соответствующая стопе; ДАМ — аналоги известных марок; ДДК — дизайн-комфорт; ДМК — модная коллекция; ДНЗ — аналоги обуви знаменитостей; ДПК — перспективная коллекция; ЭВФ -высокая формоустойчивость; ЭНИ — низкая истираемость верха и низа; ЭПП -повышенная прочность; ЭЭЧ — экологическая чистота.
Полученная база данных, подготовленная к обработке с MS Excel, состоит из семнадцати выборок (по количеству факторов) и содержит номер измерения от 1 до 20 (по количеству дней), общее количество заказов в день (n) и количество заказов обуви по факторам (m) (таблица 2.1).
Таблица 2.1. Количество заказов обуви по измерениям (Лист «Исходные данные» — фрагмент)
A B C D E F G H I J K L M N O P Q R S
1 № измерения Количество заказов (и,) I 1-4 Ы | I ! 1 £ и va 1 Ьч & о и °о % « СЛ S v—✓ Е I PO S I « S Ji e и CD *-> S S X E a Я о jf Ж О
2 1 239 13 25 8 18 10 21 5 15 12 12 19 8 15 17 18 19 4
3 2 188 10 18 7 14 8 16 6 11 8 9 15 7 12 12 14 16 5
20 19 197 10 19 10 14 8 16 6 11 9 9 16 9 12 12 15 16 5
21 20 155 10 16 6 12 7 14 5 6 9 9 14 6 6 4 12 14 5
3. Проверка выборки на нормальность
Проверка распределения на нормальность включает следующие этапы [2]:
1) Вычисляются среднее арифметическое, медиана и мода. Если полученные значения друг от друга значительно не отличаются, мы имеем дело с нормальным распределением.
Формула для вычисления среднего арифметического (выборочного) — сумма значений переменной (х; . хп), деленная на п (число значений перемен-ной —
Экономика, Статистика и Информатика^! 109 №5, 2011
Медиана разбивает выборку на две равные части. Половина значений переменной лежит ниже медианы, половина — выше. Медиана дает общее представление о том, где сосредоточены значения переменной, иными словами, где находится ее центр.
Мода представляет собой максимально часто встречающееся значение пере-менной.
2) Вычисляется эксцесс — мера крутости кривой распределения, который для нормального распределения должен быть равен 0. Эксцесс определяется по уравнению:
Таблица 3.1. Формулы для расчета статистических параметров (Лист «Нормальность»)
1 Код фактора КГГ
22 Среднее =СРЗНАЧ(С2:С21)
23 Медиана =МЕДИАНА(С2:С21)
24 Мода =МОДА(С2:С21)
25 Эксцесс =ЭКСЦЕСС (С2:С21)
Рис. 1. Окно выбора аргументов функции «Normal»
где x — среднее значение переменной;
n — число значений перемен-ной; а — стандартное отклонение выборки.
Вычисление среднего, медианы, моды и эксцесса средствами MS Excel в режиме формул показано в таблице 3.1.
Для расчета абсолютных отклонений среднего значения от медианы и моды на VBA разработана пользовательская функция «Normal», аргументами которой являются среднее значение выборки (Sr) и медиана или мода (M):
Public Function Normal(Sr As Single, M As Single) Dim Nl As Single
Nl = Abs ( ( (Sr — M) / Sr) * 100) Normal = Nl End Function
При выборе функции для возможности ее последующего копирования в качестве аргументов вводятся относительные ссылки на ячейку C22 и C23, C24 (рисунок 1).
Анализ отклонений среднего значения от медианы и моды показывают, что перечисленные величины не совпадают (рисунок 2), а эксцесс кривой распределения отличен от 0 (рисунок 3).
Вышеперечисленные расчеты позволяют сделать вывод о том, что распределение рассматриваемых выборок не подчиняется нормальному (гауссо-вому) закону распределения и позволяют использовать для анализа только непараметрические критерии статистики, которые свободны от допущения о законе распределения выборок и бази-
Рис. 2. Отклонение среднего значения от медианы и моды
Рис. 3. Значение эксцесса кривой распределения
руются на предположении и независимости наблюдений.
4. корреляционный анализ
В эконометрических и экономико-математических моделях, применяемых, в частности, при изучении и оптимизации процессов маркетинга, наиболее популярно нормальное распределение, однако практика показывает, что в большинстве случаев распределения существенно отличаются от нормальных [3], в связи с чем для корреляционного анализа в работе рассматриваются только непараметрическое оценивание.
Для количественного анализа эффективности маркетинговых мероприятий необходимо установить зависимость между генеральной совокупностью (общим количеством заказов) и количеством заказов обуви по факторам.
Отсутствие нормальности выборок делает невозможным использование коэффициента корреляции, который предполагает наличие линейной связи между признаками. При наличии нелинейной связи коэффициент корреляции может быть равен нулю. В таких случаях для выявления связи применяют другой показатель — корреляционное отношение [2], которое фиксирует наличие любой связи между признаками. Алгоритм расчета корреляционного отношения включает следующие шаги:
— область значений одного признака (генеральной совокупности) разбивается на участки;
— для каждого из участков определяется среднее значение другого признака (пофакторной выборки);
— вычисляется корреляционное отношение, величина которого, как и коэффициента корреляции, лежит между нулем и единицей; чем ближе значение корреляционного отношения к единице, тем более тесной является связь.
Разработку методики расчета корреляционного отношения рассмотрим на примере установления зависимости между общим количество заказов обуви (п) и заказов по каждому из факторов комфортности (m.) — повышенной гигиеничности (КГГ) (таблица 1.1).
Произведем группировку общего количества заказов (факторный признак), образовав 5 групп с равными интервалами, предварительно отсортировав данные по количеству заказов.
Количество групп (5) выбрано на основании того, что в практике статистических исследований руководствуют-
ся тем, чтобы в интервалы попадало число наблюдений не менее 5-10 [3].
Величина интервала группировки (й) определяется как:
й _ птах ~ пшш (4.1)
где n , n . — максимальное и ми-
нимальное значения общего количества заказов.
Границы интервалов групп nd определяются как:
где nd.+1, nd. — верхняя и нижняя границы интервалов.
Нижней границей первого интеграла является минимальное значение количества заказов n .
Для определения номера интервала на VBA разработана пользовательская функция «Interval», аргументами которой являются максимальное (nmax), минимальное (nmin) и текущее (Kz) количество заказов.
P>ublic Function Interval (nmax As Single, nmin As Single, Kz As Single) As Single Dim d As Single, II As Single d = (nmax — nmin) / 5
II = End If End If End If End If End If Interval = End Function
i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.
При выборе функции для возмож-
ности ее последующего копирования в качестве аргументов вводятся абсолютные ссылки на ячейки $В$21 и $В$2 и относительная — на ячейку В2 (рисунок 4).
После сортировки и проведения расчетов в таблицу исходных результатов (таблица 1.1) добавляется столбец «№ интервала» (таблица 4.1).
Корреляционное отношение определяется по формуле:
где DMexp — межгрупповая диспер-
общ — общая дисперсия: D
D — внутригрупповая дисперсия:
т.ср — групповые пофакторные средние (по интервалам);
тр — общее пофакторное среднее; п. — количество заказов в группах; п — общее количество заказов; k — количество групп; D. — дисперсия в группе. Дисперсия в группе вычисляется по формуле: N
где mi — текущее значение;
N — число значений в группе.
Вышеперечисленные данные, рассчитанные с помощью промежуточных итогов в Excel, показаны на рисунке 5.
Для определения корреляционного отношения нецелесообразно создавать макросы и пользовательские функции, так как в расчетах необходимо в каче-
Рис. 4. Окно выбора аргументов функции «Interval»
Экономика, Статистика и Информатика
стве аргументов использовать относительные, смешанные и абсолютные ссылки и нефиксированный диапазон данных. В связи с этим проведение вычислений с помощью переадресации, встроенных функций и групповых операций MS Excel [4] дает больший эффект автоматизации расчетов.
Полученные данные (рисунок 5) сводятся в единую таблицу (рисунок 6) для проведения расчета межгрупповой, общей, внутренней дисперсии и корреляционного отношения (формулы 4.3-4.6).
Графический анализ пофакторного коэффициента корреляционного отношения (рисунок 7) показывает, что ряд одних факторов имеют высокую (0,70,9) и весьма высокую (0,9-0,99) по шкале Чеддока [1] функциональную связь с общим количеством заказов, в то время как другие — умеренную и заметную (0,3-0,7).
Фрагмент расчетной таблицы (рисунок 6) в режиме отображения формул показан в таблице 4.2.
При значениях показателей тесноты связи меньше 0,7 величина коэффициента детерминации [3] всегда будет ниже 50 %. Это означает, что на долю вариации факторных признаков приходится меньшая часть по сравнению с остальными неучтенными в модели факторами, влияющими на изменение результативного показателя (общего количества заказов обуви).
Таки образом, формирование маркетинговой политики и построение регрессионных моделей необходимо про-
Таблица 4.1. Расчет интервалов групп по количеству заказов (Лист «Корреляционное отношение» — фрагмент)
A B C D E F G H I J K L M N O P Q R S T
1 0 1 а Количество заказов (и,) I и ы I С «Ч Е И § •» Е И £ 1 к S СП о и £ £ Е i JN & & ЭВФ (т14,) I Я К т I И С О I £ О о № интервала
2 ii 133 7 14 4 10 5 10 4 8 6 7 11 4 9 10 11 10 3 1
3 7 153 7 14 7 10 6 11 8 9 6 7 11 7 10 11 10 11 8 1
20 1 239 13 25 8 18 10 21 5 15 12 12 19 8 15 17 18 19 4 5
21 6 239 12 24 9 17 10 21 8 14 11 12 18 9 15 15 17 20 7 5
I S g: а — л — — 3 1 KIT LJ P
Ваш психолог. Работа психолога в школе.
На главную 
Лекции и практикум по психологии Разное по психологии Математические методы обработки психологических данных — Проверка нормальности распределения исходных данных
| Математические методы обработки психологических данных — Проверка нормальности распределения исходных данных |
