Как посчитать квинтили в эксель
Функция КВАРТИЛЬ в Excel используется для расчета квартиля диапазона числовых данных и возвращает соответствующее числовое значение.
Функция КВАРТИЛЬ.ВКЛ вычисляет на основе указанной процентили в качестве второго аргумента функции. Полностью соответствует первой функции. Последняя используется в Excel 2007 и более ранних версиях и оставлена для совместимости.
Функция КВАРТИЛЬ.ИСКЛ используется для расчета квартили диапазона числовых значений на основе известной процентили, за исключением граничных значений (минимального и максимального значения в диапазоне).
Квартили используются для распределения диапазона чисел на четыре равные части:
- Первый квартиль является числом из диапазона исследуемых значений, которое делит данный диапазон на две части так, что около 25% данного диапазона являются числами, которые меньше первого квартиля, а остальные (75%) – больше. Рассматриваемые функции могут возвращать результат интерполяции двух соседних значений из диапазона.
- Второй квартиль эквивалентен медиане выборки (исследуемого числового диапазона), то есть числовому значению, которое делит диапазон на две части: 50% чисел меньше медианы, остальные 50% чисел больше медианы. Так, запись =КВАРТИЛЬ.ВКЛ(A1:A10;2) возвращает значение, эквивалентное результату вычисления функции =МЕДИАНА(A1:A10), при условии, что ячейки из диапазона A1:A10 содержат числовые значения.
- Третий квартиль – числовое значение, делящее диапазон на две части, в первой из которой содержатся 75% чисел диапазона, которые меньше полученного значения, а во второй (25%) – больше.
Функция КВАРТИЛЬ.ВКЛ может быть использована не только для определения медианы выборки (второго квартиля), а и нахождения минимального и максимального значений соответственно. При работе с большими диапазонами чисел для подобных расчетов рекомендуется использовать функции МИН и МАКС соответственно.
Существует несколько алгоритмов расчета квартилей. Все рассмотренные функции используют следующую формулу:
- Qp – p-й квантиль (является частным случаем квантиля);
- x – индекс квантиля;
- i – индекс элемента из выборки;
- A1,A2…Ai – элементы выборки, отсортированной по возрастанию значений.
Для расчета индекса квантиля (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-го квартиля.
Понятие Квантиля основано на определении Функции распределения . Поэтому, перед изучением Квантилей рекомендуем освежить в памяти понятия из статьи Функция распределения вероятности .
- Определение
- Квантили специальных видов
- Квантили стандартного нормального распределения
- Квантили распределения Стьюдента
- Квантили распределения ХИ-квадрат
- Квантили F-распределения
- Квантили распределения Вейбулла
- Квантили экспоненциального распределения
Сначала дадим формальное определение квантиля, затем приведем примеры их вычисления в MS EXCEL.
Определение
Пусть случайная величина X , имеет функцию распределения F ( x ). α-квантилем ( альфа- квантиль, x a , квантиль порядка α, нижний α- квантиль ) называют решение уравнения x a =F -1 (α), где α - вероятность, что случайная величина х примет значение меньшее или равное x a , т.е. Р(х файл примера Лист Определение ):
Примечание : О построении графиков в MS EXCEL можно прочитать статью Основные типы диаграмм в MS EXCEL .
Например, с помощью графика вычислим 0,21-ю квантиль , т.е. такое значение случайной величины, что Р(X НОРМ.СТ.ОБР() , ЛОГНОРМ.ОБР() , ХИ2.ОБР(), ГАММА.ОБР() и т.д. Подробнее о распределениях, представленных в MS EXCEL, можно прочитать в статье Распределения случайной величины в MS EXCEL .
Точное значение квантиля в нашем случае можно найти с помощью формулы =НОРМ.СТ.ОБР(0,21)
СОВЕТ : Процедура вычисления квантилей имеет много общего с вычислением процентилей выборки (см. статью Процентили в MS EXCEL ).
Квантили специальных видов
Часто используются Квантили специальных видов:
В качестве примера вычислим медиану (0,5-квантиль) логнормального распределения LnN(0;1) (см. файл примера лист Медиана ).
Это можно сделать с помощью формулы =ЛОГНОРМ.ОБР(0,5; 0; 1)
Квантили стандартного нормального распределения
Необходимость в вычислении квантилей стандартного нормального распределения возникает при проверке статистических гипотез и при построении доверительных интервалов.
Примечание : Про проверку статистических гипотез см. статью Проверка статистических гипотез в MS EXCEL . Про построение доверительных интервалов см. статью Доверительные интервалы в MS EXCEL .
В данных задачах часто используется специальная терминология:
- Нижний квантиль уровняальфа ( α percentage point) ;
- Верхний квантиль уровня альфа (upper α 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,05-квантиля , т.е. больше значения 1,645. Эта вероятность равна 0,05.
На графике плотности вероятности площадь выделенной области равна 0,05 (5%) от общей площади под графиком (равна 1). Из графика функции распределения становится понятно, откуда происходит название "верхний" квантиль - выделенная область расположена в верхней части графика. Если Z 0 больше верхнего квантиля , т.е. попадает в выделенную область, то нулевая гипотеза отклоняется.
Также при проверке двухсторонних гипотез и построении соответствующих доверительных интервалов иногда используется понятие "двусторонний" α-квантиль. В этом случае условие отклонения нулевой гипотезы звучит как |Z 0 |>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 попадает в одну из выделенных областей, то нулевая гипотеза отклоняется.
Вычислить двусторонний 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)
Результат равен 3,25.
Квантили F-распределения
Вычислять квантили распределения Фишера с n 1 -1 и n 2 -1 степенями свободы требуется, если проводится проверка гипотезы о равенстве дисперсий двух нормальных распределений (см. статью Двухвыборочный тест для дисперсии: F-тест в MS EXCEL ).
При проверке таких гипотез используются, как правило, верхние квантили. Например, при двухсторонней гипотезе требуется вычислить 2 верхних квантиля F -распределения: F α/2,n1-1, n 2 -1 и F 1-α/2,n1-1, n 2 -1 . Почему требуется вычислить два квантиля , не один, как при проверке гипотез о среднем ? Причина та же, что и для распределения ХИ 2 – плотность F-распределения не является четной . Эти квантили нельзя выразить один через другой как для стандартного нормального распределения . Верхний альфа-квантиль F -распределения не равен нижнему альфа-квантилю со знаком минус.
Чтобы вычислить верхний 0,05/2-квантиль для F -распределения с числом степеней свободы 10 и 12, необходимо записать формулу =F.ОБР.ПХ(0,05/2;10;12) =FРАСПОБР(0,05/2;10;12) =F.ОБР(1-0,05/2;10;12)
Результат равен 3,37. .ПХ означает правый хвост распределения, т.е. тот который расположен вверху на графике функции распределения .
Квантили распределения Вейбулла
Иногда обратная функция распределения может быть представлена в явном виде с помощью элементарных функций, например как для распределения Вейбулла . Напомним, что функция этого распределения задается следующей формулой:
После логарифмирования обеих частей выражения, выразим x через соответствующее ему значение F(x) равное P:
Примечание : Вместо обозначения α-квантиль может использоваться p - квантиль. Суть от этого не меняется.
Это и есть обратная функция, которая позволяет вычислить P - квантиль ( p - quantile ). Для его вычисления в формуле нужно подставить известное значение вероятности P и вычислить значение х p (вероятность того, что случайная величина Х примет значение меньше или равное х p равна P).
Квантили экспоненциального распределения
Задача : Случайная величина имеет экспоненциальное распределение :
Требуется выразить p -квантиль x p через параметр распределения λ и заданную вероятность p .
Примечание : Вместо обозначения α-квантиль может использоваться p-квантиль . Суть от этого не меняется.
Решение : Вспоминаем, что p -квантиль – это такое значение x p случайной величины X, для которого P(X
Для вычисления квартилей в 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;" . В результате получим, что 26% меньше, чем 1-й квартиль .
Чем в выборке больше значений и меньше повторов , тем точнее деление выборки квартилями на четверти.
Примечание : Первый квартиль - это то же самое, что и 25-я процентиль . Подробнее см. статью про процентили .
Второй квартиль (или медиана , Q2) также делит выборку , на 2 равные части: половина чисел множества больше, чем медиана , а половина чисел меньше, чем медиана . Для вычисления 2-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;2) или =МЕДИАНА(A7:A56)
Третий квартиль (или верхний квартиль , Q3) делит выборку , на 2 части: примерно 75% значений в выборке меньше Q3, остальные 25% - больше. Для вычисления 3-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;3) или =ПРОЦЕНТИЛЬ.ВКЛ(A7:A56;0,75)
Примечание : Третий квартиль - это то же самое, что и 75-я процентиль .
Второй аргумент функции КВАРТИЛЬ.ВКЛ() может также принимать значения 0 и 4. В первом случае функция вернет минимальное значение , во втором – максимальное .
Интерквартильный размах
Интерквартильным размахом или интерквартильным интервалом (InterQuartile range, IQR) называется разность между третьим и первым квартилями (Q3 - Q1). Интерквартильный размах является характеристикой разброса значений в выборке .
Примечание : Характеристикой разброса значений в выборке является также дисперсия и стандартное отклонение .
Интерквартильный размах , а также квартили используются при построении Блочной диаграммы , которая полезна для оценки разброса значений (variation) в небольших выборках или для сравнения нескольких выборок имеющих сходные распределения.
Подробнее о построении Блочной диаграммы см. статью Блочная диаграмма в MS EXCEL .
Квартили непрерывного распределения
Если функция распределения 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) .
Сначала разберемся на примерах, что такое процентиль , затем рассмотрим соответствующие функции MS EXCEL.
Задача. Проектируют койку на круизном лайнере. Необходимо, чтобы 95% пассажиров помещались на койке в полный рост. Как вычислить длину койки?
Для решения задачи потребуется найти рост, ниже которого 95% населения. Для этого нужно сделать репрезентативную выборку , скажем, из 2000 человек, отсортировать значения выборки по возрастанию , потом определить значение с позицией равной 1901 (2000*95%+1). Пусть найденный рост оказался равен 190 см. Ответ : Длина койки должна быть 190 см (+ запас для комфортного размещения на койке).
Значение 190 см называется 95%-й процентилью данной выборки , т.е. 95% опрошенных людей имеет рост СОВЕТ : Понятие процентиля связано с понятием квантиля функции распределения . Поэтому имеет смысл освежить в памяти понятия функции распределения и обратной функции .
На основании вышесказанного сформулируем определение для процентили : K-й Процентиль представляет такое собой значение Х в наборе данных, которое разделяет набор на две части: одна часть содержит K процентов данных, меньших Х , а другая часть содержит все остальные значения набора (т.е. 1- K процентов данных б о льших Х).
Приведем алгоритм для нахождения k -й процентили выборки:
- отсортировать значениявыборки по возрастанию (пусть в выборке всего N значений);
- найти такую позицию в списке , для которой k% значений оказалось бы меньше этого значения. Это можно сделать с помощью формулы N*k%+1 (затем, округлить его до целого );
- значение, находящееся в этой позиции, и будет k-й процентилью (примерно), т.к. k% значений массива данных будет меньше этого значения.
Примечание : Более точный алгоритм расчета процентилей дан ниже в разделе про функцию ПРОЦЕНТИЛЬ.ВКЛ() .
Еще одна задача . Зачет «автоматом» поставят только тем студентам, которые в течение семестра набрали в течение семестра больше баллов, чем 90%-я Процентиль (другими словами 10% лучшим студентам поставят зачет «автоматом»).
Так как порог установлен в процентилях , то заданному % студентов придется сдавать экзамен вне зависимости от набранных баллов (т.е. 90% студентов в любом случае будут сдавать экзамен). А вот если бы порог был установлен в абсолютных значениях, например, 380 баллов из 400, то вполне вероятна ситуация, когда половине студентов поставили бы «автоматом» (если бы они, конечно, набрали бы больше 380 баллов). Или наоборот, при общих слабых результатах ни один студент не получил бы зачет «автоматом». Установка порога в процентилях создает предпосылки здоровой конкуренции (или, наоборот, сговора: даже если никто особо не учился, то в любом случае 10% получат зачет «автоматом»).
Решим эту задачу, используя заданные значения выборки . Пусть всего 120 студентов, значения баллов за семестр разместим в диапазоне A8:A127 (см. Файл примера , лист Пример-Студенты ). Максимальный суммарный балл = 400. Порог получения зачета «автоматом» - больше баллов, чем 90%-я Процентиль .
Понятно, чтобы определить тех студентов, которые получат зачет «автоматом» нужно отсортировать их по набранным баллам и отобрать 10% (т.е. 12 студентов) с максимальными баллами. Но, чтобы студенты сами определились, начинать ли им готовиться к экзамену или нет, достаточно сообщить им проходной балл (90%-ю процентиль ). Рассчитаем этот проходной балл.
Для наглядности построим Гистограмму распределения с накоплением .
90%-ю процентиль можно найти с помощью формулы =НАИМЕНЬШИЙ(A8:A127;ЦЕЛОЕ(120*0,9)+1)
Эта формула создана на основе алгоритма, приведенного выше. Результат формулы - 334 балла.
Как видно из рисунка выше, количество значений массива (студентов), у которых баллы хуже, действительно равно 108 (90% от 120). Следовательно, как и предполагалось, 12 студентов получат зачет «автоматом».
Примечание : Найденное значение процентили 334 является приблизительным. Точное значение дает формула =ПРОЦЕНТИЛЬ.ВКЛ(A8:A127;0,9) , которое равно 331,4. О том как работает функция ПРОЦЕНТИЛЬ.ВКЛ() читайте ниже.
Как показывает опыт, для данных выборки K -я процентиль не всегда отделяет точно К процентов значений, которые меньше ее. Например, в нашем примере найдем 80%-ю процентиль. Оказывается, что только 79% значений меньше 80%-й процентили (318). Это происходит из-за округления. Для выборок с большим количеством значений (>100) обычно наблюдается хорошее соответствие. Повторы значений также могут привести к несоответствию значения процентиля и соответствующего % значений (см. ниже).
Примечание : Процентили часто называют перцентилями (с этим соглашается и MS WORD) или центилями . В версии MS EXCEL 2007 и более ранних использовалась функция ПЕРСЕНТИЛЬ() , которая оставлена для совместимости. Но, начиная с версии EXCEL 2010, появились функции ПРОЦЕНТИЛЬ.ВКЛ() и ПРОЦЕНТИЛЬ.ИСКЛ() – английское название PERCENTILE.EXC(), а Условное форматирование предлагает настроить правило с использованием именно процентилей . В свою очередь, надстройка Пакет Анализа имеет инструмент Ранг и Персентиль . Google также отдает предпочтение процентилям , выдавая гораздо больше результатов на запрос «процентиль», чем на запрос «перцентиль» (на начало 2016 года).
Таким образом, для процентилей используется 3 названия: процентиль (MS EXCEL, Google) , персентиль (MS EXCEL) , перцентиль (MS WORD) .
Ниже детально рассмотрим как работает функция ПРОЦЕНТИЛЬ.ВКЛ() и создадим ее аналог с помощью альтернативной формулы. Также рассмотрим функцию ПРОЦЕНТРАНГ.ВКЛ() и кривую процентилей .
СОВЕТ : Нижеследующие разделы следует читать пользователям, владеющими базовыми понятиями математической статистики (случайная величина, функция распределения) .
Функция ПРОЦЕНТИЛЬ.ВКЛ()
Начиная с версии MS EXCEL 2010 для расчета процентилей используется функция ПРОЦЕНТИЛЬ.ВКЛ() – английское название PERCENTILE.INC(). В более ранних версиях MS EXCEL использовался ее аналог - функция ПЕРСЕНТИЛЬ() .
Напомним определение процентиля , данное выше: K-й Процентиль представляет такое собой значение Х в наборе данных, которое разделяет набор на две части: одна часть содержит K% данных, меньших Х , а другая часть содержит все остальные значения набора (т.е. 1- K % данных б о льших Х).
Разберем детально как работает функция ПРОЦЕНТИЛЬ.ВКЛ() .
Пусть имеется массив значений ( выборка ). В массиве 49 значений, массив расположен в диапазоне B15:B63 , имеются повторы значений , массив для удобства отсортирован по возрастанию (см. файл примера , лист ПРОЦЕНТИЛЬ.ВКЛ ).
Проанализируем, что мы получили.
Как видно из рисунка ниже первое значение ( минимальное , равное 1) является 0-й процентилью .
Соответственно, 1-й процентилью (100% процентилью ), является максимальное значение равное 120 (см. файл примера лист ПРОЦЕНТИЛЬ.ВКЛ).
Поясним эту формулу. Для вычисления процентили принимается, что весь диапазон значений массива (от мин до макс) разбит n значениями на равные интервалы (их всего n-1). Соответственно, 1/(n-1), это «ширина» интервала (весь диапазон равен 1 или 100%). Обратите внимание, что «ширина» интервала не зависит от данных, а только от их количества. В нашем случае «ширина» интервала равна 0,0208 или 2,08%.
Приведем алгоритм расчета 12,50%-процентили функцией ПРОЦЕНТИЛЬ.ВКЛ() (см. ячейку С21 ):
- ПРОЦЕНТИЛЬ.ВКЛ() определяет «ширину» интервала (в долях или процентах): =1/(49-1)=0,0208;
- подсчитывает Количество интервалов , которые были укладываются в 12,50%, т.е. =12,50%/2,08%=6 (значение процентиля кратно ширине интервала, т.е. делится нацело);
- 6-й интервал располагается между числами массива 10 и 11. Верхняя граница последнего 6-го интервала равна 11;
- Следовательно, 12,50%-я процентиль равна 11 (см. ячейку B21 ).
По аналогии с непрерывной функцией распределения (см. статью про квантили ), получается, что 12,50% значений должно быть меньше полученного числа 11 (в соответствии с определением процентиля ). Фактически получается, что таких значений 6 (1; 2; 3; 4; 5; 10) и процент значений меньших 11 равен 12,24%=6/49 (причины расхождения: повторы и небольшое количество значений).
Альтернативный расчет процентили с помощью формул приведен в файле примера .
Примечание : Некоторые значения процентилей имеют специальные названия:
- 25-я процентиль называется 1-й квартилью;
- 50-я процентиль называется Медианой (2-я квартиль);
- 75-я процентиль называется 3-й квартилью.
Функция ПРОЦЕНТРАНГ.ВКЛ() и Кривая процентилей
Функция ПРОЦЕНТРАНГ.ВКЛ() используется для оценки относительного положения значения в массиве. Для заданного значения функция вычисляет сколько значений в массиве меньше или равно ему. Точнее - какой процент значений массива меньше или равен ему. Результат функции называется процентиль-ранг (percentile rank) . Понятно, что для максимального значения процентиль-ранг равен 0,00%, а для наименьшего - 100% (все значения массива меньше или равны ему).
Функция ПРОЦЕНТРАНГ.ВКЛ() , английская версия – PERCENTRANK(), является, в каком-то смысле, обратной функции ПРОЦЕНТИЛЬ.ВКЛ() : т.е. задавая в качестве аргумента значение из массива, функция ПРОЦЕНТРАНГ.ВКЛ() вернет значение процентили кратной 1/(n-1).
Как видно из рисунка выше, для повторяющихся значений функция ПРОЦЕНТРАНГ.ВКЛ() вернет, естественно, одинаковые значения. Также поступает функция РАНГ.РВ() или РАНГ() (см. статью Функция РАНГ() в MS EXCEL ).
Действительно, функции РАНГ.РВ() и ПРОЦЕНТРАНГ.ВКЛ() очень похожи. Первая возвращает позицию числа в массиве в зависимости от его значения. Вторая, в принципе, делает тоже самое, но результат выводится в % от общего количества значений в массиве.
Как видно из картинки выше, чтобы получить процентиль-ранг необходимо значение ранга уменьшить на 1 и разделить на n-1. Значение ранга , естественно, должно быть отсортировано по возрастанию .
По выборке можно оценить функцию распределения Генеральной совокупности , из которой взята данная выборка. Для этой цели построим Кривую процентилей (percentile curve или percentile rank plot). Кривая процентилей представляет собой график зависимости процентиль-ранга от значений выборки .
Возьмем выборку состоящую из 100 значений (см. файл примера лист Кривая процентилей ). Значения содержатся в диапазоне А5:А104 .
Сначала построим таблицу частот для каждого из значений выборки .
Примечание : В отличие от Гистограммы , где кумулятивная таблица частот строится для интервалов значений, таблицу частот для Кривой процентилей строят для каждого из значений выборки .
Из таблицы видно (столбец Частота нарастающим итогом ), что примерно 1 процент значений меньше или равен значения 3,27, примерно 2 процента на уровне или ниже 3,28, 5 процентов на уровне или ниже 3,29, и так далее. График Кривой процентилей для этих данных приведен на картинке ниже.
СОВЕТ : Про построение графиков см. статью Основные типы диаграмм .
Следует отметить, что использование данных из таблицы приведет к точечному виду кривой (так как процентиль-ранг будет изменяться скачком для каждого значения выборки ). Поэтому, сглаженная кривая, построенная на основе этих данных будет лучше представлять оцениваемую функцию распределения (пунктирная кривая).
Построив пунктирную кривую, становится ясно, зачем нам пришлось вводить понятие процентиль-ранга: процентиль-ранг – является приблизительной вероятностью выбрать случайную величину меньше или равную соответствующему значению (сравните с определением функции распределения). Это, в частности следует из расчета процентиль-ранга по формуле =СЧЁТЕСЛИ($A$5:$A$104;"
Обратите внимание, что при построении Кривой процентилей никакие значения из выборки не были удалены или сгруппированы. В этом смысле, построение Кривой процентилей это более точная процедура для оценки вида функции распределения , чем построение Гистограммы данных (так как информация не теряется в процессе построения). Правда, для этого требуется достаточно большая выборка (лучше >100 значений).
Примечание : Формула =(РАНГ.РВ(A5;$A$5:$A$104;1)-1)/ (СЧЁТ($A$5:$A$104)-1) эквивалентна формуле =ПРОЦЕНТРАНГ.ВКЛ($A$5:$A$104;A5;5)
Функция ПЕРСЕНТИЛЬ в Excel предназначена для определения k-й доли перцентили для числовых значений исследуемого интервала и возвращает соответствующий результат.
Метод перцентилей в Excel по функции ПЕРСЕНТИЛЬ с примерами
Предположим, имеется вариационный ряд данных с минимальным и максимальным значениями, обозначаемых P0 и P100 соответственно. K-й перцентиль – это некоторое значение X из данного ряда, которое делит все имеющиеся в нем значения на две группы: K% значений, которые меньше X, и оставшиеся значения (то есть 1-K%), которые превышают X.
Для определения перцентилей необходимо:
- Отсортировать значения в исследуемом ряде данных в порядке возрастания.
- Найти некоторое значение в отсортированном ряде, для которого K% значений будут меньшими данного значения. При ручном расчете можно использовать формулу n*K%-1, где n – число элементов в исследуемом ряде значений.
- Определенное выше значение является K-й перцентилю по определению.
Функция ПЕРСЕНТИЛЬ считается устаревшей после выхода MS Office версии 2010 года, в которую были включены функции ПРОЦЕНТИЛЬ.ИСКЛ и ПРОЦЕНТИЛЬ.ВКЛ, которые в совокупности предлагают расширенный функционал для расчетов. Рассматриваемая функция была оставлена для совместимости с более старыми версиями табличного редактора.
Пример расчета перцентиля с использованием функции ПЕРСЕНТИЛЬ в Excel
Пример 1. В магазин будет завезена новая партия обуви. Ранее в рамках маркетингового исследования были записаны размеры ног 10 случайных клиентов. На основании имеющихся данных определить размер обуви, являющийся пороговым значением для 90% клиентов.
Вид таблицы данных:
Для расчета используем функцию:
- B3:B12 – исследуемый ряд значений;
- 0,9 – число, указывающее, что необходим поиск 90-й перцентили (0,9=90%).
В результате вычислений формулы получен 90 перцентиль. Найденное значение не соответствует ни одному из рассматриваемого ряда, поскольку функция ПЕРСЕНТИЛЬ выполнила интерполяцию данных. 90% клиентов покупают обувь до 41 размера включительно.
Как рассчитать перцентиль в Excel с помощью функции ПЕРСЕНТИЛЬ
Пример 2. В таблице введен ряд некоторых значений. Необходимо:
- Определить, во сколько раз 80-й перцентиль превышает 20-й перцентиль.
- Рассчитать 40-й перцентиль без использования рассматриваемой функции.
Вид таблицы данных:
Для поиска значения соотношения используем следующую запись:
То есть, для исследуемого ряда значений 80-й перцентиль превышает 20-й почти в 4,5 раз.
Альтернативный способ нахождения перцентиля – следующая формула:
Данная запись соответствует формуле, указанной в определении понятия перцентиль. Результат вычислений:
Рядом (справа) указано значение, полученное с использованием функции ПЕРСЕНТИЛЬ:
Значения отличаются, поскольку рассматриваемая функция выполняет интерполяцию данных.
Читайте также: