Когато работите с данни в Excel, често ще имате проблеми с обработката на извънредни стойности във вашия набор от данни.
Наличието на отклонения е доста често срещано явление във всички видове данни и е важно да се идентифицират и третират тези отклонения, за да се гарантира, че вашият анализ е правилен и по -смислен.
В този урок ще ви покажа как да откриете отклонения в Excel, и някои от техниките, които съм използвал в работата си за справяне с тези отклонения.
Какво представляват отклоненията и защо е важно да ги намерите?
Отклонението е точка от данни, която е далеч отвъд другите точки от данни в набора от данни. Когато имате отклонение в данните, това може да изкриви данните ви, което може да доведе до неправилни изводи.
Нека ви дам един прост пример.
Да предположим, че 30 души пътуват с автобус от дестинация А до дестинация Б. Всички хора са в сходна група с тегло и доход. За целите на този урок, нека разгледаме средното тегло от 220 паунда, а средногодишният доход от 70 000 долара.
Сега някъде по средата на маршрута ни автобусът спира и Бил Гейтс се качва.
Сега, какво мислите, че това би направило със средното тегло и средния доход на хората в автобуса.
Докато средното тегло едва ли ще се промени много, средният доход на хората в автобуса ще скочи значително.
Това е така, защото доходите на Бил Гейтс са извънредни стойности в нашата група и това ни дава грешна интерпретация на данните. Средният доход за всеки човек в автобуса би бил няколко милиарда долара, което далеч надхвърля действителната стойност.
Когато работите с реални набори от данни в Excel, можете да имате отклонения във всяка посока (т.е. положителен или отрицателен отклонение).
И за да сте сигурни, че вашият анализ е правилен, трябва по някакъв начин да идентифицирате тези отклонения и след това да решите как да ги третирате най -добре.
Сега нека да видим няколко начина да откриете отклонения в Excel.
Намерете отклонения, като сортирате данните
С малки набори от данни бърз начин за идентифициране на отклонения е просто да сортирате данните и ръчно да преминете през някои от стойностите в горната част на тези сортирани данни.
И тъй като може да има отклонения в двете посоки, не забравяйте първо да сортирате данните във възходящ ред, а след това в низходящ ред и след това да преминете през горните стойности.
Нека ви покажа пример.
По -долу имам набор от данни, където имам продължителност на обажданията (в секунди) за 15 обаждания към обслужване на клиенти.
По -долу са стъпките за сортиране на тези данни, така че да можем да идентифицираме отклоненията в набора от данни:
- Изберете Заглавката на колоната на колоната, която искате да сортирате (клетка В1 в този пример)
- Щракнете върху раздела Начало
- В групата Редактиране щракнете върху иконата Сортиране и филтриране.
- Кликнете върху Персонализирано сортиране
- В диалоговия прозорец Сортиране изберете „Продължителност“ в падащото меню Сортиране по и „Най-голямо до най-малко“ в падащото меню Поръчка
- Щракнете върху OK
Горните стъпки биха сортирали колоната с продължителност на разговора с най -високите стойности в горната част. Сега можете ръчно да сканирате данните и да видите дали има отклонения.
В нашия пример мога да видя, че първите две стойности са много по -високи от останалите (а долните две са много по -ниски).
Забележка: Този метод работи с малки набори от данни, където можете да сканирате данните ръчно. Това не е научен метод, но работи добре
Намиране на отклонения с помощта на функциите на квартила
Сега нека поговорим за по -научно решение, което може да ви помогне да определите дали има отклонения или не.
В статистиката квартилът е една четвърт от набора от данни. Например, ако имате 12 точки от данни, тогава първият квартил ще бъде най -долните три точки от данни, вторият квартил ще бъде следващите три точки от данни и т.н.
По -долу е наборът от данни, където искам да намеря отклоненията. За да направя това, ще трябва да изчисля 1 -ви и 3 -ти квартил и след това да го изчисля горната и долната граница.
По -долу е формулата за изчисляване на първия квартил в клетка E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
и ето този за изчисляване на третия квартил в клетка E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Сега мога да използвам горните две изчисления, за да получа интерквартилния диапазон (което е 50% от нашите данни в рамките на 1 -ви и 3 -ти квартил)
= 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, ROW ($ 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, за да намерите отклонения.
След като идентифицирате отклоненията, можете да се задълбочите в данните и да потърсите какво ги причинява, в същото време изберете една от техниките за справяне с тези отклонения (което може да бъде премахването на тези или нормализирането им чрез коригиране на стойността)
Надявам се, че сте намерили този урок за полезен.
