Como agrupar dados em uma tabela dinâmica do Excel. Exemplos para agrupar datas, números e campos de texto. Como solucionar problemas de agrupamento e evitar problemas.
Agrupando dados
Em uma tabela dinâmica, você pode agrupar datas, números e campos de texto. Por exemplo, agrupe datas de pedidos por ano e mês ou agrupe pontuações de testes em faixas de 10. Você pode selecionar manualmente os itens de texto em um campo da tabela dinâmica e agrupar os itens selecionados. Isso permite que você veja rapidamente os subtotais de um conjunto específico de itens em sua tabela dinâmica.
Este vídeo mostra como agrupar datas por mês e anos e como agrupar itens de texto manualmente. Existem etapas escritas abaixo do vídeo.
Agrupamento automático de datas
No Excel 2016 e em versões posteriores, as datas são agrupadas automaticamente quando você adiciona um campo de data a uma tabela dinâmica. Você pode desfazer o agrupamento no Excel 2016 e desativar esse recurso nas versões posteriores.
Desativar o agrupamento automático de datas
No Excel 2019 e no Excel 365, você pode alterar uma opção do Excel configuração, para ativar ou desativar esse recurso de agrupamento de datas
OBSERVAÇÃO: Esta é uma configuração de nível de aplicativo e afetará todas as suas pastas de trabalho do Excel.
Para ativar o recurso de agrupamento automático de datas off:
- Na faixa de opções, clique na guia Arquivo e em Opções
- À esquerda, clique na categoria Dados
- No final de a seção Opções de dados, adicione uma marca de seleção para “Desativar agrupamento automático de colunas de data / hora em tabelas dinâmicas”
- Clique em OK para aplicar as novas configurações.
Desfazer o agrupamento de datas
Para todas as versões (Excel 2016 e posterior), você pode desagrupar manualmente as datas, com um destes métodos :
Atalho de teclado
- Adicione o campo Data à tabela dinâmica
- Pressione imediatamente Ctrl + Z para desfazer o agrupamento automático de datas
- Os campos agrupados são removidos e o campo Data original permanece na tabela dinâmica
Comando Desagrupar
- Adicione o campo Data à tabela dinâmica
- A qualquer momento, clique com o botão direito em qualquer um dos itens ou títulos do campo de data
- No menu do botão direito, clique em Desagrupar
- Os campos agrupados são removidos e o campo Data original permanece na tabela dinâmica
Datas de agrupamento
Em uma tabela dinâmica, existem vários em opções para agrupar datas – segundos, minutos, horas, dias, meses, trimestres e anos.
Você pode selecionar uma ou mais dessas opções para criar o tipo de agrupamento de datas de que precisa. Os exemplos abaixo mostram como agrupar datas:
– Por mês e ano
– Por semana
– Por período de 4 semanas
Agrupar datas por mês e ano
Para agrupar os itens em um campo Data
- Clique com o botão direito do mouse em uma das datas na tabela dinâmica.
- No menu pop-up, clique em Grupo
- Na caixa de diálogo Agrupamento, selecione uma ou mais opções de a lista “Por”.
- Para limitar as datas agrupadas, você pode definir uma data de início e de término digitando as datas nas caixas “Começando em” e “Terminando em”
- Clique em OK para fechar a caixa de diálogo.
Agrupar datas por semana
Para agrupar os itens em um campo Data por semana
- Clique com o botão direito em uma das datas da tabela dinâmica.
- No menu pop-up, clique em Grupo
- Na caixa de diálogo Agrupamento, selecione Dias na lista “Por”.
- Para “Número de dias”, selecione 7
- O intervalo da semana é determinado pela data na caixa “Iniciando em”, então ajuste se necessário. No exemplo abaixo, 1º de janeiro de 2012 foi inserido como a data de início.
- Clique em OK
Agrupar datas por períodos de 4 semanas
Assista a este vídeo para ver como agrupar o campo de data em períodos de 4 semanas e definir a data de início. Há instruções escritas abaixo do vídeo.
Agrupe datas por período de 4 semanas
Neste exemplo, nossa empresa tem 13 períodos de vendas a cada ano, com 4 semanas em cada período. Na caixa de diálogo Agrupamento da tabela dinâmica, não há opção de agrupamento por semanas. Em vez disso, podemos usar a opção Dias.
Com um pouco de aritmética simples, podemos calcular o número de dias por período:
- 4 semanas por período
- 7 dias por semana
- 7 x 4 = 28 dias por período
Siga estas etapas para agrupar as datas em períodos de 4 semanas:
- Clique com o botão direito em uma das datas e clique em Grupo
- Na janela Agrupamento, clique em Dias para selecioná-lo
- Se qualquer outro tipo de agrupamento estiver selecionado, clique neles para desmarcá-los
- Defina o número de dias como 28
- NOTA: Para agrupar datas em períodos de 1 semana, use 7 como o número de dias.
Usando a data de início padrão
Como data de início, o Excel seleciona automaticamente a primeira data em seus dados de origem. Na captura de tela acima, você pode ver que 1º de janeiro de 2013 é a data de início nestes dados de origem.
Se eu verificar no Outlook, essa data cai em uma terça-feira.
Se eu clicar em OK na janela Agrupamento, o Excel criará períodos que começam em uma terça-feira. Nas datas agrupadas mostradas abaixo, o primeiro período vai de terça-feira, 1º de janeiro de 2013, a segunda-feira, 28 de janeiro de 2013.
Corrija a data de início
Em vez de usar a data de início padrão, você pode inserir uma data diferente, para obter a data de início de que precisa.
Neste exemplo, nós Vou mudar a data de início para 31 de dezembro de 2012. Essa data cai em uma segunda-feira, então isso força todos os agrupamentos a começarem em uma segunda-feira, o que corresponde aos períodos de vendas da empresa.
Para fixar a data de início:
- Clique com o botão direito em uma das células de data agrupadas na tabela dinâmica
- Clique em Grupo
- Na janela Agrupamento, altere a data de início – 31 de dezembro de 2012
- NOTA: A caixa de seleção Automático para “Iniciando às” é desmarcada automaticamente – deixe-a desmarcada.
- Clique em OK
Agora, os períodos estão agrupados corretamente, com uma segunda-feira como data de início de cada período.
O agrupamento por data adiciona itens extras
Assista a este pequeno vídeo para ver como ocultar o texto nos itens extras que são criados quando você agrupa um campo de data ou usa um linha do tempo no Excel 2013, para filtrar por um período de data. Existem instruções escritas abaixo do vídeo.
Baixe o arquivo de amostra para os itens extras no agrupamento por data. O arquivo compactado está no formato xlsx e não contém macros.
O agrupamento por data adiciona itens extras
Não há como remover esses itens extras, mas você pode alterar as legendas para substituir o texto por caracteres de espaço. Ou adicione novos campos nos dados de origem, para calcular o ano e ano / mês, para cada data, e use os novos campos na tabela dinâmica, em vez de agrupamento.
No Excel 2013, você pode use o filtro de cronograma para filtrar rapidamente por ano, trimestre, mês ou dia.
Baixe o arquivo de amostra para o extra itens no agrupamento de datas. O arquivo compactado está no formato xlsx e não contém macros.
Números de agrupamento
Use o agrupamento para criar um resumo conciso, em vez de mostrar uma longa lista de números individuais. Este vídeo mostra as etapas e as instruções escritas estão abaixo do vídeo.
Números de agrupamento
Em vez de listar os números individualmente em um campo Linha, você pode agrupá-los para criar um resumo conciso dos dados.
Neste exemplo, a tabela dinâmica original mostra as unidades vendidas, por produto. Na coluna C, mostra o número de pedidos em que essa quantidade foi vendida.
Para resumir os pedidos, agrupe as unidades por dezenas. Portanto, em vez de 3 e 9 aparecerem em linhas separadas, eles serão resumidos no agrupamento de 1 a 10.
Para agrupar os números
- Clique com o botão direito em um dos números de unidade na tabela dinâmica.
- No menu pop-up, clique em Grupo
- Na caixa de diálogo Agrupamento, insira 1 na caixa Iniciando em
- Neste exemplo, o maior número de unidades é 50 e você pode digitar um número maior, se necessário.
- Clique em OK para aplicar o agrupamento
Na tabela dinâmica, as unidades agora aparecem em grupos – 1 – 10, 11 – 20, etc.
Para fazer um layout mais conciso, você pode mover o campo de unidades agrupadas para a área Colunas, como títulos.
Agrupando itens de texto
A caixa de diálogo Agrupamento não estará disponível se você tentar agrupar itens em um campo de texto. Em vez disso, você pode agrupar manualmente os itens.
Assista a este vídeo para ver as etapas para criar grupos a partir de itens de texto. Em seguida, renomeie um grupo ou adicione mais itens. Posteriormente, você pode desagrupar um ou mais grupos. Há instruções escritas abaixo do vídeo.
Agrupando itens de texto
A caixa de diálogo Agrupamento não estará disponível se você tentar agrupar itens em um campo de texto. Em vez disso, você pode agrupar manualmente os itens.
- Selecione os itens que deseja agrupar
- Clique com o botão direito em um dos itens selecionados e clique em Grupo
Isso cria um novo campo na lista de campos, com o nome do campo original e um número, como Cidade2
O novo campo é posicionado acima do campo original no layout da tabela dinâmica e os itens agrupados têm um nome genérico, como Grupo1.
Para renomear o grupo:
- Clique nas células do cabeçalho com o nome padrão , por exemplo Grupo1
- Digite um novo nome, como Campanha A.
Para adicionar mais itens para um grupo:
- Selecione todos os itens no grupo
- Pressione a tecla Ctrl e selecione os itens que deseja adicionar ao grupo
- Clique com o botão direito do mouse em um dos itens selecionados e clique em Grupo
- NOTA: Se o grupo foi renomeado, esse nome será substituído por um nome padrão.
Desagrupando itens de texto
Depois de agrupar manualmente os itens de texto, você pode querer desagrupar alguns ou todos, dos grupos em um campo dinâmico.
Para desagrupar um único grupo:
- Clique com o botão direito do mouse em um item no grupo
- Clique em Desagrupar, e o grupo reverte para itens individuais.
Para desagrupar um único grupo:
- Clique no topo de um título de grupo ou clique na parte superior do campo dinâmico para selecionar todos os itens
- Clique com o botão direito em um item do grupo
- Clique em Desagrupar e todos os grupos selecionados serão revertidos para itens individuais.
Problemas ao agrupar dados
Se você tentar agrupar uma data ou número campo, você pode ver uma mensagem de erro que diz: “Não é possível agrupar essa seleção.” Isso pode ser causado pelo Modelo de Dados, ou células que estão em branco ou itens agrupados anteriormente. Veja os detalhes abaixo.
Modelo de dados
Quando você criou a tabela dinâmica, se você marcou a caixa “Adicionar esses dados ao modelo de dados”, você não conseguirá agrupar os itens. Em vez de uma tabela dinâmica normal, é um Power Pivot baseado em OLAP e o recurso de agrupamento não está disponível.
Células ou texto em branco
Em uma tabela dinâmica normal (não no modelo de dados), o agrupamento o problema geralmente ocorre quando o campo contém registros com
- uma célula em branco em um campo de data / número ou
- uma entrada de texto em um campo de data / número.
Para corrigir o problema
- Para células em branco, preencha a data / número (use uma data / número fictício, se necessário).
- Se houver texto no campo de data / número, remova-o.
- Se os números estiverem sendo reconhecidos como texto, use uma das técnicas para alterar o texto para números reais. Em seguida, volte a este tutorial de tabela dinâmica e tente as etapas de agrupamento.
Agrupamento anterior
Se você não tiver células ou texto em branco na coluna de data, pode haver um campo agrupado remanescente da vez anterior em que você agrupou os dados.
- Verifique a lista de campos para ver se há uma segunda cópia do campo de data, por exemplo Date2.
- Se houver, adicione-o à área da linha e desagrupe-o.
- Então, você deve ser capaz de agrupar o campo de data novamente.
O agrupamento em uma tabela dinâmica afeta outra
Se você criar duas tabelas dinâmicas com base na mesma tabela do Excel em Excel 2007, quando você altera o agrupamento em uma tabela dinâmica, o mesmo agrupamento aparece na outra tabela dinâmica.
Como você criou as duas tabelas dinâmicas a partir dos mesmos dados de origem, por padrão, elas usam o mesmo cache dinâmico, o agrupado os itens são iguais em ambas as tabelas.
Para usar agrupamentos diferentes em cada tabela dinâmica, você precisará criar um cache dinâmico separado para cada tabela dinâmica.
Use o método a seguir, sugerido nos grupos de notícias do Excel por Dave Peterson.
Para criar um cache dinâmico separado para a segunda tabela dinâmica:
- Recorte a segunda tabela dinâmica e cole-a em uma nova pasta de trabalho.
- Altere o agrupamento da segunda tabela dinâmica.
- Corte a segunda tabela dinâmica da nova pasta de trabalho e cole-a de volta na pasta de trabalho original.
Baixe o Arquivo de amostra
Baixe o arquivo de amostra compactado para este tutorial
Ferramentas de tabela dinâmica
Para economizar tempo ao construir, formatar e modificar suas tabelas dinâmicas, use as ferramentas em meu suplemento Pivot Power Premium. Com apenas alguns cliques, você pode:
- copiar a formatação de uma tabela dinâmica e aplicá-la a outra.
- altere todos os valores de Contagem para Soma
- remova a “Soma de” de todos os títulos
e muito mais!
Mais tutoriais
Limpar itens antigos na tabela dinâmica
Campo calculado – Contagem
Calculado Itens
Funções de resumo