Проверка гипотезы о равенстве средних двух выборок в excel • коэффициент вариации

Сравнение средних значений двух нормальных генеральных совокупностей

1. Генеральные совокупности X и Y распределены нормально и известны их дисперсии
2. По независимым выборкам объемами n и m найдены выборочные средние \(\hat{x}\) и \(\hat{y}\).
3. Требуется по выборочным средним оценить нулевую гипотезу о том, что генеральные средние рассматриваемых совокупностей равны:

Для проверке гипотезы о равенстве средних генеральных совокупностей используется функция scipy.stats.ttest_ind.

Аргументы функции:

• выборка 1
• выборка 2
• equal_var — принимается ли допущение о равенстве дисперсий двух выборок (True/False)
• alternative альтернативная гипотеза (‘greater’, ‘less’, ‘two-sided’)

Альтернативная гипотеза \(H_1: M(X) \neq M(Y)\). alternative=’two-sided’

Результат:

Предположим, что принятый уровень значимости равен 0,05. Полученное p-значение велико — гораздо больше уровня значимости, следовательно нет оснований отвергнуть нулевую гипотезу о равенстве средних двух генеральных совокупностей. Ожидаемый результат, с учетом того, что массивы a и b сгенерированы с одинаковыми математическими ожиданиями.

Альтернативная гипотеза \(H_1: M(X) > M(Y)\). alternative=’greater’

Результат:

Полученное p-значение велико — гораздо больше уровня значимости, следовательно нет оснований отвергнуть нулевую гипотезу о равенстве средних двух генеральных совокупностей в пользу альтернативной гипотезы.

Альтернативная гипотеза \(H_1: M(X) < M(Y)\). alternative=’less’

Результат:

Полученное p-значение велико — гораздо больше уровня значимости, следовательно нет оснований отвергнуть нулевую гипотезу о равенстве средних двух генеральных совокупностей в пользу альтернативной гипотезы.

Как написать коэффициент в экселе

Одним из основных статистических показателей последовательности чисел является коэффициент вариации. Для его нахождения производятся довольно сложные расчеты. Инструменты Microsoft Excel позволяют значительно облегчить их для пользователя.

Вычисление коэффициента вариации

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

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

Шаг 1: расчет стандартного отклонения

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

Для расчета стандартного отклонения используется функция СТАНДОТКЛОН.

Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…) = СТАНДОТКЛОН.Г(Число1;Число2;…)

= СТАНДОТКЛОН.В(Число1;Число2;…)

1. Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.

Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».

Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1».

Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д.

Когда все нужные данные введены, жмем на кнопку «OK»

В предварительно выделенной ячейке отображается итог расчета выбранного вида стандартного отклонения.

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.

1. Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».

В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».

Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки.

После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».

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

Шаг 3: нахождение коэффициента вариации

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

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

Это можно сделать после её выделения, находясь во вкладке «». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный».

После этих действий формат у элемента будет соответствующий.

Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения.

Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда.

2.4. Ранговый критерий рассеяния Зигеля и Тьюки

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

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

При
использовании критерия объединенная выборка объемом  (при ) упорядочивается
(строится вариационный ряд). Ранги элементам такой выборки присваиваются
следующим образом: наименьшее значение получает ранг 1, два наибольших значения
получают ранги 2 и 3, ранги 4 и 5 получают следующие наименьшие значения, 6 и 7
– следующие наибольшие значения и т.д. Если число наблюдений нечетно, то
среднее наблюдение не получает никакого ранга, если четное – оно получает
наивысший ранг.

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

Для оценки
разности при малых выборках () авторы дают точные критические
значения.

Для не
слишком малых выборок ( и  или  и ) используют
статистику

,

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

Следует
иметь ввиду, что при сильно различающихся объемах выборок  и  предлагается
использовать скорректированное выражение для статистики:

.

Другая
коррекция для статистики предусматривается в случае присутствия в выборках
большого числа одинаковых значений.

Построение доверительного интервала

