Como calcular a média de células localizadas em diferentes folhas do Excel?

Se precisar de calcular a média do mesmo intervalo em várias folhas de cálculo — por exemplo, para obter a média das vendas no intervalo A1:A10 nas folhas denominadas Sheet1 a Sheet5 — o Excel oferece uma solução direta e eficaz com base em fórmulas. Esta abordagem funciona na perfeição quando todas as folhas têm a mesma estrutura e uma nomenclatura consistente.

Passos:

Selecione uma célula vazia onde pretende o resultado (por exemplo, a célula C3) e introduza a seguinte fórmula:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Após premir Enter, o Excel devolverá o valor médio do intervalo limitado em todas as folhas de Sheet1 a Sheet5.

Se precisar de calcular a média de valores provenientes de intervalos diferentes em várias folhas, pode listá-los manualmente:

=AVERAGE(A1:A5, Sheet2!A3:A6, Sheet3!A7:A9, Sheet4!A2:A10, Sheet5!A4:A7)

Esta versão é ideal quando os intervalos variam entre folhas. Introduza-a na sua célula de resultado e prima Enter.

O Kutools para Excel potencia a sua capacidade de extrair e consolidar valores da mesma célula ou intervalo em múltiplas folhas de cálculo com a funcionalidade Auto-incrementar referências de planilha. É especialmente útil quando trabalha com um elevado número de folhas com layout consistente.

Passos de utilização:

1. Abra uma nova folha de cálculo (por exemplo, uma folha de resumo) e selecione a célula onde pretende calcular a média — por exemplo, D7.

2. Aceda a Kutools > Mais(no grupo)Fórmula) > Auto-incrementar referências de planilha.

3. Na caixa de diálogo:
- Escolha uma ordem de preenchimento no menu suspenso Ordem de Preenchimento(por exemplo,)Preencher por Coluna, depois por Linha).
- Em Lista de Planilhas, selecione as folhas que contêm a célula cuja média pretende calcular.
- Clique em Preencher Intervalo e, em seguida, feche a caixa de diálogo.

4. Os valores das células selecionadas serão listados no intervalo (por exemplo,)D7:D11). Em seguida, introduza a seguinte fórmula noutra célula vazia para calcular a média:

=AVERAGE(D7:D11)

Prima Enter para obter o resultado. Isto simplifica a consolidação, mas não se expande automaticamente para incluir novas folhas adicionadas — terá de voltar a executar esta funcionalidade sempre que a lista de folhas for alterada.

Em alguns casos, poderá precisar de calcular médias para várias células correspondentes em múltiplas folhas de cálculo simultaneamente — por exemplo, resumir os resultados das células A1, B1 e C1 de cada folha. Embora isto possa tornar-se moroso com fórmulas padrão, a funcionalidade Combinar (folhas de cálculo e livros de trabalho) do Kutools para Excel simplifica grandemente este processo.

Como utilizar esta função:

1. Clique em KUTOOLS PLUS > Combinar para abrir o assistente Consolidar Planilhas.

2. No assistente (Passo 1 de 3):
Assinale Consolidar e Calcular Dados de Várias Pastas de Trabalho em uma Planilha e, em seguida, clique em Seguinte para continuar.

3. No Passo 2 de 3:
- Selecione as folhas de cálculo a incluir em Lista de Planilhas.
- Utilize o botão Procurar para definir o intervalo a utilizar no cálculo da média.
- Clique em Mesmo Intervalo se os intervalos forem idênticos em todas as folhas.
- Clique em Seguinte para avançar.

4. No Passo 3 de 3:
Selecione Média no menu suspenso Função. Opcionalmente, configure os rótulos de linha/coluna conforme necessário e, em seguida, clique em Concluir.

5. Uma caixa de diálogo perguntar-lhe-á se deseja guardar as definições atuais como cenário para utilização futura. Escolha Sim ou Não, consoante as suas necessidades.

Agora, cada célula no seu Área de Colocação da Lista definido refletirá a média das células correspondentes de todos os Planilhas Selecionadas. Este método é especialmente útil para operações recorrentes ou ao consolidar grandes volumes de dados estruturados rapidamente.

Para utilizadores que precisem de automatizar o cálculo da média de células em múltiplas folhas de cálculo — especialmente quando os nomes das folhas não são adjacentes, mudam frequentemente ou quando pretendem especificar o intervalo em tempo de execução — uma macro VBA constitui uma solução eficiente. Este método é ideal para utilizadores avançados ou para livros de trabalho cujas folhas são frequentemente adicionadas ou renomeadas.

O seguinte código VBA permite-lhe introduzir dinamicamente os nomes das folhas e os intervalos de células, calculando depois a média do intervalo especificado em todas as folhas indicadas — a solução ideal para consolidar valores em livros de trabalho complexos ou frequentemente atualizados.

Como configurar e utilizar esta solução VBA:

1. Aceda ao separador Programador no Excel. Se não estiver visível, ative-o em Ficheiro > Opções > Personalizar Friso. Clique em Visual Basic para abrir o editor, depois vá a Inserir > Módulo e cole o seguinte código:

Sub AverageAcrossSheets()
    Dim xSheetNames As String
    Dim xCellRange As String
    Dim xArr As Variant
    Dim xSheet As Worksheet
    Dim xTotal As Double
    Dim xCount As Long
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    xSheetNames = Application.InputBox("Enter sheet names separated by commas (e.g., Sheet1,Sheet3,Summary):", xTitleId, Type:=2)
    If xSheetNames = "" Then Exit Sub
    
    xCellRange = Application.InputBox("Enter cell or range to average (e.g., A1 or A1:B10):", xTitleId, Type:=2)
    If xCellRange = "" Then Exit Sub
    
    xArr = Split(xSheetNames, ",")
    xTotal = 0
    xCount = 0
    
    For i = LBound(xArr) To UBound(xArr)
        Set xSheet = Nothing
        Set xSheet = ThisWorkbook.Sheets(Trim(xArr(i)))
        
        If Not xSheet Is Nothing Then
            If Not IsError(Application.WorksheetFunction.Average(xSheet.Range(xCellRange))) Then
                xTotal = xTotal + Application.WorksheetFunction.Sum(xSheet.Range(xCellRange))
                xCount = xCount + xSheet.Range(xCellRange).Count
            End If
        End If
    Next i
    
    If xCount = 0 Then
        MsgBox "No valid data found!", vbExclamation, xTitleId
    Else
        MsgBox "The average across selected sheets and range is: " & xTotal / xCount, vbInformation, xTitleId
    End If
End Sub

2. Para executar a macro, prima F5 no editor ou feche-o e aceda a Programador > Macros, selecione AverageAcrossSheets e clique em Executar.

3. Quando solicitado, introduza uma lista de nomes de folhas separados por vírgulas (por exemplo,)Sheet1,Sheet3,Summary) e, de seguida, especifique o intervalo (por exemplo, A1:A10).

4. A macro calculará o total e a contagem de cada folha válida, apresentando a média numa caixa de mensagem.

Notas sobre os parâmetros:

• Os nomes das folhas não diferem quanto a maiúsculas e minúsculas, mas devem corresponder exatamente.
• O intervalo pode ser uma única célula, uma coluna inteira (por exemplo, B:B) ou um bloco retangular (por exemplo, D2:E12).
• Folhas inválidas ou em falta serão ignoradas silenciosamente.