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 revelar información valiosa, como un error en la entrada de datos, una transacción fraudulenta o un comportamiento anómalo en un proceso.

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 es menos sensible a los propios valores extremos que otros métodos basados en la media y la desviación estándar. La fórmula que presentamos a continuación implementa de manera elegante y eficiente este método directamente en una celda de Excel, permitiéndote clasificar cada punto de tu conjunto de datos como «Normal» o «Atípico».

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 a primera vista, pero se compone de varias funciones lógicas y estadísticas de Excel que trabajan juntas. A continuación, la desglosamos para entender su funcionamiento:

• CUARTIL.EXCL(rango, 1) Esta parte calcula el primer cuartil (Q1) o el percentil 25 del conjunto de datos. Representa el valor por debajo del cual se encuentra el 25% de los datos.
• CUARTIL.EXCL(rango, 3) De forma similar, calcula el tercer cuartil (Q3) o el percentil 75. El 75% de los datos se encuentra por debajo de este valor.
• (CUARTIL.EXCL(rango,3) – CUARTIL.EXCL(rango,1)) Esta es la resta entre Q3 y Q1, que da como resultado el Rango Intercuartílico (IQR). El IQR contiene el 50% central de los datos.
• … – 1.5 * IQR Se calcula el límite inferior. Cualquier valor por debajo de Q1 – 1.5 * IQR se considera un valor atípico.
• … + 1.5 * IQR Se calcula el límite superior. Cualquier valor por encima de Q3 + 1.5 * IQR también se considera 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 sea mayor que el límite superior. Si alguna es cierta, devuelve VERDADERO.
• SI(…) La función SI actúa sobre el resultado de la función O. Si es VERDADERO (el valor es atípico), la fórmula devuelve el texto «Atípico». De lo contrario, devuelve «Normal».
• valor Es la referencia a la celda individual que se está evaluando.
• rango Es el rango completo de datos que se utiliza como referencia para calcular los cuartiles. Es fundamental que este rango se mantenga fijo con referencias absolutas (ej. $A$2:$A$20) al arrastrar la fórmula.

Ejemplos

Ejemplo 1: Identificar puntuaciones atípicas en los resultados de un examen

Imaginemos que tenemos una tabla con las puntuaciones de un grupo de estudiantes y queremos identificar aquellas que son inusualmente altas o bajas en comparación con el resto del grupo. Usaremos la fórmula para añadir una columna de clasificación.

Para obtener la clasificación en la celda C2 y luego arrastrarla hacia abajo, usaríamos la siguiente fórmula:

=SI(O(B2<(CUARTIL.EXCL($B$2:$B$10,1)-1.5*(CUARTIL.EXCL($B$2:$B$10,3)-CUARTIL.EXCL($B$2:$B$10,1)));B2>(CUARTIL.EXCL($B$2:$B$10,3)+1.5*(CUARTIL.EXCL($B$2:$B$10,3)-CUARTIL.EXCL($B$2:$B$10,1))));"Atípico";"Normal")

En este caso, la fórmula identifica la puntuación de Carla (21) como un valor atípico bajo y la de Sofía (150), que podría ser un error de tipeo, como un valor atípico alto. Nótese el uso de $B$2:$B$10 para asegurar que el rango de cálculo no cambie al copiar la fórmula.

Ejemplo 2: Resaltar automáticamente valores atípicos con Formato Condicional

En lugar de usar una columna auxiliar, podemos aplicar un formato visual directamente sobre los datos. La lógica de la fórmula se puede adaptar para crear una regla de formato condicional que resalte las celdas con valores atípicos.

1. Selecciona el rango de datos que quieres evaluar (por ejemplo, B2:B10).
2. Ve a la pestaña Inicio > Formato condicional > Nueva regla.
3. Elige la opción «Utilice una fórmula que determine las celdas para aplicar formato».
4. En el cuadro de fórmula, introduce la parte lógica de nuestra fórmula original, asegurándote de que la referencia a la celda sea relativa y la del rango sea absoluta.

=O(B2<(CUARTIL.EXCL($B$2:$B$10,1)-1.5*(CUARTIL.EXCL($B$2:$B$10,3)-CUARTIL.EXCL($B$2:$B$10,1)));B2>(CUARTIL.EXCL($B$2:$B$10,3)+1.5*(CUARTIL.EXCL($B$2:$B$10,3)-CUARTIL.EXCL($B$2:$B$10,1))))

Haz clic en el botón «Formato…», elige un color de relleno o de fuente llamativo y acepta. Excel resaltará automáticamente las celdas que cumplan la condición, haciendo que los valores atípicos sean visibles al instante.

Aplicaciones Prácticas

• 1Análisis Financiero: Detectar transacciones inusuales en extractos bancarios o identificar acciones con fluctuaciones de precio anómalas.
• 2Control de Calidad: Identificar productos con mediciones fuera de los umbrales de tolerancia en un proceso de fabricación.
• 3Ciencia de Datos: Realizar una limpieza inicial de un conjunto de datos, marcando posibles errores de entrada o mediciones experimentales erróneas antes de un análisis más profundo.
• 4Recursos Humanos: Analizar salarios o días de ausencia para detectar valores extremos que puedan indicar inequidades o problemas específicos.

Observaciones

Sensibilidad del multiplicador (1.5): El valor 1.5 es un estándar ampliamente aceptado, pero no es una regla inamovible. Si necesitas una detección más estricta (marcando más valores como atípicos), puedes reducir este número (por ejemplo, a 1.0). Si, por el contrario, solo quieres detectar los valores más extremos, puedes aumentarlo (por ejemplo, a 2.0 o 3.0).

CUARTIL.EXCL vs. CUARTIL.INC: Excel ofrece dos funciones para calcular cuartiles. CUARTIL.EXCL se basa en un método que excluye la mediana al calcular Q1 y Q3 en subconjuntos de datos, lo que la hace estadísticamente preferible para algunos modelos de análisis, incluyendo la definición clásica de los diagramas de caja y bigotes (box plot). CUARTIL.INC incluye todos los datos. Para la mayoría de los casos, los resultados serán muy similares.

Errores comunes

• #¡NUM! Este error puede aparecer si el rango de datos proporcionado a CUARTIL.EXCL está vacío o si el conjunto de datos es demasiado pequeño. CUARTIL.EXCL requiere un número suficiente de puntos para poder dividir el conjunto de datos de forma exclusiva.
• Error de lógica Un error muy frecuente es olvidar usar referencias absolutas (con el símbolo $) para el rango. Si usas referencias relativas (ej. B2:B10) y arrastras la fórmula hacia abajo, el rango de cálculo se desplazará, produciendo resultados incorrectos para las filas inferiores.

Alternativas

• Uso de celdas auxiliares: Si la fórmula anidada resulta difícil de leer o depurar, puedes calcular sus componentes en celdas separadas.

  1. En una celda (ej. F1), calcula Q1: =CUARTIL.EXCL(B2:B10, 1)
  2. En otra (F2), calcula Q3: =CUARTIL.EXCL(B2:B10, 3)
  3. En una tercera (F3), el IQR: =F2-F1
  4. La fórmula principal se simplifica enormemente:

  =SI(O(B2<($F$1-1.5*$F$3), B2>($F$2+1.5*$F$3)), "Atípico", "Normal")

• Método de Puntuación Z (Z-Score): Otra técnica estadística común, ideal para datos con una distribución cercana a la normal. Calcula cuántas desviaciones estándar se aleja un punto de la media. Un Z-score mayor a 3 o menor a -3 suele considerarse atípico.

  =SI(ABS((B2-PROMEDIO($B$2:$B$10))/DESVEST.P($B$2:$B$10))>3, "Atípico", "Normal")