Tabela Dinâmica “superdinâmica”
Tornando uma Tabela Dinâmica “superdinâmica”
Apesar do recurso do Excel se chamar tabela dinâmica, ela não é tão dinâmica assim, pois a tabela após ser criada ela fica estática, dependendo da ação do usuário para ser atualizada, tanto em termos de alteração de valores na tabela base como alteração do intervalo de fonte de dados. É comum trabalhar com tabela dinâmica num banco de dados que vai aumentando periodicamente, e com isso há a necessidade de alterar manualmente o intervalo de dados da tabela dinâmica para que ela calcule todos os dados do banco.
O intuito deste artigo é mostrar como tornar o intervalo de dados da tabela dinâmica automático utilizando somente a interação do Excel sem VBA, e também mostrar como fazer com que a tabela atualize os valores automaticamente com um código simples em VBA associado a um evento “Activate” da planilha.
Para ilustrar, vamos utilizar como exemplo um banco de dados simples de vendas, com 3 vendedores e dois produtos:
Com este banco, vamos montar a tabela dinâmica com a configuração abaixo com uma caixa de segmentação com a data de venda, selecionando o intervalo “BD!A1:G13” :
Uma alternativa para resolver a questão da inclusão de novas linhas no BD refletindo automaticamente na tabela dinâmica, seria utilizar o intervalo referenciando somente as colunas, no caso do exemplo ficaria “BD!A:G”, porém isso gera uma linha chamada “(vazio)” desnecessária na tabela e também na caixa de segmentação de dados. Veja abaixo a imagem com o intervalo de dados configurado em “BD!A:G” :
Neste caso, na tabela dinâmica até seria possível ocultar o campo vazio, mas na caixa de segmentação não. Por isso vou demonstrar uma forma de configurar o intervalo dinamicamente através da função INDIRETO.
Uso da função INDIRETO para definir o intervalo da tabela dinâmica
A função retorna uma referência indicada por um valor de texto, então temos que adicionar um nome no gerenciador de nomes com a referência desejada para o intervalo da tabela dinâmica. No caso do exemplo, o intervalo desejado é “BD!A1:G13”, sendo que o que sofre variação com a inclusão de mais linhas é o último número (“BD!A1:G13”) que será então representado através da função CONT.VALORES dentro da função INDIRETO, ficando então a fórmula:
=INDIRETO("BD!A1:G"&CONT.VALORES(BD!$A:$A))
Utilizei a Coluna A na função CONT.VALORES, pois esta contagem sempre retornará a quantidade de linhas do banco de dados, servindo então como uma variável na nossa fórmula. Essa fórmula deve ser adicionada no gerenciador de nomes conforme imagem:
Com este nome criado, podemos realizar agora a substituição do intervalo de dados da nossa tabela dinâmica. Para isso basta clicar sobre a tabela dinâmica, ir à aba “Ferramentas de tabela dinâmica” e então no botão “Alterar fonte de dados”. Na janela que surgir, basta clicar F3 que surgir a janela com as opções de nomes criados no gerenciador de nomes.
Agora a tabela dinâmica está com os intervalo de dados automatizado e sem o inconveniente (vazio) na tabela. Veja:
Automatizar a atualização da tabela através do VBA
Por fim, vamos criar uma linha de código no VBA para realizar a atualização da tabela toda a vez que entrarmos na planilha onde está a tabela dinâmica. Aperte ALT+F11 para abrir o Visual Basic.
Como eu nomeei a tabela dinâmica como “Tabela Vendas”, o código no módulo da planilha onde está a tabela ficará:
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("Tabela Vendas").PivotCache.Refresh
*End Sub
*
Salve o arquivo como pasta de trabalho habilitada para macro do Excel para poder ter esta funcionalidade sempre que abrir o arquivo.
Deste modo teremos uma tabela dinâmica “superdinâmica”, pois o intervalo de dados será ajustado automaticamente com a inclusão de novos registros e também teremos a certeza que a tabela está sempre atualizada, pois sempre que entrarmos na planilha da tabela dinâmica, o código de atualização será executado.