Cómo (y por qué) utilizar la función de valores atípicos en Excel
Un valor atípico es un valor significativamente más alto o más bajo 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, el promedio de un conjunto de datos puede reflejar realmente sus valores. Excel proporciona algunas funciones útiles para ayudar a administrar sus 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 asignados 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 mayor de datos, 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 en este artículo..
Cómo encontrar valores atípicos en sus datos
Para encontrar los valores atípicos en un conjunto de datos, utilizamos los siguientes pasos:
- Calcule el 1er y 3er cuartil (hablaremos de lo que son en un momento).
- Evalúe el rango intercuartil (también explicaremos esto un poco más abajo).
- Devuelva los límites superior e inferior de nuestro rango de datos.
- Utilice estos límites para identificar los puntos de datos periféricos.
El rango de celdas a la derecha del conjunto de datos que se ve en la imagen de abajo se utilizará para almacenar estos valores.
Empecemos.
Paso uno: calcula los cuartiles
Si divide sus datos en cuartos, cada uno de esos conjuntos se llama un cuartil. El 25% más bajo de los números en el rango conforman el primer cuartil, el siguiente 25% el segundo cuartil, y así sucesivamente. Primero tomamos este paso porque la definición más utilizada de un valor atípico es un punto de datos que tiene más de 1.5 rangos intercuartílicos (RIC) 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 CUARTIDA para calcular los cuartiles. Requiere dos datos: la matriz y el cuarto.
= CUARTIDO (array, cuarto de galón)
los formación es el rango de valores que está evaluando. Y el cuarto de galón es un número que representa el cuartil que desea devolver (por ejemplo, 1 para el 1S t cuartil, 2 para el segundo cuartil, y así sucesivamente).
Nota: 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 con varias versiones de Excel.
Volvamos a nuestra tabla de ejemplos..
Para calcular el 1S t Cuartil podemos usar la siguiente fórmula en la celda F2..
= CUARTIL (B2: B14,1)
A medida que ingresa la fórmula, Excel proporciona una lista de opciones para el argumento de cuarto de galón..
Para calcular el 3rd cuartil, podemos ingresar una fórmula como la anterior en la celda F3, pero usando tres en lugar de uno.
= CUARTIL (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 en 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 1S t cuartil a partir del 3rd cuartilla:
= F3-F2
Ahora podemos ver nuestro rango intercuartil desplegado..
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 menor o mayor que estos valores límite son los valores atípicos.
Calcularemos el límite del límite inferior en la celda F5 multiplicando el valor de 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 multiplicación se calculará antes que la parte de 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 nuevamente, pero esta vez añadir al punto de datos Q3:
= F3 + (1.5 * F4)
Paso cuatro: identificar los valores atípicos
Ahora que hemos configurado todos nuestros datos subyacentes, es hora de identificar nuestros puntos de datos externos, aquellos que son más bajos que el valor de límite inferior o más altos que el valor de límite superior.
Utilizaremos la función OR para realizar esta prueba lógica y mostrar los valores que cumplen estos criterios al ingresar la siguiente fórmula en la celda C2:
= O (B2 $ F $ 6)
Luego copiaremos ese valor en nuestras celdas C3-C14. Un valor VERDADERO indica un valor atípico, y como puede ver, tenemos dos en nuestros datos.
Ignorar los valores atípicos al calcular la media
Usando la función CUARTIDO, calculemos el IQR y trabajemos con la definición más extendida de un valor atípico. Sin embargo, al calcular el promedio para un rango de valores e ignorar 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 nuestra porción de valor atípico.
La función que necesitamos se llama TRIMMEAN, y puede ver la sintaxis para ella a continuación:
= TRIMMEAN (matriz, porcentaje)
los formación es el rango de valores que desea promediar. los por ciento es el porcentaje de puntos de datos que se excluyen de la parte superior e inferior del conjunto de datos (puede ingresarlo como un porcentaje o un valor decimal).
Ingresamos la siguiente fórmula en la celda D3 en nuestro ejemplo para calcular el promedio y excluir el 20% de los valores atípicos.
= TRIMMEAN (B2: B14, 20%)
Ahí tienes dos funciones diferentes para manejar los valores atípicos. Ya sea que desee identificarlos para algunas necesidades de informes o excluirlos de cálculos como promedios, Excel tiene una función que se adapta a sus necesidades.
