Recuperar valores de muchos libros de trabajo

Muy bien amigos míos, esta semana abordaré una pregunta que recibí sobre cómo recuperar valores de los libros de trabajo. Aquí está la idea básica de la pregunta. Tengo una carpeta con varios cientos de libros y cada libro puede contener cualquier cantidad de hojas de trabajo. Por ejemplo, algunos libros tienen dos hojas, otros tres hojas y otros hasta treinta hojas de trabajo. Necesito crear una única hoja de resumen que recupere valores de celda específicos de cada hoja en todos estos libros. Actualmente, copio y pego, pero quiero automatizar este proceso de búsqueda. Gracias Regina por tu pregunta y por inspirar esta publicación.

Objetivo

Seamos muy claros acerca de nuestro objetivo. Aquí está la configuración. Tenemos una carpeta que contiene cientos de archivos de Excel. Y, sólo para hacer esto un poco más complicado, supongamos que en cualquier momento se pueden agregar nuevos libros a la carpeta o se pueden eliminar algunos libros. En otras palabras, nuestra solución debe adaptarse a una cantidad cambiante de libros de trabajo y, esencialmente, tomar valores de todos los libros de la carpeta, sin importar cuántos haya en ese momento.

Además, los libros de trabajo no contienen la misma cantidad de hojas de trabajo. Algunos contienen dos, otros tres y otros hasta treinta. Nuestra solución necesita recuperar valores de todas las hojas y, además, se pueden agregar o eliminar hojas en cualquier momento. Y no existe una convención de nomenclatura para las hojas de trabajo… se nombran sin ningún patrón coherente. Por tanto, nuestra solución debe adaptarse a esta situación loca y dinámica.

Además, cada hoja de trabajo puede contener uno o más valores para recuperar. Y los valores pueden aparecer en cualquier fila. Entonces, no es que podamos usar una fórmula que recupere un valor de, digamos, la fila 10… el valor puede aparecer en cualquier fila y puede haber más de un valor en cada hoja. ¡Locura!

Ahora finalmente recibimos algunas buenas noticias. Los valores que necesitamos siempre se almacenan en la columna C y la columna B siempre contiene la palabra Total junto al valor. Por ejemplo, aquí está la estructura básica de cada hoja de trabajo.

Pero recuerde, el total puede aparecer en cualquier fila y algunas hojas tienen varios totales, como ésta.

Entonces, nuestra misión, si decidimos aceptarla, es crear una única hoja de resumen que recupere valores de varias filas en cualquier cantidad de hojas de trabajo, en cualquier cantidad de libros de la carpeta. Y queremos poder actualizar nuestra hoja de resumen en cualquier momento para capturar los valores actuales. Además, no queremos escribir ningún código VBA. Y, en el futuro, queremos poder actualizar nuestro resumen en menos de 10 segundos, ya que tenemos que actualizarlo todos los días.

¿Podemos hacer esto? Sí, claro. Exploraremos cómo lograr todo esto con una única consulta Get Transform. Hagámoslo.

Detalles

Lograremos nuestro objetivo con tres pasos básicos:

• Crear una consulta básica
• Personaliza la consulta
• Devolver los resultados

esto va a ser muy divertido!

Nota: Los pasos a continuación se presentan con Excel para Windows 2016. Si está utilizando una versión diferente de Excel, tenga en cuenta que es posible que las funciones presentadas no estén disponibles o que deba descargar e instalar el complemento Power Query.

Crear una consulta básica

Primero necesitamos crear una consulta que recupere una lista de todos los libros de la carpeta. Esto se puede hacer seleccionando el icono Datos Nueva consulta Desde archivo Desde carpeta . Excel muestra el cuadro de diálogo Carpeta (como se muestra a continuación) y puede escribir o Examinar para identificar la carpeta que contiene todos los libros de Excel.

Al hacer clic en Aceptar, se mostrará un cuadro de diálogo de vista previa que enumera los libros de trabajo en la carpeta. Haga clic en el botón Editar para abrir el Editor de consultas como se muestra a continuación.

En este punto, tenemos una consulta que recupera el contenido de la carpeta y proporciona una lista de todos los libros de la carpeta. Con esta consulta básica configurada, es hora de personalizarla.

Nota: si la carpeta contiene una combinación de tipos de archivos, puede filtrar la columna Extensión para conservar solo los archivos xlsx.

