Существует два способа расчета среднего значения при исключении выбросов в Excel:
1. Рассчитайте среднее значение и используйте ОБРЕЗНОЕ СРЕДНЕЕ, чтобы исключить выбросы.
2. Рассчитайте среднее значение и используйте межквартильный диапазон, чтобы исключить выбросы.
Мы будем использовать следующий набор данных в Excel, чтобы проиллюстрировать, как использовать оба метода:
Метод 1: вычислить среднее значение и использовать ОБРЕЗНОЕ СРЕДНЕЕ для исключения выбросов
Функцию ТРИММЕАН в Excel можно использовать для вычисления среднего значения диапазона значений при исключении определенного процента наблюдений сверху и снизу набора данных.
Например, мы можем использовать следующую формулу для расчета среднего значения в столбце A, исключая в общей сложности 20% наблюдений (10% сверху и 10% снизу):
=TRIMMEAN( A2:A16 , 20%)
Поскольку у нас есть 15 значений в нашем наборе данных, 10% равны 1,5, что округляется до 1. Таким образом, эта формула будет вычислять среднее значение в диапазоне, исключая наименьшее значение и наибольшее значение:
Среднее значение с исключенными выбросами оказывается равным 58,30769 .
Метод 2: вычислить среднее значение и использовать межквартильный диапазон для исключения выбросов
Межквартильный размах (IQR) — это разница между 75-м процентилем (Q3) и 25-м процентилем (Q1) в наборе данных. Он измеряет разброс средних 50% значений.
Мы можем определить наблюдение как выброс, если оно в 1,5 раза превышает межквартильный размах, превышающий третий квартиль (Q3), или в 1,5 раза превышает межквартильный размах меньше, чем первый квартиль (Q1).
Мы можем использовать следующую формулу для расчета межквартильного диапазона для нашего набора данных в Excel:
=QUARTILE( A2:A16 ,3)-QUARTILE( A2:A16 ,1)
На следующем снимке экрана показано, как использовать эту формулу:
Затем мы можем использовать следующую формулу, чтобы использовать IQR для определения значений выбросов и присвоить «1» любому значению, которое является выбросом в наборе данных:
=QUARTILE( A2:A16 ,3)-QUARTILE( A2:A16 ,1)
На следующем снимке экрана показано, как использовать эту формулу:
Мы видим, что только одно значение — 164 — оказывается выбросом в этом наборе данных.
Наконец, мы можем использовать следующую формулу для вычисления среднего значения всех значений в наборе данных, которые не являются выбросами:
=AVERAGEIF( B2:B16 , 0, A2:A16 )
На следующем снимке экрана показано, как использовать эту формулу:
Среднее значение с исключенными выбросами оказывается равным 55,42857 .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как рассчитать межквартильный диапазон (IQR) в Excel
Как рассчитать среднее по группе в Excel
Как рассчитать взвешенные скользящие средние в Excel