Somar por Categoria ou Grupo – Excel e Google Planilhas

Este tutorial demonstra como calcular subtotais por grupo usando a função SOMASES no Excel e no Google Planilhas.

Tabela de Subtotal por Categoria ou Grupo

Este primeiro exemplo requer que você tenha acesso à função ÚNICO. Essa função está disponível nas versões mais recentes do Excel, Excel 365 ou Planilhas Google. Se você não tiver acesso a essa função, pule para a próxima seção.

Podemos usar a função ÚNICO e a função SOMASES para subtotalizar automaticamente o número de produtos por grupo de produtos. Esta é a FUNÇÃO SOMASES final:

=SOMASES(C3:C11;B3:B11;E3)

Entretanto, primeiro, adicionamos a função ÚNICO à célula E3:

=ÚNICO(B3:B11)

Quando essa fórmula é inserida, uma lista é criada automaticamente abaixo da célula para mostrar todos os valores exclusivos encontrados no Grupo de Produto.

Essa é uma função de matriz dinâmica em que o tamanho da lista de resultados não precisa ser definido e ela diminuirá e aumentará automaticamente à medida que os valores dos dados de entrada forem alterados.

Observe que, no Excel 365, a função ÚNICO não diferencia maiúsculas de minúsculas, mas no Google Planilhas , sim.

Tabela de Subtotal por Categoria ou Grupo – Sem a Função ÚNICO.

Se você estiver usando uma versão do Excel sem a função ÚNICO , poderá combinar a função ÍNDICE e a função CORRESP com uma função CONT.SE para criar uma fórmula de matriz para produzir uma lista de valores exclusivos de um intervalo de células:

=ÍNDICE($B$3:$B$11;CORRESP(0;CONT.SE($E$2:E2;$B$3:$B$11);0))

 

Para que essa fórmula funcione, as referências de células fixas precisam ser escritas cuidadosamente, com a função CONT.SE fazendo referência ao intervalo $E$2:E2, que é o intervalo que começa em E2 até a célula acima da célula que contém a fórmula.

A fórmula também precisa ser inserida como uma fórmula de matriz, pressionando CTRL + SHIFT + ENTER após ter sido escrita. Essa fórmula é uma fórmula de matriz de 1 célula, que pode ser copiada e colada nas células E4, E5 etc. Não insira isso como uma fórmula de matriz para todo o intervalo E3:E5 em uma única ação.

Da mesma forma que no exemplo anterior, uma função SOMASES é usada para subtotalizar o número de produtos por grupo de produtos:

=SOMASES(C3:C11;B3:B11;E3)

Soma por Categoria ou Grupo – Subtotais em Tabelas de Dados

Como alternativa, podemos adicionar subtotais diretamente em uma tabela de dados com as funções SE e SOMASES.

=SE(B3=B2;"";SOMASES(C3:C11;B3:B11;B3))

 

Esse exemplo usa uma função SOMASES aninhada em uma função SE. Vamos dividir o exemplo em etapas:

Começamos totalizando o número de produtos que correspondem ao grupo de produtos relevante:

=SOMASES(C3:C11;B3:B11;B3)

Essa fórmula produz um valor de subtotal para cada linha de dados. Para mostrar os subtotais somente na primeira linha de dados de cada grupo de produtos, usamos a função SE. Observe que os dados já devem estar ordenados por Grupo de Produtos para garantir que os subtotais sejam exibidos corretamente.

=SE(B3=B2;"";SOMASES(C3:C11;B3:B11;B3))

A função SE compara o valor do Grupo de produtos de cada linha de dados com a linha de dados acima dela e, se eles tiverem o mesmo valor, ela gera uma célula em branco (“”).

Se os valores do Grupo de Produtos forem diferentes, a soma será exibida. Dessa forma, a soma de cada Grupo de Produtos é exibida apenas uma vez (na linha de sua primeira instância).

Classificação de Conjuntos de Dados por Grupo

Se os dados ainda não estiverem classificados, ainda poderemos usar a mesma fórmula para o subtotal.

O conjunto de dados acima não está classificado por Grupo de Produtos, portanto a coluna Subtotal por Grupo exibe cada subtotal mais de uma vez. Para colocar os dados no formato que desejamos, podemos selecionar a tabela de dados e clicar em Classificar de A a Z.

Bloqueio de Células de Referências

Para facilitar a leitura de nossas fórmulas, mostramos algumas delas sem referências de células bloqueadas:

=SE(B3=B2;"";SOMASES(C3:C11;B3:B11;B3))

Mas essas fórmulas não funcionarão corretamente quando forem copiadas e coladas em outro local do arquivo. Em vez disso, você deve usar referências de células bloqueadas como esta:

=SE(B3=B2;"";SOMASES($C$3:$C$11;$B$3:$B$11;B3))

Leia nosso artigo sobre Bloqueio de referências de células para saber mais.

Uso de Tabelas Dinâmicas para Mostrar Subtotais

Para eliminar a necessidade de pré-classificar os dados por Grupo de Produtos, podemos usar o poder das tabelas dinâmicas para resumir os dados. As tabelas dinâmicas calculam os subtotais automaticamente e exibem os totais e subtotais em vários formatos diferentes.

Soma por Categoria ou Grupo no Google Planilhas

Essas fórmulas funcionam da mesma forma no Planilhas Google e no Excel. No entanto, a função ÚNICO diferencia maiúsculas de minúsculas no Google Planilhas.