Como somar cada n linhas no Excel?
Suponha que tenha uma coluna extensa de dados na sua folha de cálculo e precise de somar cada bloco consecutivo de 5 linhas — por exemplo, com fórmulas como =SOMA(B2:B6), =SOMA(B7:B11) e assim sucessivamente — colocando cada resultado numa coluna de saída separada, tal como ilustrado na imagem de exemplo. Executar esta tarefa manualmente, especialmente com conjuntos de dados volumosos, pode ser demorado e sujeito a erros. Este artigo apresenta várias abordagens práticas para somar eficaz e rigorosamente cada *n* linhas, incluindo fórmulas dinâmicas, utilitários do Kutools para Excel, macros VBA e técnicas com Tabelas Dinâmicas. Encontrará orientações claras sobre cenários de aplicação, ajuste de parâmetros e resolução de problemas comuns, garantindo um fluxo de trabalho fluido nas suas tarefas de sumarização de dados. Explore, nas opções abaixo, a solução mais adequada às suas necessidades.
Somar cada n linhas em Excel com fórmulas
Somar cada n linhas em Excel com Kutools para Excel
Somar cada n linhas em Excel com agrupamento do Tabela Dinâmica
Somar cada n linhas em Excel com fórmulas
Por exemplo, se precisar de somar cada grupo de 5 linhas num conjunto de dados, pode criar e aplicar uma fórmula que realize automaticamente esse cálculo. Este método é flexível e imediato, ideal para dados dinâmicos ou quando não tem um extra disponível; no entanto, as fórmulas poderão necessitar de ajustes caso a estrutura dos seus dados sofra alterações significativas.
1. Introduza a seguinte fórmula numa célula vazia onde pretenda apresentar o resultado do primeiro conjunto (por exemplo, na célula C2):
=SUM(OFFSET($B$2,(ROW()-ROW($B$2))*5,0,5,1))
Dica: Na fórmula acima, B2 é a célula inicial dos seus dados (ajuste conforme necessário para corresponder ao seu intervalo de dados) e 5 especifica a dimensão do agrupamento (número de linhas por bloco a somar). Para outros intervalos — por exemplo, a cada 10 linhas —, basta substituir o 5 por 10 em toda a fórmula.
2. Após introduzir a fórmula, arraste-a para baixo na coluna utilizando a alça de preenchimento, de modo a gerar a soma para cada grupo de n linhas. Continue até surgir um zero, o que indica que todos os blocos de dados foram processados. Veja a imagem:
Explicação dos parâmetros e dicas:
- DESLOC($B$2,…): Desloca a referência para baixo por um múltiplo do intervalo de agrupamento. Ajuste, conforme necessário, a referência absoluta para o início do seu conjunto de dados.
- LIN()-LIN($B$2): Devolve um índice sequencial (0 para o primeiro grupo, 1 para o segundo, etc.). Certifique-se de que começa a preencher a partir da linha correta ou ajuste conforme necessário.
- O resultado da fórmula poderá apresentar 0 em cálculos de grupos para além do seu intervalo de dados; isto é normal e indica apenas que chegou ao fim.
Notas:
1. Em alguns casos, poderá pretender que o resultado da soma — em vez de ser colocado numa coluna separada — apareça no final de cada bloco de n linhas, alinhado com os dados, por exemplo, na coluna C, conforme ilustrado abaixo:
Para conseguir isto, aplique a fórmula abaixo (assumindo que o seu primeiro valor está em B1 e que pretende somar cada 5 linhas) em C1 e arraste para baixo:
=IF(MOD(ROW(),5),"",SUM(OFFSET(B1,,,-5)))Isto coloca a soma apenas em cada enésima linha.Notas: Esta fórmula exige que o seu conjunto de dados comece na primeira linha da folha de cálculo. Ajuste as referências se os seus dados começarem noutra linha.
2. Para dados organizados em linhas (horizontalmente), nos quais pretenda somar cada n colunas, utilize esta fórmula na célula-alvo (por exemplo, C1):
=SUM(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Aqui, A1 marca a célula inicial, A3 é onde coloca esta fórmula e 5 é o número de colunas que pretende somar. Arraste a alça de preenchimento para a direita, conforme necessário. Consulte as imagens abaixo para uma ilustração adicional:
 |
 |
 |
Se encontrar resultados incorretos, verifique atentamente as suas referências absolutas e relativas. Ajuste a dimensão do agrupamento e as referências das células à localização real dos seus dados.
Somar cada n linhas em Excel com Kutools para Excel
Kutools para Excel oferece uma forma conveniente de somar cada n linhas sem precisar de criar fórmulas manualmente. É especialmente útil para grandes conjuntos de dados ou quando precisa de calcular subtotais em lote. O utilitário combina Quebra de Página com subtotais, simplificando tarefas repetitivas e reduzindo erros nas fórmulas. Tenha em atenção que esta funcionalidade está disponível apenas para utilizadores com o extra Kutools instalado.
Para implementar este método, siga estes passos:
1. Clique em KUTOOLS PLUS > Impressão > Inserir quebras de página a cada outra linha. Veja a imagem:
2. Na caixa de diálogo Inserir quebras de página a cada outra linha, especifique o intervalo de linhas para inserir uma quebra de página (por exemplo, 5) e, em seguida, clique em OK. A ferramenta irá inserir automaticamente uma quebra de página a cada conjunto de linhas no seu conjunto de dados. Veja a imagem:
3. De seguida, vá a KUTOOLS PLUS > Impressão > Estatísticas da Página de Dados:
4. Na caixa de diálogo Estatísticas da Página de Dados, selecione o(s) cabeçalho(s) de coluna que pretende somar e escolha o método de cálculo (Soma, Média, etc.) conforme necessário. Clique em OK para continuar. Veja a imagem:
5. Todos os grupos de n linhas apresentam agora os respetivos totais na saída, tal como pretendido. Veja a imagem:
Se precisar de remover posteriormente as quebras de página, pode selecionar Remover Todos os Quebra de Página no menu pendente Impressão do KUTOOLS PLUS. Caso os dados sejam alterados após a aplicação dos subtotais, poderá ter de repetir o processo para atualizar os resultados.
Descarregue e experimente gratuitamente o Kutools para Excel agora!
Somar cada n linhas em Excel com agrupamento do Tabela Dinâmica
Uma solução com Tabela Dinâmica revela-se particularmente vantajosa para utilizadores familiarizados com as ferramentas de Análise de Dados do Excel que pretendam resumir visualmente blocos de dados e, opcionalmente, explorar análises mais detalhadas. Ao adicionar uma coluna auxiliar para indexar agrupamentos, é possível agregar a soma de cada *n* linhas sem recorrer a fórmulas ou macros. Este método destaca-se pela rapidez na análise ou apresentação de dados agrupados, exigindo apenas uma configuração única da coluna auxiliar.
Aplicável em cenários como vendas por período, processamento em lote ou sumarização de blocos sequenciais de dados para relatórios.
Passos:
- Crie uma coluna auxiliar nos seus dados (por exemplo, na coluna C) com a seguinte fórmula em C2, assumindo que os seus dados começam na linha 2 e que pretende somar cada 5 linhas:
=INT((ROW(B2)-ROW($B$2))/5)+1- Arraste a fórmula para baixo em todas as linhas. Isso cria um índice de grupo único para cada bloco de 5 linhas.
- Em seguida, selecione o seu conjunto de dados — incluindo a coluna Novo Grupo — e insira uma Tabela Dinâmica (Inserir > Tabela Dinâmica).
- Na Lista de Campos da Tabela Dinâmica, arraste o campo Grupo (a coluna auxiliar) para a área Linhas e, em seguida, arraste a coluna de valores para a área Valores, definindo-a como Soma.
- A Tabela Dinâmica apresentará a soma de cada bloco de n linhas, conforme a indexação dos respetivos grupos.
Para agrupar por outros intervalos, substitua o valor 5 pelo pretendido na fórmula da coluna auxiliar. A eliminação ou inserção de linhas nos seus dados poderá exigir a atualização da coluna auxiliar e da Tabela Dinâmica. Se os resultados parecerem incorretos, verifique a fórmula do índice de agrupamento e confirme que a LISTA DE CAMPOS está configurada conforme indicado acima.
Esta abordagem é perfeita para relatórios iterativos, agregação de dados ou comparação de resultados por blocos, sem exigir a manutenção de fórmulas na área principal de cálculo após a configuração inicial. Para conjuntos de dados dinâmicos, basta recalcular a coluna auxiliar e atualizar a Tabela Dinâmica para refletir os novos resultados.
Somar cada n linhas em Excel com código VBA
Utilizar uma macro VBA é uma forma eficiente de somar em lote cada n linhas e enviar automaticamente os resultados para uma coluna ou intervalo especificado. Isto é especialmente útil para utilizadores avançados que lidam com conjuntos de dados massivos ou quando necessita de processos regulares e repetíveis. Com o VBA, o processo torna-se mais personalizável, mas exige que ative as macros e tenha um conhecimento básico da interface do editor VBA. Tenha cuidado ao selecionar o intervalo correto e a localização de saída para evitar sobrescrever dados.
1. Prima Alt + F11 para abrir o editor do Microsoft Visual Basic for Applications. No editor, clique em Inserir > Módulo para criar um novo módulo e copie o seguinte código para a janela do módulo:
Sub SumEveryNRows()
Dim srcRange As Range
Dim resultCol As Range
Dim nRows As Integer
Dim outRow As Long
Dim i As Long
Dim sumVal As Double
Dim lastRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcRange = Application.InputBox("Select the data range to sum:", xTitleId, "", Type:=8)
If srcRange Is Nothing Then Exit Sub
Set resultCol = Application.InputBox("Select the top cell where to output the results:", xTitleId, "", Type:=8)
If resultCol Is Nothing Then Exit Sub
nRows = Application.InputBox("How many rows per sum?", xTitleId, 5, Type:=1)
If nRows < 1 Then Exit Sub
lastRow = srcRange.Rows.Count
outRow = 0
For i = 1 To lastRow Step nRows
sumVal = Application.WorksheetFunction.Sum(srcRange.Cells(i, 1).Resize(Application.WorksheetFunction.Min(nRows, lastRow - i + 1), 1))
resultCol.Offset(outRow, 0).Value = sumVal
outRow = outRow + 1
Next i
End Sub2. Regresse ao Excel e, em seguida, prima a tecla F8 ou clique em Executar.
- A macro irá pedir-lhe que selecione a área de dados a somar (por exemplo, B2:B101).
- De seguida, selecione a célula acima da posição onde deseja inserir as somas (normalmente numa coluna em branco, por exemplo, C2).
- Introduza o número de linhas por grupo que pretende (por exemplo, 5).
A macro calcula a soma de cada bloco de n linhas e apresenta os resultados consecutivamente na coluna de saída especificada. Se o último grupo tiver menos de n linhas, a soma será feita com as linhas restantes. Caso selecione a célula errada, pode cancelar a operação ou executar novamente a macro. Esta abordagem é flexível e ideal para necessidades repetitivas ou automatizadas de resumo.
Dicas e resolução de problemas:
- Faça sempre uma cópia de segurança dos seus dados antes de executar macros, para evitar alterações inesperadas.
- Certifique-se de que as macros estão ativadas na sua pasta de trabalho (Ficheiro > Opções > Centro de Confiança).
- Se encontrar erros de permissão, verifique se a folha de cálculo está protegida ou se há restrições aplicadas ao intervalo de células.
Artigos relacionados:
Como somar valores com base em critérios de texto no Excel?
Como somar células com Dividir por Texto e Número 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