Как считать квартиль в excel

3

Примеры функции КВАРТИЛЬ в Excel для расчета медианы квартиля

Функция КВАРТИЛЬ в Excel используется для расчета квартиля диапазона числовых данных и возвращает соответствующее числовое значение.

Функция КВАРТИЛЬ.ВКЛ вычисляет на основе указанной процентили в качестве второго аргумента функции. Полностью соответствует первой функции. Последняя используется в Excel 2007 и более ранних версиях и оставлена для совместимости.

Функция КВАРТИЛЬ.ИСКЛ используется для расчета квартили диапазона числовых значений на основе известной процентили, за исключением граничных значений (минимального и максимального значения в диапазоне).

Квартили используются для распределения диапазона чисел на четыре равные части:

1. Первый квартиль является числом из диапазона исследуемых значений, которое делит данный диапазон на две части так, что около 25% данного диапазона являются числами, которые меньше первого квартиля, а остальные (75%) – больше. Рассматриваемые функции могут возвращать результат интерполяции двух соседних значений из диапазона.
2. Второй квартиль эквивалентен медиане выборки (исследуемого числового диапазона), то есть числовому значению, которое делит диапазон на две части: 50% чисел меньше медианы, остальные 50% чисел больше медианы. Так, запись =КВАРТИЛЬ.ВКЛ(A1:A10;2) возвращает значение, эквивалентное результату вычисления функции =МЕДИАНА(A1:A10), при условии, что ячейки из диапазона A1:A10 содержат числовые значения.
3. Третий квартиль – числовое значение, делящее диапазон на две части, в первой из которой содержатся 75% чисел диапазона, которые меньше полученного значения, а во второй (25%) – больше.

Функция КВАРТИЛЬ.ВКЛ может быть использована не только для определения медианы выборки (второго квартиля), а и нахождения минимального и максимального значений соответственно. При работе с большими диапазонами чисел для подобных расчетов рекомендуется использовать функции МИН и МАКС соответственно.

Существует несколько алгоритмов расчета квартилей. Все рассмотренные функции используют следующую формулу:

• Q_p – p-й квантиль (является частным случаем квантиля);
• x – индекс квантиля;
• i – индекс элемента из выборки;
• A₁,A₂…A_i – элементы выборки, отсортированной по возрастанию значений.

Для расчета индекса квантиля (x) функция КВАРТИЛЬ.ВКЛ используют формулу:

x=(n-1)p, где n – количество элементов в диапазоне.

Функция КВАРТИЛЬ.ИСКЛ использует формулу x=(n+1)p.

В Excel принято так, что первые выше указанные 2 функции используют метод N-1-интерполяцию, а третья функция – N+1-интерполяцию.

Примеры использования функций КВАРТИЛЬ в Excel

Пример 1. В столбце таблицы содержится числовая последовательность. Определить число, которое делит последовательность на 2 части, 25% первой – числа меньше полученного значения, а 75% — больше. Использовать N+1-интерполяцию.

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

Для определения 1-го квартиля используем функцию:

• A2:A15 – диапазон ячеек с исследуемыми числами;
• 1 – номер вычисляемого квартиля.

Проверим утверждение о том, что второй квартиль соответствует медиане выборке. Определим 2-й по формуле:

Полученные значения совпадают:

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

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

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

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

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

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

Анализ статистики случайно сгенерированных чисел в Excel

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

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

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

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

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

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

Квартиль в excel

Для вычисления квартилей в MS EXCEL существует специальная функция КВАРТИЛЬ() . В этой статье дадим определение квартилей и научимся их вычислять для выборки и для непрерывного распределения. Также вычислим интерквартильный интервал.

Квартили (Quartiles) — значения, которые делят выборку (набор значений) на четыре части, содержащие приблизительно равное количество наблюдений (по 25%).

Поясним определение квартиля на примере. Пусть имеется выборка , состоящая из 50 значений в ячейках А7:А56 (см. файл примера , лист Квартиль-выборка). Для наглядности отсортируем значения по возрастанию и построим гистограмму .

Чтобы разделить выборку на 4 части достаточно 3-х квартилей .

Первый квартиль (или нижний квартиль , Q1) делит выборку , на 2 части: примерно 25% значений в выборке меньше Q1, остальные 75% — больше. Для вычисления 1-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;1) . Для нашей выборки формула вернет значение 224. Значения 224 нет в выборке , формула произвела интерполяцию на основе 2-х соседних значений 223 и 227.

Примечание : Функция КВАРТИЛЬ.ВКЛ() появилась в MS EXCEL 2010. В более ранних версиях использовалась аналогичная ей функция КВАРТИЛЬ() .

Чтобы убедиться, что примерно 25% значений меньше, чем 224, используем формулу =СЧЁТЕСЛИ(A7:A56;» 2 ), получим, что медиана (2-й квартиль ) вычисляется по формуле e μ или в MS EXCEL =EXP(μ). При μ=1, медиана равна 2,718.

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

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

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

Квартили в MS EXCEL

Чтобы вычислить в MS EXCEL квартили заданного распределения необходимо использовать соответствующую обратную функцию распределения .

При вычислении квартилей в MS EXCEL используются обратные функции распределения : НОРМ.СТ.ОБР() , ЛОГНОРМ.ОБР() , ХИ2.ОБР() , ГАММА.ОБР() и т.д. Подробнее о распределениях, представленных в MS EXCEL, можно прочитать в статье Распределения случайной величины в MS EXCEL .

Например, в MS EXCEL 1-й квартиль для логнормального распределения LnN(1;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,25;1;1) , а 3-й квартиль для стандартного нормального распределения по формуле =НОРМ.СТ.ОБР(0,75) .

