Как посчитать дециль в 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. Определяем номер дециля по формуле: ,

2. Определяем децильный интервал. Это первый интервал, для которого накопленная частота будет больше или равна номеру дециля.

3. Рассчитываем дециль по формуле:

где – значение j-го дециля,

— нижняя граница децильного интервала;

— ширина децильного интервала;

– сумма всех частот,

-накопленная частота интервала, предшествующего децильному;

— частота децильного интервала.

Пример. Найти 9-ый дециль D₉

Заработная плата рабочего, тыс.руб;	бригада 1
, число рабочих	, накопленная частота
Итого:

1) Определяем номер 9-го дециля

для первой бригады ;

2) Номер дециля — нецелое число. Для определения дециля нужны значения двух элементов – х₆₈ и х₆₉. Значение дециля находится между ними.
Определяем их значение с помощью самой первой накопленной частоты большей или равной порядковым номерам элементов (68 и 69). Х₆₈= 20, х₆₉= 20.
Теперь определяем значение 9-го дециля:
D₇ = x₆₈+ (х₆₉ – х₆₈)×0,4=20 + (20 – 20)×0,2 =20тыс.руб.

Это значит, что заработная плата90% рабочих бригады не превышает 18 тыс.руб.

Пример. Найти седьмой дециль D₇ для интервального ряда.

1. Определяем номер седьмого дециля по формуле 7×

2. Седьмой дециль находится в возрастной группе 30-35 лет, так как это первый интервал, для которого накопленная частота больше (или равна) номера дециля (2272 2424,1).

3. Определяем седьмой дециль по формуле

4.

Это значит, что 70% студентов младше 30,97 лет.

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Для студентов недели бывают четные, нечетные и зачетные. 9955 — | 7744 — или читать все.

Определение коэффициента эластичности

1. Задача по статистике – Коэффициент эластичности.

Для данного товара коэффициент эластичности k эл = -0,5.

Как изменится потребление этого товара, если цены на него возрастут на 10%?

Коэффициент эластичности – это отношение процентного изменения спроса к процентному изменению цены.

Следовательно процентное изменение спроса = k эл*процентное изменение цены=

=-0,5*10=5%. То есть потребление снизится на 5%.

Найти децильный коэффициен, моду и медианну по следующим данным.

Средний доход, руб. / (чел. мес)

Количество человек, млн. чел

Накопленная частота, млн. чел

Мода рассчитывается по формуле:

,

Где – нижняя граница модального интервала.

– модальный интервал.

– частота модального интервала.

– частота интервала, предшествующего модальному.

– частота интервала, следующего за модальным.

Модальный интервал определяется по наибольшей частоте. Наибольшая частота в данной задаче 31,3, что соответствует интервалу от 4000 до 6000 рублей.

=

=4000+2000*(31,3-30,6) / (( 31,3-30,6)-( 31,3-25,3))=4209 рублей.

Таким образом самый частый доход 4209 рублей.

Медианна рассчитывается по формуле:

,

Где – нижняя граница медианного интервала.

– медианный интервал.

– f частота.

– частота медианного интервала.

– накопленная частота интервала, предшествующего медианному.

Медианный интервал определяется по накопленной частоте. Суммируются f частоты до тех пор, пока очередная накопленная частота не превысит середину совокупности. В данной задаче совокупность состоит из 147,5 млн человек. Поэтому суммируем f частоты пока не превысим 147,5 /2=7 3,75 млн человек. Это произойдет в интервале от 6000 до 8000 руб, поскольку накопленная частота данного интервала равна 87.2, т.е. больше половины совокупности.

Следовательно интервал от 6000 до 8000 руб является медианным интервалом. Накопленная частота интервала, предшествующего медианному, равна 61,9.

=

=6000+2000*(0,5*147,5-61,9) /2 5,3 = 6937 руб. / мес.

Значит половина людей в совокупности имеет доход менее 6937 рублей, а половина более 6937 рублей.

Расчет децильного коэффициента

Рассчитаем дециль №1 (10% совокупности).

=0.1*1 47,5 =14. 75 млн чел.(14,75 млн чел соответствует 1-му интервалу).

Таким образом, дециль №1 входит в 1-й интервал с доходом до 4000.

Дециль рассчитывается по формуле:

,

Где – нижняя граница децильного интервала (интервала, содержащего дециль).

– децильный интервал.

– f частота.

– частота децильного интервала.

– накопленная частота интервала, предшествующего децильному.

– коэффициент дециля (для дециля №1 10%, т.е. 0,1).

Рассчитаем первый дециль

=

=0+4000*(14,75-0) / 30,6 = 1928 рублей.

Рассчитаем дециль №9 (90% совокупности).

=0. 9 *14 7,5 = 132,75 млн чел.(соответствует интервалу 16000-20000, а накопленная частота, предшествующая интервалу, содержащему дециль, равно 132,6)

Рассчитаем девятый дециль

=

=16000+4000*(132,75-132,6) /7= 16086 рублей.

Децильный коэффициент рассчитывается по формуле

.

=16086 / 1928=8,34.

Таким образом, минимальный доход 10% самого богатого населения в 8,4 раза выше, чем максимальный доход 10% самого бедного населения.

Квартили и интерквартильный интервал (IQR) в 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;» . В результате получим, что 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.

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