Cómo resaltar valores atípicos estadísticos en Excel

Al realizar el análisis de datos, generalmente asume que sus valores se agrupan alrededor de algún punto de datos central (una mediana). Pero a veces algunos de los valores caen demasiado lejos del punto central. Estos valores se denominan valores atípicos (se encuentran fuera del rango esperado). Los valores atípicos pueden sesgar sus análisis estadísticos y llevarlo a conclusiones falsas o engañosas sobre sus datos.

Puede usar algunas fórmulas simples y formato condicional para resaltar los valores atípicos en sus datos.

El primer paso para identificar valores atípicos es señalar el centro estadístico del rango. Para hacer esta localización, comience por encontrar el primer y tercer cuartil. Un cuartil es una división estadística de un conjunto de datos en cuatro grupos iguales, y cada grupo representa el 25 por ciento de los datos. El 25 por ciento superior de una colección se considera el primer cuartil, mientras que el 25 por ciento inferior se considera el cuarto cuartil.

En Excel, puede obtener fácilmente valores de cuartiles utilizando la función CUARTIL. Esta función requiere dos argumentos: un rango de datos y el número de cuartil que desea.

En el ejemplo que se muestra, los valores en las celdas E3 y E4 son el primer y tercer cuartil para los datos en el rango B3: B20.

Tomando estos dos cuartiles, puede calcular el 50 por ciento estadístico del conjunto de datos restando el tercer cuartil del primer cuartil. Este 50 por ciento estadístico se llama rango intercuartílico (IQR). La figura 9-18 muestra el IQR en la celda E5.

Ahora la pregunta es, ¿qué tan lejos del 50 por ciento medio puede situarse un valor y aún ser considerado un valor "razonable"? Los estadísticos generalmente están de acuerdo en que IQR * 1.5 se puede utilizar para establecer un límite superior e inferior razonable:

La valla inferior es igual al primer cuartil - IQR * 1.5.

La valla superior es igual al tercer cuartil + IQR * 1.5.

Como puede ver, las celdas E7 y E8 calculan las cercas superior e inferior finales. Cualquier valor mayor que la valla superior o menor que la valla inferior se considera un valor atípico.

En este punto, la regla de formato condicional es fácil de implementar.

Para crear esta regla de formato básica, siga estos pasos:

Seleccione las celdas de datos en su rango de destino (celdas B3: B20 en este ejemplo), haga clic en la pestaña Inicio de la cinta de Excel y luego seleccione Formato condicional → Nueva regla.

Esto abre el cuadro de diálogo Nueva regla de formato.

En el cuadro de lista en la parte superior del cuadro de diálogo, haga clic en la opción Usar una fórmula para determinar qué celdas formatear.

Esta selección evalúa los valores según una fórmula que especifique. Si un valor particular se evalúa como VERDADERO, el formato condicional se aplica a esa celda.

En el cuadro de entrada de la fórmula, ingrese la fórmula que se muestra aquí.

Tenga en cuenta que usa la función OR para comparar el valor en su celda objetivo (B3) con las vallas superior e inferior que se encuentran en las celdas $ E $ 7 y $ E $ 8, respectivamente. Si la celda de destino es mayor que el límite superior o menor que el límite inferior, se considera un valor atípico y, por lo tanto, se evaluará como VERDADERO, lo que activará el formato condicional.

= O (B3 <$ e $ 8, b3> $ E $ 7)

Tenga en cuenta que en la fórmula, excluye los símbolos de dólares de referencia absolutos ($) para la celda de destino (B3). Si hace clic en la celda B3 en lugar de escribir la referencia de celda, Excel automáticamente hace que su referencia de celda sea absoluta. Es importante que no incluya los símbolos de dólar de referencia absoluta en su celda de destino porque necesita Excel para aplicar esta regla de formato en función del valor propio de cada celda.

Haga clic en el botón Formato.

Esto abre el cuadro de diálogo Formato de celdas, donde tiene un conjunto completo de opciones para formatear la fuente, el borde y el relleno de la celda de destino. Una vez que haya terminado de elegir las opciones de formato, haga clic en el botón Aceptar para confirmar los cambios y volver al cuadro de diálogo Nueva regla de formato.

De vuelta en el cuadro de diálogo Nueva regla de formato, haga clic en el botón Aceptar para confirmar su regla de formato.

Si necesita editar su regla de formato condicional, simplemente coloque el cursor en cualquiera de las celdas de datos dentro de su rango formateado y luego vaya a la pestaña Inicio y seleccione Formato condicional → Administrar reglas. Esto abre el cuadro de diálogo Administrador de reglas de formato condicional. Haga clic en la regla que desea editar y luego haga clic en el botón Editar regla.