Как посчитать квинтили в эксель

Обновлено: 07.07.2024

Функция СКОС в Excel предназначена для определения коэффициента асимметрии для последовательности числовых данных и возвращает соответствующее числовое значение.

Расчет коэффициента асимметрии распределения чисел в Excel

Коэффициент асимметрии показывает степень несимметричности распределения числовых данных относительно среднего значения. Может принимать следующие значения:

Из диапазона отрицательных чисел – отклонение в сторону отрицательных значений (отрицательные величины преобладают).
Из диапазона положительных чисел – отклонение в сторону положительных значений (преобладание положительных величин).
0 – асимметрия отсутствует (например, для последовательности 1, 2, 3, -1, -2, -3 асимметрический коэффициент равен нулю – 0).

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

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

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

Для решения используем следующую формулу:

С помощью функции ЕСЛИ выполняем проверку коэффициента симметрии («имеет ли второй ряд большее значение скоса?») и возвращаем соответствующее значение с пояснением.

Проверим значения для каждого ряда по отдельности с помощью функций:

Обе последовательности имеют отклонения в отрицательную сторону, но у ряда 1 это выражено в большей степени.

Коэффициент асимметрии и аппроксимация нормальным распределением в Excel

Пример 2. Имеем последовательность чисел. Необходимо проанализировать данную последовательность и сделать вывод о возможности аппроксимации нормальным распределением.

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

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

Определим значения асимметрии и эксцесса следующими функциями:

Отклонения от 0 значительны, поэтому аппроксимация невозможна. Чтобы автоматизировать подобные расчеты введем некоторые условия:

В данном случае принято допущение о том, что максимальное допустимое отклонение модулей асимметрии и эксцесса составляет 0,1

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

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

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

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

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

Функция КВАРТИЛЬ в Excel используется для расчета квартиля диапазона числовых данных и возвращает соответствующее числовое значение.

Функция КВАРТИЛЬ.ВКЛ вычисляет на основе указанной процентили в качестве второго аргумента функции. Полностью соответствует первой функции. Последняя используется в Excel 2007 и более ранних версиях и оставлена для совместимости.

Функция КВАРТИЛЬ.ИСКЛ используется для расчета квартили диапазона числовых значений на основе известной процентили, за исключением граничных значений (минимального и максимального значения в диапазоне).

Квартили используются для распределения диапазона чисел на четыре равные части:

Первый квартиль является числом из диапазона исследуемых значений, которое делит данный диапазон на две части так, что около 25% данного диапазона являются числами, которые меньше первого квартиля, а остальные (75%) – больше. Рассматриваемые функции могут возвращать результат интерполяции двух соседних значений из диапазона.
Второй квартиль эквивалентен медиане выборки (исследуемого числового диапазона), то есть числовому значению, которое делит диапазон на две части: 50% чисел меньше медианы, остальные 50% чисел больше медианы. Так, запись =КВАРТИЛЬ.ВКЛ(A1:A10;2) возвращает значение, эквивалентное результату вычисления функции =МЕДИАНА(A1:A10), при условии, что ячейки из диапазона A1:A10 содержат числовые значения.
Третий квартиль – числовое значение, делящее диапазон на две части, в первой из которой содержатся 75% чисел диапазона, которые меньше полученного значения, а во второй (25%) – больше.

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

Существует несколько алгоритмов расчета квартилей. Все рассмотренные функции используют следующую формулу:

Q_p – p-й квантиль (является частным случаем квантиля);
x – индекс квантиля;
i – индекс элемента из выборки;
A₁,A₂…A_i – элементы выборки, отсортированной по возрастанию значений.

Для расчета индекса квантиля (x) функция КВАРТИЛЬ.ВКЛ используют формулу:

x=(n-1)p, где n – количество элементов в диапазоне.

Функция КВАРТИЛЬ.ИСКЛ использует формулу x=(n+1)p.

В Excel принято так, что первые выше указанные 2 функции используют метод N-1-интерполяцию, а третья функция – N+1-интерполяцию.

Примеры использования функций КВАРТИЛЬ в Excel

Пример 1. В столбце таблицы содержится числовая последовательность. Определить число, которое делит последовательность на 2 части, 25% первой – числа меньше полученного значения, а 75% - больше. Использовать N+1-интерполяцию.

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

Для определения 1-го квартиля используем функцию:

A2:A15 – диапазон ячеек с исследуемыми числами;
1 – номер вычисляемого квартиля.

Проверим утверждение о том, что второй квартиль соответствует медиане выборке. Определим 2-й по формуле:

Полученные значения совпадают:

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

Статистический анализ роста доли дохода в Excel за период

Пример 2. В таблице приведены данные о доходах предпринимателя за год. Доказать, что примерно 75% значений меньше, чем третий квартиль доходов.

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

Определим 3-й по формуле:

Определим соотношение чисел, меньше полученного числа, к общему количеству значений по формуле:

Анализ статистики случайно сгенерированных чисел в Excel

Пример 3. Имеется диапазон случайных чисел, отсортированный в порядке возрастания. Определить соотношение суммы чисел, которые меньше 1-го квартиля, к сумме чисел, которые превышают значение 1-го квартиля.

Чтобы сгенерировать случайное число в Excel воспользуемся функцией:

После генерации отсортируем случайно сгенерированные числа по возрастанию. Вид исходной таблицы данных со случайными числами:

Формула для расчета имеет следующий вид (формула массива CTRL+SHIFT+ENTER):

Функции СУММ с вложенными функциями ЕСЛИ выполняют расчет суммы только тех чисел, которые меньше и больше соответственно значения, возвращаемого функцией для исследуемого диапазона. Из полученных значений вычисляется частное. Результат расчетов:

Общая сумма чисел исследуемого диапазона, которые меньше 1-го квартиля, составляет всего 8,57% от общей суммы чисел, которые больше 1-го квартиля.

Функция СЧЁТЕСЛИМН применяет критерии к ячейкам в нескольких диапазонах и вычисляет количество соответствий всем критериям.

Это видео — часть учебного курса Усложненные функции ЕСЛИ.

Синтаксис

Аргументы функции СЧЁТЕСЛИМН описаны ниже.

Диапазон_условия1. Обязательный аргумент. Первый диапазон, в котором необходимо проверить соответствие заданному условию.

Условие1. Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку или текста, которые определяют, какие ячейки требуется учитывать. Например, условие может быть выражено следующим образом: 32, ">32", B4, "яблоки" или "32".

Диапазон_условия2, условие2. Необязательный аргумент. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.

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

Замечания

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

Если аргумент условия является ссылкой на пустую ячейку, то он интерпретируется функцией СЧЁТЕСЛИМН как значение 0.

В условии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (

Пример 1

Скопируйте образец данных из следующих таблиц и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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