- Alan Murray
@Computergaga1
- Actualizado el 12 de enero de 2019, 5:03am EDT
Un valor atípico es un valor que es significativamente mayor o menor que la mayoría de los valores en sus datos. Cuando se utiliza Excel para analizar datos, los valores atípicos pueden sesgar los resultados. Por ejemplo, la media de un conjunto de datos podría reflejar realmente sus valores. Excel proporciona algunas funciones útiles para ayudar a gestionar los valores atípicos, así que echemos un vistazo.
Un ejemplo rápido
En la imagen de abajo, los valores atípicos son razonablemente fáciles de detectar: el valor de dos asignado a Eric y el valor de 173 asignado a Ryan. En un conjunto de datos como este, es bastante fácil detectar y tratar esos valores atípicos manualmente.
En un conjunto de datos más grande, ese no será el caso. Ser capaz de identificar los 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 tus datos
Para encontrar los valores atípicos en un conjunto de datos, utilizamos los siguientes pasos:
- Calcular los cuartiles 1 y 3 (hablaremos de lo que son en un momento).
- Evaluar el rango intercuartil (también lo explicaremos un poco más abajo).
- Devuelve los límites superior e inferior de nuestro rango de datos.
- Usa estos límites para identificar los puntos de datos periféricos.
El rango de celdas de la derecha del conjunto de datos que se ve en la imagen de abajo se usará para almacenar estos valores.
Comencemos.
Primer paso: calcular los cuartiles
Si divides tus datos en cuartos, cada uno de esos conjuntos se llama cuartil. El 25% más bajo de los números en el rango constituye el primer cuartil, el siguiente 25% el segundo cuartil, y así sucesivamente. Este paso es el primero porque la definición más extendida de un valor atípico es un punto de datos que está más de 1,5 rangos intercuartílicos (IQR) por debajo del primer cuartil y 1,5 rangos intercuartílicos por encima del tercer cuartil. Para determinar esos valores, primero tenemos que averiguar cuáles son los cuartiles.
Excel proporciona una función QUARTILE para calcular los cuartiles. Requiere dos datos: el array y el cuartil.
=QUARTILE(array, quart)
El array es el rango de valores que está evaluando. Y el cuartil es un número que representa el cuartil que desea devolver (por ejemplo, 1 para el primer cuartil, 2 para el segundo cuartil, y así sucesivamente).
Nota: En Excel 2010, Microsoft lanzó las funciones QUARTILE.INC y QUARTILE.EXC como mejoras de la función QUARTILE. QUARTILE es más compatible con las versiones anteriores cuando se trabaja en varias versiones de Excel.
Volvamos a nuestra tabla de ejemplo.
Para calcular el 1er Cuartil podemos utilizar la siguiente fórmula en la celda F2.
=QUARTILE(B2:B14,1)
Al introducir la fórmula, Excel proporciona una lista de opciones para el argumento del cuartil.
Para calcular el tercer cuartil, podemos introducir una fórmula como la anterior en la celda F3, pero utilizando un tres en lugar de un uno.
=QUARTILE(B2:B14,3)
Ahora, tenemos los puntos de datos del cuartil mostrados en las celdas.
Paso dos: Evaluar el rango intercuartil
El rango intercuartil (o IQR) es el 50% medio de los valores de sus datos. Se calcula como la diferencia entre el valor del primer cuartil y el valor del tercer cuartil.
Vamos a utilizar una fórmula simple en la celda F4 que resta el 1er cuartil del 3er cuartil:
=F3-F2
Ahora, podemos ver nuestro rango intercuartil mostrado.
Paso tres: Devolver 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 utilizar. Cualquier valor más pequeño o más grande que estos valores límite son los valores atípicos.
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)
Nota: Los paréntesis en esta fórmula no son necesarios porque la parte de la multiplicación se calculará antes de 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, multiplicaremos el IQR por 1.5 de nuevo, pero esta vez lo añadiremos al punto de datos Q3:
=F3+(1.5*F4)
Paso cuatro: Identificar los valores atípicos
Ahora que tenemos todos nuestros datos subyacentes configurados, es el momento de identificar nuestros puntos de datos atípicos: los que son más bajos que el valor del límite inferior o más altos que el valor del límite superior.
Utilizaremos la función OR para realizar esta prueba lógica y mostrar los valores que cumplen estos criterios introduciendo la siguiente fórmula en la celda C2:
=OR(B2<$F$5,B2>$F$6)
A continuación, copiaremos ese valor en nuestras celdas C3-C14. Un valor TRUE indica un valor atípico, y como puede ver, tenemos dos en nuestros datos.
Ignorando los valores atípicos al calcular la media
Usar la función QUARTILE nos permite calcular el IQR y trabajar con la definición más utilizada de un valor atípico. Sin embargo, cuando se calcula la media de un rango de valores y se ignoran los valores atípicos, existe una función más rápida y sencilla de utilizar. Esta técnica no identificará un valor atípico como antes, pero nos permitirá ser flexibles con lo que podríamos considerar nuestra porción atípica.
La función que necesitamos se llama TRIMMEAN, y puedes ver la sintaxis de la misma a continuación:
=TRIMMEAN(array, percent)
El array es el rango de valores que quieres promediar. El porcentaje es el porcentaje de puntos de datos a excluir de la parte superior e inferior del conjunto de datos (puede introducirlo como un porcentaje o un valor decimal).
Ingresamos la fórmula siguiente en la celda D3 de nuestro ejemplo para calcular el promedio y excluir el 20% de los valores atípicos.
=TRIMMEAN(B2:B14, 20%)
Aquí tienes dos funciones diferentes para manejar los valores atípicos. Tanto si quieres identificarlos para algunas necesidades de información como si quieres excluirlos de cálculos como los promedios, Excel tiene una función que se ajusta a tus necesidades.
Alan Murray ha trabajado como formador y consultor de Excel durante veinte años. La mayoría de los días, se le puede encontrar enseñando Excel en un aula o seminario. A Alan le encanta ayudar a la gente a mejorar su productividad y su vida laboral con Excel.