Como calcular a média por dia, mês, trimestre ou hora utilizando uma Tabela Dinâmica no Excel?

A funcionalidade Tabela Dinâmica do Excel é uma ferramenta prática para resumir e analisar dados, especialmente quando precisa de calcular rapidamente médias para períodos de tempo discretos, como dias, meses, trimestres ou horas. A abordagem abaixo elimina a necessidade de filtragem manual e cálculos repetitivos, proporcionando-lhe um resumo interativo e facilmente ajustável sempre que os seus dados forem atualizados.

1. Selecione toda a sua tabela de dados original (incluindo os cabeçalhos) e, em seguida, clique no separador Inserir > Tabela Dinâmica.

2. Na caixa de diálogo Criar Tabela Dinâmica que surge, escolha Planilha existente se pretender colocar o seu resumo na folha ativa. Defina a Localização selecionando uma célula onde deseja que a Tabela Dinâmica apareça e, depois, clique em OK.
Nota: para exibir a Tabela Dinâmica numa nova planilha, selecione a opção Nova planilha em vez disso. Certifique-se de que a localização escolhida não sobrepõe quaisquer dados existentes, para evitar avisos de substituição.

3. No painel Lista de Campos da Tabela Dinâmica (normalmente à direita), arraste a sua coluna Data/Hora para a área Linhas e a coluna de dados numéricos Montante para a área Valores. Esta configuração inicial agrega os seus dados por cada carimbo de data/hora registado.

4. Para organizar os seus resultados por períodos específicos, clique com o botão direito do rato em qualquer entrada de data na Tabela Dinâmica e selecione Agrupar no menu de contexto. Esta funcionalidade permite-lhe consolidar entradas de dados em intervalos como dias, meses, trimestres ou horas.

5. Na caixa de diálogo Agrupamento, escolha o período de agrupamento pretendido marcando a opção no campo Por(por exemplo,)Meses). Clique em OK para aplicar. Em seguida, clique com o botão direito do rato no valor Soma do Montante, selecione Resumir Valores Por > Média. A Tabela Dinâmica apresenta agora o valor médio para cada grupo de período temporal, permitindo uma comparação e análise imediatas.

Se precisar de atualizar a sua média com base em períodos à medida que surgem novos dados, basta atualizar a Tabela Dinâmica através do separador Analisar > Atualizar. Em resumo, as Tabelas Dinâmicas oferecem uma forma eficiente e visual de calcular e rever médias em intervalos temporais definidos, mas podem exigir reconfiguração manual caso a estrutura de agrupamento mude ou o formato dos seus dados seja inconsistente.

Se precisar frequentemente de calcular médias para períodos específicos — como diários, semanais, mensais ou anuais — especialmente a partir de conjuntos de dados horários detalhados, o processo manual de agrupamento e cálculo torna-se repetitivo e propenso a erros. O Kutools para Excel oferece utilitários especializados que simplificam esta tarefa, como as funcionalidades Para Real e Mesclar Linhas Avançado, que racionalizam a formatação de datas e a agregação em lote, poupando-lhe tempo valioso.

1. Selecione as células que contêm data e hora e formate-as para o período pretendido. Por exemplo, para obter médias diárias, selecione os seus dados e aceda a Base > Formato de Número > Data Curta. Esta funcionalidade da Conversão de Tempo transforma os carimbos de data/hora em valores exclusivamente de data.

Nota: para calcular médias por semana, mês ou ano, o Kutools para Excel disponibiliza as funcionalidades Aplicar Formato de Data e Para Real para converter carimbos de data/hora nos formatos necessários com apenas alguns cliques. Isto garante um agrupamento consistente e cálculos precisos.

Obtenha agora uma avaliação gratuita de 30 dias com todas as funcionalidades!

2. Destaque todo o seu conjunto de dados (incluindo datas formatadas e valores) e, em seguida, no Friso do Excel, selecione Kutools > Conteúdo > Mesclar Linhas Avançado.

3. Na caixa de diálogo que se abre, selecione a sua coluna Data/Hora na lista, marque-a como Chave Primária, depois escolha a sua coluna de valores (por exemplo, Montante) e defina-a para Calcular > Média. Clique em OK para confirmar e o Kutools calculará imediatamente as médias para cada data distinta.

As médias para os períodos temporais especificados são calculadas instantaneamente, facilitando a análise. Se o seu agrupamento de datas exibir meses ou anos em vez de dias, os resultados serão agregados em conformidade. Também pode reformatar datas utilizando Kutools > Formato > Aplicar Formato de Data e finalizar com Kutools > Para Real.

