Detecta Valores Atípicos en tus Datos con la Fórmula IQR en Excel

En el análisis de datos, un valor atípico (o outlier) es una observación que se distancia significativamente del resto de los datos. Identificar estos valores es crucial, ya que pueden distorsionar los resultados de análisis estadísticos, como la media, o pueden señalar errores en la recolección de datos, fraudes o eventos anómalos que merecen una investigación más profunda.

Una de las técnicas más robustas y comúnmente utilizadas para detectar valores atípicos es el método del Rango Intercuartílico (IQR). Este método define un «rango normal» para los datos y considera cualquier valor fuera de este rango como atípico. La fórmula que presentamos aquí automatiza este cálculo directamente en tus hojas de Excel, permitiéndote clasificar cada punto de datos como «Normal» o «Atípico» de manera eficiente.

Sintaxis

=SI(O(valor < (CUARTIL.EXCL(rango,1) - 1.5 * (CUARTIL.EXCL(rango,3) - CUARTIL.EXCL(rango,1))), valor > (CUARTIL.EXCL(rango,3) + 1.5 * (CUARTIL.EXCL(rango,3) - CUARTIL.EXCL(rango,1)))), "Atípico", "Normal")

Esta fórmula puede parecer compleja, pero se basa en una combinación lógica de varias funciones para aplicar el método IQR. A continuación, la desglosamos en sus componentes principales:

• CUARTIL.EXCL(rango,3) – CUARTIL.EXCL(rango,1) Esta parte 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 y es una medida de dispersión menos sensible a valores extremos que el rango total.
• Límite Inferior Se calcula como Q1 - 1.5 * IQR. Cualquier valor en tus datos que sea menor que este límite se considera un valor atípico.
• Límite Superior Se calcula como Q3 + 1.5 * IQR. Cualquier valor que supere este límite también se clasifica como atípico.
• O(…) La función O comprueba si se cumple al menos una de las dos condiciones: que el valor sea menor que el límite inferior O que el valor sea mayor que el límite superior. Si alguna de estas condiciones es verdadera, la función O devuelve VERDADERO.
• SI(…) La función principal SI evalúa el resultado de la función O. Si es VERDADERO (el valor está fuera de los límites), devuelve el texto «Atípico». Si es FALSO (el valor está dentro de los límites), devuelve «Normal».
• valor Es la celda individual que quieres evaluar.
• rango Es el rango completo de datos que se utiliza como referencia para calcular los cuartiles (por ejemplo, $A$2:$A$20). Es importante usar referencias absolutas (con el símbolo $) para que el rango no cambie al arrastrar la fórmula.

Ejemplos

Ejemplo 1 Supongamos que tenemos un registro de las ventas diarias de un producto durante dos semanas y queremos identificar días con ventas inusualmente altas o bajas. Los datos se encuentran en la columna A.

Para clasificar cada valor, introducimos la siguiente fórmula en la celda B2 y la arrastramos hacia abajo hasta la celda B13.

=SI(O(A2<(CUARTIL.EXCL($A$2:$A$13;1)-1,5*(CUARTIL.EXCL($A$2:$A$13;3)-CUARTIL.EXCL($A$2:$A$13;1))); A2>(CUARTIL.EXCL($A$2:$A$13;3)+1,5*(CUARTIL.EXCL($A$2:$A$13;3)-CUARTIL.EXCL($A$2:$A$13;1)))); "Atípico"; "Normal")

En este caso, la fórmula identificará correctamente las ventas de 250 y 55 como valores atípicos, ya que se desvían significativamente del resto del conjunto de datos.

Aplicaciones Prácticas

• 1Detección de Fraude: En contabilidad, se puede utilizar para analizar un listado de transacciones y señalar aquellas con importes inusualmente altos o bajos que podrían indicar una operación fraudulenta o un error.
• 2Control de Calidad: En procesos de fabricación, permite identificar productos cuyas medidas (peso, longitud, temperatura) caen fuera de los límites de tolerancia establecidos, ayudando a mantener la calidad y consistencia del producto.
• 3Análisis de Rendimiento: En RRHH o en gestión de equipos, sirve para analizar métricas de rendimiento (KPIs) e identificar a los empleados con un rendimiento excepcionalmente alto o bajo, lo que puede dar pie a programas de reconocimiento o de apoyo.
• 4Limpieza de Datos: Antes de realizar análisis estadísticos complejos o entrenar modelos de machine learning, es fundamental limpiar los datos. Esta fórmula ayuda a marcar posibles errores de entrada de datos o mediciones anómalas que podrían sesgar los resultados.

Observaciones

CUARTIL.EXCL vs. CUARTIL.INC: Excel ofrece dos funciones para calcular cuartiles. CUARTIL.EXCL calcula los cuartiles basándose en un rango de percentiles que excluye 0 y 1. Por otro lado, CUARTIL.INC los incluye. Para la detección de valores atípicos, el método estándar de Tukey (que usa el factor 1.5 * IQR) se alinea mejor con la definición de CUARTIL.EXCL, por lo que es la función recomendada para este propósito.

Factor de Sensibilidad: El multiplicador 1.5 es un estándar ampliamente aceptado para identificar valores atípicos. Sin embargo, puede ser ajustado. Un factor mayor, como 3.0, se utiliza a menudo para detectar valores atípicos «extremos», haciendo la prueba menos sensible. Puedes modificar este número en la fórmula según las necesidades específicas de tu análisis.

Errores comunes

• #¡NUM! Este error puede aparecer si el rango proporcionado a la función CUARTIL.EXCL está vacío o si contiene menos de 4 valores, ya que la función no puede calcular los cuartiles exclusivos en un conjunto de datos tan pequeño.
• #¡VALOR! Ocurre si el argumento valor que se está evaluando no es un dato numérico. La función CUARTIL.EXCL ignora las celdas de texto dentro del rango, pero la comparación directa (valor < ...) fallará si valor no es un número.

Alternativas

• Uso del Formato Condicional: En lugar de escribir «Atípico» en una columna adyacente, puedes usar la misma lógica de la fórmula para resaltar visualmente las celdas. Ve a Formato Condicional > Nueva Regla > Utilice una fórmula... e introduce la parte lógica de la fórmula para aplicar un formato (por ejemplo, un fondo rojo) a las celdas que cumplan la condición.

  =O(A2<(CUARTIL.EXCL($A$2:$A$13;1)-1,5*...); A2>(CUARTIL.EXCL($A$2:$A$13;3)+1,5*...))

• Fórmula simplificada con LET (Excel 365 y posteriores): Si utilizas una versión moderna de Excel, la función LET puede hacer la fórmula mucho más legible y eficiente, ya que evita calcular los cuartiles y el IQR repetidamente.

  =LET(
      rango_datos; $A$2:$A$13;
      Q1; CUARTIL.EXCL(rango_datos; 1);
      Q3; CUARTIL.EXCL(rango_datos; 3);
      IQR; Q3 - Q1;
      limite_inf; Q1 - 1,5 * IQR;
      limite_sup; Q3 + 1,5 * IQR;
      SI(O(A2 < limite_inf; A2 > limite_sup); "Atípico"; "Normal")
  )

  Esta versión es más fácil de leer, mantener y optimiza el rendimiento en hojas de cálculo grandes.