Como calcular a média de cada grupo de 5 linhas ou colunas no Excel?
Ao trabalhar com grandes conjuntos de dados no Excel, é comum precisar de calcular médias para cada grupo de linhas ou colunas — por exemplo, a cada 5 linhas ou a cada 5 colunas. Embora possa inserir manualmente fórmulas como =MÉDIA(A1:A5), =MÉDIA(A6:A10), =MÉDIA(A11:A15) e assim sucessivamente, este processo torna-se rapidamente impraticável se a sua lista contiver centenas ou milhares de células. Repetir estas operações manualmente consome tempo e está sujeito a erros. Felizmente, o Excel oferece várias formas de automatizar esta tarefa, tornando a análise de dados muito mais eficiente e menos tediosa. Este artigo apresenta diversos métodos práticos para calcular a média a cada 5 linhas ou colunas, incluindo abordagens baseadas em fórmulas, complementos do Excel, automação com VBA e técnicas com Tabelas Dinâmicas, ajudando-o a escolher a melhor solução para o seu cenário.
Calcular a média de cada 5 linhas ou colunas com fórmulas
Calcular a média de cada 5 linhas com Kutools para Excel
Calcular a média de cada 5 linhas ou colunas com código VBA
Calcular a média de cada 5 linhas com Tabela Dinâmica
Calcular a média de cada 5 linhas ou colunas com fórmulas
Se preferir utilizar fórmulas padrão do Excel, pode automatizar o cálculo a cada 5 linhas ou colunas sem recorrer a complementos ou scripts. Esta abordagem é ideal para conjuntos de dados estáticos em que precisa apenas de gerar médias agrupadas para apoiar a sua análise. Contudo, certifique-se de referenciar corretamente os seus dados e de tratar quaisquer intervalos em branco ou irregulares.
O exemplo seguinte demonstra como calcular a média de cada 5 linhas numa coluna:
1. Introduza a seguinte fórmula na primeira célula em que pretende obter o resultado (por exemplo,)C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))Aqui, A2 é a célula inicial da sua coluna de dados, C2 é a célula onde a fórmula será inserida e 5 é o intervalo (número de linhas a considerar para o cálculo da média). Certifique-se de ajustar estas referências de acordo com o seu conjunto de dados real.
Após introduzir a fórmula, prima Enter. O primeiro resultado médio será apresentado. Veja a captura de ecrã:
2.Selecione a célula com a fórmula e, em seguida, arraste a alça de preenchimento para baixo até encontrar um valor de erro (como)#DIV/0!, caso existam menos de 5 valores nos dados restantes). Desta forma, obterá automaticamente as médias de cada grupo de 5 linhas. Veja a captura de ecrã:
Dicas e notas:Pode utilizar funções de tratamento de erros como SEERRO()para suprimir valores de erro caso os seus dados não se encaixem perfeitamente em grupos, por exemplo:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Para calcular a média de cada 5 colunas ao longo de uma linha, aplique a seguinte fórmula (coloque em)A3e arraste para a direita):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Aqui, A1 é a célula inicial, A3 é a sua célula de saída da fórmula e 5 é o número de colunas em cada grupo. Ajuste as referências das células conforme necessário para a disposição dos seus dados.
Após introduzir a fórmula e premir Enter, arraste a alça de preenchimento para a direita até obter um valor de erro. Veja a captura de ecrã:
Este método baseado em fórmulas é ideal para cálculos pontuais rápidos ou quando não pretende recorrer a ferramentas adicionais. Contudo, à medida que os seus dados mudam de tamanho ou formato, poderá ter de ajustar as fórmulas ou atualizar manualmente os intervalos de células, e lidar com grupos incompletos exigirá atenção extra.
Calcular a média de cada 5 linhas com Kutools para Excel
Kutools para Excel oferece uma solução gráfica conveniente se precisar frequentemente de calcular médias de grupos de linhas sem ter de lidar com fórmulas complexas. Com as funções Inserir quebras de página a cada outra linha e Estatísticas da Página de Dados, pode segmentar rapidamente os seus dados e calcular médias em lote com apenas alguns cliques. Este método é especialmente útil se pretender aplicar médias a intervalos repetíveis e visualizar diretamente a segmentação na sua folha de cálculo.
Após transferir e instalar Kutools para Excel, siga estes passos:
1. Clique em KUTOOLS PLUS > Impressão > Inserir quebras de página a cada outra linha. Veja a captura de ecrã:
2. Na caixa de diálogo Inserir quebras de página a cada outra linha, especifique o intervalo (por exemplo,)5) para inserir uma quebra de página após cada 5 linhas. Assim, o Kutools segmenta automaticamente os seus dados. Veja a captura de ecrã:
3. Em seguida, clique em KUTOOLS PLUS > Impressão > Estatísticas da Página de Dados. Veja a captura de ecrã:
4. Na caixa de diálogo Estatísticas da Página de Dados, selecione a(s) coluna(s) cuja média pretende calcular e, em seguida, escolha Média como método de cálculo. Veja a captura de ecrã:
5. Clique em OK e o Kutools insere imediatamente linhas de subtotal com valores médios em cada intervalo de 5 linhas. Veja a captura de ecrã:
Transfira e experimente gratuitamente o Kutools para Excel agora!
O Kutools simplifica a agrupação e análise repetitiva de dados sem precisar de ajustar fórmulas ou escrever scripts. No entanto, tenha em atenção que as quebras de página inseridas podem afetar o esquema de impressão e as vistas, pelo que poderá querer removê-las após utilização, caso não sejam necessárias no seu relatório.
Calcular a média de cada 5 linhas ou colunas com código VBA
Se precisar de calcular repetidamente a média de cada grupo fixo de linhas ou colunas em conjuntos de dados grandes ou em constante mudança, automatizar este processo com VBA pode poupar-lhe uma quantidade significativa de trabalho manual. O VBA permite percorrer os seus dados, agrupá-los conforme necessário e apresentar a média de cada lote. Este método é especialmente indicado para utilizadores avançados ou para quem trabalha com blocos de dados dinâmicos, evitando ainda que fórmulas sobrecarreguem a sua folha de cálculo. Abaixo encontra uma macro VBA genérica que pode adaptar facilmente às suas necessidades.
Automatizar o cálculo da média de cada 5 linhas:
1. Clique em Programador > Visual Basic para abrir a janela Microsoft Visual Basic para Aplicações. Em seguida, clique em Inserir > Módulo e cole o código abaixo no módulo:
Sub AverageEvery5Rows()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Integer, i As Integer, j As Integer
Dim LastRow As Long, StartRow As Long
Dim SumValue As Double, CountValue As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the data range to average (single column)", xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output", xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
LastRow = DataRange.Rows.Count
StartRow = 1
i = 0
Do While StartRow <= LastRow
SumValue = 0
CountValue = 0
For j = 0 To GroupSize - 1
If (StartRow + j) <= LastRow Then
SumValue = SumValue + DataRange.Cells(StartRow + j, 1).Value
CountValue = CountValue + 1
End If
Next j
If CountValue > 0 Then
OutputCell.Offset(i, 0).Value = SumValue / CountValue
Else
OutputCell.Offset(i, 0).Value = ""
End If
StartRow = StartRow + GroupSize
i = i + 1
Loop
End Sub2. Para executar o código, clique no botão 
ou prima F5. Selecione o seu intervalo de dados (uma coluna), indique a célula inicial para a saída e especifique o tamanho do grupo (por exemplo, 5). A macro apresentará a média de cada conjunto de 5 linhas, uma abaixo da outra, na coluna de saída indicada.
Pode utilizar uma macro semelhante para calcular a média de cada grupo de 5 colunas ao longo de uma linha.
Automatizar o cálculo da média de cada 5 colunas::
Sub AverageEveryNColumns()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Long
Dim totalCols As Long, totalRows As Long
Dim startCol As Long, endCol As Long, outCol As Long
Dim v As Variant
Dim r As Long, c As Long
Dim sumVal As Double, cntVal As Long
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set DataRange = Application.InputBox("Select the data range (single rows)", _
xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output (results will spill to the right)", _
xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
If GroupSize < 1 Then
MsgBox "Group size must be >= 1.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim prevCalc As XlCalculation
prevCalc = Application.Calculation
Application.Calculation = xlCalculationManual
totalCols = DataRange.Columns.Count
totalRows = DataRange.Rows.Count
v = DataRange.Value
outCol = 0
For startCol = 1 To totalCols Step GroupSize
endCol = startCol + GroupSize - 1
If endCol > totalCols Then endCol = totalCols
sumVal = 0
cntVal = 0
For r = 1 To totalRows
For c = startCol To endCol
If Not IsEmpty(v(r, c)) Then
If IsNumeric(v(r, c)) Then
sumVal = sumVal + CDbl(v(r, c))
cntVal = cntVal + 1
End If
End If
Next c
Next r
If cntVal > 0 Then
OutputCell.Offset(0, outCol).Value = sumVal / cntVal
Else
OutputCell.Offset(0, outCol).Value = ""
End If
outCol = outCol + 1
Next startCol
CleanExit:
Application.Calculation = prevCalc
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Calcular a média de cada 5 linhas com Tabela Dinâmica
Outra forma prática de calcular médias de grupo a cada 5 linhas é utilizar uma Tabela Dinâmica, combinada com uma coluna auxiliar de número de sequência ou índice para agrupar os dados. Este método revela-se especialmente útil para utilizadores que trabalham com dados estruturados em tabelas e precisam de um resumo rápido e interativo, sem ter de escrever fórmulas nem recorrer a complementos. A Tabela Dinâmica adapta-se dinamicamente a alterações nos dados e permite agrupamentos flexíveis — ideal para grandes volumes de informação ou tarefas regulares de elaboração de relatórios.
Veja como realizar esta operação utilizando uma coluna auxiliar e uma Tabela Dinâmica:
1.Adicione uma coluna «Índice» ou «Grupo» junto aos seus dados para identificar cada conjunto de 5 linhas. Na primeira linha de dados ()B2), introduza:
=INT((ROW()-ROW($A$2))/5)+1Esta fórmula rotula cada linha sequencialmente, atribuindo o mesmo número de grupo a cada bloco de 5 linhas. Preencha-a ao longo do seu conjunto de dados.
2. Selecione os seus dados e a nova coluna Índice e, em seguida, clique em Inserir > Tabela Dinâmica. Na caixa de diálogo de criação da Tabela Dinâmica, confirme o seu intervalo de dados e escolha onde colocar a Tabela Dinâmica.
3. Na lista de campos da Tabela Dinâmica recém-criada, arraste o campo «Group» para a área Linhas e o seu campo de valores (por exemplo, «Sales») para a área Valores.
4. Clique no menu pendente na área Valores, escolha Valor Configurações de Campo e selecione Média.
Agora, a sua Tabela Dinâmica apresenta a média de cada bloco de 5 linhas dos dados originais, agrupadas de forma conveniente pela coluna auxiliar.
As principais vantagens do método Tabela Dinâmica são a sua flexibilidade e a facilidade de atualização sempre que os Dados de Origem forem alterados. Contudo, exige a inclusão de uma coluna auxiliar e pode não ser adequado em situações em que os dados devam manter-se exatamente formatados ou inalterados.
Artigos relacionados:
Como calcular a média dos últimos 5 valores de uma coluna à medida que novos números são inseridos?
Como calcular a média dos 3 maiores ou menores valores no Excel?
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos