Como calcular a média a cada 5 linhas ou colunas no Excel?
Ao trabalhar com grandes conjuntos de dados no Excel, é comum precisar de cálculos de média para cada grupo de linhas ou colunas — como a cada 5 linhas ou a cada 5 colunas. Embora você possa inserir manualmente fórmulas como =AVERAGE(A1:A5), =AVERAGE(A6:A10), =AVERAGE(A11:A15) e assim por diante, isso rapidamente se torna impraticável se sua lista contiver centenas ou milhares de células. Repetir essas operações manualmente é demorado e suscetível a erros. Felizmente, o Excel oferece várias maneiras de automatizar essa tarefa, tornando a análise de dados muito mais eficiente e menos tediosa. Este artigo apresentará vários 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 VBA e técnicas de Tabela Dinâmica, para ajudá-lo a escolher a melhor solução para o seu cenário.
Calcular a média a cada 5 linhas ou colunas com fórmulas
Calcular a média a cada 5 linhas com Kutools para Excel
Calcular a média a cada 5 linhas ou colunas com código VBA
Calcular a média a cada 5 linhas com Tabela Dinâmica
Calcular a média a cada 5 linhas ou colunas com fórmulas
Se você prefere usar fórmulas padrão do Excel, pode automatizar o cálculo a cada 5 linhas ou colunas sem a necessidade de complementos ou scripts. Essa abordagem é particularmente adequada para conjuntos de dados estáticos onde você simplesmente precisa gerar conjuntos de médias para apoiar sua análise. No entanto, deve-se tomar cuidado para referenciar corretamente seus dados e lidar com quaisquer intervalos em branco ou irregulares.
O exemplo a seguir demonstra como calcular a média a cada 5 linhas em uma coluna:
1. Insira a seguinte fórmula na primeira célula onde você deseja 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 de saída da fórmula, e 5 é o intervalo (número de linhas para calcular a média). Certifique-se de ajustar essas referências de acordo com seu conjunto de dados real.
Depois de inserir a fórmula, pressione Enter. O primeiro resultado médio será exibido. Veja a captura de tela:
2. Selecione a célula da fórmula, depois arraste a alça de preenchimento para baixo até encontrar um valor de erro (como #DIV/0! se houver menos de 5 valores nos dados restantes). Isso recuperará automaticamente as médias para cada grupo de 5 linhas. Veja a captura de tela:
Dicas e Notas: Você pode usar funções de tratamento de erros como IFERROR() para suprimir valores de erro se seus dados não se encaixarem em grupos perfeitamente dimensionados, por exemplo:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Para calcular a média a cada 5 colunas em uma linha, aplique a seguinte fórmula (coloque em A3 e arraste para a direita):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Aqui, A1 é a célula inicial, A3 é a célula de saída da fórmula, e 5 é o número de colunas em cada grupo. Ajuste as referências de células conforme necessário para o layout dos seus dados.
Depois de inserir a fórmula e pressionar Enter, arraste a alça de preenchimento para a direita até encontrar um valor de erro. Veja a captura de tela:
Esse método baseado em fórmulas é ideal para cálculos rápidos únicos ou quando você não quer usar ferramentas adicionais. No entanto, à medida que seus dados mudam de tamanho ou forma, pode ser necessário ajustar as fórmulas ou atualizar manualmente os intervalos de células, e lidar com grupos incompletos pode exigir cuidado extra.
Calcular a média a cada 5 linhas com Kutools para Excel
Kutools para Excel oferece uma solução gráfica conveniente se você frequentemente precisa calcular a média de grupos de linhas sem gerenciar fórmulas complexas. Por meio das funções Inserir Quebra de Página a Cada Linha e Estatísticas da Página de Dados, você pode segmentar rapidamente seus dados e calcular médias em lote em poucos cliques. Esse método é especialmente útil se você deseja aplicar médias em intervalos repetíveis e visualizar agrupamentos diretamente na sua planilha.
Após baixar e instalar o Kutools para Excel, siga estas etapas:
1. Clique em Kutools Plus > Imprimir > Inserir Quebra de Página a Cada Linha. Veja a captura de tela:
2. Na caixa de diálogo Inserir Quebra de Página a Cada Linha, especifique o intervalo (por exemplo, 5) para inserir uma quebra de página após cada 5 linhas. Isso permite que o Kutools segmente seus dados automaticamente. Veja a captura de tela:
3. Em seguida, clique em Kutools Plus > Imprimir > Estatísticas da Página de Dados. Veja a captura de tela:
4. Na caixa de diálogo Estatísticas da Página de Dados, selecione a(s) coluna(s) que você deseja calcular a média, depois escolha Média como o método de cálculo. Veja a captura de tela:
5. Clique em OK, e o Kutools insere imediatamente linhas de subtotal com valores médios a cada intervalo de 5 linhas. Veja a captura de tela:
Baixe e experimente gratuitamente o Kutools para Excel agora!
O Kutools facilita a conclusão de agrupamentos e análises de dados repetitivos sem precisar ajustar fórmulas ou escrever scripts. No entanto, observe que as quebras de página inseridas podem afetar o layout e a visualização de impressão; portanto, você pode querer removê-las após o uso se elas não forem necessárias para o seu relatório.
Calcular a média a cada 5 linhas ou colunas com código VBA
Se você precisa calcular repetidamente o valor médio para um número fixo de linhas ou colunas em grandes conjuntos de dados ou em dados em constante mudança, automatizar esse processo com VBA pode economizar uma quantidade significativa de trabalho manual. O VBA permite que você percorra seus dados, agrupe-os conforme necessário e produza o resultado médio para cada lote. Esse método é especialmente adequado para usuários avançados ou aqueles que lidam com blocos de dados dinâmicos, e evita que fórmulas congestionem sua planilha. Abaixo está uma macro VBA de propósito geral que você pode adaptar facilmente.
Automatize o cálculo da média a cada 5 linhas:
1. Clique em Desenvolvedor > Visual Basic para abrir a janela Microsoft Visual Basic for Applications. Depois, 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 pressione F5. Selecione seu intervalo de dados (uma coluna), depois escolha a célula inicial para a saída e especifique o tamanho do grupo (por exemplo, 5). A macro produzirá a média para cada conjunto de 5 linhas, uma abaixo da outra na coluna de saída especificada.
Você pode usar uma macro similar para calcular a média a cada 5 colunas em uma linha.
Automatize o cálculo da média a 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 a cada 5 linhas com Tabela Dinâmica
Outra maneira prática de calcular médias de grupos a cada 5 linhas é usando uma Tabela Dinâmica, combinando-a com uma coluna de numeração sequencial ou índice para agrupar os dados. Esse método é particularmente útil para usuários que trabalham com dados estruturados em formato de tabela e precisam de um resumo rápido e interativo sem escrever fórmulas ou usar complementos. Uma Tabela Dinâmica lida dinamicamente com mudanças nos dados e suporta agrupamento flexível — ótimo para grandes conjuntos de dados ou tarefas de relatórios regulares.
Veja como realizar essa operação usando uma coluna auxiliar e uma Tabela Dinâmica:
1. Adicione uma coluna "Índice" ou "Grupo" ao lado de seus dados para marcar cada grupo de 5 linhas. Na primeira linha de dados (B2), insira:
=INT((ROW()-ROW($A$2))/5)+1Essa fórmula rotula cada linha em sequência, atribuindo o mesmo número de grupo a cada 5 linhas. Preencha essa fórmula ao longo de seu conjunto de dados.
2. Selecione seus dados e a nova coluna Índice, depois clique em Inserir > Tabela Dinâmica. Na caixa de diálogo de criação da Tabela Dinâmica, confirme seu intervalo de dados e escolha onde colocar a Tabela Dinâmica.
3. Na lista de campos da nova Tabela Dinâmica criada, arraste o campo "Grupo" para a área de Linhas e seu campo de valor (por exemplo, "Vendas") para a área de Valores.
4. Clique na seta suspensa na área de Valores, escolha Configurações de Campo de Valor e selecione Média.
Agora, sua Tabela Dinâmica mostra a média para cada 5 linhas dos dados originais, convenientemente agrupados pela coluna auxiliar.
Os principais benefícios do método de Tabela Dinâmica são sua flexibilidade e atualizações fáceis quando seus dados de origem mudam. No entanto, ele requer a adição de uma coluna auxiliar e pode não ser adequado para situações onde os dados precisam permanecer formatados precisamente ou inalterados.
Artigos relacionados:
Como calcular a média dos últimos 5 valores de uma coluna à medida que novos números entram?
Como calcular a média dos 3 valores superiores ou inferiores no Excel?
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins