Как посчитать дециль в excel

Обновлено: 06.07.2024

Функция ПЕРСЕНТИЛЬ в 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 раз.

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

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

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

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

Quartile, как звучит его название, представляет собой статистический термин, который делит данные на четверти или четыре определенных интервала. Он в основном делит точки данных на набор данных в 4 кварталах на числовой строке. Мы должны помнить одну вещь: точки данных могут быть случайными, и мы должны сначала разместить эти числа в строке чисел в порядке возрастания, а затем разделить их на квартили. Это в основном расширенная версия медианы. Медиана делит данные на две равные части, а квартили делят их на четыре части. Как только мы разделим данные, четыре квартиля будут:

1- й квартиль или нижний квартиль в основном отделяют самые низкие 25% данных от самых высоких 75%.
2- й квартиль или средний квартиль, также как и медиана, делит числа на 2 равные части.
3- й квартиль или верхний квартиль отделяют самые высокие 25% данных от самых низких 75%.

Формула для квартиля:

Допустим, у нас есть набор данных с N точками данных:

X - (X1, X2, X3 ……… .. XN)

Формула для квартилей имеет вид:

Lower Quartile (Q1) = (N+1) * 1 / 4 Middle Quartile (Q2) = (N+1) * 2 / 4 Upper Quartile (Q3 )= (N+1) * 3 / 4 Interquartile Range = Q3 – Q1

В основном это означает, что в наборе данных с N точками данных:

((N + 1) * 1/4) -й член является нижним квартилем

((N + 1) * 2/4) -й член - средний квартиль

((N + 1) * 3/4) -й член - верхний квартиль

Межквартильный диапазон в основном расстояния между нижним квартилем и верхним квартилем.

Примеры формулы Quartile (с шаблоном Excel)

Давайте рассмотрим пример, чтобы лучше понять расчет Quartile.

Вы можете скачать этот шаблон Excel Quartile Formula здесь - Шаблон Excel Quartile Formula

Формула квартиля - пример № 1

Допустим, у нас есть наборы данных A, которые содержат 19 точек данных. Рассчитать квартиль для набора данных А.

Набор данных:

Прежде всего, вы должны расположить этот порядок возрастания, т.е. от низшего к высшему:

Количество точек данных рассчитывается как:

Квартиль рассчитывается по приведенной ниже формуле

Нижний квартиль (Q1) = (N + 1) * 1/4

Нижний квартиль (Q1) = (19 + 1) * 1/4
Нижний квартиль (Q1) = 20/4 = 5- я точка данных

Итак, нижний квартиль (Q1) = 29

Средний квартал (Q2) = (N + 1) * 2/4

Средний квартал (Q2) = (19 + 1) * 2/4
Средний квартал (Q2) = 40/4 = 10- я точка данных

Средний квартал (Q2) = 43

Верхний квартиль (Q3) = (N + 1) * 3/4

Верхний квартиль (Q3) = (19 + 1) * 3/4
Верхняя четверть (Q3) = 60/4 = 15- я точка данных

Итак, верхний квартиль (Q3) = 67

Межквартильный диапазон рассчитывается по формуле, приведенной ниже

Межквартильный диапазон = Q3 - Q1

Межквартильный диапазон = 15–5
Межквартильный диапазон = 10- я точка данных

Итак, межквартирный диапазон = 43

Если вы видите набор данных, медиана этого набора: (n + 1) / 2 = 20/2 = 10- е значение, т.е. 43, это то же самое, что и Q2.

Значение 29 делит набор данных таким образом, что самые низкие 25% находятся выше него, а самые высокие 75% находятся ниже него
Значение 43 делит набор данных на две равные части
Значение 67 делит набор данных таким образом, что самые высокие 25% находятся ниже него, а самые низкие 75% находятся выше него

Формула квартиля - пример № 2

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

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

Вы собрали образец из 15 спортсменов из разных видов спорта. Рассчитать квартиль.

Набор данных приведен ниже:

Расположите размер обуви в порядке возрастания.

Квартиль рассчитывается по приведенной ниже формуле

Нижний квартиль (Q1) = (N + 1) * 1/4

Нижний квартиль (Q1) = (15 + 1) * 1/4
Нижний квартиль (Q1) = 16/4 = 4- я точка данных

Итак, нижний квартиль (Q1) = 10

Средний квартал (Q2) = (N + 1) * 2/4

Средний квартал (Q2) = 10

Верхний квартиль (Q3) = (N + 1) * 3/4

Верхняя четверть (Q3) = (15 + 1) * 3/4
Верхняя четверть (Q3) = 48/4 = 12- я точка данных

Итак, верхний квартиль (Q3) = 11

Межквартильный диапазон рассчитывается по формуле, приведенной ниже

Межквартильный диапазон = Q3 - Q1

Итак, межквартирный диапазон = 10

объяснение

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

Актуальность и использование формулы Quartile

Как обсуждалось выше, формула квартиля помогает нам очень быстро разделить данные на четыре части и, в конечном итоге, облегчает понимание данных в этих частях. Например, учитель класса хочет наградить 25% лучших учеников лакомствами и подарками и хочет дать еще один шанс 25% учеников улучшить свой результат. Он может использовать квартили и может делить данные. Таким образом, если квартили говорят 51, 65, 72, а ученик говорит 78, он получит вкусности. Если у другого ученика будет 48 баллов, у него будет еще один шанс улучшить его, быстро и легко интерпретировать.

