Как (и защо) да използвате функцията Outliers в Excel

Отклонението е стойност, която е значително по-висока или по-ниска от повечето стойности в данните ви. Когато използвате Excel за анализиране на данни, екстремните стойности могат да изкривят резултатите. Например средната средна стойност на набор от данни може наистина да отразява вашите стойности. Excel предоставя няколко полезни функции, които ще ви помогнат да управлявате извънредните си стойности, така че нека погледнем.

Бърз пример

В изображението по-долу, извънредните стойности са сравнително лесни за откриване - стойността на две, възложени на Ерик, и стойността на 173, възложени на Райън. В такъв набор от данни е достатъчно лесно да се забележат и да се справят с тези извънредни стойности ръчно.

В по-голям набор от данни това няма да е така. Възможността да се идентифицират извънредните стойности и да се отстранят от статистическите изчисления е важна - и това е, което ще разгледаме как да направим в тази статия.

Как да намерим извънредни данни в данните си

За да намерите отклоненията в набор от данни, използваме следните стъпки:

Изчислете първия и третия квартили (ще говорим за това, което са само за малко).
Оценете интерквартилния диапазон (ще ги обясним малко по-надолу).
Връща горната и долната граница на нашия обхват от данни.
Използвайте тези граници, за да идентифицирате точките на отдалечените данни.

Диапазонът на клетките вдясно от набора от данни, който се вижда на изображението по-долу, ще се използва за съхраняване на тези стойности.

Да започваме.

Първа стъпка: Изчислете квартилите

Ако разделите данните си на четвъртинки, всеки от тези набори се нарича квартил. Най-ниските 25% от числата в диапазона съставляват 1-ва четворка, следващите 25% - втория квартил и т.н. Ние правим първата стъпка, тъй като най-широко използваното определение за отклонение е точка от данни, която е повече от 1.5 интерквартилни диапазона (IQR) под 1-ва четвърт, а 1.5 интерквартилни диапазона над третия квартил. За да определим тези ценности, първо трябва да разберем какво са квартилите.

Excel предоставя функция QUARTILE за изчисляване на квартили. Тя изисква две части: масив и кварт.

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

Най- масив е диапазонът от стойности, които оценявате. И кварта е число, което представлява четвъртната част, която искате да върнете (напр. 1 за 1^во квартили, 2 за 2-ри квартил и т.н.).

Забележка: В Excel 2010 Microsoft пусна функциите QUARTILE.INC и QUARTILE.EXC като подобрения на функцията QUARTILE. QUARTILE е по-назад съвместим, когато работи в множество версии на Excel.

Нека се върнем към нашата примерна таблица.

За да се изчисли 1^во Четвъртината можем да използваме следната формула в клетка F2.

= QUARTILE (В2: B14,1)

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

За изчисляване на 3^тата Четвърти, можем да въведем формула като предишната в клетка F3, но използваме три вместо една.

= QUARTILE (В2: B14,3)

Сега имаме показаните в клетките четвъртични данни.

Втора стъпка: Оценете интерквартилния диапазон

Интерквартилният диапазон (или IQR) е средният 50% от стойностите във вашите данни. Тя се изчислява като разликата между стойността на първия квартил и третата квантилна стойност.

Ще използваме проста формула в клетка F4, която изважда 1^во квартил от 3^тата четвъртини за:

= F3-F2

Сега можем да видим нашия интерквартилен диапазон.

Трета стъпка: Върнете долната и горната граница

Долната и горната граница са най-малките и най-големите стойности на диапазона от данни, които искаме да използваме. Всякакви стойности, по-малки или по-големи от тези свързани стойности, са извънредните стойности.

Ще изчислим долната граница на ограничението в клетка F5, като умножим стойността на IQR с 1.5 и след това ще я извадим от точката за Q1 данни:

= F2-(1.5 * F4)

Забележка: Скобите в тази формула не са необходими, защото частта за умножение ще се изчисли преди частта за изваждане, но правят формулата по-лесна за четене.

За да изчислим горната граница в клетка F6, ще умножим IQR с 1.5 отново, но този път добави към точката с данни за Q3:

= F3 + (1.5 * F4)

Четвърта стъпка: Идентифицирайте извънредните стойности

Сега, след като разполагаме с всичките ни основни данни, е време да идентифицираме нашите точки на отдалечени данни - тези, които са по-ниски от долната граница или по-висока от горната граница..

Ще използваме функцията ИЛИ за извършване на този логически тест и ще покажем стойностите, които отговарят на тези критерии, като въведем следната формула в клетка С2:

= OR (В2 $ F $ 6)

След това ще копираме тази стойност в нашите клетки C3-C14. ИСТИНСКА стойност показва излитане и както виждате, имаме две в нашите данни.

Игнориране на извънредните стойности при изчисляване на средната средна стойност

Използвайки функцията QUARTILE, можем да изчислим IQR и да работим с най-широко използваното определение на outlier. Въпреки това, при изчисляване на средната стойност за диапазон от стойности и игнориране на извънредни стойности, има по-бърза и по-лесна функция. Тази техника няма да идентифицира отклонение, както преди, но ще ни позволи да сме гъвкави с това, което бихме могли да вземем под внимание.

Функцията, от която се нуждаем, се нарича TRIMMEAN и по-долу можете да видите синтаксиса за него:

= TRIMMEAN (масив, процент)

Най- масив е диапазонът от стойности, които искате да усредните. Най- на сто е процентът на точките за данни, които да бъдат изключени от горната и долната част на набора от данни (можете да го въведете като процент или десетична стойност).

Въведохме формулата по-долу в клетка D3 в нашия пример, за да изчислим средната стойност и изключим 20% от извънредните стойности.

= TRIMMEAN (B2: B14, 20%)

Там имате две различни функции за работа с извънредни стойности. Независимо дали искате да ги идентифицирате за някои отчетни нужди или да ги изключите от изчисления като средни стойности, Excel има функция, която да отговаря на нуждите ви.