Personaliza la consulta

En este punto, tenemos una consulta que incluye una lista de los libros en la carpeta. Ahora necesitamos agregar una nueva columna para incluir cada hoja de trabajo en cada uno de los libros. Para hacer esto desde el Editor de consultas, seleccionamos el comando Agregar columna Agregar columna personalizada .

En el cuadro de diálogo resultante, ingresamos un nombre para nuestra nueva columna, algo como Hojas de cálculo estaría bien, y luego la fórmula:

=Excel.Workbook(Archivo.Contenido([Ruta de la carpeta][Nombre]))

Esto se muestra a continuación:

Haga clic en Aceptar para agregar la nueva columna a la consulta.

Es necesario ampliar nuestra nueva columna, Hojas. Podemos hacer esto haciendo clic en el pequeño icono en el encabezado de la columna, o en el comando Transformar Columna estructurada Expandir . Podemos expandir todas las columnas por ahora.

El editor de consultas ahora incluye todas las hojas y todos los libros, como se muestra a continuación.

Nota: si es necesario, aplique filtros para eliminar las filas que no sean necesarias.

A continuación, debemos incorporar el contenido de la hoja de las columnas B y C a la consulta. Entonces, expandimos la columna Sheets.Data. Hacemos esto haciendo clic en el pequeño ícono en el encabezado, o haciendo clic en el ícono Transformar Columna estructurada Expandir . Dado que la etiqueta Total está en la columna B y el Valor en la columna C, no es necesario incluir todas las columnas, simplemente podemos marcar las casillas Columna 2 y Columna 3.

Ahora nuestra consulta incluye los valores de las hojas de trabajo, como se muestra a continuación.

¡Ahora tenemos todos los datos que necesitamos! Sólo tenemos que hacer un poco de limpieza y eliminar las filas y columnas que no necesitamos.

Las únicas filas de consulta que debemos conservar son aquellas con la cadena de texto Total en la Columna 2. Entonces, aplicamos un filtro usando el menú desplegable para la Columna 2 y seleccionando Total. Esto deja solo las filas que incluyen los valores totales que buscamos.

A continuación, podemos eliminar las columnas que no queremos o no necesitamos que se devuelvan a nuestra hoja de resumen. Podemos seleccionar las columnas y usar el ícono Eliminar columnas para esto. Aquí, eliminé todas las columnas excepto las que contienen el nombre del libro, el nombre de la hoja de trabajo y el valor. La consulta actualizada se muestra a continuación.

¡Ahora todo el trabajo duro está hecho! Y podemos pasar al último paso.

Nota: gracias Ken Puls (gurú de Power Query) por tu publicación .

Devolver los resultados

Finalmente, podemos usar el comando Cerrar y Cargar para devolver los resultados de la consulta a nuestra hoja de resumen, como se muestra a continuación.

Increíble… ¡lo logramos! Y la mejor parte es que no necesitamos pasar por todos esos problemas la próxima vez que queramos actualizar el resumen. Simplemente podemos hacer clic derecho en la tabla de resultados y seleccionar Actualizar. Cuando lo hacemos, Excel vuelve a la carpeta e incluye automáticamente todos los archivos que se agregaron desde la última vez. Además, ¡incluye automáticamente cualquier hoja de trabajo nueva! Y recuperará los totales de todas las hojas, independientemente de cuántas haya en cada hoja o en qué fila se encuentren. ¡Guau!

Pero Jeff, vamos hombre, solo teníamos 3 archivos, podría haber copiado y pegado más rápido que eso. Bueno, tal vez, pero dado que este resumen debe actualizarse cada día, semana o mes, la actualización seguramente será más rápida el próximo período. Por curiosidad, copié estos tres archivos y los pegué, pegué, pegué y pegué, una y otra vez, hasta que tuve alrededor de 420 archivos de Excel en la carpeta. Luego presioné Actualizar. 6,76 segundos después, mi tabla de resumen incluía 1.680 filas de datos… ¡increíblemente increíble!

Si tiene otras opciones para realizar esta tarea, o algún otro consejo divertido para consultas de Obtener y Transformar, compártalo publicando un comentario a continuación… ¡gracias!

Recursos

• Archivos de datos de muestra (comprimidos): DataFiles