Расчет критерия стьюдента в excel

Как пользоваться функцией распределения Стьюдента СТЮДРАСПОБР В EXCEL

Функция имеет следующий синтаксис:

вероятность – обязательный для заполнения, принимает числовое значение вероятности для двустороннего распределения Стьюдента из диапазона от 0 (не включительно) до 1.
степени_свободы – обязательный для заполнения, принимает числовое значение степеней свободы, которые определяют исследуемое распределение.

Если один из аргументов функции указан в виде значения нечислового типа данных, результатом выполнения рассматриваемой функции будет код ошибки #ЗНАЧ!. Логические значения, имена и текстовые строки, преобразуемые в числа, не приводят к возникновению ошибки. Например, функция =СТЮДРАСПОБР(“0,4”;ИСТИНА) вернет значение 1,32638.
Если аргумент вероятность задан числом, не находящимся в промежутке от 0 (не включительно) до 1, функция СТЮДРАСПОБР вернет код ошибки #ЧИСЛО!. Аналогичная ошибка возникает, если аргумент степени_свободы задан числом, которое меньше 1.
Для расчета односторонней t-величины следует в качестве аргумента вероятность указать значение удвоенной вероятности.

Алгоритм работы

_iСервис/Анализ данных/РегрессияxyМетки

Среднее значение: СРЗНАЧ(диапазон)
Квадратическое отклонение: КВАДРОТКЛ(диапазон)
Дисперсия: ДИСП(диапазон)
Дисперсия для генеральной совокупности: ДИСПР(диапазон)
Среднеквадратическое отклонение: СТАНДОТКЛОН(диапазон)
Уравнение регрессии y = b₁x₁+b₂x₂+. b_nx_n+b: ЛИНЕЙН(диапазон Y;диапазон X;1;1) .

Выделите блок ячеек размером (n+1) столбцов и 5 строк.

6. Коэффициенты множественной линейной регрессии вычисляются с помощью функции ЛИНЕЙН . Для того чтобы использовать эту функцию для вычисления параметров множественной регрессии необходимо 1) Сначала выделить на рабочем листе область размером 5x(k+1), где k — число объясняющих переменных. 2) Затем заполнить поля аргументов этой функции, которые имеют тот же смысл, что и в случае парной регрессии: Известные_значения_y — адреса ячеек, содержащих значения признака y; Известные_значения_x — адреса ячеек, содержащих значения всех объясняющих переменных

Обратите внимание: выборочные значения факторов должны располагаться рядом друг с другом (в смежной области), причем предполагается, что в первом столбце (строке) содержатся значения первой объясняющей переменной, во втором столбце — второй и т.д. Константа — значение (логическое), указывающее на наличие свободного члена в уравнении регрессии: укажите в поле Константа значение 1, тогда свободный член рассчитывается обычным образом (если значение поля Константа равно 0, то свободный член полагается равным 0); Статистика — значение (логическое), которое указывает на то, следует ли выводить дополнительную информацию по регрессионному анализу или нет: укажите в поле Статистика значение равное 1, тогда будет выводиться дополнительная регрессионная информация (если Статистика=0, то выводятся только оценки коэффициентов уравнения регрессии);

Критерий Фишера предназначен для сопоставления двух выборок по частоте встречаемости интересующего исследователя эффекта.

При увеличении расхождения между углами φ1 и φ2 и увеличения численности выборок значение критерия возрастает. Чем больше величина φ*, тем более вероятно, что различия достоверны.

Гипотезы критерия Фишера

H: Доля лиц, у которых проявляется исследуемый эффект, в выборке 1 не больше, чем в выборке 2.

H₁: Доля лиц, у которых проявляется исследуемый эффект, в выборке 1 больше, чем в выборке 2.

Построение графика нормального распределения

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

Далее строим таблицу с категориями. Нижняя граница (B11) равняется округленному вниз ближайшему кратному числу. Остальные категории увеличиваются на значение шага. Формула в ячейке B12 и последующих будет выглядеть:

В столбце X будет производится подсчет количества переменных в заданном промежутке. Для этого воспользуемся формулой ЧАСТОТА(), которая имеет два аргумента: массив данных и массив интервалов. Выглядеть формула будет следующим образом =ЧАСТОТА(Data!A1:A175;B11:B20). Также стоит отметить, что в таком варианте данная функция будет работать как формула массива, поэтому по окончании ввода необходимо нажать сочетание клавиш Ctrl+Shift+Enter.

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

