Tabela dinâmica em vários intervalos de dados

Formulação do problema

As tabelas dinâmicas são uma das ferramentas mais incríveis do Excel. Mas até agora, infelizmente, nenhuma das versões do Excel pode fazer uma coisa tão simples e necessária em tempo real como construir um resumo para vários intervalos de dados iniciais localizados, por exemplo, em diferentes planilhas ou em diferentes tabelas:

Antes de começarmos, vamos esclarecer alguns pontos. A priori, acredito que as seguintes condições sejam atendidas em nossos dados:

• As tabelas podem ter qualquer número de linhas com qualquer dado, mas devem ter o mesmo cabeçalho.
• Não deve haver dados extras nas planilhas com tabelas de origem. Uma folha – uma mesa. Para controlar, aconselho você a usar um atalho de teclado Ctrl+Terminar, que o move para a última célula usada na planilha. Idealmente, esta deve ser a última célula na tabela de dados. Se ao clicar em Ctrl+Terminar qualquer célula vazia à direita ou abaixo da tabela é destacada – exclua essas colunas vazias à direita ou linhas abaixo da tabela após a tabela e salve o arquivo.

Método 1: criar tabelas para um pivô usando o Power Query

A partir da versão 2010 para Excel, há um suplemento Power Query gratuito que pode coletar e transformar quaisquer dados e fornecê-los como fonte para criar uma tabela dinâmica. Resolver nosso problema com a ajuda deste suplemento não é nada difícil.

Primeiro, vamos criar um novo arquivo vazio no Excel – a montagem ocorrerá nele e, em seguida, uma tabela dinâmica será criada nele.

Então na aba Data (se você tiver o Excel 2016 ou posterior) ou na guia Consulta de energia (se você tiver o Excel 2010-2013) selecione o comando Criar Consulta – Do Arquivo – Excel (Obter dados — Do arquivo — Excel) e especifique o arquivo de origem com as tabelas a serem coletadas:

Na janela que aparece, selecione qualquer planilha (não importa qual) e pressione o botão abaixo Mudar (Editar):

A janela do Editor de Consultas do Power Query deve ser aberta na parte superior do Excel. No lado direito da janela no painel Solicitar Parâmetros exclua todas as etapas criadas automaticamente, exceto a primeira – fonte (Fonte):

Agora vemos uma lista geral de todas as folhas. Se, além das folhas de dados, houver outras folhas laterais no arquivo, nesta etapa nossa tarefa é selecionar apenas as folhas das quais as informações precisam ser carregadas, excluindo todas as outras usando o filtro no cabeçalho da tabela:

Excluir todas as colunas, exceto coluna Dataclicando com o botão direito do mouse em um cabeçalho de coluna e selecionando Excluir outras colunas (Remover outras colunas):

Você pode então expandir o conteúdo das tabelas coletadas clicando na seta dupla no topo da coluna (caixa de seleção Use o nome da coluna original como prefixo você pode desativá-lo):

Se você fez tudo corretamente, neste ponto você deve ver o conteúdo de todas as tabelas coletadas uma abaixo da outra:

Resta elevar a primeira linha para o cabeçalho da tabela com o botão Use a primeira linha como cabeçalhos (Use a primeira linha como cabeçalhos) aba INÍCIO (Home) e remova os cabeçalhos de tabela duplicados dos dados usando um filtro:

Salve tudo feito com o comando Fechar e carregar – Fechar e carregar… (Fechar e carregar — Fechar e carregar para…) aba INÍCIO (Home), e na janela que se abre, selecione a opção Somente conexão (Somente conexão):

Tudo. Resta apenas construir um resumo. Para fazer isso, vá para a guia Inserir – Tabela Dinâmica (Inserir - Tabela Dinâmica), escolha a opção Usar fonte de dados externa (Use fonte de dados externa)e depois clicando no botão Selecione conexão, nosso pedido. A criação e configuração adicional do pivô ocorre de maneira completamente padrão, arrastando os campos que precisamos para a área de linhas, colunas e valores:

Se os dados de origem forem alterados no futuro ou forem adicionadas mais algumas planilhas de armazenamento, será suficiente atualizar a consulta e nosso resumo usando o comando Atualize tudo aba Data (Dados — Atualizar tudo).

Método 2. Unimos tabelas com o comando UNION SQL em uma macro

