Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Персентиль в excel

Примеры функции ПЕРСЕНТИЛЬ для расчета перцентиля в Excel

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

Метод перцентилей в Excel по функции ПЕРСЕНТИЛЬ с примерами

Предположим, имеется вариационный ряд данных с минимальным и максимальным значениями, обозначаемых P0 и P100 соответственно. K-й перцентиль – это некоторое значение X из данного ряда, которое делит все имеющиеся в нем значения на две группы: K% значений, которые меньше X, и оставшиеся значения (то есть 1-K%), которые превышают X.

Для определения перцентилей необходимо:

  1. Отсортировать значения в исследуемом ряде данных в порядке возрастания.
  2. Найти некоторое значение в отсортированном ряде, для которого K% значений будут меньшими данного значения. При ручном расчете можно использовать формулу n*K%-1, где n – число элементов в исследуемом ряде значений.
  3. Определенное выше значение является K-й перцентилю по определению.

Функция ПЕРСЕНТИЛЬ считается устаревшей после выхода MS Office версии 2010 года, в которую были включены функции ПРОЦЕНТИЛЬ.ИСКЛ и ПРОЦЕНТИЛЬ.ВКЛ, которые в совокупности предлагают расширенный функционал для расчетов. Рассматриваемая функция была оставлена для совместимости с более старыми версиями табличного редактора.

Пример расчета перцентиля с использованием функции ПЕРСЕНТИЛЬ в Excel

Пример 1. В магазин будет завезена новая партия обуви. Ранее в рамках маркетингового исследования были записаны размеры ног 10 случайных клиентов. На основании имеющихся данных определить размер обуви, являющийся пороговым значением для 90% клиентов.

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

Для расчета используем функцию:

  1. B3:B12 – исследуемый ряд значений;
  2. 0,9 – число, указывающее, что необходим поиск 90-й перцентили (0,9=90%).

В результате вычислений формулы получен 90 перцентиль. Найденное значение не соответствует ни одному из рассматриваемого ряда, поскольку функция ПЕРСЕНТИЛЬ выполнила интерполяцию данных. 90% клиентов покупают обувь до 41 размера включительно.

Как рассчитать перцентиль в Excel с помощью функции ПЕРСЕНТИЛЬ

Пример 2. В таблице введен ряд некоторых значений. Необходимо:

  1. Определить, во сколько раз 80-й перцентиль превышает 20-й перцентиль.
  2. Рассчитать 40-й перцентиль без использования рассматриваемой функции.

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

Для поиска значения соотношения используем следующую запись:

То есть, для исследуемого ряда значений 80-й перцентиль превышает 20-й почти в 4,5 раз.

Альтернативный способ нахождения перцентиля – следующая формула:

Данная запись соответствует формуле, указанной в определении понятия перцентиль. Результат вычислений:

Рядом (справа) указано значение, полученное с использованием функции ПЕРСЕНТИЛЬ:

Значения отличаются, поскольку рассматриваемая функция выполняет интерполяцию данных.

Что такое функция ПЕРСЕНТИЛЬ и как с ней работать в Excel

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

  • массив – обязательный для заполнения, принимает статический массив числовых данных или ссылку на диапазон ячеек с числами, для которых требуется вычислить значение k-го перцентиля;
  • k – обязательный для заполнения, принимает числовые значения из диапазона от 0 до 1 (оба включительно), характеризующие номер перцентили для расчета (например, 0,25 – 25-я перцентиль, 0,5 – 50-я перцентиль).
  1. Перцентиль удобен для установления критериев отбора каких-либо данных. Например, на вступительных экзаменах почти все студенты не смогли преодолеть проходной порог (минимальное количество баллов для поступления в ВУЗ). Чтобы избежать недобора, можно ввести другой критерий – перцентиль, который поможет отобрать лучших абитуриентов на основании имеющихся данных о баллах за экзамены, а не установленных ранее критериев (проходного балла).
  2. Если исследуемый ряд (указан в виде аргумента массив) содержит нечисловые данные (текст, логические ИСТИНА или ЛОЖЬ, имена), функция исключает их из расчетов. Например, =ПЕРСЕНТИЛЬ(<1;4;5;7;11>;0,5) вернет значение 5, а =ПЕРСЕНТИЛЬ(<1;4;"е";7;11>;0,5) – 5,5.
  3. Функция возвращает код ошибки #ЗНАЧ!, если аргумент k указан в виде нечисловых данных (имя или текст, не преобразуемые в число). Нечисловые данные, преобразуемые к числам, являются допустимыми вариантами указания аргумента k . Например, =ПЕРСЕНТИЛЬ(<1;4;5;7;11>;”0,5”) вернет значение 5, =ПЕРСЕНТИЛЬ(<1;4;5;7;11>;ИСТИНА) – 11.
  4. Рассматриваемая функция генерирует код ошибки #ЧИСЛО!, если аргумент k задан в виде числа не из диапазона допустимых значений, то есть >1 или k , не кратных 1/(n-1), функция интерполирует данные для расчетов (n – число элементов массива).

