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

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

Бърз пример

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

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

Как да намерим отклонения в данните си

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

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

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

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

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

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

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

= КВАРТИЛ (масив, четвърт)

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

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

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

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

= ЧЕТВЪРТЕН (B2: B14,1)

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

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

= ЧЕТВЕРТ (B2: B14,3)

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

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

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

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

= F3-F2

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

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

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

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

= F2- (1,5 * F4)

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

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

= F3 + (1,5 * F4)

Стъпка четвърта: Идентифицирайте отклоненията

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

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

= ИЛИ (B2 $ F $ 6)

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

Пренебрегване на отклоненията при изчисляване на средната средна стойност

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

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

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

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

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

= TRIMMEAN (B2: B14, 20%)

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