Como calcular a média de células de diferentes planilhas no Excel?

Se você precisa calcular a média do mesmo intervalo em várias planilhas — por exemplo, para encontrar a média de vendas no intervalo A1:A10 nas planilhas nomeadas como Planilha1 até Planilha5 — o Excel fornece uma solução baseada em fórmulas diretas. Essa abordagem funciona melhor quando todas as planilhas possuem a mesma estrutura e nomenclatura consistente.

Passos:

Selecione uma célula em branco onde deseja exibir o resultado (por exemplo, a célula C3) e insira a seguinte fórmula:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Após pressionar Enter, o Excel retornará o valor médio do intervalo especificado em todas as planilhas de Planilha1 até Planilha5.

Se você precisar calcular a média de valores de diferentes intervalos entre planilhas, pode listá-los manualmente:

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

Esta versão é útil quando os intervalos variam entre as planilhas. Insira-a na célula de resultado e pressione Enter.

O Kutools para Excel aprimora sua capacidade de extrair e consolidar valores da mesma célula ou intervalo em várias planilhas usando seu recurso Auto-incrementar Referências de Planilha. Isso é especialmente útil ao trabalhar com um grande número de planilhas com layout consistente.

Passos de uso:

1. Abra uma nova planilha (como uma planilha de resumo), depois selecione a célula onde você deseja calcular a média — por exemplo, D7.

2. Vá para Kutools > Mais (dentro do 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).
  - No campo Lista de Planilhas, marque as planilhas que contêm a célula que você deseja calcular a média.
  - Clique em Preencher Intervalo, depois feche a caixa de diálogo.

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

=AVERAGE(D7:D11)

Pressione Enter para obter o resultado. Isso simplifica a consolidação, mas não se estende automaticamente para incluir novas planilhas — será necessário executar novamente o recurso quando a lista de planilhas mudar.

Em alguns casos, você pode precisar calcular médias de várias células correspondentes em várias planilhas simultaneamente — por exemplo, resumindo resultados de A1, B1 e C1 de cada planilha. Isso pode se tornar complicado com fórmulas padrão, mas o utilitário Consolidar (planilhas e pastas de trabalho) do Kutools para Excel simplifica muito esse processo.

Como usar essa função:

1. Clique em Kutools Plus > Consolidar para abrir o assistente de Consolidação de Planilhas.

2. No assistente (Etapa 1 de 3):
Marque Consolidar e calcular valores de várias pastas de trabalho em uma planilha, depois clique em Próximo para continuar.

3. Na Etapa 2 de 3:
  - Selecione as planilhas a serem incluídas sob Lista de Planilhas.
  - Use o botão Procurar botão para definir o intervalo para cálculo da média.
  - Clique em Mesma Faixa se os intervalos forem idênticos em todas as planilhas.
  - Clique em Próximo para prosseguir.

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

5. Uma caixa de diálogo perguntará se você gostaria de salvar as configurações atuais como um cenário para uso futuro. Escolha Sim ou Não com base em suas necessidades.

Agora, cada célula no intervalo de saída definido refletirá a média das células correspondentes de todas as planilhas selecionadas. Esse método é especialmente útil para operações recorrentes ou ao consolidar grandes quantidades de dados estruturados rapidamente.

Para usuários que precisam automatizar o cálculo da média de células em várias planilhas — especialmente quando os nomes das planilhas não são adjacentes, mudam frequentemente ou quando você deseja especificar intervalos em tempo de execução — um macro VBA pode ser uma solução eficiente. Esse método é ideal para usuários avançados ou pastas de trabalho onde as planilhas são frequentemente adicionadas ou renomeadas.

O seguinte código VBA permite que você insira dinamicamente nomes de planilhas e intervalos de células, depois calcula a média do intervalo especificado em todas as planilhas listadas. É ideal para consolidar valores de pastas de trabalho complexas ou frequentemente atualizadas.

Como configurar e usar esta solução VBA:

1. Vá para a guia Desenvolvedor no Excel. Se ela não estiver visível, habilite-a via Arquivo > Opções > Personalizar Faixa de Opções. Clique em Visual Basic para abrir o editor. Depois vá para 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, pressione F5 no editor, ou feche-o e vá para Desenvolvedor > Macros, selecione AverageAcrossSheets e clique em Executar.

3. Quando solicitado, insira uma lista separada por vírgulas de nomes de planilhas (por exemplo, Planilha1,Planilha3,Resumo), depois especifique o intervalo (por exemplo, A1:A10).

4. A macro calculará o total e a contagem de cada planilha válida e exibirá a média em uma caixa de mensagem.

Notas sobre parâmetros:

• Os nomes das planilhas não diferenciam maiúsculas e minúsculas, mas devem corresponder exatamente.
• O intervalo pode ser uma única célula, coluna inteira (por exemplo, B:B), ou intervalo retangular (por exemplo, D2:E12).
• Planilhas inválidas ou ausentes serão ignoradas silenciosamente.