Стьюдраспобр excel как пользоваться
Распределение Стьюдента (t-распределение). Распределения математической статистики в EXCEL
history 10 ноября 2016 г.
Рассмотрим Распределение Стьюдента (t-распределение). С помощью функции MS EXCEL СТЬЮДЕНТ.РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.
Распределение Стьюдента (также называется t -распределением ) применяется в различных методах математической статистики:
Плотность распределения Стьюдента выражается формулой: 
Дисперсию t -распределения можно вычислить по формуле =df/(df-2)
Графики функций
Ниже для сравнения приведены графики плотности стандартного нормального распределения и распределения Стьюдента.
t-распределение в MS EXCEL
Очевидно, что справедливо равенство
Примеры
Вышеуказанные функции можно взаимозаменять, т.к. нижеуказанные формулы возвращают одинаковый результат: =СТЬЮДЕНТ.ОБР(альфа;n) =-СТЬЮДРАСПОБР(альфа*2;n) =-СТЬЮДЕНТ.ОБР.2Х(альфа*2;n)
Функции MS EXCEL, использующие t-распределение
Например, формула =ДОВЕРИТ.СТЬЮДЕНТ(альфа;СТАНДОТКЛОН.В(B20:B79); СЧЁТ(B20:B79)) эквивалентна классической формуле для вычисления доверительного интервала =СТЬЮДЕНТ.ОБР(1-альфа/2; СЧЁТ(B20:B79)-1)* СТАНДОТКЛОН.В(B20:B79)/КОРЕНЬ(СЧЁТ(B20:B79))
Как видим, особых преимуществ в использовании ДОВЕРИТ.СТЬЮДЕНТ() нет.
Оценка параметров распределения
Т.к. обычно t-распределение используется для целей математической статистики (вычисление доверительных интервалов, проверки гипотез и др.), и практически никогда для построения моделей реальных величин, то для этого распределения обсуждение оценки параметров распределения здесь не производится.
СТЬЮДРАСПОБР функция распределения Стьюдента в Excel
Функция СТЮДРАСПОБР предназначена для расчета значения квантиля уровня, соответствующего известной вероятности (указывается в качестве первого аргумента), распределения Стьюдента для известных степеней свободы и возвращает обратное t-распределение.
Распределение Стьюдента и нормальное распределение в Excel
Рассматриваемая функция возвращает значение t, соответствующее условию P(|x|>t)=p. Здесь x является значением некоторой случайной величины с распределением Стьюдента, у которого число степеней свобод соответствует k (второй аргумент функции СТЮДРАСПОБР).
Ниже рассмотрим примеры использования функции СТЮДРАСПОБР в Excel.
Определение одностороннего и двустороннего t распределение Стьюдента
Пример 1. Определить односторонне и двустороннее t-значения для распределения Стьюдента, характеризующееся вероятностью 0,17 и числом степени свобод 16.
Вид таблицы данных:
Для расчета двустороннего t-значения используем функцию:
Для двустороннего t используем удвоенное значение вероятности:
В результате получим:
Число степеней свободы в распределении Стьюдента
Пример 2. Сгенерировать 8 случайных чисел с использованием функции СЛЧИС, для которых распределение Стьюдента имеет 4 степени свободы.
Поскольку вероятность того, что случайна величина примет как отрицательное, так и положительное значение является одинаковой и равна 0,5 (распределение Стьюдента симметрично относительно вертикальной оси графика), используем функцию ЕСЛИ для проверки значений.
Выделим 8 ячеек и запишем следующую функцию (вводить как формулу массива CTRL+SHIFT+Enter):
То есть, если случайное значение вероятности, сгенерированное функцией СЛЧИС меньше 0,5, будет сгенерировано отрицательное t-значение, иначе – положительное.
Как пользоваться функцией распределения Стьюдента СТЮДРАСПОБР В EXCEL
Функция имеет следующий синтаксис:
Функция СТЬЮДРАСПОБР
Возвращает двустороннее обратное t-распределения Стьюдента.
Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.
Дополнительные сведения о новых функциях см. в разделах Функция СТЬЮДЕНТ.ОБР.2Х и Функция СТЬЮДЕНТ.ОБР.
Синтаксис
Аргументы функции СТЬЮДРАСПОБР описаны ниже.
Вероятность Обязательный. Вероятность, соответствующая двустороннему распределению Стьюдента.
Степени_свободы Обязательный. Число степеней свободы, характеризующее распределение.
Замечания
Если любой из аргументов не является числом, то СТИФР.#VALUE! значение ошибки #ЗНАЧ!.
Если вероятность 1, то #NUM! значение ошибки #ЗНАЧ!.
Если значение «степени_свободы» не является целым, оно усекается.
Если deg_freedom t) = вероятность, где X — случайная величина, соответствующая t-распределению, и P(|X| > t) = P(X t).
Одностороннее t-значение может быть получено при замене аргумента «вероятность» на 2*вероятность. Для вероятности 0,05 и 10 степеней свободы двустороннее значение вычисляется по формуле СТЬЮДРАСПОБР(0,05;10) и равно 2,28139. Одностороннее значение для той же вероятности и числа степеней свободы может быть вычислено по формуле СТЬЮДРАСПОБР(2*0,05;10), возвращающей значение 1,812462.
Примечание: В некоторых таблицах вероятность описана как (1-p).
Если задано значение вероятности, то функция СТЬЮДРАСПОБР ищет значение x, для которого функция СТЬЮДРАСП(x, степени_свободы, 2) = вероятность. Однако точность функции СТЬЮДРАСПОБР зависит от точности СТЬЮДРАСП. В функции СТЬЮДРАСПОБР для поиска применяется метод итераций. Если поиск не закончился после 100 итераций, функция возвращает значение ошибки #Н/Д.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Вероятность, соответствующая двустороннему распределению Стьюдента.
Функция СТЬЮДРАСПОБР
Возвращает двустороннее обратное t-распределения Стьюдента.
Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.
Дополнительные сведения о новых функциях см. в разделах Функция СТЬЮДЕНТ.ОБР.2Х и Функция СТЬЮДЕНТ.ОБР.
Синтаксис
Аргументы функции СТЬЮДРАСПОБР описаны ниже.
Вероятность Обязательный. Вероятность, соответствующая двустороннему распределению Стьюдента.
Степени_свободы Обязательный. Число степеней свободы, характеризующее распределение.
Замечания
Если любой из аргументов не является числом, то СТИФР.#VALUE! значение ошибки #ЗНАЧ!.
Если вероятность 1, то #NUM! значение ошибки #ЗНАЧ!.
Если значение «степени_свободы» не является целым, оно усекается.
Если deg_freedom t) = вероятность, где X — случайная величина, соответствующая t-распределению, и P(|X| > t) = P(X t).
Одностороннее t-значение может быть получено при замене аргумента «вероятность» на 2*вероятность. Для вероятности 0,05 и 10 степеней свободы двустороннее значение вычисляется по формуле СТЬЮДРАСПОБР(0,05;10) и равно 2,28139. Одностороннее значение для той же вероятности и числа степеней свободы может быть вычислено по формуле СТЬЮДРАСПОБР(2*0,05;10), возвращающей значение 1,812462.
Примечание: В некоторых таблицах вероятность описана как (1-p).
Если задано значение вероятности, то функция СТЬЮДРАСПОБР ищет значение x, для которого функция СТЬЮДРАСП(x, степени_свободы, 2) = вероятность. Однако точность функции СТЬЮДРАСПОБР зависит от точности СТЬЮДРАСП. В функции СТЬЮДРАСПОБР для поиска применяется метод итераций. Если поиск не закончился после 100 итераций, функция возвращает значение ошибки #Н/Д.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Вероятность, соответствующая двустороннему распределению Стьюдента.
t-статистика Стьюдента в Excel
Большинству из нас хорошо знакома колоколообразная кривая нормального распределения. Она отлично работает, когда выборки большие, но занижает значения на «хвостах», когда выборки малые. Для описания статистики малых выборок была разработана t-статистика Стьюдента. Она также симметрична и подчиняется колоколообразному распределению, но дает лучшую оценку для малых выборок. В отличие от нормального распределения t-статистика не одна, а представлена целым семейством распределений. Дополнительный параметр – размер выборки или число степеней свободы.
Рис. 1. Нормальная кривая и кривые t-распределения; df – число степеней свободы (от англ. degrees of freedom); gif-файл создан с помощью бесплатного сервиса ezgif.com, на который меня навела Евгения Крюкова
Скачать заметку в формате Word или pdf, примеры в формате Excel
Подход пивовара
В начале XX века Уильяму Сили Госсету, химику и статистику ирландской пивоваренной компании Guinness, потребовалось установить, какой из двух сортов ячменя дает лучшее пиво с большим выходом.[1] Ранее была разработана статистика нормального распределения, позволяющая находить доверительный интервал на основе случайной выборки, состоящей из не менее чем 30 объектов. К сожалению, у Госсета не было возможности протестировать большое число партий пива, изготовленных из каждого сорта ячменя. Однако он не отказался от своей затеи измерить то, что как будто не поддавалось оценке, и решил вывести новый вид распределения для крайне малых выборок. К 1908 г. Госсет разработал новый эффективный метод, который назвал t-статистикой, и захотел опубликовать результаты своей работы.
Однако у Guinness уже были проблемы с разглашением коммерческой тайны, и служащим компании было запрещено публиковать любую информацию о бизнес-процессах. Госсет понимал значение своей работы. Ему сильнее хотелось рассказать о своей идее, чем добиться немедленного признания. Поэтому он опубликовал статью под псевдонимом Стьюдент. И хотя истинный автор давно известен, практически во всех работах по статистике метод называется t-статистикой Стьюдента.
От физических значений к z-статистике
Колоколообразная кривая нормального распределения описывается формулой:
где f(X) – вероятность значения Х; f(X) откладывается по оси ординат; е — основание натурального логарифма; μ — математическое ожидание генеральной совокупности, σ — стандартное отклонение генеральной совокупности, X — произвольное значение непрерывной случайной величины; X откладывается по оси абсцисс; –∞ 53,8). Мы можем отвергнуть нулевую и принять альтернативную гипотезу – продвижение сайтов влияет на посещаемость (рис. 13).
Рис. 13. Попадание среднего значения внутрь α-уровня позволяет отвергнуть нулевую гипотезу
Но, значение альфа-уровня полностью контролируется нами — это наше правило принятия решений. Если бы мы установили для альфа-уровня значение 0,01, мы бы не отвергли нулевую гипотезу (рис. 14). Мы могли бы сказать, что среднее экспериментальной группы происходит из той же генеральной совокупности, что и среднее контрольной группы. Итак, статистическая ошибка I рода: мы отвергаем нулевую гипотезу, когда она верна.
Рис. 14. Для α-уровня = 0,01 среднее экспериментальной группы не позволяет отвергнуть нулевую гипотезу
Что же мешает установить α-уровень достаточно маленьким, и не отвергать нулевую гипотезу, когда она верна? …Ошибка II рода, заключающаяся в том, что мы не принимаем альтернативную гипотезу, хотя она верна.
Ошибка второго рода
Представьте, что существуют две генеральные совокупности: одна из них состоит из сайтов, не получающих специального продвижения, вторая — из сайтов, получающих продвижение. Если вы повторите свое месячное исследование сотни или даже тысячи раз, то, возможно, получите две колоколообразные кривые (рис. 15).
Рис. 15. Ошибка второго рода при α-уровне = 0,05
Иногда среднее значение экспериментальной группы будет происходить из правого хвоста левой кривой распределения исключительно из-за выборочной ошибки. Поскольку в данном случае среднее экспериментальной группы превышает среднее контрольной группы более чем на 1,75 стандартной ошибки (попадает в область α-уровня = 0,05), вы должны отвергнуть нулевую гипотезу, даже если обе генеральные совокупности в действительности имеют одно и то же среднее. Такое неверное отбрасывание нулевой гипотезы, когда в действительности она является истинной, мы назвали ошибкой I рода. В терминах рис. 15 ошибка I рода – приписывание среднего результата экспериментальной выборки, равное 55, правой кривой, а не хвосту левой кривой.
Кривая слева представляет генеральную совокупность веб-сайтов, не получающих специального продвижения. На протяжении месяца частота посещений для некоторых из этих сайтов (очень немногих) составит всего 25 посещений в час, тогда как для других, столь же немногочисленных, – 62 посещения в час. Но 90% всех средних показателей выборок лежат в диапазоне 36,2–53,8 посещений в час.
Кривая справа представляет специально продвигаемые сайты. Как правило, показатели для них примерно на 10 посещений в час выше, чем для сайтов, представленных кривой слева. Их общее среднее составляет 55 посещений в час. Однако большая часть этой информации скрыта от вас. У вас отсутствуют данные о генеральной совокупности, и вы располагаете только результатами двух извлеченных вами выборок, но и этого вам будет вполне достаточно.
Рассмотрим правую кривую на рис. 15. Площадь под этой кривой от минимальных до критического значения (равного 53,8) выделена ярко голубым. Она определяет вероятность ошибки II рода. Средние выборок, проистекающие из этой области, мы относим к левой кривой, а не к правой. Для количественной оценки вероятности ошибки II рода найдем t-значение границы для правой кривой по формуле (7):
Вероятность того, что значение относится к правой кривой и лежит в диапазоне t-значений от –∞ до –0,247 определяется формулой =СТЬЮДЕНТ.РАСП(-0,247;15;ИСТИНА) = 0,404. Т.е., при выбранном нами α-уровне = 0,05 с вероятностью 40,4% мы отклоним альтернативную гипотезу, хотя она верна!
Что произойдет, если мы выберем α-уровень = 0,01, как на рис. 14? Вероятность ошибки II рода увеличится до 72,2% (рис. 16).
Рис. 16. Ошибка второго рода при α-уровне = 0,01
Статистическая мощность
Вероятность, количественно определяющую величину ошибки второго рода, называют β (на рис. 16 β = 72,2%). А вероятность р = (1 – β) – статистической мощностью (рис. 17).
Рис. 17. Статистическая мощность исследования при α-уровне = 0,05
Чем выше статистическая мощность, тем больше вероятность того, что мы отклоним нулевую гипотезу и примем альтернативную гипотезу. На мощность влияют четыре основных фактора:
Давайте на нашем примере рассмотрим, как каждый из указанных факторов изменяет мощность, считая, что факторы меняться по одному (рис 18; расчеты и формулы можно найти в файле Excel).
Рис. 18. Методы увеличения статистической мощности: а) базовый вариант; б) ненаправленная гипотеза; в) увеличение α с 0,05 до 0,1; г) увеличение разницы между средними экспериментальной и контрольной групп с 10 до 13 посещений в час; д) увеличение размера групп с 16 до 24 сайтов.
Первый метод повышения статистической мощности связан с подготовкой эксперимента. Если вместо ненаправленной гипотезы (двуххвостовой тест) использовать направленную (однохвостовой тест), вся величина α-уровня отнесется к одному хвосту распределения (сравните рис. 18а и 18б). В результате критическое значение сместится в сторону среднего значения распределения. Чем ближе критическое значение к среднему, тем более вероятно, что вы получите результат, превышающий критическое значение, что увеличивает статистическую мощность тестов. В нашем примере, мощность возрастет с 44,9% до 59,6%.
Второй метод повышения мощности теста предлагает ослабить α-уровень. Например, увеличивая α от 0,05 до 0,10, вы увеличиваете вероятность совершения ошибки I рода, но уменьшаете вероятность совершения ошибки II рода (сравните рис. 18а и 18в). В нашем примере, мощность возросла с 59,6% до 74%.
Оставшиеся два метода повышения статистической мощности основаны на формуле расчета t-статистики:
где t – t-значение для среднего выборки (а не для индивидуального значения), X̅ – среднее значение выборки, μ – среднее значение генеральной совокупности (или среднее значение контрольной выборки), 
– стандартная ошибка средних по выборкам (а не индивидуальных значений), равная:
где s – стандартная ошибка индивидуальных значений, n – размер выборки.
Для увеличения t-статистики (и, как следствие, статистической мощности) нужно, либо увеличить числитель, либо уменьшить знаменатель в формуле (8). Для увеличения разности X̅ – μ требуется внесение изменений в проведение эксперимента. Как это сделать, непростой вопрос, решаемый в каждом конкретном случае. В нашем примере, увеличение X̅ с 55 до 58 посещений в час при неизменном μ = 45, приведет к росту статистической мощности с 59,6% до 79,5% (рис. 18г).
Один из хороших способов познакомиться с влиянием разных факторов на мощность – это поэкспериментировать с графическим калькулятором мощности, например, здесь.
Основные положения заметки
t-статистика Стьюдента используется вместо нормального распределения: а) для малых выборок; б) если стандартное отклонение генеральной совокупности σ не известно.
t-распределение представлено семейством распределений; дополнительный параметр – размер выборки или число степеней свободы.
Число степеней свободы равно размеру выборки минус число фиксированных статистик выборки (среднее, коэффициент регрессии, …)
Чем больше степеней свободы, тем ближе t-распределение к нормальному.
Функции в Excel, имена которых включают часть РАСП, принимают t-значение в качестве аргумента и возвращают вероятность. Функции, имена которых включают часть ОБР, принимают значение вероятности в качестве аргумента и возвращают t-значение.
Для подстановки в функции Excel значения предварительно должны быть стандартизованы.
Ошибка I рода: отбрасывание нулевой гипотезы, когда в действительности она является истинной. Ошибка II рода: не принятие альтернативной гипотезы, хотя она верна. Чем больше ошибка первого рода, тем меньше ошибка второго рода.
Критерий отнесения события к маловероятному является произвольным. Традиционно маловероятным считают событие, происходящее не чаще, чем 1 раз из 20 попыток.
Один из основных методов уменьшения ошибки второго рода – увеличение элементов в выборке.
[1] При написании замети использованы материалы книг: Дуглас Хаббард. Как измерить всё, что угодно, Левин и др. Статистика для менеджеров, Сара Бослаф. Статистика для всех, Конрад Карлберг. Регрессионный анализ в Microsoft Excel.
[2] На самом деле, можно встретить довольно много различных терминов в отношении нормированных значений z. Ориентируйтесь не на названия, а на суть понятий.
[3] Могут высказать справедливое замечание, что не следует обозначать граничный уровень так же, как и математическое ожидание генеральной совокупности µ. Соглашусь, но всё же использую обозначение, поскольку, в иных задачах здесь часто фигурирует именно математическое ожидание генеральной совокупности µ.
[4] Некоторые авторы указывают знак меньше или равно для левостороннего распределения Р(Х≤t), и больше для правостороннего Р(X>t). Однако, для t=0 значения СТЬЮДЕНТ.РАСП(0;df;ИСТИНА) = СТЬЮДЕНТ.РАСП.ПХ(0;df) = 0,5 для любого значения df. На мой взгляд, Excel при интегральном расчете трактует границу, как исчезающе малую. Поэтому, нет разницы, использовать знак ≥ или >.