Como contar o número de ocorrências por ano/trimestre/mês/semana no Excel?

Quando você precisa descobrir rapidamente quantas vezes um determinado evento ocorreu em um ano ou mês específico, as fórmulas oferecem uma abordagem flexível e dinâmica. Usando funções de data embutidas junto com SOMARPRODUTO, você pode calcular diretamente contagens por ano, mês ou qualquer combinação desses, tornando seu resumo preciso e atualizado automaticamente quando os dados de origem mudam. Essa abordagem funciona bem na maioria das tarefas de análise rotineiras para conjuntos de dados pequenos e médios.

Selecione uma célula em branco onde deseja exibir o resultado da contagem e, em seguida, insira a seguinte fórmula:

=SOMARPRODUTO((MÊS($A$2:$A$24)=F2)*(ANO($A$2:$A$24)=$E$2))

Após inserir a fórmula, arraste a alça de preenchimento automático da célula para baixo para aplicar a fórmula a outras linhas conforme necessário. Como mostrado abaixo:

Notas e dicas:

• Na fórmula MÊS($A$2:$A$24)=F2 e ANO($A$2:$A$24)=$E$2 são critérios que correspondem ao mês especificado em F2 e ao ano em E2. Atualize os intervalos e referências (como A2:A24, E2, F2) para se ajustar ao layout dos seus dados.
• Para uma contagem apenas por mês, ignorando o ano, use:
  =SOMARPRODUTO(1*(MÊS($A$2:$A$24)=F2))
• Certifique-se de que a coluna de data contenha valores de data reais do Excel, não datas formatadas como texto para evitar erros ou incompatibilidades. Se sua fórmula retornar resultados inesperados, verifique novamente a formatação da data.
• Se o seu conjunto de dados for grande, considere usar Tabelas Dinâmicas ou VBA para melhor desempenho e manutenção mais fácil.

Esse método é adequado para a maioria dos cenários que exigem estatísticas rápidas de data e quando você quer que os resultados sejam atualizados automaticamente após modificações nos dados. No entanto, trabalhar com múltiplas condições de agrupamento pode tornar as fórmulas complexas e difíceis de manter.

Se você tiver o Kutools para Excel instalado, pode aproveitar seus utilitários intuitivos para agrupar e contar o número de ocorrências por ano, mês, dia da semana, dia ou combinações adicionais, como ano e mês ou mês e dia, sem precisar construir fórmulas complexas. Essa abordagem é especialmente eficiente para usuários que procuram uma solução visual e baseada em menus.

1. Selecione a coluna que contém suas datas e clique em Kutools > Formato > Aplicar Formato de Data. O seguinte diálogo aparecerá:

2. Na caixa de diálogo Aplicar Formato de Data, escolha o estilo de formatação correspondente ao seu requisito de contagem (como mês, ano, dia da semana, dia, etc.), e depois clique em OK. Por exemplo, selecione "Mar" para contar por mês.

3. Enquanto a coluna de data ainda está selecionada, clique em Kutools > Para Real. Este passo converte todas as datas para os valores exibidos (por exemplo, nomes de meses) para facilitar o agrupamento nas etapas subsequentes.

4. Em seguida, selecione o intervalo contendo seus nomes de grupos convertidos e dados associados (como colunas de Valor ou Categoria). Vá para Kutools > Texto > Mesclar Linhas Avançado. Você verá a seguinte interface:

5. Na caixa de diálogo Mesclar Linhas Avançado:
(1) Defina sua coluna de data como a Chave Primária para agrupar por ela.
(2) Para a coluna que deseja contar (por exemplo, Valor), defina o cálculo para Contagem.
(3) Você pode escolher outros métodos de agregação ou combinação para outras colunas (por exemplo, combine nomes de frutas com uma vírgula).
(4) Clique em OK para processar.

Seus dados agora exibirão a contagem de registros por período selecionado. Veja a captura de tela abaixo:

Comparado às fórmulas manuais, o Kutools simplifica o processo, reduz erros humanos e é altamente adequado para usuários que frequentemente realizam contagens agrupadas e desejam evitar a complexidade de fórmulas. Funciona bem tanto para conjuntos de dados pequenos quanto grandes. Lembre-se de fazer backup dos seus dados antes de converter ou combinar linhas em massa.

