Táboa dinámica en varios intervalos de datos

Formulación do problema

As táboas dinámicas son unha das ferramentas máis sorprendentes de Excel. Pero ata agora, por desgraza, ningunha das versións de Excel pode facer algo tan sinxelo e necesario sobre a marcha como construír un resumo para varios intervalos de datos iniciais situados, por exemplo, en diferentes follas ou en diferentes táboas:

Antes de comezar, imos aclarar un par de puntos. A priori, creo que nos nosos datos se cumpren as seguintes condicións:

• As táboas poden ter calquera número de filas con calquera dato, pero deben ter a mesma cabeceira.
• Non debería haber datos adicionais nas follas con táboas fonte. Unha folla - unha mesa. Para controlar, recoméndoche que uses un atallo de teclado Ctrl+final, que o move á última cela utilizada na folla de traballo. Idealmente, esta debería ser a última cela da táboa de datos. Se ao facer clic en Ctrl+final Resáltase calquera cela baleira á dereita ou debaixo da táboa: elimine estas columnas baleiras á dereita ou as filas debaixo da táboa despois da táboa e garda o ficheiro.

Método 1: crear táboas para un pivote usando Power Query

A partir da versión de 2010 para Excel, hai un complemento gratuíto de Power Query que pode recoller e transformar calquera dato e, a continuación, proporcionalo como fonte para construír unha táboa dinámica. Resolver o noso problema coa axuda deste complemento non é nada difícil.

En primeiro lugar, imos crear un novo ficheiro baleiro en Excel: a montaxe terá lugar nel e, a continuación, crearase unha táboa dinámica nel.

Despois na pestana Data (se tes Excel 2016 ou posterior) ou na pestana Consulta de enerxía (se tes Excel 2010-2013) selecciona o comando Crear consulta - Desde o ficheiro - Excel (Obter datos — Do ficheiro — Excel) e especifique o ficheiro fonte coas táboas que se van recoller:

Na xanela que aparece, selecciona calquera folla (non importa cal) e pulsa o botón de abaixo Cambiar (Editar):

A xanela do Editor de consultas de Power Query debería abrirse enriba de Excel. No lado dereito da xanela do panel Parámetros de solicitude eliminar todos os pasos creados automaticamente excepto o primeiro - fonte (Fonte):

Agora vemos unha lista xeral de todas as follas. Se ademais das follas de datos hai outras follas laterais no ficheiro, neste paso a nosa tarefa é seleccionar só aquelas follas das que hai que cargar a información, excluíndo todas as demais usando o filtro da cabeceira da táboa:

Elimina todas as columnas excepto a columna Datafacendo clic co botón dereito nun título de columna e seleccionando Elimina outras columnas (Quitar outras columnas):

A continuación, pode ampliar o contido das táboas recollidas facendo clic na dobre frecha situada na parte superior da columna (caixa de verificación Use o nome orixinal da columna como prefixo podes desactivalo):

Se fixeches todo correctamente, neste momento deberías ver o contido de todas as táboas recollidas unha debaixo da outra:

Queda por subir a primeira fila á cabeceira da táboa co botón Use a primeira liña como cabeceiras (Utiliza a primeira fila como cabeceiras) aba casa (Inicio) e elimine os encabezados de táboa duplicados dos datos mediante un filtro:

Garda todo o feito co comando Pechar e cargar: pechar e cargar en... (Pechar e cargar — Pechar e cargar en...) aba casa (Inicio), e na xanela que se abre, seleccione a opción Só conexión (só conexión):

Todo. Só queda facer un resumo. Para facelo, vai á pestana Inserir - Táboa dinámica (Inserir — Táboa dinámica), escolla a opción Usa unha fonte de datos externa (Usar fonte de datos externa)e despois premendo no botón Seleccione conexión, a nosa petición. A creación e configuración posterior do pivote prodúcese dun xeito completamente estándar arrastrando os campos que necesitamos á área de filas, columnas e valores:

Se os datos de orixe cambian no futuro ou se engaden algunhas follas de tenda máis, será suficiente actualizar a consulta e o noso resumo usando o comando Actualiza todo aba Data (Datos — Actualizar todo).

Método 2. Unimos táboas co comando UNION SQL nunha macro