Обычно, зная распределение и его параметры, мы можем вычислить вероятность того, что случайная величина примет значение из заданного нами интервала. Сейчас поступим наоборот: найдем интервал, в который случайная величина попадет с заданной вероятностью. Например, из свойств нормального распределения
известно, что с вероятностью 95%, случайная величина, распределенная по нормальному закону
, попадет в интервал примерно +/- 2 от среднего значения
(см. статью про ). Этот интервал, послужит нам прототипом для доверительного интервала
.

Теперь разберемся,знаем ли мы распределение,
чтобы вычислить этот интервал? Для ответа на вопрос мы должны указать форму распределения и его параметры.

Форму распределения мы знаем – это нормальное распределение
(напомним, что речь идет о выборочном распределении
статистики
Х ср
).

Параметр μ нам неизвестен (его как раз нужно оценить с помощью доверительного интервала
), но у нас есть его оценка Х ср,
вычисленная на основе выборки,
которую можно использовать.

Второй параметр – стандартное отклонение выборочного среднего
будем считать известным
, он равен σ/√n.

Т.к. мы не знаем μ, то будем строить интервал +/- 2 стандартных отклонения
не от среднего значения
, а от известной его оценки Х ср
. Т.е. при расчете доверительного интервала
мы НЕ будем считать, что Х ср
попадет в интервал +/- 2 стандартных отклонения
от μ с вероятностью 95%, а будем считать, что интервал +/- 2 стандартных отклонения
от Х ср
с вероятностью 95% накроет μ – среднее генеральной совокупности,
из которого взята выборка
. Эти два утверждения эквивалентны, но второе утверждение нам позволяет построить доверительный интервал
.

Кроме того, уточним интервал: случайная величина, распределенная по нормальному закону
, с вероятностью 95% попадает в интервал +/- 1,960 стандартных отклонений,
а не+/- 2 стандартных отклонения
. Это можно рассчитать с помощью формулы =НОРМ.СТ.ОБР((1+0,95)/2)
, см. файл примера Лист Интервал
.

Теперь мы можем сформулировать вероятностное утверждение, которое послужит нам для формирования доверительного интервала
: «Вероятность того, что среднее генеральной совокупности
находится от среднего выборки
в пределах 1,960 «стандартных отклонений выборочного среднего»
, равна 95%».

Значение вероятности, упомянутое в утверждении, имеет специальное название , который связан с
уровнем значимости α (альфа) простым выражением уровень доверия
=1
-α.
В нашем случае уровень значимости
α=1-0,95=0,05
.

Теперь на основе этого вероятностного утверждения запишем выражение для вычисления доверительного интервала
:

где Z α/2
–
стандартного
нормального распределения
(такое значение случайной величины z
,
что P
(z
>=Z α/2

)=α/2
).

Примечание
: Верхний α/2-квантиль
определяет ширину доверительного интервала
в стандартных отклонениях
выборочного среднего. Верхний α/2-квантиль

стандартного
нормального распределения
всегда больше 0, что очень удобно.

В нашем случае при α=0,05, верхний α/2-квантиль
равен 1,960. Для других уровней значимости α (10%; 1%) верхний α/2-квантиль
Z α/2

можно вычислить с помощью формулы =НОРМ.СТ.ОБР(1-α/2)
или, если известен уровень доверия
, =НОРМ.СТ.ОБР((1+ур.доверия)/2)
.

Обычно при построении доверительных интервалов для оценки среднего
используют только верхний α
/2-квантиль
и не используют нижний α
/2-квантиль
. Это возможно потому, что стандартное
нормальное распределение
симметрично относительно оси х (плотность его распределения
симметрична относительно среднего, т.е. 0
).
Поэтому, нет нужды вычислять нижний α/2-квантиль
(его называют просто α/2-квантиль
), т.к. он равен верхнему α
/2-квантилю
со знаком минус.

Напомним, что, не смотря на форму распределения величины х, соответствующая случайная величина Х ср
распределена приблизительно
нормально
N(μ;σ 2 /n) (см. статью про ). Следовательно, в общем случае, вышеуказанное выражение для доверительного интервала
является лишь приближенным. Если величина х распределена по нормальному закону
N(μ;σ 2 /n), то выражение для доверительного интервала
является точным.