As Tabelas Dinâmicas oferecem uma maneira poderosa e interativa de analisar grandes conjuntos de dados e resumir ocorrências por uma ou mais dimensões de tempo — ano, mês, trimestre, hora, etc. — tudo com uma interface fácil de clicar e arrastar. As Tabelas Dinâmicas também permitem rápida reconfiguração e filtragem, tornando-as ideais para explorar padrões de dados ou preparar relatórios gerenciais.

1. Selecione sua tabela de dados, depois vá para Inserir > Tabela Dinâmica. A caixa de diálogo Criar Tabela Dinâmica aparece.

2. No diálogo, especifique onde colocar a Tabela Dinâmica (nova planilha ou local existente, como célula E1), depois clique em OK.

3. No painel Campos da Tabela Dinâmica, arraste o campo Data para a seção Linhas e o campo Valor (ou campo alvo) para a seção Valores. Por padrão, os valores podem ser somados.

A Tabela Dinâmica aparece como na captura de tela abaixo:

4. Altere o cálculo de valor para uma contagem clicando com o botão direito no título da coluna de valor (por exemplo, Soma de Valor), depois escolhendo Resumir Valores Por > Contagem.

5. Para agrupar por períodos adicionais (como mês, ano ou trimestre), clique com o botão direito em qualquer célula na coluna Rótulos de Linha, selecione Agrupar, e na caixa de diálogo, escolha os critérios de agrupamento (como Meses, Anos ou Trimestres), depois clique em OK.

Sua tabela agora exibe contagens pelos períodos selecionados:

Nota: Agrupar por múltiplos períodos (por exemplo, mês e ano) adicionará níveis extras em Rótulos de Linha. Você pode reorganizar campos de agrupamento (por exemplo, mover Anos abaixo de Data) no painel Campos da Tabela Dinâmica para ajustar sua visão de resumo.

Essa abordagem é ideal para grandes conjuntos de dados dinâmicos, exigindo agrupamento periódico, comparação e resumo. É menos adequada para cálculos rápidos e ad hoc em nível de célula ou para usuários que não estão familiarizados com recursos de Tabela Dinâmica.

Quando você precisa gerar repetidamente resumos de ocorrências agrupadas por vários períodos de tempo, ou deseja automatizar o processo de contagem para maior eficiência — especialmente em grandes conjuntos de dados — uma macro VBA personalizada pode ser uma solução eficaz. Esse método é altamente adequado se você processa dados regularmente, produz tabelas de resumo periódicas ou precisa de agrupamentos personalizados (como trimestres fiscais ou semanas) que não são facilmente tratados por fórmulas ou Tabelas Dinâmicas.

Passos completos da operação:

• Faça backup de sua pasta de trabalho antes de executar qualquer macro pela primeira vez.
• Clique em Desenvolvedor > Visual Basic para abrir o editor VBA.
• Clique em Inserir > Módulo, depois copie e cole o código abaixo na janela Módulo.

Sub CountOccurrencesByPeriod()
    Dim lastRow As Long
    Dim ws As Worksheet, summaryWs As Worksheet
    Dim periodType As String
    Dim dict As Object, key As Variant
    Dim dateRange As Range, cell As Range
    Dim outputRow As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    Set dateRange = Application.InputBox("Select date range:", xTitleId, Selection.Address, Type:=8)
    
    periodType = Application.InputBox("Count by (Year/Quarter/Month/Week):", xTitleId, "Month", Type:=2)
    
    If dateRange Is Nothing Or periodType = "" Then Exit Sub
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In dateRange
        If IsDate(cell.Value) Then
            Select Case LCase(periodType)
                Case "year"
                    key = Year(cell.Value)
                Case "quarter"
                    key = "Q" & WorksheetFunction.RoundUp(Month(cell.Value) / 3, 0) & " " & Year(cell.Value)
                Case "month"
                    key = Format(cell.Value, "yyyy-mm")
                Case "week"
                    key = "W" & WorksheetFunction.WeekNum(cell.Value) & " " & Year(cell.Value)
                Case Else
                    key = Format(cell.Value, "yyyy-mm")
            End Select
            
            If dict.Exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell
    
    Set summaryWs = Worksheets.Add(After:=ws)
    summaryWs.Name = "Occurrence_Summary"
    
    summaryWs.Range("A1").Value = "Period"
    summaryWs.Range("B1").Value = "Occurrences"
    
    outputRow = 2
    For Each key In dict.Keys
        summaryWs.Cells(outputRow, 1).Value = key
        summaryWs.Cells(outputRow, 2).Value = dict(key)
        outputRow = outputRow + 1
    Next key
    
    MsgBox "Summary completed in sheet 'Occurrence_Summary'.", vbInformation
