Identificar identificaciones faltantes y brechas en la secuencia

Hay varias formas en Excel de encontrar ID faltantes (o espacios en blanco) en una lista grande de ID secuenciales, como números de cheque o números de factura. En esta publicación, usaremos Power Query para que cada vez que tengamos una lista nueva, simplemente hagamos clic en Actualizar. Luego, Excel crea una lista actualizada de los ID que faltan. Este enfoque agiliza las tareas recurrentes porque no es necesario ordenar, escribir fórmulas, filtrar ni aplicar formato condicional manualmente. Simplemente haga clic en Actualizar y Excel proporcionará una lista de las ID que faltan.

Objetivo

Antes de pasar a Power Query, aclaremos nuestro objetivo. Digamos que exportamos una lista que incluye una columna de ID secuenciales, por ejemplo, una lista de controles. Nuestro objetivo es revisar la lista e identificar los números de cheque que faltan, es decir, espacios en la columna de identificación.

Aquí tienes una idea de la lista:

Nuestro objetivo es encontrar los CheckNums que faltan en la secuencia. Como se trata de Excel, existen, por supuesto, muchas formas de realizar esta tarea. Por ejemplo, si se tratara de un proyecto único o de una lista corta, podríamos optar por hacerlo manualmente.

Un enfoque común es ordenar los datos por la columna CheckNum. Una vez ordenada, podríamos escanear visualmente esta lista en busca de espacios vacíos o escribir una fórmula. Por ejemplo, podríamos escribir una fórmula que reste el número del cheque de la fila de arriba, o algo similar. Si la diferencia es 1, sabemos que es secuencial; de lo contrario, sabemos que hay una brecha. Entonces, tal vez podríamos aplicar un filtro o formato condicional o algo para que sea un poco más fácil identificar los espacios. Y este enfoque haría el trabajo.

Pero, cuando se trata de una tarea que realizamos con frecuencia, como cada semana o mes, podemos optar por eliminar todos estos pasos manuales para poder realizar nuestro trabajo más rápido. Aquí es donde Power Query puede ayudar. Por supuesto, hay una compensación, porque la configuración inicial en PQ puede llevar más tiempo… pero, se trata de una inversión que paga dividendos de eficiencia cada mes subsiguiente.

Creé un video y escribí una narrativa para recorrer el proceso de configuración de Power Query.

Video

Narrativo

Realizaremos los siguientes pasos:

• Importar los datos
• Crear una lista de todas las identificaciones
• Generar lista de identificaciones faltantes

Hagámoslo.

Importar los datos

Lo primero que debemos hacer es importar los datos a Power Query. Para hacer esto, simplemente dirígete a Datos Obtener datos y elige la ubicación de datos correspondiente. En este caso, nuestros datos de muestra están en una tabla de Excel, por lo que usamos Obtener datos De tabla/rango . Luego, Excel carga los datos y abre el Editor de Power Query, como se muestra a continuación.

Hacemos clic en Cerrar y cargar en… y seleccionamos Solo crear conexión en el cuadro de diálogo Importar datos resultante, como se muestra a continuación.

Ahora, pasemos a nuestro siguiente paso… crear una lista de todas las identificaciones.

Crear una lista de todas las identificaciones

El objetivo de este paso es crear una lista de todos los ID dentro del rango… es decir, entre los valores de ID mínimo y máximo. Para lograr este paso, crearemos tres consultas nuevas. El primero determinará el valor mínimo. El segundo determinará el valor máximo. Y el tercero creará el rango de todos los valores entre el mínimo y el máximo, inclusive.

mín.

Para crear una consulta que devuelva el valor de ID mínimo, hacemos clic derecho en la consulta creada anteriormente, en este caso se llama Tabla1, y seleccionamos Referencia como se muestra a continuación.

Esto simplemente crea una nueva consulta que utiliza como punto de partida los resultados de la consulta Tabla1, como se muestra a continuación.

