Neste artigo, aprenderemos como atualizar automaticamente os dados da tabela dinâmica no Excel.
Cenário:
Como todos sabemos, sempre que fazemos alterações nos dados de origem de uma tabela dinâmica, isso não reflete imediatamente na tabela dinâmica. Precisamos atualizar as tabelas dinâmicas para ver as alterações sempre que abrir uma pasta de trabalho do Excel. E se você enviar um arquivo atualizado sem atualizar as tabelas dinâmicas, poderá ficar constrangido. Então, aprenda aqui como encontrar a opção de atualização ao usar a tabela dinâmica
Atualizar dados ao abrir um arquivo no Excel
Primeiro crie uma tabela dinâmica e, a seguir, clique com o botão direito em qualquer célula da tabela dinâmica.
Vá para as opções da tabela dinâmica> guia Dados> Marque a caixa que diz Atualizar dados ao abrir um arquivo
Isso habilitará a atualização automática dos dados sempre que o arquivo for aberto.
Exemplo :
Tudo isso pode ser confuso de entender. Vamos entender como usar a função usando um exemplo. Aqui, temos alguns dados e precisamos primeiro criar uma tabela dinâmica e, em seguida, encontrar as opções para habilitar as tabelas dinâmicas de atualização automática.
Crie uma tabela dinâmica e, a seguir, clique com o botão direito em qualquer célula da tabela dinâmica, conforme mostrado abaixo.
Selecione as Opções da tabela dinâmica e isso abrirá uma caixa de diálogo Opções da tabela PIvot.
Selecione a guia de dados e marque a caixa que diz Atualizar dados ao abrir o arquivo. Você pode fazer isso sem abrir e fechar o arquivo usando o VBA.
Usando VBA
Então, aqui, aprenderemos como atualizar automaticamente uma tabela dinâmica usando o VBA. Dessa forma é mais fácil do que você imaginou.
Esta é a sintaxe simples para atualizar automaticamente as tabelas dinâmicas na pasta de trabalho.
| 'Código no objeto da planilha de dados fonte
Sub-planilha privada_Deactivate () sheetname_of_pivot_table.PivotTables ("pivot_table_name") .PivotCache.Refresh End Sub |
O que são Pivot Caches?
Cada tabela dinâmica armazena os dados no cache dinâmico. É por isso que o pivot é capaz de mostrar dados anteriores. Quando atualizamos as tabelas dinâmicas, ele atualiza o cache com novos dados de origem para refletir as alterações na tabela dinâmica.
Portanto, precisamos apenas de uma macro para atualizar o cache das tabelas dinâmicas. Faremos isso usando um evento de planilha para que não tenhamos que executar a macro manualmente.
Onde codificar para atualizar automaticamente as tabelas dinâmicas?
Se seus dados de origem e tabelas dinâmicas estiverem em planilhas diferentes, o código do VBA deve ir na planilha de dados de origem.
Aqui, usaremos o evento Worksheet_SelectionChange. Isso fará com que o código seja executado sempre que mudarmos da planilha de dados de origem para outra planilha. Explicarei mais tarde porque usei este evento.
Aqui, tenho dados de origem na planilha2 e tabelas dinâmicas na planilha1.
Abra o VBE usando as teclas CTRL + F11. No explorador de projetos, você pode ver três objetos, Planilha1, Planilha2 e a pasta de trabalho.
Como a Planilha2 contém os dados de origem, clique duas vezes no objeto da planilha2.
Agora você pode ver dois menus suspensos no topo da área de código. Na primeira lista suspensa, selecione a planilha. E no segundo menu suspenso, selecione Desativar. Isso irá inserir um subnome vazio Worksheet_Deactivate. Nosso código será escrito neste sub. Quaisquer linhas escritas neste sub, são executadas assim que o usuário muda desta planilha para qualquer outra planilha.
Na folha 1, tenho duas tabelas dinâmicas. Quero atualizar apenas uma tabela dinâmica. Para isso, preciso saber o nome da tabela dinâmica. Para saber o nome de qualquer tabela dinâmica, selecione qualquer célula dessa tabela dinâmica e vá para a guia de análise da tabela dinâmica. No lado esquerdo, você verá o nome da tabela dinâmica. Você também pode alterar o nome da tabela dinâmica aqui.
Agora que sabemos o nome da tabela dinâmica, podemos escrever uma linha simples para atualizá-la.
| Sub-planilha privada_Deactivate ()
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub |
E está feito.
Agora, sempre que você alternar dos dados de origem, este código vba será executado para atualizar a tabela dinâmica1. Como você pode ver no gif abaixo.
Como atualizar todas as tabelas dinâmicas da pasta de trabalho?
No exemplo acima, queríamos apenas atualizar uma tabela dinâmica específica. Mas se você deseja atualizar todas as tabelas dinâmicas em uma pasta de trabalho, você só precisa fazer pequenas alterações em seu código.
| Sub-planilha privada_Deactivate ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Para cada pc neste livro de trabalho.PivotCaches pc.Refresh Próximo pc End Sub |
Neste código, estamos usando um loop For para percorrer cada cache de pivô na pasta de trabalho. O objeto ThisWorkbook contém todos os caches dinâmicos. Para acessá-los, usamos ThisWorkbook.PivotCaches.
Por que usar o evento Worksheet_Deactivate?
Se você quiser atualizar a tabela dinâmica assim que qualquer alteração for feita nos dados de origem, você deve usar o evento Worksheet_Change. Mas eu não recomendo. Isso fará com que sua pasta de trabalho execute o código toda vez que você fizer qualquer alteração na planilha. Você pode ter que fazer centenas de mudanças antes de ver o resultado. Mas o Excel atualizará a tabela dinâmica a cada mudança. Isso levará ao desperdício de tempo e recursos de processamento. Portanto, se você tiver tabelas dinâmicas e dados em planilhas diferentes, é melhor usar o evento de desativação da planilha. Ele permite que você finalize seu trabalho. Depois de alternar para as planilhas de tabela dinâmica para ver as alterações, isso corrige as alterações.
Se você tiver tabelas dinâmicas e dados de origem na mesma planilha e quiser que as tabelas dinâmicas se atualizem automaticamente, você pode usar o evento Worksheet_Change.
| Sub-planilha privada_Change (ByVal Target As Range)
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub |
Como atualizar tudo nas pastas de trabalho quando uma alteração é feita nos dados de origem?
Se você deseja atualizar tudo em uma pasta de trabalho (gráficos, tabelas dinâmicas, fórmulas, etc.), você pode usar o comando ThisWorkbook.RefreshAll.
| Sub-planilha privada_Change (ByVal Target As Range)
ThisWorkbook.RefreshAll End Sub |
Observação : O código não altera a fonte de dados. Portanto, se você adicionar dados abaixo dos dados de origem, esse código não incluirá esses dados automaticamente. Você pode usar tabelas do Excel para armazenar dados de origem. Se você não quiser usar tabelas, podemos usar o VBA para incluir novos dados também. Vamos aprender no próximo tutorial.
Espero que este artigo sobre Como atualizar automaticamente os dados da tabela dinâmica no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
Como atualizar dinamicamente o intervalo da fonte de dados da tabela dinâmica no Excel : Para alterar dinamicamente o intervalo de dados de origem das tabelas dinâmicas, usamos caches dinâmicos. Essas poucas linhas podem atualizar dinamicamente qualquer tabela dinâmica, alterando o intervalo de dados de origem. No VBA, use objetos de tabelas dinâmicas conforme mostrado abaixo …
Executar macro se houver alguma alteração feita na folha no intervalo especificado : Em suas práticas de VBA, você teria a necessidade de executar macros quando um determinado intervalo ou célula fosse alterado. Nesse caso, para executar macros quando uma alteração é feita em um intervalo de destino, usamos o evento de alteração.
Executar macro quando qualquer alteração for feita na planilha : Então, para executar sua macro sempre que a planilha for atualizada, usamos os Eventos de Planilha do VBA.
Código VBA mais simples para destacar a linha e a coluna atuais usando : Use este pequeno trecho de VBA para destacar a linha e coluna atuais da planilha.
Os eventos de planilha no Excel VBA : Os eventos de planilha são realmente úteis quando você deseja que suas macros sejam executadas quando um evento especificado ocorrer na planilha.
Artigos populares :
50 atalhos do Excel para aumentar sua produtividade : Torne suas tarefas mais rápidas no Excel. Esses atalhos o ajudarão a aumentar a eficiência do trabalho no Excel.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.