Outra solución ao noso problema está representada por esta macro, que crea un conxunto de datos (caché) para a táboa dinámica mediante o comando UNIDADE Linguaxe de consulta SQL. Este comando combina táboas de todas as especificadas na matriz Nomes das follas follas do libro nunha única táboa de datos. É dicir, en lugar de copiar e pegar fisicamente intervalos de diferentes follas a unha, facemos o mesmo na memoria RAM do ordenador. A continuación, a macro engade unha nova folla co nome dado (variable Nome da folla de resultados) e crea un resumo completo (!) sobre el a partir da caché recollida.

Para usar unha macro, use o botón Visual Basic da pestana revelador (Desenvolvedor) ou atallo de teclado alt+F11. Despois inserimos un novo módulo baleiro a través do menú Inserir – Módulo e copia alí o seguinte código:

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 'nome da folla onde se mostrará o pivote resultante ResultSheetName = "Matriz da folla de resultados" ' nomes con táboas de orixe SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'formamos unha caché para táboas a partir de follas de SheetsNames Con ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Seguinte i Set objRS = CreateObject("ADODB.Recordset") objRS .Abrir Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Finalizar con 're-crear a folla para mostrar a táboa dinámica resultante En caso de erro Reanudar a seguinte aplicación.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'mostra o resumo da memoria caché xerada nesta folla Establecer objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Establecer objPivotCache.Recordset = objRS Establecer objRS = Nada con wsPivot objPivotCache.TablewPivotCache.=CreatePivotCache.=TablewSetPivot. objPivotCache = Nothing Range("A3").Select End With End Sub    

A macro rematada pódese executar cun atallo de teclado alt+F8 ou o botón Macros da pestana revelador (Desenvolvedor - Macros).

Contras deste enfoque:

• Os datos non se actualizan porque a caché non ten conexión coas táboas de orixe. Se cambias os datos de orixe, debes executar de novo a macro e construír de novo o resumo.
• Ao cambiar o número de follas, é necesario editar o código macro (matriz Nomes das follas).
  

Pero ao final obtemos unha verdadeira táboa dinámica completa, construída en varias gamas de diferentes follas:

Voilà!

Nota técnica: se recibe un erro como "Proveedor non rexistrado" ao executar a macro, o máis probable é que teña unha versión de Excel de 64 bits ou que teña instalada unha versión incompleta de Office (sen acceso). Para solucionar a situación, substitúe o fragmento no código de macro:

	 Provedor=Microsoft.Jet.OLEDB.4.0;  

a:

	Provedor=Microsoft.ACE.OLEDB.12.0;  

E descarga e instala o motor de procesamento de datos gratuíto desde Access desde o sitio web de Microsoft: Microsoft Access Database Engine 2010 Redistributable

Método 3: Consolide o asistente de táboa dinámica das versións antigas de Excel

Este método está un pouco anticuado, pero aínda vale a pena mencionalo. Falando formalmente, en todas as versións ata 2003 incluído, había unha opción no asistente de táboa dinámica para "construír un pivote para varios intervalos de consolidación". Non obstante, un informe construído deste xeito, desafortunadamente, só será unha lamentable apariencia dun resumo completo e real e non admite moitas das "fichas" das táboas dinámicas convencionais:

En tal pivote, non hai títulos de columna na lista de campos, non hai unha configuración de estrutura flexible, o conxunto de funcións utilizadas é limitado e, en xeral, todo isto non é moi semellante a unha táboa dinámica. Quizais sexa por iso que, a partir de 2007, Microsoft eliminou esta función do diálogo estándar ao crear informes de táboa dinámica. Agora esta función só está dispoñible a través dun botón personalizado Asistente de táboa dinámica(Asistente de táboa dinámica), que, se o desexa, pódese engadir á barra de ferramentas de acceso rápido mediante Ficheiro - Opcións - Personalizar barra de ferramentas de acceso rápido - Todos os comandos (Ficheiro — Opcións — Personalizar a barra de ferramentas de acceso rápido — Todos os comandos):

Despois de facer clic no botón engadido, debes seleccionar a opción adecuada no primeiro paso do asistente:

E despois, na seguinte xanela, seleccione cada intervalo á súa vez e engádeo á lista xeral:

Pero, de novo, este non é un resumo completo, así que non esperes demasiado del. Podo recomendar esta opción só en casos moi sinxelos.

• Creación de informes con táboas dinámicas
• Configura cálculos en táboas dinámicas
• Que son as macros, como usalas, onde copiar o código VBA, etc.
• Recollida de datos de varias follas a unha (complemento PLEX)