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