Cómo detectar valores atípicos y errores de captura en una hoja de cálculo

Basta un cero de más para que un pedido de 150 se convierta en 1500 y el total del mes se dispare. Los errores de captura rara vez saltan a la vista en una hoja de cientos de filas, pero distorsionan promedios, gráficos y decisiones.

Aquí aprenderás a localizar valores sospechosos con métodos sencillos y fiables: un primer vistazo con funciones básicas, el método del rango intercuartílico, la puntuación z, el formato condicional y algunas fórmulas para errores concretos. Al final verás cómo evitar que los errores entren en la hoja.

Atípico no siempre significa error

Un valor atípico es el que se aleja mucho del resto. Puede ser un error, pero también un dato real: un cliente que hizo un pedido enorme o un mes con ventas excepcionales. Por eso la regla de oro es marcar, revisar y después decidir, nunca borrar de forma automática.

Los errores de captura más habituales son:

• Ceros de más o de menos: 1500 en lugar de 150.
• Separador decimal equivocado: 1250 en lugar de 12,50.
• Dígitos invertidos: 516 en lugar de 156.
• Unidades mezcladas: gramos en una columna de kilos.
• Valores imposibles: cantidades negativas, edades de 250 años o fechas futuras en un registro de ventas.
• Texto en columnas numéricas: "n/d", "pendiente" o un guion.
• Registros duplicados: la misma fila introducida dos veces.

Primer vistazo: mínimo, máximo y mediana

Antes de aplicar métodos más finos, calcula tres valores sobre la columna (en el ejemplo, importes en B2:B200):

=MIN(B2:B200)

=MAX(B2:B200)

=MEDIANA(B2:B200)

Si la mediana es 140 y el máximo es 14000, ya sabes por dónde empezar. Compara también la mediana con PROMEDIO: cuando el promedio está muy por encima de la mediana, unos pocos valores extremos lo están arrastrando.

Otra forma rápida es ordenar la columna de mayor a menor con Datos › Ordenar y revisar las primeras y últimas filas. Si no quieres alterar el orden original, añade antes una columna con números correlativos para poder restaurarlo.

El método del rango intercuartílico

Es el método más robusto para hojas de cálculo, porque no se deja influir por los propios valores extremos. Divide los datos en cuatro partes y considera atípico lo que queda muy lejos de la mitad central.

1. Calcula el primer cuartil en F1: =CUARTIL.INC($B$2:$B$200;1)
2. Calcula el tercer cuartil en F2: =CUARTIL.INC($B$2:$B$200;3)
3. Calcula el rango intercuartílico en F3: =F2-F1
4. Límite inferior en F4: =F1-1,5*F3
5. Límite superior en F5: =F2+1,5*F3

Después, en una columna auxiliar, marca cada fila que quede fuera de esos límites:

=O(B2<$F$4;B2>$F$5)

Con datos de muestra se ve así:

El factor 1,5 es el habitual. Si quieres marcar solo los casos más extremos, usa 3.

La puntuación z

La puntuación z indica a cuántas desviaciones típicas está cada valor del promedio. En C2:

=(B2-PROMEDIO($B$2:$B$200))/DESVEST.M($B$2:$B$200)

Y para marcar los valores alejados:

=ABS(C2)>3

Un valor absoluto mayor que 3 es muy poco frecuente si los datos siguen una distribución normal. Ten en cuenta dos limitaciones: los propios atípicos inflan la desviación típica y pueden esconderse, y con pocos datos (menos de 30, por ejemplo) el método es poco fiable. En esos casos, el rango intercuartílico funciona mejor.

Resaltar valores sospechosos con formato condicional

Si prefieres ver los atípicos sin columnas auxiliares:

1. Selecciona B2:B200.
2. Ve a Inicio › Formato condicional › Nueva regla.
3. Elige Utilice una fórmula que determine las celdas para aplicar formato.
4. Escribe =O($B2<$F$4;$B2>$F$5) y elige un relleno de color.

Para un análisis rápido sin cálculos, las Reglas superiores e inferiores del mismo menú resaltan, por ejemplo, el 10 % de valores más altos. Es útil como primera exploración, aunque siempre marcará algo, haya errores o no.

Fórmulas para errores de captura concretos

Texto en una columna numérica

La diferencia entre las celdas no vacías y las que contienen números revela cuántos textos se han colado:

=CONTARA(B2:B200)-CONTAR(B2:B200)

Valores imposibles

Cuenta cantidades negativas y fechas posteriores a hoy (con las fechas en la columna D):

=CONTAR.SI(B2:B200;"<0")

=CONTAR.SI(D2:D200;">"&HOY())

Un cero o una coma desplazados

Divide cada valor entre la mediana. Los resultados cercanos a 10 o a 0,1 suelen indicar un cero de más o un decimal mal colocado:

=B2/MEDIANA($B$2:$B$200)

Registros repetidos

Para saber si un número de pedido aparece más de una vez:

=CONTAR.SI($A$2:$A$200;A2)>1

Duplicados y comparación de versiones

Las filas introducidas dos veces son uno de los errores de captura más frecuentes, y con varios miles de registros revisarlas a mano no es práctico. Nuestra herramienta gratuita para quitar duplicados elimina las filas repetidas de un archivo de Excel, CSV u ODS en segundos. Y cuando termines de corregir, puedes comparar el archivo original con el corregido para revisar exactamente qué celdas cambiaste.

Cómo evitar errores con la validación de datos

La mejor forma de reducir errores es impedir que entren. Con la validación de datos, Excel rechaza los valores fuera del rango que definas:

1. Selecciona la columna donde se introducen las cantidades.
2. Ve a Datos › Validación de datos.
3. En Permitir, elige Número entero o Decimal, y fija un mínimo y un máximo razonables, por ejemplo entre 1 y 500.
4. En la pestaña Mensaje de error, escribe un aviso claro para quien rellena la hoja.

Para campos con valores fijos, como categorías o estados, usa la opción Lista: evitas variantes como "Pendiente", "pendiente" y "Pdte.". Ten en cuenta que la validación no revisa los datos que ya estaban en la hoja ni los que se pegan encima de las celdas.

Conclusión

Detectar errores de captura es cuestión de método: empieza por mínimo, máximo y mediana, marca los atípicos con el rango intercuartílico o la puntuación z, resáltalos con formato condicional y usa fórmulas específicas para textos, valores imposibles y duplicados. Revisa cada caso antes de corregirlo y, para el futuro, protege la entrada de datos con validación.

Preguntas frecuentes