КВАРТИЛЬ: какие формулы расчета использует Excel

Квартиль — одна из статистик, используемая при описании выборок (подробнее о различных статистиках см. Определение среднего значения, вариации и формы распределения. Описательные статистики). В то время как медиана разделяет упорядоченный массив пополам, квартили разбивают набор данных на четыре части. Первый квартиль – это число, разделяющее выборку на две части: 25% элементов меньше, а 75% — больше значения первого квартиля. Третий квартиль — это число, разделяющее выборку также на две части: 75% элементов меньше, а 25% — больше третьего квартиля.

Рис. 1. 5-числовые сводки: М – медиана, Н1 и Н2 – сгибы (они же квартили)

Скачать заметку в формате Word или pdf, примеры в формате Excel (файл содержит код VBA).

Для расчета квартилей в Excel2007 и более ранних версиях использовалась функция КВАРТИЛЬ. Начиная с версии Excel2010 применяются две функции: КВАРТИЛЬ.ВКЛ и КВАРТИЛЬ.ИСКЛ (функция КВАРТИЛЬ оставлена для совмещения с более ранними версиями Excel; эта функция возвращает те же значения, что и КВАРТИЛЬ.ВКЛ). Эти две функции возвращают различные значения, но я нигде не нашел, какой алгоритм они используют при расчетах. Замечу, что для корректной работы функций данные можно не упорядочивать.

Изучение литературы показало, что в отличие от большинства других статистик, единодушия в методике расчета квартилей нет)) Я нашел упоминание о девяти различных подходах…

Начнем с метода Джона Тьюки, описанного им в, уже ставшем классическом, труде Анализ результатов наблюдений. Разведочный анализ, изданном в 1977 г. Он начинает с введения трех сводок, характеризующих выборку: минимальное, максимальное значения и медиана. Далее он продолжает: «Если мы хотим добавить еще два числа, чтобы образовать 5-числовую сводку, то естественно определять их подсчетом до половины расстояния от каждого из концов к медиане. Процесс нахождения медианы, а затем и этих новых значений можно представить себе, как складывание листа бумаги. Поэтому эти новые значения естественно назвать сгибами» (англ. – hinge; рис. 1). Мы их называем квартилями.

Такие рисунки выглядят очень аккуратно, если число элементов выборки N = 4k + 1, например, 9, 13, 17… Но как быть, если в выборке 12 или 19 элементов? Наглядную картину представил Jon Peltier в серии заметок в своем блоге. Упорядочим элементы случайной выборки и разместим их над линейкой (рис. 2; случайная выборка, элементы которой упорядочены называется вариационным рядом). Серые числа под линейкой – индекс ряда (Джон зачем-то в качестве выборки – над линейкой – взял ряд целых чисел; наверное, чтобы запутать нас). Красное число над рядом – значение сводки; если оно дробное, значит полученное значение является интерполяцией между соседними значениями. Мы определяем медиану, как среднее значение набора данных, а первую квартиль – как медиану нижней половины данных.

Рис. 2. Инклюзивные квартили

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

Эксклюзивный подход. Некоторым статистикам не нравится, что медиана учитывается дважды. Они решили, что сгибы должны быть определены как медианы верхней и нижней половин набора данных, из которых срединное значение исключено (рис. 3). Такой взгляд отстаивали Moore и McCabe, или кратко M&M. Если набор данных содержит четное количество значений, инклюзивные и эксклюзивные квартили равны, так как нет элемента выборки (соответствующего центральной медиане), который можно было бы включить или исключить из рассмотрения. Для нечетного числа элементов, инклюзивные сгибы всегда ближе к медиане.

Рис. 3. Эксклюзивные квартили

Третий подход – компромисс между Тьюки и М&M – называется Эмпирическая функции распределения или Интегральная функция распределения (английская аббревиатура CDF). В случае нечетного числа значений в наборе данных, следует включить или исключить медиану, ориентируясь на то, чтобы оставшиеся половинки содержали нечетное число элементов. Например, если в выборке 9 элементов, медиану следует включить, а при 11 элементах – исключить. В обоих случаях половинки будут содержать по 5 элементов. Преимущество этого компромисса заключается в том, что в качестве значения квартиля всегда получается один из элементов набора данных (а не среднее значение двух соседних элементов). CDF является методом по умолчанию в статистическом пакете SAS.

Все возможные случаи N. Мы не всегда можем изобразить данные в W-образной форме, как на рис. 1, поэтому удобнее пользоваться линейкой. В общем случае возможны четыре варианта по числу элементов в выборке: N = 4k, N = 4k + 1, N = 4k + 2, N = 4k + 3… и три подхода к расчету квартилей: Тьюки, M&M, CDF (рис. 4–7).

Рис. 4. Число элементов в выборке N = 4k; все три метода дают одинаковые значения квартилей

Рис. 5. Число элементов в выборке N = 4k + 1; M&M дает значения, отстоящие дальше от медианы

Рис. 6. Число элементов в выборке N = 4k + 2; все три метода дают одинаковые значения квартилей

Рис. 7. Число элементов в выборке N = 4k + 3

Методы интерполяции. Помимо трех описанных выше методов, применяют и целый ряд индексных алгоритмов. Мы рассмотрим три из них. Первый индекс во всех методах равен 0, а последний – N–1, N, N + 1. Например, для N=8 индексированные ряды представлены на рис. 8.

Рис. 8. Индексные ряды на основе N–1, N и N + 1 для N = 8

