Baixe a apostila de exemplo
Este tutorial demonstrará como calcular subtotais por grupo usando a função SUMIFS no Excel e no Google Sheets.
Tabela de subtotal por categoria ou grupo
Primeiro, demonstraremos como criar uma tabela de resumo de subtotal dinâmica a partir de um intervalo de dados no Excel 365 em diante ou no Planilhas Google.
Usamos a função UNIQUE e a função SUMIFS para subtotalizar automaticamente o Número de produtos por Grupo de produtos:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Para criar esta tabela de subtotal, usamos a aplicação padrão da função SUMIFS para somar o Número de produtos que combinam com cada Grupo de produtos. No entanto, antes que isso seja possível, precisamos criar uma lista de Grupos de produtos. Os usuários do Microsoft Excel 365 e do Planilhas Google têm acesso à função UNIQUE para criar uma lista dinâmica de valores exclusivos de um intervalo de células. Neste exemplo, adicionamos a seguinte fórmula à célula E3:
| 1 | = ÚNICO (B3: B11) |
Quando esta fórmula é inserida, uma lista é criada automaticamente abaixo da célula para mostrar todos os valores exclusivos encontrados dentro do Grupo de produtos intervalo de dados. Neste exemplo, a lista se estendeu para cobrir E3: E5 para mostrar todos os 3 únicos Grupo de produtos valores.
Esta é 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 conforme os valores dos dados de entrada mudam.
Observe que no Excel 365, a função UNIQUE não diferencia maiúsculas de minúsculas, mas no Planilhas Google sim. Considere a lista {“A”; "uma"; “B”; “C”}. A saída da Função UNIQUE depende do programa:
- {"UMA"; “B”; “C”} no Excel 365
- {"UMA"; "uma"; “B”; “C”} no Google Sheets
Se estiver usando uma versão do Excel anterior ao Excel 365, você precisará adotar uma abordagem diferente. Isso é discutido na próxima seção.
Tabela subtotal por categoria ou grupo - Pré Excel 365
Se você estiver usando uma versão do Excel anterior ao Excel 365, a função UNIQUE não estará disponível para uso. Para replicar o mesmo comportamento, você pode combinar a função INDEX e a função MATCH com uma função COUNTIF para criar uma fórmula de matriz para produzir uma lista de valores únicos a partir de um intervalo de células:
| 1 | {= ÍNDICE ($ B $ 3: $ B $ 11, CORRESPONDÊNCIA (0, CONTADOR ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Para que esta fórmula funcione, as referências de células fixas precisam ser escritas com cuidado, com a função CONT.SE referenciando o intervalo $ E $ 2: E2, que é o intervalo que vai de 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. Esta fórmula é um Fórmula de matriz de 1 célula, que pode ser copiado e colado nas células E4, E5 etc. Não insira isso como uma fórmula de matriz para todo o intervalo E3: E5 em uma ação.
Da mesma forma que no exemplo anterior, uma função SUMIFS é então usada para subtotalizar o Número de produtos por Grupo de produtos:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Soma por categoria ou grupo - subtotais em tabelas de dados
Como alternativa ao método da tabela de resumo mostrado acima, podemos adicionar subtotais diretamente em uma tabela de dados. Vamos demonstrar isso usando as funções IF junto com a função SUMIFS para adicionar um Subtotal por Grupo para a tabela de dados original.
| 1 | = SE (B3 = B2, "", SOMASE (C3: C11, B3: B11, B3)) |
Este exemplo usa uma função SUMIFS aninhada em uma função IF. Vamos dividir o exemplo em etapas:
Para adicionar estatísticas de resumo diretamente em uma tabela de dados, podemos usar a função SUMIFS. Começamos totalizando o Número de produtos que correspondem ao relevante Grupo de produtos:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Esta fórmula produz um valor subtotal para cada linha de dados. Para mostrar os subtotais apenas na primeira linha de dados de cada Grupo de produtos, usamos a função IF. Observe que os dados já devem estar classificados por Grupo de produtos para garantir que os subtotais sejam exibidos corretamente.
| 1 | = SE (B3 = B2, "", SOMASE (C3: C11, B3: B11, B3)) |
A função IF compara cada linha de dados Grupo de produtos valor com a linha de dados acima dele, e se eles tiverem o mesmo valor, ele exibe uma célula em branco (“”).
Se o Grupo de produtos os valores são diferentes, a soma é exibida. Assim, cada Grupo de produtos sum é exibido apenas uma vez (na linha de sua primeira instância).
Classificando conjuntos de dados por grupo
Se os dados ainda não estiverem classificados, ainda podemos usar a mesma fórmula para o subtotal.
O conjunto de dados acima não é classificado por Grupo de produtos, então o Subtotal por Grupo coluna exibe cada subtotal mais de uma vez. Para obter os dados no formato que desejamos, podemos selecionar a tabela de dados e clicar em “Classificar de A a Z”.
Referências de Célula de Bloqueio
Para tornar nossas fórmulas mais fáceis de ler, mostramos algumas das fórmulas sem referências de células bloqueadas:
| 1 | = SE (B3 = B2, "", SOMASE (C3: C11, B3: B11, B3)) |
Mas essas fórmulas não funcionarão corretamente quando copiadas e coladas em outro lugar do arquivo. Em vez disso, você deve usar referências de células bloqueadas como este:
| 1 | = SE (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Leia nosso artigo sobre Bloqueio de referências de células para saber mais.
Usando tabelas dinâmicas para mostrar subtotais
A fim de remover 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 subtotais automaticamente e exibem totais e subtotais em vários formatos diferentes.
Soma por categoria ou grupo no Planilhas Google
Essas fórmulas funcionam da mesma forma no Planilhas Google e no Excel. No entanto, a função UNIQUE diferencia maiúsculas de minúsculas no Planilhas Google.
