✅ Cache dinâmico no Excel - O que é e como usá-lo da melhor maneira

Índice

O que é cache dinâmico?
Efeitos colaterais do cache dinâmico
Compartilhando o Cache Dinâmico
Criação de cache dinâmico duplicado (com a mesma fonte de dados)
Conte o número de caches dinâmicos
Melhorar o desempenho ao trabalhar com tabelas dinâmicas

Se você trabalha com tabelas dinâmicas do Excel, o cache dinâmico é algo que você definitivamente deve conhecer.

O que é cache dinâmico?

Cache Dinâmico é algo que é gerado automaticamente quando você cria uma Tabela Dinâmica.

É um objeto que contém uma réplica da fonte de dados. Embora você não possa ver, ele faz parte da pasta de trabalho e está conectado à Tabela Dinâmica. Quando você faz qualquer alteração na Tabela Dinâmica, ela não usa a fonte de dados, em vez disso, usa o Cache Dinâmico.

O motivo pelo qual um cache dinâmico é gerado é para otimizar o funcionamento da tabela dinâmica. Mesmo quando você tem milhares de linhas de dados, uma tabela dinâmica é muito rápida para resumi-los. Você pode arrastar e soltar itens nas caixas de linhas / colunas / valores / filtros e os resultados serão atualizados instantaneamente.

O Cache Dinâmico permite o funcionamento rápido de uma tabela dinâmica.

Enquanto você pensa que está diretamente vinculado aos dados de origem, na realidade, você acessa o cache dinâmico (e não os dados de origem) ao fazer alterações na tabela dinâmica.

Esse também é o motivo pelo qual você precisa atualizar a tabela dinâmica para refletir as alterações feitas no conjunto de dados.

Efeitos colaterais do cache dinâmico

Uma desvantagem do cache dinâmico é que ele aumenta o tamanho da pasta de trabalho.

Como é uma réplica dos dados de origem, quando você cria uma tabela dinâmica, uma cópia desses dados é armazenada no Cache Dinâmico.

Quando você usa grandes conjuntos de dados para criar uma tabela dinâmica, o tamanho do arquivo da pasta de trabalho aumenta significativamente.

Compartilhando o Cache Dinâmico

Do Excel 2007 em diante, se você já tem uma tabela dinâmica e cria uma tabela dinâmica adicional usando os mesmos dados de origem, o Excel compartilha automaticamente o cache dinâmico (o que significa que ambas as tabelas dinâmicas usam o mesmo cache dinâmico). Isso é útil porque evita a duplicação do cache dinâmico e, por sua vez, resulta em menos uso de memória e tamanho de arquivo reduzido.

Limitações do cache dinâmico compartilhado

Embora um cache dinâmico compartilhado melhore o funcionamento da tabela dinâmica e o uso de memória, ele sofre das seguintes limitações:

Quando você atualiza uma tabela dinâmica, todas as tabelas dinâmicas vinculadas ao mesmo cache são atualizadas.
Quando você agrupa campos em uma das tabelas dinâmicas, isso é aplicado a todas as tabelas dinâmicas usando o mesmo cache dinâmico. Por exemplo, se você agrupar datas por meses, essa alteração será refletida em todas as tabelas dinâmicas.
Quando você insere um campo / item calculado em uma das tabelas dinâmicas, ele é exibido em todas as tabelas dinâmicas que estão compartilhando o cache dinâmico.

A maneira de contornar essas limitações é forçar o Excel a criar cache dinâmico separado para tabelas dinâmicas diferentes (enquanto usa a mesma fonte de dados).

Observação: se você estiver usando fontes de dados diferentes para tabelas dinâmicas diferentes, o Excel gerará automaticamente caches dinâmicos separados para ele.

Criação de cache dinâmico duplicado (com a mesma fonte de dados)

Aqui estão três maneiras de criar cache dinâmico duplicado ao criar tabelas dinâmicas a partir da mesma fonte de dados:

Nº 1 usando nomes de tabela diferentes

Clique em qualquer lugar na fonte de dados e vá para Inserir -> Tabela (ou você pode usar o atalho de teclado - Control + T).
Na caixa de diálogo Criar Tabela, clique em OK. Ele criará uma Tabela com o nome Tabela1.
Com qualquer célula selecionada na tabela, vá para Inserir -> Tabela Dinâmica.
Na caixa de diálogo Criar Tabela Dinâmica, você notaria que no campo Tabela / Intervalo está o nome da tabela. Clique OK.
- Isso criará a primeira tabela dinâmica.
Vá para a fonte de dados (tabela), selecione qualquer célula e vá para Design de Ferramentas de Tabela -> Ferramentas -> Converter para Intervalo. Ele mostrará um prompt perguntando se você deseja converter a tabela em intervalo normal. Clique em Sim. Isso converterá a tabela em dados tabulares regulares.

Agora repita os passos acima, e apenas altere o Nome da Tabela (de Tabela1 para Tabela2 ou o que você quiser). Você pode alterá-lo inserindo o nome no campo abaixo de Nome da Tabela na guia Design das Ferramentas de Tabela.

Embora ambas as tabelas (Tabela1 e Tabela2) se refiram à mesma fonte de dados, esse método garante que dois caches dinâmicos separados sejam gerados para cada tabela.

