Tabla dinámica en múltiples rangos de datos

Formulación del problema

Las tablas dinámicas son una de las herramientas más sorprendentes de Excel. Pero hasta ahora, lamentablemente, ninguna de las versiones de Excel puede hacer sobre la marcha algo tan simple y necesario como construir un resumen para varios rangos de datos iniciales ubicados, por ejemplo, en diferentes hojas o en diferentes tablas:

Antes de comenzar, aclaremos un par de puntos. A priori, creo que en nuestros datos se cumplen las siguientes condiciones:

• Las tablas pueden tener cualquier número de filas con cualquier dato, pero deben tener el mismo encabezado.
• No debe haber datos adicionales en las hojas con tablas de origen. Una hoja, una tabla. Para controlar, te aconsejo que uses un atajo de teclado Ctrl+Fin, que lo lleva a la última celda utilizada en la hoja de trabajo. Idealmente, esta debería ser la última celda de la tabla de datos. Si al hacer clic en Ctrl+Fin se resalta cualquier celda vacía a la derecha o debajo de la tabla; elimine estas columnas vacías a la derecha o filas debajo de la tabla después de la tabla y guarde el archivo.

Método 1: Cree tablas para un pivote usando Power Query

A partir de la versión 2010 para Excel, hay un complemento gratuito de Power Query que puede recopilar y transformar cualquier dato y luego proporcionarlo como fuente para crear una tabla dinámica. Resolver nuestro problema con la ayuda de este complemento no es nada difícil.

Primero, creemos un nuevo archivo vacío en Excel: el ensamblaje se realizará en él y luego se creará una tabla dinámica en él.

Luego en la pestaña Datos (si tiene Excel 2016 o posterior) o en la pestaña Power Query (si tiene Excel 2010-2013) seleccione el comando Crear Consulta – Desde Archivo – Excel (Obtener datos — Desde archivo — Excel) y especifique el archivo de origen con las tablas que se van a recopilar:

En la ventana que aparece, seleccione cualquier hoja (no importa cuál) y presione el botón de abajo Cambios (Edit):

La ventana del editor de consultas de Power Query debería abrirse en la parte superior de Excel. En el lado derecho de la ventana en el panel Parámetros de solicitud elimine todos los pasos creados automáticamente excepto el primero – Fuente (Fuente):

Ahora vemos una lista general de todas las hojas. Si además de las hojas de datos hay otras hojas adicionales en el archivo, en este paso nuestra tarea es seleccionar solo aquellas hojas de las que se debe cargar la información, excluyendo todas las demás usando el filtro en el encabezado de la tabla:

Eliminar todas las columnas excepto la columna Datoshaciendo clic derecho en un encabezado de columna y seleccionando Eliminar otras columnas (Eliminar otras columnas):

Luego puede expandir el contenido de las tablas recopiladas haciendo clic en la flecha doble en la parte superior de la columna (casilla de verificación Utilice el nombre de la columna original como prefijo Puedes apagarlo):

Si hizo todo correctamente, en este punto debería ver el contenido de todas las tablas recopiladas una debajo de la otra:

Queda por subir la primera fila al encabezado de la tabla con el botón Usar la primera línea como encabezados (Utilice la primera fila como encabezados) de la pestaña. Inicio (Home) y elimine los encabezados de tabla duplicados de los datos usando un filtro:

Guarda todo lo hecho con el comando Cerrar y cargar – Cerrar y cargar en… (Cerrar y cargar — Cerrar y cargar a…) de la pestaña. Inicio (Home), y en la ventana que se abre selecciona la opción Solo conexión (Solo conexión):

Todo. Solo queda construir un resumen. Para hacer esto, vaya a la pestaña Insertar – Tabla dinámica (Insertar: tabla dinámica), elige la opción Usar fuente de datos externa (Usar fuente de datos externa)y luego haciendo clic en el botón Seleccione la conexión, Nuestra solicitud. La creación y configuración adicional del pivote se produce de una manera completamente estándar arrastrando los campos que necesitamos al área de filas, columnas y valores:

Si los datos de origen cambian en el futuro o se agregan algunas hojas de almacenamiento más, entonces será suficiente actualizar la consulta y nuestro resumen usando el comando Refrescar todo de la pestaña. Datos (Datos — Actualizar todo).

Método 2. Unimos tablas con el comando UNION SQL en una macro