Вычисление Р-значения

При проверке гипотез большое распространение также получил еще один эквивалентный подход, основанный на вычислении p -значения (p-value).

Если p-значение , вычисленное на основании выборки , меньше чем заданный уровень значимости α , то нулевая гипотеза отвергается и принимается альтернативная гипотеза . И наоборот, если p-значение больше α, то нулевая гипотеза не отвергается.

Формула для вычисления p-значения зависит от формулировки альтернативной гипотезы :

• Для односторонней гипотезы σ 2 2 p-значение вычисляется как =ХИ2.РАСП( χ 2 ; n-1;ИСТИНА)
• Для другой односторонней гипотезы σ 2 > σ 2 p-значение вычисляется как =ХИ2.РАСП.ПХ( χ 2 ; n-1)
• Для двусторонней гипотезыp-значение вычисляется как =2*МИН(ХИ2.РАСП( χ 2 ;n-1;ИСТИНА); ХИ2.РАСП.ПХ( χ 2 ;n-1))

Соответственно, χ 2 = (СЧЁТ( выборка )-1)* ДИСП.В( выборка )/ σ 2 , где выборка – ссылка на диапазон, содержащий значения выборки .

СОВЕТ : Подробнее про вышеуказанные функции MS EXCEL см. статью про χ 2 -распределение .

В файле примера на листе Дисперсия показано решение задач проверки двусторонней и односторонних гипотез .

Пример использования т-критерия Стьюдента

