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

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

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

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

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

Способ 1. Используйте TRIMMEAN, чтобы исключить выбросы.

Функция TRIMMEAN вычисляет среднее значение диапазона значений, исключая при этом определенный процент наблюдений сверху и снизу набора данных.

Например, мы можем использовать следующую формулу для расчета среднего значения в столбце 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).

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

 =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 .

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

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

Как посчитать среднее значение по группе в Google Таблицах
Как посчитать средний процент в Google Таблицах
Как рассчитать средневзвешенное значение в Google Таблицах