Положение перцентиля р – доля длины индексной линии, или р(N–1), рN, р(N+1), соответственно. р = 0,25 соответствует первому квартилю, а р = 0,75 – третьему. Ниже наглядно представлен расчет квартилей при различном числе элементов в выборке и трех методах интерполяции на основе N–1, N и N + 1 (рис. 9, 11–13). Обратите внимание, что рассчитанные числа (по формулам справа от линеек) являются не значениями квартилей, а значениями индексов квартилей. Над линейками показано значение квартилей для ряда значений .

Рис. 9. Число элементов в выборке N = 4k

Если, например, наша выборка , то расчет квартилей на основе N–1-метода даст индексы 1,75, 3,5 и 5,25, и значения квартилей 4,5, 9,5 и 12,5 (рис. 10).

Рис. 10. От индексов к значениям квартилей для N–1-метода и N = 4k

Рис. 11. Число элементов в выборке N = 4k + 1

Рис. 12. Число элементов в выборке N = 4k + 2

Рис. 13. Число элементов в выборке N = 4k + 3

Какой алгоритм считать стандартным для вычисления квартилей?

В 1996 году Роб Дж. Хиндман и Янан Фан опубликовали статью в American Statistician под названием Квантили выборок в статистических пакетах. В ней они рассматривали различные алгоритмы расчета квантилей (квартили – это частный случай квантилей). Их целью было указать методологию, которая могла бы стать стандартом для поставщиков статистического программного обеспечения, чтобы расчет квартилей не зависел от типа пакета. В статье они описали девять методов для расчета квантилей. Таблица показывает некоторые статистические пакеты и используемые в них алгоритмы (рис. 14; таблица, этот раздел заметки и код VBA ниже базируются на тексте с сайта Bacon Bits). Обратите внимание, что R и Maple применяют весь спектр алгоритмов.

Рис. 14. Алгоритмы, используемые в статистических пакетах

Кстати, Хиндман и Фан в завершении своей статьи рекомендовали метод 8 в качестве стандарта для статистических пакетов. По их мнению, этот метод оценки квантиля не зависит от распределения, что делает его наиболее приемлемым для расчета.

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

Функция Excel КВАРТИЛЬ.ИСКЛ использует следующую формулу для расчета квартилей:

где Q_p – p-й квантиль: p = 0 – для минимального значения, 0,25 – для первого квартиля, 0,5 – для медианы, 0,75 – для третьего квартиля, 1 – для максимального значения;

x – индекс квантиля (может быть дробным); x = (n+1)p, где n – число элементов в выборке; обратите внимание на (n+1), поэтому метод и называется N+1-интерполяция;

i – индекс элемента в упорядоченной выборке; самое большое целое всё еще меньшее, чем x;

Формула для КВАРТИЛЬ.ВКЛ отличается только методом расчета х: x = (n-1)p+1; обратите внимание на (n–1), поэтому метод называется N–1-интерполяция. Подробнее с работой формул можно ознакомиться в приложенном Excel-файле на листе Формулы.

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

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

где i – номер метода, 1 ≤ i ≤ 9, (j–m)/n ≤ p = 0 And i

Квартиль в excel

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

Аналитическая статистика или теория статистических выводов ориентирована на обработку данных, полученных в ходе эксперимента, с целью формулировки вы­водов, имеющих прикладное значение для самых различных областей человече­ской деятельности.

1. Характеристика пакета Excel

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

