Somar valores por grupo no Excel

Por vezes, poderá precisar de somar valores com base num grupo numa tabela. Por exemplo, tem uma lista de produtos com os respetivos montantes noutra coluna e pretende obter o montante total de cada produto, tal como ilustrado na imagem seguinte. Este tutorial apresenta algumas fórmulas para resolver esta tarefa no Excel.

• Somar valores por grupo – subtotais na tabela de dados original
• Somar valores por grupo – subtotais noutra localização

Somar valores por grupo – subtotais na tabela de dados original

Aqui está a sintaxe genérica para somar valores com base num grupo no Excel:

• group_name: A célula com o Nome do Grupo que pretende somar;
• cell_above_group_name: A célula acima do Nome do Grupo;
• group_range: O intervalo de células que contém o Nome do Grupo;
• sum_rangeO intervalo de células correspondente ao Nome do Grupo especificado, a ser somado em conjunto.

Se os dados estiverem ordenados pela coluna de agrupamento, os mesmos produtos ficarão agrupados, tal como ilustrado na imagem seguinte. Para obter subtotais por grupo, pode criar fórmulas com base nas funções SE e SOMASE.

1. Copie e cole a seguinte fórmula numa célula vazia junto aos seus dados:

• Nota: Na fórmula:
• A1é a célula do cabeçalho e A2é a primeira célula que contém o nome do produto que pretende utilizar;
• A2:A13é a lista que contém os nomes dos produtos com base nos quais pretende somar;
• B2:B13 é a coluna de dados cujo subtotal pretende obter.

2. Em seguida, arraste a alça de preenchimento para as células onde pretende aplicar esta fórmula e os subtotais serão calculados com base em cada nome de produto, conforme ilustrado:

Explicação da fórmula:

=IF(A2=A1,«»,SUMIF($A$2:$A$13,A2,$B$2:$B$13))

• SUMIF($A$2:$A$13,A2,$B$2:$B$13): Esta função SOMASE soma apenas os valores no intervalo B2:B13 cujas células correspondentes no intervalo A2:A13 forem iguais ao critério em A2.
• IF(A2=A1,«»,SUMIF($A$2:$A$13,A2,$B$2:$B$13)): Esta função SE compara cada valor na coluna A com o da célula imediatamente acima. Por exemplo, se o valor em A2 for igual ao de A1, não devolve nada («»); caso contrário, devolve o resultado da função SOMASE.

Somar valores por grupo – subtotais noutra localização

Se os valores de cada grupo não estiverem agrupados e aparecerem listados aleatoriamente numa coluna, para somar os valores correspondentes com base num grupo ou categoria, deverá extrair o Nome do Grupo a partir dos nomes dos produtos e, em seguida, obter o subtotal com base nesse Nome do Grupo, tal como ilustrado na imagem seguinte.

1. Em primeiro lugar, extraia os nomes únicos do grupo utilizando a seguinte fórmula matricial e, em seguida, prima simultaneamente as teclas Ctrl + Shift + Enter para obter o primeiro resultado.

• Nota: Na fórmula:
• A2:A13é o intervalo de células de onde extrair todos os valores únicos;
• D1 é a célula acima da fórmula introduzida.

2. Em seguida, selecione a célula com a fórmula e arraste a alça de preenchimento para baixo até que todos os nomes dos produtos apareçam, conforme ilustrado:

3. Agora, pode somar os valores com base no Nome do Grupo que extraiu. Neste caso, será utilizada a função SOMASE. Introduza a fórmula seguinte numa célula vazia; neste exemplo, colocá-la-ei na célula E2.

4. Em seguida, arraste a alça de preenchimento para copiar esta fórmula e obter o total encomendado dos restantes grupos, conforme ilustrado:

Função relacionada utilizada:

Mais artigos: