Como calcular a média das células ou lista filtradas no Excel?

Quando aplica diretamente a função MÉDIA a um conjunto de dados filtrado, esta continua a calcular a média de todas as células no intervalo especificado — incluindo as ocultas pela filtragem — o que resulta em valores incorretos se o objetivo for obter apenas a média das linhas visíveis. Para calcular corretamente a média dos dados filtrados (ou seja, apenas das linhas visíveis), a função SUBTOTAL do Excel oferece uma solução eficaz: ela ajusta automaticamente os cálculos com base na filtragem e nas linhas ocultas, tornando-se a escolha ideal para este fim.

Para calcular a média apenas dos resultados filtrados, siga estes passos:

• Identifique o intervalo que inclui todos os seus dados filtrados na coluna em que pretende calcular a média (neste exemplo, suponha que os valores estão em)C12:C24 da coluna Montante).
• Numa célula vazia, introduza a seguinte fórmula:

=SUBTOTAL(1,C12:C24)

Esta fórmula calcula a média das células visíveis (filtradas) no intervalo limitado ()C12:C24). O parâmetro 1 indica à função SUBTOTAL que utilize a função MÉDIA, e o SUBTOTAL ignora automaticamente quaisquer linhas ocultas pela filtragem.

Após introduzir a fórmula, prima Enter. Verá imediatamente o valor médio apenas das linhas visíveis, conforme ilustrado abaixo:

Somar/Contar/Calcular a média apenas de células visíveis, ignorando células/linhas/colunas ocultas ou filtradas

As funções habituais SOMA, CONTAR ou MÉDIA no Excel calculam com base em todas as células do intervalo especificado, independentemente de estarem visíveis ou ocultas — quer por filtros, quer por ocultação manual. Para uma gestão mais robusta destes cenários, experimente o Kutools para Excel. Com as suas funções dedicadas SOMAVISÍVEL, CONTARVISÍVEL e MÉDIAVISÍVEL, pode calcular facilmente totais, contagens e médias apenas das células verdadeiramente visíveis num dado intervalo — excluindo automaticamente células filtradas, ocultas manualmente e as respetivas linhas ou colunas. Esta funcionalidade ajuda a evitar erros em folhas de cálculo complexas e a poupar tempo, comparativamente ao uso de fórmulas elaboradas ou código personalizado.

Calcular a média de dados/lista filtrados com a função AGREGAR

Se estiver a utilizar o Excel 2010 ou posterior, a função AGREGAR oferece ainda mais flexibilidade do que o SUBTOTAL para calcular médias em dados filtrados, com opções adicionais para lidar com erros e linhas ocultas. Eis como a pode aplicar:

• Numa célula vazia, introduza a seguinte fórmula (assumindo que os seus dados filtrados estão em C12:C24):

=AGGREGATE(1,5, C12:C24)

• O primeiro argumento ()1) indica a função MÉDIA, tal como no SUBTOTAL.
• O segundo argumento ()5) indica à função AGREGAR que ignore linhas ocultas (por filtragem) e erros.

Prima Enter após introduzir a fórmula para obter imediatamente a média das linhas visíveis no seu Intervalo de Filtro. Se pretender adaptá-la a diferentes formas de ocultar linhas ou a outras funções agregadas, basta ajustar o segundo parâmetro em conformidade.

Macro VBA para calcular a média apenas das células verdadeiramente visíveis

Para necessidades mais avançadas ou personalizadas, pode utilizar uma macro VBA simples para calcular a média apenas das células visíveis (ou seja, que não estejam ocultas nem filtradas) num intervalo selecionado. Esta solução revela-se particularmente útil em folhas onde os dados podem ser ocultos de várias formas. Eis como o pode fazer:

1. Navegue até ao separador Programador no Excel e selecione Visual Basic para abrir o editor do VBA. No editor, clique em Inserir > Módulo para criar um novo módulo.
2. Copie e cole o seguinte código VBA na janela do módulo:

Sub AverageVisibleCells()
    Dim rng As Range
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to average only visible cells", xTitleId, rng.Address, Type:=8)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not cell.EntireRow.Hidden And cell.Rows.Hidden = False And cell.Columns.Hidden = False Then
            If cell.DisplayFormat.Hidden = False And IsNumeric(cell.Value) And cell.Value <> "" Then
                sum = sum + cell.Value
                count = count + 1
            End If
        End If
    Next cell
    
    If count > 0 Then
        MsgBox "Average of visible cells is: " & sum / count, vbInformation, xTitleId
    Else
        MsgBox "No visible numeric cells found.", vbExclamation, xTitleId
    End If
End Sub

3. Feche o editor do VBA. De volta à sua folha de cálculo, prima Alt+F8, selecione AverageVisibleCells e clique em Executar. Quando solicitado, selecione o seu intervalo de dados pretendido. A macro calculará e apresentará então apenas a média das células numéricas atualmente visíveis (não filtradas nem ocultas).

Média ponderada

Calcular a média/taxa de crescimento anual composta

Média móvel/rolante

Total acumulado /média acumulada

Taxa média de variação