Каждая единица информации занимает свою собственную ячей­ку (клетку) в создаваемой рабочей таблице. В каждой рабочей таблице 256 столбцов (из которых в новой рабочей таблице на экране видны, как правило, только первые 10 или 11 (от А до J или К) и 65 536 строк (из которых обычно видны только первые 15-20). Каждая новая рабочая книга содержит три чистых листа рабочих таблиц.

Вся помещаемая в электронную таблицу информация хранится в от­дельных клетках рабочей таблицы. Но ввести информацию можно только в текущую клетку. С помощью адреса в строке формул и табличного курсора Excel ука­зывает, какая из клеток рабочей таблицы является те­кущей. В основе системы адресации клеток рабочей таблицы лежит комбинация буквы (или букв) столбца и номера строки, например A 2, B 12.

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

2 Использование специальных функций

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

Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число1, число2, . — это от 1 до 30 массивов для которых вычисляется среднее.

Функция МЕДИАНА позволяет получать медиану заданной выборки. Медиана — это элемент выборки, число элементов выборки со значениями больше которого и меньше которого равно.

Функция МОДА вычисляет наиболее часто встречающееся значение в выборке.

Функция ДИСП позволяет оценить дисперсию по выборочным данным.

Функция СТАНДОТКЛОН вычисляет стандартное отклонение.

Функция ЭКСЦЕСС вычисляет оценку эксцесса по выборочным данным.

Функция СКОС позволяет оценить асимметрию выборочного распределения.

Функция КВАРТИЛЬ вычисляет квартили распределения. Функция имеет формат КВАРТИЛЬ(массив, значение), где массив – интервал ячеек, содержащих значения СВ; значение определяет какая квартиль должна быть найдена (0 – минимальное значение, 1 – нижняя квартиль, 2 – медиана, 3 – верхняя квартиль, 4 – максимальное значение распределения).

Пример 1. Провести статистический анализ методом описательной статистики доходов населения в регионе 1 и регионе 2.

Задания для самостоятельной работы

1. Наблюдение посещаемости четырех внеклассных мероприятий в экспериментальном (20 человек) и контрольном (30 человек) классах дали значения (соответственно): 18, 20, 20, 18 и 15, 23, 10, 28. Требуется найти среднее значение, стандартное отклонение, медиану и квартили этих данных.

2. Найти среднее значение, медиану, стандартное отклонение и квартили результатов бега на дистанцию 100 м у группы студентов (с): 12,8; 13,2; 13,0; 12,9; 13,5; 13,1.

3. Определите верхнюю и нижнюю квартиль, выборочную асимметрию и эксцесс для данных измерений роста групп студенток: 164, 160, 157, 166, 162, 160, 161, 159, 160, 163, 170, 171.

4. Найти наиболее популярный туристический маршрут из четырех реализуемых фирмой, если за неделю последовательно были реализованы следующие маршруты: 1, 3, 3, 2, 1, 1, 4, 4, 2, 4, 1, 3, 2, 4, 1, 4, 4, 3, 1, 2, 3, 4, 1, 1, 3.

3. Использование инструмента Пакет анализа

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

Для установки пакета Анализ данных в Excel сделайте следующее:

— в меню Сервис выберите команду Надстройки;

— в появившемся списке установите флажок Пакет анализа.

Для использования статистического пакета анализа данных необходимо:

• указать курсором мыши на пункт меню Сервис и щелкнуть левой кнопкой мыши;
• в раскрывающемся списке выбрать команду Анализданных (если команда Анализ данных отсутствует в меню Сервис, то необходимо установить в Excel пакет анализа данных);
• выбрать строку Описательнаястатистика и нажать кнопку Оk
• в появившемся диалоговом окне указать входной интервал, то есть ввести ссылки на ячейки, содержащие анализируемые данные;
• указать выходной интервал, то есть ввести ссылку на ячейку, в которую будут выведены результаты анализа;
• в разделе Группирование переключатель установить в положение по столбцам или по строкам;
• установить флажок в поле Итоговая статистика и нажать Ок.

Задание для самостоятельной работы

1. В рабочей зоне производились замеры концентрации вредного вещества. Получен ряд значений (в мг./м 3 ): 12, 16, 15, 14, 10, 20, 16, 14, 18, 14, 15, 17, 23, 16. Необходимо определить основные выборочные характеристики.

Квартиль 2020

Table of Contents:

Что такое «квартиль»

Квартал — это статистический термин, описывающий разделение наблюдений на четыре определенных интервала, основанные на значениях данных и их сравнении со всем набором наблюдений ,

Старайтесь не путать четверть с квартикой.

BREAKING DOWN ‘Quartile’

Чтобы понять квартиль, важно понять медиану как меру центральной тенденции. Медиана в статистике — это среднее значение набора чисел. Это точка, в которой ровно половина данных лежит ниже и выше центрального значения. Итак, учитывая набор из 13 чисел, медиана будет седьмым числом. Шесть чисел, предшествующих этому значению, являются наименьшими числами в данных, а шесть чисел после медианы являются наивысшими числами в приведенном наборе данных. Поскольку медиана не зависит от экстремальных значений или выбросов в распределении, иногда это бывает предпочтительнее среднего.

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

Квартал делит данные на три точки — более низкий квартиль, медианный и верхний квартиль — для формирования четырех групп набора данных. Нижняя квартиль или первый квартиль обозначаются как Q1, а среднее число находится между наименьшим значением набора данных и медианной. Второй квартиль Q2 также является медианным. Верхний или третий квартиль, обозначенный как Q3, является центральной точкой, которая находится между медианным и наибольшим числом распределения. Теперь мы можем отобразить четыре группы, сформированные из квартилей. Первая группа значений содержит наименьшее число до Q1; вторая группа включает Q1 в медиану; третий набор является медианным для Q3; и четвертая категория включает Q3 в самую высокую точку данных всего набора.

Каждый квартиль содержит 25% от общего количества наблюдений. Как правило, данные распределяются от самых маленьких до крупнейших, причем эти наблюдения падают ниже 25% от всех проанализированных данных, выделенных в 1-м квартиле, наблюдения падают между 25. 1% и 50% и распределяются во 2-м квартиле, тогда наблюдения падают между 51% и 75% выделены в 3-м квартиле и, наконец, остальные наблюдения, выделенные в 4-м квартиле.

Пример квартили

Давайте работать с примером.Предположим, что распределение математических баллов в классе из 19 учеников в порядке возрастания:

59, 60, 65, 65, 68, 69, 70, 72, 75, 75, 76, 77, 81, 82, 84 , 87, 90, 95, 98

Сначала отметьте медианную Q2, которая в этом случае будет десятым значением — 75.

Q1 — это центральная точка между наименьшим и средним. В этом случае Q1 падает между первым и девятым баллами — 68. [Обратите внимание, что медиана также может быть включена при вычислении Q1 или Q3 для нечетного набора значений. Если бы мы включили медиану по обе стороны от средней точки, то Q1 будет средним значением между первым и десятым баллами, что является средним значением пятого и шестого балла — (пятый + шестой) / 2 = (68 + 69) / 2 = 68. 5].

Q3 — среднее значение между Q2 и самым высоким счетом — 84. [Или если вы включаете медиану, Q3 = (82 + 84) / 2 = 83].

Теперь, когда у нас есть наши квартили, давайте интерпретировать их числа. Оценка 68 (Q1) представляет первый квартиль и является 25 th процентилем. 68 — медиана нижней половины балла, установленного в доступных данных i. е. медиана баллов от 59 до 75. Q1 говорит нам, что 25% баллов меньше 68 и 75% баллов в классе больше. Q2 (медиана) — это 50 th процентиль и показывает, что 50% баллов меньше 75, а 50% баллов выше 75. Наконец, Q3, 75 th Если точка данных для Q1 находится дальше от медианной, чем Q3, от медианной, то можно сказать, что существует большая дисперсия среди меньших значений набора данных, чем среди больших значений. Такая же логика применяется, если Q3 находится дальше от Q2, чем Q1 от медианы.

Если существует четное количество точек данных, медиана будет средним числом средних двух чисел. В нашем примере выше, если бы у нас было 20 учеников вместо 19, медиана их баллов будет средним арифметическим для десятого и одиннадцатого чисел.

Квартилы используются для расчета межквартильного диапазона, который является мерой изменчивости вокруг медианы. Межквартильный диапазон просто вычисляется как разница между первым и третьим квартилями: Q3 — Q1. По сути, это диапазон средней половины данных, который показывает, насколько распространены данные.

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

CFA — Квартили, квинтили, децили и процентили.

Рассмотрим квантили (т.е. квартили, квинтили, децили и процентили), — наиболее часто используемые меры положения данных в распределении, — в рамках изучения количественных методов по программе CFA.

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

Например, установление того, что 25, 50 и 75% годовой доходности портфеля лежат в пределах (то есть не выше) значений -0,05, 0,16 и 0,25 соответственно, дает краткую информацию о распределении доходности портфеля.

Статистики используют слово квантиль (или фрактиль от англ. ‘quantile’, ‘fractile’) как наиболее общий термин для порогового значения не выше которого (то есть равно или ниже) которого лежит указанная часть данных. Далее мы опишем наиболее часто используемые квантили — квартили, квинтили, децили и процентили — и их применение в инвестициях.

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

Квартили (англ. ‘quartiles’) делят распределение на четверти, квинтили (англ. ‘quintiles’) — на 5 частей, децили (англ. ‘deciles’) — на 10 частей и процентили (англ. ‘percentiles’) — на 100.

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

Например, первый квартиль (Q₁) делит распределение так, что 25 процентов наблюдений лежат не выше него; следовательно, 1-й квартиль также является 25-м процентилем.

Второй квартиль (Q₂) представляет 50-й процентиль, а третий квартиль (Q₃) представляет 75-й процентиль, потому что 75 процентов наблюдений лежат не выше него.

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

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

Пусть P_y будет значением, не выше которого лежит y-процент распределения, или y-й процентиль. (Например, P₁₈ — это точка, не выше которой лежат 18% наблюдений).

Формула для позиции процентиля в массиве из n записей, отсортированных по возрастанию:

Ly = (n + 1) y / 100 (формула 8)

где y — это процентная точка, в которой мы делим распределение на части, а L_y — местоположение (L) процентиля (P_y) в массиве, отсортированном в порядке возрастания. Значение Ly может быть или не быть целым числом. Как правило, по мере увеличения размера выборки результат расчета положения в процентилях становится более точным; в небольших выборках он может быть весьма приблизительным.

В качестве примера случая, когда L_y не является целым числом, предположим, что мы хотим определить 3-ий квартиль доходности за 2012 год (Q₃ или P₇₅) для 16 европейских фондовых рынков, представленных в Таблице 8.

В соответствии с Формулой 8 позиция третьего квартиля имеет вид L₇₅ = (16 + 1) (75/100) = 12.75 или между 12-м и 13-м позициями в Таблице 9, в которой доходность представлена в порядке возрастания.

12-й пункт в Таблице 9 — это доходность рынка акций Франции в 2012 году: 15.90%.

13-й пункт — это доходность акций в Австрии в 2012 году: 20.72%.

Определив «0.75» как «12.75», мы пришли бы к выводу, что P75 находится на 75% расстояния между 15.90% и 20.72%.

Подведем итоги:

1) Когда позиция Ly представляет собой целое число, она соответствует фактическому наблюдению. Например, если бы Дания не была включена в выборку, то n + 1 было бы равно 16, а при L₇₅ = 12 третий квартиль был бы P₇₅ = X₁₂, где X_i определяется как значение наблюдения в i-й (i = L₇₅) позиции данных, отсортированных в порядке возрастания (т. е. P₇₅ = 15.90).