А пример будет достаточно простой: мне интересно, стали ли люди выше за последние 100 лет. Для этого нужно подобрать некоторые данные. Я обнаружил интересную информацию в достаточно известной статье The Guardian (Tall story’s men and women have grown taller over last century, Study Shows (The Guardian, July 2016), которая сравнивает средний возраст человека в разных странах в 1914 году и в аналогичных странах в 2014 году.

Там приведены данные практически по всем государствам. Однако, я взял лишь 5 стран для простоты вычислений: это Россия, Германия, Китай, США и ЮАР, соответственно 1914 год и 2014 год.

Общее количество наблюдений – 5 в 1914 году в группе 1914 года и общее значение также 5 в 2014 году. Будем думать опять же для простоты, что эти данные сопоставимы, и с ними можно работать.

Дальше нужно выбрать критерии – критерии, по которым мы будем давать ответ. Равны ли средние по росту в 1914 году x̅₁₉₁₄ и в 2014 году x̅₂₀₁₄. Я считаю, что нет. Поэтому моя гипотеза это то, что они не равны (x̅₁₉₁₄≠x̅₂₀₁₄). Соответственно альтернативная гипотеза моему предположению, так называемая нулевая гипотеза (нулевая гипотеза консервативна, обратная вашей, часто говорит об отсутствии статистически значимых связей/зависимостей) будет говорить о том, что они между собой на самом деле равны (x̅₁₉₁₄=x̅₂₀₁₄), то есть о том, что все эти находки случайны, и я, по сути, не прав.

Для чего используется t-критерий Стьюдента?

t-критерий Стьюдента используется для определения статистической значимости различий средних величин. Может применяться как в случаях сравнения независимых выборок (например, группы больных сахарным диабетом и группы здоровых), так и при сравнении связанных совокупностей (например, средняя частота пульса у одних и тех же пациентов до и после приема антиаритмического препарата). В последнем случае рассчитывается парный t-критерий Стьюдента

В каких случаях можно использовать t-критерий Стьюдента?

Для применения t-критерия Стьюдента необходимо, чтобы исходные данные имели нормальное распределение. Также имеет значение равенство дисперсий (распределения) сравниваемых групп (гомоскедастичность). При неравных дисперсиях применяется t-критерий в модификации Уэлча (Welch’s t).

При отсутствии нормального распределения сравниваемых выборок вместо t-критерия Стьюдента используются аналогичные методы непараметрической статистики, среди которых наиболее известными является U-критерий Манна — Уитни.

Как интерпретировать значение t-критерия Стьюдента?

Полученное значение t-критерия Стьюдента необходимо правильно интерпретировать. Для этого нам необходимо знать количество исследуемых в каждой группе (n₁ и n₂). Находим число степеней свободы f по следующей формуле:

После этого определяем критическое значение t-критерия Стьюдента для требуемого уровня значимости (например, p=0,05) и при данном числе степеней свободы f по таблице (см. ниже).

Сравниваем критическое и рассчитанное значения критерия:

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

Вы можете внести данные для расчета критерия Т-Стьюдента поочередно вручную или скопировать их из вашего Excel файла.

Что такое дисперсия?

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

Математически дисперсия не так уж сложна:

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

1. Вычислите среднее значение набора значений. Чтобы вычислить среднее значение, возьмите сумму всех значений, разделенную на количество значений.
2. Возьмите каждое значение в вашем наборе и вычтите его из среднего.
3. Возведите полученные значения в квадрат (чтобы исключить отрицательные числа).
4. Сложите все квадраты значений вместе.
5. Вычислите среднее квадратов значений, чтобы получить дисперсию.

Как видите, вычислить это значение несложно. Однако если у вас есть сотни или тысячи значений, на то, чтобы сделать это вручную, уйдет целая вечность. Так что хорошо, что Excel может автоматизировать этот процесс!

Критические точки распределения Стьюдента.

Ниже представлена таблица значений распределения хи-квадрат. Однако эти значения можно получить и самостоятельно, для этого необходим лишь Microsoft Excel. Выделите ячейку и занесите в нее вызов статистической функции

, где P — уровень значимости α (вероятность), K — количество степеней свободы, т.е. n-1. Так можно получить значение для произвольного количества степеней свободы и для любого уровня значимости.

k	Уровень значимости α (двусторонняя критическая область)
0,1	0,05	0,02	0,01	0,002	0,001
1	6,3138	12,7062	31,8205	63,6567	318,3088	636,6192
2	2,9200	4,3027	6,9646	9,9248	22,3271	31,5991
3	2,3534	3,1824	4,5407	5,8409	10,2145	12,9240
4	2,1318	2,7764	3,7469	4,6041	7,1732	8,6103
5	2,0150	2,5706	3,3649	4,0321	5,8934	6,8688
6	1,9432	2,4469	3,1427	3,7074	5,2076	5,9588
7	1,8946	2,3646	2,9980	3,4995	4,7853	5,4079
8	1,8595	2,3060	2,8965	3,3554	4,5008	5,0413
9	1,8331	2,2622	2,8214	3,2498	4,2968	4,7809
10	1,8125	2,2281	2,7638	3,1693	4,1437	4,5869
11	1,7959	2,2010	2,7181	3,1058	4,0247	4,4370
12	1,7823	2,1788	2,6810	3,0545	3,9296	4,3178
13	1,7709	2,1604	2,6503	3,0123	3,8520	4,2208
14	1,7613	2,1448	2,6245	2,9768	3,7874	4,1405
15	1,7531	2,1314	2,6025	2,9467	3,7328	4,0728
16	1,7459	2,1199	2,5835	2,9208	3,6862	4,0150
17	1,7396	2,1098	2,5669	2,8982	3,6458	3,9651
18	1,7341	2,1009	2,5524	2,8784	3,6105	3,9216
19	1,7291	2,0930	2,5395	2,8609	3,5794	3,8834
20	1,7247	2,0860	2,5280	2,8453	3,5518	3,8495
21	1,7207	2,0796	2,5176	2,8314	3,5272	3,8193
22	1,7171	2,0739	2,5083	2,8188	3,5050	3,7921
23	1,7139	2,0687	2,4999	2,8073	3,4850	3,7676
24	1,7109	2,0639	2,4922	2,7969	3,4668	3,7454
25	1,7081	2,0595	2,4851	2,7874	3,4502	3,7251
26	1,7056	2,0555	2,4786	2,7787	3,4350	3,7066
27	1,7033	2,0518	2,4727	2,7707	3,4210	3,6896
28	1,7011	2,0484	2,4671	2,7633	3,4082	3,6739
29	1,6991	2,0452	2,4620	2,7564	3,3962	3,6594
30	1,6973	2,0423	2,4573	2,7500	3,3852	3,6460
40	1,6839	2,0211	2,4233	2,7045	3,3069	3,5510
50	1,6759	2,0086	2,4033	2,6778	3,2614	3,4960
60	1,6706	2,0003	2,3901	2,6603	3,2317	3,4602
70	1,6669	1,9944	2,3808	2,6479	3,2108	3,4350
80	1,6641	1,9901	2,3739	2,6387	3,1953	3,4163
90	1,6620	1,9867	2,3685	2,6316	3,1833	3,4019
100	1,6602	1,9840	2,3642	2,6259	3,1737	3,3905
110	1,6588	1,9818	2,3607	2,6213	3,1660	3,3812
120	1,6577	1,9799	2,3578	2,6174	3,1595	3,3735
200	1,6525	1,9719	2,3451	2,6006	3,1315	3,3398
	0,05	0,025	0,01	0,005	0,001	0,0005
	Уровень значимости α (односторонняя критическая область)

Доверительный интервал

крайне мала и равна 0,003(1–0,997). Такие маловероятные события считаются практически невозможными, а потому величину

Выборочное наблюдение дает возможность определить среднюю арифметическую выборочной совокупности x и величину предельной ошибки этой средней ∆x, которая показывает с определенной вероятностью), насколько выборочная может отличаться от генеральной средней в большую или меньшую сторону. Тогда величина генеральной средней будет представлена интервальной оценкой, для которой нижняя граница будет равна

