Un valor atípico es un valor que es significativamente más alto o más bajo que la mayoría de los valores en sus datos. Cuando usa Excel para analizar datos, los valores atípicos pueden sesgar los resultados. Por ejemplo, el promedio de un conjunto de datos realmente puede reflejar sus valores. Excel proporciona algunas funciones útiles para ayudarlo a lidiar con sus valores atípicos, así que echemos un vistazo.
Un ejemplo rapido
En la imagen de abajo, los valores atípicos son relativamente fáciles de detectar: el valor de Eric de dos y el de Ryan de 173. En un conjunto de datos como este, es bastante fácil detectar y tratar estos valores atípicos manualmente.
En un conjunto de datos más grande, este no será el caso. Ser capaz de identificar valores atípicos y eliminarlos de los cálculos estadísticos es importante, y eso es lo que veremos cómo hacer en este artículo.
Cómo encontrar valores atípicos en sus datos
Para encontrar valores atípicos en un conjunto de datos, utilizamos los siguientes pasos:
- Calcula el primer y tercer cuartiles (hablaremos de lo que son en un momento).
- Evalúa el rango intercuartílico (también los explicaremos un poco más abajo).
- Devuelve los límites superior e inferior de nuestro rango de datos.
- Utilice estos límites para identificar puntos de datos de borde.
El rango de celdas a la derecha del conjunto de datos visible en la imagen a continuación se utilizará para almacenar estos valores.
Empecemos.
Paso uno: calcula los cuartiles
Si divide sus datos en cuartos, cada uno de estos conjuntos se denomina cuartil. El 25% más bajo del rango es el primer cuartil, el siguiente 25% el segundo cuartil, y así sucesivamente. Damos este paso primero porque la definición más utilizada de un valor atípico es un punto de datos que está más de 1,5 en el rango intercuartil (IQR) por debajo del primer cuartil y 1,5 en el rango intercuartil por encima del 3er cuartil. Para determinar estos valores, primero debemos determinar cuáles son los cuartiles.
Excel proporciona una función CUARTIL para calcular cuartiles. Requiere dos piezas de información: la mesa y el trimestre.
=QUARTILE(array, quart)
La matriz es el rango de valores que está evaluando. Y el cuarto es un número que representa el cuartil que desea devolver (por ejemplo, 1 para el primer cuartil, 2 para el segundo cuartil, etc.).
Notar: En Excel 2010, Microsoft lanzó las funciones QUARTILE.INC y QUARTILE.EXC como mejoras a la función QUARTILE. QUARTILE es más compatible con versiones anteriores cuando se trabaja en varias versiones de Excel.
Volvamos a nuestra tabla de ejemplo.
Para calcular el primer cuartil, podemos usar la siguiente fórmula en la celda F2.
=QUARTILE(B2:B14,1)
Cuando ingresa la fórmula, Excel proporciona una lista de opciones para el argumento del trimestre.
Para calcular el tercer cuartil, podemos ingresar una fórmula como la anterior en la celda F3, pero usando un tres en lugar de uno.
=QUARTILE(B2:B14,3)
Ahora tenemos los puntos de datos del cuartil mostrados en las celdas.
Paso dos: evalúe el rango intercuartílico
El rango intercuartil (o IQR) es el punto medio del 50% de los valores en sus datos. Se calcula como la diferencia entre el valor del primer cuartil y el valor del tercer cuartil.
Usaremos una fórmula simple en la celda F4 que resta el primer cuartil del tercer cuartil:
=F3-F2
Ahora podemos ver nuestro rango intercuartil mostrado.
Paso tres: devuelve los límites inferior y superior
Los límites inferior y superior son los valores más pequeños y más grandes del rango de datos que queremos usar. Cualquier valor por debajo o por encima de estos valores límite es un valor atípico.
Calcularemos el límite inferior en la celda F5 multiplicando el valor IQR por 1.5 y luego restándolo del punto de datos Q1:
=F2-(1.5*F4)
Notar: Los paréntesis en esta fórmula no son necesarios porque la parte de la multiplicación se calculará antes que la parte de la resta, pero hacen que la fórmula sea más fácil de leer.
Para calcular el límite superior en la celda F6, multiplicamos el IQR por 1.5 nuevamente, pero esta vez lo agregamos al punto de datos Q3:
=F3+(1.5*F4)
Paso cuatro: identificar valores atípicos
Ahora que hemos configurado todos nuestros datos subyacentes, es hora de identificar nuestros puntos de datos de borde, que están por debajo del límite inferior o por encima del límite superior.
Usaremos el Función OR para realizar esta prueba lógica y mostrar los valores que cumplen estos criterios ingresando la siguiente fórmula en la celda C2:
=OR(B2<$F$5,B2>$F$6)
Luego copiaremos este valor en nuestras celdas C3-C14. Un valor VERDADERO indica un valor atípico y, como puede ver, tenemos dos de ellos en nuestros datos.
Ignore los valores atípicos al calcular la media media
Usando la función CUARTIL, calculemos el IQR y trabajemos con la definición más utilizada de un valor atípico. Sin embargo, al calcular la media promedio para un rango de valores e ignorar los valores atípicos, existe una función más rápida y fácil de usar. Esta técnica no identificará un valor atípico como antes, pero nos permitirá ser flexibles con lo que podríamos considerar nuestro valor atípico.
La función que necesitamos se llama TRIMMEAN, y puede ver su sintaxis a continuación:
=TRIMMEAN(array, percent)
La matriz es el rango de valores que desea promediar. El porcentaje es el porcentaje de puntos de datos para excluir de la parte superior e inferior del conjunto de datos (puede ingresarlo como un porcentaje o como un valor decimal).
Ingresamos la fórmula a continuación en la celda D3 de nuestro ejemplo para calcular la media y excluir el 20% de los valores atípicos.
=TRIMMEAN(B2:B14, 20%)
Allí tiene dos funciones diferentes para manejar valores atípicos. Ya sea que desee identificarlos para determinados fines de generación de informes o excluirlos de cálculos como promedios, Excel tiene una función que se adapta a sus necesidades.