agwebrunner.com

Un valor atípic és un valor significativament superior o inferior a la majoria dels valors de les vostres dades. Quan s’utilitza Excel per analitzar dades, els valors extrems poden distorsionar els resultats. Per exemple, la mitjana mitjana d’un conjunt de dades pot reflectir realment els vostres valors. Excel proporciona algunes funcions útils per ajudar-vos a gestionar els valors atípics, així que fem una ullada.

Un exemple ràpid

A la imatge següent, els valors atípics són raonablement fàcils de detectar: ​​el valor de dos assignats a Eric i el valor de 173 assignat a Ryan. En un conjunt de dades com aquest, és prou fàcil detectar i tractar manualment aquests valors atípics.

En un conjunt de dades més gran, no serà així. És important identificar els valors atípics i eliminar-los dels càlculs estadístics, i això és el que veurem com fer en aquest article.

Com trobar valors atípics a les vostres dades

Per trobar els valors atípics en un conjunt de dades, fem els passos següents:

1. Calculeu el 1r i el 3r quartils (parlarem de quins són aquests pocs).
2. Avalueu l'interval intercuartil (també els explicarem una mica més avall).
3. Retorna els límits superior i inferior del nostre rang de dades.
4. Utilitzeu aquests límits per identificar els punts de dades perifèrics.

L’interval de cel·les a la dreta del conjunt de dades que es mostra a la imatge següent s’utilitzarà per emmagatzemar aquests valors.

Comencem.

Primer pas: calculeu els quartils

Si dividiu les vostres dades en quarts, cadascun d’aquests conjunts s’anomena quartil. El 25% més baix dels números de l’interval constitueixen el 1r quartil, el 25% següent el 2n quartil, etc. Primer fem aquest pas perquè la definició més àmpliament utilitzada d’un valor atípic és un punt de dades que es troba a més d’1,5 rangs interquartils (IQRs) per sota del primer quartil i 1,5 rangs interquartils per sobre del 3r quartil. Per determinar aquests valors, primer hem d’esbrinar quins són els quartils.

Excel proporciona una funció QUARTILE per calcular quartils. Requereix dues informacions: la matriu i el quart.

= QUARTIL (matriu, quart)

El matriu és l'interval de valors que esteu avaluant. I la quart és un número que representa el quartil que voleu retornar (per exemple, 1 per al 1r quartil, 2 per al 2n quartil, etc.).

Nota: A Excel 2010, Microsoft va publicar les funcions QUARTILE.INC i QUARTILE.EXC com a millores a la funció QUARTILE. QUARTILE és més compatible amb les versions anteriors quan es treballa en diverses versions d'Excel.

Tornem a la nostra taula d'exemple.

Per calcular el 1r quartil podem utilitzar la següent fórmula a la cel·la F2.

= QUARTIL (B2: B14,1)

Quan introduïu la fórmula, Excel proporciona una llista d'opcions per a l'argument quart.

Per calcular el tercer quartil, podem introduir una fórmula com l'anterior a la cel·la F3, però utilitzant un tres en lloc d'un.

= QUARTIL (B2: B14,3)

Ara tenim els punts de dades del quartil a les cel·les.

Segon pas: avaluar el rang interquartil

L'interval intercuartil (o IQR) és el 50% mitjà dels valors de les vostres dades. Es calcula com la diferència entre el valor del primer quartil i el valor del tercer quartil.

Utilitzarem una fórmula senzilla a la cel·la F4 que resta el 1r quartil del 3r quartil:

= F3-F2

Ara podem veure el nostre rang interquartil.

Tercer pas: torneu els límits inferior i superior

Els límits inferior i superior són els valors més petits i més grans de l’interval de dades que volem utilitzar. Els valors més petits o més grans que aquests valors vinculats són els valors atípics.

Calcularem el límit límit inferior de la cel·la F5 multiplicant el valor IQR per 1,5 i restant-lo del punt de dades Q1:

= F2- (1,5 * F4)

Nota: Els claudàtors d’aquesta fórmula no són necessaris perquè la part de multiplicació es calcularà abans de la resta, però sí que facilita la lectura de la fórmula.

Per calcular el límit superior de la cel·la F6, tornarem a multiplicar l’IQR per 1,5, però aquesta vegada afegir al punt de dades Q3:

= F3 + (1,5 * F4)

Quart pas: identifiqueu els valors atípics

Ara que ja tenim configurades totes les nostres dades subjacents, és hora d’identificar els nostres punts de dades perifèrics, els que són inferiors al valor límit inferior o superior al valor límit superior.

Utilitzarem la funció OR per realitzar aquesta prova lògica i mostrarem els valors que compleixen aquests criteris introduint la fórmula següent a la cel·la C2:

= O (B2 $ F $ 6)

A continuació, copiarem aquest valor a les nostres cel·les C3-C14. Un valor TRUE indica un valor atípic i, com podeu veure, en tenim dues a les nostres dades.

Ignorant els valors atípics en calcular la mitjana mitjana

Mitjançant la funció QUARTILE permetem calcular l’IQR i treballar amb la definició més usada d’un valor atípic. Tanmateix, quan es calcula la mitjana mitjana d'un interval de valors i s'ignoren els valors atípics, hi ha una funció més ràpida i fàcil d'utilitzar. Aquesta tècnica no identificarà cap valor atípic com abans, però ens permetrà ser flexibles amb el que podríem considerar la nostra part atípica.

La funció que necessitem es diu TRIMMEAN i podeu veure la sintaxi següent:

= TRIMMEAN (matriu, percentatge)

El matriu és l'interval de valors que voleu fer de mitjana. El per cent és el percentatge de punts de dades que s’exclouen de la part superior i inferior del conjunt de dades (podeu introduir-lo com a percentatge o com a valor decimal).

Hem introduït la fórmula següent a la cel·la D3 al nostre exemple per calcular la mitjana i excloure el 20% dels valors atípics.

= TRIMMEAN (B2: B14, 20%)

Allà teniu dues funcions diferents per gestionar valors atípics. Tant si voleu identificar-los per a algunes necessitats d'informes o excloure'ls de càlculs com ara mitjanes, Excel té una funció que s'adapta a les vostres necessitats.