Квартиль как найти в экселе

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

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

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

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

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

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

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

Q_p=(1-(x-i)∙A_i+(x-i)∙A_(i+1), где:

• 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-й по формуле:

Вычислим медиану:

=МЕДИАНА(A2:A15)

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

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

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

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

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

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

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

=СЧЁТЕСЛИ(B2:B13;»<«&B15)/СЧЁТ(B2:B13)

Полученные результаты:

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

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

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

=СЛУЧМЕЖДУ(0;1000)

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

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

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

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

Особенности использования функций расчета квартиля в Excel

Все рассматриваемые функции имеют одни и те же аргументы:

=КВАРТИЛЬ(массив;часть)

Описание аргументов:

• массив – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек с числовыми значениями, для которых будет рассчитан требуемый квартиль;
• часть – обязательный аргумент, принимающий числовые значения, указывающие номер возвращаемого квартиля. В зависимости от используемой функции, может принимать числа из диапазонов:

1. От 0 до 4 (КВАРТИЛЬ.ВКЛ), при этом числа 0 и 1 характеризуют минимальное и максимальное значения из исследуемого диапазона соответственно. Число 1 соответствует 1-й квартили, 2 – медиане, 3 – 3-й квартили.
2. От 1 до 3 (функция КВАРТИЛЬ.ИСКЛ), соответствующие 1-й, 2-й и 3-й квартилям.

Примечания:

1. Все рассматриваемые функции не учитывают имена и текстовые строки, которые не могут быть преобразованы к числам, логические значения и пустые ячейки. Ячейки, содержащие значение 0 (нуль), в расчет включаются.
2. Если в качестве первого аргумента функций передан пустой массив или ссылка на диапазон пустых значений, все функции вернут код ошибки #ЧИСЛО!.
3. Если в качестве второго аргумента функций было передано нецелое число из диапазона допустимых значений, дробная часть будет усечена.
4. Если второй аргумент задан числом, взятым из вне диапазона допустимых значений, в результате вычислений будет возвращен код ошибки #ЧИСЛО!.

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

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

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

Квартили — это значения в Excel, которые разделяют числовые значения на четыре части. Люди находят квартили, чтобы понять процентили, например, 25% клиентов с наибольшими расходами. Четыре квартиля включают:

• Первый квартиль: Первый квартиль включает самые низкие 25% диапазона данных.

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

• Третий квартиль: Третий квартиль — это вторая по величине группа чисел, превышающая медиану.

• Четвертый квартиль: Четвертый квартиль включает самые высокие 25% чисел в диапазоне данных.

Например, если данные варьируются от одного до восьми, то каждое из них находится в следующих квартилях:

• Первый квартиль: 1 и 2

• Второй квартиль: 3 и 4

• Третий квартиль: 5 и 6

• Четвертый квартиль: 7 и 8

Как найти квартили в Excel

При нахождении квартилей в Excel можно предпринять несколько шагов:

1. Упорядочьте ваши числа

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

Для формулы квартиля необходимы две основные величины: квартили и массивы. Квартили — это значения, представленные каждым квартилем. Они варьируются от нуля до четырех:

• 0: Это минимальное значение в диапазоне чисел.

• 1: Это первый квартиль или 25-й процентиль.

• 2: Это второй, или медианный, квартиль, или 50-й процентиль.

• 3: Это представляет собой третий квартиль или 75-й процентиль.

• 4: Представляет собой максимальное значение в диапазоне.

3. Выполните функцию

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

=QUARTILE(array, quart)

Где:

• Массив — это полный диапазон значений, для которых вы хотите найти квартили

• Кварта — это то, какой квартиль вы хотите найти

Советы по использованию функции квартилей в Excel

Вот несколько советов по использованию функции квартилей в Excel:

Обзор значений

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

Рассчитать различные кварты

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

Проверьте точность вручную

Вы можете вручную рассчитать квартили с помощью нескольких формул для проверки информации:

• Нижний квартиль = (N+1) x 14

• Средний квартиль = (N+1) x 24

• Верхний квартиль = (N+1) x 34

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

Часто задаваемые вопросы

Что такое процентиль?

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

В чем разница между кварталом и квартилем?

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

Для чего используются квартили?

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

Что такое аналогичные функции в Excel?

Существует несколько аналогичных функций, которые можно рассчитать в Excel:

• MIN: Функция MIN вычисляет наименьшее потенциальное значение в наборе данных.

• MAX: Функция MAX вычисляет наименьшее возможное значение в наборе данных.

• MEDIAN: Функция MEDIAN вычисляет среднее значение в наборе данных.

• СРЕДНЕЕ: Функция MEAN вычисляет среднее значение чисел в наборе данных.

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

=MEDIAN(A1:A9)

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с Indeed.

Для вычисления квартилей в 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;»<«&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.

Примечание

: Подробнее о

Функции распределения

см. статью

Функция распределения и плотность вероятности в MS EXCEL

.

Если известна

функция плотности вероятности

p

(х)

, то 1-й

квартиль

можно найти из уравнения:

Например, решив аналитическим способом это уравнение для

Логнормального распределения

lnN(μ; σ

²

), получим, что

медиана

(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)

.