Lista de tablas y referencias del libro de trabajo

John preguntó si había alguna manera de crear una lista de todos los nombres de tablas en el libro, junto con la referencia de la tabla subyacente. En esta publicación, demostraré cómo se puede lograr esto con Power Query y una fórmula de Excel.

Objetivo

Antes de entrar en la mecánica, confirmemos nuestro objetivo aquí. Tenemos un libro de trabajo que tiene numerosas tablas almacenadas en varias hojas de trabajo. Las tablas se crearon usando el comando Insertar Tabla y se parecen un poco a esto:

Sin usar VBA, nos gustaría crear una lista de estas tablas junto con la referencia de hoja/celda correspondiente. Mientras estamos en esto, también podemos facilitar la navegación a cada tabla configurando un hipervínculo. Además, queremos que sea fácil de actualizar si algo cambia… así que tal vez debamos hacer clic derecho y actualizar para obtener las tablas nuevas que podamos agregar en el futuro.

Básicamente queremos algo como esto:

Haremos esto.

Video

Narrativo

Lograremos nuestro objetivo en tres pasos:

• Obtener nombres de tablas con Power Query
• Crear la columna de referencia con CELL
• Crea la columna de enlace con HYPERLINK

Los tomaremos uno a la vez.

Obtener nombres de tablas con Power Query

Para crear la lista de nombres de tablas, usaremos Power Query. Datos Obtener datos De otras fuentes De consulta en blanco . En la ventana resultante del Editor de Power Query, escribimos la siguiente fórmula (distingue entre mayúsculas y minúsculas) en la barra de fórmulas y presionamos Enter:

=Excel.Libro de trabajo actual()

Power Query luego muestra una lista de las tablas, como esta:

Realmente no necesitamos llevar la columna Contenido hacia adelante, por lo que podemos eliminarla.

Además, la tabla de resultados de Power Query se incluirá en los resultados de la consulta la próxima vez que actualicemos, por lo que podemos evitar que aparezca configurando un filtro ahora. (También podemos configurar el filtro más tarde si lo preferimos).

Para hacerlo, simplemente anote el nombre de la consulta actual o establezca específicamente el nombre. Utilicé el nombre TOC para recordarme que es como una tabla de contenido:

Dado que queremos excluir la tabla de resultados de Power Query (llamada TOC) de la lista de tablas en el futuro (aunque no aparezca en este momento), podemos usar el control de filtro en la columna Nombre y seleccionar Filtros de texto No Igual y luego ingresa el nombre (TOC en este caso):

Ahora podemos cerrar y cargar en una tabla en la hoja de trabajo deseada. Power Query envía esta lista de tablas a una nueva tabla en la ubicación especificada:

Una vez completado esto, es hora de agregar una columna auxiliar para mostrar una referencia a la ubicación de cada tabla dentro del libro.

Crear la columna de referencia con CELL

Crearemos la columna Referencia usando la función CELDA. La función CELDA de Excel devuelve información sobre la celda a la que se hace referencia.

Entonces, en la celda inmediatamente a la derecha del encabezado de la columna Nombre, escribimos el nombre deseado de nuestra columna de referencia (Ref) y la tabla de resultados se expande automáticamente para incluirlo:

Dado que esta tabla de resultados es una tabla, cualquier fórmula que ingresemos en la primera celda de datos de la nueva columna Ref se completará automáticamente.

Introducimos la siguiente fórmula:

=CELDA("dirección",INDIRECTA([@Nombre]))

Cuando presionamos Enter, bam:

Sí… ahora tenemos una columna que muestra la referencia de la tabla, que incluye el nombre del libro, el nombre de la hoja y la celda superior izquierda.

Algunas notas en este punto.

El primer argumento de la función CELL es el tipo de información que queremos sobre la celda. En este caso, solicitamos la “dirección” que incluye el libro de trabajo, la hoja de trabajo y la primera celda del rango.

El segundo argumento de la función CELDA utiliza la función INDIRECTA para convertir la cadena de texto del nombre de la tabla en una referencia real de Excel.

El primer resultado incluye comillas simples alrededor del libro y el nombre de la hoja de trabajo, mientras que los dos últimos resultados no las tienen. Esto se debe a que Excel los coloca automáticamente alrededor de nombres que incluyen un espacio.

Entonces, una vez completado esto, nuestro paso final es facilitar el acceso a cada tabla con un hipervínculo.

Crea la columna de enlace con HYPERLINK

Si queremos facilitarle a un usuario la navegación a una tabla específica en la lista, podemos agregar una columna Enlace usando la función HIPERVÍNCULO.

Creamos una nueva columna en la tabla de resultados escribiendo el nombre de la columna deseada (Enlace) en la celda inmediatamente a la derecha del encabezado de nuestra columna de referencia:

Luego ingresamos la siguiente fórmula:

=HIPERVÍNCULO("#"[@Ref],"Ir a")

Los hipervínculos se crean:

Podemos hacer clic en el enlace Ir a de la tabla deseada y Excel nos llevará allí instantáneamente.

Un par de notas sobre la función HIPERVÍNCULO.

El primer argumento de la función HIPERVÍNCULO proporciona el destino del enlace. Anteponemos el hash/libra para indicar que es un marcador de una referencia interna.

El segundo argumento es el nombre descriptivo que el usuario verá en la celda. Yo usé Ir a pero puedes usar cualquier otra cosa que prefieras.

Ahora, si alguna vez agregamos una nueva tabla, cambiamos el nombre de una tabla, eliminamos una tabla o cambiamos el nombre de una hoja, simplemente podemos hacer clic derecho en la tabla de resultados y hacer clic en Actualizar.

Bueno, creo que logramos nuestro objetivo… ¡sí!

Si disfrutó la publicación o tiene alguna sugerencia, compártala publicando un comentario a continuación… ¡gracias!

Archivo de muestra

TablaList.xlsxDescargar