Функции Excel PERCENTILE - Расчет k-го процентиля

Скачать пример рабочей книги

Загрузите образец книги

В этом руководстве показано, как использовать Функция ПРОЦЕНТИЛЬ в Excel в Excel для расчета процентиля.

ПРОЦЕНТИЛЬ Обзор функций

Функция PERCENTILE вычисляет k-й процентиль.

Чтобы использовать функцию PERCENTILE Excel Worksheet, выберите ячейку и введите:

(Обратите внимание, как появляются входные данные формулы)

Функция ПРОЦЕНТИЛЬ Синтаксис и входные данные:

1	= ПРОЦЕНТИЛЬ (массив; k)

множество - Массив чисел.

k - Значение процентиля от 0 до 1.

Что такое функция ПРОЦЕНТИЛЬ?

ПРОЦЕНТИЛЬ - одна из статистических функций Excel. Он возвращает k-й процентиль заданного диапазона данных.

ПРОЦЕНТИЛЬ - это функция «совместимости»

В Excel 2010 Microsoft заменила PERCENTILE двумя вариантами: PERCENTILE.INC и PERCENTILE. ИСКЛ.

PERCENTILE по-прежнему работает, поэтому старые таблицы, в которых он используется, будут работать в обычном режиме. Однако, если вам не требуется, чтобы таблица оставалась совместимой со старыми версиями Excel, следует использовать PERCENTILE.INC или PERCENTILE.EXC.

Мы обсудим все три, чтобы вы поняли все до мелочей.

Что такое процентиль?

Процентиль показывает, как значение сравнивается с другими значениями в заданном диапазоне. Когда значение находится на k-м процентиле, это означает, что оно выше, чем k% других значений в группе.

Например, если вы мужчина, живущий в США, и ваш рост 6 футов (180 см), вы находитесь в 71-м процентиле - вы выше 71% других мужчин в США. Если вы женщина и рост 180 см, то вы находитесь в группе 99^th процентиль - вы выше 99% других женщин.

Когда вы имеете дело с процентилями, обратите внимание, что 50^th процентиль равен медиане <> данных.

Функция Excel PERCENTILE сообщает вам, какова точка отсечения для данного процентиля, то есть, насколько высоким должно быть значение, чтобы оно попадало в k-й процентиль.

Как использовать функцию ПРОЦЕНТИЛЬ

Используйте PERCENTILE так:

1	= ПРОЦЕНТИЛЬ (C4: C13; F3)

Вот несколько друзей, которые решили вычислить 80^th процентиль роста их группы, и мы видим результат - 183,4 см.

Первый аргумент функции - это диапазон данных - их высота, которую мы выложили в C4: C13.

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

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

1	= ПРОЦЕНТИЛЬ (C4: C13; .8)

Или в процентах, например:

1	= ПРОЦЕНТИЛЬ (C4: C13, 80%)

И все они возвращают одно и то же значение.

Что нужно знать о PERCENTILE:

• Если определяемый вами процентиль не является числом, функция вернет #VALUE! ошибка
• Если процентиль меньше 0 или больше 1, вы получите # ЧИСЛО! ошибка
• Текст и пустые ячейки в диапазоне данных будут проигнорированы.

Два метода расчета процентилей

Давайте на секунду расставим наши данные по порядку:

Вы можете задаться вопросом, почему 80^th процентиль не 183, так как Чендлер - 8^th самый высокий человек в группе.

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

Excel предлагает два способа сделать это, что приводит нас к PERCENTILE.INC и PERCENTILE.EXC.

PERCENTILE.INC

Функция PERCENTILE.INC в Excel аналогична функции PERCENTILE. Часть «INC» - это сокращение от «включающий», потому что она может вычислять любой действительный процентиль (то есть что-либо от 0% до 100%).

Вы используете это так:

1	= ПРОЦЕНТИЛЬ.INC (C4: C13; F3)

Как видите, он возвращает тот же результат, что и ранее PERCENTILE.

Итак, возвращаясь к тому, почему 80^th процентиль не в точности равен росту Чендлера. Помните, что здесь мы выполняем инклюзивный расчет, поэтому мы включаем все значения k от 0 до 1 (или от 0% до 100%).

Это означает, что наша самая короткая подруга Дженис находится на нулевой отметке.^th процентиль, а Ричард, самый высокий, - это 100 человек.^th процентиль. Все другие друзья имеют равные приращения между двумя, и это приращение равно 1 / (n-1), где n - количество точек данных в диапазоне.

В нашем случае это:

1	1 / (10 - 1) = 11.111…

Это означает, что Чендлеру на самом деле не 80.^th процентиль, он на 77,777^th процентиль. Если мы подставим это число в PERCENTILE.INC, мы должны получить рост Чендлера …

… И мы делаем.

Линейная интерполяция

Теперь, когда указанное вами значение k не кратно 1 / (n-1), PERCENTILE.INC будет применять процесс, называемый линейной интерполяцией, для вычисления результата. Это звучит сложно, но в основном это означает, что Excel выберет одно из двух значений.

Так почему же мы получили 183,4, когда попросили 80?^th процентиль раньше?

Расчет работает следующим образом:

• 80^th процентиль падает между Чендлером и Россом, Чендлер на 77,777^th процентиль и Росс на 88,888-м месте.
• 80^th процентиль на 2,222% опережает рост Чендлера
• Мы знаем, что расстояние между высотами Чендлера и Росса составляет 11%.
• 222% / 11,111% = 20% после округления. Теперь мы знаем, что 80^th процентиль - это 20% расстояния между высотами Росса и Чендлера.
• Разница между ростом Росса в 185 см и ростом Чендлера в 183 см составляет 2 см.
• 20% 2 см составляет 0,4 см
• Добавьте это к росту Чендлера, и мы получим 183,4 см.

PERCENTILE.EXC

Вы используете PERCENTILE.EXC практически таким же образом:

1	= ПРОЦЕНТИЛЬ.ИСКЛ (C4: C13; F3)

Итак, у нас одинаковые данные, нам нужен одинаковый процентиль, но мы получили разные результаты. Это почему?

Это связано с тем, что PERCENTILE.EXC исключает первое и последнее значения при вычислении интервалов процентилей. См. ниже:

Вместо того, чтобы интервал равнялся 1 / (n-1), с PERCENTILE.EXC интервал равен 1 / (n + 1), или в данном случае 9,091%.

Все остальное работает так же, как и с PERCENTILE.INC. Опять же, 80^th процентиль находится между Чендлером и Россом, поэтому Excel применяет тот же метод линейной интерполяции:

• 80^th процентиль падает между Чендлером и Россом, Чендлер на 72,727^th процентиль и Росс на 81,818-м месте.
• 80^th процентиль на 7,272% опережает рост Чендлера
• Мы знаем, что расстояние между высотами Чендлера и Росса составляет 9,091%.
• 272% / 9,091% = 80% после округления. Теперь мы знаем, что 80^th процентиль - это 80% расстояния между высотами Росса и Чендлера.
• Разница между ростом Росса в 185 см и ростом Чендлера в 183 см составляет 2 см.
• 80% 2 см составляет 1,6 см
• Добавьте это к росту Чендлера, и мы получим 184,6 см.

PERCENTILE в Google Таблицах

Функция ПРОЦЕНТИЛЬ в Google Таблицах работает точно так же, как и в Excel:

PERCENTILE примеров в VBA

Вы также можете использовать функцию ПРОЦЕНТИЛЬ в VBA. Тип:
application.worksheetfunction.percentile (массив, k)
Для аргументов функции (массив и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные, которые будут использоваться вместо них.

Вернуться к списку всех функций в Excel