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 juntamente com SOMARPRODUTO, você pode calcular diretamente contagens por ano, mês ou qualquer combinação desses, tornando seu resumo preciso e atualizado automaticamente conforme os dados de origem mudam. Essa abordagem funciona bem na maioria das tarefas de análise rotineiras para conjuntos de dados pequenos a médios.

Selecione uma célula em branco onde deseja exibir o resultado da contagem e 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 ajustarem 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 datas e quando você deseja que os resultados sejam atualizados automaticamente ao modificar os dados. No entanto, trabalhar com várias condições de agrupamento pode tornar as fórmulas complexas e difíceis de manter.

Se você tiver o Kutools para Excel instalado, poderá 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 & mês ou mês & dia, sem precisar construir fórmulas complexas. Essa abordagem é especialmente eficiente para usuários que procuram uma solução visual e orientada por menus.

1. Selecione a coluna que contém suas datas e clique em Kutools > Formato > Aplicar Formato de Data. A seguinte caixa de 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 clique em OK. Por exemplo, selecione "Mar" para contar por mês.

3. Enquanto a coluna de data ainda estiver 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 grupo 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 você 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 a fórmulas manuais, o Kutools simplifica o processo, reduz erros humanos e é altamente adequado para usuários que frequentemente realizam contagens agrupadas e querem 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 e vá para Inserir > Tabela Dinâmica. A caixa de diálogo Criar Tabela Dinâmica aparece.

2. Na caixa de diálogo, especifique onde colocar a Tabela Dinâmica (nova planilha ou local existente, como célula E1) e 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 conforme a 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) e 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) e 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 nos 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 conjuntos de dados grandes e dinâmicos, que requerem agrupamento periódico, comparação e resumo. É menos adequada para cálculos rápidos em nível de célula ou para usuários que não estão familiarizados com os recursos da Tabela Dinâmica.

Quando você precisa gerar repetidamente resumos de ocorrências agrupadas por diferentes períodos de tempo ou deseja automatizar o processo de contagem para maior eficiência—especialmente em grandes conjuntos de dados—uma macro personalizada VBA 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:

• Volte para o Excel e pressione Alt+F8, selecione ContarOcorrênciasPorPeríodo e clique em Executar.
• Um prompt pedirá que você selecione 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: insira "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 contenha valores de data válidos do Excel; strings de texto ou formatos mistos podem produzir contagens imprecisas ou erros.
• A macro é flexível—insira "Trimestre" para agrupar contagens rapidamente por ano e trimestre, ou "Semana" para resumir semanalmente.
• 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 resumos visuais, considere usar ambas as Tabelas Dinâmicas e VBA.

Contar a frequência de entradas ou eventos por semana é um requisito comum no acompanhamento 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 determinada data dentro de um ano, facilitando o agrupamento de dados semanalmente 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 é atualizada 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 (mude para "2" se desejar que as semanas comecem na segunda-feira). Copie essa 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 entre 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 referências conforme necessário.
• A numeração da semana da função NÚMSEMANA pode variar dependendo da configuração (sistema, EUA/ISO, seu dia de início escolhido).
• Se estiver usando números de semana ISO (padrão europeu, semanas começando na segunda-feira e a primeira semana é aquela com a primeira quinta-feira), use =NUMSEMANAISO(A2) (para Excel 2013 e posterior).
• Sempre certifique-se de que todos os seus valores de data estejam no formato de data válido do Excel para obter resultados precisos.

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

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

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

Conte.se dados/lista filtrados com critérios no Excel