Як (і чому) використовувати функцію викидів у Excel

Відхилення - це значення, яке значно вище або нижче, ніж більшість значень даних. При використанні Excel для аналізу даних викиди можуть перекосувати результати. Наприклад, середня величина набору даних може відображати ваші значення. Excel надає кілька корисних функцій, які допомагають керувати викидами, так що давайте подивимося.

Швидкий приклад

На наведеному нижче знімку випромінювачі досить легко помітити - значення двох, присвоєні Еріку, і значення 173, призначене Райану. У наборі даних, подібному до цього, його досить легко виявити і впоратися з цими викидами вручну.

У більшому наборі даних це не так. Можливість ідентифікувати викиди та вилучити їх зі статистичних розрахунків є важливим - і це те, що ми будемо розглядати, як це зробити в цій статті.

Як знайти відхилення у даних

Щоб знайти відхилення у наборі даних, виконайте такі дії:

Розрахуйте 1-й і 3-й квартилі (ми говоримо про те, що вони знаходяться лише трохи).
Оцініть інтерквартильний діапазон (ми також роз'яснимо це трохи нижче).
Повертає верхню і нижню межі нашого діапазону даних.
Використовуйте ці межі для ідентифікації віддалених точок даних.

Діапазон комірок праворуч від набору даних, що видно на зображенні нижче, буде використовуватися для зберігання цих значень.

Давайте розпочнемо.

Крок перший: Розрахуйте квартилі

Якщо розділити дані на чверті, кожен з цих наборів називається квартилем. Найнижчі 25% чисел у діапазоні складають 1-й квартил, наступні 25% - 2-й квартил і так далі. Ми зробимо цей крок першим, тому що найбільш широко використовуваним визначенням викиду є точка даних, яка більше 1,5 міжквартильних діапазонів (IQR) нижче 1-го квартиля, а 1,5 міжквартильних діапазонів вище 3-го квартила. Щоб визначити ці цінності, ми повинні спочатку з'ясувати, якими є квартилі.

Excel забезпечує функцію QUARTILE для обчислення квартил. Для цього потрібні дві частини інформації: масив і кварт.

= QUARTILE (масив, кварт)

The масив - це діапазон значень, які ви оцінюєте. І то кварт - число, яке представляє квартиль, який ви хочете повернути (наприклад, 1 для 1^вул квартиль, 2 для 2-го квартиля і так далі).

Примітка: У Excel 2010 Microsoft випустила функції QUARTILE.INC і QUARTILE.EXC як поліпшення функції QUARTILE. QUARTILE є більш зворотною сумісністю при роботі з кількома версіями Excel.

Повернемося до нашої прикладної таблиці.

Для обчислення 1^вул У клітині F2 можна використовувати наступну формулу.

= QUARTILE (B2: B14,1)

Коли ви вводите формулу, Excel надає список параметрів для аргументу кварта.

Для обчислення 3^ст квартилі, ми можемо ввести формулу, подібну до попередньої, у комірці F3, але використовуючи три замість однієї.

= QUARTILE (B2: B14,3)

Тепер у клітинах відображаються дані про квартилі.

Крок другий: Оцініть інтерквартильний діапазон

Міжквартильний діапазон (або IQR) - це середній 50% значень ваших даних. Вона розраховується як різниця між першим значенням квартилі та 3-го квартильного значення.

Ми будемо використовувати просту формулу в клітинку F4, яка віднімає 1^вул квартиль з 3^ст квартиль:

= F3-F2

Тепер ми можемо бачити наш інтерквартильний діапазон.

Крок третій: Поверніть нижню і верхню межі

Нижня та верхня межі - це найменші та найбільші значення діапазону даних, які ми хочемо використовувати. Будь-які значення, менші або більші, ніж ці пов'язані значення, є викидами.

Ми обчислимо нижню граничну межу в комірці F5, помноживши значення IQR на 1,5, а потім віднявши його з точки даних Q1:

= F2- (1,5 * F4)

Примітка: Дужки в цій формулі не потрібні, тому що частина множення обчислюватиметься перед частиною віднімання, але вони роблять формулу легше читати.

Щоб обчислити верхню межу в комірці F6, ми помножимо IQR на 1,5, але цього разу add до точки даних Q3:

= F3 + (1,5 * F4)

Крок четвертий: Визначте викиди

Тепер, коли у нас створено всі наші основні дані, настав час визначити наші віддалені точки даних - ті, які нижчі за значення нижньої межі або вище значення верхньої межі.

Ми будемо використовувати функцію OR для виконання цього логічного тесту і показувати значення, які відповідають цим критеріям, ввівши наступну формулу в комірку C2:

= АБО (B2 $ F $ 6)

Потім ми скопіюємо це значення в наші клітини C3-C14. Значення TRUE вказує на викид, і, як ви бачите, у наших даних є два.

Ігнорування викидів при розрахунку середнього середнього

Використовуючи функцію QUARTILE, обчислимо IQR і працюємо з найбільш широко використовуваним визначенням outlier. Однак, при розрахунку середнього середнього значення для діапазону значень і ігнорування викидних значень, існує більш швидка і легша функція. Цей прийом не буде ідентифікувати викид, як раніше, але це дозволить нам бути гнучкими з тим, що ми можемо розглянути нашими відхиленнями.

Нам потрібна функція TRIMMEAN, і нижче ви можете побачити її синтаксис:

= TRIMMEAN (масив, відсоток)

The масив - це діапазон значень, які потрібно середньому. The відсотків відсоток точок даних, які потрібно виключити з верхньої та нижньої частин набору даних (можна ввести його у відсотках або десятковому значенні).

Ми ввели формулу нижче в комірку D3 в нашому прикладі, щоб обчислити середнє і виключити 20% викидів.

= TRIMMEAN (B2: B14, 20%)

Там ви маєте дві різні функції для обробки викидів. Якщо ви хочете визначити їх для певних потреб у звітах або виключити їх з таких розрахунків, як середні показники, Excel має функцію, яка відповідає вашим потребам.