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

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

Интерквартилният диапазон от данни е диапазонът, обхванат от "кутията" на графика с квадратчета и мустаци или повече точно резултатът от изваждане на стойността за първия квартил на данните от тази на третия квартил. Excel съдържа вградена функция за изчисляване на всеки квартил за вашите данни. Намерете резервна клетка и въведете "=QUARTILE([диапазон от данни], [квартилен номер])" и поставете диапазона от клетки за вашите данни, където пише "[диапазон от данни]" и квартила, който искате, където пише "[квартил номер]."

Например, ако имате данни в клетки от A2 до A101 и искате стойността за първия квартил, въведете "=QUARTILE(A2:A101, 1)", за да намерите стойността. За първата част на аргумента можете да маркирате съответните клетки с мишката, но след запетаята трябва да напишете номера на квартила, който искате. За третия квартил със същите данни въведете „=QUARTILE(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)“.