Межквартильный диапазон в Excel с примером формулы КВАРТИЛЬ
В Excel функция КВАРТИЛЬ используется для разделения данных на равные доли. Также еще часто используют эту функцию для поиска отстающих показателей, то есть существенно отличающихся от остальных значений в исходных данных.
Пример расчета межквартильного диапазона для статистического анализа в Excel
Ниже на рисунке представлен другой список работников с показателями производственных браков на 1000 шт. выпущенной продукции. Допустим нам необходимо узнать, какие работники делают большое и малое количество браков, существенно выходящее за пределы допустимой нормы (отстающие и превышающие ее - так называемые выборсы от медианы), чтобы потом проанализировать их. С целью поиска аномальных отклонений от показателей нормы в данном примере будет использован метод расширенного межквартильного диапазона. Межквартильный диапазон – это просто данные лежащие в среднем диапазоне, который охватывает 50% всего объема данных (находящийся между 75% и 25%). Определение «расширенный» значит, что средний диапазон данных может быть расширен с учетом определенного коэффициента, определяющего его границы. Все значения, лежащие вне границ, воспринимаются как показатели выборсы:
Для определения значения в среднем диапазоне между 75% и 25% следует воспользоваться функцией КВАРТИЛЬ.ИСКЛ вместе с указанными аргументами 3 и 1 – соответственно. Межквартильным диапазоном является разницей между этими значениями.
В случае нерасширенного межквартильного диапазона с целью определения его нижней границы достаточно всего лишь вычитать значение диапазона от 25%. А для верхней границы, нужно добавить его до 75%. Результатом применения данного метода могло бы получиться слишком большое число для найденных показателей выбросов. Умножая межквартильный диапазон на расширяющий коэффициент (в данном примере равен 1,5) расширяются границы. Таким образом, можно выбрать только особенно экстремальные значения.
Схема вычисления межквартильного диапазона в Excel
Ниже на рисунке представленные те же данные, что и в предыдущем примере, отсортированы по столбцу с показателями количества браков на 1000 шт. готовой продукции. Также для наглядности линиями наложены границы расширенного диапазона четверти и верхние с нижними границами остальных диапазонов четверти:
Чтобы определить верхнюю границу диапазона четверти, необходимо умножить расширяющий коэффициент на диапазон четверти и добавить его результат к 75%.
Чтобы определить нижнюю границу необходимо от 25% вычитать результат, полученный после умножения диапазона на коэффициент.
Может оказаться так, что расширяющий коэффициент равен 1,5 привел к исключению значения, которое казались отстающими или были выбраны значения, которые казались нормальными. В этом нет ничего особенного. Просто увеличьте или уменьшите расширяющий коэффициент, если его текущее значение не согласуются с Вашими исходными данными.
После определения границ используйте формулу со вложенными функциями ЕСЛИ с целью проверки: является ли данное значение большим чем верхнее или ниже от нижнего граничного значения. В случае значительных отклонений показателей (выбросов) от нормы формула со вложенными функциями ЕСЛИ возвращает слово «Выше» или «Ниже», а в случае значения лежащего внутри границ формула возвращает пустую строку ("").
