Lista de Números Omitidos en Secuencia – Excel y Google Sheets

Este tutorial demostrará cómo listar números omitidos en un rango

 

 

Listar Números Omitidos en Secuencia

Para encontrar números omitidos en una lista, podemos utilizar la siguiente fórmula

=K.ESIMO.MENOR(SI(CONTAR.SI($B$3:$B$8;FILA($1:$6))=0;FILA($1:$6);"");FILA(B1))

 

Notas

• ¡Esta es una fórmula de matriz! Cuando uses Excel 2019 y anteriores, debes introducir la fórmula pulsando CTRL MAYÚS INTRO. Sabrás que la fórmula se introdujo correctamente cuando veas corchetes alrededor de la fórmula.
• Esta fórmula sólo funciona con números enteros positivos (enteros).

¿Cómo Funciona la Fórmula?

Primero, la Función FILA se usa para generar un arreglo de números

{1;2;3;4;5;6}

De hecho, incluso podría introducir esta matriz de números manualmente

=K.ESIMO.MENOR(SI(CONTAR.SI($B$3:$B$8;{1;2;3;4;5;6})=0;{1;2;3;4;5;6};"");FILA(B1))

A continuación, la Función CONTAR.SI contará los valores “SI” coinciden con un valor encontrado en el array

Para visualizar esto, primero convertiremos el rango $B$3:$B$8 a su matriz de valores {3;2;0;6;0;5}

=K.ESIMO.MENOR(SI(CONTAR.SI({3;2;0;6;0;5};{1;2;3;4;5;6})=0;{1;2;3;4;5;6};"");FILA(B1))

A continuación realizaremos la operación CONTAR.SI

=K.ESIMO.MENOR(SI({0;1;1;0;1;1}=0;{1;2;3;4;5;6};"");FILA(B1))

Si no se encuentra un valor, se muestra 0. La función SI comprueba si el valor NO se encuentra y, en caso afirmativo, devuelve el valor de la matriz; en caso contrario, muestra un espacio en blanco

=K.ESIMO.MENOR({1;"";"";4;"";""};FILA(B1))

Por último, la Función K.ESIMO.MENOR devuelve el valor más pequeño que falta en la celda seleccionada

=K.ESIMO.MENOR({1;"";"";4;"";""};{1})

={1}

Consejo

Para evitar el error #NUM! envuelva su fórmula con la Función SI.ERROR

=SI.ERROR(K.ESIMO.MENOR(SI(CONTAR.SI($B$3:$B$8;FILA($1:$6))=0;FILA($1:$6);"");FILA(B1));"")

 

Lista de Números Omitidos en Secuencia en Google Sheets

Todos los ejemplos explicados anteriormente funcionan igual en Google Sheets que en Excel