Цель работы: Цель работы: принять или отвергнуть гипотезу о нормальном законе распределения выборок.
Выполнение данной лабораторной работы предполагает умение создания собственных пользовательских функций. Поэтому, для приобретения навыков создания собственной пользовательской функции (макроса) необходимо выполнить данное тренировочное упражнение.
Тренировочное упражнение. Процесс создания собственной пользовательской функции пользователя состоит из двух этапов. На первом этапе необходимо создать (вставить) новый лист модуля, так как в Microsoft Excel макросы и функции пользователя хранятся в специальных местах – модулях. На втором этапе необходимо определить само тело функции с помощью операторов Visual Basic for Application. Функции пользователя могут е зависеть от определенного рабочего листа, что позволяет применть их при работе с несколькими листами. Кроме того, несколько функций пользователя можно объединить на одном листе модуля и использовать в качестве библиотеки.
Создание собственной функции пользователя проиллюстрируем на примере реализации функции, которая вычисляет любую из сторон прямоугольного треугольника.
Согласно теореме Пифагора, в прямоугольном треугольнике квадрат гипотенузы равен сумме квадратов катетов: а2 + b2 = с2. Исходя из данной формулы, мы можем вычислить длину любой стороны прямоугольника при условии, что длины двух других сторон нам известны:
, ,
1. Активизируйте редактор Visual Basic, нажав сочетание клавиш Alt+F11.
После того, как редактор Visual Basic будет активизирован, вставьте новый модуль, войдя в меню Insert и выбрав Module.
Лист модуля похож на лист текстового редактора. На нем вы можете выполнять те же действия, что и на листе тестового редактора: копировать, перемещать, вставлять и удалять введенные операторы Visual Basic, а также комментарии к ним. В строке меню появляется новый пункт Выполнить (Run).
Для удобства в работе присвойте модулю имя. Выберите команду Переименовать (Rename) из подменю Лист (Sheet) меню Правка (Edit). В появившемся диалоговом окне Переименовать лист (Rename Sheet) введите имя, которое мнемонтически напоминало бы (желательно) о функции данного модуля. Например, для нашей цели модуль можно назвать так: Выч_стор_тр.
Введите текст пользовательской функции.
Option Base 1
Function TreugSize(Optional cat1, Optional cat2, Optional hipot)
If Not (IsMissing(cat1)) And Not (IsMissing(cat2)) Then
TreugSize = Sqr(cat1 ^ 2 + cat2 ^ 2)
End If
If Not (IsMissing(cat1)) And Not (IsMissing(hipot)) Then
TreugSize = Sqr(hipot ^ 2 - cat2 ^ 2)
End If
If Not (IsMissing(cat2)) And Not (IsMissing(hipot)) Then
TreugSize = Sqr(hipot ^ 2 - cat1 ^ 2)
End If
End Function
Изображение листа модуля с набранной на нем функцией приведена на рис. 4.1.
Рисунок 4.1 – Лист модуля с набранной на нем функцией
После того как тело функции набрано, она может быть использована на любом рабочем листе активной книги.
Необходимо отметить, что для удобства пользователей, пишущих свои функции, при вводе операторов меняется цвет отдельных их частей. Это облегчает понимание этих частей. При наборе операторов, после введения очередной строки и нажатия клавиши Enter, выполняется синтаксическая проверка введенного кода и, если была допущена ошибка, выдается сообщение.
Запомните: в программировании мелочей не бывает. Если где-то предполагается наличие какого-то слова или знака препинания, то именно это ключевое слово и/или знак препинания должен быть на месте. При вводе недопустимо путать латинские буквы с кириллицей, так как это тоже приводит к ошибке.
Решение задачи с использованием Microsoft Excel
На рисунке 4.2 приведены исходные данные для примера.
Рисунок 4.2 Исходные данные для примера
Столбцы В и С содержат по 10 значений показателя. Для проверки гипотезы о нормальном законе распределения сначала необходимо найти средние значения по каждой выборке. Для этого необходимо в ячейке В12 набрать следующий текст «=CP3HAЧ(B2:B11)», что обеспечит расчет среднего значения для столбца В. Аналогичные действия выполняются для второго столбца, только В заменяется на С.
Затем необходимо вычислить среднеквадратичные отклонения для этих же выборок. Для этого в ячейку В13 (С13) следует записать функцию =СТАНДОТКЛОН(В2:В11) и (=СТАНДОТКЛОН(С2:С11) для С13. Результат представлен на рис. 4.2
Далее необходимо для каждого столбца рассчитать столбцы отклонений от среднего D и Е соответственно. Для этого в ячейках D2, D3 следует набрать формулы =В2-38,6; =ВЗ-38,6 и т. д. (результат см. на рис. 4.3).
Теперь мы имеем всю информацию для проверки гипотезы о нормальном законе распределения.
Рисунок 4.3 Результат решения задачи с помощью Excel
Мы видим, что в каждой выборке одно из условий не выполняется (выделено), что не позволяет нам принять гипотезу о нормальном законе распределения (табл. 4.1). Это означает, что необходимо использовать не-параметрические критерии. Допустим, никаких специальных программных средств мы не имеем. В таком случае выбираем знаковый критерий, как самый простой для реализации для связанных выборок.
Таблица 4.1
Проверка, является ли закон распределения выборки нормальным
Условие
Необходимо
1-я выборка
2-я выборка
Почти все (99,7%) отклонения от среднего меньше 3 сигм (ε1< З σ)
Две трети (68,3%) отклонений меньше, чем σ
Половина отклонений меньше, чем 0,625σ
Первое, что мы должны сделать — посчитать разницу между каждой парой элементов двух выборок. На рис. 4.4 это столбик С от 16 по 25 элемент. Для его расчета необходимо в каждую ячейку записать функцию расчета разницы, например, в ячейку С17 «С2-D2».
Рисунок 4.4 – Выполнение примера
В остальных ячейках меняется только номер. Затем следует построить столбец знаков («-» для отрицательной разницы, «+» для положительной и «0», если разница равна нулю. Этот столбец можно построить «вручную» или с помощью функции, которая приведена на рис. 4.2 для ячейки D26.
Далее подсчитываем число положительных, нулевых и отрицательных разностей (1, 0 и 9 в нашем примере). Для дальнейшей работы необходимо активизировать редактор Visual Basic, нажав сочетание клавиш Alt+F11.
После этого в ячейке D30 набираем вызов этой функции.
Рисунок 4.3 Пример решения задачи для знакового критерия
Результатом работы является число 0,01074. Это вероятность принять гипотезу о равенстве нулю медианы разности. Поскольку эта величина мала (меньше 0,05), то Н0 мы отвергаем и считаем, что средние значения исследуемых выборок статистически значимо различаются.
Приведенный способ проверки является ли закон распределения выборки нормальным требует выполнения определенной последовательности действий и анализа «вручную», а сама проверка всего лишь вспомогательная операция. Возможно произвести набор функций, которые позволяют при их вызове получить ответ в виде надписи NORM или NO_NORM.
Ниже приводится текст, который необходимо записать в рабочую книгу. Поэтому необходимо в меню выбрать последовательно Insert, Macro, Module, у вас откроется лист Module, где и следует набирать текст:
Option Base 1
'Функция вычисления дисперсии выборки
Function VAR_1(R_1 As Object) As Double
'Функция возвращает значение дисперсии выборки
'R_1 - массив (анализируемая выборка)
Application.Volatile True
Dim mas1() As Double
num_elem = R_1.Count 'вычисление количества элементов в массиве
'Инициализация временных переменных
Mean_Tmp = 0#
var_tmp = 0#
'Вычисление среднего значения выборки.
For i = 1 To num_elem
Mean_Tmp = Mean_Tmp + R_1.Cells(i)
Next i
Mean1 = Mean_Tmp / num_elem
'Вычисление чтандартного отклонения
For i = 1 To num_elem
var_tmp = var_tmp + (Mean1 - R_1.Cells(i)) ^ 2
Next i
'Assign return of variance.
VAR_1 = (var_tmp / (num_elem - 1))
End Function
Function STD_1(R_1 As Object) As Double
'Функция возвращает значение стандартного отклонения выборки
'R_1 - массив (анализируемая выборка)
num_elem = R_1.Count 'вычисление количества элементов в массиве
'Инициализация временных переменных
Mean_Tmp = 0#
STD_Tmp = 0#
'Calculate the mean.
For i = 1 To num_elem
Mean_Tmp = Mean_Tmp + R_1.Cells(i)
Next i
Mean1 = Mean_Tmp / num_elem
'Calculate the standard deviation.
For i = 1 To num_elem
STD_Tmp = STD_Tmp + (Mean1 - R_1.Cells(i)) ^ 2
Next i
'Assign return of SD.
STD_1 = (STD_Tmp / (num_elem - 1)) ^ (1 / 2)
End Function
Function STD_2(R_1 As Object) As Double
'Функция возвращает значение стандартного отклонения выборки
'R_1 - массив (анализируемая выборка)
'Assign return of SD.
STD_2 = VAR_1(R_1) ^ (1 / 2)
End Function
Function MEAN_1(R_1 As Object) As Double
'Функция возвращает значение стандартного отклонения выборки
'R_1 - массив (анализируемая выборка)
num_elem = R_1.Count 'вычисление количества элементов в массиве
Mean_Tmp = 0#
'Вычисление среднего значения выборки.
For i = 1 To num_elem
Mean_Tmp = Mean_Tmp + R_1.Cells(i)
Next i
MEAN_1 = Mean_Tmp / num_elem
End Function
Function NORMSAMP_1(R_1 As Object) As String
'R_1 - массив (анализируемая выборка)
Application.Volatile True
num_elem = R_1.Count 'вычисление количества элементов в массиве
Mean_Tmp = 0#
Abs_Tmp = 0#
'Вычисление среднего
For i = 1 To num_elem
Mean_Tmp = Mean_Tmp + R_1.Cells(i)
Next i
Mean1 = Mean_Tmp / num_elem
'Вычисление абсолютного среднего отклонения.
For i = 1 To num_elem
Abs_Tmp = Abs_Tmp + Abs(R_1.Cells(i) - Mean1)
Next i
Abs_1 = Abs_Tmp / num_elem
S_1 = STD_2(R_1)
x_1 = Abs(Abs_1 / S_1 - 0.7979)
y_1 = 0.4 / (num_elem ^ 1 / 2)
If x_1 < y_1 Then NORMSAMP_1 = "NORM"
If y_1 <= x_1 Then NORMSAMP_1 = "NO_NORM"
End Function
После этого вы можете в любом листе книги вызывать функцию NORMSAMP_l(), параметрами которой передаются интервалы ячеек, где находятся данные. Для примера из рис. 4.3 - NORMSAMP_l(C2:Cll). Результатом будет появление в ячейке, в которой вызвана функция надписи NORM или NO_NORM. На рис. 4.4 показан пример проверки, является ли закон распределения нормальным.
Рисунок 4.4 Пример проверки выборки на соответствие нормальному закону распределения
Задание принять или отвергнуть гипотезу о нормальном законе распределения выборок экспериментальных данных приведенных в таблице 4.2
Таблица 4.2
Варианты заданий
Данные эксперимента
Вариант
2,3
2,6
2,2
2,1
2,5
2,6
1,20
1,42
17,3
23,5
2,37
2,85
35,2
26,1
2,1
2,6
5,63
5,62
26,1
27,0
5,67
2,67
35,9
25,8
5,1
5,63
2,34
2,37
23,9
23,3
2,35
2,34
33,6
23,8
2,34
2,38
7,71
7,90
28,0
25,2
2,59
2,58
35,7
26,0
7,63
7,6,1
1,2
1,6
1,7
2,6
1,9
2,8
1,13
1,15
21,6
21,2
2,13
2,16
31,7
1,12
1,12
1,45
1,47
24,7
24,8
2,45
2,47
34,8
24,5
1,49
1,45
3,57
3,59
25,9
25,7
2,55
2,59
36,0
25,7
3,58
3,58
3,3
3,6
2,5
2,4
3,4
3,5
Данные эксперимента
Вариант
7,3
7,6
12,2
12,1
3,5
4,6
6,20
6,42
217,3
230,5
12,37
12,85
75,2
86,1
3,1
4,6
7,63
5,62
264,1
278,0
15,67
14,67
75,9
75,8
5,1
5,63
6,34
5,37
233,9
236,3
12,35
12,34
73,6
73,8
3,34
4,38
7,71
7,90
281,0
255,2
12,59
12,58
85,7
86,0
3,63
4,6,1
6,2
6,6
11,7
12,6
3,9
4,8
4,13
4,15
251,6
261,2
12,13
12,16
71,7
5,12
4,12
5,45
6,47
244,7
247,8
12,45
12,47
74,8
84,5
3,49
4,45
5,57
5,59
250,9
255,7
12,55
12,59
86,0
85,7
3,58
3,58
5,3
5,6
12,5
12,4
3,4
3,5
В качестве исходных данных в задании можете использовать свои экспериментальные данные.
Отчет должен содержать расчеты статистических характеристик и проверку их на соответствие закону нормального распределения (по правилу трех сигм и с помощью макроса).
Контрольные вопросы:
1. От чего зависит F-распределение?
2. От чего зависит χ2-распределение?
3. В каких случаях используется биноминальное распределение?
4. В каких случаях используется распределение Пуассона?
5. В каких случаях используется экспоненциально распределение?
6. Каким образом определяют вид и параметры распределения случайных величин?
7. Какие действия осуществляются для преобразования распределения результатов испытаний по нормальному закону?
, 
, 