Outra solução para o nosso problema é representada por esta macro, que cria um conjunto de dados (cache) para a tabela dinâmica usando o comando UNIDADE Linguagem de consulta SQL. Este comando combina todas as tabelas especificadas no array Nomes de folhas folhas do livro em uma única tabela de dados. Ou seja, em vez de copiar e colar fisicamente intervalos de planilhas diferentes em uma, fazemos o mesmo na memória RAM do computador. Então a macro adiciona uma nova planilha com o nome dado (variável Nome da folha de resultados) e cria um resumo completo (!) com base no cache coletado.

Para usar uma macro, use o botão Visual Basic na guia revelador (Desenvolvedor) ou atalho de teclado outro+F11. Em seguida, inserimos um novo módulo vazio através do menu Inserir - Módulo e copie o seguinte código lá:

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 planilha onde o pivô resultante será exibido ResultSheetName = "Pivot" 'uma matriz de planilhas nomes com tabelas de origem SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'formamos um cache para tabelas de planilhas de SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Para i = LBound (SheetsNames) Para UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Em seguida, 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 ) End With 'recriar a planilha para exibir a tabela dinâmica resultante Em Erro Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'exibe o resumo do cache gerado nesta planilha Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range("A3").Selecione End With End Sub    

A macro finalizada pode ser executada com um atalho de teclado outro+F8 ou o botão Macros na guia revelador (Desenvolvedor — Macros).

Contras desta abordagem:

• Os dados não são atualizados porque o cache não tem conexão com as tabelas de origem. Se você alterar os dados de origem, deverá executar a macro novamente e criar o resumo novamente.
• Ao alterar o número de folhas, é necessário editar o código da macro (array Nomes de folhas).
  

Mas, no final, obtemos uma tabela dinâmica real completa, construída em vários intervalos de planilhas diferentes:

Voilà!

Nota técnica: se você receber um erro como "Provedor não registrado" ao executar a macro, provavelmente você tem uma versão de 64 bits do Excel ou uma versão incompleta do Office instalada (sem Access). Para corrigir a situação, substitua o fragmento no código da macro:

	 Fornecedor = Microsoft.Jet.OLEDB.4.0;  

para:

	Provedor=Microsoft.ACE.OLEDB.12.0;  

E baixe e instale o mecanismo de processamento de dados gratuito do Access no site da Microsoft – Microsoft Access Database Engine 2010 Redistributable

Método 3: Consolidar o Assistente de tabela dinâmica de versões antigas do Excel

Este método está um pouco desatualizado, mas ainda vale a pena mencionar. Formalmente falando, em todas as versões até 2003 inclusive, havia uma opção no PivotTable Wizard para “construir um pivô para vários intervalos de consolidação”. No entanto, um relatório construído dessa maneira, infelizmente, será apenas uma aparência lamentável de um resumo real e completo e não suporta muitos dos “chips” das tabelas dinâmicas convencionais:

Nesse pivô, não há cabeçalhos de coluna na lista de campos, não há configuração de estrutura flexível, o conjunto de funções usado é limitado e, em geral, tudo isso não é muito semelhante a uma tabela dinâmica. Talvez seja por isso que, a partir de 2007, a Microsoft removeu essa função da caixa de diálogo padrão ao criar relatórios de tabela dinâmica. Agora este recurso só está disponível através de um botão personalizado Assistente de tabela dinâmica(Assistente de tabela dinâmica), que, se desejado, pode ser adicionado à Barra de Ferramentas de Acesso Rápido via Arquivo – Opções – Personalizar Barra de Ferramentas de Acesso Rápido – Todos os Comandos (Arquivo — Opções — Personalizar Barra de Ferramentas de Acesso Rápido — Todos os Comandos):

Depois de clicar no botão adicionado, você precisa selecionar a opção apropriada na primeira etapa do assistente:

E então, na próxima janela, selecione cada intervalo e adicione-o à lista geral:

Mas, novamente, este não é um resumo completo, então não espere muito dele. Posso recomendar esta opção apenas em casos muito simples.

• Criando relatórios com tabelas dinâmicas
• Configurar cálculos em tabelas dinâmicas
• O que são macros, como usá-las, onde copiar o código VBA, etc.
• Coleta de dados de várias planilhas para uma (complemento PLEX)