Примеры функции КВАРТИЛЬ в Excel для расчета медианы квартиля
Функция КВАРТИЛЬ в Excel используется для расчета квартиля диапазона числовых данных и возвращает соответствующее числовое значение.
Функция КВАРТИЛЬ.ВКЛ вычисляет на основе указанной процентили в качестве второго аргумента функции. Полностью соответствует первой функции. Последняя используется в Excel 2007 и более ранних версиях и оставлена для совместимости.
Функция КВАРТИЛЬ.ИСКЛ используется для расчета квартили диапазона числовых значений на основе известной процентили, за исключением граничных значений (минимального и максимального значения в диапазоне).
Квартили используются для распределения диапазона чисел на четыре равные части:
- Первый квартиль является числом из диапазона исследуемых значений, которое делит данный диапазон на две части так, что около 25% данного диапазона являются числами, которые меньше первого квартиля, а остальные (75%) – больше. Рассматриваемые функции могут возвращать результат интерполяции двух соседних значений из диапазона.
- Второй квартиль эквивалентен медиане выборки (исследуемого числового диапазона), то есть числовому значению, которое делит диапазон на две части: 50% чисел меньше медианы, остальные 50% чисел больше медианы. Так, запись =КВАРТИЛЬ.ВКЛ(A1:A10;2) возвращает значение, эквивалентное результату вычисления функции =МЕДИАНА(A1:A10), при условии, что ячейки из диапазона A1:A10 содержат числовые значения.
- Третий квартиль – числовое значение, делящее диапазон на две части, в первой из которой содержатся 75% чисел диапазона, которые меньше полученного значения, а во второй (25%) – больше.
Функция КВАРТИЛЬ.ВКЛ может быть использована не только для определения медианы выборки (второго квартиля), а и нахождения минимального и максимального значений соответственно. При работе с большими диапазонами чисел для подобных расчетов рекомендуется использовать функции МИН и МАКС соответственно.
Существует несколько алгоритмов расчета квартилей. Все рассмотренные функции используют следующую формулу:
Qp=(1-(x-i)∙Ai+(x-i)∙A(i+1), где:
- Qp – p-й квантиль (является частным случаем квантиля);
- x – индекс квантиля;
- i – индекс элемента из выборки;
- A1,A2…Ai – элементы выборки, отсортированной по возрастанию значений.
Для расчета индекса квантиля (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
Все рассматриваемые функции имеют одни и те же аргументы:
=КВАРТИЛЬ(массив;часть)
Описание аргументов:
- массив – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек с числовыми значениями, для которых будет рассчитан требуемый квартиль;
- часть – обязательный аргумент, принимающий числовые значения, указывающие номер возвращаемого квартиля. В зависимости от используемой функции, может принимать числа из диапазонов:
- От 0 до 4 (КВАРТИЛЬ.ВКЛ), при этом числа 0 и 1 характеризуют минимальное и максимальное значения из исследуемого диапазона соответственно. Число 1 соответствует 1-й квартили, 2 – медиане, 3 – 3-й квартили.
- От 1 до 3 (функция КВАРТИЛЬ.ИСКЛ), соответствующие 1-й, 2-й и 3-й квартилям.
Примечания:
- Все рассматриваемые функции не учитывают имена и текстовые строки, которые не могут быть преобразованы к числам, логические значения и пустые ячейки. Ячейки, содержащие значение 0 (нуль), в расчет включаются.
- Если в качестве первого аргумента функций передан пустой массив или ссылка на диапазон пустых значений, все функции вернут код ошибки #ЧИСЛО!.
- Если в качестве второго аргумента функций было передано нецелое число из диапазона допустимых значений, дробная часть будет усечена.
- Если второй аргумент задан числом, взятым из вне диапазона допустимых значений, в результате вычислений будет возвращен код ошибки #ЧИСЛО!.