Процентили

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

Например, значение 30-й процентили указывает, что 30% значений располагается ниже этого уровня.

На конкретном примере поясним понятие процентиля:

Пример 1 . Группа студентов из 20 человек получила на экзамене по статистике следующие балы: три студента — 5 баллов, 8 студентов — 4 балла, 6 студентов — 3 бала и 3 студента — 2 балла. Вычислить процентиль успеваемости каждого студента.

Решение.

Формула процентиля

Процентиль = n(x≤X)/N*100

n(x≤X) — число студентов, получивших бал не менее X ,

X — количество балов конкретного студента, процентиль которого находим ,

N — число всех студентов .

Для удобства вычислений ранжируем выборку балов от максимального значения до минимального ( в порядке убывания): 5,5,5,4,4,4,4,4,4,4,4,3,3,3,3,3,3,2,2,2

Допустим нам необходимо определить процентиль студента Иванова получившего на экзамене 5 баллов:

Находим n(x≤X)=n(x≤5)=20 — т.е. 20 студентов получили бал не выше 5, тода

Процентиль (Иванова) = 20/20*100=100

Допустим необходимо определить процентиль студента Петрова получившего на экзамене 4 балла:

Находим n(x≤X)=n(x≤4)=17 — т.е. 17 студентов получили бал не выше 4, тода

Процентиль (Петрова) = 17/20*100=85

Допустим необходимо определить процентиль студента Сидорова получившего на экзамене 3 балла:

Находим n(x≤X)=n(x≤3)=9 — т.е. 9 студентов получили бал не выше 3, тода

Процентиль (Иванова) = 9/20*100=45

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

Таблица стандартизации

БалПроцентили
5100
485
345
215

Алгоритм расчета процентилей

1. Для каждого человека посчитать, какое количество человек набрало столько же или меньше баллов.

2. Посчитать сколько процентов составляет это количество от всей выборки.

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

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

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

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

Расчет процентилей в Excel

Для расчета процентилей нам понадобится функция СЧЕТЕСЛИ.

Для расчета, для каждого значения нужно ввести формулу:

=(СЧЁТЕСЛИ(диапазон;условие)*100)/N , где N – количество человек.

Перцентили

Заметим, что перцентиль представляет собой какой-то элемент массива, имеющий определенный ранг и выраженный в тех же единицах, что и сам массив в целом. Так, 60-й перцентиль эффективности сбора металлолома в конторе «Ржавая подкова» составляет, скажем, 85062 руб. (измерен не в процентах, а в рублях, как элемент набора данных). Если этот 60-й перцен- тиль, равный 85062 руб., характеризует деятельность определенного агента по заготовкам (например, г-на Пупкина), то это означает, что примерно 60 % других тружеников имеют результат ниже, чем у г-на Пупкина, а 40 % — более высокие показатели.

Перцентили используются для двух целей:

чтобы показать значение элемента в массиве при заданном перцен- тильном ранге (например, «10-й перцентиль равен 46293 руб.»);

чтобы показать перцентильный ранг значения данного элемента в рассматриваемом массиве статистических данных (например, «эффективность заготовок металлолома агента г-на Козлевича составляет 65994 руб., что соответствует 55-му перцентилю»).