Интервал, в который с данной степенью вероятности будет заключена неизвестная величина оцениваемого параметра, называют доверительным, а вероятность Р – доверительной вероятностью. Чаще всего доверительную вероятность принимают равной 0,95 или 0,99, тогда коэффициент доверия t равен соответственно 1,96 и 2,58. Это означает, что доверительный интервал с заданной вероятностью заключает в себе генеральную среднюю.

Наряду с абсолютной величиной предельной ошибки выборки рассчитывается и относительная ошибка выборки, которая определяется как процентное отношение предельной ошибки выборки к соответствующей характеристике выборочной совокупности:

Чем больше величина предельной ошибки выборки, тем больше величина доверительного интервала и тем, следовательно, ниже точность оценки. Средняя (стандартная) ошибка выборки зависит от объема выборки и степени вариации признака в генеральной совокупности.

Функция ДОВЕРИТ

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

Доверительный интервал представляет собой диапазон значений. Выборочное среднее x является серединой этого диапазона, следовательно, доверительный интервал определяется как (x ± ДОВЕРИТ). Например, если x — это среднее выборочное значение времени доставки товаров, заказанных по почте, то математическое ожидание генеральной совокупности принадлежит интервалу (x ± ДОВЕРИТ).

Для любого значения математического ожидания генеральной совокупности μ0, находящегося в этом интервале, вероятность того, что выборочное среднее отличается от μ0 более чем на x, превышает значение уровня значимости «альфа». Для любого математического ожидания μ0, не относящегося к этому интервалу, вероятность того, что выборочное среднее отличается от μ0 более чем на x, не превышает значения уровня значимости «альфа». Например, предположим, что требуется при заданном выборочном среднем x, стандартном отклонении генеральной совокупности и размере выборки создать критерий на основе двойной выборки при уровни значимости «альфа» для проверки гипотезы о том, согласно которой, математическое ожидание равно μ0. В этом случае гипотеза не отвергается, если μ0 принадлежит доверительному интервалу, и отвергается, если μ0 не принадлежит доверительному интервалу. Доверительный интервал не позволяет предполагать, что с вероятностью (1 альфа) время доставки следующей посылки окажется в пределах доверительного интервала.

Синтаксис

ДОВЕРИТ(альфа ;станд_откл;размер)

Альфа — уровень значимости, используемый для вычисления уровня надежности. Уровень надежности равняется 100*(1 — альфа) процентам или, другими словами, значение аргумента «альфа», равное 0,05, означает 95-процентный уровень надежности.

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