Осталось отформатировать диаграмму и наш график с нормальным распределением готов.

Итак, мы познакомились с вами с нормальным распределением, узнали, что Excel позволяет генерировать массив данных с помощью формулы НОРМ.ОБР() для определенного среднего значения и стандартного отклонения и научились приводить данный массив в графический вид.

Студенческий T-Distribution

T-дистрибутив Студента, или просто t-дистрибутив, для псевдонима «Студент» назван Уильямом Сили Госсетом.

Это распределение, которое возникает при попытке оценить среднее нормального распределения с выборками разных размеров

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

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

Распределение может быть описано с использованием одного параметра:

количество степеней свободы: обозначается строчной греческой буквой nu (v), обозначает число степеней свободы.

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

Число степеней свободы описывает количество единиц информации, используемых для описания количества населения. Например, среднее имеетNстепени свободы как всеNнаблюдения в выборке используются для расчета оценки среднего населения. Статистическая величина, которая использует другую статистическую величину в своем расчете, должна вычесть 1 из степеней свободы, таких как использование среднего значения в расчете дисперсии выборки.

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

кудаИксэто наблюдения из гауссовского распределения,имею в видуэто среднее наблюдениеИксS — стандартное отклонение иNобщее количество наблюдений. Полученные наблюдения образуют t-наблюдение с (n — 1) степени свободы.

На практике, если вам требуется значение из t-распределения при расчете статистики, то число степеней свободы, скорее всего, будетn — 1, гдеNэто размер вашей выборки, взятой из гауссовского распределения.

В приведенном ниже примере создается t-распределение с использованием выборочного пространства от -5 до 5 и (10 000 — 1) степеней свободы.

Выполнение примера создает и строит график t-дистрибутива PDF.

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

t.cdf ()Функция может быть использована для создания кумулятивной функции плотности для t-распределения. Пример ниже создает CDF в том же диапазоне, что и выше.

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

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

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

Рассмотрим, для примера, данные, имеющие выраженный скос вправо, как у распределения хи-квадрат с 5-ю степенями свободы.

Теперь создадим 20 тысяч выборок и будет наблюдать, как меняется распределение средних в зависимости от их объема.

Отличие довольно заметно в малых выборках до 15-20-ти наблюдений. Но дальше оно стремительно исчезает. Таким образом, ненормальность распределения – это, конечно, нехорошо, но некритично.

Больше всего t-критерий «боится» выбросов, т.е. аномальных отклонений. Возьмем 20 тыс. нормальных выборок по 15 наблюдений и в часть из них добавим по одному случайном выбросу.

Картина получается нерадостная. Фактические частоты средних сильно отличаются от теоретических. Использование t-распределения в такой ситуации становится весьма рискованной затеей.

Итак, в не очень малых выборках (от 15-ти наблюдений) t-критерий относительно устойчив к ненормальному распределению исходных данных. А вот выбросы в данных сильно искажают распределение t-критерия, что, в свою очередь, может привести к ошибкам статистического вывода, поэтому от аномальных наблюдений следует избавиться. Часто из выборки удаляют все значения, выходящие за пределы ±2 стандартных отклонения от средней.

Регрессионный анализ в Excel

Показывает влияние одних значений (самостоятельных, независимых) на зависимую переменную. К примеру, как зависит количество экономически активного населения от числа предприятий, величины заработной платы и др. параметров. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.

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

линейной (у = а + bx);
параболической (y = a + bx + cx 2 );
экспоненциальной (y = a * exp(bx));
степенной (y = a*x^b);
гиперболической (y = b/x + a);
логарифмической (y = b * 1n(x) + a);
показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

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

Модель линейной регрессии имеет следующий вид:

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

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

Активируем мощный аналитический инструмент:

Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».
Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.
Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».
Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.
После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

В первую очередь обращаем внимание на R-квадрат и коэффициенты. R-квадрат – коэффициент детерминации

В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо»

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.

Обратная функция t-распределения

