Виброс — це значення, яке значно вище або нижче за більшість значень у ваших даних. При використанні Excel для аналізу даних, викиди можуть спотворити результати. Наприклад, середнє середнє значення набору даних може справді відображати ваші значення. Excel надає кілька корисних функцій, які допомагають керувати своїми викидами, тож давайте подивимося.
Швидкий приклад
На зображенні нижче досить легко помітити відхилення — значення два, призначене Еріку, і значення 173, призначене Райану. У такому наборі даних досить легко помітити і впоратися з цими викидами вручну.
У більшому наборі даних цього не буде. Важливо вміти ідентифікувати викиди та вилучати їх зі статистичних розрахунків — і саме це ми розглянемо в цій статті.
Як знайти відхилення у ваших даних
Щоб знайти викиди в наборі даних, ми використовуємо такі кроки:
Обчисліть 1-й і 3-й квартилі (ми поговоримо про те, що це таке.
Оцініть інтерквартильний діапазон (ми також пояснимо це трохи нижче).
Поверніть верхню та нижню межі нашого діапазону даних.
Використовуйте ці межі, щоб визначити віддалені точки даних.
Діапазон комірок праворуч від набору даних, показаного на зображенні нижче, буде використовуватися для зберігання цих значень.
Давайте розпочнемо.
Крок перший: обчисліть квартилі
Якщо ви розділите свої дані на квартали, кожен з цих наборів називається квартилем. Найнижчі 25% чисел у діапазоні становлять 1-й квартиль, наступні 25% — 2-й квартиль тощо. Спершу ми робимо цей крок, тому що найбільш поширене визначення викиду — це точка даних, яка знаходиться більш ніж на 1,5 міжквартильних діапазонів (IQR) нижче 1-го квартиля і на 1,5 міжквартильних діапазонів вище 3-го квартиля. Щоб визначити ці значення, ми спочатку повинні з’ясувати, що таке квартилі.
Excel надає функцію КВАРТИЛЬ для обчислення квартилів. Для цього потрібні дві частини інформації: масив і кварта.
=QUARTILE(array, quart)
Масив — це діапазон значень, які ви оцінюєте. А кварти — це число, яке представляє квартиль, який ви хочете повернути (наприклад, 1 для 1-го квартиля, 2 для 2-го квартиля тощо).
Примітка. У Excel 2010 Microsoft випустила функції QUARTILE.INC і QUARTILE.EXC як покращення функції QUARTILE. QUARTILE має більшу зворотну сумісність під час роботи з кількома версіями Excel.
Повернемося до нашого прикладу таблиці.
Для обчислення 1-го квартиля ми можемо використати наступну формулу в клітинці F2.
=QUARTILE(B2:B14,1)
Коли ви вводите формулу, Excel надає список параметрів аргументу quart.
Щоб обчислити 3-й квартиль, ми можемо ввести формулу, подібну до попередньої, в клітинку F3, але використовуючи трійку замість одиниці.
=QUARTILE(B2:B14,3)
Тепер ми маємо квартильні точки даних, відображені в клітинках.
Крок другий: Оцініть міжквартильний діапазон
Міжквартильний діапазон (або IQR) – це середні 50% значень у ваших даних. Він розраховується як різниця між значенням 1-го квартилю та значенням 3-го квартилю.
Ми збираємося використовувати просту формулу в клітинці F4, яка віднімає 1-й квартиль від 3-го квартилю:
=F3-F2
Тепер ми можемо побачити наш міжквартильний діапазон.
Крок третій: поверніть нижню та верхню межі
Нижня та верхня межі — це найменші та найбільші значення діапазону даних, які ми хочемо використовувати. Будь-які значення, менші або більші за ці обмежені значення, є викидами.
Ми обчислимо нижню межу в клітинці F5, помноживши значення IQR на 1,5, а потім віднімаючи його з точки даних Q1:
=F2-(1.5*F4)
Примітка: дужки в цій формулі не потрібні, оскільки частина множення обчислюватиметься перед частиною віднімання, але вони полегшують читання формули.
Щоб обчислити верхню межу в клітинці F6, ми знову помножимо IQR на 1,5, але цього разу додамо його до точки даних Q3:
=F3+(1.5*F4)
Крок четвертий: Визначте винятки
Тепер, коли ми налаштували всі основні дані, настав час визначити наші віддалені точки даних — ті, які нижчі за нижнє меже значення або вищі за верхнє.
Ми будемо використовувати функція АБО щоб виконати цей логічний тест і показати значення, які відповідають цим критеріям, ввівши таку формулу в клітинку C2:
=OR(B2$F$6)
Потім ми скопіюємо це значення в наші клітинки C3-C14. Значення TRUE вказує на випадок, і, як ви бачите, у наших даних є два.
Ігнорування викидів під час обчислення середнього середнього
Використовуючи функцію QUARTILE, давайте обчислимо IQR і попрацюємо з найбільш поширеним визначенням викиду. Однак під час обчислення середнього середнього для діапазону значень та ігнорування викидів існує швидша та простіша функція. Ця техніка не дозволить ідентифікувати викиди, як раніше, але це дозволить нам бути гнучкими щодо того, що ми можемо вважати своєю частиною, що випадає.
Потрібна нам функція називається TRIMMEAN, і ви можете побачити її синтаксис нижче:
=TRIMMEAN(array, percent)
Масив — це діапазон значень, які потрібно усереднювати. Відсоток — це відсоток точок даних, які потрібно виключити з верхньої та нижньої частини набору даних (ви можете ввести його у відсотках або десятковому значенні).
Ми ввели формулу нижче в клітинку D3 в нашому прикладі, щоб обчислити середнє значення та виключити 20% викидів.
=TRIMMEAN(B2:B14, 20%)
Там у вас є дві різні функції для обробки викидів. Якщо ви хочете визначити їх для певних потреб у звітності, чи виключити їх із обчислень, наприклад середніх, Excel має функцію, яка відповідає вашим потребам.