Neste artigo, aprenderemos como calcular a soma por grupos em linhas e colunas no Excel.
Por exemplo, você tem uma grande lista de dados e precisa encontrar a SOMA dos números com um conjunto de n valores.
Para este artigo, precisaremos usar as seguintes funções:
- Função SUM
- Função OFFSET
A função SUM é uma função matemática incorporada que retorna a soma dos números dados como argumento para a função
Sintaxe:
= SOMA (número1, [número2], …)
A função OFFSET retorna uma célula ou um número especificado de intervalo de células percorrido por uma referência de célula.
Sintaxe:
= OFFSET (referência, linhas, colunas, [altura], [largura])
Referência: referência de célula para começar
Linhas: número de linhas percorridas a partir da referência da célula
Colunas: número de colunas cruzadas da referência de célula.
[altura]: altura ou número de células na coluna a serem escolhidas.
[largura]: largura ou número de células na linha a ser escolhida.
Os 3 primeiros argumentos da função são o caminho para a célula necessária.
4º e 5º argumentos da função indicam o número de células a serem retornadas.
Agora faremos uma fórmula com essas funções. Aqui forneceremos os dados e precisamos encontrar a SOMA com um número específico de células.
Use a fórmula:
= SUM (OFFSET (referência, ro, co, he, wi))
Explicação:
- A função OFFSET começa a partir da referência e atravessa ro número de linhas e co número de colunas da referência de célula.
- Agora, a função começa a selecionar grupos de números com ele número de valores na coluna e nós número de valores na linha.
- A função OFFSET agora retorna a matriz de números para a função SUM.
- A função SUM soma todos os números e retorna SUM.
Vamos testar esta fórmula executando-a em um exemplo
Aqui temos dados de A1: D16. precisamos encontrar a soma da quantidade e do preço total em um grupo de 5 com a referência fornecida na célula A1.
Primeiro, precisamos encontrar a SOMA das 5 primeiras quantidades, que são os valores {100, 23, 38, 91, 53}
Use a fórmula:
= SOMA (OFFSET (A1, 1, 2, 5, 1))
Explicação:
- A função OFFSET começa na célula A1 e atinge a célula C2 atravessando a 1 linha e 2 colunas.
- Precisamos das primeiras 5 células de C2 a C6. Portanto, a altura 5 e a largura nos retornarão os números mostrados abaixo.
= SOMA ({100; 23; 38; 91; 53})
- Agora a função SUM retorna a SUM dos valores.
Aqui, o A1 é fornecido como referência de célula e o restante dos argumentos são fornecidos diretamente para a função.
Como você pode ver no instantâneo acima, a soma das cinco primeiras quantidades acaba sendo 305 .
Agora obteremos o SUM para todos os outros grupos de 5 mudando o argumento Row da função
Use a fórmula:
= SOMA (OFFSET (A1, 6, 2, 5, 1))
Como você pode ver a soma dos próximos cinco no instantâneo acima. Para os próximos cinco, altere o argumento da linha para 11.
Obtenha a SOMA de TotalPrice para o mesmo, alterando o argumento Coluna para a função.
Como você pode ver na fórmula acima, você pode obter os valores SUM por grupos usando a função OFFSET.
Observações: a função retorna #VALUE! erro se o argumento para a função não for numérico.
Espero que este artigo sobre como somar por grupos de OFFSET em linhas e colunas no Excel seja explicativo. Encontre mais artigos sobre as funções SUMPRODUCT aqui. Por favor, compartilhe sua consulta abaixo na caixa de comentários. Nós iremos atendê-lo.
Como usar a função OFFSET no excel
Como usar a função SUM no Excel
Como usar a função ISFORMULA no Excel
Como remover texto no Excel começando de uma posição
Como usar a função ROWS no Excel
Validação de entradas de texto
Crie uma lista suspensa no Excel com cores
Remova os espaços iniciais e finais do texto no Excel
Artigos populares
Editar uma lista suspensa
Referência absoluta no Excel
Se com formatação condicional
Se com curingas
Vlookup por data
Junte-se ao nome e sobrenome no excel
Contar células que correspondam a A ou B
Converter polegadas em pés e polegadas no Excel 2016
50 Atalho do Excel para aumentar sua produtividade