Как посчитать дециль в excel
Функция ПЕРСЕНТИЛЬ в 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
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
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
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 баллов, у него будет еще один шанс улучшить его, быстро и легко интерпретировать.
Рекомендуемые статьи
Это было руководство к Quartile Formula. Здесь мы обсудим определение и как рассчитать Quartile вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше -
Дисперсия используется в тех случаях, когда у нас есть некоторые бюджеты, и мы можем знать, какие отклонения наблюдаются при исполнении бюджетов. В некоторых случаях термин дисперсия также используется для расчета разницы между запланированным и фактическим результатом, который был получен. Вычисление отклонений является отличным способом анализа данных, так как это позволяет нам определить разброс вариаций в наборе данных.
Дисперсия - это не что иное, как информация, показывающая, насколько хорошо распространяются данные. Расчет дисперсии требуется особенно в тех случаях, когда мы выполняли выборку данных. Теперь важно, чтобы мы использовали правильную функцию для вычисления дисперсии, например VAR.S или VAR.P. У нас есть несколько случаев для расчета дисперсии в Excel, где у нас есть некоторые данные, которые были спроектированы на период, и мы хотим сравнить это с фактическими цифрами.
Как рассчитать дисперсию в 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 вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Процентильная шкала определяет позицию определенного значения среди других данных в базе. Процентили в первую очередь используются для описания стандартных результатов тестов. Если же результат в стандартном тесте находится в 90-ом процентиле – это значит, что данный результат является выше чем 90% результатов показателей которые принимают участие в тесте. Другими словами, результат находится среди 10% самых высоких показателей, использованных в тесте.
Пример вычисления формулы процентиля в 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%.
Читайте также: