Como Grupo por Mês e o Ano em uma Tabela Dinâmica no Excel

Grupo de Datas em uma Tabela Dinâmica do Excel por Mês e por Ano

por Avantix da Equipe de Aprendizagem | Atualizado em 21 de novembro de 2020

Aplica-se a: Microsoft® Excel® 2013, de 2016 a 2019 e 365 (Windows)

Se você tem datas válidas entrou em sua fonte de dados, você pode agrupar por mês, ano ou outro período de tempo em uma tabela dinâmica no Excel. Existem duas abordagens comuns de agrupamento por data. Você pode agrupar por períodos de data em uma tabela pivô usando a funcionalidade de agrupamento (isso pode ocorrer automaticamente dependendo de sua versão do Excel)., Alternativamente, você também pode criar cálculos em dados de fonte para extrair o nome do mês e o ano a partir de um campo de data e usar os campos em sua tabela pivô.

artigo recomendado: como apagar linhas em branco nas folhas de cálculo do Excel (grandes estratégias, truques e atalhos)

os dados de origem são normalmente introduzidos verticalmente com os dados em colunas e nomes de Campos no topo de cada coluna de dados.

Agrupar por mês e ano numa tabela pivot

a chave para agrupar por mês e/ou ano numa tabela pivot é um campo fonte com datas válidas (como a data order)., Dependendo da configuração do seu Painel de controlo no seu dispositivo, as datas válidas podem ser introduzidas como Mês/Dia/Ano, Dia/Mês/Ano ou Ano/Mês/Dia (embora possam ser formatadas para aparecer de outras formas).

no Excel 2016 e versões posteriores, se arrastar um campo de data para a área de linhas ou colunas de uma tabela pivô, o Excel irá agrupar por omissão os incrementos de data.

A maneira mais fácil de agrupar por um período de data é clicar com o botão direito numa célula num campo de data numa tabela pivô e seleccionar os incrementos de agrupamento desejados. Você pode agrupar datas por quartos, anos, meses e dias., Os dados de origem não precisam conter uma coluna de Ano, Trimestre ou mês.

para agrupar por mês e/ou ano numa tabela pivot:

1. clique numa tabela pivot.
2. arraste um campo de data para a área da linha ou colunas na área de tarefas dos Campos Pivotáveis.
3. seleccione uma célula de campo de data na tabela pivô que deseja agrupar. O Excel pode ter criado um campo de ano e/ou mês automaticamente.
4. carregue com o botão direito na célula e seleccione o grupo no menu. Você também pode carregar com o botão direito num campo de data na área de linhas ou colunas na área de tarefas dos Campos Pivotáveis. Aparece uma caixa de diálogo.,
5. clique nos períodos de Data pelos quais deseja agrupar. Escolha Quartos, anos, meses ou dias. Você pode clicar em mais de um, como anos e meses.clique em OK.

A caixa de diálogo Agrupamento oferece várias opções para o agrupamento por data:

O campo de data serão agrupadas e novos campos será adicionado à lista de campos para os grupos (se elas não existissem previamente).

no exemplo a seguir, o Excel agrupado automaticamente por períodos de data na área de tarefas dos Campos Pivotáveis., Isso ocorre em 2016 ou versões posteriores:

Para remover um período de agrupamento para um campo de data:

1. Seleccione o campo data de célula na tabela dinâmica que tem sido agrupados.
2. carregue com o botão direito na célula e seleccione o Grupo. Você também pode carregar com o botão direito num campo de data na área de linhas ou colunas na área de tarefas dos Campos Pivotáveis. Aparece uma caixa de diálogo.Deseleccionar ou clicar nos grupos que deseja remover.clique em OK.

para remover todo o agrupamento:

1. seleccione uma célula de campo de data na tabela pivot que foi agrupada.,
2. carregue com o botão direito na célula e seleccione desmarcar.

controlar o agrupamento automático

no Excel 2016 e versões posteriores, as datas são agrupadas automaticamente se forem colocadas na área de linhas ou colunas da tabela pivot. Pode desligar isto ou ligar-se.

para desactivar o agrupamento automático das tabelas pivot:

1. carregue na página ficheiro da fita.
2. Carregue em Opções. Aparece uma caixa de diálogo.
3. clique nos dados nas categorias à esquerda. Se você estiver usando uma versão mais antiga do Excel, clique avançado nas categorias à esquerda.,
4. Seleccione ou assinale desactivar o agrupamento automático das colunas de data/hora na opção Pivotáveis.clique em OK.

Abaixo do Excel na caixa de diálogo Opções em 365:

Se você desativar o agrupamento automático, você ainda pode clique com o botão direito do mouse e grupo de tabelas dinâmicas, mas os grupos não será criado automaticamente quando você arrasta um campo de data para uma área de tabela dinâmica.,

problemas com a cache de tabela pivot

quando você cria uma tabela pivot e grupo usando a funcionalidade de grupo, os campos e os agrupamentos são armazenados em uma cache pivot, não nos dados de origem. Isso pode ser problemático se você criar mais tabelas de pivô com base no mesmo cache de pivô. Isto significa que todas as tabelas pivot que compartilham o mesmo cache, também compartilharão os agrupamentos de data.

não há uma maneira fácil de ver quais tabelas pivot compartilham o mesmo cache no Excel. Você precisa usar o VBA (Visual Basic for Applications) para encontrar esta informação.,

Você pode desligar o cache alterando o intervalo de dados de fonte, mas isso pode ser complicado se você tiver um monte de tabelas de pivô.

criar campos do período de data nos dados de origem

outra opção é criar campos nos dados de origem que calculam os períodos de data (como o ano e o mês). Você pode usar as funções de ano e texto para extrair ano e mês a partir de um campo de data válida.por exemplo, se tiver uma data na coluna A, pode criar um cálculo para extrair o ano na coluna B e o mês na coluna C.,

Para calcular o ano em B2 (assumindo que não há uma data válida no A2), introduza a seguinte fórmula e, em seguida, copiar a fórmula para baixo:

=ANO(A2)

Para calcular o nome do mês em C2 (assumindo que não há uma data válida no A2), introduza a seguinte fórmula e, em seguida, copiar a fórmula para baixo:

=TEXTO(A2;”mmmm”)

Se a fonte de dados em uma tabela do Excel, é melhor usar de referência estruturada fórmulas em cada linha, tais como a =ANO() ou =TEXTO(,”mmmm”), onde a Datadopedido é o nome do campo e @ é um especificador para referir-se a corrente de linha ou registro.,

o benefício de criar os campos nos dados de origem é que você pode criar uma tabela pivô e, em seguida, agrupar por esses campos na tabela pivô simplesmente arrastando-os para a área de linhas ou colunas.Você será capaz de criar outras tabelas pivot que não incluem o agrupamento de data.

outra vantagem de criar campos nos dados de origem é que você pode reutilizar os campos do período de data em outros relatórios, fórmulas ou Fatiadores.

resumo

o método que escolher depende das suas necessidades., Nós não temos usado o modelo de dados de Pivô de potência aqui (que fornece outro método de agrupamento por Data), mas está disponível apenas em certas versões do Excel. Além disso, estes métodos pressupõem um ano fiscal de Janeiro a dezembro. Você precisa criar outros cálculos nos dados da fonte se você quiser classificar meses por um ano fiscal que começa e termina em outros meses (como abril a março).

subscrever para obter mais artigos como este

achou este artigo útil? Se você gostaria de receber novos artigos, Junte-se à nossa lista de E-mail.,

Related

10 Great Excel Pivot Table Shortcuts

Excel Flash Fill Tricks to Clean and Extract Data (10 Examples)

How to Change Commas to Decimal Points in Excel and Vice Versa (5 Ways)

Recommended Microsoft Excel training

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >