Cum (și de ce) să utilizați funcția Outliers în Excel

O depășire este o valoare semnificativ mai mare sau mai mică decât majoritatea valorilor din datele dvs. Atunci când se utilizează Excel pentru a analiza date, valorile excesive pot schimba 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 excedentele, așa că haideți să aruncăm o privire.

Un exemplu rapid

În imaginea de mai jos, valoroasele sunt relativ ușor de observat - valoarea a două atribuită lui Eric și valoarea de 173 atribuită lui Ryan. Într-un set de date ca acesta, este suficient de ușor să observi și să tratezi cu mâna acele valori excepționale.

Într-un set mai mare de date, acest lucru nu va fi cazul. Capacitatea de a identifica valorile negative și de a le elimina de la calculele statistice este importantă - și asta ne vom uita cum să procedăm în acest articol.

Cum să găsiți cele mai importante în datele dvs.

Pentru a găsi valorile depășite într-un set de date, folosim următorii pași:

Calculați primul și al treilea trimestru (vom vorbi despre ceea ce sunt într-un pic).
Evaluați intervalul interquartilat (vom explica și acestea mai departe).
Întoarceți limitele superioare și inferioare ale intervalului de date.
Utilizați aceste limite pentru a identifica punctele de date periferice.

Gama de celule din partea dreaptă a setului de date văzută în imaginea de mai jos va fi utilizată pentru stocarea acestor valori.

Să începem.

Pasul unu: Calculați cartilele

Dacă împărțiți datele în sferturi, fiecare din aceste seturi se numește un quartil. Cele mai mici 25% din numerele din intervalul alcătuiesc primul trimestru, următorii 25% din al doilea trimestru și așa mai departe. Facem acest pas mai întâi pentru că definiția cea mai utilizată a unui outlier este un punct de date care are mai mult de 1,5 intervale de interquartile (IQR) sub prima quartilă și 1,5 intervaluri interquartile deasupra celui de-al treilea trimestru. Pentru a determina aceste valori, trebuie mai întâi să ne dăm seama care sunt quartilele.

Excel oferă o funcție QUARTILE pentru a calcula quartile. Aceasta necesită două informații: matricea și lățimea.

= QUARTILE (matrice, quart)

mulțime este intervalul de valori pe care îl evaluați. Si cvartă este un număr care reprezintă cartilajul pe care doriți să îl returnați (de exemplu, 1 pentru 1^Sf quartila, 2 pentru trimestrul 2 ș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 compatibil mai mult înapoi atunci când lucrează în mai multe versiuni de Excel.

Să revenim la tabelul de exemplu.

Pentru a calcula 1^Sf În cartilajul F2 putem folosi următoarea formulă.

= QUARTILE (B2: B14,1)

Pe măsură ce introduceți formula, Excel oferă o listă de opțiuni pentru argumentul quart.

Pentru a calcula 3^rd , putem introduce o formulă ca cea precedentă în celula F3, dar folosind o trei în loc de una.

= QUARTILE (B2: B14,3)

Acum avem punctele de date de cartilaj afișate în celule.

Pasul al doilea: Evaluați intervalul Interquartile

Intervalul interquartile (sau IQR) reprezintă 50% din valorile medii din datele dvs. Se calculează ca diferența dintre valoarea din primul trimestru și cea din al treilea trimestru.

Vom folosi o formulă simplă în celula F4 care scade 1^Sf quartile de la 3^rd cvartilei:

= F3-F2

Acum, putem vedea intervalul nostru de interquartile afișat.

Pasul trei: Întoarceți legăturile inferioare și superioare

Limitele inferioare și superioare reprezintă cele mai mici și cele mai mari valori ale intervalului de date pe care dorim să le folosim. Orice valori mai mici sau mai mari decât aceste valori limitate sunt valori extreme.

Vom calcula limita inferioară în celula F5 prin înmulțirea valorii 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 multiplicare va calcula înainte de partea de scădere, dar ele fac formula mai ușor de citit.

Pentru a calcula limita superioară în celula F6, vom multiplica din nou IQR cu 1.5, dar de data aceasta adăuga la punctul de date Q3:

= F3 + (1,5 * F4)

Etapa a patra: Identificați valorile exacte

Acum, că avem toate datele de bază stabilite, este timpul să identificăm punctele de date de la distanță - cele care sunt mai mici decât valoarea limită inferioară sau mai mare decât valoarea limită superioară.

Vom folosi funcția OR pentru a efectua acest test logic și vom arăta valorile care îndeplinesc aceste criterii introducând următoarea formulă în celula C2:

= OR (B2 $ F $ 6)

Atunci vom copia acea valoare în celulele noastre C3-C14. O valoare TRUE indică o depășire și, după cum puteți vedea, avem două în datele noastre.

Ignorarea rezultatelor la calcularea mediei medii

Folosind funcția QUARTILE, să calculați IQR și să lucrați cu cea mai răspândită definiție a unui outlier. Cu toate acestea, atunci când se calculează media medie pentru un interval de valori și se ignoră valori extreme, există o funcție mai rapidă și mai ușoară de utilizat. Această tehnică nu va identifica un excedent ca înainte, dar ne va permite să fim flexibili cu ceea ce am putea considera partea noastră exterioară.

Funcția de care avem nevoie este denumită TRIMMEAN și puteți vedea sintaxa de mai jos:

= TRIMMEAN (matrice, procente)

mulțime este intervalul de valori pe care doriți să îl obțineți în medie. la sută este procentul punctelor de date pe care să le excludeți din partea de sus și de jos a setului de date (îl puteți introduce ca procentaj sau valoare zecimală).

Am introdus formularul de mai jos în celula D3 din exemplul nostru pentru a calcula media și a exclude 20% din valoarea maximă.

= TRIMMEAN (B2: B14, 20%)

Acolo aveți două funcții diferite pentru a manevra outliers. 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ă necesităților dvs..