Продолжим рассмотрение нашей задачи. В диалоговом окне Ранг и персентиль заполним поле Входной интервал (рис.7).

Рис. 7. Диалоговое окно Ранг и перцентиль

В нем укажем данные 2-й графы табл. 3 (вместе с заголовком), относящиеся к фирме «Колокольный звон» (это диапазон ячеек $B$1:$B$13). Отметим флажком позицию Метки в первой строке (поскольку нам нужно со-хранить заголовок этой графы), а затем в окне Выходной интервал укажем ячейку $I$1, в которой будет размещена таблица с рассчитанными показателями рангов и перцентилей. После этого — кнопка ОК.

Затем аналогичным образом поступим с данными 3-й графы (сведения от фирмы «Мельхиор»). При заполнении диалогового окна Ранг и персентиль отметим диапазон ячеек $С$1:$С$13, а для опции Выходной интервал покажем ячейку, которая должна быть по соседству с первой половинкой нашей общей таблицы. Это ячейка $М$1.

В окончательном виде наша таблица примет следующий вид (рис.8).

Как видно, Excel аккуратно проранжировал результаты по каждому эпизоду, расположив студентов по местам в соответствии с их материальны- ми успехами, а также указал их перцентильный ранг (в %). Для дальнейших рассуждений данные по перцентилям мы использовать не станем, а вот ранги окажутся совершенно необходимыми. 1 J К L М N 0 Р Точка Фирма «Колокольный звон» Ранг Процент Точка Фирма «Мельхиор» Ранг Процент 11 3,5 1 100,00% 12 4,5 1 100,00% 12 3,4 2 90,90% 4 4,1 2 90,90% 6 3,3 3 81,80% 1 3,3 3 81,80% 4 3,2 4 72,70% 9 3,2 4 72,70% 2 3,1 5 63,60% 11 3,1 5 63,60% 1 2,8 6 54,50% 2 3 6 54,50% 9 2,5 7 45,40% 3 2,8 7 45,40% 5 2,4 8 36,30% 6 2,7 8 36,30% 10 2,3 9 27,20% 10 2,6 9 27,20% 7 2,2 10 18,10% 7 2,5 10 18,10% 3 2 11 9,00% 8 2,3 11 9,00% 8 1,8 12 ,00% 5 2,1 12 ,00% Рис. 8. Расчетная таблица с показателями рангов и перцентилей

На основании ранговых оценок организуем сводную таблицу, аналогичную уже знакомой нам табл.3 (рис.9). Для удобства перейдем на другой рабочий лист (Лист 2). Для выполнения последующих расчетов используем итоговый результат, отражающий сумму разностей квадратов рангов, равную 105. Оформим вспомогательную таблицу (рис.9), в которой укажем значение ScF = 105, размер выборки n = 12, а также предусмотрим в ней ячейку, где поместим рассчитанное значение коэффициента ранговой корреляции р (ячейка Е22).

Поместим курсор в ячейку Е22, а затем в поле формулы запишем уравнение, по которому будем рассчитать коэффициент р. Выглядит оно так:

= 1 — 6*(Е20)/(Е21*(Е21А2 — 1)) В ячейке появится искомый результат 0,632867. С округлением принимаем его равным 0,633 — коэффициент оказался именно таким, каким мы его вычислили «вручную».

Полученный результат показывает, что в данной ситуации надлежит высказать совершенно те же соображения по поводу исследуемого процесса, какие были сделаны для случая расчета коэффициента р традиционным способом. При доверительной вероятности 0,95 студенты вполне могут горделиво полагать, что их материальные достижения всецело определяются личным усердием и не зависят от каких-то иных привходящих факторов. Однако требование более строгой оценки (с вероятностью 99 %) делает такое мнение менее очевидным и для значимого статистического вывода возникает необходимость расширить выборку (привлечь для анализа большее число студентов) либо (при невозможности это сделать) отнестись к результату вполне философски.

Читать еще:  End if vba excel
Ссылка на основную публикацию
Adblock
detector