Când lucrați cu date în Excel, veți avea adesea probleme legate de gestionarea valorilor aberante în setul dvs. de date.
A avea valori aberante este destul de obișnuit în toate tipurile de date și este important să identificați și să tratați aceste valori aberante pentru a vă asigura că analiza dvs. este corectă și mai semnificativă.
În acest tutorial, vă voi arăta cum să găsiți valori aberante în Excel, și câteva dintre tehnicile pe care le-am folosit în munca mea pentru a face față acestor valori aberante.
Ce sunt valorile aberante și de ce este important să le găsiți?
Un outlier este un punct de date care depășește cu mult celelalte puncte de date din setul de date. Când aveți o valoare anterioară în date, vă poate distorsiona datele, ceea ce poate duce la inferențe incorecte.
Permiteți-mi să vă dau un exemplu simplu.
Să presupunem că 30 de persoane călătoresc într-un autobuz de la destinația A la destinația B. Toți oamenii fac parte dintr-un grup de greutate și un grup de venituri similar. În scopul acestui tutorial, să considerăm că greutatea medie este de 220 de lire sterline, iar venitul mediu anual este de 70.000 de dolari.
Acum, undeva în mijlocul traseului nostru, autobuzul oprește și Bill Gates intră.
Acum, ce credeți că ar face acest lucru cu greutatea medie și cu venitul mediu al persoanelor din autobuz.
Deși greutatea medie nu se va schimba prea mult, venitul mediu al persoanelor din autobuz va crește foarte mult.
Acest lucru se datorează faptului că veniturile lui Bill Gates reprezintă un aspect anormal în grupul nostru și asta ne oferă o interpretare greșită a datelor. Venitul mediu pentru fiecare persoană din autobuz ar fi de câteva miliarde de dolari, ceea ce depășește cu mult valoarea reală.
Când lucrați cu seturi de date reale în Excel, puteți avea valori anormale în orice direcție (de exemplu, un valor outlier pozitiv sau un outlier negativ).
Și pentru a vă asigura că analiza dvs. este corectă, trebuie cumva să identificați aceste valori aberante și apoi să decideți cum să le tratați cel mai bine.
Acum să vedem câteva modalități de a găsi valori aberante în Excel.
Găsiți valori anormale sortând datele
Cu seturi de date mici, o modalitate rapidă de a identifica valorile aberante este să sortați pur și simplu datele și să parcurgeți manual unele dintre valorile din partea de sus a acestor date sortate.
Și întrucât ar putea exista valori aberante în ambele direcții, asigurați-vă că mai întâi sortați datele în ordine crescătoare, apoi în ordine descendentă și apoi parcurgeți valorile de sus.
Permiteți-mi să vă arăt un exemplu.
Mai jos am un set de date în care am durate de apel (în secunde) pentru 15 apeluri de servicii pentru clienți.
Mai jos sunt pașii pentru sortarea acestor date, astfel încât să putem identifica valorile aberante din setul de date:
- Selectați Antetul coloanei coloanei pe care doriți să o sortați (celula B1 din acest exemplu)
- Faceți clic pe fila Acasă
- În grupul Editare, faceți clic pe pictograma Sortare și filtrare.
- Faceți clic pe Sortare personalizată
- În caseta de dialog Sortare, selectați „Durată” în meniul derulant Sortare și „Cel mai mare la cel mai mic” din meniul derulant Comandă
- Faceți clic pe Ok
Pașii de mai sus ar sorta coloana durata apelului cu cele mai mari valori în partea de sus. Acum puteți scana manual datele și puteți vedea dacă există valori aberante.
În exemplul nostru, pot vedea că primele două valori sunt mult mai mari decât restul valorilor (iar cele două de jos sunt mult mai mici).
Notă: Această metodă funcționează cu seturi de date mici, unde puteți scana manual datele. Nu este o metodă științifică, dar funcționează bine
Găsirea valorilor aberante utilizând funcțiile cuartile
Acum, să vorbim despre o soluție mai științifică care vă poate ajuta să identificați dacă există sau nu valori aberante.
În statistici, o quartilă reprezintă o pătrime din setul de date. De exemplu, dacă aveți 12 puncte de date, atunci prima quartilă ar fi ultimele trei puncte de date, a doua quartilă ar fi următoarele trei puncte de date și așa mai departe.
Mai jos este setul de date în care vreau să găsesc valorile aberante. Pentru a face acest lucru, va trebui să calculez prima și a treia quartilă, iar apoi folosind-o calculați limita superioară și inferioară.
Mai jos este formula pentru calcularea primei quartile din celula E2:
= QUARTILE.INC ($ B $ 2: $ B $ 15,1)
și aici este cel care calculează a treia quartilă din celula E3:
= QUARTILE.INC ($ B $ 2: $ B $ 15,3)
Acum, pot folosi cele două calcule de mai sus pentru a obține gama interquartilă (care reprezintă 50% din datele noastre în primul și al treilea quartile)
= F3-F2
Acum vom folosi gama interquartile pentru a găsi limita inferioară și superioară care ar conține majoritatea datelor noastre.
Orice lucru care se află în afara acestor limite inferioare și superioare ar fi atunci considerat a fi atras.
Mai jos este formula pentru a calcula limita inferioară:
= Quartile1 - 1,5 * (Inter Quartile Range)
care în exemplul nostru devine:
= F2-1,5 * F4
Și formula pentru a calcula limita superioară este:
= Quartile3 + 1,5 * (Inter Quartile Range)
care în exemplul nostru devine:
= F3 + 1,5 * F4
Acum că avem limita superioară și inferioară în setul nostru de date, putem reveni la datele originale și putem identifica rapid acele valori care nu se află în acest interval.
O modalitate rapidă de a face acest lucru ar fi verificarea fiecărei valori și returnarea unui ADEVĂR sau FALS într-o nouă coloană.
Am folosit formula SAU de mai jos pentru a obține ADEVĂRAT pentru acele valori care sunt cu valori anormale.
= SAU (B2 $ F $ 6)
Acum puteți filtra coloana Outlier și puteți afișa numai înregistrările unde valoarea este ADEVĂRATĂ.
Alternativ, puteți utiliza, de asemenea, formatarea condițională pentru a evidenția toate celulele în care valoarea este ADEVĂRATĂ
Notă: Deși aceasta este o metodă mai acceptată pentru a găsi valori aberante în statistici. Găsesc această metodă un pic inutilizabilă în scenarii din viața reală. În exemplul de mai sus, limita inferioară calculată prin formulă este -103, în timp ce setul de date pe care îl avem poate fi doar pozitiv. Deci, această metodă ne poate ajuta să găsim valori aberante într-o direcție (valori ridicate), este inutil să identificăm valori aberante în cealaltă direcție.Găsirea valorilor aberante Utilizarea funcțiilor LARGE / SMALL
Dacă lucrați cu o mulțime de date (valori în mai multe coloane), puteți extrage cea mai mare și cea mai mică 5 sau 7 valori și puteți vedea dacă există valori anormale în ea.
Dacă există valori aberante, le veți putea identifica fără a fi nevoie să parcurgeți toate datele în ambele direcții.
Să presupunem că avem setul de date de mai jos și vrem să știm dacă există valori aberante.
Mai jos este formula care vă va oferi cea mai mare valoare din setul de date:
= LARGE ($ B $ 2: $ B $ 16,1)
În mod similar, a doua cea mai mare valoare va fi dată de
= LARGE ($ B $ 2: $ B $ 16,1)
Dacă nu utilizați Microsoft 365, care are matrice dinamice, puteți utiliza formula de mai jos și vă va oferi cele mai mari cinci valori din setul de date cu o singură formulă:
= LARGE ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
În mod similar, dacă doriți cele mai mici 5 valori, utilizați formula de mai jos:
= MIC ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
sau următoarele în cazul în care nu aveți tablouri dinamice:
= MIC ($ B $ 2: $ B $ 16,1)
Odată ce aveți aceste valori, este foarte ușor să aflați orice elemente aberante din setul de date.
În timp ce am ales să extrag cele mai mari și cele mai mici 5 valori, puteți alege să obțineți 7 sau 10 în funcție de cât de mare este setul dvs. de date.
Nu sunt sigur dacă aceasta este o metodă acceptabilă pentru găsirea valorilor aberante în Excel sau nu, dar aceasta este metoda pe care am folosit-o când am trebuit să lucrez cu o mulțime de date financiare la locul meu de muncă acum câțiva ani. Comparativ cu toate celelalte metode acoperite în acest tutorial, am găsit că aceasta este cea mai eficientă.
Cum să gestionați valorile aberante în modul corect
Până acum, am văzut metodele care ne vor ajuta să găsim valorile aberante din setul nostru de date. Dar ce să faci după ce știi că există valori aberante.
Iată câteva metode pe care le puteți utiliza pentru a gestiona valorile aberante, astfel încât analiza datelor dvs. să fie corectă.
Ștergeți valorile aberante
Cel mai simplu mod de a elimina valorile aberante din setul de date este pur și simplu să le ștergeți. În acest fel, nu vă va distorsiona analiza.
Este o soluție mai viabilă atunci când aveți seturi de date mari și ștergerea câtorva valori anterioare nu va avea impact asupra analizei generale. Și, bineînțeles, înainte de a șterge datele, asigurați-vă că creați o copie și căutați în ce cauzează aceste valori anormale.
Normalizați valorile aberante (ajustați valoarea)
Normalizarea valorilor aberante este ceea ce obișnuiam să fac când eram în serviciul cu normă întreagă. Pentru toate valorile anterioare, le-aș schimba pur și simplu într-o valoare care este puțin mai mare decât valoarea maximă din setul de date.
Acest lucru s-a asigurat că nu șterg datele, dar, în același timp, nu le las să distrugă datele mele.
Pentru a vă oferi un exemplu din viața reală, dacă analizați marja de profit netă a companiilor, unde majoritatea companiilor se situează între -10% și 30% și există câteva valori care cresc în sus de 100%, eu ar schimba pur și simplu aceste valori anterioare la 30% sau 35%.
Deci, acestea sunt câteva dintre metodele în care puteți utiliza Excel pentru a găsi valori aberante.
După ce ați identificat valorile aberante, puteți să vă adânciți în date și să căutați ce cauzează acestea, în același timp alegeți una dintre tehnicile de gestionare a acestor valori aberante (care ar putea fi eliminarea acestora sau normalizarea acestora prin ajustarea valorii)
Sper că ți s-a părut util acest tutorial.
