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