Para utilizadores que preferem um cálculo direto através de fórmulas, sem recorrer a Tabelas Dinâmicas nem a extras, as funções integradas do Excel — como MÉDIA.SE.S, SOMA.SE.S e CONTAR.SE.S — oferecem uma abordagem flexível e baseada em células para calcular médias em períodos específicos. Este método revela-se ideal quando precisa de cálculos personalizados, pretende evitar atualizações de tabelas ou deseja que os resultados apareçam diretamente junto aos seus dados.

Segue-se um exemplo de como utilizar fórmulas para calcular valores médios para cada dia:

1. Suponha que os seus dados incluem datas na coluna A (A2:A100) e valores numéricos na coluna B (B2:B100). Numa nova coluna — por exemplo, na célula C2 — introduza a seguinte fórmula para calcular a média correspondente a um dia específico (como a data em A2):

=AVERAGEIFS(B$2:B$100, A$2:A$100, A2)

2. Prima Enter para aplicar a fórmula. Para calcular para todas as datas, copie a fórmula ao longo dos seus dados.Dica
: se pretender que as médias diárias apareçam apenas uma vez por data única, ordene ou filtre primeiro os seus dados por datas e, em seguida, aplique a fórmula em conformidade.

Para utilizadores que processam rotineiramente conjuntos de dados muito grandes ou que precisam de repetir cálculos de média para diferentes períodos, automatizar o fluxo de trabalho com macros VBA pode aumentar significativamente a consistência e a eficiência. As macros conseguem agrupar e calcular médias por dia, mês, trimestre ou hora, eliminando por completo a repetição manual. Esta abordagem é ideal para utilizadores avançados do Excel e para cenários em que os cálculos devem ser executados frequentemente ou adaptados a novas folhas.

1. Para começar, abra o editor do VBA clicando em Ferramentas de Programador > Visual Basic. Quando a janela do Microsoft Visual Basic para Aplicações aparecer, clique em Inserir > Módulo e copie o seguinte código para o módulo:

Sub AverageByPeriod()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim periodCol As String, valueCol As String
    Dim dict As Object
    Dim cell As Range
    Dim periodKey As String
    Dim i As Long, lastRow As Long
    Dim sumDict As Object, countDict As Object
    
    Set ws = ActiveSheet
    periodCol = "A" ' Date/Time column
    valueCol = "B" ' Value column
    lastRow = ws.Cells(ws.Rows.Count, periodCol).End(xlUp).Row
    
    Set dict = CreateObject("Scripting.Dictionary")
    Set sumDict = CreateObject("Scripting.Dictionary")
    Set countDict = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRow
        ' Grouping by month example; change to format for day/hour/quarter if needed
        periodKey = Format(ws.Cells(i, periodCol).Value, "yyyy-mm")
        
        If Not dict.Exists(periodKey) Then
            dict.Add periodKey, dict.Count + 1
            sumDict.Add periodKey, ws.Cells(i, valueCol).Value
            countDict.Add periodKey, 1
        Else
            sumDict(periodKey) = sumDict(periodKey) + ws.Cells(i, valueCol).Value
            countDict(periodKey) = countDict(periodKey) + 1
        End If
    Next i
    
    ws.Cells(1, 4).Value = "Period"
    ws.Cells(1, 5).Value = "Average"
    
    i = 2
    Dim k As Variant
    For Each k In dict.Keys
        ws.Cells(i, 4).Value = k
        ws.Cells(i, 5).Value = sumDict(k) / countDict(k)
        i = i + 1
    Next k
End Sub

2. Após colar o código, clique no botão para executar. A macro irá ler os seus dados (das colunas A e B, a partir da linha 2), agrupá-los pelo período escolhido — atualmente definido como mês — e apresentar a média de cada grupo nas colunas D e E.

Dicas:

• Para agrupar por dia, altere a linha Formato(..., "aaaa-mm-dd").
• Para agrupamento trimestral, utilize:chavePeríodo = "Q" & WorksheetFunction.RoundUp(Month(ws.Cells(i, periodCol).Value) /[[PH_45]]) & "-" & Ano(ws.Células(i, colPeríodo).Valor)
• Verifique sempre se as suas colunas ()colPeríodo, colValor) correspondem à estrutura dos seus dados.

Precauções:

• Se obtiver um erro ou resultados em branco, verifique se a sua coluna de agrupamento não contém células vazias nem valores que não sejam datas.
• Ajuste a atribuição das colunas conforme necessário — se os seus dados começarem noutras colunas que não A e B, atualize colPeríodo e colValor em conformidade.
• Certifique-se de guardar o seu trabalho antes de executar macros, para evitar alterações indesejadas nos dados.

Média por ano/mês/data no Excel

Média de carimbos horários por dia no Excel

Média semanal no Excel