Marcya tiene una larga lista de números ordenados en la columna A de una hoja de trabajo.

Estos números son supuestamente consecutivos, pero ella no sabe si eso es cierto. Examinar la lista manualmente es tedioso y propenso a errores, por lo que Marcya se pregunta si hay una manera de resaltar de alguna manera los «números perdidos» (los que no son consecutivos con el anterior) o de compilar una lista de números perdidos. en la lista.

Hay varias formas de averiguar dónde faltan números. El primero es uno que uso con bastante frecuencia: agrego una columna auxiliar al lado de la columna A. Suponiendo que sus números comienzan en la celda A1, pongo esto en la celda B2:

=IF(A2<>A1+1,"Error","")

Copie la fórmula tantas celdas como sea necesario y verá fácilmente la palabra «Error» junto a cualquier valor que no sea consecutivo al valor que está justo encima. Si prefiere saber un poco más sobre el error, puede utilizar una fórmula más detallada:

=IF(A2=A1,"Duplicate",IF(A2<>A1+1,"Gap",""))

Otro enfoque es usar formato condicional en las celdas de la columna A. Siga estos pasos, nuevamente asumiendo que sus valores comienzan en la celda A1:

  1. Seleccione el rango A2 hasta el último valor en la columna A.

  2. Con la pestaña Inicio de la cinta mostrada, haga clic en la opción Formato condicional en el grupo Estilos. Excel muestra una paleta de opciones relacionadas con el formato condicional.

  3. Elija Resaltar reglas de celdas y luego elija Más reglas en el submenú resultante. Excel muestra el cuadro de diálogo Nueva regla de formato.

(Ver figura 1)

  1. En el área Seleccionar un tipo de regla en la parte superior del cuadro de diálogo, elija Usar una fórmula para determinar qué celdas formatear.

  2. En el cuadro Formato de valores donde esta fórmula es verdadera, ingrese esta fórmula: = A2 <> A1 + 1. Haga clic en Formato para mostrar el cuadro de diálogo Formato de celdas.

  3. Con los controles del cuadro de diálogo, especifique un formato que desee utilizar para resaltar las celdas que no son consecutivas.

  4. Haga clic en Aceptar para cerrar el cuadro de diálogo Formato de celdas. El formato que especificó en el paso 7 debería aparecer ahora en el área de vista previa de la regla.

  5. Haga clic en Aceptar.

Finalmente, si desea compilar una lista de los números que faltan en una serie consecutiva, puede usar una fórmula de matriz. Coloque lo siguiente en la fila 1 de una columna vacía:

=IFERROR(SMALL(IF(COUNTIF($A$1:$A$135, MIN($A$1:$A$135)+ROW($1:$135)-1)=0,  MIN($A$1:$A$135)+ROW($1:$135)-1),ROW(A1)),"")

Recuerde que esta es una fórmula de matriz única, por lo que debe ingresarla como una sola línea usando Ctrl + Shift + Enter. Luego puede copiar la fórmula en varias celdas, hasta que no devuelva más valores. Además, la fórmula asume que su serie está en el rango A1: A135; si no es así, deberá modificar la fórmula para reflejar el rango real.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (4315) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.