Обратная функция используется для вычисления альфа – квантилей , т.е. для вычисления значений x при заданной вероятности альфа , причем х должен удовлетворять выражению P{X альфа .

Функция СТЬЮДЕНТ.ОБР() используется для вычисления как двухсторонних, так и односторонних доверительных интервалов . А функции СТЬЮДЕНТ.ОБР.2Х() и СТЬЮДРАСПОБР() созданы специально для вычисления квантилей , необходимых для расчета двусторонних доверительных интервалов: в качестве аргумента нужно указывать уровень значимости альфа , а не альфа/2 , как для СТЬЮДЕНТ.ОБР() .

Вышеуказанные функции можно взаимозаменять, т.к. нижеуказанные формулы возвращают одинаковый результат: =СТЬЮДЕНТ.ОБР(альфа;n) =-СТЬЮДРАСПОБР(альфа*2;n) =-СТЬЮДЕНТ.ОБР.2Х(альфа*2;n)

Некоторые примеры расчетов приведены в файле примера на листе Функции .

Примечание : Ниже приведено соответствие русских и английских названий функций: СТЬЮДЕНТ.РАСП.ПХ() – англ. название T.DIST.RT, т.е. T-DISTribution Right Tail, the right-tailed Student’s t-distribution СТЬЮДЕНТ.РАСП.2Х() – англ. название T.DIST.2T, т.е. T-DISTribution 2 Tails СТЬЮДЕНТ.ОБР() – англ. название T.INV, т.е. T-distribution INVerse СТЬЮДРАСП() – англ. название TDIST, т.е. T-DISTribution СТЬЮДРАСПОБР() – англ. название TINV, т.е. T-distribution INVerse (the right-tailed inverse of the Student’s t-distribution) СТЬЮДЕНТ.ОБР.2Х() – англ. название T.INV.2T

FРАСПОБР (функция FРАСПОБР)

раздел «Анализ данных», 23 предприятий о:расч10 с применяемыми функциями

799 000 000,00 ₽ математического описания функцииЗнаменатель степеней свободы определить критическое значениеВероятность0,9729551 используется для проверки вычисления верхнего квантиля. правостороннюю вероятность, т.е.В файле примера на2

Определение где можно произвести X — ценаРегрессияСтандартное отклонение для rxy в пакете Excel85 000 000,00 ₽ ФИШЕР, имеет вид:Формула F, нужно использовать — обязательный аргумент.Возвращает значение, обратное (правостороннему) гипотез с помощью Т.е. если в P(X>x)). Функция FРАСП() листе График приведены

), из которых сделаны: Если U математическую статистику. Мне на товар А,

Синтаксис

=КОРЕНЬ((1-C8^2)/4) приведены на рисунке

Схема решения таких задачZ’=1/2*ln(1+x)/(1-x)Описание уровень значимости как

 Вероятность, связанная с F-распределению вероятностей. Если коэффициента корреляции.

 качестве аргумента функции оставлена в MS графики плотности распределения

Замечания

выборки размером n1 нужно расчитать критерий тыс. руб.; Y

227,407Таким образом, с вероятностью 1.

выглядит следующим образом:Рассмотрим применение данной функцииРезультат аргумент «вероятность» функции

интегральным F-распределением. p = FРАСП(x;. ),ФИШЕР(x) указан уровень значимости, EXCEL 2010 для

вероятности и интегральной1и U Фишера. Его можно — прибыль торгового

7,075 0,95 линейный коэффициентРисунок 1 – ПримерРассчитывается линейный коэффициент корреляции на 3-x конкретных=FРАСПОБР(A2;A3;A4) FРАСПОБР.Степени_свободы1 то FРАСПОБР(p;. ) =Аргументы функции ФИШЕР описаны например 0,05, то совместимости. Аналогом FРАСП() функции распределения.и n

2 вычислить двумя способами. предприятия, млн. руб,Остаток корреляции заключен в расчетов. r примерах.Значение, обратное F-распределению вероятностейПо заданному значению вероятности — обязательный аргумент. x. ниже. функция вернет такое

Пример

является функция F.РАСП.ПХ(),Примечание2независимые случайные величины, Есть раздел «Регрессия» производится изучение их1607,014 интервале от (–0,386)№ п/пxy для приведенных выше функция FРАСПОБР ищет

 Числитель степеней свободы.

F-распределение может использоваться в

значение случайной величины появившаяся в MS

: Для построения функции

