Примеры формул для расчета квартиля в Excel

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

Как посчитать квартиль в Excel

Ниже на рисунке представлен список работников предприятия производящих определенный продукт, а также статистические показатели количества браков на 1000 шт. готовой производимой продукции выкрытые отделом контроля качества. Цель в данном примере найти наилучших работников и тех, которым требуется повышение квалификации. Для этого разделим данные на 4 равные доли. Для определения границ значений каждой 4-й доли Excel предлагает функцию КВАРТИЛЬ. Термин квартиль обозначает граничный раздел значения, содержащий ¼ долю 25% данных.

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

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

Функция КВАРТИЛЬ требует указания диапазона значений в первом аргументе, а во втором аргументе целое число определяющее номер квартиля, для которого граничное значение должно быть найдено. Допустимое значение для второго аргумента – это:

• 0 – для квартиля, содержащего минимальное значение;
• 1 – для 25%;
• 2 – для 50%;
• 3 – для 75%;
• 4 – для квартиля, содержащего максимальное значение.

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

В формуле ячейки F1 второй аргумент функции КВАРТИЛЬ содержит не число, а формулу: 5-СТРОКА(A1), которая позволяет уменьшать номер квартиля на 1 во время копирования формулы в очередные нижние ячейки. Выражение в ячейке F1 возвращает значение 4, означающее номер квартиля с наибольшими значениями. Во время копирования формулы в ячейки находящихся под ячейкой F1 ссылка A1 сменяется на А2 и формула возвращает число 5-3, то есть 3 в ячейке F2, которое значит границу 75-го процентиля.

Ниже на рисунке изображено как формула разделяет отсортированные данные. Для проверки, к которому квартилю относиться данное значение, была использована функция ПОИСКПОЗ. Она использует диапазон результатов, полученных с помощью функции КВАРТИЛЬ. Так как результаты вычислений отсортированы в порядке по уменьшению, в последнем аргументе функции ПОИСКПОЗ указано отрицательное число -1, которое означает использование в функции операцию сравнения «больше чем»:

Функция ПОИСКПОЗ возвращает номер позиции, на которой в списке была найдено исходное значение. Она прекратит свое вычисление если очередное значение будет меньше от искомого. Если бы искомым значением было бы число 47, тогда ПОИСКПОЗ приняла что (44,5) является меньшим от искомого и прекратила бы свое вычисление уже на первой позиции.



Альтернативная функция КВАРТИЛЬ.ИСКЛ в Excel

Какой наилучший способ вычисления границ квартиля – это все еще актуальная тема для многих дискуссий. В старых версиях программы Excel была доступна только лишь функция КВАРТИЛЬ. Ее алгоритм вычисления основан на формуле n%*(число-1). Начиная с версии Excel 2010, можно воспользоваться более новыми функциями для решения такого рода задач, но уже другими методами вычисления. Функция КВАРТИЛЬ.ВКЛ работает аналогично, как и обычная по тому же алгоритму. Старую функцию так же оставили для поддержки старых версий файлов Excel. А функция КВАРТИЛЬ.ИСКЛ использует другую формулу алгоритма: n%*(число+1).

Ниже на рисунке отображаются данные обработанные новой функцией КВАРТИЛЬ.ИСКЛ:

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

Так как функция КВАРТИЛЬ.ИСКЛ не позволяет вычислять границы между квартилями с максимальными и минимальными значениями, была использована формула с функцией ЕСЛИ для проверки: является ли текущее значение большим от доли 75% – значение 3-го квартиля.

Как работает функция КВАРТИЛЬ в Excel

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

В одном из выше описанных примеров функция КВАРТИЛЬ выполняет математические операции 0,75*(30-1). Поэтому для числового значения 30 будет возвращен результат 21,75. Далее функция сортирует значения от наименьших до наибольших и отсчитывает 21-ну строку, начиная от наименьшего значения. Так как результат первой операции не является целым числом, функция выполняет интерполяцию для обоих найденных значений. В данном примере после отсчета 21-ой строки в направлении от меньшего к большему были найдены числовые значения 43, а следующее 45. В интерполяции было использовано дробное число 21,75 с целью нахождения значения составляющего 75 процентиля между числами 43 и 45, согласно формуле 43+((45-43)*0,75).

Подобным способом для квартиля 50, функция выполняет действие по алгоритму формулы: 0,5*(30-1), результат вычисления которой возвращает дробное число 14,5. Отсчитывая строки, начиная от наименьшего значения, граница припадает на строках Александра Блашчик / 31 и Лена Николаевна / 31. Так как оба значения равны между собой интерполяция будет простой и возвратит в результате число 31. На втором рисунке представлены те самые данные о работниках и браках, однако они для наглядности отсортированы и схематически указаны линии где проходят границы между квартилями.