2) Когда L_y не является целым числом, L_y лежит между двумя ближайшими целыми числами (одно сверху и одно снизу), и мы используем линейную интерполяцию между этими двумя положениями для определения P_y. Интерполяция означает оценку неизвестного значения на основе двух известных значений, которые его окружают (лежат над и под ним); термин «линейный» относится к линейной оценке.

Возвращаясь к расчету P₇₅ для доходности капитала, мы обнаружили, что L_y = 12.75; следующее более низкое целое число равно 12, а следующее более высокое целое число равно 13.

Используя линейную интерполяцию, находим:

Как указано выше, на 12-й позиции находится доходность акций Франции, поэтому X₁₂ = 15.90%; X₁₃ = 20.72%, что соответствует доходности акций Австрии.

Таким образом, наша оценка методом линейной интерполяции составит:

P₇₅ ≈ X₁₂ + (12.75 — 12)(X₁₃ — X₁₂) = 15.90 + 0.75 [20.72 — 15.90] = 15.90 + 0.75(4.82) = 15.90 + 3.62 = 19.52%.

Значения 15,90 и 20,72 ограничивают Р₇₅ снизу и сверху соответственно. Поскольку 12.75 — 12 = 0.75, используя линейную интерполяцию, мы смещаем 75 процентов в диапазоне от 15,90 до 20,72 при нашей оценке P₇₅.

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

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

Пример расчета процентилей, квартилей и квинтилей.

EURO STOXX 50 — это индекс 50 публичных компаний, который образуют «голубую фишку» лидеров суперсекторов экономики в еврозоне. Таблица 17 показывает рыночную капитализацию акций для 50 компонентов индекса STOXX Ltd. в сентябре 2013 года.

Квантили распределений EXCEL

