Anexar todas las tablas de un libro con funciones

Un nuevo ejemplo de potencial de Excel es la respuesta, empleando solo funciones, a anexar todas las tablas de un mismo libro de manera automática.

Una posible solución a este problema es emplear Power Query, como ya se publicó en este artículo del blog.

Hoy conseguiremos el mismo resultado pero empleando algunas de 'catorce funciones':
AJUSTARFILAS, DIVIDIRTEXTO o ENFILA
Junto a otras igual de potentes como:
LET, UNIRCADENAS, MAP o LAMBDA

Te recomiendo leas previamente, antes de continuar, el artículo siguiente, donde se desarrolla un ejercicio que nos servirá para alcanzar nuestra meta.

Adicionalmente deberás entender el uso de las funciones macro 4.0 (leer artículo).

El uso de una función macro 4.0 particular es fundamental.
La función NAMES - NOMBRES(nombre_documento/archivo; tipo; criterio)
La función devuelve un vector, ordenado alfabéticamente, con los nombres definidos incluidos en el libro de trabajo descrito...
Sabiendo que:
-si omitimos el primer argumento, trabajaremos sobre el libro activo.
-el argumento tipo admite tres valores:
1-solo lista 'nombres normales'
2-nombres definidos ocultos
3-todos los nombres
-el tercer argumento de criterio, que permite el uso de comodines, permite identificar los nombres a recuperar

Llegados a este punto es importante resaltar un aspecto clave...
La función macr 4.0 'NOMBRES' trabaja exclusivamente sobre nombres definidos, NO sobre tablas (tiene sentido por que las Tablas aparecieron mucho después de la existenca/descatalogación de las macro 4.0)...
Asi que para que el trabajo se realice correctamente, a cada tabla habrá que asignarle un nombre definido!!
Un pequeño precio a pagar...

Vamos con el ejercicio...Partimos de dos tablas iniciales: TblUNO y TblDOS, a las cuales he asignado sendos nombres definidos: datosUNO y datosDOS respectivamente...
Notemos el patrón tomado a la hora de asignar nombres definidos, incluyendo un prefijo igual en ambas tablas (esto es clave).

Todo comienza creando una función LAMBDA personalizada que trabaje con la función macro 4.0 'NOMBRES'. Por tanto abrimos nuestro Administrador de nombres definidos e incluimos la siguiente fórmula, renombrándola como 'fxNAMES':
=LAMBDA(criterio;NOMBRES(;3;criterio))

Si utilizamos esta función directamente en una celda, por ejemplo en H2 y escribimos:
=TRANSPONER(fxNAMES("datos*"))&T(AHORA())
tendríamos un vector vertical 'volátil' con los nombres definidos en el libro activo que comiencen por el prefijo 'datos'...

Comprobado el resultado de esta función, ya podemos montar nuestra fórmula en E2 para obtener una matriz resultante de anexar las tablas-nombres definidos deseados:

=LET(rngs;TRANSPONER(fxNAMES("datos*"))&T(AHORA());
datos;UNIRCADENAS(";";;MAP(rngs;
                           LAMBDA(tbl;" "&ENFILA(MATRIZATEXTO( INDIRECTO(tbl);0) ))));

AJUSTARFILAS(EXTRAE(DIVIDIRTEXTO(datos;;";");2;LARGO(datos));2;"-"))

Versión reducida de la comentada en el artículo arriba indicado

La fórmula recorre los elementos obtenidos con nuestra función macro 4.0 (nombres definidos que empiecen por 'datos') para descomponerlos en sus distintos elementos con MATRIZATEXTO, trasladarlo a un vector fila (con la función ENFILA).
La función MAP se encarga de recorrer los distintos vectores obtenidos con ENFILA (un vector por cada nombre definido).
Y UNIRCADENAS nos permite concatenar en un único vector todos los elementos... Será finalmente DIVIDIRTEXTO quien 'separe' esa cadena de texto en un nuevo y único vector que AJUSTARFILAS devolverá en una matriz de dos columnas.

La fórmula se podría completar incluyendo encabezados o incluso fila de totales. Te remito al artículo anterior.

Puedes comprobar que al añadir nuevas tablas: 'TblTRES' y 'TblCUATRO', habiéndoles asignados nombres definidos: datosTRES y datosCUATRO, automáticamente aparecerán anexados!!

Increible solución sin salir del entorno de la hoja de cálculo!!