Размер — размер выборки.

Замечания

·        Если какой-либо из аргументов не является числом, функция ДОВЕРИТ возвращает значение ошибки #ЗНАЧ!.

·        Если альфа ≤ 0 или альфа ≥ 1, функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.

·        Если станд_откл ≤ 0, функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.

·        Если значения аргумента «размер» не является целым числом, то оно усекается.

·        Если размер < 1, функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.

·        Если предположить, что альфа = 0,05, то нужно определить ту часть стандартной нормальной кривой, которая равна (1 — альфа), или 95 процентам. Это значение равно ± 1,96. Следовательно, доверительный интервал, следовательно, определяется по формуле:

<<<<предыдущая || оглавление || следующая>>

Функция Z.TEST

Функция Z.TEST делает все расчетов из шагов два и три выше. Он выполняет большую часть обработки чисел для нашего теста и возвращает p-значение. В функцию можно ввести три аргумента, каждый из которых отделяется запятой. Ниже объясняются три типа аргументов для этой функции.

1. Первый аргумент для этой функции – это массив образцов данных. Мы должны ввести диапазон ячеек, который соответствует расположению выборки данных в нашей электронной таблице.
2. Второй аргумент – это значение μ, которое мы проверяем в наших гипотезах. Итак, если наша нулевая гипотеза H : μ = 5, то мы должны ввести 5 для второго аргумента.
3. Третий аргумент – это значение известное стандартное отклонение населения. Excel рассматривает это как необязательный аргумент.

Структура проверки гипотез

Рассматриваемая нами конкретная проверка гипотез имеет следующую форму:

1. Сформулируйте нулевую и альтернативную гипотезы.
2. Рассчитайте статистику теста, которая представляет собой z -счет.
3. Рассчитайте p-значение, используя нормальное распределение. В этом случае p-значение представляет собой вероятность получения не менее экстремальной, чем наблюдаемая статистика теста, при условии, что нулевая гипотеза верна.
4. Сравните p-значение с уровнем значимости, чтобы определить отвергать или не отвергать нулевую гипотезу.

Мы видим, что шаги два и три требуют больших вычислительных ресурсов по сравнению с двумя шагами один и четыре. Функция Z.TEST выполнит эти вычисления за нас.

Квантили стандартного нормального распределения

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

Примечание : Про проверку статистических гипотез см. статью Проверка статистических гипотез в MS EXCEL . Про построение доверительных интервалов см. статью Доверительные интервалы в MS EXCEL .

В данных задачах часто используется специальная терминология:

• Нижний квантиль уровняальфа ( α percentage point) файл примера лист Квантили ).

Для α=0,05, нижний 0,05-квантиль стандартного нормального распределения равен -1,645. Вычисления в MS EXCEL можно сделать по формуле:

Действительно, для α=0,05, верхний 0,05-квантиль стандартного нормального распределения равен 1,645. Т.к. функция плотности вероятности стандартного нормального распределения является четной функцией, то вычисления в MS EXCEL верхнего квантиля можно сделать по двум формулам:

Чтобы пояснить название « верхний» квантиль , построим график плотности вероятности и функцию вероятности стандартного нормального распределения для α=0,05.

Выделенная площадь на рисунке соответствует вероятности, что случайная величина примет значение больше верхнего 0,05-квантиля , т.е. больше значения 1,645. Эта вероятность равна 0,05.

Невыделенная площадь на рисунке соответствует вероятности, что случайная величина примет значение между нижним квантилем уровня α /2 и верхним квантилем уровня α /2, т.е. будет между значениями -1,960 и 1,960 при α=0,05. Эта вероятность равна в нашем случае 1-(0,05/2+0,05/2)=0,95. Если Z попадает в одну из выделенных областей, то нулевая гипотеза отклоняется.

Другими словами, двусторонние α-квантили задают интервал, в который рассматриваемая случайная величина попадает с заданной вероятностью α.

Пакет анализа

В надстройке Пакет анализа для проведения двухвыборочного F -теста имеется специальный инструмент: Двухвыборочный F-тест для дисперсии (F-Test Two Sample for Variances).