Смотря на закон распределения , мы можем понять, какова вероятность того или иного события, можем сказать, какова вероятность, что произойдёт группа событий, а в этой статье мы рассмотрим, как наши выводы “на глаз” перевести в математически обоснованное утверждение.

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

(2) E(X) = Σ(p_i•X_i) E – от английского слова Expected (ожидание)
Для математического ожидания справедливы равенства:
(3) E(X + Y) = E(X) + E(Y)
(4) E(X•Y) = E(X) • E(Y)

Момент степени k:

Центральный момент степени k:

Среднее значение

Среднее значение (μ) закона распределения – это математическое ожидание случайной величины (случайная величина – это событие), например, сколько в среднем посетителей заходит в магазин в час:

Чтобы найти среднее значение всех результатов необходимо сложить всё вместе и разделить на количество результатов:

μ = (114 • 0 + 115 • 1 + 52 • 2 + 52 • 3 + 24 • 4 + 13 • 5 + 30 • 6) / 400 = 716/400 = 1.79

То же самое мы можем проделать используя формулу 2:

μ = M(X) = Σ(X_i•p_i) = 0 • 0.29 + 1 • 0.29 + 2 • 0.13 + 3 • 0.13 + 4 • 0.06 + 5 • 0.03 + 6 • 0.08 = 1.79 Момент первой степени, формула (5)

Собственно, формула 2 представляет собой среднее арифметическое всех значений
Итог: в среднем, 1.79 посетителя в час

Отклонение от среднего

Посмотрите на это распределение, можно предположить, что в среднем случайная величина равна 100±5, поскольку кажется, что таких значений несравнимо больше чем тех, что меньше 95 или больше 105:

График 2. График функции вероятности. Распределение ≈ 100±5

Среднее значение по формуле (2): μ = 99.95, но как посчитать, насколько далеко все значения находятся от среднего? Вам должна быть знакома запись 100±5. Что бы получить это значение ±, нам необходимо определить диапазон значений вокруг среднего. И мы могли бы использовать в качестве меры удалённости “разность” между средним и случайными величинами:

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

Соответственно, среднее значение удалённости – это математическое ожидание квадратов удалённости:

(9) σ 2 = E[(X – E(X)) 2 ] Поскольку вероятности любой удалённости равносильны – вероятность каждого из них – 1/n, откуда: (10) σ 2 = E[(X – E(X)) 2 ] = ∑[(X_i – μ) 2 ]/n Она же формула центрального момента (6) второй степени

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

Возвращаясь к примеру, посчитаем среднеквадратическое отклонение для графика 2:

Квантиль

График 3. Функция распределения. Медиана

График 4. Функция распределения. 4-квантиль или квартиль

График 5. Функция распределения. 0.34-квантиль

Для анализа функции распределения ввели понятие квантиль. Квантиль – это случайная величина при заданном уровне вероятности, т.е.: квантиль для уровня вероятности 50% – это случайная величина на графике плотности вероятности, которая имеет вероятность 50%. На примере с графиком 3, квантиль уровня 0.5 = 99 (ближайшее значение, поскольку распределение дискретно и события со значением 99.3 просто не существует)

• 2-квантиль – медиана
• 4-квантиль – квартиль
• 10-квантиль – дециль
• 100-квантиль – перцентиль

То есть, если мы говорим о дециле (10-квантиле), то это означает, что мы разбили график на 10 частей, что соответствует девяти линяям, и для каждого дециля нашли значение случайной величины.

Также, используется обозначение x-квантиль, где х – дробное число, например, 0.34-квантиль, такая запись означает значение случайной величины при p = 0.34.

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

Построение интервалов

Квантили используют для построения доверительных интервалов, которые необходимы для исследования статистики не одного конкретного события (например, интерес – случайное число = 98), а для группы событий (например, интерес – случайное число между 96 и 99). Доверительный интервал бывает двух видов: односторонний и двусторонний. Параметр доверительного интервала – уровень доверия. Уровень доверия означает процент событий, которые можно считать успешными.

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

Двусторонний доверительный интервал строится следующим образом: мы задаёмся уровнем значимости, например, 10%, и выделяем область на графике так, что 90% всех событий попадут в эту область. Поскольку интервал двусторонний, то мы отсекаем по 5% с каждой стороны, т.е. мы ищем 5й перцентиль, 95й перцентиль и значения случайной величины между ними будут являться доверительной областью, значения за пределами доверительной области называются “критическая область“

Первый квартиль

Значение квартиля Q1 находится в интервале 68,98 – 71,70, соответствующего частоте fQ1 = 150:4 = 37,5

Третий квартиль

Значение квартиля находится в интервале 68,98 – 71,70, соответствующего частоте fQ3 = (3*150):4 = 112,5

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

Если функция распределения 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 равные части.

Квартили в MS EXCEL

Чтобы вычислить в MS EXCEL квартили заданного распределения необходимо использовать соответствующую обратную функцию распределения .

При вычислении квартилей в MS EXCEL используются обратные функции распределения : НОРМ.СТ.ОБР() , ЛОГНОРМ.ОБР() , ХИ2.ОБР() , ГАММА.ОБР() и т.д. Подробнее о распределениях, представленных в MS EXCEL, можно прочитать в статье Распределения случайной величины в MS EXCEL .

Например, в MS EXCEL 1-й квартиль для логнормального распределения LnN(1;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,25;1;1) , а 3-й квартиль для стандартного нормального распределения по формуле =НОРМ.СТ.ОБР(0,75) .

Моменты случайной величины

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

#1 — первый момент случайной величины — среднее значение данных, которое показывает место распределения.

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

