¿Cómo encontrar valores atípicos en Excel?

Los valores atípicos, como sugiere el nombre, son algo que no se encuentra en el rango requerido/dado. Los valores atípicos en las estadísticas deben eliminarse porque afectan la decisión que se debe tomar después de realizar los cálculos necesarios. Los valores atípicos generalmente toman una decisión sesgada, es decir, mueven la decisión en una dirección positiva o negativa. A veces es fácil encontrar un valor atípico observando los datos, pero es difícil encontrar un valor atípico cuando los datos son grandes. Veremos esto con la ayuda de un ejemplo, dado un conjunto de datos y necesita realizar el promedio del conjunto de datos 1, 89, 57, 100, 150, 139, 49, 87, 200, 250. Entonces, el promedio de el conjunto de datos dado es 112.2. Pero, es claramente visible que 1, 200 y 250 son rangos que son demasiado pequeños o demasiado grandes para ser parte del conjunto de datos. Estos rangos se conocen como valores atípicos en los datos. Después de eliminar los valores atípicos, el promedio se convierte en 95.85. Evidentemente, se ve en el ejemplo anterior que un valor atípico tomará decisiones basadas.

Encontrar valores atípicos usando Ordenar en Excel

Esta es una de las formas más fáciles de encontrar valores atípicos en MS Excel cuando sus datos no son enormes porque al observar los datos conocerá los valores que están muy lejos de los valores registrados originalmente.

A partir de la imagen de arriba, podemos decir claramente que los datos no están ordenados y, por lo tanto, nos llevaría algún tiempo identificar los valores atípicos.

Mientras miraba Img. 2, podemos decir claramente que los números 1, 200 y 250 son valores atípicos. 

Encontrar valores atípicos usando la función de Excel GRANDE/PEQUEÑA

Otra forma de encontrar valores atípicos es mediante el uso de funciones integradas de MS Excel conocidas como GRANDES y PEQUEÑAS. La función GRANDE devolverá el valor más grande de la array de datos y la función PEQUEÑO devolverá el valor más pequeño. Aquí, usaremos una función GRANDE y PEQUEÑA que es una función incorporada en Microsoft Excel. Considere el ejemplo utilizado anteriormente:

Sintaxis de la función GRANDE:

GRANDE ($B $1: $B $12, 1)

Aquí, estamos pasando una array y un número. La array tiene el conjunto de datos para el que tenemos que encontrar el valor atípico y el número, 1, representa el primer número más grande de la array. Si usamos 2, devolverá el segundo valor más grande de la array. Ahora, cuando usemos esta función en el ejemplo anterior, obtendremos el siguiente resultado:

Sintaxis de la función PEQUEÑA:

PEQUEÑO ($B $1: $B $12, 1)

La sintaxis y el valor de transmisión son los mismos. Ahora, cuando usemos esta función en el ejemplo anterior, obtendremos el siguiente resultado:

Nota: si hay varios valores atípicos en los datos, debe usar la función una y otra vez.

Encontrar valores atípicos utilizando el rango intercuartílico (IQR)

Los datos presentados en el ejemplo anterior tienen un tamaño de muestra pequeño, pero cuando se trata de una situación de la vida real, los datos pueden ser enormes, y ahí es donde llega el problema original. Según IQR , un valor atípico es cualquier punto de datos que se encuentra más de 1,5 veces IQR por debajo del primer cuartil (Q1) y 1,5 veces IQR por encima del tercer cuartil (Q3) en un conjunto de datos.

la fórmula es

Alto = Q3 + 1.5 * IQR

Bajo = Q1 – 1.5 * IQR

Encontrar valores atípicos usando los siguientes pasos:

Paso 1: abra la hoja de trabajo donde se almacenan los datos para encontrar valores atípicos. 

Paso 2: Agregue la función CUARTIL(array, cuarto) , donde una array es el conjunto de datos para el cual se calcula el cuartil y un cuarto es el número del cuartil. En nuestro caso, el cuarto es 1 porque deseamos calcular el primer cuartil para calcular el valor atípico más bajo.

Número de cuarto	Rendimientos de cuartiles
0	Valor mínimo
1	Primer cuartil (percentil 25)
2	Valor de la mediana (percentil 50)
3	Tercer cuartil (percentil 75)
4	Valor máximo

Paso 3: Similar al paso 2, agregue la fórmula del cuartil debajo de Q3 y escriba 3 como número de cuarto porque deseamos calcular el 3er cuartil, es decir, el percentil 75 para calcular el valor del cuartil más alto.

Paso 4: el rango entre cuartiles o IQR es Q3-Q1 , coloque la fórmula para obtener el valor de IQR.

Paso 5: Para encontrar el valor Alto , la fórmula es Q3+(1.5*IQR) . De manera similar, para Valor bajo , la fórmula es Q1-(1.5*IQR)

Paso 6: Para encontrar si el número en el conjunto de datos es un valor atípico o no, debemos verificar si la entrada de datos es mayor que el valor Alto o menor que el valor Bajo. Para realizar esto usaremos la función OR . La fórmula será OR(B3>$G$3, B3<$H$3). Coloque la fórmula en la celda requerida y arrastre hacia abajo la celda adyacente al último conjunto de datos, si el valor devuelve VERDADERO , entonces los datos son atípicos, de lo contrario no lo son.

Dado que ha comprobado los datos atípicos. Ahora puede eliminar los valores atípicos y utilizar los datos restantes para los cálculos y obtener resultados imparciales.