Автор: Сдвижков О.А.
Теги: черчение геометрическое и техническое рисование компьютерная графика чертеж компьютерные технологии статистика программное обеспечение
ISBN: 978-5-94074-917-2
Год: 2014
Текст
I Сдвижков O.A.
<z^
5
НЕПАРАМЕТРИЧЕСКАЯ
СТАТИСТИКА
в и WY'
е
I
О. Л. Сдвижков
Непараметрическая статистика
в MS Excel и VBA
Москва, 2014
УДК 744.4:004.92
ББК 30.11+32.973.26-018.2
С27
Сдвижков О. А.
С27 Непараметрическая статистика в MS Excel и VBA. - М.: ДМК Пресс,
2014.- 172 с.: ил.
ISBN 978-5-94074-917-2
В книгу вошли основные сведения по MS Excel и классическим ме-
тодам непараметрической статистики, применяемым к независимым
выборкам, парным наблюдениям и таблицам сопряженности, реали-
зующие эти методы программы VBA и технологии решения типовых
задач в MS Excel. Данные технологии представлены, как пошаговыми
решениями (без применения макросов), так и автоматическими, когда
задача решается одним макросом, возвращающим значение статисти-
ки, критерий принятия основной гипотезы и вывод о том, какую гипо-
тезу следует принять.
Книга ориентирована на студентов вузов, изучающих статистиче-
ские методы, но будет полезна и более широкому кругу пользователей
MS Excel.
УДК 744.4:004.92
ББК 30.11+32.973.26-018.2
Все права защищены. Любая часть этой книги не может быть воспроиз-
ведена в какой бы то ни было форме и какими бы то ни было средствами без
письменного разрешения владельцев авторских прав.
Материал, изложенный в данной книге, многократно проверен. Но по-
скольку вероятность технических ошибок все равно существует, издательство
не может гарантировать абсолютную точность и правильность приводимых
сведений. В связи с этим издательство не несет ответственности за возможные
ошибки, связанные с использованием книги.
ISBN 978-5-94074-917-2
© Сдвижков О. А., 2014
© Оформление, издание, ДМК Пресс, 2014
Содержание
Предисловие.........................................5
Глава 1. Теория вероятностей и математическая
статистика в MS Excel................................7
§1 . Встроенные функции дискретных распределений...9
§2 . Макросы для дискретных распределений.........18
§3 . Встроенные функции непрерывных распределений.23
§4 . Инструмент Гистограмма.......................26
§5 . ИнструментОписательная статистика............34
§6 . Встроенные средства корреляционного анализа..38
§7 . Макрос Correlation...........................45
§8 . Тесты надстройки Анализ данных...............47
Глава 2. Непараметрические критерии
для независимых выборок.............................58
§1 . Критерий Колмогорова-Смирнова................59
§2 . Критерий Катценбайссера-Хакли ...............65
§3 . Критерий Вилкоксона..........................69
§4 . Критерий Манна-Уитни.........................76
§5 . Критерий серий Вальда-Вольфовитца............82
§6 . Сериальный критерий Рамачандрана-Ранганатана.94
Глава 3. Непараметрические критерии для пар
наблюдений..........................................97
§1 . Критерий знаков .............................98
§2 . Критерий Фишера.............................104
§3 . Знаковый критерий Вилкоксона ...............107
§4 . Модификации критерия Вилкоксона............114
§5 . Критерий Спирмена...........................123
§6 . Критерий Кендалла...........................128
§7 . Критерий Ван дер Вардена....................132
§8 . Критерий Ширахате...........................136
Глава 4. Непараметрические критерии
для таблиц сопряженности...........................141
§1 . Четырехклеточный /2 критерий................142
§2 . Общий случай %2 критерия....................149
§3 . Точный критерий Фишера......................153
4 Содержание
§4 . G-критерий Вулфа.........................156
§5 . Критерий Макнимары.......................159
§6 . Биномиальные критерии ...................163
§7 . Критерий Ле Роя..........................167
Литература......................................170
Предисловие
Непараметрические статистика - статистические критерии проверки
гипотез, не связанных с законами распределений генеральных сово-
купностей и их параметрами. Непараметрическая статистика нахо-
дит широкое применение в исследованиях по психологии, биологии,
экономике и многим другим областям.
В макросах папки VBA_tests, которым, фактически, посвящена
данная книга, статистические критерии запрограммированы так, как
они изложены в большей части литературы по непараметрической
статистике ([10], [12], [13] и др.), то есть, в основном, с помощью кри-
тических точек, соответствующих заданному уровню значимости а.
В статистических пакетах STATISTICA, SPSS, XLSTAT, как из-
вестно ([4], [8], [15]), подход другой ([22], стр. 37), опирающийся на
значение p-level, который у не знакомых с ним пользователей вызы-
вает определенные трудности. Поэтому данные макросы, не требую-
щие специальной установки, одного из самых доступных, и популяр-
ных, пакетов обработки числовых данных, каким является MS Excel,
несомненно, будут полезны. Кроме того, папка VBA_tests содержит
макросы таких уникальных критериев, каких нет в статистических
пакетах:
• Катценбайссера-Хакли;
• Рамачандрана-Ранганатана;
• Ширахате и др.
Естественно, макросы неоднократно тестировались, результаты
перепроверялись, в том числе, когда это было возможно, в статисти-
ческих пакетах.
В книгу вошли основные сведения по MS Excel и классическим
методам непараметрической статистики, применяемым к независи-
мым выборкам, парным наблюдениям и таблицам сопряженности,
реализующие эти методы программы VBA и технологии решения
типовых задач в MS Excel. Данные технологии представлены, как по-
лезными в учебных целях пошаговыми решениями (без применения
макросов), так и автоматическими, когда задача решается одним ма-
кросом, возвращающим:
• значение статистики;
• критерий принятия основной гипотезы;
• вывод о том, какую гипотезу следует принять.
Таблицы критических точек статистик, не поддерживаемых в MS
Excel, введены как процедуры-функции.
6
Предисловие
Материал изложен просто и доступно, большое число задач и
рисунков позволяет понять рассматриваемые технологии, вообще
говоря, не включая компьютер. Условия задач, в основном, взяты из
наиболее популярных сборников задач, учебных пособий и моногра-
фий. Включенные в книгу программы VBA должны подсказать поль-
зователям MS Excel, как самостоятельно запрограммировать нужный
критерий, а не охваченных информационными математическими тех-
нологиями статистических критериев - необозримое множество.
Под стандартным применением макроса в книге, следуя [18], по-
нимается:
1) вызов макроса (рабочей книги, содержащей макрос);
2) построчный ввод данных, начиная с ячейки АГ,
3) выделение диапазона данных;
4) запуск макроса на исполнение (Сервис => Макрос => Макросы
=> Имя => Выполнить).
Ссылка для скачивания макросов непараметрической статистики,
содержащихся в папке VBA_tests: http://www.oasdv.narod.ru.
Макросы, которых более 30, поддерживаются в MS Excel 2007 и
2010.
Книга ориентирована на студентов вузов, изучающих статистиче-
ские методы, но будет полезна и более широкому кругу пользовате-
лей MS Excel.
Глава 1
Теория вероятностей
и математическая
статистика в MS Excel
§1 . Встроенные функции
дискретных
распределений.............9
§2 . Макросы для дискретных
распределений.............18
§3 . Встроенные функции
непрерывных
распределений.............23
§4 . Инструмент
Гистограмма...............26
§5 . Инструмент
Описательная
статистика...............34
§6 . Встроенные средства
корреляционного
анализа..................38
§7 . Макрос Correlation.45
§8 . Тесты надстройки
Анализ данных............47
8
Теория вероятностей и математическая статистика в MS Excel
Теория вероятностей и математическая статистика поддерживаются
в MS Excel встроенными функциями (таблица 1.1) и надстройкой
(инструментами) Анализ данных (рис. 1.1, 1.2).
Таблица 1.1
РРАСП МАКС СРЗНАЧ
РРАСПОБР МАКСА СРЗНАЧА
ZTECT МЕДИАНА СРОТКЛ
БЕТАОБР МИН СТАНДОТКЛОН
БЕТАРАСП МИНА СТАНДОТКЛОНА
БИНОМРАСП МОДА СТАНДОТКЛОНП
ВЕЙБУЛЛ НАИБОЛЬШИЙ СТАНДОТКЛОНПА
ВЕРОЯТНОСТЬ НАИМЕНЬШИЙ СТОШУХ
ГАММАОБР НАКЛОН СТЬЮДРАСП
ГАММАРАСП НОРМАЛИЗАЦИЯ СТЬЮДРАСПОБР
ГАММАНЛОГ НОРМОБР СЧЕТ
ГИПЕРГЕОМЕТ НОРМРАСП СЧЕТЕСЛИ
ДИСП НОРМСТОБР СЧЕТЗ
ДИСПА НОРМСТРАСП СЧИТАТЬПУСТОТЫ
ДИСПР ОТРБИНОМРАСП ТЕНДЕНЦИЯ
ДИСПРА ОТРЕЗОК ТТЕСТ
ДОВЕРИТ ПЕРЕСТ УРЕЗСРЕДНЕЕ
КВАДРОТКЛ ПЕРСЕНТИЛЬ ФИШЕР
КВАРТИЛЬ ПИРСОН ФИШЕРОБР
КВПИРСОН ПРЕДСКАЗ ФТЕСТ
КОВАР ПРОЦЕНТРАНГ ХИ2ОБР
КОРРЕЛ ПУАССОН ХИ2РАСП
КРИТБИНОМ РАНГ ХИ2ТЕСТ
ЛГРФПРИБЛ РОСТ ЧАСТОТА
ЛИНЕЙН СКОС ЭКСПРАСП
ЛОГНОРМОБР СРГАРМ ЭКСЦЕСС
ЛОГНОРМРАСП СРГЕОМ
Меню встроенных функций (Мастер функций) открывается кноп-
кой /(х), по каждой из них в MS Excel можно получить подробную
справку. Поэтому специально перечислять их назначения, как и зна-
чения параметров, нет необходимости, тем более что многие из них
в дальнейшем будут применяться в конкретных задачах.
Пакет Анализ данных, если его нет в меню Сервис, загружается
командами Сервис => Надстройки, после чего надо поставить галоч-
ку в поле Анализ данных и подтвердить ОК.
Щелчок по опции Анализ данных меню Сервис открывает список
инструментов:
Встроенные функции дискретных распределений
9
Рис. 1.1
Продолжение списка инструментов на рис. 1.2.
Г истограмма
Скользящее среднее
Генерация случайных чисел
Ранг и персентиль
Регрессия
Выборка
Парный двухвыборочный t-тест для средних
Двухвыборочный t-тест с одинаковыми дисперсиями
Двухвыборочный t-тест с различными дисперсиями
Двухвыборочный z-тест для средних
Рис. 1.2
§1. Встроенные функции дискретных
распределений
Встроенные функции комбинаторики ПЕРЕСТ, ЧИСЛКОМБ и
ФАКТР, причем первая входит в статистические функции, а вторая и
третья - в математические.
ПЕРЕСТ(п; го) - возвращает число выборок из п элементов по т,
каждая из которых отличается от остальных или составом элементов,
или их порядком.
Пусть требуется подсчитать, сколько вариантов шахматных ко-
манд по три игрока (1-я, 2-я, 3-я доски) можно составить из четырех
игроков. Открываем диалоговое окно ПЕРЕСТ, вводим данные и по-
лучаем результат:
10
Теория вероятностей и математическая статистика в MS Excel
Рис. 1.3
Команда ОК вставляет результат на рабочий лист.
Если т = п, то встроенная функция ПБРЕСТ возвращает п!> так же
как встроенная функция ФАКТР.
ЧИСЛКОМБ(п; т) - возвращает число сочетаний С"', то есть чис-
ло выборок из п элементов по т, каждая из которых отличается от
остальных хотя бы одним элементом.
Пусть требуется подсчитать, сколько вариантов различных ко-
манд по пляжному волейболу (в команде 2 человека) можно соста-
вить из пяти человек. Тогда, используя встроенную функцию ЧИСЛ-
КОМБ, получаем 10:
Рис. 1.4
Встроенная функция БИНОМРАСП(т, п, р, ЛОЖЬ) возвращает
значение, получаемое по формуле Бернулли
Встроенные функции дискретных распределений
11
встроенная функция ПУАССОН(т, Л, ЛОЖЬ) - значение, получае-
мое по формуле Пуассона
т\
При замене значения ЛОЖЬ (0) на значение ИСТИНА (1) они
возвращают значения интегральных функций распределений.
Пусть дискретная случайная величина X имеет, например, бино-
миальный закон распределения с параметрами п = 5, р = 0,75. Тогда
ее возможные значения 0, 1,2, 3, 4, 5. Вычислим вероятности, с кото-
рыми она их принимает, составим закон распределения, сделаем про-
верку и построим многоугольник распределения.
1. В диапазбн A1:F1 вводим значения X.
2. Выделяем ячейку А2, открываем диалоговое окно БИНОМ-
РАСП и заполняем поля (рис. 1.5).
Рис. 1.5
3. Команда ОК вставляет в ячейку А2 формулу =БИНОМ-
РАСП(А1;5;0,75;0).
4. Методом «протаскивания» маркера заполнения копируем ее в
остальные ячейки диапазона A2:F2.
5. Выделяем диапазон A2:F2 и, щелчком по кнопке Е , убеждаем-
ся в том, что сумма равна 1, то есть в диапазоне A1:F2 получен,
действительно, закон распределения дискретной случайной
величины:
12
Теория вероятностей и математическая статистика в MS Excel
А2 - А =БИНОМРАСП(А1;5;0,75;0)
А ] В _j_jc___________1 D I £. Г F “] 6 7
1 0________1________2________3_________4________5_________
2 | 0,000977 0.014648 0,087891 0,263672 0,395508 0,237305 11
Рис. 1.6
6. В графическом редакторе Мастер диаграмм, выделяя диапазон
А1 :F2, строим точечную диаграмму:
Вычисление значения интегральной функции данного биномиаль-
ного распределения при х = 5 дает F(5) = 1 (рис. 1.8), что показывает,
что в MS Excel интегральная функция распределения Е(х) = Р(Х<х),
хотя принято Е(х) = Р(Х < х) [6].
Рис. 1.8
Встроенные функции дискретных распределений
13
График интегральной функции рассматриваемого распределения
на отрезке [0, 6] с шагом 0,2, построенный с помощью встроенной
функции БИНОМ РАСП и графического редактора Мастер диа-
грамм, показан на рис. 1.9.
Задача 1.1. Дискретная случайная величина X задана законом
распределения
х, 3 4 7 10
Pi 0,2 0,3 0,1 0,4
Найти интегральную функцию распределения и построить гра-
фик.
Технология решения.
1. Вводим в ячейку А1 значение 2,8 и задаем в первом столбце
арифметическую прогрессию с шагом 0,2, предельное значение
И.
2. В ячейку В1 вводим формулу интегральной функции заданно-
го распределения
=ЕСЛИ(А1<=3;0;ЕСЛИ(А1<=4;0,2;ЕСЛИ(А1<=7;0,5;ЕСЛИ
(А1<=10;0,6;1))))
и копируем ее в ячейки столбца В, соответствующие заполнен-
ным ячейкам столбца А.
3. Выделяя полученную таблицу и применяя редактор Мастер
диаграмм, приходим к графику:
14
Теория вероятностей и математическая статистика в MS Excel
Рис. 1.10
Ответ:
F(x) =
0,2, 3<х<4
ОД 4 < х < 7 .
0,6, 7 < х < 10
1, г >10
»
Найдем математическое ожидание Л/[Х] = У^л,р, дискретной слу-
чайной величины X, заданной в задаче 1.1.
1. Введем в диапазон A1:D2 закон распределения (рис. 1.11).
2. В ячейке АЗ запишем формулу =А1*А2 и скопируем ее марке-
ром заполнения в остальные ячейки диапазона A3:D3.
3. Выделяя диапазон A3:D3, и делая щелчок по кнопке Е панели
инструментов, получаем в ячейке ЕЗ искомое значение, то есть
М[Х] = 6,5:
п
Найдем дисперсию D[X] = -М[Х})2 • pi случайной вели-
/=1
чины задачи 1.1. Продолжаем вычисления.
Встроенные функции дискретных распределений
15
4. В ячейку А4 вводим формулу =(А1-$Е$3)А2*А2 и копируем ее
маркером заполнения в остальные ячейки диапазона A4:D4.
5. Выделяя диапазон A4:D4, и применяя кнопку Е, получаем
в ячейке Е4 значение дисперсии, то есть D[X] = 9,25:
А I В I I С D I Е
1 3 4 7 10
_2 0.2 0.3 0.1 0.4
3 0.6 V 0.7 4 6.5
4 2.45 1.875 0.025 4.9 9,251
Рис. 1.12
Сделаем проверку полученного результата, применяя формулу:
D[X] = М[Х2] - М[Х]2.
Продолжаем вычисления.
6. В ячейку А5 вводим формулу =А1А2*А2 и копируем ее в осталь-
ные ячейки диапазона A5:D5.
7. Выделяя диапазон A5:D5, и применяя кнопку Е , получаем
М[Х2] =51,5 (ячейка Е5).
8. В ячейку F5 вводим формулу =Е5-ЕЗА2 Она возвращает значе-
ние, совпадающее с полученным по первой формуле в ячейке Е4:
А В I С | _о | I Е. J i F |
1 3 4 7 _10
_2_ 0.2 0.3 0.1 0.4
3 0.6 1.2Г 0.7 4 6.5
_4 2.45 1,875 0.025 4.9 9.25
5 пз 4.8 40 51tS| | 9,251
Рис. 1.13
Поданным диапазона A1:D2 (рис. 1.11) значение М[X] можно по-
лучить также функцией СУММПРОИЗВ. Достаточно открыть ее диа-
логовое окно и ввести ссылки на диапазоны A1:D1, A2:D2 (рис. 1.14).
Задача 1.2. Магазин получил 1000 бутылок минеральной воды.
Вероятность того, что при перевозке бутылка окажется разбитой,
равна 0,005. Найти вероятности того, что магазин получит разбитых
бутылок: а) ровно три; б) менее трех; в) более трех; г) хотя бы одну.
Технология решения. Так как п = 1000 велико, а р=0,003 мало, то
применима формула Пуассона, в которой Л = пр = 5. Найдем по ней
вероятности, с которыми принимаются значения 0, 1,2, 3.
16
Теория вероятностей и математическая статистика в MS Excel
Рис. 1.14
1. Вводим их в диапазон А1:АЗ (рис. 1.16).
2. Выделяем ячейку В1, открываем диалоговое окно ПУАССОН и
вводим данные, подтверждая командой ОК (рис. 1.15).
Рис. 1.15
3. Копируем формулу ячейки В1 в ячейки В2:В4. Ответ на пер-
вый вопрос находится в ячейке В4.
4. Формула = В1+В2+ВЗ, которую запишем в С1, дает ответ на
второй вопрос.
5. Кнопкой Z находим в ячейке В5 сумму значений диапазона
В1:В4. Формулой =1-В5 ячейки С2 получаем ответ на третий
вопрос.
6. Последняя величина находится по формуле = 1-В1, ее помеща-
ем в ячейку СЗ. В результате фрагмент листа Excel с решением
задачи принимает вид:
Встроенные функции дискретных распределений
17
А В С D
_1_ Ch 0,006738 0,124652
2 1 003369 0,734974
3 2 0,084224 0,993262.
4 31 0,140374
5 0,269326
Рис. 1. 16
Ответ: а) В4;б) С1; в) С2; г) СЗ.
Задача 1.3. В партии из 10 деталей имеется 8 стандартных. На-
удачу отобраны две детали. Составить закон распределения случай-
ной дискретной величины X - числа стандартных деталей среди ото-
бранных.
Технология решения. Возможные значения случайной величины
X: 0,1, 2. Закон распределения - гипергеометрический.
1. В диапазона Al:С 1 вводим 0, 1, 2 соответственно.
2. Выделяем ячейку А2, открываем диалоговое окно ГИПЕР-
ГЕОМЕТ и вводим данные, подтверждая ОК.
Рис. 1.17
3. Копируем формулу ячейки А2 в ячейки В2:С2 и переходим
в диапазоне А2:С2 к формату «Дробный», с дробями до двух
цифр, что приводит к следующему закону распределения ве-
роятностей:
А I В I г I
1 0 1 2
2 1/45 16/45 28/451
Рис. 1.18
18
Теория вероятностей и математическая статистика в MS Excel
Ответ: Р(Х = 0) = — , Р(Х = 1) = —, Р(Х = 2) = —.
45 45 45
§2. Макросы для дискретных
распределений
1. Макрос Discrete вычисляет автоматически математическое ожи-
дание Л/[Х], дисперсию D[X] и среднее квадратическое отклонение
s[X] = a/D[X] таблично заданной дискретной случайной величины
X. Для применения макроса надо ввести данные таблицы в первые
две строки рабочего листа, начиная с ячейки А1, выделить диапазон
с данными и запустить макрос на исполнение. Например, при выде-
ленном диапазоне A1:D2, показанном на рис. 1.11, он возвращает по-
лученные ранее результаты:
1. М[Х]=6,5
2. D[X]=9,25
3. s[X]=3,041381
| OK
Рис. 1.19
Код макроса Discretel
Sub Discrete ()
Dim n As Integer, x() As Single, p() As Single, _
m As Single, d As Single, s As Single
n = Selection.Columns.Count
ReDim x (1 To n)
ReDim p( 1 To n)
m = 0: d = 0
For i = 1 To n
x(i) = Cells(l, i).Value: p(i) = Cells(2, i).Value
m = m + x(i) * p(i)
Next
For i = 1 To n
d = d+ (x(i)-m) Л 2 * p(i)
Next
s = Sqr(d)
MsgBox (”1. M[X]=" & m & Chr(13) & "2. D[X]=" & d _
& Chr (13) & "3. s[X]=" & s)
End Sub
Макросы для дискретных распределений
19
2. Макрос Discrete_L для биномиального, пуассоновского и гео-
метрического законов распределений находит Р(т{ < X < тл2).
Задача 1.4. Страховой агент в результате каждого визита заклю-
чает договор с вероятностью 1/3. Найти вероятность, что из 10 визи-
тов не менее пяти завершатся заключением договора.
Технология решения. Задача сводится к нахождению вероятно-
сти Р10(5 < X < 10) для биномиального закона распределения. Вызы-
ваем макрос Discrete_L и запускаем его на исполнение, что открывает
окно ввода номера выбранного закона распределения:
Рис. 1.20
По команде ОК открывается следующее окно ввода:
Рис. 1.21
Команда ОК окна возвращает искомую вероятность:
Рис. 1.22
20
Теория вероятностей и математическая статистика в MS Excel
Ответ: 0,2131.
Задача 1.5. Случайная величина X имеет распределение Пуас-
сона с параметром Л = 15. Найти вероятность Р(12 < X < 16).
Технология решения. Запускаем макрос Discrete_L на исполне-
ние и вводим 2:
Рис. 1.23
Команда ОК открывает следующее окно ввода:
Рис. 1.24
Команда ОК возвращает:
Рис. 1.25
Ответ: 0,4794.
Задача 1.6. Вероятность завести машину на морозе с одной по-
пытки равна 0, 8. Найти вероятность, что машина будет заведена не
более чем с трех попыток.
Макросы для дискретных распределений 21
Технология решения. Запускаем макрос Discrete_L на исполне-
ние и вводим 3:
Рис. 1.26
Команда ОК открывает следующее окно ввода:
Рис. 1.27
По команде ОК получаем:
Рис. 1.28
Ответ: 0,992.
Код макроса
Sub Discrete_L ()
Dim n As Integer, tip As Integer, p As Single, nl As Integer, n2 As Integer,
s() As Single, m As Variant
ReDim s (0 To 3)
tip = Application.InputBox("Введите номер закона распределения:" & Chr(13)
22
Теория вероятностей и математическая статистика в MS Excel
& " 1. Биномиальный; 2. Пуассона; 3. Геометрический. ", Туре:=1)
Select Case tip
Case Is = 1
m = Application.InputBox(prompt:=иВведите через пробел значения n, nl, n2, р:и, _
Type:=2)
m = Split(m)
For i = 0 To 2
s(i) = CSng(m(i))
Next
If InStr(m(3), "/") > 0 Then
t = Split (m(3), "/"): s (3) = t (0) / t (1)
Else
s(3) = CSng(m(3))
End If
p = 0
For i = s (1) To s (2)
p = p + Application.WorksheetFunction.BinomDist(i, s(0), s(3), False)
Next
Case Is = 2
m = Application.InputBox(prompt:="Введите через пробел значения nl, n2, lamda:",
Type:=2)
m = Split(m)
For i = 0 To 1
s(i) = CSng(m(i))
Next
If InStr(m(2), '7") > 0 Then
t = Split(m(2), '7й)
s(2) = t(0) / t(l)
Else
s(2) = CSng(m(2))
End If
[a5] = s(2)
p = 0
For i = s(0) To s (1)
p = p + Application.WorksheetFunction.Poisson(i, s(2), False)
Next
la8] = p
Case Is = 3
m = Application.InputBox(prompt:="Введите через пробел значения nl, n2, р :", _
Туре:=2)
m = Split(m)
For i = 0 То 1
s(i) = CSng(m(i))
Next
If InStr(m(2), '7") > 0 Then
t = Split(m(2), '7"): s(2) = t(0) / t(l)
Else
s(2) = CSng(m(2))
End If
p = 0
For i = s(0) To s(l)
p = p + s(2) * (1 - s(2)) A (i - 1)
Встроенные функции непрерывных распределений
23
Next
End Select
р = FormatNumber(р, 4)
MsgBox ("Вероятность Р = " & р)
End Sub
§3. Встроенные функции
непрерывных распределений
В MS Excel богатая палитра встроенных функций непрерывных за-
конов распределений. По каждой из них в MS Excel имеется подроб-
ный справочный материал и примеры. Поэтому остановимся только
на некоторых из них.
Встроенная функция нормального закона распределения НОРМ-
РАСП.
Построим на отрезке [-1, 5], h = 0,2, с помощью нее график диф-
ференциальной функции (плотности) нормального закона распреде-
ления
/(х)=тке
yZTt
в котором а = 2, о = 1.
1. В ячейку А1 вводим -1 и в первом столбце задаем арифметиче-
скую прогрессию с шагом 0,2, предельное значение 5.
2. Выделяем ячейку В1, открываем диалоговое окно НОРМ-
РАСП и вводим данные, подтверждая их ОК:
Рис. 1.29
24
Теория вероятностей и математическая статистика в MS Excel
3. Копируем формулу ячейку В1 в остальные ячейки диапазона
В1:В31.
4. Выделяя полученную таблицу редактором Мастер диаграмм,
строим точечную диаграмму:
КРИВАЯ ГАУССА
Рис. 1.30
Рис. 1.34
Аналогично строится график интегральной функции данного рас-
пределения:
Рис. 1.31
Рисунок 1.30 хорошо иллюстрирует правило трех сигм - вероят-
ность того, что нормально распределенная случайная величина от-
клонится от математического ожидания а на величину большую Зо,
приближенно равна нулю.
Встроенные функции непрерывных распределений
25
Вычисление вероятности попадания рассматриваемой случайной
величины в интервал (0,5, 3) показано на рис. 1.32.
А1 * А =НОРМРАСП(3;2;1;1)-НОРМРАСП(р,5;2;1;1)
. А I В С | D Е F
I1 | 0,7745381
Рис. 1.32
Для нормального закона распределения с параметрами а = 0, о = 1
есть специальная встроенная функция НОРМСТРАСП.
Задача 1.7. Средний срок работы лампы 4 года. Найти вероят-
ность, что она проработает не больше года.
Технология решения. Случайная величина X - время работы до
отказа имеет показательное (экспоненциальное) распределение с па-
. 1 -
раметром Л = t - среднее время до отказа. Поэтому задача сводит-
ся к нахождению значения интегральной функции данного распреде-
ления при х = 1:
А1 ~ А =ЭКСПРАСП(1;012511)
| А .| В Г С I D I Е I
1 | 0.2211991
Рис. 1.33
Ответ: 0,221199.
Встроенная функция ГАММАРАСП(х, а, 0, 8), х > 0, а > 0, 0 > 0,
8 6 {0,1}, если 8 = 0, вычисляет значение плотности гамма - распреде-
ления:
/(х,а,0) =------хп~'е
0аГ(а)
в случае 8=1, значение интегральной функции этого распределения.
График плотности гамма - распределения при а = —, 0 = 1 показан на
рис. 1.34.
Встроенная функция ХИ2РАСП(х, п) для распределения
п
Х2„ = Xt - независимые нормально распределенные случайные
величины, Л/[Х.] = 0, q[XJ = 1, возвращает Р(%2П > х)» х > 0. График
26
Теория вероятностей и математическая статистика в MS Excel
функции ХИ2РАСП(х, 1) показан на рис. 1.35. Критические точки
распределения %2п возвращает функция ХИ2ОБР(а, п).
§4. Инструмент Гистограмма
1. Группировка выборочных данных - рутинная, но часто необходи-
мая операция обработки статистических данных. Рассмотрим прове-
дение ее в MS Excel непосредственно и инструментом Гйстограмма.
Задача 1.8. Задана выборка объема п = 50 значений некоторого
признака X:
Инструмент Гистограмма 27
9,19,11,5,10,7, 12,6,13,0, 12,3,7,46,8,92,8,80,11,6,11,9, 10,9, 5,82,
8,89, 9,32, 8,30,8,76, 8,01, 15,5, 12,3,9,46, 9,11,12,1,12,5,9,33,11,0,
10,1,9,61, 13,7,15,0, 12,2,13,1,11,7,10,4,11,5,9,02,9,23,7,16,12,0,
10,6, 6,39, 6,97,9,03, 6,84, 8,29,10,5,11,7,7,05,12,1,9,51.
Требуется, разбивая данные на к = 6 групп, составить:
1) интервальный вариационный ряд и построить гистограмму
частот;
2) дискретный вариационный ряд и построить полигон частот.
Технология решения.
1. Вводим выборочные данные в диапазон А1:А50, выделяем его
А|
и с помощью кнопки я* проводим сортировку по возрастанию.
2. Из полученного ряда находим значения minX=Al=5,82,
maxX=A50=15,5. Эти значения можно получить также встро-
енными функциями МИН и МАКС.
3. По формуле =А50-А1 находим размах выборки АХ = 9,68.
Д У
4. Оценивая шагЛ=------, получаем 1,613331. Округляем (только
6
в большую сторону) и принимаем h = 1,7.
5. По формуле
оцениваем крайнее левое значение первого интервала, что дает
5,56. Округляем до 5,6 и убеждаемся, что так округлить можно:
5,6+6*1,7=15,8>15,5.
6. В диапазоне В1 :В7 задаем арифметическую прогрессию, с первым
членом 5,6, разностью (шагом) 1,7, предельным значением 15,8:
Рис. 1.36
7. С помощью встроенной функции СЧЕТЕСЛИ подсчитываем
число вариант, принадлежащих промежутку (5,6; 7,3], и запи-
сываем результат в ячейку С1:
28
Теория вероятностей и математическая статистика в MS Excel
Рис. 1.37
Двойные кавычки в поле Критерий обязательны.
8. Аналогично, подсчитываем и записываем в ячейку С2 число
вариант, принадлежащих промежутку (5,6; 9]:
Аргументы функции Х|
СЧЁТЕСЛИ
Диапазон |а1:а50 " {5,82:6,39:6,84:6,9
Критерий |н<=9" = "<=9"
I
= 14
Подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.
Критерий условие в форме числа, выражения или текста, который
определяет, какие ячейки надо подсчитывать.
Справка по этой функции
Значение:14
Рис. 1.38
Продолжая вычисления, приходим к последовательности
С1:С6:
I С6 ▼ А1 =СЧЁТЕСЛИ(А1:А50:"<=15.8")
А В С D Е
1 5.82. 5.6 6!
2 6,39 7.3 14
3 6,84 9 29
4 6,97' 10,7 43|
5 7.Об1 12.4 48
6 7.16 14,l| 501
Рис. 1.39
Инструмент Гистограмма
29
9. Вводим в ячейку D1 формулу =(В1+В2)/2 и, копированием
ее, получаем в диапазоне D1:D6 середины интервалов. В ячей-
ку Е1 вводим 6, а в ячейку Е2 формулу =С2-С1. Копируя ее
в ячейки ЕЗ:Е6, получаем последовательность частот:
А 1 I в I с D Е
1 5.82 5.6 6 6.45 6
2 6,39 7.3 14 8.15 8
3 6.84 9, 29 9,85 15
4 6.97 10,7 43 11.55 14
5 7.06 12,4 48 13.25 5
6 7.16 14.1 50 14.95 2
Рис. 1.40
Выписываем интервальный вариационный ряд (таблица 1.2).
Таблица 1.2
(5.6; 7.3) (7.3:9) (9;10,7) (10,7; 12.4) (12,4; 14,1) (14,1; 15,8)
6 8 15 14 5 2
Выписываем дискретный вариационный ряд (таблица 1.3).
Таблица 1.3
6,45 8,15 9.85 11,55 13,25 14,95
6 8 15 14 5 2
10. По данным диапазона Е1:Е6 в графическом редакторе Мастер
диаграмм, выбирая опцию Гистограмма, проводим построение
(рис. 1.41).
Рис. 1.41
30
Теория вероятностей и математическая статистика в MS Excel
Интервалы указываем на вкладке Ряд (подписи оси X):
Мастер диаграмм (шаг 2 из 4): источник данный диаграммы... В В
|(5,6, 7,3);(7,3, 9);(9,10,7);(10,7, 12,4);(12,4, 14,1);(14,1, 15д8)| §
Рис. 1.42
Командами Диаграмма => Точечная строим полигон частот
(рис. 1.43).
Рис. 1.43
Ответ: 1) таблица 1.2, рис. 1.41; 2) таблица 1.3, рис. 1.43.
2. В автоматическом режиме группировка выполняется инстру-
ментом Гйстограмма, диалоговое окно показано на рис. 1.44. В нем:
• Входной интервал - ссылка на диапазон выборочных данных;
• Интервал карманов (необязательный параметр) - ссылка на
диапазон граничных значений интервалов, приведенных в воз-
растающем порядке;
• Метки - отмечается, когда выборочные данные имеют заголо-
вок;
• Выходной интервал/Новый рабочий лист/Новая рабочая кни-
га - переключатель места вывода результатов;
• Парето (отсортированная гистограмма) - возвращает частоты
в порядке убывания;
• Интегральный процент - возвращает накопленные частоты
в процентах и включает в гистограмму график накопления час-
тот;
Инструмент Гистограмма
31
• Вывод графика - возвращает гистограмму на листе, содержа-
щем выходной диапазон.
Если параметр Интервал карманов не задан, то проводится груп-
пировка с шагом
х — х .
л max inin
k-\ ’
k = [1 + 3,322 • In и], квадратные скобки обозначают целую часть.
Применение к данным задачи 1.5:
Рис. 1.44
Команда ОК приводит к результатам, полученным ранее:
Рис. 1.45
Нули в столбце Частота рис. 1.45 показывают, что нет значений
меньших или равных 5,6 или больших 15,8. Следует заметить, часто-
ты могут быть получены также встроенной функцией ЧАСТОТА.
32
Теория вероятностей и математическая статистика в MS Excel
3. Рассмотрим составление корреляционной таблицы по двумер-
ной выборочной совокупности. Пусть задана двумерная выборка
(X, Y) объемом п = 50, в которой значения признака X такие же, как
в задаче 8, а соответствующие им значения признака Y:
27,5, 26,0, 26,9, 24,3, 22,2, 29,1, 23,3, 24,7, 27,2, 20,8, 25,8, 25,4, 28,7,
25,9, 29,1, 25,2, 24,6, 31,3, 24,3, 32,4, 22,0, 28,6, 23,8, 20,5, 21,5, 25,6,
21,9, 25,4, 20,8, 23,9, 26,4, 26,7, 22,8, 26,5, 22,3, 18,1, 20,2, 25,3, 21,3,
26,5,19,2, 21,5,26,3, 21,1, 27,3, 31,8, 26,4,21,7, 27,1, 21,8.
Требуется, разбивая значения X на 6 групп, а значения Y на 5 групп,
составить корреляционную таблицу и построить трехмерную гисто-
грамму частот.
Группировка выборочных данных по X проведена в пункте 1.
Определим интервалы вариационного ряда по Y
1. Вводим данные по X в столбец А, данные по Y в столбец В.
2. Встроенными функциями МИН и МАКС, соответственно, на-
ходим min у = 18,1, max у = 32,4. Размах выборки Аг/ = 14,3.
3. Оцениваем шаг Аг//5 = 2,86 и принимаем hy =3.
4. Оцениваем крайнее левое значение
Округляем до 18 и получаем следующие интервалы:
(18, 21), (21,24), (24,27), (27,30), (30,33).
Переходим к вычислению корреляционной матрицы.
5. В ячейку С1 вводим формулу =С1 + 1.
6. В ячейке D1, записываем формулу =ИНДЕКС(А1:В50;СГ,1),
которая будет перебирать элементы первого столбца, а в ячейке
Е1 - формулу =ИНДЕКС(А1:В50;СГ,2), перебирающую соот-
ветствующие элементы второго столбца.
7. В ячейку F1 вводим =Е1+ЕСЛИ(И(О1<=7,3;Е1<=21);1;0), по
аналогии задаем формулы остальных ячеек корреляционной
матрицы F1:J6. Формула ячейки J6 показана на рис. 1.39.
8. На вкладке Вычисления диалогового окна Параметры меню
Сервис устанавливаем галочку в поле Итерации, число итера-
ций 50. Команда ОК возвращает в диапазоне F1:J6 корреляци-
онную матрицу (рис. 1.46).
К набору формул следует отнестись особенно внимательно. В ре-
жиме итерационных вычислений, а он особый, после запуска вычис-
лений уже ничего исправить нельзя. В случае ошибки, придется пере-
запустить Excel и набирать все формулы заново.
Инструмент Гистограмма
33
А =36+ЕС.ПИ(И(14,1<$Р$1;$Р$1<=15,8;30<$Е$1;$Е$1 <=33); 1 ;0)
С р Е F G Н I J
50 i 9,53 21,8 1 3 1 0
0 1 4 _2< “ 1
1 2 5“ 4 _зц 1
I ' 1Г|, 1 4’ бГ _2 1
2 f 2, “о 0
1 0 1 1 о|_ 01
Рис. 1.46
По полученным данным (рис. 1.46) составляем корреляционную
таблицу: ;
Таблица 1.4
X\Y 19,5 22,5 25,5 28,5 31,5 пх
6.45 1 3 1 1 0 6
8.15 0 1 4 2 1 8
9,85 2 5 4 3 1 15
11,55 1 4 6 2 1 14
13,25 2 1 2 0 0 5
14,95 0 1 1 0 0 2
Лу 6 15 18 8 3 50
Выделяя диапазон F1J6 (рис. 1.46), командами Диаграмма => Гис-
тограмма строим трехмерную гистограмму (рис. 1.47).
Рис. 1.47
34
Теория вероятностей и математическая статистика в MS Excel
з G
Найдем по корреляционной таблице 1.4.
у=| i=i
1. В диапазон А1 :Е6 вводим частоты nijt в диапазон G8:K8 - вари-
анты у, в диапазон F9.F14 - варианты х..
2. В ячейке G9 задаем формулу =А1 *$F9*G$8 и копируем ее мар-
кером заполнения в остальные ячейки диапазона G9:K14.
3. По формуле =CYMM(G9:K14) в ячейке L15 получаем искомое
значение (рис. 1.48).
19.5 22,5 25,5 28,5 31,5
6.45 125,775 435,375 164,475 183,825 0
8,15 0 183,375 831,3 464,55 256.725
9.85 384.15 1108,125 1004,7 842,175 310^75
11,55 225,225 1039,5 1767,15 658,35 363,825
13,25 516,75 298,125 675.75 0 0
14,95 0 336,375 381.225 0 0 I 12557,11
Рис. 1.48
§5. ИнструментОписательная
статистика
Среднее значение совокупности хр х2,..., хл, то есть
вычисляет встроенная функция СРЗНАЧ.
Дисперсию
Ё(-г< -*)2
Ж1=-----------
п
возвращает встроенная функция ДИСПР.
Исправленная дисперсия £)[Х| = —вычисляется встроен-
ной функцией ДИСП. Достаточно с помощью кнопки /(х) открыть
диалоговое окно нужной функции, сделать ссылку на ряд данных и
подтвердить ОК. Также можно ввести формулу функции в ячейку
(окно формул) непосредственно с клавиатуры.
Инструмент Описательная статистика
35
Инструмент Описательная статистика пакета Анализ данных
возвращает одновременно значения всех основных числовых харак-
теристик совокупности.
Например, введем в диапазон А1:А50 выборочные данные зада-
чи 1.8 командами Сервис => Анализ данных => Описательная стати-
стика => ОК откроем диалоговое окно и заполним его, как на рис. 1.49.
Рис. 1.49
Тогда команда ОК возвращает:
Столбец!
Среднее
Стандартная ошибка
Медиана
Мода
Стандартное отклонение
Дисперсия выборки
Эксцесс
Асимметричность
Интервал
Минимум
Максимум
Сумма
Счет
Наибольший(2)
Наименьший(3)
Уровень надежности(95,0%)
10,2598
0,312014193
10,25
11.5
2,206273513
4,867642816
-0,378702501
0,126293354
9,68
5,82
15,5
512,99
50
15
6,84
0,627015615
Рис. 1.50
36
Теория вероятностей и математическая статистика в MS Excel
В приведенном отчете:
• стандартная ошибка А = -^=-;
у/П
• эксцесс (находится также встроенной функцией ЭКСЦЕСС)
рассчитывается по формуле
я(я + 1) у/ xt-xV 3(и-1)2
-(n-l)(n-2)(n-3)^[ о ) ~(п-2)(п-3)’
• асимметричность (находится также встроенной функцией
СКОС) вычисляется по формуле
„ / -\3
и <4 х, -х ]
(п-1)(П-2)£Д о )
• интервал - размах выборки;
• параметр Уровень надежности включается в отчет, когда надо
найти доверительный интервал, покрывающий с заданной на-
дежностью, в данном случае 95,0%: (10,2598 - 0,6270,10,2598 +
+ 0,6270) = (9,6328, 10,8868).
Остальные параметры комментариев не требуют.
Инструментом Описательная статистика одновременно могут
быть получены значения числовых характеристик нескольких ря-
дов данных, каждому ряду будет соответствовать свой столбец ста-
тистики.
Выборки (*, и(), x-t - варианты, ni - частоты, инструмент «Описа-
тельная статистика» не поддерживает. Для них разработан макрос
Summary, возвращающий значения основных числовых характери-
стик, величины ek и as вычисляются по формулам, приведенным в [6].
Задача 1.9. По заданному распределению выборки
х, 12 14 16 18 20 22
nt 5 15 50 16 10 4
найти:
1) выборочную среднюю;
2) выборочную дисперсию;
3) исправленную выборочную дисперсию;
4) среднее квадратическое отклонение;
5) асимметрию;
6) эксцесс.
Технология решения. Вызываем макрос Summary, вводим и вы-
деляем выборочные данные:
Инструмент Описательная статистика
37
Рис. 1.51
Запуск макроса на исполнение возвращает:
Рис. 1.52
Ответ: см. рис. 1.52.
Код макроса Summary
Sub Summary()
Dim n As Integer, x() As Single, m() As Integer, h As Integer,
c As Single, d As Single, t As Single, s As Single, a As Single, e As Single
n = Selection.Columns.Count
ReDim x(l To n): ReDim m(l To n)
For i = 1 To n
x(i) = Cells(l, i).Value: m(i) = Cells(2, i).Value
Next
c = 0: h = 0
For i = 1 To n
с = c + x(i) * m(i)
h = h + m(i)
Next
c=c/h:d=0:a=0:e=0
For i = 1 To n
d = d + (x(i) - c) A 2 * m(i)
a = a + (x(i) - c) A 3 * m(i)
e = e + (x(i) - c) A 4 * m(i)
Next
d = d / h: t = h * d / (h - 1): s = Sqr(d)
a = a/ h/ sA3: e = e/ h/ sA4-3
d = FormatNumber (d, 3): t = FormatNumber (t, 3): s = FormatNumber(s, 3)
a = FormatNumber(a, 3): e = FormatNumber(e, 3)
MsgBox ("1. Хсред =" & c & Chr(13) & "2. D =" & d & Chr(13) & "3. Dncn =" & t
& Chr(13) & "4. s =" & s & Chr(13) & "5. As =" & a & Chr(13) & "6. Ek =" & e)
End Sub
38
Теория вероятностей и математическая статистика в MS Excel
§6. Встроенные средства
корреляционного анализа
1. По заданной совокупности точек (х,, z/,), i = 1,2,..., п, коэффициент
корреляции
1-=^----------
вычисляет встроенная функция КОРРЕЯ или ПИРСОН. Для вы-
числения коэффициента ковариации
Ц = -£(г, -у)
"Я
предназначена встроенная функция КОВАР. Например, пусть вход-
ные данные (х,, г/(), как на рис. 1.53.
A J В I i с
1 1 49.1 I
2 зТ" 55.3
3 6 52.5
/4 ' 8i 63.7
5 121 60,4
6 _15 72.8 I
Рис. 1.53
Тогда, открывая диалоговое окно КОРРЕЯ и заполняя поля, полу-
чаем значение коэффициента корреляции:
Рис. 1.54
Встроенные средства корреляционного анализа
39
Команда ОК вставляет значение коэффициента корреляции в ак-
тивную ячейку. Аналогично находится значение коэффициента кова-
риации.
Значения коэффициентов выборочного Y на X уравнения прямо-
линейной регрессии ух =kx + b находятся встроенными функциями
НАКЛОН и ОТРЕЗОК, соответственно. Порядок ввода данных в их
диалоговые окна является существенным, сначала вводятся значения
г/р а затем х,. Пример на рис. 1.55.
Рис. 1.55
Поместим значения k, b в ячейки С1, D1, соответственно (рис. 1.56),
и построим выборочное уравнение прямолинейной регрессии.
1. В ячейку Е1 введем формулу =$C$1*A1+$D$1 и копируем ее
в ячейки Е2:Е6:
2. Выделим диапазон А1:А6, а затем, нажимая и удерживая кла-
вишу Ctrl, диапазон Е1:Е6.
3. Запуская графический редактор Мастер диаграмм, приходим
(Диаграмма => Точечная) к графику (рис. 1.57).
40
Теория вероятностей и математическая статистика в MS Excel
ВЫБОРОЧНОЕ УРАВНЕНИЕ ПРЯМОЛИНЕЙНОЙ
РЕГРЕССИИ
Рис. 1.57
Столбец значений Е1:Е6, показанных на рис. 1.56, можно полу-
чить и с помощью встроенной функции ТЕНДЕНЦИЯ.
1. Выделим, например, диапазон F1:F6.
2. Откроем диалоговое окно ТЕНДЕНЦИЯ и заполним поля сле-
дующим образом:
Рис. 1.58
3. Тогда, нажимая F2 и Ctrl+Shift+Enter, получим те же значения,
что и в столбце Е1:Е6 (рис. 1.59).
Если в поле новых значений х задать, действительно, диапазон
новых значений, то в столбце F будут значения функции kx + b, вы-
Встроенные средства корреляционного анализа
41
F1 - Д7=ТЕНДЕНЦИЯ(В1"В6;А1:А6;А1: А6; 1)}
А в С I р Е, F
1 1 49,1 1.433922 48,21225 49,64617 49,64617
2 3 55,3 52,51402 52,51402
3 6 52,5 56,81578 56,81578
4 8 83.7 59,68363 59,68363
5 12 60,4 65,41932 65,41932
6 15 72.8! — 69,72108 69,72108
Рис. 1.59
численные для них. С помощью функции ТЕНДЕНЦИЯ можно вы-
числять и значения в одной точке, указывая ячейку, в которой она
находится, в поле новых значений. Однако в этом случае лучше
воспользоваться предназначенной для этого встроенной функцией
ПРЕДСКАЗ, имеющей более простое диалоговое окно.
2. Встроенная функция ЛИНЕЙН возвращает одновременно оба
коэффициента уравнения у v = kx + b и, по запросу пользователя, до-
полнительную статистику.
Пусть введены данные, как на рис. 1.53. Выделим диапазон 5x2,
например, D1:E5, откроем диалоговое окно ЛИНЕЙН и заполним
поля, как на рис. 1.60.
Рис. 1.60
Тогда команды F2, Ctrl+Shift+Enter возвращают в диапазоне
D1 ;Е5 таблицу значений десяти важнейших параметров:
42
Теория вероятностей и математическая статистика в MS Excel
DI - £ {=ЛИНЕИН(В1:В6.А1:АБ;1;1))
Рис. 1.61
Наименования параметров приведены в таблице 1.5.
Таблица 1.5
к - угловой коэффициент b - свободный член
ст, - стандартная ошибка - стандартная ошибка
R* - коэффициент детерминации ст - стандартная ошибка по у
(л “ 2) - наблюдаемое F-значение Число степеней свободы
£ (.V i, - У У ~ регрессионная сумма квад- ратов £ (у, ~ У, У ~ остаточная сумма квадратов 1 f
Для вычисления стандартной ошибки а, на рис. 1.61 ее значение
в ячейке ЕЗ, имеется специальная встроенная функция CTOIIIXY, ее
диалоговое окно со ссылками на рассматриваемые данные показано
на рис. 1.62.
Рис. 1.62
Встроенные средства корреляционного анализа
43
3. В MS Excel имеется уникальный инструмент Линия тренда,
возвращающий:
• график аппроксимирующей функции;
• уравнение аппроксимирующей функции;
• коэффициент достоверности аппроксимации.
Инструмент вызывается через контекстное меню, открываемое
правой кнопкой мыши, когда стрелка курсора указывает на одну из
точек диаграммы корреляционного поля.
Пусть, например, по данным, показанным на рис. 1.53, построена
диаграмма:
Корреляционное поле
РИС. 1.63
Указывая стрелкой курсора одну из точек корреляционного поля,
и щелкая правой кнопкой мыши, открываем контекстное меню:
Формат рядов данных...
Тип диаграммы... Исходные данные...
Добавить линию тренда...
Очистить
Рис. 1.64
Опция Добавить линию тренда вызывает диалоговое окно Линия
тренда:
44
Теория вероятностей и математическая статистика в MS Excel
Рис. 1.65
В нем можно установить один из шести типов построения линии
тренда, выделяя одну из картинок. Выберем Линейная и перейдем на
вкладку Параметры, где отметим галочками параметры (рис. 1.66):
• показывать уравнение на диаграмме;
• поместить на диаграмму величину достоверности аппроксима-
ции /?2.
Рис. 1.66
Макрос Correlation 45
Тогда команда ОК приведет диаграмму, показанную на рис. 1.63,
к виду, представленному на рис. 1.67. Щелчок по уравнению линии,
приведенному на диаграмме, окружает уравнение рамкой. Взяв за нее
«левой кнопкой мыши» ее, а с ней и содержимое, можно перемещать.
Построенная часть линии тренда автоматически продлевается вправо
и влево, на число шагов прогноза, установленное на вкладке Пара-
метры. Аналогичным образом проводятся построения других типов
линий тренда.
§7. Макрос Correlation
Макрос Correlation по корреляционной таблице автоматически нахо-
дит значения основных параметров и выборочные уравнения прямо-
линейных регрессий (Y на X и X на Y). Например, введем на рабочий
лист таблицу 1.4 и выделим ее (рис. 1.68).
Рис. 1.68
46
Теория вероятностей и математическая статистика в MS Excel
Тогда запуск макроса Correlation на исполнение возвращает:
Рис. 1.69
Код макроса Correlation
Sub Correlation ()
Dim m As Integer, n As Integer, v As Integer, x() As Single, у() As Single,
nx() As Integer, ny() As Integer, u() As Integer, mx As Single, my As Single,
sx As Single, sy As Single, r As Single, ml As Integer, nl As Integer, _
s2 As Single, k As Single, b As Single, kl As Single, Ы As Single,
Str As Variant, Strl As Variant
m = Selection.Rows.Count - 1
n = Selection.Columns.Count - 1
ReDim x(l To m): ReDim y(l To n): ReDim nx(l To m)
ReDim ny(l To n): ReDim u(l To m, 1 To n)
v = Application.WorksheetFunction.Sum(Range(Cells(2, 2), Cellsd + m, 1 + n)))
mx = 0: s2 = 0
For i = 1 To m
x(i) = Cellsd + i, 1).Value
nx(i) = Application. WorksheetFunction. Sum (Range (Cells (1 + i, 2), Cellsd + i, 1 + n)))
mx = mx + x(i) * nx(i)
s2 = s2 + x(i) A 2 * nx(i)
Next
ml = Application.WorksheetFunction.Sum(nx)
mx = mx / ml
sx = s2 / ml - mx A 2
sx = Sqr (sx)
my = 0: s2 = 0
For i = 1 To n
y(i) = Cellsd, i + 1).Value
ny(i) = Application.WorksheetFunction.Sum(Range(Cells(2, i + 1), Cells(m +1, i + 1)))
my = my + y(i) * ny(i)
s2 = s2 + y(i) A 2 * ny(i)
Next
m2 = Application.WorksheetFunction.Sum(ny)
my = my / m2
sy = s2 / m2 - my A 2
sy = Sqr(sy)
For i = 1 To m
Тесты надстройки Анализ данных
47
For j = 1 То п
u(i, j) = Cellsfi + 1, j + 1).Value
Next
Next
r = 0
For i = 1 To m
For j = 1 To n
r = r + u (i, j) * (X (i) - mx) * (y( j) - my)
Next
Next
r=r/v/sx/sy
k = r * sy / sx: b = my - k * mx
kl = r * sx / sy: bl = mx - kl * my
mx = FormatNumber(mx, 3): my = FormatNumber(my, 3)
sx = FormatNumber(sx, 3): sy = FormatNumber(sy, 3)
k = FormatNumber(k, 3): kl = FormatNumber(kl, 3)
b = FormatNumber(b, 3): Ы = FormatNumber(bl, 3)
r = FormatNumber(r, 3)
If b > 0 Then
Str = "+" & b
Else
Str = & Abs(b)
End If
If b = 0 Then
Str = ""
End If
If bl > 0 Then
Strl = "+" & bl
Else
Strl = & Abs(bl)
End If
If Ы = 0 Then
Strl = ,,,,
End If
MsgBox ("1. Mx =" & mx & Chr(13) & "2. My =" & my & Chr(13)
& "3. sx =" & sx & Chr(13) & "4. sy =" & sy & Chr(13)
& "5. r =" & r & Chr(13) & "6. у =" & k & "x " & Str & Chr(13)
& "7. x =" & kl & "y " & Strl)
End Sub
§8. Тесты надстройки Анализ данных
1. Двухвыборочный F-тест для дисперсии - по независимым выбор-
кам xtti = l,zw, ур j = 1,п, из нормальных генеральных совокупностей,
проверяет гипотезу Но: s* = Sy при альтернативной гипотезе Нх: > $2,
если $г2 > sj, ил и Я, : $ £ < $2, если Яо: s’2 < $2. Статистика критерия:
48
Теория вероятностей и математическая статистика в MS Excel
В список возвращаемых значений входят F- наблюдаемое (выбо-
рочное, эмпирическое) значение критерия и односторонняя критиче-
ская точка F распределения Фишера. При FH > 1 (FH < 1) гипотеза Яо
принимается, если FH < F (FH > F ), в противном случае принимается
альтернативная гипотеза. Аналогичным образом при а/2 проверяется
Я() при альтернативной гипотезе : s* * s*.
Задача 1.10. Для сравнения точности двух станков-автоматов
взяты две пробы (выборки), объемы которых пх = 10 и п2 = 8. В ре-
зультате измерения контролируемого размера отобранных изделий
получены следующие результаты:
Таблица 1.6
л; 1,08 1.10 1,12 1,14 1,15 1,25 1,36 1,38 1,40 1,42
У, 1,11 1,12 1,18 1,22 1,33 1,35 1,36 1,38
Можно ли считать, что станки обладают одинаковой точностью
[Но: D(X) = D(Y)], если принять уровень значимости а = 0,1 и в каче-
стве конкурирующей гипотезы Н{. D(X) # D(Y)?
Технология решения. Вводим выборочные данные на рабочий лист:
_ .AlBlClDlElFlGlH | I | J |
i I 1,18 1,1 1,12 1,14 1,15 1,25 1,36 1,38 1.4 1,42
2j 1,11 1,12 1,18 1,22 1,33 1,35 1.38 1,38
Рис. 1.70
Открываем диалоговое окно Двухвыборочный F-тест для дис-
персии и вводим данные, как показано на рис. 1.71.
Рис. 1.71
Тесты надстройки Анализ данных
49
Команда ОК возвращает:
____________М____________| N | О
Двухвыборочный F-тест для дисперсии
____________________________Переменная 1 Переменная 2
Среднее 1,240 1,256
Дисперсия 0,019 0,012
Наблюдения 10,000 8,000
df 9,000 7,000
F 1,511
P(F<=f) одностороннее 0,300
F критическое одностороннее 3,677
Рис. 1.72
Так как F- 1,511 < 3,677 = F , то гипотеза Яо принимается.
Ответ: да.
К задаче 1.10 можно применить также встроенную функцию
ФТЕСТ, возвращающую одностороннее значение F-теста. Гипотеза
Яо принимается, если значение Р/2 > а, в противном случае принима-
ется альтернативная гипотеза. Например, для задачи 1.10, как видно
из диалогового окна, показанного на рис. 1.73, получаем Р/2 > а = 0,1,
то есть гипотеза Яо принимается.
Рис. 1.73
2. Парный двухвыборочный t-тест для средних - по парным на-
блюдениям (х-, г/,), / = 1,и, нормально распределенных совокупностей
проверяет гипотезу Я(): X = У при альтернативной гипотезе Я! : X * У
или Я( : X > У (Я, : X < У), если х >у (х <у). Статистика критерия:
Т =
п
50 Теория вероятностей и математическая статистика в MS Excel
= xi - z/-. В списке возвращаемых значений: t-статистика - наблю-
даемое значение критерия, £К|)1 - односторонняя критическая точка,
^кр2 ” двусторонняя критическая точка. Если 111 < tK^ то гипотеза Нп
принимается, в противном случае принимается Я, : X * У.
Если | 11 < t р то гипотеза Яо принимается, в противном случае
принимается односторонняя альтернативная гипотеза Нх (X > У или
Х<У).
Задача 1.11. На двух аналитических весах, в одном и том же по-
рядке, взвешены 10 проб химического вещества и получены следую-
щие результаты взвешиваний (в мг):
Таблица 1.6
.V, 25 30 28 50 20 40 32 36 42 38
У. 28 31 26 52 24 36 33 35 45 40
При уровне значимости 0,01 установить, значимо ли различаются
результаты взвешиваний, в предположении, что они распределены
нормально.
Технология решения. Вводим выборочные данные на рабочий
лист (рис. 1.74).
..... Iв_1._C__L_D._1._ЕGJ.__J_„L-J-.J
1 I 25 30 28 50 20 40 32 36 42 38
’ 2 ”] 28 31 26 52 24 36 33 35 45 40
Рис. 1.74
Открываем диалоговое окно Парный двухвыборочный t-тест для
средних и вводим данные (рис. 1.75). По команде ОК получаем ре-
зультаты (рис. 1.76).
Рис. 1.75
Тесты надстройки Анализ данных
51
Парный двухвыборочный t-тест для средних
Переменная 1 Переменная 2
Среднее 34,100 35.000
Дисперсия 78,767 76,222
Наблюдения 10,000 10,000
Корреляция Пирсона 0,959
Гипотетическая разность сред 0.000
df 9,000
t-статистика -1.132
Р(Т<=t) одностороннее 0,143
t критическое одностороннее 2,821
P(T<=t) двухстороннее 0.287
t критическое двухстороннее 3.250
Рис. 1.76
Так как | f-статистика | = 1,132 < 3,250 = £К|)2, то результаты взвеши-
ваний различаются незначимо.
Ответ: нет.
3. Двухвыборочный t-тест с одинаковыми дисперсиями приме-
няется, если сравниваются средние нормальных генеральных сово-
купностей, дисперсии которых неизвестны и одинаковы (малые не-
зависимые выборки).
Задача 1.12. Из двух партий изделий, изготовленных на одина-
ково настроенных станках, извлечены малые выборки, объемы кото-
рых п = 10 и тп = 12. Получены следующие результаты:
Таблица 1.7
Таблица 1.8
л; 3,4 3,5 3,7 3.9
ni 2 3 4 1
у. 3.2 3.4 3,6
т. 2 2 8
Требуется при уровне значимости 0,02 проверить гипотезу
Яо: М(Х) = Л/(У) о равенстве средних размеров изделий при конку-
рирующей гипотезе Нх: М(Х) M(Y). Предполагается, что случайные
величины X и Y распределены нормально.
Технология решения. Вводим выборочные данные на рабочий
лист (рис. 1.77).
А В I С I D Е F G Н I ' J К L
J
2
3,4 3,4| 3,5 3,5____3.51
3.2 3.2 3.4 3.4 3.6
3.7 3.7 3.7
3.6 3.6- 3,6
3,7 3.9
3.6 3.6
3.6 3,6
Рис. 1.77
52
Теория вероятностей и математическая статистика в MS Excel
С помощью двухвыборочного F-теста для дисперсий проверяем
гипотезу о равенстве генеральных дисперсий. Открываем диалоговое
окно теста и вводим данные:
Двухвыборочный F-тест ддя дисперсии
Входные данные
Интервал переменной 1:
Интервал переменной &
CJ Иеткн
^льфа: [о>О1 |
Параметры вывода
0 Выходной интервал:
Новый рабочий лист:
О Новая рабочая книга
'$А$1 :$J$1
[|A$2:$L$2
I <* I
[ Отмена j
[ Справка ]
Рис. 1.78
Команда ОК возвращает:
Двухвыборочный F-тест для дисперсии
Переменная 1 Переменная 2
Среднее 3,600 3,500
Дисперсия 0,027 0,025
Наблюдения 10,000 12,000
df 9,000 11.000
F 1,048
P(F<=f) одностороннее 0,463
F критическое одностороннее 4,632 •
Рис. 1.79
Так как F= 1,048 < 4,632 = FKp, то гипотеза о равенстве генеральных
дисперсий принимается. Открываем диалоговое окно Двухвыбороч-
ный t-тест с одинаковыми дисперсиями и вводим данные:
Тесты надстройки Анализ данных
53
Рис. 1.80
Команда ОК возвращает результаты:
Двухвыборочный t-тест с одинаковыми дисперсиями__________
___________________________Переменная 1 Переменная 2
Среднее____________________3,600 3,500
Дисперсия 0,027 0,025
Наблюдения 10,000 12,000
Объединенная дисперсия 0,026
Гипотетическая разность сре 0,000
df 20,000
t-статистика 1,448
P(T<=t) одностороннее 0,081
t критическое одностороннее 2,197
P(T<=t) двухстороннее 0,163
t критическое двухстороннее 2,528___________ t
Рис. 1.81
Так как f-статистика = 1,448 < 2,528 = t nnvvf.Tnn, то принимается
гипотеза М(Х) = Л/( У).
Ответ: гипотеза М(Х) = Л/(У) принимается.
Аналогичным образом применяется двухвыборочный t-тест с раз-
личными дисперсиями.
К задачам 1.11,1.12 можно применить также встроенную функцию
ТТЕСТ(массив1;массив2; хвосты; тип).
Она возвращает Р-значение t-теста.
Аргументы функции:
• массив! - выборочные значения первого признака;
54
Теория вероятностей и математическая статистика в MS Excel
• массив2 - выборочные значения второго признака;
• хвосты - при значении 1 тест односторонний, при значении 2 -
двухсторонний;
• тип - вид t-теста, значения:
- 1 - двухвыборочный парный;
- 2 - двухвыборочный с равными дисперсиями;
- 3 - двухвыборочный с неравными дисперсиями.
Гипотеза Нп принимается, если полученное значение Р > а, в про-
тивном случае принимается альтернативная гипотеза. Например, для
задачи 1.12, как видно из диалогового окна, показанного на рис. 1.82,
получаем Р> а = 0,02, то есть гипотеза Но принимается.
Рис. 1.82
Задача 1.13. При уровне значимости а = 0,05 проверить крите-
рием Пирсона гипотезу о нормальном законе распределения призна-
ка X заданного выборочными данными:
х, 1,3 2,0 2,7 3,4 4,1
и,- 5 9 19 И 6
Технология решения.
1. В диапазон А1:В5 вводим выборочные данные (рис. 1.83)
и находим в ячейке В6 объем выборки, применяя формулу
=СУММ(В1:В5).
2. Поскольку придется вычислять выборочную дисперсию, то
в ячейку С1 вводим формулу =А1Л2 и копируем ее в ячейки
С2:С5.
3. По формуле =СУММПРОИЗВ(А1:А5;В1:В5)/В6 вячейкеО!
вычисляем выборочнуюсреднюю,вячейкеО2находимвыбороч-
Тесты надстройки Анализ данных
55
нуюдисперсию,поформуле.=СУММПРОИЗВ(С1:С5;В1:В5)/
B6-D1A2.
4. В ячейке D3 по формуле = D2A(l/2) находим о. Подготови-
тельные вычисления проведены, переходим к основным вы-
числениям.
5. В ячейку Е1 вводим формулу =(A1-$D$1)/$D$3 и копируем
ее в ячейки Е2:Е5.
6. В ячейку F1 вводим формулу =ехр((-1)*Е1А2/2)/(2*ПИ())А
(1/2) и копируем в ячейки F2:F5.
7. В столбце G, так же копированием, вычисляем теоретические
частоты, формула ячейки G1 =F1*$B$6*($B$2-$B$1)/$D$3.
8. В ячейку Н1 вводим формулу =(G1-B1)A2/G1 и копируем
в диапазоне Н2:Н5.
9. Выделяем диапазон Н1:Н5 и кнопкой Е вставляем в ячейку
Н6 наблюдаемое значение критерия:
Н1 - A =(G1-B1)A2/G1
А в С D ] I Е F G Н
1 у. 5 1,69 2,756 -1,84309 0,07299: 3,233833 0,964597
2 2 9, лГ 0,6240641 -0,95699 0,252371 11,18134 0.425551
3 2,7 19 7,29 0,789977 -0,07089 0,397941 17,63081 0,106329
4 3.4 Й] 11,56 0,815213 0,286154 12,67808 0,222111
5 4.1 6: 16,81 1,701315 0,093839 4,157544 0,816502
6 50 2,535091
Рис. 1.83
10. Открываем диалоговое окно ХИ2ОБР и находим критическое
значение:
Рис. 1.84
56
Теория вероятностей и математическая статистика в MS Excel
Так как наблюдаемое значение критерия 2,535 меньше критиче-
ского 5,991, то гипотеза Яо принимается.
Ответ: гипотеза Яо принимается.
Теоретические частоты столбца G можно вычислить и более ко-
ротким способом. Пусть выполнены пункты 1-4. Выделим ячейку
Е1, откроем диалоговое окно НОРМРАСП и зададим данные:
Рис. 1.85
Командой ОК вставим расчетную формулу в Е1 и скопируем ее
в ячейки Е2:Е5. В ячейку F1 введем формулу = Е1*$В $6*($А$2-
$А$1), копируя ее в ячейки F2:F5, получаем теоретические частоты:
А I В С . D I I Е F
1 1.3 5 1,69 2,756 0.092395 3.233833
_2' 2 9 4 0,624064 0.319467 11,18134
3 2.7 19 7.29 0,789977 0.503738 17,63081
3.4 11 11,56 0,362231 12,67808
5 4.1 6 16,81 0,118787 4,157544
6 501 I |
Рис. 1.86
По теоретическим частотам проверить гипотезу о нормальном
законе распределения, как и любого другого, можно функцией ХИ-
2ТЕСТ:
Тесты надстройки Анализ данных
57
Рис. 1.87
Так как полученное значение 0,64 больше уровня значимости 0,05,
то отвергать гипотезу Яо нет оснований.
Глава 2
Непараметрические
критерии
для независимых
выборок
§1 . Критерий Колмогорова-
Смирнова........59
§2 . Критерий
Катценбайссера-Хакли ..65
§3 . Критерий Вилкоксона ... 69
§4. Критерий
Манна-Уитни......76
§5 . Критерий серий
Вальда-Вольфовитца ...82
§6 . Сериальный критерий
Рамачандрана-
Ранганатана.....94
Критерий Колмогорова-Смирнова
59
Рассматриваемые в этой главе критерии по независимым выборкам
{х,}, i = 1,7л и {z/J, j = 1,72, если не указано иное, проверяют основную
(нулевую) гипотезу Я(): F^t) = F2(t), гдеF/0 = Р(Х<Г),F2(t) = P(Y< 0,
состоящую в однородности непрерывных генеральных совокупно-
стей X и Y, при одной из альтернативных (конкурирующих) гипотез:
l)F'(t)*F2(t)- 2)F1(0<F2(0; 3)F,(0 > F2(t).
Последние гипотезы, соответственно, записываются также в виде
1)Х#У; 2)Х>У; 3)Х<У.
§1. Критерий Колмогорова-
Смирнова
В критерии Колмогорова-Смирнова в качестве критерия можно при-
нять ([2], стр. 84) статистику:
£) = sup|F2(t) - Fl(t)|.
При 3 < п < т < 20 критические значения £>кр табулированы ([2],
таблица 6.5а). При т > 20 выполняется:
некоторые значения Л приведены в таблице 2.1.
Таблица 2.1
а 0.10 0,05 0,02 0,01
Л 1,22 1,36 1,52 1,63
Если DB < то принимается гипотеза Я(): F/0 = Е2(0, в против-
ном случае Я() отвергается и принимается гипотеза : F^t) * F2(t).
Задача 2.1. Производительность труда двух смен предприятия
характеризуется выборками:
х,: 2 5 5 6 8 8
г/, 1 2 4 7 7
При уровне значимости а = 0,1 проверить критерием Колмогоро-
ва-Смирнова гипотезу Я() об одинаковой производительности обеих
смен (X = У) при альтернативной гипотезе : X # У.
Технология решения.
1. Вводим выборочные данные:
60
Непараметрические критерии для независимых выборок
Рис. 2.1
2. Копируем данные диапазона A1:F1 и вставляем в диапазон
A4:F4, в ячейки диапазона A5:F5 вводим значение 1 (номер вы-
борки).
3. Копируем данные диапазона А2:Е2 и вставляем в диапазон
G4:K4, в ячейки диапазона G5:K5 вводим значение 2 (номер
выборки).
4. Выделяем диапазон А4:К4 и командами Данные => Сортиров-
ка (с расширением, столбцы, по возрастанию) проводим сорти-
ровку.
5. Переходим от диапазона данных А4:К5 к диапазону A4:G5,
удаляя со сдвигом влево столбцы с повторяющими первыми
элементами. В ячейках ниже значений, принадлежащих обе-
им выборкам, ставим 3. В данном случае такая ячейку одна В5.
В результате получаем:
”Т1 1 2 4 5 6 7 8
5J 2 3 2 1 1 2 1
Рис. 2.2
6. В ячейку А7 вводим формулу =ЕСЛИ(ИЛИ(А5=Г,А5=3);СЧЁ-
ТЕСЛИ($А1:$ЕГ,А4)) и копируем ее в остальные ячейки диа-
пазона A7:G7, значения ЛОЖЬ удаляем.
7. В ячейку А8 вводим формулу=ЕСЛИ(ИЛ И(А5=2;А5=3);СЧЁ-
ТЕСЛИ($А2:$Е2;А4)) и копируем ее в остальные ячейки диа-
пазона A8:G8, значения ЛОЖЬ удаляем, что дает:
ш
1
1 2 1 2
1 1 2
Рис. 2.3
8. В ячейке А10 записываем формулу накопленных частот первой
выборки =СУММ($А7:А7) и копируем ее в остальные ячейки
диапазона A10:G10.
9. Аналогично, в ячейке АН записываем формулу =СУММ
($ А8:А8) и копируем ее в остальные ячейки диапазона А11 :G 11,
что дает:
Критерий Колмогорова-Смирнова 61
ГйП 0 1 1 3 4 4 6
[_ljj 1 2 3 3 3 5 5
Рис. 2.4
10. В ячейку А13 вводим формулу =A10/$G$10 и копируем ее
в остальные ячейки диапазона A13:G13.
И. Аналогично, в ячейку А14 вводим формулу =A11/$G$ И и ко-
пируем ее в остальные ячейки диапазона А14:G 14. В результате
в диапазоне A 13:G 14 получаем значения эмпирических функ-
ций распределений выборок:
ИО 0.16667 0,16667 0.5 0,66667 0,66667 1
0.2 0.4 0.6 0.6 0.6 1 1
Рис. 2.5
12. В ячейку А16 вводим формулу =ABS(A14-A13) и копируем ее
в остальные ячейки диапазона A16:G16.
13. В любой свободной ячейке по формуле =MAKC(A16:G16) по-
лучаем наблюдаемое значение статистики DH = 0,4333.
14. По таблице критических точек [2] находим £)кр(6; 0,0,1) = 0,8.
Так как DH = 0,4333 < 0,8 = £)кр, то принимается гипотеза Но.
Ответ: принимается гипотеза Яо.
Макрос I_test_К_S, применяемый стандартно, проводит все вы-
числения автоматически и возвращает полученные результаты, и вы-
вод о том, какая гипотеза принимается. Таблица критических точек
(5 < п < т < 20) введена как процедура-функция F(x, г/, z) = DKV(m, п, k),
k - порядковый номер уровня значимости критерия.
Например, для задачи 2.1, когда входные данные, как на рис. 2.1,
запуск макроса на исполнение открывает окно, в котором вводится
номер выбранного уровня значимости критерия:
Рис. 2.6
62
Непараметрические критерии для независимых выборок
Команда ОК возвращает полученные ранее результаты:
Рис. 2.7
Задача 2.2. По данным таблицы 2.2 и уровню значимости
а = 0,05 проверить критерием Колмогорова-Смирнова нулевую ги-
потезу Но однородности признаков X и Y, принимая Hx:X*Y.
Таблица 2.2
Варианты л; 12 14 15 18 21 25 26 27 30 31 32 35 38 41 43 46 48 52 56 57 60 65 68 73 75
Варианты yt 11 13 16 17 19 20 22 23 24 26 2В 29 33 34 36 37 3 9 40 42 44 45 47 49 51 53 55 58 61 63 66
Технология решения. Учитывая объемы выборок, вводим на рабо-
чий лист сначала данные второй выборки, а затем первой, и выделяем
диапазон сданными (на рис. 2.8 он показан частично):
Рис. 2.8
Запуск макроса на исполнение вызывает окно ввода номера уров-
ня значимости критерия:
Рис. 2.9
Критерий Колмогорова-Смирнова
63
Команда ОК возвращает:
Рис. 2.10
Ответ: гипотеза однородности принимается.
Код макроса
Sub I_test_K_S()
Dim m As Integer, x() As Single, nx() As Single, n As Integer, y() As Single,
ny() As Single, sx As Single, sy As Single, u As Integer, v As Integer,
Fx() As Single, Fy() As Single, d As Single, t As Single, h As Integer, _
c As Single, lamda(l To 4) As Single, str As String, a As Integer, b As Integer
lamda(l) = 1.22: lamda(2) = 1.36: lamda(3) = 1.52: lamda(4) = 1.63
m = Selection.Columns.Count
For i = 1 To Selection.Columns.Count
If Cells (2, i) .Value > 0 Then n = i
Next
a = m: b = n
ReDim x(l To m): ReDim nx(l To m): ReDim y(l To n): ReDim ny(l To n)
For i = 1 To m
x(i) = Cells(1, i).Value
nx(i) = 1
Next
For i = 1 To n
у(i) = Cells(2, i).Value
ny(i) = 1
Next
Linel:
For i = 1 To m - 1
If x(i) = x(i + 1) Then
nx(i) = nx(i) +1
If i < m - 1 Then
For к = i + 1 To m - 1
x(k) = x (k + 1)
Next
ReDim Preserve x(lTom-l): ReDim Preserve nx(l To m - 1): m = m - 1
GoTo Linel
Else
ReDim Preserve x(l To m - 1): ReDim Preserve nx(l To m - 1): m = m - 1
End If
End If
64
Непараметрические критерии для независимых выборок
Next
Line2:
For i = 1 То n - 1
If y(i> = y(i + 1) Then
ny(i) = ny(i) + 1
If i < n - 1 Then
For k = i + 1 To n - 1
У(k) = y(k + 1)
Next
ReDim Preserve y(l To n - 1): ReDim Preserve ny(l Ton-1): n=n-l
GoTo Line2
Else
ReDim Preserve y(l To n - 1): ReDim Preserve ny(l Ton-l):n=n-l
End If
End If
Next
sx = Application.WorksheetFunction.Sum(nx)
sy = Application.WorksheetFunction.Sum(ny)
u = Application.WorksheetFunction.Min(x, y)
v = Application.WorksheetFunction.Max(x, y)
For i = 1 To m
nx(i) = nx(i) / sx
Next
For i = 1 To n
ny(i) = ny(i) / sy
Next
For i = 2 To m
nx(i) = nx(i - 1) + nx(i)
Next
For i = 2 To n
ny(i) = ny(i - 1) + ny(i)
Next
ReDim Fx(u To v): ReDim Fy(u To v)
For k = u To v
If k < x(l) Then Fx(k) = 0
If k < y(l> Then Fy(k) = 0
If k >= x(m) Then Fx(k) = 1
If k >= у(n) Then Fy(k) = 1
Next
For i = 1 To m - 1
For k = x(i) To x(i + 1) - 1
Fx(k) = nx(i)
Next
Next
For i = 1 To n - 1
For k = y(i) To y(i + 1) - 1
Fy(k) = ny(i)
Next
Next
d = 0
Критерий Катценбайссера-Хакли
65
For i = u To v
t = Fx(i) - Fy(i): t = Abs(t): d = Application.WorksheetFunction.Max(d, t)
Next
d = FormatNumber(d, 3)
h = Application.InputBox("Введите номер уровня значимости критерия:" & Chr(13)
& " 1. 0,1; 2. 0,05; 3. 0,02; 4. 0,01", Туре:=1)
Select Case m
Case Is <= 20
c = F(a, b, h)
Case Is > 20
c = lamda(h) * Sqr((a + b) / a / b)
End Select
c = FormatNumber(c, 3)
If d < c Then
str = " Ho."
Else
str = " Hl."
End If
MsgBox ("Значение статистики: D = " & d & ". Критерий принятия Ho: D < " & с
& ". Принять: " & str)
End Sub
§2. Критерий Катценбайссера-
Хакли
Критерий однородности Катценбайссера-Хакли (Katzenbeisser-
Hackl test), применяемый при т = п, также связан с эмпирическими
функциями распределений выборокх, г/., i = 1,п. Статистикой Т в нем
является число точек, в которых значения эмпирических функций
распределений совпадают [12].
При уровне значимости а гипотеза Но об однородности принима-
ется, если а < Р(Т< Тв) = 1 - Р(Т> Тв), Тв - наблюдаемое значение
критерия,
7'Сп~(
P(T>t) =--
С'2п
Задача 2.3. При уровне значимости 0,1 проверить критерием
Катценбайссера-Хакли нулевую гипотезу Но однородности призна-
ков X, Y, представленных выборками:
х, 22 22 26 27 27 29 33 34 35 36
г/. 18 22 24 25 25 25 27 28 28 30
Альтернативная гипотеза Но: X * У.
66
Непараметрические критерии для независимых выборок
Технология решения. Так же, как при решении задачи 2.1, нахо-
дим эмпирические функции распределений выборок (рис. 2.11).
Рис. 2.11
Откуда следует TR = 2. Вероятность
Р(Т > 2) = 22 -у-
б'го
вычисляем по формуле:
=2А2*ЧИСЛКОМБ(18;8)/ЧИСЛКОМБ(20;Ю)
Она дает Р(Т> 2) = 0,947, то есть Р(Т < 2) = 0,053. Так как а = 0,1 >
0,053 = Р(Т< 2), то нулевая гипотеза отклоняется.
Ответ: принимается гипотеза Hv
Критерий реализован в макросе I_test_K_H, применяемом стан-
дартным образом при п > 5. Например, для задачи 2.3 он возвращает
результаты, полученные непосредственными вычислениями:
Рис. 2.12
Критерий Катценбайссера-Хакли
67
Код макроса
Sub I_test_K_H ()
Dim m As Integer, x() As Single, nx() As Single, n As Integer, y() As Single,
ny () As Single, sx As Single, sy As Single, u As Integer, v As Integer,
Fx() As Single, Fy() As Single, t As Integer, alfa As Single,
str As String, b As Integer, p As Single
m = Selection.Columns.Count
n = m: b = n
ReDim x(l To n): ReDim nx(l To n): ReDim y(l To n): ReDim ny(l To n)
For i = 1 To n
x(i) = Cells(l, i).Value: nx(i) = 1
y(i) = Cells(2, i).Value: ny(i) = 1
Next
Linel:
For i = 1 To m - 1
If x(i) = x(i + 1) Then
nx(i) = nx(i) + 1
If i < m - 1 Then
For k = i + 1 To m - 1
x(k) = x(k + 1)
Next
ReDim Preserve x(l To m - 1): ReDim Preserve nx(l To m - 1): m = m - 1
GoTo Linel
Else
ReDim Preserve x(l To m - 1): ReDim Preserve nx(l To m - 1): m = m - 1
End If
End If
Next
Line2:
For i = 1 To n - 1
If y(i) = y(i + 1) Then
ny (i) = ny (i) + 1
If i < n - 1 Then
For k = i + 1 To n - 1
у(k) = y(k + 1)
Next
ReDim Preserve y(l To n - 1): ReDim Preserve ny(l Ton-l):n=n-l
GoTo Line2
Else
ReDim Preserve y(l To n - 1): ReDim Preserve ny(l Ton-l):n=n-l
End If
End If
Next
sx = Application.WorksheetFunction.Sum(nx)
sy = Application.WorksheetFunction.Sum(ny)
u = Application.WorksheetFunction.Min(x, y)
v = Application.WorksheetFunction.Max(x, y)
For i = 1 To m
nx(i) = nx(i) / sx
68
Непараметрические критерии для независимых выборок
Next
For i = 1 То n
ny(i) = ny(i) / sy
Next
For i = 2 To m
nx (i) = nx (i - 1) + nx (i)
Next
For i = 2 To n
ny(i) = ny(i - 1) + ny(i)
Next
ReDim Fx(u To v): ReDim Fy(u To v)
For k = u To v
If k < x(1) Then Fx(k) = 0
If k < y(l) Then Fy(k) = 0
If k >= x(m) Then Fx(k) = 1
If k >= y(n) Then Fy(k) = 1
Next
For i = 1 To m - 1
For k = x(i) To x(i + 1) - 1
Fx(k) = nx (i)
Next
Next
For i = 1 To n - 1
For k = у(i) To y(i + 1) - 1
Fy (k) = ny (i>
Next
Next
For i = 1 To b
Cells (4, i) .Value = Cells (1, i) .Value
Cells(4, b + i).Value = Cells(2, i).Value
Next
Range(Cells(4, 1), Cells(4, 2 * b)).Sort Keyl:=Range("A4"),
Orderl:=xlAscending, Orientation:=xlLeftToRight
t = 0
For i = 1 To 2 * b
If Fx(Cells(4, 1).Value) = Fy(Cells(4, 1).Value) Then t = t + 1
If i > 1 Then
If Cells(4, i).Value <> Cells(4, i - 1).Value Then
If Fx(Cells(4, i).Value) = Fy(Cells(4, i).Value) Then
t = t + 1
End If
End If
End If
Next
[a6] = t: (B6) = b
Range ("C6") .Formula = "=2 A A6 * COMBIN(2 * B6 - A6, B6 - A6) / COMBIN(2 * B6, В6)"
Range("Об").Formula = "=1-C6"
p = Range("Об").Value
p = FormatNumber(p, 4)
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
If alfa < Range("D6").Value Then
Критерий Вилкоксона
69
str = “ Но."
Else
str = " Hl."
End If
MsgBox ("Значение статистики: " & t & ". Критерий принятия Ho: alpfa < " & р
& ". Принимается: " & str)
Range (Cells(4, 1), Cells (4, 2 * b)).ClearContents
Range("A6:D6").ClearContents
End Sub
§3. Критерий Вилкоксона
Критерий однородности Вилкоксона (Wilcoxon test) относится к так
называемым ранговым критериям и является одним из первых кри-
териев такого типа.
При 6 < т < п < 25 для проверки критерием Вилкоксона на уровне
значимости а справедливости нулевой гипотезы при альтернативной
гипотезе F,(f) # F2(t) надо [6]:
1) расположить варианты в виде одного вариационного неубыва-
ющего ряда;
2) по нему найти сумму рангов WB вариант х(, учитывая, что ранги
несовпадающих вариант равны их порядковым номерам, а для
совпадающих - среднему арифметическому их порядковых но-
меров;
3) по таблице критических точек распределения Вилкоксона ([6],
приложение 10) определить о>||1|Жкр = о>(а/2, т, и);
4) найти верхнюю критическую точку по формуле
®»ерх.кр. - (от + п + 1)пх - wHm Kv .
Если о>11НЖ < WR < о>0ерхкр, то нулевая гипотеза принимается, в про-
тивном случае принимается альтернативная гипотеза.
Если проверяется нулевая гипотеза при альтернативной гипотезе
Fj(x) < F2(x), то есть X > У, то критическая область правосторонняя:
“ юперх кр “ (т + п + 1)пд. - ю,„1Жкр> ю„„Ж1(р = W(a, т, п).
В случае WB < о>кр нулевая гипотеза принимается, в противном
случае принимается альтернативная гипотеза F((x) < F2(x).
Аналогично, если альтернативная гипотеза F^x) > F2(x), то есть
X < У, то критическая область левосторонняя:
№кр = “'ииж.кр = ^(a, т, п).
В случае WB > о>кр нулевая гипотеза принимается, в противном
случае принимается альтернативная гипотеза F/x) > F2(x).
70
Непараметрические критерии для независимых выборок
При п > 25 проверка нулевой гипотезы Fx(x) = Е2(х) при альтер-
нативной гипотезе Ft(x) # F2(x) отличается в этом случае от предыду-
щего только тем, что
(?Л+ Л7Л+ 1)-иг -1
т-п(т + и + 1)
1-а
где квадратные скобки обозначают целую часть, Ф(? ) =------,
Ф(г) - функция Лапласа. При других альтернативных гипотезах
Задача 2.4. При уровне значимости 0,01 проверить критерием
Вилкоксона гипотезу Яо однородности признаков X, Y, заданных вы-
борками:
х, 3 5 6 10 13 17
г/, 1 2 5 7 16 20 22
Принять в качестве конкурирующей гипотезы Нх: Ft(x) # F2(x).
Технология решения.
1. Вводим в одну строку, начиная с ячейки А1, сначала варианты
первой выборки, затем второй. Ниже вводим номера выборок
(1 или 2).
2. Выделяем данные первой строки (А1:М1) и проводим их сор-
тировку (с расширением) по возрастанию.
3. В ячейке А4 записываем формулу:
=РАНГ(А1;$А1:$М1;1)+(СЧЁТЕСЛИ($А1:$М1;А1)-1)*0,5
4. Копируя ее в остальные ячейки диапазона А4:М4, получаем
в нем ранги вариант:
Рис. 2.13
5. В любой свободной ячейке с помощью функции СУММ ЕС-
ЛИ (рис. 2.14) находим сумму рангов вариант первой выборки
№„ = 41,5:
Критерий Вилкоксона
71
Рис. 2.14
6. По таблице критических точек распределения Вилкоксона [6]
находим
а’ш1ж.кр= и'(0,005; 6, 7) = 24.
Поэтому
№„ = (6 + 7 + 1)6-24 = 60.
Так как выполняется ®„„ЖК|) = 24 < (Т|в6л = 41,5 < ®,и.рх К|) = 60, то
гипотеза Яо принимается.
Ответ: гипотеза однородности принимается
Макрос I_test_W при т > 5 проводит вычисления критерия Вил-
коксона автоматически и возвращает полученные результаты, и со-
общение о том, какая гипотеза принимается. Таблица критических
точек введена как процедура-функция g(x, г/, z). Для применения мак-
роса надо ввести выборочные данные в первые две строки рабочего
листа Excel, начиная с ячейки А1, выделить диапазон с данными и
запустить макрос на исполнение.
Например, применим макрос I_test_W к задаче 2.4. Вызываем
макрос и вводим выборочные данные:
Рис. 2.15
Запуск макроса на исполнение открывает окно ввода номера аль-
тернативной гипотезы:
72
Непараметрические критерии для независимых выборок
Рис. 2.16
Команда ОК открывает окно для ввода номера уровня значимости
критерия:
Рис. 2.17
Команда ОК возвращает результаты, полученные ранее непосред-
ственными вычислениями:
Рис. 2.18
Задача 2.5. Эффективность каждого из двух рационов (А и В)
откорма животных характеризуется выборками объемов т = 10 и
п = 12 (в первой строке приведен вес (в кг) животных, откармливае-
мых по рациону А, во второй строке - по рациону В):
х, 24 26 27 27 30 32 33 34 35 36
г/. 21 21 22 23 25 25 25 25 27 27 29 31
Используя критерий Вилкоксона, при уровне значимости 0,05
проверить нулевую гипотезу об одинаковой эффективности рацио-
Критерий Вилкоксона
73
нов А и В, принимая в качестве конкурирующей гипотезы: рацион А
эффективнее рациона В (Н{: F/x) < F2(x), то есть X > У).
Технология решения. Вызываем макрос W_test и вводим данные:
Рис. 2.19
Запуск макроса на исполнение открывает окно ввода номера аль-
тернативной гипотезы:
Рис. 2.20
Команда ОК открывает окно ввода номера уровня значимости
критерия:
Рис. 2.21
Команда ОК возвращает результаты:
Рис. 2.22
74
Непараметрические критерии для независимых выборок
Ответ: корм А эффективнее.
Применение макроса I_test_W к данным задачи 2.2, входные дан-
ные показаны на рис. 2.23 частично, возвращает сообщение (рис. 2.24).
Рис. 2.23
Рис. 2.24
Код макроса
Sub I_test_W()
Dim m As Integer, n As Integer, nl As Integer, n2 As Integer, s() As Single,
r() As Single, tip As Integer, h As Integer, alfa As Single, _
zl As Integer, z2 As Integer, t As Single, str As String
n = Selection.Columns.Count
For i = 1 To n
If Cellsd, i) .Value > 0 Then
m = i
End If
Next
ReDim s(l To m + n): ReDim r(l To m + n)
For i = 1 To m
s(i) = Cells(1, i).Value
Next
For i=m+lTom+n
s (i) = Cells (2, i - m) .Value
Next
For i = 1 To m + n
Cells(4, i).Value = s (i)
Next
For i = 1 To m
r(i) = Application.WorksheetFunction.Rank(s(i), Range(Cells(4, 1),
Cells(4, m + n)), 1)
r(i) = r(i) + (Application.WorksheetFunction.Countlf(Range(Cells(4, 1),
Cells(4, m + n)), s (i)) - 1) * 0.5
Next
w = 0
Критерий Вилкоксона
75
For i = 1 То m
w = w + r (i)
Next
tip = Application.InputBox("Введите номер альтернативной гипотезы:" & Chr(13)
& " 1. X о Y; 2. X > Y; 3. X < Y ", Type:=l)
Select Case n
Case Is <= 25
If tip = 1 Then
h = Application.InputBox("Введите номер уровня значимости критерия:" & Chr(13)
& "1. 0,01; 2. 0,02; 3. 0,05; 4. 0,1.", Туре:=1)
Else
h = Application.InputBox("Введите номер уровня значимости критерия:" & Chr(13)
& "1. 0,005; 2. 0,01; 3. 0,025; 4. 0,05.", Туре:=1)
End If
wl = g(m, n, h)
Linel:
w2 = (m + n + 1) * m - wl
Select Case tip
Case Is = 1
If wl < w And w < w2 Then
str = " Ho."
Else
str = " Hl."
End If
MsgBox ("Значение статистики: " & w _
& ". Область принятия Но: (" & wl & ", " & w2 & ") . Принять:" & str)
Case Is = 2
If w < w2 Then
str = " X - Y."
Else
str = " X > Y."
End If
MsgBox ("Значение статистики: W = " & w & ". Критерий принятия Ho: W < " & w2
& ". Принять: " & str)
Case Is = 3
If wl < w Then
str = " X = Y."
Else
str = " X < Y."
End If
MsgBox ("Значение статистики: W = " & w & ". Критерий принятия Ho: W > " & wl
& ". Принять: " & str)
End Select
Case Else
alfa = Application.InputBox("Введите уровень значимости:", Type:=l)
t = Application.WorksheetFunction.NormSInvd - alfa / 2)
(alO) = t: (Ы0) = m: [clO] = n
Range("D10").Formula = "= RoundDown(((B10 + CIO + 1) * B10 - 1)/ 2 - AlO *
Sqrt(B10 * CIO * (B10 + CIO + 1) / 12), 0)"
wl = Range("D10").Value
76
Непараметрические критерии для независимых выборок
GoTo Linel
End Select
Range(Cells(4, 1), Cells(4, m + n)).ClearContents
Range(Cells(10, 1), Cells(10, 4)).ClearContents
End Sub
§4. Критерий Манна-Уитни
Статистика критерия однородности Манна-Уитни (Mann-Whitney
U test) [12] - число пар (х;, г/(), в которых варианты первой выборки
меньше вариант второй:
V* V* Р’ Х' <
u = LLc‘i- си= п
При перестановке местами выборок статистика [/, обозначим ее
Uv переходит в статистику
I J
1. > Уу
О, л- < //,,
Эти статистики, чтобы не исключались из рассмотрения пары
(х;, г/(), в которых xt = yt, записывают в виде:
f 1, X, < у> f 1, -Г, > yt
U! = c 4 = °’5’ x- =Ур C» = °’5’ -Г- = У>-
[ 0. X-,> yj ' [ o, X, < y,
Тогда выполняется:
Ui + U2 = m • n.
Справедливы формулы:
.. м(/л + 1) „ .. n(n +1) „
I J. = mn + —------Rh U., =/r/ /7 + —----
1 2 1 ' 2 2
В них R' и R2 суммы рангов (порядковых номеров) вариант вы-
борок xt и yj в общем неубывающем вариационном ряду, для совпада-
ющих вариант ранг равен среднему арифметическому их порядковых
номеров, то есть R} - статистика Вилкоксона.
Например, пусть заданы выборки:
х, 2 5 5
У, 3 5 7
Критерий Манна-Уитни
77
Тогда, сравнивая каждое значение первой выборки с каждым зна-
чением второй выборки, получаем:
Ц = 1 + 1 + 1 + 0,5 + 1 + 0,5 + 1 = 6,
U2 = 1 + 0,5 + 1 + 0,5 = 3,
Ц + U2 = 9 = 3 • 3.
Эти же значения можно найти и другим способом. Составляя об-
щий неубывающий вариационный ряд, и выписывая ранги, приходим
к таблице (варианты первой выборки подчеркнуты снизу):
Порядковый номер 1 2 3 4 5 6
Варианты 2 3 5 5 5 7
Ранги 1 2 4 4 4 6
Поэтому:
=1 + 4 + 4 = 9, R2 =2 + 4 + 6=12,
и. = 3-3 + —-9 = 6; и, = 3-3 + — -12 = 3.
*2 1 2
В MS Excel значение Ux находится следующим образом:
1) варианты xt вводятся в диапазон А2:А4, варианты z/7 - в диапа-
зон В 1:D1;
2) в ячейке В2 записывается, и копируется в остальные ячейки
диапазона B2:D4, формула =ЕСЛИ($А2<В$Г,Г,ЕСЛИ($А2
=В$1;1/2;0))
3) в ячейку Е5 вводится формула =CYMM(B2:D4), которая дает
Ц (рис. 2.25).
Нижние критические точки статистики Манна-Уитни табулиро-
ваны ([10], Таблица 63), верхние находятся по формуле
^всрх.кр ^ииж.кр*
При значениях т, п > 20, если гипотеза Яо верна, то параметры
статистики U:
78
Непараметрические критерии для независимых выборок
ТП -22( 772 + 72 + 1)
12
причем при 772, 72 —> оо она асимптотически приближается к нормаль-
ному распределению с этими параметрами. Поэтому в этих случаях
критические точки для двусторонней критической области находят-
о а . а
ся как квантили этого распределения уровней — и 1-, а для одно-
сторонних критических областей эти уровни а и 1 - а, соответствен-
но, для левосторонней и правосторонней.
Если ииижК|1(а/2) <UH< /7„<.|>ХЖ|>(а/2), то принимается Х = У и отвер-
гается X* Y, если UH < ^/,,|1жкр(ос), то принимается X > Y и отвергается
X = У, если UH > t/ucpx.Kp(a), то принимается X < У и отвергается X = У,
где UH = Uv
В частности, для последнего примера при а = 0,2 по таблице кри-
тических точек находим:
U =U (0,1; 3; 3) = 1 => U =9-1=8.
ииж.кр Kp\V» ’ / * '“'псрх.кр
Так как выполняется 1 < 6 < 8, то нулевая гипотеза принимается.
Критерий Манна-Уитни реализован в макросе I_test_M_W, при-
меняемом стандартным образом. Таблица критических точек для
а = 0,01; 0,025; 0,05; 0,10 при 3 < ттг, п < 20 введена как процедура-
функция.
Например, для последнего примера входные данные:
Рис. 2.26
Запуск макроса I_test_M_W на исполнение открывает окно вы-
бора альтернативной гипотезы:
Рис. 2.27
Критерий Манна-Уитни
79
Команда ОК вызывает окно для ввода номера выбранного уровня
значимости критерия:
Рис. 2.28
По команде ОК возвращаются результаты, полученные ранее не-
посредственными вычислениями:
Рис. 2.29
Применим макрос ItestMW к задаче 2.2. Входные данные (по-
казаны частично):
Рис. 2.30
Запуск макроса I_test_M_W на исполнение открывает диалого-
вое окно выбора альтернативной гипотезы, где вводим 1 (рис. 2.27).
Команда ОК вызывает окно ввода уровня значимости критерия:
Ввод fX|
Рис. 2.31
80
Непараметрические критерии для независимых выборок
По команде ОК получаем искомые результаты:
Рис. 2.32
Применение макроса I_test_M_W к данным задачи 2.5, входные
данные как на рис. 2.19, диалоговые окна ввода альтернативной ги-
потезы (Х> Y) и уровня значимости а = 0,05 для краткости не приво-
дятся, возвращает:
Рис. 2.33
Код макроса
Sub I_test_M_W()
Dim n As Integer, m As Integer, h As Integer, u As Single, v As Single,
alfa As Single, sigma As Single, s As Single, _
str As String, tip As Integer
Dim x() As Single, y() As Single
m = 0: n = 0
For i = 1 To Selection.Columns.Count
If Cellsd, i) .Value <> Empty Then
m = m + 1
End If
If Cells(2, i).Value <> Empty Then
n = n + 1
End If
Next
ReDim x(l To m): ReDim у(1 To n)
For i = 1 To m
x(i) = Cells(1, i).Value
Next
For i = 1 To n
yd) = Cells(2, i) .Value
Next
Критерий Манна-Уитни
81
s = О
For i = 1 То m
For j = 1 То n
If x(i) < y(j) Then
s = s + 1
Else
If x (i) = у (j) Then
s = s + 0.5
End If
End If
Next
Next
tip = Application.InputBox("Введите номер альтернативной гипотезы:" & Chr(13)
& " 1. X о Y; 2. X > Y; 3. X < Y ", Type: = l)
If m <= 20 And n <= 20 Then
If tip = 1 Then
h = Application.InputBox("Введите номер уровня значимости критерия:" & Chr(13)
& " 1. 0,02; 2. 0,05; 3. 0,10; 4. 0,20", Туре:=1)
Else
h = Application.InputBox("Введите номер уровня значимости критерия:" & Chr(13)
& " 1. 0,01; 2. 0,025; 3. 0,05; 4. 0,10", Туре:=1)
End If
u = Q(m, n, h)
v = m * n - u
Else
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
sigma = m * n
sigma = sigma * (m + n + 1) /12
sigma = Sqr(sigma)
If tip = 1 Then
u = Application.WorksheetFunction.Normlnv(alfa / 2, m * n / 2, sigma)
v = Application.WorksheetFunction.Normlnvd - alfa / 2, m * n / 2, sigma)
Else
u = Application.WorksheetFunction.Normlnv(alfa, m * n / 2, sigma)
v = Application.WorksheetFunction.Normlnv(1 - alfa, m * n / 2, sigma)
End If
End If
Select Case tip
Case Is = 1
If u < s And s < v Then
str = " Ho."
Else
str = " Hl."
End If
MsgBox ("Значение статистики: " & s & ". Область принятия Но: ( " & u _
& "; " & v & "). Принять: " & str)
Case Is = 2
If s < u Then
str = " X > Y."
Else
str = " Ho.
82
Непараметрические критерии для независимых выборок
End If
MsgBox ("Значение статистики: U=" & s & ". Критерий принятия Но: U > " & и
& ". Принять: " & str)
Case Is = 3
If v < s Then
str = " X < Y."
Else
str = " Ho."
End If
MsgBox ("Значение статистики: U=" & s & ". Критерий принятия Ho: U < " & v
& ". Принять: " i str)
End Select
End Sub
§5. Критерий серий
Вальда-Вольфовитца
1. Для применения критерия серий Вальда-Вольфовитца (Wald-
Wolfowitz twosample test) заданные выборки ([16], стр. 82-84) объ-
единяются в одну выборку объема т + и, которая сортируется в по-
рядке возрастания. Серией в ней называется последовательность
значений одной выборки, ограниченная значениями другой. Стати-
стикой критерия является количество серий U.
При 2 < min(m, и), max(m, п) < 20 принимается гипотеза Но: F,(x) =
= F2(x), если < U < п2, в противном случае, принимается гипотеза
Я,: F/x) *F2(x). Критические значения щ и п2 по объемам выборок и
уровню значимости а находятся из таблицы ([2], таблица 6.7).
При max(m, п) > 20 от статистики U переходят к статистике
j.
2
2т п(2т п - т - п)
(т + п)2(т + п-1)
имеющей приближенно, если гипотеза Но верна, стандартное нор-
мальное распределение. Гипотеза Но: F/x) = F2(x) принимается, если
IZ | < и го где и „ - квантиль стандартного нормального распределе-
’"2 ’’7
ния уровня 1 - —. В противном случае принимается гипотеза : F((х) *
2
Задача 2.6. Применяя критерий серий проверить при а = 0,05
гипотезу Н(): F,(x) = F2(x) однородности выборок:
Критерий серий Вальда-Вольфовитца 83
х,: 8,6 8,7 9,0 9,5 9,8 10,2
у/. 9,2 9,4 9,6 9,9 10,1 10,5 10,5 11,0
Альтернативная гипотеза : F\x) * F2(x).
Технология решения.
1. Вводим выборочные данные на рабочий лист:
А I В I С I D I Е | F | G г Н
1 8.6 8.7 9 9.5 9.8 10.2
2 92. 94 9К 99 10 10,5 10.5 11
Рис. 2.34
2. В строке 4 объединяем выборки, в строке 5 указываем номера
выборок, в которые входят варианты строки 4:
~4Т 8.6| 8.7] 9| 9.5[ 9.8} 10,2^ 9.2] 9/Г" ~ 9,6 9.9 10- 10,5 Ю.51 1П
Др 1Г 1Г 1Т 11 11 2 2 2 2 2 2 2 2-
Рис. 2.35
3. Выделяем диапазон A4:N4 (объединенную выборку) и прово-
дим сортировку по возрастанию, что дает:
! 4 L 8|6 8I7 9 9,2 9,4 9,5 9,6 9,8 9.9 10 '°? 10,5 10,5 nl
Ш 1L ’к _ц. 2 21. 11_ 2 1 2 1 2 2 __2[
Рис. 2.36
4. В ячейку А6 вводим значение 1, в ячейке В6 записываем фор-
мулу, которая возвращает номер серии, в которую входит эле-
мент, находящийся в ячейке В5: =ЕСЛИ(А5=В5;А6; А6+1).
Копируя ее в ячейки диапазона C6:N6, получаем номер серии
для каждого элемента строки 5:
Т] 8.6 8.71 9[ 9,21 9,4: 9,5.г 9.6i ~ 9.8» 9.9 10 10,2 10,5 10,5 11
5 11 1 2 2 1 2 1 2 2 1 2 2 2
6 1| 1 1 22345667888
Рис. 2.37
Откуда следует U = 8.
5. По таблице критических точек находим пх = Hj(0,05;6;8) = 3,
п2 = и2(0,05;6;8) =12. Так как пх < U < п.р то нулевая гипотеза
принимается.
84
Непараметрические критерии для независимых выборок
Ответ: гипотеза однородности принимается.
Макрос I_test_W_W проводит вычисления автоматически и воз-
вращает полученные результаты и вывод о том, какая гипотеза при-
нимается. Для применения макроса надо ввести выборочные данные
в первые две строки рабочего листа Excel, начиная с ячейки А1, вы-
делить диапазон с данными, запустить макрос на исполнение и сле-
довать указаниям.
Например, запуск макроса I_test_W_W на исполнение, когда
входные данные, как на рис. 2.34, открывает окно ввода номера уров-
ня значимости:
Рис. 2.38
Команда ОК возвращает результаты, совпадающие с полученны-
ми ранее:
Рис. 2.39
По команде ОК появляется окно:
Рис. 2.40
Критерий серий Вальда-Вольфовитца
85
Команда Да оставляет вычисления на рабочем листе, команда Нет
их удаляет.
Задача 2.7. Проверить при уровне значимости а = 0,15 гипотезу
Яо: Ft(x) = F2(x) однородности выборок:
х, : 31 26 33 И 13 5 18 1 2 16 17 23 20 21 9
г/.: 12 7 4 8 3 6 10 25 22 24 15 19 14 36 34 32 27 29 30 35 28
Альтернативная гипотеза Нх: F^x) # F2(x).
Технология решения. Вызывается макрос I test W W, вводятся,
и выделяются выборочные данные:
I А I В ГсТвГеТг] G I Н I I I J I К I L I М I N I О I Р I Q I rTs"! Т Ги
1 I 31 26 33 11 13 5 18 1 2 16 17 23 20 21 9
2~| 12 7 4 8 3 6 10 25 22 24 15 19 14 36 34 32 27 29 30 35 28
Рис. 2.41
Запуск макроса I_test_W_W на исполнение открывает окно ввода
уровня значимости:
Рис.2.42
Команда ОК возвращает:
Рис. 2.43
Ответ: гипотеза однородности принимается.
86
Непараметрические критерии для независимых выборок
Код макроса
Sub I_test_W_W()
Dim m As Integer, n As Integer, h As Integer, nl As Integer, n2 As Integer, _
z As Single, u As Integer, t As Single, alfa As Single
For i = 1 To Selection.Columns.Count
If Cells(1, i).Value <> Empty Then
m = i
End If
If Cells(2, i).Value <> Empty Then
n = i
End If
Next
For i = 1 To m
Cells(4, i).Value = Cells (1, i).Value
Next
For i = 1 To n
Cells(4, m + i).Value = Cells(2, i).Value
Next
For i = 1 To m + n
If i < m + 1 Then
Cells(5, i).Value = 1
Else
Cells (5, i) .Value = 2
End If
Next
Range (Cells (4, 1), Cells(5, m + n)).Select
Selection.Sort Keyl:=Range("A4"), Orderl:=xlAscending, Orientation:=xlLeftToRight
[a6] = 1
For i = 2 To m + n
If Cells (5, i - 1) .Value = Cells (5, i) .Value Then
Cells(6, i).Value = Cells(6, i - 1).Value
Else
Cells(6, i).Value = Cells(6, i - 1).Value + 1
End If
Next
[a8] = Cells(6, m + n).Value: u = [a8]
Select Case Application.WorksheetFunction.Max(m, n)
Case Is <= 20
h = Application.InputBox("Введите номер уровня значимости критерия:" & Chr(13) _
& " 1. 0,1; 2. 0,05; 3. 0,02; 4. 0,01", Туре:=1)
nl = F(m, n, 2 * h - 1): n2 = F(m, n, 2 * h)
If nl < u And u < n2 Then
MsgBox ("Значение статистики: " & u & ". Область принятия Но: (" & nl _
& "; " & n2 & "). Принять: Но.")
Result = MsgBox("Оставить вычисления?", 4 + 32, "Вывод")
If Result = 6 Then
Exit Sub
Else
Range(Cells(4, 1), Cells (6, m + n)).ClearContents
Критерий серий Вальда-Вольфовитца
87
Range(«А8»).ClearContents
Range(Cells (1, 1), Cells(2, h)).Select
End If
Else
MsgBox ("Значение статистики: " & u & ". Область принятия Но: (" & nl _
& " & n2 & Принять: Hl. ")
End If
Case Else
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
(Ь81 = m: [с8] = п
Range("D8").Formula =
"=ABS((ABS(A8-(2*В8*С8/(B8+C8)+1))-1/2)/sqrt(2*B8*C8*(2*B8*C8-B8-C8)/(В8+С8)А2/
(В8+С8-1)))"
z = (D8)
t = Application.WorksheetFunction.NormSInv(l - alfa / 2)
t = FormatNumber(t, 3): z = FormatNumber(z, 3)
If z < t Then
MsgBox ("Значение статистики: Z= " & z & ". Критерий принятия Ho: IZI < " & t
& ". Принять: Ho. ")
Else
MsgBox ("Значение статистики: Z= " & z & ". Критерий принятия Ho: |Z| < " & t
& ". Принять: Hl. ")
End If
End Select
Range(Cells(8, 1), Cells(8, 4)).ClearContents
Range(Cells(4, 1), Cells(6, m + n)).ClearContents
Range (Cells(1, 1), Cells (2, Application.WorksheetFunction.Max(m, n))).Select
End Sub
2. Критерий Вальда-Вольфовитца также применяется ([14], стр.
66), когда надо проверить гипотезу Но случайности заданной число-
вой последовательности i = 1,л, при альтернативной гипотезе Н{: не
случайная (тренд). Для этого находится медианами записывается по-
следовательность двух символов, например, а и fc, символ а ставится,
если .г, - d > 0, символ Ь, еслих, - d < 0, нулевые разности во внимание
не принимаются. Далее, все, как в предыдущем случае.
Задача 2-8- При уровне значимости а = 0,05 проверить гипотезу
Но случайности последовательности:
98,98,86,86,92,92,98,90,100,94,90,112,108,92,94
Технология решения.
1. Вводим данные на рабочий лист:
. А | В | С | D | Е | F | G | Н | I | J | К | L | М | N | СГ|
1 I 98 98 86 66 92 92 98 90 100 94 90 112 108 92 941
Рис. 2.44
88
Непараметрические критерии мя независимых выборок
2. В ячейке АЗ, применяя функцию МЕДИАНА, получаем значе-
ние медианы 94.
3. В ячейке А4 записываем формулу =ЕСЛИ(А1>$А$3;Г,ЕСЛИ
(А1<$А$3;-Г,0)). Копируя ее в остальные ячейки диапазона
А4: 04 (а = 1, b = -1), приходим к последовательности:
Г3 4 5. 94
; 4 | 1 1 -| -1 -1 -1 1-1 10-1 1 1-1 р|
Рис. 2.45
4. Удаляем нули, в ячейке J4 через контекстное меню, со сдвигом
влево, что дает:
3 I 94________________________________________
4 | 1 1 -1 -1 -1 -1 1-1 1-1 1 1 -ll
Рис. 2.46
5. В ячейку А5 вводим 1, в ячейку В5 формулу =ЕСЛИ(А4=В4;
А5;А5+1). Копируя ее в остальные ячейки диапазона В5:М5,
в ячейке М5 получаем значение критерия U=8:
3 94
4 11-1-1-1-11-11-111-1
5 1 £12222345677 81
Рис. 2.47
6. В диапазоне А4: М4 число положительных значений равно
6, число отрицательных - равно 7. По таблице находим и, =
= nt(0,05;6;7) = 3, п2 = и2(0,05;6;7) = 12. Так как и, < U < и2, то
нулевая гипотеза принимается.
Ответ: нулевая гипотеза принимается.
Данный тест реализован в макросе ItestM, применяемом стан-
дартным образом. Например, запуск макроса на исполнение, когда
входные данные, как на рис. 2.44, открывает окно ввода номера уров-
ня значимости (рис. 2.38). Команда ОК возвращает результаты, полу-
ченные непосредственными вычислениями:
Критерий серий Вальда-Вольфовитца
89
Рис. 2.48
По команде ОК появляется окно, показанное на рис. 2.40.
Задача 2.9. При уровне значимости а = 0,02 проверить гипотезу
Н()о том, что заданная последовательность является случайной:
65,61,67,73,51,59,48,47,58,44,41,54,52,47,51,45,78,71,36,43,73,68,65,
36,26,21,30,27,38,76,55,64,34,45,68,27,26,35,74,69,54,39,61,71,63,22,
72,50,30,24
Технология решения. Вызывается макрос I test M, в диапазон
А1:АХ1 вводятся данные и он выделяется (на рис. 2.49 показан час-
тично):
А | В | С ] D [ Е | F | G [ Н | I ] J У К ] L | М [ N |
1 | 65 61 67 73 51 59 48 47 58 44 41 54 52 47
Рис.2.49
Запуск макроса на исполнение открывает окно ввода уровня зна-
чимости:
Рис. 2.50
Команда ОК возвращает искомые результаты:
Рис. 2.51
90
Непараметрические критерии для независимых выборок
Ответ: нулевая гипотеза принимается.
Код макроса
Sub I_test_M()
Dim m As Integer, n As Integer, h As Integer, nl As Integer, n2 As Integer, _
z As Single, u As Integer, t As Single, alfa As Single, d As Single
n = Selection.Columns.Count
d = Application.WorksheetFunction.Median(Selection)
For i = 1 To n
If Cells(1, i).Value > d Then
Cells(4, i) .Value = 1
Else
If Cells(l, i).Value < d Then
Cells(4, i) .Value = -1
Else
Cells(4, i).Value = 0
End If
End If
Next
Linel:
For i = 1 To n
If Cells(4, i).Value = 0 Then
Cells(4, i).Delete Shift:=xlToLeft
n = n - 1
GoTo Linel
End If
Next
05] = 1
For i = 2 To n
If Cells (4, i - 1) .Value = Cells (4, i) .Value Then
Cells(5, i).Value = Cells(5, i - 1).Value
Else
Cells (5, i) .Value = Cells (5, i - 1) .Value + 1
End If
Next
u = Cells(5, n).Value
m = Application.WorksheetFunction.Countlf(Range(Cells(4, 1), Cells(4, n)), -1)
n = Application.WorksheetFunction.Countlf(Range(Cells(4, 1), Cells(4, n)), 1)
[a8] = u
Select Case n
Case Is <= 20
h = Application.InputBox("Введите номер уровня значимости критерия: " & Chr(13)
& " 1. 0,1; 2. 0,05; 3. 0,02; 4. 0,01", Туре: = 1)
nl = F(m, n, 2 * h - 1): n2 = F(m, n, 2 * h)
If nl < u And u < n2 Then
MsgBox ("Значение статистики: " & u & ". Область принятия Но: (" & nl
& ", " & n2 & "). Принять: Но. ")
Result = MsgBox("Оставить вычисления?", 4 + 32, "Вывод")
If Result = 6 Then
[аЗ] = d
Критерий серий Вальда-Вольфовитца Qj
Exit Sub
Else
Range(Cells(4, 1), Cells(5, m + n)).ClearContents
Range("A8").ClearContents
End If
Else
MsgBox ("Значение статистики: " & u & ". Область принятия Но: (" & nl _
& ”, " & n2 & Принять: Hl. ”)
End If
Case Else
alfa = Application.InputBox("Введите уровень значимости критерия: ", Туре:=1)
[Ь8] = m: (с8] = п
Range("D8").Formula =
”=ABS((ABS(A8-(2*В8*С8/(B8+C8)+1))-1/2)/sqrt(2*B8*C8*(2*B8*C8-B8-C8)/" _
& "(В8+С8)А2/(В8+С8-1)))"
z = [D81
t = Application.WorksheetFunction.NormSInv(1 - alfa / 2)
t = FormatNumber(t, 3): z = FormatNumber(z, 3)
If z < t Then
MsgBox ("Значение статистики: Z= " & z & ". Критерий принятия Ho: |Z| < " & t _
& ". Принять: Но. ")
Else
MsgBox ("Значение статистики: Z= " & z & ". Критерий принятия Ho: |Z| < " & t _
& ". Принять: Hl. ")
End If
End Select
Range(Cells(8, 1), Cells(8, 4)).ClearContents
Range(Cells(4, 1), Cells(5, m + n)).ClearContents
End Sub
3. Еще одно применение критерия Вальда-Вольфовитца связано
с проверкой гипотезы Но случайности последовательности двух сим-
волов, при альтернативной гипотезе Ht: не случайная (тренд).
Задача 2-10- При уровне значимости а = 0,05 проверить гипоте-
зу Но случайности последовательности:
а, а, Ь, Ь, а, а, а, Ь, Ь, Ь.
Технология решения.
1. Вводим последовательность на рабочий лист:
, А Г~в I с | рП Ё Г F I G I Н I I Г J Г К
| 1 [a a b b a a a b b b b
Рис. 2.52
2. В ячейку А2 вводим 1. В ячейке В2 записываем формулу
=ЕСЛИ(А1=ВГ,А2;А2+1). Копируя ее в ячейки С2:К2, полу-
чаем в ячейке К2 наблюдаемое значение критерия U=4:
92
Непараметрические критерии для независимых выборок
А Г В~ I С I D I Ё"Т О сП Н I I I J Г~~к
1 |а a b b a a a b b b b
2J 1‘ 4 2 2 3 3 3. 4 4 4 4*
Рис. 2.53
3. По таблице [2] находим и, = п,(0,05;5;6) = 3, п2 = и2(0,05;5;6) =
= 10. Так как и, < U < и2, то нулевая гипотеза принимается.
Ответ: нулевая гипотеза принимается.
Такое применение критерия Вальда-Вольфовитца реализовано
в макросе ItestB, применяемом обычным образом. Например, для
задачи 2.10, когда входные данные, как на рис. 2.52, запуск макроса
открывает окно ввода номера уровня значимости (рис. 2.38). Команда
ОК возвращает:
Microsoft Excel
Значение статистики: 4. Область принятия Но: (3, 10). Принять: Но.
Рис. 2.54
Задача 2.11. При уровне значимости а = 0,05 проверить гипоте-
зу Н() случайности последовательности:
010010001000100010010100100000.
Технология решения. Вызывается макрос I_test_B, в диапазон
A1:AD1 вводится последовательность и выделяется (фрагмент диа-
пазона показан на рис. 2.55):
ПГаТПГГЯ~БТе I f | бТ7ГГГ~П" | к | l | м |
| I "L о 1 о о 1 о о 0 10 О О 1
Рис. 2.55
Запуск макроса на исполнение открывает окно ввода уровня зна-
чимости:
Рис. 2.56
Критерий серий Вальда-Вольфовитца
93
Команда ОК возвращает:
Рис. 2.57
Ответ: нулевая гипотеза принимается.
Код макроса ;
Sub I_test_B()
Dim m As Integer, n As Integer, h As Integer, nl As Integer, n2 As Integer,
z As Single, u As Integer, t As Single, alfa As Single
m = 0: n = 0
For i = 1 To Selection.Columns.Count
If Cells(l, 1) .Value = Cellsd, i) .Value Then
m = m + 1
Else
n = n + 1
End If
Next
m = Application.WorksheetFunction.Min(m, n)
n = Selection.Columns.Count - m
[a2] = 1
For i = 2 To m + n
If Cellsd, i - 1) .Value = Cellsd, i) .Value Then
Cells(2, i).Value = Cells(2, i - 1).Value
Else
Cells(2, i) .Value = Cells (2, i - 1) .Value + 1
End If
Next
u = Cells(2, m + n).Value
Ia3] = u
Select Case n
Case Is <= 20
h = Application.InputBox("Введите номер уровня значимости критерия: " & Chr(13)
& "1. 0,1; 2. 0,05; 3. 0,02; 4. 0,01", Туре:=1)
nl = F(m, n, 2 * h - 1): n2 = F(m, n, 2 * h)
If nl < u And u < n2 Then
MsgBox ("Значение статистики: " & u & ". Область принятия Но: (" & nl _
& ", " & n2 & "). Принять: Но. ")
Result = MsgBox("Оставить вычисления?", 4 + 32, "Вывод")
If Result = 6 Then
Exit Sub
Else
94
Непараметрические критерии мя независимых выборок
Range(Cells(2, 1), Cells (3, m + n)).ClearContents
End If
Else
MsgBox ("Значение статистики: " & u & ". Область принятия Но: (" & nl _
& ", " & n2 & "). Принять: Hl.")
End If
Case Else
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
[ЬЗ] = ш: [сЗ] = п
Range("D3").Formula = "=ABS((ABS(АЗ-(2*ВЗ*СЗ/(ВЗ+СЗ)+1))-1/2)/" _
& "sqrt (2‘ВЗ‘СЗ*(2*ВЗ‘СЗ-ВЗ-СЗ)/(ВЗ+СЗ)А2/(ВЗ+СЗ-1)))"
z = [D3]
t = Application.WorksheetFunction.NormSInv(1 - alfa / 2)
t = FormatNumber(t, 3): z = FormatNumber(z, 3)
If z < t Then
MsgBox ("Значение статистики: Z= " & z & ". Критерий принятия Ho: |Z| < " & t
& ". Принять: Но.")
Else
MsgBox ("Значение статистики: Z= " & z & ". Критерий принятия Ho: |Z| < " & t
& ". Принять: Hl. ")
End If
End Select
Range(Cells(2, 1), Cells(3, m + n)).ClearContents
End Sub
§6. Сериальный критерий
Рамачандрана-Ранганатана
Данный критерий является более мощным, чем критерий Вальда-
Вольфовитца, рассматриваемый в последнем пункте предыдущего
параграфа, так учитывает не только количество серий, но и их длины
[ 12]. Статистика критерия:
т
1=1
т - количество серий, nt - длина i-й серии. Критические точки стати-
стики R табулированы [12, 20]. Гипотеза случайности Но при уровне
значимости а принимается, если RH < /?(и;а), в противном случае Но
отвергается.
Пусть требуется применить критерий Рамачандрана-Ранганатана
к данным задачи 2.11.
1. В диапазон A1:AD1 вводим заданный ряд.
2. В ячейку А2 вводим значение 1, а в ячейку В2 формулу =ЕСЛ И
(В1=А1;А2;А2+1) и копируем ее в ячейки диапазона C2:AD2,
что дает номера серий.
Сериальный критерий Рамачандрана-Ранганатана
95
3. В ячейку АЗ вводим значение 1, а в ячейку ВЗ формулу =ЕСЛ И
(В2=А2;АЗ+1; 1) и копируем ее в ячейки диапазона СЗ: AD3, что
указывает длины серий.
4. В ячейку А4 вводим формулу =ЕСЛИ(АЗ<ВЗ;0;АЗ) и копиру-
ем ее в ячейки диапазона B4:AD4, что оставляет только длины
серий.
5. В ячейку А5 записываем формулу =А4Л2 и копируем ее в ячей-
ки B5:AD5.
6. В ячейке А6 формулой =CYMM(A5:AD5) находим значение
критерия (результаты выполнения шагов 1-6 показаны на
рис. 2.58).
7. По таблице критических точек /?кр = R(30;0,05) = 118 > 74, при-
нимается нулевая гипотеза.
Рис. 2.58
Критерий реализован в макросе I_test_R_R при четных значени-
ях и, 6 < п < 30, а = 0,1; 0,05; 0,01. Таблица критических точек введена
как процедура-функция.
Например, для данных задачи 2.11 макрос I_test_R_R возвращает:
Microsoft Excel
Значение статистики: R - 74. Критерий принятия Но: R < 118. Принять: Но.
Рис. 2.59
Код макроса
Sub I_test_R_R()
Dim n As Integer, r As Integer, s As Single, h As Integer
n = Selection.Columns.Count
[a2] = 1
For i = 2 To n
96
Непараметрические критерии для независимых выборок
If Cellsd, i - 1) .Value = Cellsd, i).Value Then
Cells(2, i) .Value = Cells(2, i - 1) .Value
Else
Cells (2, i).Value = Cells (2, i - 1).Value + 1
End If
Next
u = Cells (2, n).Value
r = 0
For i = 1 To u
s = Application.WorksheetFunction.Countlf(Range(Cells(2, 1), Cells(2, n)), i)
r = r + s A 2
Next
h = Application.InputBox("Введите номер уровня значимости критерия:" & Chr(13) _
& " 1. 0,1; 2. 0,05; 3. 0,01 ", Туре:=1)
If г < F(n / 2, h) Then
MsgBox ("Значение статистики: R = " & г & ". Критерий принятия Но: R < "
& F(n / 2, h) & ". Принять: Но.")
Else
MsgBox ("Значение статистики: R= " & г & ". Критерий принятия Но: R < "
& F(n / 2, h) & ". Принять: Hl.")
End If
Range(Cells(2, 1), Cells(3, n)).ClearContents
End Sub
Глава 3
Непараметрические
критерии для пар
наблюдений
§ 1. Критерий знаков...98
§2 . Критерий Фишера...104
§3 . Знаковый критерий
Вилкоксона ............107
§4 . Модификации
критерия Вилкоксона ..114
§5 . Критерий Спирмена.... 123
§6 . Критерий Кендалла.128
§7 . Критерий
Ван дер Вардена.........132
§8 . Критерий Ширахате .... 136
98
Непараметрические критерии для пар наблюдений
§1. Критерий знаков
Критерий знаков ([9], стр. 339) - простейший непараметрический
критерий проверки гипотезы Н() однородности признаков X, Y, пред-
ставленных выборочными данными (хр у), х, * у,, i = 1, п.
Так как при каждом значении индекса i выполняется у. - xt > О
или yt - xt < 0, то в случае справедливости гипотезы Но число г поло-
жительных разностей yi - xt, как и число s отрицательных разностей,
подчиняется биномиальному закону распределение с параметра-
ми п, Поэтому нулевая гипотеза записывается в виде Н{} : р = 0,5,
р = P(yt > х,), альтернативной является одна из гипотез:
1) : р * 0,5; 2) Н™ : р < 0,5; 3) Н™ : р > 0,5.
В качестве статистики можно принять:
min(r, .s), если Hf0,
G = < г, если Н(2\
I 5, если
При альтернативной гипотезе Я*0 на уровне значимости а гипо-
теза принимается, если значение интегральной функции F(x) би-
номиального распределения с параметрами п,р = 0,5, удовлетворяет
неравенству:
F(C) = P(^<o=fc;W >^.
/=0 \
в противном случае принимается
При альтернативной гипотезе Н{2) или Н™ на уровне значимости
а гипотеза принимается, если
= = >а-
/=0 \ )
иначе принимается альтернативная гипотеза.
Задача 3.1. В предположении, что показания двух радаров,
определяющих скорость автомобиля, отличаются, ими измерили ско-
рости 10 автомобилей в одни и те же моменты времени (таблица 3.1).
Можно ли на уровне значимости а - 0,1 утверждать, что показания
радаров отличаются?
Критерий знаков
99
Таблица 3.1
-ч 70 85 63 54 65 80 75 95 52 55
У. 72 86 62 55 67 80 78 90 53 57
Технология решения. Вводим в диапазон A1J2 данные табли-
цы 3.1, в ячейке АЗ записываем формулу =А2-А1 и копируем ее
в остальные ячейки диапазона A3J3:
Рис. 3.1
В ячейке А5 подсчитываем число плюсов полученных разностей.
Для этого открываем диалоговое окно функции СЧЁТЕСЛИ и зада-
ем данные:
Рис. 3.2
Аналогично, в ячейке В5 подсчитываем число минусов получен-
ных разностей. В ячейке С5 формулой =А5+В5 записываем число
ненулевых разностей. В ячейку D5 функцией МИН вставляем наи-
меньшее из значений ячеек А5 и В5:
100
Непараметрические критерии для пар наблюдений
Рис. 3.3
Теперь вызываем функцию БИНОМРАСП и вводим данные:
Рис. 3.4
Так как0,0898 > = 0,05, то принимается нулевая гипотеза.
Ответ: нет.
Макрос Il test S проводит вычисления по критерию знаков авто-
матически и возвращает сообщение о том, какая гипотеза (основная
или альтернативная) принимается.
Например, задача 3.1 решается макросом II_test_S следующим об-
разом. Вызываем макрос II_test_S, вводим и выделяем данные таб-
лицы 3.1:
Рис. 3.5
Критерий знаков
101
Запуск макроса на исполнение Сервис => Макросы => Il test S
=> Выполнить открывает окно ввода уровня значимости критерия:
Рис. 3.6
Команда ОК открывает окно ввода номера альтернативной гипо-
тезы:
Рис. 3.7
Команда ОК возвращает:
Рис. 3.8
Задача 3.2. В таблице 3.2 представлены данные о числе аварий в
некотором регионе за 2011 и 2012 годы. Можно ли на уровне значимо-
сти 5% утверждать, что аварийность в 2012 году снизилось?
102
Непараметрические критерии для пар наблюдений
Таблица 3.2
2010 г. 2011 г.
Январь 125 85
Февраль 150 80
Март 80 85
Апрель 50 40
Май 40 39
Июнь 43 40
2010 г. 2011 г.
Июль 80 42
Август 75 43
Сентябрь 80 50
Октябрь 65 56
Ноябрь 50 78
Декабрь 95 83
Технология решения. Вводим табличные данные:
Рис. 3.9
Запуск макроса на исполнение открывает окно ввода уровня зна-
чимости критерия:
Рис. 3.10
Команда ОК открывает окно ввода номера альтернативной гипо-
тезы:
Рис. 3.11
Критерий знаков
103
Команда ОК возвращает результаты:
Рис. 3.12
Ответ: да.
Код макроса '
Sub II_test_S()
Dim n As Integer, r As Integer, s As Integer, g As Integer, w As Single, _
alfa As Single, tip As Integer, Str As String
For i = 1 To Selection.Columns.Count
If Cells(2, i) .Value - Cellsd, i) .Value > 0 Then
r = r + 1
Else
If Cells(2, i) .Value - Cellsd, i) .Value < 0 Then
s = s + 1
End If
End If
Next
n = r + s
g = Application.WorksheetFunction.Min(r, s)
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
tip = Application.InputBox("Введите номер альтернативной гипотезы: " & Chr(13)
& "1. р<>0,5; 2. р<0,5; 3. р>0,5," & Chr(13)
& "где p=P(xi<yi).", Туре:=1)
Select Case tip
Case Is = 1
w = Application.WorksheetFunction.BinomDist(g, n, 1/2, 1)
If w <= alfa / 2 Then
Str = " p<>0,5.": alfa = alfa / 2
Else
Str = " p=0,5.": alfa = alfa / 2
End If
Case Is = 2
w = Application.WorksheetFunction.BinomDist(r, n, 1/2, 1)
If w <= alfa Then
Str = " p<0,5."
Else
Str = " p=0,5."
End If
Case Is = 3
104 Непараметрические критерии для пар наблюдений
w = Application.WorksheetFunction.BinomDist(s, n, 1/2, 1)
If w <= alfa Then
Str = " p>0,5."
Else
Str = " p=0,5."
End If
End Select
w = FormatNumber(w, 3)
MsgBox ("Выполняется: F = " & w & ". Критерий принятия Ho: F > " & alfa
& ". Принять:" & Str)
End Sub
§2. Критерий Фишера
В данном критерии ([9], стр. 340) в качестве статистики можно при-
нять:
Г =
—-----, если Я*0,
n-h + 1 1
—, если Н{2\
, если Н{л\
h = max(r, s).
Гипотеза Яо при альтернативной гипотезе Я}0 на уровне значимо-
сти а принимается, если
Fh<F ^=2(п-Л + 1), k.,=2h,
иначе принимается Я[°.
Гипотеза Яо при альтернативной гипотезе Я{2) принимается, если
и^г^)Д. = 2(г+1)Д2 = 25,
иначе принимается Я[2).
Гипотеза Яо при альтернативной гипотезе Я[3) принимается, если
иначе принимается Я[3).
Значение Fia(kv k2) в MS Excel возвращает функция ЕРАСПОБР
(«ЛЛ)-
Для задачи 3.1, так как г = 7, s = 2:
7
R =—— = 2,333.
п 2 + 1
Критерий Фишера
105
Применяя функцию РРАСПОБР, с учетом = 2(2+1) = 6, k2 =
= 2-7-14, находим Р095(6, 14):
Рис. 3.13
Так как FB < Р095(6, 14), то принимается гипотеза Но.
В задаче 3.2 альтернативной будет гипотеза Н{2), а так как г = 2,
s = 10, то
/,.= — = 3,333.
в 2 + 1
Применяя функцию РРАСПОБР, получаем Р095(6, 20) = 2,6. Так
как FH < Р()<)5(6, 20), то принимается гипотеза Н\2}.
Критерий Фишера реализован в макросе II_test_F, применяемом
стандартным образом. В частности, применение к данным задачи 3.1
возвращает:
Рис. 3.14
Применение к данным задачи 3.2 дает:
Рис. 3.15
106
Непараметрические критерии для пар наблюдений
Код макроса
Sub II_test_F()
Dim n As Integer, r As Integer, s As Integer, h As Integer, w As Single,
F As Single, alfa As Single, tip As Integer, Str As String
For i = 1 To Selection.Columns.Count
If Cells (2, i). Value - Cellsd, i) .Value > 0 Then
r = r + 1
Else
If Cells(2, i) .Value - Cellsd, i) .Value < 0 Then
s = s + 1
End If
End If
Next
n = r + s
h = Application.WorksheetFunction.Max(r, s)
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
tip = Application.InputBox("Введите номер альтернативной гипотезы: " & Chr(13)
& "1. р<>0,5; 2. р<0,5; 3. р>0,5," & Chr (13)
& "где p=P(xi<yi) .", Туре: = 1)
Select Case tip
Case Is = 1
F = h / (n - h + 1)
w = Application.WorksheetFunction.FInv(alfa / 2, 2 * (n - h + 1), 2 * h)
If F < w Then
Str = "p = 0,5."
Else
Str = "p < > 0,5."
End If
Case Is = 2
F = s / (r + 1)
w = Application.WorksheetFunction.FInv(alfa, 2 * (r + 1), 2 * s)
If F >= w Then
Str = "p < 0,5."
Else
Str = "p = 0,5."
End If
Case Is = 3
F = r / (s + 1)
w = Application.WorksheetFunction.FInv(alfa, 2 * (s + 1), 2 * r)
If F >= w Then
Str = "p > 0,5."
Else
Str = "p = 0,5."
End If
End Select
F = FormatNumber(F, 3): w = FormatNumber(w, 3)
MsgBox ("Значение статистики: F = " & F & " . Критерий принятия Ho: F < " & w
& ". Принять: " & Str)
End Sub
Знаковый критерий Вилкоксона
107
§3. Знаковый критерий Вилкоксона
Знаковый критерий Вилкоксона ([10], стр. 288) учитывает не только
знаки yi - xit но и величины разностей. Статистика критерия записы-
вается в виде:
min(r,s), если
Т =
г, если
s, если Я{3),
где г - сумма рангов (порядковых номеров) положительных разно-
стей г/, - s - отрицательных разностей, при ранжировании | yt - xi |
в возрастающем порядке, равным значениям приписывается средний
ранг. Выполняется:
2
Критические значения статистики Т приведены в таблице 3.3
([14], таблица 21).
Таблица 3.3
Критические точки критерия Вилкоксона для пар наблюдений
л Уровень значимости
0,01 0,025 0,05 0,10
6 0 2 3
7 0 2 3 5
8 1 3 5 8
9 3 5 8 10
10 5 8 10 14
11 7 10 13 17
12 9 13 17 21
13 12 17 21 26
14 15 21 25 31
15 19 25 30 36
16 23 29 35 42
17 27 34 41 48
18 32 40 47 55
19 37 46 53 62
20 43 52 60 69
21 49 58 67 77
22 55 65 75 86
23 62 73 83 94
24 69 81 91 104
25 76 89 100 113
108
Непараметрические критерии для пар наблюдений
Нулевая гипотеза Но при альтернативной гипотезе Hf0 на уровне
f ОС
значимости а принимается, если табличное значение Гк I — I < Tliafn,
иначе принимается
Нулевая гипотеза Но при альтернативной гипотезе Н[2) или Н[3)
на уровне значимости а принимается, если табличное значение
Гкр(а, п) < Г|ийя, иначе принимается альтернативная гипотеза.
При п > 25 имеет место оценка:
. Z*LL1> + 2(а)
К,Л 7 4 v 24
z(a) - квантиль стандартного нормального распределения уровня а.
Правила проверки гипотез такие же, как в случае 5 < п < 25.
Задача 3-3. Ниже приведено время (в секундах) решения кон-
трольных задач одиннадцатью учащимися до и после специальных
упражнений по устному счету. Можно ли считать, что эти упражнения
улучшили способности учащихся в решении задач? Принять а = 0,01.
До: 87 61 95 90 97 74 83 72 81 75 83
После: 50 45 79 90 88 65 52 75 81 61 52
Технология решения.
1. Вводим выборочные данные, пропуская значения х, = у;.
1
2
А I В I с I D I Е | F | G I н I I I J
87 61 95 97 74 83 72 81 75 83
50 45 79 88 65 52 75 84 61 52
Рис. 3.16
2. В ячейке АЗ записываем формулу =А2-А1 и копируем ее
в остальные ячейки диапазона A3J3.
3. В ячейку А4 вводим формулу =ABS(A3) и копируем в осталь-
ные ячейки диапазона A4J4.
4. В ячейку А5 вводим, и копируем в ячейки В5: J5, формулу
=PAHr(A4;$A4:$J4;l)+ (C4ETEC7IH($A4:$J4;A4)-1)*O,5, что
дает ранги элементов четвертой строки:
3 I -37] -16 -16' -9. -9 -31. 3 3 -14 -31
4 37 16 16 9 9 31 3 3 14 31
5 10 6.5 -Hi 35_ 3.5 8.5 1.5 1 .5 5 8,5
Рис. 3.17
Знаковый критерий Вилкоксона
109
5. Выделяем любую свободную ячейку, например, А7, кнопкой
/(х) открываем каталог встроенных функций, где выбираем
функцию СУММЕСЛИ. Ее диалоговое окно заполняем, как на
рисунке 3.18, и подтверждаем ОК, что вставляет в А7 значение
г=3.
Рис. 3.18
6. Аналогично находится значение 5 = 52, но можно и по формуле
$ = 12111-3 = 52.
2
7. Так как проверяется гипотеза Но против гипотезы Н}2) - вре-
мя счета уменьшилось, то Т11а6л = г = 3. По таблице 3.3 находим
Тк|)(0,01; 10) = 5. Так как выполняется Ткр(0,01; 10) = 5 > 3 = Т11а6л,
то принимается гипотеза Н\2\
Ответ: да.
Критерий реализован в макросе II_test_W, таблица 2.3 задана
как процедура-функция G(x,y). Для применения макроса надо вве-
сти табличные данные, пропуская пары х, = у., если такие имеют-
ся, в первые две строки, начиная с ячейки АН, выделить диапазон
с данными и запустить макрос на исполнение, далее следовать ука-
заниям. По желанию пользователя расчетная таблица остается на
рабочем листе.
Например, применяя макрос к последней задаче, входные данные
на рис. 3.16, получаем окно ввода номера выбранной альтернативной
гипотезы:
110
Непараметрические критерии для пар наблюдений
Рис. 3.19
Команда ОК открывает следующее окно, где вводится номер вы-
бранного уровня значимости критерия:
Рис. 3.20
Команда ОК возвращает результаты, полученные ранее непосред-
ственными вычислениями в MS Excel:
Рис. 3.21
Команда ОК открывает диалоговое окно:
Рис. 3.22
Знаковый критерий Вилкоксона
111
По команде Да расчетная таблица остается на рабочем листе, по
команде Нет остаются только исходные данные.
Задача 3.4. По данным таблицы 3.4 при уровне значимости
а = 0,05 проверить нулевую гипотезу Н^\р = 0,5, конкурирующая ги-
потеза р > 0,5.
Таблица 3.4
у.
85 63 55 52 59 81 48 65 55 85 38 44 37 43 78 33 29 30 29 71 54 21 25 53 69 98 61 60
86 67 52 53 60 82 48 67 57 83 40 46 40 45 80 30 32 30 25 75 50 28 26 59 67 99 63 70
Технология решения. Вызываем макрос Il test W. Начиная
с ячейки А1, вводим табличные данные, диапазон A1:Z2 выделяем
(рис. 3.23, 3.24).
А I В I С I D I Е I F I G I Н I I I J I К I L I М
85 63 55 52 59 81 65 55 85 38 44 37 43
86 67 52 53 60 82 67 57 83 40 46 40 45
Рис. 3.23
[ N [ О | Р | Q [ R | S | Т~ | ~Ц~ [ V ] ~W | X | Y | Z I
78 33 29 29 71 54 21 25 53 69 98 61 60
80 30 32 25 75 50 28 26 59 67 99 63 701
Рис. 3.24
Запуская макрос на исполнение, получаем окно ввода номера аль-
тернативной гипотезы:
Рис. 3.25
По команде ОК появляется окно ввода уровня значимости кри-
терия:
112
Непараметрические критерии для пар наблюдений
Рис. 3.26
Команда ОК возвращает результаты вычислений:
Рис. 3.27
По команде ОК появляется диалоговое окно, показанное на
рис. 3.22. Команда Да оставляет расчетную таблицу на рабочем листе
(рис. 3.28, 3.29):
a|b|c|d|e|f|g|h|i|j|k|l|m|
1 85 63 55 52 59 81 65 55 85 38 44 37 43
2 86 67 52 53 60 82 67 57 83 40 46 40 45
__3_ 1 4 -3 1 1 1 2 2 -2 2 2 3 2
JL 1 4 3 1 1 1 2 2 2 2 2 3 2
5 з?5‘ 21.5 17.5 3.5 3.5| 3.5 11 11J 11 11 11 1 7.5 11
Рис. 3.28
rNlo[p|t|R]s|T|u|v|w|x|Y|Z
78 33 29 29 71 54 21 25 53 69 98 61 60
80 30 32 25 75 50 28 26 59 67 99 63 70
2 -3 3 -4 4 -4 7 1 6 -2 1 2 ю"
2| 3’ ЗГ 4| 4! 4_7 4 6Г_2Г 1 2 10
11 17,5 17,5121.5; 21,5121,5} 25| 3,5 24 11 3.5 11 26|
Рис. 3.29
Ответ: принимается гипотеза р > 0,5.
Знаковый критерий Вилкоксона
113
Код макроса
Sub II_test_W()
Dim n As Integer, r As Single, s As Single, c As Single, t As Single,
alfa As Single, tip As Integer, h As Integer, z As Single, str As String
n = Selection.Columns.Count
For i = 1 To n
Cells (3, i) .Value = Cells (2, i) .Value - Cells (1, i) .Value
If Cells(3, i).Value > 0 Then
Cells(4, i).Value = Cells(3, i).Value
Else
Cells(4, i).Value = -Cells(3, i).Value
End If
Next
For i = 1 To n
s = Application.WorksheetFunction.Rank(Cells(4, i), Range(Cells(4, 1),
Cells(4, n)), 1)
r = Application.WorksheetFunction.Countlf(Range(Cells(4, 1), Cells(4, n)),
Cells(4, i).Value)
Cells(5, i).Value = s + (r - 1) * 0.5
Next
s = Application.WorksheetFunction.Sumlf(Range(Cells(3, 1), Cells(3, n)),
"<0", Range(Cells(5, 1), Cells(5, n)))
r = Application.WorksheetFunction.Sumlf(Range(Cells(3, 1), Cells(3, n)),
">0", Range(Cells(5, 1), Cells(5, n)))
c = Application.WorksheetFunction.Min(r, s)
tip = Application.InputBox("Введите номер альтернативной гипотезы: " & Chr(13)
& "1. р<>0,5; 2. р<0,5; 3. р>0,5," & Chr(13)
& "где р=Р(xi<yi).", Туре:=1)
If п <= 25 Then
If tip = 1 Then
h = Application. InputBox (."Введите номер уровня значимости: " & Chr (13) _
& " 1. 0,05; 2. 0,1", Туре: = 1)
Else
h = Application.InputBox("Введите номер уровня значимости: " & Chr(13) _
& " 1. 0,01; 2. 0,025; 3. 0,05; 4. 0,1", Туре:=1)
End If
Else
alfa = Application.InputBox("Введите уровень значимости критерия:",
Туре:=1)
End If
Select Case n
Case Is <= 25
If tip = 1 Then
t = G(n, h + 1)
Else
t = G(n, h)
End If
Linel:
If tip = 1 Then
If t < c Then
str = " p = 0,5."
114
Непараметрические критерии для пар наблюдений
Else
str = " р < > 0,5."
End If
MsgBox ("Значение статистики: Т = " & с & ". Критерий принятия Но: Т > " & t
& ". Принять:" & str)
End If
If tip = 2 Then
If t < r Then
str = " p = 0,5."
Else
str = " p < 0, 5."
End If
MsgBox ("Значение статистики: T =" & г & ". Критерий принятия Но: Т > " & t
& ". Принять:" & str)
End If
If tip = 3 Then
If t < s Then
str = " p = 0,5."
Else
str = " p > 0,5."
End If
MsgBox ("Значение статистики: T = " & s & ". Критерий принятия Но: Т > " & t
& ". Принять:" & str)
End If
Case Else
If tip = 1 Then
z = Application.WorksheetFunction.NormSInv(alfa / 2)
Else
z = Application.WorksheetFunction.NormSInv(alfa)
End If
z = FormatNumber(z, 4)
t=n* (n+1): t=t* (2 * n + 1) : t = t / 24: t=z* Sqr(t)
t = n* (n + 1) / 4 + t
GoTo Linel
End Select
Result = MsgBox ("Оставить расчетную таблицу?", 4 + 32, "Расчеты")
If Result = 6 Then
Exit Sub
End If
Range (Cells (3, 1), Cells(5, n)).ClearContents
End Sub
§4. Модификации критерия
Вилкоксона
1. При п > 25 одна из модификаций критерия Вилкоксона ([10],
стр. 289) состоит в том, что в качестве статистики принимают
Модификация критерия Вилкоксона
115
п(п + 1) т
Z= , 4
1п(п + 1)(2л? +1)
V 24
В частности, для данных задачи 3.4 ее значение
26<26+1>-100
7 = ~ 1 92
Н 126(26+ 1)(52 + 1)
V 24
Нулевая гипотеза Но при альтернативной гипотезе на уровне
значимости а принимается, если <и а, иначе принимается
1~2
а ~ квантиль стандартного нормального распределения уровня
Нулевая гипотеза Но при альтернативной гипотезе Н\2} или Н{3) на
уровне значимости а принимается, если ZB < w,_a, иначе принимается
альтернативная гипотеза.
Такой подход реализован в макросе II_test_Z, применяемом стан-
дартным образом. Рассмотрим применение его к задаче 3.4. Запуск
макроса на исполнение, когда входные данные, как на рис. 3.23, 3.24,
открывает окно ввода номера альтернативной гипотезы (рис. 3.25),
затем окно ввода уровня значимости критерия (рис. 3.26), и возвра-
щает:
Рис. 3.30
Код макроса
Sub II_test_Z()
Dim n As Integer, r As Single, s As Single, c As Single, u As Single,
z As Single, alfa As Single, str As String, tip As Integer
n = Selection.Columns.Count
For i = 1 To n
116 Непараметрические критерии для пар наблюдений
Cells(3, i) .Value = Cells (2, i) .Value - Cellsd, i) .Value
If Cells(3, i).Value > 0 Then
Cells(4, i) .Value = Cells(3, i) .Value
Else
Cells(4, i).Value = -Cells(3, i).Value
End If
Next
For i = 1 To n
s = Application.WorksheetFunction.Rank(Cells(4, i), Range(Cells(4, 1), Cells(4,
n)), 1)
r = Application.WorksheetFunction.Countlf(Range(Cells(4, 1), Cells(4, n)),
Cells (4, i).Value)
Cells(5, i).Value = s + (r - 1) * 0.5
Next
s = Application.WorksheetFunction.Sumlf(Range(Cells(3, 1), Cells(3, n)), "<0",
Range(Cells(5, 1), Cells(5, n)))
r = Application.WorksheetFunction.Sumlf(Range(Cells(3, 1), Cells(3, n)), ">0",
Range(Cells(5, 1), Cells(5, n)))
c = Application.WorksheetFunction.Min(r, s)
tip = Application.InputBox("Введите номер альтернативной гипотезы: " & Chr(13)
& "1. р<>0,5; 2. р<0,5; 3. р>0,5," & Chr(13)
& "где р=Р(xi<yi).", Туре:=1)
alfa = Application.InputBox("Введите уровень значимости критерия: ", _
Туре:=1)
If tip = 1 Then
u = Application.WorksheetFunction.NormSInvd - alfa / 2)
Else
u = Application.WorksheetFunction.NormSInvd - alfa)
End If
z = n* (n + 1) /4: z = z-c:d = n* (n + 1): d = d * (2 * n + 1)
d = d / 24: d = d A (1 / 2): z = z / d
If tip = 1 Then
If z < u Then
str = " p = 0,5."
Else
str = " p <> 0,5."
End If
End If
If tip = 2 Then
If c = r And z >= u Then
str = " p < 0,5."
Else
str = " p = 0,5."
End If
End If
If tip = 3 Then
If c = s And z >= u Then
str = " p > 0,5."
Else
str = " p = 0,5."
Модификация критерия Вилкоксона
117
End If
End If
z = FormatNumber (z, 3): u = FormatNumber(u, 3)
MsgBox ("Значение статистики: Z= " & z & ". Критерий принятия Ho: Z < " & u _
& ". Принять:" & str)
Result = MsgBox("Оставить расчетную таблицу?", 4 + 32, "Расчеты")
If Result = 6 Then
Exit Sub
End If
Range(Cells(3, 1), Cells (5, n)).ClearContents
End Sub
2. При n > 25 более точная оценка Ткр(а, п) предыдущего парагра-
фа, учитывающая связки (совпадающие ранги), имеет вид:
_ z ч и(и + 1) z ч /и(и + 1)(2и + 1) 1 xn z .чу
ГхДа.п) = v - -+z(a)J—-----------£----->— у. у = —У - 1)(Г, +1),
4 V 24 4о
k - число связок, Л - длина связки. В большинстве случаев поправка
у несущественная, но может оказаться и полезной.
Задача 3.5. Найти решение задачи 3.4, применяя формулу Ткр(а, п)
с поправкой на связки.
Технология решения. Продолжаем вычисления, показанные на
рис. 3.28, 3.29. Копируем диапазон A5:Z5 и специальной вставкой
(значения) вставляем его значения в диапазон A7:Z7, затем прово-
дим сортировку по возрастанию. В ячейку А8 вводим 1, в ячейке В8
записываем формулу: =ЕСЛИ(А7=В7; А8;А8+1). Копирование ее
в ячейки C8:Z8 дает в ячейке Z8 число связок:
3,5 3.5' 3.5 3.5 3.5 3.5 11 11 11; 11 11 11 11 11 11
8 ] 1| 1 1 1 1 1 2 2 2 2 2 2 2 2 2
Рис. 3.31
17.5 17,5'17,5 17,521,5 21,5 21.5 21,5 24- 25 26
3 3 3 3 4 4 4 4 5 6 7J
Рис. 3.32
Связки 5, 6, 7, содержащие по одному элементу, можно не учиты-
вать. Поэтому вводим в диапазон A10:D10 значения: 3,5; 11; 17,5; 21,5.
Записывая в ячейке АН формулу =C4ETECJIH(A7:Z7;A1O) и копи-
руя ее в ячейки В11 :D 11, получаем длины связок 1, 2,3,4. Ввод в ячей-
ку А12 формулы =А11*(А11-1)*(А11 + 1) и копирование ее в осталь-
118
Непараметрические критерии для пар наблюдений
ные ячейки диапазона A12:D12, а затем суммирование в ячейке Е12
значений этого диапазон и деление на 48, дает значение поправки у:
ЯГ 3,5 ifi 17.5 21,5,
11 6Г 9' 4.г 4'
12 1 210 , 720 60 U'|2F1
Рис. 3.33
Остается в свободной ячейке провести вычисления Ткр(0,05, 26)
по формуле:
=26*27/4+КОРЕНЬ(26*(26+1)*(2*26+1)/24-21,9)
*НОРМСТОБР(0,05)
Она дает Ткр(0,05, 26) « 111,195 > 100 = Тна6л.
Ответ: нулевая гипотеза отклоняется.
Поправка на связки учтена в макросе II_test_P, применяемом
стандартно. Для задачи 3.5 он возвращает:
A I В T c I D [ E [ F Г G Г H
1 8,6 8.7 9 9,5 9.8 10,2
2 9,2 9,4 9,6 919 10 10,5 10.5 11
Рис. 2.34
Код макроса
Sub II_test_P()
Dim n As Integer, k As Integer, r As Single, s As Single, c As Single, _
g As Single, alfa As Single, t As Single, tip As Integer, str As String
n = Selection.Columns.Count
For i = 1 To n
Cells(3, i) .Value = Cells (2, i) .Value - Cellsd, i) .Value
If Cells(3, i).Value > 0 Then
Cells(4, i) .Value = Cells(3, i) .Value
Else
Cells (4, i).Value = -Cells (3, i).Value
End If
Next
For i = 1 To n
s = Application.WorksheetFunction.Rank(Cells(4, i), Range(Cells(4, 1), _
Cells(4, n)), 1)
r = Application.WorksheetFunction.Countlf(Range(Cells(4, 1), Cells(4, n)),
Cells(4, i).Value)
Cells(5, i).Value = s + (r - 1) * 0.5
Next
s = Application.WorksheetFunction.Sumlf(Range(Cells(3, 1), Cells (3, n)),
Модификация критерия Вилкоксона
119
"<0", Range(Cells(5, 1), Cells(5, n)>)
г = Application.WorksheetFunction.Sumlf(Range(Cells(3, 1), Cells(3, n)), _
">0", Range(Cells(5, 1), Cells(5, n)))
c = Application.WorksheetFunction.Min(r, s)
For i = 1 To n
Cells(7, i).Value = Cells(5, i).Value
Next
Range(Cells(7, 1), Cells(7, n)).Sort Keyl:=Range("A7"), _
Orderl:=xlAscending, Orientation:=xlLeftToRight
08] = 1
Range("A10").Value = Range(«А7»).Value
k = 1
For i = 2 To n
If Cells(7, i).Value = Cells(7, i - 1).Value Then
Cells(8, i).Value = Cells(8, i - 1).Value
Else
Cells(8, i).Value = Cells(8, i - 1).Value + 1
k = k + 1
CellsdO, k).Value = Cells(7, i).Value
End If
Next
For i = 1 To k
Cells(11, i).Value = Application.WorksheetFunction.Countlf(Range(Cells(7, 1),
Cells(7, n)), CellsdO, i))
Next
For i = 1 To k
Cells(12, i).Value = Cellsdl, i).Value A 3 - Cells(ll, i).Value
Next
Range("A13").Value = Application.WorksheetFunction.Sum(Range(Cells(12, 1),
Cells(12, k))) / 48
tip = Application.InputBo-x("Введите номер альтернативной гипотезы: " & Chr(13)
& "1. р<>0,5; 2. р<0,5; 3. р>0,5," & Chr(13)
& "где р=Р(xi<yi) .", Туре: = 1)
alfa = Application.InputBox("Введите уровень значимости критерия: ",
Туре:=1)
Range("В13").Value = n
If tip = 1 Then
Range("C13").Value = Application.WorksheetFunction.NormSInv(alfa / 2)
Else
Range("C13").Value = Application.WorksheetFunction.NormSInv(alfa)
End If
Range("D13").Formula = "=$B$13 * ($B$13 + 1) / 4 "
Range("E13").Formula = "=$B$13 * ($B$13 + 1) * (2 * $B$13 + 1) / 24 - $A$13"
Range("F13").Formula = "=$C$13* Sqrt($E$13)"
t = Application.WorksheetFunction.Sum(Range("D13"), Range("F13"))
Range("G13").Value = t
If tip = 1 Then
If t < c Then
str = " p = 0,5."
Else
120
Непараметрические критерии для пар наблюдений
str = " р о 0,5."
End If
End If
If tip = 2 Then
If c = r And t >= c Then
str = " p < 0,5."
Else
str = " p = 0,5."
End If
End If
If tip = 3 Then
If c = s And t >= c Then
str = " p > 0,5."
Else
str = " p = 0,5."
End If
End If
c = FormatNumber(c, 3): t = FormatNumber(t, 3)
MsgBox ("Значение статистики: T = " & с & ". Критерий принятия Но: Т > " & t
& ". Принять: " & str)
Range (Cells(3, 1), Cells(8, n)).ClearContents
Range (Cells (10, 1), Cells (13, k)).ClearContents
End Sub
3. При n > 25 еще более точный метод ([21], стр. 47), чем учиты-
вающий связки, предложен Иманом Р. (Iman R.). Статистика:
п-1
n-Z2
Критические точки вычисляются по формуле:
Ju 2
fa(n “ О “ квантиль уровня а распределения Стьюдента с п - 1 сте-
пенями свободы, za - квантиль уровня а стандартного нормального
распределения.
Нулевая гипотеза Но при альтернативной гипотезе на уровне
значимости а принимается, если Jв < J (Х, иначе принимается Н\'\
1-2
Нулевая гипотеза Но при альтернативной гипотезе Н[2) или 3) на
уровне значимости а принимается, если JB < J(_a, иначе принимается
альтернативная гипотеза.
Например, для задачи 3.4 значение статистики:
_ 1,92
./«- 2
25
26-1.922
= 1,98.
Модификация критерия Вилкоксона
121
Значения квантилей показаны на рис. 3.35,3.36.
Рис. 3.35
Рис. 3.36
Поэтому J()g5 » 1,68, что меньше JB * 1,98. Откуда следует, что при-
нимается гипотеза Н®\
Критерий Имана реализован в макросе II_test_I, применяемом
стандартно. В частности, применение макроса к задаче 3.4 возвра-
щает:
Рис. 3.37
122 Непараметрические критерии для пар наблюдений
Код макроса
Sub II_test_I()
Dim n As Integer, r As Single, s As Single, c As Single, z As Single, _
tip As Integer, u As Single, za As Single, t As Single, _
str As String, g As Single, alfa As Single
n = Selection.Columns.Count
For i = 1 To n
Cells (3, i) .Value = Cells (2, i) .Value - Cellsd, i) .Value
If Cells(3, i).Value > 0 Then
Cells(4, i).Value = Cells(3, i).Value
Else
Cells (4, i) .Value = -Cells (3, i) .Value
End If
Next
For i = 1 To n
s = Application.WorksheetFunction.Rank(Cells(4, i), Range(Cells(4, 1),
Cells(4, n)), 1)
r = Application.WorksheetFunction.Countlf(Range(Cells(4, 1), Cells(4, n)),
Cells (4, i).Value)
Cells(5, i) .Value = s + (r - 1) * 0.5
Next
s = Application.WorksheetFunction.Sumlf(Range(Cells(3, 1), Cells(3, n)),
"<0", Range(Cells(5, 1), Cells(5, n)))
r = Application.WorksheetFunction.Sumlf(Range(Cells(3, 1), Cells(3, n)),
">0", Range(Cells(5, 1), Cells(5, n)))
c = Application.WorksheetFunction.Min(r, s)
tip = Application.InputBox("Введите номер альтернативной гипотезы: " & Chr(13)
& "1. р<>0,5; 2. р<0,5; 3. р>0,5," & Chr (13)
& "где p=P(xi<yi).", Туре:=1)
alfa = Application.InputBox("Введите уровень значимости критерия: ", _
Туре:=1)
If tip = 1 Then
t = Application.WorksheetFunction.TInv(alfa, n - 1)
za = Application.WorksheetFunction.NormSInv(1 - alfa / 2) •
Else
t = Application.WorksheetFunction.TInv(alfa * 2, n - 1)
za = Application.WorksheetFunction.NormSInv(1 - alfa)
End If
t = (t + za) / 2
b = n*(n + l)/4:b = b-c:d = n* (n + 1): d = d * (2 * n + 1)
d = d / 24: d = d A (1 / 2): z = b / d: g = (1 + Sqr((n - 1) / (n - z A 2))) * z / 2
If tip = 1 Then
If g < t Then
str = " p = 0,5."
Else
str = " p <> 0,5."
End If
End If
If tip = 2 Then
Критерий Спирмена
123
If с = г And g >= t Then
str = " p < 0,5."
Else
str = " p = 0,5."
End If
End If
If tip = 3 Then
If c = s And g >= t Then
str = " p > 0,5."
Else
str = " p = 0,5."
End If
End If
t = FormatNumber(t, 3): g = FormatNumber(g, 3)
MsgBox ("Значение статистики: J = " & g & ". Критерий принятия Ho: J < " & t
& ". Принять: " & str)
Range(Cells (3, 1), Cells (5, n)).ClearContents
End Sub
§5. Критерий Спирмена
По двумерной выборке (х(, z/t), i = 1,я, выборочный коэффициент рв
ранговой корреляции Спирмена признаков X и Y находится по фор-
муле:
ri (г,*) _ ранги вариант х/г/Д | рв | < 1.
Критерий Спирмена по коэффициенту рв и заданному уровню
значимости а проверяет гипотезу Но: рг = 0, альтернативная гипотеза
Н,: рг * 0. Гипотеза Но принимается, если | рв | < Ткр, где
Ткр=1к1,(а,п-2)-^^,
£кр(а, п - 2) - критическая точка распределения Стьюдента для дву-
сторонней критической области. В противном случае принимается
гипотеза Н,. В случае принятия Норанговая корреляционная зависи-
мость между признаками признается незначимой, в противном слу-
чае - значимой.
Задача 3.6. Знания 10 студентов проверены по двум тестам: А и
В. Оценки по стобалльной системе оказались следующими (в первой
строке по тесту А, во второй - по тесту В):
124
Непараметрические критерии мя пар наблюдений
95 90 86 84 75 70 62 60 57 50
92 93 83 80 55 60 45 72 62 70
Найти рв и проверить при а =0,01 нулевую гипотезу Н(): рг = 0,
альтернативная гипотеза : р, * 0.
Технология решения. Вводим выборочные данные:
Рис. 3.38
В ячейке А4 записываем формулу ранга варианты ячейки А1, на-
пример, при ранжировании по убыванию:
=РАНГ(А1;$А1:$}1)+(СЧЁТЕСЛИ($А1:$}1;А1)-1)*0,5
Копируя формулу в остальные ячейки диапазона A4:J5 получаем
ранги всех вариант:
0 1 2^ 3* 41 5 6 ?! 8 9 10
2 I 3498 10 57 61
Рис. 3.39
В ячейке А7 записываем формулу:
=(А4-А5)А2
Копируя ее в остальные ячейки A7J7 находим значения (г - г/)2:
I 7 | 1 1 0 0 16 4 9 9 4 1б1
Рис. 3.40
Кнопкой Е в ячейку К7 вставляем сумму этих значений. В ячейке
L7 вычисляем рн, применяя формулу:
= 1-6*К7/(10А3-10)
Она дает рн = 0,636:
ll ol oj . jej 4? э| э| , 4 16 6р|о,63б1
Рис. 3.41
Критерий Спирмена
125
Переходим к проверке гипотезы Но. Выделяем свободную ячейку,
например, А9 и, пользуясь кнопкой /(х), вызываем диалоговое окно
функции СТЬЮДРАСПОБР, где вводим следующие данные, под-
тверждая их командой ОК:
Рис. 3.42
В ячейке В9 записываем формулу:
=A9*KOPEHb((l-L7A2)/(10-2))
Она возвращает ТК|> = 0,915. Так как | рв | = 0,636 < 0,915 = ТК|>, то
нулевая гипотеза рг = 0 принимается.
Ответ: рв = 0,636, ранговая зависимость незначимая.
Макрос II_test_Sp находит выборочный ранговый коэффициент и
проверяет гипотезу Но автоматически. Для применения макроса надо
ввести выборочные данные в первые две строки листа MS Excel, на-
чиная с ячейки А1, выделить диапазон с данными и запустить макрос
на исполнение. Например, если данные, как на рис. 3.38, то запуск
макроса на исполнение возвращает полученное ранее значение вы-
борочного коэффициента Спирмена:
Рис. 3.43
'126 Непараметрические критерии для пар наблюдений
По команде Да появляется окно ввода уровня значимости крите-
рия (рис. 3.44).
Рис. 3.44
Команда ОК возвращает:
Рис. 3.45
Задача 3.7. Два контролера А и В расположили образцы из-
делий, изготовленных 9 мастерами, в порядке ухудшения качества
(в скобках порядковые номера изделий одинакового качества):
(А) 1 2 (3, 4, 5) (6, 7, 8, 9)
(В) 2 1 4 3 5 (6, 7) 8 9
Найти выборочный коэффициент ранговой корреляции Спирме-
на рв и проверить при а = 0,05 нулевую гипотезу Но: рг = 0, альтерна-
тивная гипотеза Н,: рг 0.
Технология решения. Учитывая наличие изделий одинакового
качества, вводим выборочные данные в виде:
Рис. 3.46
Критерий Спирмена
127
Запуск макроса на исполнение возвращает:
Рис. 3.47
Команда Да открывает окно для ввода уровня значимости критерия:
Рис. 3.48
Команда ОК возвращает:
Рис. 3.49
Ответ: рв = 0,93, коэффициент значимый.
Код макроса
Sub II_test_Sp()
Dim n As Integer, r As Single, rx() As Single, ry() As Single,
alfa As Single, z As Single, t As Single, s As Single
n = Selection.Columns.Count
ReDim rx(l To n) As Single
ReDim ry(l To n) As Single
For i = 1 To n
rx(i) = Application.WorksheetFunction.Rank(Cells(1, i), Range(Cells(1, 1),
Cellsd, n)), 1) + 0.5 * Application.WorksheetFunction.Countlf(
128
Непараметрические критерии для пар наблюдений
Range (Cells (1, 1), Cellsd, n)), Cellsd, i)) - 0.5
ry(i) = Application.WorksheetFunction.Rank(Cells(2, i), Range(Cells(2, 1),
Cells(2, n)), 1) + 0.5 * Application.WorksheetFunction.Countlf(
Range (Cells(2, 1), Cells(2, n)), Cells(2, i)) - 0.5
Next
s = Application.WorksheetFunction.SumXMY2(rx, ry)
r = 1 - 6 * s / (n A 3 - n)
r = FormatNumber(r, 4)
Result = MsgBox("Коэффициент Спирмена г = " & г & ". Проверить значимость?",
4 + 32, "Spearman")
Select Case Result
Case 6
alfa = Application.InputBox("Введите уровень значимости: ", Type:=l)
z = Application.WorksheetFunction.TInv(alfa, n - 2)
t = z * Sqr((l - r A 2) / (n - 2))
t = FormatNumber(t, 4)
If Abs(r) < t Then
MsgBox ("Значение r=" & r & ". Критерий незначимости: | г | < " & t
& ". Принять: незначимый.")
Else
MsgBox ("Значение г=" & г & ". Критерий незначимости: | г | < " & t
& ". Принять: значимый.")
End If
Case 7
Exit Sub
End Select
End Sub
§6. Критерий Кендалла
По двумерной выборке i = 1,и, не имеющей совпадающих зна-
чений выборочный коэффициент Кендалла ранговой корреля-
ционной зависимости между признаками находится по формуле:
,,=1—«L.
»(»-!)
Q - число пар (г/, г‘) таких, что (г - - г*) < 0, | тв | < 1.
Критерий Кендалла по коэффициенту тв и уровню значимости
а проверяет нулевую гипотезу Но : тг = 0, альтернативная гипотеза
Н,: тг 0. Гипотеза Но принимается, если | тв | < Т , где
= /2(2п + 5)
кр Z,ip у9л(л-1)’
1 — (X
ф(2 | ) = —__ , ф - функция Лапласа. В противном случае принима-
ется гипотеза Нг В случае принятия Н() ранговая корреляционная за-
Критерий Кендалла
129
висимость между признаками признается незначимой, в противном
случае - значимой.
Задача 3.8. По данным задачи 3.6 найти выборочный ранговый
коэффициент Кендалла и проверить значимость.
Технология решения. Вводим выборочные данные (рис. 3.38).
Стандартным образом находим ранги (рис. 3.39). Копируем диапазон
A5J5 и специальной вставкой (значения, транспонировать) вставля-
ем числовые данные в диапазон К6:К15. В ячейку В6 вводим форму-
лу =ЕСЛИ($К6>В$5;1;0) и копируем ее во все ячейки выше главной
диагонали диапазона A6J15. В любой свободной ячейке, например,
А16, формулой =CYMM(A6:J14) находим сумму значений этого диа-
пазона, что дает Q =12. В ячейке В16 формулой =1-4*А16/(10А2-10)
вычисляем тв = 0,467 (рис. 3.50).
Для проверки гипотезы Но выделяем ячейку С16, открываем диа-
логовое окно НОРМСТОБР и вводим данные для нахождения zKpl
что подтверждаем командой ОК:
Рис. 3.51
130 Непараметрические критерии для пар наблюдений
В ячейке D16 вычисляем Ткр по формуле
=С16*КОРЕНЬ(2*(2*10+5)/9/10/(10-1))
Она дает Г = 0,64. Так как выполняется | тв | < Ткр, то Но принима-
ется.
Ответ: тн = 0,467, коэффициент незначимый.
Макрос II_test_K, применяемый стандартно, находит выбороч-
ный ранговый коэффициент Кендалла и проверяет значимость.
Например, запуск макроса на исполнение для данных последней
задачи (рис. 3.38) возвращает значение, полученное непосредствен-
ными вычислениями в MS Excel:
Рис. 3.52
Команда Да открывает окно ввода уровня значимости критерия:
Рис. 3.53
Команда ОК возвращает результаты проверки на значимость:
Рис. 3.54
Задача 3.9. По данным задачи 3.4 найти выборочный ранговый
коэффициент Кендалла и проверить значимость.
Критерий Кендалла
131
Технология решения. Входные данные показаны на рис. 3.23,3.24.
Запуск макроса на исполнение возвращает:
Рис. 3.55
По команде Да открывается окно ввода уровня значимости кри-
терия:
Рис. 3.56
Команда ОК возвращает сообщение:
Рис. 3.57
Ответ: тн = 0,92, коэффициент значимый.
Код макроса
Sub II_test_K()
Dim n As Integer, x() As Single, y() As Single, tau As Single,
alfa As Single, z As Single, t As Single, s As Integer
n = Selection.Columns.Count
ReDim x(l To n) As Single
ReDim y(l To n) As Single
For i = 1 To n
x(i) = Application.WorksheetFunction.Rank(Cells(1, i), _
Range (Cells (1, 1), Cellsd, n)), 1)
132 Непараметрические критерии для пар наблюдений
y(i) = Application.WorksheetFunction.Rank(Cells(2, i), _
Range(Cells(2, 1), Cells(2, n)), 1)
Next
s = 0
For i = 1 To n - 1
For j = i + 1 To n
If (x(i) - x(j)) * (y(i) - у (j)) < 0 Then
s = s + 1
End If
Next
Next
tau = l- 4*s/n / (n-1)
tau = FormatNumber(tau, 4)
Result = MsgBox("Коэффициент Кендалла r= " & tau & ". Проверить значимость?",
4 + 32, "Kendall")
Select Case Result
Case 6
alfa = Application.InputBox("Введите уровень значимости: ", Type:=l)
z = Application.WorksheetFunction.NormSInv(l - alfa / 2)
t = z * Sqr(2 * (2 * n + 5) / 9 / n / (n - 1))
t = FormatNumber(t, 4)
If Abs(tau) < t Then
MsgBox ("Значение r=" & tau & ". Критерий незначимое™: | г | < " & t _
& ". Принять: незначимый.")
Else
MsgBox ("Значение r=" & tau & ". Критерий незначимости: | г | < " & t _
& ". Принять: значимый.")
End If
Case 7
Exit Sub
End Select
End Sub
§7. Критерий Ван дер Вардена
По двумерной выборке (х,,//,), i = 1, л, выборочный коэффициент Ван
дер Вардена [12] ранговой корреляционной зависимости между при-
знаками находится по формуле:
Xu^u_i_
Д _ ,= 1 "+> >14-1
Рв II ’
*=1 «4-1
ri (г,) ~ ранги вариант х/г/,), ир - р-квантиль стандартного нормально-
го распределения.
При уровне значимости а коэффициент рв признается незначи-
мым, если | 51 < 5(a), где
Критерий Ван дер Вардена
133
s--i^
/=1 /1+1 и+|
5(a) = w
2
Jn-\
В противном случае коэффициент рп значимый.
Задача 3.10. По данным задачи 3.6 найти выборочный ранго-
вый коэффициент Ван дер Вардена и проверить значимость.
Технология решения.
1. Вводим выборочные данные (рис. 3.38) и находим их ранги
(рис. 3.39), слагаемое (СЧЁТЕСЛИ($А1:$}1;А1)-1)*0,5 можно
опустить.
2. В ячейку А7 вводим формулу =А4/11 и копируем ее в ячейки
диапазона A7J8.
3. Выделяем ячейку А10 и вызываем функцию НОРМСТОБР,
в диалоговое окно которой вводим А7, что подтверждаем ОК:
Рис. 3.58
4. Формулу ячейки А10 копируем в остальные ячейки диапазона
A10J11:
Рис. 3.59
134
Непараметрические критерии для пар наблюдений
5. В ячейку А13 вводим формулу =А4/11 и копируем ее в B13J13.
6. В ячейку А14, с помощью диалогового окна, показанно-
го на рисунке 3.58, или непосредственно, вводим формулу
=НОРМСТОБР(А13)А2 и копируем ее в B13J13.
7. В ячейке А16 поформуле =CyMMnPOH3B(A10:J10;All:Jll)
вычисляем 5 = 3,7443.
8. В ячейке В16 по формуле =СУММ(А14:}14) находим 6,2164.
9. В ячейке С16 по формуле =А16/В16 вычисляем выборочный
коэффициент Вандер Вардена рн = 0,6023.
10. Для проверки значимости в ячейку D16 вводим формулу
=НОРМСТОБР(1-О,01/2)*В16/3, которая дает 5(a) = 5,3374:
0.0909 0,1818 0,2727 0,3636 0.4545 0,5455 0.6364 0,7273 0.8182 0.9091
1.7827 0.8253 0.3655 0.1216 0.01301 0.01301 0,1216 0.3655 0.8253 1.7827
13
14
15
16 3,7443 6.2164 0.6023| 5,33741
"Т~
Рис. 3.60
Так как | 5| = 3,7443 < 5,3374 = 5(a), то коэффициент рн = 0,6023
незначимый.
Ответ: рн = 0,6023, коэффициент незначимый.
Макрос II_test_V, применяемый стандартно, находит по заданной
выборке коэффициент рн ранговой корреляции Ван дер Вардена и
проверяет его значимость.
Например, запуск макроса на исполнение для данных последней
задачи (рис. 3.38) дает значение, полученное непосредственными вы-
числениями в MS Excel:
Рис. 3.61
Критерий Ван дер Вардена
135
Команда Да открывает окно ввода уровня значимости критерия:
Рис. 3.62
Команда ОК возвращает:
Рис. 3.63
Код макроса
Sub II_test_V()
Dim n As Integer, r As Single, rx() As Single, ry() As Single, _
alfa As Single, z As Single, t As Single, s As Single
n = Selection.Columns.Count
ReDim rx(l To n) As Single
ReDim ry(l To n) As Single
s = 0
For i = 1 To n
rx(i) = Application.WorksheetFunction.Rank (Cells (1, i), _
Range(Cellsd, 1), Cellsd, n)), 1)
rx(i) = Application.WorksheetFunction.NormSInv(rx(i) / (n + 1))
ry(i) = Application.WorksheetFunction.Rank(Cells(2, i), _
Range (Cells (2, 1), Cellsd, n)), 1)
ry(i) = Application.WorksheetFunction.NormSInv(ry(i) / (n + 1))
s = s + rx(i) * ry(i)
Next
t = 0
For i = 1 To n
136
Непараметрические критерии для пар наблюдений
t = t + (Application.WorksheetFunction.NormSInv(i / (n + 1))) A 2
Next
r = s / t
s = FormatNumber (s, 4)
r = FormatNumber (r, 4)
Result = MsgBox("Коэффициент Ван дер Вардена r= " & г _
& ". Проверить значимость?", 4 + 32, "Ранговая зависимость")
Select Case Result
Case 6
alfa = Application.InputBox("Введите уровень значимости: ", Type:=l)
z = Application.WorksheetFunction.NormSInv(1 - alfa /2) * t / Sqr(n - 1)
z = FormatNumber(z, 4)
If Abs(s) < z Then
MsgBox ("Значение S=" & s & ". Критерий незначимое™: | S I < " & z _
& ". Принять: незначимый.")
Else
MsgBox ("Значение S=" & s & ". Критерий незначимое™: | S | < " & z
& ". Принять: значимый.")
End If
Case 7
Exit Sub
End Select
End Sub
§8. Критерий Ширахате
Критерий Ширахате (Shirahate S.) аналогичен ([12], стр. 630) кри-
терию Спирмена, но более эффективен для малых выборок (хр у),
i = 1, п. Статистика критерия:
S = t^R^’
1=1
К, = '£[h(xi-xi)+h(xl-yj)],
;=i
= £I h^‘ ~ xi > + h(M ~ У) > 1>
./=1
, fl, t>Q,
A(r) = <
[0, t<0.
Критические точки статистики 5 приведены в таблице 3.5.
Критерий Ширахате
137
Таблица 3.5
п 0,10 0,05
S, «2 S, s2
4 62 96 61 97
5 116 175 114 181
6 199 195 194 302
7 313 456 302 468
8 467 666 450 683
9 664 932 642 955
10 911 1259 881 1289
12 1575 2125 1528 2173
14 2506 3314 2436 3386
16 3748 4877 3649 4999
18 5349 6863 5214 6999
20 7353 9311 7177 9501
Если 5j(a) < 5 < 52(a), то на уровне а ранговая корреляция при-
знается незначимой, в противном случае - значимой.
Задача 3.11. Проверить критерием Ширахате на уровне a = 0,05
наличие ранговой корреляционной зависимости между признаками
X и Y, заданными выборкой:
(2,6), (4,3), (7,5), (1,7), (5,1)
Технология решения.
1. Вводим выборочные данные:
Рис. 3.64
2. Копируем диапазон А1:Е1 и вставляем данные в диапазон
B4:F4.
3. Копируем диапазон А2:Е2 и вставляем данные в диапазон
G4:K4.
4. Копируем диапазон В4: К4 и специальной вставкой (транспо-
нировать) вставляем данные в диапазон А5:А14.
5. В ячейку В5 вводим формулу =ЕСЛИ($А5>=В$4;1;0) и копи-
руем ее в остальные ячейки диапазона В5:К14.
138
Непараметрические критерии для пар наблюдений
6. В ячейку L5 вводим формулу =СУММ(В5:К5) и копируем ее
в остальные ячейки диапазона L5:L14.
7. В ячейку М15 вводим формулу =СУММПРОИЗВ(Ь5:Ь9;
L10:L 14), возвращающую значение критерия:
4 2 4 7 1 5 6 3 5 7 1
5 2 1 0 0 1 0 0 0 0 0 1 3
6 4 1 1 0 1 0 0 1 0 0 1 5
7 7 1 1 1 1 1 1 1 1 1 1 10
8 1 0 0 0 1 0 0 0 0 0 1 2
9" 5 1 1 0 1 1 0 1 1 0 1 7
io’ 6 1 1 0 1 1 1 1 1 0 1 8
’if 3 1 0 0 1 0 0 1 0 0 1 4
12 5 1 1 0 1 1 0 1 1 0 1 7
13' 7 1 1 1 1 1 1 1 1 1 1 10
14 15 1 0 0 0 1 0 0 0 0 0 1 2 |~1481
Рис. 3.65
8. По таблице 3.5 находим критические значения 5, = 114 и
S2 = 181. Так как 5, < 148 < S2, то ранговая корреляционная за-
висимость признается незначимой
Ответ: корреляционная зависимость незначимая.
Критерий Ширахатэ реализован в макросе IltestSh, применя-
емом стандартно, таблица 3.5 введена, как процедура-функция. На-
пример, если введены данные, как на рис. 3.64, то, запуская макрос
на исполнение, пользователь получает окно для ввода номера уровня
значимости критерия:
Рис. 3.66
Критерий Ширахате
139
Команда ОК возвращает полученные ранее результаты:
Рис. 3.67
Код макроса
Sub II_test_Sh()
Dim n As Integer, m As Integer, r As Single, s As Integer,
c As Single, u As Single, z As Single, w As Integer,
str As String, tip As Integer, h As Integer, alfa As Single
n = Selection.Columns.Count
For i = 1 To n
Cells(4, 1 + i) .Value = Cellsd, i) .Value
Cells(4, n + 1 + i).Value = Cells (2, i).Value
Cells (4 + i, 1) .Value = Cellsd, i) .Value
Cells(n + 4 + i, 1).Value = Cells(2, i).Value
Next
For i = 1 To 2 * n
For j = 1 To 2 * n
If Cells(4 + i, 1) .Value >= Cells (4, j + 1) .Value Then
Cells (4 + i, j + 1) .Value = 1
Else
Cells (4 + i, j + 1) .Value = 0
End If
Next
Next
For i = 1 To 2 * n
Cellsd + i, 2 * n + 2).Value = Application.WorksheetFunction. _
Sum (Range (Cells (4 + i, 2), Cellsd + i, 2 * n + 1)))
Next
s = 0
For i = 1 To n
s = s + Cellsd + i, 2 * n + 2).Value * Cellsd + n + i, 2 * n + 2) .Value
Next
Cells (2 * n + 5, 2 * n + 3) .Value = s
h = Application.InputBox("Введите номер уровня значимости: 1. 0,10; 2. 0,05 ",
Туре:=1)
If h = 1 Then
If G(n, 1) < s And s < G(n, 2) Then
140 Непараметрические критерии для пар наблюдений
MsgBox ("Значение статистики: S= " & s & ". Область незначимости: ("
& G(n, 1) & ", " & G(n, 2) & ") . Принять: незначимая.")
Else
MsgBox ("Значение статистики: S= " & s & ". Область незначимости: ("
& G(n, 1) & ", " & G(n, 2) & "). Принять: значимая.")
End If
Else
If G(n, 3) < s And s < G(n, 4) Then
MsgBox ("Значение статистики: S= " & s & ". Область незначимости: ("
& G(n, 3) & ", " & G(n, 4) & "). Принять: незначимая.")
Else
MsgBox ("Значение статистики: S= " & s & ". Область незначимости: ("
& G(n, 3) & ", " & G(n, 4) & "). Принять: значимая.")
End If
End If
Result = MsgBox("Оставить расчетную таблицу?", 4 + 32, "Расчеты")
If Result = 6 Then
Exit Sub
End If
Range (Cells(4, 1), Cells(2 * n + 5, 2 * n + 3)).ClearContents
End Sub
Глава 4
Непараметрические
критерии для таблиц
сопряженности
§1. Четырехклеточный X2 критерий ... 142
§2. Общий случай X2 критерия ... 149
§3. Точный критерий Фишера ... 153
§4. G-критерий Вулфа.... ... 156
§5. Критерий Макнимары ... 159
§6. Биномиальные критерии ... 163
§7. Критерий Ле Роя ... 167
142
Непараметрические критерии для таблиц сопряженности
§1. Четырехклеточный %2 критерий
Пусть имеются два признака X и Y, принимающие бинарные значе-
ния, например, из множества {1,0}, и две выборки значений этих при-
знаков г,, i = 1,пл и yJt j = 1, пч, пх + пу= п. В этом случае составленная
по частотам выборочных данных таблица 4.1 называется таблицей
сопряженности 2x2.
Таблица 4.1
1 0 Е
X а b я,
Y с d
я« И
Так как из соотношений
n<-nv Щ'Пц
а=——L, с=----------
п п
следует:
а _ с
то гипотеза независимости совокупностей (X, У) и (1, 0) равносильна
гипотезе однородности:
Н0:Р(Х=1|Х) = Р(У=1|У),
в краткой записи Н() : рх = р2. Альтернативная гипотеза Нх \ рх * р2.
Следуя [10] будем рассматривать и другие альтернативные гипотезы
тт. тт» а с а с
Н, :р}> р2итН} \рх <р2,когда—> — или — <—,соответственно.
п. п., пх
Четырехклеточный %2 критерий для проверки гипотезы Н() при од-
ной из альтернативных гипотез НЛ применяется, когда тш(л, b, с, d) >
> 3, и > 20. Статистика критерия:
> («-«о)2 . Ф~Ьп)2 (с-сп)2 (d-da)2
X =----------г-------г---------г--------,
Ь{} с*() d{}
где
_(а + с)пл. _(h + d)nx _(<i+c)ny _(b + d)n,t
- » И) ~ — ’ “0 — ’
п п п п
Четырехклеточный х2 критерий
143
записывается также в виде:
2 ntad-bc)1
(а + b)(c + d)(a + c)(b + d)
С поправкой Йетса (Yates Е):
, (| а - а„ | - 0.5)2 (| b - Ьо | - 0.5)2 (| с - с01 - 0.5)2 (| </ - d01 - 0.5)2
х ---------------1-------------'------------1------1’
л() Ь[} с'() п()
откуда следует
\2
4 (а + b)(c + d)(a + c)(b + d)
При альтернативной гипотезе Н{ критическая точка %2р = Х2(а; 1),
при других альтернативах х2р = Х2(а/2; 1). Критические точки х2р при
некоторых значениях а приведены в таблице ([ 10], стр. 321):
Таблица 4.2
Вероятность ошибки а 0,05 0,01 0,001
Двусторонний критерий (Hit:pl - р2> //< .pt *р2) 3,841 6,635 10,827
Односторонний критерий (//„: pt - р2, НЛ \рх< р2 или рх > р2) 2,705 5,412 9,550
Гипотеза Н{} принимается, если х2н < ХкР, в противном случае она
отклоняется и принимается альтернативная гипотеза.
Задача 4.1. Данные о проверке качества некоторого товара, вы-
пускаемого предприятиями I и II, приведены в таблице:
Таблица 4.3
Отличное Хорошее
Предприятие 1 25 64
Предприятие II 11 48
Можно ли на уровне а =0,1 утверждать, что качество товара не
зависит от наименования предприятия изготовителя? Вычисления
провести:
• без поправки Йетса;
• с поправкой Йетса.
Технология решения. Основная гипотеза Н(}: р, = р2, альтернатив-
ная гипотеза Н, : р, * р2. Вводим данные в диапазон А1:В2, в ячейке
С1 записываем формулу =СУММ(А1:В1) и копируем ее в ячейку С2.
144 Непараметрические критерии для таблиц сопряженности
Аналогично, в ячейку АЗ вводим формулу =СУММ(А1:А2) и копи-
руем ее в ячейки ВЗ:СЗ.
1. В ячейку Е1 вводим формулу =(А1-$С1*А$3/$С$3)А2/
($С1*А$3/$С$3) и копируем ее в остальные ячейки диапазона
E1:F2. В ячейке G3 по формуле =СУММ(Е1:Е2) находим на-
блюдаемое значение критерия (рис. 4.1).
Рис. 4.1
Выделяем свободную ячейку, вызываем функцию ХИ2ОБР и
вводим данные:
Рис. 4.2
Так как Хв ~ 1,72 < 2,71 » х£р, то гипотеза Н() принимается.
2. Ввод в ячейку Е1 формулы =(ABS(A1-$C1*A$3/$C$3)-O,5)A2/
($С1*А$3/$С$3) и повторение остальных действий пункта 1
дают Хв « 1,245 (рис. 4.3), гипотеза Н{] принимается.
А В _!_ с I I D Е I F ] Ь 1
_1_ 25 641 89 0.375553, 0,120713
2 11 48, 59 0,566511
1 3 36 112 148 ’ 0,182093] 1,244871
Рис. 4.3
Ответ: да.
Четырехклеточный х2 критерий
145
Данный критерий реализован в макросе Test_2_2, автоматически
возвращающем полученные результаты, и сообщение о том, какая ги-
потеза принимается, достаточно ввести таблицу частот 2x2 в диапа-
зон А1 :В2, и запустить макрос на исполнение.
Например, пусть введены данные таблицы 4.3. Запуск макроса на
исполнение открывает диалоговое окно:
Рис. 4.4
Команда Нет открывает окно ввода номера критерия:
Рис. 4.5
Команда ОК открывает окно ввода уровня значимости критерия:
Рис. 4.6
146
Непараметрические критерии для таблиц сопряженности
Команда ОК возвращает полученные ранее результаты пункта 1:
Рис. 4.7
Команда ОК открывает диалоговое окно:
Рис. 4.8
По команде Да данные рабочего листа будут иметь вид, как на
рис. 4.1, по команде Нет останется только исходная таблица. Если
в диалоговом окне, показанном на рис. 4.4, воспользоваться кнопкой
Да, то после заполнения диалоговых окон (рис. 4.5, 4.6) получим ре-
зультаты пункта 2:
Рис. 4.9
Задача 4.2. Имеются следующие данные о результатах приви-
вок против гриппа:
Таблица 4.4
Заболели Не заболели
После прививки 12 150
Без прививки 34 216
Четырехклеточный х2 критерий
147
Проверить на уровне а = 0,03 гипотезу о неэффективности при-
вивок (Но : рх = р2), принимая в качестве альтернативной гипотезы
Технология решения. Применим макрос Test_2_2. Ввод данных и
запуск макроса на исполнение открывают диалоговое окно (рис. 4.4),
где выбираем Нет, что открывает окно:
Рис. 4.10
Команда ОК открывает окно ввода уровня значимости критерия:
Рис. 4.11
Команда ОК возвращает результаты:
Рис. 4.12
Команда ОК открывает диалоговое окно (рис. 4.8), выбор Да
оставляет вычисления на рабочем листе:
148
Непараметрические критерии для таблиц сопряженности
1 1 A I В C D E F | G
1 12 150 162 2,048731 0.257491
2 34 216 _250 1.327578 0,166854
Рис. 4.13
Ответ: принимается гипотеза р, <р2.
Код макроса
Sub Test_2_2()
Dim t As Single, tip As Integer, alfa As Single, w As Single, _
h As Single, s As String, k As Integer, Str As String
[cl].Formula = "=Sum(Al:B1)": [c2].Formula = "=Sum(A2:B2)"
[аЗ].Formula = "=Sum(Al:A2)": [ВЗ].Formula = "=Sum(Bl:B2)"
[C3].Formula = "=Sum(Cl:C2)"
Result = MsgBox("Учитывать поправку Йетса?", 4 + 32, "Test_2_2")
Select Case Result
Case 6
[el]. Formula = "= (ABS (A1-CPA3/C3)-0.5) A2/(C1*A3/C3) "
[e2].Formula = "= (ABS (A2-C2*A3/C3)-0.5) A2/(C2*A3/C3) "
[fl].Formula = "= (ABS (B1-CPB3/C3) -0.5) л2/(CPB3/C3) "
[f2J.Formula = "=(ABS(B2-C2*B3/C3)-0.5)A2/(C2*B3/C3)"
Case Else
[el].Formula = "= (A1-CPA3/C3) А2/(CPA3/C3) "
[e2].Formula = "=(A2-C2*A3/C3)A2/(C2*A3/C3)"
[fl].Formula = "=(B1-CPB3/C3)A2/(CPB3/C3)"
(f2).Formula = "= (B2-C2*B3/C3)A2/(C2*B3/C3)"
End Select
[g3].Formula = "=Sum(El:F2)t = [g3]: t = FormatNumber(t, 3)
[h4]. Formula = "=APC2-A2*C1"
If [h4].Value >= 0 Then
k = 1
tip = Application.InputBox("Введите номер критерия:" & Chr(13) _
& "1. Двусторонний; 2. Односторонний (pl>p2). ", Туре:=1)
Else
к = 2
tip = Application.InputBox("Введите номер критерия:" & Chr(13) _
& "1. Двусторонний; 2. Односторонний (р1<р2). ", Туре:=1)
End If
alfa = Application.InputBox("Введите уровень значимости критерия: ",
Туре:=1)
w = Application.WorksheetFunction.Chilnv(alfa, 1)
h = Application.WorksheetFunction.Chilnv(alfa * 2, 1)
w = FormatNumber(w, 3): h = FormatNumber(h, 3)
Select Case tip
Case 1
If t < w Then
Str = "Ho."
Else
Общий случай %9 критерия
149
Str = "Hl."
End If
MsgBox ("Значение статистики: X2 = " & t & ". Критерий принятия Но: Х2 < " & w
& ". Принять: " & Str)
Case Else
If t < h Then
Else
If h <= t < w Then
If k = 1 Then
Str = "pl>=p2."
Else
Str = "pl<=p2."
End If
Else
If k = 1 Then
Str = "pl>p2."
Else
Str = "pl<p2."
End If
End If
MsgBox ("Значение статистики: X2 = " & t & ". Критерий принятия Но: X2 < " & h
& ". Принять: " & Str)
End If
End Select
Result = MsgBox("Оставить вычисления?", 4 + 32, "Test_2_2")
Select Case Result
Case 6
Exit Sub
Case Else
Range("Cl:H4").ClearContents: Range("A3:B3").ClearContents
End Select
End Sub
§2. Общий случай %2 критерия
Пусть имеются т выборок х, i = l,m, j = l,nj значений признаков Xv
Х.р .... ХП1, соответственно, принимающих k значений. Тогда таблица
частот выборочных данных называется mxk таблицей сопряженности:
Таблица 4.5
1 2 k
X, я.. nu Я|
X, ял Пп П1
X. яя1
я; я] Я* п
150 Непараметрические критерии для таблиц сопряженности
В этом случае гипотеза однородности признаков Xt,X2,...»Хт (при-
надлежности одной генеральной совокупности) записывается в ви-
де Но:
Р\\ ~ Р'21 ~ ~ PmV
Р12 “ Р-22 ~ ” Рт’2*
P\k P'2k Put#
= P(Xt= j). Альтернативная гипотеза Н,: хотя бы одна цепочка ра-
венств не выполняется.
Статистика критерия:
.\2
n>T1j
п J
ninj
При справедливости гипотезы Но она имеет (приближенно) рас-
пределение х2 с числом степеней свободы df= (т - i)(k - 1). Поэтому,
если )(£ < ХкР(а» 4/)>т0 Яо принимается, в противном случае Но отвер-
гается и принимается гипотеза Hv
Данный критерий реализован в макросе Test_m_k, применяемом
стандартным образом.
Задача 4.3. В таблице 4.6 приведены итоги в медальном зачете
XXX летних олимпийских игр в Лондоне по странам, получившим
наибольшее число медалей.
Таблица 4.6
Золото Серебро Бронза
США 46 29 29
Китай 38 27 22
Великобритания 29 17 19
Россия 24 25 33
Проверить на уровне значимости а = 0,05 гипотезу однородности
стран по числу завоеванных медалей.
Технология решения. Вызываем макрос Test_m_k, вводим таб-
личные данные и выделяем:
1 А | В .L С
46 29 29
2 38 27 22
_3_ 29 17 19
4 24 25 33.
Рис. 4.14
Общий случай х9 критерия 151
Запуск макроса на исполнение открывает окно ввода уровня зна-
чимости критерия:
Рис. 4.15
Команда ОК возвращает результаты:
Рис. 4.16
Ответ: принимается.
Задача 4.4. По данным таблицы 4.7 проверить гипотезу одно-
родности признаков Лр Л2, Л3, принимая а = 0,02.
Таблица 4.7
Л, в2 вЛ В,
Л, 13 13 12 22
д2 4 24 28 34
А, 3 8 15 24
Технология решения. Входные данные:
Рис. 4.17
Запуск макроса Test_m_k на исполнение открывает окно ввода
уровня значимости критерия:
152
Непараметрические критерии для таблиц сопряженности
Рис. 4.18
Команда ОК возвращает:
Рис. 4.19
Ответ: Но отклоняется.
Код макроса
Sub Test_m_k()
Dim m As Integer, k As Integer, n As Integer, s As Single, _
alfa As Single, w As Single, t As String
k = Selection.Columns.Count
m = Selection.Rows.Count
For i = 1 To m
Cellsd, k + 1).Value = Application.WorksheetFunction. _
Sum(Range(Cells(i, 1), Cellsd, k)))
Next
For i = 1 To k + 1
Cells(m + 1, i).Value = Application.WorksheetFunction. _
Sum(Range(Cells(1, i), Cells(m, i)))
Next
n = Cells(m +1, k + 1) .Value: s = 0
For i = 1 To m
For j = 1 To k
s = s + (Cellsd, j).Value - Cellsd, k + 1) .Value * Cells(m +1, j).
Value / n) A 2 / (Cellsd, k + 1).Value * Cells(m + 1, j).Value / n)
Next
Next
s = FormatNumber(s, 3)
alfa = Application.InputBox("Введите уровень значимости критерия: ", _
Туре:=1)
w = Application.WorksheetFunction.Chilnv(alfa, (m - 1) * (k - 1))
Точный критерий Фишера
153
w = FormatNumber(w, 3)
If s < w Then
t = " Ho."
Else
t = " Hl."
End If
MsgBox ("Значение статистики: X2 = " & s & ". Критерий принятия Но: X2 < " & w
& ". Принять: " & t)
Range(Cells(m +1, 1), Cells(m + 1, k + 1)).ClearContents
Range (Cells (1, k + 1), Cells(m, k + 1)).ClearContents
End Sub
§3. Точный критерий Фишера
При фиксированных суммах по строкам и столбцам (маргинальных
частотах) таблицы 4.1, как показано в ([11], стр. 739), частоты имеют
гипергеометрическое распределение. Учитывая это, вычисляется ве-
роятность
(a + byXc + d)\(a+c)\(b + d)\^1
(a + b + c + d)\ “J* (a-i)\(b + i)\(c + i)\(d -i)\
Если P> а, то гипотеза однородности (независимости) принима-
ется, в противном случае она отклоняется и принимается или Н,” -
односторонний критерий. Рассмотрение аналогичной суммы
в которой учитываются только те слагаемые, которые меньше зна-
чения, получаемого при i = 0, приводит к двустороннему критерию.
Если Р + Р' > а, то гипотеза однородности принимается, в противном
случае она отклоняется и принимается Нг
Задача 4.5. По данным таблицы 4.8 проверить односторонним
критерием Фишера гипотезу однородности, принимая a = 0,05.
Таблица 4.8
в. в2
4 15
а2 11 19
154
Непараметрические критерии для таблиц сопряженности
Технология решения.
1. Вводим табличные данные в диапазон А1:В2 и находим суммы
по строкам и столбцам, и сумму данных, как при решении за-
дачи 4.1.
2. В диапазоне А6:А 10 задаем последовательность значений i: 0,1,
2,3,4.
3. В ячейки диапазона В5:Е5, соответственно, вводим значения 4,
15, 11, 19.
4. В ячейки В6:Е6, соответственно, записываем формулы:
=ФАКТР(В$5-А6) =ФАКТР(С$5+А6)
=ФАКТР(В$5+А6) =ФАКТР(Е$5-А6)
5. В ячейку F6 вводим формулу =1/(B6*C6*D6*E6).
6. Выделяем диапазон B6:F6 и копируем его в остальные строки
диапазона B6:F10.
7. В ячейку F11 вводим формулу =CYMM(F6:F10), в ячейку
F12 - формулу
=ФАКТР(С1)*ФАКТР(С2)*ФАКТР(АЗ)*ФАКТР(ВЗ)/
ФАКТР(СЗ)
8. Окончательный результат получаем в ячейке G13 по формуле
=F11*F12 (рис. 4.20).
£ 4 15 11 19
6 0 24 1,307678 + 12 39916800 1,216458 + 17 6.562048-39
7 1 6 2,092288+13 4,798+08 6,402378 + 15 2,597478-39
8 2 2 3,556878 + 14 6,238+09 3,556878 + 14 6,346778-40
9 3 1 6,402378 + 15 8,728 + 10 2,092288 + 13 8,56318-41
10 4 1 1,216458 + 17 1,318 + 12 1,307678 + 12 4,807358-42
И 9,884638-39
12 2,047928+37
| 0 202431
Рис. 4.20
Так как Р = 0,20243 > 0,05 = а, то гипотеза однородности при-
нимается.
Ответ: Но принимается.
Замечание. Вычисления можно значительно упростить, если
воспользоваться встроенной функцией гипергеометрического рас-
пределения ГИПЕРГЕОМЕТ. Достаточно после выполнения пер-
вых двух пунктов ввести в ячейку В6 формулу =ГИПЕРГЕОМЕТ
(А6;$А$3;$С$ 1 ;$С$3) и скопировать ее в ячейки В7:В10. Тогда, вы-
Точный критерий Фишера
155
деляя диапазон В6:В 10, и применяя кнопку Е, получаем искомое
значение (рис. 4.21).
6 о
7 1
8 2
9 3
ТсЦ 4
11
9,8451 Е-05
0,001753659
0,012997706
0,053194314
0,134385636
0,202429766'
Рис. 4.21
Точный критерий Фишера реализован в макросе Test_F, применя-
емом стандартным образом. Например, для рассмотренной задачи он
возвращает:
Рис. 4.22
Код макроса
Sub Test_F()
Dim р As Single, w As Single, m As Integer, z As Single, t As Single
[cl] = [al] + [bl]: [c2] = [a2] + [b2]
[a3] = [al] + [a2]: [ЬЗ] = [bl] + [Ь2]: [сЗ] = [аЗ] + [ЬЗ]
m = Application.WorksheetFunction.Min(Range("A1:B2"))
p=0:w=0:t=0
For i = 1 To 2
For j = 1 To 2
If Cellsd, j).Value = m Then
For k = 0 To m
p = p + Application.WorksheetFunction.HypGeomDist
(k, Cellsd, 3).Value, Cellsd, j).Value, Cellsd, 3) .Value)
Next
For k = m To Application.WorksheetFunction.Min
(Cellsd, 3) .Value, Cellsd, j).Value)
w = w + Application.WorksheetFunction.HypGeomDist
(k, Cellsd, 3) .Value, Cellsd, j).Value, Cellsd, 3) .Value)
Next
z = Application.WorksheetFunction.HypGeomDist
(m, Cellsd, 3) .Value, Cellsd, j).Value, Cellsd, 3) .Value)
156
Непараметрические критерии для таблиц сопряженности
If р < w Then
For k = m То Application.WorksheetFunction.Min
(Cellsd, 3) .Value, Cells(3, j) .Value)
If Application.WorksheetFunction.HypGeomDist
(k, Cellsd, 3) .Value, Cells(3, j).Value, Cells(3, 3) .Value) < z Then
t = t + Application.WorksheetFunction.HypGeomDist
(k, Cellsd, 3) .Value, Cells(3, j).Value, Cells(3, 3).Value)
End If
Next
Else
For k = 0 To m
If Application.WorksheetFunction.HypGeomDist
(k, Cellsd, 3) .Value, Cells(3, j).Value, Cells(3, 3) .Value) < z Then
t = t + Application.WorksheetFunction.HypGeomDist
(k, Cellsd, 3) .Value, Cells(3, j).Value, Cells(3, 3) .Value)
End If
Next
End If
Exit For
Exit For
End If
Next
Next
p = Application.WorksheetFunction.Min(p, w)
w = p + t: p = FormatNumber(p, 3): w = FormatNumber(w, 3)
MsgBox ("Условия принятия Но:" & Chr(13) & "1. alpfa < " & p _
& " (односторонний тест) " & Chr (13) & "2. alpfa < " & w _
& " (двусторонний тест)")
Range ("A3:C3").ClearContents: Range("Cl:C2").ClearContents
End Sub
§4. G-критерий Вулфа
Данный критерий [10, 12] подобен критерию х2, применяемому для
таблиц сопряженности 2x2, статистика в нем имеет вид:
G = 2^ наблюдаемое • (1п(паблюдаемое) -1п(ожидаемое)).
Перед вычислением значения критерия таблицу сопряженности
необходимо скорректировать по Йетсу: элементы, меньшие ожидае-
мых, увеличить на 0,5, а большие ожидаемых, уменьшить на 0,5. При
G < Х2к|»(а» О гипотеза однородности принимается, в противном слу-
чае - отвергается.
Задача 4.6. По данным задачи 4.5 проверить G-критерием Вул-
фа гипотезу однородности.
Технология решения. В диапазон А1:В2 вводим табличные дан-
ные и находим суммы по строкам и столбцам, и сумму данных. Вы-
G-критерий Вулфа
157
числяем ожидаемые значения, для этого в ячейку А5 вводим формулу
=$С1*А$3/$С$3 и копируем ее в остальные ячейки диапазона А5:В6
(рис. 4.23).
Из полученных данных следует, что скорректированная таблица
должна иметь вид:
А В С _1
1 4.5 14,5 19
2 10.5 19,5 30
3_ 15 34 49 ,
Рис. 4.24
Для вычисления значений критерия в ячейку Е1 вводим формулу
=A1*(LN(A1)-LN($C1*A$3/$C$3)) и копируем ее в остальные ячей-
ки диапазона El :F2. В ячейке G3 формулой =СУММ(Е1 :F2) находим
сумму полученных значений. Остается умножить ее на 2, что выпол-
няем в ячейке Н4 формулой =G3*2, и получаем GH = 0,716 (рис. 4.25).
1
2’
з“
4
А I в с D Е 1 F | I G |. Н |
4.5 14,5 19 И.15466 1.379957
10,5 19.5 30 1,406454 -1.2738
15 234 _49 0.357948
| 0,7158951
Рис. 4.25
Так как ХкР(^,05; 1) = 3,85 > 0,716 = Gfl, то гипотеза однородности
принимается.
Ответ: Но принимается.
Макрос Test—G проводит все вычисления автоматически. Напри-
мер, для данных последней задачи, после введения уровня значимо-
сти, и подтверждения командой ОК, он возвращает:
158
Непараметрические критерии для таблиц сопряженности
Рис. 4.26
Код макроса
Sub Test_G()
Dim s() As Integer, G As Single, alfa As Single, w As Single
ReDim s (1 To 2, 1 To 2)
For i = 1 To 2
For j = 1 To 2
s(i, j) = Cells(i, j).Value
Next
Next
[Cl].Formula = "=Sum(Al:B1)": [c2].Formula = "=Sum(A2:B2)"
[A3].Formula = "=Sum(Al:A2)[b3].Formula = "=Sum(Bl:B2)"
[СЗ].Formula = "=Sum(Cl:C2)"
[A5].Formula = "=C1*A3/C3": [В5].Formula = "=C1*B3/C3"
[A6].Formula = "=C2*A3/C3": [В6].Formula = "=C2*B3/C3"
For i = 1 To 2
For j = 1 To 2
If Cells (4 + i, j) .Value > Cellsd, j) .Value Then
Cellsd, j).Value = s(i, j) + 0.5
Else
Cellsd, j) .Value = s(i, j) - 0.5
End If
Next
Next
[El] = [A1J * (Application.WorksheetFunction.Ln([Al]) Application.
WorksheetFunction.Ln([Cl] * [АЗ] / [C3]))
[Fl] = [Bl] * (Application.WorksheetFunction.Ln([Bl]) Application.
WorksheetFunction.Ln([Cl] * [ЬЗ] / [C3]))
[E2] = [A2] * (Application.WorksheetFunction.Ln([A2]) Application.
WorksheetFunction.Ln([c2] * [АЗ] / [C3]))
[F2] = [B2] * (Application.WorksheetFunction.Ln([B2]) Application.
WorksheetFunction.Ln([c2] * [ЬЗ] / [C3]))
[G3].Formula = "=Sum(El:F2)"
[Н4].Formula = "=2*G3": G = [H4]
G = FormatNumber(G, 3)
alfa = Application.InputBox("Введите уровень значимости критерия: ", _
Туре:=1)
w = Application.WorksheetFunction.Chilnv(alfa, 1)
w = FormatNumber(w, 3)
If G < w Then
MsgBox ("Значение статистики: G= " & G & ". Критерий принятия Ho: G < " & w
& ". Принять: Но.")
Критерий Макнимары
159
Else
MsgBox ("Значение статистики: G= " & G & ". Критерий принятия Но: G < " & w _
& ". Принять: Н1. ")
End If
Range("АЗ:В6").ClearContents: Range("Cl:H4").ClearContents
For i = 1 To 2
For j = 1 To 2
Cells (i, j).Value = s (i, j)
Next
Next
End Sub
§5. Критерий Макнимары
Критерий Макнимары (McNemar) [16] проверяет гипотезу однород-
ности (независимости) совокупностей X, Y по данным, так называе-
мой, перекрестной таблицы:
Таблица 4.9
X\Y 1 0 z
1 a b
0 с d r,
Z si n
В ней а, Ь, с, d - число пар (1, 1), (1, 0), (0, 1), (0, 0) в двумерной
выборке (xf,z/,), i = 1,п, соответственно, Ь*с.
Гипотеза однородности Но:
Р(Х = 1, У=0) = Р(Х=0, У = 1).
Альтернативная гипотеза с двусторонней критической областью
Я,:
Р(Х= 1, У=0)*Р(Х = 0, У- 1).
Альтернативные гипотезы с односторонними критическими об-
ластями
Н;.Р(Х= 1, У=0)>Р(Х = 0, У = 1),
Н’*:Р(Х = 1, У=0)<Р(Х=0, У = 1).
Статистика критерия:
Т = min(fc, с).
Если гипотеза Но верна, то статистики Тподчиняется биномиаль-
ному закону распределения с параметрами m = b + с,р = 0,5. Поэтому
гипотеза Но принимается при альтернативной гипотезе Нх на уровне
значимости а, если
160
Непараметрические критерии для таблиц сопряженности
F(T) = P(^T) = £c' - >$,
1=0 \ J *
в противном случае принимается Hv Гипотеза Но отклоняется и при-
нимается Hf, если
F(c) = />(/< с) = £cj,
1=0
ГипотезаНо отклоняется и принимается Н*\ если
h (1Y"
F(b) = P(f<b) = £C' - <а.
1=0 )
Задача 4.7. В таблице 4.10 приведены данные о количестве по-
тенциальных покупателей некоторого товара до и после просмотра
рекламного ролика о товаре («+» - желают,«-» - не желают купить).
Оказала ли реклама эффективное воздействие на покупателей? При-
нять а = 0,05.
Таблица 4.10
До\ После + -
+ 55 30
- 50 65
Технология решения. Проверяем гипотезу Но при альтернативной
гипотезе Н*\ Для этого в диапазон А1 :В2 вводим табличные данные,
вызываем диалоговое окно функции БИНОМРАСП и задаем в нем:
Рис. 4.27
Критерий Макнимары
161
Полученное значение 0,016 < а, принимаем гипотезу Н“.
Ответ: да.
Критерий реализован в макросе Test_M, применяемом стандарт-
ным образом. Например, запуск макроса на исполнение, когда вве-
дены табличные данные задачи 4.7, открывает окно выбора альтерна-
тивной гипотезы:
Рис. 4.28
Команда ОК вызывает окно ввода уровня значимости критерия:
Рис. 4.29
Команда ОК возвращает полученные ранее результаты:
Рис. 4.30
"| 52 Непараметрические критерии для таблиц сопряженности
Код макроса
Sub Test_M()
Dim t As Integer, F As Single, alfa As Single, tip As Integer,
v As Single, s As String
tip = Application.InputBox("Введите номер альтернативной гипотезы: " & Chr(13) _
& " 1. Р(X=l, Y=0)<>Р(Х=0,Y=l);" & Chr (13)
& " 2. Р(X=l,Y=0)>Р(Х=0,Y=l);" & Chr(13)
& " 3. Р(X=l,Y=0)<Р (Х=0,Y=l) . ", Туре: = 1)
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
Select Case tip
Case Is = 1
t = Application.WorksheetFunction.Min([A2], (B1J)
F = Application.WorksheetFunction.BinomDist(t, [A2] + (Bl), 1/2, 1)
If F <= alfa / 2 Then
v = alfa / 2: s = " Hl."
Else
v = alfa / 2: s = " Ho."
End If
Case Is = 2
F = Application.WorksheetFunction.BinomDist([A2], (A2) + (Bl), 1/2, 1)
If F <= alfa Then
v = alfa: s = " Hl."
Else
v = alfa: s = " Ho."
End If
Case Is = 3
F = Application.WorksheetFunction.BinomDist((Bl), (A2) + (Bl), 1/2, 1)
If w <= alfa Then
v = alfa: s = " Hl."
Else
v = alfa: s = " Ho."
End If
End Select
F = FormatNumber(F, 3)
MsgBox ("Выполняется: F = " & F & ". Критерий принятия Ho: F > " & v
& ". Принять:" & s)
End Sub
В модифицированном критерии Макнимары ([12], стр. 643) ста-
тистика:
С' 1)2, Л + ссЗО,
Т=[ 0+с+1
^~С)2, Ь + с>30.
я + с + 1
Если Тв< ХкР(а» 1), т0 гипотеза Но принимается, в противном слу-
чае - отвергается и принимается Нг
Биноминальные критерии
163
Такой подход реализован в макросе Test_M_M, применяемом стан-
дартным образом. Например, для данных задачи 4.7 он возвращает:
Рис. 4.31
Код макроса
Sub Test_M_M()
Dim t As Single, w As Single, alfa As Single
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
w = Application.WorksheetFunction.Chilnv(alfa, 1)
w = FormatNumber(w, 3)
Select Case [Ы] + [a2]
Case Is < 30
t = (Abs([bl] - [a2]) - 1) A 2 / ((bl) + (a2) + 1)
Linel:
t = FormatNumber(t, 3)
If t < w Then
MsgBox ("Значение статистики: T = " & t & ". Критерий принятия Но: Т < " _
& w & ". Принять: Но.")
Else
MsgBox ("Значение статистики: Т = " & t & ". Критерий принятия Но: Т < " _
& w & ". Принять: Н1.")
End If
Case Else
t = ((bl) - [a2]) A 2 / ((bl) + (a2) + 1)
GoTo Linel
End Select
End Sub
§6. Биномиальные критерии
1. Биномиальный критерий для таблиц сопряженности. Пусть спра-
ведлива гипотеза Но для признаков X и Y, заданных таблицей 4.1. Тог-
да, если фиксированы отношения — и —, то значение а таблицы 4.1
п п
можно рассматривать как число успехов - значение 1 принято объ-
ектом из X, неуспех - значение 1 принято объектом из Y, в испытани-
ях по схеме Бернулли, когда число испытаний nv вероятность успеха
164
Непараметрические критерии для таблиц сопряженности
р = —. Аналогичное верно относительно других частот таблицы 4.1,
п
причем из соотношения
(l _ 77 V
/?, п
следует
?? v _ д a a a f _ иЛ
fc _ пх -а _ п п _ п _ ni\ п ) _пх
пп п~п\ i-ZL 1—— 1-^- п
п п п
Поэтому проверка гипотезы Но сводится к проверке условия
< а < £2, где tx и t2 критические точки биномиального распределения
Вл , соответствующие заданному уровню значимости. Такой
I п )
подход реализован в макросе Test_В, применяемом стандартным об-
разом, при двусторонней альтернативной гипотезе Н,: р, #р2.
В частности, для данных задачи 4.1 он возвращает:
Рис. 4.32
По данным задачи 4.2 при двустороннем критерии и а = 0,15 мак-
рос Test B возвращает:
Рис. 4.33
Биноминальные критерии
165
Код макроса
Sub Test_B()
Dim р As Single, w As Single, n As Integer, k As Integer, _
m As Integer, Str As String
[cl] = [al] + [ВЦ: [c2] = [A2] + (b2)
[a3] = [al] + [A2]: [ЬЗ] = [Bl] + [Ь2]: [сЗ] = [аЗ] + [ЬЗ]
p = [cl] / [c3]: n = [a3]
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
For i = 0 То n
If alfa / 2 < Application.WorksheetFunction.BinomDist(i, n, p, 1) Then
k = i - 1
Exit For
End If
Next
w = 0
For i = 0 To n
w = w + Application.WorksheetFunction.BinomDist(n - i, n, p, 0)
If alfa / 2 < w Then
m = n - i + 1
Exit For
End If
Next
k = Application.WorksheetFunction.Min(k, m)
m = Application.WorksheetFunction.Max(k, m)
If k < [al] And [al] < m Then
Str = " Ho."
Else
Str = " Hl."
End If
MsgBox ("Выполняется: a=" & [al] & ". Критерий принятия Ho: "&k&"<a<"&m_
& ". Принять:" & Str)
Range("АЗ:C3").ClearContents: Range("Cl:C2").ClearContents
End Sub
2. Биномиальный критерий для перекрестных таблиц - альтер-
натива критерия Макнимары, учитывающая п.
Если справедлива гипотеза Но для признаков X и Y, представлен-
ных таблицей 4.9, то b = с, а значит г, = sp r2 = s2. Поэтому неважно ка-
кие суммы, по строкам или столбцам, фиксировать. Пусть они будут
Г1 Г2 о v
суммами по строкам, — и — - вероятности для значении признака X
п п
и, одновременно, для значений признака Y. Тогда значение s, можно
рассматривать как число успехов - значение 1 принято объектом из Y,
неуспех - значение 0 принято объектом из Y, в испытаниях по схеме
Бернулли, когда число испытании и, вероятность успеха р = —.
п
166
Непараметрические критерии для таблиц сопряженности
Таким образом, проверка гипотезы Но сводится к проверке усло-
вия < s, < t2, где и t2 критические точки биномиального распреде-
ления Вн т?,— .соответствующие заданному уровню значимости, что
\ п J
реализовано в макросе Test_S, применяемом стандартным образом,
при двусторонней альтернативной гипотезе Нх: рх * р2.
В частности, для данных задачи 4.7 он возвращает:
Рис. 4.34
Для данных, показанных на рис. 4.35 и уровня значимости а = 0,03,
он возвращает сообщение, показанное на рис. 4.36.
Рис. 4.35
30
65
Рис. 4.36
Код макроса
Sub Test_S()
Dim р As Single, w As Single, n As Integer, k As Integer,
m As Integer, Str As String
(cl) = [al] + [Ы]: [c2] = [a2] + [b2]
[a3] = [all + [a2]: [ЬЗ] = [bl] + [Ь2]: [сЗ] = [аЗ] + [ЬЗ]
p = [cl] / [c3]: n = [c3]
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
For i = 0 То п
Критерий Ле Роя
167
If alfa / 2 < Application.WorksheetFunction.BinomDist(i, n, p, 1) Then
k = i - 1
Exit For
End If
Next
w = 0
For i = 0 To n
w = w + Application.WorksheetFunction.BinomDist(n - i, n, p, 0)
If alfa / 2 < w Then
m = n - i + 1
Exit For
End If
Next
k = Application.WorksheetFunction.Min(k, m)
m = Application.WorksheetFunction.Max(k, m)
If k < [a3] And [a3] < m Then
Str = " Ho."
Else
Str = " Hl."
End If
MsgBox ("Значение статистики: s =" & [аЗ] & ". Критерий принятия Но: " & к
& " < s < " & m & ". Принять:" & Str)
Range ("АЗ:СЗ").ClearContents: Range("Cl:C2").ClearContents
End Sub
§7. Критерий Ле Роя
Критерий Ле Роя (Le Roy Н. L.) [12] проверяет гипотезу Но однород-
ности данных двух таблиц сопряженности 2x2:
я, bt а2 Ь2
с, </J |_с2 d2
Статистика критерия:
R = (n{ +и2) Q- + Ql-\ ,
Hl «2 J
*i с2 df
ax + a2 bx + b2 c, + c2 dx + d2
n al % dl
+ a2 + b2 Cj + c2 dx + d2
nv n2 - объемы выборок. ГипотезаHQ принимается на уровне значимо-
сти а, если R < %2р = %2(а; 3).
Задача 4.8. Принимая а = 0,05, проверить гипотезу однородно-
сти таблиц 4.2 и 4.7.
168
Непараметрические критерии для таблиц сопряженности
Технология решения. Вводим табличные данные и в ячейках ВЗ,
D3 функцией СУММ находим объемы выборок:
А В С В
1 25 64 4* ’5|
2 11 48 11 191
з 148 49
Рис. 4.37
В ячейке Е1 записываем формулу =А1+С1 и копируем ее в ос-
тальные ячейки E1:F2. В ячейке G1 записываем формулу =А1Л2/Е1
и копируем ее в остальные ячейки G1:H2. В ячейке НЗ формулой
=CYMM(G1:H2) находим значение Qv Аналогично, в ячейке И запи-
сываем формулу =С1А2/Е1 и копируем ее в остальные ячейки I1J2.
В ячейкеJ3 формулой =СУ MM(11 :J2) находим значение Q2. Значение
R вычисляем в ячейке А4 по формуле: =(B3+D3)*(H3/B3+J3/D3-1).
Таблица вычислений показана на рис. 4.38.
Е_|____F_ | G | Н | 1_ £ J 1
29 79 21,552 51.848 0.5517 2“,8481
22 67 5,5 34,388 5.5 5,3881
113.29] 14,288
Рис. 4.38
Выделяем свободную ячейку, вызываем функцию ХИ2ОБР и вво-
дим данные:
Рис. 4.39
Так как R = 11,24 > 7,81 = %2(0,05; 3), то гипотеза однородности
отклоняется.
Критерий Ле Роя 169
Ответ: гипотеза однородности отклоняется.
Критерий реализован в макросе Test_L_R, запускаемом на ис-
полнение, когда в диапазоны А1:В2 и C1:D2 введены данные таблиц.
Например, для данных задачи 4.8 он возвращает полученные ранее
результаты:
Рис. 4.40
Код макроса
Sub Test_L_R()
Dim n() As Integer, m() As Integer, R As Single,
ql As Single, q2 As Single, w As Single, alfa As Single
ReDim n(l To 2, 1 To 2)
ReDim m(l To 2, 1 To 2)
For i = 1 To 2
For j = 1 To 2
n(i, j) = Cellsd, j).Value: m(i, j) = Cellsd, 2 + j) .Value
Next
Next
ql = 0: q2 = 0
For i = 1 To 2
For j = 1 To 2
ql = ql + nd, j) A 2 / (nd, j) + m(i, j))
q2 = q2 + m(i, j) A 2 / (n(i, j) + m(i, j))
Next
Next
ql = ql / Application.WorksheetFunction.Sum(n)
q2 = q2 / Application.WorksheetFunction.Sum(m)
R = Application.WorksheetFunction.Sum(n, m)
R = R * (ql + q2 - 1)
R = FormatNumber(R, 2)
alfa = Application.InputBox("Введите уровень значимости критерия:", Туре:=1)
w = Application.WorksheetFunction.Chilnv(alfa, 3)
w = FormatNumber(w, 2)
If R < w Then
MsgBox ("Значение статистики: R = " & R & ". Критерий принятия Ho: R < " & w
& ". Принять: Но.")
Else
MsgBox ("Значение статистики: R = " & R & ". Критерий принятия Ho: R < " & w
& ". Принять: Hl.")
End If
End Sub
Литература
1. Аптон Г. Анализ таблиц сопряженности. - М.: Финансы и стати-
стика, 1982. - 143 с.
2. Большее Л. Н., Смирнов Н. В. Таблицы математической статисти-
ки. - М.: Наука, 1983. - 416 с.
3. Ван дер Варден Б. Л. Математическая статистика. - М.: ИЛ,
1960.- 472 с.
4. Вуколов Э. А. Основы статистического анализа: практикум по ста-
тистическим методам и исследованию операций с использовани-
ем пакетов STATISTICA и EXCEL. - М.: ФОРУМ: ИНФРА-М,
2004.-464 с.
5. Гарнаев А. Ю. MS Excel: разработка приложений. - СПб.: БХВ-
Петербург, 2003. - 786 с.
6. Гмурман В. Е. Теория вероятностей и математическая статисти-
ка. - М.: Высшая школа, 2003. - 479 с.
7. Гмурман В. Е. Руководство к решению задач по теории вероятно-
стей и математической статистике. - М.: Высшая школа, 1998. -
400 с.
8. Дубнов П. Ю. Обработка статистической информации с помощью
SPSS. - М.: НТ Пресс, 2004. - 221 с.
9. Ефимов А. В., Поспелов А. С. и др. Сборник задач по математике
для втузов. В 4 частях. Ч. 4. - М.: Издательство физико-матема-
тической литературы, 2004. - 432 с.
10. Закс Л. Статистическое оценивание. - М.: Статистика, 1976. -
320 с.
И. Кендалл М., Стьюарт А. Статистические выводы и связи. - М.:
Наука, 1973. - 899 с.
12. Кобзарь А. И. Прикладная математическая статистика. Для инже-
неров и научных работников. - М.: ФИЗМАТЛИТ, 2006. - 816 с.
13. Леман Э. Проверка статистических гипотез. - М.: Наука, 1979. -
408 с.
14. Ликеш И., Ляга Й. Основные таблицы математической статисти-
ки. - М.: Финансы и статистика, 1985. - 356 с.
15. Митина О. В. Математические методы в психологии: практи-
кум. - М.: Аспект Пресс, 2008. - 233 с.
16. Рунион Р. Справочник по непараметрической статистике: совре-
менный подход. - М.: Финансы и статистика, 1982. - 198 с.
17. Сдвижков О. А. Математика в Excel 2003. - М.: СОЛОН-Пресс,
2005.- 192 с.
Литература 171
18. Сдвижков О. А. Дискретная математика и математические методы
экономики с применением VBA Excel. - М.: ДМК Пресс, 2012. -
212 с.
19. Фадеева Л. Н., Жуков Ю. В., Лебедев А. В. Математика для эконо-
мистов: теория вероятностей и математическая статистика. Зада-
чи и упражнения. - М.: Эксмо, 2006. - 336 с.
20. Химмелъблау Д. Анализ процессов статистическими методами. -
М.: Мир, 1973. - 957 с.
21. Холлендер М., Вулф Д. Непараметрические методы статистики. -
М.: Финансы и статистика, 1983. - 513 с.
Книги издательства «ДМК Пресс» можно заказать в торгово-
издательском холдинге «АЛЬЯНС-КНИГА» наложенным плате-
жом, выслав открытку или письмо по почтовому адресу: 123242,
Москва, а/я 20 или по электронному адресу: orders@alians-kniga.ru.
При оформлении заказа следует указать адрес (полностью),
но которому должны быть высланы книги; фамилию, имя и от-
чество получателя. Желательно также указать свой телефон и
электронный адрес.
Эти книги вы можете заказать и в интернет-магазине: www.alians-
kniga.ru.
Оптовые закупки: тел. (499) 725-54-09, 725-50-27; электронный
адрес books@alians-kniga.ru.
Сдвижков Олег Александрович
Непараметрическая статистика в MS Excel и VBA
Главный редактор Мовчан Д. А.
dmkpress@gmail.com
Верстка Чаянова А. А.
Дизайн обложки Мовчан А. Г.
Подписано в печать 05.11.2013. Формат 60x90 1/16 .
Гарнитура «Петербург». Печать офсетная.
Усл. печ. л. 17,5. Тираж 200 экз.
Веб-сайт издательства: \у\у\у.дмк.рф