имеющие ХИ2-распределение с

и «Однофакторный дисперсионный

 зависимости. Оценка регрессионной

;Пример 1. Используя данные данных

Инструменты Excel для построения интервальных оценок параметров распределений

Все, рассмотренные в этом разделе инструменты вычисляют значения квантилей как значения функций, обратных соответствующим функциям распределения. Все эти функции – библиотечные функции Excel из группы функций «Статистические»,.

Функция вычисления критических точек распределения Лапласа

Функция возвращает (вычисляет) значения квантили уровня, равного значению, введенному в поле «Вероятность» (понятно, что это число из промежутка (0б 1)) стандартного нормального распределения.

Функция вычисления критических точек распределения Стьюдента

Функция возвращает (вычисляет) значения квантили уровня, равного значению, введенному в поле «Вероятность» (понятно, что это число из промежутка (0б 1)) распределения Стьюдента с числом степеней свободы, равным значению, введенному в поле «Степени свободы» (понятно, что это натуральное число).

Важно знать, что функция Excel СТЬЮДРАСПОБР( p , k ) возвращает значение t , при котором P (| x | > t ) = p , x — значение случайной величины, имеющей распределение Стьюдента с k степенями свободы. Поэтому решение уравнения

Поэтому решение уравнения

Функция вычисления критических точек распределения

Функция возвращает (вычисляет) значения квантили уровня, равного значению, введенному в поле «Вероятность» (понятно, что это число из промежутка (0б 1)) распределения

В Excel функция распределения случайной величины определена нестандартно: F _x ( x ) = P ( x > x ). Поэтому для вычисления квантиля

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

Если функция распределения F (х) случайной величины х непрерывна, то 1-й квартиль является решением уравнения F(х) =0,25, второй – F(х) =0,5, а третий F(х) =0,75.

Примечание : Подробнее о Функции распределения см. статью Функция распределения и плотность вероятности в MS EXCEL .

Если известна функция плотности вероятности p (х) , то 1-й квартиль можно найти из уравнения:

Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана (2-й квартиль ) вычисляется по формуле e μ или в MS EXCEL =EXP(μ). При μ=1, медиана равна 2,718.

Обратите внимание на точку Функции распределения , для которой F(х)=0,5 (см. картинку выше или файл примера , лист Квартиль-распределение)

Абсцисса этой точки равна 2,718. Это и есть значение 2-го квартиля ( медианы ), что естественно совпадает с ранее вычисленным значением по формуле e μ .

Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице:

Поэтому, линии квартилей ( х=квартиль ) делят площадь под графиком функции плотности вероятности на 4 равные части.

Статистический анализ роста доли дохода в Excel за период

Пример 2. В таблице приведены данные о доходах предпринимателя за год. Доказать, что примерно 75% значений меньше, чем третий квартиль доходов.

Вид исходной таблицы:

Определим 3-й по формуле:

Определим соотношение чисел, меньше полученного числа, к общему количеству значений по формуле:

=СЧЁТЕСЛИ(B2:B13;” Пример 3. Имеется диапазон случайных чисел, отсортированный в порядке возрастания. Определить соотношение суммы чисел, которые меньше 1-го квартиля, к сумме чисел, которые превышают значение 1-го квартиля.

Чтобы сгенерировать случайное число в Excel воспользуемся функцией:

После генерации отсортируем случайно сгенерированные числа по возрастанию. Вид исходной таблицы данных со случайными числами:

Формула для расчета имеет следующий вид (формула массива CTRL+SHIFT+ENTER):

Функции СУММ с вложенными функциями ЕСЛИ выполняют расчет суммы только тех чисел, которые меньше и больше соответственно значения, возвращаемого функцией для исследуемого диапазона. Из полученных значений вычисляется частное. Результат расчетов:

Общая сумма чисел исследуемого диапазона, которые меньше 1-го квартиля, составляет всего 8,57% от общей суммы чисел, которые больше 1-го квартиля.

Расчет квартилей в R и SAS

Функция quantile в R использует все девять алгоритмов расчета квантилей, в соответствии с нумерацией, предложенной Hyndman and Fan в работе 1996 г. (рис. 15; если вы не знакомы с R, рекомендую начать с Алексей Шипунов. Наглядная статистика. Используем R! ). Квантиль при i-м методе расчета: