Cómo (y por qué) usar la función de valores atípicos en Excel

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. Al utilizar Excel para analizar datos, los valores atípicos pueden sesgar los resultados. Por ejemplo, el promedio medio de un conjunto de datos podría reflejar realmente sus valores. Excel proporciona algunas funciones útiles para ayudarlo a administrar sus valores atípicos, así que echemos un vistazo.

Un ejemplo rápido

En la siguiente imagen, 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 sus datos

Para encontrar los valores atípicos en un conjunto de datos, utilizamos los siguientes pasos:

  1. Calcule el primer y tercer cuartiles (hablaremos un poco sobre cuáles son).
  2. Evalúe el rango intercuartílico (también lo explicaremos un poco más adelante).
  3. Devuelve los límites superior e inferior de nuestro rango de datos.
  4. 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 a continuación se utilizará para almacenar estos valores.

Empecemos.

Paso uno: calcular los cuartiles

Si divide sus datos en cuartos, cada uno de esos conjuntos se llama cuartil. El 25% más bajo de números en el rango constituye 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 rangos intercuartiles (IQR) por debajo del primer cuartil y 1,5 rangos intercuartiles por encima del tercer cuartil. Para determinar esos valores, primero tenemos que averiguar cuáles son los cuartiles.

Excel proporciona una función CUARTIL para calcular los cuartiles. Requiere dos piezas de información: la matriz y el cuarto.

= CUARTIL (matriz, cuarto)

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, y así sucesivamente).

Nota: En Excel 2010, Microsoft lanzó las funciones QUARTILE.INC y QUARTILE.EXC como mejoras a la función CUARTIL. QUARTILE es más compatible con versiones anteriores cuando se trabaja en varias versiones de Excel.

Regresemos a nuestra tabla de ejemplo.

Para calcular el primer 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 un cuarto.

Para calcular el 3 er cuartil, podemos introducir una fórmula como la anterior en la celda F3, pero utilizando un 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 1 er cuartil desde el 3 er 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 usar. 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 corchetes 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, multiplicaremos el IQR por 1.5 nuevamente, pero esta vez lo agregaremos 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 hora de identificar nuestros puntos de datos periféricos, los que son más bajos que el valor del límite inferior o más altos que el valor del límite superior.

Usaremos la función OR  para realizar esta prueba lógica y mostraremos los valores que cumplen con estos criterios ingresando la siguiente fórmula en la celda C2:

= O (B2 <$ F $ 5, 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 el promedio medio

El uso de la función CUARTIL nos permite calcular el IQR y trabajar con la definición más utilizada de un valor atípico. Sin embargo, al calcular el promedio medio 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 nuestra porción atípica.

La función que necesitamos se llama TRIMMEAN, y puede ver su sintaxis a continuación:

= TRIMMEAN (matriz, porcentaje)

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 un valor decimal).

Ingresamos la fórmula a continuación en la celda D3 en nuestro ejemplo para calcular el promedio 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 algunas necesidades de informes o excluirlos de cálculos como promedios, Excel tiene una función que se adapta a sus necesidades.

responroot

Entradas recientes

Steamlytics: La nueva herramienta imprescindible para los gamers de Steam

  El mundo del gaming ha experimentado un crecimiento exponencial en los últimos años. La…

2 años hace

Cuándo reemplazar su antigua unidad flash USB

Stokkete/Shutterstock.com ¿Sigue utilizando una unidad flash USB obsoleta para almacenar sus archivos? ¿Por qué no…

3 años hace

¿Qué es una pantalla Nano IPS?

LG Los fabricantes siempre intentan mejorar el rendimiento de imagen de los monitores. Como resultado,…

3 años hace

10 formas de jugar con un presupuesto bajo (o nulo)

Patty Chan/Shutterstock.com Si cree en lo que ve en las redes sociales, puede pensar en…

3 años hace

¿Demasiadas suscripciones? Aquí está cómo empezar a cortarlos

Seksan.TH/Shutterstock.com Los servicios de suscripción como Netflix, Game Pass y Spotify nos permiten consumir tantas…

3 años hace

Cómo configurar la puerta de enlace predeterminada en Linux

fatmawati achmad zaenuri/Shutterstock El acceso a Internet, oa cualquier otra red, se rige por la…

3 años hace