Када радите са подацима у Екцелу, често ћете имати проблема са руковањем изванредним вредностима у свом скупу података.
Имати истицања је уобичајено у свим врстама података, па је важно идентификовати и третирати те издвојености да бисте били сигурни да је ваша анализа тачна и смисленија.
У овом водичу ћу вам показати како пронаћи екстеријере у Екцелу, и неке од техника које сам користио у свом раду како бих се носио са овим издвојеностима.
Шта су истицања и зашто их је важно пронаћи?
Оутлиер је тачка података која је далеко изнад осталих тачака података у скупу података. Када у подацима имате истицање, то може искривити ваше податке што може довести до погрешних закључака.
Даћу вам једноставан пример.
Рецимо да 30 људи путује аутобусом од одредишта А до одредишта Б. Сви људи су у сличној тежинској групи и групи прихода. За потребе овог водича, узмимо у обзир да је просечна тежина 220 фунти, а просечни годишњи приход 70.000 долара.
Негде на пола пута, аутобус стаје, а Билл Гатес ускаче.
Шта мислите да би ово учинило просечној тежини и просечним приходима људи у аутобусу.
Иако се вероватноћа да се просечна тежина неће много променити, просечан приход људи у аутобусу ће се знатно повећати.
То је зато што је приход Билл Гатес -а изван наше групе, а то нам даје погрешно тумачење података. Просечан приход за сваку особу у аутобусу био би неколико милијарди долара, што је далеко изнад стварне вредности.
Када радите са стварним скуповима података у Екцелу, можете имати истицања у било ком смеру (тј. Позитиван или негативан испад).
А да бисте били сигурни да је ваша анализа тачна, морате некако идентификовати ове одступања, а затим одлучити како их најбоље третирати.
Погледајмо сада неколико начина за проналажење изостанка у Екцелу.
Сортирајте податке да бисте пронашли изузетке
Са малим скуповима података, брз начин да се идентификују одступања је једноставно сортирање података и ручно пролазак кроз неке од вредности на врху ових сортираних података.
А пошто би могло доћи до издвајања у оба смера, уверите се да прво сортирате податке у растућем, а затим у опадајућем редоследу, а затим прођите кроз највеће вредности.
Дозволите ми да вам покажем пример.
Испод имам скуп података у којем имам трајање позива (у секундама) за 15 позива корисничкој служби.
У наставку су наведени кораци за сортирање ових података како бисмо могли идентификовати одступања у скупу података:
- Изаберите Заглавље колоне колоне коју желите да сортирате (ћелија Б1 у овом примеру)
- Кликните на картицу Почетна
- У групи Уређивање кликните на икону Сортирај и филтрирај.
- Кликните на Прилагођено сортирање
- У дијалогу Сортирање изаберите „Трајање“ у падајућем менију Сортирај према и „Од највећег до најмањег“ у падајућем менију Наруџба
- Притисните У реду
Горе наведени кораци би сортирали колону трајања позива са највишим вредностима на врху. Сада можете ручно скенирати податке и видети да ли постоје испади.
У нашем примеру, видим да су прве две вредности далеко веће од осталих вредности (а доње две су знатно ниже).
Напомена: Ова метода ради са малим скуповима података где можете ручно скенирати податке. То није научна метода, али добро функционише
Проналажење истицања помоћу функција квартила
Хајде сада да разговарамо о научнијем решењу које вам може помоћи да идентификујете да ли постоје изузеци или не.
У статистици, квартил је једна четвртина скупа података. На пример, ако имате 12 тачака података, тада би први квартил биле три доње тачке података, други квартил би биле следеће три тачке података итд.
Испод је скуп података у коме желим да пронађем одступања. Да бих то урадио, мораћу да израчунам први и трећи квартил, а затим помоћу њега израчунам горњу и доњу границу.
Испод је формула за израчунавање првог квартила у ћелији Е2:
= КУАРТИЛЕ.ИНЦ ($ Б $ 2: $ Б $ 15,1)
и ево једне за израчунавање трећег квартила у ћелији Е3:
= КУАРТИЛЕ.ИНЦ ($ Б $ 2: $ Б $ 15,3)
Сада могу да употребим горња два израчуна за добијање интерквартилног распона (што је 50% наших података унутар првог и трећег квартила)
= Ф3-Ф2
Сада ћемо користити међуквартилни распон за проналажење доње и горње границе која би садржавала већину наших података.
Све што је изван ових доњих и горњих граница тада би се сматрало изванредним.
Испод је формула за израчунавање доње границе:
= Куартиле1 - 1,5*(Интер Куартиле Ранге)
што у нашем примеру постаје:
= Ф2-1,5*Ф4
А формула за израчунавање горње границе је:
= Квартил3 + 1,5*(међуквартилни распон)
што у нашем примеру постаје:
= Ф3+1,5*Ф4
Сада када имамо горњу и доњу границу у скупу података, можемо се вратити на изворне податке и брзо идентификовати оне вредности које не леже у овом опсегу.
Брз начин да то учините био би да проверите сваку вредност и вратите ТРУЕ или ФАЛСЕ у нову колону.
Користио сам доњу формулу ИЛИ да добијем ТРУЕ за оне вредности које су изванредне.
= ИЛИ (Б2 $ Ф $ 6)
Сада можете филтрирати колону Оутлиер и приказати само записе у којима је вредност ТРУЕ.
Алтернативно, такође можете користити условно обликовање да бисте истакли све ћелије у којима је вредност ТРУЕ
Белешка: Иако је ово прихваћенији метод за проналажење истицања у статистици. Сматрам да је ова метода помало неупотребљива у сценаријима из стварног живота. У горњем примеру, доња граница израчуната формулом је -103, док скуп података који имамо може бити само позитиван. Дакле, ова метода нам може помоћи да пронађемо истицање у једном смјеру (велике вриједности), бескорисно је у идентифицирању истицања у другом смјеру.Проналажење оутлиера помоћу функција ЛАРГЕ/СМАЛЛ
Ако радите са пуно података (вредности у више колона), можете издвојити највећу и најмању вредност од 5 или 7 и видети да ли у њој постоје истицања.
Ако постоје неки одступници, моћи ћете да их идентификујете без потребе да пролазите кроз све податке у оба смера.
Претпоставимо да имамо низ података у наставку и желимо да знамо да ли постоје изузеци.
Испод је формула која ће вам дати највећу вредност у скупу података:
= ВЕЛИКО ($ Б $ 2: $ Б $ 16,1)
Слично, другу највећу вредност ће дати
= ВЕЛИКО ($ Б $ 2: $ Б $ 16,1)
Ако не користите Мицрософт 365, који има динамичке низове, можете користити доњу формулу и она ће вам дати пет највећих вредности из скупа података са једном формулом:
= ВЕЛИКО ($ Б $ 2: $ Б $ 16, РОВ ($ 1: 5))
Слично, ако желите најмањих 5 вредности, користите доњу формулу:
= МАЛО ($ Б $ 2: $ Б $ 16, РОВ ($ 1: 5))
или следеће ако немате динамичке низове:
= МАЛО ($ Б $ 2: $ Б $ 16,1)
Једном када имате ове вредности, заиста је лако открити било какве издвојености у скупу података.
Иако сам одабрао издвајање највећих и најмањих 5 вредности, можете изабрати да добијете 7 или 10 на основу величине вашег скупа података.
Нисам сигуран да ли је ово прихватљив метод за проналажење изостанка у Екцелу или не, али ово је метода коју сам користио када сам пре неколико година морао да радим са много финансијских података у свом послу. У поређењу са свим осталим методама описаним у овом водичу, открио сам да је овај најефикаснији.
Како на прави начин поступати са оутлиерима
До сада смо видели методе које ће нам помоћи да пронађемо изузетке у нашем скупу података. Али шта да радите када знате да постоје истицања.
Ево неколико метода које можете користити за обраду изванредних вредности како би анализа података била тачна.
Избришите истицања
Најлакши начин да уклоните испадање из скупа података је да их једноставно избришете. На овај начин неће искривити вашу анализу.
То је одрживије решење када имате велике скупове података и брисање неколико издвојених података неће утицати на укупну анализу. И наравно, пре брисања података, обавезно направите копију и удубите се у узроке ових истицања.
Нормализујте истицање (прилагодите вредност)
Нормализација издвајања је оно што сам радила док сам радила са пуним радним временом. За све вањске вредности, једноставно бих их променио у вредност која је нешто већа од максималне вредности у скупу података.
Ово је осигурало да не бришем податке, али истовремено не дозвољавам да искриви моје податке.
Да вам дам пример из стварног живота, ако анализирате маржу нето добити компанија, где већина компанија лежи унутар -10%до 30%, а постоји неколико вредности које су веће од 100%, ја једноставно би променили ове вредности излаза на 30% или 35%.
Дакле, ово су неке од метода које можете користити Екцел за проналажење истицања.
Након што сте идентификовали истицања, можете заронити у податке и потражити шта их узрокује, у исто време изабрати једну од техника за руковање овим истицањима (која би могла да их уклони или нормализује подешавањем вредности)
Надам се да вам је овај водич био користан.