End Sub

Após inserir o código:

• Retorne ao Excel e pressione Alt+F8, selecione ContagemOcorrênciasPorPeríodo e clique em Executar.
• Um prompt pedirá para selecionar o intervalo de datas para análise. Selecione a coluna ou intervalo relevante contendo suas datas.
• Um segundo prompt perguntará por qual período agrupar: digite "Ano", "Trimestre", "Mês" ou "Semana" (não diferencia maiúsculas e minúsculas).
• A macro gerará uma nova planilha chamada Resumo_Ocorrências listando cada período e a contagem de ocorrências dentro dele.

Solução de problemas e dicas:

• Se você encontrar um aviso de segurança de macro, ajuste as configurações de macro em Arquivo > Opções > Central de Confiabilidade > Configurações de Macro.
• Certifique-se de que sua coluna de data contém valores válidos de data do Excel; strings de texto ou formatos mistos podem produzir contagens imprecisas ou erros.
• A macro é flexível — digite "Trimestre" para agrupar contagens rapidamente por ano e trimestre, ou "Semana" para resumir em base semanal.
• Se você deseja personalizar a saída (por exemplo, adicionar mais detalhes), pode modificar a macro para processar colunas adicionais ou regras de cálculo.

Essa solução é robusta para relatórios em lote ou análises periódicas, mas presume familiaridade básica com VBA e gestão adequada da pasta de trabalho. Se você quiser combinar resumo visual, considere usar Tabelas Dinâmicas e VBA juntas.

Contar a frequência de entradas ou eventos por semana é um requisito comum no rastreamento de vendas, gerenciamento de projetos e alocação de recursos. O Excel fornece a função NÚMSEMANA, que retorna o número da semana de uma data específica dentro de um ano, facilitando o agrupamento de dados em base semanal usando fórmulas.

Cenário aplicável: Você tem uma lista de datas (por exemplo, dados de vendas ou presença) e deseja contar quantas entradas caem em cada semana do ano. Esse método funciona bem para análises contínuas e quando seus dados mudam frequentemente, pois a contagem se atualiza automaticamente.

1. Em uma coluna vazia (por exemplo, B2), insira a seguinte fórmula para calcular o número da semana para cada data na coluna A:

=WEEKNUM(A2,1)

O segundo argumento ("1") indica que as semanas começam no domingo (altere para "2" se desejar que as semanas comecem na segunda-feira). Copie esta fórmula para baixo em todas as linhas dos seus dados de data.

2. Faça uma lista dos números de semanas que deseja resumir (por exemplo, 1,2,3,…). Em outra célula em branco (digamos, D2), use a seguinte fórmula para contar as ocorrências para um número de semana específico (assumindo que B2:B24 liste os números de semanas e D2 contenha a semana a ser pesquisada):

=COUNTIF($B$2:$B$24, D2)

Após pressionar Enter, arraste essa fórmula para baixo para sua lista de números de semanas. Cada resultado mostra a contagem de ocorrências para aquela semana.

Dicas e precauções:

• Se você deseja contar por ano e semana, para distinguir entradas em anos diferentes, use:

  =SUMPRODUCT((YEAR($A$2:$A$24)=$F$2)*(WEEKNUM($A$2:$A$24,1)=G2))

  Onde F2 é o ano alvo e G2 é o número da semana alvo. Ajuste os intervalos de coluna e as referências conforme necessário.
• A numeração de semanas da função NÚMSEMANA pode variar dependendo da configuração (sistema, EUA/ISO, seu dia de início escolhido).
• Se usar números de semanas ISO (padrão europeu, semanas começando na segunda-feira e a primeira semana é a que contém a primeira quinta-feira), use =NÚMSEMANAISO(A2) (para Excel 2013 e posterior).
• Sempre certifique-se de que todos os seus valores de data estejam em formato de data válido do Excel para resultados precisos.

Esse método é flexível para tabelas de dados dinâmicos e pode ser adaptado para dashboards, resumos periódicos e quando você deseja cruzar contagens por semana sem usar Tabelas Dinâmicas ou complementos adicionais.

Conte o número de fins de semana/dias úteis entre duas datas no Excel

Conte por data/mês/ano e intervalo de datas no Excel

Conte dados/lista filtrados com critérios no Excel