Nº 2: usando o antigo assistente de tabela dinâmica

Use essas etapas quando quiser criar uma tabela dinâmica adicional com um cache dinâmico separado enquanto usa a mesma fonte de dados.

Selecione qualquer célula nos dados e pressione ALT + D + P.
- Isso abrirá o Assistente de Tabela Dinâmica e Gráfico Dinâmico.
Na Etapa 1 de 3, clique em Avançar.
Na Etapa 2 de 3, certifique-se de que o intervalo de dados esteja correto e clique em Avançar.
O Excel mostra um prompt que basicamente diz clique em Sim para criar um cache dinâmico compartilhado e Não para criar um cache dinâmico separado.
Clique em No.
Na Etapa 3 do Assistente, selecione se deseja a Tabela Dinâmica em uma nova planilha ou na mesma planilha e clique em Concluir.

Observação: certifique-se de que os dados não sejam uma tabela do Excel.

Conte o número de caches dinâmicos

Você pode desejar contar o número de caches dinâmicos apenas para evitar vários caches dinâmicos da mesma fonte de dados.

Esta é uma maneira rápida de contá-lo:

Pressione ALT + F11 para abrir o Editor VB (ou vá para a guia Desenvolvedor -> Visual Basic).
No Menu do Editor do Visual Basic, clique em Exibir e selecione Janela Imediata (ou pressione Control + G) Isso tornará a janela imediata visível.
Na janela imediata, cole o seguinte código e pressione Enter:
```
? ActiveWorkbook.PivotCaches.Count
```

Ele mostrará instantaneamente o número de Caches Dinâmicos na pasta de trabalho.

Melhorar o desempenho ao trabalhar com tabelas dinâmicas

Existem algumas coisas que você pode fazer para melhorar o desempenho das pastas de trabalho (tamanho do arquivo e uso de memória) enquanto trabalha com tabelas dinâmicas:

# 1 Exclua os dados de origem

Você pode excluir os dados de origem e usar apenas o cache dinâmico. Você ainda poderá fazer tudo usando o cache dinâmico, pois ele contém um instantâneo dos dados originais. Mas, como você excluiu os dados de origem, o tamanho do arquivo da pasta de trabalho seria reduzido.

Caso você queira recuperar os dados de origem, basta clicar duas vezes na interseção dos Totais Gerais dessa tabela dinâmica. Ele criará uma nova planilha e mostrará todos os dados usados para criar essa tabela dinâmica.

# 2 Não salve os dados no cache dinâmico

Quando você salva um arquivo com uma tabela dinâmica e dados de origem, ele também salva o cache dinâmico que contém uma cópia dos dados de origem. Isso significa que você está salvando os dados de origem em dois lugares: na planilha que contém os dados e no cache dinâmico.

Existe a opção de não salvar os dados no cache e fechá-lo. Isso resultará em um tamanho de arquivo menor.

Para fazer isso:

Selecione qualquer célula na Tabela Dinâmica.
Vá para Analisar -> Tabela Dinâmica -> Opções.
Na caixa de diálogo Opções da tabela dinâmica, vá para a guia Dados.
Desmarque a opção - Salvar dados de origem com arquivo.
Marque a opção - Atualizar Dados ao abrir o arquivo.
- Se você não marcar esta opção, ao abrir a pasta de trabalho do Excel, os dados não serão atualizados e você não poderá usar as funcionalidades da Tabela Dinâmica. Para fazer isso funcionar, você terá que atualizar manualmente a tabela dinâmica.

Quando você fizer isso, o Excel não salvará os dados no cache dinâmico, mas os atualizará quando você abrir a pasta de trabalho do Excel na próxima vez. Seus dados podem estar na mesma pasta de trabalho, em alguma outra pasta de trabalho ou em um banco de dados externo. Quando você abre o arquivo, ele atualiza os dados e o Cache Dinâmico é recriado.

Embora isso possa diminuir o tamanho do arquivo, pode demorar um pouco mais para abri-lo (já que o Excel recria o cache).

Veja também: Salvando dados de origem com a tabela dinâmica.

Nota: Se você usar esta opção, certifique-se de ter a fonte de dados intacta. Se você excluir os dados de origem (da pasta de trabalho ou de qualquer fonte de dados externa), não poderá recriar o cache dinâmico.

Nº 3: Compartilhamento do Cache Dinâmico para melhor desempenho

Se por acidente (ou intencionalmente) você acabar em uma situação em que tem cache dinâmico duplicado e deseja excluir a duplicata e compartilhar o cache dinâmico, aqui estão as etapas para fazer isso:

Exclua uma das tabelas dinâmicas para a qual deseja excluir o cache. Para fazer isso, selecione a tabela dinâmica e vá para Home -> Limpar -> Limpar tudo.
Agora, basta copiar a Tabela Dinâmica que deseja duplicar e colá-la (na mesma planilha ou em uma planilha separada).
- Recomenda-se colá-lo em planilhas separadas para que não se sobreponha à outra tabela dinâmica ao expandi-la. Embora, às vezes, eu o copie lado a lado para comparar diferentes visões. Esta cópia e colagem da tabela dinâmica garante que o cache dinâmico seja compartilhado.