После выбора инструмента откроется окно, в котором требуется заполнить следующие поля (см. файл примера лист Пакет анализа ):

• интервал переменной 1 : ссылка на значения первой выборки . Ссылку указывать лучше с заголовком. В этом случае, при выводе результата надстройка выводит заголовки, которые делают результат нагляднее (в окне требуется установить галочку Метки );
• интервал переменной 2 : ссылка на значения второй выборки ;
• Метки: если в полях интервал переменной 1 и интервал переменной 2 указаны ссылки вместе с заголовками столбцов, то эту галочку нужно установить. В противном случае надстройка не позволит провести вычисления и пожалуется, что « входной интервал содержит нечисловые данные »;
• Альфа:уровень значимости ;
• Выходной интервал: диапазон ячеек, куда будут помещены результаты вычислений. Достаточно указать левую верхнюю ячейку этого диапазона.

В результате вычислений будет заполнен указанный Выходной интервал.

Тот же результат можно получить с помощью формул (см. файл примера лист Пакет анализа ):

Разберем результаты вычислений, выполненных надстройкой:

• Среднее : средние значения обеих выборок . Вычисления можно сделать с помощью функции СРЗНАЧ() . Значения средних в расчетах для проверки гипотез не участвуют и приводятся для информации;
• Дисперсия : дисперсии обеих выборок. Вычисления можно сделать с помощью функции ДИСП.В()
• Наблюдения : размер выборок. Вычисления можно сделать с помощью функции СЧЁТ()
• Df : число степеней свободы : n-1, где n размер выборок ;
• F : значение тестовойF-статистики (в наших обозначениях – это F – отношение дисперсий выборок );
• P(F2 > σ ₂ 2 . Эквивалентная формула =F.РАСП.ПХ(F ;n ₁ -1; n ₂ -1) ;
• Fкритическое одностороннее (F Critical one-tail):Верхний α-квантильF-распределения c n ₁ -1 и n ₂ -1 степенями свободы . Эквивалентная формула =F.ОБР.ПХ(α; n ₁ -1; n ₂ -1) .

СОВЕТ : О проверке других видов гипотез см. статью Проверка статистических гипотез в MS EXCEL .

Понятие Квантиля основано на определении Функции распределения . Поэтому, перед изучением Квантилей рекомендуем освежить в памяти понятия из статьи Функция распределения вероятности .

• Определение
• Квантили специальных видов
• Квантили стандартного нормального распределения
• Квантили распределения Стьюдента
• Квантили распределения ХИ-квадрат
• Квантили F-распределения
• Квантили распределения Вейбулла
• Квантили экспоненциального распределения

Сначала дадим формальное определение квантиля, затем приведем примеры их вычисления в MS EXCEL.

Примеры использования функции ГАУСС в Excel

Синтаксис рассматриваемой функции не представляет из себя ничего сложного, ведь функции ГАУСС присущ всего один обязательный аргумент – Z – возвращающий число.

Важно отметить, что существует определенная связь между функцией ГАУСС и такой статистической функцией, как стандартное нормальное распределение, иначе говоря – НОРМ.СТ.РАСП. Итак, всегда функция НОРМ.СТ.РАСП (0; Истина) делает возврат 0,5, тогда как ГАУСС (z) имеет в результате значение меньше на 0,5, чем результат функции НОРМ.СТ.РАСП

На рисунке, расположенном ниже, приведен пример использования данных статистических функций для возвращения числа 1,5

Итак, всегда функция НОРМ.СТ.РАСП (0; Истина) делает возврат 0,5, тогда как ГАУСС (z) имеет в результате значение меньше на 0,5, чем результат функции НОРМ.СТ.РАСП. На рисунке, расположенном ниже, приведен пример использования данных статистических функций для возвращения числа 1,5.

Для наглядности продемонстрируем зависимость между значениями функций графическим способом. Для этого – сформируем таблицу с выборкой чисел, например на интервале от -5 до 5 с шагом 0,5, а затем по имеющимся данным построим график:

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