Как рассчитать дисперсию в Excel?

Давайте разберемся, как рассчитать дисперсию в Excel на нескольких примерах.

Вы можете скачать этот шаблон Variance Excel здесь - шаблон Variance Excel.

Пример № 1 - Расчет дисперсии в Excel для всей совокупности

Если набор данных предназначен для полной совокупности, то нам нужно использовать функцию VAR.P из excel. Это потому, что в Excel у нас есть две функции, которые предназначены для разных наборов данных.

У нас могут быть данные, которые собираются на основе выборки, которой может быть население всего мира.

VAR.P использует следующую формулу:

Шаг 1 - Введите набор данных в столбцах.

Шаг 2 - Вставьте функцию VAR.P и выберите диапазон набора данных. Здесь следует отметить одну вещь: если какая-либо ячейка имеет ошибку, эта ячейка будет игнорироваться.

Шаг 3 - После нажатия клавиши Enter мы получим дисперсию.

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

Пример № 2 - Расчет дисперсии для размера выборки в Excel

Если у нас есть набор данных, представляющий образцы, нам нужно использовать функцию VAR.S вместо использования VAR.P

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

VAR.S использует следующую формулу:

Шаг 1 - Введите набор данных в столбце.

Шаг 2 - Вставьте функцию VAR.S и выберите диапазон набора данных.

Шаг 3 - Мы получим дисперсию.

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

Пример № 3 - Расчет кванта дисперсии для данных в Excel

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

Если нам нужно проверить изменение, то нам нужно использовать следующий метод.

Шаг 1 - Рассчитайте разницу между двумя данными, используя функцию вычитания.

Шаг 2 - После нажатия клавиши Enter мы получим результат. Чтобы получить дисперсию целых данных, нам нужно перетащить формулу, примененную к ячейке C2.

Шаг 3 - Теперь дисперсия может быть как положительной, так и отрицательной, и это будет вычисленная дисперсия.

Пример № 4 - Расчет процента дисперсии для набора данных в Excel

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

Шаг 1 - Во-первых, рассчитать дисперсию из метода 3-го.

Шаг 2 - Теперь рассчитайте процент с помощью функции ниже.

Изменение значения / исходного значения * 100. Это будет наше процентное изменение в наборе данных.

Шаг 3 - Чтобы получить процент дисперсии целых данных, мы должны перетащить формулу, примененную к ячейке D2.

Что нужно помнить о дисперсии в Excel

Если у нас есть набор данных, который представляет полную совокупность, то нам нужно использовать функцию VAR.P
Если у нас есть набор данных, который представляет образцы из мировых данных, то нам нужно использовать функцию VAR.S
Здесь S представляет образцы.
Если мы рассчитываем изменение в терминах кванта, то отрицательное изменение означает увеличение фактической стоимости, а положительное изменение означает уменьшение стоимости.
В случае использования VAR.P аргументы могут быть числом или именем, массивами или ссылкой, содержащей числа.
Если какая-либо из ячеек, указанных в качестве ссылки в формуле, содержит ошибку, эта ячейка будет проигнорирована.

Пример вычисления формулы процентиля в Excel

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

В программе Excel персентильную классификацию можно легко определить при использовании функции ПЕРСЕНТИЛЬ. Данная функция имеет 2 аргументы:

Массив – диапазон исходных данных.
К – значение найденного процентиля (чаще всего число в десятичной дроби диапазоном от 0 и до 1).

В примере, изображенном ниже на рисунке ячейка D6 содержит значение, которое является результатом вычисления ниже указанной формулы – число 0,75 процентиля данных диапазоне ячеек $B$2:$B$19:

Результат выше приведенной формулы указывает на то, что каждый работник, для которого годовые обороты превышают 52651 работает лучше, чем 75% всех остальных сотрудников.

Ячейка D15 содержит результат вычисления формулы, которая возвращает число 25 процентиля данных в диапазоне ячеек $B$2:$B$19.

Результат выше приведенной формулы указывает на то, что каждый работник, для которого годовой оборот не превышает 24656 находится среди 25% самых слабых сотрудников.

В данном примере используется условное форматирование использующие выше приведенные значения перцентилей. Значения больше чем 75 перцентиля выделены зеленым цветом, а значения меньше чем 25 перцентиля выделены красным цветом.

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

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

Выделите диапазон ячеек B2:B19, которые будут автоматически выделятся цветом по условию формулы и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего высветится окно как ниже на рисунке:
В верхней части окна находится список опций. Выберите из него опцию «Использовать формулу для определения форматируемых ячеек». Данная опция служит для преобразования формата в ячейках в зависимости от их значений с помощью определенной формулы с логическим выражением. Если в результате вычисления формулы будет возвращено логическое значение ИСТИНА, тогда к текущей ячейке будет применено условное форматирование.
В полю ввода введите формулу с логическим выражением, которая представлена ниже в этом разделе. Данная формула проверяет: если значение в целевой ячейке B2 меньше чем значение 25 перцентиля, тогда ей присваивается новый формат красного цвета фона для экспонирования.

В результате к одному и тому же диапазону ячеек одновременно применяется 2 правила условного форматирования. В следствии чего одна группа значений выделена красным цветом значение которых меньше 25% показателей, а вторая группа – зеленым >75%.

Читайте также: