Как посчитать квартиль в excel по данным таблицы • подсчет ячеек

PERCENTILE.INC

The Excel PERCENTILE.INC Function is the same as PERCENTILE. The “INC” part is short for inclusive, because it can calculate any valid percentile (that is, anything from 0% to 100%).

You use it like this:

As you can see, it returns the same result that PERCENTILE did earlier.

So, going back to why the 80th percentile isn’t exactly equal to Chandler’s height. Remember, we’re doing an inclusive calculation here, so we’re including all k values from 0 to 1 (or 0% to 100%).

This means that our shortest friend, Janice, is at the 0th percentile, and Richard, the tallest, is the 100th percentile. All the other friends are at equal increments between the two, and that increment is equal to 1/(n-1), where n is the number of data points in the range.

In our case, that’s:

That means Chandler is not actually at the 80th percentile, he’s at the 77.777th percentile. If we plug that number into PERCENTILE.INC, we should get Chandler’s height…

…and we do.

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

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

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

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

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

ПЕРСЕНТИЛЬ и КВАРТИЛЬ в Excel

Этот пример научит вас использовать функции PERCENTILE (ПЕРСЕНТИЛЬ) и QUARTILE (КВАРТИЛЬ) в Excel. На рисунке ниже вы видите список значений (зеленая заливка только для иллюстрации).

1. Используйте функцию PERCENTILE (ПЕРСЕНТИЛЬ), показанную ниже, чтобы вычислить 30-й процентиль. Excel возвращает значение 12,7. Это означает, что 30% (6 из 20) значений на рисунке ниже меньше или равны 12,7.

Примечание: Второй аргумент функции PERCENTILE (ПЕРСЕНТИЛЬ) должен быть десятичным числом между 0 и 1. Алгоритм расчета процентиля и квартиля в Excel не совсем такой, как в большинстве книг по статистике.

1. Используйте функцию PERCENTILE (ПЕРСЕНТИЛЬ), показанную ниже, чтобы вычислить 90-й процентиль. Excel возвращает значение 61,7. Это означает, что 90% (18 из 20) значений в диапазоне A1:A20 меньше или равны 61,7.

Используйте функцию QUARTILE (КВАРТИЛЬ), чтобы вычислить 1-й квартиль. Excel возвращает значение 11,25. Это означает, что 25% (5 из 20) значений меньше или равны 11,25.

Второй аргумент функции QUARTILE (КВАРТИЛЬ) должен быть числом между 0 и 4.

Формула =ПЕРСЕНТИЛЬ(A1:A20;0,25) дает точно такой же результат, что и =КВАРТИЛЬ(A1:A20;1).

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

Процентили в EXCEL

Рассмотрим понятие процентиля, функцию ПРОЦЕНТИЛЬ.ВКЛ() , процентиль-ранг и построим кривую процентилей.

Сначала разберемся на примерах, что такое процентиль , затем рассмотрим соответствующие функции MS EXCEL.

Задача. Проектируют койку на круизном лайнере. Необходимо, чтобы 95% пассажиров помещались на койке в полный рост. Как вычислить длину койки?

Для решения задачи потребуется найти рост, ниже которого 95% населения. Для этого нужно сделать репрезентативную выборку , скажем, из 2000 человек, отсортировать значения выборки по возрастанию , потом определить значение с позицией равной 1901 (2000*95%+1). Пусть найденный рост оказался равен 190 см. Ответ : Длина койки должна быть 190 см (+ запас для комфортного размещения на койке).

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

Примечание : Процентили часто называют перцентилями (с этим соглашается и MS WORD) или центилями . В версии MS EXCEL 2007 и более ранних использовалась функция ПЕРСЕНТИЛЬ() , которая оставлена для совместимости. Но, начиная с версии EXCEL 2010, появились функции ПРОЦЕНТИЛЬ.ВКЛ() и ПРОЦЕНТИЛЬ.ИСКЛ() – английское название PERCENTILE.EXC(), а Условное форматирование предлагает настроить правило с использованием именно процентилей . В свою очередь, надстройка Пакет Анализа имеет инструмент Ранг и Персентиль .

процентилям

Таким образом, для процентилей используется 3 названия: процентиль (MS EXCEL, Google) , персентиль (MS EXCEL) , перцентиль (MS WORD) .

Ниже детально рассмотрим как работает функция ПРОЦЕНТИЛЬ.ВКЛ() и создадим ее аналог с помощью альтернативной формулы. Также рассмотрим функцию ПРОЦЕНТРАНГ.ВКЛ() и кривую процентилей .

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

Как использовать функцию КВАРТИЛЬ

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

1	= КВАРТИЛЬ (C4: C14,2)

Группа учеников эксклюзивной школы сдала важный экзамен. Их оценки хранятся в C4: C13, а диапазон данных — это первый аргумент, который мы передаем в QUARTILE.

Следующий аргумент в функции, который мы называем «кварта”, Определяет значение, которое мы хотим вернуть. Здесь мы выбрали 2, второй квартиль / медиана. Медиана — это среднее число в диапазоне, а поскольку у нас 11 студентов, медиана — это 6th один — 74 балла Жана, которые я выделил в таблице.

Несколько полезных вещей, которые нужно знать о QUARTILE:

• Если кварт не является числом, вы получите # ЗНАЧ! ошибка
• Если в кварте меньше 0 или больше 4, вы получите # ЧИСЛО! ошибка
• Если кварт не является целым числом, КВАРТИЛЬ проигнорирует все, что находится после десятичной точки.
• Пустые или нечисловые ячейки в диапазоне данных игнорируются.
• Если диапазон данных пуст, вы получите # ЧИСЛО! ошибка

QUARTILE.INC

Как я упоминал ранее, Microsoft заменила квартиль двумя функциями — QUARTILE.INC и QUARTILE.EXC. QUARTILE.INC — это то же самое, что QUARTILE. Поэтому, если мы переключим эти функции в нашей сводной таблице из пяти чисел, мы получим точно такие же результаты:

1	= КВАРТИЛЬ.INC (C4: C14; E4)

Часть функции «INC» является сокращением от «inclusive». Это означает, что QUARTILE.INC включает наименьшие и наибольшие значения в диапазоне данных при вычислении квартилей.

Excel вычисляет квартили как процентили: это может дать несколько отличные результаты от способа вычисления квартилей в других аналитических пакетах (обычно путем разделения данных на две половины и вычисления медианы каждой половины).

Вот как Excel рассчитывает инклюзивные квартили:

1	Расположение квартиля = (n — 1) * (целевой квартиль / 100) + 1

Здесь n — количество точек данных в наборе. Итак, давайте заполним уравнение на примере первого квартиля:

1	Расположение Q1 = (11-1) * (25/100) + 1

1	Наш результат = 3,5

Это означает, что значение Q1 равно 3,5.th значение, другими словами, посередине между третьим и четвертым значением.

Чтобы получить фактическое значение, мы используем следующее:

1	Q1 = 3-е значение + (4-е значение — 3-е значение) * .5

Заполняем уравнение нашими значениями:

1	Q1 = 65 + (67-65) * 0,5

1	Наш результат: 66

Более подробное обсуждение того, как рассчитываются процентили в Excel, можно найти на главной странице функции Excel PERCENTILE <>.

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

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

• массив – обязательный для заполнения, принимает статический массив числовых данных или ссылку на диапазон ячеек с числами, для которых требуется вычислить значение k-го перцентиля;
• k – обязательный для заполнения, принимает числовые значения из диапазона от 0 до 1 (оба включительно), характеризующие номер перцентили для расчета (например, 0,25 – 25-я перцентиль, 0,5 – 50-я перцентиль).

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

ПЕРСЕНТИЛЬ и КВАРТИЛЬ в Excel

1. ​ выше, количество значений​​ заданному % студентов​​ опрошенных людей имеет​ 100% (все значения​ данных. В нашем​ включая эти числа.​Решайте сами, к какой​Этот пример научит вас​КВАРТИЛЬ(массив;часть)​ а затем —​ ячейку A1 нового​

​ соответствующему значению (сравните​ ​ совокупности, из которой​

​ помощью формул приведен​​ 12,50%, т.е. =12,50%/2,08%=6​​Другой причиной расхождения могут​​ WORD).​ массива (студентов), у​ придется сдавать экзамен​ рост​ массива меньше или​ случае это не​Если k не является​ функции вам прибегнуть​ использовать функции​

1. ​Аргументы функции КВАРТИЛЬ описаны​​ клавишу ВВОД. При​​ листа Excel. Чтобы​ с определением функции​ взята данная выборка.​ в файле примера.​ (значение процентиля кратно​ стать повторы. Например,​Ниже детально рассмотрим как​ которых баллы хуже,​ вне зависимости от​

​Примечание​ ​ равны ему). Повторы​

​ принципиально, т.к. у​​ числом, функция ПЕРСЕНТИЛЬ​​ для вычисления нужного​PERCENTILE​ ниже.​ необходимости измените ширину​ отобразить результаты формул,​ распределения). Это, в​ Для этой цели​

​Примечание​ ​ ширине интервала, т.е.​

​ заменив, первые 4​​ работает функция ПРОЦЕНТИЛЬ.ВКЛ()​​ действительно равно 108​ набранных баллов (т.е.​: Найденное значение (190см)​

​ в массиве имеют​​ нас только 1​​ возвращает значение ошибки​ квартиля. Ниже представлена​(ПЕРСЕНТИЛЬ) и​​Массив​​ столбцов, чтобы видеть​

​ выделите их и​ частности следует из​ построим Кривую процентилей​: Некоторые значения процентилей​ делится нацело);​ значения в массиве​ и создадим ее​

​ (90% от 120).​	​ 90% студентов в​	​ является оценкой 95%-й​	​ один и тот​
​ массив. Можно указать​	​ #ЗНАЧ!.​	​ небольшая таблица, где​
​QUARTILE​	​ Обязательный. Массив или​	​ все данные.​	​ нажмите клавишу F2,​
​ расчета процентиль-ранга по​	​ (percentile curve или​	​ имеют специальные названия:​
​6-й интервал располагается между​	​ (т.е. 1; 2;​	​ аналог с помощью​	​ Следовательно, как и​

​ любом случае будут​ процентили всей генеральной​​ же ранг и​​ ссылку на диапазон​

​Если k 1, функция ПЕРСЕНТИЛЬ​

Расчет децилей для интервального ряда

1. Определяем номер дециля по формуле:

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

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

где

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

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

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

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

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

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

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

Возрастные группы	Число студентовf	Накопленная частота S
До 20 лет
20 — 25
25 — 30
30 — 35
35 — 40
40 — 45
45 лет и более
Итого

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

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

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

4.

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

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

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

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

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

• массив – обязательный для заполнения, принимает статический массив числовых данных или ссылку на диапазон ячеек с числами, для которых требуется вычислить значение k-го перцентиля;
• k – обязательный для заполнения, принимает числовые значения из диапазона от 0 до 1 (оба включительно), характеризующие номер перцентили для расчета (например, 0,25 – 25-я перцентиль, 0,5 – 50-я перцентиль).

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

Что такое квартиль?

Квартили делят диапазон данных на четыре приблизительно равные группы по размеру. Excel рассчитывает квартили как процентили:

• Первый квартиль также известен как 25th процентиль — поскольку 25% данных ниже этого значения.
• Второй квартиль — это медиана набора данных, также известная как 50th процентиль, поскольку 50% данных ниже этого значения.
• Третий квартиль также называют 75th процентиль, поскольку 75% данных ниже этого значения.

Квартили вместе с наименьшими и наибольшими числами в наборе создают «пятизначную сводку». Это может помочь вам сразу увидеть, где находится середина данных и насколько они распределены.

Квартили и интерквартильный интервал (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. В первом случае функция вернет минимальное значение , во втором – максимальное .

Примеры синтаксиса функций PERCENTILE

Ниже приведены несколько примеров использования функции ПРОЦЕНТИЛЬ в различных ситуациях.

= ПРОЦЕНТИЛЬ (диапазон1; 0,4) — 40-й процентиль

Здесь функция использует именованный диапазон в качестве входных данных для массива, а выбранный k равен 0,4. В результате отобразится 40-й процентиль названного диапазона range1.

= ПРОЦЕНТИЛЬ (диапазон1; D1)

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

= ПРОЦЕНТИЛЬ (диапазон1,80%)

5 функция использует именованный диапазон в качестве входных данных для массива, и выбранный k равен 80%. В результате перцентиль именованного диапазона range1 будет отображать 80-й процентиль.

= ПРОЦЕНТИЛЬ (C5: C14.; E5)

В этом последнем примере синтаксиса ссылочный диапазон C5: C14 служит массивом, а k находится в ссылке на ячейку E5. Процентиль ссылочного диапазона C5: C14 будет отображаться в зависимости от k, которое отображается в ячейке E5.

Квартили в MS EXCEL

Чтобы вычислить в MS EXCEL квартили заданного распределения необходимо использовать соответствующую обратную функцию распределения .

При вычислении квартилей в MS EXCEL используются обратные функции распределения : НОРМ.СТ.ОБР() , ЛОГНОРМ.ОБР() , ХИ2.ОБР() , ГАММА.ОБР() и т.д. Подробнее о распределениях, представленных в MS EXCEL, можно прочитать в статье Распределения случайной величины в MS EXCEL .

Например, в MS EXCEL 1-й квартиль для логнормального распределения LnN(1;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,25;1;1) , а 3-й квартиль для стандартного нормального распределения по формуле =НОРМ.СТ.ОБР(0,75) .

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

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

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

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

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

Linear Interpolation

Now, whenever the k value you specify is not a multiple of 1/(n-1), PERCENTILE.INC will apply a process called linear interpolation to calculate the result. This sounds complicated, but it basically means Excel will settle on a value between the two.

So why did we get 183.4 when we asked for the 80th percentile earlier?

The calculation works as follows:

• The 80th percentile falls between Chandler and Ross, Chandler at the 77.777th percentile, and Ross at the 88.888th.
• The 80th percentile is 2.222% ahead Chandler’s height
• We know there’s an 11% distance between Chandler and Ross’ heights
• 222% / 11.111% = 20% after rounding. Now we know that the 80th percentile is 20% of the way between Ross and Chandler’s heights.
• The difference between Ross’ height of 185cm, and Chandler’s height of 183cm, is 2cm
• 20% of 2cm is 0.4cm
• Add that on to Chandler’s height, and we get 183.4cm

Вариации на тему

Короткий словарик похожих терминов, как и обещано:

• Перцентиль мы уже знаем. Это число N от 0 до 100, такое, что N% элементов массива меньше него.

• Процентиль это другой способ перевести percentile на русский язык. То есть синоним слова перцентиль.

• Квартиль это четверти: 25%, 50%, 75%, 100%. То есть бывает первый, второй третий и четвертый квартиль. И еще иногда используют нулевой.

• Квантиль — это, условно, перцентиль без процентов. Используется в статистике, где бывает удобно указывать абсолютную вероятность, а не в процентах

• Еще можно встретить дециль — это 10%, 20% и т.д.

Есть еще разночтения самого определения, которое, по сути, гнется и шатается под вашу задачу, например:

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

• Можно в определении использовать вместо и это все можно тоже называть перцентилями, например «95% элементов больше X».

• Можно считать перцентиль так, чтобы он всегда был элементом массива, это нужно в некоторых задачах (как и медиану не считать средним арифметическим, а брать ближайший больший/меньший элемент).

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

Среднее арифметическое

Вероятно, большинство из вас использовало такую важную описательную статистику, как среднее.

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

Доверительный интервал для среднего представляет интервал значений вокруг оценки, где с данным уровнем доверия, находится «истинное» (неизвестное) среднее популяции.

Например, если среднее выборки равно 23, а нижняя и верхняя границы доверительного интервала с уровнем p=.95 равны 19 и 27 соответственно, то можно заключить, что с вероятностью 95% интервал с границами 19 и 27 накрывает среднее популяции.

Если вы установите больший уровень доверия, то интервал станет шире, поэтому возрастает вероятность, с которой он «накрывает» неизвестное среднее популяции, и наоборот.

Хорошо известно, например, что чем «неопределенней» прогноз погоды (т.е. шире доверительный интервал), тем вероятнее он будет верным. Заметим, что ширина доверительного интервала зависит от объема или размера выборки, а также от разброса (изменчивости) данных. Увеличение размера выборки делает оценку среднего более надежной. Увеличение разброса наблюдаемых значений уменьшает надежность оценки.

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

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

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

Среднее арифметическое, которое очень часто называют просто «среднее», получают путем сложения всех значений и деления этой суммы на число значений в наборе.

Это можно показать с помощью алгебраической формулы. Набор n наблюдений переменной X можно изобразить как X₁, X₂, X₃, …, X_n. Например, за X можно обозначить рост индивидуума (см), X₁ обозначит рост 1-го индивидуума, а X_i — рост i-го индивидуума. Формула для определения среднего арифметического наблюдений (произносится «икс с чертой»):

= (Х₁ + Х₂ + … + X_n) / n

Можно сократить это выражение:

где (греческая буква «сигма») означает «суммирование», а индексы внизу и вверху этой буквы означают, что суммирование производится от i = 1 до i = n. Это выражение часто сокращают еще больше:

или

Квартили непрерывного распределения

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

Вычисление дисперсии

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

Способ 1: расчет по генеральной совокупности

Для расчета данного показателя в Excel по генеральной совокупности применяется функция ДИСП.Г. Синтаксис этого выражения имеет следующий вид:

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

Посмотрим, как вычислить это значение для диапазона с числовыми данными.

1. Производим выделение ячейки на листе, в которую будут выводиться итоги вычисления дисперсии. Щелкаем по кнопке «Вставить функцию», размещенную слева от строки формул.
2. Запускается Мастер функций. В категории «Статистические» или «Полный алфавитный перечень» выполняем поиск аргумента с наименованием «ДИСП.Г». После того, как нашли, выделяем его и щелкаем по кнопке «OK».
3. Выполняется запуск окна аргументов функции ДИСП.Г. Устанавливаем курсор в поле «Число1». Выделяем на листе диапазон ячеек, в котором содержится числовой ряд. Если таких диапазонов несколько, то можно также использовать для занесения их координат в окно аргументов поля «Число2», «Число3» и т.д. После того, как все данные внесены, жмем на кнопку «OK».
4. Как видим, после этих действий производится расчет. Итог вычисления величины дисперсии по генеральной совокупности выводится в предварительно указанную ячейку. Это именно та ячейка, в которой непосредственно находится формула ДИСП.Г.

Урок: Мастер функций в Эксель

Способ 2: расчет по выборке

В отличие от вычисления значения по генеральной совокупности, в расчете по выборке в знаменателе указывается не общее количество чисел, а на одно меньше. Это делается в целях коррекции погрешности. Эксель учитывает данный нюанс в специальной функции, которая предназначена для данного вида вычисления – ДИСП.В. Её синтаксис представлен следующей формулой:

Количество аргументов, как и в предыдущей функции, тоже может колебаться от 1 до 255.

1. Выделяем ячейку и таким же способом, как и в предыдущий раз, запускаем Мастер функций.
2. В категории «Полный алфавитный перечень» или «Статистические» ищем наименование «ДИСП.В». После того, как формула найдена, выделяем её и делаем клик по кнопке «OK».
3. Производится запуск окна аргументов функции. Далее поступаем полностью аналогичным образом, как и при использовании предыдущего оператора: устанавливаем курсор в поле аргумента «Число1» и выделяем область, содержащую числовой ряд, на листе. Затем щелкаем по кнопке «OK».
4. Результат вычисления будет выведен в отдельную ячейку.

Урок: Другие статистические функции в Эксель

Как видим, программа Эксель способна в значительной мере облегчить расчет дисперсии. Эта статистическая величина может быть рассчитана приложением, как по генеральной совокупности, так и по выборке. При этом все действия пользователя фактически сводятся только к указанию диапазона обрабатываемых чисел, а основную работу Excel делает сам. Безусловно, это сэкономит значительное количество времени пользователей.