Cuando trabaje con datos en Excel, a menudo tendrá problemas para manejar valores atípicos en su conjunto de datos.
Tener valores atípicos es bastante común en todo tipo de datos, y es importante identificar y tratar estos valores atípicos para asegurarse de que su análisis sea correcto y más significativo.
En este tutorial, te mostraré cómo encontrar valores atípicos en Excel, y algunas de las técnicas que he usado en mi trabajo para manejar estos valores atípicos.
¿Qué son los valores atípicos y por qué es importante encontrarlos?
Un valor atípico es un punto de datos que está mucho más allá de los otros puntos de datos del conjunto de datos. Cuando tiene un valor atípico en los datos, puede sesgar sus datos, lo que puede conducir a inferencias incorrectas.
Dejame darte un ejemplo simple.
Supongamos que 30 personas viajan en un autobús desde el destino A hasta el destino B. Todas las personas están en un grupo de peso y grupo de ingresos similar. Para el propósito de este tutorial, consideremos que el peso promedio es de 220 libras y el ingreso anual promedio es de $ 70,000.
Ahora, en algún lugar en el medio de nuestra ruta, el autobús se detiene y Bill Gates sube.
Ahora, ¿qué crees que afectaría esto al peso promedio y al ingreso promedio de las personas en el autobús?
Si bien es poco probable que el peso promedio cambie mucho, el ingreso promedio de las personas en el autobús se disparará enormemente.
Eso se debe a que los ingresos de Bill Gates son un valor atípico en nuestro grupo y eso nos da una interpretación incorrecta de los datos. El ingreso promedio de cada persona en el autobús sería de unos pocos miles de millones de dólares, que está muy por encima del valor real.
Al trabajar con conjuntos de datos reales en Excel, puede tener valores atípicos en cualquier dirección (es decir, un valor atípico positivo o un valor atípico negativo).
Y para asegurarse de que su análisis sea correcto, de alguna manera necesita identificar estos valores atípicos y luego decidir cómo tratarlos mejor.
Ahora veamos un par de formas de encontrar valores atípicos en Excel.
Encuentre valores atípicos ordenando los datos
Con conjuntos de datos pequeños, una forma rápida de identificar valores atípicos es simplemente ordenar los datos y revisar manualmente algunos de los valores en la parte superior de estos datos ordenados.
Y dado que podría haber valores atípicos en ambas direcciones, asegúrese de ordenar primero los datos en orden ascendente y luego en orden descendente y luego pasar por los valores superiores.
Dejame mostrarte un ejemplo.
A continuación, tengo un conjunto de datos en el que tengo la duración de las llamadas (en segundos) para 15 llamadas de servicio al cliente.
A continuación, se muestran los pasos para ordenar estos datos para que podamos identificar los valores atípicos en el conjunto de datos:
- Seleccione el encabezado de columna de la columna que desea ordenar (celda B1 en este ejemplo)
- Haga clic en la pestaña Inicio
- En el grupo Edición, haga clic en el icono Ordenar y filtrar.
- Haga clic en Orden personalizado
- En el cuadro de diálogo Ordenar, seleccione "Duración" en el menú desplegable Ordenar por y "De mayor a menor" en el menú desplegable Orden.
- Haga clic en Aceptar
Los pasos anteriores ordenarían la columna de duración de la llamada con los valores más altos en la parte superior. Ahora puede escanear manualmente los datos y ver si hay valores atípicos.
En nuestro ejemplo, puedo ver que los dos primeros valores son mucho más altos que el resto de los valores (y los dos últimos son mucho más bajos).
Nota: este método funciona con pequeños conjuntos de datos en los que puede escanear manualmente los datos. No es un método científico pero funciona bien
Encontrar valores atípicos con las funciones cuartiles
Ahora hablemos de una solución más científica que puede ayudarlo a identificar si existen valores atípicos o no.
En estadística, un cuartil es una cuarta parte del conjunto de datos. Por ejemplo, si tiene 12 puntos de datos, entonces el primer cuartil serían los tres puntos de datos inferiores, el segundo cuartil serían los siguientes tres puntos de datos, y así sucesivamente.
A continuación se muestra el conjunto de datos donde quiero encontrar los valores atípicos. Para hacer esto, tendré que calcular el primer y tercer cuartil, y luego usarlo para calcular el límite superior e inferior.
A continuación se muestra la fórmula para calcular el primer cuartil en la celda E2:
= CUARTIL.INC ($ B $ 2: $ B $ 15,1)
y aquí está el que calcula el tercer cuartil en la celda E3:
= CUARTIL.INC ($ B $ 2: $ B $ 15,3)
Ahora, puedo usar los dos cálculos anteriores para obtener el rango intercuartil (que es el 50% de nuestros datos dentro del primer y tercer cuartil)
= F3-F2
Ahora usaremos el rango intercuartil para encontrar el límite superior e inferior que contendría la mayoría de nuestros datos.
Todo lo que esté fuera de estos límites superior e inferior se consideraría un valor atípico.
A continuación se muestra la fórmula para calcular el límite inferior:
= Cuartil1 - 1.5 * (Intervalo entre cuartiles)
que en nuestro ejemplo se convierte en:
= F2-1.5 * F4
Y la fórmula para calcular el límite superior es:
= Cuartil3 + 1.5 * (Intervalo entre cuartiles)
que en nuestro ejemplo se convierte en:
= F3 + 1.5 * F4
Ahora que tenemos el límite superior e inferior en nuestro conjunto de datos, podemos volver a los datos originales e identificar rápidamente aquellos valores que no se encuentran en este rango.
Una forma rápida de hacer esto sería verificar cada valor y devolver VERDADERO o FALSO en una nueva columna.
He usado la fórmula OR a continuación para obtener VERDADERO para aquellos valores que son valores atípicos.
= O (B2 $ F $ 6)
Ahora puede filtrar la columna de valores atípicos y mostrar solo los registros donde el valor es VERDADERO.
Alternativamente, también puede usar formato condicional para resaltar todas las celdas donde el valor es VERDADERO
Nota: Si bien este es un método más aceptado para encontrar valores atípicos en las estadísticas. Encuentro este método un poco inutilizable en escenarios de la vida real. En el ejemplo anterior, el límite inferior calculado por la fórmula es -103, mientras que el conjunto de datos que tenemos solo puede ser positivo. Entonces, este método puede ayudarnos a encontrar valores atípicos en una dirección (valores altos), es inútil para identificar valores atípicos en la otra dirección.Encontrar los valores atípicos con las funciones GRANDE / PEQUEÑA
Si trabaja con muchos datos (valores en varias columnas), puede extraer los 5 o 7 valores más grandes y más pequeños y ver si hay valores atípicos en ellos.
Si hay valores atípicos, podrá identificarlos sin tener que revisar todos los datos en ambas direcciones.
Supongamos que tenemos el siguiente conjunto de datos y queremos saber si hay valores atípicos.
A continuación se muestra la fórmula que le dará el valor más grande en el conjunto de datos:
= GRANDE ($ B $ 2: $ B $ 16,1)
De manera similar, el segundo valor más grande vendrá dado por
= GRANDE ($ B $ 2: $ B $ 16,1)
Si no está usando Microsoft 365, que tiene matrices dinámicas, puede usar la siguiente fórmula y le dará los cinco valores más grandes del conjunto de datos con una sola fórmula:
= GRANDE ($ B $ 2: $ B $ 16, FILA ($ 1: 5))
Del mismo modo, si desea los 5 valores más pequeños, use la siguiente fórmula:
= PEQUEÑO ($ B $ 2: $ B $ 16, FILA ($ 1: 5))
o lo siguiente en caso de que no tenga matrices dinámicas:
= PEQUEÑO ($ B $ 2: $ B $ 16,1)
Una vez que tenga estos valores, es muy fácil descubrir cualquier valor atípico en el conjunto de datos.
Si bien elegí extraer los 5 valores más grandes y más pequeños, puede elegir obtener 7 o 10 según el tamaño de su conjunto de datos.
No estoy seguro de si este es un método aceptable para encontrar valores atípicos en Excel o no, pero este es el método que utilicé cuando tuve que trabajar con muchos datos financieros en mi trabajo hace unos años. En comparación con todos los otros métodos cubiertos en este tutorial, encontré que este es el más efectivo.
Cómo manejar los valores atípicos de la manera correcta
Hasta ahora, hemos visto los métodos que nos ayudarán a encontrar los valores atípicos en nuestro conjunto de datos. Pero qué hacer una vez que sepa que existen valores atípicos.
Aquí hay un par de métodos que puede usar para manejar valores atípicos para que su análisis de datos sea correcto.
Eliminar los valores atípicos
La forma más fácil de eliminar valores atípicos de su conjunto de datos es simplemente eliminarlos. De esta forma, no desviará su análisis.
Es una solución más viable cuando tiene grandes conjuntos de datos y eliminar un par de valores atípicos no afectará el análisis general. Y, por supuesto, antes de eliminar los datos, asegúrese de crear una copia y profundizar en la causa de estos valores atípicos.
Normalizar los valores atípicos (ajustar el valor)
Normalizar los valores atípicos es lo que solía hacer cuando estaba en mi trabajo de tiempo completo. Para todos los valores atípicos, simplemente los cambiaría a un valor que sea ligeramente más alto que el valor máximo en el conjunto de datos.
Esto aseguró que no eliminaré los datos pero, al mismo tiempo, no permitiré que desvirtúen mis datos.
Para darle un ejemplo de la vida real, si está analizando el margen de beneficio neto de las empresas, donde la mayoría de las empresas se encuentran entre -10% y 30%, y hay un par de valores que superan el 100%, yo simplemente cambiaría estos valores atípicos a 30% o 35%.
Estos son algunos de los métodos que puede utilizar en Excel para encontrar valores atípicos.
Una vez que haya identificado los valores atípicos, puede profundizar en los datos y buscar qué los está causando, al mismo tiempo elegir una de las técnicas para manejar estos valores atípicos (que podría ser eliminarlos o normalizarlos ajustando el valor).
Espero que este tutorial te haya resultado útil.
