Como atualizo automaticamente um intervalo de tabela dinâmica?

Atualizar uma tabela dinâmica é uma dor, não é? Se você usa tabelas dinâmicas com frequência em seu trabalho, tenho certeza de que se identifica.

O problema é que toda vez que você adiciona novos dados à planilha de origem, você precisa atualizar o intervalo de origem da tabela dinâmica antes de atualizar sua tabela dinâmica.

Agora imagine que se você adicionar dados à sua planilha de origem todos os dias, precisará atualizar o intervalo de origem todos os dias.

E sempre que alterar o intervalo da tabela dinâmica é uma bagunça. Sim, isso mesmo, quanto mais frequentemente você adiciona dados, mais você precisa atualizar o intervalo de origem.

Portanto, a questão é que você precisa de um método para atualizar automaticamente o intervalo de origem ao adicionar novos dados.

NOTA : As tabelas dinâmicas fazem parte das HABILIDADES INTERMEDIÁRIAS DE EXCEL .

Aplicar tabela para ter intervalo de tabela dinâmica com atualização automática

Há alguns dias, perguntei a John Michaloudis sobre sua dica de mesa dinâmica de um milhão de dólares. Diz: Coloque seus dados de origem em uma tabela. Acredite em mim, esta é uma gorjeta de um milhão de dólares.

Ao aplicar uma tabela nos dados de origem, você não precisa alterar o intervalo de origem da sua tabela dinâmica repetidamente.

Cada vez que você adiciona novos dados, ele atualiza automaticamente o intervalo da tabela dinâmica.

Converta dados em tabela antes de criar uma tabela dinâmica

Sempre antes de criar uma tabela dinâmica, certifique-se de aplicar a tabela aos dados de origem usando as etapas a seguir.

1. Selecione uma das células em seus dados.
2. Use a tecla de atalho Ctrl + T ou navegue até → Inserir guia → Tabelas → Tabela.
3. Você receberá um pop-up com seu intervalo de dados atual.
   
4. Clique OK.
5. Agora, para criar uma tabela dinâmica, selecione qualquer célula em seus dados. Vá para → guia Design → Ferramentas → Resumir com tabela dinâmica.
   
6. Clique OK.

Agora, sempre que você adiciona novos dados à sua planilha de dados, o intervalo da tabela dinâmica é atualizado automaticamente e você só precisa atualizar sua tabela dinâmica.

Converta dados em tabela após criar uma tabela dinâmica

Se você já possui uma tabela dinâmica em sua planilha, poderá usar as etapas a seguir para converter sua fonte de dados em uma tabela.

1. Selecione uma das células da sua fonte de dados.
2. Use a tecla de atalho Ctrl + T ou navegue até → Inserir guia → Tabelas → Tabela.
3. Você receberá um pop-up com seu intervalo de dados atual.
4. Clique OK.
5. Agora, selecione uma das células em sua tabela dinâmica e vá para → Analisar → Dados → Editar fonte de dados → Editar fonte de dados (menu suspenso).
6. Você receberá um pop-up para selecionar novamente sua fonte de dados ou também poderá inserir o nome da tabela na entrada do intervalo.
   
7. Clique OK.

De agora em diante, sempre que você adicionar novos dados à planilha de origem, o intervalo da tabela dinâmica aumentará para atualizá-los automaticamente.

Crie um intervalo de tabela dinâmica dinâmica com a função OFFSET

A outra melhor maneira de atualizar automaticamente o intervalo da tabela dinâmica é usar um intervalo dinâmico.

O intervalo dinâmico pode se expandir automaticamente sempre que você adicionar novos dados à planilha de origem. Aqui estão as etapas para criar um intervalo dinâmico.

1. Vá para → Guia Fórmulas → Nomes Definidos → Gerenciador de Nomes.
2. Depois de clicar no gerenciador de nomes, você verá uma janela pop-up.
   
3. Na janela do Gerenciador de nomes, clique em Novo para criar um intervalo nomeado.
4. Na janela do novo nome, digite
   1. Um nome para sua nova linha. Eu uso o nome “SourceData”.
   2. Especifique a extensão do intervalo. Você pode especificar entre a planilha ou pasta de trabalho atual.
   3. Adicione um comentário para descrever seu intervalo nomeado. Insira a fórmula abaixo na barra de entrada “Consulte”.
      

       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))

5. No final, clique em OK.

Agora você tem faixa dinâmica para criar uma tabela dinâmica.

Tudo o que você precisa fazer é simplesmente criar uma tabela dinâmica com seus dados de origem e, em seguida, alterar a fonte com o intervalo nomeado usando o mesmo método que usei no primeiro método de tabelas.

Depois de adicionar novos dados à planilha de origem, basta atualizar a tabela dinâmica.

Como funciona essa fórmula?

Na fórmula acima, usei a função offset para criar uma faixa dinâmica.

Mencionei a célula A1 como ponto de partida e, sem mencionar linhas e colunas, especifiquei a altura e a largura do intervalo usando COUNTA.

COUNTA contará as células com valores da coluna A e da linha 1 e informará ao deslocamento para expandir sua altura e largura de acordo.

A única coisa que você precisa ter cuidado é que não haja nenhuma célula vazia entre a coluna A e a linha 1.

Atualizar tabela dinâmica usando código VBA

A maioria das pessoas gosta de usar códigos VBA. Então aqui está o código a ser usado para atualizar o intervalo da tabela dinâmica com VBA.

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

Coisas que você precisa alterar antes de usá-lo em sua pasta de trabalho.

1. Linha 13: Altere o nome da planilha de origem.
2. Linha 14: Altere o nome da planilha da tabela dinâmica.
3. Linha 17: Altere o nome da tabela dinâmica.

Se você ainda enfrentar algum problema ao usar este código, escreva para mim na caixa de comentários. Agora deixe-me mostrar como esse código funciona para que você possa modificá-lo facilmente de acordo com suas necessidades.

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

Na parte acima do código, especificamos a tabela dinâmica e as variáveis da planilha dos dados de origem. Você pode alterar o nome da planilha aqui.

 PivotName = "PivotTable2"

Na parte acima do código, insira o nome da tabela dinâmica na qual deseja usar este código.

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

A parte do código acima criará um intervalo dinâmico usando a célula A1 da planilha da fonte de dados.

Ele verificará a última coluna e a última linha com dados para criar um intervalo dinâmico. Cada vez que você executar esta macro, ela criará um novo intervalo dinâmico.

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

A parte acima do código atualizará a tabela dinâmica e exibirá uma mensagem ao usuário informando que a tabela dinâmica agora está atualizada.