Detección Avanzada de Outliers con Rango Intercuartílico en Excel

En el análisis de datos, un «outlier» o valor atípico es una observación que se desvía significativamente de otras observaciones en el mismo conjunto de datos. Identificar estos valores es crucial para la limpieza de datos, el análisis estadístico y la creación de modelos precisos. La fórmula que se presenta a continuación utiliza el método del Rango Intercuartílico (IQR), una técnica estadística robusta y ampliamente aceptada para la detección de outliers.

Este método es menos sensible a los propios outliers que otros métodos basados en la media y la desviación estándar, lo que lo convierte en una herramienta ideal para una detección precisa. La fórmula combina varias funciones de Excel para determinar si un valor específico se encuentra fuera de los «límites» aceptables definidos por los cuartiles de los datos.

Sintaxis

=SI(O(valor_a_evaluar < (CUARTIL.INC(rango_de_datos,1) - 1.5*(CUARTIL.INC(rango_de_datos,3)-CUARTIL.INC(rango_de_datos,1))), valor_a_evaluar > (CUARTIL.INC(rango_de_datos,3) + 1.5*(CUARTIL.INC(rango_de_datos,3)-CUARTIL.INC(rango_de_datos,1)))), "Outlier", "No Outlier")

Esta fórmula puede parecer compleja, pero se descompone en partes lógicas que trabajan juntas. A continuación, se detalla cada componente principal:

• valor_a_evaluar Es la celda individual que se desea comprobar si es un valor atípico.
• rango_de_datos Es el rango completo de celdas que contiene el conjunto de datos. Al copiar la fórmula, este rango debe permanecer constante, por lo que es recomendable usar referencias absolutas (ej. $A$2:$A$100).
• CUARTIL.INC(rango_de_datos,3) – CUARTIL.INC(rango_de_datos,1) Este fragmento calcula el Rango Intercuartílico (IQR). El IQR es la diferencia entre el tercer cuartil (Q3, el percentil 75) y el primer cuartil (Q1, el percentil 25). Representa el 50% central de los datos.
• Límite inferior La fórmula Q1 - 1.5 * IQR calcula la barrera inferior. Cualquier valor por debajo de este umbral se considera un outlier.
• Límite superior La fórmula Q3 + 1.5 * IQR calcula la barrera superior. Cualquier valor por encima de este umbral se considera un outlier.
• O(…) La función O comprueba si el valor_a_evaluar cumple al menos una de las dos condiciones: ser menor que el límite inferior O ser mayor que el límite superior. Si alguna es cierta, devuelve VERDADERO.
• SI(…) La función SI envuelve toda la lógica. Si la función O devuelve VERDADERO, la fórmula escribe «Outlier». De lo contrario, escribe «No Outlier».

Ejemplos

Ejemplo 1: Identificar outliers en un listado de ventas

Imaginemos que tenemos un registro de las ventas diarias de un producto durante un mes y queremos identificar días con ventas inusualmente altas o bajas. Nuestra lista de ventas está en el rango B2:B32.

Para analizar el primer dato (celda B2), escribimos la siguiente fórmula en la celda C2. Es fundamental usar referencias absolutas ($B$2:$B$32) para el rango de datos, de modo que no cambie al arrastrar la fórmula hacia abajo.

=SI(O(B2<(CUARTIL.INC($B$2:$B$32,1)-1.5*(CUARTIL.INC($B$2:$B$32,3)-CUARTIL.INC($B$2:$B$32,1))), B2>(CUARTIL.INC($B$2:$B$32,3)+1.5*(CUARTIL.INC($B$2:$B$32,3)-CUARTIL.INC($B$2:$B$32,1)))), "Outlier", "No Outlier")

Al arrastrar esta fórmula desde C2 hasta C32, Excel evaluará cada día de venta individualmente contra las estadísticas del conjunto completo, etiquetando correctamente los valores atípicos como la venta de 250€ (inusualmente alta) y la de 15€ (inusualmente baja).

Aplicaciones Prácticas

• 1Limpieza de Datos: Antes de realizar análisis estadísticos o alimentar modelos de Machine Learning, es vital identificar y tratar los outliers que podrían sesgar los resultados.
• 2Análisis Financiero: Detectar transacciones fraudulentas, movimientos anómalos en el precio de acciones o gastos inusuales en un presupuesto.
• 3Control de Calidad: Identificar productos con defectos en una línea de producción midiendo características como el peso, el tamaño o la resistencia.
• 4Monitorización de Sistemas: En el ámbito de la tecnología, sirve para detectar picos anómalos de tráfico en una red, tiempos de respuesta de un servidor fuera de lo común o consumo excesivo de CPU.

Observaciones

Referencias absolutas: Es crucial utilizar referencias de celda absolutas (ej. $B$2:$B$32) para el rango_de_datos. Si no se utiliza el símbolo $, el rango se desplazará al copiar y pegar o arrastrar la fórmula, lo que llevará a cálculos incorrectos en las filas posteriores.

Sensibilidad del multiplicador: El valor 1.5 es un estándar comúnmente aceptado para identificar outliers «leves». Si se necesita una detección más estricta que solo identifique valores «extremos», se puede aumentar este multiplicador a 3.

CUARTIL.INC vs. CUARTIL.EXC: Esta fórmula utiliza la función CUARTIL.INC, que se basa en un método inclusivo para calcular los cuartiles (percentiles 0 al 100). Excel también ofrece CUARTIL.EXC, que los calcula de forma exclusiva (percentiles 0 y 100 no incluidos). La elección depende del estándar estadístico que se prefiera seguir, aunque para la mayoría de los casos, CUARTIL.INC es adecuado.

Errores comunes

• #¡NUM! Este error ocurre si el rango_de_datos proporcionado a la función CUARTIL.INC está vacío. Asegúrese de que el rango contiene al menos un valor numérico.
• #¡VALOR! Se produce si el valor_a_evaluar no es numérico (por ejemplo, es texto o un valor de error). Las funciones de cuartil ignoran el texto dentro del rango_de_datos, pero la comparación directa (< o >) con un valor no numérico generará este error.

Alternativas

• Uso de Celdas Auxiliares: Para mejorar la legibilidad y facilitar la depuración, especialmente en hojas de cálculo complejas, se pueden calcular los componentes de la fórmula en celdas separadas.

  Celda E1 (Q1): =CUARTIL.INC($B$2:$B$32; 1)
  Celda E2 (Q3): =CUARTIL.INC($B$2:$B$32; 3)
  Celda E3 (IQR): =E2 - E1
  Celda E4 (Límite Inferior): =E1 - 1.5 * E3
  Celda E5 (Límite Superior): =E2 + 1.5 * E3

  Luego, la fórmula principal se simplifica enormemente:

  =SI(O(B2<$E$4; B2>$E$5); "Outlier"; "No Outlier")