Cuprins
Un outlier este o valoare care este semnificativ mai mare sau mai mică decât majoritatea valorilor din datele dvs. Când utilizați Excel pentru a analiza datele, valorile aberante pot denatura rezultatele. De exemplu, media medie a unui set de date ar putea reflecta cu adevărat valorile dvs. Excel oferă câteva funcții utile pentru a vă ajuta să vă gestionați valorile aberante, așa că să aruncăm o privire.
Un exemplu rapid
În imaginea de mai jos, valorile aberante sunt destul de ușor de observat – valoarea a doi atribuită lui Eric și valoarea 173 atribuită lui Ryan. Într-un set de date ca acesta, este destul de ușor să identificați și să tratați manual acele valori aberante.
Într-un set mai mare de date, acesta nu va fi cazul. A fi capabil să identifice valorile aberante și să le elimine din calculele statistice este important – și asta vom analiza cum să facem în acest articol.
Cum să găsiți valori aberante în datele dvs
Pentru a găsi valorile aberante dintr-un set de date, folosim următorii pași:
Calculați primul și al treilea quartile (vom vorbi despre ce sunt acestea doar puțin).
Evaluați intervalul intercuartil (le vom explica și acestea puțin mai jos).
Returnează limitele superioare și inferioare ale intervalului nostru de date.
Utilizați aceste limite pentru a identifica punctele de date periferice.
Intervalul de celule din dreapta setului de date văzut în imaginea de mai jos va fi folosit pentru a stoca aceste valori.
Să începem.
Pasul unu: Calculați quartilele
Dacă împărțiți datele în sferturi, fiecare dintre aceste seturi se numește un cuartil. Cele mai mici 25% dintre numere din interval alcătuiesc prima cuartilă, următoarele 25% din a doua cuartilă și așa mai departe. Facem acest pas mai întâi, deoarece definiția cea mai utilizată a unui valori aberante este un punct de date care se află la mai mult de 1,5 intervale intercuartile (IQR) sub prima cuartilă și 1,5 intervale intercuartile deasupra celei de-a treia cuartile. Pentru a determina aceste valori, mai întâi trebuie să ne dăm seama care sunt quartilele.
Excel oferă o funcție QUARTILE pentru a calcula quartile. Necesită două informații: matricea și quartul.
=QUARTILE(array, quart)
Matricea este intervalul de valori pe care îl evaluați. Iar quartila este un număr care reprezintă quartila pe care doriți să o returnați (de exemplu, 1 pentru prima quartila, 2 pentru a doua quartila și așa mai departe).
Notă: În Excel 2010, Microsoft a lansat funcțiile QUARTILE.INC și QUARTILE.EXC ca îmbunătățiri ale funcției QUARTILE. QUARTILE este mai compatibil cu versiunea inversă atunci când lucrați în mai multe versiuni de Excel.
Să revenim la tabelul nostru exemplu.
Pentru a calcula primul quartil putem folosi următoarea formulă în celula F2.
=QUARTILE(B2:B14,1)
Pe măsură ce introduceți formula, Excel oferă o listă de opțiuni pentru argumentul quart.
Pentru a calcula a treia cuartilă, putem introduce o formulă ca cea anterioară în celula F3, dar folosind un trei în loc de unul.
=QUARTILE(B2:B14,3)
Acum, avem punctele de date quartile afișate în celule.
Pasul doi: Evaluați intervalul intercuartil
Intervalul intercuartil (sau IQR) reprezintă 50% din mijlocul valorilor din datele dvs. Se calculează ca diferență între valoarea primei cuartile și valoarea a treia.
Vom folosi o formulă simplă în celula F4 care scade prima quartila din a treia:
=F3-F2
Acum, putem vedea gama noastră interquartile afișată.
Pasul trei: Reveniți limitele inferioare și superioare
Limitele inferioare și superioare sunt cele mai mici și cele mai mari valori ale intervalului de date pe care dorim să-l folosim. Orice valori mai mici sau mai mari decât aceste valori legate sunt valori aberante.
Vom calcula limita inferioară în celula F5 înmulțind valoarea IQR cu 1,5 și apoi scăzând-o din punctul de date Q1:
=F2-(1.5*F4)
Notă: Parantezele din această formulă nu sunt necesare deoarece partea de înmulțire se va calcula înainte de partea de scădere, dar fac formula mai ușor de citit.
Pentru a calcula limita superioară în celula F6, vom înmulți din nou IQR-ul cu 1,5, dar de data aceasta îl vom adăuga la punctul de date Q3:
=F3+(1.5*F4)
Pasul patru: identificați valorile aberante
Acum că ne-am configurat toate datele de bază, este timpul să identificăm punctele noastre de date periferice – cele care sunt mai mici decât valoarea limită inferioară sau mai mari decât valoarea limită superioară.
Vom folosi funcția SAU pentru a efectua acest test logic și pentru a afișa valorile care îndeplinesc aceste criterii introducând următoarea formulă în celula C2:
=OR(B2$F$6)
Apoi vom copia acea valoare în celulele noastre C3-C14. O valoare TRUE indică o valoare anormală și, după cum puteți vedea, avem două în datele noastre.
Ignorarea valorii aberante la calcularea mediei
Folosind funcția QUARTILE, ne permite să calculăm IQR și să lucrăm cu cea mai utilizată definiție a valorii aberante. Cu toate acestea, atunci când se calculează media medie pentru un interval de valori și se ignoră valorile aberante, există o funcție mai rapidă și mai ușor de utilizat. Această tehnică nu va identifica o valoare anormală ca înainte, dar ne va permite să fim flexibili cu ceea ce am putea considera porțiunea noastră anormală.
Funcția de care avem nevoie se numește TRIMMEAN și puteți vedea sintaxa ei mai jos:
=TRIMMEAN(array, percent)
Matricea este intervalul de valori pe care doriți să o medieți. Procentul este procentul de puncte de date de exclus din partea de sus și de jos a setului de date (puteți introduce ca procent sau valoare zecimală).
Am introdus formula de mai jos în celula D3 din exemplul nostru pentru a calcula media și a exclude 20% din valori aberante.
=TRIMMEAN(B2:B14, 20%)
Acolo aveți două funcții diferite pentru gestionarea valorii aberante. Indiferent dacă doriți să le identificați pentru anumite nevoi de raportare sau să le excludeți din calcule, cum ar fi mediile, Excel are o funcție care să se potrivească nevoilor dvs.