Excel: как рассчитать среднее значение, исключая выбросы

Существует два способа расчета среднего значения при исключении выбросов в 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