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

Al realizar el análisis de datos, por lo general asume que sus valores se agrupan alrededor de un punto central de datos (una mediana). Pero a veces algunos de los valores caen demasiado lejos del punto central. Estos valores se denominan valores atípicos (que se encuentran fuera del rango esperado). Outliers pueden sesgar los análisis estadísticos, que le conduce a conclusiones falsas o engañosas sobre sus datos.

Puede utilizar algunas fórmulas sencillas y formato condicional para resaltar los valores atípicos en los datos.

El primer paso en la identificación de los valores atípicos es establecer claramente el centro estadístico de la gama. Para ello localización exacta, se inicia mediante la búsqueda de la 1ª y 3ª cuartiles. LA cuartil es una división estadística de un conjunto de datos en cuatro grupos iguales, con cada grupo que constituye 25 por ciento de los datos. La parte superior 25 por ciento de una colección se considera que es la primera cuartil, mientras que la parte inferior 25 por ciento es considerado el cuarto cuartil.

En Excel, usted puede conseguir fácilmente los valores cuartil mediante la función CUARTIL. Esta función requiere dos argumentos: una serie de datos y el número cuartil desea.

En el ejemplo mostrado, los valores de las celdas E3 y E4 son la primera y 3º cuartiles para los datos de rango B3: B20.

Tomando estos dos cuartiles, se puede calcular la estadística del 50 por ciento del conjunto de datos restando el tercero cuartil de la primera cuartil. Esta estadística del 50 por ciento se llama el rango intercuartílico (RIC). La figura 9-18 muestra la RIC en la celda E5.

Ahora la pregunta es, ¿cómo lejos de la media del 50 por ciento puede un valor sentarse y todavía ser considerado un " razonables " valor? Estadísticos de acuerdo en que IQR * 1.5 puede utilizarse para establecer un cerco superior e inferior razonable:

La valla inferior es igual a la primera cuartil - RIC * 1.5.La valla superior es igual a la tercera cuartil + RIC * 1.5.

Como se puede ver, las células E7 y E8 calculan las vallas superiores e inferiores finales. Cualquier valor mayor que la valla superior o inferior a la valla inferior se considera un caso atípico.

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

Para construir esta regla de formato básico, siga estos pasos:

1. Seleccione las celdas de datos en su rango objetivo (células B3: B20 en este ejemplo), haga clic en la ficha Inicio de la cinta de opciones de Excel y, a continuación, seleccione Regla-Formateo Nueva condicional.

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

   

2. En el cuadro de lista en la parte superior del cuadro de diálogo, haga clic en el uso de una fórmula para determinar qué células a la opción Formato.

   Esta selección evalúa valores basados ​​en una fórmula que especifique. Si un determinado valor se evalúa como TRUE, el formato condicional se aplica a esa celda.

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

   Tenga en cuenta que se utiliza la función OR para comparar el valor en su célula diana (B3) tanto a las vallas superiores e inferiores que se encuentran en las células $ E $ 7 y $ E $ 8, respectivamente. Si la célula diana es mayor que la valla superior o inferior a la valla inferior, se considera una de las demás y por lo tanto evaluará a TRUE, lo que provocó el formato condicional.

   = O (B3lt; $ E $ 8, B3> $ E $ 7)

   Tenga en cuenta que en la fórmula, se excluye a los símbolos del dólar de referencia absolutos ($) para la célula diana (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 absoluta. Es importante que usted no incluye los símbolos absolutos en dólares referencia en su célula diana porque necesita Excel para aplicar esta regla de formato basado en propio valor de cada celda.

4. Haga clic en el botón Formato.

   Esto abre el cuadro de diálogo Formato de celdas, donde se tiene un conjunto completo de opciones para dar formato a la fuente, en la frontera, y llenar para su célula diana. Después de haber completado la elección de las opciones de formato, haga clic en el botón Aceptar para confirmar los cambios y regresar al cuadro de diálogo Nueva regla de formato.

5. De vuelta en el cuadro de diálogo Nueva regla de formato, haga clic en el botón Aceptar para confirmar la 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 con formato y luego ir a la pestaña Inicio y seleccione Reglas de formato condicionales-Gestión. Esto abre el cuadro de diálogo Administrador de reglas de formato condicional. Haga clic en la regla que desea editar continuación, haga clic en el botón Editar regla.