Під час роботи з даними в Excel у вас часто виникають проблеми з обробкою викидів у наборі даних.
Наявність викидів є досить поширеним явищем у всіх видах даних, і важливо виявити та вилікувати ці викиди, щоб переконатися, що ваш аналіз правильний та більш змістовний.
У цьому уроці я покажу вам як знайти викиди в Excel, та деякі прийоми, які я використовував у своїй роботі для вирішення цих викидів.
Що таке викиди і чому їх важливо знайти?
Викид - це точка даних, яка виходить далеко за межі інших точок даних у наборі даних. Якщо у даних є викиди, це може спотворити ваші дані, що може призвести до неправильних висновків.
Наведу вам простий приклад.
Скажімо, 30 людей їдуть автобусом від пункту А до пункту B. Усі люди перебувають у подібній ваговій групі та групі доходів. Для цілей цього підручника розглянемо середню вагу 220 фунтів, а середній річний дохід - 70 000 доларів.
Зараз десь посередині нашого маршруту автобус зупиняється, і Білл Гейтс сідає.
Тепер, як ви думаєте, що це зробить із середньою вагою та середнім доходом людей у автобусі.
Хоча середня вага, ймовірно, не сильно зміниться, середній дохід людей в автобусі значно зросте.
Це тому, що дохід Білла Гейтса є винятковим у нашій групі, і це дає нам неправильну інтерпретацію даних. Середній дохід кожної людини в автобусі складе кілька мільярдів доларів, що значно перевищує фактичну вартість.
Під час роботи з фактичними наборами даних у Excel ви можете мати викиди в будь -якому напрямку (наприклад, позитивне або негативне викидання).
І щоб переконатися, що ваш аналіз правильний, вам потрібно якось визначити ці викиди, а потім вирішити, як найкраще їх лікувати.
Тепер давайте розглянемо кілька способів знайти викиди в Excel.
Знайдіть викиди, відсортувавши дані
За допомогою невеликих наборів даних швидкий спосіб визначити викиди - це просто відсортувати дані та вручну переглянути деякі значення у верхній частині цих відсортованих даних.
А оскільки в обох напрямках можуть бути викиди, переконайтеся, що ви спочатку відсортуєте дані за зростанням, а потім за спаданням, а потім перейдете до найвищих значень.
Дозвольте мені показати вам приклад.
Нижче у мене є набір даних, де я маю тривалість дзвінків (у секундах) для 15 викликів служби підтримки клієнтів.
Нижче наведено кроки для сортування цих даних, щоб ми могли визначити викиди у наборі даних:
- Виберіть заголовок стовпця стовпця, який потрібно відсортувати (клітинка В1 у цьому прикладі)
- Натисніть вкладку Домашня сторінка
- У групі Редагування натисніть на значок Сортування та фільтр.
- Натисніть на Спеціальне сортування
- У діалоговому вікні "Сортування" виберіть "Тривалість" у спадному меню "Сортувати за" і "Від найбільшого до найменшого" у спадному меню "Порядок"
- Натисніть ОК
Наведені вище кроки відсортують стовпець тривалості виклику з найвищими значеннями вгорі. Тепер ви можете вручну сканувати дані та перевірити, чи є якісь викиди.
У нашому прикладі я бачу, що перші два значення набагато вище, ніж решта значень (а два нижніх набагато нижче).
Примітка: Цей метод працює з невеликими наборами даних, де можна сканувати дані вручну. Це не науковий метод, але він працює добре
Знаходження викидів за допомогою квартильних функцій
Тепер поговоримо про більш наукове рішення, яке може допомогти вам визначити, чи є якісь викиди чи ні.
У статистиці квартиль становить одну чверть набору даних. Наприклад, якщо у вас 12 точок даних, то перший квартиль буде трьома нижніми точками даних, другий квартиль буде наступними трьома точками даних тощо.
Нижче наведено набір даних, де я хочу знайти викиди. Для цього мені доведеться обчислити 1 -й і 3 -й квартилі, а потім за його допомогою обчислити верхню та нижню межу.
Нижче наведено формулу для обчислення першого квартилю в клітині E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
і ось той, який обчислює третій квартиль у клітинці E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Тепер я можу скористатися наведеними вище двома розрахунками, щоб отримати міжквартильний діапазон (що становить 50% наших даних у першому та третьому квартилях)
= F3-F2
Тепер ми будемо використовувати міжквартильний діапазон, щоб знайти нижню та верхню межу, яка міститиме більшість наших даних.
Тоді все, що виходить за межі нижньої та верхньої меж, вважатиметься викидами.
Нижче наведена формула для розрахунку нижньої межі:
= Квартиль1 - 1,5*(Міжквартильний діапазон)
що в нашому прикладі виглядає так:
= F2-1,5*F4
І формула для розрахунку верхньої межі така:
= Квартиль3 + 1,5*(Міжквартильний діапазон)
що в нашому прикладі виглядає так:
= F3+1,5*F4
Тепер, коли у наборі даних є верхня та нижня межа, ми можемо повернутися до вихідних даних і швидко визначити ті значення, які не лежать у цьому діапазоні.
Швидкий спосіб зробити це - перевірити кожне значення та повернути TRUE або FALSE у новому стовпці.
Я використав формулу АБО нижче, щоб отримати ІСТИНУ для тих значень, які є викидами.
= АБО (B2 $ F $ 6)
Тепер ви можете відфільтрувати стовпець Outlier і показувати лише записи, де значення TRUE.
Крім того, ви також можете використовувати умовне форматування, щоб виділити всі клітинки, де значення TRUE
Примітка: Хоча це більш прийнятний метод пошуку викидів у статистиці. Я вважаю цей метод дещо непридатним у реальних сценаріях. У наведеному вище прикладі нижня межа, обчислена за формулою, становить -103, тоді як набір даних, який ми маємо, може бути тільки позитивним. Отже, цей метод може допомогти нам знайти викиди в одному напрямку (високі значення), він марний у виявленні викидів в іншому напрямку.Пошук викидів за допомогою функцій LARGE/SMALL
Якщо ви працюєте з великою кількістю даних (значення у кількох стовпцях), ви можете витягти найбільші та найменші 5 або 7 значень і подивитися, чи є в цьому якісь викиди.
Якщо є якісь викиди, ви зможете їх ідентифікувати, не перебираючи всіх даних в обох напрямках.
Припустимо, у нас є набір даних нижче, і ми хочемо знати, чи є якісь викиди.
Нижче наведена формула, яка дасть вам найбільше значення в наборі даних:
= ВЕЛИКИЙ ($ B $ 2: $ B $ 16,1)
Аналогічно, друге за величиною значення буде надано
= ВЕЛИКИЙ ($ B $ 2: $ B $ 16,1)
Якщо ви не використовуєте Microsoft 365, який має динамічні масиви, ви можете скористатися формулою нижче, і вона дасть вам п’ять найбільших значень із набору даних за допомогою однієї формули:
= ВЕЛИКИЙ ($ B $ 2: $ B $ 16, РЯД ($ 1: 5))
Аналогічно, якщо вам потрібні найменші 5 значень, використовуйте формулу нижче:
= МАЛИЙ ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
або наступне, якщо у вас немає динамічних масивів:
= МАЛЕНЬКИЙ ($ B $ 2: $ B $ 16,1)
Після того як у вас є ці значення, дійсно легко виявити будь -які викиди в наборі даних.
Хоча я вирішив витягти найбільше та найменше 5 значень, ви можете отримати 7 або 10 залежно від того, наскільки великий ваш набір даних.
Я не впевнений, чи це прийнятний метод для виявлення викидів у Excel чи ні, але це той метод, який я використовував, коли кілька років тому мені доводилося працювати з великою кількістю фінансових даних у своїй роботі. У порівнянні з усіма іншими методами, описаними в цьому підручнику, я знайшов цей метод найбільш ефективним.
Як правильно поводитися з викидами
Поки що ми бачили методи, які допоможуть нам знайти викиди у наборі даних. Але що робити, коли ви знаєте, що існують викиди.
Ось кілька методів, які ви можете використовувати для обробки викидів, щоб ваш аналіз даних був правильним.
Видаліть викиди
Найпростіший спосіб видалити викиди з набору даних - просто видалити їх. Таким чином, це не перекрутить ваш аналіз.
Це більш життєздатне рішення, якщо у вас є великі набори даних і видалення кількох викидів не вплине на загальний аналіз. І, звичайно, перед видаленням даних переконайтеся, що ви створили копію та заглибились у причину цих викидів.
Нормалізувати викиди (відрегулювати значення)
Нормалізація викидів-це те, що я робив, коли працював на повну ставку. Для всіх значень викидів я б просто змінив їх на значення, яке трохи вище максимального значення в наборі даних.
Це переконалося, що я не видаляю дані, але в той же час я не дозволяю це перекручувати мої дані.
Щоб навести вам реальний приклад, якщо ви аналізуєте маржу чистого прибутку компаній, де більшість компаній лежить у межах від -10%до 30%, і є кілька значень, які перевищують 100%, я просто змінили б ці значення викидів на 30% або 35%.
Отже, це деякі з методів, які ви можете використовувати Excel, щоб знайти викиди.
Після того, як ви визначили викиди, можна заглибитися в дані та з’ясувати, що їх спричиняє, одночасно вибрати один із способів обробки цих викидів (який може бути видаленням або нормалізацією цих параметрів шляхом коригування значення)
Сподіваюся, цей підручник був вам корисним.