#3 — третий момент случайной величины — коэффициент асимметрии — мера того, насколько неравномерным является распределение. Коэффициент асимметрии положителен, если распределение наклонено влево и левый хвост короче правого. То есть среднее значение находится правее. И наоборот:

#4 — четвертый момент случайной величины — коэффициент эксцесса, который описывает то, насколько толстый хвост и насколько острый пик распределения. Этот коэффициент показывает, насколько вероятно найти точки экстремума в данных. Чем выше значение, тем вероятнее выбросы. Это похоже на разброс (дисперсию), но между ними есть отличия.

Как видно на графике, чем выше значение пики, тем выше коэффициент эксцесса, т.е. у верхней кривой коэффициент эксцесса выше, чем у нижней.

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

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

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

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

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

Анализ статистики случайно сгенерированных чисел в Excel

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

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

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

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

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

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

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

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

где i – номер метода, 1 ≤ i ≤ 9, (j–m)/n ≤ p < (j–m+1)/n, х_j – j-ый порядковый элемент упорядоченного ряда, n – размер выборки, γ является функцией двух параметров: j = floor(np + m) и g = np + m – j, где floor – функция возвращающая наибольшее целое, но всё еще меньшее, чем аргумент функции (аналог в Excel – ОКРВНИЗ.МАТ), m – константа, определяемая типом алгоритма расчета квантиля. Если вас интересуют подробности, обратитесь к справочной системе R.

SAS предлгает 5 методов расчета квантилей.

Расчет децилей для дискретного ряда

Определяем номер дециля по формуле: ,

Если номер дециля – целое число, то значение дециля будет равно величине элемента ряда, которое обладает накопленной частотой равной номеру дециля. Например, если номер дециля равен 20, его значение будет равно значению признака с S =20 (накопленной частотой равной 20).

Если номер дециля – нецелое число, то дециль попадает между двумя наблюдениями. Значением дециля будет сумма, состоящая из значения элемента, для которого накопленная частота равна целому значению номера дециля, и указанной части (нецелая часть номера дециля) разности между значением этого элемента и значением следующего элемента.

Например, если номер дециля равна 20,25, дециль попадает между 20-м и 21-м наблюдениями, и его значение будет равно значению 20-го наблюдения плюс 1/4 разности между значением 20-го и 21-го наблюдений.

Квантили специальных видов

Часто используются Квантили специальных видов:

• процентили x _p/100 , p=1, 2, 3, …, 99
• квартили x _p/4 , p=1, 2, 3
• медиана x _1/2

В качестве примера вычислим медиану (0,5-квантиль) логнормального распределения LnN(0;1) (см. файл примера лист Медиана ).

Это можно сделать с помощью формулы =ЛОГНОРМ.ОБР(0,5; 0; 1)

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

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

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

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

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

Выделенная площадь на рисунке соответствует вероятности, что случайная величина примет значение меньше α-квантиля . Из определения квантиля эта вероятность равна α . Из графика функции распределения становится понятно, откуда происходит название ” нижний квантиль” – выделенная область расположена в нижней части графика.

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

Однако, при проверке гипотез и построении доверительных интервалов чаще используется “верхний” α-квантиль. Покажем почему.

Верхним α – квантилем называют такое значение x _α , для которого вероятность, того что случайная величина X примет значение больше или равное x _α равна альфа: P(X>= x _α )= α . Из определения понятно, что верхний альфа – квантиль любого распределения равен нижнему (1- α) – квантилю. А для распределений, у которых функция плотности распределения является четной функцией, верхний α – квантиль равен нижнему α – квантилю со знаком минус . Это следует из свойства четной функции f(-x)=f(x), в силу симметричности ее относительно оси ординат.

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

Почему применяют понятие верхний α – квантиль? Только из соображения удобства, т.к. он при α всегда положительный (в случае стандартного нормального распределения ). А при проверке гипотез α равно уровню значимости , который обычно берут равным 0,05, 0,1 или 0,01. В противном случае, в процедуре проверки гипотез пришлось бы записывать условие отклонения нулевой гипотезы μ>μ ₀ как Z ₀ >Z _{1- α} , подразумевая, что Z _{1- α} – обычный квантиль порядка 1- α (или как Z ₀ >-Z _α ). C верхнем квантилем эта запись выглядит проще Z ₀ >Z _α .

Примечание : Z ₀ – значение тестовой статистики , вычисленное на основе выборки . Подробнее см. статью Проверка статистических гипотез в MS EXCEL о равенстве среднего значения распределения (дисперсия известна) .

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

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

На графике плотности вероятности площадь выделенной области равна 0,05 (5%) от общей площади под графиком (равна 1). Из графика функции распределения становится понятно, откуда происходит название “верхний” квантиль – выделенная область расположена в верхней части графика. Если Z ₀ больше верхнего квантиля , т.е. попадает в выделенную область, то нулевая гипотеза отклоняется.

Также при проверке двухсторонних гипотез и построении соответствующих доверительных интервалов иногда используется понятие “двусторонний” α-квантиль. В этом случае условие отклонения нулевой гипотезы звучит как |Z ₀ |>Z _{α /2} , где Z _{α /2} – верхний α/2-квантиль . Чтобы не писать верхний α/2-квантиль , для удобства используют “двусторонний” α-квантиль. Почему двусторонний? Как и в предыдущих случаях, построим график плотности вероятности стандартного нормального распределения и график функции распределения .

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

Вычислить двусторонний 0,05 – квантиль это можно с помощью формул MS EXCEL: =НОРМ.СТ.ОБР(1-0,05/2) или =-НОРМ.СТ.ОБР(0,05/2)

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

