Як використовувати функцію Outliers в Excel

Коли ви аналізуєте дані, викиди можуть спотворити результати і зробити їх менш надійними. Наприклад, якщо одне або кілька ваших значень значно вищі за більшість даних, вони збільшують середнє значення, що може не відображати дані в цілому. Таким чином, видалення цих віддалених точок даних є важливим кроком у виконанні надійних статистичних обчислень. Без спеціальної функції викидів найпростіший спосіб виконати перевірку викидів у Excel – це використовувати міжквартильний діапазон для визначення викидів, але ви також можете використовувати функцію Trimmean, щоб наблизити те саме результат.

Міжквартильний діапазон даних – це діапазон, охоплений «коробкою» на діаграмі «ящик із вусами» або більше точніше, результат віднімання значення для першого квартиля даних від третього квартиль. Excel містить вбудовану функцію для обчислення будь-якого квартиля для ваших даних. Знайдіть запасну клітинку та введіть "=QUARTILE([діапазон даних], [число квартилю])" і введіть діапазон комірок для ваших даних, де написано "[діапазон даних]", і потрібний квартиль, де написано "[квартиль" номер]."

Наприклад, якщо у вас є дані в клітинках від A2 до A101, і ви хочете отримати значення для першого квартиля, введіть "=QUARTILE(A2:A101, 1)", щоб знайти значення. У першій частині аргументу ви можете виділити відповідні клітинки за допомогою миші, але після коми вам потрібно написати номер потрібного квартилю. Для третього квартилю з тими ж даними ви вводите "=КВАРТИЛЬ(A2:A101, 3)", щоб отримати результат.

Використовуючи іншу порожню клітинку, відніміть значення першої квартильної клітинки від значення третьої квартильної клітинки. Якщо перший квартиль знаходиться в клітинці C2, а третій – в клітинці D2, введіть "=D2-C2", щоб отримати результат. Це інтерквартильний діапазон.

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

Якщо перше значення квартилю знаходиться в C2, третє квартильне значення знаходиться в клітинці D2, а інтерквартильний діапазон дорівнює у клітинку E2 ви повинні ввести "=C2-(1,5 * E2)", щоб знайти нижню межу, і "=D2+(1,5 * E2)", щоб знайти верхню ліміт. Загалом, ви вводите «=[перший квартиль] – (1,5 * [міжквартильний діапазон])», щоб знайти нижню межу, і «=[третій квартиль] + (1,5 * [міжквартильний діапазон])», щоб знайти верхню межу.

Все, що нижче нижньої межі або вище верхньої межі, є викидом.

Щоб завершити перевірку викидів у Excel, використовуйте логічну функцію «АБО», щоб ефективно визначити, які значення у вашому класі даних є викидами. Введіть "=АБО([комірка даних]>[верхня межа], [комірка даних]$F$2, A2

Якщо значення в A2 вище верхньої межі або нижче нижньої межі, відображається "TRUE", що вказує на те, що значення є викидом. Ви можете перетягнути цю формулу вниз, клацнувши правому нижньому куті комірки з формулою і перетягніть його вниз так, щоб він закінчився біля кінцевої комірки даних, щоб виконати однакові обчислення для кожної точка даних.

Ви також можете виділити дані та перейти до «Умовне форматування» у розділі «Стилі» вкладки «Головна», якщо ви хочете змінити форматування для вибросів. Виберіть «Нове правило» та виділіть параметр «Використовуйте формулу для визначення клітинок для форматування». Введіть ту саму формулу, що й у попередньому абзаці, а потім натисніть опцію «Формат», щоб вибрати унікальне форматування для викидів.

Функція "Trimmean" - це простіший спосіб визначення викидів. Введіть "=TRIMMEAN([діапазон даних], [пропорція до обрізання])", щоб скористатися функцією, з діапазоном комірок, що містять дані, замість "[діапазон даних]" і десяткову дробу відсоток, який потрібно обрізати там, де написано "[пропорція до обрізання]." Це видаляє екстремальні значення у верхній і нижній частині, а потім обчислює середнє на основі них залишилися. Отже, якщо ви обрізаєте 10 відсотків, то перед обчисленням середнього значення буде видалено верхні 5 відсотків і нижні 5 відсотків.

Якщо дані проходять від A2 до A101, і ви хочете обрізати крайні 5 відсотків значень, введіть "=TRIMMEAN(A2:A101, 0,05)", щоб знайти скориговане середнє. Ви можете обрізати 15 відсотків, написавши замість цього "=TRIMMEAN(A2:A101, 0,15)".