Непараметрическая статистика в MS Excel и VBA

Непараметрическая статистика 

в MS Excel и VBA

О. А. Сдвижков

Москва, 2017

УДК 744.4:004.92
ББК 30.11+32.973.26-018.2
 
C27

 

 
Сдвижков О. А.

C27 
Непараметрическая статистика в MS Excel и VBA. – М.: ДМК Пресс, 
2017. – 172 с.: ил.

 

ISBN  978-5-97060-512-7

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

Книга ориентирована на студентов вузов, изучающих статистиче
ские методы, но будет полезна и более широкому кругу пользователей 
MS Excel. 

 
УДК 744.4:004.92

 
ББК  30.11+32.973.26-018.2

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

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

 
 
©  Сдвижков О. А.

ISBN 978-5-97060-512-7 
    ©  Оформление, издание, ДМК Пресс

Содержание

Предисловие .........................................................................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)  построчный ввод данных, начиная с ячейки A1;
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

Теория вероятностей и математическая статистика в MS Excel
8

Теория вероятностей и математическая статистика поддерживаются 
в MS Excel встроенными функциями (таблица 1.1) и надстройкой 
(инструментами) Анализ данных (рис. 1.1, 1.2).

Таблица 1.1

FРАСП
МАКС
СРЗНАЧ

FРАСПОБР
МАКСА
СРЗНАЧА

ZТЕСТ
МЕДИАНА
СРОТКЛ

БЕТАОБР
МИН
СТАНДОТКЛОН

БЕТАРАСП
МИНА
СТАНДОТКЛОНА

БИНОМРАСП
МОДА
СТАНДОТКЛОНП

ВЕЙБУЛЛ
НАИБОЛЬШИЙ
СТАНДОТКЛОНПА

ВЕРОЯТНОСТЬ
НАИМЕНЬШИЙ
СТОШYX

ГАММАОБР
НАКЛОН
СТЬЮДРАСП

ГАММАРАСП
НОРМАЛИЗАЦИЯ
СТЬЮДРАСПОБР 

ГАММАНЛОГ
НОРМОБР
СЧЕТ

ГИПЕРГЕОМЕТ
НОРМРАСП
СЧЕТЕСЛИ

ДИСП
НОРМСТОБР
СЧЕТЗ

ДИСПА
НОРМСТРАСП
СЧИТАТЬПУСТОТЫ

ДИСПР
ОТРБИНОМРАСП
ТЕНДЕНЦИЯ

ДИСПРА
ОТРЕЗОК
ТТЕСТ

ДОВЕРИТ
ПЕРЕСТ
УРЕЗСРЕДНЕЕ

КВАДРОТКЛ
ПЕРСЕНТИЛЬ
ФИШЕР

КВАРТИЛЬ
ПИРСОН
ФИШЕРОБР

КВПИРСОН
ПРЕДСКАЗ
ФТЕСТ

КОВАР
ПРОЦЕНТРАНГ
ХИ2ОБР

КОРРЕЛ
ПУАССОН
ХИ2РАСП

КРИТБИНОМ
РАНГ
ХИ2ТЕСТ

ЛГРФПРИБЛ
РОСТ
ЧАСТОТА

ЛИНЕЙН
СКОС
ЭКСПРАСП

ЛОГНОРМОБР
СРГАРМ
ЭКСЦЕСС

ЛОГНОРМРАСП
СРГЕОМ

Меню встроенных функций (Мастер функций) открывается кнопкой f(x), по каждой из них в MS Excel можно получить подробную 
справку. Поэтому специально перечислять их назначения, как и значения параметров, нет необходимости, тем более что многие из них 
в дальнейшем будут применяться в конкретных задачах.
Пакет Анализ данных, если его нет в меню Сервис, загружается 
командами Сервис  Надстройки, после чего надо поставить галочку в поле Анализ данных и подтвердить ОК. 
Щелчок по опции Анализ данных меню Сервис открывает список 
инструментов:

Встроенные функции дискретных распределений

Продолжение списка инструментов на рис. 1.2.

Рис. 1.1 

Рис. 1.2 

§1. Встроенные функции дискретных 
распределений

Встроенные функции комбинаторики ПЕРЕСТ, ЧИСЛКОМБ и 
ФАКТР, причем первая входит в статистические функции, а вторая и 
третья – в математические.
ПЕРЕСТ(n; m) – возвращает число выборок из n элементов по m, 
каждая из которых отличается от остальных или составом элементов, 
или их порядком.
Пусть требуется подсчитать, сколько вариантов шахматных команд по три игрока (1-я, 2-я, 3-я доски) можно составить из четырех 
игроков. Открываем диалоговое окно ПЕРЕСТ, вводим данные и получаем результат: 

Теория вероятностей и математическая статистика в MS Excel
10

Команда ОК вставляет результат на рабочий лист.
Если m = n, то встроенная функция ПЕРЕСТ возвращает n!, так же 
как встроенная функция ФАКТР.
ЧИСЛКОМБ(n; m) – возвращает число сочетаний Cn
m, то есть число выборок из n элементов по m, каждая из которых отличается от 
остальных хотя бы одним элементом.
Пусть требуется подсчитать, сколько вариантов различных команд по пляжному волейболу (в команде 2 человека) можно составить из пяти человек. Тогда, используя встроенную функцию ЧИСЛКОМБ, получаем 10:

Рис. 1.3 

Рис. 1.4 

Встроенная функция  БИНОМРАСП(m, n, p, ЛОЖЬ) возвращает 
значение, получаемое по формуле Бернулли

Pn(m) = Cn
mp m(1 – p)n–m,

встроенная функция ПУАССОН(m, , ЛОЖЬ) – значение, получаемое по формуле Пуассона 

При замене значения ЛОЖЬ (0) на значение ИСТИНА (1) они 
возвращают значения интегральных функций распределений. 
Пусть дискретная случайная величина Х имеет, например, биномиальный закон распределения с параметрами n = 5, p = 0,75. Тогда 
ее возможные значения 0, 1, 2, 3, 4, 5. Вычислим вероятности, с которыми она их принимает, составим закон распределения, сделаем проверку и построим многоугольник распределения.
1. В диапазон A1:F1 вводим значения Х.
2. Выделяем ячейку А2, открываем диалоговое окно БИНОМРАСП и заполняем поля (рис. 1.5). 

Рис. 1.5 

3. Команда ОК вставляет в ячейку А2 формулу =БИНОМРАСП(A1;5;0,75;0).
4. Методом «протаскивания» маркера заполнения копируем ее в 
остальные ячейки диапазона A2:F2.
5. Выделяем диапазон A2:F2 и, щелчком по кнопке 
, убеждаемся в том, что сумма равна 1, то есть в диапазоне A1:F2 получен, 
действительно, закон распределения дискретной случайной 
величины: 

Встроенные функции дискретных распределений

Теория вероятностей и математическая статистика в MS Excel
12

6. В графическом редакторе Мастер диаграмм, выделяя диапазон 
A1:F2, строим точечную диаграмму:

Рис. 1.6 

Рис. 1.7 

Вычисление значения интегральной функции данного биномиального распределения при х = 5 дает F(5) = 1 (рис. 1.8), что показывает, 
что в MS Excel интегральная функция распределения F(x) = P(X  x), 
хотя принято F(x) = P(X < x) [6].

Рис. 1.8