Квантили распределения Стьюдента

Аналогичным образом квантили вычисляются и для распределения Стьюдента . Например, вычислять верхний α/2- квантиль распределения Стьюдента с n -1 степенью свободы требуется, если проводится проверка двухсторонней гипотезы о среднем значении распределения при неизвестной дисперсии ( см. эту статью ).

Для верхних квантилей распределения Стьюдента часто используется запись t _α/2,n-1 . Если такая запись встретилась в статье про проверку гипотез или про построение доверительного интервала , то это именно верхний квантиль .

Примечание : Функция плотности вероятности распределения Стьюдента , как и стандартного нормального распределения , является четной функцией.

Чтобы вычислить в MS EXCEL верхний 0,05/2 – квантиль для t-распределения с 10 степенями свободы (или тоже самое двусторонний 0,05-квантиль ), необходимо записать формулу =СТЬЮДЕНТ.ОБР.2Х(0,05; 10) или =СТЬЮДРАСПОБР(0,05; 10) или =СТЬЮДЕНТ.ОБР(1-0,05/2; 10) или =-СТЬЮДЕНТ.ОБР(0,05/2; 10)

.2X означает 2 хвоста, т.е. двусторонний квантиль .

Квантили распределения ХИ-квадрат

Вычислять квантили распределения ХИ-квадрат с n -1 степенью свободы требуется, если проводится проверка гипотезы о дисперсии нормального распределения (см. статью Проверка статистических гипотез в MS EXCEL о дисперсии нормального распределения ).

При проверке таких гипотез также используются верхние квантили. Например, при двухсторонней гипотезе требуется вычислить 2 верхних квантиля распределения ХИ 2 : χ 2 _α/2,n-1 и χ 2 _{1- α/2,n-1} . Почему требуется вычислить два квантиля , не один, как при проверке гипотез о среднем , где используется стандартное нормальное распределение или t-распределение ?

Дело в том, что в отличие от стандартного нормального распределения и распределения Стьюдента , плотность распределения ХИ 2 не является четной (симметричной относительно оси х). У него все квантили больше 0, поэтому верхний альфа-квантиль не равен нижнему (1-альфа)-квантилю или по-другому: верхний альфа-квантиль не равен нижнему альфа-квантилю со знаком минус.

Чтобы вычислить верхний 0,05/2 – квантиль для ХИ 2 -распределения с числом степеней свободы 10, т.е. χ 2 _0,05/2,n-1 , необходимо в MS EXCEL записать формулу =ХИ2.ОБР.ПХ(0,05/2; 10) или =ХИ2.ОБР(1-0,05/2; 10)

Результат равен 20,48. .ПХ означает правый хвост распределения, т.е. тот который расположен вверху на графике функции распределения .

Чтобы вычислить верхний (1-0,05/2)- квантиль при том же числе степеней свободы , т.е. χ 2 _1-0,05/2,n-1 и необходимо записать формулу =ХИ2.ОБР.ПХ(1-0,05/2; 10) или =ХИ2.ОБР(0,05/2; 10)

19. Функция квартиль

Описание:Возвращает квартиль множества данных. Квартиль часто используются при анализе продаж для разбиения генеральной совокупности на группы. Например, можно воспользоваться функцией КВАРТИЛЬ, чтобы найти среди всех предприятий 25 процентов наиболее доходных.

КВАРТИЛЬ(массив;часть)

Массив— массив или интервал ячеек с числовыми значениями, для которых определяются значения квартилей.

20. Функция квпирсон

Описание:Возвращает квадрат коэффициента корреляции Пирсона для точек данных в аргументах известные_значения_y и известные_значения_x. Дополнительные сведения см. в описании функции ПИРСОН. Значение r-квадрат можно интерпретировать как отношение дисперсии для y к дисперсии для x.

КВПИРСОН(известные_значения_y;известные_значения_x)

Известные_значения_y— массив или интервал точек данных.

Известные_значения_x— массив или интервал точек данных.

— Аргументы должны быть либо числами, либо содержащими числа именами, массивами или ссылками.

— Учитываются логические значения и текстовые представления чисел, которые введены непосредственно в список аргументов.

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

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

— Если аргументы известные_значения_y и известные_значения_x пусты или указанное в них количество число точек данных не совпадает, функция КВПИРСОН возвращает значение ошибки #Н/Д.

— Если аргументы известные_значения_y и известные_значения_x содержат только одну точку данных, функция КВПИРСОН возвращает значение ошибки #ДЕЛ/0!.

— Коэффициент корреляции Пирсона (r) вычисляется с помощью следующего уравнения:

где x и y — выборочные средние значения СРЗНАЧ(массив1) и СРЗНАЧ(массив2).

Функция КВПИРСОН возвращает значение r 2 , являющееся квадратом коэффициента корреляции.

21. Ковар (функция ковар)

Описание:Возвращает ковариацию, т. е. среднее произведений отклонений для каждой пары точек. Ковариация используется для определения связи между двумя наборами данных. Например, можно проверить, соответствует ли более высокому уровню доходов более высокий уровень образования.

КОВАР(массив1,массив2)

Массив1:первый диапазон ячеек с целыми числами.

Массив2:второй диапазон ячеек с целыми числами.

— Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.

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

— Если массив1 и массив2 имеют различное число точек данных, функция КОВАР возвращает значение ошибки #Н/Д.

— Если массив1 или массив2 пуст, функция КОВАР возвращает значение ошибки #ДЕЛ/0!.

— Ковариация определяется следующим образом:

являются выборочными средними значениями СРЗНАЧ(массив1) и СРЗНАЧ(массив2), а n — размер выборки.