Excel: как рассчитать среднее значение без учета выбросов

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

1. Рассчитайте среднее значение и используйте TRIMMEAN, чтобы исключить выбросы.

2. Рассчитайте среднее значение и используйте межквартильный размах, чтобы исключить выбросы.

Мы будем использовать следующий набор данных в Excel, чтобы проиллюстрировать, как использовать оба метода:

Метод 1. Рассчитайте среднее значение и используйте TRIMMEAN, чтобы исключить выбросы.

Функцию TRIMMEAN в 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» любому значению, которое является выбросом в наборе данных:

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

На следующем снимке экрана показано, как использовать эту формулу:

Мы видим, что только одно значение — 164 — оказывается выбросом в этом наборе данных.

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

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

На следующем снимке экрана показано, как использовать эту формулу:

Среднее значение, исключая выбросы, оказывается 55,42857 .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Как рассчитать межквартильный размах (IQR) в Excel
Как посчитать среднее значение по группе в Excel
Как рассчитать взвешенные скользящие средние в Excel