Otra solución a nuestro problema está representada por esta macro, que crea un conjunto de datos (caché) para la tabla dinámica usando el comando UNIDAD Lenguaje de consultas SQL. Este comando combina tablas de todas las especificadas en la matriz Nombres de hojas hojas del libro en una sola tabla de datos. Es decir, en lugar de copiar y pegar físicamente rangos de distintas hojas a una sola, hacemos lo mismo en la memoria RAM del ordenador. Luego, la macro agrega una nueva hoja con el nombre dado (variable NombreHojaResultados) y crea un resumen completo (!) Basado en el caché recopilado.

Para usar una macro, use el botón de Visual Basic en la pestaña revelador (Desarrollador) o atajo de teclado otro+F11. Luego insertamos un nuevo módulo vacío a través del menú Insertar – Módulo y copiar el siguiente código allí:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'nombre de hoja donde se mostrará el pivote resultante ResultSheetName = "Pivot" 'una matriz de hoja nombres con tablas de origen SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'formamos un caché para las tablas de las hojas de SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Siguiente i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Terminar con 'volver a crear la hoja para mostrar la tabla dinámica resultante En caso de error Reanudar siguiente aplicación.DisplayAlerts = False Hojas de trabajo (ResultSheetName). Eliminar Establecer wsPivot = Hojas de trabajo. t. Name = ResultSheetName 'muestra el resumen de la memoria caché generada en esta hoja objPivotCache = Nothing Range("A3").Seleccione End With End Sub    

La macro terminada se puede ejecutar con un atajo de teclado otro+F8 o el botón Macros en la pestaña revelador (Desarrollador — Macros).

Contras de este enfoque:

• Los datos no se actualizan porque la memoria caché no tiene conexión con las tablas de origen. Si cambia los datos de origen, debe ejecutar la macro nuevamente y generar el resumen nuevamente.
• Al cambiar el número de hojas, es necesario editar el código de la macro (array Nombres de hojas).
  

Pero al final obtenemos una verdadera tabla dinámica completa, construida sobre varios rangos de diferentes hojas:

Voilà!

Nota técnica: Si obtiene un error como "Proveedor no registrado" al ejecutar la macro, lo más probable es que tenga una versión de Excel de 64 bits o una versión incompleta de Office instalada (sin acceso). Para solucionar la situación, reemplace el fragmento en el código de la macro:

	 Proveedor = Microsoft.Jet.OLEDB.4.0;  

para:

	Proveedor=Microsoft.ACE.OLEDB.12.0;  

Y descargue e instale el motor de procesamiento de datos gratuito de Access desde el sitio web de Microsoft: Microsoft Access Database Engine 2010 Redistributable

Método 3: Asistente de consolidación de tablas dinámicas de versiones anteriores de Excel

Este método está un poco desactualizado, pero vale la pena mencionarlo. Hablando formalmente, en todas las versiones hasta 2003 inclusive, había una opción en el asistente de tablas dinámicas para "crear un pivote para varios rangos de consolidación". Sin embargo, un informe construido de esta manera, desafortunadamente, solo será una lamentable apariencia de un resumen real completo y no es compatible con muchos de los "chips" de las tablas dinámicas convencionales:

En dicho pivote, no hay encabezados de columna en la lista de campos, no hay una configuración de estructura flexible, el conjunto de funciones utilizadas es limitado y, en general, todo esto no es muy similar a una tabla dinámica. Quizás por eso, a partir de 2007, Microsoft eliminó esta función del cuadro de diálogo estándar al crear informes de tablas dinámicas. Ahora esta función solo está disponible a través de un botón personalizado Asistente para tablas dinámicas(Asistente de tablas dinámicas), que, si lo desea, se puede agregar a la barra de herramientas de acceso rápido a través de Archivo – Opciones – Personalizar barra de herramientas de acceso rápido – Todos los comandos (Archivo — Opciones — Personalizar barra de herramientas de acceso rápido — Todos los comandos):

Después de hacer clic en el botón agregado, debe seleccionar la opción adecuada en el primer paso del asistente:

Y luego, en la siguiente ventana, seleccione cada rango a su vez y agréguelo a la lista general:

Pero, de nuevo, este no es un resumen completo, así que no esperes demasiado de él. Puedo recomendar esta opción solo en casos muy simples.

• Creación de informes con tablas dinámicas
• Configurar cálculos en tablas dinámicas
• Qué son las macros, cómo usarlas, dónde copiar el código VBA, etc.
• Recopilación de datos de varias hojas a una (complemento PLEX)