Cómo usar la función de valores atípicos en Excel

Cuando analiza datos, los valores atípicos pueden sesgar los resultados y hacerlos menos confiables. Por ejemplo, si uno o más de sus valores son sustancialmente más altos que la mayoría de los datos, hacen que la media sea más alta, lo que podría no reflejar los datos en su conjunto. Eliminar estos puntos de datos periféricos es, por lo tanto, un paso importante para realizar cálculos estadísticos confiables. Sin una función de valores atípicos dedicada, la forma más fácil de realizar una prueba de valores atípicos en Excel es utilizar la rango intercuartílico para identificar valores atípicos, pero también puede usar la función Trimmean para aproximar el mismo resultado.

El rango intercuartílico de datos es el rango cubierto por el "cuadro" en un diagrama de caja y bigotes, o más precisamente, el resultado de restar el valor del primer cuartil de los datos del del tercer cuartilla. Excel contiene una función incorporada para calcular cualquier cuartil de sus datos. Busque una celda de repuesto y escriba "= CUARTIL ([rango de datos], [número de cuartil])" y coloque el rango de celdas para sus datos donde dice "[rango de datos]" y el cuartil que desea donde dice "[cuartil número]."

Por ejemplo, si tiene datos en las celdas A2 a A101 y desea el valor para el primer cuartil, ingrese "= CUARTIL (A2: A101, 1)" para encontrar el valor. Para la primera parte del argumento, puede resaltar las celdas relevantes con el mouse, pero después de la coma, debe escribir el número del cuartil que desea. Para el tercer cuartil con los mismos datos, escribe "= CUARTIL (A2: A101, 3)" para obtener el resultado.

Usando otra celda vacía, reste el valor de la celda del primer cuartil del valor de la celda del tercer cuartil. Si el primer cuartil está en la celda C2 y el tercer cuartil está en la celda D2, escriba "= D2-C2" para obtener el resultado. Este es el rango intercuartílico.

Para encontrar valores atípicos, ahora puede usar el rango intercuartílico en la fórmula de valores atípicos, que establece que el límite superior de los datos es el valor de la tercer cuartil más 1,5 veces el rango intercuartil, y el límite inferior es el valor del primer cuartil menos 1,5 veces el intercuartil rango.

Si el valor del primer cuartil está en C2, el valor del tercer cuartil está en la celda D2 y el rango intercuartil es en la celda E2, escribiría "= C2- (1.5 * E2)" para encontrar el límite inferior y "= D2 + (1.5 * E2)" para encontrar el límite superior límite. En general, ingresa "= [primer cuartil] - (1.5 * [rango intercuartil])" para encontrar el límite inferior y "= [tercer cuartil] + (1.5 * [rango intercuartil])" para encontrar el límite superior.

Todo lo que esté por debajo del límite inferior o por encima del límite superior es un valor atípico.

Para finalizar la prueba de valores atípicos en Excel, use la función lógica "O" para identificar qué valores en su clase de datos son valores atípicos de una manera eficiente. Ingrese "= OR ([celda de datos]> [límite superior], [celda de datos] $ F $ 2, A2

Si el valor en A2 está por encima del límite superior o por debajo del límite inferior, muestra "VERDADERO", lo que indica que el valor es un valor atípico. Puede arrastrar esta fórmula hacia abajo haciendo clic en la esquina inferior derecha de la celda con la fórmula y arrastrándolo hacia abajo para que termine al lado de la celda de datos final para realizar el mismo cálculo en cada punto de datos.

También puede resaltar los datos e ir a "Formato condicional" en la sección "Estilos" de la pestaña "Inicio" si desea cambiar el formato de los valores atípicos. Elija "Nueva regla" y resalte la opción "Usar una fórmula para determinar qué celdas formatear". Escriba la misma fórmula que en el párrafo anterior y luego haga clic en la opción "Formato" para elegir el formato único para los valores atípicos.

La función "Trimmean" es una forma más sencilla de identificar valores atípicos. Escriba "= TRIMMEAN ([rango de datos], [proporción para recortar])" para usar la función, con el rango de celdas que contienen datos en lugar de "[rango de datos]" y un decimal porcentaje que desea recortar donde dice "[proporción para recortar]". Esto elimina los valores extremos en la parte superior e inferior y luego calcula la media en función de esos restante. Entonces, si recortara el 10 por ciento, eliminaría el 5 por ciento superior y el 5 por ciento inferior antes de calcular la media.

Si los datos van de A2 a A101 y desea recortar el 5 por ciento extremo de los valores, ingrese "= TRIMMEAN (A2: A101, 0.05)" para encontrar la media ajustada. En su lugar, podría recortar un 15 por ciento escribiendo "= TRIMMEAN (A2: A101, 0.15)".