Luego, para obtener el valor mínimo en la columna CheckNum, seleccionamos la columna CheckNum y luego hacemos clic en el comando Transformar Estadísticas Mínimo . Power Query busca y devuelve el CheckNum más pequeño, como se muestra a continuación.

En este punto, simplemente le damos a la consulta un Nombre descriptivo, como IDMin y Cerrar y cargar para… Crear solo conexión .

Ahora vemos la nueva consulta de IDMin en el panel Consultas y conexiones, como se muestra a continuación.

máx.

Básicamente enjuagamos y repetimos para crear otra consulta que devuelva el valor máximo en el rango. A la nueva consulta le asignamos el nombre IDMax .

Rango

El último paso es utilizar los valores IDMin e IDMax para crear un rango de todos los ID. Hacemos esto configurando una nueva consulta en blanco ( Datos Obtener datos De otras fuentes Consulta en blanco ).

Simplemente ingresamos la siguiente fórmula en la barra de fórmulas (que actualiza el paso Fuente):

={IDMín..IDMáx}

Y presiona enter. Bam:

¿¡Esperar lo!? Lo sé, cierto… ¡es súper genial! Las llaves le indican a PQ que cree una lista de valores entre los valores IDMIn e IDMax.

Nota: PQ distingue entre mayúsculas y minúsculas, por lo tanto, asegúrese de utilizar los nombres de consulta exactos (IDMax vs idmax).

Lo último es convertir la “lista” resultante en una “tabla” para que podamos usarla en el siguiente paso. Esto es facil. Simplemente hacemos clic en el comando A tabla en el grupo Convertir que se muestra arriba. Hacemos clic en Aceptar en el cuadro de diálogo resultante y ahora tenemos una tabla que podemos usar en nuestro paso final, como se muestra a continuación.

Podemos limpiarlo un poco cambiando la etiqueta de la columna Columna1 a ID y cambiando el nombre de la consulta a IDRange, como se muestra a continuación.

Simplemente cerramos y cargamos para… Crear conexión únicamente .

Ahora estamos listos para el paso final.

Generar lista de identificaciones faltantes

El objetivo del paso final es que PQ cree una lista de los ID que faltan. Más específicamente, para crear una lista de ID en la consulta IDRange que no se encuentran en la consulta de fuente de datos original. Para lograr esto, fusionaremos las dos consultas y especificaremos el tipo de unión deseado.

Primero, seleccionamos el comando Datos Obtener datos Combinar consultas Combinar . En el cuadro de diálogo Combinar resultante, seleccionamos nuestra consulta IDRange y luego nuestra consulta de fuente de datos original (Tabla1) como se muestra a continuación.

Luego, identificamos las columnas que se comparten entre las dos tablas, en este caso ID y CheckNum , seleccionándolas como se muestra a continuación:

Finalmente, debemos elegir el tipo de unión deseado. En este caso, queremos crear una lista que incluya los ID de la primera tabla que no aparecen en la segunda tabla. Entonces, seleccionamos Left Anti (filas solo en primera) como se muestra a continuación.

Le damos a Aceptar y bam:

¡Lo hicimos! Ahora, podemos simplemente eliminar la columna Tabla1 (seleccionarla y presionar la tecla Eliminar en su teclado) y Cerrar y cargar en… una tabla en una nueva hoja de trabajo . Los ID que faltan se muestran en una tabla en Excel, como se muestra a continuación.

Y ahora que el trabajo está hecho, esta lista de identificaciones faltantes es fácil de actualizar en cada período posterior. Siempre que haya datos nuevos, solo debemos hacer clic derecho en la tabla de resultados y hacer clic en Actualizar. Power Query repite instantáneamente todos los pasos automáticamente y actualiza la tabla de resultados. ¡Pendiente!

Si prefiere otros enfoques para realizar esta tarea, compártalo publicando un comentario a continuación.

Archivo de muestra: MissingIDs.xlsx