Mais de 100 truques ocultos de tabela dinâmica
Tabelas Dinâmicas é uma das habilidades intermediárias do Excel e este é um tutorial avançado de Tabelas Dinâmicas que mostra as 100 principais dicas e truques para dominar essa habilidade. O fato é que, quando se trata de análise de dados, criação de relatórios rápidos e eficientes ou apresentação de dados resumidos, nada se compara a uma tabela dinâmica.
Ele é dinâmico e flexível. Mesmo se você comparar fórmulas e tabelas dinâmicas, descobrirá que as tabelas dinâmicas são fáceis de usar e gerenciar. Se você quiser usar suas habilidades em tabelas dinâmicas, a melhor maneira é ter uma lista de dicas e truques que você pode aprender.
Ao longo desta lista usei as palavras “Analyze Tab” e “Design Tab”. Para obter essas duas guias na faixa do Excel, primeiro você deve selecionar uma Tabela Dinâmica. Além disso , certifique-se de baixar este arquivo de amostra aqui para tentar esses truques.
5 coisas a considerar antes de criar uma tabela dinâmica
Antes de criar uma tabela dinâmica, você deve dedicar alguns minutos trabalhando na fonte de dados que usará para verificar se há alguma correção que precise ser feita.
1. Sem colunas e linhas vazias nos dados de origem
Uma das coisas que você precisa controlar nos dados de origem é que não deve haver linhas ou colunas vazias.
Ao criar uma tabela dinâmica, se você tiver uma linha ou coluna em branco, o Excel levará apenas os dados até essa linha ou coluna.
2. Nenhuma célula em branco na coluna de valor
Além da linha e coluna vazias, você não deve ter uma célula vazia na coluna onde estão os valores.
O principal motivo para verificar isso é que se você tiver uma célula em branco na coluna do campo Valores: o Excel aplicará o número no pivô em vez da SOMA dos valores.
3. Os dados devem estar no formato correto
Ao usar dados de origem para uma tabela dinâmica, eles devem estar no formato correto.
Digamos que você tenha datas em uma coluna e essa coluna esteja formatada como texto. Nesse caso, não seria possível agrupar as datas na tabela dinâmica que você criou.
4. Use uma tabela para dados de origem
Antes de criar uma tabela dinâmica, você deve converter os dados de origem em uma tabela.
Uma tabela se expande cada vez que você adiciona novos dados a ela e isso facilita a alteração da fonte de dados da tabela dinâmica (quase automática).
Aqui estão as etapas:
- Selecione todos os seus dados ou uma das células.
- Pressione a tecla de atalho Ctrl + T.
- Clique OK.
5. Remova totais de dados
Por fim, certifique-se de excluir o total da fonte de dados.
Se você tiver dados de origem com totais gerais, o Excel considerará esses totais como valores e os valores da tabela dinâmica serão aumentados em dobro.
Dica: Se você aplicou uma tabela na fonte de dados, o Excel não incluirá esse total ao criar uma tabela dinâmica.
Dicas para ajudá-lo ao criar uma tabela dinâmica
Agora, essas dicas você pode usar quando os dados estiverem preparados e você estiver pronto para criar uma tabela dinâmica com eles.
1. Tabelas dinâmicas recomendadas
Existe uma opção na aba “Inserir” para verificar as tabelas dinâmicas recomendadas. Quando você clica em “Tabelas Dinâmicas Recomendadas”, ele mostra um conjunto de tabelas dinâmicas que podem ser possíveis com os dados que você possui.
Esta opção é muito útil quando você deseja ver todas as possibilidades que tem com os dados disponíveis.
2. Criando uma tabela dinâmica a partir de uma análise rápida
Existe uma ferramenta no Excel chamada “Análise Rápida” que se parece com uma barra de ferramentas rápida que aparece toda vez que você seleciona o intervalo de dados.
E a partir desta ferramenta você também pode criar uma tabela dinâmica.
Ferramenta de análise rápida ➜ Tabelas ➜ Tabela dinâmica vazia.
3. Pasta de trabalho externa como fonte para tabela dinâmica
Esta é uma das dicas de tabela dinâmica mais úteis desta lista que quero que você comece a usar agora.
Digamos que você queira dinamizar a partir de uma pasta de trabalho que está em uma pasta diferente e não queira adicionar dados dessa pasta de trabalho à sua planilha atual.
Você pode vincular este arquivo como fonte sem adicionar nenhum dado no arquivo atual. Aqui estão as etapas.
- Na caixa de diálogo Criar Tabela Dinâmica, selecione “Usar fonte de dados externa”.
- Depois disso, vá até a aba Conexões e clique em “Procurar mais”.
- Localize o arquivo que deseja usar e selecione-o.
- Clique OK.
- Agora selecione a planilha na qual você possui dados.
- Clique em OK (duas vezes).
Agora você pode criar uma tabela dinâmica com todas as opções de campo do arquivo de origem externo.
4. O assistente clássico de tabela dinâmica e gráfico dinâmico
Em vez de criar uma tabela dinâmica na guia Inserir, você também pode usar o “Assistente de tabela dinâmica e gráfico dinâmico clássico”.
A única coisa que gosto no assistente clássico é que existe uma opção para extrair dados de várias planilhas antes de criar uma tabela dinâmica.
Uma maneira fácil de abrir este assistente é usar o atalho de teclado: Alt + D + P.
5. Campos de pesquisa
Nas configurações dos campos da tabela dinâmica, existe uma opção para pesquisar os campos. Você pode pesquisar o campo onde há centenas de colunas.
Quando você começa a digitar na caixa de pesquisa, ela começa a filtrar as colunas.
6. Alterar o estilo da janela do campo da tabela dinâmica
Existe uma opção que você pode usar para alterar o estilo da “janela de campo da tabela dinâmica”. Clique no ícone de engrenagem no canto superior direito e selecione o estilo que deseja aplicar.
7. Classifique a ordem de sua lista de campos
Se você tiver um grande conjunto de dados, poderá classificar a lista de campos em ordem de A a Z para facilitar a localização dos campos obrigatórios.
Clique no ícone de engrenagem no canto superior direito e selecione “Classificar de A a Z”. Por padrão, os campos são classificados pelos dados de origem.
8. Abrir/Mostrar lista de campos
Acontece comigo que quando crio uma tabela dinâmica e clico nela, aparece a “Lista de Campos” do lado direito e isso acontece toda vez que clico em uma tabela dinâmica.
Mas você pode desativá-lo e para isso basta clicar no “Botão Feild List” na aba “PivotTable Analyze”.
9. Nomeie uma tabela dinâmica
Depois de criar uma tabela dinâmica, a próxima coisa que acho que você precisa fazer é nomear uma tabela dinâmica.
E para isso, você pode ir até a aba Analisar ➜ Tabela Dinâmica ➜ Opções da Tabela Dinâmica e inserir o novo nome.
10. Crie uma tabela dinâmica na versão online do Excel
Recentemente, a opção de criar uma tabela dinâmica foi adicionada ao aplicativo online Excel (opções limitadas).
É tão simples quanto criar um pivô na aplicação web do Excel:
Na guia Inserir, clique no botão “Tabela Dinâmica” no grupo de tabelas…
…em seguida, selecione o intervalo de dados de origem…
…e a planilha onde deseja inseri-la…
…e, no final, clique em OK.
11. Código VBA para criar uma tabela dinâmica no Excel
Se quiser automatizar o processo de criação de tabela dinâmica, você pode usar o código VBA para isso.
Neste guia , mencionei um processo passo a passo simples para criar uma tabela dinâmica usando código de macro.
Formate uma tabela dinâmica como um PRO
Como você pode usar uma tabela dinâmica como relatório, é importante fazer algumas alterações na formatação padrão.
1. Altere o estilo da tabela dinâmica ou crie um novo estilo
Existem vários estilos predefinidos no Excel para uma tabela dinâmica que você pode aplicar com apenas um clique.
Na aba projetada, você pode encontrar “Estilo de tabela dinâmica” e ao clicar em “Mais”, você pode simplesmente selecionar um estilo de sua preferência.
Você também pode criar um novo estilo personalizado, usando a opção “Novo estilo de tabela dinâmica”.
Quando terminar seu estilo personalizado, você pode simplesmente salvá-lo para usar na próxima vez, ele ainda estará lá.
2. Preservar a formatação das células ao atualizar uma tabela dinâmica
Vá para Opções de Tabela Dinâmica (clique com o botão direito na Tabela Dinâmica e vá para Opções de Tabela Dinâmica) e marque a caixa “Preservar formatação de célula ao atualizar”.
A vantagem desta opção é que toda vez que você atualizar sua tabela dinâmica, você não perderá a formatação que possui.
3. Desative a atualização automática de largura ao atualizar uma tabela dinâmica
Além de formatar um, você também precisa mantê-lo, que é “Largura da Coluna”.
Para fazer isso, vá em “Opções da tabela dinâmica” e desmarque “Ajustar automaticamente a largura da coluna na atualização” e clique em OK.
4. Repita os rótulos dos itens
Ao usar vários itens em uma tabela dinâmica, você pode simplesmente repetir os rótulos dos itens principais. Facilita a compreensão da estrutura da tabela dinâmica.
- Selecione a tabela dinâmica e navegue até a guia “Design”.
- Na guia Design, vá para Layout ➜ Layout do relatório ➜ Repetir todos os rótulos de item.
5. Formatando valores
Na maioria dos casos, você deve formatar os valores após criar uma tabela dinâmica.
Por exemplo, se você deseja alterar o número de casas decimais dos números. Basta selecionar a coluna de valores e abrir a opção “Formatar célula”.
E a partir desta opção você pode alterar os números decimais. Na opção “Formato” você pode até alterar outras opções.
6. Altere o estilo da fonte das tabelas dinâmicas
Uma das minhas coisas favoritas sobre formatação é alterar o “estilo da fonte” de uma tabela dinâmica.
Você pode usar a opção de formato, mas a maneira mais fácil é fazer isso na guia Página inicial. Selecione toda a tabela dinâmica e selecione o estilo da fonte.
7. Ocultar/Mostrar subtotais
Ao adicionar uma tabela dinâmica com mais de um campo de item, você obtém subtotais para o campo principal.
Mas às vezes não há necessidade de exibir subtotais. Nessa situação, você pode ocultá-los fazendo o seguinte:
- Clique na tabela dinâmica e vá para a guia Analisar.
- Na guia Analisar, vá para Layout ➜ Subtotais ➜ Não mostrar subtotais.
8. Ocultar/mostrar total geral
Assim como os subtotais, você também pode ocultar e mostrar os totais gerais. Abaixo estão as etapas simples para fazer isso.
- Clique na tabela dinâmica e vá para a guia Analisar.
- Na guia Analisar, vá para Layout ➜ Total Geral ➜ Desativado para Linhas e Colunas.
9. Formato de dois dígitos em uma tabela dinâmica
Em uma tabela dinâmica normal, temos apenas um formato de valores na coluna de valores.
Porém, existem algumas situações (raras) em que você precisa ter formatos diferentes em uma única tabela dinâmica, como abaixo. Para fazer isso, você precisa usar uma formatação personalizada .
10. Aplique um tema à tabela dinâmica
No Excel, existem temas de cores predefinidos que você pode usar. Esses temas também podem ser aplicados a tabelas dinâmicas. Vá para a guia “Layout” e clique no menu suspenso “Temas”.
Existem mais de 32 temas que você pode aplicar com apenas um clique ou pode salvar seu estilo de formatação atual como tema.
11. Alterando o layout de uma tabela dinâmica
Para cada tabela dinâmica, você pode escolher um layout.
No Excel (se você estiver usando a versão 2007 ou superior) você pode ter três layouts diferentes. Na guia Design, vá para Relatório de layout ➜ Layout e selecione o layout que deseja aplicar.
12. Colunas e linhas listradas
Uma das primeiras coisas que faço ao criar uma tabela dinâmica é aplicar “Linha e coluna com marca”.
Você pode aplicá-lo na aba de design e marcar “Coluna com faixas” e “Linhas com faixas”.
Filtrar dados em uma tabela dinâmica
O que torna a Tabela Dinâmica uma das ferramentas de análise de dados mais poderosas são os “Filtros”.
1. Habilitar/desabilitar filtros
Assim como um filtro normal, você pode ativar/desativar filtros em uma tabela dinâmica. Na “Guia Analisar”, você pode clicar no botão “Cabeçalho de campo” para ativar ou desativar filtros.
2. Seleção atual para filtrar
Você selecionou uma ou mais células em uma tabela dinâmica e deseja filtrar apenas essas células. Aqui está a opção que você pode usar.
Após selecionar as células, clique com o botão direito e vá em “Filtrar”, depois selecione “Manter apenas os itens selecionados”.
3. Ocultar seleção
Assim como filtrar células selecionadas, você também pode ocultá-las. Para fazer isso, vá em “Filtro” e em seguida selecione “Ocultar itens selecionados”.
4. Filtro de valor e rótulo
Além dos filtros normais, você usa filtros de rótulo e filtros de valor para filtrar com um valor ou critério específico.
Filtro de rótulo:
Filtro de valor:
5. Use rótulo e filtro de valor juntos
Como falei na dica acima, você pode ter o campo Label e Value, mas precisa habilitar uma opção para usar essas duas opções de filtro juntas.
- Primeiro, abra as “Opções da Tabela Dinâmica” e vá para a aba “Total e Filtro”.
- Na aba “Total e filtro”, marque a caixa “Permitir múltiplos filtros por campo” .
- Depois disso, clique em OK.
6. Filtre os primeiros 10 valores
Uma das minhas opções favoritas em filtros é filtrar por “Top 10 Values” . Esta opção de filtro é útil ao criar um relatório instantâneo.
Para isso, você deve ir até o “Filtro de Valores” e clicar em “Top 10” e depois clicar em OK.
7. Filtrar campos na janela Campos da tabela dinâmica
Se quiser filtrar ao criar uma tabela dinâmica, você pode fazê-lo na janela “Campo Dinâmico”.
Para filtrar os valores em uma coluna, você pode clicar na seta para baixo à direita e filtrar os valores conforme necessário.
8. Adicione um fatiador
Uma das melhores coisas que encontrei para filtrar dados em uma tabela dinâmica é usar um “Slicer”.
Para inserir um slicer, basta ir até “Aba Analisar” e no grupo “Filtro” clicar no botão “Inserir Slicer”, após selecionar o campo para o qual deseja inserir um slicer e clicar em OK.
Relacionado: Excel SLICER – Um guia completo sobre como filtrar dados com ele
9. Formate um slicer e outras opções
Depois de inserir um segmento, você poderá alterar seu estilo e formato.
- Selecione o slicer e vá para a guia Opções.
- Em “Slicer Styles”, clique no menu suspenso e selecione o estilo que deseja aplicar.
Além dos estilos, você pode alterar a configuração na janela de configurações: clique no botão “Configurações do Slicer” para abrir a janela de configurações.
10. Fatiador único para todas as tabelas dinâmicas
Às vezes, quando você tem várias tabelas dinâmicas, é difícil controlar todas elas. Mas se você conectar um único segmento a várias tabelas dinâmicas , poderá controlar todos os pivôs sem esforço.
- Primeiro, insira um fatiador.
- E, em seguida, clique com o botão direito no slicer e selecione “Report Connections”.
- Na caixa de diálogo, selecione todos os pivôs e clique em OK.
Agora você pode simplesmente filtrar todas as tabelas dinâmicas com uma única segmentação de dados.
11. Adicione uma linha do tempo
Ao contrário de um segmento, uma linha do tempo é uma ferramenta de filtragem específica para filtrar datas e é muito mais poderosa que o filtro normal.
Para inserir um slicer, basta ir até “Aba Analisar” e no grupo “Filtro” clicar no botão “ Inserir Linha do Tempo ” e em seguida selecionar a coluna de data e clicar em OK.
12. Formate um filtro de linha do tempo e outras opções
Depois de inserir uma linha do tempo, você poderá alterar seu estilo e formato.
- Selecione a linha do tempo e vá para a guia Opções.
- Em “Estilos de linha do tempo”, clique no menu suspenso e selecione o estilo que deseja aplicar.
Além dos estilos, você também pode alterar as configurações.
13. Filtrar usando curingas
Você pode usar curingas do Excel em todas as opções de filtro onde for necessário inserir o valor a ser filtrado. Veja os exemplos abaixo onde usei um asterisco para filtrar valores começando com a letra A.
14. Limpe todos os filtros
Se você aplicou filtros em vários campos, poderá remover todos esses filtros na guia Analisar ➜ Ações ➜ Limpar ➜ Limpar filtro.
Dicas para aproveitar ao máximo as tabelas dinâmicas
Trabalhar com uma tabela dinâmica pode ser mais fácil se você conhecer as dicas que mencionei acima.
Essas dicas ajudarão você a economizar mais de 2 horas por semana.
1. Atualize manualmente uma tabela dinâmica
As tabelas dinâmicas são dinâmicas, portanto, quando você adiciona novos dados ou atualiza valores nos dados de origem, é necessário atualizá-los para que a tabela dinâmica obtenha todos os novos valores adicionados da origem. Atualizar uma tabela dinâmica é simples:
- Primeiro, clique com o botão direito em um pivô e selecione “Atualizar”.
- Em segundo lugar, vá para a guia “Analisar” e clique no botão “Atualizar”.
2. Atualize uma tabela dinâmica ao abrir um arquivo
Existe uma opção simples no Excel que você pode ativar e fazer com que uma tabela dinâmica seja atualizada automaticamente sempre que abrir a pasta de trabalho. Para fazer isso, aqui estão as etapas simples:
- Primeiro, clique com o botão direito em uma tabela dinâmica e vá para “Opções de tabela dinâmica”.
- Depois disso, vá até a aba “Dados” e marque a caixa “Atualizar dados ao abrir arquivo”.
- No final, clique em OK.
Agora, toda vez que você abrir a pasta de trabalho, esta tabela dinâmica será atualizada instantaneamente.
3. Atualize os dados após um intervalo de tempo específico
Se você deseja atualizar sua tabela dinâmica automaticamente após um intervalo específico, esta dica é para você.
… Veja como fazer isso.
- Primeiramente, ao criar uma Tabela Dinâmica, na janela “Criar Tabela Dinâmica”, marque a caixa “Adicionar estes dados ao modelo de dados”.
- Depois disso, depois de criar uma tabela dinâmica, selecione uma das células e vá para “Aba Analisar”.
- Na guia Analisar, Dados ➜ Editar fonte de dados ➜ Propriedades da conexão.
- Agora, em “Propriedades da conexão”, na aba de uso, marque a caixa “Atualizar a cada” e insira os minutos.
- No final, clique em OK.
Agora, após esse período específico inserido, seu pivô será atualizado automaticamente.
4. Substitua os valores de erro
Às vezes, quando você tem erros nos dados de origem, eles refletem o mesmo no pivô e isso não é nada bom.
A melhor maneira é substituir esses erros por um valor significativo.
Abaixo estão as etapas a seguir:
- Primeiro, clique com o botão direito em sua tabela dinâmica e abra Opções de tabela dinâmica.
- Agora, em “Layout e Formato”, marque a caixa “Para mostrar valor de erro” e insira o valor na caixa de entrada.
- No final, clique em OK.
Agora, para todos os erros, você terá o valor especificado.
5. Substitua as células vazias
Digamos que você tenha um pivô para dados de vendas e algumas células estejam em branco.
Porque uma pessoa que não sabe por que essas células estão vazias pode perguntar sobre isso. Portanto, é melhor substituí-lo por uma palavra significativa.
…etapas simples que você precisa seguir para isso.
- Primeiro, “clique com o botão direito” em sua tabela dinâmica e abra as opções da tabela dinâmica.
- Agora, em “Layout e Formato”, marque a caixa “Para mostrar células vazias” e insira o valor na caixa de entrada.
- No final, clique em OK.
Agora, para todas as células vazias, você terá o valor especificado.
6. Defina um formato numérico
Digamos que você tenha um pivô para dados de vendas e algumas células estejam vazias.
Porque uma pessoa que não sabe por que essas células estão vazias pode perguntar sobre isso. Portanto, é melhor substituí-lo por uma palavra significativa.
…etapas simples que você precisa seguir para isso.
- Primeiro, “clique com o botão direito” em sua tabela dinâmica e abra as opções da tabela dinâmica.
- Agora, em “Layout e Formato”, marque a caixa “Para mostrar células vazias” e insira o valor na caixa de entrada.
- No final, clique em OK.
Agora, para todas as células vazias, você terá o valor especificado.
7. Adicione uma linha em branco após cada elemento
Agora suponha que você tenha uma grande tabela dinâmica com vários itens.
Aqui você pode inserir uma linha em branco após cada elemento para que não haja confusão no pivô.
Confira estas etapas a seguir:
- Selecione a tabela dinâmica e vá para a guia Design.
- Na guia Design, vá para Layout ➜ Linhas em branco ➜ Inserir linha em branco após cada elemento.
A melhor coisa sobre essa opção é que ela oferece uma visão mais clara do seu relatório.
8. Arraste e solte itens em uma tabela dinâmica
Quando você tem uma longa lista de itens em seu pivô, pode organizar todos esses itens em uma ordem personalizada simplesmente arrastando e soltando.
9. Crie várias tabelas dinâmicas a partir de uma
Digamos que você criou uma tabela dinâmica a partir de dados de vendas mensais e usou produtos como filtro de relatório.
Com a opção “Mostrar páginas de filtro de relatório”, você pode criar várias planilhas com uma tabela dinâmica para cada produto.
Digamos que se você tiver 10 produtos em um filtro dinâmico, poderá criar 10 planilhas diferentes com apenas um clique.
Siga esses passos:
- Selecione seu pivô e vá para a aba de análise.
- Na guia Analisar, navegue até Tabela Dinâmica ➜ Opções ➜ Mostrar páginas de filtro de relatório.
Agora você tem quatro tabelas dinâmicas em quatro planilhas separadas.
10. Opção de cálculo de valor
Quando você adiciona uma coluna de valor no campo de valor, ela mostra SUM ou COUNT (às vezes), mas há algumas outras coisas que você pode calcular aqui:
Para abrir as opções “Configurações de valor”, selecione uma célula na coluna de valor e clique com o botão direito.
E no menu de contexto, abra “Configurações do campo de valor” e clique em
No campo “Resumir valor por”, selecione o tipo de cálculo que deseja exibir no pivô.
11. Executando a coluna Total em uma tabela dinâmica
Digamos que você tenha uma venda de tabela dinâmica por mês.
Agora você deseja inserir um total acumulado em sua tabela dinâmica para mostrar o crescimento total das vendas durante todo o mês.
Aqui estão as etapas:
- Clique com o botão direito nele e clique em “Configuração do campo de valor”.
- Na lista suspensa “Mostrar valores como”, selecione “Total acumulado em”.
- No final, clique em OK.
Saiba mais sobre como adicionar um total acumulado a uma tabela dinâmica .
12. Adicionar linhas em uma tabela dinâmica
A classificação oferece uma maneira melhor de comparar as coisas entre si…
…e para inserir uma coluna de classificação em uma tabela dinâmica, você pode seguir os seguintes passos:
- Primeiro, insira o mesmo campo de dados duas vezes no pivô.
- Depois disso, para o segundo campo, clique com o botão direito sobre ele e abra “Configurações do campo de valor”.
- Vá para a guia “Mostrar valores como” e selecione “Ordenar do maior para o menor”.
- No final, clique em OK.
…clique aqui para saber mais sobre classificação em uma tabela dinâmica .
13. Crie uma participação percentual
Imagine que você tem uma tabela dinâmica para vendas de produtos.
E agora você deseja calcular a participação percentual de todos os produtos nas vendas totais.
Passos para usar:
- Primeiro, insira o mesmo campo de dados duas vezes no pivô.
- Depois disso, para o segundo campo, clique com o botão direito sobre ele e abra “Configurações do campo de valor”.
- Vá para a guia “Mostrar valores como” e selecione “% do total geral”.
- No final, clique em OK.
Também é uma opção perfeita para criar um relatório rápido.
14. Mova uma tabela dinâmica para uma nova planilha
Quando você cria uma Tabela Dinâmica, o Excel solicita que você adicione uma nova planilha para a Tabela Dinâmica…
…mas também tem a opção de mover uma tabela dinâmica existente para uma nova planilha.
- Para fazer isso, vá em “Aba Analisar” ➜ Ações ➜ Mover tabelas dinâmicas.
15. Desative GetPivotData
Existe uma situação em que você precisa fazer referência a uma célula em um pivô.
Porém, pode haver um problema porque quando você se refere a uma célula em um pivô, o Excel usa automaticamente a função GetPivotData para referência.
O melhor é que você pode desativá-lo e aqui estão os passos:
- Vá para a guia Arquivo ➜ Opções.
- Nas opções, vá em Fórmulas ➜ Trabalhar com fórmulas ➜ desmarque “Usar funções GetPivotData para referência de tabela dinâmica”.
Você também pode usar o código VBA para isso:
SubdisableGetPivotData()
Application.GenerateGetPivotData = Falso
Finalizando a legenda
Confira estes ➜ Os 100 principais códigos VBA do Excel + arquivo PDF
16. Agrupar datas em uma tabela dinâmica
Imagine que você deseja criar uma tabela dinâmica por mês, mas tem datas em seus dados.
Nesta situação, você terá que adicionar uma coluna extra por meses.
Mas a melhor maneira é criar usando métodos de agrupamento de datas na tabela dinâmica. Usando este método, você não precisa adicionar uma coluna auxiliar.
Use as etapas abaixo:
- Primeiro, você precisa inserir a data como um item de linha em sua tabela dinâmica.
- Clique com o botão direito na tabela dinâmica e selecione “Grupo…”.
- Selecione “Mês” na seção por e clique em OK.
Irá agrupar todas as datas em meses e se quiser saber mais sobre esta opção aqui está o guia completo.
17. Agrupe dados numéricos em uma tabela dinâmica
Assim como as datas, você também pode agrupar valores numéricos.
Os passos são simples.
- Clique com o botão direito na tabela dinâmica e selecione “Grupo…”.
- Insira o valor para criar um intervalo de grupo na caixa “por” e clique em OK.
… clique aqui para saber como a opção de agrupamento de tabelas dinâmicas pode ajudá-lo a criar um histograma no Excel.
18. Colunas de grupo
Para agrupar colunas como linhas, você pode seguir as mesmas etapas das linhas. Mas você precisa selecionar um cabeçalho de coluna antes disso.
19. Desagrupar linhas e colunas
Quando você não precisa de grupos em sua tabela dinâmica, você pode simplesmente desagrupar eles clicando com o botão direito e selecionando “Desagrupar”.
20. Use cálculo em uma tabela dinâmica
Para se tornar um usuário avançado de tabela dinâmica, você precisa aprender como criar um campo calculado e um item em uma tabela dinâmica.
Digamos que na tabela dinâmica abaixo você precise criar novos dados por campo multiplicando o campo de dados atual por 10.
Nessa situação, em vez de criar uma coluna separada em uma tabela dinâmica, você pode inserir um item calculado.
➜ um guia completo para criar um item e campo calculado em uma tabela dinâmica
21. Lista de fórmulas utilizadas
Depois de adicionar um cálculo a uma tabela dinâmica ou ter uma tabela dinâmica com um campo ou item calculado, você poderá ver a lista de fórmulas usadas.
Para isso, basta acessar “Aba Analisar” ➜ Cálculo ➜ Campos, Elementos e Conjuntos ➜ Listar Fórmulas.
Você receberá instantaneamente uma nova planilha com uma lista de fórmulas usadas na tabela dinâmica.
22. Obtenha uma lista de valores exclusivos
Se você tiver valores duplicados em sua data, poderá usar uma tabela dinâmica para obter uma lista de valores exclusivos.
- Primeiro, você precisa inserir uma tabela dinâmica e, em seguida, adicionar a coluna onde há valores duplicados como um campo de linha.
- Depois disso, copie este campo de linha do pivô e cole-o como valores.
- Agora, a lista que você tem como valores é uma lista de valores exclusivos.
O que eu gosto em usar uma tabela dinâmica para verificar valores exclusivos é que ela é uma configuração que serve para todos.
Você não precisa criá-lo repetidamente.
23. Mostrar itens sem dados
Digamos que você tenha entradas em seus dados de origem onde não há valores ou valores zero.
Você pode ativar a opção “Mostrar elementos sem dados” no campo.
- Primeiro, clique com o botão direito no campo e abra “Configurações de campo”.
- Agora vá em “Layout e Impressão” e marque “Mostrar itens sem dados” e clique em OK.
Estrondo! Todos os itens para os quais você não possui dados serão exibidos na tabela dinâmica.
24. Diferença do valor anterior
Esta é uma das minhas opções favoritas de tabela dinâmica.
Com isso você pode criar uma coluna onde mostra a diferença dos valores atuais em relação ao valor anterior.
Digamos que você tenha um pivô com valores de mês,…
…então, com esta opção…
… você pode adicionar uma coluna de valor de diferença do mês anterior, como abaixo.
Aqui estão as etapas:
- Primeiro, você precisa adicionar a coluna onde estão os valores, duas vezes no campo de valor.
- Depois disso, para o segundo campo, abra “Configuração de valor” e “Mostrar valor como”.
- Agora, na lista suspensa “Mostrar valores como”, selecione “Diferença de” e selecione “Mês” e “(Anterior)” em “Item base”.
- No final, clique em OK.
Isso converterá instantaneamente a coluna de valores em uma coluna diferente da anterior.
25. Desative Mostrar detalhes
Quando você clica duas vezes em uma célula de valor em uma tabela dinâmica, os dados subjacentes a esse valor são exibidos.
Isso é bom, mas não é todo o tempo que você precisa para que isso aconteça e é por isso que você pode desligá-lo se necessário.
Tudo o que você precisa fazer é abrir as opções da tabela dinâmica, ir até a “Guia Dados” e desmarcar “Ativar detalhes de exibição”.
E então clique em OK.
26. Tabela dinâmica para PowerPoint
Aqui estão as etapas simples para colar uma tabela dinâmica em um slide do PowerPoint.
- Primeiro, selecione uma tabela dinâmica e copie-a.
- Depois disso, vá até o slide do PowerPoint e abra as opções especiais de colagem.
- Agora, na caixa de diálogo de colagem especial, selecione “Objeto gráfico do Microsoft Excel” e clique em OK.
Insira uma imagem
Para fazer alterações na tabela dinâmica, você deve clicar duas vezes no gráfico.
27. Adicionar tabela dinâmica ao documento do Word
Para adicionar uma tabela dinâmica no Microsoft Word, você deve seguir os mesmos passos do PowerPoint.
28. Expanda ou recolha os cabeçalhos dos campos
Se você tiver vários campos de dimensão em uma linha ou coluna, poderá expandir ou recolher os campos externos.
Você precisa clicar no botão + para expandir e no botão – para recolher…
…e para expandir ou recolher todos os grupos de uma vez, você pode clicar com o botão direito e escolher a opção.
29. Ocultar e mostrar botões de expansão ou recolhimento
Se você tiver vários campos de dimensão em uma linha ou coluna, poderá expandir ou recolher os campos externos.
30. Conte apenas números em valores
Existe uma opção em uma tabela dinâmica onde você pode contar o número de células com o valor numérico.
Para isso, basta abrir a “Opção de Valor” e selecionar “Número de Contagem” no “Campo de Valor Resumido por” e clicar em OK.
31. Classifique os itens por valor correspondente
Sim, você pode classificar por valores correspondentes.
- Basta abrir o filtro e selecionar “Mais opções de classificação”.
- Em seguida, selecione “Acesso (A a Z) por:” e selecione a coluna para classificar e clique em OK.
Observação:
Se você tiver várias colunas de valores, poderá usar apenas uma coluna para ordem de classificação.
32. Ordem de classificação personalizada
Sim, você pode usar uma ordem de classificação personalizada para sua tabela dinâmica.
- Para isso, ao abrir “Mais opções de classificação”, clique em “Mais opções” e desmarque a caixa “Classificar automaticamente quando o relatório for atualizado”.
- Depois disso, selecione a ordem de classificação e clique em OK no final.
Você precisa criar uma nova ordem de classificação personalizada e, em seguida, criá-la na guia Arquivo ➜ Opções ➜ Avançado ➜ Geral ➜ Editar lista personalizada.
33. Layout diferido
Se você ativar “Atualização atrasada de layout” e depois arrastar e soltar campos entre as áreas.
Sua tabela dinâmica não será atualizada a menos que você clique no botão Atualizar abaixo, no canto dos campos da tabela dinâmica.
Isso torna mais fácil para você verificar a tabela dinâmica.
34. Alterar nome do campo
Quando você insere um campo de valor, o nome obtido para o campo se parece com “Soma do Valor” ou “Contagem de Unidades”.
Mas às vezes (bem, o tempo todo) você precisa alterar esse nome para um nome sem “Soma de” ou “Contagem de”.
Para fazer isso, basta remover “Contagem de” ou “Soma de” da célula e adicionar um espaço no final do nome.
Sim é isso.
35. Selecione toda a tabela dinâmica
Se você quiser selecionar uma tabela dinâmica inteira de uma vez:
Selecione uma das células da tabela dinâmica e use o atalho de teclado Control + A.
Ou…
Vá para a guia Analisar ➜ Selecione ➜ Tabela dinâmica inteira.
36. Converter em valores
Se você deseja converter uma tabela dinâmica em valores, basta selecionar a tabela dinâmica inteira e então:
Use Control + C para copiá-lo e cole especial ➜ Valores.
37. Use uma tabela dinâmica em uma planilha protegida
Ao proteger uma planilha que contém uma tabela dinâmica, certifique-se de verificar:
“Usar a tabela dinâmica e o gráfico dinâmico”
de “Permitir que todos os usuários desta planilha façam:”.
38. Clique duas vezes para abrir as configurações do campo de valor
Se você deseja abrir as “Configurações de valor” para uma coluna de valor específica…
…ENTÃO…
…a melhor maneira é clicar duas vezes no cabeçalho da coluna.
Torne suas tabelas dinâmicas um pouco mais perfeitas
As tabelas dinâmicas são uma das maneiras mais eficazes e fáceis de criar relatórios. E precisamos compartilhar relacionamentos com outras pessoas o tempo todo. Antes, compartilhei algumas dicas úteis que podem ajudá-lo a compartilhar uma tabela dinâmica facilmente.
1. Reduz o tamanho de um relatório de tabela dinâmica
Se você pensa assim: Quando você cria uma tabela dinâmica do zero, o Excel cria um cache dinâmico.
Portanto, quanto mais tabelas dinâmicas você criar do zero, mais cache dinâmico o Excel criará e seu arquivo terá que armazenar mais dados.
Então qual é o objetivo?
Certifique-se de que todas as tabelas dinâmicas provenientes de fontes de dados tenham o mesmo cache.
Mas Puneet, como eu poderia fazer isso?
Simples, sempre que precisar criar um segundo, terceiro ou quarto… basta copiar e colar o primeiro e fazer alterações nele.
2. Exclua os dados de origem e a tabela dinâmica ainda funcionará bem
Outra coisa que você pode fazer antes de enviar uma tabela dinâmica para alguém é excluir os dados de origem.
Sua tabela dinâmica ainda funcionará corretamente.
E, se alguém precisar dos dados de origem, poderá obtê-los clicando no total geral da tabela dinâmica.
3. Salve uma tabela dinâmica como uma página da web [HTML]
Outra maneira de compartilhar uma tabela dinâmica com alguém é criar uma página da web.
Sim, um arquivo HTML simples com uma tabela dinâmica.
- Para fazer isso, basta salvar a pasta de trabalho como uma página da web [html].
- Na página Publicar como Web, selecione a tabela dinâmica e clique em “Publicar”.
Agora você pode enviar esta página HTML para qualquer pessoa e ela poderá ver a tabela dinâmica (não editável) até mesmo em seu celular.
4. Criando uma tabela dinâmica por meio de uma pasta de trabalho a partir de um endereço da Web
Suponha que você tenha um link da web para um arquivo Excel, como abaixo:
https://exceladvisor.org/book1.xlsx
Nesta pasta de trabalho você tem os dados e com esses dados você precisa criar uma tabela dinâmica.
Para isso, POWER QUERY é a chave.
Confira isto: Exemplos de Power Query + dicas e truques
- Primeiro, vá para a guia Dados ➜ Obter e transformar dados ➜ Da Web.
- Agora, na caixa de diálogo “Da Web”, insira o endereço da web da pasta de trabalho e clique em OK.
- Depois disso, selecione a planilha e clique em “Carregar para”.
- Em seguida, selecione o relatório de tabela dinâmica e clique em OK.
Neste ponto, você tem uma tabela dinâmica em branco conectada à pasta de trabalho a partir do endereço da Web inserido.
Agora você pode criar uma tabela dinâmica como desejar.
O que você pode fazer em uma tabela dinâmica com CF
Para mim, a formatação condicional é uma formatação inteligente. Tenho certeza que você concorda com isso. Bem, quando se trata de tabela dinâmica, CF funciona perfeitamente.
1. Aplicação de opções gerais de CF
Existem todas as opções de CF disponíveis para uso com uma tabela dinâmica.
➜ aqui está o guia que pode ajudá-lo a aprender todas as diferentes maneiras de usar CF em tabelas dinâmicas .
2. Destaque os 10 principais valores
Em vez de filtrar, você pode destacar os 10 primeiros valores em uma tabela dinâmica.
Para fazer isso, você deve usar a formatação condicional.
As etapas estão abaixo:
- Selecione uma das células na coluna de valor da sua tabela dinâmica.
- Vá para a guia Início ➜ Estilos ➜ Formatação Condicional.
- Agora em Formatação Condicional, vá para Regras Superiores/Inferiores ➜ 10 Principais Elementos.
- Selecione a cor na janela que você possui.
- E no final clique em OK.
Esta opção é muito útil ao criar relatórios rápidos com uma tabela dinâmica e uma vez que você.
3. Remova CF de uma tabela dinâmica
Você pode simplesmente remover a formatação condicional de uma tabela dinâmica seguindo as etapas abaixo:
- Primeiro, selecione uma das células da tabela dinâmica.
- Depois disso, vá para a aba Home ➜ Estilos ➜ Formatação Condicional ➜ Limpar Regras ➜ “Limpar as regras para esta tabela dinâmica”.
Se você tiver várias tabelas dinâmicas, deverá excluir os CFs um por um.
Use gráficos dinâmicos com tabelas dinâmicas para visualizar seus relatórios
Sou um grande fã da tabela dinâmica.
Se você souber usar uma tabela dinâmica corretamente, poderá aproveitar ao máximo uma das melhores ferramentas do Excel.
Aqui estão algumas dicas que você pode usar para se tornar um PRO de tabela dinâmica rapidamente e se quiser saber tudo sobre uma tabela dinâmica , você pode aprender com este guia .
1. Inserindo uma tabela dinâmica
Compartilhei um atalho de teclado simples para inserir uma tabela dinâmica , mas você também usa as etapas abaixo:
- Selecione uma célula na tabela dinâmica e vá para “Aba Analisar” .
- Na aba “Analisar”, clique em “Gráfico Dinâmico”.
Ele criará instantaneamente uma tabela dinâmica a partir da tabela dinâmica que você possui.
2. Criando um histograma usando um gráfico dinâmico e uma tabela dinâmica
Uma tabela dinâmica e um gráfico dinâmico são minha forma favorita de criar um histograma no Excel.
3. Desative os botões da tabela dinâmica
Quando você insere uma nova tabela dinâmica, ela vem com botões para filtrá-la que às vezes não são muito úteis.
E se você pensa assim, você pode esconder todos ou alguns deles.
Clique com o botão direito no botão e selecione “Ocultar botão de campo de valor no gráfico” para ocultar o botão selecionado ou clique em “Ocultar todos os botões de campo no gráfico” para ocultar todos os botões.
Quando você oculta todos os botões em uma Tabela Dinâmica, ele também oculta o botão de filtro na parte inferior do gráfico , mas você ainda pode filtrá-lo usando o filtro de Tabela Dinâmica, Slicer ou Linha do Tempo.
4. Adicione uma tabela dinâmica ao PowerPoint
Aqui estão as etapas simples para colar uma tabela dinâmica em um slide do PowerPoint.
- Primeiro, selecione uma tabela dinâmica e copie-a.
- Depois disso, vá para o slide do PowerPoint e abra Opções de colagem especiais .
- Agora, na caixa de diálogo de colagem especial, selecione “Objeto gráfico do Microsoft Excel” e clique em OK.
Para fazer alterações na tabela dinâmica, você precisa clicar duas vezes nela.
Atalhos de teclado para aumentar seu trabalho na tabela dinâmica
Todos nós amamos atalhos de teclado. CERTO? Listei aqui alguns dos atalhos de teclado comuns, mas úteis, que você pode usar para acelerar o trabalho da tabela dinâmica.
1. Crie uma tabela dinâmica
Alt + N + V
Para usar esta tecla de atalho, certifique-se de ter selecionado os dados de origem ou de que a célula ativa seja dos dados de origem.
2. Agrupar itens selecionados da tabela dinâmica
Alt + Shift + Seta para a direita
Digamos que você tenha uma tabela dinâmica com meses e queira agrupar os primeiros seis ou os últimos seis meses.
Tudo que você precisa fazer é selecionar essas seis células e usar esta tecla de atalho.
3. Desagrupar itens selecionados da tabela dinâmica
Alt + Shift + Seta para a esquerda
Assim como você pode criar um grupo de itens, este atalho ajuda a desagrupar esses itens do grupo.
4. Oculte o item ou campo selecionado
Ctrl + –
Esta tecla de atalho simplesmente ocultará as células selecionadas.
Na verdade, ele não oculta as células, mas as filtra, o que você pode limpar nas opções de filtros.
5. Abra a janela Campo Calculado
Ctrl + –
Para usar esta tecla de atalho, você deve selecionar uma célula na coluna do campo de valor.
E ao pressionar esta tecla de atalho, abre a janela “ Campo Calculado ” .
Abra o antigo Assistente de Tabela Dinâmica
Alt + D e P
Neste atalho de teclado, você deve pressionar as teclas posteriormente.
7. Abra a lista de campos da célula ativa
Alt + seta para baixo
Esta chave abre a lista de campos.
8. Insira um gráfico dinâmico de uma tabela dinâmica
Alt+F1
Para usar este atalho de teclado, você deve selecionar uma célula na tabela dinâmica. Esta chave insere uma tabela dinâmica na planilha existente.
F11
E, se quiser inserir um pivô em uma nova planilha, basta usar a chave acima.
No final
Como eu disse, as tabelas dinâmicas são uma daquelas ferramentas que podem ajudá-lo a melhorar a geração de relatórios e a análise de dados rapidamente.
E com essas dicas e truques você pode economizar ainda mais tempo. Se você me perguntar, quero que comece a usar pelo menos 10 dicas primeiro, depois passe para as próximas 10 e assim por diante.
Mas agora você tem que me dizer uma coisa: qual é a sua dica favorita de tabela dinâmica?